Filtrování dat v Excelu. Jak vytvořit filtr na každý sloupec v Excelu? Proč filtry nemusí fungovat v Excelu

Filtrování dat v Excelu umožňuje zobrazit mezi velkým množstvím informací pouze to, co aktuálně potřebujete. Například když máte před sebou seznam tisíců produktů velkého hypermarketu, můžete si z něj vybrat pouze šampony nebo krémy a zbytek dočasně skrýt. V této lekci se naučíme, jak použít filtry na seznamy v Excelu, nastavit filtrování na několik sloupců najednou a odstranit filtry.

Pokud tabulka obsahuje velké množství dat, může být obtížné najít potřebné informace. Filtry se používají k zúžení množství dat zobrazených na listu aplikace Excel, což vám umožní zobrazit pouze informace, které potřebujete.

Použití filtru v Excelu

V následujícím příkladu vyfiltrujeme hardwarový protokol tak, aby se zobrazily pouze notebooky a tablety, které jsou k dispozici ke kontrole.

1. Vyberte libovolnou buňku v tabulce, například buňku A2.

Aby filtrování v Excelu fungovalo správně, musí list obsahovat řádek záhlaví, který se používá k pojmenování každého sloupce. V následujícím příkladu jsou data na listu uspořádána jako sloupce s nadpisy na řádku 1: ID #, Typ, Popis hardwaru atd.

Filtrování lze také použít výběrem příkazu Třídit a filtrovat tab Domov.

Použijte více filtrů v Excelu

Filtry v Excelu lze shrnout. To znamená, že můžete použít více filtrů na stejnou tabulku a zúžit výsledky filtrů. V předchozím příkladu jsme již filtrovali tabulku tak, aby zobrazovala pouze notebooky a tablety. Nyní je naším úkolem data ještě více zúžit a zobrazit pouze notebooky a tablety odeslané ke kontrole v srpnu.

Odstranění filtru v Excelu

Po použití filtru bude dříve nebo později nutné jej odstranit nebo odstranit, aby bylo možné filtrovat obsah jiným způsobem.

Chcete-li odebrat všechny filtry v tabulce aplikace Excel, klikněte na příkaz Filtr tab Data.

Filtrování dat aplikace Excel zahrnuje dva filtry: Automatický filtr a Rozšířený filtr. Předpokládejme, že máte velký soubor dat, ale z celého pole se potřebujete podívat nebo vybrat data, která se týkají konkrétního data, konkrétní osoby atd. Existují na to filtry. Pro ty, kteří se s tímto nástrojem setkají poprvé, filtr nesmaže, ale skryje záznamy, které nesplňují podmínky filtrování, které jste jim nastavili.

Prvním z nich je autofiltr určený pro nejzákladnější operace – zvýraznění záznamů s konkrétní hodnotou (například pouze zvýraznění záznamů souvisejících s LeBronem Jamesem), dat, která leží v určitém rozsahu (nebo nadprůměru či první desítky) nebo buněk/fontů určité barvy (mimochodem velmi pohodlné). Díky tomu se velmi snadno používá. Stačí vybrat data, která chcete filtrovat. Poté příkaz "Data" / "Filtr". V každé horní buňce horní tabulky se objeví pole se seznamem, je již snadné zjistit každý příkaz, je snadné jej zvládnout a vysvětlit, doufám, že není potřeba, pouze nuance použití automatického filtru:

1) Funguje pouze s nepřerušitelným rozsahem. Již není možné filtrovat dva různé seznamy na stejném listu.

2) Horní řádek tabulky je automaticky přiřazen jako nadpis a neúčastní se filtrování.

3) Můžete použít libovolné filtry v různých sloupcích, ale mějte na paměti, že v závislosti na pořadí, ve kterém jsou filtry aplikovány, nemusí být některé podmínky použity, protože předchozí filtry již skryly požadované položky. Žádný problém, tyto položky by byly stejně skryty, ale pokud chcete použít více sad filtrů, je nejlepší začít s podmínkami, které mají nejméně využití.

