Funkce filtru Excel - vysvětleno pomocí příkladů + video

Podívejte se na video - Příklady funkcí FILTRU Excel

Office 365 přináší některé úžasné funkce - například XLOOKUP, SORT a FILTER.

Pokud jde o filtrování dat v Excelu, ve světě před Office 365 jsme byli většinou závislí na integrovaném filtru Excelu nebo maximálně na pokročilém filtru nebo složitých vzorcích SUMPRODUCT. V případě, že jste museli filtrovat část datové sady, obvykle šlo o složité řešení (něco, co jsem zde popsal).

Ale s novou funkcí FILTER je nyní opravdu snadné rychle filtrovat část datové sady na základě podmínky.

A v tomto tutoriálu vám ukážu, jak úžasná je nová funkce FILTER a některé užitečné věci, které s tím můžete dělat.

Než se však pustím do příkladů, pojďme se rychle seznámit se syntaxí funkce FILTER.

V případě, že chcete získat tyto nové funkce v aplikaci Excel, můžete upgradovat na Office 365 (připojte se k insider programu a získejte přístup ke všem funkcím/vzorcům)

Funkce filtru Excel - Syntaxe

Níže je syntaxe funkce FILTER:

= FILTER (pole, zahrnout, [if_empty])

• pole - toto je rozsah buněk, kde máte data a chcete z nich filtrovat některá data
• zahrnout - toto je podmínka, která říká funkci, jaké záznamy má filtrovat
• [if_empty] - toto je volitelný argument, kde můžete určit, co se má vrátit v případě, že funkce FILTER nenalezne žádné výsledky. Ve výchozím nastavení (pokud není uvedeno) vrátí #CALC! chyba

Nyní se podívejme na některé úžasné příklady funkcí filtru a věci, které může dělat a které bývaly v jeho nepřítomnosti docela složité.

Kliknutím sem stáhnete ukázkový soubor a budete pokračovat

Příklad 1: Filtrování dat podle jednoho kritéria (oblast)

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete filtrovat všechny záznamy pouze pro USA.

Níže je uveden vzorec FILTER, který to provede:

= FILTR ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = "US")

Výše uvedený vzorec používá jako pole datovou sadu a podmínkou je $ B $ 2: $ B $ 11 = ”US”

Tato podmínka způsobí, že funkce FILTER zkontroluje každou buňku ve sloupci B (tu, která má oblast) a budou filtrovány pouze ty záznamy, které odpovídají tomuto kritériu.

Také v tomto příkladu mám původní data a filtrovaná data na stejném listu, ale můžete je mít také v samostatných listech nebo dokonce sešitech.

Funkce filtru vrací výsledek, který je dynamickým polem (což znamená, že namísto vrácení jedné hodnoty vrací pole, které se rozlije do jiných buněk).

Aby to fungovalo, musíte mít oblast, kde bude výsledek prázdný. V kterékoli z buněk v této oblasti (v tomto případě E2: G5) již něco obsahuje, funkce vám zobrazí chybu #SPILL.

Protože se jedná o dynamické pole, nemůžete také změnit část výsledku. Můžete buď odstranit celý rozsah, který má výsledek, nebo buňku E2 (kde byl vzorec zadán). Oba by odstranili celé výsledné pole. Nemůžete však změnit žádnou jednotlivou buňku (ani ji odstranit).

Ve výše uvedeném vzorci jsem pevně zakódoval hodnotu oblasti, ale můžete ji mít také v buňce a poté odkazovat na tuto buňku, která má hodnotu oblasti.

Například v níže uvedeném příkladu mám hodnotu oblasti v buňce I2 a na tu se pak odkazuje ve vzorci:

= FILTR ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = I1)

Díky tomu je vzorec ještě užitečnější a nyní můžete jednoduše změnit hodnotu oblasti v buňce I2 a filtr by se automaticky změnil.

Můžete také mít rozevírací seznam v buňce I2, kde můžete jednoduše provést výběr, a to by okamžitě aktualizovalo filtrovaná data.

Příklad 2: Filtrování dat na základě jednoho kritéria (více než nebo méně než)

