Осваиваем вложенные операторы IF в Excel – пошаговое руководство

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

• Синтаксис вложенного ЕСЛИ
• Практические примеры вложенного ЕСЛИ
• Вложенное ЕСЛИ с условиями И / ИЛИ

• Как сделать вложенное ЕСЛИ легко читаемым
• Порядок вложенных функций ЕСЛИ
• Числа и текст следует обрабатывать по-разному
• Ограничения вложенного ЕСЛИ

• Использование ВПР
• Использование ЕСЛИМН
• Использование ВЫБОР и ПОИСКПОЗ

Функция ЕСЛИ в Excel и вложенные операторы ЕСЛИ

Функция ЕСЛИ и вложенные операторы ЕСЛИ в Excel решают схожие задачи, но существенно различаются по сложности и областям применения.

Использование вложенного ЕСЛИ

В этом разделе показано базовое использование вложенных функций ЕСЛИ в Excel: их синтаксис, практические примеры и совместное применение с условиями И или ИЛИ.

Синтаксис вложенного ЕСЛИ

Понимание синтаксиса функции — залог её правильного и эффективного использования в Excel. Начнём с синтаксиса вложенных операторов ЕСЛИ.

Синтаксис:

=IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result4)))

Аргументы:

• Условие 1, Условие 2, Условие 3: это условия, которые необходимо проверить. Каждое из них оценивается последовательно, начиная с Условия 1.
• Результат1: это значение возвращается, если условие1 имеет значение ИСТИНА.
• Результат2: это значение возвращается, если Условие1 — ЛОЖЬ, а Условие2 — ИСТИНА. Важно отметить, что Результат2 вычисляется только тогда, когда Условие1 — ЛОЖЬ.
• Результат3: это значение возвращается, если Условие1 и Условие2 — ЛОЖЬ, а Условие3 — ИСТИНА. По сути, чтобы получить Результат3, предыдущие условия (Условие1 и Условие2) должны быть ЛОЖЬ.
• Результат4: Этот результат возвращается, если все условия (Условие1, Условие2 и Условие3) имеют значение ЛОЖЬ.
  
  Кратко данное выражение можно интерпретировать следующим образом:
  Test condition1, if TRUE, return result1, if FALSE,
  test condition2, if TRUE, return result2, if FALSE,
  test condition3, if TRUE, return result3, if FALSE,
  return result4

Помните: во вложенной конструкции ЕСЛИ каждое последующее условие проверяется только если все предыдущие оказались ЛОЖЬ. Эта последовательная проверка имеет ключевое значение для понимания работы вложенных функций ЕСЛИ.

Практические примеры вложенного ЕСЛИ

Теперь перейдём к практическому применению вложенных функций ЕСЛИ на двух примерах.

Пример 1: Система оценивания

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

Выберите пустую ячейку (в данном случае C2), введите приведённую ниже формулу и нажмите Enter, чтобы получить результат. Затем перетащите маркер заполнения вниз, чтобы быстро заполнить остальные ячейки.

=IF(B2>,=90,$F$2,IF(B2>,=80,$F$3,IF(B2>,=70,$F$4,IF(B2>,=60,$F$5,$F$6))))

Пример 2: Расчёт комиссионных с продаж

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

Выберите пустую ячейку (в данном случае C2), введите приведённую ниже формулу и нажмите Enter, чтобы получить результат. Затем перетащите маркер заполнения вниз, чтобы автоматически заполнить остальные ячейки.

=B2*IF(B2>,20000,$F$2,IF(B2>,=10000,$F$3,$F$4))

Вложенное ЕСЛИ с условиями И / ИЛИ

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

Использование вложенной функции ЕСЛИ с условием И

Если студент одновременно набирает 60 баллов или больше и имеет посещаемость 95 % или выше, его оценка повышается: вместо «A» — «A+», вместо «B» — «B+» и так далее. Если же посещаемость ниже 95 %, оценка выставляется исключительно по баллам. В этом случае следует использовать вложенную функцию ЕСЛИ с условием И.

Выберите пустую ячейку (в данном случае D2), введите приведённую ниже формулу и нажмите Enter, чтобы получить результат. Затем перетащите маркер заполнения вниз, чтобы автоматически заполнить остальные ячейки.

=IF(AND(B2>,=60, C2>,=95%),IF(B2>,=90, "A+", IF(B2>,=80, "B+", IF(B2>,=70, "C+", "D+"))),IF(B2>,=90, "A", IF(B2>,=80, "B", IF(B2>,=70, "C", IF(B2>,=60, "D", "F")))))

Использование вложенной функции ЕСЛИ с условием ИЛИ

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

Выберите пустую ячейку (в данном случае D2), введите приведённую ниже формулу и нажмите Enter, чтобы получить результат. Затем протяните маркер заполнения вниз, чтобы автоматически заполнить остальные ячейки.

=IF(OR(B2>,=95, C2>,=95%),IF(B2>,=90, "A+", IF(B2>,=80, "B+", IF(B2>,=70, "C+", IF(B2>,=60, "D+", "F+")))),IF(B2>,=90, "A", IF(B2>,=80, "B", IF(B2>,=70, "C", IF(B2>,=60, "D", "F")))))

Советы и рекомендации по использованию вложенного ЕСЛИ

В этом разделе вы найдёте четыре полезных совета и приёма для работы с вложенными функциями ЕСЛИ.

Как сделать вложенное ЕСЛИ легко читаемым

Типичный вложенный оператор ЕСЛИ может выглядеть компактно, но его непросто интерпретировать.

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

=IF(A2>,=90, "A", IF(A2>,=80, "B", IF(A2>,=70, "C", IF(A2>,=60, "D", "F"))))

Решение: добавление разрывов строк и отступов

Чтобы повысить читаемость вложенной функции ЕСЛИ, разбейте формулу на несколько строк, размещая каждый вложенный оператор ЕСЛИ на отдельной строке. Для этого установите курсор перед нужным ЕСЛИ и нажмите Alt + Enter.

После форматирования приведённая выше формула будет выглядеть следующим образом:

=IF(A2>,=90, "A",
      IF(A2>,=80, "B",
          IF(A2>,=70, "C",
              IF(A2>,=60, "D", "F")))
)

Такой формат наглядно показывает, где расположено каждое условие и соответствующий ему результат, что существенно повышает читаемость формулы.

Порядок вложенных функций ЕСЛИ

Порядок логических условий во вложенной формуле ЕСЛИ имеет решающее значение, ведь именно он определяет, как Excel оценивает эти условия, и напрямую влияет на итоговый результат формулы.

Правильная формула

В примере системы оценивания мы используем следующую формулу для выставления оценок на основе набранных баллов.

=IF(B2>,=90, "A", IF(B2>,=80, "B", IF(B2>,=70, "C", IF(B2>,=60, "D", "F"))))

Excel последовательно проверяет условия во вложенной формуле ЕСЛИ — от первого к последнему. Сначала формула оценивает самый высокий порог баллов (>=90 для оценки «A»), а затем переходит к более низким. Такой подход гарантирует, что балл будет сопоставлен с наивысшей возможной оценкой. Если первое условие выполняется (A2>=90), функция сразу возвращает «A», и остальные условия не проверяются.

Неправильно упорядоченная формула

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

=IF(B2>,=60, "D", IF(B2>,=70, "C", IF(B2>,=80, "B", IF(B2>,=90, "A", "F"))))

В этой некорректной формуле балл 95 сразу удовлетворит первому условию B2>=60 и ошибочно получит оценку «D».

Числа и текст следует обрабатывать по-разному

В этом разделе показано, как числа и текст обрабатываются по-разному во вложенных функциях ЕСЛИ.

Числа

Числа применяются для арифметических сравнений и вычислений. Во вложенных функциях ЕСЛИ их можно напрямую сравнивать с помощью операторов >, =, <= и других.

Текст

Во вложенных функциях ЕСЛИ текст обязательно заключайте в двойные кавычки. Обратите внимание на значения A, B, C, D и F в приведённой ниже формуле:

=IF(A2>,=90, "A", IF(A2>,=80, "B", IF(A2>,=70, "C", IF(A2>,=60, "D", "F"))))

Ограничения вложенного ЕСЛИ

В этом разделе описаны ключевые ограничения и недостатки вложенных функций ЕСЛИ.

Сложность и читаемость:

Хотя Excel позволяет вкладывать до 64 функций ЕСЛИ, делать это крайне не рекомендуется. Чем больше уровней вложенности, тем сложнее становится формула. Это приводит к трудностям в чтении, понимании и обслуживании таких формул.

Подверженность ошибкам:

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

Сложность расширения и масштабирования:

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

Понимание этих ограничений крайне важно для эффективного применения вложенных функций ЕСЛИ в Excel. Часто комбинация вложенных ЕСЛИ с другими функциями или использование альтернативных подходов позволяет создавать более эффективные и удобные в обслуживании решения.

Альтернативы вложенному ЕСЛИ

В этом разделе представлены несколько функций Excel, способных заменить вложенные операторы ЕСЛИ.

Использование ВПР

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

Пример 1: Система оценивания с помощью ВПР

Здесь я покажу, как с помощью ВПР присваивать оценки на основе набранных баллов.

Шаг 1: Создание справочной таблицы для оценок

Сначала необходимо создать справочную таблицу (например, E1:F6) с диапазонами баллов и соответствующими оценками.Примечание:Баллы в первом столбце таблицы должны быть отсортированы по возрастанию.

Шаг 2: Применение функции ВПР для присвоения оценок

Выберите пустую ячейку (в данном случае C2), введите приведённую ниже формулу и нажмите Enter, чтобы получить первую оценку. Затем выделите ячейку с формулой и протяните её маркер заполнения вниз — так вы быстро получите остальные оценки.

=VLOOKUP(B2,$E$2:$F$6,2,TRUE)

Пример 2: Расчёт комиссионных с продаж с помощью ВПР

Вы также можете использовать ВПР для расчёта комиссионных с продаж в Excel. Следуйте инструкциям ниже.

Шаг 1: Создайте справочную таблицу для оценок

Сначала создайте справочную таблицу для объёмов продаж и соответствующих процентов комиссионных, например E2:F4. Примечание: Объёмы продаж в первом столбце таблицы должны быть отсортированы по возрастанию.

Шаг 2: Примените функцию ВПР для присвоения оценок

Выберите пустую ячейку (в данном случае C2), введите приведённую ниже формулу и нажмите Enter, чтобы рассчитать первую комиссионную сумму. Затем выделите ячейку с формулой и перетащите её маркер заполнения вниз — так вы быстро получите остальные результаты.

=B2*VLOOKUP(B2,$E$2:$F$4,2,TRUE)

Использование ЕСЛИМН

Функция IFS упрощает работу, избавляя от необходимости вложенных конструкций и делая формулы более читаемыми и удобными в управлении. Она значительно повышает ясность и облегчает обработку множества условных проверок. Чтобы воспользоваться функцией IFS, убедитесь, что у вас установлен Excel 2019 или более поздняя версия либо активна подписка на Office 365. Давайте рассмотрим её практическое применение на реальных примерах.

Пример 1: Система оценивания с использованием IFS

Предполагая те же критерии оценивания, что и ранее, функцию IFS можно использовать следующим образом:

Выберите пустую ячейку, например C2, введите приведённую ниже формулу и нажмите Enter, чтобы получить первый результат. Затем выделите эту ячейку и перетащите маркер заполнения вниз — так вы быстро получите остальные результаты.

=IFS(B2>,=90,"A",B2>,=80,"B",B2>,=70,"C",B2>,=60,"D",B2<,60,"F")

