Functiile de cautare si consultare

Functiile de cautare si consultare

Functiile de cautare si consultare (Lookup & Reference) permit cautarea, identificarea si referirea continutului unor celule:

=Vlookup(cheie; camp de consultare; coloana de recuperat) returneaza continutul unei celule ce figureaza intr-o coloana dintr-un tablou de consultare verticala.

Sintaxa functiei de consultare verticala admite trei argumente si anume:

cheie : reprezinta valoarea dupa care are loc cautarea sau consultarea, (adresa absoluta / relativa sau nume de camp);

camp (sau tabel) de consultare: este campul asupra caruia opereaza consultarea prin cautarea valorii cheii precizate anterior;

coloana de recuperat: este numarul coloanei (numerotarea incepe de la 1) de unde va fi recuperata informatia gasita in tabelul de consultare, corespunzator valorii cheii de consultare.

In mod obligatoriu tabelul de consultare va fi sortat crescator dupa coloana care contine valorile cheii de consultare ( comanda Data Sort, iar in rubrica Sort by se va preciza numarul sau numele coloanei dupa care se va face sortarea)

=Hlookup(cheie; camp de consultare ; linie de recuperat) returneaza continutul unei celule ce figureaza intr-o coloana dintr-un talbou de consultare verticala.

Argumentul cheie (sub forma unei referinte celulare sau nume cammp) va fi cautat in prima linie a campului de consultare, iar daca valoarea va fi gasita pe un numar de linie precizat de ultimul argument, valoarea respectiva va fi returnata de functia Hlookup.

In mod obligatoriu tabelul de consultare orizontala trebuie sortat dupa valorile crescatoare ale cheii de consultare aflate in prima linie (sortare de la stanga la dreapta). Daca valorile cheii nu sunt sortate, se va selecta tabelul de consultare si se va activa comanda de sortare (de la stanga spre dreapta) : Data Sort, butonul Option si din rubrica Orientation se alege optiunea Sort left to right.

Daca informatia cautata in tabelul de consultare verticala sau orizontala nu va fi gasita, se va returna cea mai apropiata valoare (pe verticala sau pe orizontala) de cheia de consultare.

Exemplu:

O societate comerciala de distributie efectueaza si transportul marfii comandate la domiciliul clientului, firma practicand tarife diferentiate in functie de cantitatea transportata (in tone) si de orasul de destinatie.

Tarifele de transport sunt grupate intr-un tablou in functie de destinatie (prima linie) si de cantitatea transportata (prima coloana). Tabloul care urmeaza a fi considerat tabel de consultare orizontala a fost definit pe coordonatele F20:J28(FOTO) si a fost in prealabil sortat de la stanga la dreapta dupa prima linie, adica dupa destinatie.

Firma isi are inregistrati clientii intr-o baza de date (definita pe coordonatele E1:I7) care grupeaza elementele de identificare ale acestora ("Client", "Adresa", "Localitate", "Cod fiscal", "Cont bancar").

In egala masura exista si o alta baza de date - definita pe coordonatele A20:C28 (FOTO) sub forma unui nomenclator de preturi pentru fiecare produs in parte. Cele doua baze de date sunt sortate dupa valorile crescatoare ale primei coloane si contin informatii pertinente ce concura la realizarea automata a facturii.

Factura procesata cu Excel are urmatoarea forma:

Utilizatorul va introduce prin tastare, pentru completarea facturii doar denumirea clientului, codul produsului facturat, cota de adaos comercial, cantitatea livrata, iar optional daca se doreste sau nu transport, precum si destinatia transportului. In rest toate operatiile sunt facute automat cu ajutorul formulelor si a functiilor Excel.

Factura se proceseaza in mod obisnuit, incepand a se calcula intr0un prim timp "Valoarea", "Majorarile", "TVA-ul" si "Valoarea facturata". La calculul "Valorii" se va lua in calcul si o cota variabila de adaos comercial (celula D10 a fost fixata cu adresa absoluta - $D$10 - pentru a nu se decala la copierea formulei ce calculeaza valoarea), precum si cheltuielile de transport.

"Majorarile" de intarziere se pot calcula pe transe, prin structuri conditionale imbricate. "TVA-ul" reprezinta 19% din "Valoare" + "Majorari", iar "Valoarea facturii" reprezinta suma dintre "Valoare", "Majorari" si "TVA".

