Yleinen kaava
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Yhteenveto
Löydät lähimmän vastaavuuden numeerisista tiedoista käyttämällä INDEX- ja MATCH-toimintoja ABS- ja MIN-toimintojen avulla. Esitetyssä esimerkissä F5: n kaava on kopioitu alaspäin:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
missä matka (B5: B14) ja hinta (C5: C14) on nimetty alueiksi.
Kohdissa F5, F6 ja F7 kaava palauttaa kustannuksiltaan lähimmän matkan vastaavaksi 500, 1000 ja 1500.
Huomaa: tämä on matriisikaava, ja se on syötettävä näppäimillä + + shift + enter paitsi Excel 365: ssä.
Selitys
Ytimessä tämä on INDEX- ja MATCH-kaava: MATCH etsii lähimmän vastaavuuden sijainnin, syöttää sijainnin INDEX: lle ja INDEX palauttaa arvon siihen kohtaan Trip-sarakkeessa. Kova työ tehdään MATCH-toiminnolla, joka on konfiguroitu huolellisesti vastaamaan "vähimmäiseroa" seuraavasti:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Asiat askel askeleelta, hakuarvo lasketaan MIN: llä ja ABS: llä seuraavasti:
MIN(ABS(cost-E5)
Ensimmäinen, arvo E5 vähennetään nimetty alue kustannukset (C5: C14). Tämä on matriisitoiminto, ja koska alueella on 10 arvoa, tuloksena on taulukko, jolla on 10 tällaista arvoa:
(899;199;250;-201;495;1000;450;-101;500;795)
Nämä luvut edustavat eroa kunkin C5: C15: n kustannuksen ja solussa E5, 700 olevan kustannuksen välillä. Jotkut arvot ovat negatiivisia, koska kustannukset ovat pienemmät kuin E5: ssä olevat luvut. Negatiivisten arvojen muuntamiseksi positiivisiksi käytämme ABS-toimintoa:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
joka palauttaa:
(899;199;250;201;495;1000;450;101;500;795)
Etsimme lähintä ottelua, joten käytämme MIN-toimintoa löytääksesi pienimmän eron, joka on 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Tästä tulee hakuarvo MATCH: n sisällä. Hakutaulukko luodaan kuten aiemmin:
ABS(cost-E5) // generate lookup array
joka palauttaa saman matriisin, jonka näimme aiemmin:
(899;199;250;201;495;1000;450;101;500;795)
Meillä on nyt mitä tarvitsemme lähimmän ottelun sijainnin löytämiseksi (pienin ero), ja voimme kirjoittaa kaavan MATCH-osan uudestaan seuraavasti:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Kun hakuarvo on 101, MATCH palauttaa arvon 8, koska 101 on taulukon 8. paikassa. Lopuksi tämä sijainti syötetään INDEX: iin riviargumenttina, nimetty alue trip matriisina:
=INDEX(trip,8)
ja INDEX palauttaa alueen 8. matkan "Espanja". Kun kaava kopioidaan alas soluihin F6 ja F7, se löytää lähimmän vastaavuuden luvuille 1000 ja 1500, "Ranska" ja "Thaimaa", kuten kuvassa.
Huomaa: jos tasapeli on tasan, tämä kaava palauttaa ensimmäisen ottelun.
XLOOKUP: n kanssa
XLOOKUP-toiminto tarjoaa mielenkiintoisen tavan ratkaista tämä ongelma, koska hakutyyppi 1 (tarkka ottelu tai seuraava suurin) tai -1 (tarkka haku tai seuraava pienin) ei vaadi tietojen lajittelua. Tämä tarkoittaa, että voimme kirjoittaa seuraavan kaavan:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Kuten yllä, käytämme (kustannus-E5) absoluuttista arvoa hakutaulukon luomiseen:
(899;199;250;201;495;1000;450;101;500;795)
Sitten määritämme XLOOKUPin etsimään nollaa, kun hakutyyppi on asetettu 1, tarkalle haulle tai seuraavaksi suurimmaksi. Toimitamme nimettyyn matkalle paluumatkaa, joten tulos on "Espanja" kuten aiemmin.
