Blog

În acest tutorial voi prezenta mai multe metode de filtrare a datelor în Excel.

AutoFilter

Filtrarea unui tabel presupune afișarea numai a acelor rânduri care îndeplinesc anumite criterii (celelalte rânduri sunt ascunse).

Afișarea butonului de filtrare este opțională.

În fila Home – grupul Editing – Sort & Filter → butonul Filter dacă este activat/dezactivat face să apară/dispară butonul Filter în/din antetul tabelului (condiția este ca mouse-ul să fie într-o celulă din tabel). Când faceți click pe acest buton sunt afișate opțiuni de sortare și filtrare.

Pentru înlăturarea filtrelor se apasă din fila Home – grupul Editing – Sort & Filter – butonul Clear.
Funcția AutoFilter din Excel face ca filtrarea datelor dintr-o listă de date să fie foarte ușoară.

Faceți click pe butonul AutoFilter din coloana pe care doriți să filtrați datele și apoi alegeți criteriile de filtrare corespunzătoare din meniul derulant al coloanei respective.

Opțiunile de filtrare din meniul derulant AutoFilter al unei coloane depind de tipul datelor din acea coloană (număr, text, dată).

În tabelul Vânzări de mai jos, presupunem că sunteți interesați doar de vânzările din luna aprilie.

Pentru aceasta, faceți click pe butonul de filtrare al coloanei Luna, eliminați bifa din caseta Select All (care deselectează totul). Apoi puneți o bifă lângă Aprilie și apăsați OK.

Rezultatul este următorul.

Se observă că unele numere de rând lipsesc. Aceste rânduri sunt ascunse și conțin date care nu îndeplinesc criteriile specificate.

La același rezultat ajungeți și dacă realizați filtarea după culoarea fontului (roșu pentru luna Aprilie), astfel:

În mod analog se realizeză filtrarea după culoarea de umplere a celulelor.

Puteți aplica filtre pentru mai multe coloane.

Dacă, spre exemplu, doriți ca pentru luna aprilie să vedeți doar vânzările agentului de vânzări al cărui cod este BVV15, în tabelul anterior acționați butonul de filtrare pe coloana Cod Agent vânzări, eliminați bifa din caseta Select All (care deselectează totul) apoi puneți o bifă lângă BVV15 și apăsați OK. Rezultatul este următorul:

Se observă că Excel adaugă un filtru conic butoanelor AutoFilter pentru câmpurile Cod Agent vânzări și Luna, indicând faptul că lista este filtrată folosind criterii care implică ambele câmpuri.

Meniul derulant AutoFilter pentru un câmp care conține doar text sau o combinație de text – valori numerice conține o opțiune Text Filters care, atunci când faceți click pe ea, afișează un submeniu care conține opțiunile auto-explicative alăturate:

Meniul derulant AutoFilter pentru câmpuri numerice conține o opțiune Number Filters care, atunci când faceți click pe ea, afișează un submeniu care conține opțiunile auto-explicative alăturate:

Dacă, spre exemplu, doriți ca pentru luna aprilie să vedeți doar vânzările cu valoare mai mare de 15000, în tabelul anterior acționați butonul de filtrare pe coloana Valoare vânzări și din meniul derulant alegeți Number Filters → Greater Than.

În fereastra Custom AutoFilter completați valoarea 15000 și apăsați butonul OK.

Rezultatul este acesta:

Meniul derulant AutoFilter pentru un câmp care conține doar intrări de tip dată are o opțiune Date Filters care, atunci când faceți click pe ea, afișează un submeniu care conține opțiunile auto-explicative de mai jos:

Custom Filter

Puteți face clic pe opțiunea Custom Filter (filtru personalizat) care apare pe ultima poziție în meniul derulant AutoFilter, atât pentru câmpurile de tip text, cât și pentru câmpurile de tip dată și număr. Când faceți click pe opțiunea Custom Filter, Excel 2019 deschide caseta de dialog Custom AutoFilter unde puteți specifica propriile criterii de filtrare utilizând operatorii logici AND/OR.

De exemplu, folosind tabelul Vânzări prezentat anterior, dacă doriți să vedeți doar valorile vânzărilor situate în intervalul închis la dreapta [15874,45123), acționați butonul de filtrare pe coloana Valoare vânzări, alegeți operatorul AND și completați caseta Custom AutoFilter astfel:

După filtrare tabelul arată așa:

Dacă doriți să vedeți doar valorile vânzărilor din luna Mai sau valorile vânzărilor din luna Aprilie, acționați butonul de filtrare pe coloana Luna și completați caseta Custom AutoFilter astfel:

După filtrare tabelul arată așa:

Dacă doriți să vedeți doar valorile vânzărilor din luna Mai sau valorile vânzărilor din luna Octombrie, acționați butonul de filtrare pe coloana Luna și completați caseta Custom AutoFilter astfel:

După filtrare tabelul arată așa:

Dacă vreți să realizați filtrare pentru câmpuri de tip text atunci când există o potrivire parțială, atunci trebuie să utilizați metacaracterele (sau wildcards). Acestea sunt:

• semnul de întrebare (?) pentru a se potrivi cu orice caracter unic,
• asteriscul (*) pentru a se potrivi cu orice secvență de caractere.

Spre exemplu, dacă doriți să vedeți valoarea vânzărilor doar pentru lunile care încep cu Ma, acționați butonul de filtrare pe coloana Luna și completați caseta Custom AutoFilter astfel:

După filtrare tabelul arată așa:

Dacă doriți să vedeți valoarea vânzărilor doar pentru luna care are 3 caractere, începe cu litera “M” și se termină cu litera “i” acționați butonul de filtrare pe coloana Luna și completați caseta Custom AutoFilter astfel:

După filtrare tabelul arată așa:

Filltre Avansate

Un filtru avansat și un macro înlocuiesc cu succes formule de căutare sofisticate de genul :

=IF(ISERROR(INDEX($A$1:$B$7;SMALL(IF($A$1:$A$7=$A$10;ROW($A$1:$A$7));ROW(2:2));2));"";INDEX($A$1:$B$7;SMALL(IF($A$1:$A$7=$A$10;ROW($A$1:$A$7))))

Să nu uitam faptul că, în Excel, dacă nu comutăm modul de calcul în Manual, toate formulele, din toate celule fișierului (din toate foile de calcul), primesc comanda de executare SIMULTAN, la orice acțiune de activare/dezactivare a unei celule.

De aceea nu vă recomand să abuzați de utilizarea formulelor, mai ales a celor de căutare, care au deja încorporată o serie destul de mare de operații.

De ce avem nevoie pentru aplicarea unui filtru avansat?

De un tabel (baza de date) din care dorim să extragem informații, de un “filtru”(o serie de condiții pe care trebuie să le îndeplinească înregistrările din tabel) și de o zonă în care se vor afișa aceste înregistrări “filtrate”.

Sunt două opțiuni de afișare a rezultatului:

1. Filter the list in place (însemnând că toate rândurile corespunzătoare înregistrărilor tabelului, care nu îndeplinesc condițiile filtrului, vor fi ascunse) și
2. Copy to another location (înregistrările care îndeplinesc condițiile filtrului se vor copia într-o altă locație pe care va trebui să o specificăm).

Să luam un exmplu concret:

Avem un tabel cu angajați în care am înregistrat informații despre aceștia. Dorim să extragem angajații din departamentul A.

Avem mai multe posibilități, fie aplicăm un filtru automat și copiem informația în altă parte cu Copy – Paste fie apelăm la filtrul avansat pe care îl înregistrăm într-un macro și la modificarea condiției de filtrare și rularea macroinstrucțiunii, se reface extragerea.

Pentru filtrarea avansată, vom introduce un criteriu (zona A1:A2) astfel: scriem numele coloanei pe care vrem să impunem condiția de filtrare.

Observație: Denumirile coloanelor la care se face referire, indiferent unde, trebuie să fie absolut identice cu cele ale tabelului sursă!).

Acum să analizăm fereastra Advanced Filter (Fila Data – grupul Sort&Filter – butonul Advanced).

În zona Action avem cele două opțiuni semnalate mai sus.

Considerăm mai întâi varianta Filter the list, in place. Observați, în acest caz, că zona Copy to
another location este inactivă.

Alegem pentru List range tabelul pe care dorim să-l filtrăm (mare atenție să cuprindeți în selecție
și linia de antet a tabelului), iar în zona Criteria range selectăm “tabelul de criterii” (în cazul nostru
zona A1:A2) și apăsăm OK.

Rezultatul va fi:

Folosirea opțiunii Copy to another location

În primul rând va trebui să ținem cont de un factor foarte important: la aplicarea unui filtru
avansat, criteriul și rezultatul filtrării trebuie să fie în aceeași foaie de calcul!!

Pentru a extrage rândurile din tabelul sursă într-o altă foaie de calcul (foaia “extragere” în cazul
exemplului nostru) sau chiar într-un alt fișier, introducem întâi tabelul de criterii și, în cazul în care
dorim să extragem doar anumite coloane, vom trece și numele acestora în zona în care dorim să fie
extras rezultatul (ca în figura de mai jos).

Completăm fereastra Advanced Filter ca mai jos:

• List range: tabelul sursă (atenție să selectați și antetul)
• Criteria range: din foaia “extragere” celulele componente ale tabelului de criterii
• Copy to: din foaia “extragere”, celulele în care am scris denumirile coloanelor din sursa pentru care dorim să se extragă informația. Dacă alegem o celulă blank, atunci se vor returna toate coloanele tabelului sursă. În ambele situații, rândurile vor fi doar acelea care îndeplinesc condiția de filtrare!

După care apăsăm OK și obținem rezultatul:

În mod similar puteți extrage date și dintr-un alt fișier cu condiția să-l deschideți în prealabil.
Pentru a realiza o filtrare avansată avem nevoie de criterii de filtrare construite în așa fel încât să
obținem exact informația pe care o dorim dintr-o bază de date.

Să considerăm spre exemplu tabelul de mai jos:

Tipuri de criterii

Criterii de filtrare pe una sau mai multe coloane

Ne dorim să extragem, spre exemplu, toți angajații din Departamentul A.

Criteriul pentru o asemenea problemă este:

Și se interpretează astfel : [Dept.]=”A”.

Dacă dorim afișarea angajaților din departamentele A și D vom scrie:

Și interpretăm [Dept.]=”A” OR [Dept.]=”D”, cu alte cuvinte, dacă scriem condițiile pe două
rânduri, între ele avem operatorul logic OR.

Dacă sunt scrise pe linie, între ele se subînțelege operatorul logic AND. Cu alte cuvinte, dacă
dorim construirea unui criteriu cu OR scriem condițiile pe coloană și dacă dorim compunerea cu
AND le scriem pe rând.

De exemplu:

Care se interpretează astfel: ( [Dept.]=”A” AND [Age]>40 ) OR ([Dept.]=”D” AND [Age]>40)
și va avea ca rezultat extragerea angajaților din departamentul A cu vârste peste 40 de ani și a celor
din departamentul D cu vârste sub 40 de ani, rezultat pe care nu l-am putea obține prin filtrare
automată decât repetând mai multe operații de filtrare și copiere a rezultatelor.

Dacă avem un criteriu de genul:

Obținem toți angajații din departamentul A, indiferent de vârstă și toți ceilalți cu vârste mai mici
de 40 de ani. Semnificația unei celule goale în criteriu este: “Select all”.

Dacă dorim să se afișeze înregistrările care, pe o anumită coloană, să aibă valorile cuprinse
într-un interval (în cazul nostru, să zicem, angajații cu vârste între 30 și 40 de ani), scriem criteriul
astfel:

Criterii de filtrare cu formule logice

Se întâmplă însă, să avem o problemă de selecție mai complexă și să dorim, spre exemplu,
afișarea angajaților care au vârsta mai mare decât media de vârstă a angajaților din departamentul A.

Cum procedam în acest caz? Introducerea unei coloane cu formule în tabelul sursă ar fi o soluție
dar, rețineți că orice formulă pe care o introduceți, în modul de calcul automat, îngreunează lucrul în
Excel!

Vom crea un criteriu de filtrare cu formule. Un criteriu cu formule este format dintr-o celulă
blank (în locul celulei cu denumirea coloanei) și celula cu formula logică ce va reprezenta condiția
noastră de filtrare. Mare atenție la utilizarea adreselor relative și absolute!

Funcțiile utilizate în formula logică din criteriu fac subiectul unei alte lecții.

În celula în care ați aplicat această formulă, va fi afișată valoarea TRUE sau FALSE, în funcție de evaluarea pentru celula E6 (prima celulă din tabelul sursă pe coloana cu vârste). La aplicarea instrumentului de filtrare avansată, aceasta se va actualiza automat pentru întregul tabel sursă!

Copierea numai a celulelor vizibile

În mod implicit, Excel copiază atât celulele vizibile, cât și celulele ascunse. Cu toate acestea, este posibil să copiați numai celulele vizibile, astfel:

• Selectați celulele pe care doriți să le copiați
• Din fila Home – Find & Select – alegeți Go To Special.

• Se deschide fereastra Go To Special în care faceți click pe Visible cells only – OK. Excel selectează celulele vizibile.
• Faceți click pe Copy (sau apăsați CTRL+C)
• Selectați celula din stânga sus a zonei de lipire și faceți click pe Paste (sau apăsați Ctrl+V).

Extragerea valorilor distincte de pe o coloană

Pentru exemplificarea acestei teme este folosit tabelul Vânzări prezentat la începutul acestui modul.

Dacă doriți să extrageți o listă unică a lunilor într-o altă celulă din aceeași foaie de lucru, procedați astfel:

1. Selectați o celulă din tabel și din fila Data – grupul Sort&Filter – faceți click pe butonul Advanced.
2. În fereastra Advanced Filter selectați butonul Copy to another location, în campul List Range selectați coloana Luna (inclusiv antetul de coloană), în campul Copy to: selectați celula G1, bifați opțiunea Unique records only și apăsați butonul OK.

Rezultatul este acesta:

Pentru a elimina valorile dublate, Excel pune la dispoziție instrumentul Remove Duplicates din fila Data – grupul Data Tools. Ca prim pas, trebuie să selectați zona de celule care are valori dublate pe care doriți să le eliminați și apoi să apelați Remove Duplicates.

Atunci când utilizați această caracteristică, datele dublate vor fi șterse definitiv.

Observație: Înainte de a șterge dublurile, este indicat să realizați o copie a datelor inițiale pentru a vă asigura că nu pierdeți din greșeală nicio informație.