De ce INDEX și MATCH sunt mai bune decât VLOOKUP în Excel

Le-am explicat anterior începătorilor cum să folosească funcțiile de bază ale CĂUTARE V (în engleză CĂUTARE V, abrevierea înseamnă „funcție de căutare verticală”). Și utilizatorilor experimentați li s-au arătat câteva formule mai complicate.

Și în acest articol vom încerca să oferim informații despre o altă metodă de lucru cu căutarea verticală.

S-ar putea să vă întrebați: „De ce este necesar acest lucru?”. Și acest lucru este necesar pentru a afișa toate metodele de căutare posibile. În plus, numeroase restricții VLOOKUP împiedică adesea obținerea rezultatului dorit. În acest sens, INDEX( ) MATCH( ) este mult mai funcțional și divers și au, de asemenea, mai puține restricții.

Elemente de bază INDEX MATCH

Deoarece scopul acestui ghid este de a arăta cât de bună este această caracteristică, noi Să ne uităm la informațiile de bază referitoare la principiile funcționării acestuia. Și vom arăta exemple și vom lua în considerare, de asemenea, de ce, este mai bine decât VLOOKUP ().

Sintaxa și utilizarea funcției INDEX

Această funcție ajută la găsirea valorii dorite printre zonele de căutare specificate pe baza numărului coloanei sau liniei. Sintaxă:

=INDEX(matrice, număr rând, număr coloane):

• matrice – zona în care va avea loc căutarea;
• line number – numărul liniei care trebuie căutată în matricea specificată. Dacă numărul rândului este necunoscut, trebuie specificat numărul coloanei;
• numărul coloanei – numărul coloanei care se găsește în matricea specificată. Dacă valoarea este necunoscută, este necesar un număr de linie.

Un exemplu de formulă simplă:

=INDEX(A1:S10,2,3)

Funcția va căuta în intervalul de la A1 la C10. Numerele arată din ce rând (2) și coloana (3) să afișeze valoarea dorită. Rezultatul va fi celula C2.

Destul de simplu, nu? Dar atunci când lucrați cu documente reale, este puțin probabil să aveți informații despre numerele coloanelor sau celulelor. Pentru asta este funcția MATCH().

Sintaxa și utilizarea funcției MATCH

Funcția MATCH() caută valoarea dorită și arată numărul ei aproximativ în zona de căutare specificată.

Sintaxa searchpos() arată astfel:

=MATCH(valoare la căutare, matrice la căutare, tip de potrivire)

• valoare de căutare – numărul sau textul de găsit;
• searched array – zona în care va avea loc căutarea;
• tip de potrivire – specifică dacă se caută valoarea exactă sau valorile cele mai apropiate de aceasta:
  • 1 (sau nicio valoare specificată) – returnează cea mai mare valoare care este egală sau mai mică decât valoarea specificată;
  • 0 – arată o potrivire exactă cu valoarea căutată. În combinația INDEX() MATCH() veți avea nevoie aproape întotdeauna de o potrivire exactă, așa că scriem 0;
  • -1 – Afișează cea mai mică valoare care este mai mare sau egală cu valoarea specificată în formulă. Sortarea se realizează în ordine descrescătoare.

De exemplu, în intervalul B1:B3 sunt înregistrate New York, Paris, Londra. Formula de mai jos va arăta numărul 3, deoarece Londra este a treia pe listă:

=EXPOZE(Londra,B1:B3,0)

Cum să lucrați cu funcția INDEX MATCH 

Probabil că ați început deja să înțelegeți principiul prin care se construiește munca comună a acestor funcții. Pe scurt, atunci INDEX() caută valoarea dorită printre rândurile și coloanele specificate. Și MATCH() arată numerele acestor valori:

=INDEX(coloana din care este returnată valoarea, MATCH(valoare de căutat, coloană de căutare, 0))

Îți este încă greu să înțelegi cum funcționează? Poate un exemplu va explica mai bine. Să presupunem că aveți o listă cu capitalele lumii și populația lor:

Pentru a afla dimensiunea populației unei anumite capitale, de exemplu, capitala Japoniei, folosim următoarea formulă:

=INDEX(C2:C10, MATCH(Japonia, A2:A10,0))

Explicaţie:

• Funcția MATCH() caută valoarea – „Japan” în tabloul A2:A10 și returnează numărul 3, deoarece Japonia este a treia valoare din listă. 
• Această cifră merge lanumărul liniei” în formula INDEX() și îi spune funcției să imprime o valoare din acest rând.

Deci formula de mai sus devine formula standard INDEX(C2:C10,3). Formula caută din celulele C2 la C10 și returnează date din a treia celulă din acest interval, adică C4, deoarece numărătoarea inversă începe din al doilea rând.

Nu doriți să prescrieți numele orașului în formulă? Apoi scrieți-l în orice celulă, spuneți F1, și utilizați-l ca referință în formula MATCH(). Și ajungi cu o formulă de căutare dinamică:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Important! Numărul de linii în mulțime INDEX() trebuie să fie același cu numărul de rânduri din matrice considerată în MATCH(), altfel veți obține un rezultat greșit.

Așteptați puțin, de ce nu folosiți formula VLOOKUP()?

=CĂUTAREV(F1, A2:C10, 3, Fals)

 Ce rost are să pierzi timpul încercând să-ți dai seama toate aceste complexități ale INDEX MATCH?

În acest caz, nu contează ce funcție să folosești. Acesta este doar un exemplu pentru a înțelege cum funcționează împreună funcțiile INDEX() și MATCH(). Alte exemple vor arăta de ce sunt capabile aceste funcții în situațiile în care VLOOKUP este neputincioasă. 

POTRIVIRE INDEX sau CĂUTARE V

Atunci când decid ce formulă de căutare să folosească, mulți sunt de acord că INDEX() și MATCH() sunt cu mult superioare față de VLOOKUP. Cu toate acestea, mulți oameni încă folosesc VLOOKUP(). În primul rând, VLOOKUP() este mai simplu, iar în al doilea rând, utilizatorii nu înțeleg pe deplin toate avantajele lucrului cu INDEX() și MATCH(). Fără aceste cunoștințe, nimeni nu va fi de acord să-și petreacă timpul studiind un sistem complex.

Iată avantajele cheie ale INDEX() și MATCH() față de VLOOKUP():

• Căutați de la dreapta la stânga. VLOOKUP() nu poate căuta de la dreapta la stânga, așa că valorile pe care le căutați trebuie să fie întotdeauna în coloanele din stânga tabelului. Dar INDEX() și MATCH() se pot descurca fără probleme. Acest articol vă va spune cum arată în practică: cum să găsiți valoarea dorită în partea stângă.

1. Adăugarea sau îndepărtarea în siguranță a coloanelor. Formula VLOOKUP() arată rezultate incorecte la eliminarea sau adăugarea coloanelor, deoarece VLOOKUP() are nevoie de numărul exact al coloanei pentru a avea succes. Desigur, atunci când coloanele sunt adăugate sau eliminate, numerele lor se schimbă și ele. 

Și în formulele INDEX() și MATCH(), este specificată o serie de coloane, nu coloane individuale. Ca rezultat, puteți adăuga și elimina coloane în siguranță, fără a fi necesar să actualizați formula de fiecare dată.

1. Fără limite pentru volumele de căutare. Când utilizați VLOOKUP(), numărul total de criterii de căutare nu trebuie să depășească 255 de caractere sau veți obține o #VALoare! Deci, dacă datele dumneavoastră conțin un număr mare de caractere, INDEX() și MATCH() sunt cea mai bună opțiune.
2. Viteză mare de procesare. Dacă mesele dvs. sunt relativ mici, atunci este puțin probabil să observați vreo diferență. Dar, dacă tabelul conține sute sau mii de rânduri și, în consecință, există sute și mii de formule, INDEX () și MATCH () se vor descurca mult mai repede decât VLOOKUP (). Cert este că Excel va procesa doar coloanele specificate în formulă, în loc să proceseze întregul tabel. 

Impactul asupra performanței VLOOKUP() va fi vizibil mai ales dacă foaia de lucru conține un număr mare de formule precum VLOOKUP() și SUM(). Verificări separate ale funcțiilor VLOOKUP() sunt necesare pentru a analiza fiecare valoare dintr-o matrice. Deci Excel trebuie să proceseze o cantitate imensă de informații, iar acest lucru încetinește semnificativ munca.

