Alăturați-vă meselor (VLOOKUP)

Ce este VLOOKUP

Funcția încorporată VLOOKUP este una dintre cele mai puternice funcții din Excel. Aparține celor mai populare trei din Excel — după SUM și AVERAGE. Sarcina ei este să găsească valoarea dorită în tabelul de date și să o afișeze în celula specificată.

Sintaxa funcției include o valoare de căutare (ceea ce căutați), un tabel cu una sau mai multe coloane (unde să căutați), un număr de index al coloanei (din ce coloană să returnați datele) și un tip de căutare (un argument suplimentar). care vă permite să alegeți un rezultat aproximativ sau exact).

Funcția VLOOKUP este utilizată în mod obișnuit pentru analiza financiară, gestionarea datelor și lucrul cu bazele de date. Folosind această caracteristică, utilizatorii pot găsi rapid și ușor informații într-un set mare de date, ceea ce poate economisi timp și îmbunătăți acuratețea.

Funcția VLOOKUP caută valori pe verticală, adică printre rânduri (acest lucru este indicat de prima literă V - Verticală). În Excel, există și o funcție similară HLOOKUP, care are o funcționalitate similară, dar face o căutare orizontală (H - Orizontală) pe coloane.

Un exemplu de problemă

Să presupunem că avem două tabele cu date - un tabel cu împrumuturi și un tabel cu garanții:

Trebuie să inserăm automat garanții în tabelul de împrumut, pe baza numărului contractului, pentru raportări ulterioare.

Soluţie

În Excel în setul standard de funcții din categoria Căutare și referință există o funcție VLOOKUP. Această funcție caută valoarea specificată (în exemplul nostru, numărul acordului) în coloana din stânga a tabelului specificat (tabelul de garanții) deplasându-se de sus în jos și, găsindu-le, afișează valoarea celulei adiacente (tipul garanției de împrumut).). Schematic, funcționarea funcției arată astfel:

Deci, să folosim funcția VLOOKUP. Selectați celula în care va fi introdusă (E2) și deschideți expertul pentru formule (meniul Insert - Function). În categoria (Căutare și referință), găsiți funcția VLOOKUP și faceți clic pe OK. Va apărea o fereastră pentru introducerea argumentelor funcției:

Completați-le unul câte unul:

Căutare Value- numărul acordului, pe care funcția îl va căuta în coloana din stânga a tabelului de provizionare. În cazul nostru - numărul acordului "KF568" din celula C2.
Table Array- tabelul din care vom obține valorile necesare este tabelul nostru de aprovizionare. Pentru referință, vom folosi intervalul $G$2:$H$11 (intervalul este fixat cu un semn dolar ($), astfel încât să nu se miște în jos la copierea formulei).
Column număr de index- seria (nu o literă!) al coloanei din tabelul cu provizii, din care vom prelua denumirea provizionului. Prima coloană a tabelului de provizii are numărul de ordine 1, așa că trebuie să luăm tipul de furnizare din a doua coloană (numărul 2).
Căutare Range- putem introduce doar două valori în acest câmp: TRUE sau FALSE:

1) Dacă introducem valoarea 0 sau FALSE, înseamnă de fapt că căutarea este permisă numai cu o potrivire exacta, adică dacă funcția nu găsește numărul de acord în tabelul de aprovizionare (dacă nu există), atunci va fi afișată eroarea #N/A (fără date).

2) Dacă introducem o valoare de 1 sau TRUE, va însemna că nu numai căutarea exactă este permisă, ci și coincidenta aproximativa. În cele mai multe cazuri, această înlocuire aproximativă poate provoca rezultate false atunci când VLOOKUP înlocuiește valorile altor tranzacții. Deci, ar trebui să utilizați căutarea aproximativă numai pentru valori numerice (presortate în ordine crescătoare).

Rămâne să apăsați OK și să copiați funcția introdusă pe întreaga coloană.

Dezavantajele funcției VLOOKUP

Principalul dezavantaj este că căutarea valorii dorite poate avea loc doar în prima coloană a intervalului dat, iar funcția poate returna valoarea dorită doar din coloanele din dreapta.

Al doilea dezavantaj al VLOOKUP este că funcția nu mai funcționează dacă eliminați sau adăugați o coloană la tabelul de căutare. Un element inserat sau șters va schimba rezultatul formulei, deoarece sintaxa funcției necesită să specificați întregul interval și numărul de coloană specific din care doriți să extrageți datele.

De asemenea, funcția VLOOKUP are o limită de lungime de căutare de 255 de caractere, altfel va fi returnată eroarea #VALUE!

Probleme la lucrul cu funcția

Funcția VLOOKUP returnează o eroare (#N/A) dacă:

Căutarea exactă este permisă (argument Range Lookup=0) și valoarea căutată nu este în tabel (Table).
Căutare aproximativă permisă (Range Lookup=1), dar în tabelul (Table) în care căutăm valorile, numele nu sunt sortate în ordine crescătoare.
Formatul celulei din care luăm numărul tranzacției (de exemplu, C2 în cazul nostru) și formatul celulei primei coloane (G2:G11) a tabelului sunt diferite (de exemplu, numeric și text). Această situație este tipică în special atunci când sunt folosite coduri numerice în locul numelor textuale (numere de cont, coduri de identificare, date etc.). În acest caz, puteți utiliza funcțiile VALUE și TEXT pentru a converti formatele de date. Arata cam asa:
=VLOOKUP(TEXT(C2);$G$2:$H$11;0).
Funcția nu găsește o valoare deoarece codul conține spații și caractere invizibile care nu se imprimă (alimentări cu bandă etc.). În acest caz, puteți utiliza funcția text (TRIM) și (CLEAN) pentru a le elimina:
=VLOOKUP(TRIM(CLEAN(C2));$G$2:$H$11;0).

Suprimarea erorilor

Pentru a suprima mesajul de eroare (#N/A) atunci când funcția nu poate găsi o potrivire exactă, puteți utiliza funcția IFERROR. Această funcție verifică dacă eroarea (#N/A) este rezultatul lui VLOOKUP și, dacă da, returnează un șir gol ("") sau nul, în caz contrar rezultatul lui VLOOKUP.

Articole pe tema: