Ako filtrovať dáta v programe Excel

Nedávno som napísal článok o tom, ako používať súhrnné funkcie v programe Excel na ľahké zhrnutie veľkého množstva údajov, ale tento článok zohľadnil všetky údaje na pracovnom liste. Čo ak sa chcete pozrieť iba na podmnožinu údajov a zhrnúť podmnožinu údajov?

V aplikácii Excel môžete vytvárať filtre v stĺpcoch, ktoré skryjú riadky, ktoré sa nezhodujú s vaším filtrom. Okrem toho môžete v programe Excel použiť aj špeciálne funkcie na zhrnutie údajov iba pomocou filtrovaných údajov.

V tomto článku vás prevediem kroky na vytvorenie filtrov v programe Excel a tiež pomocou vstavaných funkcií na zhrnutie filtrovaných údajov.

Vytvorte jednoduché filtre v programe Excel

V programe Excel môžete vytvoriť jednoduché filtre a zložité filtre. Začnime s jednoduchými filtrami. Pri práci s filtrami by ste mali mať vždy jeden riadok v hornej časti, ktorý sa používa pre štítky. Nie je to požiadavka mať tento riadok, ale práca s filtrami je trochu jednoduchšia.

Hore, mám nejaké falošné údaje a chcem vytvoriť filter na veľkomesto stĺpec. V programe Excel je to naozaj jednoduché. Pokračujte a kliknite na tlačidlo údaje kartu na karte a potom kliknite na tlačidlo filter Tlačidlo. Nemusíte vyberať údaje na hárku alebo kliknúť v prvom riadku.

Keď kliknete na Filter, každý stĺpec v prvom riadku bude automaticky pridaný malý rozbaľovací tlačidlo vpravo.

Teraz pokračujte a kliknite na rozbaľovaciu šípku v stĺpci Mesto. Uvidíte niekoľko rôznych možností, ktoré vysvetlím nižšie.

V hornej časti môžete rýchlo zoradiť všetky riadky podľa hodnôt v stĺpci Mesto. Upozorňujeme, že keď zoradíte údaje, presunie sa celý riadok, nielen hodnoty v stĺpci Mesto. Tým sa zabezpečí, že vaše údaje zostanú neporušené rovnako ako predtým.

Tiež je dobré pridať stĺpec na samom fronte pod názvom ID a číslovať ho z jedného do mnohých riadkov, ktoré máte vo svojom pracovnom hárku. Týmto spôsobom môžete vždy zoradiť podľa stĺpca ID a získať svoje údaje späť v tom istom poradí, aký bol pôvodne, ak je pre vás dôležité.

Ako vidíte, všetky údaje v tabuľke sú teraz zoradené na základe hodnôt v stĺpci Mesto. Zatiaľ nie sú žiadne riadky skryté. Poďme sa pozrieť na zaškrtávacie políčka v spodnej časti dialógu filtra. V mojom príklade mám v stĺpci Mesto iba tri jedinečné hodnoty a tieto tri sa zobrazia v zozname.

Išiel som dopredu a nekontroloval dve mestá a nechal jeden kontrolovaný. Teraz mám len 8 riadkov údajov a ostatné sú skryté. Môžete ľahko povedať, že sa pozeráte na filtrované údaje, ak skontrolujete čísla riadkov vľavo. V závislosti od toho, koľko riadkov sa skrýva, uvidíte niekoľko ďalších horizontálnych riadkov a farba čísel bude modrá.

Teraz povedzme, že chcem filtrovať druhý stĺpec, aby sa ďalej znížil počet výsledkov. V stĺpci C mám celkový počet členov v každej rodine a chcem vidieť len výsledky pre rodiny s viac ako dvoma členmi.

Pokračujte a kliknite na rozbaľovaciu šípku v stĺpci C a v stĺpci uvidíte rovnaké začiarkavacie polia pre každú jedinečnú hodnotu. V tomto prípade však chceme kliknúť Počet filtrov a potom kliknite na tlačidlo Väčší než. Ako môžete vidieť, existuje aj veľa ďalších možností.

Zobrazí sa nové dialógové okno a tu môžete zadať hodnotu pre filter. Môžete tiež pridať viac ako jedno kritérium s funkciou AND alebo OR. Môžete povedať, že chcete riadky, kde hodnota je väčšia ako 2 a nie je rovná 5, napríklad.

Teraz idem len na 5 riadkov údajov: rodiny iba z New Orleans a 3 alebo viacerými členmi. Ešte ľahké? Všimnite si, že môžete jednoducho vymazať filter v stĺpci kliknutím na rozbaľovací zoznam a potom kliknutím na Vymazať filter z "Názov stĺpca" odkaz.

Takže to je o jednoduché filtre v programe Excel. Sú veľmi ľahko použiteľné a výsledky sú celkom rovno. Teraz poďme sa pozrieť na zložité filtre pomocou pokročilý filtrovanie.

Vytvorte pokročilé filtre v programe Excel

Ak chcete vytvoriť pokročilejšie filtre, musíte použiť pokročilý dialógu filtra. Povedzme napríklad, že som chcel vidieť všetky rodiny, ktoré žijú v New Orleans s viac ako 2 členmi v ich rodine OR všetky rodiny v Clarksville s viac ako 3 členmi v ich rodine A len tie, ktoré majú .EDU koncovú e-mailovú adresu. Teraz to nemôžete urobiť jednoduchým filtrom.

Ak to chcete urobiť, musíme nastaviť list Excel trochu inak. Pokračujte a vložte niekoľko riadkov nad sadu údajov a skopírujte popisy nadpisov presne do prvého riadku, ako je uvedené nižšie.

