Note: The other languages of the website are Google-translated. Back to English
Autentificare  \/ 
x
or
x
Înregistrare  \/ 
x

or

Cum să utilizați INDEX și MATCH împreună în Excel

Când lucrați cu tabele Excel, este posibil să găsiți în mod constant situații de care aveți nevoie pentru a căuta o valoare. În acest tutorial, vă vom arăta cum să aplicați combinația funcțiilor INDEX și MATCH pentru a efectua căutări orizontale și verticale, căutări bidirecționale, căutări sensibile la majuscule și căutări care îndeplinesc criterii multiple.

Ce fac funcțiile INDEX și MATCH în Excel

Cum se utilizează funcțiile INDEX și MATCH împreună


Ce fac funcțiile INDEX și MATCH în Excel

Înainte de a utiliza funcțiile INDEX și MATCH, să ne asigurăm că știm cum ne pot ajuta INDEX și MATCH să căutăm mai întâi valorile.

Utilizarea funcției INDEX în Excel

INDEX funcția din Excel returnează valoarea la o locație dată într-un interval specific. Sintaxa funcției INDEX este următoarea:

=INDEX(array, row_num, [column_num])
  • mulțime (obligatoriu) se referă la intervalul din care doriți să returnați valoarea.
  • rând_num (obligatoriu, cu excepția cazului în care numărul_coloană este prezent) se referă la numărul rândului matricei.
  • coloană_num (opțional, dar necesar dacă rândul_num este omis) se referă la numărul de coloană al matricei.

De exemplu, să știe scorul final de examen al lui Jeff, al 6-lea elev din listă, puteți folosi funcția INDEX astfel:

=INDEX(E2:E11, 6) >>> revine 60

potrivire index excel 01

√ Notă: intervalul E2: E11 este locul în care este listat examenul final, în timp ce numărul 6 găsește scorul de examen al 6elevul.

Aici să facem un mic test. Pentru formula = INDEX (B2: E2,3), ce valoare va reveni? --- Da, se va întoarce China, 3 valoare în intervalul dat.

Acum ar trebui să știm că funcția INDEX poate funcționa perfect cu domenii orizontale sau verticale. Dar dacă avem nevoie de el pentru a returna o valoare într-un interval mai mare, cu mai multe rânduri și coloane? Ei bine, în acest caz, ar trebui să aplicăm atât un număr de rând, cât și un număr de coloană. De exemplu, pentru a afla țara din care provine Emily cu INDEX, putem localiza valoarea cu un număr de rând de 8 și un număr de coloană de 3 în celulele de la B2 la E11 astfel:

= INDEX (B2: E11,8,3) >>> revine China

potrivire index excel 02

Conform exemplelor de mai sus, despre funcția INDEX în Excel, ar trebui să știți că:

  • Funcția INDEX poate funcționa cu intervale verticale și orizontale.
  • Funcția INDEX nu face distincție între majuscule și minuscule.
  • Numărul rândului merge înaintea numărului coloanei (dacă aveți nevoie de ambele numere) în formula INDEX.

Cu toate acestea, pentru o bază de date foarte mare, cu mai multe rânduri și coloane, cu siguranță nu este convenabil pentru noi să aplicăm formula cu un număr de rând și un număr de coloane exacte. Și asta atunci când ar trebui să combinăm utilizarea funcției MATCH.

Acum, să învățăm mai întâi despre elementele de bază ale funcției MATCH.


Utilizarea funcției MATCH în Excel

Funcția MATCH din Excel returnează o valoare numerică, locația unui anumit element în intervalul dat. Sintaxa funcției MATCH este următoarea:

=MATCH(lookup_value, lookup_array, [match_type])
  • matrice_căutare (obligatoriu) se referă la intervalul de celule în care doriți să caute MATCH.
  • tip_potrivire (optional), 1, 0 or -1:
  • 1(implicit), MATCH va găsi cea mai mare valoare mai mică sau egală cu lookup_value. Valorile din matrice_căutare trebuie plasate în ordine crescătoare.
  • 0, MATCH va găsi prima valoare care este exact egală cu lookup_value. Valorile din matrice_căutare poate fi în orice ordine. (Pentru cazurile în care tipul de potrivire este setat la 0, puteți utiliza caractere joker.)
  • -1, MATCH va găsi cea mai mică valoare care este mai mare sau egală cu lookup_value. Valorile din matrice_căutare trebuie plasate în ordine descrescătoare.

