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!