INDEX și MATCH pe mai multe coloane
Pentru a căuta o valoare prin potrivirea pe mai multe coloane, o formulă matrice bazată pe INDEX și MECI funcţii care încorporează MMULT, TRANSPUNE și COLOANA îți va face o favoare.
Cum să căutați o valoare prin potrivirea pe mai multe coloane?
Pentru a completa clasa corespunzătoare fiecărui elev așa cum se arată în tabelul de mai sus, unde informațiile sunt listate pe mai multe coloane, puteți utiliza mai întâi trucul funcției MMULT, TRANSPOSE și COLUMN pentru a produce o matrice matrice. Apoi funcția MATCH vă va oferi poziția valorii dvs. de căutare, care va fi transmisă la INDEX pentru a prelua valoarea pe care o căutați în matrice.
Sintaxa generică
=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))
√ Notă: Aceasta este o formulă matrice care necesită să introduceți cu Ctrl + Schimba + Intrați.
- return_range: Intervalul din care doriți ca formula să returneze informațiile despre clasă. Aici se referă la intervalul de clasă.
- valoare_cercare: Valoarea pe care formula a folosit-o pentru a găsi informațiile de clasă corespunzătoare. Aici se referă la numele dat.
- lookup_array: Gama de celule în care lookup_value este listat; Intervalul cu valorile de comparat cu lookup_value. Aici se referă la gama de nume.
- tip_potrivire 0: Forțează MATCH să găsească prima valoare care este exact egală cu lookup_value.
Pentru a găsi o clasa lui Jimmy, vă rugăm să copiați sau să introduceți formula de mai jos în celula H5 și apăsați Ctrl + Schimba + Intrați pentru a obține rezultatul:
= INDEX ($ B $ 5: $ B $ 7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPUNERE(coloana($C$5:$E$7)^0)),0)))
√ 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. Rețineți că nu ar trebui să adăugați semne dolar la referința celulei care reprezintă valoarea de căutare, deoarece doriți ca aceasta să fie relativă atunci când o copiați în alte celule. După ce introduceți formula, trageți mânerul de umplere în jos pentru a aplica formula celulelor de mai jos.
Explicația formulei
=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))
- --($C$5:$E$7=G5): Acest segment verifică fiecare valoare din interval $C$5:$E$7 dacă sunt egale cu valoarea din celula G5 și generează o matrice TRUE și FALSE astfel:
{ADEVAR,FALS,FALS;FALS,FALS,FALS;FALS,FALS,FALS}.
Negativul dublu va converti apoi valorile TRUE și FALSE la 1s și 0s pentru a produce o matrice ca aceasta:
{1,0,0; 0,0,0; 0,0,0}. - COLONĂ($C$5:$E$7): Funcția COLUMN returnează numerele coloanelor pentru interval $C$5:$E$7 într-o matrice ca aceasta: 3,4,5 {}.
- TRANSPUNE(COLONĂ($C$5:$E$7)^0) = TRANSPUNE(3,4,5 {}^0): După creșterea puterii la 0, toate numerele din matricea {3,4,5} vor fi convertite la 1: {1,1,1}. Funcția TRANSPOSE convertește apoi matricea de coloane într-o matrice de rânduri astfel: {1; 1; 1}.
- MMULT(--($C$5:$E$7=G5),TRANSPUNE(COLONĂ($C$5:$E$7)^0)) = MMULT({1,0,0; 0,0,0; 0,0,0},{1; 1; 1}): Funcția MMULT returnează produsul matriceal al celor două tablouri astfel: {1; 0; 0}.
- MECI (1,MMULT(--($C$5:$E$7=G5),TRANSPUNE(COLONĂ($C$5:$E$7)^0)), 0) = MECI (1,{1; 0; 0}, 0): Meciul_tip 0 forțează funcția MATCH să returneze poziția primei potriviri a 1 în matrice {1; 0; 0}, Care este 1.
- INDEX($ B $ 5: $ B $ 7,(POTRIVIT(1,MMULT(--($C$5:$E$7=G5),TRANSPUNE(COLONĂ($C$5:$E$7)^0)), 0))) = INDEX ($ B $ 5: $ B $ 7,1): Funcția INDEX returnează fișierul 1prima valoare din intervalul clasei $ B $ 5: $ B $ 7, Care este A.
Pentru a căuta cu ușurință o valoare prin potrivirea în mai multe coloane, puteți utiliza, de asemenea, programul nostru de completare Excel profesional Kutools pentru Excel. Consultați instrucțiunile de aici pentru a îndeplini misiunea.
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 Excel MMULT returnează produsul matriceal a două tablouri. Rezultatul matricei are același număr de rânduri ca și matricea1 și același număr de coloane ca și matricea2.
Funcția Excel TRANSPOSE rotește orientarea unui interval sau a unei matrice. De exemplu, poate roti un tabel aranjat orizontal în rânduri până la vertical în coloane sau invers.
Funcția COLUMN returnează numărul coloanei pentru care apare formula sau returnează numărul coloanei referinței date. De exemplu, formula =COLUMN(BD) returnează 56.
Formule conexe
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.
Căutare bidirecțională cu INDEX și MATCH
Pentru a căuta ceva atât pe rânduri, cât și pe coloane în Excel, sau spunem că pentru a căuta o valoare la intersecția rândului și coloanei specifice, putem folosi ajutorul funcțiilor INDEX și MATCH.
Căutați cea mai apropiată valoare de potrivire cu mai multe criterii
În unele cazuri, poate fi necesar să căutați valoarea de potrivire cea mai apropiată sau aproximativă pe baza mai multor criterii. Cu combinația funcțiilor INDEX, MATCH și IF, puteți realiza rapid în Excel.
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.
