Мастерство условного форматирования и расчетов в Excel

Чтобы создать формулу с несколькими условиями в Excel, используйте либо вложенные функции ЕСЛИ (для всех версий), либо функцию ЕСЛИМН (для Excel 2019 и новее). Вложенность позволяет проверять условия последовательно: =ЕСЛИ(условие1; результат1; ЕСЛИ(условие2; результат2; результат_по_умолчанию)). Функция ЕСЛИМН упрощает запись: =ЕСЛИМН(условие1; результат1; условие2; результат2; ИСТИНА; результат_по_умолчанию). Ниже приведены готовые примеры и разбор ошибок для мгновенного применения.

Базовый синтаксис и логика работы

Функция ЕСЛИ проверяет одно условие и возвращает одно из двух значений. Однако в реальных задачах часто требуется градация: например, присвоение скидки в зависимости от объема продаж или оценка результата теста по баллам.

Стандартная формула выглядит так:

=ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь)

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

Способ 1: Вложенные функции ЕСЛИ (Универсальный метод)

Этот метод работает во всех версиях Excel. Суть заключается в том, что вместо аргумента «значение_если_ложь» вставляется новая функция ЕСЛИ.

Пример: Расчет скидки в зависимости от суммы покупки

Допустим, в ячейке B2 находится сумма покупки.

• Более 50 000 руб. — скидка 10% (коэффициент 0,9).
• От 10 000 до 50 000 руб. — скидка 5% (коэффициент 0,95).
• Менее 10 000 руб. — без скидки.

Формула для ячейки C2:

=ЕСЛИ(B2>50000; B2*0,9; ЕСЛИ(B2>10000; B2*0,95; B2))

Как это работает:

1. Программа проверяет, больше ли 50 000. Если да — умножает на 0,9 и завершает работу.
2. Если нет, переходит ко второму ЕСЛИ: больше ли 10 000? Если да — умножает на 0,95.
3. Если оба условия ложны, возвращается значение последнего аргумента (B2 — полная стоимость).

Пример: Оценка успеваемости студентов

Требуется преобразовать баллы (ячейка A2) в текстовые оценки:

• ≥ 90: «Отлично»
• ≥ 75: «Хорошо»
• ≥ 60: «Удовлетворительно»
• < 60: «Неудовлетворительно»

=ЕСЛИ(A2>=90; "Отлично"; ЕСЛИ(A2>=75; "Хорошо"; ЕСЛИ(A2>=60; "Удовл."; "Неуд.")))

Способ 2: Функция ЕСЛИМН (Для Excel 2019, 2021 и Office 365)

В современных версиях Excel появилась функция ЕСЛИМН (англ. IFS), которая избавляет от необходимости писать множественные скобки и повторять слово ЕСЛИ.

Синтаксис проще: пары «условие – результат» перечисляются через точку с запятой.

=ЕСЛИМН(условие1; результат1; условие2; результат2; ...; ИСТИНА; результат_по_умолчанию)

Реализация примера со скидкой через ЕСЛИМН

Тот же сценарий со скидками записывается так:

=ЕСЛИМН(B2>50000; B2*0,9; B2>10000; B2*0,95; ИСТИНА; B2)

Здесь ИСТИНА в конце выступает в роли «иначе». Если ни одно из предыдущих условий не выполнилось, применяется последнее значение.

Комбинация с функциями И и ИЛИ

Иногда одно условие недостаточно, и нужно проверить сразу несколько критериев внутри одного блока. Для этого используются логические связки И (все условия должны быть верны) и ИЛИ (достаточно одного).

Пример сложного условия

Нужно начислить премию (20%), только если план выполнен (B2 > 100000) И сотрудник работает в отделе продаж (C2 = "Отдел продаж").

=ЕСЛИ(И(B2>100000; C2="Отдел продаж"); B2*0,2; 0)

Если бы требовалось выполнить хотя бы одно из условий (план выполнен ИЛИ стаж более 5 лет), использовалась бы конструкция ИЛИ.

Сравнение методов решения задач

Частые ошибки и способы их устранения

1. #ЗНАЧ! или #ИМЯ?

   • Причина: Опечатка в названии функции или использование неправильного разделителя. В русской версии Excel аргументы разделяются точкой с запятой (;), в английской — запятой (,). Проверьте настройки региона.
   • Решение: Убедитесь, что используете ЕСЛИМН только в поддерживаемых версиях. В старых файлах замените её на вложенные ЕСЛИ.

2. Неверный результат из-за порядка условий

   • Причина: Если поставить условие B2 > 10000 перед B2 > 50000, то для числа 60 000 сработает первое условие (оно тоже больше 10 000), и скидка будет рассчитана неверно.
   • Решение: Всегда сортируйте условия от самого строгого к самому мягкому (или наоборот, но последовательно).

3. Отсутствие значения «по умолчанию»

   • В ЕСЛИМН часто забывают добавить финальное условие ИСТИНА. Если ни одно условие не совпадет, функция вернет ошибку #Н/Д.
   • Решение: Всегда добавляйте пару ИСТИНА; "Значение по умолчанию" в конец формулы ЕСЛИМН.

FAQ

Сколько условий можно вложить в одну формулу? Технический лимит для вложенных ЕСЛИ составляет 64 уровня. Однако на практике рекомендуется не превышать 3–5 уровней, иначе формула становится неподдерживаемой. Для большего количества условий используйте ЕСЛИМН или функцию ВПР с таблицей справочником.

Почему формула возвращает ЛОЖЬ вместо числа? Скорее всего, вы забыли указать третий аргумент (значение, если условие ложно) в самой последней вложенной функции. По умолчанию Excel подставляет логическое ЛОЖЬ. Добавьте нужное значение или пустую строку "" в конец.

Можно ли комбинировать текстовые и числовые условия? Да. Например: =ЕСЛИ(И(A2="Москва"; B2>1000); "Бонус"; "Стандарт"). Главное — заключать текстовые значения в кавычки.