Excela

Funcția Excel VLOOKUP

Excel Vlookup Function

Funcția Excel VLOOKUPrezumat

VLOOKUP este o funcție Excel pentru a căuta date într-un tabel organizat vertical. VLOOKUP acceptă potrivirea aproximativă și exactă și wildcards (*?) pentru meciuri parțiale. Valorile de căutare trebuie să apară în primul coloana tabelului a trecut în VLOOKUP.





Scop Căutați o valoare într-un tabel prin potrivirea pe prima coloană Valoare returnată Valoarea potrivită dintr-un tabel. Sintaxă = VLOOKUP (valoare, tabel, col_index, [range_lookup]) Argumente
  • valoare - Valoarea de căutat în prima coloană a unui tabel.
  • masa - Tabelul din care să extrageți o valoare.
  • col_index - Coloana din tabel din care să extrageți o valoare.
  • range_lookup - [opțional] TRUE = potrivire aproximativă (implicit). FALS = potrivire exactă.
Versiune Excel 2003 Note de utilizare

VLOOKUP este o funcție Excel pentru a obține date dintr-un tabel organizat vertical. Valorile de căutare trebuie să apară în primul coloana tabelului a trecut în VLOOKUP. VLOOKUP acceptă potrivirea aproximativă și exactă și wildcards (*?) pentru meciuri parțiale.

Date verticale | Numere coloane | Arată doar corect | Moduri de potrivire | Potrivire perfecta | Meci aproximativ | Primul meci | Meci cu comodine | Căutare bidirecțională | Criterii multiple | # N / A Erori | Videoclipuri





V este pentru verticală

Scopul VLOOKUP este de a obține informații dintr-un tabel organizat astfel:

VLOOKUP este pentru date verticale



Folosind numărul comenzii din coloana B ca valoare de căutare, VLOOKUP poate obține codul de client, suma, numele și statul pentru orice comandă. De exemplu, pentru a obține numele clientului pentru comanda 1004, formula este:

 
= VLOOKUP (1004,B5:F9,4,FALSE) // returns 'Sue Martin'

Pentru date orizontale, puteți utiliza fișierul CĂUTARE , INDICE și MATCH , sau XLOOKUP .

cum să copiați în Excel

VLOOKUP se bazează pe numerele de coloane

Când utilizați VLOOKUP, imaginați-vă că fiecare coloană din masa este numerotat, începând de la stânga. Pentru a obține o valoare dintr-o anumită coloană, furnizați numărul corespunzător ca „index de coloană”. De exemplu, indexul coloanei pentru a prelua prenumele de mai jos este 2:

VLOOKUP exemplu de potrivire exactă

Numele și adresa de e-mail pot fi recuperate cu coloanele 3 și 4:

 
= VLOOKUP (H3,B4:E13,2,FALSE) // first name = VLOOKUP (H3,B4:E13,3,FALSE) // last name = VLOOKUP (H3,B4:E13,4,FALSE) // email address

VLOOKUP arată doar corect

VLOOKUP poate privi doar spre dreapta. Datele pe care doriți să le extrageți (valorile rezultatelor) pot apărea în orice coloană La dreapta valorilor de căutare:

VLOOKUP poate privi doar spre dreapta

Dacă trebuie să căutați valori în stânga, consultați INDICE și MATCH , sau XLOOKUP .

Potrivire exactă și aproximativă

VLOOKUP are două moduri de potrivire, exactă și aproximativă. Numele argumentului care controlează potrivirea este „ range_lookup '. Acesta este un nume confuz, deoarece se pare că are ceva de-a face cu intervalele de celule ca A1: A10. De fapt, cuvântul „interval” în acest caz se referă la „interval de valori” - când range_lookup este ADEVĂRAT, VLOOKUP se va potrivi cu un gama de valori mai degrabă decât o valoare exactă. Un bun exemplu în acest sens este utilizarea VLOOKUP pentru a calcula notele .

Este important să înțelegem asta range_lookup implicit la TRUE , ceea ce înseamnă că VLOOKUP va utiliza în mod implicit potrivirea aproximativă, care poate fi periculos . A stabilit range_lookup la FALS pentru a forța potrivirea exactă:

 
= VLOOKUP (value, table, col_index) // approximate match (default) = VLOOKUP (value, table, col_index, TRUE) // approximate match = VLOOKUP (value, table, col_index, FALSE) // exact match

Notă: De asemenea, puteți furniza zero (0) în loc de FALS pentru o potrivire exactă.

Potrivire perfecta

În majoritatea cazurilor, probabil că veți dori să utilizați VLOOKUP în modul de potrivire exactă. Acest lucru are sens atunci când aveți o cheie unică de utilizat ca valoare de căutare, de exemplu, titlul filmului din aceste date:

VLOOKUP potrivire exactă cu filme

Formula din H6 de găsit An , bazat pe o potrivire exactă a titlului filmului, este:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

Meci aproximativ

În cazurile în care doriți cea mai buna potrivire , nu neapărat un potrivire perfecta , veți dori să utilizați modul aproximativ. De exemplu, mai jos vrem să căutăm o rată de comision în tabelul G5: H10. Valorile de căutare provin din coloana C. În acest exemplu, trebuie să folosim VLOOKUP în meci aproximativ modul, deoarece în majoritatea cazurilor nu se va găsi niciodată o potrivire exactă. Formula VLOOKUP din D5 este configurată pentru a efectua o potrivire aproximativă setând ultimul argument la TRUE:

VLOOKUP rata aproximativă a comisionului de meci

 
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match

VLOOKUP va scana valorile din coloana G pentru valoarea de căutare. Dacă se găsește o potrivire exactă, VLOOKUP îl va folosi. Dacă nu, VLOOKUP va „face un pas înapoi” și se va potrivi cu rândul anterior.

