Salt la conținutul principal

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

notițe: valoarea de căutare trebuie să fie în prima coloană a intervalului de date.
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

notițe: Dacă doriți să copiați funcția VLOOKUP pentru a căuta mai multe valori în aceeași coloană și pentru a obține rezultate diferite, trebuie să utilizați referințe absolute adăugând semnul dolar, astfel:
=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.

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.

notițe: Aici am blocat matricea tabelului ($B$6:$C$12) în funcția VLOOKUP pentru a face referire rapidă la o consistent set de date față de mai multe valori de căutare.
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.

notițe:
  • Î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.

  1. 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:
  2. Î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.
  3. 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

notițe:
  • 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.

  Prezentare generală a erorilor comune de CĂUTARE VL:
          
         Motivul 1: valoarea de căutare nu este în prima coloană  
     Motivul 2: valoarea de căutare nu a fost găsită  
  ------  Motivul 3: valoarea de căutare este mai mică decât cea mai mică valoare  
     Motivul 4: numerele sunt formatate ca text  
       Motivul 5: Table_array nu este constantă  
         
  ------  Motivul 1: valoarea de căutare depășește 255 de caractere  
   Motivul 2: Col_index este mai mic de 1  
         
  ------  Motivul 1: Col_index este mai mare decât numărul de coloane  
   
         
  ------  Motivul 1: Coloana Căutare nu este sortată în ordine crescătoare  
   Motivul 2: O coloană este inserată sau eliminată  
         

#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.

varful: Dacă un număr este convertit în text, în colțul din stânga sus al celulei este afișat un mic triunghi verde.

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:

  1. 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.
  2. Î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.

Cele mai bune instrumente de productivitate de birou

Caracteristici populare: Găsiți, evidențiați sau identificați duplicatele   |  Ștergeți rândurile goale   |  Combinați coloane sau celule fără a pierde date   |   Rundă fără Formula ...
Super căutare: VLookup cu mai multe criterii    VLookup cu valori multiple  |   VLookup pe mai multe foi   |   Căutare fuzzy ....
Listă derulantă avansată: Creați rapid o listă derulantă   |  Listă drop-down dependentă   |  Listă derulantă cu selectare multiplă ....
Manager de coloane: Adăugați un număr specific de coloane  |  Mutați coloanele  |  Comutați starea vizibilității coloanelor ascunse  |  Comparați intervale și coloane ...
Caracteristici prezentate: Focus pe grilă   |  Vedere de proiectare   |   Big Formula Bar    Manager registru de lucru și foi   |  Biblioteca de resurse (Text automat)   |  Data Picker   |  Combinați foi de lucru   |  Criptare/Decriptare celule    Trimiteți e-mailuri după listă   |  Super Filtru   |   Filtru special (filtrează bold/italic/barat...) ...
Top 15 seturi de instrumente12 Text Instrumente (Adăuga text, Eliminați caractere,...)   |   50+ Diagramă Tipuri de (Gantt Chart,...)   |   40+ Practic Formule (Calculați vârsta pe baza zilei de naștere,...)   |   19 inserare Instrumente (Introduceți codul QR, Inserați imaginea din cale,...)   |   12 Convertire Instrumente (Numere la cuvinte, conversie valutara,...)   |   7 Merge & Split Instrumente (Rânduri combinate avansate, Celule divizate,...)   |   ... și altele

Î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 kte 201905


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!
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
thank you so much
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations