Utilizarea funcției VLOOKUP și exemple specifice

Funcția CĂUTARE V din Excel este extrem de utilă în căutarea datelor. Iată ce trebuie să știți despre utilizarea funcției CĂUTARE V din Excel .

Doriți să verificați o anumită valoare într-o foaie de calcul Excel și să căutați informații despre aceasta. Rularea unui VLOOKUP vă economisește efort semnificativ în acest caz. Este una dintre cele mai bune modalități de a executa o interogare verticală în Microsoft Excel. Articolul vă va spune semnificația, utilizarea și exemplele ilustrative ale funcției CĂUTARE V din Microsoft Excel.

• Acestea sunt cele mai de bază funcții din Excel pe care trebuie să le înțelegeți

Cum se utilizează funcția CĂUTARE VL

• Ce este funcția VLOOKUP?
• Formula funcției VLOOKUP în Excel 2016, 2013, 2010, 2007, 2003
• Tutorial video despre utilizarea funcției CĂUTARE V
• Exemplul 1: Utilizați funcția CĂUTARE V pentru a evalua evaluările studenților și ale personalului:
• Exemplul 2: Utilizați VLOOKUP pentru detectarea absolută pentru a obține date
• Exemplul 3: Utilizați VLOOKUP pentru a extrage date
• Exemplul 4: Utilizați funcția CĂUTARE V pe 2 foi Excel diferite
• Pentru ce să folosești funcția VLOOKUP?
• Lucruri de reținut despre funcția VLOOKUP

Ce este funcția VLOOKUP?

Funcția VLOOKUP este o funcție de căutare a datelor în Excel. Presupun că aveți un fundal în Excel și puteți utiliza funcții de bază precum SUM, AVERAGE și TODAY. Una dintre cele mai obișnuite funcții ale VLOOKUP este o funcție de date, adică funcționează pe tabele de baze de date sau, mai simplu, pe liste de articole. Lista are multă varietate. Puteți crea un tabel despre personalul companiei dvs., categoriile de produse, listele de clienți sau orice altceva. Și mai jos este o listă de produse pe care compania A le vinde pe piață:

Tabelele bazelor de date au adesea identificatori pentru fiecare produs. În tabelul de date de mai sus, semnul special de identificare este coloana „Cod articol”. Notă: Pentru a putea folosi funcția CĂUTARE V, tabelul de date trebuie să aibă o coloană care să conțină semne de identificare, cum ar fi codul sau ID, și trebuie să fie prima coloană ca tabelul de mai sus.

CĂUTARE V este probabil cea mai faimoasă funcție din Excel, dar din motive atât bune, cât și rele. Pe partea bună, funcția VLOOKUP este ușor de utilizat și face ceva foarte util. În special pentru utilizatorii noi, vizionarea funcției VLOOKUP scanează tabelul, găsește o potrivire și returnează un rezultat precis este extrem de interesantă. Utilizarea cu competență a funcției CĂUTARE V este o abilitate esențială, de la începători până la utilizatori excelenți de Excel.

Pe partea negativă, funcția VLOOKUP este limitată și are valori implicite periculoase. Spre deosebire de INDEX și MATCH (sau XLOOKUP), funcția VLOOKUP are nevoie de un tabel complet cu valori de căutare în prima coloană. Acest lucru face dificilă utilizarea funcției VLOOKUP cu mai multe criterii. În plus, comportamentul de potrivire implicit al VLOOKUP facilitează obținerea de rezultate incorecte. Dar nu iti face griji! Cheia pentru utilizarea cu succes a funcției CĂUTARE V este să stăpânești elementele de bază.

VLOOKUP are 4 argumente: lookup_value, table_array, column_index_num și range_lookup. Lookup_value este valoarea de căutat, iar table_array este intervalul de date vertical în care trebuie căutat. Prima coloană a table_array trebuie să conțină valorile de căutare pentru căutare. Argumentul column_index_num este numărul coloanei valorii de preluat, în care prima coloană din table_array este coloana 1.

