Filtre dinamice sau interdependente în Excel cu Data Validation

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

clip_image002

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

clip_image004

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:

clip_image006

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.

clip_image008

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.

clip_image010

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.

Problema Excel – Transpose Like – Versiunea 2 (UPDATED 23.01.2017)

După interesul crescut pentru problema anterioară de Excel, un cititor a sugerat o variantă ”mai realistă” a problemei, inroducând conceptul de calendar.

Calendarul este generat de o aplicație alternativă și ajunge ca sursă de date într-o foaie nouă de Excel. Structura calendarului este: Marca, Nume Prenume, DataStart, DataFinal, Situație.

image

Situațiile din tabel au următoarea semnificație: CO – Concediu Odihnă, CM – Concediu Medical, D – Delegație, ZL – Zi Liberă. In Foaia colectivă de prezență trebuie să apară doar indicativul.

Cerințele problemei

În foaia FCP (Foaia Colectivă de Prezență) se dorește completarea cât mai corectă a datelor în așa fel încât să apară numărul de ore lucrate pe fiecare zi în parte, prin preluare din SursaDate, nimic, 0 (zero) sau X dacă nu există ore lucrate și coloana Situație în cazul în care data se află înregistrată în tabelul foaia de calcul Calendar.

Fișierul FCP – Transpose Like – ver 2.xlsx  poate fi descărcat de la această adresa: https://1drv.ms/x/s!ApGubfWFh8Nuq6gtIkcY3l5QPujXyQ

Constrângeri: nu sunt permise adăugiri de coloane suplimentare. Încercați să evitați folosirea VBA.

Indicații: Nu sunt.

Transmiterea soluțiilor

Aștept cu interes rezolvarea voastră până luni (23.01.2017) ora 16:59 pe adresa de e-mail valy.greavu@outlook.com cu subiectul Problema Transpose Like V2.

Premii

Așteptăm propuneri de sponsorizare pentru cele mai bune soluții. Smile (pe bune – le anunț aici dacă se oferă cineva)

UPDATE – Rezultatele pe 23.01.2017

Nu știu câți dintre voi au așteptat rezultatele acestei formule.

Prima soluție a sosit după  de la Iulian

În semnătura lui Iulian este trecută compania PROWORK COOPERATIVE din Italia. Interesant este că Iulian a oferit o soluție după doar două ore de la publicarea articolului.

Soluția lui Iulian se bazează pe un Index și Lookup cu vector de rezultat iar soluția lui întoarce rezultate corecte pe sursele de date propuse:

Iulian

Formula lui Iulian pentru celula C6 este:

=IFNA(INDEX(SursaDate!$D:$D;MATCH(DATE($B$3;$B$2;C$5)&$A6;INDEX(SursaDate!$A:$A&SursaDate!$B:$B;);0));IFNA(LOOKUP(2;1/(Calendar!$C:$C<=DATE($B$3;$B$2;C$5))/(Calendar!$D:$D>=DATE($B$3;$B$2;C$5))/(Calendar!$A:$A=$A6);Calendar!$E:$E);”X”))

Formula lui Iulian poate fi îmbunătățită prin adresarea specifică a coloanelor din cele doua tabele de date în loc de folosirea referinței la nivel de coloană. Am văzut de multe ori în practică referirea la nivel de coloana, dar asta presupune calcul în formule complexe până la ultima linie din foaia de calcul, ceea ce duce la o scădere de performanță.

A doua soluție a sosit de la FlorinE.

Soluția lui Florin folosește o formulă array pe bază de Index Match condițional.

FlorinE

Formula lui Florin pentru celula C6 este:

=IFERROR(INDEX(SursaDate!$D$1:$D$94;MATCH(1;(DAY(SursaDate!$A$1:$A$94)=FCP!C$5)*(SursaDate!$B$1:$B$94=FCP!$A6);0));IFERROR(INDEX(Calendar!$E$1:$E$7;MATCH(1;(Calendar!$A$1:$A$7=FCP!$A6)*(DAY(Calendar!$C$1:$C$7)<=FCP!C$5)*(DAY(Calendar!$C$1:$C$7)+Calendar!$D$1:$D$7-Calendar!$C$1:$C$7>=FCP!C$5);0));0))

