Általános képlet
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Összegzés
Értékek megkereséséhez több oszlopba illesztéssel használhatja az MMULT, TRANSPOSE, COLUMN és INDEX alapú tömbképletet. A bemutatott példában a H4 képlete a következő:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
ahol a "nevek" a C4: E7 elnevezett tartomány, a "csoportok" pedig a B4: B7 nevű tartomány. A képlet visszaadja azt a csoportot, amelyhez minden név tartozik.
Megjegyzés: ez egy tömbképlet, amelyet a vezérlő eltolásával kell megadni.
Magyarázat
Belülről kifelé haladva a képletben használt logikai kritériumok a következők:
--(names=G4)
ahol a nevek a megnevezett tartomány C4: E7. Ez IGAZ / HAMIS eredményt generál az adatok minden értékéhez, és a kettős negatív az IGAZ HAMIS értékeket 1-re és 0-ra kényszeríti, így kap egy ilyen tömböt:
(0,0,0;1,0,0;0,0,0;0,0,0)
Ez a tömb 4 sorral, 3 oszloppal rendelkezik, és megfelel a "nevek" szerkezetének.
Egy második tömb jön létre ezzel a kifejezéssel:
TRANSPOSE(COLUMN(names)^0))
Az OSZLOP funkció segítségével 3 oszlopos és 1 soros numerikus tömb jön létre, és a TRANSPOSE ezt a tömböt 1 oszlopra és 3 sorra konvertálja. A nulla hatványára való emelés egyszerűen átalakítja a tömb összes számát 1-re. Az MMULT függvénnyel ezután mátrixszorzást hajtanak végre:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
és az így kapott tömbként a MATCH függvénybe kerül, 1-vel a keresési érték:
MATCH(1,(0;1;0;0),0)
A MATCH függvény visszaadja az első mérkőzés helyzetét, amely megfelel az első egyező sornak, amely megfelel a megadott feltételeknek. Ez az INDEX-be kerül sorszámként, tömbként a megnevezett tartomány "csoportok":
=INDEX(groups,2)
Végül az INDEX visszatér a "Medve" szóhoz, amelyhez Adam csoport tartozik.
A literál tartalmazza a kritériumokat
A pontos egyezés helyett konkrét szöveges értékek ellenőrzéséhez használhatja az ISNUMBER és a SEARCH függvényeket együtt. Például az "almát" tartalmazó cellák megfeleltetéséhez használhatja:
=ISNUMBER(SEARCH("apple",data))
Ezt a képletet itt magyarázzuk el.