Praktické uplatnění v práci: například procházením tohoto seznamu najdete chybu nebo zkontrolujete data. Po použití automatického filtru můžete procházet celou tabulku jednu po druhé a postupně označovat již prohlížená data. Tlačítka "Vymazat" a "Znovu použít" určují vzhled tabulky po aplikaci podmínek. Po ukončení práce s tabulkou pak můžete vrátit písma zpět do původní podoby, aniž byste měnili samotná data. Mimochodem, někteří jsou zmateni tím, že všechny záznamy v tabulce po aplikaci jakýchkoli podmínek zmizí. Dobře, podívejte se blíže, nastavili jste podmínky, za kterých neexistují žádné záznamy, které tyto podmínky splňují. Fakt, že je tabulka filtrovaná, je modré zvýraznění čísel řádků tabulky.

Nyní přejdeme k pokročilému filtru. Od autofiltru se liší jemnějším doladěním, ale také větším výběrem při filtrování dat. Zejména:

1) Určuje tolik podmínek, kolik je potřeba.

2) Umožňuje vybrat buňky s jedinečnými (neopakujícími se) daty. To je často potřeba při práci s daty a tato možnost si s problémem skvěle poradí.

3) Umožňuje zkopírovat výsledek filtru na samostatné místo, aniž byste se dotkli hlavního pole.

Hlavním rozdílem při práci s tímto filtrem je tedy to, že nejprve musíme připravit tabulku podmínek. Dělá se to jednoduše. Nadpisy hlavní tabulky jsou zkopírovány a vloženy na místo, které nám vyhovuje (doporučuji nad hlavní tabulkou). V této tabulce by mělo být tolik řádků, aby po definování podmínek nevylezla na hlavní tabulku.

Příklady podmínek:

1) „L*“ – buňky začínající na L

2) „>5“ – data jsou větší než 5

Pokud odstraníte řádky z filtrované tabulky, budou odstraněny, aniž by s sebou vzali jejich sousedy. Tito. pokud je tabulka filtrovaná a zobrazuje řádky 26-29 a 31-25, výběr všech řádků a jejich smazání neodstraní řádek 30. To je pohodlné, osobně to při psaní maker často používám. Jakou výhodu to přináší - často dostáváme stoly, které je potřeba uvést do funkční podoby, tzn. odstranit například prázdné řádky. To, co děláme, je filtrování tabulky, aby se zobrazily pouze řádky, které nechceme, a pak vypuštění celé tabulky včetně záhlaví. Nepotřebné řádky a záhlaví jsou odstraněny, zatímco tabulka nemá žádné mezery a tvoří jeden rozsah. Řádek záhlaví lze přidat jednoduchými operacemi kopírování z předem připravené oblasti. Proč je to důležité při psaní maker? Není známo, od kterého řádku nechtěná data začínají a není jasné, od kterého řádku je začít mazat, odstranění celé tabulky pomáhá tento problém rychle vyřešit.

Pravděpodobně všichni uživatelé, kteří neustále pracují s Microsoft Excel, vědí o takové užitečné funkci tohoto programu, jako je filtrování dat. Ne každý si ale uvědomuje, že tento nástroj má i pokročilé funkce. Pojďme se podívat na to, co umí pokročilý filtr Microsoft Excel a jak jej používat.

Nestačí ihned spustit rozšířený filtr – k tomu musí být splněna ještě jedna podmínka. Dále popíšeme posloupnost akcí, které by měly být provedeny.

Krok 1: Vytvořte tabulku s kritérii výběru

Chcete-li nainstalovat rozšířený filtr, musíte nejprve vytvořit další tabulku s podmínkami výběru. Její klobouk je úplně stejný jako ten hlavní, který ve skutečnosti budeme filtrovat. Například jsme umístili doplňkovou tabulku nad hlavní a vybarvili její buňky oranžově. I když jej můžete umístit na libovolné volné místo a dokonce i na jiný list.

Nyní zadáme do doplňkové tabulky informace, které bude potřeba odfiltrovat z hlavní tabulky. V našem konkrétním případě jsme se ze seznamu vydaných mezd zaměstnancům rozhodli vybrat údaje o hlavním mužském osazenstvu za 25.7.2016.

Krok 2: Spuštění pokročilého filtru

Teprve po vytvoření doplňkové tabulky můžete přistoupit ke spuštění rozšířeného filtru.

Můžeme tedy konstatovat, že rozšířený filtr poskytuje více možností než běžné filtrování dat. Je ale třeba poznamenat, že práce s tímto nástrojem je stále méně pohodlná než se standardním filtrem.

Pokročilý filtr se tak nazývá, protože má pokročilé a skutečně neomezené a jedinečné možnosti ve srovnání s automatickým filtrem. Pokračujeme v seznámení s nástroji MS Excel pro práci s databázovými tabulkami v tomto článku...

Podíváme se blíže na to, jak funguje pokročilý filtr. Stejně jako automatický filtr je pokročilý filtr v Excelu navržen tak, aby skryl část informací z pohledu uživatele, aby se zbytek snadněji četl, nebo aby se vytiskly pouze vybrané databázové záznamy.

S pokročilým filtrem můžete filtrovat cokoli v tabulce! Limit tohoto nástroje je pouze fantazie uživatele!

Čtete čtvrtý příspěvek ze série článků o vytváření databází v SLEČNA vynikat a organizací zpracování informací.

Jak na to, si můžete přečíst v předchozích článcích seriálu.

Filtr č. 2 – pokročilý filtr!

Chcete-li začít, musíte vytvořit další tabulku nad databází, ve které uvedeme údaje, které jsou kritériem výběru pro rozšířený filtr. (Někdy je vhodnější umístit takový stůl na nový list.)

Pamatujete si, že když jsme vytvářeli ukázkovou databázi, nechali jsme nad tabulkou několik prázdných řádků s tím, že je budeme potřebovat později při analýze dat? Ten čas nadešel.

1. Otevřete soubor v MS Excel.

2. Při vytváření tabulky kritérií pro výběr pokročilého filtru doporučuji nejprve jednat podle vzoru, aniž byste přemýšleli o úkolech, které je třeba vyřešit. kopírovat Všechno záhlaví sloupců polí z buněk A7…F7 do buněk A1…F1 – sochor pro tabulku kritérií je připravena! Můžete začít hlavní práci.

Pro stabilní a bezchybný provoz filtru musí být mezi tabulkou výběrových kritérií a databázovou tabulkou alespoň jeden prázdný řádek!

Pokračujme ve studiu pokročilého filtru v Excelu, řešení praktických problémů na příkladu práce s databází DB2 "Výroba kovových konstrukcí podle sekce č. 2".

Úkol číslo 5:

Zobrazit informace o výrobě nosníků u všech zakázek za celé období.

1. Parametr filtrování - slovo "beam" zapíšeme do sloupce "Produkt" v horní tabulce výběrových kritérií.

2. Aktivujte („vstaňte myší“) libovolnou buňku v databázové tabulce – tím se automaticky vyplní pole „Počáteční rozsah“ v rozevíracím dialogovém okně „Pokročilý filtr“.

3. Rozšířený filtr v Excelu 2003 zapneme přes hlavní nabídku programu. Vyberte: "Data" - "Filtr" - "Pokročilý filtr".

4. V rozevíracím okně "Pokročilý filtr" vyplňte okna, jak je znázorněno na snímku obrazovky pod tímto textem.

Rozšířený filtr umožňuje filtrovat seznam na místě, ale může také zkopírovat výsledek filtrování na jiné místo určené uživatelem.

5. Výsledek pokročilého filtru je zobrazen na následujícím snímku obrazovky. Rozšířený filtr zobrazil všechny databázové záznamy, které obsahují slovo "beam" ve sloupci "Produkt" - úkol dokončen.

