Kako filtrirati podatke u programu Excel
Nedavno sam napisao članak o tome kako koristiti sažetak funkcija u Excelu za jednostavno sažimanje velikih količina podataka, ali taj članak je uzeo u obzir sve podatke na radnom listu. Što ako želite pogledati samo podskup podataka i sažeti podskup podataka?
U programu Excel možete stvoriti filtre na stupcima koji će skrivati retke koji se ne podudaraju s vašim filtrom. Osim toga, možete koristiti i posebne funkcije u Excelu za sažimanje podataka koristeći samo filtrirane podatke.
U ovom članku, provest ću vas kroz korake za stvaranje filtera u Excelu i također pomoću ugrađenih funkcija za sumiranje filtriranih podataka.
Stvorite jednostavne filtre u Excelu
U programu Excel možete stvoriti jednostavne filtre i složene filtre. Počnimo s jednostavnim filtrima. Kada radite s filtrima, uvijek biste trebali imati jedan redak na vrhu koji se koristi za oznake. Nije potrebno imati ovaj redak, ali rad s filtrima čini lakšim.
Iznad, imam neke lažne podatke i želim stvoriti filtar na Grad kolona. U Excelu je to stvarno lako. Idi naprijed i klikni na Podaci na vrpci, a zatim kliknite na filtar dugme. Ne morate odabrati podatke na listu ili kliknuti u prvom retku.
Kada kliknete na filtar, svaki stupac u prvom retku automatski će imati mali gumb padajućeg popisa koji se dodaje na desnoj strani.
Sada idite naprijed i kliknite na strelicu padajućeg izbornika u stupcu Grad. Vidjet ćete nekoliko različitih opcija, koje ću objasniti u nastavku.
Na vrhu možete brzo sortirati sve retke prema vrijednostima u stupcu Grad. Imajte na umu da će se prilikom sortiranja podataka premjestiti cijeli redak, a ne samo vrijednosti u stupcu Grad. Time ćete osigurati da podaci ostanu netaknuti kao i prije.
Također, dobra je ideja dodati stupac na samom početku nazvan ID i broj ga iz jednog u koliko redova imate na vašem radnom listu. Na taj način uvijek možete sortirati po stupcu ID i vratiti podatke u isti redoslijed kako je izvorno, ako vam je to važno.
Kao što možete vidjeti, svi podaci u proračunskoj tablici sada su razvrstani na temelju vrijednosti u stupcu Grad. Do sada nijedan redak nije skriven. Pogledajmo sada potvrdne okvire pri dnu dijaloga filtra. U mom primjeru, imam samo tri jedinstvene vrijednosti u stupcu Grad, a ta tri se pojavljuju na popisu.
Otišao sam naprijed i neoznačio dva grada i jednu ostavio provjerenu. Sada imam samo 8 redova podataka koji se prikazuju, a ostatak je skriven. Možete jednostavno reći da gledate filtrirane podatke ako provjerite brojeve redaka na krajnjoj lijevoj strani. Ovisno o tome koliko je redaka skriveno, vidjet ćete nekoliko dodatnih horizontalnih linija, a boja brojeva bit će plava.
Recimo da želim filtrirati drugi stupac kako bi se još smanjio broj rezultata. U stupcu C, imam ukupan broj članova u svakoj obitelji i želim vidjeti rezultate samo za obitelji s više od dva člana.
Idite naprijed i kliknite na strelicu padajućeg izbornika u stupcu C i vidjet ćete iste potvrdne okvire za svaku jedinstvenu vrijednost u stupcu. Međutim, u ovom slučaju želimo kliknuti Broj filtara , a zatim kliknite Veće od. Kao što možete vidjeti, tu je i gomila drugih opcija.
Pojavit će se novi dijalog i ovdje možete upisati vrijednost za filtar. Također možete dodati više kriterija s funkcijom AND ili OR. Moglo bi se reći da želite retke gdje je vrijednost veća od 2, a ne jednaka 5, na primjer.
Sada imam samo 5 redova podataka: obitelji samo iz New Orleansa i 3 ili više članova. Jednostavno? Napominjemo da filtar na stupcu možete jednostavno izbrisati klikom na padajući izbornik, a zatim klikom na Izbriši filtar iz "Naziv stupca" veza.
Dakle, to je sve o jednostavnim filtrima u Excelu. Vrlo su jednostavni za korištenje i rezultati su prilično izravni. Pogledajmo sada složene filtre koji koriste Napredna dijaloga filtara.
Stvorite napredne filtre u Excelu
Ako želite stvoriti naprednije filtre, morate upotrijebiti Napredna dijaloški filtar. Na primjer, recimo da sam htjela vidjeti sve obitelji koje žive u New Orleansu s više od 2 člana u svojoj obitelji ILI sve obitelji u Clarksvilleu s više od 3 člana u svojoj obitelji I samo oni s .EDU završava adresu e-pošte. Sada to ne možete učiniti jednostavnim filtrom.
Da biste to učinili, moramo postaviti Excelov list nešto drugačije. Nastavite i umetnite nekoliko redova iznad skupa podataka i kopirajte oznake naslova točno u prvi red kao što je prikazano u nastavku.
Evo kako funkcioniraju napredni filtri. Prvo morate unijeti kriterije u stupce na vrhu, a zatim kliknite Napredna pod Sortiraj i filtriraj na Podaci kartica.
Pa što točno možemo utipkati u te stanice? OK, počnimo s našim primjerom. Želimo vidjeti samo podatke iz New Orleansa ili Clarksvillea, pa ih upišite u ćelije E2 i E3.
Kada unosite vrijednosti u različite retke, to znači OR. Sada želimo obitelji u New Orleansu s više od dva člana i obitelji Clarksville s više od 3 člana. Da biste to učinili, upišite > 2 u C2 i > 3 u C3.
Budući da su> 2 i New Orleans u istom retku, to će biti operator AND. Isto vrijedi i za gornji red 3. Konačno, želimo samo obitelji s .EDU završnom adresom e-pošte. Da biste to učinili, samo upišite * Edu u oba D2 i D3. Simbol * označava bilo koji broj znakova.
Kada to učinite, kliknite bilo gdje u vašem skupu podataka, a zatim kliknite na Napredna dugme. Popis RangPolje će automatski otkriti vaš skup podataka otkad ste kliknuli na njega prije nego što kliknete gumb Napredno. Kliknite na mali mali gumb na desnoj strani Raspon kriterija dugme.
Odaberite sve od A1 do E3, a zatim ponovno kliknite na isti gumb kako biste se vratili u dijalog Napredni filtar. Kliknite U redu i vaši podaci bi se sada trebali filtrirati!
Kao što možete vidjeti, sada imam samo 3 rezultata koji odgovaraju svim tim kriterijima. Imajte na umu da se oznake za raspon kriterija moraju točno podudarati s oznakama za skup podataka kako bi ovo funkcioniralo.
Očigledno možete stvoriti mnogo kompliciranijih upita koristeći ovu metodu, pa se poigrajte s njom da biste dobili željene rezultate. Konačno, govorimo o primjeni funkcija sumacije na filtrirane podatke.
Zbrajanje filtriranih podataka
Recimo da želim sumirati broj članova obitelji na mojim filtriranim podacima, kako bih to učinio? Pa, ispraznimo naš filter klikom na Čisto na vrpci. Ne brinite, vrlo je jednostavno ponovno primijeniti napredni filtar klikom na gumb Napredno i ponovno kliknite U redu.
Na dnu našeg skupa podataka, dodajmo ćeliju ukupno i zatim dodajte funkciju zbroja da sumiramo ukupne članove obitelji. U mom primjeru, upravo sam upisao = SUM (C7: C31).
Dakle, ako pogledam sve obitelji, imam ukupno 78 članova. Sada idemo naprijed i ponovno primijenimo naš Napredni filtar i vidjeti što će se dogoditi.
Joj! Umjesto prikazivanja točnog broja, 11, još uvijek vidim ukupno 78! Zašto je to? Pa, funkcija SUM ne zanemaruje skrivene retke, tako da još uvijek radi izračun koristeći sve retke. Srećom, postoji nekoliko funkcija koje možete koristiti za ignoriranje skrivenih redaka.
Prvi je SUBTOTAL. Prije nego upotrijebimo bilo koju od ovih posebnih funkcija, htjet ćete očistiti svoj filtar, a zatim upisati funkciju.
Nakon uklanjanja filtra naprijed i upišite = SUBTOTAL ( i trebali biste vidjeti padajući okvir s mnoštvom opcija. Pomoću ove funkcije najprije odabirete vrstu funkcije zbrajanja koju želite koristiti pomoću broja.
U našem primjeru želim koristiti IZNOS, tako da bih upisao broj 9 ili samo kliknuo na njega iz padajućeg izbornika. Zatim unesite zarez i odaberite raspon ćelija.
Kada pritisnete enter, trebali biste vidjeti vrijednost 78 ista kao i prije. Međutim, ako sada ponovno primijenite filtar, vidjet ćemo 11!
Izvrsno! To je upravo ono što želimo. Sada možete prilagoditi filtre, a vrijednost će uvijek odražavati samo one retke koji se trenutno prikazuju.
Druga funkcija koja funkcionira gotovo jednako kao i funkcija SUBTOTAL AGREGAT. Jedina razlika je u tome što postoji još jedan parametar u AGGREGATE funkciji gdje morate specificirati da želite zanemariti skrivene retke.
Prvi parametar je funkcija zbrajanja koju želite koristiti i kao kod SUBTOTAL, 9 predstavlja funkciju SUM. Druga opcija je mjesto gdje morate upisati 5 da biste zanemarili skrivene retke. Zadnji parametar je isti i raspon je ćelija.
Također možete pročitati moj članak o funkcijama sažetka kako biste saznali više o tome kako koristiti funkciju AGGREGATE i druge funkcije kao što su MODE, MEDIAN, AVERAGE itd..
Nadajmo se da će vam ovaj članak dati dobru početnu točku za stvaranje i korištenje filtara u programu Excel. Ako imate bilo kakvih pitanja, slobodno postavite komentar. Uživati!
