1Aug
VLOOKUP este una dintre cele mai utile funcții ale Excel și este și una dintre cele mai puțin înțelese.În acest articol, demonstrăm VLOOKUP prin intermediul unui exemplu din viața reală.Vom crea un șablon utilizabil pentru o companie fictivă.
VLOOKUP este o funcție Excel .Acest articol va presupune că cititorul are deja o înțelegere trecătoare a funcțiilor Excel și poate utiliza funcții de bază cum ar fi SUM, AVERAGE și TODAY.În cea mai comună utilizare, VLOOKUP este o bază de date , ceea ce înseamnă că funcționează cu tabele de baze de date - sau mai simplu, listează de lucruri într-o foaie de lucru Excel. Ce fel de lucruri? Ei bine, orice tip de .Este posibil să aveți o foaie de lucru care conține o listă de angajați, produse sau clienți sau CD-uri în colecția dvs. de CD-uri sau vedeți pe cerul de noapte. Nu contează cu adevărat.
Iată un exemplu de listă sau bază de date.În acest caz, este vorba de o listă de produse pe care compania noastră fictivă o vinde:
De obicei, listele de acest fel au un fel de identificator unic pentru fiecare element din listă.În acest caz, identificatorul unic este în coloana "Codul articolului".Notă: Pentru ca funcția VLOOKUP să funcționeze cu o bază de date / listă, acea listă
trebuie să conțină o coloană care să conțină identificatorul unic( sau "cheie" sau "ID") și acea coloană trebuie să fie prima coloană din tabel.Baza noastră de date de mai sus îndeplinește acest criteriu.Cea mai dificilă parte a utilizării VLOOKUP este înțelegerea exactă pentru ce este. Așadar, să vedem dacă putem obține primul clar:
VLOOKUP preia informațiile dintr-o bază de date / o listă pe baza unei instanțe furnizate a identificatorului unic.
În exemplul de mai sus, introduceți funcția VLOOKUP într-o altă foaie de calcul cu un cod de produs și vă va returna fie descrierea articolului respectiv, prețul acestuia, fie disponibilitatea acestuia( cantitatea sa "în stoc"), așa cum este descris înlista originală.Care dintre aceste informații vă va trimite înapoi? Ei bine, veți decide acest lucru atunci când creați formula.
Dacă tot ce ai nevoie este o singură informație din baza de date, ar fi o mulțime de probleme să mergi să construiești o formulă cu o funcție VLOOKUP în ea. De obicei, ați folosi acest tip de funcționalitate într-o foaie de calcul reutilizabilă, cum ar fi un șablon. De fiecare dată când cineva introduce un cod de element valid, sistemul va prelua toate informațiile necesare despre elementul corespunzător.
Să formăm un exemplu de acest lucru: Un Șablon de factură pe care îl putem reutiliza din nou în compania noastră fictivă.
Mai întâi pornim Excel și ne creăm o factură necompletată:
Acesta este modul în care va funcționa: Persoana care utilizează șablonul de factură va completa o serie de coduri de articole în coloana "A", iar sistemul va prelua fiecare elementdescrierea și prețul din baza de date a produselor noastre. Aceste informații vor fi folosite pentru a calcula totalul liniei pentru fiecare element( presupunând că vom introduce o cantitate valabilă).
Pentru a păstra acest exemplu simplu, vom găsi baza de date a produsului pe o foaie separată în același registru de lucru:
În realitate, este mai probabil ca baza de date a produsului să fie localizată într-un registru de lucru separat. Funcția VLOOKUP nu are nicio importanță, care nu are mare importanță dacă baza de date este localizată pe aceeași foaie, o altă foaie sau un registru de lucru complet diferit.
Așadar, am creat baza de date a produselor noastre, care arată astfel:
Pentru a testa formula VLOOKUP pe care o vom scrie, introducem mai întâi un cod de element valid în celula A11 din factura necompletată:
mutați celula activă în celula în care doriți să fie stocate informațiile obținute din baza de date de către VLOOKUP.Interesant este că acesta este pasul pe care cei mai mulți dintre ei îl înșelă.Pentru a explica mai departe: Vom crea o formulă VLOOKUP care va prelua descrierea care corespunde codului elementului din celula A11.Unde dorim ca această descriere să fie pusă când o primim?În celula B11, desigur. Deci, acolo scriem formula VLOOKUP: în celula B11.Selectați celula B11 acum.
Trebuie să găsim lista tuturor funcțiilor disponibile pe care Excel le poate oferi, astfel încât să putem alege VLOOKUP și să obțină asistență în completarea formulei. Acest lucru se găsește făcând clic mai întâi pe fila Formule și apoi pe Introduceți funcția :
Apare o casetă care ne permite să selectăm oricare dintre funcțiile disponibile în Excel.
Pentru a găsi cea pe care o căutăm, am putea introduce un termen de căutare ca "căutare"( deoarece funcția în care ne interesează este funcția ).Sistemul ne va returna o listă a tuturor funcțiilor legate de căutare în Excel. VLOOKUP este al doilea din listă.Selectați un clic OK .
Argumentul funcțiilor Argumentul apare, solicitându-ne toate argumentele ( sau parametrii ) necesare pentru a finaliza funcția VLOOKUP.Vă puteți gândi la această casetă ca la funcția care ne întreabă următoarele întrebări:
- Ce identificator unic te uiți în baza de date?
- Unde este baza de date?
- Ce informație din baza de date, asociată cu identificatorul unic, doriți să fi fost preluată pentru dvs.?
Primele trei argumente sunt afișate cu bold , indicând că acestea sunt argumente obligatorii ( funcția VLOOKUP este incompletă fără ele și nu va întoarce o valoare validă).Al patrulea argument nu este îndrăzneț, adică este opțional:
Vom completa argumentele în ordine, de sus în jos.
Primul argument pe care trebuie să-l finalizăm este argumentul Lookup_value .Funcția are nevoie să ne spunem unde să găsim identificatorul unic( codul al elementului în acest caz) că ar trebui să returneze descrierea lui. Trebuie să selectăm codul elementului introdus mai devreme( în A11).
Faceți clic pe pictograma selector din partea dreaptă a primului argument:
Apoi faceți clic o dată pe celula care conține codul elementului( A11) și apăsați Introduceți :
Valoarea lui "A11" este introdusă în primul argument.
Acum trebuie să introduceți o valoare pentru argumentul Table_array .Cu alte cuvinte, trebuie să le spunem VLOOKUP unde să găsească baza de date / lista. Faceți clic pe pictograma selector de lângă al doilea argument:
Acum găsiți baza de date / listă și selectați întreaga listă - care nu include linia de antet .În exemplul nostru, baza de date este localizată pe o foaie de lucru separată, deci facem primul clic pe fila de lucru:
Apoi selectăm întreaga bază de date, fără a include linia de antet:
. .. și apăsați Introduceți .Gama de celule care reprezintă baza de date( în acest caz "Baza de date de produs"! A2: D7 ") este introdusă automat pentru noi în al doilea argument.
Acum trebuie să introduceți al treilea argument, Col_index_num .Folosim acest argument pentru a specifica pentru VLOOKUP care informație din baza de date, asociată cu codul nostru de element în A11, dorim să ne întoarcem la noi.În acest exemplu, dorim să ne trimitem descrierea a elementului .Dacă vă uitați în foaia de lucru a bazei de date, veți observa că coloana "Descriere" este cea de-a doua coloană din baza de date. Acest lucru înseamnă că trebuie să introduceți o valoare de "2" în caseta AS_CS_And_And :
Este important să rețineți că nu intrăm aici "2" deoarece coloana "Descriere" se află în coloana B peacea foaie de lucru. Dacă baza de date a început să apară în coloana K a foii de lucru, vom introduce încă un "2" în acest câmp, deoarece coloana "Descriere" este a doua coloană din setul de celule pe care am selectat-o când specificăm "Table_array".
În cele din urmă, trebuie să decidem dacă să introduceți o valoare în argumentul final VLOOKUP, Range_lookup .Acest argument necesită fie o valoare adevărată sau false , fie ar trebui să fie lăsată necompletată.Atunci când se utilizează VLOOKUP cu baze de date( așa cum este adevărat 90% din timp), modul de a decide ce se poate pune în acest argument poate fi considerat după cum urmează:
Dacă prima coloană a bazei de date( coloana care conține identificatorii unici)este sortat în ordine alfabetică / numerică în ordine ascendentă, atunci este posibil să introduceți o valoare a adevărată în acest argument sau să lăsați-o goală.
Dacă prima coloană a bazei de date este nu este sortată în sau este ordonată în ordine descrescătoare, atunci trebuie să introducă false în acest argument
Ca prima coloană a bazei noastre de date nu este sortată ,vom introduce false în acest argument:
Asta este! Am introdus toate informațiile necesare pentru VLOOKUP pentru a returna valoarea de care avem nevoie. Faceți clic pe butonul OK și observați că descrierea corespunzătoare codului elementului "R99245" a fost corect introdusă în celula B11:
Formula creată pentru noi arată astfel:
Dacă introduceți un cod diferit de element în celulăA11, vom începe să vedem puterea funcției VLOOKUP: Celula de descriere se modifică pentru a se potrivi cu noul cod al articolului:
Putem efectua un set similar de pași pentru a obține prețul al elementului returnat în celula E11.Rețineți că noua formulă trebuie creată în celula E11.Rezultatul va arăta astfel:
. .. și formula va arăta astfel:
Rețineți că singura diferență dintre cele două formule este al treilea argument( Col_index_num ) sa schimbat de la "2" la "3"( deoarecedorim datele recuperate din coloana 3 din baza de date).
Dacă am decis să cumpărăm 2 dintre aceste articole, am introduce un "2" în celula D11.Apoi vom introduce o formulă simplă în celula F11 pentru a obține linia totală:
= D11 * E11
. .. care arată așa. ..
Finalizarea șablonului facturii
Am învățat multe despre VLOOKUP până acum. De fapt, am învățat tot ce vom învăța în acest articol. Este important să rețineți că VLOOKUP poate fi utilizat și în alte circumstanțe, pe lângă bazele de date. Acest lucru este mai puțin obișnuit și poate fi acoperit în viitor în articolele How-To Geek.
Șablonul facturii noastre nu este încă finalizat. Pentru a le completa, am face următoarele:
- Am elimina codul elementului de probă din celula A11 și "2" din celula D11.Acest lucru va determina formulele noi VLOOKUP create pentru a afișa mesaje de eroare:
Putem remedia acest lucru prin utilizarea judicioasă a IF() și ISBLANK() funcții .Vom schimba formula noastra din aceasta. .. = VLOOKUP( A11, 'Baza de date a produsului! A2: D7,2, FALSE) . .. la aceasta. .. = IF( IS11L, A11,'A2: D7,2, FALSE))
- Am fi copiat formulele din celulele B11, E11 și F11 până la restul rândurilor de articole ale facturii. Rețineți că, dacă procedăm astfel, formulele rezultate nu se vor mai referi corect la tabela de baze de date. Am putea rezolva acest lucru prin schimbarea referințelor de celule pentru baza de date la referințe absolute de celule. Alternativ - și chiar mai bine - am putea crea un pentru întreaga bază de date a produsului( cum ar fi "Produse") și să folosim acest nume de domeniu în locul referințelor de celule. Formula = IF( ISBLANK( A11), "", VLOOKUP( A11, Baza de date a produsului! A2: D7,2, FALSE)) . .. la acest. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, Produse, 2, FALSE)) . .. și apoi copiați formulele până la restul rândurilor de articole facturate.
- Probabil că vom "bloca" celulele care conțin formulele noastre( sau mai curând deblocați celelalte celule ) și apoi protejați foaia de lucru, pentru a ne asigura că formulele noastre construite cu atenție nu sunt suprascrise accidental când cineva vine să umpleîn factură.
- Am salva fișierul ca un șablon , astfel încât acesta să poată fi reutilizat de către oricine din compania noastră
Dacă am simțit cu adevărat inteligent , am crea o bază de date a tuturor clienților noștri într-o altă foaie de lucru, ID-ul client introdus în celula F5 pentru a completa automat numele și adresa clientului în celulele B6, B7 și B8.
Dacă doriți să exersați cu VLOOKUP sau pur și simplu să vedeți șablonul de facturare rezultat, acesta poate fi descărcat de aici.
