Filtrování dat v Excelu pomocí pokročilého filtru. Filtr v Excelu – Základy automatického filtru a Pokročilý filtr v Excelu

Filtrování dat v Excelu umožňuje zobrazit mezi velkým množstvím informací jen to, co v daný okamžik potřebujete. Když máte například před sebou seznam tisíců produktů ve velkém hypermarketu, můžete si z něj vybrat pouze šampony nebo krémy a zbytek dočasně skrýt. V tomto tutoriálu se naučíme, jak aplikovat filtry na seznamy v Excelu, nastavit filtrování na více sloupcích najednou a odstranit filtry.

Pokud vaše tabulka obsahuje velké množství dat, může být obtížné najít potřebné informace. Filtry se používají ke zúžení množství zobrazovaných dat Excelový list umožňuje zobrazit pouze informace, které potřebujete.

Použití filtru v Excelu

V následujícím příkladu vyfiltrujeme protokol zařízení tak, aby zobrazoval pouze notebooky a tablety, které lze zkontrolovat.

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ý slouží k pojmenování každého sloupce. V následujícím příkladu jsou data v listu uspořádána do sloupců s nadpisy na řádku 1: ID #, Typ, Popis zařízení a tak dále.

Výběrem příkazu můžete také použít filtrování Třídit a filtrovat v záložce Domov.

Použití 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 filtrování. 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ě zúžit a zobrazit pouze notebooky a tablety, které byly odeslány k ověření v srpnu.

Odstraňte filtr 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 Excel, klikněte na příkaz Filtr v záložce Data.

Microsoft Excel je všudypřítomný a pohodlný tabulkový nástroj. Široký funkčnost učinit tento program druhým nejoblíbenějším po MS Word mezi všemi kancelářské programy... Využívají ho ekonomové, účetní, vědci, studenti a zástupci dalších profesí, kteří potřebují zpracovat matematická data.

Jeden z nejvíce pohodlné funkce v tomto programu je filtrování dat. Podívejme se, jak nastavit a používat filtry MS excel.

Kde v Excelu filtry - jejich typy

Nalezení filtrů v tomto programu je snadné – musíte otevřít hlavní nabídku nebo stačí podržet klávesy Ctrl + Shift + L.

Jak nastavit filtr v Excelu

Základní funkce filtrování v Excelu:

výběr podle barvy: umožňuje třídit data podle písma nebo barvy výplně,
textové filtry v excelu: umožňují nastavit určité podmínky pro řetězce, například: menší než, větší než, rovno, nerovná se a další, a také nastavit logické podmínky - a, nebo,
číselné filtry: řazení podle číselných podmínek, například podprůměrné, prvních 10 a další,
manuální: výběr lze provést podle kritérií, která si sami zvolíte.

Je snadné je používat. Je nutné vybrat tabulku a vybrat v menu sekci s filtry a následně určit, podle jakého kritéria budou data odcloněna.

Jak používat pokročilý filtr v Excelu - jak jej přizpůsobit

Standardní filtr má významnou nevýhodu - abyste si zapamatovali, která kritéria výběru byla použita, musíte otevřít nabídku. A o to více způsobuje nepříjemnosti, když je nastaveno více než jedno kritérium. Z tohoto pohledu je pohodlnější rozšířený filtr, který se zobrazuje v samostatné tabulce nad daty.

VIDEONÁVOD

Postup nastavení:

Vytvořte tabulku s daty pro další práci s nimi. Neměl by obsahovat prázdné řádky.
Vytvořte tabulku s podmínkami výběru.
Spusťte rozšířený filtr.

Podívejme se na příklad přizpůsobení.
Máme tabulku se sloupci Produkt, Množství a Cena.

Například potřebujete seřadit řádky, jejichž názvy produktů začínají slovem „Nehty“. Této podmínce spadá několik řádků.

Tabulka s podmínkami bude umístěna v buňkách A1: A2. Důležité je uvést název sloupce, kde bude výběr probíhat (buňka A1) a samotné slovo pro výběr - Nehty (buňka A2).

Nejpohodlnější je umístit jej nad data nebo na stranu. Není to také zakázáno, ale není to vždy vhodné, protože pravidelně může být potřeba přidávat další řádky. Mezi dvěma tabulkami odsaďte alespoň jeden prázdný řádek.

Pak musíte:

vyberte kteroukoli z buněk,
otevřete "Pokročilý filtr" podél cesty: Data - Seřadit a filtrovat - Pokročilé,
zkontrolujte, co je uvedeno v poli "Source range" - tam by měla být celá tabulka s informacemi,
v "Rozsah podmínek" je nutné nastavit hodnoty buněk s podmínkou výběru, v tento příklad toto je rozsah A1: A2.

Po kliknutí na tlačítko "OK" se vyberou potřebné informace a v tabulce se objeví pouze řádky s požadovaným slovem, v našem případě je to "Nehty". Čísla zbývajících řádků se zbarví modře. Chcete-li zrušit zadaný filtr, stačí stisknout CTRL + SHIFT + L.

Je také snadné nastavit filtrování řádků obsahujících přesně slovo „Nehty“ bez ohledu na velikost písmen. Do rozsahu B1: B2 umístěte sloupec s novým kritériem výběru a nezapomeňte uvést záhlaví sloupce, ve kterém bude screening prováděn. V buňce B2 musíte zadat následující vzorec = "= Nehty".

vyberte kteroukoli z buněk tabulky,
otevřete "Pokročilý filtr",
zkontrolujte, zda je celá tabulka s údaji zahrnuta v "Původním rozsahu",
v "Rozsahu podmínek" označte B1: B2.

Po kliknutí na "OK" budou data odfiltrována.

Těch je nejvíc jednoduché příklady práce s filtry v excelu... V pokročilé verzi je vhodné nastavit další podmínky výběru, například výpadek s parametrem „OR“, výpadek s parametrem „Hřebíky“ a hodnotou ve sloupci „Množství“> 40.

Jak vytvořit filtr v Excelu podle sloupců

Informace v tabulce lze filtrovat podle jednoho nebo více sloupců. Vezměme si příklad tabulky se sloupci "Město", "Měsíc" a "Prodej".

Pokud potřebujete filtrovat data podle sloupce s názvy měst v abecedním pořadí, musíte vybrat kteroukoli z buněk v tomto sloupci, otevřít Seřadit a filtrovat a vybrat parametr LO. V důsledku toho se informace zobrazí s ohledem na první písmeno v názvu města.

Pro získání informací na opačném principu je potřeba použít parametr „YA“.

Je potřeba odškrtávat informace po měsících a město s velkým objemem prodeje by mělo být v tabulce výše než město s nižším objemem prodeje. Chcete-li problém vyřešit, musíte vybrat možnost Třídění v části Seřadit a filtrovat. V okně, které se zobrazí s nastavením, určete "Seřadit podle" - "Měsíc".

Dále je třeba přidat druhou úroveň řazení. Chcete-li to provést, vyberte v části "Řazení" - "Přidat úroveň" a zadejte sloupec "Prodej". Ve sloupci „Pořadí“ nastavení zadejte „Sestupně“. Po kliknutí na „OK“ proběhne výběr údajů podle zadaných parametrů.

VIDEONÁVOD

Proč filtry nemusí fungovat v Excelu

Uživatelé mají často potíže s prací s nástrojem, jako jsou filtry. Obvykle jsou spojeny s porušením pravidel pro používání určitých nastavení.

Problém s filtrováním podle data je jedním z nejčastějších. Vyskytuje se po načtení dat z účetního systému ve formě pole. Když se pokusíte filtrovat řádky podle sloupce obsahujícího data, nedojde k filtrování podle data, ale podle textu.

Řešení:

vyberte sloupec s daty,
otevřete kartu Excel v hlavní nabídce,
vyberte tlačítko "Buňky", v rozevíracím seznamu vyberte parametr "Převést text na datum".

Oblíbené chyby uživatelů při práci s tímto programem by také měly zahrnovat:

absence záhlaví sloupců (bez nich nebude fungovat filtrování, řazení a řada dalších důležitých parametrů),
přítomnost prázdných řádků a sloupců v tabulce s daty (to mate systém řazení, Excel vnímá informace jako dvě různé tabulky nezávislé na sobě),
umístění několika tabulek na jednu stránku (vhodnější je umístit každou tabulku na samostatný list),
umístění dat stejného typu do několika sloupců,
umístění dat na několik listů, například po měsících nebo letech (množství práce lze okamžitě vynásobit počtem listů s informacemi).

A další kritickou chybou, která neumožňuje plně využít možnosti aplikace Excel, je použití nelicencovaného produktu.

Není možné zaručit jeho správnou funkci, navíc se neustále objevují chyby. Pokud máte v úmyslu použít tento nástroj zpracovávání matematických informací průběžně, zakoupit plnou verzi programu.

Rozšířený filtr v aplikaci Excel vám poskytuje větší kontrolu nad daty v tabulkovém procesoru. Je náročnější na nastavení, ale mnohem efektivnější v akci.

Pomocí standardního filtru nemůže uživatel Microsoft Excel vyřešit zdaleka všechny úkoly. Neexistuje žádné vizuální zobrazení použitých podmínek filtrování. Není možné použít více než dvě výběrová kritéria. Nemůžete filtrovat duplicitní hodnoty, abyste zachovali pouze jedinečné záznamy. A samotná kritéria jsou schematická a jednoduchá. Funkčnost pokročilého filtru je mnohem bohatší. Pojďme se blíže podívat na jeho schopnosti.

Jak vytvořit pokročilý filtr v Excelu?

Pokročilý filtr umožňuje filtrovat data podle neomezené sady podmínek. Pomocí nástroje může uživatel:

nastavit více než dvě výběrová kritéria;
zkopírujte výsledek filtrování na jiný list;
nastavit podmínku jakékoli složitosti pomocí vzorců;
extrahovat jedinečné hodnoty.

Algoritmus pro použití pokročilého filtru je jednoduchý:

Vytvoříme tabulku s počátečními daty nebo otevřeme stávající. Například takto:
Vytvoříme tabulku podmínek. Vlastnosti: řádek záhlaví se zcela shoduje s "záhlavím" filtrované tabulky. Abyste předešli chybám, zkopírujte řádek záhlaví v původní tabulce a vložte jej na stejný list (strana, nahoře, dole) nebo na jiný list. Kritéria výběru zapíšeme do tabulky podmínek.
Přejděte na kartu "Data" - "Třídit a filtrovat" - "Upřesnit". Pokud mají být filtrované informace zobrazeny na jiném listu (NE tam, kde se nachází původní tabulka), musíte spustit rozšířený filtr z jiného listu.
V okně "Pokročilý filtr", které se otevře, vyberte způsob zpracování informací (na stejném listu nebo na jiném), nastavte počáteční rozsah (tabulka 1, příklad) a rozsah podmínek (tabulka 2, podmínky). Řádky záhlaví musí být zahrnuty do rozsahů.
Chcete-li zavřít okno "Pokročilý filtr", klikněte na OK. Vidíme výsledek.

Horní tabulka je výsledkem filtrování. Spodní deska s podmínkami je uvedena pro přehlednost vedle.

Jak používat pokročilý filtr v Excelu?

Chcete-li akci pokročilého filtru zrušit, umístěte kurzor kamkoli do tabulky a stiskněte kombinaci kláves Ctrl + Shift + L nebo "Data" - "Seřadit a filtrovat" - "Vymazat".

Pomocí nástroje "Advanced Filter" najdeme informace o hodnotách, které obsahují slovo "Set".

Kritéria doplníme do tabulky podmínek. Například takové:

V tomto případě program vyhledá všechny informace o zboží, jehož název obsahuje slovo „Set“.

K nalezení přesné hodnoty můžete použít znak "=". Přidejme do tabulky podmínek následující kritéria:

Excel interpretuje znak "=" jako signál: uživatel nyní nastaví vzorec. Aby program správně fungoval, musí řádek vzorců obsahovat záznam ve tvaru: = "= Nastavit oblast 6 cl."

Po použití "Pokročilého filtru":

