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.

Transformare numere in litere in Excel si SharePoint

În urma publicării articolului trecut legat de transformarea numerelor în litere în Excel, un mare specialist în Excel, Ciprian Stoian, membru al comunității DrExcel.ro și ITLearning.ro mi-a trimis o variantă actualizată a formulei care poate fi folosită fără tabele de căutare și funcții VLOOKUP(). Provocarea a fost legată de idea de a implementa această funcționalitate în listele de tip SharePoint, care se poate realiza prin coloane calculate în mod nativ sau xPath pe formularele InfoPath.

Problema coloanelor calculate în SharePoint este dată de limitarea formulelor la 1024 de caractere, așa că am folosit formula lui Ciprian (vezi imaginea) și am descompus-o în 3 coloane calculate diferite (Bani, SuteZeci, SuteMii) a căror rezultat l-am reconcatenat într-o coloană calculată cu LitereTotal.

image

Noua versiune, pentru care am obținut acordul lui Ciprian, a fișierului Excel incluzând ambele variante poate fi descărcat de aici, sau aici: https://gallery.technet.microsoft.com/Romanian-Transformare-917b9167 . Din punctul meu de vedere formula lui Ciprian este mai bună decât cea propusă inițial de mine pentru că funcționează oarecum diferit pe partea de calcul matematic a zecimalelor și este mai scurtă, deci mai ușor de memorat. ”Marele meu merit” este că am reușit să-l provoc! Smile Mai trebuie să vă amintesc faptul că Ciprian a fost cel care a propus cea mai funcțională soluție pentru problema cu combinațiile unice între mulțimi.

Mai jos găsiți un model de implemetare adaptată a formulei lui Ciprian în format funcțional pentru SharePoint din Office 365.

image

Pentru cei care doresc să dezvolte astfel de formulare citiți înainte seria de articole despre business forms. Pe lângă formula în sine apare problema completării on-line a formularului. Câmpurile calculate nu apar pe formularele de date Excel, ci apar abia după ce a fost completat formularul. Poate fi deranjant pentru cineva care are de completat mai multe date din cauză ca trebuie să deschidă și apoi să printeze formularul. Pentru a rezolva problema am creat un secondary data connection către lista de OP-uri care să fie declanșată la modificarea sumei. Utilizatorul scrie suma în caseta dedicată, ceea ce înseamnă change. După change, formularul trimite datele în listă (sursa Main), pasul 2 este interogarea pe secondary și apoi setarea formulei calculate de litere pe formularul curent. Asta se traduce prin a salva în timp real.

Cineva m-a întrebat de ce fără VBA?

Pentru că VBA-ul chiar dacă este excelent de bun și util, nu este suportat în toate mediile. În SharePoint poți introduce date în liste de date asemănător Excelului și poți folosi coloane calculate pentru combinarea valorilor. Dar nu poți folosi toate funcțiile Excel și nici pe departe VBA. De asemenea, în Excel Services (aplicațiile de editare on-line a fișierelor Excel) nu sunt permise macrourile VBA, din rațiuni de securitate. Pentru mine este suficient să caut metode, sau prieteni dispuși să-și își aloce timp pentru a ajuta. Nu în ultimul rând, consider că utilizatorii obișnuiți caută soluții simple la probleme complicate. Iar pentru mulți VBA-ul este deja o soluție suficient de complexă. Sigur uneori chiar nu se poate fără VBA, vezi aici propunerea mea pentur formula de însumare a unor celule Excel pe bază de culori.

Dacă mai aveți probleme interesate de Excel nu exitați să mă contactați sau să contactați forumurile

 

Sper să vă fie util! Mulțumim Ciprian!

Transformare numere in litere in Excel fara VBA

Pentru fanii aplicației Excel cred că nu este prima dată când aud despre o astfel de problemă. Transformarea sumelor din cifre în litere în Excel este utilă pentru documente de tip Chitanța, Ordin de plată, Mandate poștale. Excelul poate fi personalizat pentru a se adapta pe tipurile de formulare diferite.

Am întâlnit mai multe rezolvări ale acestei probleme, cea mai mare parte a lor programatic prin VBA. Nu am testat soluțiile respective pentru că am vrut să nu mă las influențat de modul lor de rezolvare. Formula pe care v-o propun eu este în imagine:

Formula de transformare din numere în litere

Pentru cei care se ”minunează” despre modul cum am ajuns la o asemenea formulă, tehnica este ”din aproape în aproape”. Smile Adică am descompus întâi zecile, apoi sutele și așa mai departe. Tabelul de căutare tblNr este în pagină cu scris alb ca să nu ”încurce”.

Rezultatul execuției este asemănător modelului următor:

Transformare numere in litere

Numărul maxim la care m-am oprit a fost de exact sub 1 milion. Presupun că cine are de făcut un OP de 1 milion are și o ditamai aplicația pentru asta sau îl scrie cu pixul, deci nu are nevoie de acest fișier Excel. Smile

Probleme întâmpinate: Excelul în funcție de calculele pe care le face cu zecimalele mai dă câte o eroare din când în când. De exemplu, zecimalele de genul: 0,10 se transformă în 0,0999999999999000 ceea ce nu reprezintă o egalitate din punct de vedere matematic pentru a putea face operațiuni cu ele. Problema celor 15 zecimale este documentată foarte interesant pe Wikipedia în articolul: Numeric precision in Microsoft Excel.

Descărcare fișier Excel

Dacă nu vă funcționează link-ul anterior puteți să accesați aceată pagină: https://gallery.technet.microsoft.com/Romanian-Transformare-917b9167

Referințe cu VBA

Referințe on-line

Articol asemănător

Vă rog să respectați drepturile de proprietate intelectuală și să nu comercializați acest produs. Se poate utiliza în scop personal sau profesional. În cazul în care sesizați erori în formule vă rog să mi le semnalați.

Vă mulțumesc pentru feedback și partajare articol.

Blog la WordPress.com.

SUS ↑