Filtrování dat v Excelu – snadné a komplexní

Příkaz Filtr aplikace Excel může pomoci filtrovat data v rozsahu nebo tabulce tak, aby zobrazovala pouze data, která potřebujete, a ostatní skryla. Jeho integrované operátory můžete použít ke snadnému filtrování čísel, textů nebo dat, jako je filtrování všech čísel, která jsou větší nebo rovna určitému číslu, filtrování textu začíná, končí nebo obsahuje konkrétní znak nebo slovo nebo zobrazuje pouze řádky, kde datum splatnosti je před nebo po určitém datu atd. Po filtrování dat v rozsahu nebo v tabulce, pokud dojde ke změně dat, můžete buď znovu použít filtr, abyste získali nová data, nebo vymazat filtr, aby se zobrazila všechna data.

V tomto kurzu si ukážeme, jak přidat, použít nebo odebrat filtry v aplikaci Excel. Kromě toho vás provedeme návodem, jak vylepšit funkci filtru pro řešení složitějších problémů aplikace Excel.

1. Jak přidat filtr v aplikaci Excel

Chcete-li filtrovat data v rozsahu nebo tabulce, musíte nejprve přidat filtr dat. Tato část poskytuje 3 způsoby přidání filtru v aplikaci Excel.

1.1 Příkaz Filtr na kartě Data

Vyberte buňky v oblasti nebo tabulce, do kterých chcete přidat filtr, klikněte na „Data“ > „Filtr“.

1.2 Příkaz Filtr na kartě Domů

Vyberte buňky v oblasti nebo tabulce, do kterých chcete přidat filtr, klikněte na "Domů" > "Seřadit a filtrovat" > "Filtrovat".

1.3 Přidejte filtr pomocí zástupce

Vyberte buňky v oblasti nebo tabulce, do kterých chcete přidat filtr, a poté stiskněte klávesy "Ctrl" + "Shift" + "L".

Po použití jedné z výše uvedených operací můžete vidět, že do záhlaví sloupců vybraných buněk jsou přidány rozevírací šipky.

2. Jak použít filtr v aplikaci Excel (jedno nebo více kritérií)

Po přidání filtru jej musíte ručně použít. Tato část vám ukáže, jak použít filtr v jednom nebo více sloupcích v aplikaci Excel.

2.1 Použít filtr na jeden sloupec (jedno kritérium)

Pokud chcete použít filtr pouze na jeden sloupec, například data filtru ve sloupci C, jak je znázorněno na následujícím snímku obrazovky. Přejděte do tohoto sloupce a postupujte následovně.

1. 1) Klikněte na rozevírací šipku v záhlaví sloupce.
2. 2) Podle potřeby zadejte podmínku filtru.
3. 3) Kliknutím na tlačítko „OK“ zahájíte filtrování. Viz snímek obrazovky:
   

Nyní je filtr aplikován na sloupec C. Všechna data, která splňují kritéria filtru, budou zobrazena a zbytek bude skryt.

Po použití filtru můžete vidět, že rozevírací šipka se změní na ikonu filtru .

Je velmi ohleduplné, že když podržíte kurzor nad ikonou filtru, zadaná kritéria filtru se zobrazí jako tip na obrazovce, jak je znázorněno na následujícím obrázku obrazovky. Pokud tedy zapomenete kritéria, která jste pro filtr zadali, jednoduše najeďte kurzorem na ikonu filtru.

2.2 Použít filtr s více kritérii na více sloupců (více kritérií)

2.2.1 Použít filtr s více kritérii na více sloupců jeden po druhém

Pokud chcete použít filtr na více sloupců s více kritérii, opakujte výše uvedenou metodu na více sloupců jeden po druhém.

Po použití filtru na více sloupců můžete vidět, že rozbalovací šipky ve filtrovaných sloupcích jsou proměněny na ikony filtrů.

2.2.2 Současně aplikujte filtr s více kritérii na více sloupců

S výše uvedenou metodou musíte použít filtr na sloupce jeden po druhém a nejdůležitějším bodem je, že tato metoda podporuje pouze kritéria "AND". Zde pro vás představíme metody nejen použít filtr na více sloupců současně, ale také použít kritéria „AND“ a „OR“.

Předpokládejme, že máte datovou tabulku, jak je zobrazena na níže uvedeném snímku obrazovky, a chcete filtrovat data z více sloupců na základě více kritérií: „Produkt = AAA-1“ a „Objednávka > 80“ nebo „Celková cena > 10000 XNUMX“. Zkuste to provést jedním z následujících způsobů.

2.2.2.1 Aplikovat filtr na více sloupců pomocí funkce Rozšířený filtr

Funkce Pokročilý filtr vám může pomoci tento problém vyřešit, postupujte prosím krok za krokem.

1. Nejprve vytvořte kritéria v listu, jak je znázorněno na následujícím obrázku obrazovky.

Poznámka: Pro kritéria AND umístěte hodnoty kritérií do různých buněk stejného řádku. A umístěte hodnotu kritéria NEBO na druhý řádek.

2. Kliknutím na „Data“ > „Upřesnit“ zapněte funkci „Pokročilý filtr“.

3. V dialogovém okně "Advanced Filter" proveďte konfiguraci následovně.

Nyní jsou sloupce filtrovány současně na základě daných kritérií, jak je ukázáno na následujícím snímku obrazovky.

2.2.2.2 Jednoduše použijte filtr na více sloupců pomocí úžasného nástroje

Protože kritéria filtru „A“ a „NEBO“ nejsou ve výše uvedené metodě snadno ovladatelná, velmi doporučujeme funkci „Super Filter“ v „Kutools pro Excel“. Pomocí této funkce můžete v Excelu snadno použít filtr na více sloupců s kritérii AND i OR.

1. Po instalaci Kutools pro Excel klikněte na „Kutools Plus“ > „Super filtr“.

Poté se na pravé straně listu zobrazí podokno „Super filtr“.

Ve výchozím nastavení jsou v podokně "Superfiltr" přidány dvě prázdné skupiny kritérií se vztahem NEBO mezi nimi. A vztah mezi kritérii ve stejné skupině je AND. Vztah mezi různými skupinami můžete změnit podle svých potřeb.

2. V podokně "Super Filter" nakonfigurujte kritéria filtru následovně.

