Проверки и ЕСЛИ

В столбце A — числа. Нужно отметить те, что лежат от 10 до 20 включительно, и сосчитать их. Формула должна спросить про каждую строку «подходит ли?» и записать ответ. Сравнение даёт ИСТИНА или ЛОЖЬ, а ЕСЛИ превращает такой ответ в нужное значение — например, в 1 для подходящей строки и 0 для остальных.

Разберём знаки сравнения, функции И, ИЛИ, НЕ и ЕСЛИ, а затем сосчитаем подходящие строки.

Знаки сравнения: = <> < > <= >=, и первое «=» формулы — не сравнение. =A2=5 проверяет равенство, =A2<>5 — неравенство, =A2>=5 — «не меньше», =A2<=5 — «не больше». Поменяй число в A2 и посмотри, как меняются ответы:

В записи =A2=5 первый знак = сообщает, что начинается формула. Второй выполняет сравнение. Формула возвращает ИСТИНА или ЛОЖЬ. Присваивания числа 5 ячейке A2 здесь нет.

Фразы «больше 10» и «не меньше 10» различаются на границе: число 10 подходит только во втором случае. Для «от 10 до 20 включительно» нужно проверить обе границы — но не математической цепочкой 10<=A2<=20: Excel выполнит сравнения по очереди, и смысл получится другой.

Для 15 цепочка даёт ЛОЖЬ, хотя 15 лежит между 10 и 20: второе сравнение проверяет уже не A2, а результат первого.

И истинна, когда истинны все проверки; ИЛИ — когда истинна хотя бы одна; НЕ переворачивает ответ. =И(A2>=10;A2<=20) проверяет, что число лежит от 10 до 20 включительно, а =ИЛИ(A2<10;A2>20) — что оно вне этого промежутка. На числовой прямой видно, что остаётся от двух проверок:

НЕ меняет ИСТИНА на ЛОЖЬ и наоборот: =НЕ(A2=5) — то же, что =A2<>5. Пригождается для сложных проверок: =НЕ(И(A2>=10;A2<=20)) — «вне промежутка».

Все четыре сочетания двух проверок — на одном листе; столбцы C, D и E сравнивают И, ИЛИ и «ровно одна»:

ИЛИ истинна и тогда, когда истинны обе проверки (строка 5). Для «ровно одна из двух» нужна другая проверка: =A2<>B2 истинна именно тогда, когда ответы различаются.

ЕСЛИ выбирает одно из двух значений по условию.

Например, =ЕСЛИ(A2>=60;"зачёт";"незачёт") возвращает текст. Кавычки показывают, что «зачёт» — буквальный текст, а не имя ячейки или функции. Числа и ссылки обычно не заключают в кавычки.

Для подсчёта подходящих строк удобно возвращать числовой признак:

Вложенную формулу Excel вычисляет изнутри наружу. При A2 = 15:

Подходящая строка даёт 1, неподходящая — 0, поэтому сумма признаков =СУММ(C2:C6) равна числу подходящих строк. Границы 10 и 20 вошли в подсчёт: проверки «не меньше» и «не больше».

Сложное условие удобнее разложить на столбцы: каждая проверка в своём столбце, итоговый признак — их И. Строка подходит, если сумма трёх чисел больше 10 и среди них есть пятёрка. Каждая проверка — в своём столбце: D — сумма, E — пятёрка, а в F итог =ЕСЛИ(И(D2;E2);1;0). Так видно, на каком условии строка перестала подходить.

В строке 3 видно сразу: сумма подходит, а пятёрки нет. Когда всё работает, столбцы можно объединить в одну формулу, но это не обязательно: несколько коротких проверок легче проверить, чем одну длинную.

Деление защищают выбором ветви ЕСЛИ, а не скрытием ошибки. Средняя цена штуки — сумма, делённая на число штук; при нуле штук делить нельзя. =ЕСЛИ(B2=0;"нет данных";A2/B2) при нуле даже не начинает делить, а выбирает текст.

Столбец D проверяет то же через И — и в строке 3 получает #ДЕЛ/0!: И вычисляет все свои аргументы, даже если первый уже ложен. Поэтому от деления на ноль защищает только ЕСЛИ.