Мастер функций и логические выражения в Excel

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

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

Ø В первую строку занесите заголовок: РЕЗУЛЬТАТЫ СЕССИИ

Ø В ячейки второй строки: Фамилия, Физика, Химия, История, Информатика, Экология.

Ø Заполните семь строк фамилиями студентов и оценками.

По результатам сессии надо назначить стипендию студентам, исходя из следующих правил: если среди оценок есть двойка, стипендию не назначать; если студент получил только пятерки, то назначить стипендию, повышенную на 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 запишем новую формулу:

=ЕСЛИ (G3=2;"двоечник";ЕСЛИ(G3=5;"отличник";"успевает"))

Ø Эту формулу вводим с клавиатуры.

Ø Распространите эту формулу на остальные клетки.

3. Разделим студентов на хорошистов и всех остальных (отличников, двоечников, просто успевающих).

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

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

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

Ø Введем новую формулу в ячейке K3.

=ЕСЛИ(И(G3=4;H3=5);"хорошист";ЕСЛИ(G3=2;"двоечник";ЕСЛИ(G3=5;"отличник";"успевает")))

Объединим все предыдущие условия, чтобы начислить стипендию.

Ø Очистите колонки I, J, K.

Ø Внесите в клетку I2 -"Стипендия", в пустую ячейку, например, А10, значение обычной стипендии – 2000,00 р

Ø При копировании формулы адрес A10 должен быть постоянным для каждой клетки, поэтому задаем адрес A$10.

Ø В ячейку I3 введите окончательную формулу:

=ЕСЛИ(G3=2;0;ЕСЛИ(G3=5;A$10+50%*A$10;ЕСЛИ(И(G3=4;H3=5); A$10+25%*A$10;A$10)))

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

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

Ø Установим график дежурства для студентов. В клетку J2 введите заголовок "Дежурство". В ячейку J3 с помощью Мастера функций введите функцию "Сегодня" из категории "Дата и время". Выделите диапазон ячеек, в который заносите график дежурств. Установите с помощью команды Прогрессия интервал дежурств в одну неделю.
Значок вызова команды Прогрессия находится на линейке команд ГЛАВНАЯ.

Ø Лично для себя вычислите количество прожитых дней на текущую дату.

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

Ø Сохраните таблицу в своей папке.

Оформление таблицы

Для оформления таблицы используйте панели ШРИФТ и ВЫРАВНИВАНИЕ линейки команд ГЛАВНАЯ или команду ФОРМАТ ЯЧЕЕК, которую можно вызвать, щёлкнув правой копкой мыши на предварительно выделенном диапазоне.

Ø Оформите подготовленную таблицу, используя эти возможности.

Защита таблицы

Во избежание несанкционированного изменения содержимого ячеек применяются команды защиты. Средства Excel позволяют защитить текущий лист, книгу, ограничить доступ к книге. Для этого надо вызвать соответствующую команду, расположенную на линейке команд РЕЦЕНЗИРОВАНИЕ.

Защита выполняется для всех клеток таблицы, после выполнения этой команды уже невозможно внести какие-либо изменения в таблицу, и меню становится при этом практически недоступным. Если есть необходимость оставить часть клеток доступной для последующей корректировки, то эти ячейки выделяют, вызыют команду: Формат ЯчеЕк. В появившемся окне надо выбрать вкладку "Защита", а дальше сбросить флажок в поле защищаемая ячейка.

Ø Установите защиту листа для всех данных, кроме оценок. Попытайтесь изменить название любого предмета, оценку. Снимите защиту.

Задание №1

1. Выполните все упражнения, помеченные в тексте.

2. Сохранить созданную рабочую книгу на диске в своей папке.

Задание №2

1. Создайте таблицу "Экзаменационная ведомость". Таблица должна содержать следующие графы: № студента, ФИО, оценка (число), оценка (прописью).

2. Заполните ведомость для 10 студентов.

3. Выполните сортировку данных по графе ФИО (по алфавиту).

4. Добавьте в таблицу строки: количество студентов, получивших "отлично", количество студентов, получивших "хорошо", количество студентов, получивших "удовлетворительно", количество студентов, получивших "неудовлетворительно", количество студентов, не явившихся на экзамен.

5. Защитите таблицу от изменений, исключая оценки.