Функция ЕСЛИ в Excel: несколько условий, ЕСЛИМН и примеры формул

Коротко

ЕСЛИ проверяет условие и возвращает одно из двух значений. Всё остальное — несколько условий, сегменты, обработка ошибок — собирается из этой проверки.

• Формула: =ЕСЛИ(B2>=5000;"крупный";"обычный"). Текст пишется в кавычках, числа и ИСТИНА/ЛОЖЬ — без них.
• Два условия сразу — И (оба выполнены) или ИЛИ (хотя бы одно): =ЕСЛИ(И(B2>=5000;C2>=3);"ключевой";"").
• Во вложенных ЕСЛИ выигрывает первое истинное условие. Пороги идут от большего к меньшему, иначе крупные клиенты станут средними.
• ЕСЛИМН короче вложенных ЕСЛИ, но есть только в Excel 2019, 2021, 2024 и Microsoft 365. Без последней пары ИСТИНА;"…" она возвращает #Н/Д.
• Пустая ячейка в сравнении ведёт себя как ноль. Проверяйте её отдельно: ЕСЛИ(B2="";"нет данных";…).
• ЕСЛИОШИБКА прячет любую ошибку, включая опечатку в формуле. Для ВПР берите ЕСНД, для деления — явную проверку знаменателя.
• Когда порогов больше трёх-четырёх, держите их в отдельной таблице и ищите сегмент через ВПР или ПРОСМОТРX.

Как записать функцию ЕСЛИ

Справка Microsoft записывает функцию так: ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь]). Первый аргумент — проверка, которая даёт ИСТИНА или ЛОЖЬ: B2>=5000, D2="Москва", C2=0. Второй — что вернуть, если проверка выполнена. Третий, необязательный, — что вернуть в остальных случаях. В русском Excel аргументы разделяются точкой с запятой.