2.1) Zaškrtněte políčko "Specifikováno" a klikněte na tlačítko pro výběr původního rozsahu nebo tabulky, kterou budete filtrovat;

2.2) V rozevíracím seznamu "Vztah" zvolte "Nebo";

3.3) Klikněte na první prázdný řádek v první skupině a poté zadejte kritéria podle své potřeby;

Tipy: První rozevírací seznam je pro záhlaví sloupců, druhý je pro typy filtrů (z tohoto rozbalovacího seznamu můžete vybrat "Text", "Číslo", "Datum", "Rok", "Formát textu" atd.), třetí je pro typy kritérií a poslední textové pole je pro hodnotu kritéria.

Jako příklad, který jsme uvedli výše, zde vyberu „Produkt“ > „Text“ > „Rovná se“ odděleně ze tří rozevíracích seznamů a do textového pole vepíšu „AAA-1“. Viz snímek obrazovky:

2.4) Pokračujte ve vytváření zbývajících kritérií a kritéria "nebo" je třeba vytvořit v nové skupině. Jak ukazuje níže uvedený snímek obrazovky, všechna kritéria jsou vytvořena. Prázdné kritérium můžete ze skupin odstranit.

2.5) Kliknutím na tlačítko „Filtr“ zahájíte filtrování.

Nyní se v původním rozsahu dat zobrazí pouze shodná data a zbytek se skryje. Viz snímek obrazovky:

Tipy: Pomocí této užitečné funkce můžete přidat další kritéria do skupiny, přidat další skupiny, uložit aktuální nastavení filtru jako scénář pro budoucí použití a tak dále. Je to nepostradatelný nástroj, který může ušetřit spoustu pracovního času a zvýšit efektivitu práce.

Klepnutím získáte další informace o této funkci.

3. Jak používat filtr v aplikaci Excel

V této části se naučíte, jak pomocí příkazu filter filtrovat různé druhy datových typů, jako jsou text, čísla, data a formáty.

3.1 Filtrování textových hodnot

3.1.1Filtrování textových buněk se specifickými kritérii (začátek, konec, obsah atd.)

Ve skutečnosti vestavěný operátor filtrů – „Textové filtry“ poskytuje mnoho užitečných kritérií pro snadné filtrování textu. Předpokládejme, že chcete filtrovat buňky, které začínají konkrétním znakem, například J, proveďte to následovně.

1. Přidejte filtr do záhlaví sloupce původního rozsahu dat. Kliknutím zjistíte, jak přidat filtr do záhlaví sloupce.

2. Kliknutím na rozevírací šipku v buňce záhlaví rozbalíte nabídku filtru.

3. Klikněte na "Textové filtry" > "Začíná na".

4. V dialogovém okně "Vlastní automatický filtr" zadejte konkrétní znak (zde zadávám J) do textového pole a klikněte na "OK".

Tipy: Podle potřeby můžete přidat další kritéria vztahu „A“ nebo „Nebo“.

Nyní všechny buňky začínají znakem J, jsou zobrazeny ve sloupci D, jak je znázorněno na následujícím obrázku obrazovky.

3.1.2 Filtr s rozlišováním velkých a malých písmen

Zdá se, že je snadné filtrovat textové buňky na základě konkrétních kritérií pomocí integrovaného operátoru filtru. Jelikož však funkce Filtr nepodporuje filtrování textu s rozlišováním velkých a malých písmen, jak můžeme v aplikaci Excel udělat filtr citlivý na velká a malá písmena? Tato část vám ukáže metody, jak toho dosáhnout.

3.1.2.1 Filtrovat určitý text s rozlišením velkých a malých písmen podle vzorce a příkazu Filtr

Předpokládejme, že chcete filtrovat všechna velká písmena určitého textu, například „TEXTOVÉ NÁSTROJE“ ve sloupci B, proveďte prosím následující.

1. Vytvořte pomocný sloupec vedle původního rozsahu dat (zde volím jako pomocný sloupec sloupec D). Zadejte níže uvedený vzorec do druhé buňky a poté stiskněte klávesu "Enter". Vyberte buňku s výsledkem, přetáhněte její "AutoFill Handle" dolů, abyste získali další výsledky.

=EXACT(B2,UPPER(B2))

Poznámka: Tento vzorec pomáhá identifikovat buňky s velkými a malými písmeny. Pokud buňka obsahuje všechna velká písmena, výsledek bude „PRAVDA“, jinak bude výsledek „NEPRAVDA“.

2. Vyberte sloupec B a D (sloupec C bude vybrán, nevadí), klikněte na "Data" > "Filtr", chcete-li k nim přidat filtry.

3. Přejděte do sloupce B (sloupec obsahuje texty, které budete filtrovat) a proveďte následující konfiguraci.

Nyní jsou ve sloupci B zobrazena pouze velká a malá písmena „textových nástrojů“.

4. Klikněte na rozbalovací šipku ve sloupci D, zrušte zaškrtnutí políčka „Vybrat vše“, zaškrtněte políčko vedle položky „PRAVDA“ a poté klikněte na tlačítko „OK“.

Poté jsou všechna velká textová „textová data“ ve sloupci B filtrována, jak je znázorněno na následujícím obrázku obrazovky.

3.1.2.2 Snadno filtrujte buňky s rozlišováním velkých a malých písmen pomocí úžasného nástroje

Pokud chcete filtrovat pouze všechna velká nebo malá písmena ve sloupci, zde doporučujeme funkci "Speciální filtr" v "Kutools pro Excel". Díky této funkci bude veškerý text s velkými nebo malými písmeny snadno filtrován pouze několika kliknutími.

1. Vyberte rozsah sloupců, ve kterých budete filtrovat texty, a poté klikněte na „Kutools Plus“ > „Speciální filtr“ > „Speciální filtr“.

2. V dialogovém okně "Speciální filtr" proveďte níže uvedená nastavení.

3. Poté se objeví dialogové okno "Kutools pro Excel", které vám řekne, kolik buněk bylo nalezeno a bude filtrováno, klikněte na tlačítko "OK".

Nyní jsou všechny velké nebo malé buňky okamžitě filtrovány, jak je znázorněno na následujícím obrázku obrazovky.

Klepnutím získáte další informace o této funkci.

3.1.3 Filtrování podle délky textu

Pokud chcete filtrovat buňky podle délky textu, například k filtrování buněk s délkou textu rovnou 10 znakům, co byste měli dělat? Zde vám níže uvedené tři metody mohou udělat laskavost.