Пример 2: Расчёт комиссионных с использованием IFS

В сценарии расчёта комиссионных функция IFS применяется следующим образом:

Выберите пустую ячейку, например C2, введите приведённую ниже формулу и нажмите Enter, чтобы получить первый результат. Затем выделите эту ячейку и перетащите её маркер заполнения вниз — так вы мгновенно получите все остальные результаты.

=B2*IFS(B2>,20000,20%,B2>,=10000,15%,TRUE,10%)

Использование ВЫБОР и ПОИСКПОЗ

Подход с использованием функций CHOOSE и MATCH может быть эффективнее и проще в управлении по сравнению со вложенными операторами IF. Данный метод упрощает формулу и делает обновления или изменения более простыми. Ниже я продемонстрирую, как использовать комбинацию функций CHOOSE и MATCH для решения двух практических задач из этой статьи.

Пример 1: Система оценивания с использованием CHOOSE и MATCH

С помощью комбинации функций CHOOSE и MATCH вы можете легко присваивать оценки на основе различных баллов.

Шаг 1: Создайте массив поиска с помощью Значение для поиска

Во-первых, необходимо создать диапазон ячеек, содержащий пороговые значения, по которым будет осуществляться поиск с помощью функции MATCH, например $E$2:$E$6 в данном случае.Примечание:Числа в этом диапазоне должны быть отсортированы по возрастанию, чтобы функция MATCH корректно работала при использовании приблизительного соответствия.

Шаг 2: Примените CHOOSE и MATCH для присвоения оценок

Выберите пустую ячейку (в данном случае C2), введите приведённую ниже формулу и нажмите Enter, чтобы получить первую оценку. Затем выделите ячейку с формулой и перетащите её маркер заполнения вниз — так вы быстро получите остальные результаты.

=CHOOSE(MATCH(B2, $E$2:$E$6, 1), "F", "D", "C", "B", "A")

Пример 2: Расчёт комиссионных с использованием IFS

Комбинация функций ВЫБОР и ПОИСКПОЗ для расчёта комиссионных также может быть весьма эффективной — особенно когда ставки зависят от заданных пороговых значений продаж. Давайте разберёмся, как это реализовать.

Шаг 1: Создайте массив поиска с помощью Значение для поиска

Во-первых, необходимо создать диапазон ячеек, содержащий пороговые значения, по которым будет осуществляться поиск с помощью функции MATCH, например $E$2:$E$4 в данном случае.Примечание:Числа в этом диапазоне должны быть отсортированы по возрастанию, чтобы функция MATCH корректно работала при использовании приблизительного соответствия.

Шаг 2: Примените CHOOSE и MATCH для получения результатов

Выберите пустую ячейку (в данном случае C2), введите приведённую ниже формулу и нажмите клавишу Enter, чтобы получить первую оценку. Затем выделите эту ячейку с формулой и перетащите её маркер заполнения вниз — так вы быстро получите остальные результаты.

=B2*CHOOSE(MATCH(B2, $E$2:$E$4, 1), 10%, 15%, 20%)

В заключение, владение вложенными операторами IF в Excel — ценный навык, который значительно расширяет ваши возможности при обработке сложных логических сценариев в анализе данных и принятии решений. Хотя вложенные конструкции IF отлично справляются со сложной логикой, важно помнить об их ограничениях. В определённых ситуациях более простые альтернативы — такие как VLOOKUP, IFS или комбинация CHOOSE с MATCH — позволяют создавать более компактные и читаемые решения. Вооружившись этими знаниями, вы теперь можете уверенно выбирать наиболее подходящие методы Excel для своих задач в области анализа данных, обеспечивая ясность, точность и эффективность ваших таблиц. Если вы стремитесь глубже освоить возможности Excel, на нашем сайте вас ждёт множество полезных обучающих материалов.Узнайте больше советов и приёмов работы в Excel здесь.

Связанные статьи