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

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

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

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

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

=ИНДЕКС(диапазон_возврата;ПОИСКПОЗ(искомое_значение;диапазон_поиска;[тип_совпадения]))

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

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

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

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

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

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

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

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

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