Обща формула
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Обобщение
За да намерите най-близкото съвпадение в числови данни, можете да използвате INDEX и MATCH, с помощта на функциите ABS и MIN. В показания пример формулата във F5, копирана надолу, е:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
където пътуването (B5: B14) и цената (C5: C14) са наречени диапазони.
Във F5, F6 и F7 формулата връща пътуването, което е най-близко по цена, съответно до 500, 1000 и 1500.
Забележка: това е формула на масив и трябва да се въведе с control + shift + enter, освен в Excel 365.
Обяснение
В основата си това е формула INDEX и MATCH: MATCH локализира позицията на най-близкото съвпадение, подава позицията на INDEX и INDEX връща стойността на тази позиция в колоната Trip. Усилената работа се извършва с функцията MATCH, която е внимателно конфигурирана да съответства на "минималната разлика" по следния начин:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Вземайки нещата стъпка по стъпка, справочната стойност се изчислява с MIN и ABS по следния начин:
MIN(ABS(cost-E5)
Първо, стойността в E5 се изважда от посочената стойност на диапазона (C5: C14). Това е операция с масив и тъй като в диапазона има 10 стойности, резултатът е масив с 10 стойности като тази:
(899;199;250;-201;495;1000;450;-101;500;795)
Тези числа представляват разликата между всяка цена в C5: C15 и стойността в клетка E5, 700. Някои стойности са отрицателни, тъй като разходите са по-ниски от броя в E5. За да преобразуваме отрицателните стойности в положителни, използваме функцията ABS:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
което връща:
(899;199;250;201;495;1000;450;101;500;795)
Търсим най-близкото съвпадение, затова използваме функцията MIN, за да намерим най-малката разлика, която е 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Това се превръща в справочна стойност в MATCH. Търсещият масив се генерира както преди:
ABS(cost-E5) // generate lookup array
който връща същия масив, който видяхме по-рано:
(899;199;250;201;495;1000;450;101;500;795)
Сега имаме това, от което се нуждаем, за да намерим позицията на най-близкото съвпадение (най-малката разлика), и можем да пренапишем частта MATCH на формулата по следния начин:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
С 101 като справочна стойност, MATCH връща 8, тъй като 101 е на 8-ма позиция в масива. И накрая, тази позиция се подава в INDEX като аргумент на реда, като наименованието trip range е като масив:
=INDEX(trip,8)
и INDEX връща 8-то пътуване в диапазона „Испания“. Когато формулата се копира до клетки F6 и F7, тя намира най-близкото съвпадение на 1000 и 1500, "Франция" и "Тайланд", както е показано.
Забележка: ако има равенство, тази формула ще върне първия мач.
С XLOOKUP
Функцията XLOOKUP предоставя интересен начин за решаване на този проблем, тъй като типът на съвпадение 1 (точно съвпадение или следващо най-голямо) или -1 (точно съвпадение или следващо най-малко) не изисква сортиране на данни. Това означава, че можем да напишем формула по следния начин:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Както по-горе, използваме абсолютната стойност на (cost-E5), за да създадем справочен масив:
(899;199;250;201;495;1000;450;101;500;795)
След това конфигурираме XLOOKUP да търси нула, като типът на съвпадението е зададен на 1, за точно съвпадение или следващия по големина. Ние предоставяме посоченото пътуване с диапазон като масив за връщане, така че резултатът е "Испания", както преди.
