Jak filtrovat data v aplikaci Excel

Nedávno jsem napsal článek o tom, jak používat souhrnné funkce v aplikaci Excel, aby bylo možné snadno shrnout velké množství dat, ale tento článek vzal v úvahu všechna data na listu. Co když se chcete podívat pouze na podmnožinu dat a shrnout podmnožinu dat?

V aplikaci Excel můžete vytvořit filtry ve sloupcích, které budou skrýt řádky, které neodpovídají vašemu filtru. Kromě toho můžete také použít speciální funkce v aplikaci Excel, abyste shrnuli data pouze pomocí filtrovaných dat.

V tomto článku vás provedu kroky pro vytváření filtrů v aplikaci Excel a také pomocí vestavěných funkcí pro shrnutí filtrovaných dat.

Vytvořit jednoduché filtry v aplikaci Excel

V aplikaci Excel můžete vytvářet jednoduché filtry a složité filtry. Začněme s jednoduchými filtry. Při práci s filtry byste měli mít vždy nahoře jeden řádek, který se používá pro popisky. Není to požadavek mít tento řádek, ale usnadňuje práci s filtry.

Nahoře, mám nějaké falešné údaje a chci vytvořit filtr na Město sloupec. V aplikaci Excel je to velmi snadné. Pokračujte a klikněte na Data kartu na pásu karet a potom klepněte na tlačítko Filtr tlačítko. Nemusíte vybírat data na listu ani kliknout na první řádek.

Když kliknete na Filtr, každý sloupec v prvním řádku bude mít automaticky přidáno malé rozevírací tlačítko.

Nyní pokračujte a klikněte na rozevírací šipku ve sloupci Město. Uvidíte několik různých možností, které vysvětlím níže.

V horní části můžete rychle třídit řádky hodnotami ve sloupci Město. Všimněte si, že při třídění dat se přesune celý řádek, nikoli pouze hodnoty ve sloupci Město. Tím zajistíte, že vaše data zůstanou nedotčena stejně jako dříve.

Je také vhodné přidat sloupec na přední straně nazvaný ID a číslo od jednoho k mnoha řádkům, které máte ve svém listu. Tímto způsobem můžete vždy třídit podle sloupce ID a získat data zpět ve stejném pořadí, v jakém byla původně, pokud je to pro vás důležité..

Jak vidíte, všechna data v tabulce jsou nyní tříděna na základě hodnot ve sloupci Město. Zatím 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 zobrazují v seznamu.

Šel jsem dopředu a nezaškrtnul dvě města a nechal jsem jednu. Nyní mám k dispozici pouze 8 řádků dat a ostatní jsou skryté. Můžete snadno zjistit, že se díváte na filtrovaná data, pokud zkontrolujete čísla řádků na levé straně. V závislosti na tom, kolik řádků je skrytých, uvidíte několik dalších vodorovných čar a barva čísel bude modrá.

Řekněme, že chci filtrovat na druhém sloupci, aby se dále snížil počet 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 rozevíracího seznamu ve sloupci C a uvidíte stejné zaškrtávací políčka pro každou jedinečnou hodnotu ve sloupci. V tomto případě však chceme kliknout Počet filtrů a potom klepněte na tlačítko Větší než. Jak vidíte, existuje i spoustu dalších možností.

Objeví se nové dialogové okno, ve kterém můžete zadat hodnotu filtru. Můžete také přidat více než jedno kritérium s funkcí AND nebo OR. Dalo by se říci, že chcete například řádky, jejichž hodnota je větší než 2, například 5.

Nyní jsem jen na 5 řádků dat: rodiny pouze z New Orleans a 3 nebo více členů. Snadno? Všimněte si, že můžete snadno vymazat filtr ve sloupci kliknutím na rozevírací nabídku a klepnutím na tlačítko Vymazat filtr z názvu sloupce odkaz.

Takže to je asi pro jednoduché filtry v Excelu. Jsou velmi snadno použitelné a výsledky jsou velmi přímé. Nyní se podívejme na komplexní filtry pomocí Pokročilý filtru.

Vytvořit rozšířené filtry v aplikaci Excel

Pokud chcete vytvořit pokročilejší filtry, musíte použít Pokročilý dialogového okna filtru. Řekněme například, že jsem chtěl vidět všechny rodiny, které žijí v New Orleans s více než 2 členy ve své rodině NEBO všechny rodiny v Clarksville s více než 3 členy ve své rodině A pouze ty s a .EDU e-mailovou adresu. Nemůžete to udělat s jednoduchým filtrem.

K tomu musíme nastavit Excel list trochu jinak. Pokračujte a vložte několik řádků nad sadu dat a zkopírujte štítky nadpisů přesně do prvního řádku, jak je uvedeno níže.

