- •Методические указания к лабораторной работе №2
- •III. Применение полученных знаний для решения однотипных задач 26
- •II. Изучение средствExcel для обработки данных
- •2.1. Постановка задачи
- •2.2. Решение задачи
- •2.2.1. Работа с примечаниями
- •2.2.2. Определение среднего балла
- •2.2.4. Определение категории студента
- •2.2.5. Определение стипендии студента
- •2.2.6. Определение стипендиального фонда
- •2.2.7. Вычисление процентных отношений
- •2.2.8. Вычисление среднего балла по предметам и определение максимального из них
- •2.2.9. Выделение категории студентов с помощью условного форматирования
- •2.2.10. Построение диаграммы
- •2.2.11. Создание программного кода
- •III. Применение полученных знаний для решения однотипных задач
- •3.1. Задача обработки информации об измерениях температуры
- •3.2. Некоторые особенности решения задачи обработки температур
- •3.2.1. Нахождение максимального, минимального и среднего значения
- •3.2.2. Упорядочивание данных
- •3.2.3. Расчет средних значений для каждого дня
- •3.2.4. Расчет первых пяти наибольших и последних пяти наименьших значений
- •3.2.5. Расчет числа дней, в которых температура была в одном из диапазонов
- •3.2.6. Составление таблицы температур
- •3.2.7. Условное форматирование и автофильтр
- •3.2.8. Составление программы
- •3.3. Задача на самостоятельное решение
- •IV. Самоконтроль
- •4.1. Контрольные вопросы
2.2.4. Определение категории студента
Для определения категории студента нам необходимо знать количество оценок каждого вида. И, так же как и человек определяет сравнением количества оценок каждого вида для определения категории студента, так и Excel будет сравнивать. Для простоты мы разберем классификацию студентов на две категории: отличник и двоечник. Полный же анализ произведем далее - программно.
Для сравнения в Excel'е есть функцияЕСЛИ. Данная функция возвращает одно значение, если заданное условие при вычислении дает значениеИСТИНА(условие выполняется), и другое значение, еслиЛОЖЬ(условие не выполняется). ФункцияЕСЛИиспользуется для условной проверки значений и формул.
Примеры.
В следующем примере, если значение ячейки A10 равно 100, то условие выполняется, т.е. имеет значение ИСТИНАи вычисляется сумма для ячеек B5:B15. В противном случае условие не выполняется, т.е. имеет значениеЛОЖЬи возвращается пустой текст (""), очищающий ячейку, которая содержит функциюЕСЛИ.
=ЕСЛИ(A10=100;СУММ(B5:B15);"")
Предположим, что рабочий лист по расходам содержит в ячейках 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. Замените в таблице формулу в столбцеAS, так чтобы она определяла все категории студентов (двоечник, троечник, хорошист и отличник). Формула приведенная в таблице определяет только две категории: двоечник и отличник.
План лабораторной работы: Контрольные вопросы