itasistenta blog

Meniu: Data -> Filter

Prin operaţia de filtrare a datelor, Excel afişează doar acele înregistrări (rânduri) care corespund unuia sau mai multor criterii impuse de utilizator. Filtrarea nu sortează datele (nu schimbă ordinea lor), ci doar ascunde datele pe care nu dorim să le vizualizăm.

Pentru a filtra o zonă compactă de date se plasează cursorul în una din celulele intervalului evaluat (ca la Sortare) şi se aplică una din cele două variante de filtrare a datelor:

• automată (AutoFilter);
• avansată (Advanced Filter).

Pentru a afişa din nou toate înregistrările se foloseşte meniul Data -> Filter -> Show All.

FILTRARE AUTOMATĂ

Prin filtrare automată (AutoFilter), în antetul coloanelor apar săgeţi pentru liste derulante din care se pot alege pentru afişare:

• o anumită înregistrare (linie) care corespunde selecţiei;
• mai multe înregistrări care îndeplinesc o condiţie specificată de utilizator:
  • (Top 10…) – primele/ultimele n înregistrări identificate ca număr sau ca procent;
  • (Custom…) – permite filtrarea liniilor care îndeplinesc una sau două condiţii legate prin operatorii logici AND şi/sau OR. În fiecare condiţie se specifică:
    • un operator (equals, does not equal, is greater than, is greater than or equal to, is less than, is less than or equal to, begins with, does not begin with, ends with, does not end with, contains, does not contain);
    • o valoare cu care se compară conţinutul celulei.

Prin alegerea unei filtrări particularizate pe coloane diferite se pot afişa înregistrările care îndeplinesc condiţii multiple, cu mai multe criterii (coloane).

În comparaţii de text se pot utiliza următoarele caractere de substituire:

Pentru a anula filtrul se debifează AutoFilter.

Exemplu:

Avem următoarele date:

Aplicarea unui filtru automat (AutoFilter) va duce la apariţia săgeţilor derulante în celulele din antetul tabelului.

Filtrare automată (AutoFilter)

Din aceste săgeţi putem afişa o singură înregistrare – de exemplu, departamentul Dep1 – sau, dacă se alege Custom…, toate departamentele care au vânzări între 1300 şi 1500 inclusiv.

AutoFilter - o înregistrare

Rezultat:

AutoFilter Custom

Rezultat:

Pentru a răspunde la întrebarea “Ce departament are vânzări de peste 1400 şi cei mai puţini angajaţi?” vom introduce criterii de filtrare atât pe coloana Vânzări, cât şi pe coloana Angajaţi astfel:

• Vânzări: Custom -> is greater than 1400;
• Angajaţi: Top10 -> Bottom 1 Items.

Se obţine:

FILTRARE AVANSATĂ

În cazul Filtrării avansate (Advanced Filter) nu sunt adăugate săgeţi pentru listele verticale cu opţiuni (ca la filtrarea automată), ci criteriile de filtrare sunt specificate chiar în celulele registrului de lucru.

Filtrare avansată (Advanced Filter)

Din fereastra Advanced Filter se pot alege:

• unde se face filtrarea datelor:
  • în acelaşi loc (Filter the list, in-place) – vor fi afişate în tabelul iniţial doar înregistrările care îndeplinesc criteriile de filtrare);
  • într-o altă celulă a aceleiaşi foi de lucru (Copy to another location) – datele filtrate vor fi copiate în această nouă locaţie, specificată de utilizator în caseta Copy to;
• intervalul de celule filtrat (List range), detectat automat de Excel dacă se dă clic pe una din celulele intervalului evaluat înainte de a accesa meniul Filter;
• celula (celulele) în care se află criteriul (criteriile) de filtrare (Criteria range) – este preferabil să fie introduse deasupra tabelului sau în altă foaie a registrului de lucru, deoarece filtrarea ar putea ascunde celulele care conţin criteriile de filtrare;
• afişarea înregistrărilor unice (Unique records only) – în rezultatele filtrate nu se afişează înregistrările identice. Prin această opţiune se pot elimina înregistrările care se repetă într-o listă de date.

În criteriile care folosesc şiruri de caractere pentru comparaţii se pot folosi aceleaşi caractere de substituire * (asterisc),  ? (semnul întrebării) sau ~ (tilda) prezentate în capitolul Filtrare automată.

Criteriile simple de filtrare se introduc după modelul:

unde Câmp este numele unei coloane din tabelul de filtrat, iar Valoare / Expesie este criteriul de filtrare.

Pentru criteriile multiple de filtrare există următoarele moduri de introducere:

MAI MULTE CONDIŢII ÎN ACEEAŞI COLOANĂ (SAU LOGIC)

Afişează înregistrările care conţin:

Valoare1 în coloana Câmp
SAU
Valoare2 în coloana Câmp

(sau îndeplinesc condiţiile Expresie1 pentru Câmp SAU Expresie2 pentru Câmp).

O CONDIŢIE ÎNTR-O COLOANĂ SAU ALTA (SAU LOGIC)

Afişează înregistrările care conţin:

Valoare1 în coloana Câmp1
SAU
Valoare2 în coloana Câmp2
SAU
Valoare3 în coloana Câmp3

(sau îndeplinesc condiţiile Expresie1 pentru Câmp1 SAU Expresie2 pentru Câmp2 SAU Expresie3 pentru Câmp3).

