Neseniai parašiau straipsnį apie tai, kaip naudoti „Excel“ suvestinės funkcijas, kad būtų galima lengvai apibendrinti didelius duomenų kiekius, tačiau tame straipsnyje buvo atsižvelgta į visus duomenis darbalapyje. O kas, jei norėtumėte pažvelgti tik į duomenų pogrupį ir apibendrinti duomenų pogrupį?

„Excel“ stulpeliuose galite sukurti filtrus, kurie paslėps eilutes, kurios neatitinka jūsų filtro. Be to, naudodami specialias „Excel“ funkcijas, galite apibendrinti duomenis naudodami tik filtruotus duomenis.

Šiame straipsnyje paaiškinsiu, kaip sukurti filtrus „Excel“ ir naudoti įmontuotas funkcijas, kad apibendrintumėte tuos filtruotus duomenis.

Sukurkite paprastus filtrus „Excel“

Programoje „Excel“ galite sukurti paprastus ir sudėtingus filtrus. Pradėkime nuo paprastų filtrų. Dirbdami su filtrais, viršuje visada turėtumėte vieną eilutę, kuri naudojama etiketėms. Nebūtina turėti šios eilutės, tačiau tai šiek tiek palengvina darbą su filtrais.

duomenų pavyzdžiai Excel

Aukščiau turiu suklastotų duomenų ir noriu sukurti filtrą stulpelyje „Miestas“. „Excel“ programoje tai padaryti yra labai lengva. Eikite į priekį ir spustelėkite juostoje skirtuką Duomenys, tada spustelėkite mygtuką Filtruoti. Jums taip pat nereikia pasirinkti duomenų lape arba spustelėti pirmoje eilutėje.

„excel“ duomenų filtras

Spustelėjus „Filtras“, kiekviename pirmosios eilutės stulpelyje automatiškai pridedamas mažas išskleidžiamasis mygtukas, esantis dešinėje.

pridėtas filtras „excel“

Dabar eik į priekį ir stulpelyje Miestas spustelėkite išskleidžiamąją rodyklę. Pamatysite keletą skirtingų variantų, kuriuos paaiškinsiu toliau.

filtrų parinktys puikiai tinka

Viršuje galite greitai surūšiuoti visas eilutes pagal reikšmes stulpelyje Miestas. Atminkite, kad rūšiuodami duomenis, ji perkels visą eilutę, o ne tik stulpelio „City“ reikšmes. Tai užtikrins, kad jūsų duomenys išliks nepažeisti taip, kaip buvo anksčiau.

Taip pat patartina pridėti stulpelį pačiame priekyje, vadinamą ID, ir sunumeruoti jį nuo vienos iki daugybės eilučių, kurias turite savo darbalapyje. Tokiu būdu jūs visada galite rūšiuoti pagal ID stulpelį ir gauti duomenis atgal ta pačia tvarka, kokia buvo iš pradžių, jei tai jums svarbu.

duomenys surūšiuoti Excel

Kaip matote, visi skaičiuoklės duomenys dabar yra rūšiuojami pagal stulpelyje Miestas esančias reikšmes. Kol kas nė viena eilutė nėra paslėpta. Dabar pažvelkime į žymimuosius laukelius filtro dialogo apačioje. Mano pavyzdyje aš turiu tik tris unikalias reikšmes stulpelyje „Miestas“, o šios trys pateikiamos sąraše.

filtruotos eilutės išsiskiria

Aš nuėjau į priekį, nepažymėjęs dviejų miestų, o vieną palikęs patikrintą. Dabar turiu tik 8 duomenų eilutes, o likusios yra paslėptos. Galite lengvai pasakyti, kad žiūrite į filtruotus duomenis, jei patikrinate eilučių numerius kairėje. Priklausomai nuo to, kiek eilučių paslėpta, pamatysite keletą papildomų horizontalių linijų, o skaičių spalva bus mėlyna.

Dabar tarkime, kad noriu filtruoti antrame stulpelyje, kad dar labiau sumažintumėte rezultatų skaičių. C stulpelyje turiu bendrą narių skaičių kiekvienoje šeimoje ir noriu pamatyti tik šeimų, turinčių daugiau nei du narius, rezultatus.

skaičių filtras

