Tooprogram.ru

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

Excel функция case

Эквивалент функции Case в Excel

у меня интересная задача — мне нужно проверить следующие данные в Excel:

вы должны извинить мое удивительно плохое искусство ASCII. Поэтому мне нужен столбец D (x) для проверки соседних ячеек, а затем при необходимости преобразовать значения. Вот критерии:

если столбец B больше 0, все работает отлично, и я могу получить кофе. Если он не соответствует этому требованию, то мне нужно преобразовать A1 в соответствии с таблицей-для пример, 32 = 1420 и в D . К сожалению, нет никакой связи между A и тем, что ему нужно преобразовать, поэтому о создании вычисления не может быть и речи.

оператор case или switch был бы идеальным в этом сценарии, но я не думаю, что это собственная функция в Excel. Я также думаю, что было бы безумно цеплять кучу =IF() заявления вместе, которые я сделал около четырех раз, прежде чем решил, что это плохая идея (история моей жизни).

9 ответов

звучит как работа для 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 — не уверен, что вы хотите там.

Читать еще:  Как выровнять ширину столбцов в excel

ниже начинается функция с 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 (функция 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 функция case

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

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

    Читать еще:  Функция exp в excel

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

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

    В качестве куска [Значение] можно вставить любую переменную или свойство, значение которой или которого Вы можете проверить. Можно также проверять значение конкретной ячейки. При этом работать можно не только с числами, но и с текстами. И даже с булевыми значениями 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», если по ходу проверок требуется менять проверяемое значение, но значительно выигрывает при разнообразных проверках одного и того же выражения. Для чего собственно и был создан.

    Case Function Equivalent in Excel

    I have an interesting challenge — I need to run a check on the following data in Excel:

    You’ll have to excuse my wonderfully bad ASCII art. So I need the D column (x) to run a check against the adjacent cells, then convert the values if necessary. Here’s the criteria:

    If column B is greater than 0, everything works great and I can get coffee. If it doesn’t meet that requirement, then I need to convert A1 according to a table — for example, 32 = 1420 and place into D . Unfortunately, there is no relationship between A and what it needs to convert to, so creating a calculation is out of the question.

    A case or switch statement would be perfect in this scenario, but I don’t think it is a native function in Excel. I also think it would be kind of crazy to chain a bunch of =IF() statements together, which I did about four times before deciding it was a bad idea (story of my life).

    12 Answers 12

    Sounds like a job for VLOOKUP!

    You can put your 32 -> 1420 type mappings in a couple of columns somewhere, then use the VLOOKUP function to perform the lookup.

    Читать еще:  Скачать видео посылки

    Without reference to the original problem (which I suspect is long since solved), I very recently discovered a neat trick that makes the Choose function work exactly like a select case statement without any need to modify data. There’s only one catch: only one of your choose conditions can be true at any one time.

    The syntax is as follows:

    On the assumption that only one of the conditions 1 to N will be true, everything else is 0, meaning the numeric value will correspond to the appropriate result.

    If you are not 100% certain that all conditions are mutually exclusive, you might prefer something like:

    That said, if Excel has an upper limit on the number of arguments a function can take, you’d hit it pretty quickly.

    Honestly, can’t believe it’s taken me years to work it out, but I haven’t seen it before, so figured I’d leave it here to help others.

    EDIT: Per comment below from @aTrusty: Silly numbers of commas can be eliminated (and as a result, the choose statement would work for up to 254 cases) by using a formula of the following form:

    Note the second argument to the LOG clause, which puts it in base 2 and makes the whole thing work.

    Edit: Per David’s answer, there’s now an actual switch statement if you’re lucky enough to be working on office 2016. Aside from difficulty in reading, this also means you get the efficiency of switch, not just the behaviour!

    The Switch function is now available, in Excel 2016 / Office 365

    SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])

    WHERE
    X = The columns you are indexing into
    Y = The number of columns to the left (-Y) or right (Y) of the indexed column to get the value you are looking for
    Z = 0 if exact-match (if you want to handle errors)

    I used this solution to convert single letter color codes into their descriptions:

    You basically look up the element you’re trying to decode in the array, then use CHOOSE() to pick the associated item. It’s a little more compact than building a table for VLOOKUP() .

    I know it a little late to answer but I think this short video will help you a lot.

    Essentially it is using the choose function. He explains it very well in the video so I’ll let do it instead of typing 20 pages.

    Another video of his explains how to use data validation to populate a drop down which you can select from a limited range.

    You could combine the two and use the value in the drop down as your index to the choose function. While he did not show how to combine them, I’m sure you could figure it out as his videos are good. If you have trouble, let me know and I’ll update my answer to show you.

    I understand that this is a response to an old post-

    I like the If() function combined with Index()/Match():

    The if function compare what is in column b and if it is greater than 0, it returns x, if not it uses the array (table of information) identified by the Index() function and selected by Match() to return the value that a corresponds to.

    The Index array has the absolute location set $H$2:$I$9 (the dollar signs) so that the place it points to will not change as the formula is copied. The row with the value that you want returned is identified by the Match() function. Match() has the added value of not needing a sorted list to look through that Vlookup() requires. Match() can find the value with a value: 1 less than, 0 exact, -1 greater than. I put a zero in after the absolute Match() array $H$2:$H$9 to find the exact match. For the column that value of the Index() array that one would like returned is entered. I entered a 2 because in my array the return value was in the second column. Below my index array looked like this:

    The value in your ‘a’ column to search for in the list is in the first column in my example and the corresponding value that is to be return is to the right. The look up/reference table can be on any tab in the work book — or even in another file. -Book2 is the file name, and Sheet2 is the ‘other tab’ name.

    If you do not want x return when the value of b is greater than zero delete the x for a ‘blank’/null equivalent or maybe put a 0 — not sure what you would want there.

    Below is beginning of the function with the x deleted.

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