Formula este funcțională și întoarce rezultate corecte și complete. Soluția lui Florin este modul în care am rezolvat și eu problema numai că în loc de adresarea pe celule eu am folosit adresarea specifică pe coloane de tabel.

Un pic mai optim, soluția lui Florin ar fi putut fi scrisă:

=IFERROR(INDEX(tblSD[Nr ore lucrate];MATCH(1;(DAY(tblSD[Data])=C$5)*(tblSD[ [ MarcaAngajat] ]=$A6);0));IFERROR(INDEX(tblCalendar[Situație];MATCH(1;(tblCalendar[ [ MarcaAngajat] ]=$A6)*(DAY(tblCalendar[DataStart])<=C$5)*(DAY(tblCalendar[DataStart])+tblCalendar[DataFinal]-tblCalendar[DataStart]>=C$5);0));0))

Din formula lui Florin lipsește luna calendaristică și anul, rezultând că nu se aplică pentru surse de date care se ”întind” pe mai multe luni calendaristice.

A treia soluția a venit de la Ciprian Stoian

El a propus două metode de rezolvare: Una clasică cu formule și una cu Power Query.

Mesajul complet a lui Ciprian este:

1. Cea din pagina FCP e cu formule și are ca sursă tabelele date.

2. Cea din pagina Query funcționează doar în Office 2016 (de testat și pe 2013 dacă este instalat Power Query). Pagina Tabelă sau view SQL e folosită ca sursă în acest moment, dar într-un mediu de lucru real ar trebui să fie un view în SQL. Pentru „floricele” anul și luna pot fi definiți ca parametri în șirul de conexiune. Userul ar avea o singură foaie în Excel și cu un simplu refresh are FCP-ul dorit.

Având în vedere că avem cu toții de învîțat ceva din formulele lui Ciprian voi încărca direct fișierul lui Ciprian de la adresa: Fișier Transpose Like v 2 CiprianS

Formula lui Ciprian este diferită de tot ce știam până acum. Valoarea formulei pentru celula C6 este:

=CHOOSE(1+IfOre+2*IFCal;””;Ore;INDEX(tblCalendar[Situație];rCal))

CiprianSF1

Mi-a luat aproape 1 minut să mă minunez și să descoper cum a fost implementată. Dacă vă uitați cu atenție la formulă referințele cu albastru sunt referințe de blocuri de celule, dar în fapt, aceste referințe sunt obținute printr-un set de formule din NameManager.

Cei care doresc să studieze pot descărca fișierul de mai sus pentru o analiză detaliată:

image

Despre a doua soluție, cei care sunt interesați pot consulta de asemenea fișierul atașat. Trebuie doar să aveți Power Query instalat.

Soluția propusă de mine

Având în vedere că este vorba de o continuare a unui articol, personal am propus o formulă array ca o continuare a celei din articolul precedent.

Formula în schimb nu este completă, în timpul redactării ajungând la concluzia că varianta aleasă nu este optimă.

FormulaValy - Offset dinamic

Formula funcționează acum doar pe două intervale de timp din calendar. Exemplu angajatul cu Marca 123 are 3 intervale în calendar. Ingeniozitatea formulei pe lângă monstruozitatea sa este dată de faptul că folosește un concept de Offset dinamic. În lina 11 preiau în idex valorile ofsetului de pe coloana 2 din calendar, în cazul în care există marca. Altfel pun 0 (linia 13). Zona de Else a IF-ului de pe lina 10 semnifică faptul că marca există în calendar dar nu este pe intevalul de date de la linia 11. Așa că la linia 14 generez un nou IF care să compare cu un nivel mai jos față de prima valoare întâlnită a câmpului Marca. Căutarea stabilește în mod dinamic începutul blocului de căutare la adresa celei dea doua valori a mărci de pe coloana specifică.

Pare dificil dar funcționează parțial. Pentru al treilea interval ar trebui modificată locația lui 0 de pe lina 23.

Personal ca să le pot scrie mai ușor astfel de formule le dezvolt bucată cu bucată în Notepad++.

Menționez că am mai primit o soluție parțială din partea FlorentineiS cu IF si SUMPRODUCT.

 

Sper că vă plac și apreciați rezolvărilor fanilor Excel de pe Internet. De asemenea, sper să vă fie utile.

În cazul în care mai aveți probleme interesante de Excel vă rog să nu ezitați să mă contactați!

Respect!

Blog la WordPress.com.

SUS ↑