Vispārēja formula
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Kopsavilkums
Lai meklētu vērtību, saskaņojot vairākas kolonnas, varat izmantot masīva formulu, kuras pamatā ir MMULT, TRANSPOSE, COLUMN un INDEX. Parādītajā piemērā formula H4 ir:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
kur "nosaukumi" ir nosauktais diapazons C4: E7 un "grupas" ir nosauktais diapazons B4: B7. Formula atgriež grupu, kurai pieder katrs vārds.
Piezīme: šī ir masīva formula, un tā jāievada ar vadības maiņas ievadi.
Paskaidrojums
Strādājot no iekšpuses uz āru, šajā formulā izmantotie loģiskie kritēriji ir:
--(names=G4)
kur nosaukumi ir nosauktais diapazons C4: E7. Tas rada TRUE / FALSE rezultātu katrai datu vērtībai, un dubultnegatīvs TRUE FALSE vērtības piespiež uz 1 un 0, lai iegūtu šādu masīvu:
(0,0,0;1,0,0;0,0,0;0,0,0)
Šis masīvs ir 4 rindas pa 3 kolonnām, kas atbilst "vārdu" struktūrai.
Tiek izveidots otrais masīvs ar šo izteicienu:
TRANSPOSE(COLUMN(names)^0))
Funkciju COLUMN izmanto, lai izveidotu skaitlisku masīvu ar 3 kolonnām un 1 rindu, un TRANSPOSE pārveido šo masīvu 1 kolonnā un 3 rindās. Paaugstinot līdz nulles jaudai, visi skaitļi masīvā tiek vienkārši pārveidoti par 1. Pēc tam matricas reizināšanas veikšanai tiek izmantota funkcija MMULT:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
un iegūtais nonāk MATCH funkcijā kā masīvs, ar 1 kā uzmeklēšanas vērtību:
MATCH(1,(0;1;0;0),0)
Funkcija MATCH atgriež pirmās spēles pozīciju, kas atbilst pirmās atbilstošās rindas rindai, kas atbilst piegādātajiem kritērijiem. Tas tiek ievadīts INDEX kā rindas numurs, ar masīvu nosaukto diapazonu "grupas":
=INDEX(groups,2)
Visbeidzot, INDEX atgriež "Lācis", grupai, kurai pieder Ādams.
Burtiskā satur kritērijus
Lai pārbaudītu, vai precīzas atbilstības vietā nav noteiktas teksta vērtības, varat izmantot funkcijas ISNUMBER un SEARCH kopā. Piemēram, lai saskaņotu šūnas, kurās ir “ābols”, varat izmantot:
=ISNUMBER(SEARCH("apple",data))
Šī formula ir izskaidrota šeit.
