Pokud data, která chcete filtrovat, vyžadují kritéria ve více polích, například filtrování podle několika podmínek, které musí být všechny splněné, nebo zobrazení řádků odpovídajících několika různým podmínkám (například Typ = "Plodiny" NEBO Prodejce = "Chvojková"), můžete použít dialogové okno Rozšířený filtr .
Pokud chcete otevřít dialogové okno Rozšířený filtr, klikněte naUpřesnitdata>.
| Rozšířený filtr | Příklad |
|---|---|
| Přehled rozšířených kritérií filtru | |
| Víc kritérií, jeden sloupec, pravdivé kterékoli kritérium | Prodejce = "Chvojková" NEBO Prodejce = "Stoklasa" |
| Víc kritérií, víc sloupců, pravdivá všechna kritéria | Typ = "Plodiny" A Prodej > 1000 |
| Víc kritérií, víc sloupců, pravdivé kterékoli kritérium | Typ = "Plodiny" NEBO Prodejce = "Stoklasa" |
| Víc sad kritérií, jeden sloupec ve všech sadách | (Prodej > 6000 A Prodej < 6500) NEBO (Prodej < 500) |
| Víc sad kritérií, víc sloupců ve všech sadách | (Prodejce = "Chvojková" A Prodej >3000) NEBO (Prodejce = "Stoklasa" A Prodej > 1500) |
| Kritéria se zástupnými znaky | Prodejce = jméno obsahující „a“ jako druhé písmeno |
Přehled rozšířených kritérií filtru
Rozšířený filtr se od filtru liší v několika důležitých ohledech.
- Místo nabídky automatického filtru se po kliknutí na tento příkaz zobrazí dialogové okno Rozšířený filtr.
- Vytvoříte oblast kritérií (samostatné buňky nad daty), kam zadáte podmínky filtru, a pak nastavíte dialogové okno Rozšířený filtr, aby tuto oblast používalo.
- Rozšířený filtr se při změně hodnot kritérií automaticky neaktualizuje
Poznámka
Rozšířený filtr zůstává k dispozici pro složité scénáře filtrování, i když novější funkce, jako je Copilot v Excelu, teď můžou uživatelům pomoct s analýzou dat a filtrováním dotazů v přirozeném jazyce jako alternativním přístupem pro některé případy použití.
Principy AND vs. logiky OR
| Typ logiky | Jak nastavit | Příklad | Co najde |
|---|---|---|---|
| Logika funkce AND (všechna kritéria musí být splněna) | Vložení kritérií na stejný řádek | Typ = "Plodiny" ve sloupci 1 Prodej > 1000 ve sloupci 2 (obojí na stejném řádku) |
Pouze řádky, kde Typ JE "Plodiny" A Prodej JE větší než 1000 |
| Logika funkce NEBO (pravdivé může být jakékoli kritérium) | Vložení kritérií do jiného řádku | Řádek 1: Typ = "Plodiny" Řádek 2: Typ = "Maso" (různé řádky, stejný sloupec) |
Řádky, kde Typ JE "Plodiny" NEBO Typ JE "Maso" (nebo obojí) |
Ukázková data
Ve všech postupech v tomto článku se používají následující vzorová data.
Data obsahují tři prázdné řádky nad oblastí seznamu, které se použijí jako oblast kritérií (A1:C4) a oblast seznamu (A6:C10). Oblast kritérií obsahuje popisky sloupců a je v ní aspoň jeden prázdný řádek mezi hodnotami kritérií a oblastí seznamu.
Pokud chcete pracovat s těmito daty, vyberte v následující tabulce, zkopírujte je a potom je vložte do buňky A1 v novém listu Excelu.
| Typ: | Prodejce | Prodej |
|---|---|---|
| Nápoje | Miklus | 51 220 Kč |
| Maso | Chvojková | 4 500 Kč |
| plodiny | Stoklasa | 63 280 Kč |
| Plodiny | Chvojková | 65 440 Kč |
V tomto příkladu bude výsledný list vypadat takto – oblast kritérií filtrování je ohraničená modře a oblast seznamu (data, která chcete filtrovat) červeně.
Relační operátory
Pomocí následujících operátorů můžete porovnat dvě hodnoty. Při porovnání dvou hodnot pomocí těchto operátorů je výsledkem logická hodnota – PRAVDA nebo NEPRAVDA.
| Relační operátor | Význam | Příklad |
|---|---|---|
| = (symbol rovná se) | Je rovno | A1=B1 |
| > (symbol větší než) | Větší než | A1>B1 |
| < (symbol menší než) | Menší než | A1<B1 |
| >= (symbol větší než nebo rovno) | Větší než nebo rovno | A1>=B1 |
| <= (symbol menší než nebo rovno) | Menší než nebo rovno | A1<=B1 |
| <> (symbol není rovno) | Není rovno | A1<>B1 |
Použití znaku rovná se k zadání textu nebo hodnoty
Vzhledem k tomu, že znaménko rovná se (=) se používá k označení vzorce při zadání textu nebo hodnoty do buňky, Excel to, co zadáte, vyhodnotí. To ale může způsobit neočekávané výsledky filtrování. Pokud chcete použít rovnost jako porovnávací operátor pro text nebo pro hodnotu, zadejte kritéria do příslušné buňky oblasti kritérií jako řetězcový výraz:
=''=položka''
kde položka představuje hledaný text nebo hodnotu. Příklad:
| Data zadaná v buňce | Vyhodnocení a zobrazení v aplikaci Excel |
|---|---|
| ="=Chvojková" | =Chvojková |
| ="=3000" | =3000 |
Rozlišování velkých a malých písmen
Při filtrování textových dat nerozlišuje Excel malá a velká písmena. Jestli potřebujete vyhledávat s rozlišováním malých a velkých písmen, můžete použít vzorec. Příklad najdete v části Kritéria se zástupnými znaky.
Použití předdefinovaných názvů
Oblast můžete pojmenovat názvem Kritéria a odkaz na oblast se automaticky zobrazí v poli Oblast kritérií. Pro oblast seznamu, který chcete filtrovat, můžete taky definovat název Databáze a pro oblast, do které chcete vložit řádky, název Extrakce. Tyto oblasti se automaticky zobrazí v polích Oblast seznamu nebo Kopírovat do.
Vytvoření kritéria pomocí vzorce
Jako kritérium jde použít počítanou hodnotu, která je výsledkem vzorce. Zapamatujte si následující důležité informace:
- Výsledkem vzorce musí být hodnota PRAVDA nebo NEPRAVDA.
- Vzorec zadejte jako obvykle. Nezadávejte výraz následujícím způsobem:
=''=vstup'' - Jako popisek kritéria nepoužívejte popisek sloupce. Buď popisek kritéria vůbec nezadávejte, nebo použijte popisek, který není popiskem sloupce v oblasti seznamu (v následujících příkladech se jedná o položky Vypočtený průměr a Přesná shoda).
Jestliže ve vzorci místo relativního odkazu na buňku nebo názvu oblasti použijete popisek sloupce, zobrazí aplikace Excel chybovou hodnotu, například #NAME? nebo #VALUE! #HODNOTA!. Tuto chybu můžete ignorovat, protože způsob filtrování oblasti seznamu neovlivní. - Vzorec, který použijete pro kritérium, musí používat relativní odkaz odkazující na odpovídající buňku v prvním řádku dat.
- Všechny ostatní odkazy ve vzorci musí být absolutní odkazy.
Víc kritérií, jeden sloupec, pravdivé kterékoli kritérium
Způsob použití logických operátorů: (Prodejce = "Chvojková" NEBO Prodejce = "Stoklasa")
Tuto možnost použijte, když chcete filtrovat řádky, kde jeden sloupec odpovídá některé z několika hodnot. Zobrazí se oba řádky s názvem Chvojková A řádky s příjmením Novák.
Pokud chcete najít řádky, které odpovídají víc kritériím v jednom sloupci, zadejte kritéria těsně pod sebe do samostatných řádků v oblasti kritérií. Do prvních dvou řádků oblasti kritérií například zadáte:
Typ: Prodejce Prodej ="=Chvojková" ="=Stoklasa" Klikněte na buňku v oblasti seznamu.
Na kartě Data klikněte ve skupině Seřadit a filtrovat na tlačítko Upřesnit.
Zvolte, jestli chcete seznam filtrovat, přímo na místě, nebo skrýt řádky, které neodpovídají zadaným kritériím, nebo zkopírovat do jiného umístění, tedy řádky odpovídající vašim kritériím, zkopírovat do jiné oblasti listu.
Zadejte do pole Oblast kritérií odkaz na oblast kritérií včetně popisků kritérií. V příkladu zadáte $A$1:$C$3.
V příkladu bude filtrovaný výsledek pro oblast seznamu vypadat takto:
Typ: Prodejce Prodej Maso Chvojková 4 500 Kč plodiny Stoklasa 63 280 Kč Plodiny Chvojková 65 440 Kč
Víc kritérií, víc sloupců, pravdivá všechna kritéria
Způsob použití logických operátorů: (Typ = "Plodiny" A Prodej > 1000)
Pokud chcete najít řádky, které splňují víc kritérií ve víc sloupcích, zadejte v oblasti kritérií všechna kritéria do stejného řádku. Jako příklad zadáte:
Typ: Prodejce Prodej ="=Plodiny" >1000 Klikněte na buňku v oblasti seznamu.
Na kartě Data klikněte ve skupině Seřadit a filtrovat na tlačítko Upřesnit.
Zvolte, jestli chcete seznam filtrovat, přímo na místě, nebo skrýt řádky, které neodpovídají zadaným kritériím, nebo zkopírovat do jiného umístění, tedy řádky odpovídající vašim kritériím, zkopírovat do jiné oblasti listu.
Zadejte do pole Oblast kritérií odkaz na oblast kritérií včetně popisků kritérií. V příkladu zadáte $A$1:$C$2.
V příkladu bude filtrovaný výsledek pro oblast seznamu vypadat takto:
Typ: Prodejce Prodej plodiny Stoklasa 63 280 Kč Plodiny Chvojková 65 440 Kč
Víc kritérií, víc sloupců, pravdivé kterékoli kritérium
Způsob použití logických operátorů: (Typ = "Plodiny" NEBO Prodejce = "Stoklasa")
Pokud chcete najít řádky splňující víc kritérií ve víc sloupcích, kde může být pravdivé libovolné kritérium, zadejte kritéria do různých sloupců a řádků oblasti kritérií. Jako příklad zadáte:
Typ: Prodejce Prodej ="=Plodiny" ="=Stoklasa" Klikněte na buňku v oblasti seznamu.
Na kartě Data klikněte ve skupině Seřadit & filtr na Upřesnit.
Zvolte, jestli chcete seznam filtrovat, přímo na místě, nebo skrýt řádky, které neodpovídají zadaným kritériím, nebo zkopírovat do jiného umístění, tedy řádky odpovídající vašim kritériím, zkopírovat do jiné oblasti listu.
Zadejte do pole Oblast kritérií odkaz na oblast kritérií včetně popisků kritérií. V příkladu zadáte $A$1:$B$3.
V příkladu bude filtrovaný výsledek pro oblast seznamu vypadat takto:
Typ: Prodejce Prodej plodiny Stoklasa 63 280 Kč Plodiny Chvojková 65 440 Kč
Víc sad kritérií, jeden sloupec ve všech sadách
Způsob použití logických operátorů: ((Prodej > 6000 A Prodej < 6500) NEBO (Prodej < 500))
Pokud chcete najít řádky splňující víc sad kritérií, kde každá sada obsahuje kritéria pro jeden sloupec, zadejte víc sloupců se stejným záhlavím. Jako příklad zadáte:
Typ: Prodejce Prodej Prodej >6000 <6500 <500 Klikněte na buňku v oblasti seznamu. V příkladu kliknete na libovolnou buňku v oblasti seznamu A6:C10.
Na kartě Data klikněte ve skupině Seřadit a filtrovat na tlačítko Upřesnit.
Zvolte, jestli chcete seznam filtrovat, přímo na místě, nebo skrýt řádky, které neodpovídají zadaným kritériím, nebo zkopírovat do jiného umístění, tedy řádky odpovídající vašim kritériím, zkopírovat do jiné oblasti listu.
Tip:
Pokud kopírujete filtrované řádky na jiné místo, můžete zadat, které sloupce se mají zkopírovat. Před filtrováním zkopírujte popisky požadovaných sloupců do prvního řádku oblasti, do které chcete vložit filtrované řádky. Při filtrování zadejte do textového pole Kopírovat do odkaz na zkopírované popisky sloupců. Zkopírované řádky budou potom obsahovat jenom sloupce, u kterých jste zkopírovali popisky.
Zadejte do pole Oblast kritérií odkaz na oblast kritérií včetně popisků kritérií. V příkladu zadáte $A$1:$D$3.
V příkladu bude filtrovaný výsledek pro oblast seznamu vypadat takto:
Typ: Prodejce Prodej Maso Chvojková 4 500 Kč plodiny Stoklasa 63 280 Kč
Víc sad kritérií, víc sloupců ve všech sadách
Způsob použití logických operátorů: ( (Prodejce = "Chvojková" A Prodej >30000) NEBO (Prodejce = "Stoklasa" A Prodej > 1500) )
Pokud chcete najít řádky, které splňují víc sad kritérií, kde každá sada obsahuje kritéria pro víc sloupců, zadejte každou sadu kritérií do samostatných řádků a sloupců. Jako příklad zadáte:
Typ: Prodejce Prodej ="=Chvojková" >3000 ="=Stoklasa" >1500 Klikněte na buňku v oblasti seznamu. V příkladu kliknete na libovolnou buňku v oblasti seznamu A6:C10.
Na kartě Data klikněte ve skupině Seřadit a filtrovat na tlačítko Upřesnit.
Zvolte, jestli chcete seznam filtrovat, přímo na místě, nebo skrýt řádky, které neodpovídají zadaným kritériím, nebo zkopírovat do jiného umístění, tedy řádky odpovídající vašim kritériím, zkopírovat do jiné oblasti listu.
Zadejte do pole Oblast kritérií odkaz na oblast kritérií včetně popisků kritérií. V příkladu zadáte $A$1:$C$3.
V příkladu by filtrovaný výsledek pro oblast seznamu vypadal takto:
Typ: Prodejce Prodej plodiny Stoklasa 63 280 Kč Plodiny Chvojková 65 440 Kč
Kritéria se zástupnými znaky
Způsob použití logických operátorů: Prodejce = jméno obsahující „a“ jako druhé písmeno
Chcete-li najít textové hodnoty, které sdílejí pouze některé znaky, postupujte některým z následujících způsobů:
Chcete-li ve sloupci najít textovou hodnotu začínající určitými znaky, zadejte jeden nebo více požadovaných znaků bez rovnítka (=). Pokud jako kritérium zadáte třeba text Chvo, Excel vyhledá položky Chvojková, Chvost nebo Chvostovský.
Použijte zástupný znak.
Znak Hledaný obsah ? (otazník) Libovolný jednotlivý znak
Kritérium ko?ář například najde položky kolář a kovář* (hvězdička) Jakýkoli počet znaků
Kritérium *východ například najde položky jihovýchod a severovýchod~ (tilda) následovaná znakem ?, * nebo ~ Otazník, hvězdička nebo tilda
Kritérium fy91~? například nalezne fy91?.
Vložte nad oblast seznamu aspoň tři prázdné řádky, které bude možné použít jako oblast kritérií. Oblast kritérií musí obsahovat popisky sloupců. Mezi hodnotami kritérií a oblastí seznamu musí zůstat alespoň jeden prázdný řádek.
Do řádků pod popisky sloupců zadejte kritéria, která chcete použít pro filtrování seznamu. V příkladu zadáte:
Typ: Prodejce Prodej ="=Ma*" ="=?a*" Klikněte na buňku v oblasti seznamu. V příkladu kliknete na libovolnou buňku v oblasti seznamu A6:C10.
Na kartě Data klikněte ve skupině Seřadit a filtrovat na tlačítko Upřesnit.
Zvolte, jestli chcete seznam filtrovat, přímo na místě, nebo skrýt řádky, které neodpovídají zadaným kritériím, nebo zkopírovat do jiného umístění, tedy řádky odpovídající vašim kritériím, zkopírovat do jiné oblasti listu.
Zadejte do pole Oblast kritérií odkaz na oblast kritérií včetně popisků kritérií. V příkladu zadáte $A$1:$B$3.
V příkladu bude filtrovaný výsledek pro oblast seznamu vypadat takto:
Typ: Prodejce Prodej Nápoje Miklus 51 220 Kč Maso Chvojková 4 500 Kč plodiny Stoklasa 63 280 Kč
Jak odebrat nebo vymazat rozšířený filtr
Když použijete rozšířený filtr, můžete ho odebrat, abyste znovu viděli všechna svá data. Tady je postup:
- Klikněte na libovolnou buňku ve filtrované oblasti dat.
- Přechod na kartu Data
- Ve skupině Seřadit & filtr klikněte na tlačítko Vymazat.
- Znovu se zobrazí všechny řádky.
Potřebujete další pomoc?
Kdykoli se můžete zeptat odborníka z technické komunity Excelu nebo získat podporu v komunitách.