Foreversoft.ru

IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel switch case

SWITCH (функция SWITCH)

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

Примечание: Эта функция доступна в Windows или Mac, если у вас есть Office 2019 или подписка на Office 365. Если вы являетесь подписчиком Office 365, Убедитесь в том, что у вас установлена новейшая версия Office.

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

выражение — это значение (например, число, дата или текст), которое сравнивается со значениями значение1…значение126.

значениеN — это значение, с которым сравнивается выражение.

результатN — это значение, возвращаемое, если аргумент значениеN совпадает с выражением. Значение результатN необходимо задать для каждого аргумента значениеN .

по умолчанию
необязательно

Значение, возвращаемое, если совпадения выражения со значениями значение1. значение126 не обнаружены. Аргумент по_умолчанию можно быстро найти, поскольку для него не задается соответствующее значение результатN (см. примеры). Значение по_умолчанию должно быть последним в функции.

Поскольку у функций не может быть больше 254 аргументов, можно использовать до 126 пар аргументов значениеN и результатN.

Обзор

Самая простая функция ПЕРЕКЛЮЧ имеет такую структуру:

=ПЕРЕКЛЮЧ(значение для переключения;значение, которое должно совпасть1. [2–126];значение, возвращаемое при совпадении1. [2–126];значение, возвращаемое при отсутствии совпадений)

