{= 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 , нет необходимости вводить формулы массива специальным образом.
случайное число от 1 до 12Вложения ИНДЕКС и ПОИСКПОЗ с несколькими критериями.xlsx Автор Дэйв Брунс