Nedavno sam napisao članak o korištenju sažetih funkcija u programu Excel za jednostavno sažimanje velikih količina podataka, ali taj je član uzimao u obzir sve podatke na radnom listu. Što ako želite samo pogledati podskup podataka i sažeti podskup podataka?
U programu Excel možete izraditi filtre na stupcima koji će sakriti retke koji se ne podudaraju s vašim filtrom. Osim toga, u Excelu možete koristiti i posebne funkcije kako biste saželi podatke koristeći samo filtrirane podatke.
U ovom ću članku proučiti korake za izradu filtara u programu Excel i upotrebom ugrađenih funkcija kako bi se saželi taj filtrirani podaci.
U programu Excel možete izraditi 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 zahtjev da se ovaj redak, ali čini rad s filtrima malo lakše.
Iznad, imam neke lažne podatke i želim stvoriti filtar na Grad kolona. U programu Excel ovo je stvarno lako učiniti. Idite naprijed i kliknite na Podaci karticu na vrpci i zatim kliknite na filtar dugme. Ne morate odabrati podatke na listu ili kliknuti ni u prvom redu.
Kada kliknete na Filtar, svaki stupac u prvom retku automatski će imati manju tipku padajućeg popisa.
Sada idi naprijed i kliknite strelicu padajućeg izbornika u stupcu Grada. 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, kada poredete podatke, premjestit će cijeli red, a ne samo vrijednosti u stupcu Grad. To će osigurati da podaci ostanu netaknuti baš kao i prije.
Također, preporučujemo da dodate stupac na samom prednjem dijelu naziva ID i brojite ga s jedne na međutim mnoge retke koje imate u radnom listu. Na taj način uvijek možete sortirati prema ID stupcu i vratiti podatke u istom redoslijedu koji je izvorno, ako je to važno za vas.
Kao što vidite, svi se podaci u proračunskoj tablici sada razvrstavaju na temelju vrijednosti u stupcu Grad. Do sada nema redaka skrivenih. Pogledajmo sada potvrdne okvire na dnu dijaloškog okvira za filtriranje. U mom primjeru imam samo tri jedinstvene vrijednosti u stupcu Grad i ta se tri pojavljuju na popisu.
Otišao sam naprijed i neoznačio dva grada i ostavio jednu provjerenu. Sada imam samo 8 redaka podataka i ostali su skriveni. Možete jednostavno reći da gledate filtrirane podatke ako provjerite brojeve redaka s lijeve strane. Ovisno o tome koliko je redaka skriveno, vidjet ćete nekoliko dodatnih vodoravnih linija, a boja brojeva bit će plava.
Recimo da želim filtrirati na drugom stupcu kako bi se dodatno 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 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 Brojni filtri i zatim kliknite na Veći od, Kao što možete vidjeti, postoji i hrpa drugih mogućnosti.
Otvorit će se novi dijalog i ovdje možete upisati vrijednost filtra. Možete dodati i više kriterija pomoću funkcije AND ili OR. Možete reći da želite redove gdje je vrijednost veća od 2, a ne jednaka broju 5, na primjer.
Sad dolazim na samo pet redaka podataka: obitelji samo iz New Orleansa i s 3 ili više članova. Dovoljno jednostavno? Imajte na umu da možete lako izbrisati filtar na stupcu tako da kliknete padajući izbornik, a zatim kliknete Izbriši filtar iz "Naziv stupca" veza.
Tako je riječ o jednostavnim filtrima u Excelu. Vrlo su jednostavni za uporabu, a rezultati su prilično ravno naprijed. Pogledajmo sada složene filtre pomoću značajke Napredna filtriranje dijaloga.
Ako želite stvoriti naprednije filtre, morate upotrijebiti Napredna dijalog filtra. Na primjer, recimo da želim vidjeti sve obitelji koje žive u New Orleansu s više od 2 članova u njihovoj obitelji ILI sve obitelji u Clarksvilleu s više od 3 članova u njihovoj obitelji I samo one s a Edu kraj adrese e-pošte. Sada to jednostavno ne možete učiniti jednostavnim filtrom.
Da biste to učinili, moramo postaviti Excel listu malo drugačije. Idite naprijed i umetnite nekoliko redaka iznad skup podataka i kopirajte naslovne oznake točno u prvi redak, kao što je prikazano u nastavku.
Evo kako napredni filteri rade. Najprije morate unijeti kriterije u stupce na vrhu, a zatim kliknite gumb Napredna gumb ispod Sortiraj i filtriraj na Podaci kartica.
Pa što točno možemo upisati u te stanice? OK, pa počnimo s našim primjerom. Samo želimo vidjeti podatke iz New Orleansa ili Clarksvillea, pa ćemo ih upisati u stanice E2 i E3.
Kada upisujete vrijednosti na različite retke, to znači OR. Sada želimo New Orleansove obitelji 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 redu, to će biti AND operater. Isto vrijedi i za redak 3 gore. Konačno, želimo samo obitelji s .EDU koji završavaju e-adresu. Da biste to učinili, jednostavno upišite * Edu u oba D2 i D3. Simbol * označava bilo koji broj znakova.
Kada to učinite, kliknite bilo gdje u skupu podataka, a zatim kliknite gumb Napredna dugme. Popis rangaPolje e automatski će otkriti vaš skup podataka jer ste kliknuli na njega prije nego što kliknete gumb Napredno.Sada kliknite na mali mali gumb s desne strane Raspon kriterija dugme.
Odaberite sve od A1 do E3, a zatim ponovno kliknite na isti gumb da biste se vratili u dijaloški okvir Napredno filtriranje. Kliknite U redu, a vaši će podaci sada biti filtrirani!
Kao što vidite, sada imam samo 3 rezultate koji odgovaraju svim tim kriterijima. Imajte na umu da se oznake za raspon kriterija moraju točno podudarati s oznakama skupa podataka kako bi to funkcioniralo.
Očigledno možete stvoriti puno složenije upite pomoću ove metode, tako da se igrate s njim da biste dobili željene rezultate. Konačno, razgovarajmo o primjeni funkcija zbrajanja filtriranim podacima.
Recimo da želim sažeti broj članova obitelji na mojim filtriranim podacima, kako bih to mogao učiniti? Pa, očistimo filtar klikom na Čisto gumb na vrpci. Nemojte se brinuti, vrlo je lako primijeniti napredni filtar tako da jednostavno kliknete gumb Napredno i ponovo kliknete U redu.
Na dnu našeg skupa podataka dodajte pozvanu ćeliju ukupno a zatim dodajte funkciju zbroja kako biste saželi 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 filter i vidimo što se događa.
Joj! Umjesto prikazivanja točnog broja, 11, još uvijek vidim da je ukupno 78! Zašto je to? Pa, funkcija SUM ne zanemaruje skrivene retke, stoga još uvijek radi izračun pomoću svih redaka. Srećom, postoji nekoliko funkcija koje možete koristiti za zanemarivanje skrivenih redaka.
Prvi je SUBTOTAL, Prije no što koristimo bilo koju od ovih posebnih funkcija, morat ćete očistiti filtar, a zatim upisati tu funkciju.
Kada se filtar ispravi, idite naprijed i upišite = SUBTOTAL ( i trebali biste vidjeti padajući okvir s hrpom opcija. Pomoću ove funkcije prvo odaberite vrstu zbrajanja koja želite koristiti pomoću broja.
U našem primjeru želim koristiti IZNOS, pa ću upisati broj 9 ili jednostavno kliknuti na nju s padajućeg izbornika. Zatim upišite zarez i odaberite raspon ćelija.
Kada pritisnete Enter, trebali biste vidjeti vrijednost 78 koja je ista kao i prethodno. Međutim, ako ponovno primijenite filtar, vidjet ćemo 11!
Izvrsno! To je upravo ono što želimo. Sada možete prilagoditi filtre i vrijednost će uvijek odražavati samo one redove koji se trenutačno prikazuju.
Druga funkcija koja funkcionira gotovo jednako kao i funkcija SUBTOTAL AGREGAT, Jedina je razlika u tome što postoji još jedan parametar u funkciji AGGREGATE gdje morate navesti da želite ignorirati skrivene retke.
Prvi parametar je funkcija zbrajanja koju želite koristiti i kao s SUBTOTAL, 9 predstavlja SUM funkciju. Druga opcija je mjesto gdje morate upisati 5 da biste zanemarili skrivene retke. Posljednji parametar je isti i raspon je stanica.
Također možete pročitati članak sa sažetim funkcijama kako biste saznali kako koristiti detaljnije funkciju AGGREGATE i druge funkcije kao što su MODE, MEDIAN, AVERAGE itd.
Nadamo se da vam ovaj članak pruža dobru polaznu točku za izradu i upotrebu filtara u programu Excel. Ako imate bilo kakvih pitanja, slobodno pošaljite komentar. Uživati!
