Salt la conținutul principal

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.

index potrivește mai multe coloane 1

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.

index potrivește mai multe coloane 2

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

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

Funcția Excel MATCH caută o anumită valoare într-un interval de celule și returnează poziția relativă a valorii.

Funcția Excel MMULT

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

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 Excel COLUMN

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

Caracteristici populare: Găsiți, evidențiați sau identificați duplicatele  |  Ștergeți rândurile goale  |  Combinați coloane sau celule fără a pierde date  |  Rundă fără Formula ...
Super VLookup: Criterii multiple  |  Valoare multiplă  |  Pe mai multe foi  |  Căutare fuzzy...
Adv. Lista verticală: Lista drop-down ușoară  |  Listă drop-down dependentă  |  Listă derulantă cu selectare multiplă...
Manager de coloane: Adăugați un număr specific de coloane  |  Mutați coloanele  |  Comutați starea vizibilității coloanelor ascunse  Comparați coloanele cu Selectați aceleași și diferite celule ...
Caracteristici prezentate: Focus pe grilă  |  Vedere de proiectare  |  Big Formula Bar  |  Manager registru de lucru și foi | Biblioteca de resurse (Text automat)  |  Data Picker  |  Combinați foi de lucru  |  Criptare/Decriptare celule  |  Trimiteți e-mailuri după listă  |  Super Filtru  |  Filtru special (filtrează bold/italic/barat...) ...
Top 15 seturi de instrumente12 Text Instrumente (Adăuga text, Eliminați caractere ...)  |  50+ Diagramă Tipuri de (Gantt Chart ...)  |  40+ Practic Formule (Calculați vârsta pe baza zilei de naștere ...)  |  19 inserare Instrumente (Introduceți codul QR, Inserați imaginea din cale ...)  |  12 Convertire Instrumente (Numere la cuvinte, conversie valutara ...)  |  7 Merge & Split Instrumente (Rânduri combinate avansate, Împărțiți celule Excel ...)  |  ... și altele

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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations