Utilizarea formulelor pentru cautarea datelor in tabele

Utilizarea formulelor pentru cautarea datelor in tabele

Tabelele de cautare ofera o modalitate eficienta de a produce numere sau text care nu pot fi calculate prin intermediul unei formule, cum ar fi de exemplu o tabela de impozite sau de comisioane. Excel are doua tehnici de cautare a informatiei in tabele.

Prima metoda utilizeaza functiile LOOKUP(), care informeaza asupra existentei in tabel a valorii cautate, iar tabelul trebuie sa fie sortat.

A doua metoda foloseste combinatia de functii INDEX() si MATCH() pentru a gasi o echivalenta exacta in tabel, metoda buna in cazul echivalentelor exacte, cum ar fi aflarea cantitatilor aflate in stoc la un anumit produs.

Utilizarea functiilor Lookup in cazul tabelelor

Excel are trei functii utile pentru cautarea valorilor in tabele:

LOOKUP(), care realizeaza o cautare in tot tabelul, si are doua forme:

LOOKUP(lookup_value;lookup_vector;result_vector), in care:

Lookup_value = valoarea care se cauta

Lookup_vector = coloana in care se cauta

Result_vector = coloana de unde se preia reultatul cautarii

LOOKUP(lookup_value;array), in care:

Lookup_value = valoarea care se cauta

Array = domeniul in care se cauta; rezultatul cautarii va fi preluat din ultima coloana a    domeniului selectat

VLOOKUP(), care realizeaza o cautare pe verticala, pe coloana din    stanga tabelului, pana la gasirea valorii de comparare adecvate.

HLOOKUP(), care realizeaza o cautare orizontala, pe randul din partea de sus a tabelului, pana la gasirea valorii de comparare adecvate.

Atentie! Lista din tabel folosita pentru comparare trebuie sa fie sortata crescator

Functiile VLOOKUP() si HLOOKUP() au urmatoarea forma:

VLOOKUP(lookup_value; table_array; col_index_num; range_lookup)

HLOOKUP(lookup_value; table_array; row_index_num; range_lookup), in care:

Lookup_value = valoarea care se cauta

Precizeaza numarul coloanei, respectiv randului din tabel de unde se va prelua rezultatul cautarii

Table_array = domeniul in care se cauta

Col_index_num = index coloana

Row_index_num = index linie

Range_lookup = mod de cautare (echivalenta exacta sau aproximativa)

Imaginea contine un exemplu de tabel VLOOKUP() care prezinta comisioanele de vanzari. Comenzile VLOOKUP si HLOOKUP sunt utile in operatiile de cautare in tabele de impozite si comisioane, pentru ca aceste valori sunt dificil de calculat exact. Vanzarea pe care se bazeaza comisionul, de exemplu, poate sa aiba o valoare aflata intre doua numere din lista. Formula care regaseste comisionul acestei vanzari este in D4. Comisionul este calculat prin inmultirea valorii 0.45 cu 12425, care este valoarea vanzarii.

Gasirea echivalentelor exacte

Al patrulea argument care poate aparea optional (mode de cautare - range_lookup, din Function Wizard) controleaza daca functiile VLOOKUP() si HLOOKUP() cauta o echivalenta exacta sau cea mai apropiata valoare posibila. In cazul in care nu este posibila o echivalenta exacta, pentru gasirea echivalentelor aproximative, se omite argumentul domeniu cautare sau se foloseste pentru el valoarea TRUE. Dimpotriva, pentru o echivalenta exacta, al patrulea argument trebuie sa aiba valoarea FALSE. Daca s-ar fi introdus acest argument in formula din celula C4, atunci ea ar fi intors o valoare de eroare #N/A pentru ca o echivalenta exacta pentru valoarea 12425 din celula D2 nu poate fi gasita in coloana Vanzari.

Utilizarea functiilor Match si Index

Daca lista sursa nu este sortata, functiile de cautare nu pot opera corect. In aceasta situatie, se poate folosi    combinatia de functii MATCH si INDEX pentru a cauta valori. In figura de mai sus, la introducerea codului de produs, Excel afiseaza denumirea acestuia. Daca numarul este inexistent, atunci foaia de calcul afiseaza #N/A in celula C8.

Exemple

Formula urmatoare se gaseste in celula C8. Ea cauta si afiseaza denumirea produsului avand codul scris in celula C6, adica 500:

=INDEX(F4:G12, MATCH(C6,F4:F12,0),2)

Cele doua functii au sintaza:

=INDEX(array,row_num,column_num)

=MATCH(lookup_value,lookup_array,match_type)

array = precizeaza domeniul care contine datele;

row_num = desemneaza randul care contine valoarea cautata

column_num = desemneaza numarul coloanei care contine valoarea cautata

lookup_value = valoarea care se foloseste pentru a gasi informatia dorita din tabel; aceasta poate fi numar, text, valoare logica, nume sau referinta la o valoare. De exemplu, daca ne intereseaza numarul de telefon al unei persoane, vom folosi numele persoanei pentru lookup_value, dar ceea ce cautam este numarul ei de telefon ;

lookup_array = domeniul contiguu de celule care contine lista de valori in care se realizeaza cautarea; acest domeniu poate fi un tabel sau o referinta la un tabel.

match_type = specifica tipul de echivalenta cerut (0 = echivalenta exacta, -1 = cea mai mica valoare mai mare sau egala cu lookup_value, +1 = cea mai mare valoare mai mica sau egala cu lookup_value). Daca tipul de echivalenta este omis sau este 1, atunci vectorul de cautare (lookup_array trebuie sa fie ordonat crescator. Daca tipul de echivalenta este -1, atunci vectorul de cautare (lookup_array trebuie sa fie ordonat descrescator.

Functia MATCH returneaza pozitia relativa a informatiei cautate in tabloul specificat si nu informatia in sine.

Daca match_type este omis, se considera implicit 1.

MATCH('b',,0) returneaza valoarea 2, adica pozitia relativa a lui 'b' in tabloul (vectorul) .

MATCH nu face distinctie intre litere mari si mici

Daca MATCH nu gaseste valoarea cautata, returneaza valoarea de eroare #N/A.

Daca match_type este 0 si lookup_value este text, lookup_value poate sa contina caracterele de inlocuire (wildcard characters) asterisc (*) si semnul de intrebare (?).