3.1.3.1 Filtrujte buňky podle délky textu pomocí příkazu Filtr

Ve skutečnosti má příkaz Filtr vestavěný operátor k vyřešení tohoto problému, postupujte následovně.

1. Vyberte rozsah buněk, které mají být filtrovány (zde volím B1:B27), a poté do tohoto rozsahu sloupců přidejte filtr kliknutím na „Data“ > „Filtr“.

2. Klikněte na rozbalovací šipku v záhlaví sloupce a poté klikněte na „Textové filtry“ > „Vlastní filtr“. Viz snímek obrazovky:

3. V V dialogovém okně "Vlastní automatický filtr" vyberte kritéria jako rovná se, zadejte 10 otazníků (?) jako vzor do textového pole a klikněte na tlačítko "OK".

Tipy: Těchto 10 otazníků znamená, že bude odpovídat textovému řetězci, jehož délka je 10.

Nyní jsou všechny buňky, jejichž délka textového řetězce je 10 (včetně mezer), okamžitě filtrovány.

3.1.3.2 Filtrování buněk podle délky textu pomocí vzorce a příkazu Filtr

Kromě toho můžete pomocí funkce LEN vypočítat délku textového řetězce každé buňky a poté pomocí příkazu Filtr filtrovat potřebné buňky délky textu na základě vypočítaného výsledku.

1. Vytvořte pomocný sloupec sousedící s původním rozsahem dat. Zadejte do něj níže uvedený vzorec a poté stiskněte klávesu "Enter". Vyberte buňku s výsledkem a poté přetáhněte její "AutoFill Handle" dolů, abyste získali další výsledky.

=LEN(B2)

Nyní dostáváte délku textu každé buňky v zadaném sloupci.

2. Vyberte pomocný sloupec (včetně záhlaví), klikněte na „Data“ > „Filtr“ a přidejte do něj filtr.

3. Klikněte na rozevírací šipku, zrušte zaškrtnutí políčka „Vybrat vše“, čímž zrušíte výběr všech položek, a poté pouze zaškrtněte políčko vedle čísla 10 a nakonec klikněte na tlačítko „OK“.

Nyní jsou všechny buňky, jejichž délka textového řetězce je 10 (včetně mezer), okamžitě filtrovány.

3.1.3.3 Jednoduše filtrujte buňky podle délky textu pomocí úžasného nástroje

Zde doporučujeme nástroj "Speciální filtr" z "Kutools pro Excel", který vám pomůže snadno filtrovat buňky podle délky textu v Excelu.

1. Vyberte rozsah sloupců, ve kterém budete filtrovat buňky na základě určité délky textu, klikněte na „Kutools Plus“ > „Speciální filtr“ > „Speciální filtr“. Viz snímek obrazovky:

2. V dialogovém okně "Speciální filtr" proveďte konfiguraci následovně.

3. Objeví se dialogové okno "Kutools for Excel", které vám řekne, kolik buněk bylo nalezeno a bude filtrováno, pokračujte kliknutím na "OK".

Poté jsou všechny buňky s délkou textového řetězce rovnou 10 filtrovány, jak je znázorněno na následujícím obrázku obrazovky.

3.2 Filtr číselných hodnot

V aplikaci Excel je také velmi snadné filtrovat čísla pomocí příkazu Číselné filtry.

Předpokládejme, že chcete filtrovat buňky s čísly mezi 15000 20000 a XNUMX XNUMX ve sloupci (jako je sloupec C, jak je zobrazen níže uvedený snímek obrazovky), můžete toho dosáhnout následovně.

1. Vyberte rozsah sloupců obsahující čísla, která budete filtrovat, kliknutím na "Data" > "Filtr" přidejte filtr.

2. Po přidání filtru proveďte konfiguraci následujícím způsobem.

Tipy: Protože chci filtrovat buňky s čísly mezi 15000 a 20000, zde zadávám 15000 a 20000 samostatně do textových polí.

Nyní jsou buňky s čísly mezi 15000 20000 a XNUMX XNUMX filtrovány, jak je znázorněno na následujícím obrázku obrazovky.

3.3 Filtrovat data

Ve výchozím nastavení vestavěná funkce Date Filters poskytuje mnoho společných kritérií pro filtrování dat. Jak vidíte, neexistuje žádná vestavěná možnost filtrování dat podle dne v týdnu, víkendu nebo pracovního dne. Tato část vás naučí, jak těchto operací dosáhnout.

3.3.1 Filtrování dat podle dne v týdnu nebo víkendů

Předpokládejme, že máte tabulku dat, jak je zobrazeno na níže uvedeném snímku obrazovky, pokud chcete filtrovat data podle dne v týdnu nebo víkendů, použijte jednu z níže uvedených metod.

3.3.1.1 Filtrování dat podle dne v týdnu nebo víkendů pomocí vzorce a příkazu Filtr

V této části použijete funkci WEEKDAY pro výpočet dne v týdnu každého data a poté použijete filtr pro filtrování určitého dne v týdnu nebo víkendů, jak potřebujete.

1. Do prázdné buňky (v tomto případě D2) zadejte níže uvedený vzorec a stiskněte klávesu "Enter". Chcete-li použít tento vzorec, vyberte buňku výsledku a poté přetáhněte "Popisovač automatického vyplňování" přes buňky níže.

=WEEKDAY(A2)

Tip:

Poznámka: Jak můžete vidět, vzorec vrací čísla od "1" do "7", což označuje den v týdnu od "neděle" do "sobota" (1 je pro neděli, 7 je pro sobotu).

2. Vyberte celé výsledky vzorce (včetně buňky záhlaví), klikněte na "Data" > "Filtr".

3. Klikněte na rozbalovací šipku a zrušte zaškrtnutí políčka „Vybrat vše“.

Poté jsou všechny víkendy nebo buňky určitého dne v týdnu filtrovány. Viz snímek obrazovky:

3.3.1.2 Snadno filtrujte data podle dne v týdnu nebo víkendů pomocí úžasného nástroje

Pokud vám výše uvedená metoda nevyhovuje, zde doporučujeme nástroj "Speciální filtr" aplikace "Kutools for Excel". Pomocí této funkce můžete snadno filtrovat buňky obsahující libovolný den v týdnu pouze několika kliknutími.

