Funkce FILTER v aplikaci Microsoft Excel je jednou z nejdůležitějších funkcí, které je třeba zvládnout. Bez něj jen těžko najdete potřebná data. Zde je rychlý kurz používání FILTER v Excelu.
Je také užitečné poznamenat, že tato funkce není jediným způsobem, jak filtrovat data v MS Excel. K dosažení stejné věci máte nástroje jako Auto Filter a Advanced Filter, s některými důležitými upozorněními, o kterých budeme diskutovat v této příručce.
Co je funkce FILTER?
Funkce nebo vzorce Excel jsou chlebem a máslem Excelu a umožňují vám dělat věci, jako je zjištění průměru velké datové sady nebo vytvoření grafu Bellovy křivky. Každá funkce má svou syntaxi, kterou obvykle můžete zkontrolovat pouhým zadáním názvu funkce v Excelu.
Funkce Excel FILTER, jak název popisuje, se používá k „filtrování“ hodnot zadaného rozsahu podle určitých podmínek. Rozsah i podmínky, které se mají použít, se zadávají spolu s funkcí, takže je extrémně přizpůsobitelná.
Se správnými parametry můžete z tabulky extrahovat přesné informace, které potřebujete, aniž byste museli procházet celou věc a hledat odpovídající položky ručně. A protože výstup je obsažen v buňce, můžete řetězit více funkcí pro provádění výpočtů nebo vizualizaci výsledků v grafu.
Proč je funkce FILTER upřednostňována před funkcí Rozšířený filtr?
Většina Excel začátečníci se raději drží vestavěných nástrojů pro filtrování dat v Excelu, než aby se pokoušela naučit syntaxi funkce. Automatický filtr je nejjednodušší, umožňuje vám vyloučit sloupce a nastavit kritéria filtrování z rozhraní založeného na nabídce. Pak je tu pokročilý filtr se schopností použít více kritérií pro implementaci složitých schémat filtrování.
Proč se tedy vůbec obtěžovat používáním funkce FILTER?
Hlavní výhodou používání funkcí aplikace Excel oproti ručnímu provádění jakýchkoli operací (pomocí jiného nástroje aplikace Excel nebo dokonce jakéhokoli jiného programu) je to, že funkce jsou dynamické. Automatický filtr nebo Pokročilý filtr vám poskytuje jednorázové výsledky, které se při úpravě zdrojových dat nezmění. Na druhou stranu funkce FILTER aktualizuje své výsledky odpovídajícím způsobem, když se data změní.
Syntaxe funkce FILTER
Syntaxe vzorce FILTER je dostatečně přímočará:
=FILTER(pole, zahrnout, [if_empty])
Pole je obdélníková podmnožina tabulky, která je označena určením rozsahu mezi levou horní buňkou a pravou dolní buňkou. Například A3:E10 je pole sestávající ze sloupců A až E a řádků 3 až 10..
Dalším parametrem jsou jednoduše kritéria, která se mají použít, nebo technicky vzato booleovské pole. Ten se zadává ve formě výrazu vyhodnocujícího hodnotu rozsahu buněk (obvykle sloupec), který vrací hodnotu TRUE nebo FALSE. Například A3:A10=”Pass” vrátí TRUE, když hodnota buňky odpovídá danému řetězci.
Nakonec můžete zadat hodnotu, která má být vrácena funkcí FILTER, když podmínky neodpovídají žádnému řádku. Může to být jednoduchý řetězec jako „Nenalezeny žádné záznamy“.
Související: Potřebujete zjistit, jak přesně se vzorová data hodí do větší datové sady? Podívejte se na našeho průvodce na výpočet standardní chyby v Excelu.
Použití funkce FILTER
Nyní, když známe syntaxi funkce FILTER, pojďme se podívat na to, jak ve skutečnosti použít FILTER v tabulce.
Ukázková data, která používáme pro tuto demonstraci, má pole od A2 do F11, které uvádí biologie skóre deseti studentů spolu s normálním rozdělením.
Napišme funkci, která bude filtrovat záznamy na základě jejich skóre zkoušek (uvedených ve sloupci D) a vrátíme pouze ty, které dosáhly méně než 30. Toto by měla být syntaxe:
=FILTER(A2:F11,D2:D11<30,”Nenalezeny žádné shody”)
Vzhledem k tomu, že filtrované výsledky jsou podmnožinou pole, použijte funkci v buňce s dostatečným prostorem za ní. Uděláme to pod původní tabulkou:
A dostáváme očekávané výsledky. Všechny položky se skóre nižším než 30 jsou vybrány a zobrazeny ve stejném formátu tabulky.
Nejste omezeni ani na jednu podmínku. Použijte operátor AND (*) k řetězení více výrazů jako jednoho parametru, čímž vytvoříte složitější filtr.
Pojďme sestavit funkci, která vrátí položky ležící mezi 30 a 70 značkami. Zde je syntaxe a výsledky:
=FILTR(A2:F11,(D2:D11>30)*(D2:D11<70)„Nenalezeny žádné shody“)
Pro nevýhradní kritéria můžete také použít operátor OR (+). To odpovídá filtru, i když je pouze jedna ze zahrnutých podmínek vyhodnocena jako PRAVDA.
V následujícím vzorci jej používáme k nalezení odlehlých hodnot pomocí filtrování výsledků menší než 15 nebo více než 70.
=FILTR(A2:F11,(D2:D11<30)+(D2:D11>70),”Nenalezeny žádné záznamy”)
Nakonec, namísto použití jediné hodnoty nebo řetězce pro návrat, když funkce FILTER nic nenajde, můžete zadat hodnoty pro každý sloupec, abyste zajistili, že výstup bude vždy zachován v konzistentním formátu..
Nejprve zkusme podmínku, o které víme, že je nepravdivá, abychom viděli, jak vypadá ve výchozím nastavení:
=FILTER(A2:F11,D2:D11>90,”Nenalezeny žádné shody”)
Jak vidíte, výsledek má pouze jeden řetězec, což je v rozporu s očekávaným formátem. To obvykle není problém, pokud nechcete vložit výsledky (nebo některé hodnoty z nich) do jiného vzorce.
Zkusme tedy zadat výchozí hodnoty ve stejném formátu jako položka pole. Toho lze dosáhnout zadáním hodnot oddělených čárkami ve složených závorkách. Takto:
=FILTR(A2:F11,D2:D11>90,{“Žádný záznam”, “Žádný záznam”, “Žádný záznam”, 0})
To nám dává chutnější výsledky, konzistentní se zbytkem formátu tabulky.
Vyplatí se funkce FILTER?
I když používáte MS Excel pouze pro správu záznamů a nemáte v úmyslu provádět žádné přepychové výpočty, funkce FILTER je jednou z mála, kterou byste si přesto měli prohlédnout.
Protože jakmile váš sešit dosáhne určité velikosti, ruční lokalizace dat může být bolestivá. A i když jsou nástroje Auto filter a Advanced filter užitečné, použití funkce je z dlouhodobého hlediska pohodlnější, protože výsledky se samy aktualizují a lze je spárovat s dalšími funkcemi.
.