Большинство из нас никогда не подвергает сомнению функцию ЕСЛИ. Это работает, это знакомо, и для всего есть формула, если вы готовы собрать их достаточно. Но функционирование и хорошее функционирование — это две разные вещи, и ПЧ начинает давать сбои, когда в вашей логике больше двух или трёх условий.
Резьбовая планка становится стенкой кронштейна; Одна неправильная скобка ломает все дело, и его трудно отладить. Функция ПЕРЕКЛЮЧАТЕЛЬ обрабатывает ту же логику в части символов – и как только вы к ней привыкнете, возвращение к вложенным формулам ЕСЛИ покажется ненужным.
Используйте команды TAKE и DROP в Excel для создания потрясающих самообновляющихся списков.
Вам не нужно делать это вручную.
Switch — это функция, которую вы должны были использовать
Это работает так же, как вложенный ЕСЛИ, но без беспорядка.
SWITCH вычисляет одно выражение, сопоставляет его со списком значений и возвращает результат первого найденного совпадения. Это одна из тех условных функций, которые делают мой лист Excel умнее. Синтаксис:
=SWITCH(expression, value1, result1, value2, result2, ..., [default])
- Выражение: Значение, которое вы тестируете. Обычно это ссылка на ячейку.
- цена: С чем вы сопоставляете это выражение?
- Результат: Что возвращает SWITCH, когда найдено совпадение.
- По умолчанию (необязательно): Что он возвращает, если ничего не соответствует — всегда стоит включить.
Если IF заставляет вас перебирать условие на каждом уровне, SWITCH оценивает выражение один раз и обрабатывает список. Это небольшая разница, которая быстро накапливается, когда вы манипулируете пятью или шестью ситуациями.
Возьмем, к примеру, калькулятор оценок. Используя вложенное ЕСЛИ, вы пишете формулу, которая многократно повторяется. С помощью Switch вы перечисляете оценки по порядку и двигаетесь дальше. Формула остается плоской, а плоские формулы не лгут вам.
Switch справляется с реальными сценариями лучше, чем IF
Вот как это выглядит на реальных данных
Данные о продажах являются хорошим примером того, где SWITCH занимает свое место. Столбец «Регион» имеет четыре значения: Север, Юг, Восток и Запад. Предположим, вам нужно назначить регионального менеджера для каждой записи. При использовании вложенного IF вы уже пишете четыре уровня глубины. Для переключателей формула представляет собой чистую линию:
=SWITCH(B2, "North", "John", "South", "Sarah", "East", "Mike", "West", "Lisa", "Unassigned")
Аргумент «неназначенный» используется по умолчанию и охватывает все несовпадающие поля. Вложенный IF требует для этого дополнительного условия. Switch, наконец, исправляет это.
Та же логика применима к ставкам комиссий, меткам статуса или любому столбцу, в котором определенный набор значений соответствует определенному набору результатов. Добавление пятого поля означает добавление пары значение-результат — больше в формуле ничего не меняется. Используя вложенный IF, вы проводите рефакторинг структуры, которую изначально было трудно читать.
Есть некоторые вещи, которые нельзя переключить
Если ваши условия связаны с диапазонами, лучше использовать IFS.
Столбец «Маржа прибыли» является наглядным примером того, где переключатель достигает своих пределов. В столбце каждая продажа классифицируется как «высокая», «средняя» или «низкая» на основе рассчитанных пределов маржи, и SWITCH не может этого сделать. Он соответствует точным значениям, а не условиям. В тот момент, когда ваш аргумент включает в себя «больше», «меньше» или «между», вам понадобится функция IFS.
IFS оценивает ряд логических условий по порядку и возвращает результат, если первое из них истинно:
=IFS(margin>0.3, "High", margin>0.15, "Medium", margin<=0.15, "Low")
Он не требует вложения или оборачивания одной функции внутри другой, и каждая ситуация находится на одном уровне. Это похоже на дерево решений, написанное простым языком.
Главное, что нужно правильно сделать с IFS, — это порядок условий. Поскольку он останавливается при первом истинном совпадении, размещение широкой ставки перед узкой ставкой поглотит результаты, которые должны были произойти в другом месте. Итак, начните с конкретного, работайте дальше, и если вы хотите получить все это, повторите последнее условие как гарантированно истинное утверждение.
Переключитесь с IF в существующей электронной таблице.
Не обязательно переписывать все сразу
Столбец комиссий в примере таблицы Excel — разумное место для начала. В настоящее время он использует IF внутри LET для применения множителя бонуса, если комиссия превышает 500 долларов США. Это не нечитабельно, но и не то, насколько чистым может быть пол.
Используйте любую формулу, прежде чем переписывать ее. Ctrl+` (серьезный акцент) Чтобы изложить все сутры вместе. Это упрощает идентификацию вложенных IF с первого взгляда.
Сначала поработайте над копией листа. Измените формулу, убедитесь, что результат соответствует оригиналу, затем двигайтесь дальше. Не заменяйте столбец пакетно, пока не подтвердите логику для крайних случаев, например комиссию в размере 500 долларов США, соответствующую граничным условиям.
Инструмент формулы оценки (с помощью которого вы можете получить доступ к Формула > Оценка формулы) стоит использовать здесь. Он выполняет операции Формулы-1 поочередно, что позволяет легко обнаружить условия, активирующиеся в неправильном порядке.
есть еще кое-что, откуда это взялось
Switch и IFS хороши, но с этими функциями работают еще лучше
Как только вы освоитесь с переключателями и IFS, стоит посмотреть, как они связаны с другими функциями. Например, обертывание SWITCH внутри LET позволяет вам назвать выражение один раз и использовать его повторно, что имеет значение, когда это выражение представляет собой длинную формулу, а не простую ссылку на ячейку. Вы можете создавать формулы быстрого поиска с помощью XLOOKUP, который обрабатывает многие сценарии, с которыми люди обычно справляются с помощью ЕСЛИ, часто более эффективно. Схема та же самая. Новые функции Excel делают больше с меньшими затратами.
