EXCEL -KAAVA: INDEX JA MATCH, JOISSA ON USEITA EHTOJA

Yleinen kaava | _+_ | Yhteenveto

Voit etsiä arvoja INDEX- ja MATCH -funktioilla käyttämällä useita ehtoja käyttämällä taulukkokaavaa. Esitetyssä esimerkissä H8: n kaava on:

{= INDEX (range1, MATCH (1,(A1=range2)*(B1=range3)*(C1=range4),0))}

Huomaa: tämä on taulukon kaava , ja se on annettava näppäimellä control + shift + enter, paitsi kohdassa Excel 365 .

Selitys

Tämä on kehittyneempi kaava. Perusasiat, katso INDEXin ja MATCHin käyttö .

kuinka lasket prosentuaalisen eron kahden luvun välillä Excelissä

Normaalisti INDEX MATCH -kaava on määritetty MATCH-asetuksella etsimään yhden sarakkeen alue ja tarjoamaan osuma annettujen ehtojen perusteella. Ilman yhdistäviä arvoja a auttajapylväs tai itse kaavassa ei ole mitään tapaa toimittaa useampaa kuin yhtä kriteeriä.

Tämä kaava kiertää tämän rajoituksen käyttämällä boolen logiikka luoda matriisi ykkösiä ja nollia, jotka edustavat rivejä, jotka vastaavat kaikkia kolmea ehtoa, ja käytä sitten MATCH -hakua vastaamaan ensimmäistä löydettyä. Väliaikainen joukko ykkösiä ja nollia luodaan tällä katkelmalla:

 {= INDEX (E5:E11, MATCH (1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0))}

Tässä vertaamme H5: n tuotetta kaikkiin tuotteisiin, H6: n kokoa kaikkiin kokoihin ja H7: n väriä kaikkiin väreihin. Alkuperäinen tulos on kolme TOSI/EPÄTOSI -tulostaulua, kuten tämä:

 (H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)

Kärki: käytä F9 nähdäksesi nämä tulokset . Valitse vain lauseke kaavapalkista ja paina F9.

Matemaattinen operaatio (kertolasku) muuttaa TRUE FALSE -arvot arvoiksi 1s ja 0s:

° c - ° f kaava

 {TRUETRUETRUEFALSEFALSEFALSETRUE}*{FALSEFALSETRUEFALSEFALSETRUEFALSE}*{TRUEFALSETRUEFALSEFALSEFALSETRUE}

Kertomisen jälkeen meillä on yksi tällainen taulukko:

 {1110001}*{0010010}*{1010001}

joka syötetään hakutoimintoksi MATCH -funktioon ja jonka hakuarvo on 1:

 {0010000}

Tässä vaiheessa kaava on tavallinen INDEX MATCH -kaava. MATCH -funktio palauttaa 3 kohtaan INDEX:

  MATCH (1,{0010000})

ja INDEX palauttaa lopullisen tuloksen 17,00 dollaria.

Taulukon visualisointi

Edellä selitettyjä matriiseja voi olla vaikea visualisoida. Alla oleva kuva näyttää perusidean. Sarakkeet B, C ja D vastaavat esimerkin tietoja. Sarake F luodaan kertomalla kolme saraketta yhteen. Se on matriisi, joka luovutettiin MATCHille.

INDEX ja MATCH useilla kriteereillä - matriisin visualisointi

Ei-matriisiversio

Tähän kaavaan on mahdollista lisätä toinen INDEKSI välttäen tarvetta syöttää taulukkokaavaksi control + shift + enter:

 = INDEX (E5:E11,3)

INDEX -toiminto pystyy käsittelemään matriiseja natiivisti, joten toinen INDEX lisätään vain boolean -logiikkaoperaatiolla luodun taulukon 'kiinniottamiseen' ja saman taulukon palauttamiseen uudelleen MATCH -järjestelmään. Tätä varten INDEXissä on nolla riviä ja yksi sarake. Nollarivin temppu saa INDEXin palauttamaan sarakkeen 1 taulukosta (joka on joka tapauksessa jo yksi sarake).

Miksi haluat ei-array-version? Joskus ihmiset unohtavat syöttää taulukkokaavan Control + Shift + Enter, ja kaava palauttaa virheellisen tuloksen. Joten ei-matriisikaava on luodinkestävämpi. Kompromissi on kuitenkin monimutkaisempi kaava.

Huomautus: Sisään Excel 365 , matriisikaavoja ei tarvitse syöttää erityisellä tavalla.