Bendroji formulė
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Santrauka
Norėdami rasti artimiausią skaitinių duomenų atitikimą, galite naudoti INDEX ir MATCH, naudodamiesi ABS ir MIN funkcijomis. Parodytame pavyzdyje F5 formulė, nukopijuota žemiau, yra:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
kur kelionė (B5: B14) ir kaina (C5: C14) yra įvardijami diapazonai.
Formulėse F5, F6 ir F7 formulė grąžina kelionę, atitinkančią 500, 1000 ir 1500.
Pastaba: tai yra masyvo formulė, kurią reikia įvesti paspaudus „Control + Shift + Enter“, išskyrus „Excel 365“.
Paaiškinimas
Iš esmės tai yra „INDEX“ ir „MATCH“ formulė: „MATCH“ nustato artimiausios atitikties poziciją, paduoda poziciją į „INDEX“, o „INDEX“ pateikia stulpelio „Kelionė“ reikšmę toje pozicijoje. Sunkus darbas atliekamas su funkcija MATCH, kuri yra kruopščiai sukonfigūruota taip, kad atitiktų „minimalų skirtumą“ taip:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Žingsnis po žingsnio, paieškos vertė apskaičiuojama naudojant MIN ir ABS taip:
MIN(ABS(cost-E5)
Pirma, E5 vertė atimama iš nurodyto diapazono kainos (C5: C14). Tai yra masyvo operacija, ir kadangi diapazone yra 10 reikšmių, rezultatas yra masyvas su 10 tokių reikšmių:
(899;199;250;-201;495;1000;450;-101;500;795)
Šie skaičiai rodo skirtumą tarp kiekvienos sąnaudos C5: C15 ir sąnaudų langelyje E5, 700. Kai kurios reikšmės yra neigiamos, nes kaina yra mažesnė už skaičių E5. Norėdami konvertuoti neigiamas reikšmes į teigiamas, naudojame ABS funkciją:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
kuris grąžina:
(899;199;250;201;495;1000;450;101;500;795)
Mes ieškome artimiausios atitikties, todėl mes naudojame MIN funkciją, kad rastume mažiausią skirtumą, kuris yra 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Tai tampa paieškos verte MATCH viduje. Paieškos masyvas generuojamas kaip ir anksčiau:
ABS(cost-E5) // generate lookup array
kuris grąžina tą patį masyvą, kurį matėme anksčiau:
(899;199;250;201;495;1000;450;101;500;795)
Dabar mes turime tai, ko mums reikia, kad rastume artimiausios atitikties poziciją (mažiausias skirtumas), ir mes galime perrašyti formulės MATCH dalį taip:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Kai paieškos reikšmė yra 101, MATCH grąžina 8, nes 101 yra 8-oje masyvo pozicijoje. Galiausiai ši pozicija įtraukiama į INDEX kaip eilutės argumentą, nurodant diapazono kelionę kaip masyvą:
=INDEX(trip,8)
o INDEX grąžina 8-ą diapazono kelionę „Ispanija“. Kai formulė nukopijuojama žemyn į langelius F6 ir F7, ji randa artimiausią atitiktį 1000 ir 1500, „Prancūzija“ ir „Tailandas“, kaip parodyta.
Pastaba: jei yra lygus rezultatas, ši formulė grąžins pirmąsias rungtynes.
Su XLOOKUP
XLOOKUP funkcija suteikia įdomų būdą išspręsti šią problemą, nes 1 (tiksli atitiktis arba kita didžiausia) arba -1 (tiksli atitiktis arba kita mažiausia) atitikties rūšis nereikalauja duomenų rūšiavimo. Tai reiškia, kad galime parašyti tokią formulę:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Kaip aukščiau, mes naudojame absoliučią (sąnaudų-E5) vertę, kad sukurtume paieškos masyvą:
(899;199;250;201;495;1000;450;101;500;795)
Tada sukonfigūruojame „XLOOKUP“ ieškoti nulio, kai atitikties tipas nustatytas į 1, tikslios atitikties arba kitos didžiausios. Mes tiekiame įvardytą nuotolio kelionę kaip grįžtamąjį masyvą, taigi rezultatas yra „Ispanija“ kaip ir anksčiau.
