INDEX și MATCH cu mai multe matrice
Să presupunem că aveți mai multe tabele cu aceleași titluri ca cele prezentate mai jos, pentru a căuta valori care se potrivesc cu criteriile de atribuire din aceste tabele poate fi o muncă grea pentru dvs. În acest tutorial, vom vorbi despre cum să căutați o valoare în mai multe matrice, intervale sau grupuri prin potrivirea unor criterii specifice cu INDEX, MECI și ALEGE funcții.
Cum se caută o valoare în mai multe matrice?
Pentru a cunoaște lideri ai diferitelor grupuri care aparțin unor departamente diferite, puteți utiliza mai întâi funcția ALEGE pentru a viza tabelul din care să returnați numele liderului. Funcția MATCH va afla apoi poziția liderului în tabelul căruia îi aparține. În cele din urmă, funcția INDEX va prelua liderul pe baza informațiilor despre poziție plus coloana specifică în care sunt listate numele liderilor.
Sintaxa generică
=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)
- array_num: Numărul CHOOSE folosit pentru a indica o matrice din listă matrice1, matrice2,... pentru a returna rezultatul de la.
- matrice1, matrice2,...: Matricele din care să returneze rezultatul. Aici se referă la cele trei tabele.
- valoare_cercare: Valoarea pe care formula de combinație a folosit-o pentru a găsi poziția liderului corespunzător. Aici se referă la grupul dat.
- lookup_array: Gama de celule în care lookup_value este listat. Aici se referă la intervalul de grup. Notă: Puteți utiliza gama de grup din orice departament, deoarece toate sunt la fel și trebuie doar să obținem numărul poziției.
- column_num: Coloana pe care o indicați din care doriți să preluați datele.
Pentru a cunoaște lider al Grupei D care aparține Departamentului A, copiați sau introduceți formula de mai jos în celula G5 și apăsați Intrați pentru a obține rezultatul:
=INDEX(ALEGE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MECI(F5,$ B $ 5: $ B $ 8,0),2)
√ Notă: semnele dolar ($) de mai sus indică referințe absolute, ceea ce înseamnă că numele și intervalele de clasă din formulă nu se vor schimba atunci când mutați sau copiați formula în alte celule. După ce introduceți formula, trageți mânerul de umplere în jos pentru a aplica formula celulelor de mai jos, apoi modificați array_num în consecinţă.
Explicația formulei
=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
- CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): Funcția ALEGE returnează 1st tablou din cele trei tablouri enumerate în formulă. Deci se va întoarce $B$5:$C$8, adică intervalul de date al Departamentului A.
- POTERE (F5, $ B $ 5: $ B $ 8,0): Meciul_tip 0 forțează funcția MATCH să returneze poziția primei potriviri a Grupa D, valoarea din celulă F5, în matrice $ B $ 5: $ B $ 8, Care este 4.
- INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),POTERE (F5, $ B $ 5: $ B $ 8,0),2) = INDEX ($B$5:$C$8,4,2): Funcția INDEX preia valoarea de la intersecția 4al treilea rând și 2a doua coloană a intervalului $B$5:$C$8, Care este Emily.
Pentru a evita schimbarea array_num în formulă de fiecare dată când o copiați, puteți utiliza coloana helper, coloana D. Formula ar fi astfel:
=INDEX(ALEGE(D5,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MECI(F5,$ B $ 5: $ B $ 8,0),2)
√ Notă: numerele 1, 2, 3 în coloana de ajutor indicați matrice1, matrice2, matrice3 în interiorul funcției ALEGE.
Funcții conexe
Funcția Excel INDEX returnează valoarea afișată pe baza unei poziții date dintr-un interval sau dintr-o matrice.
Funcția Excel MATCH caută o anumită valoare într-un interval de celule și returnează poziția relativă a valorii.
Funcția CHOOSE returnează o valoare din lista argumentului valorii pe baza numărului de index dat. De exemplu, CHOOSE (3, „Apple”, „Peach”, „Orange”) returnează Orange, numărul de index este 3, iar Orange este a treia valoare după numărul de index din funcție.
Formule conexe
Căutați valori dintr-o altă foaie de lucru sau registru de lucru
Dacă știți cum să utilizați funcția VLOOKUP pentru a căuta valori într-o foaie de lucru, valorile vlookup dintr-o altă foaie de lucru sau registru de lucru nu vor fi o problemă pentru dvs.
Vlookup cu numele foii dymanice
În multe cazuri, poate fi necesar să colectați date pe mai multe foi de lucru pentru rezumat. Cu combinația dintre funcția VLOOKUP și funcția INDIRECT, puteți crea o formulă pentru a căuta valori specifice în foile de lucru cu nume de foaie dinamică.
Căutare cu mai multe criterii cu INDEX și MATCH
Atunci când ai de-a face cu o bază de date mare într-o foaie de calcul Excel cu mai multe coloane și titluri de rând, este întotdeauna dificil să găsești ceva care să îndeplinească mai multe criterii. În acest caz, puteți utiliza o formulă matrice cu funcțiile INDEX și MATCH.
Cele mai bune instrumente de productivitate Office
Kutools pentru Excel - Vă ajută să vă distingeți de mulțime
Kutools pentru Excel se mândrește cu peste 300 de caracteristici, Asigurați-vă că ceea ce aveți nevoie este la doar un clic distanță...
Fila Office - Activați lectura și editarea cu file în Microsoft Office (includeți Excel)
- O secundă pentru a comuta între zeci de documente deschise!
- Reduceți sute de clicuri de mouse pentru dvs. în fiecare zi, spuneți adio de la mâna mouse-ului.
- Vă crește productivitatea cu 50% atunci când vizualizați și editați mai multe documente.
- Aduce file eficiente la Office (inclusiv Excel), la fel ca Chrome, Edge și Firefox.
