Вложенные друг в друга функции ЕСЛИ
Лабораторная работа 5 Логические и статистические функции
Логические выражения используются для записи условий, в которых сравниваются числа, функции, формулы, текстовые или логические значения. Любое логическое выражение должно содержать по крайней мере один оператор сравнения, который определяет отношение между элементами логического выражения.
Функции И, ИЛИ
Функция И принимает значение ИСТИНА в случае, когда истины все логические выражения входящие в нее. Оная является аналогом логического умножения (конъюнкции) в математической логике.
Функция ИЛИ принимает значение истина в случае, когда истинно хотя бы одно логическое выражение. Оная является аналогом логического сложения (дизъюнкции) в математической логике.
Для того, чтобы разобраться с синтаксисом данных функций, воспользуйтесь справкой по этим функциям:
Задание 1
Откройте Файл Лабораторная работа 5. На листе Л 1 при помощи логической функции И определите является ли ученик отличником, при помощи логической функции ИЛИ определить, является ли ученик двоечником.
Отличником является ученик, у которого за каждый экзамен стоит оценка 5.
Двоечником является ученик, у которого есть хотя бы одна двойка.
Вычислить средний балл каждого ученика.
Функция ЕСЛИ
ЕСЛИ — очень интересная функция, позволяющая вывести в ячейку одно значение, если заданное пользователем условие выполняется и другое — если условие не выполняется. Функция имеет три аргумента:
логическое выражение, задающее условие (Лог_выражение);
значение, выводимое в случае выполнения условия (Значение_если_истина);
значение, выводимое в случае невыполнения условия (Значение_если _ложь).
Задание 2
На листе Л 2 в ячейках, выделенных желтым цветом будем вводить значения, а в зеленых ячейках формулы
В ячейке Е4 при помощи функции ЕСЛИ необходимо определить положительным или отрицательным является число в ячейке С4.
Для этого введите любое число в ячейку С4, затем в ячейке Е4 Пишем формулу:
И нажимаем кнопку ОК.
Аналогичным образом определите тепло или холодно в комнате. Комфортная комнатная температура начинается с 20 градусов.
Вложенные друг в друга функции ЕСЛИ
Согласно синтаксису функции ЕСЛИ, возможно только два пути развития событий: когда условие выполняется и когда оно не выполняется, но в жизни все бывает сложнее, вот хотя бы у богатыря, что остановился перед указательным камнем впереди 3 дороги.
В этом случае необходимо рассмотреть случаи: пойдет направо, не пойдет направо. В случае если не пойдет направо может быть два варианта: пойдет налево, не пойдет налево (остался последний вариант: прямо).
Задание 3
В зависимости от того, какое решение примет богатырь в ячейке К17 напишите предсказание в ячейке О17
Аналогичным образом вводя различные числа в ячейки К8 и N8 в ячейке L8 сравните их словами «больше», «меньше», «равно».
На листе Л1 определите стипендии отличникам – 5000 р., двоечникам – 0 р., всем остальным – 2500 р.
Другие вложенные функции
В функцию ЕСЛИ могут быть вложены и другие логические функции, например И и ИЛИ. Это бывает необходимо, когда требуется выполнение всех или хотя бы одного условия для выполнения дальнейших действий.
Задание 4
На листе Л3 дан список учеников. Из этих учеников нужно выбрать людей, которые могут войти в команды по волейболу.
В женскую команду принимаются девушки возрастом от 13 лет и ростом от 165 см.
В мужскую команду принимаются юноши с 14 лет и ростом от 170 см.
Сначала не забудьте вычислить возраст учащихся.
Затем в ячейке F3 введите формулу:
Аналогичным образом определите участников мужской команды.
Функция СЧЁТЕСЛИ
Функция СЧЁТЕСЛИ относится к статистическим функциям.
При помощи функции СЧЁТЕСЛИ можно посчитать количество повторении какого-либо элемента в диапазоне. Например на листе Л1 можно посчитать количество отличников по повторению слова ИСТИНА в диапазоне G5:G39.
Синтаксис функции СЧЁТЕСЛИ:
СЧЁТЕСЛИ(диапазон;критерий)
Для данного примера диапазоном будет G5:G39, а критерием «ИСТИНА».
Задание 5
На листе Л1 вычислить количество отличников и количество двоечников.
На листе задание 3 вычислить количество игроков каждой команды.
Функция СУММЕСЛИ
Функция СУММЕСЛИ используется, если необходимо просуммировать значения диапазона, соответствующие указанному критерию.
Синтаксис функции:
СУММЕСЛИ(диапазон,критерий,[диапазон_суммирования])
Диапазон. Обязательный аргумент. Диапазон ячеек, оцениваемых по критериям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки на числа. Пустые ячейки и ячейки, содержащие текстовые значения, пропускаются.
Критерий. Обязательный аргумент. Критерий в форме числа, выражения, ссылки на ячейку, текста или функции, определяющий, какие ячейки необходимо просуммировать. Например, критерий может быть представлен в следующем виде: 32, ">32", B5, "32", "яблоки" или СЕГОДНЯ().
Важно. Все текстовые критерии и критерии с логическими и математическими знаками необходимо заключать в двойные кавычки ("). Если критерием является число, использовать кавычки не требуется.
Диапазон_суммирования. Необязательный аргумент. Ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Microsoft Excel суммирует ячейки, указанные в аргументе диапазон (те же ячейки, к которым применяется критерий).
Задание 6
На листе Л4 приведен список сотрудников предприятия. Прежде всего необходимо вычислить возраст и страж работы каждого сотрудника. Для этого необходимо вставить дополнительные столбцы на лист. Выделите столбец ПЕРЕД которым вы хотите добавить новый столбец и на вкладке Главная выберите пункт меню Вставить – Вставить столбцы на лист.
Назначьте премию сотрудникам со стажем работы более 8 лет – 15% от оклада, а всем остальным 7%.
Вычислите общую зарплату каждого сотрудника.
Внизу диапазона вычислите всю сумму выплаченных зарплат.
Далее используя функцию СУММЕСЛИ посчитайте, сумму выплаченную всем мужчинам. В данном случае диапазоном будет являться столбец В (пол), критерием «м», диапазоном суммирования – столбец Всего зарплата.
Вычислите остальные значения используя функции СУММЕСЛИ, СЧЁТЕСЛИ и их сочетание.