Двустороннее приблизительное совпадение по нескольким критериям

В этом руководстве мы покажем, как найти приблизительное совпадение по нескольким критериям, заданным одновременно в столбцах и строках таблицы Excel, с помощью функций ИНДЕКС, ПОИСКПОЗ и ЕСЛИ.

Как выполнить двустороннее приблизительное сопоставление по нескольким критериям в Excel?

Чтобы определить размер одежды для женщины, рост которой составляет 165,5 см, а вес — 55 кг, используйте две функции ПОИСКПОЗ в формуле: одна (в сочетании с функцией ЕСЛИ) определяет номер строки (по полу и росту), другая — номер столбца (по весу). Затем функция ИНДЕКС по этим координатам найдёт соответствующий размер. (Обратите внимание: в функции ИНДЕКС сначала указывается номер строки, затем — номер столбца.)

Универсальный синтаксис

=INDEX(return_range,MATCH(lookup_value1,IF(lookup_array2=lookup_value2,lookup_array1),match_type),MATCH(lookup_value3,lookup_array3,match_type))

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

Чтобы определить размер одеждыдля женщины, рост которой составляет 165,5см, а вес —55кг, скопируйте или введите приведенную ниже формулу в ячейку M9 и нажмите Ctrl+Shift+Enter, чтобы получить результат:

=ИНДЕКС()D5:J16,ПОИСКПОЗ()M6,ЕСЛИ()B5:B16=M5,C5:C16),1),ПОИСКПОЗ(M7,D4:J4,1))

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

=INDEX(D5:J16,MATCH(M6,IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))

• IF(B5:B16=M5,C5:C16):Функция ЕСЛИ поочерёдно проверяет каждое значение в диапазоне B5:B16 на совпадение со значением в ячейке M5 — Женский. Если условие выполняется, функция сохраняет соответствующее значение роста из диапазона C5:C16; в противном случае возвращает ЛОЖЬ. В результате формула формирует массив следующего вида: {157;160;162,5;165;167,5;170;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ}.
• ПОИСКПОЗ(M6,)IF(B5:B16=M5,C5:C16),1)=ПОИСКПОЗ(M6,){157;160;162,5;165;167,5;170;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ},1): Аргумент тип_сопоставления = 1 заставляет функцию ПОИСКПОЗ вернуть позицию наибольшего значения, которое меньше или равно 165,5(значение в ячейке)M6) в массиве. Таким образом, функция ПОИСКПОЗ вернёт 4 — позицию значения 165.
• MATCH(M7,D4:J4,1):Функция ПОИСКПОЗ возвращает 3, поскольку наибольшее значение, которое меньше или равно значению в M7 (55), находится на 3-й позиции диапазона D4:J4.
• ИНДЕКС()D5:J16,ПОИСКПОЗ(M6,)IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1)) = ИНДЕКС(D5:J16: Функция ИНДЕКС извлекает значение на пересечении 4-й строки и 3-го столбца в диапазоне D5:J16, то есть значение в ячейке F8 — M.

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

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