Vispārēja formula
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Kopsavilkums
Lai meklētu vērtības ar INDEX un MATCH, izmantojot vairākus kritērijus, varat izmantot masīva formulu. Parādītajā piemērā formula H8 ir:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Piezīme: šī ir masīva formula, un tā jāievada ar vadības taustiņu + Shift + Enter, izņemot Excel 365.
Paskaidrojums
Šī ir sarežģītāka formula. Pamatinformāciju skatiet sadaļā Kā izmantot INDEX un MATCH.
Parasti INDEX MATCH formula tiek konfigurēta ar MATCH kopu, lai apskatītu vienas kolonnas diapazonu un nodrošinātu atbilstību, pamatojoties uz norādītajiem kritērijiem. Ja nesavienojat vērtības palīgkolonnā vai pašā formulā, nevar nodrošināt vairāk par vienu kritēriju.
Šī formula darbojas ap šo ierobežojumu, izmantojot loģisko loģiku, lai izveidotu masīvu ar vienām un nullēm, lai attēlotu rindas, kas atbilst visiem 3 kritērijiem, pēc tam izmantojot MATCH, lai atbilstu pirmajiem 1 atrastajiem. Pagaidu vienumu un nulles masīvs tiek ģenerēts ar šo fragmentu:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Šeit mēs salīdzinām vienumu H5 ar visiem vienumiem, izmēru H6 pret visiem izmēriem un krāsu H7 pret visām krāsām. Sākotnējais rezultāts ir trīs šādu TRUE / FALSE rezultātu masīvi:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Padoms: izmantojiet F9, lai redzētu šos rezultātus. Vienkārši atlasiet izteiksmi formulas joslā un nospiediet F9.
Matemātiskā darbība (reizināšana) TRUE FALSE vērtības pārveido par 1s un 0s:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Pēc reizināšanas mums ir viens šāds masīvs:
(0;0;1;0;0;0;0)
kas tiek ievadīts funkcijā MATCH kā uzmeklēšanas masīvs ar uzmeklēšanas vērtību 1:
MATCH(1,(0;0;1;0;0;0;0))
Šajā brīdī formula ir standarta INDEX MATCH formula. Funkcija MATCH atgriež 3 uz INDEX:
=INDEX(E5:E11,3)
un INDEX atgriež gala rezultātu 17,00 USD.
Masīva vizualizācija
Iepriekš paskaidrotos masīvus var būt grūti vizualizēt. Zemāk redzamais attēls parāda pamatideju. B, C un D kolonnas atbilst piemēra datiem. F kolonna tiek izveidota, reizinot trīs kolonnas kopā. Tas ir masīvs, kas nodots MATCH.
Bez masīva versija
Šai formulai ir iespējams pievienot vēl vienu INDEX, izvairoties no nepieciešamības ievadīt kā masīva formulu ar vadības + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
INDEX funkcija var apstrādāt masīvus dabiski, tāpēc otrais INDEX tiek pievienots tikai, lai "noķertu" masīvu, kas izveidots ar Būla loģikas darbību, un atkal to pašu masīvu atgrieztu MATCH. Lai to izdarītu, INDEX ir konfigurēts ar nulles rindām un vienu kolonnu. Nulles rindas triks liek INDEX atgriezt 1. kolonnu no masīva (kas jau tāpat ir viena kolonna).
Kāpēc jūs vēlaties, lai nebūtu masīva versija? Dažreiz cilvēki aizmirst ievadīt masīva formulu ar taustiņu + Shift + Enter, un formula atgriež nepareizu rezultātu. Tātad formula, kas nav masīva, ir vairāk “izturīga pret lodēm”. Tomēr kompromiss ir sarežģītāka formula.
Piezīme: Programmā Excel 365 nav nepieciešams masīva formulas ievadīt īpašā veidā.
