Эксель формула типа case
The SWITCH function evaluates one value (called the expression) against a list of values, and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.
Note: This feature is available on Windows or Mac if you have Office 2019, or if you have a Microsoft 365 subscription. If you are a Microsoft 365 subscriber, make sure you have the latest version of Office.
SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])
Description
expression
(required)
Expression is the value (such as a number, date or some text) that will be compared against value1…value126.
value1…value126
ValueN is a value that will be compared against expression.
result1…result126
ResultN is the value to be returned when the corresponding valueN argument matches expression. ResultN and must be supplied for each corresponding valueN argument.
default
(optional)
Default is the value to return in case no matches are found in the valueN expressions. The Default argument is identified by having no corresponding resultN expression (see examples). Default must be the final argument in the function.
Because functions are limited to 254 arguments, you can use up to 126 pairs of value and result arguments.
Overview
In its simplest form, the SWITCH function says:
=SWITCH(Value to switch, Value to match1. 97, Value to return if there's a match1. 79, Value to return if there's no match)
Where you can evaluate up to 126 matching values and results.
See the following formula:
Value to switch? In this case, WEEKDAY(A2) equals 2.
What value do you want to match? In this case, it's 1, 2 and 3.
If there's a match, what do you want to return as a result? In this case, it would be Sunday for 1, Monday for 2 and Tuesday for 3.
Default value to return if there's no match found. In this case, it's the text "No match".
Examples
You can copy the example data in the following table and paste it in cell A1 of a new Excel worksheet to see the SWITCH function in action. If the formulas don't show results, you can select them, then press F2 > Enter. If you need to, you can adjust the column widths to see all the data.
Because A2=2, and Monday is the result argument corresponding to the value 2, SWITCH returns Monday
у меня интересная задача - мне нужно проверить следующие данные в Excel:
вы должны извинить мое удивительно плохое искусство ASCII. Поэтому мне нужен столбец D (x) для проверки соседних ячеек, а затем при необходимости преобразовать значения. Вот критерии:
если столбец B больше 0, все работает отлично, и я могу получить кофе. Если он не соответствует этому требованию, то мне нужно преобразовать A1 в соответствии с таблицей-для пример, 32 = 1420 и в D . К сожалению, нет никакой связи между A и тем, что ему нужно преобразовать, поэтому о создании вычисления не может быть и речи.
оператор case или switch был бы идеальным в этом сценарии, но я не думаю, что это собственная функция в Excel. Я также думаю, что было бы безумно цеплять кучу =IF() заявления вместе, которые я сделал около четырех раз, прежде чем решил, что это плохая идея (история моей жизни).
звучит как работа для VLOOKUP!
вы можете поместить свои 32 -> 1420 сопоставлений типов в пару столбцов где-нибудь, а затем использовать функцию VLOOKUP для выполнения поиска.
без ссылки на исходную проблему (которая, как я подозреваю, давно решена), я совсем недавно обнаружил аккуратный трюк, который заставляет функцию выбора работать точно так же, как select case оператор без необходимости изменять данные. Есть только одна загвоздка: только одно из ваших условий выбора может быть истинным в любой момент времени.
синтаксис выглядит следующим образом:
в предположении, что только одно из условий от 1 до N будет истинным, все остальное равно 0, что означает числовое значение будет соответствовать соответствующему результату.
если вы не на 100% уверен, что все условия являются взаимоисключающими, вы можете предпочесть что-то вроде:
тем не менее, если Excel имеет верхний предел количества аргументов, которые может принимать функция, вы бы попали в него довольно быстро.
изменить: За комментарий ниже от @aTrusty: Глупые числа запятых можно устранить (и в результате оператор choose будет работать до 254 случаев), используя формулу следующей формы:
обратите внимание на второй аргумент LOG предложение, которое помещает его в базу 2 и заставляет все это работать.
Edit: Per Дэвид, теперь есть фактический оператор коммутатора, если вам посчастливилось работать в office 2016. Помимо трудностей в чтение, это также означает, что вы получаете эффективность переключателя, а не только поведение!
функция Switch теперь доступна в Excel 2016 / Office 365
Переключатель (выражение, value1, result1, [по умолчанию или value2, result2]. [по умолчанию или value3, result3])
здесь
X = столбцы, которые вы индексируете в
Y = количество столбцов слева (- Y) или справа (Y) индексированного столбца, чтобы получить значение, которое вы ищете
Z = 0, если точное совпадение (если вы хотите обрабатывать ошибки)
Я знаю, что немного поздно отвечать, но я думаю, что это короткое видео поможет вам много.
по существу, он использует функцию choose. Он очень хорошо объясняет это в видео, поэтому я позволю сделать это вместо того, чтобы печатать 20 страниц.
другое видео его объясняет, как использовать проверку данных для заполнения выпадающего списка, который вы можете выбрать из ограниченного диапазон.
вы можете объединить эти два и использовать значение в раскрывающемся списке в качестве индекса для функции выбора. Хотя он не показал, как их комбинировать, я уверен, что вы могли бы понять это, поскольку его видео хорошие. Если у вас возникли проблемы, дайте мне знать, и я обновлю свой ответ, чтобы показать вам.
Я понимаю, что это ответ на старый пост-
мне нравится функция If () в сочетании с Index ()/Match ():
функция if сравнивает то, что находится в столбце b, и если она больше 0, она возвращает x, если нет, она использует массив (таблицу информации), идентифицированный функцией Index() и выбранный Match (), чтобы вернуть значение, которому соответствует a.
массив индексов имеет абсолютное расположение $H:$I (знак доллара) так что место, на которое он указывает, не изменится при копировании формулы. Строка со значением, которое вы хотите вернуть, определяется функцией Match (). Match () имеет дополнительное значение, не нуждаясь в отсортированном списке, чтобы просмотреть, что требует Vlookup (). Match () может найти значение со значением: 1 меньше, 0 точно, -1 больше. Я поставил ноль после абсолютного массива Match () $H:$H найти точное совпадение. Для столбца значение массива Index (), которое нужно вернуть, является введенный. Я ввел 2, потому что в моем массиве возвращаемое значение было во втором столбце. Ниже моего массива индексов выглядело так:
значение в столбце для поиска в списке в первом столбце В моем примере и соответствующее значение, которое будет возвращать права. Таблица look up / reference может быть на любой вкладке рабочей книги или даже в другом файле. - Book2-это имя файла, а Sheet2-это имя "другой вкладки".
если у вас не хочу, чтобы X возвращался, когда значение b больше нуля, удалите x для "пустого" /нулевого эквивалента или, возможно, поместите 0 - не уверен, что вы хотите там.
ниже начинается функция с X удален.
я использовал это решение для преобразования однобуквенных цветовых кодов в их описания:
вы в основном ищете элемент, который вы пытаетесь декодировать в массиве, а затем используете CHOOSE() для выбора связанного элемента. Это немного компактнее, чем создание таблицы для VLOOKUP() .
даже если старый, это, кажется, популярные вопросы, поэтому я опубликую другое решение, которое я думаю, очень элегантно:
это элегантно, потому что он использует только функцию IF. В принципе, это сводится к следующему:
Если(условие, выберите / используйте значение из таблицы, если(условие, выбрать/использовать другое значение из таблицы.
работает красиво, даже лучше, чем HLOOKUP или VLOOOKUP
но. Будьте осторожны - существует ограничение на количество вложенных операторов if в Excel может обрабатывать.
если у вас нет оператора SWITCH в Вашей версии Excel (pre-Excel-2016), вот реализация VBA для него:
Он работает так же, как и ожидалось, падени-в замене, например, для электронных таблиц от Google .
- селектор-это любое выражение, которое сравнивается с ключами
- key1, key2, . это выражения, которые сравниваются с селектор
- value1, value2. значения, которые выбираются, если селектор равен соответствующему ключу (только)
- defaultvalue используется, если ключ не соответствует селектору
чтобы использовать его, откройте Excel, перейдите на вкладку инструменты разработки, нажмите Visual Basic, щелкните правой кнопкой мыши на ThisWorkbook, выберите Вставить, затем модуль, наконец, скопируйте код в Редактор. Вы должны сохранить как макро-дружественную книгу Excel (файл xlsm).
Функция ПЕРЕКЛЮЧ вычисляет значение (которое называют выражением) на основе списка значений и возвращает результат, соответствующий первому совпадающему значению. Если совпадения не обнаружены, может быть возвращено необязательное стандартное значение.
SWITCH(выражение;значение1;результат1;[по_умолчанию или значение2;результат2];…[по_умолчанию или значение3;результат3])
выражение
(обязательно)
выражение — это значение (например, число, дата или текст), которое сравнивается со значениями значение1…значение126.
значение1. значение126
значениеN — это значение, с которым сравнивается выражение.
результат1…результат126
результат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, — это "понедельник", функция ПЕРЕКЛЮЧ возвращает значение "понедельник".
Выполняет несколько групп операторов в зависимости от значения выражения.
Синтаксис
Выбор тестэкспрессии case
[ Case expressionlist-n [ statements-n ]]
[ Case Else [ elsestatements ]]
End Select
Синтаксис оператора Select Case состоит из следующих частей:
Делимитированный список одной или более из следующих форм: выражение, expressionToexpression, Iscomparisonoperator_._
Ключевое слово To указывает диапазон значений. Если вы используете ключевое слово To, то наименьшее значение должно быть указано до To.
Комментарии
Если testexpression совпадает с любым выражением экспрессионистов Case, утверждения, следующие за этим пунктом Case, выполняются до следующего пункта Case или, для последнего предложения, до End Select. Затем контроль передается оператору после End Select. Если testexpression совпадает с выражением expressionlist в нескольких предложениях Case, выполняются только операторы после первого совпадения.
Предложение Case Else используется для указания того, что выражения elsestatements выполняются, если не обнаружено совпадение между testexpression и expressionlist в других предложениях Case. Хотя это необязательно, рекомендуется использовать оператор Case Else в блоке Select Case для обработки непредвиденных значений testexpression. Если экспрессиолист Case не соответствует тестэкспрессии и не существует утверждения Case Else, выполнение продолжается в заявлении, следующем end Select.
Используйте несколько выражений или диапазонов в каждом пункте Case . Например, допустима следующая строка.
Оператор сравнения Is — не то же самое, что ключевое слово Is, используемое в операторе Select Case.
Вы также можете указать диапазоны и несколько выражений для строк символов. В следующем примере Case everything совпадает со строками, которые точно равны строкам, nuts soup которые находятся между алфавитным порядком и в алфавитном порядке, TestItem и текущим значением :
Операторы Select Case могут быть вложенными. Каждое вложенное заявление Select Case должно иметь совпадающий конечный выбор .
Пример
В этом примере оператор Select Case используется для оценки значения переменной. Второе предложение Case содержит значение оцениваемой переменной, поэтому выполняется только связанный с ним оператор.
См. также
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Excel case statements can be handled with either SWITCH function or nested IF statements. A popular use for the IF function is creating nested formulas that can check for various criteria. However, nested IF statements can get pretty complicated and cumbersome when dealing with several conditions.
Excel has introduced the SWITCH function which can essentially do what nested IF functions can, using only one function. In this article, we’re going to take a look at the differences between these two Excel case statements and how you can compare several conditions more efficiently. You can download our sample workbook below.
The IF function is one of the most popular functions of Excel. It allows creating conditions, on which a logic can be implemented. The IF function gives a TRUE or FALSE result depending on the outcome of the condition check.
There really are no other alternatives to the IF function, therefore, users typically prefer using nested structures which means using the function over and over again. Let's see how this works on an example. Below is a set of IF formulas inside one another.
This formula checks for 4 conditions, "S", "M", "L" and “other” to give a measurement. To do this, we need 2 extra IF functions which are connected to the negative result argument of the previous one. The logic in this structure is to continue the formula if first condition is not met. Then look at the second condition and continue likewise until the criteria is satisfied.
As you can imagine, nested IF statements become harder to read and maintain as the number of condition increase. The SWITCH function was introduced as a user-friendly alternative to alleviate the burden of too many IF formulas.
The SWITCH function was first introduced in Excel 2016 not to replace the IF function, but instead as an alternative to nested IF formulas. The SWITCH function evaluates the value in its first argument against the value-result pairs, and returns the result from the matched pair if there is a match, or the default value if there are none.
Syntax
=SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])
Note: Because functions are limited to 254 parameters, you can use up to 126 pairs of value and result arguments.
Let's revisit the measurement example using the SWITCH function this time.
The first advantage is the number of formulas used in this argument. When creating a nested IF statement, you need to be actively tracing where you’re at in the formula at each step. Using the SWITCH formula, you can do what 126 IF functions could, using a single function.
Note: It is not advisable to create a SWITCH function that contains 126 value-result pairs. Instead, try using the VLOOKUP function for matching large condition sets. For more information about the VLOOKUP, check out our HOW TO VLOOKUP article.
The second advantage comes from the fact that your formula is going to be much shorter in most cases. Formula text with the SWITCH function is shorter, and easier to read.
Читайте также: