Логические функции Excel
Функция ЕСЛИ
Функции И, ИЛИ, НЕ
Вложенные функции ЕСЛИ
Функции ИСТИНА и ЛОЖЬ
Функция ЕПУСТО
Больше Меньше = Больше или равно Меньше или равно Не равно Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0). " width="640"
Любое логическое выражение должно содержать по крайней мере один оператор сравнения, который определяет отношение между элементами логического выражения.
Список операторов сравнения Excel
= Равно
Больше
Меньше
= Больше или равно
Меньше или равно
Не равно
Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).
СИНТАКСИС ФУНКЦИИ ЕСЛИ С ОДНИМ УСЛОВИЕМ
Оператор проверяет ячейку А1 и сравнивает ее с 20.
Это «логическое_выражение».
Когда содержимое графы больше 20, появляется истинная надпись «больше 20».
Нет – «меньше или равно 20».
Внимание! Слова в формуле необходимо брать в кавычки. Чтобы Excel понял, что нужно выводить текстовые значения.
Еще один пример.
Чтобы получить допуск к экзамену, студенты группы должны успешно сдать зачет. Результаты занесем в таблицу с графами: список студентов, зачет, экзамен.
Обратите внимание : оператор ЕСЛИ должен проверить не цифровой тип данных, а текстовый. Поэтому мы прописали в формуле В2= «зач.» . В кавычки берем, чтобы программа правильно распознала текст.
ФУНКЦИЯ ЕСЛИ В EXCEL С НЕСКОЛЬКИМИ УСЛОВИЯМИ
Здесь оператор проверяет два параметра. Если первое условие истинно, то формула возвращает первый аргумент – истину. Ложно – оператор проверяет второе условие.
Таблица для анализа успеваемости.
Ученик получил 5 баллов – «отлично».
4 – «хорошо».
3 – «удовлетворительно».
Оператор ЕСЛИ проверяет 2 условия: равенство значения в ячейке 5 и 4.
В этом примере мы добавили третье условие, подразумевающее наличие в табеле успеваемости еще и «двоек».
Принцип «срабатывания» оператора ЕСЛИ тот же.
=80;А1 "Обычно";ЕСЛИ(И(А1=60;А1 "Никогда"))) Если значение в ячейке А1 является целым числом, формула читается следующим образом: "Если значение в ячейке А1 равно 100, возвратить строку "Всегда". В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить "Обычно". В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку "Иногда". И, если ни одно из этих условий не выполняется, возвратить строку "Никогда". Всего допускается до 7 уровней вложения функций ЕСЛИ. " width="640"
Вложенные функции ЕСЛИ
Иногда бывает очень трудно решить логическую задачу только с помощью операторов сравнения и функций И, ИЛИ, НЕ. В этих случаях можно использовать вложенные функции ЕСЛИ. Например, в следующей формуле используются три функции ЕСЛИ:
= ЕСЛИ(А1=100;"Всегда";ЕСЛИ(И(А1=80;А1
"Обычно";ЕСЛИ(И(А1=60;А1
"Никогда")))
Если значение в ячейке А1 является целым числом, формула читается следующим образом: "Если значение в ячейке А1 равно 100, возвратить строку "Всегда". В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить "Обычно". В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку "Иногда". И, если ни одно из этих условий не выполняется, возвратить строку "Никогда". Всего допускается до 7 уровней вложения функций ЕСЛИ.
РАСШИРЕНИЕ ФУНКЦИОНАЛА С ПОМОЩЬЮ ОПЕРАТОРОВ « И » и « ИЛИ »
Когда нужно проверить несколько истинных условий, используется функция И .
Суть такова : ЕСЛИ а = 1 И а = 2 ТОГДА значение X ИНАЧЕ значение Y.
Функция ИЛИ проверяет условие 1 или условие 2.
Как только хотя бы одно условие истинно, то результат будет истинным.
Суть такова : ЕСЛИ а = 1 ИЛИ а = 2 ТОГДА значение X ИНАЧЕ значение Y.
Функции И и ИЛИ могут проверить до 30 условий.
Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.
Приведем пример.
Функции ИСТИНА и ЛОЖЬ
Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом:
=ИСТИНА() =ЛОЖЬ()
Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратить значение "Проходите", если выражение в ячейке А1 имеет значение ИСТИНА:
=ЕСЛИ(А1=ИСТИНА();"Проходите";"Стоп")
В противном случае формула возвратит "Стоп".
Функция ЕПУСТО
Если нужно определить, является ли ячейка пустой, можно использовать функцию ЕПУСТО (ISBLANK), которая имеет следующий синтаксис:
=ЕПУСТО(значение)
Аргумент значение может быть ссылкой на ячейку или диапазон. Если значение ссылается на пустую ячейку или диапазон, функция возвращает логическое значение ИСТИНА , в противном случае ЛОЖЬ.
В электронную таблицу занесли данные о тестировании учеников. Ниже приведены первые пять строк таблицы:
1
A
B
округ
2
C
C
3
фамилия
4
предмет
Ученик 1
D
В
Ю
5
Ученик 2
балл
обществознание
немецкий язык
СВ
Ученик 3
246
русский язык
530
Ученик 4
576
обществознание
304
В столбце А записан округ, в котором учится ученик; в столбце В — фамилия; в столбце С — любимый предмет; в столбце D — тестовый балл. Всего в электронную таблицу были занесены данные по 1000 ученикам.
Выполните задание.
- Сколько учеников в Восточном округе (В) выбрали в качестве любимого предмета информатику ?
2. Каков средний тестовый балл у учеников Северного округа (С)?
Пояснение.
- Запишем в ячейку E2 следующую формулу =ЕСЛИ(A2="В";C2;0) и скопируем ее в диапазон E3:E1001.
- В ячейку столбца E будет записываться название предмета, если ученик из Восточного округа и «0», если это не так.
- Применив операцию =ЕСЛИ(E2="информатика";1;0), получим столбец (F) : с единицами и нулями.
- Далее, используем операцию =СУММ(F2:F1001). Получим количество учеников, которые считают своим любимым предметом информатику.
- Для ответа на второй вопрос используем операцию «ЕСЛИ».
- Запишем в ячейку G2 следующее выражение: =ЕСЛИ(A2="С";D2;0), в результате применения данной операции к диапазону ячеек G2:G1001 , получим столбец, в котором записаны баллы только учеников Северного округа.
- Сложив значения в ячейках , получим сумму баллов учеников: 56 737.
- Найдём количество учеников Северного округа с помощью команды =СЧЁТЕСЛИ(A2:A1001;"С"), получим 105.
- Разделив сумму баллов на количество учеников , получим: 540,352 — искомый средний балл.
В электронную таблицу занесли данные о калорийности продуктов. Ниже приведены первые пять строк таблицы:
A
1
B
Продукт
2
3
C
Жиры, г
Арахис
D
Белки, г
4
45,2
Арахис жареный
52
Горох отварной
5
26,3
Углеводы, г
E
0,8
Горошек зелёный
26
9,9
Калорийность, Ккал
0,2
10,5
13,4
552
5
20,4
626
8,3
130
55
В столбце А записан продукт; в столбце В — содержание в нём жиров; в столбце С — содержание белков; в столбце D — содержание углеводов и в столбце Е — калорийность этого продукта.
Выполните задание .
1. Сколько продуктов в таблице содержат меньше 5 г жиров и меньше 5 г белков? Запишите число этих продуктов в ячейку Н2 таблицы.
2. Какова средняя калорийность продуктов с содержанием жиров 0 г? Ответ на этот вопрос запишите в ячейку НЗ таблицы с точностью не менее двух знаков после запятой.
Пояснение.
1. Запишем в ячейку G2 следующую формулу =ЕСЛИ(И(B2 и скопируем ее в диапазон G3:G1001. В таком случае, в ячейку столбца G будет записываться единица, если продукт содержит меньше 5 г жиров и меньше 5 г белков.
2. Применив операцию =СУММ(G2:G1001) , получим ответ: 394.
- Запишем в ячейку J2 следующее выражение: =СУММЕСЛИ(B2:B1001;0;E2:E1001) , в результате получим сумму калорий с нулевым содержанием жиров: 10 628.
- Применив операцию =СЧЁТЕСЛИ(B2:B1001;0 ), получим количество продуктов с нулевым содержанием жиров: 113.
- Разделив, получим среднее значение продуктов с содержанием жиров 0 г: 94,05.
№ 19
На основании данных, содержащихся в таблице, ответьте на 2 вопроса:
- Сколько продуктов в таблице содержат меньше 50г углеводов и меньше 50г белков? Запишите число этих продуктов в ячейку Н2 таблицы.
A
1
Продукт
B
2
Арахис
3
Жиры, г
C
Белки, г
Арахис жареный
45,2
4
D
E
52
Горох отварной
Углеводы, г
5
26,3
Горошек зелёный
Калорийность, Ккал
26
9,9
0,8
13,4
552
10,5
0,2
5
626
20,4
130
8,3
55
F2: =ЕСЛИ(И(C2
H2: =СУММ(F2:F1001)
№ 19
2) Какова средняя калорийность продуктов с содержанием жира менее 1г? Ответ запишите в ячейку Н3 с точностью менее менее двух знаков после запятой.
A
1
Продукт
2
B
C
3
Жиры, г
Арахис
Арахис жареный
Белки, г
4
45,2
D
Горох отварной
26,3
5
E
52
Углеводы, г
Горошек зелёный
Калорийность, Ккал
26
0,8
9,9
13,4
10,5
552
0,2
626
5
20,4
130
8,3
55
= СУММЕСЛИ (диапазон; критерий; диапазон_суммирования) - суммирует ячейки, заданные указанным условием
= СЧЁТЕСЛИ (диапазон; критерий) – подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному условию
H3: = СУММЕСЛИ (B2:B1001;”СЧЁТЕСЛИ (В2:В1001;”