Tady je, jak fungují pokročilé filtry. Musíte nejprve zadat svá kritéria do sloupců nahoře a potom kliknout na tlačítko Pokročilý pod Seřadit a filtrovat na Data kartu.

Co přesně tedy můžeme do těchto buněk napsat? OK, tak začněme s naším příkladem. Chceme vidět pouze data z New Orleans nebo Clarksville, takže je zapište do buněk E2 a E3.

Když zadáváte hodnoty do různých řádků, znamená to OR. Nyní chceme rodiny New Orleans s více než dvěma členy a rodinami Clarksville s více než 3 členy. Chcete-li to provést, zadejte > 2 v C2 a > 3 v C3.

Protože> 2 a New Orleans jsou na stejném řádku, bude to operátor AND. Totéž platí pro řádek 3 výše. Nakonec chceme pouze rodiny s e-mailovou adresou .EDU. K tomu stačí zadat * .edu do D2 a D3. Symbol * znamená libovolný počet znaků.

Jakmile to uděláte, klikněte kdekoli v datové sadě a pak klikněte na Pokročilý tlačítko. Seznam RangPole e automaticky zobrazí vaši datovou sadu, protože jste na ni klikli před kliknutím na tlačítko Upřesnit. Nyní klikněte na malé malé tlačítko vpravo Rozsah kritérií tlačítko.

Vyberte vše od A1 do E3 a pak znovu klikněte na stejné tlačítko, abyste se dostali zpět do dialogu Rozšířený filtr. Klepněte na tlačítko OK a data by měla být nyní filtrována!

Jak vidíte, nyní mám jen 3 výsledky, které odpovídají všem těmto kritériím. Všimněte si, že popisky pro rozsah kritérií musí odpovídat přesně štítkům datové sady, aby to fungovalo.

Pomocí této metody můžete samozřejmě vytvořit mnohem složitější dotazy, takže si s ní můžete hrát, abyste získali požadované výsledky. Konečně, pojďme mluvit o použití součtových funkcí pro filtrovaná data.

Shrnutí filtrovaných dat

Teď řekněme, že chci shrnout počet členů rodiny na mých filtrovaných datech, jak bych to udělal? No, pojďme vymazat náš filtr kliknutím na Průhledná tlačítko na pásu karet. Nedělejte si starosti, je snadné znovu použít rozšířený filtr pouhým kliknutím na tlačítko Upřesnit a opětovným klepnutím na tlačítko OK.

V dolní části naší datové sady přidejme buňku Celkový a pak přidejte součtovou funkci, aby se sčítali celkové členy rodiny. V mém příkladu jsem právě napsal = SUM (C7: C31).

Když se tedy podívám na všechny rodiny, mám celkem 78 členů. Pojďme se tedy vrátit a znovu použít náš rozšířený filtr a zjistit, co se stane.

Jejda! Namísto zobrazení správného čísla, 11, stále vidím celkem 78! Proč je to? No, funkce SUM ignoruje skryté řádky, takže stále dělá 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 svůj filtr vymazat a pak zadat funkci.

Jakmile je filtr vymazán, pokračujte a zadejte = SUBTOTAL ( a měli byste vidět rozevírací pole se spoustou možností. Pomocí této funkce nejprve vyberete typ funkce sumace, kterou chcete použít pomocí čísla.

V našem příkladu chci použít SOUČET, tak bych napsal číslo 9 nebo na něj klikněte z rozevíracího seznamu. Potom zadejte čárku a vyberte rozsah buněk.

Když stisknete enter, měli byste vidět, že hodnota 78 je stejná jako dříve. Pokud však filtr znovu použijete, uvidíme 11!

Vynikající! To je přesně to, co chceme. Nyní můžete upravit filtry a hodnota bude vždy odrážet pouze řádky, které se právě zobrazují.

Druhá funkce, která funguje téměř stejně jako funkce SUBTOTAL AGREGÁT. Jediný rozdíl spočívá v tom, že existuje další parametr ve funkci AGGREGATE, kde musíte zadat, že chcete skryté řádky ignorovat..

První parametr je součtová funkce, kterou chcete použít, a jako u SUBTOTAL, 9 představuje funkci SUM. Druhou možností je místo, kde musíte vložit 5, abyste ignorovali skryté řádky. Poslední parametr je stejný a je rozsahem buněk.

Můžete si také přečíst můj článek o souhrnných funkcích, abyste se dozvěděli, jak používat funkci AGGREGATE a další funkce jako MODE, MEDIAN, AVERAGE atd..

Doufejme, že tento článek vám dává dobrý výchozí bod pro vytváření a používání filtrů v aplikaci Excel. Máte-li jakékoli dotazy, napište nám komentář. Užívat si!