Nedávno jsem napsal článek o jak používat souhrnné funkce v aplikaci Excel, abych snadno shrnul velké množství dat, ale tento článek vzal v úvahu všechna data na pracovním listu. Co když se chcete podívat pouze na podmnožinu dat a shrnout podmnožinu dat?
V aplikaci Excel můžete vytvářet filtry ve sloupcích, které skryjí řádky, které neodpovídají vašemu filtru. Kromě toho můžete také použít speciální funkce v aplikaci Excel pro shrnutí dat za použití pouze filtrovaných dat.
V tomto článku vás provede kroky pro vytváření filtrů v aplikaci Excel a také pomocí vestavěných
Vytvořit jednoduché filtry v aplikaci Excel
V aplikaci Excel můžete vytvářet jednoduché filtry a složité filtry. Začneme s jednoduchými filtry. Při práci s filtry byste vždy měli mít jeden řádek nahoře, který se používá pro štítky. Není to požadavek mít tento řádek, ale pracuje s filtry trochu jednodušší.
chcete vytvořit filtr ve sloupci Město. V aplikaci Excel je to opravdu snadné. Pokračujte a klikněte na kartu Datav karet a klikněte na tlačítko Filtr. Není nutné vybrat data na listu nebo kliknout v prvním řádku.
Když kliknete na filtr, v prvním řádku bude automaticky přidáno malé rozbalovací tlačítko vpravo.
Nyní pokračujte a klikněte na rozbalovací šipku Městský sloupec. Uvidíte pár různých možností, které vysvětlím níže.
V horní části můžete rychle řadit všechny řádky hodnoty ve sloupci Město. Mějte na paměti, že při řazení dat přesunete celý řádek, nejen hodnoty ve sloupci Město. To zajistí, že vaše data zůstanou neporušená stejně jako předtím.
Také je vhodné přidat sloupec přímo na frontu s názvem ID a číslo z jednoho do mnoha řádků, které máte ve svém pracovního listu. Tímto způsobem můžete vždy seřadit podle sloupce ID a získat data zpět ve stejném pořadí, jaký byl původně, pokud je to pro vás důležité.
Jak vidíte, všechna data v tabulce jsou nyní seřazeny podle hodnot ve sloupci Město. Dosud nejsou žádné řádky skryté. Nyní se podívejme na zaškrtávací políčka v dolní části dialogového okna filtru. V mém příkladu mám ve sloupci Město pouze tři jedinečné hodnoty a ty tři se zobrazí v seznamu.
Jel jsem dopředu a nekontrolován dvě města a jeden z nich zkontroloval. Nyní mám pouze 8 řádků dat a ostatní jsou skryté. Můžete snadno zjistit, že se díváte na filtrované údaje, pokud zkontrolujete čísla řádků vlevo. V závislosti na tom, kolik řádků jsou skryté, uvidíte pár dalších vodorovných čar a barva čísel bude modrá.
Nyní řekněme, že chci filtrovat druhý sloupec, aby se dále zmenšilo číslo výsledků. Ve sloupci C mám celkový počet členů v každé rodině a chci vidět výsledky pouze pro rodiny s více než dvěma členy.
Pokračujte a klikněte na šipku rozbalovacího seznamu ve sloupci C a ve sloupci uvidíte stejné zaškrtávací políčka pro každou jedinečnou hodnotu. V tomto případě však chceme kliknout na Filtry čísela potom kliknout na Větší než. Jak vidíte, existuje i řada dalších možností.
Zobrazí se nové dialogové okno a zde můžete zadat hodnotu pro filtr. Můžete také přidat více kritérií s funkcí AND nebo OR. Řeknete-li, že chcete řádky, kde je hodnota větší než 2 a není rovno 5.
Nyní jsem na pouze 5 řádků dat: rodiny pouze z New Orleans as 3 a více členy. Je to dost snadné? Všimněte si, že můžete snadno vymazat filtr ve sloupci kliknutím na rozbalovací nabídku a klepnutím na odkaz Vymazat filtr z názvu sloupce.
Takže to jde o jednoduché filtry v aplikaci Excel. Jsou velmi snadné a výsledky jsou poměrně jednoduché. Nyní se podívejme na složité filtry pomocí dialogového okna Pokročiléfiltry.
Vytvořte pokročilé filtry v aplikaci Excel
Chcete-li vytvořit pokročilé filtry, musíte použít dialog Pokročiléfiltru. Řekněme například, že chci vidět všechny rodiny žijící v New Orleans s více než 2 členy ve své rodině NEBOvšechny rodiny v Clarksville s více než 3 členy v jejich rodině Astrong>pouze ty, které mají koncovou e-mailovou adresu .EDU. Teď to nemůžete udělat jednoduchým filtrem.
Chcete-li to provést, potřebujeme nastavit list aplikace Excel trochu jinak. Pokračujte a vložte několik řádků nad sadu dat a zkopírujte popisky nadpisů přesně do prvního řádku, jak je znázorněno níže.
Zde je, jak pokročilé filtry fungují. Nejprve musíte zadat kritéria do sloupců v horní části a poté kliknout na tlačítko Upřesnitv části Seřadit & amp; Filtrna kartě Data.
OK, tak začněte s příkladem. Chceme pouze zobrazit data z New Orleans nebo Clarksville, takže je zadejte do buněk E2 a E3.
řádky, to znamená NEBO. Nyní chceme rodiny z New Orleans s více než dvěma členy a Clarksville rodiny s více než 3 členy. Chcete-li to provést, zadejte & gt; 2v C2 a & gt; 3v C3.
Protože & gt; 2 a New Orleans jsou ve stejném řádku, bude to operátor AND. Totéž platí pro řádek 3 výše. Nakonec chceme pouze rodiny s koncovou e-mailovou adresou .EDU. Chcete-li to provést, stačí zadat *. Edudo formátu D2 a D3. Symbol * znamená libovolný počet znaků.
Jakmile to provedete, klikněte kdekoli v datovém souboru a klikněte na . Pole Seznam raneke automaticky zjistí vaši datovou množinu, protože jste k ní klikli předtím, než kliknete na tlačítko Pokročilé. Nyní klikněte na malé tlačítko vpravo od tlačítka Rozsah kritérií.
Vyberte vše od A1 do E3 a znovu klikněte na toto tlačítko, abyste se vrátili do dialogu Advanced Filter. Klikněte na OK a vaše data by měla být nyní filtrována!
Jak vidíte, nyní mám pouze 3 výsledky, které odpovídají všem těmto kritériím. Všimněte si, že štítky pro rozsah kritérií musí přesně odpovídat štítkům datové sady, aby to fungovalo.
Pomocí této metody můžete zřejmě vytvořit mnohem složitější dotazy, a proto si s nimi hrajte abyste získali požadované výsledky. Konečně, řekneme o použití součtových funkcí na filtrované údaje.
Shrnutí filtrovaných dat
Nyní řekněme, že chci shrnout počet členů rodiny na filtrované údaje, jak bych máš to dělat? Vymažte náš filtr kliknutím na tlačítko Vyčistitna kazetě. Nebojte se, je snadné znovu použít rozšířený filtr kliknutím na tlačítko Upřesnit a opětovným kliknutím na tlačítko OK.
dolní část naší datové sady, přidejte buňku nazvanou Celkema pak přidejte souhrnnou funkci, která shrne všechny členy rodiny. V mém příkladu jsem zadal = SUM (C7: C31).
rodiny, mám celkem 78 členů. Teď jdeme dál a znovu použijeme náš Pokročilý filtr a uvidíme, co se stane.
Whoops! Místo toho, abych zobrazil správné číslo, 11, stále vidím, že celkový počet je 78! Proč je to tak? Funkce SUM ignoruje skryté řádky, takže stále provádí výpočet pomocí všech řádků. Naštěstí existuje několik funkcí, které můžete použít k ignorování skrytých řádků.
První je SUBTOTAL. Než použijeme některou z těchto speciálních funkcí, budete chtít vymazat filtr a poté zadat funkci.
Jakmile je filtr vymazán, pokračujte a zadejte = SUBTOTAL (a měli byste vidět rozbalovací rámeček, který se zobrazí s množstvím možností. Pomocí této funkce nejdříve vybíráte typ sumační funkce, kterou chcete použít s číslem.
V našem příkladu chci použít SUM, tak bych z rozbalovacího okna zadejte číslo 9 nebo z něj klikněte přímo na něj. Pak zadejte čárku a vyberte rozsah buněk.
21
Když stisknete klávesu enter, uvidíte, že hodnota 78 je stejná jako předtím. Pokud však znovu použijete filtr, uvidíme 11!
s>22
Vynikající! To je přesně to, co chceme.Nyní můžete nastavit filtry a hodnota bude vždy odrážet pouze ty řádky, které se momentálně zobrazují. druhá funkce, která funguje téměř přesně stejně jako funkce SUBTOTAL, je AGGREGATE. že je jiný parametr ve funkci AGGREGATE, kde musíte specifikovat, že chcete ignorovat skryté řádky.
je funkce sčítání, kterou chcete použít, a stejně jako u SUBTOTAL, 9 představuje funkci SUM. Druhou možností je, kde musíte zadat 5 pro ignorování skrytých řádků. Poslední parametr je stejný a je rozsah buněk.
Můžete také přečíst můj článek o souhrnných funkcích, abyste se dozvěděli, jak použijte funkci AGGREGATE a další funkce jako MODE, MEDIAN, AVERAGE atd. podrobněji.
článek poskytuje dobrý výchozí bod pro vytváření a používání filtrů v aplikaci Excel. Máte-li jakékoli dotazy, neváhejte a vložte komentář. Užijte si!