Приблизительное совпадение с помощью ИНДЕКС и ПОИСКПОЗ

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

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

Чтобы определить тариф доставкив соответствии с заданным весом посылки ()9, значение в ячейке F6), как показано на скриншоте выше, можно объединить функции ИНДЕКС и ПОИСКПОЗ, применив небольшой приём для поиска необходимого приблизительного совпадения: тип_сопоставления -1 указывает функции ПОИСКПОЗ найти позицию наименьшего значения, которое больше или равно 9, после чего ИНДЕКС определит соответствующий тариф доставки в той же строке.

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

=INDEX(return_range,MATCH(lookup_value,lookup_array,[match_type]))

Чтобы найти тариф доставки для посылки весом 9кг, скопируйте или введите приведённую ниже формулу в ячейку F7и нажмите клавишу ENTER, чтобы получить результат:

=ИНДЕКС()C6:C10,ПОИСКПОЗ()9,B6:B10,-1))

Или используйте Ссылка на ячейку, чтобы сделать формулу динамической:

=ИНДЕКС()C6:C10,ПОИСКПОЗ()F6,B6:B10,-1))

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

=INDEX(C6:C10,MATCH(F6,B6:B10,-1))

• MATCH(F6,B6:B10,-1):Функция ПОИСКПОЗ определяет позицию заданного веса 9(значение в ячейке)F6) в диапазоне весов B6:B10. Тип сопоставления -1 указывает функции ПОИСКПОЗ найти наименьшее значение, которое больше или равно заданному весу 9, то есть 10. Поэтому функция вернёт 3, так как это 3-е значение в диапазоне. (Обратите внимание: при типе сопоставления)-1 значения в диапазоне B6:B10 должны быть расположены по убыванию.)
• ИНДЕКС()C6:C10,MATCH(F6,B6:B10,-1)) = ИНДЕКС(C6:C10 Функция ИНДЕКС возвращает 3-е значение из диапазона тарифов C6:C10, то есть 50.

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

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