Търсене на най-близкото съвпадение
За да търсите най-близкото съвпадение на търсена стойност в числов набор от данни в Excel, можете да използвате INDEX, MATCH, ABS и MIN функционират заедно.
Как да намеря най-близкото съвпадение в Excel?
Да знам кои продавач направи най-близките продажби до целта от $20,000 XNUMX както е показано по-горе, формула, комбинираща функциите INDEX, MATCH, ABS и MIN, ще ви помогне по този начин: Функцията ABS ще превърне всички стойности на разликата между продажбите, направени от всеки продавач, и целта за продажби положителни, след което MIN ще намери най-малката разлика, което означава съвпадение в шкафа. Сега можем да използваме функцията MATCH, за да намерим позицията на съвпадението в шкафа и да имаме INDEX, за да извлечем стойността на съответната позиция.
Общ синтаксис
=INDEX(return_range,MATCH(MIN(ABS(lookup_array-lookup_value)),ABS(lookup_array-lookup_value),0))
√ Забележка: Това е формула за масив, която изисква да въведете с Ctrl + Превключване + Въведете.
- върнат_обхват: Диапазонът, от който искате комбинираната формула да върне продавача. Тук се отнася до диапазона от имена.
- търсене_масив: Диапазонът от клетки със стойностите за сравнение с търсена_стойност. Тук се отнася до асортимента на продажбите.
- търсена_стойност: Стойността, с която да се сравни, за да се намери най-близкото ѝ съвпадение. Тук се отнася до целта за продажби.
Да знам кои продавач направи най-близките продажби до целта от $20,000 XNUMX, моля, копирайте или въведете формулата по-долу в клетка F5 и натиснете Ctrl + Превключване + Въведете за да получите резултата:
=ИНДЕКС(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))
- 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(ABS (C5:C10-F4)) = MIN({4322;2451;6931;1113;6591;4782}): Функцията MIN ще намери най-малкото число от масива {4322;2451;6931;1113;6591;4782}, което означава най-малката разлика, или ние казваме съвпадението в шкафа. Така че функцията ще се върне 1113.
- СЪВПАДА(MIN(ABS (C5:C10-F4)),ABS (C5:C10-F4), 0) = СЪВПАДА(1113,{4322;2451;6931;1113;6591;4782}, 0): - match_type 0 принуждава функцията MATCH да намери позицията на точното число 1113 в масива {4322;2451;6931;1113;6591;4782}. Функцията ще се върне 4 тъй като номерът е на 4та позиция.
- ИНДЕКС(B5: B10,СЪВПАДА(MIN(ABS (C5:C10-F4)),ABS (C5:C10-F4), 0)) = ИНДЕКС(B5: B10,4): Функцията INDEX връща 4та стойност в диапазона от имена B5: B10, кое е бала.
Свързани функции
Функцията ИНДЕКС на Excel връща показаната стойност въз основа на дадена позиция от диапазон или масив.
Функцията MATCH на Excel търси конкретна стойност в диапазон от клетки и връща относителната позиция на стойността.
Функцията ABS връща абсолютната стойност на число. Отрицателните числа ще бъдат преобразувани в положителни с тази функция, но положителните числа и нулата няма да бъдат засегнати.
Свързани формули
Търсене на стойност на най-близкото съвпадение с множество критерии
В някои случаи може да се наложи да търсите най-близката или приблизителна стойност на съвпадение въз основа на повече от един критерий. С комбинацията от функции INDEX, MATCH и IF можете бързо да го направите в Excel.
Приблизително съвпадение с INDEX и MATCH
Има моменти, когато трябва да намерим приблизителни съвпадения в Excel, за да оценим представянето на служителите, да оценим резултатите на учениците, да изчислим пощенските разходи въз основа на теглото и т.н. В този урок ще говорим как да използваме функциите INDEX и MATCH, за да извлечем резултати, от които се нуждаем.
Търсете стойност на най-близкото съвпадение с множество критерии
В някои случаи може да се наложи да търсите най-близката или приблизителна стойност на съвпадение въз основа на повече от един критерий. С комбинацията от функции INDEX, MATCH и IF можете бързо да го направите в Excel.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
