Z enim edinstvenim ključem v tabeli je enostavno iskati vrednost. Lahko preprosto uporabimo funkcijo VLOOKUP. Če pa v svojih podatkih nimate tega edinstvenega stolpca in morate poiskati več stolpcev, da se ujemajo z vrednostjo, VLOOKUP ne pomaga.
Za iskanje vrednosti v tabeli z več merili bomo uporabili formulo INDEX-MATCH-INDEX.
Splošna formula za iskanje po več merilih
= INDEX (iskalni obseg, MATCH (1, INDEX ((merila1 = obseg1)**(merila2 = obseg2)**(merilaN = obsegN), 0,1), 0))
lookup_range: To je obseg, iz katerega želite pridobiti vrednost.
Merila1, Merila2, Merila N: To so merila, ki se jim želite ujemati v območju 1, razponu2 in razponu N. Lahko imate do 270 meril - parov območij.
Razpon 1, obseg2, obsegN: To so območja, v katerih boste ustrezali vašim kriterijem.
Kako bo delovalo? Pa poglejmo…
INDEX in MATCH s primerom več meril
Tukaj imam podatkovno tabelo. Želim potegniti ime stranke z datumom rezervacije, graditeljem in površino. Tu imam torej tri kriterije in en obseg iskanja.
To formulo zapišite v celico I4, pritisnite Enter.
= INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0))
Kako deluje:
Že vemo, kako funkciji INDEX in MATCH delujeta v EXCEL -u, zato tega ne bom razlagal tukaj. Govorili bomo o triku, ki smo ga uporabili tukaj.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Glavni del je ta. Vsak del te izjave vrne polje true false.
Ko se logične vrednosti pomnožijo, vrnejo niz 0 in 1. Množenje deluje kot operator AND. Torej, ko so vse vrednosti resnične, potem vrne 1 else 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Vse skupaj se bo vrnilo
{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}* {FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; ; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}* {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Kar se bo prevedlo v
{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}
KAZALO((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Funkcija INDEX bo vrnila isto matriko ({0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0}) v funkcijo MATCH kot iskalno polje.
UJEMA(1,KAZALO((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): funkcija MATCH bo iskala 1 v matriki {0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0}. In vrnil bo indeksno številko prve 1, najdene v matriki. Kar je tukaj 8.
KAZALO(E2: E16,UJEMA(1,KAZALO((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Na koncu bo INDEX vrnil vrednost iz danega območja (E2: E16) pri najdenem indeks (8).
Enostavno ????. Žal ni bilo mogoče poenostaviti.
Rešitev niza
Če lahko pritisnete CTRL + SHIFT + ENTER, lahko odstranite notranjo funkcijo INDEX. Samo napišite to formulo in pritisnite CTRL + SHIFT + ENTER.
= INDEX (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0))
Splošna formula matrike za iskanje po več kriterijih
= INDEX (iskalno območje, MATCH (1, (merila1 = obseg1)*(merila2 = obseg2)*(merilaN = obsegN), 0))
Formula deluje enako kot zgornja razlaga.
Trudil sem se, da bi to čim bolj preprosto razložil. Če pa nisem bil dovolj jasen, mi to sporočite v spodnjem oddelku za komentarje. Mimogrede, za vožnjo avtomobila vam ni treba vedeti, kako motor deluje. Le voziti se morate. In to zelo dobro veste.
Kako poiskati top 5 vrednosti z podvojenimi vrednostmi z uporabo INDEX-MATCH v Excelu
Kako VLOOKUP več vrednosti v Excelu
Kako VLOOKUP z Dynamic Col Index v Excelu
Kako uporabljati VLOOKUP iz dveh ali več iskalnih tabel v Excelu
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost
Kako uporabljati funkcijo VLOOKUP v Excelu
Kako uporabljati funkcijo COUNTIF v Excelu
Kako uporabljati funkcijo SUMIF v Excelu
