Microsoft Office Excel 2007. Работа с мастером функций

Цели работы:

- научиться использовать мастер функций;

- научиться использовать логические функции;

- научиться использовать математические функции.

В результате выполнения лабораторной работы студент должен:

знать:

- назначение Мастера функций;

- категории Мастера функций;

- аргумент функции;

уметь:

- использовать логические функции «ЕСЛИ» и «И»;

- пользоваться математической функцией «СУММ»;

- пользоваться статистическая функция «СРЗНАЧ» и «РАНГ».

Пояснение к работе

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

Чтобы воспользоваться Мастером, необходимо: на вкладке Формулы нажать кнопку Вставить функцию ; в открывшемся диалоговом окне в разделе Категория выберите необходимую Вам категорию.

Логические функции И, ИЛИ, НЕ, ЕСЛИ могут принимать два значения ИСТИНА, если утверждение истинно или ЛОЖЬ, если оно ложно.

Их конструкция:

=И(логическое выражение 1; логическое выражение 2; …).

Она = ИСТИНА(), если истинно и выражение 1, и выражение 2, и все другие выражения стоящие в скобках, или же = ЛОЖЬ(), если хотя бы одно из них ложно.

=ИЛИ(логическое выражение 1; логическое выражение 2; …).

Она =ИСТИНА(), если истинно или выражение 1, или выражение 2, или же =ЛОЖЬ(), если все выражения внутри скобки ложны.

=НЕ(логическое выражение).

Она =ИСТИНА(), если логическое выражение ложное или же ЛОЖЬ(), если оно истинно логическое выражение состоит из двух выражений формального типа или чисел, соединенных знаками отношений: <, >, <=, >=, <>(не равно).

Особо важна логическая функция ЕСЛИ. Она имеет вид:

=ЕСЛИ(условие; выражение 1; выражение 2).

Если условие =ИСТИНА(), то функция принимает значение выражения 1, если же условие =ЛОЖЬ(), то значение выражения 2.

Выражения могут быть формулы, числовые или символьные значения. Примеры:

=ЕСЛИ(Е6>1000;”пригоден”; “не пригоден”).

=ЕСЛИ(Е5+Е6<100;C7+5;C9/B3+8).

Математическая функция СУММ суммирует все числа в интервале ячеек.

=СУММ(число1;число2;...)

Число1, число2,... — от 1 до 255 аргументов, для которых требуется определить итог или сумму.

Статистическая функция СРЗНАЧ возвращает среднее арифметическое своих аргументов.

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

Число1, число2,... — от 1 до 255 аргументов, для которых вычисляется среднее.

Статистическая функция РАНГ возвращает ранг числа в списке чисел. Ранг числа — это его величина относительно других значений в списке. (Если отсортировать список, то ранг числа будет его позицией.)

=РАНГ(число;ссылка;порядок)

Число — число, для которого определяется ранг.

Ссылка — массив или ссылка на список чисел. Нечисловые значения в ссылке игнорируются.

Порядок — число, определяющее способ упорядочения.

Задание

Задача № 1

1. Создайте таблицу по образцу (рис. 10.1):

Рис. 10.1 - Исходная таблица

2. Лист переименуйте в «Задача 1».

3. При расчете среднего балла (столбец I) используйте функции «СРЗНАЧ» (рис. 10.2).

Число 1 - интервал ячеек, содержащих оценки студента, для которого вычисляется средний балл; Число 2 - количество знаков после запятой.

Рис. 10.2 - Мастер функций СРЗНАЧ для вычисления среднего балла

4. При расчете суммы стипендии (столбец K) с помощью Мастера функций используйте формулу:

- если средний балл (столбец I) = 5.0, то стипендия (столбец K) = 1050;

- если средний балл >= 4.0, то стипендия = 960;

- если средний бал > 3.5 и < 4, то стипендия = 750;

- иначе стипендия = 0.

Для получения необходимого результата используем функцию «ЕСЛИ» (рис. 10.3)

Рис. 10.3 - Мастер функций ЕСЛИ

5. В ячейке «Примечание» ввести данные (функция «ЕСЛИ»):

- если средний балл (столбец I) = 5.0, то Примечание (столбец L) = повышенная стипендия на 50%;

- если средний балл >= 4.0, то Примечание = повышенная стипендия на 25%;

- если средний бал > 3.5 и < 4, то Примечание = минимальная стипендия.

Выполняем аналогично п.3, вместо суммы стипендии указывается столбец Примечание, в результате формула принимает вид (рис. 10.4):

Рис. 10.4 - Формула «Примечания»

6. В столбце «Рейтинг» указывается место учащегося по результатам успеваемости. Данные рассчитываются с помощью Мастера функций «РАНГ» (рис. 10.5). Аргументы: Число – ячейка (I3), содержащая средний балл студента, для которого считаем рейтинг, Ссылка – интервал ячеек (I3:I12): все заполненные ячейки из столбца «средний балл».

Рис. 10.5 - Мастер функций РАНГ

Рис. 10.6 – Выходные данные

7. Сохранить файл на своем компьютерном носителе.

Задача №2

1. Создать таблицу расчета стипендии по образцу (рис. 10.7).

Рис. 10.7 – Исходные данные

2. Минимальная оценка вычисляется с помощью формулы «МИН»: число 1 – диапазон ячеек в которых нужно найти минимальное число.

3. Значение столбца Стипендия вычислить по формуле «ЕСЛИ», учитывая минимальную стипендию и возможность получения повышенной стипендии в зависимости от минимальной оценки. Стипендия начисляется следующим образом:

Ø при минимальной оценке 2 (столбец Е) – нет стипендии (в ячейке должен быть «0»),

Ø при минимальной оценке 3 – минимальная стипендия (ячейка С1),

Ø при минимальной оценке 4 – стипендия выше в 1,25 раза (минимальная стипендия * 1,25),

Ø при минимальной оценке 5 – стипендия выше в 1,5 раза (минимальная стипендия * 1,5).

Рис. 10.8 – Выходные данные

4. На отдельном листе создать таблицу, которая будет представлять собой ведомость на выдачу стипендии (рис. 10.9), т.е. содержать фамилии тех студентов, которые будут получать стипендию, а так же размер их стипендии.

Рис. 10.9 – Ведомость на выдачу стипендии

5. На отдельном листе подсчитать количество студентов, которые учатся на «4» и «5», имеют оценку «3», имеют оценку «2» (рис. 10.10).

Рис. 10.10 – Успеваемость

6. Создать круговую диаграмму «Качество успеваемости», содержащую процентное отношение этих категорий студентов друг к другу (рис. 10.11).

Рис. 10.11 – Успеваемость

 

Работа в лаборатории

1. Выполнить предложенные задания.

2. Сформировать отчет.

Содержание отчета

1. Созданный файл.

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

1. Способы изменения ширины столбца и высоты строки.

2. Как Excel разбивает таблицу на страницах при печати?

3. Каким образом вставить (удалить) строку (столбец, ячейку)?

4. Что делать, если подменю раскрываются в неполном составе? Как получить доступ к нужным командам? (3 способа).

5. На панели инструментов нет некоторых знакомых кнопок, и, напротив, присутствуют совершенно ненужные. Как вернуть привычное состояние панели?

6. Что делать, если при двойном щелчке на ячейке рамка вокруг не изменилась, внутри ячейки нет точки вставки и вместо редактирования происходит замена старых данных вводимыми новыми?

ЛАБОРАТОРНАЯ РАБОТА №11