Funcția Filter() din #Excel 365

Odată cu introducerea formulelor de tip dynamic array în Excelul din Office 365, multe metode de lucru și formule se pot schimba prin simplificarea acestora sau obținerea mai rapidă a rezultatelor dorite. Excelul din O365 este disponibil doar celor care au o subscripție activă de Office 365 și sunt disponibile și funcționale atât în aplicația client, cât și în browser.

Din toate funcțiile noi, funcția Filter() atrage atenția prin simplitatea sa și varietatea de cazuri de utilizare. Pe parcursul acestui articol voi exemplifica diferite cazuri de utilizare, dar și comparația cu alte funcții de căutare.

Fișierul cu exemplele din acest articol, poate fi descărcat de la adresa: https://1drv.ms/x/s!ApGubfWFh8NurNdyu77XsSFFnhHMVw?e=Pzx6xg

Sintaxa funcției este foarte simplă:

Sau în format text: =FILTER(bloc de celule; conditii; dacă nu sunt înregistrări care să regăsească condiția)

Separatorul ; (punct și virgulă) apare din cauză că eu folosesc localizarea în română. Pentru cei care folosesc localizarea în Engleză, separatorul este virgula.

Parametrii funcției explicați:

Array (bloc de celule) este parametrul obligatoriu al funcției care permite specificarea:
- Numelui unui tabel – extrage date de pe toate coloanele tabelului în cazul în care condiția este îndeplinită;
- Numele unei coloane din tabel: numetabel[numecoloana] – extrage datele doar de pe coloana specificată;
- Numele mai multor coloane adiacente: Numetabel[colana1]:numetabel[Coloanan] – extrage datele de la coloana 1 pana la n
- Un bloc de celule: echivalentul unei coloane (A1:A10) sau unui tabel (A1:D10) – Extrage datele doar din blocul de celule specificat.
Include (condiție sau condiții cumulative) este un parametru obligatoriu care permite definirea:
- Unei singure condiții sub forma de coloană de tabel: numetabel[numecoloana]=valoare sau bloc de celule A1:A10 = valoare;
- Mai multor condiții în formatul: (conditia1)*(conditia2)*(conditia3)
If_Empty (textul de afișat dacă nu sunt înregistrări în blocul specificat care să îndeplinească una din condițiile cerute): este un parametru opțional sub formă de text sau o altă formulă care să ofere un rezultat în cazul în care Filter() nu returnează valori. Exemplu: „Nu există înregistrări care să îndeplinească condiția”

Exemplificare comparativă

În exemplul din imagine avem un tabel de stocuri cu intrări pentru diferite date. Tabelul se numește tblStoc, iar produsele care au codul evidențiat au mai multe intrări.

Problema simplă este de a căuta denumirea unui produs. Aparent cea mai simplă formulă este VLOOKUP, dar pentru a putea returna textul Nu este trebuie folosită și funcția IFNA(). Filter și Xlookup sunt singurele funcții care au incorporat parametrul de tratare a rezultatului #N/A în cazul în care codul de căutare nu există în tabelul de date.

Vulnerabilitatea funcțiilor VLOOKUP și OFFSET este dată de specificarea în mod absolut a numărului coloanei de pe care trebuie să extragem informația: 2 la Vlookup și 1 la Offset (La Offset numerotarea liniilor și coloanelor începe de la 0 față de celula de referință. Vezi detalii aici). Ca să putem îmbunătăți puțin această experiență putem combina cele două funcții cu un Match. (Vezi foaia de calcul P1(2). Am înlocuit în cele două funcții valorile absolute cu funcția Match care permite căutare după numele coloanei din I4 (Nume produs) în capul de tabel inițial. În felul acesta cele două funcții returnează valori corecte chiar dacă se adaugă sau elimină coloane.

Dezavantajul funcțiilor clasice este acela de combinare a mai multor funcții pentru obținerea aceluiași rezultat.

Căutare în stânga

În problema 2, pe același set de date (Numele tabelului este schimbat în tblStocP2), ne propunem ca pe baza numelui produsului să extragem codul acestui, ceea ce înseamnă căutare în stânga. Această cerință „scoate din joc” funcția VLOOKUP care nu poate realiză căutare decât în dreapta coloanei pe care se află valoare de regăsit.

În celelalte doar schimbăm coloanele din tabel și rezultatul va fi returnat corect. La Offset trebuie să schimbăm numărul coloanei de pe care se vor extrage datele, în cazul nostru prima coloana va avea valoarea 0.

Căutarea pe bază de wildcard-uri

În problema 3 am schimbat cerința în a aduce prețul de înregistrare, pentru a permite și funcției VLOOKUP să „ia parte” la acțiune.

În prima variantă am introdus numele întreg al produsului pentru a verifica dacă toate funcțiile aduc date corect.

Ca să poată funcționa, VLOOKUP-ul în acest caz NU mai permite adresarea sub forma numelui de tabel, fiind necesară adresarea blocului de celule în format „clasic”: B1:E31, coloana B fiind coloana 1 în acest caz, cea pe care se găsește valoarea de căutare.

În cazul în care introducem înlocuitorul * (asterix) în numele produsului vom observa că funcția Filter și XLOOKUP nu mai funcționează corect.

Caracterele pentru Wildcard-uri în Excel sunt:

* (asterix) înlocuiește oricare caracter dintr-o celulă; În Exemplul nostru: Paste* va regăsi toate produsele care încep cu Paste inclusiv Pastelini
? înlocuiește un singur caracter din celulă: De exemplu: Ia?i va regăsi toate valorile de tip: Iași sau Iasi.
~ (tilda) se folosește pentru a regăsi în celule cele două caractere. De exemplu dacă la un import diacriticele sunt înlocuite cu semnul întrebării, pentru a le regăsi pe toate va trebui să trecem la șirul de căutare expresia: *~?*

Pentru a putea face Filter să funcționeze cu Wildcard-uri trebuie să îl combinăm cu funcția SEARCH() în format dinamic array. Această funcție generează un tabel (array) cu poziția de start a șirului de căutare din tabelul de date. În acest caz, dacă va regăsi șirul de căutare va genera un număr sau eroarea #VALUE! dacă șirul nu apare în celulă. Acest artificiu ne permite să extragem prin combinare cu funcția ISNUMER() toate valorile care îndeplinesc criteriul de căutare specifificat. În felul acesta FILTER devine un instrument mult mai puternic decât toate celelalte funcții pentru că nu scoate doar prima valoare întâlnită ci toate valorile de căutare.

Extragerea mai multor coloane și linii

În cazul în care la tabelul de căutare (primul parametru din funcție) introducem numele tabelului (var 1) sau a adresarea mai multor coloane adiacente (var 2), filter extrage toate informațiile despre produsul cu codul specificat.

Pentru cei care se doresc la o comparație cu DGET() vă reamintesc că acesta poate extrage o singură valoare din tabel. În cazul în care sunt mai multe linii care îndeplinesc același criteriu vom obține eroarea #NUM!

O alternativă la această implementare de Filter() este Offset() cu precizarea că datele din tabel trebuie să fie ordonate după cod pentru a putea să extragă corect valorile. În acest fel va trebui să realizăm întâi sortarea într-o altă zonă, dacă nu vrem să modificăm tabelul inițial, prin utilizarea funcției SORT() apoi facem referire la această zonă în Offset().

Exemplificare cu Offset și Sort

Căutarea după criterii cumulative („AND”)

Filter permite în același timp căutarea după mai multe criterii.

În acest caz, cele două criterii sunt trecute separat în paranteze cu simbolul * între ele.

Căutarea cu „OR”

Operatorul OR nu poate fi folosit explicit în Filter. Dar în cazul în care avem două condiții sub formă de OR atunci putem adăuga simbolul + între cele două condiții pentru a realiza căutarea.

Fișierul cu exemplele din acest articol, poate fi descărcat de la adresa: https://1drv.ms/x/s!ApGubfWFh8NurNdyu77XsSFFnhHMVw?e=Pzx6xg

Reamintesc că ele funcționează doar în versiunea de Excel din Office 365.

Pentru utilizatorii de Google Spreadsheet, funcția Filter funcționează asemănător acestui articol și acolo cu diferența că nu este tratată componenta de #N/A dar cu plusul că aduce și capul de tabel în mod automat..

Sper să fie util cuiva.