De exemplu, să știe poziţia Verei în lista de nume, puteți folosi formula MATCH astfel:

= MATCH ("vera", C2: C11,0) >>> revine 4

potrivire index excel 03

√ Notă: Funcția MATCH nu este sensibilă la majuscule și minuscule. Rezultatul „4” indică faptul că numele „Vera” se află pe poziția 4 a listei. „0” din formulă este tipul de potrivire care va găsi prima valoare din matricea de căutare care este egală cu valoarea de căutare „Vera”.

Să știu poziția scorului „96” în rândul de la B2 la E2, puteți folosi MATCH astfel:

= MATCH (96, B2: E2,0) >>> revine 4

potrivire index excel 04

☞ Lucruri pe care ar trebui să le știm despre funcția MATCH din Excel:

  • Funcția MATCH returnează poziția valorii de căutare în matricea de căutare, nu valoarea în sine.
  • Funcția MATCH returnează prima potrivire în cazul dublurilor.
  • La fel ca funcția INDEX, funcția MATCH poate funcționa și cu intervale verticale și orizontale.
  • MATCH nu ține cont și de majuscule.
  • Dacă valoarea de căutare a formulei MATCH este sub formă de text, includeți-o între ghilimele.

Acum, că știm despre utilizările de bază ale funcțiilor INDEX și MATCH în Excel, să ne răsucim mânecile și să ne pregătim să combinăm cele două funcții.


Cum se utilizează funcțiile INDEX și MATCH împreună

În această parte, vom vorbi despre circumstanțe diferite pentru a utiliza funcțiile INDEX și MATCH pentru a satisface diferite nevoi.

Exemplu pentru a combina INDEX și MATCH

Vă rugăm să consultați exemplul de mai jos pentru a afla cum putem combina funcțiile INDEX și MATCH:

De exemplu, să știe Scorul final al examenului lui Evelyn, ar trebui să folosim formula:

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> revine 90

potrivire index excel 05

Ei bine, deoarece formula poate părea complicată, să parcurgem fiecare parte a acesteia.

potrivire index excel 06

După cum puteți vedea mai sus, cel mare INDEX formula conține trei argumente:

  • mulțime: A2: D11 îi spune INDEX să returneze valoarea potrivită din celule A2 la D11.
  • rând_num: MATCH („evelyn”, B2: B11,0) spune INDEX rândul exact al valorii.
  • Despre formula MATCH, o putem explica astfel: pentru a returna poziția primei valori care este exact egală cu „evelyn” în celulele de la B2 la B11 într-o valoare numerică, care este 5.
  • coloană_num: MATCH („examen final”, A1: D1,0) spune INDEX coloana exactă a valorii.
  • Despre formula MATCH, o putem explica astfel: pentru a returna poziția primei valori care este exact egală cu „examenul final” în celulele de la A1 la D1 într-o valoare numerică, care este 4.

Deci, puteți vedea marea formulă la fel de simplă ca cea pe care am arătat-o ​​mai jos:

= INDEX (A2: D11,5,4)

În exemplu, am folosit valori codificate, „evelyn” și „examen final”. Cu toate acestea, într-o formulă atât de mare, nu vrem valori codificate, deoarece va trebui să le schimbăm de fiecare dată când vom căuta ceva nou. În astfel de circumstanțe, putem folosi referințe de celule pentru a face formula dinamică astfel:

= INDEX (A2: D11,MECI(G2,B2:B11,0),MECI(F3, A1: D1,0))

potrivire index excel 07


INDEX și MATCH pentru a aplica o căutare la stânga

Acum, să presupunem că trebuie să cunoașteți clasa lui Evelyn, cum putem folosi INDEX și MATCH pentru a afla răspunsul? Dacă ați acordat atenție, ar trebui să observați că coloana clasei se află în partea stângă a coloanei cu nume și care depășește capacitatea funcției de căutare puternică a unui alt Excel, VLOOKUP.

De fapt, abilitatea de căutare stângă se întâmplă să fie unul dintre aspectele în care combinația INDEX și MATCH este superioară VLOOKUP.

Să știu clasa lui Evelyn, tot ce trebuie să faceți este să schimbați valoarea din celula F3 în „Class” și să utilizați aceeași formulă ca cea prezentată mai sus, funcțiile INDEX și MATCH vă vor spune răspunsul imediat:

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> revine A

potrivire index excel 08

Dacă aveți instala condus Kutools pentru Excel, un supliment Excel profesional dezvoltat de echipa noastră, puteți aplica, de asemenea, o căutare la stânga pentru valorile specificate cu ajutorul acestuia CAUTĂ de la dreapta la stânga caracteristică cu câteva clicuri. Pentru a implementa caracteristica, accesați Kutools fila în excel, găsiți Formulă grup și faceți clic pe CAUTĂ de la dreapta la stânga pe lista derulantă a Super CAUTARE. Veți vedea o casetă de dialog pop-up ca aceasta:

potrivire index excel 09

Faceți clic aici pentru pași concreți pentru a aplica caracteristica de căutare din stânga cu Kutools pentru Excel.


INDEX și MATCH pentru a aplica o căutare bidirecțională

Acum, puteți face formula combinată INDEX și MATCH cu valori de căutare dinamice pentru a aplica căutări în două direcții? Să practicăm realizarea formulelor în celulele G3, G4 și G5 așa cum se arată mai jos:

potrivire index excel 10

Iată răspunsurile:

Celula G3: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
Celula G4: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
Celula G5: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

√ Notă: După aplicarea formulelor, puteți obține cu ușurință informațiile oricărui elev schimbând numele din celula G2.


INDEX și MATCH pentru a aplica o căutare sensibilă la majuscule

Din exemplele de mai sus, știm că funcțiile INDEX și MATCH nu sunt sensibile la majuscule. Cu toate acestea, în cazurile în care aveți nevoie de formula dvs. pentru a distinge caracterele majuscule și minuscule, puteți adăuga CORECT funcţionează la formulele dvs. astfel:

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ Notă: Aceasta este o formulă matrice care necesită să introduceți cu Ctrl + Shift + Enter. O pereche de paranteze cretate va apărea apoi în bara de formule.

De exemplu, să știe scorul la examen al lui JIMMY, utilizați funcțiile astfel:

potrivire index excel 11

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> revine 86

Sau puteți utiliza referințe de celule:

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> revine 86
Notă: Nu uitați să intrați cu Ctrl + Shift + Enter.


INDEX și MATCH pentru a aplica o căutare cu mai multe criterii

Când aveți de-a face cu o bază de date mare, cu mai multe coloane și subtitrări de rând, este întotdeauna dificil să găsiți ceva care îndeplinește condiții multiple. În acest caz, vă rugăm să consultați formula de mai jos pentru a căuta mai multe criterii:

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
Notă: Aceasta este o formulă matrice care necesită introducerea cu Ctrl + Shift + Enter. O pereche de paranteze cretate va apărea apoi în bara de formule.

De exemplu, pentru a găsi scorul final la examenul Coco din clasa A, care este din India, formula este următoarea:

potrivire index excel 12

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> revine 88
√ Notă: Nu uitați să intrați cu Ctrl + Shift + Enter.

Ei bine, ce se întâmplă dacă uiți constant să folosești Ctrl + Shift + Enter pentru a completa formula astfel încât formula să returneze rezultate incorecte? Aici avem o formulă mai complexă, cu care puteți completa doar cu una simplă Introduce cheie:

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

Pentru același exemplu de mai sus pentru a găsi scorul final la examenul Coco din clasa A, care este din India, formula care are nevoie doar de un obișnuit Introduce hit-ul este după cum urmează:

potrivire index excel 13

=INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0)) >>> revine 88

Aici nu vom folosi valori codificate, deoarece ne vom dori o formulă universală în cazul cu criterii multiple. Numai în acest fel, putem obține cu ușurință rezultatul dorit prin modificarea valorilor din celulele G2, G3, G4 din exemplul de mai sus.

Cu Kutools pentru Excel"s Funcția de căutare în mai multe condiții, puteți căuta valori specifice cu mai multe criterii în câteva clicuri. Pentru a implementa caracteristica, accesați Kutools fila în excel, găsiți Formulă grup și faceți clic pe Căutare în mai multe condiții pe lista derulantă a Super CAUTARE. Veți vedea apoi o casetă de dialog pop-up, așa cum se arată mai jos:

potrivire index excel 14

Faceți clic aici pentru pași concreți pentru a aplica funcția de căutare cu mai multe condiții cu Kutools pentru Excel.


INDEX și MATCH pentru a aplica o căutare pe mai multe coloane

Dacă avem o foaie de calcul Excel cu coloane diferite care împărtășesc o subtitrare așa cum se arată mai jos, cum putem asocia numele fiecărui elev cu clasa sa cu INDEX și MATCH?

potrivire index excel 15

Aici, permiteți-mi să vă arăt modalitatea de a finaliza sarcina cu instrumentul nostru profesional Kutools pentru Excel. Cu al ei; cu al lui Formula Helper, puteți potrivi rapid elevii cu clasele lor, conform pașilor indicați mai jos:

1. Selectați celula de destinație unde doriți să aplicați funcția.

2. Sub Kutools filă, accesați Formula Helper, Faceți clic pe Formula Helper în lista derulantă.

potrivire index excel 16

3. Alege Căutați din tipul de formulă, apoi faceți clic pe Indexați și potriviți pe mai multe coloane.

potrivire index excel 17

4. a. Faceți clic pe primul pictogramă de potrivire a indexului Excelbutonul din partea dreaptă a Căutați_col pentru a selecta celulele din care doriți să returnați o valoare, adică numele clasei. (Aici puteți selecta o singură coloană sau rând.)
    b. Faceți clic pe al doilea pictogramă de potrivire a indexului Excelbutonul din partea dreaptă a Table_rng pentru a selecta celulele pentru a se potrivi cu valorile din cele selectate Căutați_col, adică numele elevilor.
    c. Faceți clic pe al treilea pictogramă de potrivire a indexului Excelbutonul din partea dreaptă a Căutare_valor pentru a selecta celula care urmează să fie căutată, adică numele elevului pe care doriți să îl potriviți cu clasa sa.

potrivire index excel 18

5. Faceți clic pe Ok, veți vedea numele clasei lui Jimmy apărut în celula de destinație.

potrivire index excel 19

6. Acum puteți trage mânerul de umplere în jos pentru a completa clasele altor studenți.

potrivire index excel 20

Faceți clic pentru a descărca Kutools pentru Excel pentru o încercare gratuită de 30 de zile.



  • Super Formula Bar (editați cu ușurință mai multe linii de text și formulă); Layout de citire (citiți și editați cu ușurință un număr mare de celule); Lipiți la interval filtrat...
  • Merge celule / rânduri / coloane și păstrarea datelor; Conținut de celule divizate; Combinați rânduri duplicate și sumă / medie... Prevenirea celulelor duplicate; Comparați gamele...
  • Selectați Duplicat sau Unic Rânduri; Selectați Rânduri goale (toate celulele sunt goale); Super Find și Fuzzy Find în multe cărți de lucru; Selectare aleatorie ...
  • Copie exactă Mai multe celule fără modificarea referinței formulelor; Creați automat referințe la foi multiple; Introduceți gloanțe, Casete de selectare și multe altele ...
  • Formule favorite și inserare rapidă, Gama, Diagrame și Imagini; Criptați celulele cu parola; Creați o listă de corespondență și trimiteți e-mailuri ...
  • Extrageți textul, Adăugați text, eliminați după poziție, Eliminați spațiul; Creați și imprimați subtotaluri de paginare; Convertiți conținutul dintre celule și comentarii...
  • Super Filtru (salvați și aplicați scheme de filtrare altor foi); Sortare avansată după lună / săptămână / zi, frecvență și multe altele; Filtru special cu bold, italic ...
  • Combinați cărți de lucru și foi de lucru; Merge Tables pe baza coloanelor cheie; Împărțiți datele în mai multe foi; Conversia în loturi xls, xlsx și PDF...
  • Gruparea tabelului pivot după numărul săptămânii, ziua săptămânii și multe altele ... Afișați celulele deblocate, blocate prin diferite culori; Evidențiați celulele care au formulă / nume...
fila kte 201905
  • Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
  • Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ​​ferestrele noi.
  • Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!
fundul officetab
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.