Intr-un al doilea timp se pot calcula totalurile pe rubricile procesate anterior utilizand clasica functie SUM.

Interesante de prezentat sunt facilitatile de consultare vericala si orizontala.

La tastarea numelui de client in celula C3, se vor recupera automat dintr-un tabel de consultare verticala (definit anterior pe coordonatele E1:I7), informatiile legate de acest identificator si anume : "Adresa", "Localitatea", "Codul fiscal" si "Contul bancar".

Astfel in celula C4 s-a scris formula de consultare verticala (VLOOKUP) pentru recuperarea adresei clientului, anume: "se cauta cheia de consultare (celula $C$3 - Client) in tabelul de consultare definit pe coordonatele $E$1:$I$7 - Client) si in caz ca valoarea este gasita, se va recupera informatia din coloana 2, corespunzatoare cheii de consultare".

Coordonatele cheii si tabelului de consultare au fost blocate prin utilizarea de adrese absolute pentru ca formula ce contine consultarea verticala sa poata fi copiata fara ca respectivele coordonate sa se decaleze.

Cheia de consultare fiind in acest caz de tip text nu trebuie sa aiba valori vide si nici numerice. Pentru aceasta, procedura de consultare verticala a fost completata cu teste facute asupra celulei care contine cheia de consultare($C$3). Daca cheia are valoarea vida "ISBLANK($C$3)" sau (OR()) daca contine o valoare alta decat text "ISNONTEXT($C$3)", atunci se va afisa un spatiu (""), altfel se va face consultarea verticala.

In aceste conditii, consultarea verticala va avea urmatoarea forma:

=IF(OR(ISBLANK($C$3); ISNONTEXT($C$3));""; VLOOKUP($C$3;$E$1:$H$7;3)), fapt ilustrat si in figura

Daca se tasteaza un client care nu exista in nomenclatorul de clienti (in tabelul de consultare verticala), functia VLOOKUP nu va semnala lipsa informatiei din table ci va returna informatia legata de cea mai apropiata valoare a cheii de consultare. De exemplu, daca s-ar introduce clientul cu numele "Sarmis", se vor recupera prin VLOOKUP informatiile aditionale corespunzatoare celei mai apropiate valori ale cheii, adica informatiile legate de clientul "Star". Functia VLOOKUP nu va semnala inexistenta cheii de consultare "Sarmis". Pentru inlaturarea acestui neajuns, procedura de consultare verticala a fost completata cu un test de existenta a cheii de consultare in tabelul de consultare.

Acest test de existenta verifica daca valoarea cheii de consultare este gasita in prima coloana a tabelului de consultare. Daca valoarea respectiva exista in tabel inseamna ca s-a gasit cheia de consultare si in consecinta consultarea verticala se va efectua returnand un rezultat corect, altfel se va afisa spatiu sau zero (ultimul caz folosindu-se daca celula respectiva participa ulterior la calcule) sau un mesaj de genul "cheie inexistenta".

Formula de testare a existentei cheii de consultare in tabel este urmatoarea:

IF(VLOOKUP($C$3;$E$1:$I$7;1)<>$C$3;"";VLOOKUP($C$3; $E$1:$I$7;4)).

Rubricile : "Adresa" -C4-, "Localitatea" -C5-, "Cod fiscal" -C6-, "Cont bancar" -C7- se vor recupera prin acelasi procedeu de consultare verticala, recuperandu-se dupa caz, prin functia VLOOKUP continutul coloanelor 2, 3, 4, si 5, corespunzator valorilor cheii de consultare declarate la adresa $C$3 .

In mod asemanator se procedeaza si cu a doua consultare verticala, anume: in momentul tastarii "Codului de produs" este consultat vertical tabelul "PRETURI" declarat la adresa A20:C28, si daca in tabelul respectiv este gasita cheia de consultare "Cod produs" - se vor recupera automat : continutul coloanei 2 si 3 din tablou, adica "Denumire produs" si "Pret".

In figura este prezentata procedura completa ( cu teste facute asupra celulei ce contine cheia de consultare si cu test de existenta a valorii cheii in tabelul de consultare) de extragere a denumirii produsului, prin consultare verticala. Similar se procedeaza pentru extragerea pretului din tablou, corespunzator valorilor luate de codul produsului.