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

В Excel функция IF необходима для выполнения базовых логических проверок, однако для работы со сложными условиями часто требуется использовать вложенные операторы IF, чтобы расширить возможности обработки данных. В этом подробном руководстве мы рассмотрим основы вложенных IF: от синтаксиса до практического применения, включая сочетание вложенных IF с условиями AND/OR. Кроме того, вы узнаете, как повысить читаемость вложенных IF, получите полезные советы по их использованию и познакомитесь с мощными альтернативами, такими как VLOOKUP, IFS и другими инструментами, которые делают работу со сложной логикой проще и эффективнее.

• Синтаксис вложенных IF
• Практические примеры вложенных IF
• Вложенный IF с условиями AND / OR

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

• Использование VLOOKUP
• Использование IFS
• Использование CHOOSE и MATCH

Сравнение функции IF и вложенных операторов IF в Excel

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

Использование вложенных IF

В этом разделе показано базовое применение вложенных IF в Excel: синтаксис, практические примеры и использование с условиями AND или OR.

Синтаксис вложенных IF

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

Синтаксис:

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

Аргументы:

• Condition1, Condition2, Condition3: Это условия, которые вы хотите проверить. Каждое условие оценивается по порядку, начиная с Condition1.
• Result1: Значение, возвращаемое если Condition1 истинно.
• Result2: Это значение возвращается, если Condition1 ложно, а Condition2 истинно. Важно: Result2 вычисляется только если Condition1 ложно.
• Result3: Это значение возвращается, если и Condition1, и Condition2 ложны, а Condition3 истинно. То есть для вычисления Result3 предыдущие условия должны быть ложными.
• Result4: Это результат, который возвращается, если все условия (Condition1, Condition2 и Condition3) ложны.
  
  Вкратце, это выражение можно интерпретировать так:
  Проверить condition1, если TRUE, вернуть result1, если FALSE,
  проверить condition2, если TRUE, вернуть result2, если FALSE,
  проверить condition3, если TRUE, вернуть result3, если FALSE,
  вернуть result4

Помните: в структуре вложенных IF каждое следующее условие проверяется только если все предыдущие условия оказались ложными. Последовательная проверка — ключ к пониманию работы вложенных IF.

Практические примеры вложенных IF

Теперь рассмотрим применение вложенных IF на двух практических примерах.

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

Как показано на скриншоте ниже, предположим, у вас есть список баллов студентов, и вы хотите присвоить оценки на их основе. Для этого можно использовать вложенные IF.

Выделите пустую ячейку (например, 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: Расчет комиссии с продаж

Представьте ситуацию, когда торговые представители получают разные ставки комиссии в зависимости от объема продаж. Как показано на скриншоте ниже, вы хотите рассчитать комиссию сотрудника на основе разных порогов продаж, и вложенные IF помогут вам в этом.

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

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

Вложенный IF с условиями AND / OR

В этом разделе я модифицирую первый пример "система оценивания", чтобы показать, как сочетать вложенные IF с условиями AND или OR в Excel. В обновленном примере добавлено дополнительное условие — "Посещаемость".

Использование вложенного IF с условием AND

Если студент соответствует и критерию по баллам, и по посещаемости, его оценка повышается на один уровень. Например, если балл60 и выше, а посещаемость95% и более, оценка увеличивается: A становится A+, B — B+ и так далее. Если посещаемость ниже95%, оценка выставляется только по баллам. В таких случаях нужно использовать вложенный IF с условием AND.

Выделите пустую ячейку (например, 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")))))

Использование вложенного IF с условием OR

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

Выделите пустую ячейку (например, 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

В этом разделе представлены четыре полезных совета и хитрости для работы с вложенными IF.

Как сделать вложенные IF более читаемыми

Обычный вложенный IF может выглядеть компактно, но его сложно быстро понять.

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

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

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

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

После разбиения формула будет выглядеть так:

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

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

Порядок выполнения вложенных функций IF

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

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

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

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

Excel последовательно проверяет условия во вложенной формуле IF — от первого к последнему. Формула сначала проверяет наивысший порог (>=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".

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

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

Числа

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

Текст

Вложенные IF требуют, чтобы текстовые значения были заключены в двойные кавычки. Например, A, B, C, D и F в следующей формуле:

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

Ограничения вложенных IF

В этом разделе перечислены основные ограничения и недостатки вложенных IF.

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

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

Склонность к ошибкам:

Кроме того, сложные вложенные IF часто становятся источником ошибок и их трудно отлаживать или изменять.

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

Если логика меняется или нужно добавить условия, глубоко вложенные IF сложно модифицировать или расширять.

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

Альтернативы вложенным IF

В этом разделе перечислены функции Excel, которые могут служить альтернативой вложенным IF.

Использование VLOOKUP

Вместо вложенных IF вы можете использовать функцию VLOOKUP для решения рассмотренных выше практических задач. Вот как это делается:

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

Здесь показано, как использовать VLOOKUP для присвоения оценок по баллам.

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

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

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

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

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

Пример2: Расчет комиссии с помощью VLOOKUP

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

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

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

Шаг2: Примените функцию VLOOKUP для расчета комиссии

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

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

Использование IFS

Функция IFS упрощает процесс, устраняя необходимость во вложенности и делая формулы более читаемыми и удобными для поддержки. Она повышает наглядность и облегчает обработку нескольких условий. Для использования функции IFS убедитесь, что у вас Excel2019 или новее, либо подписка Office365. Рассмотрим применение IFS на практических примерах.

Пример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

Комбинация 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

Использование сочетания CHOOSE и MATCH для расчета комиссии с продаж также может быть эффективным, особенно если ставки зависят от заданных порогов. Рассмотрим, как это реализовать.

Шаг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 вы найдете здесь.

Похожие статьи