Funkcia filtra programu Excel - vysvetlená s príkladmi + video

Pozrite si video - Príklady funkcií filtra Excel

Office 365 prináša niekoľko úžasných funkcií - napríklad XLOOKUP, SORT a FILTER.

Pokiaľ ide o filtrovanie údajov v programe Excel, vo svete pred Office 365 sme boli väčšinou závislí od zabudovaného filtra v programe Excel alebo maximálne od rozšíreného filtra alebo komplexných vzorcov SUMPRODUCT. V prípade, že ste museli filtrovať časť množiny údajov, bolo to zvyčajne komplexné riešenie (niečo, čo som tu zahrnoval).

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

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

Ale skôr, ako sa dostanem k príkladom, rýchlo sa zoznámime so syntaxou funkcie FILTER.

V prípade, že chcete získať tieto nové funkcie v programe Excel, môžete inovácia na Office 365 (Pripojte sa k programu insider a získajte prístup ku všetkým funkciám/vzorcom)

Funkcia filtra programu Excel - syntax

Nasleduje syntax funkcie FILTER:

= FILTER (pole, zahrnúť, [if_empty])

• pole - je to rozsah buniek, v ktorých máte údaje a chcete z nich niektoré údaje filtrovať
• zahrnúť - toto je podmienka, ktorá hovorí funkcii, ktoré záznamy má filtrovať
• [if_empty] - toto je voliteľný argument, kde môžete určiť, čo sa má vrátiť v prípade, že funkcia FILTER nenájde žiadne výsledky. V predvolenom nastavení (ak nie je uvedené) vráti #CALC! chyba

Teraz sa pozrime na niekoľko úžasných príkladov funkcií filtra a na veci, ktoré môže robiť a ktoré boli bez neho dosť zložité.

Kliknutím sem stiahnete vzorový súbor a budete pokračovať

Príklad 1: Filtrovanie údajov na základe jedného kritéria (oblasť)

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete filtrovať všetky záznamy iba pre USA.

Nasleduje vzorec FILTER, ktorý to urobí:

= FILTER ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = „USA“)

Vyššie uvedený vzorec používa ako pole množinu údajov a podmienkou je $ B $ 2: $ B $ 11 = „USA“

Vďaka tejto podmienke by funkcia FILTER skontrolovala každú bunku v stĺpci B (tú, ktorá má oblasť) a filtrovali by sa iba tie záznamy, ktoré zodpovedajú tomuto kritériu.

Tiež v tomto prípade mám pôvodné údaje a filtrované údaje na rovnakom hárku, ale môžete ich mať aj v samostatných listoch alebo dokonca v zošitoch.

Funkcia filtra vráti výsledok, ktorý je dynamickým poľom (čo znamená, že namiesto vrátenia jednej hodnoty vráti pole, ktoré sa rozleje do iných buniek).

Aby to fungovalo, musíte mať oblasť, kde by bol výsledok prázdny. V ktorejkoľvek z buniek v tejto oblasti (v tomto prípade E2: G5) už niečo obsahuje, funkcia vám zobrazí chybu #SPILL.

Pretože ide o dynamické pole, nemôžete tiež zmeniť časť výsledku. Môžete buď odstrániť celý rozsah, ktorý má výsledok, alebo bunku E2 (kde bol vzorec zadaný). Obaja by vymazali celé výsledné pole. Nemôžete však zmeniť žiadnu jednotlivú bunku (ani ju odstrániť).

Vo vyššie uvedenom vzorci som pevne zakódoval hodnotu oblasti, ale môžete ju mať aj v bunke a potom odkazovať na bunku, ktorá má hodnotu oblasti.

Napríklad v nižšie uvedenom príklade mám hodnotu oblasti v bunke I2 a na to sa potom odkazuje vo vzorci:

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

Vďaka tomu je vzorec ešte užitočnejší a teraz môžete jednoducho zmeniť hodnotu oblasti v bunke I2 a filter sa automaticky zmení.

Môžete mať tiež rozbaľovaciu ponuku v bunke I2, kde stačí vykonať výber a filtrované údaje by sa okamžite aktualizovali.

Príklad 2: Filtrovanie údajov na základe jedného kritéria (viac ako alebo menej ako)

V rámci funkcie filtra môžete použiť aj porovnávacie operátory a extrahovať všetky záznamy, ktoré sú viac alebo menej ako konkrétna hodnota.

Predpokladajme napríklad, že máte množinu údajov, ako je uvedené nižšie, a chcete filtrovať všetky záznamy, kde je hodnota predaja vyššia ako 10 000.

Nasledujúci vzorec to dokáže:

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

Argument poľa sa týka celého súboru údajov a podmienkou v tomto prípade je ($ C $ 2: $ C $ 11> 10 000).

Vzorec kontroluje hodnotu každého stĺpca v stĺpci C. Ak je hodnota väčšia ako 10 000, filtruje sa, inak sa ignoruje.

V prípade, že chcete získať všetky záznamy nižšie ako 10 000, môžete použiť nasledujúci vzorec:

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

Môžete byť tiež kreatívnejší so vzorcom FILTER. Ak napríklad chcete filtrovať prvé tri záznamy podľa hodnoty predaja, môžete použiť nasledujúci vzorec:

= FILTER ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> = VEĽKÉ (C2: C11,3)))

Vyššie uvedený vzorec používa funkciu VEĽKÁ na získanie tretej najväčšej hodnoty v množine údajov. Táto hodnota sa potom použije v kritériách funkcie FILTER na získanie všetkých záznamov, kde je predajná hodnota väčšia alebo rovná tretej najväčšej hodnote.

Kliknutím sem stiahnete vzorový súbor a budete pokračovať

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