1. Vyberte buňky obsahující data, která chcete filtrovat na základě určitého dne v týdnu.

2. Klikněte na „Kutools Plus“ > „Speciální filtr“ > „Speciální filtr“.

3. V dialogovém okně "Speciální filtr" proveďte konfiguraci následovně.

4. Poté se objeví dialogové okno "Kutools for Excel" s počtem nalezených buněk a budou filtrovány, pokračujte kliknutím na "OK".

Nyní jsou všechny buňky o víkendu nebo libovolném dni v týdnu filtrovány.

3.3.2 Filtrujte buňky podle pracovních dnů pomocí úžasného nástroje

Kromě filtrování buněk podle dne v týdnu nebo víkendů může nástroj "Speciální filtr" z "Kutools pro Excel" také pomoci filtrovat buňky podle pracovních dnů.

1. Použijte stejné kroky jako výše pro aktivaci nástroje "Speciální filtr".

2. V dialogovém okně "Speciální filtr" proveďte níže uvedená nastavení.

3. Poté se objeví dialogové okno "Kutools pro Excel". Pokračujte kliknutím na „OK“.

Nyní jsou všechny buňky pracovního dne filtrovány.

3.4 Formáty filtrů

Excel normálně podporuje filtrování dat na základě vizuálních kritérií, jako je barva písma, barva buňky nebo sady ikon, jak ukazuje následující snímek obrazovky.

Pokud však chcete filtrovat data na základě jiných vizuálních kritérií, jako je styl písma (tučné písmo, kurzíva), efekty písma (přeškrtnuté) nebo speciální buňka (obsahující vzorce), Excel to nepomůže. Tato část poskytuje metody, které vám pomohou tyto problémy vyřešit.

3.4.1 Filtrování podle textu formátovaného tučnou kurzívou

Předpokládejme, že chcete filtrovat data pomocí tučného nebo kurzívého formátování textu, jak je znázorněno na níže uvedeném snímku obrazovky, následující metody vám mohou pomoci. Postupujte prosím následovně.

3.4.1.1 Filtrování textu formátovaného tučně / kurzívou pomocí vzorce a příkazu Filtr

Kombinace vzorce Get.Cell a příkazu Filtr může pomoci filtrovat tučně formátovaný text v rozsahu sloupců.

1. Klikněte na "Vzorce" > "Definovat název".

2. V dialogovém okně "Nové jméno" musíte:

=GET.CELL(20,$B2)

=GET.CELL(21,$B2)

Syntaxe vzorce:

=GET.CELL(type_num, reference)

Argumenty vzorce

3. Vyberte prázdnou buňku ve stejném řádku B2, zadejte do ní níže uvedený vzorec a stiskněte klávesu "Enter". Chcete-li použít tento vzorec, vyberte buňku výsledku, přetáhněte její "Popisovač automatického vyplňování" přes buňky níže.

=Filter_Bold_Cells

4. Vyberte celé buňky výsledků (včetně záhlaví), klikněte na "Data" > "Filtr".

5. Klikněte na rozbalovací šipku, zaškrtněte pouze políčko vedle možnosti „PRAVDA“ a poté klikněte na „OK“.

Poté se filtrují všechny textové buňky tučným písmem nebo kurzívou. Viz screenshot:

3.4.1.2 Filtrování textu formátovaného tučně nebo kurzívou pomocí příkazů Najít a nahradit a Filtrovat

K dosažení můžete také použít kombinaci příkazů Najít a Nahradit a Filtr.

1. Vyberte rozsah sloupců obsahující tučné nebo kurzíva textové buňky, které budete filtrovat, a poté stiskněte klávesy "Ctrl" + "F".

2. V dialogovém okně "Najít a nahradit" je třeba provést konfiguraci následovně.

3. Nyní jsou všechny buňky s tučným písmem nebo kurzívou vybrány v původním rozsahu, klikněte na „Domů“ > „Barva výplně“ a poté vyberte barvu výplně pro vybrané buňky.

4. Znovu vyberte celý rozsah sloupců a kliknutím na „Data“ > „Filtr“ do něj přidejte filtr.

5. Klikněte na rozbalovací šipku, vyberte "Filtrovat podle barvy" a potom klikněte na barvu výplně, kterou jste právě zadali pod "Filtrovat podle barvy buňky". Viz snímek obrazovky:

Poté se filtrují všechny textové buňky tučným písmem nebo kurzívou.

3.4.1.3 Snadno filtrujte text ve formátu tučně nebo kurzívou pomocí úžasného nástroje

Jak jsme zmínili výše, nástroj "Speciální filtr" z "Kutools pro Excel" může pomoci snadno filtrovat s rozlišováním velkých a malých písmen, filtrovat podle délky textu, filtrovat data. Zde se naučíme, jak tuto funkci použít k filtrování textových buněk ve formátu tučně nebo kurzívou v aplikaci Excel.

1. Vyberte rozsah sloupců (včetně záhlaví), který obsahuje tučné nebo kurzíva formátované buňky, které budete filtrovat.

2. Klikněte na „Kutools Plus“ > „Speciální filtr“ > „Filtrovat tučně / Filtrovat kurzívou“. Viz snímek obrazovky:

3. Klikněte na "OK" ve vyskakovacím dialogovém okně "Kutools for Excel" pro pokračování (toto dialogové okno se zobrazí, abyste zjistili, kolik buněk splňuje kritéria).

Nyní jsou filtrovány všechny textové buňky tučně nebo kurzívou.

3.4.2 Filtrování přeškrtnutím formátovaného textu

Předpokládejme, že jste obdrželi seznam s lidmi, kteří stříhají přidáním přeškrtnutí k nim a potřebujete zjistit všechny přeškrtnuté buňky filtrováním, níže uvedené metody vám mohou pomoci.

3.4.2.1 Filtrování přeškrtnutého formátovaného textu pomocí funkce definované uživatelem a příkazu Filtr

Pomocí funkce definované uživatelem můžete identifikovat přeškrtnuté textové buňky a poté pomocí příkazu Filtr filtrovat všechny přeškrtnuté buňky na základě výsledků.

1. Stisknutím kláves "Alt" + "F11" otevřete okno "Microsoft Visual Basic for Applications".

2. V okně „Microsoft Visual Basic for Applications“ klikněte na „Vložit“ > „Modul“. A poté zkopírujte níže uvedený kód VBA do okna Kód.

