2.2.4. Определение категории студента

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

Для сравнения в Excel'е есть функцияЕСЛИ. Данная функция возвращает одно значение, если заданное условие при вычислении дает значениеИСТИНА(условие выполняется), и другое значение, еслиЛОЖЬ(условие не выполняется). ФункцияЕСЛИиспользуется для условной проверки значений и формул.

Примеры.

1. В следующем примере, если значение ячейки A10 равно 100, то условие выполняется, т.е. имеет значение ИСТИНАи вычисляется сумма для ячеек B5:B15. В противном случае условие не выполняется, т.е. имеет значениеЛОЖЬи возвращается пустой текст (""), очищающий ячейку, которая содержит функциюЕСЛИ.

=ЕСЛИ(A10=100;СУММ(B5:B15);"")

2. Предположим, что рабочий лист по расходам содержит в ячейках B2:B4 фактические расходы за январь, февраль, март: 1500, 500 и 500 соответственно. Ячейки C2:C4 содержат данные по предполагаемым расходам за те же периоды: 900, 900 и 925. Можно написать формулу для проверки соответствия бюджету расходов определенного месяца, генерируя тексты сообщений с помощью следующих формул:

=ЕСЛИ(B2>C2;"Превышение бюджета";"")

условие выполняется, значение в ячейке с условием равняется "Превышение бюджета"

=ЕСЛИ(B3>C3;"Превышение бюджета";"")

условие не выполняется, значение в ячейке с условием равняется ""

Замечание.

До семи функций ЕСЛИмогут быть вложены друг в друга в качестве значений аргументов.

В нашем случае для диагностирования двух категорий студентов необходимо проверить количество двоек (если оно не равно нулю, то студент двоечник) и количество пятерок (если оно равно количеству предметов, т.е. 12, то студент отличник). В ячейке AS17 записана соответствующая формула:

=ЕСЛИ(AO17<>0;"двоечник";ЕСЛИ(AR17=12;"отличник";""))

Обратим внимание на то, что второе условие ЕСЛИпроверяется в том случае, когда первое условие не выполняется, т.е. имеет значениеЛОЖЬ. Если продолжить написание условий, то, поставив условие в качестве третьего аргумента вложенной функцииЕСЛИ, можно определить и троечников и хорошистов:

=ЕСЛИ(AO17<>0;"двоечник";ЕСЛИ(AR17=12;"отличник";ЕСЛИ(AP17<>0;"троечник","хорошист")))

Замечание.

1. Необходимо строго следить за числом скобок в формулах: число открытых скобок должно равняться числу закрытых.

2. Рекомендуется рисовать блок-схему условий на бумаге для более эффективной записи условий и самоконтроля.

Для нашего последнего условия блок-схема имеет вид:

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

Задание №1. Замените в таблице формулу в столбцеAS, так чтобы она определяла все категории студентов (двоечник, троечник, хорошист и отличник). Формула приведенная в таблице определяет только две категории: двоечник и отличник.

План лабораторной работы: Контрольные вопросы