Kaip filtruoti duomenis „Excel“
Neseniai parašiau straipsnį apie tai, kaip naudoti Excel funkcijas, kad būtų galima lengvai apibendrinti didelius duomenų kiekius, tačiau šiame straipsnyje buvo atsižvelgta į visus darbalapio duomenis. Ką daryti, jei norite tik žiūrėti į duomenų pogrupį ir apibendrinti duomenų pogrupį?
„Excel“ sistemoje galite sukurti filtrus stulpeliuose, kurie bus paslėpti eilutes, kurios neatitinka jūsų filtro. Be to, galite naudoti „Excel“ specialias funkcijas, kad būtų galima apibendrinti duomenis naudojant tik filtruotus duomenis.
Šiame straipsnyje aš nueisiu jus per veiksmus, kaip sukurti filtrus „Excel“ programoje ir taip pat naudoti įmontuotas funkcijas, kad apibendrintumėte filtruotus duomenis.
„Excel“ sukurti paprastus filtrus
„Excel“ programoje galite sukurti paprastus filtrus ir sudėtingus filtrus. Pradėkime nuo paprastų filtrų. Dirbdami su filtrais, visada turite turėti vieną eilutę viršuje, kuri naudojama etiketėms. Tai nėra reikalavimas turėti šią eilutę, tačiau tai leidžia dirbti su filtrais šiek tiek lengviau.
Viršuje turiu netikrų duomenų ir noriu sukurti filtrą Miestas stulpelyje. „Excel“ programoje tai tikrai lengva padaryti. Eikite į priekį ir spustelėkite Duomenys juostoje esančiame skirtuke ir spustelėkite Filtras mygtukas. Jūs neturite pasirinkti duomenų lape arba spustelėti pirmoje eilutėje.
Kai spustelėsite „Filter“, kiekviena pirmoje eilutėje esanti stulpelis automatiškai turės mažą išskleidžiamąjį mygtuką, esantį dešinėje.
Dabar eikite į priekį ir spustelėkite skiltyje Miesto išskleidžiamajame rodyklėje. Pamatysite keletą skirtingų parinkčių, kurias paaiškinsiu toliau.
Viršuje galite greitai surūšiuoti visas eilutes pagal reikšmes miesto stulpelyje. Atkreipkite dėmesį, kad rūšiuodami duomenis jis perkelia visą eilutę, o ne tik stulpelio „Miestas“ vertes. Tai užtikrins, kad jūsų duomenys išliktų tokie patys kaip ir anksčiau.
Taip pat gera idėja pridėti stulpelį pačiame priekyje, vadinamą ID, ir suskaičiuoti jį nuo vieno iki daugelio eilučių, esančių jūsų darbalapyje. Tokiu būdu visada galite rūšiuoti pagal ID stulpelį ir gauti savo duomenis tuo pačiu būdu, kaip tai buvo iš pradžių, jei tai jums svarbu.
Kaip matote, visi skaičiuoklėje esantys duomenys yra rūšiuojami pagal miesto stulpelio vertes. Iki šiol eilutės nėra paslėptos. Dabar pažiūrėkime į langelius, esančius filtro dialogo apačioje. Mano pavyzdyje turiu tik tris unikalias vertybes miesto skiltyje, o trys rodomos sąraše.
Aš nuvažiavau į priekį ir nepažymėjau dviejų miestų ir palikau vieną. Dabar aš turiu tik 8 duomenų eilutes ir likusios yra paslėptos. Jūs galite lengvai pasakyti, kad žiūrite į filtruotus duomenis, jei patikrinate eilės numerius kairėje pusėje. Priklausomai nuo to, kiek eilučių yra paslėptos, pamatysite keletą papildomų horizontalių linijų ir numerių spalva bus mėlyna.
Dabar tarkime, kad noriu toliau filtruoti antrame stulpelyje, kad dar labiau sumažintumėte rezultatų skaičių. C skiltyje aš turiu bendrą kiekvienos šeimos narių skaičių ir noriu matyti tik tuos rezultatus, kurie susiję su šeimomis, kuriose yra daugiau nei du nariai.
Eikite į priekį ir spragtelėkite ant stulpelio C stulpelyje esančią rodyklę ir pamatysite tuos pačius langelius kiekvienai unikaliai vertei. Tačiau šiuo atveju norime spustelėti Skaičių filtrai tada spustelėkite Geresnis negu. Kaip matote, taip pat yra daug kitų galimybių.
Atsiras naujas dialogo langas ir čia galite įvesti filtro vertę. Taip pat galite pridėti daugiau nei vieną kriterijų su IR arba OR funkcija. Galima sakyti, kad norite, kad, pavyzdžiui, eilutės, kurių vertė yra didesnė nei 2, o ne lygi 5.
Dabar aš nusileidžiu tik į penkias duomenų eilutes: šeimos tik iš Naujosios Orleano ir 3 ar daugiau narių. Pakankamai paprasta? Atkreipkite dėmesį, kad galite lengvai išvalyti filtrą stulpelyje spustelėję išskleidžiamąjį meniu ir spustelėdami Išvalyti filtrą iš „Stulpelio pavadinimas“ nuorodą.
Taigi tai yra apie paprastus filtrus „Excel“. Jie yra labai paprasti naudoti ir rezultatai yra gana paprasti. Dabar pažiūrėkime į sudėtingus filtrus, naudodami Išplėstinė filtrų dialogo langas.
„Excel“ sukurkite išplėstinius filtrus
Jei norite sukurti pažangesnius filtrus, turite naudoti Išplėstinė filtro dialogo langas. Pavyzdžiui, tarkime, kad norėjau pamatyti visas šeimas, gyvenančias Naujoje Orleane, turinčioje daugiau nei 2 narius savo šeimoje ARBA visos Clarksville šeimos, kuriose yra daugiau nei 3 šeimos nariai IR tik tie, kurie turi a .EDU galutinis el. pašto adresas. Dabar negalite to padaryti su paprastu filtru.
Norėdami tai padaryti, reikia šiek tiek skirtingai nustatyti „Excel“ lapą. Eikite į priekį ir įdėkite keletą eilučių virš duomenų rinkinio ir nukopijuokite antraštės etiketes tiksliai į pirmąją eilutę, kaip parodyta žemiau.
Dabar čia veikia, kaip veikia filtrai. Pirmiausia turite įvesti savo kriterijus į stulpelius, esančius viršuje, o tada spustelėkite Išplėstinė mygtukas po Rūšiuoti ir filtruoti ant Duomenys skirtuką.
Taigi, ką tiksliai galime įvesti į šias ląsteles? Gerai, todėl pradėkime nuo mūsų pavyzdžio. Mes tik norime matyti duomenis iš Naujosios Orleano ar Clarksville, todėl įvesime juos į ląsteles E2 ir E3.
Kai įvedate reikšmes skirtingose eilutėse, tai reiškia OR. Dabar norime, kad New Orleans šeimos, turinčios daugiau nei du narius, ir Clarksville šeimos, kuriose yra daugiau nei 3 nariai. Norėdami tai padaryti, įveskite > 2 C2 ir > 3 C3.
Kadangi> 2 ir Naujasis Orleanas yra toje pačioje eilutėje, tai bus IR operatorius. Tas pats pasakytina ir apie 3 eilutę. Galiausiai, norime tik šeimų, turinčių .EDU pabaigos el. Pašto adresą. Norėdami tai padaryti, tiesiog įveskite * .edu į D2 ir D3. * Simbolis reiškia bet kokį simbolių skaičių.
Kai tai padarysite, spustelėkite bet kurią duomenų rinkinio vietą ir spustelėkite „ Išplėstinė mygtukas. The Sąrašas „Rang“Laukas automatiškai išsiaiškins jūsų duomenų rinkinį, kai spustelėjote jį prieš spustelėję mygtuką Advanced. Dabar spustelėkite mažą mažą mygtuką, esantį dešinėje Kriterijų diapazonas mygtukas.
Pasirinkite viską nuo A1 iki E3 ir dar kartą spustelėkite tą patį mygtuką, kad grįžtumėte į išplėstinio filtro dialogą. Spustelėkite Gerai, o duomenys turėtų būti filtruojami!
Kaip matote, dabar turiu tik 3 rezultatus, kurie atitinka visus šiuos kriterijus. Atkreipkite dėmesį, kad tam, kad jis veiktų, kriterijų diapazono etiketės turi tiksliai atitikti duomenų rinkinio etiketes.
Akivaizdu, kad naudodami šį metodą galite sukurti daug sudėtingesnes užklausas, todėl žaisti su juo, kad gautumėte norimus rezultatus. Galiausiai, kalbėkime apie sumavimo funkcijų taikymą filtruotiems duomenims.
Apibendrinant filtruotus duomenis
Tarkime, kad noriu apibendrinti šeimos narių skaičių mano filtruotuose duomenyse, kaip tai padaryti? Na, išvalykite mūsų filtrą spustelėję Išvalyti mygtukas juostelėje. Nesijaudinkite, dar kartą lengva taikyti išplėstinį filtrą tiesiog spustelėję mygtuką Išsamiau ir dar kartą spustelėję Gerai.
Mūsų duomenų rinkinio apačioje pridėkime skambutį Iš viso ir tada pridėkite sumos funkciją, kad apibendrintumėte visus šeimos narius. Mano pavyzdyje aš tiesiog įvedžiau = SUM (C7: C31).
Taigi, jei žiūriu į visas šeimas, aš turiu 78 narius. Dabar eikime į priekį ir pakartotinai pritaikykime „Advanced“ filtrą ir pamatysime, kas vyksta.
Kviečia! Vietoj to, kad rodytumėte teisingą skaičių, 11, vis dar matau, kad bendras skaičius yra 78! Kodėl taip? Na, SUM funkcija nepaiso paslėptų eilučių, todėl vis dar atlieka skaičiavimus naudodama visas eilutes. Laimei, yra keletas funkcijų, kuriomis galite ignoruoti paslėptas eilutes.
Pirmasis yra SUBTOTAL. Prieš naudodami bet kurią iš šių specialiųjų funkcijų, norite išvalyti filtrą ir įvesti funkciją.
Išvalius filtrą, eikite į priekį ir įveskite = SUBTOTAL ( ir turėtumėte pamatyti išskleidžiamąjį langelį su daugybe parinkčių. Naudodami šią funkciją, pirmiausia pasirinkite skaičių sumavimo funkciją, kurią norite naudoti naudodami skaičių.
Mūsų pavyzdyje noriu naudoti SUMA, taigi norėčiau įrašyti 9 numerį arba tiesiog spustelėkite jį iš išskleidžiamojo meniu. Tada įveskite kablelį ir pasirinkite ląstelių diapazoną.
Paspaudus Enter, turėtumėte matyti, kad 78 vertė yra tokia pati, kaip ir anksčiau. Tačiau, jei jūs vėl taikysite filtrą, pamatysime 11!
Puikus! Būtent tai mes norime. Dabar galite reguliuoti savo filtrus ir vertė visada atspindės tik šiuo metu rodomas eilutes.
Antroji funkcija, kuri veikia beveik lygiai taip pat, kaip ir SUBTOTAL funkcija SUDARYMAS. Vienintelis skirtumas yra tas, kad funkcija AGGREGATE yra dar vienas parametras, kuriame turite nurodyti, kad norite ignoruoti paslėptas eilutes.
Pirmasis parametras yra suvestinė funkcija, kurią norite naudoti ir kaip ir SUBTOTAL, 9 reiškia SUM funkciją. Antrasis variantas yra tai, kur reikia įvesti 5, kad ignoruotumėte paslėptas eilutes. Paskutinis parametras yra tas pats ir yra ląstelių diapazonas.
Taip pat galite perskaityti mano straipsnį apie suvestines funkcijas, kad sužinotumėte, kaip naudoti AGGREGATE funkciją ir kitas funkcijas, pvz., MODE, MEDIAN, AVERAGE ir kt..
Tikimės, kad šis straipsnis suteikia jums gerą pradžios tašką kuriant ir naudojant „Excel“ filtrus. Jei turite klausimų, rašykite komentarą. Mėgautis!
