Cum filtram avansat in Excel, explicatii complete cu 11 exemple.

Acest articol explica instrumentul de filtrare avansata din Excel, respectiv cum sa il folositi pentru a gasi liniile care indeplinesc unul sau mai multe criterii complexe.

Probabil stiti ca instrumentul clasic/standard de filtrare (AutoFilter) ofera o varietate mare de optiuni pentru diferitele tipuri de scenarii de interogare a tabelelor Excel.

Multe, dar nu toate! Cand un filtru obisnuit (AutoFilter) nu poate executa ceea ce doriti, folositi instrumentul Advanced Filter pentru a trece peste limitarile native in materie de cautare Excel.

Filtrul avansat Excel este foarte util cand vine vorba de gasirea datelor care indeplinesc doua sau mai multe criterii complexe, cum ar fi (fara a se limita la acestea): extragerea de potriviri si/sau diferente dintre doua coloane, filtrarea randurilor care se potrivesc cu elemente dintr-o alta lista, gasirea de potriviri exacte, inclusiv caractere majuscule si minuscule, etc.

Filtru avansat este disponibil in toate versiunile de Excel (incepand cu versiunea 2003 sau mai recenta).

1. In ce fel este diferit Advanced Filter fata de AutoFilter?

Comparativ cu instrumentul de baza AutoFilter, Advanced Filter functioneaza diferit din multe puncte de vedere.

Excel AutoFilter este o capacitate incorporata care se aplica cu un singur clic de buton. Apasati butonul Filtru din tab-ul DATA si filtrul dvs. Excel este gata de pornire.

Advanced Filter nu poate fi aplicat imediat, deoarece necesita o configurare manuala a criteriilor de cautare.

AutoFilter permite filtrarea datelor cu maximum 2 criterii simultane, iar aceste conditii sunt specificate direct in caseta de dialog Custom Filter.

Folosind Advanced Filter puteti gasi randuri care indeplinesc oricate criterii logice, toate aceste conditii putand fi introduse direct in foaia de lucru.

Mai jos veti gasi indrumari detaliate despre utilizarea filtrului avansat in Excel, precum si cateva exemple utile ce se aplica pentru toate tipurile de date, de la cele text pana la datele calendaristice sau numerice.

2. Cum se creeaza un filtru avansat in Excel

Folosirea Excel Advanced Filter nu este la fel de usoara ca aplicarea AutoFilter, dar cu siguranta merita efortul. Pentru a crea un filtru avansat pentru foaia dvs., efectuati urmatorii 3 pasi:

Pasul 1. Organizati datele sursa

Pentru rezultate bune, aranjati-va setul de date pe care urmeaza sa-l filtrati urmand aceste 2 reguli simple:

  • Adaugati un rand de antet in care fiecare coloana are un titlu unic – titlurile duplicate vor cauza confuzie la Advanced Filter.

  • Asigurati-va ca nu exista randuri necompletate in setul de date.

De exemplu, iata cum arata tabelul nostru esantion pe care urmeaza sa-l filtram in continuare:

Pasul 2. Stabiliti criteriile logice de cautare

Introduceti conditiile logice de cautare intr-o zona separata din foaia de lucru Excel.

Teoretic, intervalul de criterii poate sta oriunde in foaie (chiar si in alt fisier/carte).

In practica, este mai convenabil sa creati tabelul cu criteriile in partea de sus a tabelului pe care urmeaza sa-l filtrati, apoi sa-l separati de setul de date cu unul sau mai multe randuri goale.

Observatii pentru tabelul cu criterii avansate:

Tabelul de criterii trebuie sa aiba aceleasi etichete de coloana ca tabelul pe care doriti sa il filtrati.

Criteriile enumerate pe acelasi rand functioneaza cu AND logic. Criteriile introduse pe diferite randuri functioneaza cu OR logic.

De exemplu, pentru a filtra inregistrarile din tabelul de mai sus, astfel incat pe ecran sa ramana doar tranzactiile companiei Tri Fly care au Costuri mai mari de 50, configurati un tabel separat cu urmatoarele criterii:

Pasul 3. Aplicati filtrul avansat Excel

Dupa ce ati creat tabelul cu criteriile (in poza de mai sus este cel cu doua coloane), puteti sa le aplicati tabelului mare cu date, astfel:

Selectati orice celula din tabelul cu date (nu mai mult de o celula 😊).

In Excel 365, 2019, 2016, Excel 2013, Excel 2010 si Excel 2007, accesati tab-ul Data> grupul Sort and Filter si faceti clic pe Advanced.

In Excel 2003, faceti clic pe meniul Data> Filter, apoi faceti clic pe Advanced Filter.

Va aparea caseta de dialog Advanced Filter pe care trebuie sa o configurati asa cum este explicat mai jos.

Pasul 4. Configurati parametri Advanced Filter

In fereastra de dialog Advanced Filter, specificati urmatoarele (vedeti poza):

Zona action: Alegeti daca filtrati lista in loc sau copiati rezultatele intr-o alta locatie.

Daca selectati „Filter the list in place„, tabelul cu date va fi filtrat direct corespunzator cu criteriile stabilite

Daca alegeti „Copy to another location” veti lasa tabelul cu date neatins si veti copia doar rezultatul filtrarii intr-o alta locatie (stabilita prin casuta Copy to…)

List range este tabelul pe care doriti sa-l filtrati. Aveti grija sa fie selectate si etichetele de coloana!

Criteria Range: Este tabelul in care ati introdus criteriile de cautare.

Pentru primul exemplu, lasati primul buton radio apasat (Filter the list in place), la List range indicati tabelul de filtrat si la Criteria Range tabelul cu criteriile logice, apoi apasati OK (vedeti animatia):

Grozav, nu? 😊 … totusi acelasi rezultat poate fi obtinut si cu ajutorul filtrului clasic.

Nu va grabiti sa parasiti aceasta pagina, deoarece am aratat un exemplu banal astfel incat sa aveti ideea de baza despre cum functioneaza Excel Advanced Filter.

Mai departe in acest articol, veti gasi cateva exemple care pot fi realizate doar cu filtrul avansat.

Pentru a face lucrurile mai usor de urmarit, sa vorbim mai intai despre criteriile multiple acceptate de Filtrul avansat.

Dupa ce veti afla tot ce este de aflat despre acest instrument, optiunile dvs. de filtrare in Excel vor fi aproape nelimitate!

3. Operatori de comparatie pentru numere / date calendaristice

In criteriile Advanced Filter, puteti compara diferite valori numerice sau pe cele de tip data calendaristica, folosind urmatorii operatori de comparatie:

Operator de comparatie

Ce inseamna

Exemplu

=

Egal cu

=”Bucuresti”

sau

„Bucuresti”

>

Mai mare decat

>50

<

Mai mic decat

<100

>=

Mai mare sau egal decat

>=75

<=

Mai mic sau egal decat

<=0

<>

Diferit

<>”Cluj”

Utilizarea operatorilor de comparatie cu numere este evidenta. In exemplul de mai sus, am folosit deja criteriile numerice <50 pentru a filtra inregistrarile ce au COST pana in 50.

Inca un exemplu.

Presupunand ca doriti sa afisati inregistrarile pentru Compania Gel Boomerangs care a vandut Produsul Carlota la un Net Sales mai mare de 300, trebuie sa faceti urmatorul panou de criterii:

Pozitionati-va pe o celula din tabelul cu date (orice celula din range-ul A5:E18), apasati Advanced (din ribbon-ul Data), apoi completati fereastra astfel:

List Range: $A$5:$E$18

Criteria Range: $B$1:$D$2

Rezultatul ar trebui sa fie cel din tabelul din poza de mai jos:

4. Criterii pentru texte

La cautarile bazate pe texte am multe sa va arat.

Schimb putin tabelul pentru a explica mici nuante ale cautarilor bazate pe texte:

Cum cautam toate Companiile ce incep cu „Colorado”?

In panoul cu criterii daca scriu Colorado, o sa-mi gaseasca toate Companiile care incep cu Colorado:

Iata si rezultatul aplicarii filtrului avansat:

Dar cum gasesc doar companiile care se numesc fix Colorado?

Simplu, scriu in celula de criterii:

=”=Colorado”

Dupa aplicarea filtrului avansat ajungem la urmatorul rezultat:

Dar cum gasim pe Colorado tinand cont de tipul literelor? Vreau sa va spun ca mai toate instrumentele din Excel NU sunt CASE-SENSITIVE, adica nu tin cont de tipul literelor (majuscule sau minuscule). Aceste este motivul pentru care chiar si filtrul avansat nu face diferenta intre: Colorado si COLORADO

Explic spre finalul acestui articol cum se poate face cu filtrul avansat o cautare CASE-SENSITIVE

Criteriul

Descriere

=”=text”

Cauta fix „text” (dar chiar si asa, NU tine cont de tipul literelor)

text

Gaseste celulele care INCEP cu „text”

<>text

Gaseste celulele diferite de „text

>text

Filtreaza celulele ale caror valori se afla alfabetic DUPA „text”.

<text

Filtreaza celulele ale caror valori se afla alfabetic INAINTE de „text”.

5. Caractere wildcard (metacaractere) pentru Filtrele Avansate

Pentru a filtra inregistrarile de text cu o potrivire partiala, puteti utiliza urmatoarele caractere cu caracter de inlocuire in criteriile Advanced Filter:

Semn de intrebare (?) Pentru a se potrivi cu un singur caracter

Semnul Asterisk (*) pentru a se potrivi cu orice secventa de caractere.

Semnul Tilda (~) urmat de *,?, Sau ~ pentru a filtra celule care contin un semn de intrebare, asterisc sau tilda REALE.

Urmariti urmatorul tabel pentru a vedea operatorii de cautare aproximativa suportati de filtrul avansat Excel:

Criterul

Descriere

Exemplu

*text*

Filtreaza celulele ce CONTIN text

*colorado* gaseste toate celulele ce contin (oriunde) textul colorado

??text

Filtreaza celulele ce contin text precedat de oricare alte doua caractere

??colorado ne gaseste orice celula care are cuvantul colorado incepand de la al treilea caracter. De ex.: 10colorado sau XYcolorado

text*text

Filtreaza celulele ce incep cu cuvantul text SI care mai contin inca o data a doua oara acel text

Mere*mere ne va gasi:

Mere versi, mere ionatane

=”=text*text”

Filtreaza celulele ce incep cu cuvantul text SI in acelasi timp se termina cu text

=”=mere*mere” ne va gasi „mere, dulci mere” insa nu ne va gasi „mere, mere dulci”

=”=text1?text2″

Filtreaza celulele ce incep cu cuvantul text SI in acelasi timp se termina cu text, iar intre ele se afla UN singur caracter

=”=mere?prune” ne va gasi „mere/prune” sau „mere-prune” adica orice incepe cu mere, se termina cu prune iar intre ele se afla un singur caracter

text~**

Na gaseste celulele ce incep cu un text urmat de semnul *

mere~** ne gaseste mere*ionatane si nu ne gaseste mere-ionatane

=”=????”

Gaseste celulele ce contin fix 4 caractere

=”=????” ne gaseste mere, pere si orice alta celula ce contine un text format din FIX 4 caractere

Folosim urmatorul tabel pentru a gasi toate Companiile ce contin textul „boom” si care au vandut Produse la care penultima litera este „e”:

Rezultatul, dupa aplicarea filtului avansat, este cel de mai jos:

Ma opresc momentan cu facilitatile filtrelor avansate, insa va rog sa retineti ca ne aflam abia la jumatatea drumului in materie de filtrare …

Pe curand,
(va continua)

RESURSE ARTICOL

Puteți descărca fișierul cu exemplele din acest articol.

INVĂȚAȚI EXCEL CU DR.EXCEL!

Cursuri Excel/VBA/BI pentru toate categoriile de utilizatori

de la incepători la experți

AVEȚI ÎNTREBĂRI TEHNICE EXCEL, VBA, SQL, BI?

Alăturați-vă celor 12.000 de membri ai Comunității Dr.Excel!