Note: The other languages of the website are Google-translated. Back to English
English English

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

Doriți să vă finalizați munca zilnică rapid și perfect? Kutools pentru Excel aduce 300 de funcții avansate puternice (Combinați registre de lucru, sumă după culoare, divizați conținutul celulei, convertiți data și așa mai departe ...) și economisiți 80% timp pentru dvs.

  • Conceput pentru 1500 de scenarii de lucru, vă ajută să rezolvați 80% probleme Excel.
  • Reduceți mii de clicuri pe tastatură și mouse în fiecare zi, ușurați-vă ochii și mâinile obosite.
  • Deveniți expert Excel în 3 minute. Nu mai este nevoie să vă amintiți formule dureroase și coduri VBA.
  • Încercare gratuită nelimitată de 30 de zile. Garanție de restituire a banilor de 60 de zile. Upgrade gratuit și asistență timp de 2 ani.
Panglică Excel (cu Kutools pentru Excel instalat)

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 în Office (include Excel), la fel ca Chrome, Firefox și noul Internet Explorer.
Captură de ecran a Excel (cu fila Office instalată)
Comentarii (0)
Încă nu există evaluări. Fii primul care evaluează!
Nu există comentarii postate aici încă
Lăsa comentarii
Postare ca invitat
×
Evaluează această postare:
0   Caractere
Locații sugerate