Általános képlet
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Összegzés
Az index és az egyeztetés értékeinek több feltétel használatával történő tömbképletet használhat. A bemutatott példában a H8 képlete a következő:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Megjegyzés: ez egy tömbképlet, amelyet a Control + Shift + Enter billentyűkombinációval kell megadni, az Excel 365 kivételével.
Magyarázat
Ez egy fejlettebb képlet. Az alapokat lásd: Az INDEX és a MATCH használata.
Normál esetben az INDEX MATCH képletet úgy konfigurálják, hogy a MATCH beállítva van, hogy átnézzen egy oszlopos tartományt, és egyezést biztosítson a megadott kritériumok alapján. Az értékek összefűzése nélkül egy segítő oszlopban vagy magában a képletben egyetlen kritérium megadása nem lehetséges.
Ez a képlet a korlátozás körül jár azzal, hogy logikai logikával létrehoz egy és nulla tömböt, amelyek a mind a 3 feltételnek megfelelő sorokat ábrázolják, majd a MATCH használatával illeszkedik az első 1 találathoz. Az egyek és nullák ideiglenes tömbjét ezzel a kódrészlettel generálják:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Itt összehasonlítjuk a H5 elemet az összes elemgel, a H6 méretet az összes mérettel és a H7 színt az összes színnel. A kezdeti eredmény a következő IGAZ / HAMIS eredmények három tömbje:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tipp: használja az F9 billentyűt az eredmények megtekintéséhez. Csak válasszon ki egy kifejezést a képletsávból, és nyomja meg az F9 billentyűt.
A matematikai művelet (szorzás) az IGAZ HAMIS értékeket 1s és 0s értékekké alakítja:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
A szorzás után egyetlen ilyen tömbünk van:
(0;0;1;0;0;0;0)
amely a MATCH függvénybe kerül keresési tömbként, 1 keresési értékkel:
MATCH(1,(0;0;1;0;0;0;0))
Ezen a ponton a képlet egy standard INDEX MATCH képlet. A MATCH függvény 3-at ad vissza INDEX-re:
=INDEX(E5:E11,3)
és az INDEX 17,00 dolláros végeredményt ad vissza.
Tömb vizualizáció
A fent kifejtett tömbök nehezen képzelhetők el. Az alábbi kép az alapötletet mutatja. A B, C és D oszlop megfelel a példában szereplő adatoknak. Az F oszlop úgy jön létre, hogy a három oszlopot megszorozzuk. A MATCH-nak átadott tömb.
Nem tömb verzió
Ehhez a képlethez hozzáadhat egy másik INDEX-et, elkerülve, hogy tömbképlettként írja be a vezérlő + shift + enter billentyűkombinációt:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
Az INDEX funkció natívan képes kezelni a tömböket, így a második INDEX csak azért kerül hozzáadásra, hogy "elkapja" a logikai művelettel létrehozott tömböt, és ugyanazt a tömböt visszaadja a MATCH-nak. Ehhez az INDEX nulla sorral és egy oszloppal van konfigurálva. A nulla soros trükk hatására az INDEX visszaküldi az 1. oszlopot a tömbből (ami egyébként már egy oszlop).
Miért szeretné a nem tömb verziót? Előfordul, hogy az emberek elfelejtik beírni a tömbképletet a control + shift + enter billentyűkombinációval, és a képlet helytelen eredményt ad vissza. Tehát egy nem tömb képlet "golyóállóbb". A kompromisszum azonban összetettebb képlet.
Megjegyzés: Az Excel 365-ben nem szükséges külön tömbképleteket megadni.