Nyní vyfiltrujme původní tabulku podle podmínky „NEBO“ pro různé sloupce. Operátor OR je k dispozici také v nástroji Automatický filtr. Ale tam to lze použít v rámci jednoho sloupce.

V tabulce podmínek zadáme kritéria výběru: = "= Sada regionu 6 tř." (ve sloupci "Jméno") a = "

Poznámka: kritéria musí být zaznamenána pod příslušnými nadpisy na RŮZNÝCH řádcích.

Výsledek výběru:

Pokročilý filtr umožňuje použít vzorce jako kritérium. Podívejme se na příklad.

Výběr řádku s maximálním dluhem: = MAX (Tabulka1).

Dostaneme tedy výsledky jako po provedení několika filtrů na jednom listu Excelu.

Jak vytvořit více filtrů v Excelu?

Vytvořme filtr podle několika hodnot. K tomu zadáme do tabulky podmínek několik kritérií pro výběr dat najednou:

Aplikujme nástroj Rozšířený filtr:

Nyní z tabulky s vybranými údaji vytáhneme nové informace vybrané podle jiných kritérií. Například pouze zásilky za rok 2014.

Do tabulky podmínek zavedeme nové kritérium a použijeme nástroj pro filtrování. Původní rozsah je tabulka s daty vybranými podle předchozího kritéria. Takto se provádí vícesloupcový filtr.

Chcete-li použít více filtrů, můžete na nových listech vygenerovat více tabulek podmínek. Způsob implementace závisí na úkolu nastaveném uživatelem.

Jak vytvořit filtr v Excelu podle řádků?

Standardními způsoby - nic. Microsoft Excel vybere data pouze ve sloupcích. Proto je třeba hledat jiná řešení.

Zde jsou příklady pokročilých kritérií řetězců filtrů v aplikaci Excel:

Převést tabulku. Vytvořte například seznam tří sloupců ze tří řádků a aplikujte filtrování na transformovanou variantu.
Pomocí vzorců zobrazíte přesně ta data v řádku, který potřebujete. Udělejte například z nějakého indikátoru rozevírací seznam. A do sousední buňky zadejte vzorec pomocí funkce KDYŽ. Když je z rozevíracího seznamu vybrána určitá hodnota, objeví se vedle ní její parametr.

Abychom uvedli příklad, jak funguje filtr řádků v Excelu, vytvořte tabulku:

Vytvořme rozevírací seznam pro seznam produktů:

Vložte prázdný řádek nad tabulku s původními daty. Do buněk zadáme vzorec, který ukáže, ze kterých sloupců se informace přebírají.

Vedle rozevíracího seznamu buněk zadejte následující vzorec: Jeho úkolem je vybrat z tabulky ty hodnoty, které odpovídají určitému produktu

Stáhněte si příklady pokročilých filtrů

Tedy pomocí nástroje Dropdown Tool a vestavěného Excel funkce vybírá data v řádcích podle konkrétního kritéria.

Filtrování dat v Excelu vám umožní zobrazit ve sloupcích tabulky informace, které uživatele v konkrétním okamžiku zajímají. Výrazně zjednodušuje proces práce s velkými tabulkami. Budete moci ovládat jak data, která se budou ve sloupci zobrazovat, tak data vyloučená ze seznamu.

Pokud jste vytvořili tabulku v Excelu přes kartu "Vložit" - "Tabulka" nebo kartu "Domů" - "Formátovat jako tabulku", pak je v takové tabulce ve výchozím nastavení filtr povolen. Zobrazuje se ve formě šipky, která se nachází v horní buňce na pravé straně.

Pokud jste buňky pouze naplnili daty a poté je naformátovali jako tabulku, musí být filtr zapnutý. Chcete-li to provést, vyberte celý rozsah buněk tabulky, včetně záhlaví sloupců, protože tlačítko filtru je umístěno v horním sloupci, a pokud vyberete sloupec začínající buňkou s daty, nebude odkazovat na filtrovaná data. tohoto sloupce. Poté přejděte na kartu Data a klikněte na tlačítko Filtr.

V příkladu je šipka filtru v záhlaví, a to je správně - všechna data ve sloupci pod ní budou filtrována.

Pokud vás zajímá otázka, jak vytvořit tabulku v Excelu, klikněte na odkaz a přečtěte si článek na toto téma.

Nyní se podívejme, jak funguje filtr v Excelu. Jako příklad použijeme následující tabulku. Má tři sloupce: „Název produktu“, „Kategorie“ a „Cena“, použijeme na ně různé filtry.

Klikněte na šipku v horní buňce požadovaného sloupce. Zde uvidíte seznam neopakujících se dat ze všech buněk umístěných v tomto sloupci. U každé hodnoty bude zatržítko. Zrušte zaškrtnutí políček u hodnot, které chcete ze seznamu vyloučit.

Pro příklad ponechme v "Kategorii" pouze ovoce. Zrušte zaškrtnutí políčka „zelenina“ a klikněte na „OK“.

Pro ty sloupce tabulky, na které je filtr aplikován, se v horní buňce objeví odpovídající ikona.

Pokud potřebujete odstranit filtr dat v Excelu, klikněte na ikonu filtru v buňce a z nabídky vyberte „Odebrat filtr z (název sloupce)“.

V Excelu můžete vytvořit datový filtr různé způsoby... Rozlišujte mezi textovými a číselnými filtry. Použijí se odpovídajícím způsobem, pokud je do buněk sloupce zapsán text nebo čísla.

Aplikujme "Číselný filtr" na sloupec "Cena". Klikněte na tlačítko v horní buňce a vyberte příslušnou položku z nabídky. Z rozevíracího seznamu můžete vybrat podmínku, kterou chcete použít na data sloupce. Zobrazíme například všechny produkty, jejichž cena je nižší než „25“. Volíme „méně“.

Do odpovídajícího pole zadejte požadovanou hodnotu. Chcete-li filtrovat data, můžete použít více podmínek pomocí logického „A“ a „NEBO“. Při použití "AND" - musí být splněny obě podmínky, při použití "OR" - jedna z uvedených. Můžete například nastavit: "méně" - "25" - "AND" - "více" - "55". Z tabulky tedy vyřadíme produkty, jejichž cena se pohybuje v rozmezí od 25 do 55.

Tabulka s filtrem podle sloupce „Cena“ pod 25.

"Textový filtr" ve vzorové tabulce lze použít na sloupec "Název produktu". Klikněte na tlačítko filtru ve sloupci a vyberte z nabídky stejnojmennou položku. V rozevíracím seznamu, který se otevře, například použijeme „začíná na“.

Ponechme v tabulce produkty, které začínají na „ka“. V dalším okně do pole napíšeme: "ka *". Klikněte na "OK".

"*" Ve slově nahrazuje posloupnost znaků. Pokud například zadáte podmínku "obsahuje" - "s * l", slova stůl, židle, sokol a tak dále zůstanou. "?" nahradí jakýkoli znak. Například "b? Tón" - bochník, pupen. Pokud chcete zachovat slova skládající se z 5 písmen, napište "?????".

Filtrujte sloupec Název produktu.

Filtr lze přizpůsobit podle barvy textu nebo barvy buňky.

Udělejme buňku "Filtrovat podle barvy" pro sloupec "Název produktu". Klikněte na tlačítko filtru a z nabídky vyberte stejnojmennou položku. Vyberme červenou.

V tabulce zůstaly pouze červené produkty.

Filtrování podle barvy textu se aplikuje na sloupec "Kategorie". Necháme jen ovoce. Znovu vyberte červenou.

Nyní jsou v tabulce příkladů zobrazeny pouze červené plody.

Pokud chcete, aby byly viditelné všechny buňky tabulky, ale nejprve tam byla červená buňka, pak zelená, modrá a tak dále, použijte řazení v Excelu. Kliknutím na odkaz si můžete přečíst článek k tématu.

Filtry v Excelu vám pomohou pracovat s velkými tabulkami. Probrali jsme hlavní body, jak vyrobit filtr a jak s ním pracovat. Vyzvednout potřebné podmínky a data, která vás zajímají, ponechte v tabulce.

A pak umět rychle vyberte pouze data, která potřebujete, úkryt zbytečné struny tabulky. Filtr tedy umožňuje bez smazáníŘádky tabulky Excel je pouze dočasně skryjí.

Řádky tabulky skryté filtrem nezmizí. Můžete si podmíněně představit, že jejich výška se rovná nule (mluvil jsem o změně výšky řádků a šířky sloupců). Zbytek čar, neskrytých filtrem, je tedy jakoby „slepen. Výsledkem je filtrovaná tabulka.

Navenek vypadá tabulka s filtrem v Excelu stejně jako každá jiná, ale v její horní části se v každém sloupci objevují speciální šipky. Příklad tabulky s vloženým filtrem můžete vidět na obrázku níže.

Nyní se podívejme, jak vlastně filtry do tabulky přidat.

Jak vytvořit (aplikovat) filtr na excelovou tabulku

Je to velmi jednoduché! Řekněme, že máme tabulku se záhlavími. Vzhledem k tomu, že filtr na excelovou tabulku je uvalen na určitou část tabulky, pak právě tato část musí být nejprve zvýraznit... Excel sám nikdy neuhodne, co chcete, takže vybereme potřebné buňky v řádku se záhlavím tabulky, jak je znázorněno na obrázku níže.

[pro zvětšení klikněte na obrázek]

Poznámka:

Pokud vyberete řádek VŠECHNY, pak Excel automaticky nastaví filtr na VŠECHNY sloupce, ve kterých je alespoň něco. Pokud ve sloupci nic není, filtr se nepoužije.

Poté, co jste vybrali požadované sloupce, musíte skutečně nainstalovat filtr. Nejjednodušší způsob je přejít na kartu „Hlavní“ / „Filtrovat a třídit“ na pásu karet a kliknout na filtr. Zde je snímek obrazovky z Office 2010 v jiných verzích Excelový filtr tabulka je překryta stejným způsobem.

[pro zvětšení klikněte na obrázek]

Po kliknutí na toto tlačítko se do vybraných buněk přidá filtr.

Pokud nevyberete několik buněk, ale jednoduše kliknete na jednu, pak se filtr přidá na CELÝ řádek, jako byste jej vybrali.

V mém příkladu ze tří sloupců zobrazených na obrázku bude filtr přidán pouze do dvou, jejichž buňky byly dříve vybrány. Výsledek můžete vidět na snímku obrazovky níže.

[pro zvětšení klikněte na obrázek]

Uvedený příklad povolení filtru pro tabulku je nejjednodušší. Pojďme se podívat, jak to funguje.

Jak používat filtr v Excelu

Ihned po zapnutí filtru se tabulka nezmění (kromě šipek, které se zobrazují v záhlaví sloupců). Chcete-li odfiltrovat některá data, která potřebujete, klikněte na šipku ve sloupci, podle kterého chcete filtrovat. V praxi to vypadá jako na obrázku níže.

[pro zvětšení klikněte na obrázek]

Význam filtru:

je, že Excel ponechá pouze ty řádky tabulky, které v TOMTO (s nakonfigurovaným filtrem) sloupci obsahují buňku s vybranou hodnotou. Ostatní řádky budou skryté.

Chcete-li odstranit filtrování (bez odstranění filtru!) Stačí zaškrtnout všechna políčka. Stejný efekt se projeví po úplném odstranění filtru – tabulka se vrátí do původní podoby.

Použijte více filtrů na tabulku aplikace Excel

Filtry v excelové tabulce lze kombinovat. Vícenásobné filtry jsou aplikovány podle logického principu "AND". Co to znamená, podívejme se na příklad.

Pokud máme v tabulce více sloupců, pak můžeme nejprve nastavit filtr např. na první sloupec. V tomto případě bude část řádků skryta v souladu s podmínkou filtru. Poté nastavíme filtr v dalším sloupci. V tomto případě zbývající po prvním filtru struny budou dodatečně filtrovaný. Jednoduše řečeno, na každý řádek budou aplikovány 2 filtry najednou – na první sloupec a na druhý.

Při použití každého následujícího filtru se počet řádků zobrazených v tabulce, které splňují podmínky VŠECH filtrů, sníží. Postupným použitím několika filtrů tedy můžete rychle udělat malý výběr z obrovské tabulky tisíců řádků.

S výsledným vzorkem můžete provádět další operace. Tyto řádky můžete například zkopírovat a přenést do jiné tabulky. Případně můžete odfiltrovat ty řádky, které chcete smazat úplně! A pak, po odstranění filtru po jejich odstranění, dostanete tabulku vyčištěnou od zbytečných dat.

Nyní se podívejte na video, ve kterém ukazuji reálný příklad filtrování dat v ceníku o více než 15 000 řádcích.

Pojďme si to shrnout

Pokud musíte v Excelu pracovat s velkými tabulkami a potřebujete umět rychle vybrat určitá data, pak vám používání filtrů ušetří spoustu času. Čím větší tabulka, tím větší užitek z použití filtrů.

Tento příklad je z mého výukového programu Microsoft Excel. Použití filtrů s více obtížné podmínky výběr zvažuji ve třídě.

Stáhnout soubor, na jehož příkladu jsem zvažoval práci s filtry, můžete

Povolení automatického filtru:

Vyberte jednu buňku z rozsahu dat.
V záložce Data najít skupinu Třídit a filtrovat .
Klepněte na tlačítko Filtr .

Filtrování záznamů:

V horním řádku rozsahu vedle každého sloupce se objevila tlačítka se šipkami. Ve sloupci obsahujícím buňku, podle které chcete filtrovat, klikněte na tlačítko se šipkou. Otevře se seznam možné možnosti filtrace.

Vyberte podmínku filtrování.

Při výběru možnosti Filtry čísel zobrazí se následující možnosti filtrování: rovná se, více, menší, Prvních 10... atd.
Při výběru možnosti Textové filtry v kontextovém menu můžete označit možnost filtrování obsahuje..., začít s… atd.
Při výběru možnosti Datumové filtry možnosti filtrování - zítra, příští týden, minulý měsíc atd.
Ve všech výše uvedených případech obsahuje položku kontextové menu Vlastní filtr..., pomocí kterého můžete současně nastavit dvě podmínky výběru související vztahem A- současné splnění 2 podmínek, NEBO- splnění alespoň jedné podmínky.

Pokud byla data po filtrování změněna, filtrování nefunguje automaticky, takže je třeba spustit postup znovu kliknutím na tlačítko Opakovat ve skupině Třídit a filtrovat v záložce Data.

Zrušení filtrování

Chcete-li zrušit filtrování rozsahu dat, stačí znovu kliknout na tlačítko Filtr.

Chcete-li odstranit filtr pouze z jednoho sloupce, stačí kliknout na tlačítko se šipkou v prvním řádku a vybrat řádek v kontextové nabídce: Odstraňte filtr ze sloupce.

Chcete-li rychle odstranit filtrování ze všech sloupců, spusťte příkaz Průhledná v záložce Data

Plátky

Průřezy jsou stejné filtry, ale umístěné v samostatné oblasti a mají pohodlné grafické znázornění. Slicery nejsou součástí listu s buňkami, ale samostatným objektem, sadou tlačítek, umístěným na listu Excelu. Použití slicerů nenahrazuje AutoFilter, ale díky pohodlné vizualizaci usnadňuje filtrování: všechna použitá kritéria jsou viditelná současně. Od roku 2010 byly do Excelu přidány řezy.

Krájení

V Excelu 2010 lze průřezy použít pro kontingenční tabulky a v roce 2013 můžete vytvořit průřez pro jakoukoli tabulku.

Chcete-li to provést, postupujte takto:

Vyberte jednu buňku v tabulce a vyberte kartu Konstruktér .
Ve skupině Servis(nebo na kartě Vložit ve skupině Filtry) tlačítko pro výběr Vložte plátek .

Vyberte plátek.
Na kartách pásu karet Možnosti vybrat skupinu Styly řezů, obsahující 14 standardních stylů a možnost vytvořit si vlastní uživatelský styl.

Vyberte tlačítko s vhodným stylem formátování.

