Ako používať funkciu FILTER v programe Microsoft Excel

Zhrnutie:

Ak chcete použiť funkciu FILTER, zadajte jednoducho pole a rozsah pre vaše kritériá. Ak sa chcete vyhnúť chybe programu Excel pre prázdne výsledky filtra, použite tretí voliteľný argument na zobrazenie vlastného indikátora.

Microsoft Excel ponúka vstavanú funkciu filtra spolu s možnosťou použiť rozšírený filter. Ak však chcete filtrovať podľa viacerých kritérií a dokonca triediť výsledky, vyskúšajte funkciu FILTER v Exceli.

Pomocou funkcie FILTER môžete použiť operátory pre „a“ a „alebo“ na kombinovanie kritérií. Ako bonus vám ukážeme, ako použiť funkciu SORT na vzorec na zobrazenie výsledkov vo vzostupnom alebo zostupnom poradí podľa konkrétneho stĺpca.

Čo je funkcia FILTER v Exceli?

Syntax vzorca je FILTER(pole, rozsah=kritérium, ak_prázdne), kde sú povinné iba prvé dva argumenty. V závislosti od vašich údajov môžete pre kritériá použiť odkaz na bunku, číslo alebo text v úvodzovkách.

Ak vaša množina údajov môže vrátiť prázdny výsledok, použite tretí voliteľný argument, pretože zobrazí #CALC! štandardne chyba. Ak chcete nahradiť chybové hlásenie, môžete vložiť text, písmeno alebo číslo do úvodzoviek alebo jednoducho nechať úvodzovky prázdne pre prázdnu bunku.

Ako vytvoriť základný vzorec filtra

Na začiatok začneme základným filtrom, aby ste videli, ako funkcia funguje. Na každej snímke obrazovky vpravo uvidíte naše výsledky filtrovania.

Na filtrovanie údajov v bunkách A2 až D13 pomocou obsahu bunky B2 (elektronika) ako kritéria je tu vzorec:

=FILTER(A2:D13,B2:B13=B2)

Ak chcete vzorec rozdeliť, uvidíte, že argument pole je A2:D13 a argument rozsah=kritérium je B2:B13=B2. Toto vráti všetky výsledky obsahujúce elektroniku.

Ďalším spôsobom, ako napísať vzorec, je zadať obsah bunky B2 do úvodzoviek takto:

=FILTER(A2:D13,B2:B13="Electronics")

Na filtrovanie údajov v oblasti rozsah=kritérium môžete použiť aj kritériá z inej bunky. Tu použijeme údaje v bunke B15.

=FILTER(A2:D13,B2:B13=B15)

Ak vaše údaje obsahujú číslo, môžete ho použiť ako kritérium bez úvodzoviek. V tomto príklade použijeme rovnaký rozsah buniek, ale filtrujeme podľa buniek D2 až D13 a hľadáme 10.

=FILTER(A2:D13,D2:D13=10)

Ak pre svoj vzorec nedostávate žiadne výsledky alebo sa vám zobrazuje #CALC! chyba, môžete použiť tretí argument if_empty. Napríklad, ak je výsledok prázdny, zobrazíme Žiadne.

=FILTER(A2:D13,D2:D13=75,"None")

Ako vidíte, údaje range=criteria nezahŕňajú 75, preto je náš výsledok Žiadne.

Filter pomocou viacerých kritérií vo funkcii FILTER

Výhodou funkcie FILTER v Exceli je, že môžete filtrovať podľa viacerých kritérií. Zadáte operátor pre AND (*) alebo OR (+).

Napríklad budeme filtrovať našu množinu údajov podľa A3 (západ) a B2 (elektronika) pomocou hviezdičky (*) s týmto vzorcom:

=FILTER(A2:D13,(A2:A13=A3)*(B2:B13=B2))

Ako vidíte, máme jeden výsledok, ktorý zahŕňa West aj Electronics.

Ak chcete použiť druhý operátor, vyfiltrujeme A3 alebo B2 pomocou znamienka plus (+) takto:

=FILTER(A2:D13,(A2:A13=A3)+(B2:B13=B2))

Teraz môžete vidieť, že naše výsledky obsahujú päť záznamov s West alebo Electronics.

Ako triediť filtrované údaje v Exceli

Ak chcete zoradiť výsledky, ktoré získate pomocou funkcie FILTER, môžete do vzorca pridať funkciu SORT. Toto je jednoducho alternatíva k použitiu funkcie Zoradiť na karte Údaje, ale nevyžaduje si zmenu umiestnenia údajov.

Ak chcete získať ďalšie informácie o funkcii SORT predtým, ako ju vyskúšate, pozrite si náš návod, kde nájdete všetky podrobnosti.

Tu použijeme náš základný filter zo začiatku tohto návodu: FILTER(A2:D13,B2:B13=B2). Potom pridáme SORT s jeho argumentmi na zoradenie podľa štvrtého stĺpca (Strata) v zostupnom poradí (-1):

=SORT(FILTER(A2:D13,B2:B13=B2),4,-1)

Aby sme tento vzorec rozdelili, máme náš vzorec FILTER ako argument pole pre funkciu SORT. Potom máme 4 na zoradenie podľa štvrtého stĺpca v množine údajov a -1 na zobrazenie výsledkov v zostupnom poradí.

Ak chcete namiesto toho zobraziť výsledky vo vzostupnom poradí, nahraďte -1 znakom 1:

=SORT(FILTER(A2:D13,B2:B13=B2),4,1)

Vstavaný filter Excelu je skvelý na rýchle zobrazenie konkrétnych záznamov v množine údajov. A rozšírený filter funguje dobre na filtrovanie podľa rozsahu kritérií na mieste alebo inom mieste. Ale ak chcete použiť viacero kritérií a triediť súčasne, vyskúšajte funkciu FILTER.