Můžete také použít srovnávací operátory v rámci funkce filtru a extrahovat všechny záznamy, které jsou více nebo méně než konkrétní hodnota.

Předpokládejme například, že máte datovou sadu, jak je uvedeno níže, a chcete filtrovat všechny záznamy, kde je hodnota prodeje vyšší než 10 000.

Následující vzorec to dokáže:

= FILTR ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10 000))

Argument pole odkazuje na celou datovou sadu a podmínkou je v tomto případě ($ C $ 2: $ C $ 11> 10 000).

Vzorec zkontroluje hodnotu každého sloupce ve sloupci C. Pokud je hodnota vyšší než 10 000, je filtrována, jinak je ignorována.

V případě, že chcete získat všechny záznamy pod 10 000, můžete použít následující vzorec:

= FILTR ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11 <10 000))

Se vzorcem FILTR můžete také být kreativnější. Pokud například chcete filtrovat tři nejlepší záznamy na základě prodejní hodnoty, můžete použít níže uvedený vzorec:

= FILTR ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> = VELKÝ (C2: C11,3)))

Výše uvedený vzorec používá funkci LARGE k získání třetí největší hodnoty v datové sadě. Tato hodnota je poté použita ve funkčních kritériích FILTER k získání všech záznamů, kde je prodejní hodnota větší nebo rovna třetí největší hodnotě.

Kliknutím sem stáhnete ukázkový soubor a budete pokračovat

Příklad 3: Filtrování dat pomocí více kritérií (AND)

Předpokládejme, že máte níže uvedenou datovou sadu a chcete filtrovat všechny záznamy pro USA, kde hodnota prodeje je vyšší než 10 000.

Toto je podmínka A, kde musíte zkontrolovat dvě věci - region potřebuje USA a tržby musí být vyšší než 10 000. Pokud je splněna pouze jedna podmínka, výsledky by neměly být filtrovány.

Níže je uveden vzorec FILTER, který bude filtrovat záznamy s USA jako regionem a tržbami více než 10 000:

= FILTR ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")*($ C $ 2: $ C $ 11> 10 000))

Kritérium (nazývané argument zahrnout) je ($ B $ 2: $ B $ 11 = ”US”)*($ C $ 2: $ C $ 11> 10 000)

Jelikož používám dvě podmínky a potřebuji, aby obě byly pravdivé, použil jsem pro zkombinování těchto dvou kritérií multiplikační operátor. Tím se vrátí pole 0 a 1, kde 1 je vráceno pouze tehdy, jsou -li splněny obě podmínky.

V případě, že neexistují žádné záznamy, které by splňovaly kritéria, funkce by vrátila #CALC! chyba.

A v případě, že chcete vrátit něco, co má význam (namísto chyby), můžete použít vzorec, jak je uvedeno níže:

= FILTR ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "USA")*($ C $ 2: $ C $ 11> 10 000), "Nic nenalezeno")

Zde jsem jako třetí argument použil „Nenalezeno“, který se používá, pokud nejsou nalezeny žádné záznamy, které by odpovídaly kritériím.

Příklad 4: Filtrování dat s více kritérii (NEBO)

Můžete také upravit argument „zahrnout“ ve funkci FILTR, abyste zkontrolovali kritéria NEBO (kde kterákoli z daných podmínek může být pravdivá).

Předpokládejme například, že máte datovou sadu, jak je uvedeno níže, a chcete filtrovat záznamy, kde je zemí USA nebo Kanada.

Níže je uveden vzorec, který to provede:

= FILTR ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")+($ B $ 2: $ B $ 11 = "Kanada"))

Všimněte si, že ve výše uvedeném vzorci jsem jednoduše přidal dvě podmínky pomocí operátoru sčítání. Protože každá z těchto podmínek vrací řadu TRUE a FALSE, mohu přidat, abych získal kombinované pole, kde je TRUE, pokud je splněna některá z podmínek.

Dalším příkladem může být situace, kdy chcete filtrovat všechny záznamy, kde je buď země USA, nebo hodnota prodeje je vyšší než 10 000.

