Функция случая, эквивалентная в Excel

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

| A - B - C - D | |------|------|------|------| | 36 | 0 | 0 | x | | 0 | 600 | 700 | x | |___________________________| 

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

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

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

Звучит как работа для VLOOKUP !

Вы можете разместить сопоставления типов 32 -> 1420 в нескольких столбцах где-нибудь, а затем использовать функцию VLOOKUP для выполнения поиска.

Без ссылки на исходную проблему (которая, как я подозреваю, давно решена), я совсем недавно обнаружил опрятный трюк, который делает функцию «Выбрать» работать точно так же, как оператор select case без необходимости изменять данные. Есть только один улов: только одно из ваших выбранных условий может быть истинным в любой момент.

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

 CHOOSE( (1 * (CONDITION_1)) + (2 * (CONDITION_2)) + ... + (N * (CONDITION_N)), RESULT_1, RESULT_2, ... , RESULT_N ) 

В предположении, что только одно из условий от 1 до N будет истинным, все остальное равно 0, что означает, что числовое значение будет соответствовать соответствующему результату.

Если вы не на 100% уверены, что все условия являются взаимоисключающими, вы можете предпочесть что-то вроде:

 CHOOSE( (1 * TEST1) + (2 * TEST2) + (4 * TEST3) + (8 * TEST4) ... (2^N * TESTN) OUT1, OUT2, , OUT3, , , , OUT4 , , <LOTS OF COMMAS> , OUT5 ) 

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

Честно говоря, не могу поверить, что мне потребовались годы, чтобы разобраться, но я не видел этого раньше, поэтому решил, что оставлю его здесь, чтобы помочь другим.

EDIT: За комментарий ниже от @aTrusty: Глубокие числа запятых могут быть устранены (и в результате оператор select будет работать до 254 случаев), используя формулу следующего вида:

 CHOOSE( 1 + LOG(1 + (2*TEST1) + (4*TEST2) + (8*TEST3) + (16*TEST4),2), OTHERWISE, RESULT1, RESULT2, RESULT3, RESULT4 ) 

Обратите внимание на второй аргумент предложения LOG , который помещает его в базу 2 и заставляет все это работать.

