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:

Fig. 1.68 Modul de utilizare a functiilor baza de date

=CHOOSE(index-numeric;lista de valori) returneaza in urma unei alegeri dintr-o lista de valori, o actiune sau o valoare, ce urmeaza a fi activata sau executata,

corespunzator unui index numeric. Indexul numeric determina care valoare (de tip text, numerica sau referinta celulara) din lista de argumente va fi selectata. Indexul este un numar cuprins intre 0 si 29.

=COLUMN(referinta celulara sau camp) returneaza numarul colanei corespunzatoare referintei celulare sau numarul primei coloane pentru campul specificat;

=COLUMNS(camp) returneaza numarul de coloane aferente campului specificat ca argument;

=ROW(referinta celulara sau camp) returneaza numarul liniei corespunzatoare referintei celulare sau numarul primei linii a campului specificat ca argument;

=ROWS(camp) returneaza numarul de linii pe care il ocupa campul specificat ca argument;

=AREAS(referinta celulara) indica numarul de zone contigue dintr-un camp. Daca respectivul camp contine mai multe zone contigue, atunci argumentul se mai inchide  intr-o paranteza suplimentara;

in figura 1.69 sunt prezentate mai multe exemple de utilizare a functiilor enumerate mai sus.

Fig. 1.69 Aplicatie pentru functiile de cautare si consultare

=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 cu 1) de unde va fi recuperata informatia gasita in tabelul de consultare, corespunzator valorii cheii de cautare.

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 anumita linie a unui tablou de consultare orizontala.

Argumentul cheie (sub forma unei referinte celulare sau nume de camp) va fi cautat in prima linie a campului de consultare, iar daca valoarea va fi gasita pe un numarul 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 vericala sau pe orizontala) de cheia de consultare.

Pentru exemplificarea celor doua functii de consultare propunem urmatoarea aplicatie:

O societate comerciala de distributie intocmeste, cu ajutorul procesorului de tabele EXCEL, facturi pentru livrarile efectuate.

Optional, respectiva societate 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 (figura 1.70) si a fost in prealabil sortat de la stanga la dreapta dupa prima linie, adica dupa destinatie.

Fig. 1.70 Date pentru aplicatia de cautare.

Firma isi are inregistrati clientii intr-o baza de date (definita pe coordonatele E1:I7) (figura 1.71) care regrupeaza 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 (figura 1.72) 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.

Fig. 1.71 Date pentru aplicatia de cautare

Factura procesata cu Excel are urmatoarea forma (figura

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 intr-un prim timp 'Valoarea', "Majorarile", 'TVA-ul' si 'Valoarea facturata'. La

Fig. 1.72 Nomenclatorul de preturi

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

Fig. 1.73 Factura obtinuta

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 22% 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 verticala 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) (figura 1.74) 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 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.

Fig. 1.74 Functia de consultare verticala

Cheia de consultare fiind in acest caz de tip text nu trebuie sa aiba valori vide si nici numerice. Pentru aceasta, procedura de consultarea 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 1.75.

Fig. 1.75 Functia de consultare verticala

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 tabel 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 ce 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

Fig. 1.76 Consultare verticala.

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 (figura 1.76).

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'.

Fig. 1.77 Aplicatie de consultare verticala

In figura 1.77 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, corespunzaror valorilor luate de codul produsului.

Consultarea orizontala a tabelului declarat pe coordonatele $F$20:$J$28 are loc dupa valorile luate de cheia de consultare - $G$10 "Destinatia". Daca cheia este gasita in tablou, se va recupera numarul de linie care va contine valoarea cheltuielilor de transport corespunzatoare destinatiei specificate.

in exemplul prezentat in figura 1.78, s-a operat un mic artificiu, anume 'Cantitatea livrata' coincide logic cu numarul de linie de recuperat orizontal din tablou (astfel, nu s-a precizat numarul liniei recuperate, ci celula care contine livrata, aflata la adresa C13). Datorita faptului ca procedura de consultare orizontala este operationala incepand cu linia 1 (care contine invariabil titlurile rubricilor aferente destinatiei), celula "Cantitatea livrata" va indica numarul liniei de recuperat si va avea valoarea incrementata cu o unitate pentru a exista o concordanta intre valorile luate de aceasta si numarul liniei de recuperat. Daca nu s-ar fi operat acest artificiu, numarul liniei de recuperat ar fi decalat cu o unitate (adica, daca celula C13 - "Cantitatea livrata" - ar fi avut valoarea 3, s-ar fi recuperat linia numarul 3 din tabel -prima linie contine titlul rubricilor-, adica valoarea cheltuielilor de transport aferente pentru 2 tone transportate)

Prin functia HLOOKUP s-a consultat deci respectivul tablou, cautandu-se valoarea luata de "Destinatie" in celula $G$10, recuperandu-se numarul de linie ce corespunde logic cu "Cantitatea livrata".

Tabloul de consultare trebuie in mod obligatoriu sortat alfabetic dupa prima linie a sa.

Procedura de consultare orizontala poate fi completata si astfel imbunatatita (celula D14) prin urmatoarele teste:

- un test facut asupra celulelor ce contin: "Destinatia" (sa nu fie valoare vida sau numerica) si "Cantitatea livrata" (sa nu fie valoare de tip text, vida sau zero):

=IF(OR(ISNONTEXT($G$10),ISBLANK($G$10),ISTEXT(C14),ISBLANK(C14),C14=0;0;IF(..) . Daca cel putin unul din argumente este adevarat, functia va returna valoarea zero, altfel se vor testa si alte conditii de indeplinit;

Fig. 1.78 Aplicatie de consultare orizontala

- un test de existenta a "Destinatiei" (celula $G$10) in prima linie a tabloului de consultare orizontala: =IF(OR(.;$G$10<>HLOOKUP($G$10;$F$20:$J$28;1));0;...). Daca "Destinatia" este inexistenta in tablou, functia va returneaza valoarea zero, altfel se procedeaza la consultarea propriu-zisa;

- un test de existenta a "Cantitatii livrate" in prima coloana a unui tablou de consultare verticala:

=IF(OR(C14<>VLOOKUP(C14;$E$20:$E$28;1);...). Daca nu exista valoarea unei cantitati livrate in tabloul de consultare verticala definit pe coordonatele $E$20:$E$28, functia returneaza zero, altfel se procedeaza la consultarea propriu-zisa:

(..HLOOKUP($G$10;$F$20:$J$28;C14+1));

- procedura ar putea fi completata si cu un test de efectuare a transportului: astfel, daca transportul este facut de furnizor (celula $D$8 are valoarea "da"), atunci se procedeaza la testele de mai sus si se executa in final consultarea orizontala, altfel cheltuielile de transport vor fi zero.

Figura 1.79 indica corespondentele creeate intre diferitele campuri, in procesul de consultare verticala si orizontala.

Fig. 1.79 Consultarea verticala si orizontala. Corespondente