Funcția VLOOKUP în Excel – Ghid pentru începători: Sintaxă și exemple

Cuprins

Funcția VLOOKUP în Excel – descriere generală și sintaxă
- Sintaxa funcției CĂUTARE V
CĂUTARE V Exemple
Potrivire exactă sau aproximativă în funcția CĂUTARE V
- Exemplul 1: Găsirea unei potriviri exacte cu VLOOKUP
- Exemplul 2: Utilizarea VLOOKUP pentru a găsi o potrivire aproximativă
VLOOKUP în Excel - trebuie să vă amintiți acest lucru!

Astăzi începem o serie de articole care descriu una dintre cele mai utile caracteristici ale Excel − VPR (CĂUTARE V). Această funcție, în același timp, este una dintre cele mai complexe și mai puțin înțelese.

În acest tutorial pe VPR Voi încerca să expun elementele de bază cât mai simplu posibil pentru a face procesul de învățare cât mai clar posibil pentru utilizatorii fără experiență. În plus, vom studia câteva exemple cu formule Excel care vor demonstra cele mai comune cazuri de utilizare pentru funcție VPR.

Funcția VLOOKUP în Excel – descriere generală și sintaxă

Deci ce este VPR? Ei bine, în primul rând, este o funcție Excel. Ce face ea? Acesta caută valoarea pe care o specificați și returnează valoarea corespunzătoare din cealaltă coloană. Tehnic vorbind, VPR caută valoarea din prima coloană a intervalului dat și returnează rezultatul dintr-o altă coloană din același rând.

În cea mai comună aplicație, funcția VPR caută în baza de date un anumit identificator unic și extrage unele informații legate de acesta din baza de date.

Prima literă din numele funcției VPR (CĂUTARE V) înseamnă Вvertical (Vvertical). După el poți distinge VPR din GPR (HLOOKUP), care caută o valoare în rândul de sus al unui interval − Гorizontal (Horizontal).

Funcţie VPR disponibil în Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP și Excel 2000.

Sintaxa funcției CĂUTARE V

Funcţie VPR (CĂUTARE V) are următoarea sintaxă:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

După cum puteți vedea, o funcție VPR în Microsoft Excel are 4 opțiuni (sau argumente). Primele trei sunt obligatorii, ultima opțională.

lookup_value (lookup_value) – Valoarea de căutat. Aceasta poate fi o valoare (număr, dată, text) sau o referință de celulă (care conține valoarea de căutare) sau o valoare returnată de o altă funcție Excel. De exemplu, această formulă va căuta valoarea 40:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

Dacă valoarea de căutare este mai mică decât cea mai mică valoare din prima coloană a intervalului căutat, funcția VPR va raporta o eroare #LA (#N / A).

table_array (tabel) – două sau mai multe coloane de date. Amintiți-vă, funcția VPR caută întotdeauna valoarea din prima coloană a intervalului dat în argument table_array (masa). Intervalul care poate fi vizualizat poate conține diverse date, cum ar fi text, date, numere, valori booleene. Funcția nu ține seama de majuscule, ceea ce înseamnă că caracterele majuscule și minuscule sunt considerate la fel. Deci formula noastră va căuta valoarea 40 în celule din A2 la A15, deoarece A este prima coloană a intervalului A2:B15 dat în argument table_array (masa):
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

col_index_num (număr_coloană) este numărul coloanei din intervalul dat din care va fi returnată valoarea din rândul găsit. Coloana din stânga din intervalul dat este 1, a doua coloană este 2, a treia coloană este 3 si asa mai departe. Acum puteți citi întreaga formulă:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)
Formula care cauta valoare 40 în intervalul A2: A15 și returnează valoarea corespunzătoare din coloana B (deoarece B este a doua coloană din intervalul A2:B15).

Dacă valoarea argumentului col_index_num (număr_coloană) mai mic decât 1apoi VPR va raporta o eroare #VALOARE! (#VALOARE!). Și dacă este mai mare decât numărul de coloane din interval table_array (tabel), funcția va returna o eroare #REF! (#LEGĂTURĂ!).

interval_lookup (range_lookup) – determină ce să cauți:
- potrivire exactă, argumentul trebuie să fie egal FALS (FALS);
- potrivire aproximativă, argument egal COD ADEVĂRAT (ADEVĂRAT) sau nu este specificat deloc.
Acest parametru este opțional, dar foarte important. Mai târziu în acest tutorial VPR Vă voi arăta câteva exemple care explică cum să scrieți formule pentru găsirea potrivirilor exacte și aproximative.

CĂUTARE V Exemple

Sper ca functia VPR devin puțin mai clar pentru tine. Acum să ne uităm la câteva cazuri de utilizare VPR în formule cu date reale.

Cum să utilizați VLOOKUP pentru a căuta într-o altă foaie Excel

În practică, formule cu o funcție VPR sunt rareori folosite pentru a căuta date pe aceeași foaie de lucru. De cele mai multe ori, veți căuta în sus și veți prelua valorile corespunzătoare dintr-o altă foaie.

Pentru a putea utiliza VPR, caută într-o altă foaie Microsoft Excel, Trebuie să în argument table_array (tabel) specificați numele foii cu un semn de exclamare urmat de un interval de celule. De exemplu, următoarea formulă arată că intervalul A2: B15 se află pe o foaie numită Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Desigur, numele foii nu trebuie introdus manual. Începeți doar să introduceți formula și când vine vorba de argument table_array (tabel), comutați la foaia dorită și selectați intervalul dorit de celule cu mouse-ul.

Formula prezentată în captura de ecran de mai jos caută textul „Produs 1” în coloana A (este prima coloană a intervalului A1:B2) pe o foaie de lucru Prețuri.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Vă rugăm să rețineți că atunci când căutați o valoare de text, trebuie să o includeți între ghilimele (""), așa cum se face de obicei în formulele Excel.

Pentru argumentare table_array (tabel) este de dorit să folosiți întotdeauna referințe absolute (cu semnul $). În acest caz, intervalul de căutare va rămâne neschimbat atunci când copiați formula în alte celule.

Căutați într-un alt registru de lucru cu VLOOKUP

A functiona VPR a lucrat între două registre de lucru Excel, trebuie să specificați numele registrului de lucru între paranteze drepte înainte de numele foii.

De exemplu, mai jos este o formulă care caută valoarea 40 pe foaie Sheet2 in carte Numere.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Iată cel mai simplu mod de a crea o formulă în Excel cu VPRcare face legătura cu un alt registru de lucru:

Deschide ambele cărți. Acest lucru nu este necesar, dar este mai ușor să creați o formulă în acest fel. Nu doriți să introduceți manual numele registrului de lucru, nu-i așa? În plus, vă va proteja de greșelile de scriere accidentale.
Începeți să tastați o funcție VPRiar când vine vorba de argument table_array (tabel), comutați la alt registru de lucru și selectați intervalul de căutare necesar în acesta.

Captura de ecran de mai jos arată formula cu căutarea setată la un interval din registrul de lucru PriceList.xlsx pe foaie Prețuri.

Funcţie VPR va funcționa chiar și atunci când închideți registrul de lucru căutat și calea completă către fișierul registrului de lucru apare în bara de formule, după cum se arată mai jos:

Dacă numele registrului de lucru sau al foii conține spații, atunci acesta trebuie inclus în apostrofe:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Cum să utilizați un interval sau un tabel numit în formule cu CĂUTARE V

Dacă intenționați să utilizați același interval de căutare în mai multe funcții VPR, puteți crea un interval denumit și puteți introduce numele acestuia în formulă ca argument table_array (masa).

Pentru a crea un interval denumit, pur și simplu selectați celulele și introduceți un nume adecvat în câmp Prenume, în stânga barei de formule.

Acum puteți nota următoarea formulă pentru a afla prețul unui produs Produs 1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

Majoritatea numelor de intervale funcționează pentru întregul registru de lucru Excel, deci nu este nevoie să specificați numele foii pentru argument table_array (tabel), chiar dacă formula și intervalul de căutare sunt pe foi de lucru diferite. Dacă se află în registre de lucru diferite, atunci înainte de numele intervalului trebuie să specificați numele registrului de lucru, de exemplu, astfel:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

Deci formula pare mult mai clară, de acord? De asemenea, utilizarea intervalelor denumite este o alternativă bună la referințele absolute, deoarece intervalul denumit nu se modifică atunci când copiați formula în alte celule. Aceasta înseamnă că puteți fi sigur că intervalul de căutare din formulă va rămâne întotdeauna corect.

Dacă convertiți o serie de celule într-o foaie de calcul Excel cu drepturi depline utilizând comanda Tabel fila (Tabel). inserare (Inserați), apoi când selectați un interval cu mouse-ul, Microsoft Excel va adăuga automat numele coloanelor (sau numele tabelului dacă selectați întregul tabel) la formulă.

Formula finală va arăta cam așa:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

Sau poate chiar așa:

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

Când utilizați intervale denumite, legăturile vor indica aceleași celule, indiferent unde copiați funcția VPR în registrul de lucru.

Folosirea caracterelor metalice în formulele VLOOKUP

Ca și în cazul multor alte funcții, VPR Puteți utiliza următoarele caractere joker:

Semnul întrebării (?) – înlocuiește orice caracter.
Asterisc (*) – înlocuiește orice succesiune de caractere.

Utilizarea wildcards în funcții VPR poate fi util în multe cazuri, de exemplu:

Când nu vă amintiți exact textul pe care trebuie să îl găsiți.
Când doriți să găsiți un cuvânt care face parte din conținutul unei celule. Să știi asta VPR caută după conținutul celulei ca întreg, ca și cum opțiunea ar fi activată Potriviți întregul conținut al celulei (întreaga celulă) în căutarea standard Excel.
Când o celulă conține spații suplimentare la începutul sau la sfârșitul conținutului. Într-o astfel de situație, îți poți strânge mintea pentru o lungă perioadă de timp, încercând să-ți dai seama de ce formula nu funcționează.

Exemplul 1: Căutați text care începe sau se termină cu anumite caractere

Să presupunem că doriți să căutați un anumit client în baza de date prezentată mai jos. Nu vă amintiți numele lui de familie, dar știți că începe cu „ack”. Iată o formulă care va face treaba bine:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Acum că sunteți sigur că ați găsit numele corect, puteți utiliza aceeași formulă pentru a afla suma plătită de acest client. Pentru a face acest lucru, trebuie doar să schimbați al treilea argument al funcției VPR la numărul de coloană dorit. În cazul nostru, aceasta este coloana C (a treia în interval):

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Iată mai multe exemple cu metacaractere:

~ Găsiți un nume care se termină în „om”:

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Găsiți un nume care începe cu „ad” și se termină cu „fiu”:

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Prenumele îl găsim în listă, format din 5 caractere:

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

A functiona VPR cu wildcards a funcționat corect, ca al patrulea argument pe care ar trebui să-l folosiți întotdeauna FALS (FALS). Dacă intervalul de căutare conține mai multe valori care potrivesc termenii de căutare cu metacaractere, atunci prima valoare găsită va fi returnată.

Exemplul 2: combinați metacaracterele și referințele de celule în formulele CĂUTARE V

Acum să ne uităm la un exemplu puțin mai complex despre cum să căutați folosind funcția VPR după valoarea într-o celulă. Imaginează-ți că coloana A este o listă de chei de licență, iar coloana B este o listă de nume care dețin o licență. În plus, aveți o parte (mai multe caractere) dintr-un fel de cheie de licență în celula C1 și doriți să găsiți numele proprietarului.

Acest lucru se poate face folosind următoarea formulă:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Această formulă caută valoarea din celula C1 în intervalul dat și returnează valoarea corespunzătoare din coloana B. Rețineți că în primul argument, folosim un caracter și (&) înainte și după referința celulei pentru a lega șirul de text.

După cum puteți vedea în figura de mai jos, funcția VPR returnează „Jeremy Hill” deoarece cheia de licență conține secvența de caractere din celula C1.

Rețineți că argumentul table_array (tabelul) din captura de ecran de mai sus conține numele tabelului (Tabelul 7) în loc să specifice un interval de celule. Aceasta este ceea ce am făcut în exemplul anterior.

Potrivire exactă sau aproximativă în funcția CĂUTARE V

Și, în sfârșit, să aruncăm o privire mai atentă la ultimul argument care este specificat pentru funcție VPR - interval_lookup (interval_view). După cum am menționat la începutul lecției, acest argument este foarte important. Puteți obține rezultate complet diferite în aceeași formulă cu valoarea ei COD ADEVĂRAT (ADEVĂRAT) sau FALS (FALS).

Mai întâi, să aflăm ce înseamnă Microsoft Excel prin potriviri exacte și aproximative.

Dacă argumentul interval_lookup (range_lookup) este egal cu FALS (FALSE), formula caută o potrivire exactă, adică exact aceeași valoare ca cea dată în argument lookup_value (valoare_căutare). Dacă în prima coloană a intervalului table_array (tabelul) întâlnește două sau mai multe valori care se potrivesc cu argumentul lookup_value (search_value), apoi va fi selectat primul. Dacă nu se găsesc potriviri, funcția va raporta o eroare #LA (#N / A). De exemplu, următoarea formulă va raporta o eroare #LA (#N/A) dacă nu există nicio valoare în intervalul A2:A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)
Dacă argumentul interval_lookup (range_lookup) este egal cu COD ADEVĂRAT (ADEVĂRAT), formula caută o potrivire aproximativă. Mai exact, mai întâi funcția VPR caută o potrivire exactă și, dacă nu este găsită, selectează una aproximativă. O potrivire aproximativă este cea mai mare valoare care nu depășește valoarea specificată în argument. lookup_value (valoare_căutare).

Dacă argumentul interval_lookup (range_lookup) este egal cu COD ADEVĂRAT (adevărat) sau nu este specificat, atunci valorile din prima coloană a intervalului trebuie sortate în ordine crescătoare, adică de la cel mai mic la cel mai mare. În caz contrar, funcția VPR poate returna un rezultat eronat.

Pentru a înțelege mai bine importanța alegerii COD ADEVĂRAT (ADEVĂRUL) sau FALS (FALSE), să ne uităm la câteva formule cu funcția VPR si uita-te la rezultate.

Exemplul 1: Găsirea unei potriviri exacte cu VLOOKUP

După cum vă amintiți, pentru a căuta o potrivire exactă, al patrulea argument al funcției VPR ar trebui să conteze FALS (FALS).

Să revenim la tabel de la primul exemplu și să aflăm ce animal se poate mișca cu o viteză 50 mile pe oră. Cred că această formulă nu vă va cauza dificultăți:

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Rețineți că intervalul nostru de căutare (coloana A) conține două valori 50 – în celule A5 и A6. Formula returnează valoarea din celulă B5. De ce? Pentru că atunci când căutați o potrivire exactă, funcția VPR folosește prima valoare găsită care se potrivește cu cea căutată.

Exemplul 2: Utilizarea VLOOKUP pentru a găsi o potrivire aproximativă

Când utilizați funcția VPR pentru a căuta o potrivire aproximativă, adică atunci când argumentul interval_lookup (range_lookup) este egal cu COD ADEVĂRAT (TRUE) sau omis, primul lucru pe care trebuie să-l faceți este să sortați intervalul după prima coloană în ordine crescătoare.

Acest lucru este foarte important deoarece funcția VPR returnează următoarea valoare cea mai mare după cea dată și apoi căutarea se oprește. Dacă neglijezi sortarea corectă, vei ajunge cu rezultate foarte ciudate sau cu un mesaj de eroare. #LA (#N / A).

Acum puteți utiliza una dintre următoarele formule:

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

După cum puteți vedea, vreau să aflu care dintre animale are viteza cea mai apropiată 69 mile pe oră. Și iată rezultatul pe care mi-a returnat funcția VPR:

După cum puteți vedea, formula a returnat un rezultat Antilopă (Antilope), a cărei viteză 61 mile pe oră, deși lista include și ghepard (Ghepard) care aleargă cu viteză 70 mile pe oră, iar 70 este mai aproape de 69 decât 61, nu-i așa? De ce se întâmplă asta? Pentru că funcția VPR când se caută o potrivire aproximativă, returnează cea mai mare valoare care nu este mai mare decât cea căutată.

Sper că aceste exemple să arunce puțină lumină în lucrul cu funcția VPR în Excel și nu o mai privești ca pe un outsider. Acum nu strica sa repetam pe scurt punctele cheie ale materialului pe care l-am studiat pentru a-l fixa mai bine in memorie.

VLOOKUP în Excel - trebuie să vă amintiți acest lucru!

Funcţie VPR Excel nu poate privi la stânga. Întotdeauna caută valoarea din coloana din stânga a intervalului dat de argument table_array (masa).
În funcțiune VPR toate valorile sunt insensibile la majuscule, adică literele mici și mari sunt echivalente.
Dacă valoarea pe care o căutați este mai mică decât valoarea minimă din prima coloană a intervalului căutat, funcția VPR va raporta o eroare #LA (#N / A).
Dacă al 3-lea argument col_index_num (număr_coloană) mai mic decât 1funcţie VPR va raporta o eroare #VALOARE! (#VALOARE!). Dacă este mai mare decât numărul de coloane din interval table_array (tabel), funcția va raporta o eroare #REF! (#LEGĂTURĂ!).
Utilizați referințe absolute de celule în argument table_array (tabel) astfel încât intervalul de căutare corect să fie păstrat la copierea formulei. Încercați să utilizați intervale sau tabele denumite în Excel ca alternativă.
Când efectuați o căutare de potrivire aproximativă, rețineți că prima coloană din intervalul pe care îl căutați trebuie sortată în ordine crescătoare.
În cele din urmă, amintiți-vă importanța celui de-al patrulea argument. Utilizați valori COD ADEVĂRAT (ADEVĂRUL) sau FALS (FALS) în mod deliberat și vei scăpa de multe dureri de cap.

În următoarele articole din tutorialul nostru de funcții VPR în Excel, vom afla exemple mai avansate, cum ar fi efectuarea diferitelor calcule folosind VPR, extragerea valorilor din mai multe coloane și multe altele. Vă mulțumesc că ați citit acest tutorial și sper să ne revedem săptămâna viitoare!