Exemple de formule 

Ne-am dat seama deja de utilitatea acestor funcții, așa că putem trece la partea cea mai interesantă: aplicarea cunoștințelor în practică.

Formula de căutare de la dreapta la stânga

După cum sa menționat deja, VLOOKUP nu poate efectua această formă de căutare. Deci, dacă valorile dorite nu sunt în coloana din stânga, VLOOKUP() nu va produce un rezultat. Funcțiile INDEX() și MATCH() sunt mai versatile, iar locația valorilor nu joacă un rol important pentru ca acestea să funcționeze.

De exemplu, vom adăuga o coloană de rang în partea stângă a tabelului nostru și vom încerca să ne dăm seama ce rang în termeni de populație ocupă capitala Țării Noastre.

În celula G1, scriem valoarea care trebuie găsită și apoi folosim următoarea formulă pentru a căuta în intervalul C1:C10 și returnăm valoarea corespunzătoare din A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Prompt. Dacă intenționați să utilizați această formulă pentru mai multe celule, asigurați-vă că remediați intervalele folosind adresarea absolută (de exemplu, $A$2: $A$10 și $C$2: 4C$10).

INDEX MAI EXPUS MAI EXPUS  pentru a căuta în coloane și rânduri

În exemplele de mai sus, am folosit aceste funcții ca înlocuitor pentru VLOOKUP() pentru a returna valori dintr-un interval predefinit de rânduri. Dar ce se întâmplă dacă trebuie să faceți o căutare matriceală sau pe două fețe?

Sună complicat, dar formula pentru astfel de calcule este similară cu formula standard INDEX() MATCH(), cu o singură diferență: formula MATCH() trebuie folosită de două ori. Prima dată pentru a obține numărul rândului și a doua oară pentru a obține numărul coloanei:

=INDEX(matrice, MATCH(valoare de căutare verticală, coloană de căutare, 0), MATCH(valoare de căutare orizontală, rând de căutare, 0))

Să ne uităm la tabelul de mai jos și să încercăm să facem o formulă INDEX() EXPRESS() EXPRESS() pentru a afișa datele demografice dintr-o anumită țară pentru un an selectat.

Țara țintă se află în celula G1 (căutare verticală), iar anul țintă este în celula G2 (căutare orizontală). Formula va arăta astfel:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Cum funcționează această formulă

Ca și în cazul oricărei alte formule complexe, ele sunt mai ușor de înțeles prin descompunerea lor în ecuații individuale. Și apoi puteți înțelege ce face fiecare funcție individuală:

• MATCH(G1,A2:A11,0) – caută o valoare (G1) în intervalul A2:A11 și arată numărul acestei valori, în cazul nostru este 2;
• CĂUTARE(G2,B1:D1,0) – caută o valoare (G2) în intervalul B1:D1. În acest caz, rezultatul a fost 3.

Numerele rândurilor și coloanelor găsite sunt trimise la valoarea corespunzătoare din formula INDEX():

=INDEX(B2:D11,2,3)

Ca rezultat, avem o valoare care se află într-o celulă la intersecția a 2 rânduri și 3 coloane în intervalul B2:D11. Și formula arată valoarea dorită, care se află în celula D3.

Căutați după mai multe condiții cu INDEX și MATCH

Dacă ați citit ghidul nostru pentru VLOOKUP(), probabil că ați încercat mai multe formule de căutare. Dar această metodă de căutare are o limitare semnificativă - necesitatea de a adăuga o coloană auxiliară.

Dar vestea bună este că Cu INDEX() și MATCH() puteți căuta mai multe condiții fără a fi nevoie să editați sau să schimbați foaia de lucru.

Iată formula generală de căutare cu mai multe condiții pentru INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Nota: această formulă trebuie utilizată împreună cu comanda rapidă de la tastatură CTRL+SHIFT+ENTER.

Să presupunem că trebuie să găsiți valoarea pe care o căutați pe baza a 2 condiții: Cumpărător и Produs.

Aceasta necesită următoarea formulă:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