Function HasStrike(Rng As Range) As Boolean
HasStrike = Rng.Font.Strikethrough
End Function

3. Stisknutím kláves "Alt" + "Q" zavřete okno "Microsoft Visual Basic for Applications".

4. Vyberte prázdnou buňku (tato buňka by měla být ve stejném řádku jako buňka, kterou chcete vypočítat), zadejte níže uvedený vzorec a stiskněte klávesu "Enter". Chcete-li použít tento vzorec, vyberte buňku výsledku, přetáhněte její "Rukovadlo automatického vyplňování" přes buňku níže.

=HasStrike(B2)

Poznámka: Pokud má odpovídající buňka efekt přeškrtnutého písma, vrátí hodnotu „PRAVDA“, jinak vrátí hodnotu „NEPRAVDA“.

5. Vyberte celé buňky výsledků (včetně buňky záhlaví), klikněte na „Data“ > „Filtr“.

6. Poté klikněte na rozbalovací šipku > zaškrtněte pouze políčko vedle možnosti „PRAVDA“ > klikněte na „OK“. Viz zobrazený snímek obrazovky.

Nyní můžete vidět filtrování všech přeformátovaných buněk.

3.4.2.2 Snadno filtrujte přeškrtnutý formátovaný text pomocí úžasného nástroje

Pomocí nástroje "Speciální filtr" z "Kutools pro Excel" lze všechny přeškrtnuté formátované buňky filtrovat přímo pouze několika kliknutími.

1. Vyberte rozsah sloupců, ve kterém budete filtrovat všechny přeškrtnuté buňky, klikněte na „Kutools Plus“ > „Speciální filtr“ > „Filtrovat přeškrtnuté“.

2. Poté se objeví dialogové okno "Kutools for Excel", které vám řekne, kolik buněk je kvalifikováno, klikněte na "OK" pro pokračování.

Poté jsou všechny přeformátované buňky filtrovány, jak je znázorněno na následujícím obrázku obrazovky.

3.4.3 Filtrování podle písma nebo barvy pozadí

Jak jsme zmínili na samém začátku v této sekci formátů filtrů, Excel pomocí své vestavěné funkce podporuje filtrování dat na základě vizuálních kritérií, jako je barva písma, barva buněk nebo sady ikon. Tato část podrobně ukazuje, jak použít funkci "Filtrovat podle barvy" k filtrování buněk podle písma nebo barvy pozadí. Mezitím doporučujeme šikovnou funkci třetí strany, která vám pomůže tento problém vyřešit.

3.4.3.1 Filtrování podle jednoho písma nebo barvy pozadí pomocí příkazu Filtr

Funkci Filtr podle barvy v příkazu Filtr můžete přímo použít k filtrování buněk podle konkrétního písma nebo barvy pozadí v aplikaci Excel.

1. Vyberte rozsah sloupců, ve kterém budete filtrovat buňky podle písma nebo barvy pozadí, a poté klikněte na „Data“ > „Filtrovat“.

2. Klikněte na rozbalovací šipku > "Filtrovat podle barvy". Poté můžete vidět všechny barvy buněk a barvy písma aktuálního rozsahu sloupců. Klepnutím na libovolnou barvu buňky nebo barvu písma filtruje všechny buňky na základě vybrané barvy.

3.4.3.2 Filtrování podle více barev pozadí pomocí funkce definované uživatelem a příkazu Filtr

Pokud chcete filtrovat buňky podle více barev pozadí, použijte níže uvedenou metodu.

Předpokládejme, že chcete filtrovat všechny buňky s oranžovou a modrou barvou pozadí ve sloupci B, jak je znázorněno na obrázku níže. Nejprve musíte vypočítat barevný index těchto buněk.

1. Stiskněte současně klávesy "Alt" + "F11".

2. V okně „Microsoft Visual Basic for Applications“ klikněte na „Vložit“ > „Modul“. Poté zkopírujte níže uvedený kód VBA do okna kódu.

Kód VBA: Získejte index barvy pozadí buňky

Function GetColor(x As Range) As Integer
GetColor = x.Interior.ColorIndex
End Function

3. Stisknutím kláves "Alt" + "Q" zavřete okno "Microsoft Visual Basic for Applications".

4. V novém sloupci zadejte záhlaví do první buňky (tato buňka by měla být na stejném řádku záhlaví původního rozsahu).

5. Vyberte prázdnou buňku vedle buňky záhlaví (zde volím E2), zadejte do ní níže uvedený vzorec a stiskněte klávesu "Enter". Chcete-li použít tento vzorec, vyberte výslednou buňku a poté přetáhněte její "Popisovač automatického vyplňování" přes buňky níže.

=GetColor(B2)

Poznámka: Pokud buňka nemá barvu výplně, vrátí hodnotu -4142.

6. Vyberte buňky pomocného sloupce (včetně záhlaví), klikněte na "Data" > "Filtr" pro přidání filtru do sloupce.

7. Kliknutím na šipku rozevíracího seznamu otevřete rozevírací seznam a proveďte následující konfiguraci.

Nyní jsou buňky filtrovány podle zadaných barev pozadí, jak je ukázáno na následujícím snímku obrazovky.

3.4.3.3 Snadné filtrování podle písma nebo barvy pozadí pomocí úžasného nástroje

Nepochybně je snadné použít vestavěnou funkci Filtrovat podle barvy k filtrování buněk podle písma nebo barvy pozadí. Nevýhodou však je, že rozbalovací seznam zakrývá obsah původních dat, takže data pro správný výběr písma nebo barvy pozadí nemůžeme kdykoli zobrazit. Chcete-li se tomuto problému vyhnout, zde doporučujeme nástroj "Speciální filtr" aplikace "Kutools pro Excel".

1. Vyberte rozsah sloupců, ve kterém budete filtrovat buňky podle písma nebo barvy pozadí, a poté klikněte na „Kutools Plu“s > „Speciální filtr“ > „Speciální filtr“.

2. V dialogovém okně "Speciální filtr" proveďte konfiguraci následovně.

2.1) Adresa buňky vybraného rozsahu sloupců se zobrazí v poli "Rozsah";

2.2) V části „Pravidla filtru“ vyberte možnost „Formát“;

2.3) Z rozevíracího seznamu vyberte "Barva pozadí" nebo "Barva písma";

