Funcția de filtrare Excel - explicată cu exemple + Video

Urmăriți videoclipuri - Exemple de funcții FILTRU Excel

Office 365 aduce câteva funcții minunate - cum ar fi XLOOKUP, SORT și FILTER.

Când vine vorba de filtrarea datelor în Excel, în lumea pre-Office 365, am fost în mare parte dependenți de filtrul încorporat Excel sau la maxim de filtrul avansat sau de formulele complexe SUMPRODUCT. În cazul în care a trebuit să filtrați o parte a unui set de date, acesta a fost de obicei o soluție complexă (ceva ce am acoperit aici).

Dar cu noua funcție FILTER, acum este foarte ușor să filtrați rapid o parte a setului de date pe baza unei condiții.

Și în acest tutorial, vă voi arăta cât de minunat este noua funcție FILTER și câteva lucruri utile pe care le puteți face cu aceasta.

Dar, înainte de a intra în exemple, să învățăm rapid despre sintaxa funcției FILTER.

În cazul în care doriți să obțineți aceste caracteristici noi în Excel, puteți faceți upgrade la Office 365 (alăturați-vă programului privilegiat pentru a obține acces la toate caracteristicile / formule)

Funcția de filtrare Excel - Sintaxă

Mai jos este sintaxa funcției FILTER:

= FILTER (matrice, include, [if_empty])

• matrice - aceasta este gama de celule în care aveți datele și doriți să filtrați unele date din aceasta
• include - aceasta este condiția care spune funcției ce înregistrări trebuie filtrate
• [if_ vid] - acesta este un argument opțional în care puteți specifica ce să returnați în cazul în care funcția FILTER nu găsește rezultate. În mod implicit (când nu este specificat), returnează #CALC! eroare

Acum, să aruncăm o privire la câteva exemple uimitoare de funcții de filtrare și la lucrurile pe care le poate face, care au fost destul de complexe în absența sa.

Faceți clic aici pentru a descărca fișierul Exemplu și urmați

Exemplul 1: Filtrarea datelor pe baza unui singur criteriu (regiune)

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să filtrați toate înregistrările numai pentru SUA.

Mai jos este formula FILTER care va face acest lucru:

= FILTRU ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = „SUA”)

Formula de mai sus folosește setul de date ca matrice și condiția este de $ B $ 2: $ B $ 11 = „SUA”

Această condiție ar face ca funcția FILTER să verifice fiecare celulă din coloana B (una care are regiunea) și numai acele înregistrări care corespund acestui criteriu vor fi filtrate.

De asemenea, în acest exemplu, am datele originale și datele filtrate pe aceeași foaie, dar le puteți avea și în foi separate sau chiar în registre de lucru.

Funcția de filtrare returnează un rezultat care este un tablou dinamic (ceea ce înseamnă că, în loc să returneze o valoare, returnează un tablou care se varsă în alte celule).

Pentru ca acest lucru să funcționeze, trebuie să aveți o zonă în care rezultatul ar fi gol. În oricare dintre celulele din această zonă (E2: G5 în acest exemplu) are deja ceva, funcția vă va da eroarea #SPILL.

De asemenea, deoarece acesta este un tablou dinamic, nu puteți schimba o parte din rezultat. Puteți șterge întregul interval care are rezultatul sau celula E2 (unde a fost introdusă formula). Ambele vor șterge întreaga matrice rezultată. Dar nu puteți schimba nicio celulă individuală (sau șterge).

În formula de mai sus, am codificat cu duritate valoarea regiunii, dar o puteți avea și într-o celulă și apoi faceți referire la acea celulă care are valoarea regiunii.

De exemplu, în exemplul de mai jos, am valoarea regiunii în celula I2 și aceasta este apoi menționată în formula:

= FILTRU ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = I1)

Acest lucru face ca formula să fie și mai utilă și acum puteți schimba pur și simplu valoarea regiunii din celula I2, iar filtrul s-ar schimba automat.

Puteți avea, de asemenea, un meniu derulant în celula I2, unde puteți face pur și simplu selecția și aceasta va actualiza instantaneu datele filtrate.

Exemplul 2: Filtrarea datelor pe baza unui singur criteriu (mai mult de sau mai puțin de)

