Kako filtrirati podatke v Excelu

Pred kratkim sem napisal članek o uporabi povzetnih funkcij v Excelu za preprosto povzemanje velikih količin podatkov, vendar je ta članek upošteval vse podatke na delovnem listu. Kaj, če želite pogledati samo podmnožico podatkov in povzeti podmnožico podatkov?

V Excelu lahko ustvarite filtre za stolpce, ki bodo skrivali vrstice, ki se ne ujemajo z vašim filtrom. Poleg tega lahko v Excelu uporabite tudi posebne funkcije za povzemanje podatkov samo s filtriranimi podatki.

V tem članku vas bom vodil skozi korake za ustvarjanje filtrov v Excelu in tudi z uporabo vgrajenih funkcij za povzetek filtriranih podatkov.

Ustvarite preproste filtre v Excelu

V Excelu lahko ustvarite preproste filtre in zapletene filtre. Začnimo s preprostimi filtri. Pri delu s filtri morate imeti na vrhu vedno eno vrstico, ki se uporablja za oznake. To ni zahteva za to vrstico, vendar pa je delo z filtri malo lažje.

Zgoraj imam nekaj ponarejenih podatkov in želim ustvariti filter na Mesto v stolpcu. V Excelu je to zelo enostavno. Pojdi naprej in klikni na Podatki na traku in nato kliknite na Filter gumb. Ni vam treba izbrati podatkov na listu ali klikniti v prvi vrstici.

Ko kliknete na Filter, bo vsak stolpec v prvi vrstici samodejno dodan majhen spustni gumb na desni.

Zdaj nadaljujte in kliknite na puščico v stolpcu Mesto. Videli boste nekaj različnih možnosti, ki jih bom razložil spodaj.

Na vrhu lahko vse vrstice hitro razvrstite po vrednostih v stolpcu Mesto. Upoštevajte, da bo pri razvrščanju podatkov premaknjena celotna vrstica, ne samo vrednosti v stolpcu Mesto. Tako boste zagotovili, da bodo vaši podatki ostali nedotaknjeni, kot je bilo prej.

Prav tako je dobra ideja, da dodate stolpec na samem sprednjem delu, imenovanem ID, in ga navedete iz ene v toliko vrstic, ki jih imate v delovnem listu. Tako lahko vedno razvrstite po stolpcu ID in dobite podatke nazaj v enakem vrstnem redu, kot je bil prvotno, če je to za vas pomembno.

Kot lahko vidite, so vsi podatki v preglednici razvrščeni glede na vrednosti v stolpcu Mesto. Do sedaj ni nobenih vrstic. Zdaj pa si oglejte potrditvena polja na dnu pogovornega okna filtra. V mojem primeru imam v stolpcu City samo tri edinstvene vrednosti in ti trije so prikazani na seznamu.

Šel sem naprej in nepreverjen dve mesti in pustil eno preverjeno. Zdaj imam samo 8 vrstic podatkov, ostale pa so skrite. Z lahkoto lahko ugotovite, da gledate filtrirane podatke, če preverite številke vrstic na skrajni levi strani. Glede na to, koliko vrstic je skritih, boste videli nekaj dodatnih vodoravnih črt in barva številk bo modra.

Zdaj pa recimo, da želim filtrirati v drugem stolpcu, da bi še zmanjšali število rezultatov. V stolpcu C imam skupno število članov v vsaki družini in želim videti rezultate samo za družine z več kot dvema članoma.

Nadaljujte in v stolpcu C kliknite puščico v spustnem meniju in videli boste enaka potrditvena polja za vsako unikatno vrednost v stolpcu. Vendar pa v tem primeru želimo klikniti Številčni filtri in nato kliknite Večji kot. Kot lahko vidite, obstaja tudi veliko drugih možnosti.

Odprlo se bo novo pogovorno okno, v katerem lahko vnesete vrednost filtra. S funkcijo AND ali OR lahko dodate tudi več kriterijev. Lahko bi rekli, da želite vrstice, kjer je vrednost večja od 2 in ne enaka 5, na primer.

Zdaj imam samo 5 vrstic podatkov: družine samo iz New Orleansa in 3 ali več članov. Dovolj preprosto? Upoštevajte, da lahko filter v stolpcu preprosto izbrišete tako, da kliknete spustni meni in nato kliknete Počisti filter iz »Ime stolpca« povezavo.

To je torej za preproste filtre v Excelu. So zelo enostavne za uporabo in rezultati so precej naravnost naprej. Zdaj pa si poglejmo zapletene filtre z uporabo Napredno pogovorno okno filtrov.

Ustvarite napredne filtre v Excelu

Če želite ustvariti bolj napredne filtre, morate uporabiti Napredno pogovorno okno filtra. Recimo, da sem hotel videti vse družine, ki živijo v New Orleansu, z več kot dvema članoma v svoji družini ALI vse družine v Clarksvilleu z več kot 3 člani v svoji družini IN samo tisti z a .EDU končni e-poštni naslov. Sedaj tega ne morete storiti s preprostim filtrom.