Tu je, ako fungujú pokročilé filtre. Najprv musíte zadať kritériá do stĺpcov v hornej časti a potom kliknúť na tlačidlo pokročilý tlačidlo pod Zoradiť & Filter na údaje pútko.

Takže, čo presne môžeme napísať do týchto buniek? OK, tak začnime s naším príkladom. Chceme iba zobraziť údaje z New Orleans alebo Clarksville, a preto ich zadáme do buniek E2 a E3.

Keď zadávate hodnoty na rôznych riadkoch, znamená to OR. Teraz chceme rodiny v New Orleans s viac ako dvomi členmi a Clarksville rodiny s viac ako 3 členmi. Ak to chcete urobiť, zadajte príkaz > 2 v C2 a > 3 v C3.

Keďže> 2 a New Orleans sú na rovnakom riadku, bude to operátor AND. To isté platí pre riadok 3 uvedený vyššie. Na záver chceme len rodiny s koncovou e-mailovou adresou .EDU. Ak to chcete urobiť, zadajte ho * .edu do oboch D2 a D3. Symbol * znamená ľubovoľný počet znakov.

Akonáhle to urobíte, kliknite na ľubovoľný bod v súprave a potom kliknite na pokročilý Tlačidlo. Zoznam radovPo kliknutí na pole po kliknutí na tlačidlo Pokročilé pole pole automaticky zistí vašu dataset. Teraz kliknite na malé malé tlačidlo vpravo od Rozsah kritérií gombík.

Vyberte všetko od A1 do E3 a potom opäť kliknite na toto tlačidlo, aby ste sa vrátili do dialógového okna Rozšírený filter. Kliknite na tlačidlo OK a vaše údaje by sa mali teraz filtrovať!

Ako môžete vidieť, teraz mám iba 3 výsledky, ktoré zodpovedajú všetkým týmto kritériám. Upozorňujeme, že štítky pre rozsah kritérií musia presne zodpovedať štítkom pre súbor údajov, aby to fungovalo.

Môžete zrejme vytvoriť oveľa komplikovanejšie dotazy pomocou tejto metódy, takže si s ňou zahrajte, aby ste získali požadované výsledky. Nakoniec, poďme sa rozprávať o použití súčtových funkcií na filtrované údaje.

Zhrnutie filtrovaných údajov

Teraz povedzme, že chcem zhrnúť počet členov rodiny na filtrované údaje, ako by som to mohol urobiť? Pošlite filter tak, že kliknete na jasný v páse. Nebojte sa, je jednoduché aplikovať rozšírený filter jednoduchým kliknutím na tlačidlo Rozšírené a opätovným kliknutím na tlačidlo OK.

V dolnej časti našej množiny údajov pridáme bunku nazvanú totálnej a potom pridajte sumovú funkciu na zhrnutie všetkých členov rodiny. V mojom príklade som práve napísal = SUM (C7: C31).

Takže ak sa pozriem na všetky rodiny, mám 78 členov celkom. Teraz poďme a opäť použite náš Pokročilý filter a uvidíme, čo sa stane.

Och! Namiesto zobrazenia správneho čísla 11, stále vidím, že celková hodnota je 78! Prečo to je? Funkcia SUM ignoruje skryté riadky, takže stále robí výpočet pomocou všetkých riadkov. Našťastie existuje niekoľko funkcií, ktoré môžete použiť na ignorovanie skrytých riadkov.

Prvým je SUBTOTAL. Skôr ako použijeme niektorú z týchto špeciálnych funkcií, budete chcieť vymazať filter a potom zadať funkciu.

Po odstránení filtra, pokračujte a napíšte = SUBTOTAL ( a mali by ste vidieť rozbaľovacie pole sa objaví s banda možností. Pomocou tejto funkcie najprv vyberiete typ sumárnej funkcie, ktorú chcete použiť pomocou čísla.

V našom príklade chcem použiť SUM, tak by som napísal číslo 9 alebo jednoducho kliknite na ňu z rozbaľovacieho zoznamu. Potom zadajte čiarku a vyberte rozsah buniek.

Keď stlačíte kláves Enter, mali by ste vidieť, že hodnota 78 je rovnaká ako predtým. Ak však teraz znovu použijete filter, uvidíme 11!

Výborne! To je presne to, čo chceme. Teraz môžete upraviť filtre a hodnota bude vždy odrážať iba riadky, ktoré sa momentálne zobrazujú.

Druhá funkcia, ktorá funguje skoro presne rovnaká ako funkcia SUBTOTAL CELKOVÁ. Jediný rozdiel je v tom, že existuje iný parameter vo funkcii AGGREGATE, kde musíte špecifikovať, že chcete ignorovať skryté riadky.

Prvý parameter je funkcia sumácie, ktorú chcete použiť, a rovnako ako pri SUBTOTAL, 9 reprezentuje funkciu SUM. Druhou možnosťou je miesto, kde musíte skopírovať 5 a ignorovať skryté riadky. Posledný parameter je rovnaký a je rozsah buniek.

Môžete tiež prečítať môj článok o súhrnných funkciách, aby ste sa dozvedeli, ako používať funkciu AGGREGATE a ďalšie funkcie ako MODE, MEDIAN, AVERAGE atď..

Dúfajme, že tento článok vám poskytuje dobrý východiskový bod pre vytváranie a používanie filtrov v programe Excel. Ak máte akékoľvek otázky, neváhajte a uverejnite komentár. Užite si to!