De asemenea, puteți utiliza operatori comparativi în cadrul funcției de filtrare și puteți extrage toate înregistrările care sunt mai mult sau mai puțin decât o anumită valoare.

De exemplu, să presupunem că aveți setul de date așa cum se arată mai jos și doriți să filtrați toate înregistrările în care valoarea vânzărilor este mai mare de 10000.

Formula de mai jos poate face acest lucru:

= FILTRU ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000))

Argumentul matrice se referă la întregul set de date și condiția, în acest caz, este ($ C $ 2: $ C $ 11> 10000).

Formula verifică fiecare înregistrare pentru valoarea din coloana C. Dacă valoarea este mai mare de 10000, aceasta este filtrată, altfel este ignorată.

În cazul în care doriți să obțineți toate înregistrările sub 10000, puteți utiliza formula de mai jos:

= FILTRU ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11 <10000))

De asemenea, puteți deveni mai creativ cu formula FILTER. De exemplu, dacă doriți să filtrați primele trei înregistrări pe baza valorii vânzărilor, puteți utiliza formula de mai jos:

= FILTRU ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> = LARGE (C2: C11,3)))

Formula de mai sus folosește funcția LARGE pentru a obține a treia cea mai mare valoare din setul de date. Această valoare este apoi utilizată în criteriile funcției FILTER pentru a obține toate înregistrările în care valoarea vânzărilor este mai mare sau egală cu a treia cea mai mare valoare.

Faceți clic aici pentru a descărca fișierul Exemplu și urmați

Exemplul 3: Filtrarea datelor cu criterii multiple (ȘI)

Să presupunem că aveți setul de date de mai jos și doriți să filtrați toate înregistrările pentru SUA, unde valoarea vânzării este mai mare de 10000.

Aceasta este o condiție AND în care trebuie să verificați două lucruri - regiunea are nevoie în SUA, iar vânzările trebuie să fie mai mari de 10000. Dacă este îndeplinită o singură condiție, rezultatele nu ar trebui să fie filtrate.

Mai jos este formula FILTER care va filtra înregistrările cu SUA ca regiune și vânzările de peste 10000:

= FILTRU ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = „SUA”) * ($ C $ 2: $ C $ 11> 10000))

Rețineți că criteriul (denumit argumentul include) este ($ B $ 2: $ B $ 11 = „SUA”) * ($ C $ 2: $ C $ 11> 10000)

Deoarece folosesc două condiții și am nevoie ca ambele să fie adevărate, am folosit operatorul de multiplicare pentru a combina aceste două criterii. Aceasta returnează o serie de 0 și 1, unde un 1 este returnat numai atunci când sunt îndeplinite ambele condiții.

În cazul în care nu există înregistrări care îndeplinesc criteriile, funcția ar returna #CALC! eroare.

Și în cazul în care doriți să returnați ceva semnificativ (în loc de eroare), puteți utiliza o formulă așa cum se arată mai jos:

= FILTRU ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = „SUA”) * ($ C $ 2: $ C $ 11> 10000), „Nimic găsit”)

Aici, am folosit „Not Found” ca al treilea argument, care este utilizat atunci când nu se găsesc înregistrări care să corespundă criteriilor.

Exemplul 4: Filtrarea datelor cu criterii multiple (SAU)

De asemenea, puteți modifica argumentul „include” din funcția FILTER pentru a verifica dacă există criterii SAU (unde oricare dintre condițiile date poate fi adevărată).

De exemplu, să presupunem că aveți setul de date așa cum se arată mai jos și doriți să filtrați înregistrările în care țara este fie SUA, fie Canada.

Mai jos este formula care va face acest lucru:

= FILTRU ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = „SUA”) + ($ B $ 2: $ B $ 11 = „Canada”))

Rețineți că în formula de mai sus, am adăugat pur și simplu cele două condiții folosind operatorul de adăugare. Deoarece fiecare dintre aceste condiții returnează o matrice de ADEVĂRATE și FALSE, pot adăuga pentru a obține o matrice combinată în care este ADEVĂRAT dacă oricare dintre condiții este îndeplinită.

Un alt exemplu ar putea fi atunci când doriți să filtrați toate înregistrările în care fie țara este SUA, fie valoarea de vânzare este mai mare de 10000.

