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

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

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

• Использование ВПР
• Использование ИФС
• Использование ВЫБЕРИТЕ и СООТВЕТСТВУЙТЕ

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

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

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

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

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

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

Синтаксис:

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

аргументы:

• Condition1, Condition2, Condition3: These are the conditions you want to test. Each condition is evaluated in order, starting with Condition1.
• Result1: This is the value returned if Condition1 is TRUE.
• Result2: This value is returned if Condition1 is FALSE and Condition2 is TRUE. It's important to note that Result2 is only evaluated if Condition1 is FALSE.
• Result3: This value is returned if both Condition1 and Condition2 are FALSE, and Condition3 is TRUE. Essentially, for Result3 to be evaluated, the previous conditions (Condition1 and Condition2) must both be FALSE.
• Result4: This result is returned if all the conditions (Condition1, Condition2, and Condition3) are FALSE.
  
  In short, this expression can be interpreted as follows:
  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

Помните, что во вложенной структуре IF каждое последующее условие оценивается только в том случае, если все предыдущие условия имеют значение FALSE. Эта последовательная проверка имеет решающее значение для понимания того, как работают вложенные 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))

Вложенный, если с условием И/ИЛИ

В этом разделе я модифицирую приведенный выше первый пример «системы оценок», чтобы продемонстрировать, как объединить вложенное ЕСЛИ с условием И или ИЛИ в 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 с условием ИЛИ

В этом случае оценка учащегося будет повышена на один уровень, если его балл составит 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

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

Создание вложенных IF, которые легко читать

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

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

=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 для «А»), а затем переходит к более низким пороговым значениям. Это гарантирует, что оценка сравнивается с наивысшей оценкой, на которую она соответствует. Если первое условие истинно (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 в Excel. Часто объединение вложенных IF с другими функциями или поиск альтернативных подходов может привести к более эффективным и удобным в сопровождении решениям.

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

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

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

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

Пример 1: Система оценок с помощью VLOOKUP

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

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

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

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

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

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

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

Вы также можете использовать VLOOKUP для расчета комиссии с продаж в Excel. Пожалуйста, сделайте следующее.

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

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

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

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

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

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

Ассоциация функция ЕСЛИ упрощает процесс, устраняя необходимость вложенности и упрощая чтение формул и управление ими. Это повышает читаемость и упрощает обработку нескольких условных проверок. Чтобы использовать функцию 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. Этот метод упрощает формулу и делает обновления или изменения более простыми. Ниже я покажу, как использовать комбинацию функций ВЫБОР и ПОИСКПОЗ для обработки двух практических примеров в этой статье.

Пример 1. Система оценок с использованием ВЫБЕРИТЕ и СООТВЕТСТВУЙТЕ.

Вы можете использовать комбинацию функций ВЫБОР и СООТВЕТСТВИЕ, чтобы выставлять оценки на основе различных баллов.

Шаг 1. Создайте массив поиска со значениями поиска.

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

Шаг 2. Примените ВЫБЕРИТЕ и СООТВЕТСТВУЙТЕ, чтобы выставить оценки.

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

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

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

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

Шаг 1. Создайте массив поиска со значениями поиска.

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

Шаг 2. Примените ВЫБЕРИТЕ и СООТВЕТСТВУЙТЕ, чтобы получить результаты.

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

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

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

Статьи по теме