Функции ЕСЛИ, ЕСЛИМН, И, ИЛИ в Excel: формулы с условиями
Основы3 минРедакция Innovialabs
Коротко
ЕСЛИ проверяет одно условие и возвращает одно из двух значений, И и ИЛИ объединяют несколько проверок, ЕСЛИМН заменяет вложенные ЕСЛИ, а при множестве порогов удобнее таблица соответствий с ВПР.
Коротко: как работает ЕСЛИ
ЕСЛИ проверяет условие и возвращает одно значение, если оно истинно, и другое — если ложно:
=ЕСЛИ(условие; значение_если_истина; значение_если_ложь)
Пример: клиент с заказом от 100 000 считается оптовым.
=ЕСЛИ(B2>=100000; "Опт"; "Розница")
В русской версии Excel аргументы разделяются точкой с запятой, в английской — запятой. Google Таблицы принимают английские названия функций (IF, AND, OR, IFS), разделитель зависит от региональных настроек таблицы.
Операторы сравнения
| Оператор | Значение | Пример |
|---|---|---|
= | равно | C2="Оплачено" |
<> | не равно | C2<>"" (ячейка не пустая) |
> < | больше, меньше | B2>0 |
>= <= | больше или равно, меньше или равно | D2<=СЕГОДНЯ() |
Текст всегда пишется в кавычках, числа — без. Сравнение текста в ЕСЛИ не учитывает регистр: «оплачено» и «Оплачено» совпадут.
Несколько условий через И и ИЛИ
- И возвращает ИСТИНА, только если выполнены все условия.
- ИЛИ возвращает ИСТИНА, если выполнено хотя бы одно.
Отгружаем заказ, только если он крупный и оплачен:
=ЕСЛИ(И(B2>=100000; C2="Оплачено"); "Отгрузить"; "Ждать")
Даём скидку постоянному клиенту или при заказе больше 10 позиций:
=ЕСЛИ(ИЛИ(D2="Постоянный"; E2>10); "Скидка"; "Без скидки")
Их можно комбинировать: И(B2>0; ИЛИ(F2="Ташкент"; F2="Самарканд")). Если логика стала сложной, вынесите часть проверок во вспомогательный столбец — так формулу проще читать и отлаживать.
Вложенные ЕСЛИ
Когда вариантов больше двух, одну функцию ЕСЛИ ставят внутрь другой. Пример — уровень клиента по сумме покупок:
=ЕСЛИ(B2>=500000; "Платина"; ЕСЛИ(B2>=200000; "Золото"; ЕСЛИ(B2>=50000; "Серебро"; "Базовый")))
Главное правило — порядок проверок. Условия идут от самого строгого к самому мягкому. Если первым поставить B2>=50000, все клиенты от 50 000 и выше получат «Серебро», и до остальных проверок дело не дойдёт.
ЕСЛИМН вместо вложенных ЕСЛИ
ЕСЛИМН (в английской версии IFS) принимает пары «условие — результат» и возвращает результат первого истинного условия:
=ЕСЛИМН(B2>=500000; "Платина"; B2>=200000; "Золото"; B2>=50000; "Серебро"; ИСТИНА; "Базовый")
- Скобок меньше, формулу легче читать.
- Последняя пара
ИСТИНА; "Базовый"работает как «во всех остальных случаях». Без неё ЕСЛИМН вернёт ошибку#Н/Д, если ни одно условие не подошло. - Порядок проверок важен так же, как во вложенных ЕСЛИ.
- Функция есть в Google Таблицах и в современных версиях Excel; в старых версиях Excel её нет, и файл с ней там покажет ошибку.
Когда перейти на таблицу соответствий
Если порогов больше трёх-четырёх или они меняются, не держите их внутри формулы. Создайте отдельный лист «Уровни»:
| Порог | Уровень |
|---|---|
| 0 | Базовый |
| 50000 | Серебро |
| 200000 | Золото |
| 500000 | Платина |
И ищите уровень приблизительным поиском — пороги должны быть отсортированы по возрастанию:
=ВПР(B2; Уровни!$A$2:$B$5; 2; ИСТИНА)
Плюсы: пороги видны и меняются без правки формул, а один и тот же справочник используют все отчёты. Для точных совпадений («код региона → название») используйте ВПР с ЛОЖЬ или ПРОСМОТРX.
Типичные ошибки
- Числа сохранены как текст.
B2>=100000даёт неверный результат, если в B2 стоит текст «150000». Проверьте выравнивание и формат ячеек. - Не тот разделитель. Запятая вместо точки с запятой в русской версии вызывает ошибку синтаксиса.
- Кавычки вокруг чисел.
B2>"100"сравнивает число с текстом и работает не так, как ожидается. - Пустые ячейки. Пустая ячейка считается нулём, поэтому
B2<1000для неё истинно. Добавьте проверкуB2<>"". - Слишком глубокая вложенность. Если формула не помещается в строку формул, это сигнал перейти на ЕСЛИМН или справочник.