Následující vzorec to udělá:

= FILTR ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")+(C2: C11> 10 000))

Poznámka: Při použití kritéria AND ve funkci FILTER použijte operátor násobení (*) a při použití kritéria NEBO operátor sčítání (+).

Příklad 5: Filtrování dat za účelem získání záznamů nad/pod průměrem

Pomocí vzorců ve funkci FILTR můžete filtrovat a extrahovat záznamy, kde je hodnota nad nebo pod průměrem.

Předpokládejme například, že máte datovou sadu, jak je uvedeno níže, a chcete filtrovat všechny záznamy, kde je hodnota prodeje nadprůměrná.

To lze provést pomocí následujícího vzorce:

= FILTR ($ A $ 2: $ C $ 11, C2: C11> PRŮMĚR (C2: C11))

Podobně pro podprůměr můžete použít níže uvedený vzorec:

= FILTR ($ A $ 2: $ C $ 11, C2: C11<>

Kliknutím sem stáhnete ukázkový soubor a budete pokračovat

Příklad 6: Filtrování pouze EVEN záznamů čísel (nebo záznamů čísel ODD)

V případě, že potřebujete rychle filtrovat a extrahovat všechny záznamy z řad sudých čísel nebo řádků lichého čísla, můžete to udělat pomocí funkce FILTR.

Chcete -li to provést, musíte ve funkci FILTER zkontrolovat číslo řádku a filtrovat pouze čísla řádků, která splňují kritéria počtu řádků.

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a já chci z této datové sady extrahovat pouze sudé záznamy.

Níže je uveden vzorec, který to provede:

= FILTR ($ A $ 2: $ C $ 11, MOD (ŘADA (A2: A11) -1,2) = 0)

Výše uvedený vzorec používá funkci MOD ke kontrole čísla řádku každého záznamu (který je dán funkcí ROW).

Vzorec MOD (ROW (A2: A11) -1,2) = 0 vrací TRUE, když je číslo řádku sudé, a FALSE, když je liché. Všimněte si toho, že jsem odečetl 1 od části ŘÁDEK (A2: A11), protože první záznam je ve druhém řádku, a tím se upraví číslo řádku tak, aby byl druhý řádek považován za první záznam.

Podobně můžete filtrovat všechny liché záznamy pomocí následujícího vzorce:

= FILTR ($ A $ 2: $ C $ 11, MOD (ŘADA (A2: A11) -1,2) = 1)

Příklad 7: Seřazení filtrovaných dat pomocí vzorce

Použití funkce FILTER s jinými funkcemi nám umožňuje zvládnout mnohem více.

Pokud například filtrujete datovou sadu pomocí funkce FILTER, můžete s ní použít funkci TŘÍDAT, abyste získali již seřazený výsledek.

Předpokládejme, že máte datovou sadu, jak je ukázáno níže, a chcete filtrovat všechny záznamy, kde hodnota prodeje je vyšší než 10 000. Pomocí funkce SORT můžete pomocí této funkce zajistit, aby byla výsledná data seřazeny podle hodnoty prodeje.

Následující vzorec to udělá:

= TŘÍDIT (FILTR ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10 000)), 3, -1)

Výše uvedená funkce používá funkci FILTER k získání dat, kde hodnota prodeje ve sloupci C je větší než 10 000. Toto pole vrácené funkcí FILTER je poté použito ve funkci SORT k třídění těchto dat na základě hodnoty prodeje.

Druhý argument ve funkci SORT je 3, což je řazení na základě třetího sloupce. A čtvrtý argument je -1, což je seřadit tato data sestupně.

Kliknutím sem stáhnete ukázkový soubor

Toto je 7 příkladů použití funkce FILTR v aplikaci Excel.

Doufám, že jste našli tento návod užitečný!

Také by se vám mohly líbit následující tutoriály k Excelu:

1. Jak filtrovat buňky pomocí formátování tučného písma v aplikaci Excel
2. Pole pro vyhledávání dynamického filtru aplikace Excel
3. Jak filtrovat data v kontingenční tabulce v aplikaci Excel