Formula generică
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
rezumat
Pentru a căuta valori cu INDEX și MATCH, utilizând mai multe criterii, puteți utiliza o formulă matrice. În exemplul prezentat, formula din H8 este:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter, cu excepția Excel 365.
Explicaţie
Aceasta este o formulă mai avansată. Pentru elementele de bază, consultați Cum se utilizează INDEX și MATCH.
În mod normal, o formulă INDEX MATCH este configurată cu MATCH setat pentru a privi printr-un interval cu o coloană și pentru a oferi o potrivire pe baza criteriilor date. Fără concatenarea valorilor într-o coloană de ajutor sau în formula în sine, nu există nicio modalitate de a furniza mai multe criterii.
Această formulă funcționează în jurul acestei limitări utilizând logica booleană pentru a crea o matrice de unii și zerouri pentru a reprezenta rânduri care corespund tuturor celor 3 criterii, apoi folosind MATCH pentru a se potrivi cu primele 1 găsite. Matricea temporară de unii și zerouri este generată cu acest fragment:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Aici comparăm elementul din H5 cu toate articolele, dimensiunea în H6 cu toate dimensiunile și culoarea din H7 cu toate culorile. Rezultatul inițial este de trei matrici de rezultate ADEVĂRATE / FALSE astfel:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Sfat: utilizați F9 pentru a vedea aceste rezultate. Doar selectați o expresie în bara de formule și apăsați F9.
Operația matematică (înmulțirea) transformă valorile TRUE FALSE în 1s și 0s:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
După multiplicare, avem o singură matrice ca aceasta:
(0;0;1;0;0;0;0)
care este introdus în funcția MATCH ca matrice de căutare, cu o valoare de căutare de 1:
MATCH(1,(0;0;1;0;0;0;0))
În acest moment, formula este o formulă standard INDEX MATCH. Funcția MATCH returnează 3 la INDEX:
=INDEX(E5:E11,3)
iar INDEX returnează un rezultat final de 17,00 USD.
Vizualizare matrice
Tablourile explicate mai sus pot fi dificil de vizualizat. Imaginea de mai jos prezintă ideea de bază. Coloanele B, C și D corespund datelor din exemplu. Coloana F este creată prin înmulțirea celor trei coloane împreună. Este matricea predată MATCH.
Versiune non-array
Este posibil să adăugați un alt INDEX la această formulă, evitând necesitatea introducerii ca formulă matrice cu control + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
Funcția INDEX poate gestiona matrici în mod nativ, astfel încât al doilea INDEX este adăugat doar pentru a „prinde” matricea creată cu operația logică booleană și pentru a returna aceeași matrice din nou la MATCH. Pentru a face acest lucru, INDEX este configurat cu zero rânduri și o coloană. Trucul cu rândul zero face ca INDEX să returneze coloana 1 din matrice (care oricum este deja o coloană).
De ce ați dori versiunea non-array? Uneori, oamenii uită să introducă o formulă matrice cu control + shift + enter, iar formula returnează un rezultat incorect. Deci, o formulă non-matrice este mai „antiglonț”. Cu toate acestea, compromisul este o formulă mai complexă.
Notă: În Excel 365, nu este necesar să introduceți formule matrice într-un mod special.
