Acest articol este un fel de continuare a articolului în care a fost explicată funcția OFFSET(). Pentru a înțelege mai ușor acest articol este recomandat să descărcați fișierele din imaginile prezentate.

Una din problemele interesante în elaborarea rapoartelor economice dinamice constă în realizarea de filtre dinamice pentru date. Aplicabilitatea filtrelor dinamice are sens doar în momentul în care datele sunt clasificate pe diferite categorii, categorii care pot fi uneori clasificate pe subcategorii.

Cerința problemei

Extragerea stocului din tabelul de stocuri pe baza categoriei și a denumirii produsului. Denumirea produsului trebuie să poată fi selectată doar în raport cu categoria selectată.

Pașii de rezolvare

Având în vedere modul complex de funcționare a funcțiilor următoare, pentru rezultate corecte se recomandă ca tabelul de date să fie sortat după Categorie. În cazul în care avem mai multe valori interdependente acestea trebuie sortate pe niveluri de subordonare corecte semnificației acelor date.

În tabel categoria de produs poate apărea pe mai multe linii. În acest context, pentru a putea implementa un filtru dinamic, avem nevoie de valoarea unică a categoriei.

Pentru a obține valoarea unică a categoriei, am creat zona categorii K3:K25 care conține o funcție INDEX() de extragere a valorii unice.

În celula K4 am inserat formula:

=IFNA(INDEX(tblStoc[Categorie];MATCH(0;COUNTIF($K$3:K3;tblStoc[Categorie]);0));””)

care permite indexarea valorilor de pe tblStoc, coloana Categorie în funcție de poziția unică a unui element, determinat pe egalitatea lui CountIf față de categoriile de mai sus cu 0. Este în fapt un artificiu prin care se păstrează doar unicatele din listă. IFNA() afișează un spațiu liber, în celulă, dacă în tabel sunt mai puține categorii decât formulele copiate în jos. Formula din K4 este o funcție array, deci trebuie introdusă cu CTRL+SHIFT+ENTER.

Pasul următor a fost definirea zonei de celule de categorii. Se selectează o zonă mai mare de celule de pe coloana K după care se apasă combinația de taste Ctrl+Shift+F3

După care se apasă Ok și vom putea apela blocul de celule după numele Categorii.

Pentru a ne asigura că numele este creat corect putem apăsa combinația Ctrl+F3 pentru afișarea numelor blocurilor de celule din fișierul curent.

Pentru definirea listei pentru celula de categorie (I3), se accesează meniul Data, DataValidation, Allow: List și se trece la sursă numele blocului de celule:

Pentru afișarea doar a produselor din categoria selectată la I3 va trebui să folosim o formulă OFFSET care să indexeze conținutul tabelului de date pe baza valorilor din categoria selectată.

Una din limitele Excel-ului este dată de faptul că în casetele de validare a datelor nu se pot folosi nume de tabele și coloane din tabelele de date. Poate în edițiile viitoare va fi rezolvată această problemă. De exemplu în versiunea Excel 2016 se poate scrie la sursa în Data Validation numele unui tabel și a unei coloane asociate, se selectează corelat zona de date, dar nu este permisă validarea formulei introduse în acest format.

Revenind, pașii pe care trebuie să-i parcurgem pentru a putea selecta doar produsele din categoria selectată, accesăm celula I4, meniul Data, Data Validation, Allow, List iar la sursă se va scrie formula:

=OFFSET($A$2;MATCH(I3;$C$3:$C$10;0);1;COUNTIF($C$3:$C$10;I3);1)

în care:

– $A$2 – reprezintă celula de start a tabelului cu date care va fi indexat;

– MATCH(I3;$C$3:$C$10;0) – reprezintă linia din tabel în care se regăsește prima înregistrare din categoria specificată în I3. (Reamintesc faptul că tabelul trebuie să fie sortat după categorie);

– 1 – reprezintă numărul coloanei față de $A$2 de pe care se vor extrage datele. În cazul prezentat denumirea produsului este pe a doua coloană ceea ce înseamnă prima coloană față de referință;

– COUNTIF($C$3:$C$10;I3) – reprezintă numărul de linii aferente produselor din categoria selectată. Acest număr specifică parametrul opțional height al funcției Offset. În cazul tabelului prezentat pentru produsele din categoria Panificație vom obține un vector (array) cu 3 înregistrări;

– 1 de final reprezintă lățimea vectorului, în cazul nostru o coloană.

În cazul în care doriți să studiați mai în amănunt modul în care a fost creat acest exemplu, puteți descărca fișierul de aici.

Un exemplu mai elaborat

Un alt exemplu de problemă este aceea în care dorim mai multe filtre interdependente.

Cerința este de a realiza un raport dinamic pentru o sursă de date de vânzări care să permită pe baza mai multor selecții, afișarea dinamică a unui tabel cu date.

Pentru câteva explicații, deschideți fișierul Filtre Dinamice. Atenție nu puteți lucra cu acest fișier on-lie. Trebuie descărcat local. Fișierul nu conține macros.

În foaia de calcul DateVanzari este stocată sursa de date. Această sursă este sortată după Continent, Tară, Categorie și nume Produs.

În foaia de calcul DateUnice, care în practică poate fi ascunsă, am implementat un mecanism de identificare a valorilor unice, specifice filtrelor selectate. De exemplu, dacă se va selecta un anumit continent pe coloana de Țări, vor apărea toate țările specifice acelui continent, corespondent fiecărei înregistrări din tabelul sursă. Pentru a stabili unicitatea țărilor pe coloana Tara am extras valorile unice înregistrărilor de pe coloana Țări. La fel am procedat și cu coloanele Categorii/Categorie.

Pasul următor a fost atribuirea numelor blocurilor de celule pentru a le folosi pentru filtrele din foaia de calcul Raport.

Formulele din DateUnice sunt combinații de Offset și Index/Match, asemănătoare celor explicate anterior. Limitele acestui model sunt date de faptul că nu putem folosi mai mult de 3 coloane imbricate. De exemplu dacă dorim să introducem și o coloană de Produse/Produs vom observa că Excelul oferă și alte valori decât cele specifice categoriei de produs. Aceeași problemă apare și în versiunea Excel 2016.

În zona de raport am implementat Data Validation pentru celulele corespondente continentului, țării și categoriei, după care am indexat dinamic datele din tabel pentru afișarea lor și am folosit formatarea condițională pentru afișarea dinamică a liniilor de raport și un simplu IF pentru determinarea poziției liniei de total.

Un must have pentru un raport ar fi acela de generare a unor grafice dinamice, genul celui din imagine.

Astfel de grafice pot fi fezabile doar pentru același număr de linii rezultat.

O soluție alternativă, mai simplă, dar cu neajunsurile sale, la această problemă, este utilizarea tehnicilor de Advanced filter.

Sper să fie util cuiva. Vă rog să nu ezitați să dați comentarii acestui articol în cazul în care aveți metode mult mai utile sau ușoare de filtrare avansată sau dacă ați descoperit erori în acest articol. Vă mulțumesc.