În această formulă, C2:C10 este intervalul în care va avea loc căutarea, F1 – această condiție, A2:A10 — este intervalul pentru a compara starea, F2 – condiția 2, V2:V10 – interval pentru compararea condiției 2.

Nu uitați să apăsați combinația la sfârșitul lucrării cu formula CTRL+SHIFT+ENTER – Excel va închide automat formula cu acolade, așa cum se arată în exemplu:

Dacă nu doriți să utilizați o formulă matrice pentru munca dvs., adăugați un alt INDEX() la formulă și apăsați ENTER, va arăta ca în exemplu:

Cum funcționează aceste formule

Această formulă funcționează în același mod ca formula standard INDEX() MATCH(). Pentru a căuta mai multe condiții, pur și simplu creați mai multe condiții False și Adevărate care reprezintă condițiile individuale corecte și incorecte. Și apoi aceste condiții se aplică tuturor elementelor corespunzătoare ale matricei. Formula convertește argumentele False și Adevărat la 0 și, respectiv, 1 și emite o matrice în care 1 este valorile potrivite care au fost găsite în șir. MATCH() va găsi prima valoare care se potrivește cu 1 și o va transmite formulei INDEX(). Și, la rândul său, va returna valoarea deja dorită în linia specificată din coloana dorită.

O formulă fără matrice depinde de capacitatea lui INDEX() de a le gestiona singur. Al doilea INDEX() din formulă se potrivește cu fals (0), așa că trece întreaga matrice cu acele valori la formula MATCH(). 

Aceasta este o explicație destul de lungă a logicii din spatele acestei formule. Pentru mai multe informații citiți articolul „INDEX MATCH cu mai multe condiții".

MEDIE, MAX și MIN în INDEX și MATCH

Excel are propriile sale funcții speciale pentru a găsi medii, maxime și minime. Dar dacă doriți să obțineți date din celula asociată cu acele valori? În acest caz AVERAGE, MAX și MIN trebuie utilizate împreună cu INDEX și MATCH.

INDEX MATCH și MAX

Pentru a găsi cea mai mare valoare în coloana D și pentru a o afișa în coloana C, utilizați formula: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH și MIN

Pentru a găsi cea mai mică valoare în coloana D și pentru a o afișa în coloana C, utilizați următoarea formulă:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

INDEX DE CĂUTARE și ȘARPE

Pentru a găsi valoarea medie în coloana D și pentru a afișa această valoare în C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

În funcție de modul în care sunt scrise datele dvs., al treilea argument pentru MATCH() este fie 1, 0, fie -1:

• dacă coloanele sunt sortate în ordine crescătoare, setați 1 (atunci formula va calcula valoarea maximă, care este mai mică sau egală cu valoarea medie);
• dacă sortarea este descendentă, atunci -1 (formula va scoate valoarea minimă care este mai mare sau egală cu media);
• dacă matricea de căutare conține o valoare care este exact egală cu media, atunci setați-o la 0. 

 În exemplul nostru, populația este sortată în ordine descrescătoare, așa că punem -1. Și rezultatul este Tokyo, deoarece valoarea populației (13,189) este cea mai apropiată de valoarea medie (000).

VLOOKUP() poate efectua și astfel de calcule, dar numai ca formulă matrice: VLOOKUP cu AVERAGE, MIN și MAX.

INDEX MATCH și ESND/IFERROR

Probabil ați observat deja că, dacă formula nu poate găsi valoarea dorită, aruncă o eroare #N / A. Puteți înlocui mesajul de eroare standard cu ceva mai informativ. De exemplu, setați argumentul în formulă În al XNUMX-lea:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Cu această formulă, dacă introduceți date care nu sunt în tabel, formularul vă va oferi mesajul specificat.

Dacă doriți să prindeți toate erorile, atunci cu excepția În al XNUMX-lea poate fi utilizat DACA EROARE:

=IFEROARE(INDEX(C2:C10,MATCH(F1,A2:A10,0)), „Ceva a mers prost!”)

Dar rețineți că mascarea erorilor în acest fel nu este o idee bună, deoarece erorile standard raportează încălcări ale formulei.

Sperăm că ați găsit util ghidul nostru de utilizare a funcției INDEX MATCH().