O CONDIŢIE ÎN DOUĂ SAU MAI MULTE COLOANE (ŞI LOGIC)

Afişează înregistrările care conţin:

Valoare1 în coloana Câmp
ŞI
Valoare2 în coloana Câmp

(sau îndeplinesc condiţiile Expresie1 pentru Câmp ŞI Expresie2 pentru Câmp).

Afişează înregistrările care conţin:

Valoare1 în coloana Câmp1
ŞI
Valoare2 în coloana Câmp2

(sau îndeplinesc condiţiile Expresie1 pentru Câmp1 ŞI Expresie2 pentru Câmp2).

UNUL DIN DOUĂ SETURI DE CONDIŢII ÎN COLOANE DIFERITE (SAU LOGIC)

Afişează înregistrările care conţin:

Valoare11 în coloana Câmp ŞI Valoare21 în coloana Câmp
SAU
Valoare12 în coloana Câmp

sau îndeplinesc condiţiile (Expresie11 pentru Câmp ŞI Expresie21 pentru Câmp) SAU Expresie12 pentru Câmp.

Afişează înregistrările care conţin:

Valoare11 în coloana Câmp1 ŞI Valoare21 în coloana Câmp2
SAU
Valoare12 în coloana Câmp1 ŞI Valoare22 în coloana Câmp2

sau îndeplinesc condiţiile (Expresie11 pentru Câmp1 ŞI Expresie21 pentru Câmp2) SAU (Expresie12 pentru Câmp1 ŞI Expresie22 pentru Câmp2).

O CONDIŢIE REZULTAT AL UNEI FORMULE

Afişează înregistrările care conţin pe coloana C o valoare mai mare decât media aritmetică a celulelor C5..C10.

Exemplu:
Avem următoarele date introduse în celulele A10…C15:

Pentru fiecare din cazurile de mai jos, în caseta List range (intervalul de celule pe care dorim să-l filtrăm) vom introduce valoarea $A$10:$C$15.

Criteriile de filtrare le vom introduce la începutul foii de lucru, în zona de celule A1:B3, după caz (pot fi introduse oriunde în foaia de lucru curentă, în altă foaie sau în alt registru de lucru).

1. Filtrare: toate vânzările mai mici de 1200 SAU mai mari de 1500

2. Filtrare: departamentele cu vânzări între 1200 ŞI 1500

3. Filtrare: departamentele cu vânzări sub 1300 SAU mai mult de 50 angajaţi

4. Filtrare: departamentele cu vânzări peste 1300 ŞI mai puţini de 50 angajaţi

5. Filtrare: toate departamentele care au vânzări mai mici decât media

Observaţii:

În acest caz se foloseşte funcţia Excel AVERAGE (media aritmetică) drept criteriu de filtrare. Expresia introdusă în celula A2 este evaluată (TRUE sau FALSE), filtrarea făcându-se apoi în funcţie de datele din coloane. Pentru ca filtrarea să se realizeze este obligatorie respectarea următoarelor indicaţii:

• în celula A1 (sau în orice altă celulă în care ar fi introdus), antetul zonei de criterii (“Media vânzări” în acest exemplu) trebuie să fie diferit de antetul coloanei din zona filtrată (“Vânzări” în acest exemplu);
• în celula A2 formula se va scrie exact ca în exemplu. Ea trebuie să conţină:
  • o referinţă relativă la prima celulă a coloanei evaluate (celula B11 în acest exemplu), care va fi modificată de Excel automat pe verticală pentru fiecare înregistrare şi comparată cu media aritmetică a tuturor înregistrărilor (rezultatul funcţiei AVERAGE);
  • referinţele absolute $B$11 şi $B$15, care definesc zona de date a cărei medie aritmetică se calculează (cu funcţia AVERAGE). Pentru setul de date folosit în exemplu, media aritmetică este 1414, iar această valoare va fi comparată succesiv cu celulele B11…B15. În cazul în care celulele conţin valori mai mici decât media, înregistrările (rândurile) respective vor fi afişate.

Exemplu:

Avem următoarele date:

Pentru fiecare din cazurile de mai jos se alege List range (intervalul de celule pe care dorim să-l filtrăm): $A$10:$C$18.

1.  Filtrare: toate persoanele al căror nume începe cu “Dan”

2. Filtrare: toate persoanele al căror nume se termină cu “a” şi au sub 20 ani

Observaţie: Excel consideră formulă orice expresie care începe cu semnul egal şi o calculează (în cazul celulei A2 va da eroare dacă scriem pur şi simplu =*a). De aceea va trebui să introducem expresia =*a ca şir de caractere prin următoarele metode:

• =”=*a” – celula conţine o formulă datorită primului semn egal, prin care valoarea celulei este egală cu şirul de caractere =*a introdus între ghilimele duble ” “;
• ‘=*a – caracterul ghilimele simple ‘ la începutul celulei defineşte conţinutul acesteia de tip Text şi astfel nu va fi evaluat ca formulă;
• formatarea celulei ca fiind de tip Text (meniul Format -> Cells, tab-ul Number, categoria Text).

Observaţie: Pentru a găsi toate numele care conţin ghilimele duble (“) se poate folosi expresia ‘=*”*.

3. Filtrare: toţi băieţii cu vârste peste 20 ani

4. Filtrare: toate persoanele cu nume formate din 5 litere

5. Filtrare: toate persoanele al căror nume este “Dan” (exact)