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.
Obsah: [ Skrýt ]
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 libovolné buňky v rozsahu nebo tabulce, do kterých chcete přidat filtr, klikněte na Data > Filtr.
1.2 Příkaz Filtr na kartě Domů
Vyberte libovolné buňky v rozsahu nebo tabulce, do kterých chcete přidat filtr, klikněte na Domů > Třídit a filtrovat > Filtr.
1.3 Přidejte filtr pomocí zástupce
Vyberte libovolné buňky v rozsahu nebo tabulce, do kterých chcete přidat filtr, a stiskněte Ctrl + směna + L klíče.
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) Klikněte na rozevírací šipku v záhlaví sloupce.
- 2) Podle potřeby zadejte podmínku filtru.
- 3) Klikněte na OK tlačítko pro spuštění filtrování. Viz screenshot:
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ů
U výše uvedené metody musíte použít filtr na sloupce jeden po druhém a nejdůležitější je, že tato metoda pouze podporuje A AUTOMATIZACI kritéria. Zde představte metody, které nejenže použijí filtr na více sloupců současně, ale také použijí oba A AUTOMATIZACI a OR kritéria.
Předpokládejme, že máte tabulku dat, jak je ukázáno níže, a chcete filtrovat data z více sloupců na základě více kritérií: Produkt = AAA-1 a objednávka> 80, or Celková cena> 10000 XNUMX. Zkuste to některou z následujících metod.
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: U kritérií AND umístěte hodnoty kritérií do různých buněk stejného řádku. Na druhý řádek umístěte hodnotu kritéria NEBO.
2. cvaknutí Data > pokročilý zapnout Pokročilý filtr funkce.
3. V Pokročilý filtr V dialogovém okně proveďte následující konfiguraci.
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
Vzhledem k tomu, A AUTOMATIZACI a OR Kritéria filtrování nejsou ve výše uvedené metodě snadná, zde velmi doporučujeme Super filtr vlastnost Kutools pro Excel. Díky této funkci můžete v aplikaci Excel snadno použít filtr na více sloupců s kritérii AND i OR.
1. Po instalaci Kutools pro Excel, Klepněte na tlačítko Kutools Plus > Super filtr.
potom se Super filtr podokno se zobrazí na pravé straně listu.
Ve výchozím nastavení jsou do skupiny přidány dvě prázdné skupiny kritérií se vztahem NEBO Super filtr podokno. 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 Super filtr v podokně nakonfigurujte kritéria filtru následujícím způsobem.
Tip: První rozevírací seznam je pro záhlaví sloupců, druhý pro typy filtrů (můžete si vybrat Text, číslo, datum, rok, formát textu a tak dále z této rozevírací nabídky), třetí je pro typy kritérií a poslední textové pole pro hodnotu kritérií.
Jako příklad, který jsme zmínili výše, zvolím zde Produktový > Text > rovná odděleně od tří rozevíracích seznamů a poté zadejte AAA-1 do textového pole. Viz snímek obrazovky:
Nyní se v původním rozsahu dat zobrazí pouze shodná data a zbytek se skryje. Viz snímek obrazovky:
Tip: S touto užitečnou funkcí můžete do skupiny přidat další kritéria, přidat další skupiny, uložit aktuální nastavení filtru jako scénář pro budoucí použití atd. Jedná se o nepostradatelný nástroj, který může ušetřit spoustu pracovní doby a zvýšit efektivitu práce.
Pokud chcete mít bezplatnou (30denní) zkušební verzi tohoto nástroje, kliknutím jej stáhněte, a poté přejděte k použití operace podle výše uvedených kroků.
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.)
Vlastně vestavěný operátor filtru - 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, jako je J, a proveďte následující kroky.
1. Přidejte filtr do záhlaví sloupce původního rozsahu dat. Kliknutím zjistíte jak.
2. Kliknutím na rozevírací šipku v buňce záhlaví rozbalíte nabídku filtru.
3. cvaknutí Textové filtry > Začíná s.
4. V Vlastní automatický filtr V dialogovém okně zadejte do textového pole konkrétní znak (zde zadávám písmeno J) a poté klikněte OK.
Tip: Můžete přidat další A or Or kritéria vztahu, jak potřebujete.
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, postupujte následovně.
1. Vytvořte pomocný sloupec kromě původního rozsahu dat (zde zvolím sloupec D jako pomocný sloupec). Zadejte následující vzorec do druhé buňky a stiskněte klávesu Enter. Vyberte buňku výsledku a přetáhněte ji Rukojeť automatického vyplňování dolů, abyste získali další výsledky.
= PŘESNÉ (B2, HORNÍ (B2))
Poznámka: Tento vzorec pomáhá identifikovat velká a malá písmena. Pokud buňka obsahuje všechna velká písmena, bude výsledek SKUTEČNÝ, jinak získáte výsledek jako NEPRAVDIVÉ.
2. Vyberte sloupce B a D (sloupec C bude vybrán, nevadí), klikněte na Data> Filtr přidat k nim 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 rozevírací šipku ve sloupci D, zrušte zaškrtnutí políčka Vybrat vše zaškrtávací políčko, zaškrtněte políčko vedle TRUE položku a poté klikněte na 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 ve sloupci filtrovat pouze veškerý text s malými nebo malými písmeny, zde doporučujeme Speciální filtr vlastnost Kutools pro Excel. Díky této funkci bude veškerý velký nebo malý text snadno filtrován pouze několika kliknutími.
1. Vyberte rozsah sloupců, do kterých budete filtrovat texty, a poté klikněte Kutools Plus > Speciální filtr > Speciální filtr.
2. V Speciální filtr V dialogovém okně proveďte níže uvedená nastavení.
3. Pak a Kutools pro Excel Zobrazí se dialogové okno, které vám řekne, kolik buněk je nalezeno a bude filtrováno, klikněte na 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.
Pokud chcete mít bezplatnou (30denní) zkušební verzi tohoto nástroje, kliknutím jej stáhněte, a poté přejděte k použití operace podle výše uvedených kroků.
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 vyberu B1: B27), a poté přidejte filtr do tohoto rozsahu sloupců kliknutím Data > Filtr.
2. Klikněte na rozevírací šipku v záhlaví sloupce a poté klikněte na Textové filtry > Vlastní filtr. Viz snímek obrazovky:
3. V Vlastní automatický filtr V dialogovém okně vyberte kritéria jako rovná se, do textového pole zadejte 10 otazníků (?) jako režim vzoru a poté klikněte na OK .
Tipy: Těchto 10 otazníků naznačuje, ž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 sloupec pomocníka sousedící s původním rozsahem dat. Zadejte do něj následující vzorec a stiskněte klávesu Enter. Vyberte buňku výsledku a přetáhněte ji Rukojeť automatického vyplňování 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 sloupec pomocníka (včetně hlavičky), klepněte na Data > Filtr přidat do něj filtr.
3. Klikněte na rozevírací šipku, zrušte zaškrtnutí políčka Vybrat vše 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 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čuji Speciální filtr užitečnost Kutools pro Excel abyste mohli snadno filtrovat buňky podle délky textu v aplikaci Excel.
1. Vyberte rozsah sloupců, podle kterých budete filtrovat buňky podle určité délky textu, klepněte na Kutools Plus > Speciální filtr > Speciální filtr. Viz snímek obrazovky:
2. V Speciální filtr V dialogovém okně proveďte následující konfiguraci.
3. Kutools pro Excel Zobrazí se dialogové okno, které vám řekne, kolik buněk je nalezeno a bude filtrováno, klikněte OK jít dál.
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.
Pokud chcete mít bezplatnou (30denní) zkušební verzi tohoto nástroje, kliknutím jej stáhněte, a poté přejděte k použití operace podle výše uvedených kroků.
3.2 Čísla filtrů
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 (například sloupec C, jak je znázorněno na následujícím obrázku obrazovky), abyste toho dosáhli.
1. Vyberte rozsah sloupců obsahujících čísla, která chcete filtrovat, klepněte na Data > Filtr přidat filtr.
2. Po přidání filtru proveďte konfiguraci následujícím způsobem.
Tip: Protože chci filtrovat buňky s čísly mezi 15000 20000 a 15000 20000, zde zadávám do textových polí zvlášť XNUMX XNUMX až XNUMX XNUMX.
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í poskytuje vestavěná funkce Filtry data mnoho běžných kritérií pro filtrování dat. Jak vidíte, neexistuje žádná integrovaná možnost filtrování dat podle dne v týdnu, víkendů nebo pracovních dnů. Tato část vás naučí, jak těchto operací dosáhnout.
3.3.1 Filtrujte data podle dnů v týdnu nebo víkendů
Předpokládejme, že máte zobrazenou tabulku dat, jak je zobrazeno na následujícím snímku obrazovky. Pokud chcete filtrovat data podle dne v týdnu nebo víkendu, použijte některou z níže uvedených metod.
3.3.1.1 Filtrujte data podle dne v týdnu nebo víkendu 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íkendu, jak potřebujete.
1. Do prázdné buňky (v tomto případě D2) zadejte do ní následující vzorec a stiskněte vstoupit klíč. Vyberte buňku výsledku a poté přetáhněte Rukojeť automatického vyplňování nad níže uvedenými buňkami použít tento vzorec.
= WEEKDAY (A2)
Tip:
Poznámka: Jak vidíte, vzorec vrací čísla z 1 na 7, který označuje den v týdnu od NEDĚLE na Sobota (1 je pro neděli, 7 je pro sobotu).
2. Vyberte celé výsledky vzorce (včetně buňky záhlaví), klepněte na Data > Filtr.
3. Klikněte na rozevírací šipku a poté zrušte zaškrtnutí políčka Vybrat vše checkbox.
Poté se filtrují všechny víkendy nebo určité dny v týdnu. Viz screenshot:
3.3.1.2 Jednoduše filtrujte data podle dne v týdnu nebo víkendu pomocí úžasného nástroje
Pokud výše uvedená metoda pro vás není vhodná, zde doporučujeme Speciální filtr užitečnost Kutools pro Excel. Díky této funkci můžete snadno filtrovat buňky obsahující jakýkoli den v týdnu pouze několika kliknutími.
1. Vyberte buňky obsahující data, která chcete filtrovat, podle určitého dne v týdnu.
2. cvaknutí Kutools Plus > Speciální filtr > Speciální filtr.
3. V Speciální filtr V dialogovém okně proveďte následující konfiguraci.
4. Pak a Kutools pro Excel Zobrazí se dialogové okno s počtem buněk, které jsou nalezeny a budou filtrovány, klepněte na OK jít dál.
Nyní jsou filtrovány všechny víkendy nebo kterékoli dny v týdnu.
Pokud chcete mít bezplatnou (30denní) zkušební verzi tohoto nástroje, kliknutím jej stáhněte, a poté přejděte k použití operace podle výše uvedených kroků.
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íkendu, Speciální filtr užitečnost Kutools pro Excel může také pomoci filtrovat buňky podle pracovních dnů.
1. Použijte stejné kroky jako výše povolit nástroj Speciální filtr.
2. V Speciální filtr dialogové okno, proveďte níže uvedená nastavení.
3. Pak a Kutools pro Excel objeví se dialogové okno. Klepněte na OK jít dál.
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 formátováním textu tučnou nebo kurzívou, jak je ukázáno na následujícím snímku obrazovky, následující metody vám udělají laskavost. 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. cvaknutí Vzorce > Definujte jméno.
2. V Nové jméno dialogové okno, musíte:
Syntaxe vzorce:
=GET.CELL(type_num, reference)
Argumenty vzorce
3. Vyberte prázdnou buňku na stejném řádku B2, zadejte do ní následující vzorec a stiskněte klávesu vstoupit klíč. Vyberte buňku výsledku a přetáhněte ji Rukojeť automatického vyplňování nad níže uvedenými buňkami použít tento vzorec.
= Filter_Bold_Cells
4. Vyberte celé buňky výsledků (včetně hlavičky), klepněte na Data > Filtr.
5. Klikněte na rozevírací šipku, zaškrtněte pouze políčko vedle TRUE a klepněte na tlačítko OK 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ích buňky textu s tučným písmem nebo kurzívou, které chcete filtrovat, a potom stiskněte Ctrl + F klíče.
2. V Najít a nahradit dialogové okno, musíte nakonfigurovat následujícím způsobem.
3. Nyní jsou všechny buňky textu tučně nebo kurzívy vybrány v původním rozsahu, klepněte na Domů > Vyplň barvu, a poté vyberte barvu výplně pro vybrané buňky.
4. Znovu vyberte celý rozsah sloupců, klepněte na Data > Filtr přidat do něj filtr.
5. Klikněte na rozevírací šipku a vyberte Filtr podle barvya potom klikněte na barvu výplně, kterou jste právě zadali pod Filtrování 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, Speciální filtr užitečnost Kutools pro Excel může pomoci snadno filtrovat malá a velká písmena, filtrovat podle délky textu, filtrovat data. Zde se naučíme, jak použít tuto funkci k filtrování textových buněk ve formátu tučného nebo kurzíva 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. cvaknutí Kutools Plus > Speciální filtr > Tučné filtry / Filtrovat kurzívu. Viz snímek obrazovky:
3. cvaknutí OK při vyskakování Kutools pro Excel pokračovat (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.
Pokud chcete mít bezplatnou (30denní) zkušební verzi tohoto nástroje, kliknutím jej stáhněte, a poté přejděte k použití operace podle výše uvedených kroků.
3.4.2 Filtrování přeškrtnutím formátovaného textu
Předpokládejme, že jste dostali seznam lidí, kteří stříhají přidáním přeškrtnutí a potřebujete zjistit všechny přeškrtnuté buňky filtrováním, níže uvedené metody vám udělají laskavost.
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. zmáčkni Další + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.
2. V Microsoft Visual Basic pro aplikace okno, klepněte na tlačítko Vložit > Modul. A pak zkopírujte níže uvedený kód VBA do Kód okno.
Function HasStrike(Rng As Range) As Boolean
HasStrike = Rng.Font.Strikethrough
End Function
3. zmáčkni Další + Q klávesy pro zavření Microsoft Visual Basic pro aplikace okno.
4. Vyberte prázdnou buňku (tato buňka by měla být ve stejném řádku buňky, kterou chcete vypočítat), zadejte následující vzorec a stiskněte vstoupit klíč. Vyberte buňku výsledku a přetáhněte ji Rukojeť automatického vyplňování přes tuto buňku použijte tento vzorec.
= HasStrike (B2)
Poznámka: Pokud má odpovídající buňka efekt přeškrtnutí písma, vrátí se SKUTEČNÝ, jinak se vrátí NEPRAVDIVÉ.
5. Vyberte celé buňky výsledků (včetně buňky záhlaví), klepněte na Data > Filtr.
6. Poté klikněte na rozevírací šipku> zaškrtněte pouze políčko vedle možnosti PRAVDA> klikněte OK. Zobrazit 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
S Speciální filtr užitečnost Kutools pro Excel, všechny přeformátované buňky lze filtrovat přímo pouze několika kliknutími.
1. Vyberte rozsah sloupců, kde budete filtrovat všechny přeformátované buňky, klikněte na Kutools Plus > Speciální filtr > Filtrování přeškrtnutí.
2. Pak a Kutools pro Excel Zobrazí se dialogové okno, které vám sdělí, kolik buněk je kvalifikovaných, klikněte na OK jít dál.
Poté jsou všechny přeformátované buňky filtrovány, jak je znázorněno na následujícím obrázku obrazovky.
Pokud chcete mít bezplatnou (30denní) zkušební verzi tohoto nástroje, kliknutím jej stáhněte, a poté přejděte k použití operace podle výše uvedených kroků.
3.4.3 Filtrování podle písma nebo barvy pozadí
Jak jsme již zmínili na začátku této sekce formátů filtrů, Excel podporuje filtrování dat na základě vizuálních kritérií, jako je barva písma, barva buňky nebo sady ikon, s integrovanou funkcí. Tato část ukazuje, jak použít Filtr podle barvy funkce pro podrobné filtrování buněk podle písma nebo barvy pozadí. Mezitím doporučujeme šikovnou funkci jiného výrobce, 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ů, kde budete filtrovat buňky podle písma nebo barvy pozadí, a poté klikněte Data > Filtr.
2. Klikněte na rozevírací šipku> Filtr podle barvy. Pak můžete vidět, že jsou uvedeny všechny barvy buněk a barvy písma aktuálního rozsahu sloupců. Kliknutím na libovolnou barvu buňky nebo barvu písma vyfiltrujete všechny buňky podle ní.
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žovými a modrými barvami pozadí ve sloupci B, jak je zobrazeno níže. Nejprve musíte vypočítat index barev těchto buněk.
1. zmáčkni Další + F11 současně.
2. V Microsoft Visual Basic pro aplikace okno, klepněte na tlačítko 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. zmáčkni Další + Q klávesy pro zavření Microsoft Visual Basic pro aplikace okno.
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 vyberu E2), zadejte do ní následující vzorec a stiskněte klávesu vstoupit klíč. Vyberte buňku výsledku a přetáhněte ji Rukojeť automatického vyplňování nad níže uvedenými buňkami použít tento vzorec.
= GetColor (B2)
Poznámka: Pokud buňka nemá žádnou barvu výplně, vrátí -4142.
6. Vyberte buňky sloupce pomocníka (včetně hlavičky), klepněte na Data > Filtr přidat do sloupce filtr.
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 Filtr podle barvy k filtrování buněk podle písma nebo barvy pozadí. Nevýhodou však je, že rozevírací seznam pokrývá obsah původních dat, takže nemůžeme kdykoli zobrazit data pro správnou volbu písma nebo barvy pozadí. Chcete-li se tomuto problému vyhnout, zde doporučujeme Speciální filtr užitečnost Kutools pro Excel.
1. Vyberte rozsah sloupců, kde budete filtrovat buňky podle písma nebo barvy pozadí, a poté klikněte Kutools Plus > Speciální filtr > Speciální filtr.
2. V Speciální filtr V dialogovém okně proveďte následující konfiguraci.
Poté jsou filtrovány všechny buňky se zadanou barvou písma nebo barvou pozadí ve vybraném rozsahu.
Pokud chcete mít bezplatnou (30denní) zkušební verzi tohoto nástroje, kliknutím jej stáhněte, a poté přejděte k použití operace podle výše uvedených kroků.
3.4.4 Filtrování buněk obsahujících 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. zmáčkni Další + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.
2. V Microsoft Visual Basic pro aplikace okno, klepněte na tlačítko Vložit > Modul, poté zkopírujte níže uvedený kód VBA do okna Kód.
Function HasFormula(Cell)
HasFormula = Cell.HasFormula
End Function
3. zmáčkni Další + Q klávesy pro zavření Microsoft Visual Basic pro aplikace okno.
4. Vyberte prázdnou buňku, tato buňka by měla být ve stejném řádku buňky, kterou chcete zkontrolovat, zda se jedná o buňku vzorce, zadejte do ní následující vzorec a poté stiskněte vstoupit klíč. Vyberte buňku výsledku a přetáhněte ji Rukojeť automatického vyplňování nad níže uvedenými buňkami použít tento vzorec.
= HasFormula (C2)
Jak ukazuje výše uvedený snímek obrazovky, výsledky jsou NEPRAVDIVÉ a SKUTEČNÝ, což znamená, že pokud je odpovídající buňkou buňka vzorce, vrátí TRUE, jinak vrátí FALSE.
5. Vyberte buňky výsledku (včetně buňky záhlaví), klepněte na Data > Filtr přidat do něj filtr.
6. Klikněte na rozevírací šipku, zaškrtněte pouze políčko vedle TRUE a potom klepněte na tlačítko OK 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 užitečnost speciálního filtru Kutools pro Excel, která vám pomůže snadno filtrovat buňky vzorců v seznamu pouze několika kliknutími.
1. Vyberte seznam, který chcete filtrovat, všechny buňky vzorce a klikněte na Kutools Plus > Speciální filtr > Vzorec filtru.
2. Kutools pro Excel Zobrazí se dialogové okno, které vám sdělí, kolik buněk je kvalifikovaných, klikněte na OK jít dál.
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 Speciální filtr vlastnost Kutools pro Excel snadno filtrovat buňky s jiným formátováním, 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 ...
Pokud chcete mít bezplatnou (30denní) zkušební verzi tohoto nástroje, kliknutím jej stáhněte, a poté přejděte k použití operace podle výše uvedených kroků.
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:
Zástupný znak | Popis | Příklad |
* (hvězdička) | Představuje libovolný počet znaků | Například, *bobule najde „Blackberry","Jahoda","Borůvka" a tak dále |
? (otazník) | Představuje libovolný jeden znak | Například, já? najde „olizovat","zamknout","nedostatek" a tak dále |
~ (příliv) následuje *, ?nebo ~ | Představují skutečný *,? or ~ znak | Například, Tvář ~ * Obchod najde „Face * Shop“ |
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. cvaknutí Data > Advanced.
3. V otvoru Pokročilý filtr dialogové okno nakonfigurujte následujícím způsobem.
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ů, kde budete filtrovat data, klikněte na Data > Filtr přidat do něj filtr.
2. Klikněte na rozevírací šipku a zadejte "Trh" do vyhledávacího pole a potom 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, jak je znázorněno na následujícím obrázku obrazovky.
4. Zkopírujte pouze viditelná data (ignorujte skrytá nebo filtrovaná data)
Ve výchozím nastavení Excel kopíruje viditelné i skryté buňky. Pokud chcete viditelné buňky kopírovat pouze po filtrování, můžete vyzkoušet jednu z níže uvedených metod.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. zmáčkni Další a ; současně. Nyní jsou vybrány pouze viditelné buňky.
3. zmáčkni Ctrl + C zkopírujte vybrané buňky a stiskněte Ctrl + V klávesy pro jejich vložení.
Snadno kopírujte viditelná data pouze pomocí úžasného nástroje
Zde představte Vložit do viditelného vlastnost Kutools pro Excel můžete snadno kopírovat viditelná data pouze v aplikaci Excel. Kromě toho můžete pomocí této funkce kopírovat a vkládat hodnoty pouze do viditelných buněk v oblasti, která byla filtrována.
1. Vyberte filtrovaný rozsah, do kterého chcete kopírovat pouze viditelné buňky, a klepněte na Kutools > Rozsah > Pasta to Viditelné > Zobrazit vše / Pouze vkládat hodnoty.
Pro buňky vzorce vyberte Zobrazit vše zkopíruje výsledek i vzorce, zvolte Pouze vkládat hodnoty kopíruje pouze skutečné hodnoty.
2. Ve vyskakovacím okně Vložte do viditelného rozsahu V dialogovém okně vyberte prázdnou buňku pro výstup zkopírovaných buněk a 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.
Pokud chcete mít bezplatnou (30denní) zkušební verzi tohoto nástroje, kliknutím jej stáhněte, a poté přejděte k použití operace podle výše uvedených kroků.
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é odebere řádky, které jste ručně skryli.
1. V listu obsahujícím skryté řádky, které chcete odebrat, stiskněte Další + F11 současně otevřete Microsoft Visual Basic pro aplikace okno.
2. V Microsoft Visual Basic pro aplikace okno, klepněte na tlačítko Vložit > Modul, a poté zkopírujte níže uvedený kód VBA do okna modulu.
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. zmáčkni F5 klíč ke spuštění kódu.
4. Pak a Kutools pro Excel Zobrazí se dialogové okno, které vám řekne, kolik skrytých řádků bylo odstraněno, klikněte na OK zavřít.
Nyní jsou všechny skryté řádky (včetně automaticky skrytých řádků a ručně skrytých řádků) odstraněny.
Po filtrování pomocí funkce Přejít na odstranit viditelné řádky
Pokud chcete odstranit pouze viditelné řádky v určitém rozsahu, Přejít na funkce vám může pomoci.
1. Vyberte filtrovaný rozsah, ze kterého chcete odstranit viditelné řádky, stiskněte F5 klávesa pro otevření Přejít na dialogové okno.
2. V Přejít na dialogové okno, klepněte na tlačítko Speciální .
3. V Přejít na speciální dialogové okno vyberte Pouze viditelné buňky a klepněte na tlačítko OK OK .
4. Nyní jsou vybrány všechny viditelné buňky. Klikněte pravým tlačítkem na vybraný rozsah a klikněte na Odstranit řádek v místní nabídce.
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čuji Odstranit skryté (viditelné) řádky a sloupce vlastnost 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, několika vybraných listech nebo v 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. cvaknutí Kutools > Vymazat > Odstranit skryté (viditelné) řádky a sloupce.
3. V Odstranit skryté (viditelné) řádky a sloupce dialogové okno, musíte nakonfigurovat následujícím způsobem.
4. Poté jsou všechny viditelné nebo skryté řádky smazány najednou. Mezitím se zobrazí dialogové okno s informacemi o počtu odstraněných řádků, klikněte OK dokončit celou operaci.
Pokud chcete mít bezplatnou (30denní) zkušební verzi tohoto nástroje, kliknutím jej stáhněte, a poté přejděte k použití operace podle výše uvedených kroků.
6. Filtrujte přes více listů
Za normálních okolností je snadné filtrovat data v listu. V této části se naučíte, jak filtrovat data se stejnými kritérii napříč několika listy, které mají společnou datovou strukturu.
Předpokládejme, že sešit obsahuje tři listy, jak je ukázáno na následujícím snímku obrazovky, nyní chcete filtrovat data napříč těmito třemi listy současně se stejnými kritérii. “Produkt = KTE”, Níže uvedený kód VBA vám může udělat laskavost.
1. zmáčkni Další + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.
2. V Microsoft Visual Basic pro aplikace okno, klepněte na tlačítko 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: Linie "Xws.Range („A1“. AutoFilter 1, „= KTE“)„V kódu označuje, že budete filtrovat data ve sloupci A s kritérii = KTE a číslo 1 je číslo sloupce ve sloupci A. Můžete je podle potřeby změnit. Například pokud chcete filtrovat všechna čísla větší než 500 ve sloupci B, můžete tento řádek změnit na „Xws.Range („B1“. AutoFilter 2, „> 500“)".
3. zmáčkni F5 klíč ke spuštění kódu.
Potom jsou zadané sloupce filtrovány současně ve všech listech aktuálního 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 filtrovaného rozsahu. Výsledek filtru však zůstává stejný bez ohledu na to, jaké změny jste provedli v rozsahu (viz screenshot níže). V této části vám ukážeme dvě metody ručního nebo automatického opětovného použití filtru v aktuálním rozsahu, aby byly zahrnuty provedené změny.
Ručně znovu použijte filtr pomocí příkazu Znovu použít
Excel má vestavěný Znovu použít funkce, která pomůže ručně znovu použít filtr. Můžete použít následující postup.
klikněte Data > Znovu použít znovu použít 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 kartu listu a vyberte Zobrazit kód.
2. V otvoru Microsoft Visual Basic pro aplikace 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 „List2“ je název aktuálního listu. Můžete to změnit podle svých potřeb.
3. zmáčkni Další + Q klávesy pro zavření Microsoft Visual Basic pro aplikace okno.
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 filtrovanou ikonu a poté klikněte na Vymazat filtr z „Název záhlaví“ z rozbalovací nabídky.
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 > Průhledná.
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 v sešitu použili filtry na více listů a chcete tyto filtry vymazat najednou. Níže uvedený kód VBA vám může udělat laskavost.
1. Otevřete sešit, vymažete z něj všechny filtry a stiskněte klávesu Další + F11 současně.
2. V otvoru Microsoft Visual Basic pro aplikace okno, klepněte na tlačítko 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. zmáčkni F5 klíč ke spuštění kódu. Poté jsou 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 a filtry stále zůstávají v listu. Pokud chcete z listu odebrat všechny filtry, vyzkoušejte níže uvedené metody.
Odstraňte všechny filtry v listu vypnutím filtru
klikněte Data > Filtr pro vypnutí funkce (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 odebrat, stiskněte Ctrl + směna + L současně.
Poté jsou všechny filtry v aktuálním listu okamžitě odstraněny.
Nejlepší nástroje pro produktivitu v kanceláři
Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času. Kliknutím sem získáte funkci, kterou nejvíce potřebujete...
Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci
- Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
- Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
- Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!