Cum să utilizați funcția VLOOKUP în Microsoft Excel [+ Tutorial video]

Coordonarea unei cantități masive de date în Microsoft Excel este o durere de cap consumatoare de timp. Din fericire, nu trebuie. Funcția VLOOKUP vă poate ajuta să automatizați această sarcină și să vă economisiți o mulțime de timp.

Ce face VLOOKUP, mai exact? Iată explicația simplă: funcția CĂUTARE VĂ caută o anumită valoare în datele dvs. și, odată ce identifică acea valoare, poate găsi - și afișa - o altă informație care este asociată cu acea valoare.

Funcția VLOOKUP din Microsoft Excel este mai ușor de utilizat decât credeți. În plus, este incredibil de puternic și este cu siguranță ceva ce vrei să ai în arsenalul tău de arme analitice.

Sari la:

Cum funcționează VLOOKUP?

VLOOKUP înseamnă „căutare verticală”. În Excel, aceasta înseamnă acțiunea de a căuta date vertical pe o foaie de calcul, folosind coloanele foii de calcul - și un identificator unic în acele coloane - ca bază a căutării. Când căutați datele dvs., acestea trebuie să fie listate vertical oriunde se află acele date.

CĂUTARE V Formula Excel

Microsoft descrie formula sau funcția VLOOKUP după cum urmează:

=CĂUTAREV(valoare de căutare, interval care conține valoarea de căutare, numărul coloanei din intervalul care conține valoarea returnată, potrivire aproximativă (ADEVĂRAT) sau potrivire exactă (FALSE)).

Vă ajută să vă organizați datele astfel încât valoarea pe care doriți să o căutați să fie în stânga valorii de returnare pe care doriți să o găsiți.

Formula caută întotdeauna în dreapta.

Când efectuați o CĂUTARE VL în Excel, căutați în esență date noi într-o foaie de calcul diferită care este asociată cu datele vechi din cea actuală. Când VLOOKUP rulează această căutare, caută întotdeauna noile date în dreapta datelor dvs. curente.

De exemplu, dacă o foaie de calcul are o listă verticală de nume, iar o altă foaie de calcul are o listă neorganizată a acelor nume și a adreselor lor de e-mail , puteți utiliza CUȚIUNE V pentru a prelua acele adrese de e-mail în ordinea în care le aveți în prima foaie de calcul. Acele adrese de e-mail trebuie să fie listate în coloana din dreapta numelor din a doua foaie de calcul, altfel Excel nu le va putea găsi. (Dă-ți seama … )

Formula are nevoie de un identificator unic pentru a prelua datele.

Secretul cum funcționează VLOOKUP? Identificatori unici.

Un identificator unic este o informație pe care ambele surse de date o partajează și, după cum sugerează și numele, este unică (adică identificatorul este asociat doar cu o înregistrare din baza de date). Identificatorii unici includ coduri de produs, unități de stocare (SKU) și contacte cu clienții.

Bine, suficientă explicație: să vedem un alt exemplu de CĂUTARE V în acțiune!

Exemplu Excel de CĂUTARE V

În videoclipul de mai jos, vom arăta un exemplu în acțiune, folosind funcția CĂUTARE V pentru a potrivi adresele de e-mail (de la o a doua sursă de date) cu datele corespunzătoare într-o foaie separată.

Nota autorului: există multe versiuni diferite de Excel, așa că ceea ce vedeți în videoclipul de mai sus s-ar putea să nu se potrivească întotdeauna exact cu ceea ce veți vedea în versiunea dvs. De aceea, vă încurajăm să urmați instrucțiunile scrise de mai jos.

Cum se utilizează VLOOKUP în Excel

1. Identificați o coloană de celule pe care doriți să o completați cu date noi.
2. Selectați „Funcție” (Fx) > CĂUTARE V și introduceți această formulă în celula evidențiată.
3. Introduceți valoarea de căutare pentru care doriți să preluați date noi.
4. Introduceți matricea tabelului din foaia de calcul unde se află datele dorite.
5. Introduceți numărul coloanei datelor pe care doriți să le returneze Excel.
6. Introduceți căutarea intervalului pentru a găsi o potrivire exactă sau aproximativă a valorii dvs. de căutare.
7. Faceți clic pe „Terminat” (sau „Enter”) și completați noua coloană.

Pentru referință, iată cum arată sintaxa pentru o funcție VLOOKUP:

CĂUTARE V(valoare_căutare, matrice_tabelă, număr_index_col, căutare_interval)

