Генеричка формула
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Резиме
Да бисте пронашли најближе подударање у нумеричким подацима, можете да користите ИНДЕКС и МАТЦХ, уз помоћ функција АБС и МИН. У приказаном примеру, формула у Ф5, копирана доле, је:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
где су путовање (Б5: Б14) и трошак (Ц5: Ц14) именовани опсези.
У Ф5, Ф6 и Ф7, формула враћа путовање најближе по цени на 500, 1000 и 1500, респективно.
Напомена: ово је формула низа и мора се унети са цонтрол + схифт + ентер, осим у програму Екцел 365.
Објашњење
У основи је ово ИНДЕКС и МАТЦХ формула: МАТЦХ лоцира позицију најближег подударања, храни позицију ИНДЕКС и ИНДЕКС враћа вредност на тој позицији у колони Трип. Напоран рад се обавља са функцијом МАТЦХ, која је пажљиво конфигурисана тако да одговара „минималној разлици“ овако:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Узимајући ствари корак по корак, вредност претраживања израчунава се са МИН и АБС овако:
MIN(ABS(cost-E5)
Прво се вредност у Е5 одузима од именоване цене опсега (Ц5: Ц14). Ово је операција низа, а пошто у опсегу има 10 вредности, резултат је низ са 10 вредности попут ове:
(899;199;250;-201;495;1000;450;-101;500;795)
Ови бројеви представљају разлику између сваког трошка у Ц5: Ц15 и трошкова у ћелији Е5, 700. Неке вредности су негативне, јер је трошак нижи од броја у Е5. За претварање негативних вредности у позитивне вредности користимо функцију АБС:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
који се враћа:
(899;199;250;201;495;1000;450;101;500;795)
Тражимо најближе подударање, па користимо функцију МИН да пронађемо најмању разлику, која је 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Ово постаје вредност претраживања унутар МАТЦХ. Прегледни низ се генерише као и раније:
ABS(cost-E5) // generate lookup array
који враћа исти низ који смо раније видели:
(899;199;250;201;495;1000;450;101;500;795)
Сада имамо оно што нам треба за проналажење положаја најближег подударања (најмања разлика), а део МАТЦХ формуле можемо преписати овако:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Са 101 као вредност претраживања, МАТЦХ враћа 8, јер је 101 на 8. месту у пољу. Коначно, ова позиција се уводи у ИНДЕКС као аргумент реда, а именовано путовање домета као низ:
=INDEX(trip,8)
и ИНДЕКС враћа осмо путовање у низу, „Шпанија“. Када се формула копира у ћелије Ф6 и Ф7, проналази најближе подударање са 1000 и 1500, „Француска“ и „Тајланд“, као што је приказано.
Напомена: ако постоји нерешено, ова формула враћа први меч.
Уз КСЛООКУП
Функција КСЛООКУП пружа занимљив начин за решавање овог проблема, јер тип подударања 1 (тачно подударање или следеће највеће) или -1 (тачно подударање или следеће најмање) не захтева сортирање података. То значи да можемо написати формулу попут ове:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Као и горе, користимо апсолутну вредност (цост-Е5) за креирање низа претраживања:
(899;199;250;201;495;1000;450;101;500;795)
Затим конфигуришемо КСЛООКУП да тражи нулу, са типом подударања постављеним на 1, за тачно подударање или следеће највеће. Именовано путовање испоручујемо као повратни низ, тако да је резултат „Шпанија“ као и раније.