Velká a malá písmena neovlivňují výsledky filtrování!

Pravidla pro společnou „práci“ více výběrových podmínek

Pokročilý filtr v Excelu umožňuje filtrovat podle různých velmi složitých kombinací podmínek filtru. Můžete nastavit několik různých podmínek pro několik sloupců, můžete nastavit několik podmínek pro jeden sloupec nebo můžete nastavit parametry filtrování pomocí vzorce - možností je mnoho!

Existují však pouze dvě hlavní pravidla! Všechny ostatní případy jsou různými kombinacemi těchto dvou pravidel.

1. Podmínky výběru zveřejněny na jednom řádku v jednom nebo více různých sloupcích tabulky kritérií dejte pokročilému filtru pokyn, aby zobrazil řádky, pro které se provádějí současně všechny bez výjimky tyto podmínky. (Parametry výběru jsou spojeny logickými operátory "AND".)

2. Podmínky výběru zveřejněny na různých linkách v jednom nebo více sloupcích tabulky kritérií dejte pokročilému filtru pokyn, aby zobrazil všechny řádky, pro které aspoň jeden z těchto podmínek. (Parametry výběru jsou spojeny logickými operátory "OR".)

Pojďme si na příkladech ilustrovat působení výše uvedených pravidel a ukázat, jak funguje pokročilý filtr v Excelu při řešení složitých problémů.

Vzhledem k tomu, že jsme v předchozím příkladu podrobně prozkoumali posloupnost uživatelských akcí při povolení pokročilého filtru, níže budou zobrazeny pouze podmínky výběru a konečné výsledky filtrování.

Úkol číslo 6:

Filtrujte informace o deskách vážících méně než 0,1 tuny napříč celou databází.

V důsledku operace pokročilého filtru jsou zobrazeny všechny databázové desky s hmotností menší než 0,1 tuny (ilustrace pravidla č. 1).

Úkol číslo 7:

Zobrazte informace o všech tabulích dostupných v databázi, stejně jako o jakýchkoli jiných produktech vážících více než 1 tuna.

Problém je vyřešen - záznamy jsou zobrazeny pro všechny databázové štítky a pro všechny výrobky těžší než 1 tuna (ilustrace pravidla č. 2).

Chcete-li zrušit akci pokročilého filtru, musíte provést příkaz z hlavní nabídky programuvynikat"Data" - "Filtr" - "Zobrazit vše".

Upozorňuji na nutnost pečlivé kontroly správnosti zadání počátečního rozsahu databáze a rozsahu tabulky podmínek v rozbalovacím dialogovém okně "Pokročilý filtr"!

Konkrétně v posledním příkladu musíte zadat: "Rozsah podmínek: $A$1: $ F$3 »!

Pokud jsou v rozsahu podmínek nebo ve vaší databázi zcela prázdné řádky, pak nebude rozšířený filtr fungovat!

Výsledek.

Při zadávání hledaných výrazů můžete použít běžné zástupné znaky a matematické symboly:

• * - libovolný počet libovolných znaků
• ? - jakákoliv jedna postava
• = - rovná se
• < — меньше
• > - více
• <= — меньше или равно
• >= - větší nebo rovno
• <>- ne rovné

Pokročilý filtr v Excelu je flexibilní a informativní. Výběrová kritéria má uživatel vždy před očima ve formě tabulky výběrových kritérií, což je nepochybně velmi pohodlné.

Pokud existuje mnoho záznamů a ve skutečných databázích jsou tisíce jedinečných záznamů, pak se filtrování potřebných stává problematickým pomocí automatického filtru, protože musíte rolovat v rozevíracím seznamu. Pokročilý filtr tento nedostatek nemá.

"Ponořte se" do možností tohoto nástroje - stojí za to na to přijít! Zvýšená efektivita vaší práce znásobí čas strávený studiem!

Pokračování tématu ukládání a správa velkých objemů informací- v následujících článcích cyklu.