2.4) Klikněte na ikonu kapátka ;

2.5) V otevíracím dialogovém okně "Filtrovat speciální buňky" vyberte buňku obsahující barvu písma nebo barvu pozadí, podle které budete filtrovat, a poté klikněte na "OK".

2.6) Když se vrátíte do dialogového okna "Speciální filtr", v textovém poli se zobrazí barva písma nebo barva pozadí vybrané buňky (barvu můžete upravit podle potřeby), kliknutím na tlačítko "OK" zahájíte filtrování buněk.

Poté jsou filtrovány všechny buňky se zadanou barvou písma nebo barvou pozadí ve vybraném rozsahu.

3.4.4 Filtrování buněk vzorce

Pokud máte dlouhý seznam dat, který obsahuje skutečné hodnoty i vzorce a potřebujete pouze filtrovat buňky vzorce, co můžete udělat? Tato část poskytuje dva způsoby, jak toho dosáhnout.

3.4.4.1 Filtrování buněk vzorce pomocí funkce definované uživatelem a příkazu Filtr

Nejprve musíte zjistit všechny buňky vzorců v seznamu pomocí funkce definované uživatelem a poté pomocí příkazu Filtr filtrovat buňky vzorců na základě výsledků.

1. Stisknutím kláves "Alt" + "F11" otevřete okno "Microsoft Visual Basic for Applications".

2. V okně „Microsoft Visual Basic for Applications“ klikněte na „Vložit“ > „Modul“ a poté zkopírujte níže uvedený kód VBA do okna Kód.

Function HasFormula(Cell)
HasFormula = Cell.HasFormula
End Function

3. Stisknutím kláves "Alt" + "Q" zavřete okno "Microsoft Visual Basic for Applications".

4. Vyberte prázdnou buňku, tato buňka by měla být ve stejném řádku jako buňka, u které chcete zkontrolovat, zda se jedná o buňku vzorce, zadejte do ní níže uvedený vzorec a stiskněte klávesu "Enter". Chcete-li použít tento vzorec, vyberte buňku výsledku, přetáhněte její "Popisovač automatického vyplňování" přes buňky níže.

=HasFormula(C2)

Jak ukazuje výše uvedený snímek obrazovky, výsledky jsou „NEPRAVDA“ a „PRAVDA“, což znamená, že pokud je odpovídající buňka buňka vzorce, vrátí hodnotu PRAVDA, v opačném případě vrátí hodnotu FALSE.

5. Vyberte buňky výsledků (včetně buňky záhlaví), klikněte na "Data" > "Filtr" a přidejte k nim filtr.

6. Klikněte na rozbalovací šipku, zaškrtněte pouze políčko vedle políčka „PRAVDA“ a poté klikněte na „OK“.

Pak můžete vidět, že jsou filtrovány všechny buňky vzorce.

3.4.4.2 Snadno filtrujte buňky vzorců pomocí úžasného nástroje

Zde demonstrujte nástroj "Speciální filtr" aplikace "Kutools pro Excel", který vám pomůže snadno filtrovat buňky vzorce v seznamu pouze několika kliknutími.

1. Vyberte seznam, ve kterém chcete filtrovat všechny buňky vzorce, a poté klikněte na „Kutools Plus“ > „Speciální filtr“ > „Filtrovat vzorec“.

2. Objeví se dialogové okno "Kutools for Excel", které vám řekne, kolik buněk je kvalifikováno, klikněte na "OK" pro pokračování.

Poté jsou všechny buňky vzorce filtrovány, jak je znázorněno na následujícím obrázku obrazovky.

Kromě toho můžete použít funkci "Speciální filtr" v "Kutools pro Excel" pro snadné filtrování buněk s jiným formátováním, jako například:

Filtrovat všechny buňky s komentáři, kliknutím se dozvíte více ...

Filtrovat všechny sloučené buňky na základě určité hodnoty, kliknutím se dozvíte více ...

3.5 Filtrovat pomocí zástupných znaků

Někdy můžete při filtrování zapomenout na přesná kritéria vyhledávání. V takovém případě doporučujeme použít zástupné znaky.

V aplikaci Excel existují pouze 3 zástupné znaky:

Podívejme se, jak použít zástupný znak při filtrování.

Předpokládejme, že je třeba filtrovat všechny buňky, které končí na trhu ve sloupci B jako snímek obrazovky níže, postupujte takto.

1. Nejprve vytvořte rozsah kritérií. Zadejte záhlaví stejné jako původní záhlaví sloupce a potom zadejte kritéria filtru do buňky níže. Viz screenshot:

2. Klikněte na "Data" > "Upřesnit".

3. V otevřeném dialogovém okně "Pokročilý filtr" nakonfigurujte následovně.

Poté jsou všechny buňky ukončeny Marketem filtrovány. Viz screenshot:

Použití zástupných znaků * a ~ ve filtrování je stejné jako výše uvedené operace.

3.6 Filtr s integrovaným vyhledávacím polem

Pokud používáte Excel 2010 nebo novější verze, můžete si všimnout, že ve filtru aplikace Excel je integrované vyhledávací pole. Tato část předvede, jak použít toto vyhledávací pole k filtrování dat v aplikaci Excel.

Jak je ukázáno na níže uvedeném snímku obrazovky, chcete filtrovat všechny buňky, které obsahují „Market“, vyhledávací pole vám pomůže snadno to provést.

1. Vyberte rozsah sloupců, který chcete filtrovat, kliknutím na „Data“ > „Filtr“ přidejte filtr.

2. Klikněte na rozbalovací šipku, do vyhledávacího pole zadejte „Market“ a klikněte na „OK“.

Při zadávání údajů do vyhledávacího pole můžete vidět, že všechny kvalifikované texty jsou uvedeny v reálném čase.

Poté jsou všechny buňky obsahující „Market“ filtrovány podle níže uvedeného snímku obrazovky.

4. Zkopírujte pouze viditelná data (ignorujte skrytá nebo filtrovaná data)

Kopírujte viditelná data pouze pomocí klávesových zkratek

Pomocí klávesových zkratek můžete vybrat pouze viditelné buňky a poté je ručně zkopírovat a vložit na požadované místo.

1. Vyberte rozsah, do kterého chcete kopírovat pouze viditelné buňky. V tomto kroku jsou vybrány viditelné i skryté buňky.