Набрать формулу можно прямо в ячейке: =ЕСЛИ( и аргументы. Если удобнее окно с подписями аргументов — откройте «Формулы → Библиотека функций → Логические → ЕСЛИ», Excel покажет три поля и подскажет, что в каждом. В Google Таблицах функция называется так же и пишется так же.

Текст в формуле всегда берётся в кавычки: "крупный", "Москва". Без кавычек Excel примет слово за имя или ссылку и выдаст ошибку. Исключения справка называет прямо — ИСТИНА и ЛОЖЬ Excel узнаёт сам. Пустые кавычки "" означают пустую строку: так ячейка выглядит пустой, если условие не выполнено.

Для сравнения есть шесть операторов: =, <> (не равно), >, >=, <, <=. На границе решает знак: клиент Бета купил ровно на 5 000 ₽, и с >= он крупный, а с > — уже нет. Поэтому формулировку задачи («от 5 000» или «больше 5 000») сверяйте со знаком до того, как протянуть формулу.

ЕСЛИ с несколькими условиями: И и ИЛИ

Когда решение зависит от двух столбцов, условие собирают функциями И и ИЛИ, а результат передают в ЕСЛИ. И(условие1; условие2; …) даёт ИСТИНА, только если выполнены все условия; ИЛИ — если выполнено хотя бы одно. Условий может быть до 255, но на практике дальше трёх-четырёх формулу уже трудно читать.

Допустим, менеджеру нужны «ключевые» клиенты — крупные и при этом регулярные, от трёх заказов. Это И: Альфа проходит обе проверки, Бета крупный, но с двумя заказами, Вега с четырьмя заказами не дотянула до 5 000 ₽ одного рубля. А список на приоритетный звонок собирают по правилу «крупный или частый» — это ИЛИ, и туда попадают все трое.

Частая ошибка — записать двойное условие как в математике: =ЕСЛИ(1000<=B2<5000;…). Excel посчитает сначала 1000<=B2, получит ИСТИНА или ЛОЖЬ и сравнит с 5 000 уже это логическое значение. Ошибки не будет, но все клиенты, от 650 до 12 400 ₽, получат один и тот же ответ. Диапазон пишется через И: И(B2>=1000;B2<5000).

Вложенные ЕСЛИ: сегменты клиентов по сумме

Три сегмента — это два решения подряд. Сначала: крупный ли клиент? Если нет, второй вопрос: средний ли? Если и нет — малый. Второй ЕСЛИ встаёт на место третьего аргумента первого: =ЕСЛИ(B2>=5000;"крупный";ЕСЛИ(B2>=1000;"средний";"малый")).

Пишите формулу в ячейку E2 и протягивайте вниз. Ссылки на B2 сдвинутся сами, закреплять их знаком $ не нужно — числа порогов стоят прямо в формуле. Это удобно для одного отчёта и неудобно, когда пороги меняются: тогда их выносят в таблицу, об этом ниже.

Справка Microsoft разрешает вложить до 64 функций ЕСЛИ и тут же пишет, что делать это совершенно не рекомендуется. Каждый уровень — лишняя пара скобок, в которой легко ошибиться, а через полгода никто не вспомнит, почему порог стоит именно там.

Порядок проверок: почему крупные клиенты стали средними

Вложенный ЕСЛИ проверяет условия сверху вниз и останавливается на первом истинном. Поменяйте пороги местами — =ЕСЛИ(B2>=1000;"средний";ЕСЛИ(B2>=5000;"крупный";"малый")) — и формула не выдаст ни одной ошибки. Только у Альфы с 12 400 ₽ первое условие B2>=1000 выполнено, Excel возвращает «средний» и до проверки на 5 000 не доходит. Вторая ветка не сработает никогда: всё, что больше 5 000, уже забрала первая.

В сводке по сегментам это выглядит правдоподобно: средних четверо, малых двое, крупных нет. Ровно такой пример — с комиссией, где порог в 5 000 стоит раньше более высокого, — Microsoft разбирает в статье о вложенных ЕСЛИ. Подробный разбор того же правила на заказах есть в задачах по Excel на собеседовании.

Проверка занимает минуту: подставьте значения у каждой границы — 5 000, 4 999, 1 000, 999 — и убедитесь, что каждое попало в свой сегмент. Ещё быстрее посчитать сегменты через СЧЁТЕСЛИ: пустой сегмент, который точно не должен быть пустым, выдаёт ошибку порядка.

ЕСЛИМН: несколько условий без вложенности

ЕСЛИМН принимает пары «условие — значение» и возвращает значение первой истинной пары: =ЕСЛИМН(B2>=5000;"крупный";B2>=1000;"средний";ИСТИНА;"малый"). Скобок меньше, порог и подпись стоят рядом, читать проще. Правило порядка то же: первая истинная пара выигрывает, поэтому пороги снова идут от большего к меньшему.

У ЕСЛИМН нет ветки «иначе». Если ни одно условие не выполнено, функция возвращает #Н/Д. Справка предлагает поставить последним условием ИСТИНА — оно выполняется всегда и работает как значение по умолчанию. Без этой пары Дельта и Сигма получат #Н/Д.

Главное ограничение — версии. По справке Microsoft ЕСЛИМН есть в Excel 2019, 2021, 2024 и Microsoft 365, в том числе для Mac. В Excel 2016 её нет: если коллега откроет ваш файл в старой версии, сегменты не посчитаются. Для файлов, которые уходят наружу, надёжнее вложенный ЕСЛИ или таблица порогов. В Google Таблицах такая функция тоже есть (IFS) и по справке Google тоже возвращает #Н/Д, когда все условия ложны.

ЕСЛИ и пустая ячейка

У клиента Сигма сумма не заполнена: CRM не подтянула оплаты, и в выгрузке пусто. В сравнении пустая ячейка ведёт себя как ноль — B7>=1000 даёт ЛОЖЬ, и формула сегмента уверенно пишет «малый». В отчёте Сигма ничем не отличается от клиента, который купил на 650 ₽, хотя про неё мы просто ничего не знаем.

Проверку на пустоту ставят первой: =ЕСЛИ(B2="";"нет данных";ЕСЛИ(B2>=5000;"крупный";ЕСЛИ(B2>=1000;"средний";"малый"))). Вместо B2="" можно написать ЕПУСТО(B2), но есть разница: ЕПУСТО даёт ИСТИНА только для действительно пустой ячейки, а B2="" — ещё и для ячейки с формулой, вернувшей пустую строку. Для выгрузок, где пустоту могли оставить формулы, ="" надёжнее.

Сколько таких клиентов, стоит посчитать до отчёта. Если «нет данных» у одного из шести — это строчка в примечании. Если у трети — сегментация не готова, и вопрос к выгрузке, а не к формуле. После проверки малых в примере становится один, а не два.

ЕСЛИОШИБКА и ЕСНД: когда ошибку можно прятать

Средний чек — сумма, делённая на число заказов: =B2/C2. У Сигмы ноль заказов, и формула даёт #ДЕЛ/0!. Первое желание — обернуть её: =ЕСЛИОШИБКА(B2/C2;""). Ошибка исчезает, но вместе с ней исчезает и любая другая. По справке Microsoft ЕСЛИОШИБКА перехватывает #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? и #ПУСТО!. Ошибётесь столбцом — напишете B2/D2, разделив сумму на город, — и вместо #ЗНАЧ! получите аккуратный пустой столбец по всем клиентам.

Точнее назвать ожидаемую ситуацию: =ЕСЛИ(C2=0;"нет заказов";B2/C2). Ноль заказов обработан, а настоящие ошибки формулы остаются видны. Если среднее потом идёт в СРЗНАЧ, подпись-текст функция пропустит, а ноль вместо пропуска исказил бы итог — этот случай разобран в задачах по Excel на собеседовании.

Для поиска по справочнику есть ЕСНД: она перехватывает только #Н/Д, то есть «ключ не найден», и пропускает остальные ошибки. =ЕСНД(ВПР(…;ЛОЖЬ);"нет в справочнике") подпишет отсутствующих клиентов, но не спрячет #ССЫЛКА! от неверного номера столбца. Подробнее — в статье о ВПР в Excel. По справке ЕСНД есть начиная с Excel 2016.

Таблица порогов вместо цепочки ЕСЛИ

Пороги сегментов живут дольше одного отчёта и меняются: финансы решили, что крупный клиент теперь от 7 000 ₽. Если пороги вбиты в формулу, их придётся найти и исправить в каждой. Microsoft в разборе вложенных ЕСЛИ советует другое: вынести пороги в небольшую таблицу и искать по ней через ВПР.

Положите таблицу в H2:I4: в первом столбце нижняя граница сегмента по возрастанию — 0, 1 000, 5 000, во втором — название. Формула =ВПР(B2;$H$2:$I$4;2;ИСТИНА) с приблизительным поиском найдёт последнюю границу, не превышающую сумму. Для 4 999 это 1 000 — «средний», для 12 400 — 5 000, «крупный». Сортировка по возрастанию здесь обязательна: на несортированной таблице приблизительный ВПР возвращает непредсказуемое значение. Знак $ закрепляет таблицу при протягивании.

В Excel 2021, 2024 и Microsoft 365 то же делает ПРОСМОТРX с режимом сопоставления −1 — «точное совпадение или ближайшее меньшее»: =ПРОСМОТРX(B2;$H$2:$H$4;$I$2:$I$4;"нет";-1). Пустые суммы при любом способе проверяйте до поиска, как в разделе выше. Чтобы изменить порог или добавить сегмент, теперь достаточно поправить одну ячейку таблицы.

Тот же сегмент в SQL: CASE WHEN

Когда клиентов не шесть, а тысячи и выгрузка обновляется каждый день, сегменты считают запросом. В SQL роль вложенного ЕСЛИ играет CASE WHEN: ветки проверяются сверху вниз, выигрывает первая истинная, ELSE — значение «иначе». Ниже — учебная база «Маяк»: сумма платежей каждого пользователя и условные пороги в долларах, от 78 — крупный, от 38 — средний.

Пустая ячейка из Excel здесь называется NULL: у пользователя без платежей после LEFT JOIN сумма неизвестна. Сравнение с NULL не даёт ИСТИНА, поэтому без отдельной ветки такие пользователи молча уйдут в ELSE. Первая ветка IS NULL делает то же, что проверка B2="" в Excel. Результат — 48 крупных, 311 средних, 553 малых и 3 701 пользователь без оплат.

WITH totals AS (
  SELECT user_id, sum(amount) AS paid_total
  FROM payments
  GROUP BY user_id
)
SELECT
  CASE
    WHEN t.paid_total IS NULL THEN 'не платил'
    WHEN t.paid_total >= 78 THEN 'крупный'
    WHEN t.paid_total >= 38 THEN 'средний'
    ELSE 'малый'
  END AS segment,
  count(*) AS users,
  coalesce(sum(t.paid_total), 0) AS revenue
FROM users AS u
LEFT JOIN totals AS t ON t.user_id = u.user_id
GROUP BY 1
ORDER BY min(coalesce(t.paid_total, 0)) DESC

Обе ловушки в одном запросе

Повторим в SQL две ошибки из Excel сразу: меньший порог поставлен первым, а ветки для NULL нет. Запрос выполняется без ошибок и возвращает две строки. Крупного сегмента нет совсем, а 3 701 пользователь без единого платежа смешался с 553 малыми плательщиками — в «малых» 4 254 человека.

Разбор порядка WHEN, поведения без ELSE и CASE внутри агрегатов — в статье CASE WHEN в SQL. Если сегменты потом нужно выразить долями, пригодится процент и доля в SQL.

WITH totals AS (
  SELECT user_id, sum(amount) AS paid_total
  FROM payments
  GROUP BY user_id
)
SELECT
  CASE
    WHEN t.paid_total >= 38 THEN 'средний'
    WHEN t.paid_total >= 78 THEN 'крупный'
    ELSE 'малый'
  END AS segment,
  count(*) AS users
FROM users AS u
LEFT JOIN totals AS t ON t.user_id = u.user_id
GROUP BY 1
ORDER BY 2

Частые ошибки в формуле ЕСЛИ

Почти все они не дают сообщения об ошибке: формула считает, но не то. Поэтому каждую новую формулу сегмента проверяйте на значениях у границ и подсчётом строк в каждом сегменте.

• Меньший порог стоит первым — верхние сегменты пустеют. Пороги от большего к меньшему.
• > вместо >= — клиент ровно на границе уходит в соседний сегмент. Сверяйте знак с формулировкой «от» или «больше».
• Текст без кавычек — #ИМЯ?. Кавычки нужны вокруг каждого слова-результата и каждого текстового значения в сравнении.
• Двойное неравенство 1000<=B2<5000 — у всех строк один ответ, и без ошибки. Диапазон пишется через И(B2>=1000;B2<5000).
• Пустая ячейка попадает в нижний сегмент. Первой ставьте проверку B2="".
• ЕСЛИМН без последней пары ИСТИНА;"…" — #Н/Д у всех, кто не прошёл ни одно условие.
• ЕСЛИОШИБКА вокруг всей формулы прячет опечатки. Обрабатывайте конкретную ситуацию: ноль в знаменателе — через ЕСЛИ, ненайденный ключ — через ЕСНД.
• Пороги вбиты в десятки формул. Вынесите их в таблицу и ищите сегмент через ВПР или ПРОСМОТРX.

Частые вопросы

Итог

ЕСЛИ — это одна проверка и два ответа, а всё сложное собирается из неё: И и ИЛИ для нескольких условий, вложенные ЕСЛИ и ЕСЛИМН для сегментов, ЕСНД и явная проверка знаменателя для ошибок. Три правила держат сегментацию честной: пороги от большего к меньшему, пустые ячейки проверяются первыми, ошибки прячутся только ожидаемые. Когда порогов больше трёх, им место в таблице, а не в формуле.

Готовые сегменты обычно идут в сводную таблицу и дашборд в Excel. Формулу может подсказать и нейросеть, но проверку на границах она за вас не сделает. Когда клиентов тысячи, тот же сегмент считают CASE WHEN и GROUP BY в SQL.