Попробуй это;

 =IF(B1>=0, B1, OFFSET($X$1, MATCH(B1, $X:$X, Z) - 1, Y) 

ГДЕ
X = столбцы, которые вы индексируете в
Y = количество столбцов влево (-Y) или справа (Y) индексированного столбца, чтобы получить значение, которое вы ищете
Z = 0, если точное совпадение (если вы хотите обрабатывать ошибки)

Функция Switch теперь доступна в Excel 2016 / Office 365

SWITCH (выражение, value1, result1, [default или value2, result2], … [default или value3, result3])

 example: =SWITCH(A1,0,"FALSE",-1,"TRUE","Maybe") 

Поддержка Microsoft -Office

Я понимаю, что это ответ на старый пост-

Мне нравится функция If () в сочетании с Index () / Match ():

 =IF(B2>0,"x",INDEX($H$2:$I$9,MATCH(A2,$H$2:$H$9,0),2)) 

Функция if сравнивает то, что находится в столбце b, и если оно больше 0, оно возвращает x, если не использует массив (таблицу информации), идентифицированный функцией Index () и выбранный Match (), чтобы вернуть значение, которое a соответствует.

Массив индекса имеет абсолютное местоположение, устанавливающее $H$2:$I$9 (знаки доллара), так что место, на которое оно указывает, не изменится по мере копирования формулы. Строка со значением, которое вы хотите вернуть, идентифицируется функцией Match (). Match () имеет добавленную стоимость, которая не нуждается в отсортированном списке, чтобы просмотреть, что требуется Vlookup (). Match () может найти значение со значением: 1 меньше, 0 точный, -1 больше. Я положил нуль после абсолютного массива Match () $H$2:$H$9 чтобы найти точное совпадение. Для столбца будет введено значение массива Index (), которое будет возвращено. Я ввел 2, потому что в моем массиве возвращаемое значение было во втором столбце. Ниже мой индексный массив выглядел так:

 32 1420 36 1650 40 1790 44 1860 55 2010 

Значение в столбце «a» для поиска в списке находится в первом столбце в моем примере, и соответствующее значение, которое должно быть возвратом, находится справа. Таблица поиска / ссылки может быть на любой вкладке рабочей книги – или даже в другом файле. -Book2 – это имя файла, а Sheet2 – это имя другой вкладки.

 =IF(B2>0,"x",INDEX([Book2]Sheet2!$A$1:$B$8,MATCH(A2,[Book2]Sheet2!$A$1:$A$8,0),2)) 

Если вы не хотите, чтобы x возвращался, когда значение b больше нуля, удалите x для «пустого» / нулевого эквивалента или, возможно, положите 0 – не знаете, что вы хотели бы там.

Ниже приводится начало функции с удаленным x.

 =IF(B2>0,"",INDEX... 

Я знаю, что немного поздно ответить, но я думаю, что это короткое видео поможет вам много.

http://www.xlninja.com/2012/07/25/excel-choose-function-explained/

По сути, он использует функцию выбора. Он очень хорошо объясняет это в видео, поэтому я позволю сделать это, вместо того, чтобы набирать 20 страниц.

В другом видео его объясняется, как использовать проверку данных для заполнения раскрывающегося списка, который вы можете выбрать из ограниченного диапазона.

http://www.xlninja.com/2012/08/13/excel-data-validation-using-dependent-lists/

Вы можете объединить два и использовать значение в раскрывающемся списке, как ваш индекс, для функции выбора. Пока он не показывал, как их сочетать, я уверен, что вы можете понять, как его видео хорошие. Если у вас есть проблемы, сообщите мне, и я обновлю свой ответ, чтобы показать вам.

Даже если старый, это, кажется, популярные вопросы, поэтому я опубликую другое решение, которое, я думаю, очень изящно:

http://fiveminutelessons.com/learn-microsoft-excel/using-multiple-if-statements-excel

Он изящный, потому что он использует только функцию IF. В основном, это сводится к следующему:

if ( условие , выбрать / использовать значение из таблицы , if ( условие , выбрать / использовать другое значение из таблицы

И так далее

Прекрасно работает, даже лучше, чем HLOOKUP или VLOOOKUP

но … Будьте предупреждены – существует ограничение на количество вложенных операторов if excel.

  • Подсчет числа блоков столбцов с определенным условием
  • значения combobox и связывание их с другой ячейкой внутри той же таблицы без использования VBA
  • Сумифы с Array условными
  • Excel INDEX MATCH повторяет только первое значение
  • Использовать результат сопоставления Excel в качестве выбора столбца
  • Заполните непересекающиеся пустые ячейки со значением из ячейки над первым пробелом
  • Выделите ячейки, если столбец содержит заданное число X раз
  • Excel - работа с текстом на нескольких листах
  • Excel: СРЕДНЕЕ, ЕСЛИ
  • Расширенный подсчет
  • Excel: Как объявить переменные в одной ячейке и определить их в другом?
  • Interesting Posts

    SSIS: «Ошибка: результат выражения« @ … не может быть записан в свойство »

    Разделить текст в ячейках запятыми

    из файла xlsx, содержащего ссылки / ссылки на файл необработанных данных xls

    Вставить одно и то же изображение в Excel с использованием EPplus

    Можно ли изменить цвет фона и другие визуальные атрибуты управления ползунком в Excel (через VBA или иначе)?

    Установить цвет сетки с помощью EPPlus?

    POST-запросы или объем данных, предоставленных в запросе, превышает предел емкости

    Проверка VBA-списка Excel – уникальная ошибка выполнения

    Laravel 5.5 превосходит очереди и куски

    Превосходно не закрывается правильно – пользовательская форма висит?

    Можно ли связать данные между двумя рабочими книгами Excel в SharePoint?

    Как улучшить обновление для Excel Data Connections?

    Помощь в написании этого скрипта в файле excel

    Как сохранить и сгенерировать файл excel и сохранить на сервере

    excel время захвата ОС спящего режима

    Давайте будем гением компьютера.