ИНДЕКС и ПОИСКПОЗ по нескольким столбцам

Для поиска значения путем сопоставления по нескольким столбцам, формула массива, основанная на функциях ИНДЕКС и ПОИСКПОЗ, которая включает МУМНОЖ, ТРАНСП, и СТОЛБЕЦ, окажет вам услугу.

Как найти значение путем сопоставления по нескольким столбцам?

Чтобы заполнить соответствующий класс каждого студента, как показано в таблице выше, где информация перечислена по нескольким столбцам, вы можете сначала использовать комбинацию функций МУМНОЖ, ТРАНСП и СТОЛБЕЦ для создания матричного массива. Затем функция ПОИСКПОЗ даст вам позицию вашего искомого значения, которое будет передано в ИНДЕКС для получения нужного значения из массива.

Общий синтаксис

=ИНДЕКС(диапазон_возврата;(ПОИСКПОЗ(1;МУМНОЖ(--(массив_поиска=искомое_значение);ТРАНСП(СТОЛБЕЦ(массив_поиска)^0));0)))

√ Примечание: Это формула массива, для которой требуется ввод с помощью Ctrl + Shift + Enter.

Чтобы найти класс Джимми, скопируйте или введите формулу ниже в ячейку H5 и нажмите Ctrl + Shift + Enter, чтобы получить результат:

=ИНДЕКС($B$5:$B$7;($C$5:$E$7=G5);ТРАНСП(СТОЛБЕЦ($C$5:$E$7)^0));0)))

√ Примечание: Знаки доллара ($) выше указывают на абсолютные ссылки, что означает, что диапазоны имен и классов в формуле не изменятся, когда вы переместите или скопируете формулу в другие ячейки. Обратите внимание, что вы не должны добавлять знаки доллара к ссылке на ячейку, представляющую искомое значение, поскольку вы хотите, чтобы она была относительной при копировании в другие ячейки. После ввода формулы перетащите маркер заполнения вниз, чтобы применить формулу к нижним ячейкам.

Объяснение формулы

=ИНДЕКС($B$5:$B$7;(ПОИСКПОЗ(1;МУМНОЖ(--($C$5:$E$7=G5);ТРАНСП(СТОЛБЕЦ($C$5:$E$7)^0));0)))

• --($C$5:$E$7=G5): Этот сегмент проверяет каждое значение в диапазоне $C$5:$E$7 если они равны значению в ячейке G5 и генерирует массив ИСТИНА и ЛОЖЬ следующего вида:
  {TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}.
  Двойной минус затем преобразует ИСТИНЫ и ЛОЖИ в 1 и 0, чтобы получить массив следующего вида:
  {1,0,0;0,0,0;0,0,0}.
• СТОЛБЕЦ($C$5:$E$7): Функция СТОЛБЕЦ возвращает номера столбцов для диапазона $C$5:$E$7 в виде массива следующего вида: {3,4,5}.
• ТРАНСП(СТОЛБЕЦ($C$5:$E$7)^0) = ТРАНСП({3,4,5}^0): После возведения в степень 0 все числа в массиве {3,4,5} будут преобразованы в 1: {1,1,1}. Функция ТРАНСП затем преобразует массив столбцов в массив строк следующего вида: {1;1;1}.
• МУМНОЖ(--($C$5:$E$7=G5),ТРАНСП(СТОЛБЕЦ($C$5:$E$7)^0)) = МУМНОЖ({1,0,0;0,0,0;0,0,0},{1;1;1}): Функция МУМНОЖ возвращает матричное произведение двух массивов следующего вида: {1;0;0}.
• ПОИСКПОЗ(1,МУМНОЖ(--($C$5:$E$7=G5),ТРАНСП(СТОЛБЕЦ($C$5:$E$7)^0)),0) = ПОИСКПОЗ(1,{1;0;0},0): Тип_сопоставления 0 заставляет функцию ПОИСКПОЗ вернуть позицию первого совпадения 1 в массиве {1;0;0}, что равно 1.
• ИНДЕКС($B$5:$B$7$B$5:$B$7,,,(ПОИСКПОЗ(1,МУМНОЖ(--($C$5:$E$7=G5),,,ТРАНСП(СТОЛБЕЦ($C$5:$E$7)^0)),0))) = ИНДЕКС($B$5:$B$7$B$5:$B$7,,,1): Функция ИНДЕКС возвращает 1-е значение в диапазоне классов $B$5:$B$7, что является A.

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

Связанные функции

Связанные формулы