ФОРМУЛА EXCEL: ИНДЕКС И ПОИСКПОЗ С НЕСКОЛЬКИМИ КРИТЕРИЯМИ

Универсальная формула

{= INDEX (range1, MATCH (1,(A1=range2)*(B1=range3)*(C1=range4),0))}

Резюме

Чтобы найти значения с помощью INDEX и MATCH, используя несколько критериев, вы можете использовать формулу массива. В показанном примере формула H8 имеет следующий вид:

 {= INDEX (E5:E11, MATCH (1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0))}

Примечание: это формула массива , и должен вводиться с помощью Ctrl + Shift + Enter, кроме Excel 365 .

Объяснение

Это более продвинутая формула. Основы см. Как использовать ИНДЕКС и ПОИСКПОЗ .

как преобразовать время в десятичное в Excel

Обычно формула ИНДЕКС ПОИСКПОЗ настраивается с ПОИСКПОЗ, чтобы просматривать диапазон из одного столбца и обеспечивать соответствие на основе заданных критериев. Без объединения значений в вспомогательный столбец или в самой формуле нет возможности указать более одного критерия.

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

 (H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)

Здесь мы сравниваем элемент в H5 со всеми элементами, размер в H6 со всеми размерами и цвет в H7 со всеми цветами. Первоначальный результат - это три массива результатов ИСТИНА / ЛОЖЬ, например:

 {TRUETRUETRUEFALSEFALSEFALSETRUE}*{FALSEFALSETRUEFALSEFALSETRUEFALSE}*{TRUEFALSETRUEFALSEFALSEFALSETRUE}

Кончик: используйте F9, чтобы увидеть эти результаты . Просто выберите выражение в строке формул и нажмите F9.

Математическая операция (умножение) преобразует значения ИСТИНА ЛОЖЬ в единицы и нули:

как добавить выпадающий список в Excel

 {1110001}*{0010010}*{1010001}

После умножения у нас есть такой единственный массив:

 {0010000}

который передается в функцию ПОИСКПОЗ как поисковый массив со значением поиска 1:

  MATCH (1,{0010000})

На этом этапе формула представляет собой стандартную формулу ИНДЕКС ПОИСКПОЗ. Функция ПОИСКПОЗ возвращает 3 в ИНДЕКС:

 = INDEX (E5:E11,3)

а ИНДЕКС возвращает окончательный результат 17,00 долларов США.

Визуализация массива

Массивы, описанные выше, может быть трудно визуализировать. На изображении ниже показана основная идея. Столбцы B, C и D соответствуют данным в примере. Столбец F создается путем умножения трех столбцов вместе. Это массив, переданный в MATCH.

ИНДЕКС и ПОИСКПОЗ с несколькими критериями - визуализация массива

Версия без массива

К этой формуле можно добавить еще один ИНДЕКС, избегая необходимости вводить формулу массива с помощью Ctrl + Shift + Enter:

 = INDEX (rng1, MATCH (1, INDEX ((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))

Функция ИНДЕКС может обрабатывать массивы изначально, поэтому второй ИНДЕКС добавляется только для того, чтобы «поймать» массив, созданный с помощью логической операции, и снова вернуть тот же массив в ПОИСКПОЗ. Для этого INDEX настроен с нулевыми строками и одним столбцом. Уловка с нулевой строкой заставляет INDEX возвращать столбец 1 из массива (который в любом случае уже является одним столбцом).

Зачем вам нужна версия без массива? Иногда люди забывают ввести формулу массива с помощью Ctrl + Shift + Enter, и формула возвращает неверный результат. Таким образом, формула без массива является более «пуленепробиваемой». Однако это более сложная формула.

Примечание: в Excel 365 , нет необходимости вводить формулы массива специальным образом.