Работа с таблицами Excel

Лабораторная  работа №3 

Тема: работа с таблицами Excel

Цель  работы: Освоить операции по использованию  данных, содержащихся в таблице Excel

Задание к лабораторной работе 

      

1. Создаем таблицу успеваемости, заполняем ее группой студентов в количестве 25 человек, произвольно ставим оценки.

 

Вычисляем среднюю оценку каждого студента, используя функцию СРЗНАЧ(). Получим  следующие значения:

       Вычислим  размер стипендии для каждого  студента. Размер стипендии зависит  от средней оценки. Зависимостьв ыглядит следующим образом:

       Если  сред.балл =5, стипендия – МРОТ*5;

       Где МРОТ составляет 130 рублей

       Если 5 > сред. Балл ≥4,5 Стипендия =МРОТ*4

       Если 4.5>Сред.балл ≥4,0 Стипендия = МРОТ *3

       Если 4,0>сред.балл≥3,5 стипендия = МРОТ *2

       Если  сред.балл <3,5 Стипендия = 0.

       Получаем  следующие расчеты в таблице:

 

      Создадим  итоговую таблицу результатов сессии

 

      Создадим  пять проивзольных диаграмм по таблицам. 

      Диаграмма распределения видов оценок по группе:

 

Диаграмма одного студента по всем предметам:

 

 

Размер  стипендии в группе: 

 

 

Круговая  диаграмма распределения оценок по одной дисциплине: 

 

График  средних оценок по группе 
 

Контрольные вопросы: 

1. Порядок вызова Мастера функций  с панели инструментов и меню Вставка

 

      Excel содержит более 400 встроенных  функций. Поэтому непосредственного  вводить с клавиатуры в формулу  названия функций и значения  входных параметров не всегда  удобно. В Excel есть специальное  средство для работы с функциями  — Мастер функций. При работе с этим средством вам сначала предлагается выбрать нужную функцию из списка категорий, а затем в окне диалога предлагается ввести входные значения.

      Мастер  функций вызывается командой Вставка | Функции или нажатием на кнопку  Мастер функций. Эта кнопка расположена на панели инструментов Стандартная, а также в строке формул.  
 

2. Дать  формат и объяснить  работу логических функций  ЕСЛИ, И, ИЛИ.

 

    ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь).

• Предназначение: Функция ЕСЛИ выполняет то ("Значение если ИСТИНА") или иное ("Значение если ЛОЖЬ") действие в зависимости от того, выполняется (равно ИСТИНА) условие или нет (равно ЛОЖЬ).
• аргумент1. Логическое выражение: Все, что дает в результате логические значения ЛОЖЬ или ИСТИНА. Обычно либо выражения отношения (A1>=12) либо функции, возвращающие логические значения (И, ИЛИ).
• аргумент2. Значение если ИСТИНА: любое допустимое в Excel выражение.
• аргумент3. Значение если ЛОЖЬ: любое допустимое в Excel выражение.
• возвращаемое значение: может возвращать значения любых типов, в зависимости от аргументов 2 и 3.

Функция ЕСЛИ позволяет организовать в формуле  ветвление. Вспомните сказки: налево пойдешь — коня потеряешь, прямо пойдешь — в болото попадешь, направо пойдешь — засосёт в чёрную дыру. Использование функций ЕСЛИ, И, ИЛИ граничит с программированием. Неудивительно, что для многих людей разобраться, как они работают, очень сложно. В голове должен быть чёткий алгоритм решения задачи и требуется хорошее понимание понятия "тип данных"

И

Синтаксис: Логич_знач И( логич_знач1; логич_знач2; ... ; логич_знач30 )

• Предназначение: Функция И используется тогда, когда нужно проверить, выполняются ли несколько условий ОДНОВРЕМЕННО. Одно из наиболее часто испльзуемых применений функции И — проверка, попадает ли число x в диапазон от x1 до x2.
• аргументы: Функция И принимает от 1 до 30 аргументов (в Office 2007 — до 256), каждый из которых является логическим значением ЛОЖЬ или ИСТИНА, либо любым выражением или функцией, которое в результате дает ЛОЖЬ или ИСТИНА.
• возвращаемое значение: Функция И возвращает логическое значение. Если ВСЕ аргументы функции И равны ИСТИНА, возвращает ИСТИНА. Если хотя бы один аргумент имеет значение ЛОЖЬ, возвращает ЛОЖЬ.

Примечание: Функция И почти никогда не используется сама по себе, обычно её используют в качестве аргумента других функций, например, ЕСЛИ.

ИЛИ

Синтаксис: Логич_знач ИЛИ( логич_знач1; логич_знач2; ... ; логич_знач30 )

• Предназначение: Функция ИЛИ используется тогда, когда нужно проверить, выполняется ли ХОТЯ-БЫ ОДНО из многих условий.
• аргументы: Функция ИЛИ принимает от 1 до 30 аргументов (в Office 2007 — до 256), каждый из которых является логическим значением ЛОЖЬ или ИСТИНА, либо любым выражением или функцией, которое в результате дает ЛОЖЬ или ИСТИНА.
• возвращаемое значение: Функция ИЛИ возвращает логическое значение. Если ХОТЯ БЫ ОДИН аргумент имеет значение ИСТИНА, возвращает ИСТИНА. Если ВСЕ аргументы имеют значение ЛОЖЬ, возвращает ЛОЖЬ.

Примечание: Функция ИЛИ почти никогда не используется сама по себе, обычно её используют в качестве аргумента других функций, например, ЕСЛИ. 

3. Дать  формат и объяснить работу функций СРЗНАЧ и СУММЕСЛИ

 

    СРЗНАЧ(число1; число2; ... ; число30)

    Функция СРЗНАЧ (среднее значение) принимает  от 1 до 30 аргументов (в Office 2007 — до 255) и возвращает их среднее арифметическое (сумма чисел, делённая на количество чисел). Эту функцию также можно вставить с помощью кнопки "сигма"

    Часто необходимо не просто просуммировать все значения в диапазоне, а включить в сумму только те, которые удовлетворяют  определённому условию. Функция  СУММ суммирует все, что ей дают, функция СУММЕСЛИ позволяет просуммировать только значения, соответствующие определённому условию 

    Синтаксис: СУММЕСЛИ(диапазон; критерий; [диапазон_суммирования] )

• диапазон: Проверяемый диапазон, каждая ячейка из которого проверяется на соответствие условию, указанному во втором аргументе.
• критерий: Условие для суммирования, на соответствие которому проверяется каждая ячейка из проверяемого диапазона. Если необходимо использовать операцию сравнения, то "логическое выражение" указывается без левого операнда и заключается в двойные кавычки (например, ">=100" — суммировать все числа, большие 100). Также можно использовать текстовые значения (например, "яблоки" — суммировать все значения, находящиеся напротив текста "яблоки") и числовые (например, 300 — суммировать значения в ячейках, значения в которых 300).
• диапазон_суммирования: Необязательный аргумент, используется тогда, когда проверяемый диапазон и диапазон суммирования находятся в разных диапазонах. Если он не указан, то в качестве диапазона суммирования используется проверяемый диапазон (первый аргумент). Если он указан, то суммируются значения из ячеек этого диапазона, находящиеся "напротив" соответствующих ячеек проверяемого диапазона.

 

4. Порядок вставки функции  в формулу

 

      Вводить формулу надо со знака равенства. Это надо для того, чтобы Excel понял, что в ячейку вводится именно формула, а не данные.

      Чтобы правильно использовать знаки надо четко представлять их приоритет.

1. В первую очередь выполняются выражения внутри скобок.
2. Умножение и деление имеют более высокий приоритет чем сложение и вычитание.
3. Операторы с одинаковым приоритетом выполняются слева направо.

      Если  в формуле количество закрывающих  и открывающих скобок не совпадает, Excel выдаст сообщение об ошибке и предложит вариант ее исправления. Сразу после ввода закрывающей скобки Excel отображает жирным шрифтом (или другим цветом) последнюю пару скобок, что очень удобно при наличии в формуле большого количества скобок.

      Дабы  не ошибиться при вводе адресов  ячеек, можно использовать при вводе  ссылок мышь. 

5. Порядок построения таблицы  по данным другой таблицы.

 

    Для построения сводной таблицы данные должны быть организованы в виде списка. Порядок следования столбцов не важен, но их логичное расположение упростит восприятие данных.

    Чтобы программа могла построить сводную  таблицу, список данных должен удовлетворять  нескольким базовым требованиям:

    ·         список не должен содержать пустых строк и пустых столбцов;

    ·         каждый столбец должен обладать уникальным именем;

    ·         в ячейках, соседствующих со списком, не должно быть лишних данных, — это означает, что у левой и правой границ списка должен находиться пустой столбец (или край листа), а снизу должна находиться минимум одна пустая строка;

    ·         список не может содержать повторяющихся ключей