Excel

INDEX in MATCH z več merili

Index Match With Multiple Criteria

Excel formula: INDEX in MATCH z več meriliSplošna formula | _+_ | Povzetek

Za iskanje vrednosti z INDEX in MATCH z uporabo več meril lahko uporabite formulo matrike. V prikazanem primeru je formula v H8:



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

Opomba: to je formula matrike , in jih je treba vnesti s tipko control + shift + enter, razen v Excel 365 .

Pojasnilo

To je bolj napredna formula. Za osnove glej Kako uporabljati INDEX in MATCH .





zaokrožite na tisoč excel

Običajno je formula INDEX MATCH konfigurirana z nastavitvijo MATCH za pregled obsega enega stolpca in zagotavljanje ujemanja na podlagi danih meril. Brez združevanja vrednosti v a pomožni stolpec ali v sami formuli ni možnosti, da bi podali več kot eno merilo.

Ta formula obide to omejitev z uporabo logična logika ustvariti matriko enot in ničel za predstavitev vrstic, ki ustrezajo vsem trem kriterijem, nato pa uporabite MATCH za ujemanje prvih 1 najdenih. Začasna matrika enot in nič se ustvari s tem delčkom:



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

Tukaj primerjamo izdelek v H5 z vsemi predmeti, velikost v H6 z vsemi velikostmi in barvo v H7 z vsemi barvami. Začetni rezultat so tri vrste rezultatov TRUE/FALSE, kot je ta:

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

Nasvet: Če želite videti te rezultate, uporabite F9 . Samo izberite izraz v vrstici s formulo in pritisnite F9.

Matematična operacija (množenje) spremeni vrednosti TRUE FALSE v 1s in 0s:

kako oštevilčim stolpce v
 
{TRUETRUETRUEFALSEFALSEFALSETRUE}*{FALSEFALSETRUEFALSEFALSETRUEFALSE}*{TRUEFALSETRUEFALSEFALSEFALSETRUE}

Po množenju imamo eno samo matriko, kot je ta:

 
{1110001}*{0010010}*{1010001}

ki se vnese v funkcijo MATCH kot iskalna matrika z iskalno vrednostjo 1:

 
{0010000}

Na tej točki je formula standardna formula INDEX MATCH. Funkcija MATCH vrne 3 v INDEX:

 
 MATCH (1,{0010000})

in INDEX vrne končni rezultat 17,00 USD.

Vizualizacija matrike

Zgornje matrike, opisane zgoraj, je težko vizualizirati. Spodnja slika prikazuje osnovno idejo. Stolpci B, C in D ustrezajo podatkom v primeru. Stolpec F nastane z množenjem treh stolpcev skupaj. To je matrika, predana MATCH -u.

INDEX in MATCH z več merili - vizualizacija matrike

Različica brez matrike

V to formulo je mogoče dodati še en INDEX, pri čemer se izognemo potrebi po vnosu kot formulo matrike s control + shift + enter:

 
= INDEX (E5:E11,3)

Funkcija INDEX lahko ročno upravlja z matrikami, zato je drugi INDEX dodan samo za 'ulov' matrike, ustvarjene z logično logično operacijo, in vrnitev iste matrike v MATCH. Če želite to narediti, je INDEX konfiguriran z nič vrsticami in enim stolpcem. Trik z ničelno vrstico povzroči, da INDEX vrne stolpec 1 iz matrike (ki je tako ali tako že en stolpec).

Zakaj bi želeli različico brez matrike? Včasih ljudje pozabijo vnesti formulo matrike s tipkami control + shift + enter in formula vrne napačen rezultat. Torej je formula brez matrike bolj 'neprebojna'. Vendar je kompromis bolj zapletena formula.

Opomba: V Excel 365 , formule matrike ni treba vnašati na poseben način.

kako narediti e v
Priloge mapa INDEX in MATCH z več merili.xlsx Avtor Dave Bruns


^