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