În cele din urmă, range_lookup controlează comportamentul de a găsi o potrivire. Dacă range_lookup este TRUE, funcția VLOOKUP realizează o potrivire aproximativă. Dacă range_lookup este FALSE, funcția VLOOKUP va realiza o potrivire exactă. Important, range_lookup este opțional și este implicit TRUE, așa că VLOOKUP va efectua o potrivire aproximativă în mod implicit.

Formula funcției CĂUTARE V în Excel

Funcția CĂUTARE V din Excel are următoarea formulă:

=CĂUTAREV(valoare_căutare, matrice_tabelă, număr_index_col ,[căutare_gamă] )

Acolo:

• CĂUTARE V: Este numele funcției
• Parametrii cu caractere aldine sunt necesari.
• lookup_value: valoare utilizată pentru căutare
• table_array: Tabel care conține valoarea de căutat, introdus în valoare absolută cu semnul $ în față, de exemplu: $A$3:$E$40.
• col_index_num: Ordinea coloanei care conține valoarea de căutare pe table_array. De exemplu, în tabelul $A$3:$E$40, coloana B conține valoarea de căutat, apoi col_index_num aici va fi 2; tabelul $C$3:$F$40, coloana E conține valoarea de căutare, apoi col_index_num aici este 3.
• range_lookup: este domeniul de căutare, TRUE este echivalent cu 1 (căutare relativă), FALSE este echivalent cu 0 (căutare absolută). Acest parametru nu este întotdeauna necesar în formulă.

Căutarea relativă poate fi aplicată numai atunci când valorile de căutat în table_array au fost aranjate în ordine (crescător sau descrescător sau alfabetic). Cu astfel de tabele puteți utiliza căutarea relativă, care este similară cu utilizarea unei funcții IF infinite . Pentru valorile care nu pot fi căutate sau nu sunt sortate, utilizați căutarea absolută pentru a găsi valoarea exactă.

Tutorial video despre utilizarea funcției CĂUTARE V

Exemplul 1: Funcția VLOOKUP pentru a evalua evaluările studenților și ale personalului

Să presupunem că aveți un foaia matricolă de student după cum urmează:

Iar tabelul de reglementare a clasamentului este următorul:

Acum vom folosi funcția VLOOKUP pentru a introduce evaluări pentru studenți. Veți observa că tabelul de clasare a fost aranjat în ordine de la mic la mare (de la slab la bun), așa că în acest caz putem folosi căutarea relativă.

În Excel aceste 2 tabele sunt prezentate după cum urmează:

Aici, valoarea de detectat se află în coloana C, începând cu rândul 6. Intervalul de căutare este $A$18:$B$21, ordinea coloanei care conține valoarea detectată este 2.

În celula D6, introduceți formula: =VLOOKUP($C6,$A$18:$B$21,2,1). Aceasta este o formulă de căutare relativă, puteți efectua o căutare absolută dacă doriți (sau pentru că clasamentul nu este sortat) adăugând 0 la formula astfel: =VLOOKUP($C6 ,$A$18:$B$21,2, 0). Apasa Enter.

Faceți clic pe celula D6, un mic pătrat apare în colțul din dreapta jos, faceți clic pe el și trageți în jos tabelul pentru a copia formula de notare pentru studenții rămași.

La acel moment, avem rezultatele utilizării funcției CĂUTARE VL pentru a clasifica performanța academică a studenților după cum urmează:

Vă întrebați de ce trebuie folosit $ înainte de C6? $ va menține coloana C fixă, schimbând rândurile doar când trageți formula în jos tabel. Există, de asemenea, $A$18:$B$21 pentru a ajuta la repararea tabelului cu regulile de clasare, făcându-l să nu se schimbe atunci când trageți formula.

Exemplul 2: Funcția VLOOKUP caută absolut pentru a obține date

Să presupunem că aveți un tabel de date despre angajați, care stochează codul angajatului, numele complet și funcția. Un alt tabel stochează codul de angajat, orașul natal și nivelul de educație. Acum doriți să completați informații despre orașul natal și nivelul de educație pentru fiecare angajat, ce ar trebui să faceți?

Să presupunem că aveți un tabel cu angajații și orașele natale ale angajaților, după cum urmează:

Acum doriți să completați informații despre orașul natal pentru angajați. În celula D4, introduceți formula de căutare absolută după cum urmează: =Vlookup($A4,$A$16:$C$25,2,0)

Apoi apăsați Enter. Faceți clic pe pătratul mic care apare în colțul celulei D4 și trageți-l în jos peste tabel pentru a copia formula altor angajați.

Pentru a completa informațiile de calificare pentru angajați, în celula E4, introduceți formula de căutare absolută: =VLOOKUP($A4,$A$16:$C$25,3,0)

Continuați să apăsați Enter și să derulați în jos pentru a copia formula angajaților rămași, vom obține următorul rezultat:

Exemplul 3: Utilizați VLOOKUP pentru a extrage date

Continuând cu setul de date din exemplul 2, vom găsi acum orașele natale a 3 angajați: Nguyen Hoang Anh, Tran Van Anh și Nguyen Quang Vinh. L-am extras într-un nou tabel F15:G18.

Voi face această căutare pe tabelul A3:E13, după ce am completat orașul natal și calificările. Acum, introduceți următoarea formulă de căutare absolută în celula G16: =VLOOKUP($F16,$B$3:$E$13,3,0) > apăsați Enter.

Copiind formula pentru restul de 2 angajați obținem următorul rezultat:

Rețineți că în acest exemplu, valoarea de detecție este în coloana B, deci aria de detectare este calculată din coloana B și nu din coloana A.

Exemplul 4: Utilizați funcția CĂUTARE V pe 2 foi Excel diferite

Revenind la setul de date din exemplul 2, după ce se completează calificarea angajatului și orașul natal, denumim foaia QTM.

Într-o altă foaie a foii de calcul, numită QTM1, trebuie să obțineți informații despre calificările și poziția angajatului cu ordinea de aranjare a angajatului schimbat. Acesta este momentul în care vedeți adevărata putere a funcției VLOOKUP.

Pentru a găsi date despre „Calificările” angajatului, introduceți formula: =VLOOKUP($B4,QTM!$B$3:$E$13,4,0) în celula C4 din foaia QTM1.

Acolo:

• B4 este coloana care conține valoarea utilizată pentru detecție.
• QTM! este numele foii care contine tabelul cu valoarea de detectat.Dupa numele foii se adauga un !
• $B$3:$E$13 este tabelul care conține valorile de căutare și foaia care conține tabelul (QTM).
• 4 este numărul de serie al coloanei „Calificări”, calculat din coloana „Nume complet” de pe foaia QTM.
• 0 este detecția absolută.

Apăsați Enter, apoi copiați formula pentru toți angajații rămași din tabel, obținem următorul rezultat:

Pentru a găsi datele „Poziție” ale angajatului, în celula D4 a foii QTM1, introduceți formula: =VLOOKUP($B4,QTM!$B$3:$E$13,2,0), apăsați Enter.

Copiind formula pentru angajații rămași, obținem următoarele:

Pentru ce să folosești funcția VLOOKUP?

Mai întâi trebuie să găsim răspunsul exact la întrebarea „pentru ce folosim funcția CĂUTARE VL?”.

Funcția VLOOKUP este utilizată pentru a sprijini căutarea informațiilor într-un câmp de date sau o listă pe baza identificatorilor disponibili.

De exemplu, inserarea funcției VLOOKUP cu un cod de produs într-o altă foaie de calcul va afișa informațiile despre produs corespunzătoare codului respectiv. Aceste informații pot fi descriere, preț, cantitate de inventar, în funcție de conținutul rețetei pe care o scrii.

Cu cât cantitatea de informații pe care trebuie să o găsiți este mai mică, cu atât va fi mai dificil să scrieți funcția CĂUTARE V. De obicei, ați folosi această funcție într-o foaie de lucru reutilizabilă ca șablon. De fiecare dată când este introdus codul de produs corespunzător, sistemul va prelua toate informațiile necesare despre produsul corespunzător.

Lucruri de reținut despre funcția VLOOKUP

Iată o listă de lucruri importante de reținut despre funcția CĂUTARE V din Excel:

• Când range_lookup este omis, VLOOKUP va permite potriviri non-exacte, dar va folosi în continuare potriviri exacte dacă sunt disponibile.
• Cea mai mare limitare a funcției este că ia întotdeauna valoarea din dreapta. Funcția va prelua date din coloanele din dreapta primei coloane din tabel.
• Dacă coloana de căutare conține valori duplicate, funcția CĂUTARE V se va potrivi numai cu prima valoare.
• Funcția nu face distincție între majuscule și minuscule.
• Să presupunem că există o formulă VLOOKUP existentă într-o foaie de lucru. În acest caz, formulele se pot rupe dacă inserați o coloană în tabel, deoarece valorile indexului coloanei sunt codificate, care nu se schimbă automat când coloanele sunt inserate sau șterse.
• CĂUTARE V permite utilizarea caracterelor wildcard, de exemplu, asteriscuri (*) sau semne de întrebare (?).
• Să presupunem că într-un tabel lucrați cu o funcție care conține numere introduse ca text. Dacă primiți doar numere ca text dintr-o coloană dintr-un tabel, asta nu contează. Dar dacă prima coloană a tabelului conține numere introduse ca text, #N/A! se va afișa dacă și valoarea de căutare nu este text.
• Eroare #N/A! Apare dacă funcția LOOKUP nu găsește o potrivire pentru căutarea_valoare furnizată.
• Eroare #REF! se întâmplă dacă:
  • Argumentul col_index_num este mai mare decât numărul de coloane din table_array furnizat
  • Formula a încercat să facă referire la celule care nu există.
• Eroare #VALUE! se întâmplă dacă:
  • Argumentul col_index_num este mai mic de 1 sau nu este recunoscut ca valoare numerică
  • Argumentul range_lookup nu este recunoscut ca una dintre valorile logice TRUE sau FALSE.

Diferențele esențiale dintre funcțiile VLOOKUP și XLOOKUP în Excel

Y - Da

N - Nu

Vezi mai mult:

• Cum se utilizează funcția IF în Excel
• Cum se utilizează funcția AVERAGEIF în Excel

După ce știți cum să utilizați VLookup, trebuie să cunoașteți și erorile comune și cum să remediați problemele.

Erori frecvente la utilizarea funcției CĂUTARE V din Excel

Valoarea de căutare este în coloana greșită

Una dintre cele mai frecvente erori pe care le veți întâlni atunci când utilizați o formulă CĂUTARE V este că funcția returnează doar valoarea #N/A. Această eroare apare atunci când nu poate găsi valoarea de căutare pe care i-ați cerut VLOOKUP să o caute.

În unele cazuri, puteți utiliza funcția XLOOKUP în loc de VLOOKUP, cu toate acestea, această eroare poate fi corectată cu ușurință doar în funcția VLOOKUP.

Cum să remediați problema:

Motivul pentru care VLOOKUP nu a găsit valoarea dorită poate fi faptul că valoarea nu este în tabel. Cu toate acestea, dacă returnează toate valorile #N/A, valoarea de căutare este text, atunci este cel mai probabil pentru că poate citi doar de la stânga la dreapta.

Când creați un tabel în Excel pentru VLOOKUP, asigurați-vă că plasați valoarea de căutare în stânga valorii pe care doriți să o returnați. Cel mai simplu mod de a face acest lucru este să îl plasați în prima coloană a tabelului.

Acum formula va returna o valoare:

În general, VLOOKUP este o modalitate excelentă de a interoga mai rapid datele în Microsoft Excel. Deși interogările VLOOKUP rulează vertical pe coloane și au alte câteva limitări, Microsoft actualizează continuu caracteristicile de căutare ale Excel pentru a le extinde aplicabilitatea. De exemplu, HLOOKUP, XLOOKUP... vă pot ajuta să căutați date în multe direcții diferite. LuckyTemplates.com va continua să vă ofere informații despre cum să utilizați această funcție în articolele următoare.