Üldine valem
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Kokkuvõte
Väärtuse otsimiseks mitme veeru vahel sobitamise abil saate kasutada massiivivalemit, mis põhineb MMULT, TRANSPOSE, COLUMN ja INDEX. Näidatud näites on H4 valem:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
kus "nimed" on nimega vahemik C4: E7 ja "rühmad" on nimega vahemik B4: B7. Valem tagastab rühma, kuhu iga nimi kuulub.
Märkus: see on massiivi valem ja see tuleb sisestada juhtnupu sisestusega.
Selgitus
Seestpoolt töötades on selles valemis kasutatud loogilised kriteeriumid:
--(names=G4)
kus nimed on nimega vahemik C4: E7. See genereerib kõigi andmete väärtuste jaoks TÕENE / VALE tulemuse ja topeltnegatiiv sunnib TÕELISE VÄÄRI väärtused väärtusele 1 ja 0, et saada selline massiiv:
(0,0,0;1,0,0;0,0,0;0,0,0)
See massiiv on 4 rida 3 veeru võrra, mis sobib "nimede" struktuuriga.
Selle avaldisega luuakse teine massiiv:
TRANSPOSE(COLUMN(names)^0))
Funktsiooni COLUMN kasutatakse 3 veeru ja 1 reaga arvulise massiivi loomiseks ning TRANSPOSE teisendab selle massiivi 1 veeruks ja 3 reaks. Nulli astmele tõstmine teisendab massiivi kõik numbrid lihtsalt 1. Maatriksi korrutamiseks kasutatakse funktsiooni MMULT:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
ja saadud läheb massiivi funktsiooniks MATCH, kusjuures otsingu väärtus on 1:
MATCH(1,(0;1;0;0),0)
Funktsioon MATCH tagastab esimese vaste positsiooni, mis vastab esimese vastava rea reale, mis vastab esitatud kriteeriumidele. See sisestatakse rea numbriks INDEX-i, massiivina nimega vahemik "rühmad":
=INDEX(groups,2)
Lõpuks tagastab INDEX "Karu", rühma Adam kuulub.
Sõnasõnaline sisaldab kriteeriume
Täpse vaste asemel konkreetsete tekstiväärtuste kontrollimiseks võite kasutada funktsioone ISNUMBER ja SEARCH koos. Näiteks "õuna" sisaldavate lahtrite sobitamiseks võite kasutada järgmist.
=ISNUMBER(SEARCH("apple",data))
Seda valemit selgitatakse siin.
