Excel-kaava: Hanki n. Ottelu hakemistolla INDEX / MATCH -

Sisällysluettelo

Yleinen kaava

(=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth)))

Yhteenveto

Jos haluat hakea useita vastaavia arvoja tietojoukosta kaavan avulla, voit selvittää IF- ja SMALL-funktioiden avulla kunkin vastaavuuksien rivinumeron ja syöttää kyseisen arvon takaisin hakemistoon INDEX. Esitetyssä esimerkissä kaava kohdassa I7 on:

(=INDEX(amts,SMALL(IF(ids=id,ROW(ids)-ROW(INDEX(ids,1,1))+1),H6)))

Missä nimetyt alueet ovat amteja (D4: D11), id (I3) ja ids (C4: C11).

Huomaa, että tämä on matriisikaava ja se on syötettävä painamalla Control + Vaihto + Enter.

Selitys

Ytimessä tämä kaava on yksinkertaisesti INDEX-kaava, joka hakee matriisin arvon tietyssä paikassa. N: n arvo annetaan sarakkeessa H, ja kaikki "raskas" työ, jonka kaava tekee, on selvittää rivi, josta arvo noudetaan, missä rivi vastaa "n: tä" vastaavuutta.

IF-funktio selvittää, mitkä rivit sisältävät vastaavuuden, ja PIENI-funktio palauttaa luettelon n: nnen arvon. IF: n sisällä looginen testi on:

ids=id

joka antaa tämän taulukon:

(TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)

Huomaa, että asiakastunnukset vastaavat 1. ja 4. sijaa, jotka näkyvät TOSI. IF: n "value if true"-argumentti luo luettelon suhteellisista rivinumeroista tällä lausekkeella:

ROW(ids)-ROW(INDEX(ids,1,1))+1

joka tuottaa tämän taulukon:

(1;2;3;4;5;6;7)

Tämä taulukko "suodatetaan" loogisten testitulosten avulla, ja IF-funktio palauttaa seuraavan taulukon tuloksen:

(1;FALSE;FALSE;4;FALSE;FALSE;FALSE)

Huomaa, että meillä on kelvolliset rivinumerot riveille 1 ja 2.

Tämän matriisin käsittelee sitten SMALL, joka on määritetty käyttämään sarakkeessa H olevia arvoja palauttamaan "n." Arvot. Pieni toiminto ohittaa taulukon loogiset arvot TOSI ja EPÄTOSI. Loppujen lopuksi kaavat pienenevät:

=INDEX(amts,1) // I6, returns $150 =INDEX(amts,4) // I7, returns $125

Käsittelyvirheet

Kun tietylle tunnukselle ei ole enää vastaavuuksia, SMALL-toiminto palauttaa #NUM -virheen. Voit käsitellä tätä virhettä IFERROR-toiminnolla tai lisäämällä logiikkaa otteluiden laskemiseen ja keskeyttämään käsittely, kun sarakkeessa H oleva luku on suurempi kuin osumien määrä. Tässä esimerkissä esitetään yksi lähestymistapa.