Cel.ro

Filtrarea datelor (Filter) în Excel 2003

by gmp
Published on: 23 August 2011
Comments: No Comments

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:

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:

Caracter Substituie Exemple
* (asterisk) orice serie de caractere t* – toate şirurile de caractere care încep cu “t”

*t – toate şirurile de caractere care se termină cu “t”

t*t – toate şirurile de caractere care încep şi se termină cu “t”

? (question mark) un singur caracter t? – toate şirurile de 2 caractere care încep cu “t”

?t – toate şirurile de 2 caractere care se termină cu “t”

t?t – toate şirurile de 3 caractere care încep şi se termină cu “t”

~ (tilde)
urmat de *, ? sau ~
caracterele *, ?, ~ da~? – înlocuieşte şirul de caractere “da?”

da~*nu –  înlocuieşte şirul de caractere “da*nu”

Pentru a anula filtrul se debifează AutoFilter.

Exemplu:

Avem următoarele date:

Departament Vânzări Angajaţi
Dep1 1500 50
Dep2 1200 80
Dep3 1800 20
Dep4 1150 40
Dep5 1420 35

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

AutoFilter

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

AutoFilter - o înregistrare

Rezultat:

Departament Vânzări Angajaţi
Dep1 1500 50
AutoFilter Custom

AutoFilter Custom

Rezultat:

Departament Vânzări Angajaţi
Dep1 1500 50
Dep5 1420 35

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:

Departament Vânzări Angajaţi
Dep3 1800 20

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.

Advanced Filter

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:

Câmp
Valoare / Expresie

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)

Câmp
Valoare1 / Expresie1
Valoare2 / Expresie2

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)

Câmp1 Câmp2 Câmp3
Valoare1 / Expresie1
Valoare2 / Expresie2
Valoare3 / Expresie3

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)

Câmp Câmp
Valoare1 / Expresie1 Valoare2 / Expresie2

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).

Câmp1 Câmp2
Valoare1 / Expresie1 Valoare2 / Expresie2

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)

Câmp Câmp
Valoare11 / Expresie11 Valoare21 / Expresie21
Valoare12 / Expresie12

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.

Câmp1 Câmp2
Valoare11 / Expresie11 Valoare21 / Expresie21
Valoare12 / Expresie12 Valoare22 / Expresie22

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

Câmp
=C5>AVERAGE($C$5:$C$10)

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)


No Comments - Leave a comment

Leave a comment



Utilizator: , data curenta: 19 March 2024