Excel IF с AND/OR: примеры и шпаргалка

MS-Excel IF Function Cover

Короткий обзор: что такое IF (ЕСЛИ) и как она устроена

MS Excel If Function Arguments

Функция IF требует одного обязательного аргумента — логической проверки. В классическом виде аргументы такие:

Логическое_выражение — условие, которое проверяется на значение ИСТИНА/ЛОЖЬ.
value_if_true — значение или выражение, которое возвращается, если тест истинный.
value_if_false — значение или выражение, которое возвращается, если тест ложный.

Важно: если value_if_true или value_if_false оставить пустыми, нужно указать пустую строку как “” — иначе Excel может вернуть 0 или ошибку.

Определение терминов (1 строка):

Логическое выражение — сравнение или функция, которая дает ИСТИНА/ЛОЖЬ.

Замечание о локали: в англоязычном Excel разделитель аргументов — запятая (,), в русской локали часто — точка с запятой (;). Имена функций тоже локализованы: IF → ЕСЛИ, AND → И, OR → ИЛИ.

Пример: магазин украшений — бонусы сотрудникам

Предположим, у вас таблица с продажами и вы хотите выдать бонус, если сотрудник выполнил минимальный порог.

Worker Weekly Sales Data

Условия примера: 7 сотрудников, порог по количеству продаж — 4, дополнительный порог по выручке — 2500.

Простой IF: только по количеству продаж

В ячейке результата применим формулу, которая пометит сотрудника как Eligible (или “Допущен”). В англоязычной версии формула выглядит так:

=IF(B4>=4,"Eligible","")

Локализованный вариант для русской версии Excel (если в вашей системе используется русский интерфейс и разделитель — точка с запятой):

=ЕСЛИ(B4>=4;"Допущен";"")

Объяснение: тест B4>=4 — это логическое выражение; если оно ИСТИНА, функция возвращает “Допущен”, иначе — пустую строку.

Используйте автозаполнение, чтобы протянуть формулу на остальные строки.

Applying Simple IF Function To Dataset

Calculated Result For IF Function Application

Если вместо пустой строки вы хотите явно написать “Ineligible”/“Не допущен”, подставьте это значение в третий аргумент:

=IF(B4>=4,"Ineligible","Not eligible")

Final Result Using Different Arguments

Использование оператора AND (И) с IF

Когда требуется проверять одновременно несколько условий — например, и количество продаж, и выручка — используется AND/И.

Англоязычная формула:

=IF(AND(B4>=4,C4>2500),"Eligible","Ineligible")

Русская версия:

=ЕСЛИ(И(B4>=4;C4>2500);"Допущен";"Не допущен")

Обратите внимание на локальный разделитель аргументов. Если условие ИСТИНА (оба подусловия выполняются), функция возвращает значение для true, иначе — значение для false.

Using IF Function with AND Operator

На нашем примере соответствуют критериям только те сотрудники, у которых и количество продаж >=4, и выручка >2500.

