Функция ЕСЛИ в Excel проверяет заданное условие и возвращает один из двух результатов: первый — когда условие выполняется, второй — когда оно ложно. С ее помощью можно автоматически присваивать статусы, сравнивать план с фактом, рассчитывать скидки, проверять несколько критериев и классифицировать числовые показатели. В этой инструкции разберем синтаксис функции, простые и вложенные условия, связки с И и ИЛИ, а также распространенные ошибки.
Как работает функция ЕСЛИ в Excel
Функция ЕСЛИ относится к категории логических функций. В простейшем случае она отвечает на вопрос: выполняется ли указанное условие? Если да, Excel возвращает одно значение, если нет — другое.
Общий синтаксис выглядит так:
=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)
Формула содержит три аргумента:
1. Логическое_выражение — проверяемое условие. Например, A2>100, B2=»Оплачен» или C2>=D2.
2. Значение_если_истина — результат, который нужно вернуть при выполнении условия.
3. Значение_если_ложь — результат для ситуации, когда условие не выполняется.
В русской локализации Excel аргументы формул обычно разделяются точкой с запятой. В некоторых системах, в зависимости от региональных параметров Windows или macOS, разделителем может быть запятая. Если скопированная формула не принимается, сначала проверьте именно разделители.
Для сравнения значений можно использовать следующие операторы:
= — равно;
> — больше;
< — меньше;
>= — больше или равно;
<= — меньше или равно;
<> — не равно.
Текстовые значения в формулах заключаются в двойные кавычки. Числа, ссылки на ячейки и логические значения ИСТИНА и ЛОЖЬ записываются без кавычек.
Пример 1. Сравнение плана и фактического результата
Рассмотрим таблицу продаж. В столбце B находится план, в столбце C — фактическая сумма. В столбце D требуется вывести статус выполнения.
1. Выделите ячейку D6.
2. Введите формулу:
=ЕСЛИ(C6>=B6;"Выполнен";"Не выполнен")
3. Нажмите Enter.
4. Протяните формулу вниз за маркер заполнения в правом нижнем углу ячейки.
Excel сравнит факт с планом. Если значение в C6 больше значения в B6 или равно ему, появится статус «Выполнен». В остальных случаях будет выведено «Не выполнен».
Обратите внимание на оператор >=. Благодаря ему точное совпадение плана и факта тоже считается выполнением. Если использовать только знак >, строка с одинаковыми значениями получит статус «Не выполнен».
Пример 2. Проверка текста с помощью ЕСЛИ
Функция умеет сравнивать не только числа, но и текст. Предположим, в столбце C находится состояние оплаты заказа. Если указано слово «Оплачен», заказ можно передавать на отгрузку.
Формула для ячейки D6:
=ЕСЛИ(C6="Оплачен";"Отгрузить";"Ожидать оплату")
Здесь Excel проверяет точное совпадение содержимого C6 с текстом «Оплачен». Если условие истинно, возвращается команда «Отгрузить». Для любого другого значения выводится «Ожидать оплату».
При сравнении текста важно учитывать лишние пробелы. Значения «Оплачен» и «Оплачен » с пробелом в конце визуально похожи, но могут обрабатываться по-разному. Если данные загружаются из другой программы, их желательно предварительно очистить.
Текст внутри формулы обязательно заключается в двойные кавычки. Запись C6=Оплачен без кавычек может привести к ошибке ИМЯ?, потому что Excel попытается найти именованный диапазон с таким названием.
Пример 3. Расчет скидки по сумме заказа
Аргументы функции ЕСЛИ могут содержать не только готовый текст, но и числа, проценты или вычисления. Допустим, магазин применяет следующие правила:
— при сумме меньше 50 000 рублей скидки нет;
— от 50 000 рублей предоставляется скидка 5 процентов;
— от 100 000 рублей предоставляется скидка 10 процентов.
Поскольку возможны три результата, потребуется вложенная функция ЕСЛИ:
=ЕСЛИ(B6>=100000;10%;ЕСЛИ(B6>=50000;5%;0%))
Проверка начинается с самого высокого порога. Если сумма не меньше 100 000, Excel возвращает 10 процентов и завершает вычисление. Если первое условие ложно, программа переходит ко второй функции ЕСЛИ и проверяет порог 50 000.
После определения скидки итоговую сумму можно рассчитать отдельной формулой:
=B6*(1-C6)
Здесь B6 — первоначальная сумма заказа, а C6 — найденный процент скидки. Разделение расчета на два столбца делает таблицу понятнее: пользователь видит и примененную ставку, и итоговую сумму.
Что такое вложенные условия ЕСЛИ
Вложенные условия в Excel применяются, когда двух результатов недостаточно. Одна функция ЕСЛИ помещается внутрь аргумента другой функции. Таким способом можно последовательно проверить несколько критериев.
По официальной документации Microsoft в формулу допускается вкладывать до 64 уровней функций. Однако использовать десятки вложений на практике не рекомендуется. Длинную формулу трудно читать, проверять и изменять. Если условий много, лучше рассмотреть функцию ЕСЛИМН, таблицу соответствий или функции поиска.
Логика вложенной формулы обычно выглядит так:
=ЕСЛИ(условие1;результат1;ЕСЛИ(условие2;результат2;результат3))
Если первое условие истинно, Excel возвращает первый результат и больше ничего не проверяет. Вторая функция выполняется только тогда, когда первое условие оказалось ложным. Поэтому порядок проверок напрямую влияет на ответ.
Пример 4. Оценка результата по нескольким диапазонам
Предположим, результат тестирования оценивается по такой шкале:
— от 90 баллов — «Отлично»;
— от 75 баллов — «Хорошо»;
— от 60 баллов — «Зачет»;
— меньше 60 баллов — «Не сдано».
Для оценки значения из B6 подойдет формула:
=ЕСЛИ(B6>=90;"Отлично";ЕСЛИ(B6>=75;"Хорошо";ЕСЛИ(B6>=60;"Зачет";"Не сдано")))
Разберем ее по шагам:
1. Excel проверяет, набрано ли не меньше 90 баллов.
2. Если первая проверка ложна, программа сравнивает результат с порогом 75.
3. Если и это условие не выполнено, проверяется порог 60.
4. Все оставшиеся значения получают результат «Не сдано».
Пороговые условия расположены от большего значения к меньшему. Если начать с проверки B6>=60, то любое значение от 90 также удовлетворит первому условию и получит «Зачет». До проверки категории «Отлично» вычисление уже не дойдет.
При составлении длинной формулы удобно временно добавлять переносы в строке формул или собирать каждую проверку отдельно. После этого отдельные выражения можно объединить в одну конструкцию.
Пример 5. Одновременная проверка с функцией И
Не всегда требуется выбирать один из нескольких диапазонов. Иногда результат зависит от одновременного выполнения нескольких требований. Для этого используется связка ЕСЛИ и И.
Допустим, сотруднику назначается премия при соблюдении двух условий:
— стаж составляет не меньше 12 месяцев;
— KPI равен 90 процентам или превышает это значение.
Формула:
=ЕСЛИ(И(B6>=12;C6>=90%);"Премия";"Без премии")
Сначала функция И проверяет оба выражения. Она возвращает ИСТИНА, только если истинны все перечисленные условия. Затем внешняя функция ЕСЛИ преобразует логический ответ в понятный текст.
Сотрудник со стажем 18 месяцев и KPI 94 процента получит премию. При стаже 8 месяцев премии не будет даже с KPI 97 процентов, поскольку одно из требований не выполнено.
Связка ЕСЛИ с И удобна при проверке допуска, комплектности документов, минимальных показателей, диапазона дат и других ситуаций, где важны сразу несколько критериев.
Пример 6. Проверка альтернативных условий с ИЛИ
Функция ИЛИ используется, когда достаточно выполнения хотя бы одного критерия. Например, заказ необходимо проверить, если у клиента есть просрочка или сумма долга превышает 50 000 рублей.
Формула:
=ЕСЛИ(ИЛИ(B6>0;C6>50000);"Проверить";"ОК")
Функция ИЛИ возвращает ИСТИНА в трех случаях:
— существует просрочка;
— долг превышает установленный предел;
— одновременно выполнены оба условия.
Статус «ОК» появится только тогда, когда просрочки нет и задолженность не превышает 50 000 рублей.
Различие между И и ИЛИ принципиально. Функция И требует выполнения всех условий, а ИЛИ — хотя бы одного. Если результаты в таблице кажутся нелогичными, в первую очередь проверьте, правильно ли выбрана логическая функция.
Как оставить результат пустым
Частая проблема возникает, когда исходная ячейка еще не заполнена, но формула уже выводит отрицательный статус. Например, пустое поле с баллами может быть обработано как значение, не достигшее проходного порога.
Чтобы результат оставался пустым до ввода данных, сначала нужно проверить исходную ячейку:
=ЕСЛИ(B6="";"";ЕСЛИ(B6>=60;"Зачет";"Не зачет"))
Первая часть B6=»» проверяет отсутствие значения. Если ячейка пуста, формула возвращает пустую строку «». Если данные введены, запускается вторая проверка.
Такой прием особенно полезен в шаблонах, которые заранее содержат формулы на сотнях строк. Пользователь видит чистую таблицу, а результаты появляются по мере заполнения исходных данных.
Следует учитывать, что пустая строка, возвращенная формулой, и полностью пустая ячейка технически различаются. Для большинства отчетов это не мешает, но может иметь значение при подсчете непустых ячеек и последующей обработке данных.
ЕСЛИМН как альтернатива вложенным функциям
В современных версиях Excel длинную цепочку вложенных условий можно заменить функцией ЕСЛИМН. Она последовательно проверяет пары «условие — результат» и возвращает значение для первого истинного условия.
Для рассмотренной шкалы оценок формула будет выглядеть так:
=ЕСЛИМН(B6>=90;"Отлично";B6>=75;"Хорошо";B6>=60;"Зачет";ИСТИНА;"Не сдано")
Последняя пара ИСТИНА;»Не сдано» играет роль результата по умолчанию. До нее вычисление доходит, если ни один из предыдущих порогов не сработал.
Преимущество ЕСЛИМН заключается в более простой структуре: не требуется добавлять множество закрывающих скобок. При этом порядок условий остается важным — проверки также выполняются сверху вниз.
По документации Microsoft функция ЕСЛИМН доступна в Excel 2019, Excel 2021, Excel 2024 и Microsoft 365. Если файл должен открываться в более старых выпусках Excel, безопаснее использовать обычные вложенные функции ЕСЛИ или таблицу соответствий.
Типовые ошибки в формулах ЕСЛИ
Функция ЕСЛИ не работает чаще всего из-за синтаксической ошибки или неверного порядка условий.
1. Текст записан без кавычек.
Неверно:
=ЕСЛИ(A2=Да;1;0)
Правильно:
=ЕСЛИ(A2="Да";1;0)
2. Использован неподходящий разделитель аргументов.
Для русской локали обычно применяется точка с запятой. Если Excel подчеркивает формулу сразу после ввода, проверьте региональные настройки и замените запятые на точки с запятой либо наоборот.
3. Не закрыты скобки.
Каждой открывающей скобке должна соответствовать закрывающая. Чем больше вложенных функций, тем легче пропустить одну из них.
4. Условия расположены в неправильном порядке.
При классификации чисел начинайте с самого высокого порога. Excel останавливается на первом истинном условии и не анализирует оставшуюся часть цепочки.
5. Число записано как текст.
Если значение импортировано из внешнего файла, оно может выглядеть как число, но храниться в текстовом формате. В такой ситуации сравнения и расчеты могут давать неожиданный результат. Обычно Excel показывает предупреждение рядом с ячейкой и предлагает преобразовать значение в число.
6. В формулу вручную записаны изменяемые нормативы.
Формула =ЕСЛИ(B2>=100000;10%;0%) работает, но при изменении порога придется редактировать все подобные формулы. В рабочих книгах порог и процент лучше хранить в отдельных ячейках и использовать абсолютные ссылки, например $F$2 и $G$2.
7. Используется слишком много вложений.
Хотя Excel поддерживает до 64 уровней вложенности, это технический предел, а не рекомендация. Если формулу сложно прочитать без длительного разбора, стоит заменить ее функцией ЕСЛИМН, вспомогательными столбцами или таблицей соответствий.
Как проверить сложную формулу пошагово
При работе с вложенными условиями полезно проверять не только итоговый результат, но и каждую часть выражения.
1. Выделите ячейку с формулой.
2. Откройте вкладку «Формулы».
3. Нажмите «Вычислить формулу».
4. Последовательно просматривайте результаты отдельных сравнений.
Также можно выделить фрагмент формулы в строке формул и нажать F9, чтобы увидеть вычисленное значение. После такой проверки нажмите Esc, а не Enter, иначе выделенный фрагмент может быть заменен постоянным результатом.
Для поиска логической ошибки временно вынесите отдельные условия в соседние столбцы. Например, выражения B6>=12 и C6>=90% можно проверить независимо. Если каждое из них возвращает ожидаемые ИСТИНА или ЛОЖЬ, их уже безопасно объединять функцией И.
Практические рекомендации
— Начинайте формулу со схемы условий на бумаге или в текстовом редакторе.
— Располагайте числовые пороги от большего к меньшему, если проверка идет через оператор «больше или равно».
— Храните изменяемые лимиты, проценты и нормативы в отдельных ячейках.
— Используйте абсолютные ссылки со знаком $, если при копировании формулы адрес порога не должен изменяться.
— Проверяйте граничные значения: ровно 50 000, ровно 60 баллов, ровно 90 процентов.
— Отдельно тестируйте пустые ячейки, нулевые и отрицательные значения.
— Не объединяйте множество бизнес-правил в одну трудночитаемую формулу.
— При большом количестве диапазонов рассмотрите ЕСЛИМН или отдельную таблицу соответствий.
Функция ЕСЛИ в Excel подходит для большинства задач, связанных с автоматическим выбором результата. Простая формула обрабатывает два варианта, вложенная конструкция позволяет добавить несколько уровней, функция И проверяет одновременное выполнение критериев, а ИЛИ срабатывает при выполнении хотя бы одного условия.
Главное при составлении формулы — правильно выбрать порядок проверок, учитывать граничные значения и не забывать о кавычках вокруг текста. Если вложенная конструкция становится слишком длинной, ее лучше заменить функцией ЕСЛИМН, вспомогательными столбцами или таблицей правил. Такой подход упрощает проверку и дальнейшее обслуживание книги.
