Funcția Excel VLOOKUP
Funcția Excel VLOOKUP este un instrument puternic care vă ajută să căutați o valoare specificată prin potrivirea pe prima coloană a unui tabel sau a unui interval pe verticală și apoi returnând o valoare corespunzătoare dintr-o altă coloană din același rând. Deși VLOOKUP este incredibil de util, uneori poate fi dificil de înțeles pentru începători. Acest tutorial își propune să vă ajute să stăpâniți VLOOKUP furnizând explicarea pas cu pas a argumentelor, exemple utile și soluții la erorile comune puteți întâlni când utilizați funcția CĂUTARE V.
Related Videos
Explicarea pas cu pas a argumentelor
După cum se arată în captura de ecran de mai sus, funcția VLOOKUP este utilizată pentru a găsi un e-mail pe baza unui anumit număr de identificare. Voi oferi acum o explicație detaliată a modului de utilizare a VLOOKUP în acest exemplu, defalcând fiecare argument pas cu pas.
Pasul 1: Porniți funcția VLOOKUP
Selectați o celulă (H6 în acest caz) pentru a scoate rezultatul, apoi porniți funcția VLOOKUP tastând următorul conținut în Formula Barului.
=VLOOKUP(
Pasul 2: specificați valoarea de căutare
Mai întâi, specificați valoarea de căutare (care este ceea ce căutați) în funcția CĂUTARE V. Aici, mă refer la celula G6 care conține un anumit număr de identificare 1005.
=VLOOKUP(G6
Pasul 3: Specificați matricea tabelului
Apoi, specificați un interval de celule care să conțină atât valoarea pe care o căutați, cât și valoarea pe care doriți să o returnați. În acest caz, selectez intervalul B6:E12. Formula apare acum după cum urmează:
=VLOOKUP(G6,B6:E12
=VLOOKUP(G6,$B$6:$E$12
Pasul 4: Specificați coloana din care doriți să returnați o valoare
Apoi specificați coloana din care doriți să returnați o valoare.
În acest exemplu, deoarece trebuie să returnez e-mailul pe baza unui număr ID, aici introduc un număr 4 pentru a spune VLOOKUP să returneze o valoare din a patra coloană a intervalului de date.
=VLOOKUP(G6,B6:E12,4
Pasul 5: Găsiți o potrivire aproximativă sau exactă
În cele din urmă, determinați dacă căutați o potrivire aproximativă sau o potrivire exactă.
- Pentru a găsi un potrivire perfecta, trebuie să utilizați FALS ca ultim argument.
- Pentru a găsi un potrivire aproximativă, Utilizare TRUE ca ultim argument sau lăsați-l necompletat.
În acest exemplu, folosesc FALSE pentru potrivirea exactă. Formula arată acum astfel:
=VLOOKUP(G6,B6:E12,4,FALSE
Apăsați tasta Enter pentru a obține rezultatul
Explicând fiecare argument unul câte unul în exemplul de mai sus, sintaxa și argumentele funcției CĂUTARE V sunt acum mult mai ușor de înțeles.
Sintaxă și argumente
=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])
- Căutare_valor (obligatoriu): valoarea (o valoare reală sau o referință de celulă) pe care o căutați. Amintiți-vă că această valoare trebuie să fie în prima coloană a table_array.
- Table_array (obligatoriu): un interval de celule conține atât coloana valorii de căutare, cât și coloana valorii returnate.
- Col_index (obligatoriu): un număr întreg reprezintă numărul coloanei care conține valoarea returnată. Începe cu numărul 1 pentru coloana cea mai din stânga a table_array.
- Range_lookup (opțional): o valoare logică care determină dacă doriți ca VLOOKUP să găsească o potrivire aproximativă sau o potrivire exactă.
- Meci aproximativ - Setează acest argument la TRUE, 1 sau lasa-l necompletat.
Important: Pentru a găsi o potrivire aproximativă, valorile din prima coloană a table_array trebuie să fie sortate în ordine crescătoare în cazul în care VLOOKUP returnează un rezultat greșit. - Potrivire perfecta - Setează acest argument la FALS or 0.
- Meci aproximativ - Setează acest argument la TRUE, 1 sau lasa-l necompletat.
Exemple
Această secțiune demonstrează câteva exemple pentru a vă ajuta să aveți o înțelegere mai cuprinzătoare a funcției CĂUTARE V.
Exemplul 1: potrivire exactă vs. potrivire aproximativă în CĂUTARE V
Dacă sunteți confuz în ceea ce privește potrivirea exactă și potrivirea aproximativă atunci când utilizați CĂUTARE V, această secțiune vă poate ajuta să clarificați această confuzie.
Potrivire exactă în CĂUTARE V
În acest exemplu, voi găsi numele corespunzătoare pe baza scorurilor enumerate în intervalul E6: E8, așa că introdu următoarea formulă în celula F6 și trageți mânerul de completare automată în jos la F8. În această formulă, ultimul argument este specificat ca FALS pentru a efectua o căutare a potrivirii exacte.
=VLOOKUP(E6,$B$6:$C$12,2,FALSE)
Cu toate acestea, deoarece scorul 98 nu există în prima coloană a intervalului de date, CĂUTARE V returnează rezultatul erorii #N/A.
Potrivire aproximativă în VLOOKUP
Folosind în continuare exemplul de mai sus, dacă schimbați ultimul argument în TRUE, VLOOKUP va efectua o căutare aproximativă a potrivirii. Dacă nu se găsește nicio potrivire, va găsi următoarea valoare cea mai mare care este mai mică decât valoarea de căutare și va returna rezultatul corespunzător.
=VLOOKUP(E6,$B$6:$C$12,2,TRUE)
Deoarece scorul 98 nu există, CĂUTARE V găsește următoarea valoare cea mai mare care este mai mică de 98, care este 95, și returnează numele scorului 95 ca rezultat cel mai apropiat.
- În acest caz de potrivire aproximativă, valorile din prima coloană a table_array trebuie sortate în ordine crescătoare. În caz contrar, VLOOKUP poate să nu returneze valoarea corectă.
- Aici am blocat matricea tabelului ($B$6:$C$12) în funcția VLOOKUP pentru a face referire rapidă la un set consistent de date față de mai multe valori de căutare.
Exemplul 2: Utilizați CĂUTARE V cu mai multe criterii
Această secțiune demonstrează cum să utilizați VLOOKUP cu mai multe condiții în Excel. După cum se arată în captura de ecran de mai jos, dacă încercați să găsiți un salariu pe baza unui nume furnizat (în celula H5) și a unui departament (în celula H6), urmați pașii de mai jos pentru a finaliza.
Pasul 1: Adăugați o coloană de ajutor pentru a concatena valorile din coloanele de căutare
În acest caz, trebuie să creăm o coloană de ajutor pentru a concatena valorile din Nume si Prenume coloana și Departament coloana.
- Adăugați o coloană de ajutor în stânga intervalului de date și dați un antet acestei coloane. Vedeți captura de ecran:
- În această coloană de ajutor, selectați prima celulă de sub antet, introduceți următoarea formulă în Bara de formule, și apăsați Intrați.
=C6&" "&D6
notițe: În această formulă, folosim un ampersand (&) pentru a uni textul în două coloane pentru a produce o singură bucată de text.- C6 este prenumele lui Nume si Prenume coloană la care să se alăture, D6 este primul departament al Departament coloană la care să se alăture.
- Valorile acestor două celule sunt concatenate cu un spațiu între ele.
- Selectați această celulă rezultat, apoi trageți Mâner de completare automată jos pentru a aplica această formulă altor celule din aceeași coloană.
Pasul 2: Aplicați funcția VLOOKUP cu criteriile date
Selectați o celulă în care doriți să scoateți rezultatul (aici selectez I7), introduceți următoarea formulă în Bara de formule, apoi apăsați Intrați.
=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)
Rezultat
- Coloana de ajutor trebuie utilizată ca prima coloană a intervalului de date.
- Acum, coloana salariului este a cincea coloană a intervalului de date, așa că folosim numărul 5 ca index de coloană în formulă.
- Trebuie să ne unim criteriile I5 și I6 (I5& " "&I6) în același mod ca și coloana de ajutor și utilizați valoarea concatenată ca lookup_value argument în formulă.
- De asemenea, puteți pune cele două condiții direct în argumentul lookup_value și le puteți separa cu un spațiu (dacă condițiile sunt text, nu uitați să le încadrați între ghilimele duble).
=VLOOKUP("Albee IT",B6:F12,5,FALSE)
- O alternativă mai bună - căutare cu mai multe criterii în câteva secunde
Căutare în mai multe condiții caracteristică a Kutools pentru Excel vă poate ajuta să căutați cu ușurință cu mai multe criterii în câteva secunde. Obțineți acum o perioadă de încercare gratuită cu funcții complete de 30 de zile!
Erori și soluții comune de CĂUTARE VL
Această secțiune listează erorile comune pe care le puteți întâlni când utilizați VLOOKUP și oferă soluții pentru a le remedia.
#N/A eroare returnată
Cea mai frecventă eroare cu VLOOKUP este eroarea #N/A, ceea ce înseamnă că Excel nu a putut găsi valoarea pe care o căutați. Iată câteva motive pentru care VLOOKUP poate returna eroarea #N/A.
Motivul 1: valoarea de căutare nu se află în prima coloană a table_array
Una dintre limitările Excel VLOOKUP este că vă permite doar să priviți de la stânga la dreapta. Deci, valorile de căutare trebuie să fie în prima coloană a table_array.
După cum se arată în captura de ecran de mai jos, vreau să returnez un nume bazat pe titlul postului dat. Aici valoarea de căutare (manager de vanzari) se află în a doua coloană a table_array și valoarea returnată este în stânga coloanei de căutare, astfel încât VLOOKUP returnează eroare #N/A.
soluţii
Puteți aplica oricare dintre următoarele soluții pentru a remedia această eroare.
- Rearanjați coloanele
Puteți rearanja coloanele pentru a plasa coloana de căutare în prima coloană a table_array. - Utilizați împreună funcțiile INDEX și MATCH
Aici folosim funcțiile INDEX și MATCH împreună ca alternativă la VLOOKUP pentru a rezolva această problemă.=INDEX(B6:B12,MATCH(F6,C6:C12,0))
- Utilizați funcția XLOOKUP (disponibilă în Excel 365, Excel 2021 și versiuni ulterioare)
=XLOOKUP(F6,C6:C12,B6:B12)
Motivul 2: valoarea de căutare nu este găsită în coloana de căutare (potrivire exactă)
Unul dintre cele mai comune motive pentru care VLOOKUP returnează eroarea #N/A este că valoarea pe care o căutați nu este găsită.
După cum se arată în exemplul de mai jos, vom găsi numele pe baza scorului dat de 98 în E6. Cu toate acestea, acest scor nu există în prima coloană a intervalului de date, astfel încât CUȚIUNEA returnează rezultatul erorii #N/A.
soluţii
Pentru a remedia această eroare, puteți încerca una dintre următoarele soluții.
- Dacă doriți căutări VLOOKUP pentru următoarea valoare cea mai mare care este mai mică decât valoarea de căutare, schimbați ultimul argument FALS (potrivire exactă) la TRUE (potrivire aproximativă). Pentru mai multe informații, consultați Exemplul 1: potrivire exactă vs. potrivire aproximativă utilizând CĂUTARE V.
- Pentru a evita schimbarea ultimului argument și pentru a primi un memento în cazul în care valoarea de căutare nu este găsită, puteți include funcția VLOOKUP în funcția IFERROR:
=IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")
Motivul 3: valoarea de căutare este mai mică decât cea mai mică valoare din coloana de căutare (potrivire aproximativă)
După cum se arată în captura de ecran de mai jos, efectuați o căutare aproximativă a potrivirii. Valoarea pe care o căutați (numărul ID 1001 în acest caz) este mai mică decât cea mai mică valoare 1002 din coloana de căutare, prin urmare, CĂUTARE V returnează eroarea #N/A.
soluţii
Iată două soluții pentru tine.
- Asigurați-vă că valoarea de căutare este mai mare sau egală cu cea mai mică valoare din coloana de căutare.
- Dacă doriți ca Excel să vă reamintească că valoarea de căutare nu a fost găsită, pur și simplu plasați funcția CUĂUTARE V în funcția IFEROARE, după cum urmează:
=IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")
Motivul 4: numerele sunt formatate ca text
După cum puteți vedea în captura de ecran de mai jos, rezultatul erorii #N/A din acest exemplu se datorează unei nepotriviri de tip de date între celula de căutare (G6) și coloana de căutare (B6:B12) a tabelului original. Aici valoarea din G6 este un număr, iar valorile din intervalul B6:B12 sunt numere formatate ca text.
soluţii
Pentru a rezolva această problemă, trebuie să convertiți valoarea de căutare înapoi în număr. Iată două metode pentru tine.
- Aplicați caracteristica Convertiți în număr
Faceți clic pe celula în care doriți să convertiți textul în număr, selectați acest buton lângă celulă și apoi selectați Convertiți în număr. - Aplicați un instrument la îndemână pentru a converti în loturi între text și număr
Convertiți între text și număr caracteristică a Kutools pentru Excel vă ajută să convertiți cu ușurință o serie de celule din text în număr și invers. Obțineți acum o perioadă de încercare gratuită cu funcții complete de 30 de zile!
Motivul 5: table_array nu este constantă atunci când trageți formula VLOOKUP în alte celule
După cum se arată în captura de ecran de mai jos, există două valori de căutare în E6 și E7. După obținerea primului rezultat în F6, trageți formula VLOOKUP din celula F6 în F7, a fost returnat un rezultat de eroare #N/A. Acest lucru se datorează faptului că referințele de celule (B6:C12) sunt relative în mod implicit și ajustate pe măsură ce vă deplasați în jos prin rânduri. Matricea tabelului a fost mutată în jos la B7:C13, care nu mai conține scorul de căutare 73.
Soluţie
Trebuie să blocați matricea tabelului pentru a o menține constantă adăugând a $ semnează înaintea rândurilor și coloanelor din referințele de celule. Pentru a afla mai multe despre referința absolută în Excel, aruncați o privire la acest tutorial: Referință absolută Excel (cum se face și cum se utilizează).
#VALUE eroare returnată
Următoarele condiții pot determina VLOOKUP să returneze rezultatul erorii #VALUE.
Motivul 1: valoarea de căutare depășește 255 de caractere
După cum se arată în captura de ecran de mai jos, valoarea de căutare din celula H4 depășește 255 de caractere, așa că CUȚIUNEA returnează un rezultat de eroare #VALOR.
soluţii
Pentru a evita această limitare, puteți aplica o altă funcție de căutare care poate gestiona șiruri mai lungi. Încercați una dintre următoarele formule.
- INDEX și MATCH:
=INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))
- Funcția XLOOKUP (disponibil în Excel 365, Excel 2021 și versiuni ulterioare):
=XLOOKUP(H4,B5:B11,E5:E11)
Motivul 2: argumentul col_index este mai mic decât 1
Indexul coloanei specifică numărul coloanei din matricea tabelului care conține valoarea pe care doriți să o returnați. Acest argument trebuie să fie un număr pozitiv care corespunde unei coloane valide din matricea tabelului.
Dacă introduceți un index de coloană care este mai mic de 1 (adică zero sau negativ), CĂUTARE V nu va putea localiza coloana în matricea tabelului.
Soluţie
Pentru a remedia această problemă, asigurați-vă că argumentul index al coloanei din formula dvs. VLOOKUP este un număr pozitiv care corespunde unei coloane valide din matricea tabelului.
Eroarea #REF este returnată
Această secțiune listează un motiv pentru care VLOOKUP returnează eroarea #REF și oferă soluții la această problemă.
Motiv: argumentul col_index este mai mare decât numărul de coloane
După cum puteți vedea în captura de ecran de mai jos, matricea tabelului are doar 4 coloane. Cu toate acestea, indexul coloanei pe care l-ați specificat în formula CĂUTARE V este 5, care este mai mare decât numărul de coloane din matricea tabelului. Ca rezultat, VLOOKUP nu va putea localiza coloana și va returna în cele din urmă o eroare #REF.
soluţii
- Specificați un număr corect de coloană
Asigurați-vă că argumentul index al coloanei din formula dvs. VLOOKUP este un număr care corespunde unei coloane valide din matricea tabelului.
- Obțineți automat numărul coloanei pe baza antetului de coloană specificat
Dacă tabelul conține multe coloane, este posibil să aveți probleme la determinarea numărului corect de index al coloanei. Aici, puteți imbrica funcția MATCH în funcția VLOOKUP pentru a găsi poziția coloanei pe baza unui antet de coloană cert.
=VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)
notițe: În formula de mai sus, MATCH(„E-mail”, B5:E5, 0) funcția este folosită pentru a obține numărul coloanei "E-mail" din intervalul de date B6:E12. Aici rezultatul este 4, care este folosit ca index col în funcția CĂUTARE V.
Valoarea incorectă este returnată
Dacă descoperiți că VLOOKUP nu returnează rezultatul corect, aceasta poate fi cauzată de următoarele motive
Motivul 1: coloana de căutare nu este sortată în ordine crescătoare
Dacă ați setat ultimul argument la TRUE (Sau l-a lăsat gol) pentru o potrivire aproximativă și coloana de căutare nu este sortată în ordine crescătoare, valoarea rezultată poate fi incorectă.
Soluţie
Sortați coloana de căutare în ordine crescătoare vă poate ajuta să rezolvați această problemă. Pentru a face acest lucru, vă rugăm să urmați pașii de mai jos:
- Selectați celulele de date din coloana de căutare, accesați Date fila, faceți clic pe Sortați de la cel mai mic la cel mai mare în Sortați și filtrați grup.
- În Avertizare Sortare caseta de dialog, selectați Extindeți selecția și faceți clic pe OK.
Motivul 2: O coloană este inserată sau eliminată
După cum se arată în captura de ecran de mai jos, valoarea pe care am vrut inițial să o returnez este în a patra coloană a matricei tabelului, așa că specific numărul col_index ca 4. Pe măsură ce se introduce o nouă coloană, coloana rezultat devine a cincea coloană a tabelului matrice, determinând VLOOKUP să returneze rezultatul dintr-o coloană greșită.
soluţii
Iată două soluții pentru tine.
- Puteți modifica manual numărul de index al coloanei pentru a se potrivi cu poziția coloanei de returnare. Formula de aici ar trebui schimbată în:
=VLOOKUP(H6,B6:F12,5,FALSE)
- Dacă doriți întotdeauna să returnați rezultatul dintr-o coloană certă, cum ar fi coloana Email din acest exemplu. Următoarea formulă poate ajuta la potrivirea automată a indexului coloanei pe baza antetului de coloană dat, indiferent dacă coloanele sunt inserate sau eliminate din matricea tabelului.
=VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)
Alte note de funcționare
- VLOOKUP caută doar valoarea de la stânga la dreapta.
Valoarea de căutare se află în coloana din stânga, iar valoarea rezultatului ar trebui să fie în orice coloană din dreapta coloanei de căutare. - Dacă lăsați ultimul argument necompletat, VLOOKUP folosește potrivirea aproximativă în mod implicit.
- VLOOKUP efectuează o căutare care nu ține seama de majuscule și minuscule.
- Pentru mai multe potriviri, VLOOKUP returnează doar prima potrivire pe care o găsește în matricea tabelului, pe baza ordinii rândurilor din matricea tabelului.
Articole pe aceeaşi temă
Peste 20 de exemple de CĂUTARE V pentru utilizatorii Excel începători și avansați
Acest tutorial demonstrează cum să utilizați funcția Vlookup în Excel cu zeci de exemple de bază și avansate pas cu pas.
CĂUTARE V de la dreapta la stânga
Dacă doriți să căutați o anumită valoare în orice altă coloană și să returnați valoarea relativă la stânga, metodele din acest tutorial vă pot ajuta să îndepliniți această sarcină.
Căutare de jos în sus
Acest tutorial oferă două metode pentru a vă ajuta să căutați valoarea potrivită de jos în sus.
Faceți o căutare cu majuscule și minuscule
Dacă doriți să faceți o CĂUTARE VD cu majuscule și minuscule în Excel, metoda din acest tutorial vă poate face o favoare.
VLOOKUP păstrează formatarea sursei
Acest tutorial oferă o metodă pentru a vă ajuta să păstrați toată formatarea celulei rezultate atunci când faceți Vlookup în Excel.
Cele mai bune instrumente de productivitate de birou
Îmbunătățiți-vă abilitățile Excel cu Kutools pentru Excel și experimentați eficiența ca niciodată. Kutools pentru Excel oferă peste 300 de funcții avansate pentru a crește productivitatea și a economisi timp. Faceți clic aici pentru a obține funcția de care aveți cea mai mare nevoie...
Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară
- 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!
Cuprins
- Related Videos
- Explicarea pas cu pas a argumentelor
- Sintaxă și argumente
- CĂUTARE V Exemple
- Potrivire exactă vs. potrivire aproximativă
- CĂUTARE V cu mai multe condiții
- Erori și soluții comune
- #N/A eroare
- Eroare #VALUE
- Eroare #REF
- Valoare incorectă
- Alte note de funcționare
- Articole pe aceeaşi temă
- Cele mai bune instrumente de productivitate Office
- Comentarii