În pașii de mai jos, vom atribui valoarea corectă fiecăreia dintre aceste componente, folosind numele clienților ca identificator unic pentru a găsi MRR-ul fiecărui client.

1. Identificați o coloană de celule pe care doriți să o completați cu date noi.

Amintiți-vă că doriți să preluați date dintr-o altă foaie și să le depuneți în aceasta. Având în vedere acest lucru, etichetați o coloană de lângă celulele despre care doriți mai multe informații cu un titlu adecvat în celula de sus, cum ar fi „MRR”, pentru venituri lunare recurente. Această nouă coloană este locul unde vor merge datele pe care le preluați.

2. Selectați „Funcție” (Fx) > CĂUTARE V și introduceți această formulă în celula evidențiată.

În stânga barei de text de deasupra foii de calcul, veți vedea o pictogramă de funcție mică care arată ca un script: Fx . Faceți clic pe prima celulă goală de sub titlul coloanei și apoi faceți clic pe această pictogramă funcție. O casetă intitulată Formula Builder sau Insert Function va apărea în partea dreaptă a ecranului (în funcție de versiunea de Excel pe care o aveți).

Căutați și selectați „VLOOKUP” din lista de opțiuni incluse în Formula Builder. Apoi, selectați OK sau Insert Function pentru a începe să vă construiți VLOOKUP. Celula pe care ați evidențiat-o în prezent în foaia de calcul ar trebui să arate acum astfel: „ =VLOOKUP() „

De asemenea, puteți introduce această formulă într-un apel manual, introducând textul aldin de mai sus exact în celula dorită.

Cu textul =VLOOKUP introdus în prima celulă, este timpul să completați formula cu patru criterii diferite. Aceste criterii vor ajuta Excel să restrângă exact unde se află datele pe care le doriți și ce să caute.

3. Introduceți valoarea de căutare pentru care doriți să preluați date noi.

Primul criteriu este valoarea de căutare - aceasta este valoarea foii de calcul care are date asociate cu ea, pe care doriți ca Excel să le găsească și să le returneze pentru dvs. Pentru a o introduce, faceți clic pe celula care poartă o valoare pentru care încercați să găsiți o potrivire. În exemplul nostru, prezentat mai sus, este în celula A2. Veți începe să migrați noile date în D2, deoarece această celulă reprezintă MRR-ul numelui de client afișat în A2.

Rețineți că valoarea dvs. de căutare poate fi orice: text, numere, link-uri de site-uri web, cum doriți. Atâta timp cât valoarea pe care o căutați se potrivește cu valoarea din foaia de calcul de referință - despre care vom vorbi despre asta în pasul următor - această funcție va returna datele dorite.

4. Introduceți matricea tabelului din foaia de calcul în care se află datele dorite.

Lângă câmpul „matrice de tabel”, introduceți intervalul de celule pe care doriți să le căutați și foaia în care se află aceste celule, folosind formatul afișat în captura de ecran de mai sus. Intrarea de mai sus înseamnă că datele pe care le căutăm sunt într-o foaie de calcul intitulată „Pagini” și pot fi găsite oriunde între coloana B și coloana K.

Foaia în care se află datele dvs. trebuie să fie în fișierul dvs. Excel actual. Aceasta înseamnă că datele dvs. pot fi fie într-un tabel diferit de celule undeva în foaia de calcul actuală, fie într-o altă foaie de calcul conectată în partea de jos a registrului de lucru, după cum se arată mai jos.

De exemplu, dacă datele dvs. se află în „Sheet2” între celulele C7 și L18, intrarea în matricea tabelului va fi „Sheet2!C7:L18”.

5. Introduceți numărul coloanei datelor pe care doriți să le returneze Excel.

Sub câmpul matrice de tabel, veți introduce „numărul de index al coloanei” al matricei de tabel prin care căutați. De exemplu, dacă vă concentrați pe coloanele de la B la K (notate „B:K” când sunt introduse în câmpul „matrice de tabel”), dar valorile specifice pe care le doriți sunt în coloana K, veți introduce „10” în câmpul „număr index al coloanei”, deoarece coloana K este a zecea coloană din stânga.

6. Introduceți căutarea intervalului pentru a găsi o potrivire exactă sau aproximativă a valorii dvs. de căutare.

În situații precum a noastră, care se referă la venitul lunar, doriți să găsiți potriviri exacte din tabelul pe care îl căutați. Pentru a face acest lucru, introduceți „FALSE” în ​​câmpul „cautare interval”. Acest lucru îi spune Excel că doriți să găsiți doar venitul exact asociat fiecărui contact de vânzări.