2. Stiskněte "Alt" a ";" klíče současně. Nyní jsou vybrány pouze viditelné buňky.

3. Stisknutím kláves „Ctrl“ + „C“ zkopírujte vybrané buňky a poté je stisknutím kláves „Ctrl“ + „V“ vložte.

Snadno kopírujte viditelná data pouze pomocí úžasného nástroje

Zde představujeme funkci "Vložit do viditelného" v "Kutools pro Excel", abyste mohli snadno kopírovat viditelná data pouze v Excelu. Kromě toho pomocí této funkce můžete kopírovat a vkládat hodnoty pouze do viditelných buněk v rozsahu, který byl filtrován.

1. Vyberte filtrovaný rozsah, který chcete zkopírovat pouze viditelné buňky, a poté klikněte na „Kutools“ > „Rozsah“ > „Vložit do viditelného“ > „Vše / Pouze vložit hodnoty“.

Pro buňky vzorců zvolte "Vše", chcete-li zkopírovat výsledek i vzorce, zvolte "Pouze vložit hodnoty", chcete-li zkopírovat pouze skutečné hodnoty.

2. Ve vyskakovacím dialogovém okně "Vložit do viditelného rozsahu" vyberte prázdnou buňku pro výstup zkopírovaných buněk a poté klikněte na "OK".

Pak se pouze viditelné buňky ve vybraném filtrovaném rozsahu zkopírují a vloží na nové místo.

Poznámka: Pokud byl cílový rozsah filtrován, vybrané hodnoty se vloží pouze do viditelných buněk.

5. Po filtrování odstraňte skryté nebo viditelné řádky

U filtrovaného seznamu možná budete muset odstranit skryté řádky, aby se zachovala pouze viditelná data. Zde v této části se naučíte tři způsoby, jak odstranit skryté nebo viditelné řádky ve filtrovaném seznamu v aplikaci Excel.

Odstraňte všechny skryté řádky z aktuálního listu pomocí kódu VBA

Níže uvedený kód VBA vám pomůže odstranit všechny skryté řádky z aktuálního listu v aplikaci Excel.

Poznámka: Tento VBA odstraní nejen skrytý řádek ve filtrovaném seznamu, ale také odstraní řádky, které jste ručně skryli.

1. V listu obsahujícím skryté řádky, které chcete odstranit, stiskněte současně klávesy "Alt" + "F11" pro otevření okna "Microsoft Visual Basic for Applications".

2. V okně „Microsoft Visual Basic for Applications“ klikněte na „Vložit“ > „Modul“ a poté zkopírujte níže uvedený kód VBA do okna Modul.

Kód VBA: Odstraňte všechny skryté řádky z aktuálního listu

Sub RemoveHiddenRows()
    Dim xRow As Range
    Dim xRg As Range
    Dim xRows As Range
    On Error Resume Next
    Set xRows = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange)
    If xRows Is Nothing Then Exit Sub
        For Each xRow In xRows.Columns(1).Cells
            If xRow.EntireRow.Hidden Then
                If xRg Is Nothing Then
                    Set xRg = xRow
                Else
                    Set xRg = Union(xRg, xRow)
                End If
            End If
        Next
        If Not xRg Is Nothing Then
            MsgBox xRg.Count & " hidden rows have been deleted", , "Kutools for Excel"
            xRg.EntireRow.Delete
        Else
            MsgBox "No hidden rows found", , "Kutools for Excel"
        End If
End Sub

3. Stiskněte klávesu "F5" pro spuštění kódu.

4. Poté se objeví dialogové okno "Kutools for Excel", které vám řekne, kolik skrytých řádků bylo odstraněno, kliknutím na "OK" jej zavřete.

Nyní jsou odstraněny všechny skryté řádky (včetně automaticky skrytých a ručně skrytých řádků).

Po filtrování pomocí funkce Přejít na odstranit viditelné řádky

Pokud chcete odstranit pouze viditelné řádky v určitém rozsahu, může vám pomoci funkce „Přejít na“.

1. Vyberte filtrovaný rozsah, ze kterého chcete odstranit viditelné řádky, stisknutím klávesy "F5" otevřete dialogové okno "Přejít na".

2. V dialogovém okně "Přejít na" klikněte na tlačítko "Speciální".

3. V dialogovém okně "Přejít na speciální" vyberte možnost "Pouze viditelné buňky" a poté klikněte na tlačítko "OK".

4. Nyní jsou vybrány všechny viditelné buňky. Klikněte pravým tlačítkem na vybraný rozsah a v kontextové nabídce klikněte na „Odstranit řádek“.

Poté budou odstraněny všechny viditelné buňky.

Po filtrování snadno odstraníte skryté nebo viditelné řádky pomocí úžasného nástroje

Výše uvedené metody jsou zdlouhavé a časově náročné. Zde doporučujeme funkci "Odstranit skryté (viditelné) řádky a sloupce" v "Kutools pro Excel". Pomocí této funkce můžete snadno odstranit skryté nebo viditelné řádky nejen ve vybraném rozsahu, ale také v aktuálním listu, více vybraných listech nebo celém sešitu. Postupujte prosím následovně.

1. Vyberte rozsah, ze kterého chcete odstranit všechny skryté nebo viditelné řádky.

Poznámky:

2. Klikněte na „Kutools“ > „Odstranit“ > „Odstranit skryté (viditelné) řádky a sloupce“.

3. V dialogovém okně "Odstranit skryté (viditelné) řádky a sloupce" je třeba provést konfiguraci následovně.

4. Poté jsou všechny viditelné nebo skryté řádky odstraněny najednou. Mezitím se objeví dialogové okno, které vám sdělí počet odstraněných řádků, kliknutím na „OK“ celou operaci dokončíte.

6. Filtrujte přes více listů

Normálně je snadné filtrovat data v listu. V této části se dozvíte, jak filtrovat data pomocí stejných kritérií ve více listech, které mají společnou datovou strukturu.

Předpokládejme, že sešit obsahuje tři pracovní listy, jak je zobrazeno na níže uvedeném snímku obrazovky, nyní chcete filtrovat data přes tyto tři listy současně se stejnými kritérii „Produkt = KTE“, níže uvedený kód VBA vám může udělat laskavost.

1. Stisknutím kláves "Alt" + "F11" otevřete okno "Microsoft Visual Basic for Applications".

