Am scris recent un articol despre cum se utilizează funcțiile sumare în Excel pentru a rezuma cu ușurință cantități mari de date, însă 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 ascund rânduri care nu se potrivesc cu filtrul. În plus, puteți utiliza, de asemenea, funcții speciale în Excel pentru a rezuma datele utilizând doar datele filtrate. În acest articol, vă voi îndruma prin pașii de creare a filtrelor în Excel și, de asemenea, cu ajutorul built-in pentru a rezuma aceste 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 lucrul cu filtrele este mai ușor de făcut.
doriți să creați un filtru în coloana Oras. În Excel, acest lucru este foarte ușor de făcut. Mergeți mai departe și faceți clic pe fila datedin panglică, apoi faceți clic pe butonul Filtru. Nu trebuie să selectați datele din foaia respectivă sau să faceți clic în primul rând.
Când faceți clic pe Filter, în 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 Coloana orașului. 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 valorile din coloana orașului. 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 exact așa cum a fost înainte.
De asemenea, este o idee bună să adăugați o coloană pe frontul numit ID și să o numerotați de la una la oricâte rânduri aveți în fisa de lucru. În acest fel, puteți oricând să sortați după coloana ID și să obțineți datele dvs. înapoi î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 City. 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 neînchipuit două orașe și a 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ă filtreze o a 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 faceți clic pe săgeata drop-down din coloana C și veți vedea aceleași căsuțe pentru fiecare valoare unică din coloană. Cu toate acestea, în acest caz, vrem să dăm clic pe Filtrele de numereși apoi să dăm clic pe Mai mare decât. După cum puteți vedea, există și o grămadă de alte opțiuni.
Se va afișa 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. Puteti spune ca doriti rânduri unde valoarea este mai mare de 2 si nu egala cu 5, de exemplu.
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 într-o coloană dând clic pe meniul derulant și făcând clic pe linkul Ștergeți filtrul din numele coloanei.
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 folosind dialogul Advancedfiltre.
Creați filtre avansate în Excel
Dacă doriți să creați filtre mai avansate, trebuie să folosești dialogul de filtrare Advanced. De exemplu, să spunem că am vrut să văd toate familiile din New Orleans cu mai mult de 2 membri în familia lor SAUtoate familiile din Clarksville cu mai mult de 3 membri din familia lor ȘIputernice>numai cele care au o adresă de e-mail terminată .EDU. Acum nu puteți face acest lucru 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 așa cum este prezentat mai jos.
Acum aici este modul în care 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 butonul Advanceddin secțiunea Sortare & amp; Filtrudin fila date.
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.
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 aceasta, tastați & gt; 2în C2 și & gt; 3în C3.
>
Întrucât>2 și New Orleans se află 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, tastați *. Eduîn ambele D2 și D3. Simbolul * înseamnă orice număr de caractere.
După ce faceți acest lucru, faceți clic oriunde în setul de date, apoi faceți clic pe Advanced. Campul Rangă listă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 Criteriu range.
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 setului de date pentru ca aceasta să funcționeze.
Puteți crea în mod evident întrebări mult mai complicate utilizând această metodă, deci jucați cu ea pentru a obține rezultatele dorite. În cele din urmă, să vorbim despre aplicarea funcțiilor de sumare la datele filtrate.
Sumarul datelor filtrate
Acum, să spunem că vreau să rezumă numărul membrilor familiei pe datele mele filtrate, cum aș ai făcut asta? Ei bine, hai să ștergem filtrul făcând clic pe butonul Ștergețidin 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 Advanced și făcând din nou clic pe OK.
în partea de jos a setului nostru de date, să adăugăm o celulă numită Totalși apoi să adăugăm o funcție sumă pentru a rezuma totalul membrilor familiei. În exemplul meu, am introdus doar = SUM (C7: C31).
familii, 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ă.
Oameni! Î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. După ce filtrul este șters, continuați și introduceți = SUBTOTAL (și ar trebui să vedeți o casetă drop-down care să apară cu o grămadă de opțiuni. Folosind această funcție, mai întâi alegeți tipul de funcție de sumare pe care doriți să o utilizați cu ajutorul unui număr.
În exemplul nostru, SUM, așadar tastați numărul 9 sau faceți clic pe el din meniul drop-down, apoi tastați o virgulă și selectați intervalul de celule
21
Atunci când apăsați pe Enter, ar trebui să vedeți că valoarea 78 este aceeași ca și înainte, dar dacă aplicați din nou filtrul, vom vedea 11!
s>22
Excelent! Asta e exact ceea ce dorim.Acum poț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 este AGGREGATE. Diferența 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 de rezumat pentru a afla cum să utilizați funcția AGGREGATE și alte funcții cum ar fi MODE, MEDIAN, AVERAGE etc., în mai multe detalii
. 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ă!