Ako používať funkciu FILTER Excelu

Office 365 prináša niekoľko skvelých funkcií ako XLOOKUP, SORT a FILTER.

Pri filtrovaní údajov v Exceli sme sa v pre-Office 365 svete väčšinou spoliehali na Excel vstavané filtre alebo nanajvýš pokročilé filtre či zložité vzorce SUMPRODUCT.Toto je často komplikované riešenie, ak musíte filtrovať časť množiny údajov.

Ale s novou funkciou FILTER je teraz naozaj jednoduché rýchlo filtrovať časti množiny údajov na základe podmienok.

V tomto návode vám ukážem, aká úžasná je nová funkcia FILTER a niekoľko užitočných vecí, ktoré s ňou môžete robiť.

Ale skôr, než sa pustím do príkladu, pozrime sa rýchlo na syntax funkcie FILTER.

Ak chcete získať tieto nové funkcie v Exceli, môžeteInovujte na Office 365(Pripojte sa k internému programu a získajte prístup ku všetkým funkciám/vzorcom)

Funkcia filtra Excel – syntax

Nasleduje syntax funkcie FILTER:

=FILTER(pole,zahrnutie,[ak_prázdne])

• rad - toto je rozsah buniek, kde máte údaje a chcete z nich filtrovať niektoré údaje
• zahrnúť - Toto je podmienka, ktorá hovorí funkcii, ktoré záznamy má filtrovať
• [if_empty] – Toto je voliteľný parameter, v ktorom môžete určiť, čo sa má vrátiť, ak funkcia FILTER nenájde žiadne výsledky.V predvolenom nastavení (ak nie je zadané) vracia #CALC!omyl

Teraz sa pozrime na niekoľko úžasných príkladov funkcií filtra a na to, čo dokáže, keď to bolo bez neho veľmi komplikované.

Príklad 1: Filtrovanie údajov na základe jednej podmienky (región)

Predpokladajme, že máte množinu údajov, ako je tá nižšie, a chcete filtrovať iba všetky záznamy v Spojených štátoch.

Tu je vzorec FILTER, ktorý to robí:

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

Vyššie uvedený vzorec berie množinu údajov ako pole a podmienka je $B$2:$B$11=”US”

Táto podmienka spôsobí, že funkcia FILTER skontroluje každú bunku v stĺpci B (bunky s rozsahom) a vyfiltruje len tie záznamy, ktoré vyhovujú tejto podmienke.

Aj v tomto príklade som vložil pôvodné a filtrované údaje na rovnaký hárok, ale môžete ich vložiť aj do samostatných hárkov alebo dokonca zošitov.

Funkcia filtra vracia dynamické pole výsledkov (to znamená, že namiesto vrátenia hodnoty vracia pole, ktoré preteká do iných buniek).

Na to musíte mať oblasť, kde je výsledok prázdny.V ktorejkoľvek bunke v rozsahu už niečo je (v tomto príklade E2:G5) a funkcia vám zobrazí chybu #SPILL.

Keďže ide o dynamické pole, nemôžete zmeniť časť výsledku.Môžete odstrániť celý rozsah s výsledkom alebo bunkou E2 (kde je zadaný vzorec).Obidve tieto odstránia celé pole výsledkov.Nemôžete však zmeniť žiadnu jednu bunku (alebo ju odstrániť).

Vo vzorci vyššie som napevno zakódoval hodnotu rozsahu, ale môžete ju tiež vložiť do bunky a odkazovať na túto bunku s hodnotou rozsahu.

Napríklad v nižšie uvedenom príklade mám hodnotu rozsahu v bunke I2 a potom na ňu odkazujem vo vzorci:

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

Vďaka tomu je vzorec ešte užitočnejší, teraz stačí zmeniť hodnotu rozsahu v bunke I2 a filter sa automaticky zmení.

V bunke I2 môžete mať aj rozbaľovaciu ponuku, kde môžete jednoducho vybrať a filtrované údaje sa okamžite aktualizujú.

Príklad 2: Filtrovanie údajov na základe jedného kritéria (väčšie alebo menšie ako)

Môžete tiež použiť porovnávacie operátory vo funkciách filtrovania a extrahovať všetky záznamy väčšie alebo menšie ako určitá hodnota.

Predpokladajme napríklad, že máte množinu údajov zobrazenú nižšie a chcete filtrovať všetky záznamy s predajom nad 10000 XNUMX.

Môže to urobiť nasledujúci vzorec:

=FILTER($A$2:$C$11,($C$2:$C$11>10000))

Argument poľa sa vzťahuje na celú množinu údajov, v tomto prípade na podmienku ($C$2:$C$11>10000).

Vzorec kontroluje každý záznam na hodnotu v stĺpci C.Ak je hodnota väčšia ako 10000 XNUMX, filtruje sa, inak sa ignoruje.

Ak chcete získať všetky záznamy menšie ako 10000 XNUMX, môžete použiť nasledujúci vzorec:

=FILTER($A$2:$C$11,($C$2:$C$11<10000))

S formulou FILTER môžete byť tiež kreatívnejší.Ak by ste napríklad chceli filtrovať prvé tri záznamy na základe predaja, môžete použiť nasledujúci vzorec:

=FILTER($A$2:$C$11,($C$2:$C$11>=LARGE(C2:C11,3)))

Vyššie uvedený vzorec používa funkciu LARGE na získanie tretej najväčšej hodnoty v množine údajov.Potom použite túto hodnotu v podmienke funkcie FILTER, aby ste získali všetky záznamy s predajom väčším alebo rovným tretej najväčšej hodnote.

Príklad 3: Filtrovanie údajov pomocou viacerých podmienok (A)

Predpokladajme, že máte nasledujúci súbor údajov a chcete filtrovať všetky záznamy v Spojených štátoch s hodnotou predaja vyššou ako 10000 XNUMX.

Toto je podmienka AND, musíte skontrolovať dve veci - región musí byť v USA a predaj musí byť vyšší ako 10000 XNUMX.Ak je splnená iba jedna podmienka, výsledky by sa nemali filtrovať.

Tu je vzorec filtra, ktorý bude filtrovať záznamy s regiónom USA as viac ako 10000 XNUMX predajmi:

=FILTER($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))

請注意，標準（稱為包含參數）是 ($B$2:$B$11=”US”)*($C$2:$C$11>10000)

Keďže som použil dve podmienky a potreboval som, aby obe boli pravdivé, použil som na spojenie týchto dvoch podmienok operátor násobenia.Toto vráti pole 0s a 1s, pričom 1 sa vráti iba vtedy, ak sú splnené obe podmienky.

Ak neexistujú žiadne zodpovedajúce záznamy, funkcia vráti #CALC!Omyl.

Ak chcete vrátiť niečo zmysluplné (nie chyba), môžete použiť vzorec, ako je tento:

=FILTER($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nothing Found")

Tu som použil "nenájdený" ako tretí parameter, ktorý sa používa, keď sa nenájde žiadny zodpovedajúci záznam.

Príklad 4: Filtrovanie údajov pomocou viacerých kritérií (ALEBO)

Môžete tiež upraviť parameter "obsahuje" vo funkcii FILTER, aby ste skontrolovali podmienky ALEBO (kde môže byť každá daná podmienka pravdivá).

Povedzme napríklad, že máte množinu údajov zobrazenú nižšie a chcete filtrovať záznamy, ktorých krajinou sú Spojené štáty americké alebo Kanada.

Tu je vzorec, ako to urobiť:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Canada"))

Všimnite si, že vo vyššie uvedenom vzorci iba pridávam dve podmienky pomocou operátora sčítania.Keďže každá z týchto podmienok vracia pole TRUE a FALSE, môžem pridať kombinované pole, ktoré bude mať hodnotu TRUE, ak bude splnená niektorá z podmienok.

Ďalším príkladom môže byť situácia, keď chcete filtrovať všetky záznamy, ktorých krajinou sú Spojené štáty americké alebo hodnota predaja je vyššia ako 10000 XNUMX.

Urobí to nasledujúci vzorec:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))

POZNÁMKA: Pri použití podmienky AND vo funkcii FILTER použite operátor násobenia (*) a pri použití podmienky OR použite operátor sčítania (+).

Príklad 5: Filtrovanie údajov pre záznamy nad/pod priemerom

Vzorce vo funkcii FILTER môžete použiť na filtrovanie a extrahovanie záznamov s hodnotami nad alebo pod priemerom.

Povedzme napríklad, že máte množinu údajov zobrazenú nižšie a chcete filtrovať všetky záznamy s hodnotou predaja nad priemerom.

Môžete to urobiť pomocou nasledujúceho vzorca:

=FILTER($A$2:$C$11,C2:C11>AVERAGE(C2:C11))

Opäť platí, že pre podpriemerné hodnoty môžete použiť nasledujúci vzorec:

=FILTER($A$2:$C$11,C2:C11<AVERAGE(C2:C11))

Príklad 6: Filtrujte iba párne záznamy (alebo nepárne záznamy)

Ak potrebujete rýchlo filtrovať a extrahovať všetky záznamy v párnych alebo nepárnych riadkoch, môžete na to použiť funkciu FILTER.

Ak to chcete urobiť, musíte skontrolovať číslo riadku vo funkcii FILTER a vyfiltrovať iba čísla riadkov, ktoré zodpovedajú podmienke čísla riadku.

Predpokladajme, že máte súbor údajov, ako je uvedené nižšie, a chcem z tohto súboru údajov extrahovať iba párne záznamy.

Tu je vzorec, ako to urobiť:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=0)

Vyššie uvedený vzorec používa funkciu MOD na kontrolu čísla riadku (daného funkciou ROW) každého záznamu.

Vzorec MOD(ROW(A2:A11)-1,2)=0 vráti hodnotu TRUE, keď je číslo riadka párne, a FALSE, keď je nepárne.Všimnite si, že som odčítal 2 od časti ROW(A11:A1), pretože prvý záznam je v druhom riadku, čím sa čísla riadkov upravia tak, aby sa druhý riadok považoval za prvý záznam.

Podobne môžete filtrovať všetky nepárne záznamy podľa nasledujúceho vzorca:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=1)

Príklad 7: Triedenie filtrovaných údajov pomocou vzorca

Používanie funkcie FILTER s ďalšími funkciami nám umožňuje urobiť viac.

Ak napríklad používate funkciu FILTER na filtrovanie množiny údajov, môžete použiť funkciu SORT na získanie zoradených výsledkov.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete filtrovať všetky záznamy s predajom nad 10000 XNUMX.Pomocou funkcie SORT môžete s touto funkciou zabezpečiť triedenie výsledných údajov podľa predaja.

Urobí to nasledujúci vzorec:

=SORT(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)

Vyššie uvedená funkcia používa funkciu FILTER na získanie údajov v stĺpci C s predajom väčším ako 10000 XNUMX.Potom použite pole vrátené funkciou FILTER vo funkcii SORT na zoradenie údajov podľa predaja.

Druhý parameter vo funkcii SORT je 3, čo znamená triedenie podľa tretieho stĺpca.Štvrtý parameter je -1, ktorý zoraďuje údaje v zostupnom poradí.

Toto je teda 7 príkladov použitia funkcie FILTER v Exceli.

Dúfam, že tento návod bol pre vás užitočný.