Eikite į priekį ir spustelėkite stulpelį C stulpelyje ir pamatysite tuos pačius žymės langelius kiekvienai stulpelio unikaliajai vertei. Tačiau šiuo atveju mes norime spustelėti Skaičių filtrai ir tada spustelėkite Didesnis nei. Kaip matote, yra ir daugybė kitų variantų.

yra didesnis už filtrą

Atsidarys naujas dialogo langas, kuriame galite įvesti filtro vertę. Taip pat galite pridėti daugiau nei vieną kriterijų su funkcija AND arba OR. Galima sakyti, kad norite eilučių, kurių vertė yra didesnė nei 2, o ne lygi, pavyzdžiui, 5.

du filtrai pranašesni

Dabar man trūksta tik 5 duomenų eilučių: šeimos tik iš Naujojo Orleano ir turinčios 3 ar daugiau narių. Pakankamai lengva? Atminkite, kad stulpelio filtrą galite lengvai išvalyti spustelėdami išskleidžiamąjį meniu ir spustelėdami nuorodą Išvalyti filtrą iš „Stulpelio pavadinimo“.

skaidrus filtras excel

Taigi apie tai kalbama paprastiems „Excel“ filtrams. Juos labai lengva naudoti, o rezultatai yra labai aiškūs. Dabar pažvelkime į sudėtingus filtrus naudodami dialogo langą Išplėstiniai filtrai.

Sukurkite išplėstinius filtrus „Excel“

Jei norite sukurti sudėtingesnius filtrus, turite naudoti dialogo langą Išplėstinis filtras. Pvz., Tarkime, aš norėjau pamatyti visas šeimas, kurios gyvena Naujajame Orleane ir kurių šeimoje yra daugiau nei 2 nariai ARBA visos Clarksville šeimos, kuriose yra daugiau nei 3 nariai, IR tik tas, kurių el. Pašto adresas yra .EDU. Dabar to negalite padaryti naudodamiesi paprastu filtru.

Norėdami tai padaryti, turime šiek tiek kitaip nustatyti „Excel“ lapą. Eikite į priekį ir įdėkite keletą eilučių virš savo duomenų rinkinio ir nukopijuokite antraščių etiketes tiksliai į pirmąją eilutę, kaip parodyta žemiau.

išplėstinis filtro nustatymas

Dabar paaiškinta, kaip veikia išplėstiniai filtrai. Pirmiausia turite įvesti kriterijus į stulpelius viršuje ir tada spustelėkite mygtuką Išplėstinė, esančią skirtuko Duomenys skiltyje Rūšiuoti ir filtruoti.

išplėstinė filtro juosta

Taigi ką tiksliai galime įvesti į tas ląsteles? Gerai, todėl pradėkime nuo mūsų pavyzdžio. Mes norime pamatyti tik duomenis iš Naujojo Orleano ar Clarksville, todėl įrašykime juos į langelius E2 ir E3.

išplėstinis filtras miestas

Kai įvedate reikšmes skirtingose ​​eilutėse, tai reiškia ARBA. Dabar norime Naujojo Orleano šeimos, kuriose yra daugiau nei du nariai, ir Clarksville šeimų, kuriose yra daugiau nei 3 nariai. Norėdami tai padaryti, C2 įveskite> 2 ir> 3 - C3.

Išplėstiniai filtrai išsiskiria

Kadangi> 2 ir Naujasis Orleanas yra toje pačioje eilutėje, tai bus IR operatorius. Tas pats pasakytina ir apie 3 eilutę aukščiau. Galiausiai norime tik šeimų, kurių .EDU pabaigos el. Pašto adresas. Norėdami tai padaryti, tiesiog įveskite * .edu į D2 ir D3. Simbolis * reiškia bet kokį simbolių skaičių.

kriterijų diapazonas išsiskiria

Kai tai padarysite, spustelėkite bet kurią duomenų rinkinio vietą ir spustelėkite mygtuką „Advanced“. Laukas Sąrašo diapazonas automatiškai išsiaiškins jūsų duomenų rinkinį, nes jūs jį spustelėjote prieš paspausdami išplėstinį mygtuką. Dabar spustelėkite mygtuką „Kriterijų diapazonas“, dešinėje, esantį mažą mygtuką.

pasirinkti kriterijų diapazoną

Pasirinkite viską nuo A1 iki E3, tada dar kartą spustelėkite tą patį mygtuką, kad grįžtumėte į išplėstinio filtro dialogą. Spustelėkite Gerai ir jūsų duomenys dabar turėtų būti filtruojami!

filtruoti rezultatus

Kaip matote, dabar turiu tik 3 rezultatus, kurie atitinka visus tuos kriterijus. Atminkite, kad kriterijų diapazono etiketės turi tiksliai sutapti su duomenų rinkinio etiketėmis, kad tai veiktų.

Akivaizdu, kad naudodamiesi šiuo metodu galite sukurti daug sudėtingesnių užklausų, todėl pažaisk su ja norimų rezultatų siekdami. Galiausiai pakalbėkime apie sumavimo funkcijų taikymą filtruojamiems duomenims.

Filtruotų duomenų apibendrinimas

Dabar tarkime, kad noriu susumuoti šeimos narių skaičių pagal mano filtruojamus duomenis, kaip aš tai padariau? Na, išvalykime filtrą spustelėdami mygtuką Išvalyti juostelėje. Nesijaudinkite, labai lengva vėl pritaikyti išplėstinį filtrą, tiesiog spustelėdami mygtuką Advanced ir dar kartą spustelėdami Gerai.

skaidrus filtras „excel“

Duomenų rinkinio apačioje pridėkime langelį pavadinimu Iš viso ir tada pridėkime sumos funkciją, kad būtų galima susumuoti visus šeimos narius. Savo pavyzdyje aš tiesiog įvedžiau = SUM (C7: C31).

suma bendra excel

Taigi, jei žvelgiu į visas šeimas, iš viso turiu 78 narius. Dabar eikime į priekį ir dar kartą pritaikykite išplėstinį filtrą ir pažiūrėkime, kas atsitiks.

neteisingas bendras filtras

Oi! Užuot rodęs teisingą skaičių 11, vis tiek matau, kad jų skaičius 78! Kodėl taip yra? Na, SUM funkcija neignoruoja paslėptų eilučių, todėl vis tiek skaičiuoja naudodama visas eilutes. Laimei, yra keletas funkcijų, kuriomis galite ignoruoti paslėptas eiles.

Pirmasis yra SUBTOTAL. Prieš naudodamiesi bet kuria iš šių specialiųjų funkcijų, turite išvalyti filtrą ir tada įvesti funkciją.

Kai filtras išvalytas, eikite į priekį ir įveskite = SUBTOTAL (ir turėtumėte pamatyti, kad pasirodys išskleidžiamasis langas su daugybe galimybių. Naudodamiesi šia funkcija pirmiausia išsirinksite sumavimo funkcijos tipą, kurį norite naudoti naudodami skaičių.

Mūsų pavyzdyje noriu naudoti SUM, todėl norėčiau įvesti skaičių 9 arba tiesiog spustelėkite jį išskleidžiamajame meniu. Tada įveskite kablelį ir pasirinkite langelių diapazoną.

tarpinė funkcija

Kai paspausite „Enter“, turėtumėte pamatyti, kad 78 vertė yra tokia pati kaip ir anksčiau. Tačiau jei vėl pritaikysite filtrą, pamatysime 11!

tarpinė suma filtre

Puikiai! Būtent to ir norime. Dabar galite reguliuoti filtrus, o vertė visada atspindės tik šiuo metu rodomas eilutes.

Antroji funkcija, veikianti beveik taip pat kaip SUBTOTAL funkcija, yra AGREGATUOTI. Vienintelis skirtumas yra tas, kad funkcijos AGGREGATE yra dar vienas parametras, kuriame jūs turite nurodyti, kad norite nepaisyti paslėptų eilučių.

agregato funkcija

Pirmasis parametras yra sumavimo funkcija, kurią norite naudoti, o kaip ir SUBTOTAL, 9 reiškia SUM funkciją. Antrasis variantas - turite įvesti 5, kad nepaisytumėte paslėptų eilučių. Paskutinis parametras yra tas pats ir yra langelių diapazonas.

Taip pat galite perskaityti mano straipsnį apie suvestines funkcijas, kad išmoktumėte išsamiau naudotis AGGREGATE funkcija ir kitomis funkcijomis, tokiomis kaip MODE, MEDIAN, AVERAGE ir kt.

Tikimės, kad šis straipsnis suteikia jums gerą pradžią kuriant ir naudojant filtrus „Excel“. Jei turite klausimų, nedvejodami rašykite komentarą. Mėgautis!