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

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

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

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

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

=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))

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

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

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

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

Пояснение к формуле

=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))

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

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

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

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