Cum extragem date dintr-un tabel in functie de o conditie?

Buna,

Astazi vreau sa va impartasesc o formula pe care o folosesc ca sa extrag date dintr-un tabel in functie de o conditie. Tin sa va spun ca este o formula mai grea pentru ca se folosesc multe functii: If, Match, Index, Countif, Row, insa va invit sa urmariti logica din spatele ei pentru a vedea cum va poate ajuta.

Configurarea tabelului

Pentru a putea exemplifica cum se pot extrage date dintr-un tabel in functie de o conditie, vom folosi un tabel in care sunt centralizate vanzarile dintr-un an in functie de client si produs.

Pentru ca aceasta formula sa functioneze corect, tabelul trebuie sortat alfabetic in functie de coloana pe care se pune conditia, in cazul nostru coloana Produs.


In alt sheet, in zona B3:C3 am creat o lista derulanta pentru a putea selecta produsul in functie de care vom extrage clientul din tabelul initial.

Formula calcul program plata - Chandoo

Unul din blogurile de excel la care sunt abonata este http://chandoo.org/wp si aseara am primit newsletter cu noua postare de pe acest blog. In aceasta postare cititorii erau provocati sa creeze formula pentru calcularea programului de plata a unui reprezentant de vanzari. (Calculate Payment Schedule Homework)

Si cum era sa ratez asemenea provocare .... am downloadat fisierul pus la dispozitie de autor si m-am pus pe treaba. M-am chinuit cam 20 de minute pe urma am facut o pauza si l-am dat gata. Rezolvarea mea este una care nu foloseste functii prea complexe, un alt utilizator a postat o rezolvare chiar misto :D. Dar toate la timpul lor si s-o luam ca la scoala:


Datele problemei

Dupa cum am spus mai sus, provocarea este crearea formulei care calculeaza programul de plata pentru un reprezentant de vanzari. Dar cum era de asteptat sunt si conditii pentru a-si primi veniturile:

1. Trebuie sa castige cel putin 200 dolari inainte de a fi platit;
2. Trebuie sa existe o diferenta de 7 zile intre platile succesive.

Pentru aceasta problema am primit un tabel care contine urmatoarele coloane: Data (coloana B), Comisiul castigat in aceea zi (coloana C) si  Valoarea platii (coloana D). In rezolvarea problemei puteai introduce inca o coloana ajutatoare (lucru pe care l-am facut).

Utilizarea functiilor LOOKUP pentru interogarea tabelelor de date - INDEX

Am ramas datoare cu o ultima postare pentru interogarea tabelelor de date. Asa cum v-am promis voi prezenta functia INDEX care este favorita mea pe parte de functii Lookup. Este preferata mea pentru ca pot cauta cum vreau eu si in plus pot cauta avand mai multe conditii :D.

• Functia INDEX - se foloseste pentru a returna o valoare dintr-un tabel in functie de coloana si randul la care se gaseste acea valoare. Sintaxa acestei functii este urmatoarea: INDEX(array,row_num,column_num)

1.  Array - reprezinta tabelul din care dorim ca functia sa ne returneze rezultatul. Un mic sfat ar fi sa faceti referinta regiunea de celule daca doriti sa copiati formula si in alte celule.
2. Row_num - reprezinta numarul randului din care dorim sa fie adusa valoarea. Daca row_num este omis atunci column_num este obligatoriu. Tot in acest caz, functia index nu va returna doar o valoare ci va returna toate valorile din coloana respectiva.
3. Column_num - reprezinta numarul coloanei din care se va aduce valoarea dorita. La fel ca la mai sus, daca column_num este omis, row_num este obligatoriu.

Cea mai usoara utilizarea a functiei INDEX este atunci cand cunoastem numarul randului si coloanei, dar de cele mai multe ori interogarile se fac in tabele care sunt destul de mari. In acest caz, numarul randului sau al coloanei nu se cunoaste. La fel ca in postarea anterioara, pentru a afla aceste necunoscute folosim functia MATCH.

Pentru a intelege mai bine cum putem folosi INDEX, am creat un tabel in care avem urmatoarele coloane: Cod produs, Denumire produs si stocul existent in Depozit si Magazin:

Utilizarea functiilor LOOKUP pentru interogarea tabelelor de date - HLOOKUP

Desi in postul anterior am spus ca o sa revin cu un alt post despre functiile HLOOKUP si INDEX, acum va voi scrie doar despre functia HLOOKUP pentru ca am inceput sa scriu si a iesit cam lunga povestea ca sa mai pot scrie si despre INDEX.

• Functia HLOOKUP - este similara cu VLOOKUP, insa face oposului ei. Daca VLOOKUP cauta o valoare in prima coloana a unui tabel, HLOOKUP cauta o valoare in primul rand al unui tabel de date. Sintaxa acestei functii este urmatoarea:  HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

1. Lookup_value - reprezinta valoarea pe care dorim sa o cautam in primul rand al tabelului de date. Dca in tabelul de date nu exista valoarea cautata de hlookup atunci functia va returna eroarea #N/A.
2. Table_array - este tabelul de date in care cautam valoarea dorita. Pentru table_array se poate folosi o selectie din fisier sau o zona definita prin Named Range. Principala conditie in hlookup este ca primul rand al tabelului sa contina valorile unde cautam lookup_value.
3. Row_index - reprezinta numarul randului din care dorim sa fie returnata informatia pentru valoarea cautata. Daca row_index este mai mic ca 1, functia va returna eroare #VALUE!, iar daca row_index este un numar mai mare decat numarul de randuri din tabel, hlookup va returna eraoare #REF!.
4. Range_lookup - la fel ca la VLOOKUP, acest camp reprezinta o valoare logica ce specifica daca se doreste o potrivire exacta sau o potrivire aproximativa. Daca este scris True, 1 sau este omis functia va returna o potrivire aproximativa, iar daca este scris False sau 0 atunci va returna o potrivire exacta.