Če želite to narediti, moramo Excelov list nekoliko drugače nastaviti. Nadaljujte in vstavite nekaj vrstic nad svoj niz podatkov in kopirajte naslovne oznake točno v prvo vrstico, kot je prikazano spodaj.

Zdaj tukaj je, kako napredni filtri delujejo. Najprej vnesite merila v stolpce na vrhu in nato kliknite Napredno pod Razvrsti in filtriraj na Podatki zavihek.

Torej, kaj točno lahko vtipkamo v te celice? OK, začnimo z našim primerom. Želimo videti samo podatke iz New Orleansa ali Clarksvillea, zato jih vnesimo v celice E2 in E3.

Ko vnesete vrednosti na različne vrstice, pomeni OR. Zdaj želimo družine New Orleansa z več kot dvema članoma in družinami Clarksville z več kot 3 člani. To naredite tako, da vnesete > 2 v C2 in > 3 v C3.

Ker sta> 2 in New Orleans v isti vrstici, bo operater AND. Enako velja za vrstico 3 zgoraj. Nazadnje želimo le družine z končnim e-poštnim naslovom .EDU. To naredite tako, da vtipkate * .edu D2 in D3. Simbol * pomeni poljubno število znakov.

Ko to storite, kliknite kjerkoli v vašem nizu podatkov in nato kliknite na Napredno gumb. The Seznam RangPolje bo samodejno ugotovilo vaš nabor podatkov, odkar ste kliknili nanj, preden kliknete gumb Napredno. Sedaj kliknite na majhen gumb na desni strani Obseg meril gumb.

Izberite vse od A1 do E3 in nato znova kliknite isti gumb, da se vrnete v pogovorno okno Napredni filter. Kliknite V redu in vaši podatki bodo zdaj filtrirani!

Kot lahko vidite, zdaj imam samo tri rezultate, ki ustrezajo vsem tem merilom. Upoštevajte, da se morajo oznake za razpon meril natančno ujemati z oznakami za nabor podatkov, da bo to delovalo.

Očitno lahko s to metodo ustvarite veliko bolj zapletenih poizvedb, zato se z njimi poigrajte, da dobite želene rezultate. Nazadnje, govorimo o uporabi funkcij sumacije v filtriranih podatkih.

Povzetek filtriranih podatkov

Zdaj pa recimo, da želim povzeti število družinskih članov na mojih filtriranih podatkih, kako bi to storil? No, počistite naš filter s klikom na Jasno na traku. Ne skrbite, zelo enostavno je ponovno uporabiti napredni filter tako, da preprosto kliknete gumb Dodatno in ponovno kliknete V redu.

Na dnu našega nabora podatkov dodamo klicano celico Skupaj in nato dodajte funkcijo vsote, da povzamemo celotne družinske člane. V mojem primeru sem samo natipkal = SUM (C7: C31).

Torej, če pogledam vse družine, imam skupaj 78 članov. Zdaj pa gremo naprej in ponovno uporabimo naš napredni filter in vidimo, kaj se zgodi.

Ups! Namesto prikazovanja pravilne številke, 11, še vedno vidim, da je skupno 78! Zakaj? Funkcija SUM ne ignorira skritih vrstic, zato še vedno opravlja izračun z uporabo vseh vrstic. Na srečo obstaja nekaj funkcij, ki jih lahko uporabite za ignoriranje skritih vrstic.

Prva je SUBTOTAL. Preden uporabite katero od teh posebnih funkcij, boste želeli počistiti filter in vnesti funkcijo.

Ko je filter odstranjen, nadaljujte in vnesite = SUBTOTAL ( in prikazalo se bo spustno polje z vrsto možnosti. S to funkcijo najprej izberete vrsto funkcije seštevanja, ki jo želite uporabiti s številko.

V našem primeru želim uporabiti SUM, zato bi vtipkala številko 9 ali samo kliknila na spustni meni. Nato vnesite vejico in izberite obseg celic.

Ko pritisnete enter, boste videli vrednost 78, kot je bila prej. Če pa boste filter ponovno uporabili, bomo videli 11!

Odlično! Točno to hočemo. Sedaj lahko prilagodite filtre, vrednost pa bo vedno odražala le trenutno prikazane vrstice.

Druga funkcija, ki deluje skoraj popolnoma enako kot funkcija SUBTOTAL, je AGREGAT. Edina razlika je, da v funkciji AGGREGATE obstaja še en parameter, v katerem morate podati, da želite prezreti skrite vrstice.

Prvi parameter je funkcija seštevanja, ki jo želite uporabiti in kot pri SUBTOTAL, 9 predstavlja funkcijo SUM. Druga možnost je, kje morate vnesti 5, da ignorirate skrite vrstice. Zadnji parameter je enak in je obseg celic.

Prav tako lahko preberete moj članek o funkcijah povzetka, če želite izvedeti, kako uporabljati funkcijo AGGREGATE in druge funkcije, kot so MODE, MEDIAN, AVERAGE itd..

Upamo, da vam ta članek ponuja dobro izhodišče za ustvarjanje in uporabo filtrov v Excelu. Če imate kakršnakoli vprašanja, lahko objavite komentar. Uživajte!