prosím respektování autorské dílo předplatit pro oznámení článků v okně umístěném na konci každého článku nebo v okně v horní části stránky!

Vážení čtenáři, dotazy a komentáře pište do komentářů dole na stránce.

Pomocí automatického filtru nebo vestavěných operátorů porovnání, jako je „větší než“ a „10 nejlepších“ v Excelu, zobrazte požadovaná data a zbytek skryjte. Po filtrování dat v rozsahu buněk nebo tabulce můžete filtr buď znovu použít, abyste získali aktuální výsledky, nebo můžete filtr vymazat a znovu zobrazit všechna data.

Pomocí filtrů můžete dočasně skrýt některá data v tabulce a zobrazit pouze data, která chcete.

Filtrování datového rozsahu

Filtrování dat v tabulce

Filtrovaná data zobrazí pouze řádky, které odpovídají zadané podmínce, a skryjí řádky, které nechcete zobrazit. Po filtrování dat můžete kopírovat, hledat, upravovat, formátovat, grafovat a tisknout podmnožinu filtrovaných dat, aniž byste je přesouvali nebo upravovali.

Můžete také filtrovat podle více sloupců. Filtry jsou aditivní, což znamená, že každý další filtr staví na aktuálním filtru a dále snižuje podmnožinu dat.

Poznámka: Při použití dialogového okna Vyhledávání pro vyhledání filtrovaných dat se prohledají pouze data, která se objeví v seznamu. Data, která nejsou zobrazena, nejsou prohledávána. Chcete-li najít všechna data, vymažte všechny filtry.

Více o filtrování

Dva typy filtrů

Pomocí automatického filtru můžete vytvořit dva typy filtrů: podle hodnoty seznamu nebo podle kritérií. Každý z těchto typů filtrů se vzájemně vylučuje pro každou oblast buněk nebo tabulku sloupců. Můžete například filtrovat podle seznamu čísel nebo podmínky, ale ne podle obojího; Můžete filtrovat podle ikony nebo vlastního filtru, ale ne obojího.

Opětovné použití filtru

Chcete-li zjistit, zda byl použit filtr, podívejte se na ikonu v záhlaví sloupce.

Opětovné použití filtru vede k odlišným výsledkům z následujících důvodů.

Data byla přidána, změněna nebo odstraněna v rozsahu buněk nebo sloupci tabulky.

hodnoty vrácené vzorcem se změnily a list byl přepočítán.

Nepoužívejte smíšené datové typy

Chcete-li dosáhnout nejlepších výsledků, neměli byste kombinovat datové typy, jako je text a číslo nebo čísla a data ve stejném sloupci, protože pro každý sloupec je k dispozici pouze jeden typ příkazu filtru. Pokud je použita směs datových typů, je zobrazený příkaz datovým typem, který je nejčastěji vyvoláván. Pokud například sloupec obsahuje tři hodnoty uložené jako číslo a čtyři uložené jako text, zobrazí se příkaz textové filtry .

Filtrování dat v tabulce

Při zadávání dat do tabulky se do záhlaví sloupců automaticky přidávají ovládací prvky filtrování.

Filtrování datového rozsahu

Pokud data nechcete formátovat jako tabulku, můžete také použít filtry na řadu dat.

Vyberte data, která chcete filtrovat. Pro dosažení nejlepších výsledků by sloupce měly obsahovat nadpisy.

Na kartě " data"stisknout tlačítko" Filtr".

Možnosti filtrování pro tabulky a rozsahy

Výběrem můžete použít obecný filtr Filtr nebo vlastní filtr, který závisí na typu dat. Například při filtrování čísel se položka zobrazí Číselné filtry, položka se zobrazí pro data Datumové filtry a pro text - Textové filtry. Použitím obecného filtru si můžete vybrat zobrazení požadovaných dat ze seznamu existujících, jak je znázorněno na obrázku:

Výběr možnosti Číselné filtry můžete použít jeden z následujících vlastních filtrů.