Lectia 10 VLOOKUP si HLOOKUP

Scopul lectiei:

Rolul functiilor VLOOKUP si HLOOKUP

Tabele care sa se bazeze pe o lista de tip Nomenclator sunt foarte des īntālnite. Nomenclatorul are sute, chiar mii de repere. Functia IF nu poate fi folosita. Atunci se folosesc doua functii VLOOKUP si HLOOKUP, care fac acelasi lucru. Consulta un nomenclator.

Functiile VLOOKUP

Fig  10.1

Sa luam un exemplu simplu, adica dorim tabelul din Fig 10.1, īn care atunci cānd tastam codul īn coloana E, sa apara simultan denumirea produsului īn coloana F si pretul produsului īn coloana G. Cum procedam. Deschidem un Registru si redenumim foile ca īn Fig 10.2 .

Nr de ordine a coloanei din Nom de unde se ia denumirea

Nomenclator Nom adica domeniul sau

Celula de referinta unde este CODP īn Foaia Fact

Fig 10.4 (Functia VLOOKUP() pentru Denumire)

Urmariti Fig 10 si vedeti ca celula de referinta este E2 adica CODP (codul produsului). Nomenclatrul se gaseste īn foaia Nom si este cuprins īn dreptunghiul format de celulele A1 si C7. S-a scris $A$1:$C$7 pentru ca este o zona fixa, (un domeniu). Denumirea produsului se gaseste īn Nom īn coloana 2 pe cānd pretul produsului se gaseste īn coloana 3. Formulele sunt cele din Fig 10.5. Se scrie prima formula cea din F2 si apoi este copiata prin glisare, pe coloana īn jos. Formula se poate scrie direct sau se poate apela functia VLOOKUP vezi Fig 10.6 si Fig 10.7

Fig 10.5

Īn concluzie avem Nomenclatorul pe o Foaie de lucru separata (nu e obligatoriu, e recomandabil) codurile din Nomenclator sunt obligatoriu īn ordine strict crescatoare (adica fara dubluri) si cautarile se fac pe verticala. Īn Fig 10.8 si Fig 10.9 am redenumit domeniul si acest lucru se vede īn argumentele functiei VLOOKUP.

Fig 10.6 (Inserarea comenzii functie)

Cautati iconita _S sau īn meniul principal comanda Īnserare, apoi Functie si cautam VLOOKUP.

Fig 10.7 (Apelarea functieiVLOOKUP)

Pentru Lookup_value nu este nevoie sa se scrie E2 ci sa se faca clic pe celula E2.

Pentru Table_array se face clic pe acest marcator declansator si cu ajutorul mausului se face clic pe numele foii Nom si se baleiaza apoi lista nomenclator A1:C7, īnca un clic pe marcatorul declansator, A1:C7 apare īn ferestruica, apasam tasta F4 sa punem semnul dolar $ īnaintea liniei si a coloanei si apare $A$1:$C$7.

Īn ferestruica Col_index_num care īnseamna numarul de ordine al coloanei din Nomenclator, scriem 2, pentru ca denumirea este a doua coloana din Nomenclator.

Vedeti īn Fig 10.7 Range lookup. Este un argument logic. Rolul lui se gaseste īn Fig 10.9. el specifica cum se face cautarea: exact sau aproximativ.

Formulele generate pentru VLOOKUP () difera dupa cum īn Range lookup exista TRUE sau FALSE ca īn Fig 10.8

Fig 10.8

Argumentul FALSE e obligatoriu, daca este omis se subīntelege TRUE, cu consecintele de rigoare din Fig 10.9.

Fig 10.9

Observati īn Fig 10.7 ca facānd clic pe E2 īn ferestruica Lookup_value a aparut nu E2 ci CODP, numele coloanei, pentru ca a avut loc, īn prealabil, o redenumire a domeniului. Vezi Lectia 6.

Respectati urmatoarele reguli:

• Suntem atenti sa nu dam coduri īn afara celor din Nomenclator pentru ca rezultatele sunt eronate, se va afisa ultimul cod din Nom.
• Nu este obligatoriu ca sa avem denumirea de Nomenclator, poate exista si fara denumire.
• Daca schimbam denumirea sau pretul unui produs īn Nomenclator toate rāndurile din foaia Fact, care au acelasi cod, se vor modifica.
• Daca apar coduri noi, īntāi le introducem īn Nom sortam din nou nomenclatorul si intervenim īn formule din lista ca sa schimbam zona īn care se gaseste noul Nomenclator.

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!

Functia HLOOKUP

Exista si functia HLOOKUP care face cautari pe orizontala īntr-un tabel de tip Nomenclator. (Nomenclatorul este aranjat pe orizontala si de aceea se fac cautari pe orizontala).

Fig 10.15 (Ce īnseamna HLOOKUP)

Functia HLOOKUP are aceiasi logica a sintaxei ca functia VLOOKUP.

Fig 10.16

Observatie:

Exista riscul de a gresi atunci cānd 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 īn Facturi, decāt codurile din Nomenclator. Aceasta este procedura (comanda Validare).

Ce īnseamna VALIDARE

Īn sens general īn informatica, prin validare, se īntelege verificarea datelor de intrare īntr-un program de calculator. Īn cazul nostru e necesar sa aducem Nomenclatorul īn aceiasi Foaie de lucru cu Tabelul facturilor, pentru ca altfel nu functioneaza comanda Validare. A fost adus si lucrurile se prezinta ca īn Fig 10.13. Mentin afirmatia ca Nomenclatorul era mai bine sa fie pe o Foaie de lucru separata, nu pentru ca este o regula specifica EXCEL-ului, ci pentru mai multa ordine.

Fig 10.17

Comanda Validare se apeleaza ca īn Fig 10.18

Fig 10.18

La Criterii de validare din Meniul derulant alegem lista si se obtine Fig 10.18! Īn dreapta avem un marcaj declansator care īmi permite sa aleg cu mausul domeniul unde se afla lista.

Facem clic pe marcajul declansator si baleiem domeniul unde se afla lista.E vorba de lista de coduri din Nomenclator.

Facem din nou clic pe marcajul declansator si apare domeniul =$k$4:$K$9 Fig 10.19.

Aici este marcajul declansator. Faceti clic pe el si baleiati coloana K. Īn ferestruica apare Lista

Fig 10.19

Īn final apare Fig 10.20, unde la coloana CODP avem un meniu ascuns (derulant) si īn el sunt codurile din Nomenclator. Īn plus se pot stabili, atunci cānd se instaleaza Validarea, mesaje de intrare, avertizare de eroare, lucru care s-a si facut, dar nu sunt īntotdeauna necesare. Fig 10.20 si Fig 10.21

Faceti clic aici! Apar codurile din Lista singurele ce pot fi utilizate.

Fig 10.20

Fig 10.20 (Semnalarea unei erori de introducere a datelor. Codul 777 nu este corect!)

. La īnceput asemenea mesaje plac. Dupa un timp devin enervante!