Formula de mai jos va face acest lucru:

= FILTRU ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = „SUA”) + (C2: C11> 10000))

Notă: Când utilizați criteriile ȘI într-o funcție FILTER, utilizați operatorul de multiplicare (*) și când utilizați criteriile SAU, utilizați operatorul de adunare (+).

Exemplul 5: Filtrarea datelor pentru a obține înregistrări peste / sub medie

Puteți utiliza formule în cadrul funcției FILTRU pentru a filtra și extrage înregistrări unde valoarea este peste sau sub medie.

De exemplu, să presupunem că aveți setul de date așa cum se arată mai jos și doriți să filtrați toate înregistrările în care valoarea vânzării este peste medie.

Puteți face acest lucru folosind următoarea formulă:

= FILTRU ($ A $ 2: $ C $ 11, C2: C11> MEDIE (C2: C11))

În mod similar, pentru sub medie, puteți utiliza formula de mai jos:

= FILTRU ($ A $ 2: $ C $ 11, C2: C11<>

Faceți clic aici pentru a descărca fișierul Exemplu și urmați

Exemplul 6: Filtrarea numai a înregistrărilor de numere EVEN (sau înregistrări de număr ODD)

În cazul în care trebuie să filtrați și să extrageți rapid toate înregistrările din rândurile de numere pare sau rânduri de numere impare, puteți face acest lucru cu funcția FILTER.

Pentru a face acest lucru, trebuie să verificați numărul rândului din funcția FILTER și să filtrați numai numerele rândurilor care îndeplinesc criteriile numărului rândului.

Să presupunem că aveți setul de date așa cum se arată mai jos și vreau doar să extrag înregistrări par din acest set de date.

Mai jos este formula care va face acest lucru:

= FILTRU ($ A $ 2: $ C $ 11, MOD (ROW (A2: A11) -1,2) = 0)

Formula de mai sus folosește funcția MOD pentru a verifica numărul rândului fiecărei înregistrări (care este dat de funcția ROW).

Formula MOD (ROW (A2: A11) -1,2) = 0 returnează TRUE atunci când numărul rândului este par și FALSE când este impar. Rețineți că am scăzut 1 din partea ROW (A2: A11), deoarece prima înregistrare se află în al doilea rând, iar aceasta ajustează numărul rândului pentru a considera al doilea rând ca fiind prima înregistrare.

În mod similar, puteți filtra toate înregistrările impare utilizând formula de mai jos:

= FILTRU ($ A $ 2: $ C $ 11, MOD (ROW (A2: A11) -1,2) = 1)

Exemplul 7: Sortați datele filtrate cu formula

Utilizarea funcției FILTER cu alte funcții ne permite să facem mult mai multe lucruri.

De exemplu, dacă filtrați un set de date folosind funcția FILTER, puteți utiliza funcția SORT cu acesta pentru a obține rezultatul care este deja sortat.

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să filtrați toate înregistrările în care valoarea vânzărilor este mai mare de 10000. Puteți utiliza funcția SORT cu funcția pentru a vă asigura că datele rezultate sunt sortate pe baza valorii vânzărilor.

Formula de mai jos va face acest lucru:

= SORT (FILTRU ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000)), 3, -1)

Funcția de mai sus utilizează funcția FILTER pentru a obține datele în care valoarea de vânzare din coloana C este mai mare de 10000. Această matrice returnată de funcția FILTER este apoi utilizată în cadrul funcției SORT pentru a sorta aceste date pe baza valorii vânzărilor.

Al doilea argument din funcția SORT este 3, care este de sortat pe baza celei de-a treia coloane. Și al patrulea argument este -1, care este de a sorta aceste date în ordine descrescătoare.

Faceți clic aici pentru a descărca fișierul Exemplu

Deci, acestea sunt 7 exemple de utilizare a funcției FILTER în Excel.

Sper că ați găsit util acest tutorial!

S-ar putea să vă placă următoarele tutoriale Excel:

1. Cum se filtrează celulele cu formatarea fonturilor în gras în Excel
2. Casetă de căutare dinamică a filtrului Excel
3. Cum se filtrează datele într-un tabel pivot în Excel