Chcete-li řez odstranit, vyberte jej a stiskněte klávesu Vymazat.

Pokročilý filtr

Rozšířený filtr poskytuje další možnosti. Umožňuje kombinovat několik podmínek, umístit výsledek do jiné části listu nebo na jiný list atd.

Nastavení podmínek filtrování

V dialogovém okně Pokročilý filtr vyberte možnost pro záznam výsledků: seznam filtrů na místě nebo zkopírujte výsledek na jiné místo .

Upřesněte Původní sortiment zvýrazněním původní tabulky spolu se záhlavím sloupců.
Upřesněte Rozsah podmínek zvýrazněním rozsahu podmínek pomocí kurzoru, včetně buněk se záhlavím sloupců.
V poli uveďte případně místo s výsledky Umístěte výsledek do rozsahu zvýrazněním buňky v rozsahu umístíte výsledky filtrování.
Pokud chcete vyloučit duplicitní položky, zaškrtněte políčko v řádku Pouze unikátní záznamy .

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 potřebujete zobrazit 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 neodstraní, ale skryje záznamy, které nesplňují podmínky filtrování, které jim zadáte.

Prvním z nich je automatický filtr, určený pro nejjednodušší operace - zvýraznění záznamů s konkrétní hodnotou (například pouze zvýraznění pouze záznamů souvisejících s LeBronem Jamesem), dat ležících v určitém rozsahu (nebo nadprůměru nebo první desítky) nebo buněk / písma určité barvy (mimochodem velmi pohodlné). V souladu s tím je jeho použití velmi snadné. Stačí si vybrat data, která chcete filtrovat. Poté příkaz "Data" / "Filtr". V každé horní buňce horní tabulky se objeví seznam, tam je již snadné porozumět každému příkazu, je snadné se naučit a vysvětlit, doufám, že není potřeba další, pouze nuance použití automatického filtru:

1) Funguje pouze s nezlomným dosahem. Již nebude možné filtrovat dva různé seznamy na jednom listu.

2) Nejvyšší řádek tabulky je automaticky přiřazen jako záhlaví 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. předchozí filtry již skryly požadované položky. Zde není žádný problém, tyto položky by byly stejně skryty, ale pokud chcete používat více sad filtrů, je lepší začít s podmínkami, které mají nejméně uplatnění.

Praktické použití v práci: například pracujete na tomto seznamu, abyste našli chybu nebo zkontrolovali 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. Poté, po ukončení práce s tabulkou, můžete vrátit písma zpět do původního vzhledu, 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í. No, podívejte se blíže, máte zadané podmínky, za kterých neexistují žádné záznamy splňující tyto podmínky. Fakt, že tabulka je filtrována, spočívá v tom, že čísla řádků tabulky jsou zvýrazněna modře.

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) Nastaví 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 volba 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 pro nás vhodné místo (doporučuji nad hlavní tabulkou). V této tabulce by mělo být tolik řádků, abyste se po určení podmínek nedostali do hlavní tabulky.

Příklady podmínek:

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

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

Pokud odstraníte řádky z filtrované tabulky, budou odstraněny, aniž byste s sebou vzali sousedy. Tito. pokud je tabulka filtrovaná a zobrazuje řádky 26-29 a 31-25, výběr všech řádků a jejich smazání nesmaže řádek 30. To je pohodlné, osobně to často používám při psaní maker. V čem je výhoda - často dostáváme stoly, které je potřeba uvést do funkční podoby, tzn. odstranit, například prázdné řádky. Co děláme: na tabulku aplikujeme filtr, zobrazující pouze ty řádky, které nepotřebujeme, poté smažeme celou tabulku včetně záhlaví. Nepotřebné řádky a nadpisy jsou odstraněny a tabulka neobsahuje mezery a je tvořena jedním rozsahem. Řádek záhlaví lze přidat pomocí jednoduchých operací kopírování z předem připravené oblasti. Proč je to důležité při psaní maker? Není známo, ze kterého řádku nechtěná data začínají a není jasné, ze kterého řádku začít mazat, odstranění celé tabulky pomáhá tento problém rychle vyřešit.