Cum se filtrează datele în Excel
Recent am scris un articol despre modul de utilizare a funcțiilor sumare în Excel pentru a rezuma cu ușurință cantități mari de date, dar articolul a luat în considerare toate datele din foaia de lucru. Ce se întâmplă dacă doriți doar să vă uitați la un subset de date și să rezumați subsetul de date?
În Excel, puteți crea filtre pe coloane care vor ascunde rânduri care nu se potrivesc cu filtrul. În plus, puteți utiliza și funcții speciale în Excel pentru a rezuma datele utilizând doar datele filtrate.
În acest articol, vă voi parcurge pașii pentru crearea de filtre în Excel și, de asemenea, utilizând funcții încorporate pentru a rezuma acele date filtrate.
Creați filtre simple în Excel
În Excel, puteți crea filtre simple și filtre complexe. Să începem cu filtre simple. Când lucrați cu filtre, trebuie să aveți întotdeauna un rând în partea de sus utilizat pentru etichete. Nu este o cerință de a avea acest rând, dar face mai ușoară lucrul cu filtrele.
Mai sus, am câteva date false și vreau să creez un filtru pe Oraș coloană. În Excel, acest lucru este foarte ușor de făcut. Mergeți mai departe și faceți clic pe Date în panglică și apoi faceți clic pe Filtru buton. Nu trebuie să selectați datele de pe foaie sau să faceți clic în primul rând fie.
Când faceți clic pe Filtru, fiecare coloană din primul rând va avea în mod automat un mic buton drop-down adăugat la dreapta.
Acum, mergeți mai departe și faceți clic pe săgeata în jos în coloana Oraș. Veți vedea câteva opțiuni diferite, pe care le voi explica mai jos.
În partea de sus, puteți sorta rapid toate rândurile după valorile din coloana City. Rețineți că atunci când sortați datele, aceasta va muta întregul rând, nu doar valorile din coloana Oraș. Acest lucru vă va asigura că datele dvs. rămân intacte așa cum a fost înainte.
De asemenea, este o idee bună să adăugați o coloană în partea din față numită ID și să o numerotați de la unul la oricât de multe rânduri aveți în foaia dvs. de lucru. În acest fel, puteți oricând să sortați după coloana ID și să obțineți datele înapoi în aceeași ordine în care a fost inițial, dacă este important pentru dvs..
După cum puteți vedea, toate datele din foaia de calcul sunt acum sortate pe baza valorilor din coloana Oraș. Până în prezent, nu sunt ascunse rânduri. Acum, să aruncăm o privire la casetele de selectare din partea de jos a ferestrei de filtrare. În exemplul meu, am doar trei valori unice în coloana Oraș, iar cele trei se afișează în listă.
Am mers înainte și am deselectat două orașe și am lăsat unul verificat. Acum am doar 8 rânduri de date care arată și restul sunt ascunse. Puteți afla cu ușurință că vă uitați la date filtrate dacă verificați numerele de rând aflate în partea stângă. În funcție de numărul de rânduri ascunse, veți vedea câteva linii orizontale suplimentare, iar culoarea numerelor va fi albastră.
Acum, să spunem că vreau să filtrez pe o a doua coloană pentru a reduce în continuare numărul de rezultate. În coloana C, am numărul total de membri în fiecare familie și doresc să văd rezultatele numai pentru familiile cu mai mult de doi membri.
Mergeți mai departe și faceți clic pe săgeata drop-down din coloana C și veți vedea aceleași căsuțe de bifare pentru fiecare valoare unică din coloană. Cu toate acestea, în acest caz, dorim să faceți clic pe Numărul de filtre apoi faceți clic pe Mai mare ca. După cum puteți vedea, există și o grămadă de alte opțiuni.
Va apărea un nou dialog și aici puteți introduce valoarea pentru filtru. De asemenea, puteți adăuga mai mult de un criteriu cu o funcție AND sau OR. Ați putea spune că doriți ca rânduri în cazul în care valoarea este mai mare de 2 și nu egală cu 5, de exemplu.
Acum sunt la doar 5 rânduri de date: familii numai din New Orleans și cu 3 sau mai mulți membri. Destul de usor? Rețineți că puteți șterge cu ușurință un filtru pe o coloană făcând clic pe meniul derulant și apoi dând clic pe Ștergeți filtrul de la "Nume coloană" legătură.
Deci, este vorba despre filtre simple în Excel. Ele sunt foarte usor de folosit si rezultatele sunt destul de drepte. Acum, să aruncăm o privire asupra filtrelor complexe care utilizează Avansat filtre.
Creați filtre avansate în Excel
Dacă doriți să creați filtre mai avansate, trebuie să utilizați Avansat dialog de filtrare. De exemplu, să spun că am vrut să văd toate familiile care locuiesc în New Orleans cu mai mult de 2 membri în familia lor SAU toate familiile din Clarksville cu mai mult de 3 membri în familia lor ȘI numai cei cu a .EDU care încheie adresa de e-mail. Acum nu poți face asta cu un filtru simplu.
Pentru a face acest lucru, trebuie să configurați foaia de lucru Excel puțin diferit. Mergeți mai departe și introduceți câteva rânduri deasupra setului de date și copiați etichetele de titlu exact în primul rând, după cum se arată mai jos.
Acum, aici funcționează filtrele avansate. Trebuie să introduceți mai întâi criteriile dvs. în coloanele din partea de sus și apoi să faceți clic pe Avansat butonul sub Sortați și filtrați pe Date fila.
Deci, ce anume putem introduce în acele celule? OK, să începem cu exemplul nostru. Vrem doar să vedem date din New Orleans sau Clarksville, așa că să le introducem în celulele E2 și E3.
Când introduceți valori pe diferite rânduri, înseamnă OR. Acum vrem familii din New Orleans cu mai mult de doi membri și familii Clarksville cu mai mult de 3 membri. Pentru a face acest lucru, tastați > 2 în C2 și > 3 în C3.
Întrucât> 2 și New Orleans sunt pe același rând, acesta va fi un operator AND. Același lucru este valabil și pentru rândul 3 de mai sus. În cele din urmă, dorim numai familiile cu adresa de e-mail ending .EDU. Pentru a face acest lucru, introduceți doar * .edu în ambele D2 și D3. Simbolul * înseamnă orice număr de caractere.
Odată ce faceți acest lucru, faceți clic oriunde în setul de date și apoi faceți clic pe Avansat buton. Rangul de listăcâmpul e va determina automat setul de date de când ați făcut clic pe el înainte de a da clic pe butonul Avansat. Acum faceți clic pe butonul mic din partea dreaptă a butonului Gama de criterii buton.
Selectați totul de la A1 la E3 și apoi faceți din nou clic pe același buton pentru a reveni la dialogul Filtru avansat. Faceți clic pe OK și datele dvs. ar trebui filtrate acum!
După cum puteți vedea, acum am doar 3 rezultate care corespund tuturor acestor criterii. Rețineți că etichetele pentru intervalul de criterii trebuie să se potrivească exact cu etichetele pentru setul de date pentru ca aceasta să funcționeze.
În mod evident, puteți crea interogări mult mai complicate utilizând această metodă, deci jucați cu ea pentru a obține rezultatele dorite. În cele din urmă, hai să vorbim despre aplicarea funcțiilor de sumare la datele filtrate.
Rezumând datele filtrate
Acum, să spunem că vreau să însumez numărul membrilor familiei pe datele mele filtrate, cum aș face asta? Ei bine, haideți să eliminăm filtrul făcând clic pe clar butonul din panglică. Nu vă faceți griji, este foarte ușor să aplicați din nou filtrul avansat, făcând clic pur și simplu pe butonul Avansat și făcând clic din nou pe OK.
În partea de jos a setului nostru de date, să adăugăm o celulă numită Total și apoi adăugați o funcție sumă pentru a însuma totalul membrilor familiei. În exemplul meu, am scris doar = SUM (C7: C31).
Deci, dacă mă uit la toate familiile, am 78 de membri în total. Acum să mergem mai departe și să aplicăm din nou filtrul nostru avansat și să vedem ce se întâmplă.
Hopa! În loc de a afișa numărul corect, 11, văd încă totul este de 78! De ce este asta? Funcția SUM nu ignoră rândurile ascunse, așa că face tot calculul utilizând toate rândurile. Din fericire, există câteva funcții pe care le puteți utiliza pentru a ignora rândurile ascunse.
Primul este SUBTOTAL. Înainte de a utiliza oricare dintre aceste funcții speciale, veți dori să ștergeți filtrul și apoi tastați funcția.
Odată ce filtrul este șters, continuați și tastați = SUBTOTAL ( și ar trebui să vedeți o căsuță dropdown care apare cu o grămadă de opțiuni. Cu această funcție, alegeți mai întâi tipul de funcție de sumare pe care doriți să o utilizați utilizând un număr.
În exemplul nostru, vreau să-l folosesc SUMĂ, așa că aș introduce numărul 9 sau doar faceți clic pe acesta din meniul derulant. Apoi tastați o virgulă și selectați intervalul de celule.
Când apăsați pe Enter, ar trebui să vedeți că valoarea 78 este identică cu cea precedentă. Cu toate acestea, dacă aplicați din nou filtrul, vom vedea 11!
Excelent! Asta e exact ce vrem. Acum puteți ajusta filtrele și valoarea va reflecta întotdeauna numai rândurile care se afișează în prezent.
A doua funcție care funcționează exact la fel ca și funcția SUBTOTAL AGREGAT. Singura diferență este că există un alt parametru în funcția AGGREGATE în care trebuie să specificați că doriți să ignorați rânduri ascunse.
Primul parametru este funcția de sumare pe care doriți să o utilizați și la fel ca la SUBTOTAL, 9 reprezintă funcția SUM. A doua opțiune este în cazul în care trebuie să tastați în 5 pentru a ignora rândurile ascunse. Ultimul parametru este același și este domeniul de celule.
De asemenea, puteți citi articolul meu cu privire la funcțiile de rezumat pentru a afla cum să utilizați în detaliu funcția AGGREGATE și alte funcții precum MODE, MEDIAN, AVERAGE etc..
Sperăm că acest articol vă oferă un bun punct de pornire pentru crearea și utilizarea de filtre în Excel. Dacă aveți întrebări, nu ezitați să postați un comentariu. se bucura!
