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 acest articol 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 ascund 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 numai 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.
Î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 lucrul cu filtrele este ușor de făcut.
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 Filtrare, 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 Oraș. Rețineți că atunci când sortați datele, aceasta va muta întregul rând, nu doar valorile din coloana orașului. 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 dvs. în aceeași ordine în care a fost inițial, dacă acest lucru 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șului și cele trei se afișează în listă.
Am mers înainte și am desecat 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 să afli 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 oa doua coloană pentru a reduce în continuare numărul de rezultate. În coloana C, am numărul total de membri din fiecare familie și doresc să văd rezultatele numai pentru familiile cu mai mult de doi membri.
Mergeți mai departe și daț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ândurile î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 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 de filtre.
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 adresa de e-mail terminată. Acum nu poți face asta cu un filtru simplu.
Pentru a face acest lucru, trebuie să configurați foaia 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 tab.
Deci, ce anume putem introduce în acele celule? OK, deci începeți 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 care au o adresă de e-mail care se termină cu .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 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.
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 dând 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 arăta numărul corect, 11, văd încă totul este de 78! De ce este asta? Funcția SUM nu ignoră rândurile ascunse, deci tot face calculul folosind 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 introduceți = SUBTOTAL ( și ar trebui să vedeți o casetă de tip 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ă folosesc SUMĂ, așa că am tastat numărul 9 sau doar dați clic pe el 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 ceea 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 unde trebuie să specificați că doriți să ignorați rândurile ascunse.
Primul parametru este funcția de sumare pe care doriți să o utilizați și ca în cazul 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 sumare 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. Bucurați-vă!
