De cele mai multe ori pentru filtrarea unui tabel de date folosim opțiunea AutoFilter, iar în situațiile mai complexe folosim Advanced Filter.
Iar o dată cu introducerea Dynamic Arrays, a fost adăugată și formula Filter.
Poate te întrebi în ce situații ar fi necesară această formulă când deja există cele două opțiuni de filtrare a datelor.
Din punctul meu de vedere formula Filter poate fi considerată ca una de tip lookup atunci când este necesar să returnezi toate rezultatele găsite într-un tabel.
Atât Vlookup/Xlookup, cât și combinația Index+Match extrag doar prima valoare găsită într-un tabel de date.
În urmă cu mai mulți ani am avut nevoie să extrag toate înregistrările dintr-un tabel care îndeplineau o condiție. Iar soluția găsită s-a bazat pe o combinație de if, countif, Index și Match. Însă, tabelul trebuia sortat de fiecare dată și mereu trebuia să verific dacă rândurile cu formulă erau suficiente pentru rezultatele afișate.
Definiția și sintaxa FILTER
Formula Filter permite să preiei sau să filtrezi un set de date pe baza criteriilor furnizate printr-un argument. Astfel poți crea liste dinamice, pe baza unor criterii, care se actualizează automat, atunci când datele sursă se modifică.
Aceasta este disponibilă în Office365 și Excel 2021.
Formula este clasificată în categoria celor de tipul Dynamic Arrays. Rezultatul este o matrice dinamică care se revarsă automat în celulele învecinate vertical sau orizontal.
FILTER(array, include, [if_empty]])
Filter are doar trei argumente, primele două fiind obligatorii.
- array – reprezintă intervalul de celule pe care dorești să îl filtrezi.
- include – este un argument de tip logic în care furnizezi criteriile de filtrare. Acest argument se asemănă cu porțiunea de testare a unei funcții If; mai exact se selectează un interval de celule și se completează valoarea criteriu. În urma acestei comparații, orice înregistrare care îndeplinește criteriul specificat (TRUE) va fi inclusă în rezultatul formulei Filter. Iar orice înregistrare care nu îndeplinește criteriul (False) va fi exclusă din rezultat.
- [if_empty] – este un argument opțional în care poți introduce valoarea care va fi afișată atunci când nu există rezultate în urma aplicării criteriilor de la argumentul include.
Caracteristici importante ale formulei FILTER
Funcția FILTER se poate utiliza pentru a filtra o zonă de date atât orizontal, cât și vertical.
Atunci când utilizezi FILTER între două fișiere excel este necesar ca ambele să fie deschise. Dacă nu sunt, funcția va returna eroarea #REF!.
Deoarece funcția FILTER varsă rezultatul în mai multe celule, este nevoie să te asiguri că sunt suficiente celule goale în dreapta și dedesubt. Dacă nu, funcția va afișa eroarea #SPILL.
Cum se utilizează FILTER? (exemple)
Următoarele exemple ilustrează modul în care poți utiliza funcția Filter.
Să presupunem că ai următoarea bază de date cu vânzările din anul 2022 pe produse și pe locații:
Pentru a descărca fișierul cu exemple folosește butonul de mai jos:
DownloadExemplul 1: Filtrarea valorilor în funcție de un criteriu de tip text
Este necesar să extragi fiecare locație care a realizat vânzări de Capsatoare și valoarea acestor vânzări.
Mai jos este formula care face acest lucru:
=FILTER(DataSet[[Magazin]:[Valoare vanzari ]],DataSet[Produs]="Capsator","Not found")
Pentru primul argument am selectat doar coloanele cu locațiile și valoarea vânzării. Însă se poate selecta și tot tabelul.
Când apeși Enter, Excel va afișa prima locație găsită pentru produsul Capsator în celula J11, iar valoarea vânzării în celula K11. Celelalte rezultate fiind vărsate în celulele de mai jos.
Pentru baza de date din acest exemplu am folosit un tabel de tip Table: atunci când este adăugată o nouă înregistrare în tabel, Filter se actualizează automat pentru a include valoarea suplimentară în Spill Range.
Exemplul 2: Filtrarea valorilor în funcție de un criteriu de tip numeric
De data aceasta, dorești să extragi produsele și locațiile care au realizat vânzări mai mari de 1000 de lei.
Mai jos este formula care face acest lucru:
=FILTER(DataSet2[[Produs]:[Valoare vanzari ]],DataSet2[[Valoare vanzari ]]>J8,"Not found")
În acest exemplu, am introdus criteriul în formulă folosind celula J8. Acest lucru oferă mai multă flexibilitate utilizatorului pentru a verifica mai multe situații fără a fi nevoie să modifice formula.
Exemplul 3: Filtrarea valorilor folosind wildcards la criteriu
Formula FILTER nu acceptă caractere de tip wildcard, dar putem folosi o combinație de formule pentru a introduce un test logic în argumentul include.
Este necesar să extragi produsele, locațiile si valoarea vânzărilor pentru toate produsele care conțin caractere “ca”.
Pentru această situație vei folosi următoarea formulă:
=FILTER(DataSet3[[Produs]:[Valoare vanzari ]],ISNUMBER((SEARCH(J8,DataSet3[Produs]))),"Not found") 
Să parcurgem fiecare componentă a formulei și să vedem ce rol joacă fiecare.
Prima oară se calculează rezultatul formulei SEARCH. Aceasta caută orice text din coloana Produs care are conține „ca”. Avem 3 astfel de produse: Capsator, Creion mecanic și Caiet dictando. Este important să reținem faptul că Search returnează doar poziția caracterului căutat.
În continuare, folosim formula ISNUMBER, care are un rol foarte simplu. Aceasta verifică fiecare rezultat oferit de Search și va atribui TRUE pentru rezultatele care sunt un număr și FALSE pentru rezultatele care nu sunt un număr (adică sunt o eroare #VALUE! în cazul nostru).
În continuare, Isnumber transmite rezultatul acestei verificări (adică 12 TRUE pentru numere și 12 FALSE pentru erorile #VALUE!) către formula Filter.
Exemplul 4: Filtrarea valorilor prin respectarea a două criterii în același timp
Poate te întrebi dacă Filter se poate folosi și atunci când avem nevoie ca filtrarea să se realizeze în funcție de două condiții care să fie respectate simultan.
Da, se poate și chiar într-un mod foarte ușor.
=FILTER(DataSet4[[Produs]:[Valoare vanzari ]],(DataSet4[Produs]=J7)*(DataSet4[[Valoare vanzari ]]>J8),"Not found") Introducem cele două criterii în argumentul include, fiecare fiind scrise intre paranteze () cu semnul înmulțirii * între condiții.
Folosind „*” practic, rezultatele după verificarea celor două criterii se înmulțesc. Astfel există două posibilități:
- Se obține FALSE dacă expresiile din ambele paranteze returnează false (false*false) sau una dintre ele returnează false (false*true).
- Se obține TRUE dacă expresiile din ambele paranteze returnează true (true*true).
Exemplul 5: Filtrarea valorilor care respectă cel puțin un criteriu
În acest exemplu, vom folosi Filter pentru a extrage informațiile legate de produse, locații și valoarea vânzărilor care respectă una din următoarele condiții produsul să fie Capsator, Locația să fie Unirii.
Mai jos este formula care face acest lucru:
=FILTER(DataSet4[[Produs]:[Valoare vanzari ]],(DataSet5[Produs]=J7)+(DataSet5[Magazin]=J8),"Not found") De data aceasta, introducem cele două criterii în argumentul include, fiecare fiind scrise între paranteze () cu semnul plus + între condiții.
Concluzie
Sper că prin acest articol am acoperit cât mai multe din modalitățile în care se poate folosi formula FILTER și că vei putea aplica această formulă în fișierele tale Excel.
Folosește zona de comentarii pentru a împărtăși experiența ta sau alte sfaturi utile legate de Filter.
