Introducere în funcția VLOOKUP
Funcția VLOOKUP în Excel este fără îndoială, este cea mai utilizată și vorbită funcție. În orice interviu de muncă care necesită abilități Excel, aceasta este cea mai adresată întrebare, dacă știi cum să folosești funcția VLOOKUP.
Funcția VLOOKUP face parte din categoria de căutare a funcțiilor Excel. V în VLOOKUP înseamnă verticală. Funcția este utilizată pentru căutarea datelor structurate pe verticală. Pentru căutare orizontală, există o funcție numită HLOOKUP.
Funcția Excel VLOOKUP caută prima potrivire a unei valori date numită valoare de căutare, într-un tabel de date și returnează valoarea din indexul coloanei date. Meciul poate fi aproximativ sau exact.
Deși VLOOKUP este ușor de utilizat, are propriile limite și fundaluri. Vom discuta despre toate, inclusiv punctele tari și punctele slabe ale funcției.
Sintaxă:
= VLOOKUP (lookup_value, table_array, col_index_number, [range_lookup])
Ce este valoarea de căutare
Lookup_value: Este valoarea pe care doriți să o căutați într-o matrice de tabele.
De exemplu, dacă sunteți în căutarea listei nr. 110 în tabelul elevului, apoi este 110 este valoarea de căutare.
Table_array: Tabelul din care doriți să căutați valoarea de căutare.
De exemplu, dacă căutați 110 în matricea de tabele B5: E17. Atunci B5: E17 este matricea ta de masă.
col_index_number: Numărul coloanei din tabelul Array din care doriți să obțineți rezultate.
De exemplu, dacă este în tabel B5: E17 doriți să obțineți valoare din coloana D. atunci indexul coloanei dvs. va fi 3 (numărând de la B la D (B, C, D)).
[range_lookup]: FALS dacă doriți să căutați valoarea exactă, ADEVĂRAT dacă doriți o potrivire aproximativă.
Cum se folosește funcția VLOOKUP
Când aveți date structurate și doriți să căutați date în acele date, funcția VLOOKUP este soluția.
Moduri de potrivire VLOOKUP
VLOOKUP are două moduri de potrivire, potrivire aproximativă și potrivire exactă. În mod implicit, VLOOKUP efectuează o potrivire aproximativă. Dar dacă doriți să forțați funcția VLOOKUP să facă o potrivire exactă, puteți face și asta. De cele mai multe ori, eu și eu cred cu tărie și pe alții, încercăm să facem o potrivire exactă folosind funcția VLOOKUP. Nu înțeleg de ce dezvoltatorii Excel consideră că utilizatorii ar dori să folosească mai ales potrivirea aproximativă.
1. VLOOKUP Meci aproximativ
= VLOOKUP (lookup_value, table_array, col_index_number, 1)
În VLOOKUP potrivire aproximativă, dacă nu se găsește o valoare, atunci ultima valoare care este mai mică decât valoarea de căutare este potrivită și se returnează valoarea din indexul coloanei date.
VLOOKUP implicit se potrivește, dacă omitem variabila de căutare a intervalului. O potrivire aproximativă este utilă atunci când doriți să faceți o potrivire aproximativă și când aveți matricea sortată în ordine crescătoare.
Vlookup caută valoarea și dacă nu găsește valoarea în matricea de tabele, atunci se potrivește cu valoarea care este mai mică decât acea valoare din matricea de tabele. Să-l înțelegem printr-un exemplu.
Exemplul 1
Avem aici o listă de studenți. Fiecare elev a obținut câteva note la un test. Acum vreau să dau nota în funcție de notele lor.
Orice student care a obținut un scor mai mic de 40 ar trebui să fie notat F, studentul care a obținut un scor între 40 și 60 ar trebui să fie notat C, și așa mai departe, așa cum se arată în imaginea de mai jos.
Am scrie această formulă VLOOKUP în E2 și am trage-o în jos.
= VLOOKUP (D2, $ H $ 2: $ I $ 6,2, ADEVĂRAT)
Cum functioneaza?
În exemplul de mai sus valorile noastre de căutare sunt în coloana D, începând de la D2. Tabela Array este H2: I6 (rețineți că este sortată în ordine crescătoare și absolută). Coloana din care obținem date este 2. Și tipul de potrivire l-am definit aproximativ trecând TRUE (nimic și 1 nu vor însemna același).
Deci, să examinăm prima valoare de căutare D2 care conține 40.
- VLOOKUP caută 40 în prima coloană (H) a tabloului H2: I6.
- Se găsește 40 la a doua poziție în celula H3.
- Acum se trece la a doua coloană de la H3 la I3 și returnează D.
În acest caz, vlookup a găsit potrivirea exactă. Să vedem următorul caz.
A doua valoare de căutare D3 care conține 36.
- VLOOKUP caută 36 în prima coloană a tabloului H2: I6.
- VLOOKUP nu a putut găsi 36 nicăieri în prima coloană.
- Deoarece VLOOKUP nu a putut găsi 36, se potrivește cu ultima valoare găsită, care este mai mică decât valoarea de căutare.
- Prima valoare mai mică de 36 este 0, deci returnează F care este legat de 0.
Acest lucru se întâmplă pentru fiecare caz aici. Pentru 92, ultima valoare care este mai mică decât 92 este 90. Prin urmare, A este returnată pentru potrivirea aproximativă.
2. VLOOKUP Exact Match
Această formă cea mai utilizată de VLOOKUP și poate fi și cea mai utilă. Când doriți să căutați valoarea exactă în prima coloană a matricei de tabele, utilizați potrivirea exactă. De exemplu, dacă efectuați o căutare dintr-un ID, cel mai mult ați dori să doriți o potrivire exactă în loc de orice valoare care este mai mică decât această valoare.
Pentru a forța VLOOKUP la o potrivire exactă în Excel, trecem 0 sau FALSE ca parametru range_lookup.
= VLOOKUP (lookup_value, table_array, col_index_number, 0)
Dacă valoarea nu este găsită în prima coloană a matricei de tabele, formula va întoarce # N / A eroare.
Să vedem un exemplu.
Exemplul 2
În acest exemplu, vreau să scriu doar rulează nr de student în A2 și în B2 vreau să aduc Numele studentului si in C2 a lui Grad. Simplu. Matricea de tabele este B5: D17. De ce?
Deci, pentru a face acest lucru, scrieți aceste două formule în celula din celula B2 și respectiv C2.
= VLOOKUP (A2, $ B $ 5: $ E $ 17,2,0)
= VLOOKUP (A2, $ B $ 5: $ E $ 17,3,0)
Acum, indiferent de ID pe care îl veți scrie în celula A2, funcția VLOOKUP va căuta potrivirea exactă în coloana B și va returna valoarea din col_index dat.
Cum funcționează Exact Match VLOOKUP?
Exact Match VLOOKUP în Excel este simplu. Se caută valoarea de căutare în prima coloană a table_array dată și returnează valoarea din valoarea din numărul column_index dat. Dacă valoarea nu este găsită, atunci VLOOKUP returnează o eroare # N / A.
Cele mai bune utilizări ale funcției VLOOKUP
Vlookup are multe utilizări. Voi discuta despre cele mai utile cazuri de utilizare.
3. Folosiți Excel VLOOKUP pentru notarea funcției IF imbricate complexe
Utilizați potrivirea aproximativă VLOOKUP pentru a înlocui funcția IF imbricată. În exemplul 1 am văzut cum putem folosi vlookup pentru a nota elevii în funcție de notele lor.
Potrivirea aproximativă VLOOKUP este mai rapidă decât imbricată dacă, deoarece folosește căutare binară intern.
4. Utilizați VLOOKUP pentru a prelua date
Această sarcină cea mai comună care poate fi realizată cu ușurință utilizând funcția VLOOKUP în Excel. În exemplul 2 am făcut acest lucru pentru a prelua datele studenților folosind lista lor nr.
5. Verificați dacă o valoare este într-o înregistrare folosind Vlookup.
VLOOKUP poate fi folosit pentru a verifica dacă o valoare este sau nu într-o listă.
În exemplul 2 când scriem 152, returnează # N / A eroare. Dacă VLOOKUP returnează # N / A, înseamnă că nu a găsit valoarea dată nicăieri în datele date.
Am pregătit un scurt articol despre aceasta Puteți citi aici.
Utilizarea unei formule VLOOKUP pentru a verifica dacă există o valoare
Obținerea automată a indexului coloanei
Aparțin acelui grup de oameni, care nu doresc să repete aceeași sarcină din nou și din nou. Așadar, schimbarea numărului col_index din nou și din nou mă enervează și o evit mereu. De fapt, evit orice fel de editare în formulele mele odată ce am scris-o, cu excepția cazului în care și până când nu există altă opțiune.
Indexul coloanei poate fi calculat automat în Excel. Există mai multe moduri de a face acest lucru. Să le vedem aici.
6. Utilizați funcția VLOOKUP cu funcția MATCH
Deci, după cum știm că funcția MATCH returnează indexul găsit de valoare potrivită.
Deci, dacă avem exact aceleași titluri la locația de căutare ca datele sursă, atunci putem folosi pentru a obține col_index. Cum? Să vedem…
În datele de mai jos, dorim să scriem doar numărul ruloului în G2 și dorim să preluăm numele, notele și nota elevului în H2, indiferent de titlu pe care îl scriem acolo.
Scrieți această formulă în celula H2
= VLOOKUP (G2, B2: E14, MATCH (H1, B1: E1,0), 0)
Acum, ori de câte ori veți schimba titlul în H1, această valoare în H2 va fi afișată din acea coloană.
Puteți utiliza o listă derulantă utilizând validarea datelor care are toate titlurile din tabel, pentru a fi mai ușor de utilizat.
Aici VLOOKUP face o potrivire exactă. Acum VLOOKUP își face obișnuitul de a extrage date. Magia se face prin funcția MATCH în poziția col_index.
Aici VLOOKUP vede orice valoare în G2 în prima coloană a tabloului de tabel B2: E14. Acum la col_index avem MATCH (H1, B1: E1,0).
Funcția MATCH caută orice valoare din H1 în interval B1: E1 și returnează indicele valorii potrivirii.
De exemplu, atunci când tastăm student în H1 îl caută în raza de acțiune B1: E1. Se găsește în C2. prin urmare se întoarce 2. Dacă tastăm Grad se va întoarce nota elevului.
Rețineți că atunci când tastăm Region, acesta returnează # N / A. Deoarece este în afara scopului tabelului. Vom vorbi despre aceasta din urmă în articol.
Acesta este cel mai bun mod de a face VLOOKUP automat. Există și alte metode, dar nu sunt atât de flexibile decât aceasta.
7. Utilizați funcția VLOOKUP cu funcția COLUMN
Funcția coloană returnează numărul coloanei de referință furnizată. Și de ce avem nevoie pentru preluarea datelor folosind VLOOKUP? Col_index. Prin urmare, folosind funcția COLUMN putem obține desigur date din orice set de date.
Să luăm exemplul de mai sus. Dar acum trebuie să preluăm date întregi. Nu doar o coloană.
Scrieți această formulă în H2 și copiați în I2 și J2.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, COLUMN (B1), 0)
Acest lucru vă va oferi un rezultat dinamic. Cum? Să vedem.
VLOOKUP funcționează ca de obicei. La column_index am scris CULOANĂ (B1), care se va traduce la 2, deoarece avem numele studentului în coloana 2 din coloana B, returnează numele studentului.
Important: Acest lucru funcționează deoarece avem tabelul nostru pornind de la coloana B. Dacă tabelul dvs. se află în mijlocul foii, va trebui să scădeți sau să adăugați câteva numere, astfel încât să corespundă cerințelor dvs.
De exemplu, dacă tabelul de mai sus începea de la C1 în aceeași structură, atunci toate formulele de mai jos ar funcționa bine.
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, COLUMN (B1), 0)
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, COLUMN (C1) -1,0)
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, COLUMN (A1) +1,0)
8. VLOOKUP pentru a îmbina datele
În majoritatea bazelor de date există o singură interogare care fuzionează cu tabele, numită în principal o interogare de asociere, dar MS Excel nu are nicio bază de date, prin urmare nu are interogare de asociere. Dar acest lucru nu înseamnă că nu putem uni două tabele dacă avem câteva coloane comune în două tabele.
Deci, atunci când primiți câteva date de la două departamente ale aceleiași companii, ați dori să le combinați pentru a analiza aceste date.
De exemplu, când obțineți note de elevi de la profesori și apoi înregistrări de prezență de la departamentul administrativ, ați dori să vedeți ce elev a obținut ce notă și care este procentul său de prezență, la un loc.
Trebuie să le îmbinăm în tabelul de mai jos.
Mărci, trebuie să obținem din Tabelul Mărci, și prezența din datele privind prezența.
Scrieți mai jos Formule în celula D19 pentru a obține marcaje și trageți în jos.
= VLOOKUP (B19, $ B $ 2: $ D $ 15,3,0)
Scrieți mai jos Formula în celula E19
= VLOOKUP (B19, $ G $ 3: $ I $ 15,3,0)
Și am îmbinat două tabele într-un singur tabel. Desigur, puteți adăuga mai mult de două tabele. Trebuie doar să obțineți date din toate tabelele pe care le doriți la un singur loc folosind VLOOKUP.
Cele mai bune practici ale VLOOKUP
Deci, toate exemplele de mai sus tocmai am folosit vlookup cu date brute. În toate exemplele de mai sus, am fost atenți la datele pe care le foloseam. Trebuia să fim atenți, oferind în același timp referințe la tablouri. Dar există anumite moduri care pot reduce acest efort pe scară largă.
9. Folosiți Referințe absolute cu VLOOKUP
Este posibil să fi observat că în unele articole am folosit intervale absolute (intervalul $ semn). Ei bine, furia absolută este utilizată atunci când nu dorim ca intervalul să fie modificat în timp ce copiați celula formulă în alte celule.
De exemplu, dacă am = VLOOKUP (G2,B2: E14, 2,0), 0) în celula H2 și, dacă o copiez sau o trag în jos în celula H3, formula se va schimba în = VLOOKUP (G3,B3: E15, 2,0). Toate referințele sunt modificate relativ. Aici s-ar putea să dorim să schimbăm referința valorii de căutare, dar nu și matricea de tabele. Deoarece B2 iese sau variază și este posibil să dorim să îl avem întotdeauna în tablourile noastre de tabele.
Deci, pentru a preveni faptul că folosim referințe absolute. Facem acele intervale absolute pe care nu vrem să le schimbăm, cum ar fi table_array în H2, = VLOOKUP (G2,$ B $ 2: $ E $ 14, 2,0), 0). Când copiați H2 în H3 formula va fi = VLOOKUP (G3,$ B $ 2: $ E $ 14, 2,0), 0). Rețineți că valoarea căutării este modificată, dar nu table_array.
10. Utilizați gamele numite cu VLOOKUP
În exemplul de mai sus, am folosit referințe absolute pentru remedierea table_array. Arată destul de neclar și greu de citit. Ce zici dacă poți scrie doar 'date' la locul de $ B $ 2: $ E $ 14 și s-ar referi la $ B $ 2: $ E $ 14 mereu. Va face și mai ușor citirea și scrierea formulei.
Deci, trebuie doar să selectați intervalul și să-l denumiți „date”.
Pentru a denumi un interval, selectați intervalul. Faceți clic dreapta, faceți clic pe Definiți numele și denumiți intervalul dorit. Acum doar = VLOOKUP (G3,date,2,0), 0) formula va funcționa exact la fel ca anterior.
Puteți citi mai multe despre intervalele denumite în Excel. Am explicat aici fiecare aspect al gamei numite.
11. Comercii pentru potrivirea parțială folosind VLOOKUP
VLOOKUP permite potrivirea parțială. Da, în timp ce faceți potrivirea exactă a VLOOKUP, puteți utiliza operatorii cu metacaracter pentru a face potrivire parțială. De exemplu, dacă doriți să căutați o valoare care începe cu „Gil”, puteți utiliza operatorul wildcard * cu „Gil” ca „Gil *”. Să vedem un exemplu.
Aici vreau să obțin note de student al căror nume începe cu Gil. Pentru a ajunge astfel, voi scrie mai jos formula.
= VLOOKUP ("* Gill", C2: D14,2,0)
Există două metacaractere disponibile în Excel pentru a fi utilizate cu funcția VLOOKUP.
Asterisk (*) wildcard. Acesta este utilizat atunci când, utilizatorul nu cunoaște numărul de caractere și cunoaște doar unele caractere de potrivire. De exemplu, dacă utilizatorul cunoaște un nume care începe cu „Sm”, atunci va scrie „Sm *”. Dacă utilizatorul știe că o valoare de căutare care se termină cu „123” atunci va scrie „* 123”. (wild cardul funcționează numai cu texte). Și știe că „se” intră într-un text, apoi va scrie „* se *”.
Semnul întrebării ("?"). Aceasta este utilizată atunci când utilizatorul știe numărul de caractere care lipsesc. De exemplu, dacă vreau să caut o valoare care conține 4 caractere, dar nu știu ce sunt, voi scrie pur și simplu „????” la locul valorii de căutare.
Vlookup va returna valoarea respectivă a primei valori găsite care are exact patru caractere în ea.
Important: Comodul funcționează numai cu valori text. Convertiți numerele în text dacă doriți să utilizați cu ele caractere wildcard.
Funcția Puncte slabe ale VLOOKUP
VLOOKUP este într-adevăr o funcție puternică și ușoară pentru preluarea valorilor, dar există multe domenii în care VLOOKUP nu este atât de util. Dacă lucrați la Excel, trebuie să le cunoașteți și pe ei și cum să faceți o sarcină pe care VLOOKUP nu o poate face.
12. Nu se poate prelua valoarea din stânga Table_Array
Cel mai mare defect al funcției VLOOKUP este că nu poate prelua valoare din stânga matricei de tabele. VLOOKUP caută numai în dreptul valorii de căutare.
Acest lucru se datorează faptului că, VLOOKUP caută date valoarea de căutare în prima coloană a datei matrice de masă. Nu va returna niciodată o valoare din afara mesei. Și dacă încercați să păstrați coloana stângă în matricea de tabele, aceasta devine prima coloană a matricei de tabele, prin urmare valoarea de căutare va fi căutată în acel interval. Ceea ce va genera probabil erori.
Pentru a căuta valori din stânga unei valori de căutare, utilizăm în schimb INDEX-MATCH. Funcția INDEX-MATCH poate căuta valori din orice coloană de pe foaie. De fapt, intervalul de căutare și intervalul din care doriți să căutați valori sunt total independente, ceea ce face ca potrivirea indexului să fie foarte personalizabilă.
VLOOKUP returnează # N / A chiar și atunci când există o valoare de căutare.
# N / A eroare este returnată de funcția VLOOKUP atunci când valoarea de căutare nu este găsită. S-ar putea să vă fie enervant când există o valoare în interval, dar VLOOKUP returnează o eroare # N / A.
14. Când numerele sunt formate ca text
Acest lucru se întâmplă mai ales atunci când numerele sunt formate ca text. Când găsiți folosind comanda CTRL + F, Excel o va găsi, dar când încercați să utilizați VLOOKUP, acesta returnează # N / A. Această sarcină este dată în majoritatea interviurilor ca întrebări truc.
În imaginea de mai sus, puteți vedea că 101 este chiar acolo, dar formula returnează o eroare. Cum te descurci? Ei bine, există două metode.
1. Convertiți textul în număr din date
Puteți vedea acea etichetă verde în colțul din stânga sus în coloana rollno. Acestea indică faptul că ceva este neobișnuit în valoarea celulei. Când selectați celula, va arăta că numărul este formatat ca text.
Când faceți clic pe semnul exclamării (!), Acesta vă va arăta opțiunea Conversie în număr. Selectați toate celulele și faceți clic pe această opțiune. Toate valorile vor fi convertite în text.
2. Convertiți numărul de căutare în text în formulă
În exemplul de mai sus, am mutat datele originale. Este posibil să nu doriți să modificați nimic din datele originale. Așadar, ați dori să faceți acest lucru în formulă. Pentru a schimba numărul în text în formula VLOOKUP, scrieți acest lucru.
= VLOOKUP (TEXT (G2, "0"), B2: D14,2,0)
Aici formula schimbă dinamic numărul în text și apoi îl potrivește cu intervalul dat.
15. Textul având spații principale și finale
Unele date furnizate de operatorii de introducere a datelor și datele de pe internet nu sunt curate. Acestea pot avea spații finale sau unele caractere neprimabile. În timpul efectuării căutării folosind aceste valori, este posibil să vedeți o eroare # N / A. Pentru a evita acest lucru, curățați-vă mai întâi datele. Pentru a elimina spațiile principale folosiți funcția TRIM. TRIM elimină din text orice număr de spații de conducere sau de urmărire.
Deci, vă sugerez să adăugați o nouă coloană și să obțineți datele curățate acolo. Apoi, valoarea lipiți-l. Acum, dacă doriți, puteți scăpa de coloana originală.
= TRIM (CLEAN (text))
Tratarea erorilor VLOOKUP
După cum știți, VLOOKUP nu reușește să găsească o valoare, returnează o eroare # N / A. Ei bine, este ok să primiți o eroare # N / A, poate intenționați uneori să obțineți acest lucru, cum ar fi atunci când doriți să verificați dacă valoarea a există deja în listă sau nu. Acolo # N / A înseamnă că valoarea nu este acolo.
Dar # N / A arată urât. Ce zici de obținerea unei ieșiri prietenoase cu utilizatorul, cum ar fi „Codul nu a fost găsit” sau „Clientul nu a fost găsit”. Putem folosi IFERROR cu funcția VLOOKUP pentru a evita # N / A.
Utilizați formula de mai jos pentru a detecta eroarea în VLOOKUP.
= IFERROR (VLOOKUP (150, B2: E14,2,0), "Roll no not found")
16. VLOOKUP cu MULTIPLE criterii
VLOOKUP nu poate funcționa cu mai multe criterii.Da, puteți avea un singur criteriu pentru a căuta valori folosind formula VLOOKUP.
De exemplu, dacă aveți prenumele și prenumele în două coloane separate.
Și acum, dacă doriți să căutați o valoare al cărei prenume este John și prenumele Dave, atunci nu o puteți face în mod normal.
Dar există o soluție pentru asta. Puteți crea o coloană separată concatenând prenumele și prenumele și apoi căutați acea coloană de nume și prenume.
Este un Alternativă INDEX-MATCH care poate căuta mai multe criterii fără nicio coloană de ajutor.
17. VLOOKUP preia numai prima valoare găsită
Imaginați-vă că aveți câteva date în regiunea centrală. Acum doriți să obțineți primele 5 înregistrări de date din regiunea centrală. VLOOKUP va returna prima valoare centrală din toate cele cinci înregistrări. Acesta este un fundal major.
Dar nu înseamnă că nu putem realiza acest lucru. Putem folosi o formulă complexă de tablouri, după cum urmează.
= INDEX ($ C $ 2: $ C $ 14, SMALL (IF (G2 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14)) -ROW ($ A $ 2) +1), ROW (1: 1)))
Scrieți această formulă și apăsați CTRL + SHIFT + ENTER.
Și s-a făcut.
L-am explicat în articol cum să căutați mai multe valori în Excel.
Deci, băieți, în acest articol am acoperit toate aspectele posibile ale funcției VLOOKUP conform cunoștințelor mele. Dacă credeți că am ratat ceva, vă rugăm să ne anunțați în secțiunea de comentarii de mai jos.
Vlookup Top 5 valori cu valori duplicate folosind INDEX-MATCH în Excel
VLOOKUP Valori multiple
VLOOKUP cu Index Dynamic Col
Potrivire parțială cu funcția VLOOKUP
Utilizați VLOOKUP din două sau mai multe tabele de căutare
Vlookup după dată în Excel
Utilizarea unei formule VLOOKUP pentru a verifica dacă există o valoare
Cum să căutați din diferite foi Excel
VLOOKUP cu numere și text
Funcția IF, ISNA și VLOOKUP
Funcția ISNA și VLOOKUP
Funcția IFERROR și VLOOKUP