Поиск ближайшего совпадения

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

Как найти ближайшее совпадение в Excel?

Чтобы определить, какой продавец достиг объема продаж, наиболее близкого к целевому значению $20 000, как показано выше, используйте формулу, объединяющую функции ИНДЕКС, ПОИСКПОЗ, ABS и МИН. Функция ABS преобразует все разности между объемами продаж каждого продавца и целевым значением в положительные числа. Затем функция МИН находит наименьшую из этих разностей — то есть ближайшее совпадение. С помощью функции ПОИСКПОЗ определяется позиция этого ближайшего совпадения, а функция ИНДЕКС возвращает соответствующее ей значение.

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

=INDEX(return_range,MATCH(MIN(ABS(lookup_array-lookup_value)),ABS(lookup_array-lookup_value),0))

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

Чтобы определить,какой продавец достиг объема продаж, наиболее близкого к целевому значению $20,000, скопируйте или введите приведенную ниже формулу в ячейку F5 и нажмите сочетание клавиш Ctrl+Shift+Enter, чтобы получить результат:

=ИНДЕКС()B5:B10,ПОИСКПОЗ(МИН(ABS()))C5:C10-20000)),ABS(C5:C10-20000),0))

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

=ИНДЕКС()B5:B10,ПОИСКПОЗ(МИН(ABS()))C5:C10-F4)),ABS(C5:C10-F4),0))

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

=INDEX(B5:B10,MATCH(MIN(ABS(C5:C10-F4)),ABS(C5:C10-F4),0))

• ABS(C5:C10-F4): Часть C5:C10-F4 вычисляет разности между объёмами продаж в диапазоне C5:C10 и целевым объёмом продаж $20,000 из ячейки F4, формируя массив следующего вида: {-4322;2451;6931;-1113;6591;-4782}. Функция ABS преобразует все отрицательные значения в положительные, в результате чего получается массив: {4322;2451;6931;1113;6591;4782}.
• МИН()ABS(C5:C10-F4))=МИН(){4322;2451;6931;1113;6591;4782}):Функция МИН находит наименьшее число в массиве {4322;2451;6931;1113;6591;4782} — то есть минимальную разность, или, иными словами, ближайшее совпадение. Таким образом, функция возвращает значение 1113.
• ПОИСКПОЗ()МИН()ABS(C5:C10-F4)),ABS(C5:C10-F4)Параметр тип_сопоставления 0 заставляет функцию ПОИСКПОЗ найти позицию точного значения 1113 в массиве {4322;2451;6931;1113;6591;4782}. Функция вернёт значение 4, поскольку это число находится на 4-й позиции.
• ИНДЕКС()B5:B10,ПОИСКПОЗ()МИН()ABS(C5:C10-F4)),ABS(C5:C10-F4) Функция ИНДЕКС возвращает 4-е значение из диапазона B5:B10, то есть Бейл.

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

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