Важное замечание: если одна из проверок может вернуть ошибку (например, деление на ноль или #N/A), рассмотрите добавление функций ЕСЛИОШИБКА/IFERROR вокруг выражений.

Использование оператора OR (ИЛИ) с IF

Если бонус выдают при выполнении хотя бы одного из условий, используйте OR/ИЛИ.

Англоязычная формула:

=IF(OR(B4>=4,C4>2500),"Eligible","Ineligible")

Русская версия:

=ЕСЛИ(ИЛИ(B4>=4;C4>2500);"Допущен";"Не допущен")

Using IF Funciton with OR Operator

В этом сценарии сотрудник считается допущенным, если выполнено хотя бы одно условие. Например, сотрудник 7 в нашем наборе попадает под правило, хотя выручка у него меньше порога.

Вложенные IF и альтернативы: когда применять и когда избегать

Вложенные IF часто используют для множественных ветвлений (например, A/B/C уровни). Пример вложенного подхода — ранжирование по уровню бонуса:

=IF(B4>=10,"Высокий";IF(B4>=6,"Средний";IF(B4>=4,"Низкий";"Нет")))

Русская версия:

=ЕСЛИ(B4>=10;"Высокий";ЕСЛИ(B4>=6;"Средний";ЕСЛИ(B4>=4;"Низкий";"Нет")))

Однако вложенные IF быстро становятся трудночитаемыми и уязвимыми к ошибкам. Альтернативы:

IFS (ЕСЛИМН) — удобна для последовательных проверок (в новых версиях Excel).
ВСПР/INDEX+MATCH или сводные таблицы для агрегаций по группам.
Таблица правил (внешняя таблица с VLOOKUP/XLOOKUP по порогам).

Пример IFS (англ.):

=IFS(B4>=10,"Высокий",B4>=6,"Средний",B4>=4,"Низкий",TRUE,"Нет")

Русский вариант (если доступна функция ЕСЛИМН):

=ЕСЛИМН(B4>=10;"Высокий";B4>=6;"Средний";B4>=4;"Низкий";ИСТИНА;"Нет")

Производительность и масштабирование

Большое количество сложных вложенных IF в таблице с десятками тысяч строк может замедлить книгу. Хорошая практика — вынести правила в отдельную справочную таблицу и использовать XLOOKUP/ВПР.
Избегайте волатильных функций внутри логики IF (например, СЕГОДНЯ) без необходимости.
При массовых вычислениях рассмотрите перевод формул в вычисляемые колонки Power Query/Power BI.

Совместимость и локализация

Имена функций: IF→ЕСЛИ, AND→И, OR→ИЛИ, IFS→ЕСЛИМН, IFERROR→ЕСЛИОШИБКА.
Разделители аргументов: английская локаль — “,”; русская — “;” (проверьте настройки Excel в вашей системе).
При обмене файлами между локалями формулы автоматически переводятся Excel’ем при открытии в другой локали, но при копировании текста формулы вручную нужно корректировать имена и разделители.

Шпаргалка: короткие формулы и шаблоны

Простой IF:

=IF(условие,значение_если_TRUE,значение_если_FALSE)

AND + IF:

=IF(AND(условие1,условие2),"Да","Нет")

OR + IF:

=IF(OR(условие1,условие2),"Да","Нет")

Вложенные IF (ранжирование):
```
=IF(B4>=10,"A",IF(B4>=5,"B","C"))
```

Локализация (русская):

=ЕСЛИ(ИЛИ(B4>=4;C4>2500);"Допущен";"Не допущен")

Мини-методология: как спроектировать условную логику для отчёта

Перечислите все правила и приоритеты (какое условие важнее).
Отдельно протестируйте каждую проверку в отдельных ячейках как логический тест.
Составьте таблицу правил (уровень, порог, результат).
Решите: формула (ЕСЛИ/ЕСЛИМН) или внешняя таблица + XLOOKUP.
Напишите формулу, протестируйте на краевых значениях, проверьте ошибки.
Задокументируйте логику в примечаниях листа.

Checklist для ролей

Аналитик:

Убедиться в корректности порогов.
Протестировать формулы на примерах с краевыми значениями.

HR/Менеджер магазина:

Подтвердить текст меток (“Допущен”/“Не допущен”).
Сверить логику с политикой бонусирования.

Разработчик отчётов/BI:

Оценить объём данных и необходимость Power Query.
Проверить влияние на производительность.

Критерии приёмки

Результаты для контрольного набора из 10 строк соответствуют ожиданиям.
Все формулы работают без ошибок при пустых значениях в столбцах B и C.
Документация правил доступна и понятна для менеджера.

Тестовые случаи (примеры для проверки)

B=4, C=2501 → при AND и OR должен быть “Допущен”.
B=4, C=2200 → при AND — “Не допущен”, при OR — “Допущен”.
B=3, C=2600 → при AND — “Не допущен”, при OR — “Допущен”.
Пустые ячейки B/C → формула не должна выдавать ошибку.

Ментальные модели и подсказки

“AND” требует все галочки — представьте список условий, все должны быть отмечены галкой.
“OR” — достаточно одной галочки.
Вложенные IF — как древовидный набор вопросов: сначала высокий уровень, затем уточнения.

Когда подход с IF не сработает (контрпримеры)

Когда у вас много уровней логики — тогда лучше использовать таблицу правил + XLOOKUP/ВПР.
Когда логика меняется часто — трудно поддерживать вложенные формулы.
При больших объёмах данных встроенные формулы могут замедлить файл.

Диаграмма принятия решения (Mermaid)

flowchart TD
  A[Начало: взять строку сотрудника] --> B{B4>=4}
  B -- Да --> C{C4>2500}
  B -- Нет --> D[Не допущен]
  C -- Да --> E[Допущен]
  C -- Нет --> F{Тип правила}
  F -- AND --> D
  F -- OR --> E
  E --> G[Конец]
  D --> G

Факты и ключевые числа (из примера)

Сотрудников в примере: 7
Порог по продажам: 4
Порог по выручке: 2500 (валюта зависит от вашей таблицы)

Короткий глоссарий

IF / ЕСЛИ — функция ветвления: возвращает результат в зависимости от проверки.
AND / И — возвращает ИСТИНА, если все подусловия истинны.
OR / ИЛИ — возвращает ИСТИНА, если хотя бы одно подусловие истинно.

Резюме

Функция IF (ЕСЛИ) — базовый инструмент для автоматизации проверок в Excel. В сочетании с AND/OR вы можете легко реализовать правила принятия решений для бонусов, категорий и фильтров. Для сложной или часто меняющейся логики рекомендуется выносить правила в отдельную таблицу и использовать современные функции (IFS/ЕСЛИМН, XLOOKUP/ВПР) или Power Query.

Важно: учитывайте локализацию Excel (имена функций и разделители аргументов) и тестируйте формулы на граничных значениях.

Применяйте автозаполнение и документируйте правила — это снизит количество ошибок и упростит поддержку отчётов.