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 trei cele mai populare 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 - tabelul de împrumut și tabelul de 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 categorie Căutare și referință este 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 securitate) deplasându-se de sus în jos și, găsindu-le, afișează valoarea celulei adiacente (tipul garanției de credit).). 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 categorie (Căutare și referință) găsiți funcția VLOOKUP și apăsați Bine . Va apărea o fereastră pentru introducerea argumentelor funcției:

Completați-le unul câte unul:

• Lookup Value - numărul acordului, pe care funcția îl va căuta în coloana din stânga extremă a tabelului de provizionare. În cazul nostru - numărul acordului „KF568” din celulă C2 .
• Table Array - tabelul din care vom obține valorile necesare este tabelul nostru de aprovizionare. Vom folosi un interval pentru referință $G$2:$H$11 (intervalul este fixat cu semnul dolarului ($) , astfel încât să nu se miște în jos la copierea formulei).
• Column index number - seria (nu o literă!) al coloanei din tabelul cu provizii, din care vom prelua denumirea provizionului. Prima coloană a tabelului de provizioane are numărul de ordine 1, așa că trebuie să luăm tipul de furnizare din a doua coloană (numărul 2).
• Range Lookup - putem introduce doar două valori în acest câmp: TRUE sau FALSE:
  
  1) Dacă introducem o valoare 0 sau FALSE , atunci asta înseamnă de fapt că numai căutarea este permisă cu o potrivire exacta , adică dacă funcția nu găsește numărul acordului în tabelul de aprovizionare (dacă nu există), atunci va fi afișată o eroare #N / A (nu există date).
  
  2) Dacă introducem o valoare 1 sau TRUE , aceasta va însemna că este permisă căutarea nu numai exactă, ci și coincidenta aproximativa . În cele mai multe cazuri, o astfel de înlocuire aproximativă poate provoca un rezultat fals când VLOOKUP va substitui valorile altor acorduri. Deci, ar trebui să utilizați căutarea aproximativă numai pentru valori numerice (presortate în ordine crescătoare).

Rămâne de apăsat Bine și 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 modifica 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ţie VLOOKUP returnează o eroare (#N / A) dacă:

1. Căutarea exactă este permisă (argument Range Lookup=0) și valoarea căutată nu este în tabel (Table) .
2. Căutare aproximativă permisă (Range Lookup=1), dar în tabel (Table) , în care căutăm valori, numele nu sunt sortate în ordine crescătoare.
3. Formatul celulei din care luăm numărul tranzacției (de exemplu, C2 în cazul nostru) și formatul de celulă al primei coloane (G2:G11) tabelele sunt diferite (de exemplu, numerice ș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ții VALUE și TEXT pentru a converti formatele de date. Arata cam asa:
   =VLOOKUP(TEXT(C2);$G$2:$H$11;0) .
4. 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) , când funcția nu poate găsi o potrivire exactă, puteți utiliza funcția IFERROR . Această funcție verifică dacă există o eroare (#N / A) este rezultatul muncii VLOOKUP și dacă da, scoate o bandă goală ( "" ) sau zero, dacă nu - rezultatul lucrării VLOOKUP .

Articole pe tema:

• Funcția îmbunătățită VLOOKUP (VLOOKUP2)
• VLOOKUP pentru combinație în funcție de două condiții (VLOOKUP3)
• Îmbinare rapidă a meselor mari (VLOOKUP2D)