Мы с вами рассматривали примеры, когда надо было проверять несколько логических условий. Для этого мы применяли несколько вложенных друг в друга функций ЕСЛИ, либо использовали функцию ЕСЛИМН. Но в обоих случаях отдельно указывалось о некоторых недостатках этих методов.
И вот Microsoft начиная с версии Excel 2019 предложила функцию ПЕРЕКЛЮЧ, которая превосходит в удобстве обе предыдущие функции.
Функция ПЕРЕКЛЮЧ проверяет выражение на соответствие списку значений и возвращает результат для первого совпавшего значения.
Синтаксис функции ПЕРЕКЛЮЧ:
=ПЕРЕКЛЮЧ(выражение; значение1; результат1; [по_умолчанию_или_значение2]; [результат2]; ...)
- Выражение: Выражение, которое проверяется на соответствие значениям. Это может быть какое-то конкретное значение, ссылка на ячейку или формула. Это значение сравнивается со списком значений: значение1, значение2 и так далее.
- Значение1: Первое значение, сравниваемое с выражением.
- Результат1: Результат, который будет возвращен, если значение1 соответствует выражению. Это может быть значение, диапазон или формула.
- [По умолчанию или значение2]: Второе значение для сравнения с выражением или значение по умолчанию, возвращаемое в случае отсутствия совпадений.
- [Результат2]: Результат, который будет возвращен, если значение2 соответствует выражению.
Таким образом аргументы можно продолжать далее и далее: [значение_по_умолчанию_или_значение3], [результат3] и пр. Всего функция ПЕРЕКЛЮЧ может проверить 126 значений.
В чём преимущество ПЕРЕКЛЮЧ перед вложенными ЕСЛИ или ЕСЛИМН?
- Её структура лаконична. В ней нет повторения скобок и на выражение надо ссылаться только один раз.
- Она предоставляет возможность ввести значение по умолчанию, которое возвращается, если не находится подходящее значение. У функции ЕСЛИМН нет такой возможности.
Нет ничего лучше, что закрепит теорию, чем практика. Поэтому давайте перейдём к примерам.
Пример 1. Поиск по списку текстовых значений
Вспомните пример, который мы разбирали в уроке про вложенные ЕСЛИ. У нас есть список клиентов с четырьмя уровнями членства. Каждый уровень имеет разный размер скидки: Платиновый - 20%, Золотой - 15%, Серебряный - 10% и Бронзовый - 5%. Нужно применить к каждому клиенту скидку в соответствии с его членством. Посмотрите, как мы решили эту задачу с вложенными ЕСЛИ. Теперь нам нужно сделать то же самое с помощью ПЕРЕКЛЮЧ.
Пишем в ячейку D2 формулу:
=ПЕРЕКЛЮЧ(B2;
"Платиновый";C2*(1-20%);
"Золотой";C2*(1-15%);
"Серебряный";C2*(1-10%);
C2*(1-5%))
Формула проверяет выражение в ячейке В1 на значение "Платиновый" и если оно соответствует, то возвращает уменьшенное на 20% значение ячейки C2; иначе, если не соответствует, то проверяет на значение "Золотой" и если оно соответствует, то возвращает уменьшенное на 15% значение ячейки C2; иначе, если не соответствует, то проверяет на значение "Серебряный" и если оно соответствует, то возвращает уменьшенное на 10% значение ячейки C2; иначе, если не соответствует, то возвращает уменьшенное на 5% значение ячейки C2.
Как видите, для последнего аргумента мы используем значение по умолчанию, и не проверяем лишний раз ячейку B1 на соответствие значению "Бронзовый". Если выражение не соответствует "Платиновому", "Золотому" и "Серебряному", то можно считать, что оно автоматически является "Бронзовым" и мы применяем для всех этих случаев скидку 5%.
Но если бы мы не предоставили в последнем аргументе значение по умолчанию, а при проверке оказалось бы, что выражение не соответствует ни одному значению, то формула вернула бы ошибку #Н/Д.
Сравните, вот формула с использованием вложенных если для решения этой же задачи:
=ЕСЛИ(B2="Платиновый";C2*(1-20%);
ЕСЛИ(B2="Золотой";C2*(1-15%);
ЕСЛИ(B2="Серебряный";C2*(1-10%);
C2*(1-5%))))
Преимущество ПЕРЕКЛЮЧ в том, что нет необходимости многократного повторения "ЕСЛИ(B2=" и использования дополнительных закрывающих скобок в конце формулы. Зачастую, в сложных формулах поиск пропущенной скобки превращается в "увлекательный" квест.
А вот как выглядела бы формула, если бы мы использовали функцию ЕСЛИМН:
=ЕСЛИМН(B2="Платиновый";C2*(1-20%);
B2="Золотой";C2*(1-15%);
B2="Серебряный";C2*(1-10%);
B2="Бронзовый";C2*(1-5%))
В ЕСЛИМН нет значения по умолчанию или аргумента значение если ложь, поэтому для учета этой скидки нам пришлось добавить проверку B2="Бронзовый". Эта функция позволяет избежать многократного повторения ЕСЛИ, но многократно проверять "B2=" всё равно приходится.
Таким образом, функция ПЕРЕКЛЮЧ при проверке нескольких значений более аккуратную и лаконичную формулу.
Рассмотрим ещё один пример.
Пример 2. Соответствие ИСТИНЕ
В предыдущем примере мы рассматривали типичное использование функции ПЕРЕКЛЮЧ: проверка точного совпадения между выражением и списком значений.
Но есть одна неочевидный приём, который может расширить возможность применения ПЕРЕКЛЮЧ. Мы можем в качестве выражения просто написать ИСТИНА. Что нам это даст? Да то, что теперь мы сможем в значениях использовать логические операторы, такие как >, <, <= и <=. Теперь мы не ограничены только точными совпадениями выражения и значения!
Сразу же врываемся в пример. Вспомните пример из урока про ЕСЛИМН. Есть список абитуриентов с баллами за экзамен. Нам надо разбить их на группы, в зависимости от балла: 90 и больше баллов - группа А, от 80 до 90 - группа B, от 70 до 80 - группа C, 60-70 - группа D и меньше 60 баллов - группа E.
Нам надо проверить несколько логических утверждений: количество баллов >= 90, или >= 80 и т.д. Так как это логические утверждения, то ответом на них могут быть ИСТИНА или ЛОЖЬ. И если в ПЕРЕКЛЮЧ мы в выражение напишем ИСТИНА, то каждое логическое выражение будет проверяться на соответствие ей.
=ПЕРЕКЛЮЧ(ИСТИНА;B2>=90;"A";B2>=80;"B";B2>=70;"C";"D")
Если утверждение, что значение ячейки B2 больше или равно 90 истинно, то пишем "А", иначе если утверждение, что значение ячейки B2 больше или равно 80 истинно, то пишем "B", иначе если утверждение, что значение ячейки B2 больше или равно 70 истинно, то пишем "C", иначе пишем "D".
Главное помнить, что важен порядок значений, так как функция ПЕРЕКЛЮЧ возвращает результат для первого встретившегося ей истинного значения. Если вы перепутаете порядок значений, то на выходе получите некорректные результаты.
Файл с сегодняшними примерами вы можете скачать по ссылке. Вопросы задавайте в комментариях. В следующий раз мы продолжим рассматривать возможности функции ПЕРЕКЛЮЧ.
Не переключайтесь;)