Predpokladajme, že máte nižšie uvedenú množinu údajov a chcete filtrovať všetky záznamy pre USA, kde je hodnota predaja vyššia ako 10 000.

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

Nasleduje vzorec FILTER, ktorý bude filtrovať záznamy s USA ako regiónom a tržbami viac ako 10 000:

= FILTER ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = „US“)*($ C $ 2: $ C $ 11> 10 000))

Kritérium (nazývané argument zahrnúť) je ($ B $ 2: $ B $ 11 = „USA“)*($ C $ 2: $ C $ 11> 10 000)

Pretože používam dve podmienky a potrebujem, aby boli obidve pravdivé, použil som operátor násobenia na kombináciu týchto dvoch kritérií. Vráti pole 0 a 1, kde 1 sa vráti iba vtedy, ak sú splnené obe podmienky.

V prípade, že neexistujú žiadne záznamy, ktoré by spĺňali kritériá, funkcia vráti #CALC! chyba.

A v prípade, že chcete vrátiť niečo s významom (namiesto chyby), môžete použiť vzorec, ako je uvedené nižšie:

= FILTER ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = „USA“)*($ C $ 2: $ C $ 11> 10 000), „Nič sa nenašlo“)

Tu som ako tretí argument použil „Nenašiel sa“, ktorý sa používa vtedy, keď sa nenájdu žiadne záznamy, ktoré by zodpovedali kritériám.

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

Môžete tiež upraviť argument „zahrnúť“ vo funkcii FILTER, aby vyhľadal kritériá ALEBO (kde môže byť splnená ktorákoľvek z daných podmienok).

Predpokladajme napríklad, že máte množinu údajov, ako je uvedené nižšie, a chcete filtrovať záznamy, kde je krajina USA alebo Kanada.

Nasleduje vzorec, ktorý to urobí:

= FILTER ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = „USA“)+($ B $ 2: $ B $ 11 = „Kanada“))

Všimnite si toho, že vo vyššie uvedenom vzorci som jednoducho pridal dve podmienky pomocou operátora sčítania. Pretože každá z týchto podmienok vracia pole PRAVDIE a NEPRAVDA, môžem pridať, aby som získal kombinované pole, kde je PRAVDA, ak je splnená jedna z podmienok.

Ďalším príkladom môže byť prípad, keď chcete filtrovať všetky záznamy, kde je buď krajina USA, alebo hodnota predaja je vyššia ako 10 000.

Nasledujúci vzorec to urobí:

= FILTER ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = „USA“)+(C2: C11> 10 000))

Poznámka: Pri použití kritéria AND vo funkcii FILTER použite operátor násobenia (*) a pri použití kritéria ALEBO operátor sčítania (+).

Príklad 5: Filtrovanie údajov na získanie záznamov nad/pod priemerom

Vzorce vo funkcii FILTER môžete použiť na filtrovanie a extrahovanie záznamov, kde je hodnota nad alebo pod priemerom.

Predpokladajme napríklad, že máte množinu údajov, ako je uvedené nižšie, a chcete filtrovať všetky záznamy, kde je hodnota predaja nadpriemerná.

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

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

Podobne pre podpriemer môžete použiť nasledujúci vzorec:

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

Kliknutím sem stiahnete vzorový súbor a budete pokračovať

Príklad 6: Filtrovanie iba EVEN číselných záznamov (alebo záznamov čísel ODD)

V prípade, že potrebujete rýchlo filtrovať a extrahovať všetky záznamy z riadkov s párnym alebo nepárnym číslom, môžete to urobiť pomocou funkcie FILTER.

Ak to chcete urobiť, musíte vo funkcii FILTER skontrolovať číslo riadku a filtrovať iba čísla riadkov, ktoré spĺňajú kritériá počtu riadkov.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a ja chcem z tejto množiny údajov extrahovať iba párne záznamy.

Nasleduje vzorec, ktorý to urobí:

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

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

Vzorec MOD (ROW (A2: A11) -1,2) = 0 vráti TRUE, ak je číslo riadka párne, a FALSE, ak je nepárne. Všimnite si toho, že som odpočítal 1 od časti ROW (A2: A11), pretože prvý záznam je v druhom rade, a tým sa upraví číslo riadka tak, aby považoval druhý riadok za prvý záznam.

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

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

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

Použitie funkcie FILTER s inými funkciami nám umožní urobiť oveľa viac práce.

Ak napríklad filtrujete množinu údajov pomocou funkcie FILTER, môžete s ňou použiť funkciu Triediť a získať tak výsledok, ktorý je už zoradený.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete filtrovať všetky záznamy, kde je hodnota tržieb viac ako 10 000. Pomocou funkcie Triedenie môžete zaistiť zoradenie výsledných údajov podľa hodnoty predaja.

Nasledujúci vzorec to urobí:

= Triediť (FILTER ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10 000)), 3, -1)

Vyššie uvedená funkcia používa funkciu FILTER na získanie údajov, kde je predajná hodnota v stĺpci C viac ako 10 000. Toto pole vrátené funkciou FILTER sa potom použije vo funkcii Triediť na triedenie týchto údajov na základe predajnej hodnoty.

Druhý argument vo funkcii SORT je 3, ktorý je radený podľa tretieho stĺpca. A štvrtý argument je -1, ktorým je zoradiť tieto údaje zostupne.

Kliknutím sem stiahnete vzorový súbor

Toto je 7 príkladov použitia funkcie FILTER v programe Excel.

Dúfam, že ste našli tento návod užitočný!

Tiež by sa vám mohli páčiť nasledujúce tutoriály pre Excel:

1. Ako filtrovať bunky pomocou formátovania tučným písmom v programe Excel
2. Vyhľadávacie pole dynamického filtra programu Excel
3. Ako filtrovať údaje v kontingenčnej tabuľke v programe Excel