Tutorial Excel VLOOKUP pentru începători

Ce este VLOOKUP?

VLOOKUP (V înseamnă Vertical) este o funcție Excel încorporată care stabilește o relație între coloanele dintr-o foaie de calcul. Vă permite să căutați o valoare într-o coloană și să returnați valoarea corespunzătoare dintr-o altă coloană din același rând.

Sintaxa și argumentele VLOOKUP

Înainte de a aplica VLOOKUP, este util să înțelegeți structura formulei. Funcția primește patru argumente și urmează un model consistent în fiecare versiune de Excel.

• lookup_value — valoarea pe care doriți să o găsiți (o referință de celulă sau un literal).
• table_array — intervalul de celule care conține coloana de căutare și coloana de returnare.
• col_index_num — numărul coloanei din table_array din care se returnează valoarea (1 este cea mai din stânga).
• interval_lookup — FALSE pentru o potrivire exactă, TRUE (sau omis) pentru o potrivire aproximativă pe date sortate.

Important: Valoarea de căutare trebuie să se afle în coloana cea mai din stânga a table_array, iar VLOOKUP caută doar de la stânga la dreapta.

Utilizarea VLOOKUP

Când trebuie să găsiți informații specifice într-o foaie de calcul mare sau să recuperați același tip de valoare în mod repetat, funcția VLOOKUP economisește timp semnificativ în comparație cu filtrarea manuală.

Luați în considerare a Tabelul salariilor companiei întreținut de echipa financiară. Începeți cu o informație cunoscută — un index — și utilizați VLOOKUP pentru a obține valoarea necunoscută.

De exemplu, știți deja numele angajatului:

Și vrei să cauți salariul angajatului:

Foaie de calcul Excel pentru exemplul de mai sus:

Descărcați fișierul Excel de mai sus

Pentru a găsi salariul necunoscut al angajatului, introducem numărul angajatului. Code care este deja disponibil.

Prin aplicarea VLOOKUP, valoarea salariului corespunzător angajatului respectiv Code apare automat.

Cum se utilizează funcția CĂUTARE V în Excel

Urmați acest ghid pas cu pas pentru a aplica funcția VLOOKUP în Excel:

Pasul 1) Navigați la celula țintă

Faceți clic pe celula în care doriți să apară salariul angajatului selectat — în acest exemplu, celula H3.

Pasul 2) Introduceți funcția VLOOKUP =VLOOKUP()

Tastați funcția în celulă. Începeți cu un semn egal (care îi spune lui Excel că urmează o formulă) și apoi cu cuvântul cheie VLOOKUP: =CĂUTARE V().

Parantezele conțin setul de argumente (elementele de date de care are nevoie funcția).

Funcția VLOOKUP necesită patru argumente:

Pasul 3) Primul argument — valoarea de căutare

Primul argument este referința celulei pentru valoarea pe care doriți să o căutați. În acest caz, Angajat Code este valoarea de căutare, deci primul argument este H2 — celula al cărei conținut ar trebui să corespundă în Excel.

Pasul 4) Al doilea argument — matricea tabelului

Aceasta se referă la blocul de valori care urmează să fie căutat, cunoscut în Excel ca matrice de tabel sau tabel de căutare. În exemplul nostru, tabelul de căutare rulează de la B2 la E25.

NOTĂ: Coloana de căutare trebuie să fie cea mai din stânga coloană a matricei din tabel.

Pasul 5) Al treilea argument — col_index_num

Aceasta funcție indică funcției VLOOKUP care coloană din matricea tabelului conține valoarea returnată. Salariul angajatului se află în a patra coloană, deci indicele coloanei este 4.

Pasul 6) Al patrulea argument — potrivire exactă sau aproximativă

Ultimul argument este indicatorul de căutare în interval. Acesta controlează dacă VLOOKUP returnează o potrivire exactă sau aproximativă. Aici dorim o potrivire exactă (FALSE).

1. FALS — potrivire exactă.
2. TRUE — potrivire aproximativă.

Pasul 7) Apăsați Enter

Apăsați Enter pentru a completa formula. Inițial veți vedea o eroare deoarece niciun angajat nu Code a fost încă introdus în H2.

După ce introduceți un Angajat valid Code În H2, celula returnează Salariul Angajatului corespunzător.

Pe scurt, formula îi spune lui Excel că valorile cunoscute se află în coloana din stânga a datelor (Angajat Code). Funcția VLOOKUP scanează apoi tabelul și returnează valoarea din a patra coloană de pe rândul corespunzător — Salariul angajatului.

Acest exemplu a acoperit potrivirile exacte (cuvântul cheie FALSE). Următoarea secțiune explică potrivirile aproximative.

CĂUTARE V pentru potriviri aproximative (cuvânt cheie TRUE ca ultim parametru)

Să luăm în considerare un scenariu în care un tabel calculează reduceri pentru clienții care nu achiziționează exact zeci sau sute de articole.

După cum se arată mai jos, o companie aplică reduceri la cantități cuprinse între 1 și 10,000:

Descărcați fișierul Excel de mai sus

Un client rareori cumpără exact 100 sau 1,000 de unități. Modul de potrivire aproximativă permite funcției VLOOKUP să găsească cea mai apropiată valoare mai mică, în loc să insiste asupra unei cifre exacte. Pași:

Pas 1) Faceți clic pe celula în care va fi plasată funcția VLOOKUP — referința celulei I2.

Pas 2) Introduceți =VLOOKUP() în celulă și adăugați argumentele între paranteze.

