Поиск ближайшего совпадения
Чтобы найти ближайшее совпадение для значения поиска в числовом наборе данных в Excel, вы можете использовать функции INDEX, MATCH, ABS, и MIN вместе.
Как найти ближайшее совпадение в Excel?
Чтобы узнать, какой продавец совершил продажи, наиболее близкие к цели в $20,000, как показано выше, формула, объединяющая функции INDEX, MATCH, ABS и MIN, поможет вам следующим образом: Функция ABS преобразует все разностные значения между продажами каждого продавца и целевым значением в положительные. Затем функция MIN найдет наименьшую разницу, что означает ближайшее совпадение. Теперь мы можем использовать функцию MATCH для определения позиции ближайшего совпадения и функцию INDEX для извлечения значения в соответствующей позиции.
Общий синтаксис
=INDEX(диапазон_возврата,MATCH(MIN(ABS(массив_поиска-значение_поиска)),ABS(массив_поиска-значение_поиска),0))
√ Примечание: Это формула массива, которая требует ввода с помощью Ctrl + Shift + Enter.
- диапазон_возврата: Диапазон, из которого комбинированная формула должна вернуть имя продавца. Здесь ссылается на диапазон имен.
- массив_поиска: Диапазон ячеек со значениями, которые нужно сравнить со значением_поиска. Здесь ссылается на диапазон продаж.
- значение_поиска: Значение, с которым сравнивают для поиска ближайшего совпадения. Здесь ссылается на целевое значение продаж.
Чтобы узнать, какой продавец совершил продажи, наиболее близкие к цели в $20,000, скопируйте или введите формулу ниже в ячейку F5 и нажмите Ctrl + Shift + Enter, чтобы получить результат:
=INDEX(B5:B10,MATCH(MIN(ABS(C5:C10-20000)),ABS(C5:C10-20000),0))
Или используйте ссылку на ячейку, чтобы сделать формулу динамической:
=INDEX(B5:B10,MATCH(MIN(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}.
- MIN(MIN(ABS(C5:C10-F4))) = MIN(MIN({4322;2451;6931;1113;6591;4782})): Функция MIN найдет наименьшее число из массива {4322;2451;6931;1113;6591;4782}, что означает наименьшую разницу, или, другими словами, ближайшее совпадение. Таким образом, функция вернет 1113.
- MATCH(MATCH(MIN(ABS(C5:C10-F4)ABS(C5:C10-F4)),,ABS(C5:C10-F4)ABS(C5:C10-F4),0),0) = MATCH(MATCH(1113,,{4322;2451;6931;1113;6591;4782},0),0): Тип_совпадения 0 заставляет функцию MATCH найти позицию точного числа 1113 в массиве {4322;2451;6931;1113;6591;4782}. Функция вернет 4, так как число находится на 4-й позиции.
- INDEX(B5:B10B5:B10,,,MATCH(MIN(ABS(C5:C10-F4)ABS(C5:C10-F4))),,,ABS(C5:C10-F4)ABS(C5:C10-F4),0))) = INDEX(B5:B10B5:B10,,,4): Функция INDEX возвращает 4-е значение в диапазоне имен B5:B10, которое является Bale.
Связанные функции
Функция INDEX в Excel возвращает отображаемое значение на основе заданной позиции из диапазона или массива.
Функция MATCH в Excel ищет определенное значение в диапазоне ячеек и возвращает относительную позицию этого значения.
Функция ABS возвращает абсолютное значение числа. Отрицательные числа будут преобразованы в положительные с помощью этой функции, но положительные числа и ноль останутся неизменными.
Связанные формулы
Поиск ближайшего совпадения с несколькими критериями
В некоторых случаях вам может понадобиться найти ближайшее или приблизительное совпадение на основе более чем одного критерия. С помощью комбинации функций INDEX, MATCH и IF вы можете быстро это сделать в Excel.
Приблизительное совпадение с использованием INDEX и MATCH
Бывают случаи, когда нам нужно найти приблизительные совпадения в Excel для оценки производительности сотрудников, выставления оценок студентам, расчета стоимости доставки на основе веса и т.д. В этом руководстве мы расскажем, как использовать функции INDEX и MATCH для получения необходимых результатов.
Поиск ближайшего совпадения с несколькими критериями
В некоторых случаях вам может понадобиться найти ближайшее или приблизительное совпадение на основе более чем одного критерия. С помощью комбинации функций INDEX, MATCH и IF вы можете быстро это сделать в Excel.
Лучшие инструменты для повышения производительности Office
Kutools для Excel - Помогает вам выделиться из толпы
Kutools для Excel имеет более 300 функций, гарантируя, что то, что вам нужно, находится всего в одном клике...
Office Tab - Включите работу с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите сотни кликов мышью каждый день, попрощайтесь с болью в руке от использования мышки.
- Увеличивает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Привносит эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.