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.

Descifrarea formulei pentru extragerea datelor

=IF((MATCH($B$3,tabel!$B$2:$B$26,0)+ROW(H1)-1)<=
(MATCH($B$3,tabel!$B$2:$B$26,0)+
COUNTIF(tabel!$B$2:$B$26,extract!$B$3)-1),INDEX(tabel!$A$2:$A$26,
MATCH($B$3,tabel!$B$2:$B$26,0)+ROW(extract!H1)-1,1),"") 

 O formula foarte lunga dar pe care o s-o analizam separat.

Si sa incepem chiar cu functia de extragere a informatiilor.

INDEX(tabel!$A$2:$A$26,MATCH($B$3,tabel!$B$2:$B$26,0)
+ROW(extract!H1)-1,1)

Pentru a extrage clientii care au comandat produsul selectat in zona B3:C3, am folosit functia INDEX: 

• Array - Am selectat prima coloana din tabelul nostru; 
• Row_num - reprezinta numarul randului din care dorim sa fie adusa valoarea. Atunci cand dorim sa aflam numarul unui rand folosim functia MATCH, insa astazi doar MATCH nu este de ajuns. Pentru a afla numarul randului la care se afla informatia pe care dorim sa o extragem folosim sintaxa urmatoare: MATCH($B$3,tabel!$B$2:$B$26,0)+ROW(extract!H1)-1. Cu MATCH aflam primul rand unde se gaseste produsul selectat in zona B3:C3 la care trebuie sa adunam cate 1 pentru fiecare rand din tabel completat cu aceasta formula. Pentru acest lucru am folosit ROW(extract!H1)-1. Despre ROW am vorbit intr-un articol anterior, in exemplul de azi il folosesc pentru ca am nevoie de o functie de incrementare, iar ROW poate face asta daca celula selectata este fara referinta. In exemplul de azi, am folosit celula H1 dar se poate folosi orice celula cu conditia sa fie de pe primul rand din sheet. Daca nu am scade 1, informatia de pe randul in care se gaseste prima oara produsul selectat ar fi sarita.
• Column_num - reprezinta numarul coloanei din care se va aduce valoarea dorita. In cazul nostru, aici introducem 1 deoarece avem nevoie de prima coloana din tabel.

De ce formula noastra nu este doar partea de mai sus? Pai daca am folosi doar partea cu INDEX, formula ar aduce toti clientii care se afla sub primul rand care contine produsul selectat in zona B3:C3. Ca sa aducem doar clientii care au cumparat acest produs trebuie sa folosim o conditie care sa opreasca formula INDEX cand pe coloana cu produse se ajunge la alta inregistrare decat cea dorita de noi.

Ca sa putem face acest lucru folosim IF. Ca sa oprim formula INDEX  avem nevoie de o sintaxa prin care sa comparam numarul randului de la care extragem informatia cu numarul ultimului rand la care se afla produsul selectat in zona B3:C3:

 (MATCH($B$3,tabel!$B$2:$B$26,0)+ROW(H1)-1)<=
(MATCH($B$3,tabel!$B$2:$B$26,0)+
COUNTIF(tabel!$B$2:$B$26,extract!$B$3)-1)

Pentru a afla numarul randului de la care extragem informatia este secventa din INDEX, de la Row_number.

In continuare trebuie sa aflam numarul ultimului rand la care se afla produsul selectat in tabelul din care extragem datele. Ca sa facem acest lucru trebuie sa adunam la numarul primul rand unde se gaseste produsul selectat, MATCH($B$3,tabel!$B$2:$B$26,0) , numarul total de inregistrari de pe coloana Produs unde se gaseste produsul selectat, COUNTIF(tabel!$B$2:$B$26,extract!$B$3). Din numarul rezultat trebuie scazut 1 deoarece atat MATCH cat si COUNTIF numara primul rand la care se gaseste produsul selectat, astfel numarul final va fi cu 1 mai mult decat rezultatul corect.

Daca comparatia de mai sus este adevarata, INDEX va extrage informatia dorita, iar daca este falsa functia nu va returna nimic.

In tabelul in care extragem clientii care au achizitionat un anumit produs, avem si coloana Valoare. Aceasta coloana reprezinta valoarea achizitiilor pe respectivul client si produs. Ca sa aducem si aceasta informatie din tabelul initial se foloseste functia INDEX dar in varianta ei cu doua conditii. Daca doriti sa vedeti cum se poate folosi INDEX in acest caz, va invit sa cititi articolul urmator:
http://invatamexcel.blogspot.ro/2011/10/utilizarea-functiilor-lookup-pentru.html

Exemplul de azi este mai greu, dar va invit sa incercati formula aceasta o data si o sa vedeti ca dupa aceea insuirea de mai sus vi se va parea mult mai logic.

Daca doriti sa intelegeti mai bine exemplul din acest articol, puteti downloada fisierul de lucru de la urmatorul link: extract from table.xlsx.