Excel are o mulțime de funcții - aproximativ 450+ dintre ele.
Și multe dintre acestea sunt pur și simplu minunate. Cantitatea de muncă pe care o puteți face cu câteva formule mă surprinde (chiar și după ce am folosit Excel timp de peste 10 ani).
Și printre toate aceste funcții uimitoare, se evidențiază combo-ul funcțiilor INDEX MATCH.
Sunt un mare fan al combo-ului INDEX MATCH și l-am clarificat de multe ori.
Am scris chiar și un articol despre Index Match Vs VLOOKUP care a stârnit puțină dezbatere (puteți consulta secțiunea de comentarii pentru niște artificii).
Și astăzi, scriu acest articol axat exclusiv pe Index Match pentru a vă arăta câteva scenarii simple și avansate în care puteți folosi această formulă puternică combo și puteți face treaba.
Notă: Există alte formule de căutare în Excel - cum ar fi VLOOKUP și HLOOKUP și acestea sunt excelente. Mulți oameni consideră că VLOOKUP este mai ușor de utilizat (și asta este adevărat și). Cred că INDEX MATCH este o opțiune mai bună în multe cazuri. Dar, din moment ce oamenilor le este greu, se obișnuiește mai puțin. Așa că încerc să îl simplific folosind acest tutorial.Acum, înainte de a vă arăta cum combinația INDEX MATCH schimbă lumea analiștilor și a oamenilor de știință a datelor, permiteți-mi să vă prezint mai întâi părțile individuale - funcțiile INDEX și MATCH.
Funcția INDEX: găsește valoarea bazată pe coordonate
Cel mai simplu mod de a înțelege cum funcționează funcția Index este gândindu-l la un satelit GPS.
De îndată ce spuneți satelitului coordonatele de latitudine și longitudine, acesta va ști exact unde să meargă și să găsească acea locație.
Deci, în ciuda faptului că are un număr uimitor de combinații de lungă durată, satelitul ar ști exact unde să caute.
Am căutat rapid locul de muncă și asta am obținut.
Oricum, suficientă geografie.
La fel ca un satelit are nevoie de coordonate de latitudine și longitudine, funcția INDEX din Excel ar avea nevoie de numărul rândului și coloanei pentru a ști la ce celulă vă referiți.
Și aceasta este funcția Excel INDEX într-o coajă de nucă.
Așadar, permiteți-mi să o definesc în cuvinte simple pentru dvs.
Funcția INDEX va utiliza numărul rândului și numărul coloanei pentru a găsi o celulă în intervalul dat și a returna valoarea din ea.
În sine, INDEX este o funcție foarte simplă, fără utilitate. La urma urmei, în majoritatea cazurilor, este puțin probabil să cunoașteți numerele de rânduri și coloane.
Dar…
Faptul că îl puteți utiliza cu alte funcții (indiciu: MATCH) care pot găsi numărul rândului și numărul coloanei face din INDEX o funcție Excel extrem de puternică.
Mai jos este sintaxa funcției INDEX:
= INDEX (matrice, rând_num, [col_num]) = INDEX (matrice, rând_num, [col_num], [zonă_num])
- matrice - A gama de celule sau o constantă de matrice.
- rând_num - numărul rândului din care urmează să fie preluată valoarea.
- [col_num] - numărul coloanei din care urmează să fie preluată valoarea. Deși acesta este un argument opțional, dar dacă rândul_num nu este furnizat, trebuie dat.
- [area_num] - (Opțional) Dacă argumentul matricei este alcătuit din mai multe intervale, acest număr va fi utilizat pentru a selecta referința din toate intervalele.
Funcția INDEX are 2 sintaxe (doar FYI).
Primul este utilizat în majoritatea cazurilor. Al doilea este utilizat numai în cazuri avansate (cum ar fi efectuarea unei căutări în trei direcții) pe care îl vom acoperi într-unul dintre exemplele de mai jos din acest tutorial.
Dar dacă sunteți nou în această funcție, amintiți-vă prima sintaxă.
Mai jos este un videoclip care explică modul de utilizare a funcției INDEX
Funcția MATCH: găsește poziția pe o valoare de căutare
Revenind la exemplul meu anterior de longitudine și latitudine, MATCH este funcția care poate găsi aceste poziții (în lumea foilor de calcul Excel).
Într-un limbaj simplu, funcția Excel MATCH poate găsi poziția unei celule într-un interval.
Și pe ce bază ar găsi poziția unei celule?
Pe baza valorii de căutare.
De exemplu, dacă aveți o listă așa cum se arată mai jos și doriți să găsiți poziția numelui „Mark” în ea, atunci puteți utiliza funcția MATCH.
Funcția returnează 3, deoarece aceasta este poziția celulei cu numele Mark în ea.
Funcția MATCH începe să caute de sus în jos valoarea de căutare (care este „Mark”) în intervalul specificat (care este A1: A9 în acest exemplu). De îndată ce găsește numele, returnează poziția în acel interval specific.
Mai jos este sintaxa funcției MATCH în Excel.
= MATCH (lookup_value, lookup_array, [match_type])
- valoare_cercare - Valoarea pentru care căutați o potrivire în lookup_array.
- lookup_array - Gama de celule în care căutați valoarea de căutare.
- [match_type] - (Opțional) Aceasta specifică modul în care Excel ar trebui să caute o valoare potrivită. Poate lua trei valori -1, 0 sau 1.
Înțelegerea argumentului tipului de potrivire în funcția MATCH
Trebuie să știți un lucru suplimentar despre funcția MATCH și este despre modul în care trece prin date și găsește poziția celulei.
Al treilea argument al funcției MATCH poate fi 0, 1 sau -1.
Mai jos este o explicație a modului în care funcționează aceste argumente:
- 0 - aceasta va căuta o potrivire exactă a valorii. Dacă se găsește o potrivire exactă, funcția MATCH va reveni la poziția celulei. Altfel, va apărea o eroare.
- 1 - găsește cea mai mare valoare care este mai mică sau egală cu valoarea de căutare. Pentru ca acest lucru să funcționeze, intervalul dvs. de date trebuie să fie sortat în ordine crescătoare.
- -1 - aceasta găsește cea mai mică valoare care este mai mare sau egală cu valoarea de căutare. Pentru ca acest lucru să funcționeze, intervalul dvs. de date trebuie să fie sortat în ordine descrescătoare.
Mai jos este un videoclip care explică modul de utilizare a funcției MATCH (împreună cu argumentul tipului de potrivire)
Pentru a rezuma și a le pune în cuvinte simple:
- INDEX are nevoie de poziția celulei (numărul rândului și coloanei) și oferă valoarea celulei.
- MATCH găsește poziția utilizând o valoare de căutare.
Să le combinăm pentru a crea o centrală (INDEX + MATCH)
Acum, că aveți o înțelegere de bază despre modul în care funcțiile INDEX și MATCH funcționează individual, să le combinăm pe acestea două și să aflăm despre toate lucrurile minunate pe care le poate face.
Pentru a înțelege mai bine acest lucru, am câteva exemple care folosesc combinația INDEX MATCH.
Voi începe cu un exemplu simplu și vă voi arăta și câteva cazuri de utilizare avansate.
Faceți clic aici pentru a descărca fișierul de exemplu
Exemplul 1: o căutare simplă folosind combo-ul INDEX MATCH
Să facem o căutare simplă cu INDEX / MATCH.
Mai jos este un tabel în care am notele pentru zece elevi.
Din acest tabel, vreau să găsesc semnele pentru Jim.
Mai jos este formula care poate face cu ușurință acest lucru:
= INDEX ($ A $ 2: $ B $ 11, MATCH („Jim”, $ A $ 2: $ A $ 11,0), 2)
Acum, dacă vă gândiți că acest lucru se poate face cu ușurință utilizând o funcție VLOOKUP, aveți dreptate! Aceasta nu este cea mai bună utilizare a genialității INDEX MATCH. În ciuda faptului că sunt fan al INDEX MATCH, este puțin mai dificil decât VLOOKUP. Dacă preluați date dintr-o coloană din dreapta este tot ce doriți să faceți, vă recomand să utilizați VLOOKUP.
Motivul pentru care am arătat acest exemplu, care poate fi realizat cu ușurință cu VLOOKUP, este să vă arăt cum funcționează INDEX MATCH într-un cadru simplu.
Acum permiteți-mi să arăt un beneficiu al INDEX MATCH.
Să presupunem că aveți aceleași date, dar în loc să le aveți în coloane, le aveți în rânduri (așa cum se arată mai jos).
Știi ce, poți folosi în continuare combo-ul INDEX MATCH pentru a obține notele lui Jim.
Mai jos este formula care vă va oferi rezultatul:
= INDEX ($ B $ 1: $ K $ 2,2, MATCH („Jim”, $ B $ 1: $ K $ 1,0))
Rețineți că trebuie să schimbați intervalul și să comutați părțile rândului / coloanei pentru a face această formulă să funcționeze și pentru datele orizontale.
Acest lucru nu se poate face cu VLOOKUP, dar puteți face acest lucru cu ușurință cu HLOOKUP.
Combinația INDEX MATCH poate gestiona cu ușurință datele orizontale și verticale.Faceți clic aici pentru a descărca fișierul de exemplu
Exemplul 2: Căutați în stânga
Este mai frecvent decât crezi.
De multe ori, vi se poate cere să preluați datele dintr-o coloană aflată în stânga coloanei care are valoarea de căutare.
Ceva așa cum se arată mai jos:
Pentru a afla vânzările lui Michael, va trebui să faceți o căutare în stânga.
Dacă te gândești la VLOOKUP, lasă-mă să te opresc chiar acolo.
VLOOKUP nu este făcut să caute și să aducă valorile din stânga.
Puteți face acest lucru folosind VLOOKUP?
Da, poti!
Dar asta se poate transforma într-o formulă lungă și urâtă.
Deci, dacă doriți să efectuați o căutare și să preluați date din coloanele din stânga, este mai bine să utilizați combo-ul INDEX MATCH.
Mai jos este formula care va obține numărul de vânzări al lui Michael:
= INDEX ($ A $ 2: $ C $ 11, MATCH („Michael”, C2: C11,0), 2)
Exemplul 3: căutare bidirecțională
Până acum, am văzut exemplele în care am vrut să preluăm datele din coloana adiacentă coloanei care are valoarea de căutare.
Dar în viața reală, datele se întind adesea prin mai multe coloane.
INDEX MATCH poate gestiona cu ușurință o căutare în două direcții.
Mai jos este un set de date cu notele elevului la trei subiecte diferite.
Dacă doriți să obțineți rapid notele unui elev la toate cele trei discipline, puteți face acest lucru cu INDEX MATCH.
Formula de mai jos vă va da notele pentru Jim pentru toate cele trei subiecte (copiați și lipiți într-o singură celulă și trageți pentru a umple alte celule sau copiați și lipiți pe alte celule).
= INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 3, $ A $ 2: $ A $ 11,0), MATCH (G $ 2, $ B $ 1: $ D $ 1,0))
Permiteți-mi să explic rapid această formulă.
Formula INDEX utilizează B2: D11 ca interval.
Primul MATCH folosește numele (Jim în celula F3) și preia poziția acestuia în coloana de nume (A2: A11). Acesta devine numărul rândului din care trebuie preluate datele.
A doua formulă MATCH folosește numele subiectului (în celula G2) pentru a obține poziția acelui nume de subiect specific în B1: D1. De exemplu, Matematica este 1, Fizica este 2 și Chimia este 3.
Deoarece aceste poziții MATCH sunt introduse în funcția INDEX, acesta returnează scorul pe baza numelui studentului și a subiectului.
Această formulă este dinamică, ceea ce înseamnă că, dacă schimbați numele studentului sau numele subiectului, acesta ar funcționa și ar prelua datele corecte.
Un lucru extraordinar despre utilizarea INDEX / MATCH este că, chiar dacă schimbați numele subiectelor, acesta va continua să vă ofere rezultatul corect.Exemplul 4: Valoarea de căutare din mai multe coloane / criterii
Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să obțineți semnele pentru „Mark Long”.
Deoarece datele se află în două coloane, nu pot căuta pentru Mark și nu pot obține datele.
Dacă o fac așa, voi obține datele de notare pentru Mark Frost și nu pentru Mark Long (deoarece funcția MATCH îmi va oferi rezultatul pentru MARK-ul pe care îl întâlnește).
O modalitate de a face acest lucru este să creați o coloană de ajutor și să combinați numele. Odată ce aveți coloana de asistență, puteți utiliza VLOOKUP și puteți obține datele marcajelor.
Dacă sunteți interesat să aflați cum să faceți acest lucru, citiți acest tutorial despre utilizarea VLOOKUP cu mai multe criterii.
Dar cu combo-ul INDEX / MATCH, nu aveți nevoie de o coloană de ajutor. Puteți crea o formulă care gestionează mai multe criterii în formula însăși.
Formula de mai jos va da rezultatul.
= INDEX ($ C $ 2: $ C $ 11, MATCH ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))
Permiteți-mi să explic rapid ce face această formulă.
Partea MATCH a formulei combină valoarea de căutare (Mark și Long), precum și întreaga matrice de căutare. Când $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11 este folosit ca matrice de căutare, acesta verifică de fapt valoarea căutării în raport cu șirul combinat de nume și prenume (separate prin simbolul țevii).
Acest lucru vă asigură că obțineți rezultatul corect fără a utiliza coloane de ajutor.
Puteți face acest tip de căutare (unde există mai multe coloane / criterii) și cu VLOOKUP, dar trebuie să utilizați o coloană de ajutor. Combo-ul INDEX MATCH face ușor acest lucru fără coloane de ajutor.Exemplul 5: obțineți valori din întreaga linie / coloană
În exemplele de mai sus, am folosit funcția INDEX pentru a obține valoare de la o celulă specifică. Furnizați numărul rândului și coloanei, iar acesta returnează valoarea în celula respectivă.
Dar poți face mai mult.
De asemenea, puteți utiliza funcția INDEX pentru a obține valorile dintr-un întreg rând sau coloană.
Și cum poate fi util acest lucru!
Să presupunem că doriți să cunoașteți scorul total al lui Jim la toate cele trei subiecte.
Puteți utiliza funcția INDEX pentru a obține mai întâi toate semnele lui Jim și apoi utilizați funcția SUM pentru a obține un total.
Să vedem cum să facem acest lucru.
Mai jos am scorurile tuturor studenților la trei discipline.
Formula de mai jos îmi va da scorul total al lui Jim la toate cele trei subiecte.
= SUMĂ (INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))
Permiteți-mi să vă explic cum funcționează această formulă.
Trucul aici este să folosiți 0 ca număr de coloană.
Când utilizați 0 ca număr de coloană în funcția INDEX, acesta va returna toate valorile rândului. În mod similar, dacă utilizați 0 ca număr de rând, acesta va returna toate valorile din coloană.
Deci, partea de mai jos a formulei returnează o serie de valori - {97, 70, 73}
INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)
Dacă introduceți doar această formulă de mai sus într-o celulă din Excel și apăsați Enter, veți vedea un #VALUE! eroare. Acest lucru se datorează faptului că nu returnează o singură valoare, ci o serie de valori.
Dar nu vă faceți griji, gama de valori este încă acolo. Puteți verifica acest lucru selectând formula și apăsați tasta F9. Vă va arăta rezultatul formulei care în acest caz este o matrice de trei valori - {97, 70, 73}
Acum, dacă înfășurați această formulă INDEX în funcția SUM, aceasta vă va oferi suma tuturor notelor marcate de Jim.
Puteți utiliza, de asemenea, același lucru pentru a obține cele mai mari, mai mici și medii note de Jim.
La fel cum am făcut acest lucru pentru un student, puteți face acest lucru și pentru un subiect. De exemplu, dacă doriți scorul mediu la un subiect, puteți păstra numărul rândului ca 0 în formula INDEX și vă va oferi toate valorile coloanei acelui subiect.
Faceți clic aici pentru a descărca fișierul de exemplu
Exemplul 6: Găsiți nota elevului (Tehnica de potrivire aproximativă)
Până acum, am folosit formula MATCH pentru a obține potrivirea exactă a valorii de căutare.
Dar îl puteți folosi și pentru a face o potrivire aproximativă.
Acum, ce naiba este meciul aproximativ?
Lasă-mă să explic.
Când căutați lucruri precum nume sau ID-uri, căutați o potrivire exactă. Dar, uneori, trebuie să cunoașteți intervalul în care se află valorile dvs. de căutare. Acesta este de obicei cazul numerelor.
De exemplu, în calitate de profesor de clasă, poate doriți să știți care este nota fiecărui elev la o disciplină, iar nota se decide pe baza scorului.
Mai jos este un exemplu, în care vreau nota pentru toți elevii, iar notarea se decide pe baza tabelului din dreapta.
Deci, dacă un elev obține mai puțin de 33, nota este F și dacă el / ea primește mai puțin de 50, dar mai mult de 33, este E și așa mai departe.
Mai jos este formula care va face acest lucru.
= INDEX ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8,1), 1)
Permiteți-mi să vă explic cum funcționează această formulă.
În funcția MATCH, am folosit 1 ca argument [match_type]. Acest argument va returna cea mai mare valoare care este mai mică sau egală cu valoarea de căutare.
Aceasta înseamnă că formula MATCH trece prin intervalul de note și, de îndată ce găsește un interval de note care este egal sau mai mic decât valoarea marcajelor de căutare, se va opri acolo și își va întoarce poziția.
Deci, dacă valoarea marcajului de căutare este 20, funcția MATCH ar reveni 1 și dacă este 85, ar întoarce 5.
Iar funcția INDEX folosește această valoare de poziție pentru a obține nota.
IMPORTANT: Pentru ca acest lucru să funcționeze, datele dvs. trebuie să fie sortate în ordine crescătoare. În caz contrar, puteți obține rezultate greșite.Rețineți că cele de mai sus se pot face și folosind formula VLOOKUP de mai jos:
= VLOOKUP (B2, $ E $ 3: $ F $ 8,2, ADEVĂRAT)
Dar funcția MATCH poate face un pas mai departe atunci când vine vorba de potrivirea aproximativă.
De asemenea, puteți avea date descrescătoare și puteți utiliza combo-ul INDEX MATCH pentru a găsi rezultatul. De exemplu, dacă schimb ordinea tabelului de note (așa cum se arată mai jos), pot găsi în continuare notele elevilor.
Pentru a face acest lucru, tot ce trebuie să fac este să schimb argumentul [match_type] la -1.
Mai jos este formula pe care am folosit-o:
= INDEX ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8, -1), 1)VLOOKUP poate face, de asemenea, o potrivire aproximativă, dar numai atunci când datele sunt sortate în ordine crescătoare (dar nu funcționează dacă datele sunt sortate în ordine descrescătoare).
Exemplul 7: Căutări sensibile la majuscule
Până în prezent toate căutările pe care le-am făcut nu au fost sensibile la majuscule.
Aceasta înseamnă că indiferent dacă valoarea de căutare a fost Jim sau JIM sau jim, nu a contat. Veți obține același rezultat.
Dar dacă doriți ca căutarea să fie sensibilă la majuscule și minuscule.
Acesta este, de obicei, cazul în care aveți seturi de date mari și o posibilitate de repetare sau nume / id distincte (singura diferență fiind cazul)
De exemplu, să presupunem că am următorul set de date de studenți în care există doi studenți cu numele Jim (singura diferență fiind că unul este introdus ca Jim și altul ca Jim).
Rețineți că există doi studenți cu același nume - Jim (celula A2 și A5).
Deoarece o căutare normală nu ar funcționa, trebuie să efectuați o căutare sensibilă la majuscule.
Mai jos este formula care vă va oferi rezultatul corect. Deoarece aceasta este o formulă matrice, trebuie să utilizați Control + Shift + Enter.
= INDEX ($ B $ 2: $ B $ 11, MATCH (ADEVĂRAT, EXACT (D3, A2: A11), 0), 1)
Permiteți-mi să vă explic cum funcționează această formulă.
Funcția EXACT verifică o potrivire exactă a valorii de căutare (care este „jim” în acest caz). Trece prin toate numele și returnează FALS dacă nu este un meci și ADEVĂRAT dacă este un meci.
Deci, ieșirea funcției EXACT din acest exemplu este - {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Rețineți că există un singur ADEVĂRAT, care este atunci când funcția EXACT a găsit o potrivire perfectă.
Funcția MATCH găsește apoi poziția TRUE în matricea returnată de funcția EXACT, care este 4 în acest exemplu.
Odată ce avem poziția, funcția INDEX o folosește pentru a găsi semnele.
Exemplul 8: Găsiți cea mai apropiată potrivire
Haideți să ne avansăm puțin acum.
Să presupunem că aveți un set de date în care doriți să găsiți persoana care are experiența de lucru cea mai apropiată de experiența necesară (menționată în celula D2).
În timp ce formulele de căutare nu sunt făcute pentru a face acest lucru, îl puteți combina cu alte funcții (cum ar fi MIN și ABS) pentru a face acest lucru.
Mai jos este formula care va găsi persoana cu experiența cea mai apropiată de cea necesară și va returna numele persoanei. Rețineți că experiența trebuie să fie cea mai apropiată (care poate fi fie mai mică, fie mai mare).
= INDEX ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))
Deoarece aceasta este o formulă matrice, trebuie să utilizați Control + Shift + Enter.
Trucul din această formulă este de a schimba valoarea de căutare și matricea de căutare pentru a găsi diferența minimă de experiență în valorile necesare și reale.
Înainte de a explica formula, să înțelegem cum ați face-o manual.
Veți parcurge fiecare celulă din coloana B și veți găsi diferența în experiența dintre ceea ce este necesar și cel pe care îl are o persoană. Odată ce ai toate diferențele, îl vei găsi pe cel care este minim și vei prelua numele acelei persoane.
Exact asta facem cu această formulă.
Lasă-mă să explic.
Valoarea de căutare din formula MATCH este MIN (ABS (D2-B2: B15)).
Această parte vă oferă diferența minimă între experiența dată (care este de 2,5 ani) și toate celelalte experiențe. În acest exemplu, returnează 0,3
Rețineți că am folosit ABS pentru a mă asigura că caut cel mai apropiat (care poate fi mai mult sau mai puțin decât experiența dată).
Acum, această valoare minimă devine valoarea noastră de căutare.
Matricea de căutare din funcția MATCH este ABS (D2- $ B $ 2: $ B $ 15).
Aceasta ne oferă o serie de numere din care s-a scăzut 2,5 (experiența necesară).
Deci, acum avem o valoare de căutare (0,3) și o matrice de căutare ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})
Funcția MATCH găsește poziția 0.3 în această matrice, care este și poziția numelui persoanei care are cea mai apropiată experiență.
Acest număr de poziție este apoi utilizat de funcția INDEX pentru a returna numele persoanei.
Citiți în legătură: Găsiți cea mai apropiată potrivire în Excel (exemple folosind formule de căutare)
Faceți clic aici pentru a descărca fișierul de exemplu
Exemplul 9: Utilizați INDEX MATCH cu caractere wildcard
Dacă doriți să căutați o valoare atunci când există o potrivire parțială, atunci trebuie să utilizați caractere wildcard.
De exemplu, mai jos este un set de date cu numele companiei și capitalizările lor de piață și doriți să obțineți capitalizarea pieței. date pentru cele trei companii din dreapta.
Deoarece acestea nu sunt potriviri exacte, nu puteți efectua o căutare regulată în acest caz.
Dar puteți obține în continuare datele corecte folosind un asterisc (*), care este un caracter wildcard.
Mai jos este formula care vă va oferi datele prin potrivirea numelor companiei din coloana principală și preluarea cifrei de capitalizare a pieței pentru aceasta.
= INDEX ($ B $ 2: $ B $ 10, MATCH (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)
Permiteți-mi să vă explic cum funcționează această formulă.
Deoarece nu există o potrivire exactă a valorilor de căutare, am folosit-o D2 și „*” ca valoare de căutare în funcția MATCH.
Un asterisc este un caracter wildcard care reprezintă orice număr de caractere. Aceasta înseamnă că Apple * în formulă ar însemna orice șir de text care începe cu cuvântul Apple și poate avea orice număr de caractere după acesta.
Deci când Măr* este folosit ca valoare de căutare și formula MATCH o caută în coloana A, returnează poziția „Apple Inc.”, deoarece începe cu cuvântul Apple.
De asemenea, puteți utiliza caractere wildcard pentru a găsi șiruri de text în care se află valoarea de căutare. De exemplu, dacă utilizați * Apple * ca valoare de căutare, acesta va găsi orice șir care are cuvântul mere oriunde în el.
Notă: Această tehnică funcționează bine atunci când aveți doar o singură instanță de potrivire. Dar dacă aveți mai multe instanțe de potrivire (de exemplu Apple Inc și Apple Corporation, atunci funcția MATCH va returna doar poziția primei instanțe de potrivire.
Exemplul 10: căutare în trei direcții
Aceasta este o utilizare avansată a INDEX MATCH, dar o voi acoperi în continuare pentru a vă arăta puterea acestei combinații.
Amintiți-vă că am spus că funcția INDEX are două sintaxe:
= INDEX (matrice, rând_num, [col_num]) = INDEX (matrice, rând_num, [col_num], [zonă_num])
Până în prezent, în toate exemplele noastre, am folosit doar primul.
Dar pentru o căutare în trei direcții, trebuie să utilizați a doua sintaxă.
Permiteți-mi să vă explic mai întâi ce înseamnă un aspect cu trei căi.
Într-o căutare bidirecțională, folosim formula INDEX MATCH pentru a obține notele atunci când avem numele elevului și numele subiectului. De exemplu, preluarea semnelor lui Jim în Matematică este o căutare în două direcții.
Un aspect în trei direcții i-ar adăuga o altă dimensiune. De exemplu, să presupunem că aveți un set de date așa cum se arată mai jos și doriți să cunoașteți scorul lui Jim în matematică la examenul intermediar, atunci aceasta ar fi o căutare în trei direcții.
Mai jos este formula care va da rezultatul.
= INDEX (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0), MATCH (G $ 4 , $ B $ 2: $ D $ 2,0), (IF (G $ 3 = "Test de unitate", 1, IF (G $ 3 = "Termen mediu", 2,3))))
Formula de mai sus a verificat trei lucruri - numele studentului, subiectul și examenul. După ce găsește valoarea corectă, o returnează în celulă.
Permiteți-mi să explic cum funcționează această formulă prin descompunerea formulei în părți.
- matrice - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): În loc să folosesc o singură matrice, în acest caz, am folosit trei tablouri în paranteză.
- row_num - MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0): Funcția MATCH este utilizată pentru a găsi poziția numelui studentului în celula $ F $ 5 din lista numelui studentului.
- col_num - MATCH (G $ 4, $ B $ 2: $ D $ 2,0): Funcția MATCH este utilizată pentru a găsi poziția numelui subiectului în celula $ B $ 2 din lista numelui subiectului.
- [area_num] - IF (G $ 3 = ”Test de unitate”, 1, IF (G $ 3 = ”Termen mediu”, 2,3)): Valoarea numărului de zonă indică funcției INDEX care dintre cele trei matrice să folosească pentru a prelua valoarea. Dacă examenul este Unit Term, funcția IF va returna 1 și funcția INDEX ar folosi prima matrice pentru a prelua valoarea. Dacă examenul este pe termen mediu, formula IF va întoarce 2, altfel va întoarce 3.
Acesta este un exemplu avansat de utilizare a INDEX MATCH și este puțin probabil să găsiți o situație când trebuie să utilizați acest lucru. Dar este totuși bine să știți ce pot face formulele Excel.
Faceți clic aici pentru a descărca fișierul de exemplu
De ce este INDEX / MATCH mai bun decât VLOOKUP?
Sau este?
Da, este - în majoritatea cazurilor.
Îmi voi prezenta cazul peste puțin timp.
Dar, înainte de a face asta, permiteți-mi să spun asta - VLOOKUP este o funcție extrem de utilă și îmi place. Poate face o mulțime de lucruri în Excel și îl folosesc din când în când și eu. Acestea fiind spuse, nu înseamnă că nu poate exista nimic mai bun, iar INDEX / MATCH (cu mai multă flexibilitate și funcționalități) este mai bun.
Deci, dacă doriți să efectuați o căutare de bază, este mai bine să folosiți VLOOKUP.
INDEX / MATCH este VLOOKUP pe steroizi. Și odată ce ați învățat INDEX / MATCH, s-ar putea să preferați întotdeauna utilizarea acestuia (mai ales datorită flexibilității pe care o are).
Fără a-l întinde prea mult, permiteți-mi să vă dau rapid motivele pentru care INDEX / MATCH este mai bun decât VLOOKUP.
INDEX / MATCH poate privi în stânga (precum și în dreapta) valorii de căutare
L-am acoperit într-unul dintre exemplele de mai sus.
Dacă aveți o valoare care se află în partea stângă a valorii de căutare, nu puteți face asta cu VLOOKUP
Cel puțin nu doar cu VLOOKUP.
Da, puteți combina VLOOKUP cu alte formule și faceți acest lucru, dar devine complicat și dezordonat.
INDEX / MATCH, pe de altă parte, este făcut pentru a căuta peste tot (fie că este stânga, dreapta, sus sau jos)
INDEX / MATCH poate funcționa cu intervale verticale și orizontale
Din nou, cu respect deplin față de VLOOKUP, nu este făcut să facă acest lucru.
La urma urmei, V în VLOOKUP reprezintă verticală.
VLOOKUP poate trece doar prin date verticale, în timp ce INDEX / MATCH poate trece prin date pe verticală și orizontală.
Desigur, există funcția HLOOKUP pentru a avea grijă de căutarea orizontală, dar nu este VLOOKUP atunci … nu?
Îmi place faptul că combo-ul INDEX MATCH este suficient de flexibil pentru a lucra atât cu date verticale cât și orizontale.
VLOOKUP nu poate funcționa cu date descendente
Când vine vorba de meciul aproximativ, VLOOKUP și INDEX / MATCH sunt la același nivel.
Dar INDEX MATCH ia ideea, deoarece poate gestiona și date în ordine descrescătoare.
Arăt acest lucru într-unul dintre exemplele din acest tutorial unde trebuie să găsim nota elevilor pe baza tabelului de notare. Dacă tabelul este sortat în ordine descrescătoare, VLOOKUP nu ar funcționa (dar INDEX MATCH ar funcționa).
INDEX / MATCH poate fi ușor mai rapid
Voi fi sincer. Nu am efectuat acest test eu însumi.
Mă bazez pe înțelepciunea unui maestru Excel - Charley Kyd.
Diferența de viteză în VLOOKUP și INDEX / MATCH este greu de observat atunci când aveți seturi de date mici. Dar dacă aveți mii de rânduri și multe coloane, acesta poate fi un factor decisiv.
În articolul său, Charley Kyd afirmă:
„În cel mai rău caz, metoda INDEX-MATCH este la fel de rapidă ca VLOOKUP; în cel mai bun caz, este mult mai rapid ”.
INDEX / MATCH este independent de poziția reală a coloanei
Dacă aveți un set de date așa cum se arată mai jos pe măsură ce obțineți scorul lui Jim în fizică, puteți face acest lucru folosind VLOOKUP.
Și pentru a face acest lucru, puteți specifica numărul coloanei ca 3 în VLOOKUP.
Totul este bine.
Dar dacă șterg coloana Matematică.
În acest caz, formula VLOOKUP se va sparge.
De ce? - Deoarece a fost codificat cu greu să folosesc a treia coloană, iar când șterg o coloană între ele, a treia coloană devine a doua coloană.
Utilizarea INDEX / MATCH, în acest caz, este mai bună, deoarece puteți face numărul coloanei dinamic utilizând MATCH. Deci, în loc de un număr de coloană, verifică numele subiectului și îl folosește pentru a returna numărul coloanei.
Cu siguranță puteți face acest lucru combinând VLOOKUP cu MATCH, dar dacă combinați oricum, de ce să nu o faceți cu INDEX, care este mult mai flexibil.
Când utilizați INDEX / MATCH, puteți introduce / șterge în siguranță coloane în setul de date.
În ciuda tuturor acestor factori, există un motiv pentru care VLOOKUP este atât de popular.
Și este un motiv important.
VLOOKUP este mai ușor de utilizat
VLOOKUP ia doar patru argumente. Dacă vă puteți înfășura capul în jurul acestor patru, sunteți bine să plecați.
Și, deoarece majoritatea cazurilor de căutare de bază sunt gestionate și de VLOOKUP, a devenit rapid cea mai populară funcție Excel.
Eu îl numesc funcțiile Regelui Excel.
INDEX / MATCH, pe de altă parte, este puțin mai dificil de utilizat. Este posibil să primiți un blocaj dacă începeți să-l utilizați, dar pentru un începător, VLOOKUP este mult mai ușor de explicat și de învățat.
Și acesta nu este un joc cu sumă zero.
Deci, dacă sunteți nou în lumea căutării și nu știți cum să folosiți VLOOKUP, învățați mai bine acest lucru.
Am un ghid detaliat despre utilizarea VLOOKUP în Excel (cu o mulțime de exemple)
Intenția mea din acest articol nu este de a lansa două funcții minunate una împotriva celeilalte. Am vrut să vă arăt puterea combo-ului INDEX MATCH și toate lucrurile minunate pe care le poate face.
Sper că ați găsit util acest articol.
Spuneți-mi părerile dvs. în secțiunea de comentarii și, în cazul în care găsiți vreo greșeală în acest tutorial, vă rugăm să ne anunțați.