2. V okně „Microsoft Visual Basic for Applications“ klikněte na „Vložit“ > „Modul“ a poté zkopírujte níže uvedený kód VBA do okna modulu.

Kód VBA: Filtrujte data napříč několika listy najednou

Sub apply_autofilter_across_worksheets()
'Updateby Extendoffice 20210518
    Dim xWs As Worksheet
    On Error Resume Next
    For Each xWs In Worksheets
        xWs.Range("A1").AutoFilter 1, "=KTE"
    Next
End Sub

Poznámka: Řádek „Xws.Range(“A1“.AutoFilter 1, “=KTE”)” v kódu znamená, že budete filtrovat data ve sloupci A s kritériem =KTE a číslo 1 je číslo sloupce sloupce A. Můžete je změnit podle své potřeby. Chcete-li například filtrovat všechna čísla, která jsou ve sloupci B větší než 500, můžete tento řádek změnit na „Xws.Range(“B1“.AutoFilter 2, „>500“)“.

3. Stiskněte klávesu "F5" pro spuštění kódu.

Zadané sloupce jsou nyní filtrovány současně ve všech listech v aktuálním sešitu. Podívejte se na níže uvedené výsledky.

7. Po změně dat znovu použijte filtr

Někdy jste možná provedli změny pro filtrovaný rozsah. Výsledek filtru však zůstává stejný bez ohledu na to, jaké změny jste v rozsahu provedli (viz snímek obrazovky níže). Tato část ukazuje dva způsoby, jak ručně nebo automaticky znovu použít filtr na aktuální rozsah, aby byly zahrnuty provedené změny.

Ručně znovu použijte filtr pomocí příkazu Znovu použít

Excel má vestavěnou funkci "Znovu použít", která pomáhá ručně znovu použít filtr. Můžete se přihlásit následovně.

Kliknutím na "Data" > "Použít znovu" znovu použijete filtr v aktuálním listu.

Pak můžete vidět, že filtrovaný rozsah je znovu použit, aby zahrnoval provedené změny.

Automaticky znovu použít filtr s kódem VBA

Pokud je třeba filtrovaný seznam často měnit, musíte opakovaně kliknout na použití této funkce Znovu použít. Zde poskytněte kód VBA, který vám pomůže automaticky znovu použít filtr v reálném čase při změně dat.

1. V listu obsahuje filtr, který chcete automaticky znovu použít, klikněte pravým tlačítkem na záložku listu a vyberte "Zobrazit kód".

2. V otevíracím okně "Microsoft Visual Basic for Applications" zkopírujte níže uvedený kód VBA do okna Kód.

Kód VBA: Automaticky znovu použít filtr při změně dat

Private Sub Worksheet_Change(ByVal Target As Range)
   Sheets("Sheet2").AutoFilter.ApplyFilter
End Sub

Poznámka: V kódu je „Sheet2“ název aktuálního listu. Můžete jej změnit podle svých potřeb.

3. Stisknutím kláves "Alt" + "Q" zavřete okno "Microsoft Visual Basic for Applications".

Od této chvíle se při změně dat ve filtrovaném seznamu bude filtrovaný výsledek dynamicky upravovat. Viz obrázek níže gif.

8. Vyčistěte nebo vyjměte filtr

Naučili jsme se, jak přidat, použít a použít filtr ve výše uvedeném obsahu. Zde se naučíme, jak vymazat nebo odebrat filtr v aplikaci Excel.

8.1 Vymazání filtru ze sloupce

Po použití filtru ve sloupci, pokud jej chcete vymazat, klikněte na ikonu filtru a poté v rozbalovací nabídce klikněte na „Vymazat filtr z názvu záhlaví“.

8.2 Vymažte všechny filtry v listu

Pokud jste použili filtr na více sloupců a chcete je vymazat všechny najednou, klikněte na „Data“ > „Vymazat“.

Poté jsou všechny filtry vymazány, jak je znázorněno na níže uvedeném snímku obrazovky.

8.3 Vymazání filtrů ze všech listů v aktuálním sešitu

Předpokládejme, že jste použili filtry na více listech v sešitu a chcete tyto filtry vymazat najednou. Níže uvedený kód VBA vám může udělat laskavost.

1. Otevřete sešit, ze kterého vymažete všechny filtry, a poté stiskněte současně klávesy "Alt" + "F11".

2. V otevíracím okně „Microsoft Visual Basic for Applications“ klikněte na „Vložit“ > „Modul“ a poté zkopírujte níže uvedený kód VBA do okna modulu.

Kód VBA: Vymazání filtrů ze všech listů v aktuálním sešitu

Sub Auto_Open()
'Updated by Extendoffice 20201113
    Dim xAF As AutoFilter
    Dim xFs As Filters
    Dim xLos As ListObjects
    Dim xLo As ListObject
    Dim xRg As Range
    Dim xWs As Worksheet
    Dim xIntC, xF1, xF2, xCount As Integer
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each xWs In Application.Worksheets
        xWs.ShowAllData
        Set xLos = xWs.ListObjects
        xCount = xLos.Count
        For xF1 = 1 To xCount
         Set xLo = xLos.Item(xF1)
         Set xRg = xLo.Range
         xIntC = xRg.Columns.Count
         For xF2 = 1 To xIntC
            xLo.Range.AutoFilter Field:=xF2
         Next
        Next
    Next
    Application.ScreenUpdating = True
End Sub

3. Stiskněte klávesu "F5" pro spuštění kódu. Poté budou všechny filtry vymazány ze všech listů v aktuálním sešitu.

8.4 Odeberte všechny filtry z listu

Výše uvedené metody pomáhají pouze vymazat filtrovaný stav, ale funkce filtru zůstává v listu aktivní. Pokud chcete z listu odstranit všechny filtry, vyzkoušejte níže uvedené metody.

Odstraňte všechny filtry v listu vypnutím filtru

Kliknutím na "Data" > "Filtr" funkci vypnete (tlačítko Filtr není ve stavu zvýraznění).

Odeberte všechny filtry v listu pomocí klávesové zkratky

Kromě toho můžete použít klávesovou zkratku k odstranění všech filtrů v listu.

V listu obsahuje filtry, které chcete odstranit, stiskněte současně klávesy "Ctrl" + "Shift" + "L".

Všechny filtry v aktuálním listu jsou poté okamžitě odstraněny.