Как сделать переключатель в Excel

Обновлено: 04.07.2024

Функция ПЕРЕКЛЮЧАТЕЛЬ относится к категории логических функций Excel. Список функций включает наиболее важные функции Excel для финансовых аналитиков. Эта шпаргалка охватывает сотни функций, которые важно знать аналитику Excel. Эта функция оценит заданное выражение (или значение) по списку значений и вернет результат, соответствующий первому совпадающему значению. В случае отсутствия подходящего значения будет возвращено необязательное значение по умолчанию.

SWITCH был добавлен в MS Excel 2016 и недоступен в более ранних версиях. Однако ранее эта функция была доступна в VBA. Функцию ПЕРЕКЛЮЧАТЕЛЬ можно использовать вместо вложенных функций ЕСЛИ.

Формула

=SWITCH(выражение, значение1, результат1, [значение по умолчанию или значение2, результат2],…[значение по умолчанию или значение3, результат3])

Функция ПЕРЕКЛЮЧАТЕЛЬ использует следующие аргументы:

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

< /таблица>

Value1 ………value126 (обязательный аргумент) — это значение, которое будет сравниваться с выражением.
Result1 ………result126 — значение, которое будет возвращено, когда соответствующий аргумент valueN совпадет с выражением.
По умолчанию (необязательный аргумент). Это значение, которое функция должна вернуть, если в выражении valueN не найдено совпадений.

Как использовать функцию ПЕРЕКЛЮЧЕНИЕ в Excel?

Давайте рассмотрим несколько примеров, чтобы понять, как работает функция ПЕРЕКЛЮЧЕНИЕ:

Пример 1

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

Формула будет =SWITCH(A6, "DR", "Должники", "CR", "Кредиторы", "JE", "Запись в журнале", "LB", "Главная книга", "неизвестно") .

Мы получаем следующий результат:

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

Самое лучшее в функции ПЕРЕКЛЮЧАТЕЛЬ заключается в том, что нам не нужно повторять выражение несколько раз, что иногда происходит во вложенных формулах ЕСЛИ.

Теперь, если бы нам нужно было использовать функцию ЕСЛИ, формула была бы такой:

=IF(B5="DR","Должники",IF(A2="CR","Кредиторы",IF(B5="JE","Запись в журнале",IF(B5="LB")," Бухгалтерская книга», «неизвестно»))))

Пример 2. Использование SWITCH с другими функциями

Предположим, нам дано несколько дат, и мы хотим узнать, относятся ли они к сегодняшнему дню, завтрашнему дню или вчерашнему дню. Используя вместе функции ПЕРЕКЛЮЧАТЕЛЬ, ДНИ и СЕГОДНЯ, мы можем добиться желаемых результатов.

Функция СЕГОДНЯ вернет порядковый номер текущей даты, а функция ДНЕЙ вернет количество дней между двумя датами.

Используемая формула =SWITCH(DAYS(TODAY(),B5), 0, «Текущая дата», 1, «Вчера», -1, «Завтра», «Неизвестно»).

Пример 3

В некоторых странах финансовый год начинается с даты, отличной от 1 января, поэтому им часто приходится классифицировать даты по финансовым кварталам. Например, если апрель-март — финансовый год, то апрель-июнь будет 1-м кварталом и так далее. Мы можем использовать SWITCH в таких сценариях:

Мы получаем следующие результаты:

Здесь мы использовали формулу для поиска номера месяца в столбце "Даты": если это месяц 1, это квартал 4; если это месяц 2, это квартал 4; если это месяц 3, это квартал 4; если месяц 4, то квартал 1; и так далее.

Несколько замечаний о функции SWITCH

Дополнительные ресурсы

Спасибо, что прочитали руководство CFI по функции ПЕРЕКЛЮЧЕНИЕ в Excel. Потратив время на изучение и освоение функций Excel, вы значительно ускорите свой финансовый анализ. Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:

Функции Excel для финансов Excel для финансов В этом руководстве по Excel для финансов представлены 10 основных формул и функций, которые необходимо знать, чтобы стать отличным финансовым аналитиком в Excel.
Усовершенствованные формулы Excel, которые необходимо знать Усовершенствованные формулы Excel, которые необходимо знать Эти расширенные формулы Excel крайне важны для понимания и выведут ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel!
Сочетания клавиш Excel для ПК и Mac Ярлыки Excel для ПК Mac Сочетания клавиш Excel — список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, работу с данными, редактирование формул и ячеек и другие сочетания клавиш.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel — формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

Функция ПЕРЕКЛЮЧАТЕЛЬ Excel сравнивает одно значение со списком значений и возвращает результат, соответствующий первому найденному совпадению. Если совпадений не найдено, SWITCH может вернуть необязательное значение по умолчанию.

выражение — значение или выражение для сопоставления.
значение1/результат1 – первая пара значение-результат.
значение2/результат2 – [необязательно] вторая пара значения и результата.
по умолчанию – [необязательно] значение по умолчанию, используемое, если совпадений не найдено.

Функция ПЕРЕКЛЮЧАТЕЛЬ сравнивает одно значение со списком значений и возвращает результат, соответствующий первому найденному совпадению. Вы можете использовать функцию SWITCH, когда хотите выполнить «автономный» поиск точного совпадения с несколькими возможными результатами. Если совпадений не найдено, SWITCH может вернуть необязательное значение по умолчанию.

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

В показанном примере формула в D5 выглядит следующим образом:

SWITCH выполняет только точное совпадение, поэтому вы не можете использовать логические операторы, такие как больше (>), меньше ( ) или меньше ( 70, "Удачно", "Не пройдено"). С помощью вложенных функций ЕСЛИ можно проверить более одного условия. ЕСЛИ.

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

Функция ВЫБОР Excel возвращает значение из списка, используя заданную позицию или индекс. Например, =ВЫБОР(2,"красный","синий","зеленый") возвращает "синий", поскольку синий является вторым значением в списке после порядкового номера. Значения, предоставленные CHOOSE, могут.

ВПР – это функция Excel для поиска данных в таблице, организованной по вертикали. Функция ВПР поддерживает приблизительное и точное совпадение, а также подстановочные знаки (* ?) для частичного совпадения. Значения поиска должны отображаться сначала.

ПОИСКПОЗ — это функция Excel, используемая для поиска положения искомого значения в строке, столбце или таблице. ПОИСКПОЗ поддерживает приблизительное и точное совпадение, а также подстановочные знаки (* ?) для частичного совпадения. Часто ПОИСКПОЗ сочетается.

В этом учебном пособии по Excel объясняется, как использовать функцию ПЕРЕКЛЮЧЕНИЕ в Excel с синтаксисом и примерами.

Описание

Функция SWITCH Microsoft Excel сравнивает выражение со списком значений и возвращает соответствующий результат.

Функция ПЕРЕКЛЮЧЕНИЕ – это встроенная в Excel функция, относящаяся к категории логическая функция. Его можно использовать как функцию рабочего листа (WS) в Excel. В качестве функции рабочего листа функцию ПЕРЕКЛЮЧАТЕЛЬ можно ввести как часть формулы в ячейке рабочего листа.

Пожалуйста, ознакомьтесь с нашей страницей функции SWITCH (VBA), если вы ищете версию VBA функции SWITCH, так как она имеет совершенно другой синтаксис.

Если вы хотите следовать этому руководству, загрузите пример электронной таблицы.

Синтаксис

Синтаксис функции ПЕРЕКЛЮЧАТЕЛЬ в Microsoft Excel:

Параметры или аргументы

выражение Выражение для сравнения со списком значений. значение1, значение2, . value_n Список значений, которые сравниваются с выражением. Функция ПЕРЕКЛЮЧАТЕЛЬ ищет первое значение, соответствующее выражению. результат1, результат2, . result_n Список результатов. Функция ПЕРЕКЛЮЧАТЕЛЬ возвращает соответствующий результат, когда значение соответствует выражению. по умолчанию Необязательный. По умолчанию возвращается, если выражение не соответствует ни одному из значений (значение1, значение2, . < em>значение_n).

Возврат

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

Применимо к

Тип функции

Пример (как функция рабочего листа)

Давайте рассмотрим несколько примеров функции ПЕРЕКЛЮЧЕНИЕ в Excel и узнаем, как использовать функцию ПЕРЕКЛЮЧЕНИЕ в качестве функции рабочего листа в Microsoft Excel:

Исходя из приведенной выше электронной таблицы Excel, будут возвращены следующие примеры SWITCH:

Еще примеры

Вот еще примеры, показывающие, как использовать функцию ПЕРЕКЛЮЧАТЕЛЬ в Excel:

Функция ПЕРЕКЛЮЧАТЕЛЬ в MS Excel — это логическая функция, используемая для сравнения одного значения (или результата выражения) со списком значений и возвращающая первое точное совпадение, найденное в результате.

Если соответствующее значение не найдено, функция ПЕРЕКЛЮЧАТЕЛЬ может вернуть необязательное значение по умолчанию. Функция ПЕРЕКЛЮЧЕНИЕ доступна только в Excel 2019 или более поздних версиях.

Оглавление

Синтаксис

Синтаксис функции ПЕРЕКЛЮЧЕНИЕ в Excel следующий:

Аргументы:

выражение — здесь приводится выражение, которое необходимо сравнить со списком значений. Это выражение может быть константой, ссылкой на ячейку, результатом другой формулы или простым логическим значением, таким как ИСТИНА или ЛОЖЬ.
значение1 — здесь указывается первое значение, с которым должно сравниваться выражение.
result1 — результат, который должен быть возвращен, когда выражение соответствует первому значению.
значение2 — второе значение, с которым должно сравниваться выражение, если первое совпадение оказывается ложным. Это необязательный параметр.
result2 — результат, который должен быть возвращен, когда выражение соответствует второму значению.
по умолчанию — необязательный параметр. Значение по умолчанию возвращается, если во время сравнения не найдено точного совпадения.

Важные характеристики функции SWITCH в Excel

Давайте посмотрим на некоторые примеры функции ПЕРЕКЛЮЧЕНИЕ.

Пример 1. Простая ванильная версия функции SWITCH

В этом примере у нас есть Данные об учащихся, которые включают «Имена учащихся» и «Коды учащихся». Наша цель — заполнить значения в столбце «Тип учащегося».

Логика заключается в том, что все учащиеся со значением "Код учащегося", равным 1, относятся к категории "Международные", а учащиеся со значением "Код учащегося" 2 относятся к категории "Местные".

Чтобы заполнить значения «Тип учащегося», мы можем использовать формулу –

Попробуем разобрать приведенную выше формулу –

Здесь параметр 'выражение', переданный функции SWITCH, имеет значение B2. B2 — это ссылка на ячейку, которая содержит значение «1».

"1" передается функции как параметр "value1", а "International" передается как параметр "result1". По сути, это означает, что когда переданное 'выражение' равно 1, результат должен быть 'International'.

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

Таким образом, для первого учащегося "выражение" равно 1, поэтому результатом является "Международный". В то время как для второго учащегося 'выражение' равно 2, и, следовательно, результатом является 'Домашний'. Таким же образом тип ученика создается для всех учеников.

Пример 2. Функция SWITCH со значением по умолчанию

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

Чтобы предотвратить эту ошибку, мы присваиваем значение по умолчанию в функции для выражений, которые не соответствуют указанным значениям. В этом случае мы добавим «Недопустимый тип» в качестве аргумента «по умолчанию».

Итак, формула примет вид:

Пример 3. Функция SWITCH с логическими операторами

Мы знаем, что функцию ПЕРЕКЛЮЧЕНИЕ можно использовать только для точного соответствия.

Что, если я скажу вам, что для этого есть обходной путь?

Да, с разумным использованием логических операторов (например, больше (>) или меньше ( = ПЕРЕКЛЮЧЕНИЕ (ИСТИНА, B2 >= 91, "Уровень A", B2 >= 81, "Уровень B", B2 >= 71, "Класс С", B2 71, "Класс D", "Недействительный")

Теперь давайте попробуем разобрать эту формулу и попытаться понять, что происходит внутри.

В приведенной выше формуле выражение имеет значение TRUE. После этого для параметра values выражение в ячейке B2 сравнивается с помощью логических операторов. Поскольку каждый параметр значения может принимать значение ИСТИНА или ЛОЖЬ, и функция ПЕРЕКЛЮЧЕНИЕ вернет результат, соответствующий первому значению, которое окажется ИСТИНА.

Например, в первом случае значение выражения "ИСТИНА" сопоставляется со следующим.

B2 >= 91 . Значение в B2 равно 75, поэтому выражение еще больше упрощается до 75 >= 91 , что оценивается как FALSE. Следовательно, совпадения в этом случае не произойдет.
Далее B2 сравнивается с 81, например B2 >= 81 (или 75 >= 81 ). Это снова оказывается ЛОЖЬЮ. Следовательно, и в этом случае совпадения не произойдет.
После этого B2 сравнивается с 71, например B2 >= 71 (или 75 >= 71 ). Это условие оценивается как ИСТИНА, и, следовательно, определяется совпадение, и в результате возвращается соответствующее значение, т. е. "Уровень C".

Формула, которую мы использовали выше, верна, но есть небольшая ошибка.

Если вы внимательно посмотрите на строку 7, то увидите, что, несмотря на то, что значение в ячейке B7 равно 124, а мы указали значение по умолчанию как «Недействительное», функция ПЕРЕКЛЮЧАТЕЛЬ возвращает результат «Уровень А», который кажется быть неверным.

Почему это происходит?

Мы очень хорошо знаем, что функция ПЕРЕКЛЮЧЕНИЕ возвращает значение первого найденного точного совпадения и не проверяет дальнейшие совпадения. Это потому, что 124 больше, чем 91. Мы объясним это, пробежавшись по мозгу функции.

Функция вычисляет значение в ячейке B7, т. е. 124, по первому логическому выражению — B2 >= 91 . Он находит, что 124 больше, чем 91, и в результате возвращает «Класс А». Он не позволил обработать 124 до конца функции, потому что уже нашел первое точное совпадение.

Ой! Теперь давайте проверим, решена ли эта проблема, когда мы указываем условие – B2 >100 , «Неверно» в качестве первого проверочного значения в формуле.

Формула принимает следующий вид:

= SWITCH (ИСТИНА, B2 > 100, "Неверно", B2 >= 91, "Класс A", B2 >= 81, "Класс B", B2 >= 71, "Класс C", B2 71," Оценка Д")

Это сработало! А как?

Помните, что функция ПЕРЕКЛЮЧЕНИЕ всегда возвращает результат ПЕРВОГО найденного точного совпадения. Поместив значение по умолчанию первым в формулу, мы позволяем сначала отлавливать недопустимые значения.

ПЕРЕКЛЮЧЕНИЕ И ФУНКЦИЯ IFS

Функции SWITCH и IFS помогают проверять набор условий и возвращать результат на основе заданного выражения. Они оценивают несколько условий и служат простой и быстрой альтернативой вложенным операторам IF.

Небольшое преимущество SWITCH над IFS заключается в том, что выражение или условие, которые нужно проверить, появляются в функции только один раз и не должны повторяться.

Однако существенное различие между функциями ЕСЛИ и ПЕРЕКЛЮЧЕНИЕ заключается в том, что при стандартном синтаксисе функция ПЕРЕКЛЮЧЕНИЕ может вычислять только точное соответствие выражению, указанному в формуле.

В SWITCH нельзя использовать такие логические операторы, как больше (>) или меньше ( 15 000+ членов семейства!

Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.

Читайте также:

Тип выражения	Пример
Ссылка на ячейку	A2
Логический тест	A2=“PASS”
Число или текст< /td>	2 или «FAIL»
Именованный диапазон	Именованный_диапазон
Boolean значения	ИСТИНА или ЛОЖЬ
Математическое выражение	A2+1