Pasul 3) Argumentul 1: Introduceți referința celulei a cărei valoare trebuie să corespundă cu tabelul de căutare.

Pasul 4) Argumentul 2: Selectați tabelul de căutare — aici, coloanele Cantitate și Reducere.

Pasul 5) Argumentul 3: Introduceți indexul coloanei în tabelul de căutare din care se returnează valoarea corespondentă.

Pasul 6) Argumentul 4: Setați ultimul argument la TRUE pentru potriviri aproximative.

Pas 7) Apăsați Enter. Formula se aplică acum celulei. Când introduceți orice cantitate, Excel returnează banda de reducere pe baza potrivirii aproximative.

NOTĂ: Dacă lăsați al patrulea argument necompletat, Excel va avea implicit valoarea TRUE (potrivire aproximativă). Pentru potriviri aproximative, coloana de căutare trebuie sortată în ordine crescătoare.

Funcția de căutare aplicată între 2 foi diferite plasate în același registru de lucru

Acum luați în considerare un registru de lucru cu două foi. Foaia 1 listează Angajatul Code, Nume și Funcție; Foaia 2 enumeră Angajat Code și Salariul Angajaților.

FIȘA 1:

FIȘA 2:

Descărcați fișierul Excel de mai sus

Obiectivul este de a consolida toate datele de pe Foaia 1, așa cum se arată mai jos:

VLOOKUP poate agrega date, astfel încât angajatul Code, Numele și Salariul apar împreună pe o singură foaie.

Începem cu Foaia 2 deoarece aceasta oferă două argumente — coloana Salariul angajatului este aici și indicele coloanei este 2.

Vrem să găsim salariul care se potrivește fiecărui angajat Code.

Datele se întind de la A2 la B25 — aceasta este matricea noastră de tabel.

Pas 1) Treceți la Foaia 1 și introduceți titlurile afișate.

Pas 2) Faceți clic pe celula de lângă Salariul angajatului — celula F3 — unde va apărea formula VLOOKUP.

Introduceți funcția VLOOKUP: =VLOOKUP().

Pasul 3) Argumentul 1: Introduceți F2 — celula care conține Angajat Code pentru a se potrivi în tabelul de căutare.

Pasul 4) Argumentul 2: Tabelul de căutare se află pe cealaltă foaie, așa că faceți referire la el cu numele foii: Foaia2!A2:B25.

Pasul 5) Argumentul 3: Introduceți indexul coloanei în tabelul de căutare care conține valoarea returnată.

Pasul 6) Argumentul 4: Folosiți FALSE pentru o potrivire exactă, deoarece dorim salariul exact care corespunde fiecărui angajat. Code.

Pas 7) Apăsați Enter. Când introduceți un Angajat Code, celula returnează salariul corespunzător extras din Foaia 2.

Erori și remedieri comune ale funcției VLOOKUP

Chiar și utilizatorii experimentați se confruntă cu erori VLOOKUP. Cele mai frecvente și remedieri rapide:

• #N / A — Funcția VLOOKUP nu poate găsi valoarea de căutare. Verificați dacă există spații suplimentare, tipuri de date nepotrivite (numere stocate ca text) sau dacă valoarea există într-adevăr în prima coloană a funcției table_array.
• #REF! — col_index_num este mai mare decât numărul de coloane din table_array. Reduceți indexul coloanei sau extindeți intervalul.
• #VALOARE! — col_index_num este mai mic decât 1 sau un argument este invalid. Verificați sintaxa formulei.
• Rezultat greșit returnat — al patrulea argument este TRUE sau omis, dar coloana de căutare este nesortată. Comutați la FALSE sau sortați coloana crescător.
• Referințe blocate — atunci când copiați o formulă, utilizați referințe absolute (de exemplu, $B$2:$E$25) pentru ca table_array să nu se deplaseze.

VLOOKUP vs XLOOKUP: Pe care ar trebui să îl utilizați?

Microsoft a introdus XLOOKUP în Microsoft 365 și Excel 2021 ca înlocuitor modern pentru VLOOKUP. Elimină mai multe limitări ale funcției VLOOKUP și este acum alegerea recomandată în versiunile acceptate.

Caracteristică	CĂUTARE	CĂUTARE XL
Direcția de căutare	Numai de la stânga la dreapta	Orice direcție (stânga, dreapta, sus, jos)
Tipul de potrivire implicit	Aproximativ (TRUE)	Exact
Gestionarea erorilor „dacă nu se găsește”	Returnări #N/A	Argumentul if_not_found încorporat
Indexul coloanei	Număr codificat fix	Referință la un interval de coloane returnate
Disponibilitate	Toate versiunile Excel	Microsoft 365, Excel 2021, Excel pentru web

Când se alege VLOOKUP: Registrul de lucru trebuie să ruleze în Excel 2019 sau o versiune anterioară, sau păstrați formulele vechi. Când să alegeți XLOOKUP: Creați registre de lucru noi în Excel-ul modern și doriți căutări la stânga, o gestionare mai curată a erorilor și o potrivire exactă în mod implicit. Aflați mai multe despre funcțiile de căutare în Tutoriale Excel serie.

Concluzie

Cele trei scenarii de mai sus explică modul în care funcționează VLOOKUP pentru potriviri exacte, potriviri aproximative și referințe încrucișate. Exersați pe propriile seturi de date pentru a vă dezvolta fluența. VLOOKUP rămâne o caracteristică importantă în MS-Excel pentru gestionarea eficientă a datelor, iar XLOOKUP extinde acest set de instrumente în Excel-ul modern.

Întrebări frecvente