Pentru a răspunde la întrebarea dvs. arzătoare: Da, puteți permite Excel să caute o potrivire aproximativă în loc de o potrivire exactă. Pentru a face acest lucru, introduceți pur și simplu TRUE în loc de FALSE în al patrulea câmp afișat mai sus.

Când CĂUTARE V este setată pentru o potrivire aproximativă, se caută date care seamănă cel mai mult cu valoarea de căutare, mai degrabă decât date care sunt identice cu acea valoare. Dacă căutați date asociate cu o listă de linkuri de site-uri, de exemplu, și unele dintre linkurile dvs. au „https://” la început, ar putea fi cazul să găsiți o potrivire aproximativă în cazul în care există linkuri care nu au această etichetă „https://”. În acest fel, restul linkului se poate potrivi fără ca această etichetă text inițială să provoace ca formula ta VLOOKUP să returneze o eroare dacă Excel nu o poate găsi.

7. Faceți clic pe „Terminat” (sau „Enter”) și completați noua coloană.

Pentru a introduce oficial valorile pe care le doriți în noua dvs. coloană de la Pasul 1, faceți clic pe „Terminat” (sau „Enter”, în funcție de versiunea dvs. de Excel) după completarea câmpului „căutare interval”. Aceasta va popula prima celulă. Puteți profita de această ocazie pentru a căuta în cealaltă foaie de calcul pentru a vă asigura că aceasta este valoarea corectă.

Dacă da, completați restul noii coloane cu fiecare valoare ulterioară făcând clic pe prima celulă completată, apoi făcând clic pe pătratul mic care apare în colțul din dreapta jos al acestei celule. Terminat! Ar trebui să apară toate valorile tale.

Tutorial CĂUTARE V

Ați rămas blocat după ce ați încercat să vă efectuați propria CĂUTARE V cu pașii de mai sus? Consultați acest tutorial la îndemână de la Microsoft are un tutorial la îndemână care vă va ghida prin utilizarea corectă a funcției.

Sursa imaginii

Dacă ați urmat pașii de mai sus și VLOOKUP încă nu funcționează, va fi fie o problemă cu:

• Sintaxă (adică cum ați structurat formula)
• Valori (adică dacă datele pe care le caută sunt bune și formatate corect)

Depanarea sintaxei VLOOKUP

Începeți cu privire la formula VLOOKUP pe care ați scris-o în celula desemnată.

• Se referă la valoarea de căutare corectă pentru identificatorul de cheie?
• Specifică intervalul corect de matrice de tabel pentru valorile pe care trebuie să le recupereze
• Specifică foaia corectă pentru gama?
• Foaia aia este scrisă corect?
• Folosește sintaxa corectă pentru a se referi la foaie? (de exemplu, Pagini!B:K sau „Foaie 1”!B:K)
• A fost specificat numărul corect al coloanei? (de exemplu, A este 1, B este 2 și așa mai departe)
• Este Adevărat sau Fals ruta corectă pentru modul în care este configurată foaia dvs.?

Depanarea valorilor VLOOKUP

Dacă sintaxa nu este problema, cum puteți avea o problemă cu valorile pe care încercați să le primiți. Acest lucru se manifestă adesea ca o eroare #N/A în care CĂUTARE V nu poate găsi o valoare de referință.

• Valorile sunt formatate vertical și de la dreapta la stânga?
• Valorile se potrivesc cu modul în care vă referiți la ele?

De exemplu, dacă căutați date URL, fiecare adresă URL trebuie să fie un rând cu datele corespunzătoare în partea stângă a acesteia, în același rând. Dacă aveți adresele URL ca antete de coloană, cu datele care se mișcă vertical, CĂUTARE V nu va funcționa.

Respectând acest exemplu, adresele URL trebuie să se potrivească în format în ambele foi. Dacă aveți o foaie care include „https://” în valoare, în timp ce cealaltă foaie omite „https://”, VLOOKUP nu va putea să se potrivească cu valorile.

VLOOKUPs ca instrument de marketing puternic

Specialiștii în marketing trebuie să analizeze datele dintr-o varietate de surse pentru a obține o imagine completă a generării de clienți potențiali (și mai mult). Microsoft Excel este instrumentul perfect pentru a face acest lucru cu precizie și la scară, în special cu funcția CĂUTARE V.

Nota editorului: această postare a fost publicată inițial în martie 2019 și a fost actualizată pentru a fi completă.