Вложенный ЕСЛИ в Excel — формула с несколькими условиями
В учебнике объясняется, как использовать несколько ЕСЛИ в Excel, и приводится несколько примеров вложенных формул ЕСЛИ для наиболее распространенных задач.
Если кто-нибудь спросит вас, какую функцию Excel вы используете чаще всего, каков будет ваш ответ? В большинстве случаев это функция Excel ЕСЛИ. Обычная формула If, проверяющая одно условие, очень проста и удобна в написании. Но что, если ваши данные требуют более сложных логических тестов с несколькими условиями? В этом случае вы можете включить несколько функций ЕСЛИ в одну формулу, и эти несколько операторов Если называются вложенными ЕСЛИ Excel. Самым большим преимуществом вложенного оператора If является то, что он позволяет вам проверять более одного условия и возвращать разные значения в зависимости от результатов этих проверок, и все это в одной формуле.
Microsoft Excel имеет ограничения на уровни вложенных ЕСЛИ. В Excel 2003 и более ранних версиях допускается до 7 уровней. В Excel 2007 и более поздних версиях в одну формулу можно вложить до 64 функций ЕСЛИ.
Далее в этом руководстве вы найдете несколько вложенных примеров Excel вместе с подробным объяснением их синтаксиса и логики.
Пример 1. Классическая вложенная формула ЕСЛИ
Вот типичный пример Excel If с несколькими условиями. Предположим, у вас есть список студентов в столбце A и их экзаменационные баллы в столбце B, и вы хотите классифицировать баллы со следующими условиями:
- Отлично: более 249
- Хорошо: от 249 до 200 включительно
- Удовлетворительно: от 199 до 150 включительно
- Бедные: до 150 лет
А теперь давайте напишем вложенную функцию ЕСЛИ на основе вышеуказанных критериев. Считается хорошей практикой начинать с самого важного условия и максимально упрощать свои функции. Наша вложенная формула ЕСЛИ в Excel выглядит следующим образом:
=ЕСЛИ(В2>249, «Отлично», ЕСЛИ(В2>=200, «Хорошо», ЕСЛИ(В2>150, «Удовлетворительно», «Плохо»)))
И работает именно так, как должно:
Понимание логики вложенных ЕСЛИ в Excel
Я слышал, как некоторые люди говорят, что Excel сводит их с ума, если несколько раз. Попробуйте взглянуть на это под другим углом:
Что формула на самом деле говорит Excel, так это оценить логический_тест первой функции ЕСЛИ и, если условие выполнено, вернуть значение, указанное в значение_если_истина аргумент. Если условие 1-й функции If не выполняется, то проверяется 2-я инструкция If и так далее.
ЕСЛИ(проверить, если В2>=249, если правда – вернуть “Превосходно”, или иначе
ЕСЛИ(проверить, если В2>=200, если правда – вернуть “Хороший”, или иначе
ЕСЛИ(проверить, если В2>150, если правда – вернуть «Удовлетворительно», если неверно –
возвращаться “Бедный”)))
Пример 2. Множественное ЕСЛИ с арифметическими вычислениями
Вот еще одна типичная задача: цена за единицу варьируется в зависимости от указанного количества, и ваша цель — написать формулу, которая вычисляет общую стоимость для любого количества товаров, введенных в определенную ячейку. Другими словами, ваша формула должна проверять несколько условий и выполнять различные вычисления в зависимости от того, в какой диапазон сумм попадает указанное количество:
Единица Количество Цена за единицу От 1 до 10 $20 От 11 до 19 $18 От 20 до 49 $16 От 50 до 100 $13 Более 101 $12
Эту задачу также можно решить, используя несколько функций ЕСЛИ. Логика та же, что и в приведенном выше примере, с той лишь разницей, что вы умножаете указанное количество на значение, возвращаемое вложенными ЕСЛИ (т. е. соответствующую цену за единицу).
Предполагая, что пользователь вводит количество в ячейку B8, формула выглядит следующим образом:
=B8*IF(B8>=101, 12, ЕСЛИ(B8>=50, 13, ЕСЛИ(B8>=20, 16, ЕСЛИ( B8>=11, 18, ЕСЛИ(B8>=1, 20, “” )))))
И результат будет выглядеть примерно так:
Как вы понимаете, этот пример демонстрирует только общий подход, и вы можете легко настроить эту вложенную функцию If в зависимости от вашей конкретной задачи.
Например, вместо «жесткого кодирования» цен в формуле вы можете ссылаться на ячейки, содержащие эти значения (ячейки с B2 по B6). Это позволит вашим пользователям редактировать исходные данные без необходимости обновлять формулу:
=B8*IF(B8>=101,B6, IF(B8>=50, B5, IF(B8>=20, B4, IF( B8>=11, B3, IF(B8>=1, B2, “” )))))
Или вы можете включить дополнительные функции ЕСЛИ, которые фиксируют верхнюю, нижнюю или обе границы диапазона сумм. Когда количество выходит за пределы диапазона, формула будет отображать сообщение «вне диапазона». Например:
=ЕСЛИ(ИЛИ(B8>200,B8<1), "Количество вне диапазона", B8*IF(B8>=101,12, IF(B8>=50, 13, IF(B8>=20, 16) , ЕСЛИ( В8>=11, 18, ЕСЛИ(В8>=1, 20, “”))))))
Описанные выше вложенные формулы ЕСЛИ работают во всех версиях Excel. В Excel 365 и Excel 2021 вы также можете использовать функцию IFS для той же цели.
Опытные пользователи Excel, знакомые с формулами массива, могут использовать эту формулу, которая в основном делает то же самое, что и рассмотренная выше вложенная функция ЕСЛИ. Хотя формулу массива гораздо сложнее понять, не говоря уже о написании, у нее есть одно неоспоримое преимущество — вы указываете диапазон ячеек, содержащих ваши условия, а не ссылаетесь на каждое условие по отдельности. Это делает формулу более гибкой, и если ваши пользователи изменят какое-либо из существующих условий или добавят новое, вам нужно будет обновить только одну ссылку на диапазон в формуле.
Вложенный ЕСЛИ в Excel — советы и рекомендации
Как вы только что убедились, в использовании нескольких ЕСЛИ в Excel нет ничего сложного. Следующие советы помогут вам улучшить вложенные формулы ЕСЛИ и предотвратить распространенные ошибки.
Вложенные ограничения ЕСЛИ
В Excel 2007 — Excel 365 можно вложить до 64 функций ЕСЛИ. В более ранних версиях Excel 2003 и более ранних можно использовать до 7 вложенных функций ЕСЛИ. Однако тот факт, что вы можете вложить множество ЕСЛИ в одну формулу, не означает, что вы должны это делать. Имейте в виду, что каждый дополнительный уровень усложняет понимание формулы и устранение неполадок. Если в вашей формуле слишком много вложенных уровней, вы можете оптимизировать ее, используя один из этих вариантов.
Порядок вложенных функций ЕСЛИ имеет значение
Вложенная функция ЕСЛИ в Excel оценивает логические проверки в том порядке, в котором они появляются в формуле, и, как только одно из условий оценивается как ИСТИНА, последующие условия не проверяются. Другими словами, формула останавливается после первого результата TRUE.
Давайте посмотрим, как это работает на практике. При значении B2, равном 274, приведенная ниже вложенная формула ЕСЛИ оценивает первую логическую проверку (B2>249) и возвращает «Отлично», поскольку эта логическая проверка ИСТИНА:
=ЕСЛИ(В2>249, «Отлично», ЕСЛИ(В2>=200, «Хорошо», ЕСЛИ(В2>150, «Удовлетворительно», «Плохо»)))
Теперь давайте поменяем порядок функций ЕСЛИ на обратный:
=ЕСЛИ(В2>150, “Удовлетворительно”, ЕСЛИ(В2>200, “Хорошо”, ЕСЛИ(В2>249, “Отлично”, “Плохо”)))
Формула проверяет первое условие, и, поскольку 274 больше 150, результат этой логической проверки также ИСТИНА. Следовательно, формула возвращает «удовлетворительно» без проверки других условий.
Видите ли, изменение порядка функций ЕСЛИ меняет результат:
Оцените логику формулы
Чтобы пошагово просмотреть логическую последовательность вашей вложенной формулы ЕСЛИ, используйте функцию «Вычислить формулу», расположенную на Формула вкладка, в Аудит формул группа. Подчеркнутое выражение — это часть, которая в данный момент оценивается, и нажатие кнопки «Оценить» покажет вам все этапы процесса оценки.
Например, оценка первого логического теста вложенной формулы ЕСЛИ, показанная на снимке экрана ниже, будет выглядеть следующим образом: B2>249; 274>249; ПРАВДА; Превосходно.
Сбалансируйте круглые скобки вложенных функций ЕСЛИ
Одной из основных проблем с вложенными ЕСЛИ в Excel является сопоставление пар скобок. Если скобки не совпадают, ваша формула не будет работать. К счастью, Microsoft Excel предоставляет несколько функций, которые могут помочь вам сбалансировать круглые скобки при редактировании формулы:
- Если у вас более одного набора скобок, пары скобок затеняются разными цветами, чтобы открывающая скобка совпадала с закрывающей.
- Когда вы закрываете скобки, Excel ненадолго выделяет совпадающую пару. Тот же эффект выделения полужирным шрифтом или «мерцания» создается при перемещении по формуле с помощью клавиш со стрелками.
Дополнительные сведения см. в статье Сопоставление пар скобок в формулах Excel.
Обрабатывайте текст и числа по-разному
При построении логических тестов ваших вложенных формул ЕСЛИ помните, что к тексту и числам следует относиться по-разному — всегда заключайте текстовые значения в двойные кавычки, но никогда не заключайте в кавычки числа:
Справа: =ЕСЛИ(B2>249, “Отлично”,…)
Неправильно: =ЕСЛИ(B2>”249″, “Отлично”,…)
Логическая проверка второй формулы вернет ЛОЖЬ, даже если значение в ячейке B2 больше 249. Почему? Потому что 249 — это число, а «249» — числовая строка, а это две разные вещи.
Добавьте пробелы или разрывы строк, чтобы упростить чтение вложенных ЕСЛИ.
При построении формулы с несколькими вложенными уровнями ЕСЛИ вы можете сделать логику формулы более понятной, разделив различные функции ЕСЛИ пробелами или разрывами строк. Excel не заботится о дополнительных пробелах в формуле, поэтому вы можете не беспокоиться о его искажении.
Чтобы переместить определенную часть формулы на следующую строку, просто щелкните место, где вы хотите вставить разрыв строки, и нажмите Alt + Enter. Затем разверните панель формул настолько, насколько это необходимо, и вы увидите, что ваша вложенная формула ЕСЛИ стала намного проще для понимания.
Альтернативы вложенному ЕСЛИ в Excel
Чтобы обойти ограничение в семь вложенных функций ЕСЛИ в Excel 2003 и более ранних версиях и сделать ваши формулы более компактными и быстрыми, рассмотрите возможность использования следующих альтернатив вложенным функциям ЕСЛИ Excel.
- Чтобы протестировать несколько условий и вернуть разные значения на основе результатов этих тестов, вы можете использовать функцию ВЫБОР вместо вложенных ЕСЛИ.
- Создайте справочную таблицу и используйте ВПР с приблизительным соответствием, как показано в этом примере: ВПР вместо вложенного ЕСЛИ в Excel.
- Используйте ЕСЛИ с логическими функциями ИЛИ/И, как показано в этих примерах.
- Используйте формулу массива, как показано в этом примере.
- Объедините несколько операторов IF с помощью функции CONCATENATE или оператора объединения (&). Пример формулы можно найти здесь.
- Для опытных пользователей Excel лучшей альтернативой использованию нескольких вложенных функций ЕСЛИ может быть создание пользовательской функции рабочего листа с помощью VBA.
Вот как вы используете формулу If в Excel с несколькими условиями. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе.
Практическая рабочая тетрадь для скачивания
Вложенные операторы If Excel (файл .xlsx)