В ней можно вычислить до 126 совпадающих значений и результатов.

    Значение для переключения? В данном случае значение ДЕНЬНЕД(A2) равно 2.

    Какое значение должно совпасть? В данном случае — 1, 2 и 3.

    Какой результат должен быть возвращен при совпадении? В данном случае: «воскресенье» для значения 1, «понедельник» для значения 2 и «вторник» для значения 3.

    Стандартное значение, возвращаемое при отсутствии совпадений. В данном случае — текст «совпадения отсутствуют».

    Примечание: Если совпадающих значений нет и аргумент по умолчанию не указан, функция ПЕРЕКЛЮЧ возвращает ошибку #Н/Д!.

    Примеры

    Вы можете проверить, как работает функция ПЕРЕКЛЮЧ, скопировав образец данных из следующей таблицы и вставив его в ячейку A1 нового листа Excel. Если результаты формул не отображаются, выделите формулы и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

    Поскольку в ячейке A2 указано значение 2 и аргумент «результат», соответствующий значению 2, — это «понедельник», функция ПЕРЕКЛЮЧ возвращает значение «понедельник».

    Поскольку совпадения отсутствуют и аргумент иначе не указан, функция ПЕРЕКЛЮЧ возвращает значение #Н/Д!.

    Excel switch case

    При создании сложных программ один из ключевых моментов — возможность предусмотреть несколько вариантов развития событий. Самый простой и классический пример — оператор «If . Then . Else . End«, который позволяет выбрать одно из двух действий в зависимости от результатов проверки каких-либо значений. Бывает, что в результате такой проверки необходимо выбрать из множества вариантов. Один из выходов: добавить множество «. ElseIf . «, что несколько усложняет синтаксис программы (лёгкость её чтения). Однако это очень мощный оператор, открывающий большие возможности. Подробнее о нём можно узнать здесь.

    Альтернативой оператору «If . End» служит оператор «Select Case» (с английского «Select Case» можно перевести как «Выбор Ситуации»), который упрощает восприятие кода «на глаз». И если «If . End» оператор в каждом своём «ElseIf» вынужден обращаться к проверяемым значениям снова и снова (допустим, выражение каждый раз одинаковое), то «Select Case» делает это только один раз, что позволяет последнему на больших массивах данных работать быстрее. Этот оператор позволяет удобно задать ветвление программы из одной точки в большое количество веток. То есть в основном применяется при множественных условиях проверки, когда проверяемых условий больше двух.

    Структура оператора «Select Case».

    Давайте посмотрим, как выглядит обобщённая структура оператора и разберём, что есть что (разные примеры частного использования кода будут приведены в конце статьи):

    Читать еще:  Линейн в excel

    В качестве куска [Значение] можно вставить любую переменную или свойство, значение которой или которого Вы можете проверить. Можно также проверять значение конкретной ячейки. При этом работать можно не только с числами, но и с текстами. И даже с булевыми значениями TRUE/FALSE («Правда» и «Ложь»), о чем знают не все.

    [Конкретное Значение] — это то, с чем сравнивается [Проверяемое Значение]. И, если одно удовлетворяет другому, то выполняется [Некоторое Действие]. Есть несколько вариантов записи для блока [Конкретное Значение]. Для текстовых и числовых значений можно записывать разные значения через запятую:

    Для чисел можно выбирать диапазоны:

    Также для чисел можно использовать логический оператор сравнения вместе с частицей «Is«:

    Допустимо использовать и логические операторы, что позволит предусматривать сложнейшие случаи и проводить параллельные сравнения с другими переменными. Кроме оператора «Or», который заменяется обычной запятой.

    [Некоторое Действие] может быть абсолютно любым. Если вы его пропускаете — то для данного случая программа будет бездействовать. «Case [Конкретное Значение]» вместе с частью [Некоторое Действие] складываются в один блок:

    Таких блоков может быть любое количество, которое уложится в предельные размеры процедуры (она должна весить не более 64 килобайт). Полезно знать, что VBA просматривает соответствие [Конкретного Значения] и [Проверяемого Значения] вдоль по блокам сверху вниз. То есть, у Вас может быть два блока с одинаковым «Case«, но выполнится только тот, который будет раньше найден программой при просмотре кода сверху вниз.

    Case Else — это все другие случаи, которые не подошли ни под одно другое [Конкретное Значение] во всех блоках оператора «Select Case«. Если блок «Case Else» отсутствует и ни один другой блок не подошёл, то программа делает логичное «ничего». Case Else должен быть последним проверяемым случаем среди всех блоков проверки в операторе. После него других блоков быть не должно, иначе получим синтаксическую ошибку «Case without Select Case«.

    В конце оператора должен стоять «End Select«, который служит «точкой» в «предложении» оператора.

    Примеры использования.

    Рассмотрим несколько примеров использования кода и начнём с самого простого. В первом примере в зависимости от значения Х выводится сообщение.

    Второй пример показывает некоторые виды записи проверяемого значения. В зависимости от количества листов в книге с макросом выводится разное сообщение. Обратите внимание, что если листов в книге 7, то первым сработает “Case 7”, хотя условие “Case 5 to 12” тоже подходит, но стоит позже.

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

    Четвёртый пример показывает, что «Case» может ориентироваться и на другие переменные. В данном случае будем проверять равенство трёх переменных с помощью логического оператора «And»:

    Пятый пример показывает, как через запятую в проверяемом значении для «Case» можно указать целый набор чисел. Допустим, есть некоторая функция и мы проверяем, может ли наше число в этой функции использоваться. По условию, нас устраивают числа в диапазоне от 5 (не включая 5) до минус бесконечности, от 12 до 15 включая концы и от 20 (включая 20) до плюс бесконечности.

    Подводя черту, замечу, что оператор «Select Case» по структуре довольно прост и удобен в использовании. Он менее гибок по сравнению с «If … End», если по ходу проверок требуется менять проверяемое значение, но значительно выигрывает при разнообразных проверках одного и того же выражения. Для чего собственно и был создан.

    Читать еще:  Excel определить цвет ячейки

    Функция ПЕРЕКЛЮЧ для создания переключателя значений в Excel

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

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

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

    1. Данная функция доступна пользователям MS Office версии 2016 и более новых.
    2. Для тех, кто знаком с языками программирования, для лучшего понимания рассматриваемой функции можно использовать ее аналогию с конструкцией switch – case, используемой, например в Java или C#.

    Пример 1. В таблице содержится ячейка со списком, в котором указаны числовые значения (от 1 до 7). Необходимо выводить название дня недели, соответствующее выбранному номеру в списке (например, 1 – понедельник).

    Вид таблицы данных:

    Реализуем задачу с использованием рассматриваемой функции:

    Значение, переданное в качестве первого аргумента (ссылка на ячейку A2 со списком), последовательно сравнивается с последующими аргументами (значение1, значение2…). В случае совпадения будет выведен соответствующий результат.

    Выберем число 3 в списке. Полученное значение:

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

    Создание переключателя между формулами в Excel

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

    Для быстрого переключения между операциями создадим список.

    Вид таблицы данных:

    Для быстрого вывода результатов вычислений в столбец C используем следующую формулу массива:

    Для проверки выберем значение «Умножить» из списка. Полученные результаты:

    Правила использования функции ПЕРЕКЛЮЧ в Excel

    Рассматриваемая функция имеет следующую синтаксическую запись:

    =ПЕРЕКЛЮЧ( выражение; значение1; результат1; [по_умолчанию или значение2; результат2]; …[по_умолчанию или значение3; результат3])

    • выражение – обязательный, принимает ссылку на ячейку с проверяемыми данными или логическое выражение, результат вычисления которого будет сравниваться с последующими значениями;
    • значение1 – обязательный, принимает ссылку на ячейку или значение, с которым будут сравниваться данные, принятые в качестве первого аргумента;
    • результат1 – обязательный, принимает любое значение или ссылку на ячейку, содержащую выводимый результат, если данные, переданные в качестве аргументов выражение и значение1 совпали;
    • [по_умолчанию_или_значение2] – не обязательный, принимает либо данные, сравниваемые с выражение или значение, которое будет выводиться по умолчанию, если необходимо выполнить сравнение только с одним значением;
    • [результат2] – необязательный, принимает значение или ссылку на ячейку с данными, которые будут возвращены, если значения выражение и [по_умолчанию_или_значение2] совпали. Последующие аргументы имеют тот же смысл.
    1. Максимальное число аргументов, принимаемых функцией ПЕРКЛЮЧ, составляет 254. То есть, можно выполнить проверку по списку из 126 значений/результатов.
    2. Если рассматриваемая функция содержит только 3 аргумента, то есть значение по умолчанию явно не задано, в случае если проверяемые значения не совпадают, функция вернет код ошибки #Н/Д. Например, =ПЕРЕКЛЮЧ(3*2;7;”семь”) вернет #Н/Д, поскольку 3*2=6, то есть не совпадает с 7, а значение по умолчанию не введено в виде следующего аргумента.

    Excel switch case

    Наконец-то и до меня дошла волна обновлений и в моем Excel 2016 по подписке Office 365 Pro Plus появились новые функции для работы с текстом и логикой. Давайте я пробегусь по ним на примерах:

    Функция СЦЕП (CONCAT)

    Многим, думаю, известна функция СЦЕПИТЬ (CONCATENATE) , которую можно использовать для склеивания фрагментов текста из нескольких ячеек в одно целое. На практике, часто также используется спецсимвол & для аналогичного действия. Но оба этих способа предполагают указание каждой ячейки с текстовым фрагментом отдельно, что, при большом количестве ячеек, начинает напрягать:

    Читать еще:  Меню правка в excel 2020

    В апрельском обновлении Excel 2016 добавили функцию СЦЕП , которая работает совершенно аналогично, но позволяет задать сразу целый диапазон (даже двумерный), всё содержимое ячеек которого будет склеено в единое целое:

    Функция ОБЪЕДИНИТЬ (TEXTJOIN)

    В предыдущем примере видно, что для красивого объединения мне пришлось предусмотреть отдельные ячейки с символами-разделителями. Новая функция ОБЪЕДИНИТЬ работает аналогично СЦЕП , но автоматически добавляет еще заданный символ между разными фрагментами:

    Также обратите внимание на второй аргумент этой функции — он определяет, нужно ли игнорировать пустые ячейки (ИСТИНА) или нет (ЛОЖЬ).

    В реальной практике такая функция представляется весьма полезной. Например, для склейки адресных строк это будет весьма компактным и красивым решением:

    Функция УСЛОВИЯ (IFS)

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

    Теперь же можно ощутимо упростить формулу, если использовать новую функцию УСЛОВИЯ (IFS) . В её аргументах попарно перечисляются условия и то, что нужно вывести при их выполнении:

    Получается не сильно компактнее, но заметно понятнее, не правда ли?

    Если будете ее использовать, то обратите особое внимание на два важных момента:

    • Функция проверяет условия в порядке их указания и при выполнении какого-либо условия уже не проверяет оставшиеся . Поэтому порядок условий играет роль: в приведенном выше примере проверка нарушений скорости идет от большего к меньшему, а не наоборот.
    • Если нужно задать результат, который должен выводиться, если ни одно из условий не выполнилось (как Else в VBA), то в конце списка условий можно задать условие ИСТИНА. В нашем примере — это сумма штрафа (0), если ни одного из нарушений скорости не было.

    Функция ПЕРЕКЛЮЧ (SWITCH)

    Некий аналог предыдущей функции или конструкции Select Case в языке программирования Visual Basic, если вам знакомы макросы. Сравнивает ячейку с набором заданных значений и выдает один из заданных в наборе результатов. Например:

    Очень похоже на предыдущую функцию УСЛОВИЯ , но в ней условия можно задавать гибко (использовать знаки <>= и т.д.), а здесь проверяется только точное совпадение. Последним аргументом можно задать то значение, которое должно выводиться, если ни одно из предыдущих условий не выполнилось. Раньше, чтобы реализовать что-то подобное приходилось шаманить с функцией ИНДЕКС (INDEX) и массивом констант в фигурных скобках внутри формулы:

    Функции МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)

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

    Как видно, все по аналогии с СУММЕСЛИМН, СЧЁТЕСЛИМН и т.д. — переучиваться не нужно.

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

    И помните о том, что все эти приятные фишки будут работать только у тех, кто установил себе последнюю версию Excel 2016 и регулярно ее обновляет (сейчас это происходит, в основном, автоматически). В противном случае в списке доступных функции вы их не найдете, а другие пользователи увидят ошибку #ИМЯ на ячейках, где вы их использовали.

    Файл со всеми вышеприведенными примерами новых функций можно скачать ниже:

    Ссылка на основную публикацию
    Adblock
    detector