Puteți utiliza funcția Excel MATCH pentru a găsi poziția relativă a unei anumite valori într-un interval de celule sau o matrice.
Funcția MATCH este similară cu funcția VLOOKUP, deoarece ambele sunt clasificate în Excel Căutare/Funcții de referință. VLOOKUP caută o anumită valoare într-o coloană și returnează o valoare pe același rând, în timp ce funcția MATCH caută o anumită valoare într-un interval și returnează poziția acelei valori.
Funcția Excel MATCH caută o valoare specificată într-un interval de celule sau într-o matrice și returnează poziția relativă a primei apariții a acelei valori în interval. Funcția MATCH poate fi folosită și pentru a căuta o anumită valoare și a returna valoarea ei corespunzătoare cu ajutorul funcției INDEX (la fel ca Vlookup). Să vedem cum să folosiți funcția Excel MATCH pentru a găsi poziția unei valori de căutare într-un interval de celule.
Funcția Excel MATCH
Funcția MATCH este o funcție încorporată în Excel și este utilizată în principal pentru a localiza poziția relativă a unei valori de căutare într-o coloană sau un rând.
Sintaxa funcției MATCH:
=POTIRE(valoare_căutare,matrice_căutare,[tip_potrivire})Unde:
valoare_căutare – Valoarea pe care doriți să o căutați într-un interval specificat de celule sau într-o matrice. Poate fi o valoare numerică, o valoare text, o valoare logică sau o referință de celulă care are o valoare.
matrice_căutare – Matricele de celule în care căutați o valoare. Trebuie să fie o singură coloană sau un singur rând.
tip_potrivire – Este un parametru opțional care poate fi setat la 0,1 sau -1, iar valoarea implicită este 1.
- 0 caută o potrivire exactă, când nu este găsită, returnează o eroare.
- -1 caută cea mai mică valoare care este mai mare sau egală cu lookup_value atunci când matricea de căutare este în ordine crescătoare.
- 1 caută cea mai mare valoare care este mai mică sau egală cu valoarea look_up atunci când matricea de căutare este în ordine descrescătoare.
Găsiți poziția unei potriviri exacte
Să presupunem că avem următorul set de date în care dorim să găsim poziția unei anumite valori.
În acest tabel, dorim să găsim poziția unui nume de oraș (Memphis) în coloana (A2:A23), așa că folosim această formulă:
=POTIRE(„memphis”, A2:A23,0)Al treilea argument este setat la „0” deoarece dorim să găsim o potrivire exactă a numelui orașului. După cum puteți vedea că numele orașului „memphis” în formulă este cu litere mici, în timp ce în tabel prima literă a numelui orașului este cu litere mari (Memphis). Cu toate acestea, formula este capabilă să găsească poziția valorii specificate în intervalul dat. Se datorează faptului că funcția MATCH nu face distincție între majuscule și minuscule.
Notă: Dacă valoarea_căutare nu este găsită în intervalul de căutare sau dacă specificați un interval de căutare greșit, funcția va returna eroarea #N/A.
Puteți utiliza o referință de celulă în primul argument al funcției în loc de o valoare directă. Formula de mai jos găsește poziția valorii în celula F2 și returnează rezultatul în celula F3.
Găsiți poziția unei potriviri aproximative
Există două moduri în care puteți căuta o potrivire aproximativă sau exactă a valorii de căutare și puteți returna poziția acesteia.
- O modalitate este de a găsi cea mai mică valoare care este mai mare sau egală (următoarea cea mai mare potrivire) cu valoarea specificată. Poate fi realizat prin setarea ultimului argument (match_type) al funcției ca „-1”
- O altă modalitate este cea mai mare valoare care este mai mică sau egală (următoarea cea mai mică potrivire) cu valoarea dată. Se poate realiza prin setarea tipului de potrivire al funcției la „1”
Următorul cel mai mic meci
Dacă funcția nu poate găsi o potrivire exactă cu valoarea specificată când tipul de potrivire este setat la „1”, ea localizează cea mai mare valoare care este puțin mai mică decât valoarea specificată (ceea ce înseamnă următoarea cea mai mică valoare) și returnează poziția acesteia . Pentru ca acest lucru să funcționeze, trebuie să sortați matricea în ordine crescătoare, dacă nu, va avea ca rezultat o eroare.
În exemplu, folosim formula de mai jos pentru a găsi următoarea potrivire cea mai mică:
=POTRIV (F2;D2:D23;1)Când această formulă nu a putut găsi potrivirea exactă pentru valoarea din celula F2, ea indică poziția (16) a următoarei valori mai mici, adică 98.
Următorul Cel mai mare meci
Când tipul de potrivire este setat la „-1” și funcția MATCH nu poate găsi o potrivire exactă, găsește cea mai mică valoare care este mai mare decât valoarea specificată (ceea ce înseamnă următoarea valoare cea mai mare) și returnează poziția acesteia. Matricea de căutare trebuie sortată în ordine descrescătoare pentru această metodă, altfel va returna o eroare.
De exemplu, introduceți următoarea formulă pentru a găsi următoarea potrivire cea mai mare cu valoarea de căutare:
=POITIRE(F2;D2:D23;-1)Această funcție MATCH caută valoarea din F2 (55) în intervalul de căutare D2:D23 și, când nu poate găsi potrivirea exactă, returnează poziția (16) următoarei valori mai mari, adică 58.
Potrivire wildcard
Caracterele metalice pot fi utilizate în funcția MATCH numai atunci când tipul de potrivire este setat la „0” și valoarea de căutare este un șir de text. Există caractere metalice pe care le puteți folosi în funcția MATCH: un asterisc (*) și un semn de întrebare (?).
- Semnul întrebării (?) este folosit pentru a potrivi orice caracter sau literă cu șirul de text.
- asterisc (*) este folosit pentru a potrivi orice număr de caractere cu șirul.
De exemplu, am folosit două caractere metalice „?” în căutarea_valoare (Lo??n) a funcției MATCH pentru a găsi o valoare care se potrivește cu șirul de text cu oricare două caractere (în locurile metacaracterelor). Și funcția returnează poziția relativă a valorii potrivite în celula E5.
=POTIRE(„Lo??n”, A2:A22,0)
Puteți utiliza caracterul metalic (*) în același mod ca și (?), dar un asterisc este folosit pentru a potrivi orice număr de caractere, în timp ce un semn de întrebare este folosit pentru a potrivi orice singur caracter.
De exemplu, dacă utilizați „sp*”, funcția se poate potrivi cu difuzor, viteză sau spielberg etc. Dar dacă funcția găsește valori multiple/duplicate care se potrivesc cu valoarea de căutare, va returna doar poziția primei valori.
În exemplu, am introdus „Kil*o” în argumentul lookup_value. Deci, funcția MATCH() caută un text care conține „Kil” la început, „o” la sfârșit și orice număr de caractere între ele. „Kil*o” se potrivește cu Kilimanjaro în matrice și, prin urmare, funcția returnează poziția relativă a lui Kilimanjaro, care este 16.
INDEX și MATCH
Funcțiile MATCH sunt rareori folosite singure. Ele s-au asociat adesea cu alte funcții pentru a crea formule puternice. Când funcția MATCH este combinată cu funcția INDEX, aceasta poate efectua căutări avansate. Mulți oameni încă preferă să folosească CĂUTARE VOL pentru a căuta o valoare, deoarece este mai simplu, dar POTRIVIREA INDEXULUI este mai flexibil și mai rapid decât CĂUTARE V.
CĂUTARE V poate căuta doar o valoare pe verticală, adică coloane, în timp ce combinația INDEX MATCH poate face atât căutări verticale, cât și orizontale.
Funcția INDEX utilizată pentru a prelua o valoare într-o anumită locație dintr-un tabel sau dintr-un interval. Funcția MATCH returnează poziția relativă a unei valori într-o coloană sau un rând. Atunci când este combinat, MATCH găsește numărul rândului sau coloanei (locația) unei anumite valori, iar funcția INDEX preia o valoare bazată pe acel număr de rând și coloană.
Sintaxa funcției INDEX:
=INDEX(matrice,row_num,[col_num],)Oricum, să vedem cum funcționează INDEX MATCH cu un exemplu.
În exemplul de mai jos, dorim să recuperăm scorul „Quiz2” pentru elevul „Anne”. Pentru a face asta vom folosi formula de mai jos:
=INDEX(B2:F20,POTRIVIRE(H2;A2:A20,0),3)INDEX are nevoie de un număr de rând și de coloană pentru a prelua o valoare. În formula de mai sus, funcția MATCH imbricată găsește numărul de rând (poziția) al valorii „Anne” (H2). Apoi furnizăm acel număr de rând funcției INDEX cu un interval B2:F20 și un număr de coloană (3), pe care îl specificăm. Și funcția INDEX returnează scorul „91”.
Căutare bidirecțională cu INDEX și MATCH
De asemenea, puteți utiliza funcțiile INDEX și MATCH pentru a căuta o valoare într-un interval bidimensional (căutare în două direcții). În exemplul de mai sus, am folosit funcția MATCH pentru a localiza numărul rândului unei valori, dar am introdus manual numărul coloanei. Dar putem găsi atât rând, cât și coloană prin imbricarea a două funcții MATCH, una în argumentul row_num și alta în argumentul column_num al funcției INDEX.
Utilizați această formulă pentru o căutare bidirecțională cu INDEX și MATCH:
=INDEX(A1:F20,POTRIBUIRE(H2,A2:A20,0),POTIRE(H3,A1:F1,0))După cum știm, funcția MATCH poate căuta o valoare atât pe orizontală, cât și pe verticală. În această formulă, a doua funcție MATCH din argumentul colum_num găsește poziția Quiz2 (4) și o furnizează funcției INDEX. Iar INDEX-ul preia scorul.
Acum, știți cum să utilizați funcția Potrivire în Excel.