Notă: datele trebuie să fie sortate în ordine crescătoare după valoarea de căutare atunci când utilizați modul de potrivire aproximativ cu VLOOKUP.

Primul meci

În cazul valorilor duplicate, VLOOKUP va găsi fișierul primul meci când modul de potrivire este exact. În ecranul de mai jos, VLOOKUP este configurat pentru a găsi prețul pentru culoarea „Verde”. Există trei intrări cu culoarea verde, iar VLOOKUP returnează prețul pentru primul intrare, 17 USD. Formula din celula F5 este:

 
= VLOOKUP (E5,B5:C11,2,FALSE) // returns 17

VLOOKUP returnează primul meci

Potrivire cu wildcard

Funcția VLOOKUP acceptă wildcards , ceea ce face posibilă efectuarea unei potriviri parțiale pe o valoare de căutare. De exemplu, puteți utiliza VLOOKUP pentru a prelua valori dintr-un tabel după ce ați introdus numai parte a unei valori de căutare. Pentru a utiliza metacaracterele cu VLOOKUP, trebuie să specificați modul de potrivire exact furnizând FALS sau 0 pentru ultimul argument, range_lookup . Formula din H7 recuperează prenumele, „Michael”, după ce a introdus „Aya” în celula H4:

 
= VLOOKUP ($H&'*',$B:$E4,2,FALSE)

VLOOKUP potrivire cu metacaracter

Citiți mai multe explicație detaliată aici .

Căutare în două sensuri

În funcția VLOOKUP, argumentul indexului coloanei este în mod normal codificat ca un număr static. Cu toate acestea, puteți crea și un fișier indexul coloanei dinamice utilizând funcția MATCH pentru a localiza coloana din dreapta. Această tehnică vă permite să creați o căutare dinamică bidirecțională, potrivită pe ambele rânduri și coloane. În ecranul de mai jos, VLOOKUP este configurat pentru a efectua o căutare bazată pe nume și lună. Formula din H6 este:

 
= VLOOKUP (H4,B5:E13, MATCH (H5,B4:E4,0),0)

Căutare VLOOKUP în două direcții

Pentru mai multe detalii, vezi acest exemplu .

Notă: În general, INDICE și MATCH este un mod mai flexibil de a efectuați căutări bidirecționale .

Criterii multiple

Funcția VLOOKUP nu gestionează mai multe criterii în mod nativ. Cu toate acestea, puteți utiliza un coloana de ajutor pentru a uni mai multe câmpuri împreună și a utiliza aceste câmpuri ca mai multe criterii în VLOOKUP. În exemplul de mai jos, Coloana B este o coloană de ajutor care concatenează numele și prenumele împreună cu această formulă:

 
=C5&D5 // helper column

VLOOKUP este configurat să facă același lucru pentru a crea o valoare de căutare. Formula din H6 este:

 
= VLOOKUP (H4&H5,B5:E13,4,0)

VLOOKUP cu mai multe criterii

Pentru detalii, vezi acest exemplu .

Notă: INDICE și MATCH și XLOOKUP sunt modalități mai robuste de a gestiona căutările pe baza mai multor criterii.

VLOOKUP și # N / A erori

Dacă utilizați VLOOKUP, veți întâlni inevitabil eroarea # N / A. Eroarea # N / A înseamnă doar „nu a fost găsit”. De exemplu, în ecranul de mai jos, valoarea de căutare „Toy Story 2” nu există în tabelul de căutare și toate cele trei formule VLOOKUP returnează # N / A:

VLOOKUP # N / A exemplu de eroare

O modalitate de a „prinde” eroarea NA este de a utiliza fișierul Funcția IFNA asa:

VLOOKUP # N / A exemplu de eroare - remediat

Formula din H6 este:

 
= IFNA ( VLOOKUP (H4,B5:E9,2,FALSE),'Not found')

Mesajul poate fi personalizat după cum doriți. Pentru a nu returna nimic (adică pentru a afișa un rezultat necompletat) când VLOOKUP returnează # N / A puteți utiliza un șir gol ca acesta:

 
= IFNA ( VLOOKUP (H4,B5:E9,2,FALSE),'') // no message

Eroarea # N / A este utilă, deoarece vă spune că ceva nu este în regulă. În practică, există multe motive pentru care ați putea vedea această eroare, inclusiv:

  • Valoarea de căutare nu există în tabel
  • Valoarea căutării este greșită sau conține spațiu suplimentar
  • Modul de potrivire este exact, dar ar trebui să fie aproximativ
  • Intervalul tabelului nu este introdus corect
  • Copiați VLOOKUP și tabelul referința nu este blocată

Citeste mai mult: VLOOKUP fără erori # N / A

Mai multe despre VLOOKUP

Alte note

  • Range_lookup controlează dacă valoare trebuie să se potrivească exact sau nu. Valoarea implicită este TRUE = permite potrivirea non-exactă.
  • A stabilit range_lookup a FALS a solicita o potrivire exactă și ADEVĂRAT la permite o potrivire neexactă .
  • Dacă range_lookup este TRUE (setarea implicită), o potrivire neexactă va face ca funcția VLOOKUP să se potrivească cu cea mai apropiată valoare din tabelul care este încă mai puțin de valoare .
  • Cand range_lookup este omis, funcția VLOOKUP va permite o potrivire non-exactă, dar va folosi o potrivire exactă dacă există una.
  • Dacă range_lookup este TRUE (setarea implicită) asigurați-vă că valorile căutării din primul rând al tabelului sunt sortate în ordine crescătoare. În caz contrar, VLOOKUP poate returna o valoare incorectă sau neașteptată.
  • Dacă range_lookup este FALS (necesită potrivire exactă), valorile din prima coloană a masa nu trebuie să fie sortate.


^