Составим таблицу, содержащую сведения о результатах сдачи экзаменов по пяти предметам для студентов, на примере которой будем изучать работу с функциями и другими командами.
Ø В первую строку занесите заголовок: РЕЗУЛЬТАТЫ СЕССИИ
Ø В ячейки второй строки: Фамилия, Физика, Химия, История, Информатика, Экология.
Ø Заполните семь строк фамилиями студентов и оценками.
По результатам сессии надо назначить стипендию студентам, исходя из следующих правил: если среди оценок есть двойка, стипендию не назначать; если студент получил только пятерки, то назначить стипендию повышенную на 50%; если оценки только пятерки и четверки, стипендия повышается на 25%; во всех остальных случаях - обычная стипендия.
Для определения отличника, двоечника или троечника достаточно вычислить минимальный балл по оценкам для каждого студента, по его значению определить статус студента. Для определения студента, который может рассчитать на стипендию, повышенную на 25%, необходимо, чтобы одновременно минимальная оценка была 4, а максимальная - 5.
Ø Внесите в заголовок таблицы: "Минимум" и "Максимум".
Для определения минимальной оценки используем кнопку "Вставка функции" пиктографического меню:
Ø Установите курсор в клетке G3.
Ø Зафиксируйте мышь на кнопке Вставка функции. В окне диалога в поле "Категория" из списка выберите "Статистические", в поле "Функция" зафиксируйте "МИН":
Ø Кнопка "ОК" выводит следующее окно мастера функций:
Ø Мастер функций выделяет ближайшие клетки с числами: с B3 по F3. В окне "Число 1" указывает этот диапазон - B3:F3, в поле формулы таблицы формула приведена полностью:
= МИН (B3:F3)
Обратите внимание, что Мастер функции в своем окне сразу же показывает результаты вычислений.
Ø Зафиксируйте мышь на кнопке "ОК". Если формула введена правильно, на экране в ячейке G3 должно быть выведено число - минимальное из оценок.
Следует отметить, что это не единственный вариант ввода формул.
Пользователь имеет возможность лично напечатать формулу в соответствующей клетке: = мин(b3:f3). Но использование Мастера функций более наглядно для выбора имени функции и аргументов.
Ø При вводе подобным образом в ячейку H3 максимального балла для первого студента, Мастер функций указал неверный диапазон оценок:B3:G3. В таком случае следует мышью выделить нужные клетки:
Ø Скопируйте аналогичные формулы для остальных студентов.
Для выполнения расчетов, связанных с проверкой выполнения каких-либо условий, предназначена логическая функция ЕСЛИ.
Общий вид функции:
=ЕСЛИ (условие; выражение 1; выражение 2)
Если условие, записанное в формуле, выполняется, то в ячейку, содержащую формулу, помещается выражение 1, в противном случае - выражение 2.
В условии используются операции:
> - больше, < - меньше, >= - больше или равно, <= - меньше, = - равно, <> - не равно.
Числовые значения, текстовые константы и даты могут быть связаны любыми отношениями. Но результат таких операций всегда ИСТИНА или ЛОЖЬ.
На месте выражений можно записать числа, формулы, текстовые константы и другие логические операторы ЕСЛИ.
Рассмотрим варианты использования функции ЕСЛИ.
1. Для выявления студентов - двоечников, в ячейку I3 следует записать формулу:
=ЕСЛИ (G3=2;"двоечник";"успевает")
Ø Для ввода формулы с использованием Мастера функций установите курсор в клетке I3 (i3).
Ø В окне диалога в поле "Категория" из списка выберите "Логические", в поле "Функции" зафиксируйте "ЕСЛИ". Кнопка "ОК" выводит следующее окно мастера функций. В поле "логическое выражение" введите: G3=2 (ячейку G3 можно указать мышкой, можно просто напечатать), в следующем поле "значение если истина" - слово "двоечник", в последнем поле "значение если ложь" - слово "успевает":
Ø Обратите внимание на строку ввода, Мастер функций расставил в формуле все имена, знаки и скобки. Зафиксируйте мышь на кнопке "ОК".
Ø В зависимости от поставленных оценок на экран в ячейке I3 должны получить текст: "двоечник" или "успевает". Распространите эту формулу на остальные клетки.
2. Для разделения студентов на двоечников, отличников и остальных в ячейку J3 запишем новую формулу:
Ø В поле "Категория" можно отметить пункт "10 недавно использовавшихся", в окне "Функции" выбираем "ЕСЛИ".
Ø Кнопка "ОК" выводит следующее окно мастера функций. В поле "логическое выражение" введите: G3=2, в поле "значение если истина" - слово "двоечник", в поле "значение если ложь" - щелкните по кнопке в строке ввода, должно открыться меню:
Ø В меню выбираем "ЕСЛИ", последовательно в полях записываем: G3=5, "отличник", "успевает". Вводим формулу кнопкой "ОК".
Ø Распространите эту формулу на остальные клетки.
3. Разделим студентов на хорошистов и всех остальных (отличников, двоечников, просто успевающих).
Если необходимо выполнение двух или нескольких условий одновременно, то используют логическую операцию "И", если достаточно выполнения одного из любых условий, то указывают операцию "ИЛИ".
=ЕСЛИ(И(условие1; условие2; ...); выражение 1; выражение 2)
=ЕСЛИ(ИЛИ(условие1; условие2;...); выражение 1; выражение 2)
Ø Введем новую формулу в ячейке K3.
=ЕСЛИ(И(G3=4;H3=5);"хорошист","......")
Также используем кнопку Вставка функции.
Ø Выбираем "ЕСЛИ", фиксируем "ОК", фиксируем поле "логическое выражение". Щелкните по кнопке в строке ввода таблицы, в меню выделяем "Другие функции…", категория "Логические", выбираем "И". В поле "Логическое 1" заносим условие: G3=4, в поле "Логическое 2" - H3=5, для возвращения в предыдущее окно "Если" щелкаем мышью в строке ввода за скобками:
Ø В строку "Истина" заносим "хорошист", в строку "Ложь" заносим несколько точек (".....").
Ø Щелкаем по кнопке "ОК" и распространяем эту формулу на остальные клетки.
Объединим все предыдущие условия, чтобы начислить стипендию.
Ø Очистите колонки I, J, K.
Ø Внесите в клетку I2 -"Стипендия", в пустую ячейку, например, А10, значение обычной стипендии - 166,98 р. (на сегодняшний день - 27 ноября 2000).
Ø При копировании формулы адрес A10 должен быть постоянным для каждой клетки, поэтому задаем адрес A$10.
Ø Введите формулу с помощью Вставки функции или напечатайте сами, скопируйте ее для подсчета стипендии остальным студентам.
Ø Вычислите средний балл по каждому предмету, среднюю минимальную и максимальную оценку, среднюю стипендию (функция СРЗНАЧ).
Ø Установим график дежурства для студентов. В клетку J2 введите заголовок "Дежурство". В ячейку J3 с помощью Мастера функций введите функцию "Сегодня" из категории "Дата и время". Установите с помощью команд
Правка, Заполнить, Прогрессия
интервал дежурств в одну неделю.
Ø Лично для себя вычислите количество прожитых дней на текущую дату.
Ø В соответствии с личным вкусом оформите таблицу, используя все возможности программы (из пункта оформление ячеек таблицы).
в строке ввода, должно открыться меню:
