VLOOKUP (Funcia VLOOKUP)Acest articol descrie sintaxa de formul i utilizareafuncieiVLOOKUPn Microsoft Excel.DescriereAvei posibilitatea s utilizai funciaVLOOKUPpentru a cuta n prima coloan dintr-ozonde celule, apoi a returna o valoare din orice celul din acelai rnd din zon. De exemplu, s presupunem c avei o list de angajai n zona A2:C10. Numerele ID ale angajailor sunt stocate n prima coloan din zon, dup cum se afieaz n ilustraia urmtoare.

n cazul n care cunoatei numrul ID al angajatului, avei posibilitatea s utilizai funciaVLOOKUPpentru a returna departamentul sau numele angajatului. Pentru a obine numele angajatului numrul 38, avei posibilitatea s utilizai formula=VLOOKUP(38; A2:C10; 3; FALSE). Aceast formul caut valoarea 38 n prima coloan din zona A2:C10, apoi returneaz valoarea coninut n a treia coloan din zon i n acelai rnd ca valoarea de cutare ("Alex Lupu").Litera V dinVLOOKUPnseamn vertical. Utilizai funciaVLOOKUPn locul funcieiHLOOKUPatunci cnd valorile de comparat sunt localizate ntr-o coloan din partea stng a datelor pe care dorii s le gsii.SintaxVLOOKUP(cutare_valoare, matrice_tabel, num_index_col, [zon_cutare])Sintaxa funciei VLOOKUP are urmtoareleargumente: cutare_valoareObligatoriu. Valoarea de cutat din prima coloan a tabelului sau zonei. Argumentulcutare_valoarepoate fi o valoare sau o referin. Dac valoarea pe care o oferii pentru argumentulcutare_valoareeste mai mic dect cea mai mic valoare din prima coloan a argumentuluimatrice_tabel,VLOOKUPreturneaz valoarea de eroare #N/A. matrice_tabelObligatoriu. Reprezint zona de celule care conine datele. Avei posibilitatea s utilizai o referin la o zon (cum ar fiA2:D8sau un nume de zon. Valorile din prima coloan amatrice_tabelsunt valorile cutate dupcutare_valoare. Aceste valori pot fi text, numere sau valori logice. Textul cu litere mari este echivalent cu cel cu litere mici. num_index_colObligatoriu. Este numrul de coloan din argumentulmatrice_tabelpentru care trebuie returnat valoarea potrivit. Un argumentnum_index_colegal cu 1 returneaz valoarea din prima coloan dinmatrice_tabel; unnum_index_colegal cu 2 returneaz valoarea din a doua coloan dinmatrice_tabeletc.Dac argumentulnum_index_coleste: Mai mic dect 1,VLOOKUPreturneaz valoarea de eroare #VALUE!. Mai mare dect numrul de coloane dinmatrice_tabel,VLOOKUPreturneaz valoarea de eroare #N/A. zon_cutareOpional. O valoare logic, care specific dacVLOOKUPcaut o potrivire exact sau o potrivire aproximativ: Daczon_cutareeste TRUE sau este omis, se returneaz o potrivire exact sau aproximativ. Dac nu se gsete o potrivire exact, se va returna urmtoarea valoare maxim care este mai mic dectcutare_valoare.IMPORTANTDaczon_cutareeste TRUE sau este omis, valorile din prima coloan dinmatrice_tabeltrebuie plasate n ordine ascendent; altfel, este posibil caVLOOKUPs nu returneze valoarea corect.Pentru mai multe informaii, consultaiSortarea datelor dintr-o zon sau un tabel.Daczon_cutareeste FALSE, valorile din prima coloan dinmatrice_tabelnu au nevoie de sortare. Dac argumentulzon_cutareeste FALSE,VLOOKUPva gsi exact o potrivire. Dac exist dou sau mai multe valori n prima coloan dinmatrice_tabelcare se potrivesc cucutare_valoare, se va utiliza prima valoare. Dac nu se gsete o potrivire exact, se returneaz valoarea #N/A.Observaii Cnd se caut valori text n prima coloan amatrice_tabel, asigurai-v c datele din prima coloan amatrice_tabelnu conin spaii la nceput, spaii n plus, utilizri inconsistente ale apostrofului sau ghilimelelor drepte ( ' sau " ) sau curbate ( sau ) sau caractere neimprimabile. n acest caz,VLOOKUPpoate returna o valoare incorect sau neateptat.Pentru mai multe informaii, consultai funciileCLEANiTRIM. Atunci cnd se caut valori numerice sau date, asigurai-v c datele din prima coloan amatrice_tabelnu sunt memorate ca valori text. n acest cazVLOOKUPpoate returna o valoare incorect sau neateptat. Dac zon_cutare este icutare_valoareeste text, avei posibilitatea s utilizai metacaracterele semnul de ntrebare (?) i asteriscul (*) ncutare_valoare. Un semn de ntrebare se potrivete cu orice caracter individual; un asterisc se potrivete cu orice secven de caractere. Dac dorii s gsii un semn de ntrebare sau un asterisc efectiv, tastai simbolul tilda (~) naintea caracterului.ExempleExemplul 1Utilizai registrul de lucru ncorporat afiat aici pentru a lucra cu exemple ale acestei funcii. Putei s inspectai i s modificai formulele existente, s introducei formule proprii i s citii informaii suplimentare despre modul n care funcioneaz funcia.n acest exemplu se caut n coloana Densitate a unui tabel cu proprieti atmosferice pentru a gsi valorile corespunztoare din coloanele Vscozitate i Temperatur. (Valorile sunt pentru aer la 0 grade Celsius la nivelul mrii sau la 1 atmosfer.)EWA1Pentru a lucra n profunzime cu acest registru de lucru, putei s l descrcai pe computer i s l deschidei n Excel. Pentru mai multe informaii, consultai articolulDescrcarea unui registru de lucru ncorporat de pe SkyDrive i deschiderea acestuia pe computer..Exemplul 2n acest exemplu se caut n coloana Cod articol a unui tabel cu produse pentru copii i se potrivesc valorile din coloanele Cost i Adaos pentru a calcula preurile i a verifica starea lor.EWA2Exemplul 3n acest exemplu se caut n coloana Cod a unui tabel cu angajai i se potrivesc valorile din alte coloane pentru a calcula vrste, testnd condiiile de eroare.EWA3

Utilizarea functiilor LOOKUP pentru interogarea tabelelor de date - VLOOKUPAstazi am ales sa va scriu despre functiile LOOKUP care se folosesc pentru a aduce intr-o celule informatii dintr-un tabel de date. Aceste functii sunt foarte utilizate in randul celor care folosesc in mod intens excel-ul.

Eu folosesc cel mai des functiile LOOKUP, in cadrul fisierelor in care se introduc date pentru a completa automat tabelul cu informatiile pe care le cunoastem deja. Un exemplu ar fi intr-o format de factura, in care putem folosi formula vlookup pentru a aduce, in functie de numele clientului, celelalte date ale sale ca adresa, contul bancar, CUI-ul etc.

Functia VLOOKUP -este folosita pentru acutadatele specificate inprima coloana aunui tabeldedate. Sintaxa functiei VLOOKUP este urmatoarea:=vlookup(lookup_value,table_array,col_index_num,range_lookup)1. Lookup_value- reprezinta ceea ce dorim sa cautam in tabelul de date.Lookup_value poate fi o valoare sau o referinta. Daca in tabelul de date nu exista valoarea cautata de vlookup atunci functia va returna eroarea #N/A.2. Table_array -reprezinta tabelul de date in care cautam valoarea dorita.Tabelul de date poate sa aibe doua sau mai multe coloane. Principala conditie in vlookup este ca prima coloana a tabelului sa contina valorile unde cautam lookup_value. Aceste valori pot fi text, numere sau valori logice. Textul cu litere mari este echivalent cu cel cu litere mici.3. Col_index_num- reprezintanumarul coloanei din table_array din care trebuie returnata valoarea potrivita. Dac col_index_num este mai mic dect 1, functia returneaza eroarea #VALUE!, iar daca este mai mare decat numarul de coloane din tabel, returneaza eroarea #N/A.4. Range_lookup -reprezintao valoare logica, care specifica daca functia cauta o potrivire exacta sau o potrivire aproximativa. In general se foloseste cautarea exacta care are ca argument FALSE sau 0. In cazul cautarii aproximative (argumentul TRUE, 1 sau omis), tabelul trebuie sortat in ordine crescatoare dupa prima coloana. De asemenea, daca functia nu gaseste valoarea din lookup_value, ea returnaza valoarea cea mai mare urmatoare care este mai mica decat lookup_value.

Pentru a intelege mai bine functia VLOOKUP am creat un tabel cu 3 coloaneCod angajat, DepartamentsiAngajat.

In exemplul nostru interogam acest tabel de date dupa codul angajatului. Adica punem intrebarea: Angajatul cu codul 110 ce nume are.Sintaxa formulei este urmatoarea=VLOOKUP(F2,$A:$C,3,0)

1. Selectam celula unde vrem sa aducem numele angajatului.2. Scrien =Vlookup(3. Alegem celula unde vom scrie codul angajatului pentru care vrem sa aflam numele, in cazul nostru F2.4. Alegem tabelul de date cu angajati, in exemplul nostru acesta este format din coloanele A:C. Va recomand ca dupa selectarea tabelului sa faceti adresa acestuia referinta, mai ales daca formula este copiata si in alte celule.5. Scrien numarul coloanei din care sa ne fie returnat rezultatul. In cazul nostru, acesta este 3.6. Alegem True sau False/ 0 sau 1 in functie de cum dorim sa fie executata cautarea. In cazul nostru, dorim o cautare exacta deci alegem 0.7. Apasam ENTER.

Lectia10VLOOKUP si HLOOKUPScopul lectiei:Folosirea unor functii de cautare VLOOKUPsi HLOOKUP care permit renuntarea laIFRestrictii n folosirea acestor functii1.1Rolul functiilor VLOOKUP si HLOOKUPTabele care sa se bazeze pe o lista de tipNomenclatorsunt foarte des ntlnite.Nomenclatorulare sute, chiar mii de repere. FunctiaIFnu poate fi folosita. Atunci se folosesc doua functiiVLOOKUPsiHLOOKUP,care fac acelasi lucru.Consulta un nomenclator.1.2Functiile VLOOKUP

Fig10.1Sa luam un exemplu simplu, adica dorim tabelul din Fig10.1, n care atunci cnd tastam codul n coloanaE, sa apara simultandenumirea produsuluin coloanaFsipretul produsuluin coloanaG.Cum procedam.Deschidem unRegistrusi redenumim foile ca n Fig10.2[1].PrimaFoaie de lucruva fiFact(FACTURI). Aici facem capul de tabel ca n Fig10.1A douaFoaie de lucruNom(NOMENCLATOR) n foaiaNomfacem urmatorul tabel (lista): acesta va fiNomen-clatorul.Fig10.3.n acestNOMENCLATORcodurile produselor sa fie nordine strict crescatoare.Poate sa fie "goluri" dar sa nu fie dubluri! Codurile pot fi numere, dar si litere amestecate cu cifre. Denumirile rubricilo 14114t198o r nu trebuie sa fie acelasi nNOMENCLATORcu cele existente n tabelul (lista)FACTURI,pentru ca identificarea se face dupa numarul de ordine al coloanei, nu dupa denumirea ei.Functia, care rezolva acest lucru este:VLOOKUP () B.Fiindca s-a facut redenumirea n prealabil se nteleg mai usor argumentele. Functia este scrisa n FoaiaFactiarNomenclatorulse gaseste foaiaNom.

Fig10.2

Fig10.3

=VLOOKUP(E2;Nom!$A$1:$C$7;2)

Nr de ordine a coloanei dinNomde unde se iadenumirea

NomenclatorNomadica domeniul sau

Celula de referinta unde esteCODPn FoaiaFact

Fig10.4 (FunctiaVLOOKUP()pentruDenumire)UrmaritiFig10.1si vedeti ca celula de referinta esteE2adicaCODP(codul produsului). Nomenclatrul se gaseste n foaiaNomsi este cuprins n dreptunghiul format de celuleleA1siC7. S-a scris$A$1:$C$7pentru caeste o zona fixa, (un domeniu).Denumirea produsului se gaseste nNomn coloana 2 pe cnd pretul produsului se gaseste n coloana 3. Formulelesuntcele din Fig10.5. Se scrie prima formula cea dinF2si apoi este copiata prin glisare, pe coloana n jos. Formula se poate scrie direct sau se poate apela functiaVLOOKUPveziFig10.6 si Fig10.7

Fig10.5n concluzie avemNomenclatorulpe oFoaie de lucruseparata (nu e obligatoriu, e recomandabil) codurile dinNomenclatorsuntobligatoriun ordine strict crescatoare (adica fara dubluri) si cautarile se fac pe verticala. n Fig10.8 si Fig10.9 am redenumit domeniul si acest lucru se vede n argumentele functieiVLOOKUP.

Fig10.6 (Inserarea comenziifunctie)Cautati iconitasaun meniul principal comandanserare,apoiFunctiesi cautamVLOOKUP.

Fig10.7 (Apelarea functieiVLOOKUP)PentruLookup_valuenu este nevoie sa se scrieE2ci sa se faca clic pe celulaE2.PentruTable_arrayse face clic peacestmarcator declansatorsi cu ajutorul mausului se face clic pe numele foiiNomsi se baleiaza apoi lista nomenclatorA1:C7,nca un clic pemarcatorul declansator, A1:C7apare n ferestruica, apasam tastaF4sa punem semnul dolar $naintea liniei si a coloaneisi apare$A$1:$C$7.n ferestruicaCol_index_numcare nseamna numarul de ordine al coloanei dinNomenclator,scriem 2, pentru ca denumirea este a doua coloana dinNomenclator.Vedeti n Fig10.7Range lookup.Este un argument logic. Rolul lui se gaseste n Fig10.9. el specifica cum se face cautarea:exact sau aproximativ.Formulele generate pentruVLOOKUP ()difera dupa cum nRange lookupexistaTRUEsauFALSEca n Fig10.8

Fig10.8Argumentul FALSE e obligatoriu, daca este omis se subntelege TRUE, cu consecintele de rigoare din Fig10.9.Daca nRange lookupeste scris:Aceasta nseamna:

Truesau nimicn cazul n care codul nu exista nNomse va afisa denumirea si pretul celui mai apropiat cod ca valoare. Codul 222 nu exista nNom.Cel mai apropiat cod este 112. s-a afisat denumirea de la 112 "Zahar", ceea ce este o aberatie din punct de vedere a gestiunii corecte.

FalseDaca exista codul nNom, atunci i se afiseaza atributele sale, daca nu exista se afiseaza un mesaj de eroare.Obligatoriu n gestiuni se foloseste aceasta a doua forma!Nu sunt permise ambiguitati. Un produs are un cod!

Fig10.9Observati n Fig 10.7 ca facnd clic peE2n ferestruicaLookup_valuea aparut nuE2ciCODP,numele coloanei, pentru ca a avut loc, n prealabil,o redenumire a domeniului. VeziLectia 6.Respectati urmatoarele reguli: Suntem atenti sa nu dam coduri n afara celor dinNomenclatorpentru ca rezultatele sunt eronate, se va afisa ultimul cod dinNom. Nu este obligatoriu ca sa avem denumirea deNomenclator, poate exista si fara denumire. Daca schimbamdenumireasaupretul unui produs nNomenclatortoate rndurile din foaiaFact,care au acelasicod,se vor modifica. Daca aparcoduri noi, nti le introducem nNomsortam din nou nomenclatorul si intervenim n formule din lista ca sa schimbam zona n care se gaseste noulNomenclator.

Fig10.10Fig10.11

Fig10.12Fig10.13

Fig10.10Lista nainte de introducerea unui nou cod

Fig10.11Nomenclatorul nainte de introducerea unui nou cod

Fig10.12s-a introdus un nou cod 222 si s-a resortatNom

Fig10.13s-a modificat formula n loc de$C$9 s-a scris $C$10

Lista cuprinde si noul produs222 Oua.Concluzia:cnd construimNomenclatorulsa punem n elnu numai codurile care se gasesc la un moment datn depozit, ci pe toate care pot sa apara n viitor. Ne ghidam dupa ofertele furnizorilor, dupa propria noastra experienta etc.Cnd apare o noua marfa o introducem nNomenclator,dar poate sa fie acolo fiindca odata si odata a mai fost.

Fig10.14

Nu stergeti coduri de produse care vi se par vechi. Mare greseala. Ex: n 2001 a fost un produs "ABS"cu codul 123. Trei ani nu a mai aparut si n locul lui s-a trecut cu acelasi cod produsul "XCV". Cineva a vrut sa consulte arhiva din 2001 peste tot vechiul produs "ABS" a fost nlocuit cu "XCV". S-a putut reface toata ncurcatura, dar cu ce cheltuieli!1.3Functia HLOOKUPExista si functiaHLOOKUPcare face cautari pe orizontala ntr-un tabel de tipNomenclator.(Nomenclatoruleste aranjat pe orizontala si de aceea se fac cautari pe orizontala).

Fig10.15 (Ce nseamnaHLOOKUP)FunctiaHLOOKUPare aceiasi logica a sintaxei ca functiaVLOOKUP.

Fig10.16Observatie:Exista riscul de a gresi atunci cnd dam un cod gresit, dar codul exista n Nomenclator si n loc de un produs apare altul. Aici doar atentia operatorului poate preveni o asemenea greseala.O metoda buna prin care se elimina o serie de greseli este aceea ca nu permite scrierea nFacturi,dect codurile dinNomenclator.Aceasta este procedura (comandaValidare).1.4Ce nseamna VALIDAREn sens general n informatica, prinvalidare,se ntelege verificarea datelor de intrare ntr-un program de calculator. n cazul nostru e necesar sa aducemNomenclatoruln aceiasiFoaie de lucrucuTabelul facturilor,pentru ca altfel nu functioneaza comandaValidare.A fost adus si lucrurile se prezinta ca n Fig10.13. Mentin afirmatia caNomenclatorulera mai bine sa fie pe oFoaie de lucruseparata, nu pentru ca este o regula specificaEXCEL-ului, ci pentru mai multa ordine.

Fig10.17ComandaValidarese apeleaza ca n Fig10.18Nu mai repet ca se face clic peDate,apoi peValidare,etc.

Fig10.18LaCriterii de validaredin Meniul derulant alegem lista si se obtine Fig10.18! n dreapta avem un marcaj declansator care mi permite sa aleg cu mausul domeniul unde se afla lista.Facem clic pemarcajul declansatorsi baleiem domeniul unde se afla lista.E vorba de lista de coduri dinNomenclator.Facem din nou clic pemarcajul declansatorsi apare domeniul=$k$4:$K$9Fig10.19.Aici este marcajul declansator. Faceti clic pe el si baleiati coloanaK.n ferestruica apareLista

Fig10.19n final apare Fig10.20, unde la coloanaCODPavem un meniu ascuns (derulant) si n el sunt codurile dinNomenclator.n plus se pot stabili, atunci cnd se instaleazaValidarea,mesaje de intrare, avertizare de eroare, lucru care s-a si facut, dar nu sunt ntotdeauna necesare. Fig10.20 si Fig10.21Faceti clic aici! Apar codurile din Lista singurele ce pot fi utilizate.

Fig10.20

Fig10.20 (Semnalarea unei erori de introducere a datelor. Codul 777 nu este corect!).La nceput asemenea mesaje plac. Dupa un timp devin enervante!