Урок 5. Условия: ЕСЛИ, И, ИЛИ, ЕСЛИОШИБКА
До сих пор формулы считали всегда одинаково. Но часто ответ зависит от условия: «если сумма больше 100 — доставка бесплатная», «если балл ниже 4 — незачёт». Для этого есть функция ЕСЛИ и её помощники.
Сравнения
Условие — это сравнение, его результат — ИСТИНА или ЛОЖЬ. Введите в ячейку =5>3 — Excel ответит ИСТИНА.
| Знак | Значит | Пример | Результат |
|---|---|---|---|
= | равно | =2=3 | ЛОЖЬ |
<> | не равно | =2<>3 | ИСТИНА |
> | больше | =5>3 | ИСТИНА |
< | меньше | =5<3 | ЛОЖЬ |
>= | больше или равно | =4>=4 | ИСТИНА |
<= | меньше или равно | =7<=4 | ЛОЖЬ |
Текст сравнивается без учёта регистра: ="Минск"="минск" даёт ИСТИНА.
Функция ЕСЛИ
=ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)Три аргумента: условие, что вернуть, если оно выполнено, и что — если нет. Пусть зачёт ставится от 4 баллов:
| A | B | C | |
|---|---|---|---|
| 1 | Ученик | Балл | Пропуски |
| 2 | Иванов | 7 | 2 |
| 3 | Петрова | 3 | 0 |
| 4 | Сидоров | 9 | 6 |
| 5 | Козлова | 5 | 1 |
В D2 и вниз по столбцу:
=ЕСЛИ(B2>=4;"зачёт";"незачёт")| A | B | D | |
|---|---|---|---|
| 2 | Иванов | 7 | зачёт |
| 3 | Петрова | 3 | незачёт |
| 4 | Сидоров | 9 | зачёт |
| 5 | Козлова | 5 | зачёт |
Текст в формуле пишется в кавычках, числа и ссылки — без них. Ответом может быть не только текст, но и число или формула: =ЕСЛИ(C2>100;C2*0,9;C2) — скидка 10 % на суммы больше 100.
Пустой ответ. Две кавычки подряд "" — пустой текст. =ЕСЛИ(B2<4;"пересдача";"") пишет «пересдача» только у тех, кому нужно, а у остальных ячейка выглядит пустой.
Несколько условий: И, ИЛИ
И возвращает ИСТИНА, только если выполнены все условия, ИЛИ — если выполнено хотя бы одно. Сами по себе они дают ИСТИНА или ЛОЖЬ, поэтому их ставят внутрь ЕСЛИ.
К экзамену допускают, если балл не ниже 4 и пропусков не больше 5:
=ЕСЛИ(И(B2>=4;C2<=5);"допущен";"не допущен")С учеником нужна беседа, если балл ниже 4 или пропусков больше 5:
=ЕСЛИ(ИЛИ(B2<4;C2>5);"беседа";"")| Ученик | Балл | Пропуски | Допуск (И) | Беседа (ИЛИ) |
|---|---|---|---|---|
| Иванов | 7 | 2 | допущен | |
| Петрова | 3 | 0 | не допущен | беседа |
| Сидоров | 9 | 6 | не допущен | беседа |
| Козлова | 5 | 1 | допущен |
Сидоров не допущен при отличном балле: второе условие И (пропусков не больше 5) не выполнено. Функция НЕ переворачивает результат: =НЕ(B2>=4) — то же, что =B2<4.
Вложенные ЕСЛИ и ЕСЛИМН
Когда вариантов больше двух, на место «иначе» ставят ещё одну ЕСЛИ. Условная шкала: от 9 — «отлично», от 6 — «хорошо», от 4 — «удовлетворительно», ниже — «неудовлетворительно»:
=ЕСЛИ(B2>=9;"отлично";ЕСЛИ(B2>=6;"хорошо";ЕСЛИ(B2>=4;"удовлетворительно";"неудовлетворительно")))| Ученик | Балл | Оценка |
|---|---|---|
| Иванов | 7 | хорошо |
| Петрова | 3 | неудовлетворительно |
| Сидоров | 9 | отлично |
| Козлова | 5 | удовлетворительно |
Excel проверяет условия по порядку и останавливается на первом выполненном, поэтому начинать надо с самого строгого (>=9). Если поставить первым B2>=4, все от 4 до 10 получат «удовлетворительно».
В Excel 2019, 2021 и Microsoft 365 то же короче пишется функцией ЕСЛИМН — пары «условие; ответ», а последнее условие ИСТИНА значит «во всех остальных случаях»:
=ЕСЛИМН(B2>=9;"отлично";B2>=6;"хорошо";B2>=4;"удовлетворительно";ИСТИНА;"неудовлетворительно")Считайте скобки: у каждой ЕСЛИ своя пара, и в конце вложенной формулы их столько, сколько функций. Excel раскрашивает пары скобок разными цветами — это помогает.
ЕСЛИОШИБКА: вместо ошибки — свой ответ
Некоторые расчёты дают ошибку: деление на ноль, ненайденное значение. Средний чек — выручка, делённая на число покупателей; во вторник магазин не работал:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | День | Выручка | Покупателей | Средний чек |
| 2 | Пн | 540 | 45 | 12 |
| 3 | Вт | 0 | 0 | #ДЕЛ/0! |
| 4 | Ср | 630 | 42 | 15 |
В D3 формула =B3/C3 дала ошибку деления на ноль. Обернём деление:
=ЕСЛИОШИБКА(B2/C2;"нет продаж")Теперь в D3 — «нет продаж», в остальных строках — обычный результат. Первый аргумент — формула, второй — что показать, если она вернула любую ошибку.
ЕСЛИОШИБКА прячет все ошибки, в том числе настоящие: опечатку в имени листа, удалённую ячейку, неверный диапазон. Оборачивайте формулу только тогда, когда она уже проверена и ошибка в ней ожидаема.
Частые ошибки
- Кавычки вокруг чисел.
=ЕСЛИ(B2>="4";…)сравнивает число с текстом «4» и даёт неверный ответ. Числа — без кавычек. - Неверный порядок во вложенных ЕСЛИ — первым должно идти самое строгое условие.
- Условие «между» одним сравнением.
=ЕСЛИ(4<=B2<=8;…)в Excel не работает как в математике: нужноИ(B2>=4;B2<=8). - Буква «ё». «зачёт» и «зачет» — разные строки: если потом искать или считать по этому слову, пишите одинаково.
Коротко
- Сравнения
=,<>,>,<,>=,<=дают ИСТИНА или ЛОЖЬ; регистр текста не важен. ЕСЛИ(условие;если_да;если_нет); текст — в кавычках,""— пустой ответ.И— все условия сразу,ИЛИ— хотя бы одно; их ставят внутрьЕСЛИ.- Несколько вариантов — вложенные
ЕСЛИилиЕСЛИМН; первым — самое строгое условие. ЕСЛИОШИБКА(формула;ответ)заменяет любую ошибку своим ответом — только для проверенных формул.