Akademie Microsoft 365

Dynamické filtrování a řazení tabulky v Excelu podle více kritérií (Microsoft 365)

, , , , , , , , , , , , , ,

·

Rozsáhlé tabulky s prodejními či jinými daty mohou být nepřehledné, pokud potřebujeme rychle získat specifický pohled na informace. V tomto návodu si ukážeme, jak v Excelu pro Microsoft 365 vytvořit plně dynamický přehled, který filtruje data z velké zdrojové tabulky na základě měnících se kritérií a zároveň umožňuje výběr a řazení sloupců podle aktuální potřeby. Využijeme k tomu moderní dynamické funkce jako FILTR, ZVOLIT.SLOUPCE a SEŘADIT.

Výchozí situace

Představme si, že máme velkou zdrojovou tabulku (například s názvem „data“, formátovanou jako oficiální Excel Tabulka) obsahující prodejní data za několik let. Tato tabulka má mnoho sloupců, jako jsou datum, produkt, typ produktu, kategorie, pobočka, prodejce, počet kusů, tržby atd.

Naším cílem je na jiném listu (pojmenovaném například „Přehled“) vytvořit menší, souhrnnou tabulku, která bude obsahovat:

  1. Pouze vybrané sloupce ze zdrojové tabulky.
  2. Pouze řádky splňující několik kritérií (např. datum od, typ produktu, typ obchodu).
  3. Možnost dynamicky měnit tato kritéria.
  4. Možnost dynamicky řadit výslednou tabulku podle zvoleného sloupce (např. Datum nebo Tržby) a zvoleného směru (vzestupně nebo sestupně).

Všechny změny kritérií nebo řazení se musí okamžitě projevit ve výsledné tabulce bez nutnosti manuálních úprav vzorců.

Příprava dat

  1. Zdrojová tabulka: Ujistěte se, že vaše zdrojová data jsou formátována jako Excel Tabulka (Ctrl+T nebo Domů -> Formátovat jako tabulku). Pojmenujte tuto tabulku smysluplně, například „data“. Použití oficiální Tabulky zjednodušuje odkazování na data a zajišťuje automatické rozšiřování vzorců při přidání nových řádků.
  2. List Přehled: Vytvořte nový list. Zde umístíme buňky pro zadávání kritérií (např. pomocí ověření dat a rozbalovacích seznamů) a buňku, kam vložíme náš hlavní dynamický vzorec.

Krok 1: Výběr relevantních sloupců pomocí funkce ZVOLIT.SLOUPCE

Nejprve potřebujeme ze zdrojové tabulky „data“ vybrat jen ty sloupce, které chceme zobrazit v našem přehledu. K tomu použijeme funkci ZVOLIT.SLOUPCE (anglicky CHOOSECOLS).

  • Syntaxe: ZVOLIT.SLOUPCE(pole; číslo_sloupce1; [číslo_sloupce2]; ...)
  • Použití: Do první buňky naší budoucí přehledové tabulky (např. A5 na listu „Přehled“) vložíme vzorec.
    • pole: Odkaz na celou zdrojovou tabulku. Pokud se jmenuje „data“, napíšeme data. Excel automaticky rozpozná, že jde o tabulku.
    • číslo_sloupceN: Pořadová čísla sloupců ze zdrojové tabulky, které chceme vrátit. Pořadí, v jakém čísla uvedete, určuje pořadí sloupců ve výsledku.

Předpokládejme, že ze zdrojové tabulky „data“ chceme sloupce: Datum (1. sloupec), Produkt (2.), Typ produktu (3.), Pobočka (11.), Počet (10.) a Tržby (7.). Všimněte si, že můžeme měnit pořadí – Pobočka je původně 11., Počet 10., ale my je chceme v jiném pořadí.

Vzorec v buňce A5 bude vypadat takto:

=ZVOLIT.SLOUPCE(data; 1; 2; 3; 11; 10; 7)

Po potvrzení (Enter) Excel automaticky vyplní („přeteče“) data do potřebného počtu buněk doprava a dolů. Výsledkem je tabulka obsahující pouze námi vybrané sloupce ze všech řádků zdrojové tabulky.

Krok 2: Filtrování dat podle více kritérií pomocí funkce FILTR

Nyní potřebujeme data z kroku 1 dále filtrovat podle kritérií zadaných na listu „Přehled“. Použijeme funkci FILTR (anglicky FILTER). Tuto funkci vložíme „před“ naši stávající funkci ZVOLIT.SLOUPCE.

  • Syntaxe: FILTR(pole; zahrnout; [pokud_prázdné])
  • Použití: Upravíme vzorec v buňce A5.
    • pole: Výsledek naší funkce ZVOLIT.SLOUPCE. Ta nám dodává data ve správném formátu sloupců.
    • zahrnout: Zde definujeme logické podmínky pro filtrování. Protože chceme, aby všechna kritéria platila zároveň (logická podmínka A – AND), jednotlivé podmínky propojíme znaménkem násobení (*) a každou podmínku uzavřeme do samostatných závorek ().
    • [pokud_prázdné]: Volitelný argument, co vrátit, pokud filtr nenajde žádné odpovídající řádky. Můžeme vynechat nebo zadat např. „Žádná data“.

Předpokládejme, že máme kritéria na listu „Přehled“:

  • Datum od: v buňce E1
  • Druh produktu: v buňce E2 (např. rozbalovací seznam)
  • Typ obchodu: v buňce E3 (např. rozbalovací seznam)

Podmínky budou odkazovat na příslušné sloupce v původní zdrojové tabulce „data“:

  1. Datum ve sloupci data[Datum] je větší nebo rovno (>=) hodnotě v buňce E1.
  2. Druh produktu ve sloupci data[Druh produktu] se rovná (=) hodnotě v buňce E2.
  3. Typ obchodu ve sloupci data[Typ obchodu] se rovná (=) hodnotě v buňce E3.

Upravený vzorec v buňce A5 bude vypadat takto:

=FILTR(
    ZVOLIT.SLOUPCE(data; 1; 2; 3; 11; 10; 7);
    (data[Datum]>=E1) * (data[Druh produktu]=E2) * (data[Typ obchodu]=E3);
    "Žádná data nenalezena"
)

(Poznámka: Pro lepší čitelnost je vzorec rozdělen na více řádků, v Excelu ho můžete psát na jeden řádek.)

Po potvrzení se tabulka dynamicky přefiltruje. Pokud změníte hodnotu v buňkách E1, E2 nebo E3, tabulka se okamžitě automaticky aktualizuje. Všimněte si modrého orámování kolem výsledné tabulky – to značí dynamické pole.

Krok 3: Přidání dynamického řazení pomocí funkcí KDYŽ a SEŘADIT

Chceme náš přehled ještě vylepšit: umožnit uživateli vybrat, podle kterého sloupce (např. Datum nebo Tržby) a jakým směrem (vzestupně/sestupně) se má výsledná tabulka řadit.

Příprava pro řazení:

  1. Rozbalovací seznamy: Vytvořte dva další rozbalovací seznamy na listu „Přehled“:
    • Buňka H1: „Řadit podle“ s možnostmi „Datum“, „Tržby“.
    • Buňka H2: „Typ řazení“ s možnostmi „Vzestupně“, „Sestupně“.
  2. Pomocná buňka: Funkce SEŘADIT vyžaduje pro směr řazení číslo: 1 pro vzestupné a -1 pro sestupné. Vytvoříme si pomocnou buňku (např. I2), která převede text z H2 na číslo pomocí funkce KDYŽ: Excel=KDYŽ(H2="Vzestupně"; 1; -1)

Úprava hlavního vzorce:

Celý náš dosavadní vzorec FILTR(...) nyní zabalíme do funkce KDYŽ, která rozhodne, jaké řazení použít na základě hodnoty v buňce H1. Pokud je v H1 „Datum“, seřadíme podle data (1. sloupec výsledné tabulky). Jinak (pokud je tam „Tržby“), seřadíme podle tržeb (6. sloupec výsledné tabulky). Pro samotné řazení použijeme funkci SEŘADIT (anglicky SORT).

  • Syntaxe SEŘADIT:SEŘADIT(pole; [index_řazení]; [pořadí_řazení]; [podle_sloupce])
    • pole: Data, která chceme seřadit (náš výsledek z FILTR(...)).
    • index_řazení: Číslo sloupce v rámci řazeného pole, podle kterého se má řadit.
    • pořadí_řazení: 1 pro vzestupné, -1 pro sestupné (odkaz na naši pomocnou buňku I2).

Finální vzorec v buňce A5 bude kombinovat všechny funkce:

=KDYŽ(
    H1="Datum";
    SEŘADIT(
        FILTR(ZVOLIT.SLOUPCE(data; 1; 2; 3; 11; 10; 7); (data[Datum]>=E1) * (data[Druh produktu]=E2) * (data[Typ obchodu]=E3); "Žádná data nenalezena");
        1;  /* Index řazení pro Datum (1. sloupec výsledku) */
        I2  /* Pořadí řazení (odkaz na pomocnou buňku) */
    );
    SEŘADIT(
        FILTR(ZVOLIT.SLOUPCE(data; 1; 2; 3; 11; 10; 7); (data[Datum]>=E1) * (data[Druh produktu]=E2) * (data[Typ obchodu]=E3); "Žádná data nenalezena");
        6;  /* Index řazení pro Tržby (6. sloupec výsledku) */
        I2  /* Pořadí řazení (odkaz na pomocnou buňku) */
    )
)

(Poznámka: Všimněte si, že část FILTR(ZVOLIT.SLOUPCE(...)) je ve vzorci dvakrát. Je to proto, že se aplikuje buď řazení podle data, nebo řazení podle tržeb na stejný filtrovaný výsledek.)

Nyní máte plně dynamickou tabulku. Změnou jakéhokoli kritéria v buňkách E1-E3 nebo volby řazení v H1-H2 se celý přehled okamžitě automaticky aktualizuje a správně seřadí.

Časté dotazy (FAQ)

  • V jakých verzích Excelu tyto funkce fungují? Funkce FILTR, ZVOLIT.SLOUPCE, SEŘADIT a koncept dynamických polí jsou dostupné pouze v Excelu pro předplatitele Microsoft 365. Ve starších verzích Excelu (např. 2019, 2016) tyto funkce nejsou k dispozici.
  • Musí být zdrojová data ve formátu Tabulky? Není to striktně nutné, ale je to silně doporučeno. Pokud nepoužijete Tabulku, musíte ve vzorcích odkazovat na pevné oblasti buněk (např. List1!$A$1:$L$5000), což je méně flexibilní a při přidání nových dat musíte vzorce ručně upravovat. Odkazy na sloupce Tabulky (např. data[Datum]) jsou čitelnější a automaticky se přizpůsobují velikosti tabulky.
  • Jak funguje násobení (*) podmínek ve funkci FILTR? V kontextu funkce FILTR se násobení logických hodnot (PRAVDA/NEPRAVDA, které Excel interně reprezentuje jako 1/0) chová jako logický operátor A (AND). Výsledek násobení je 1 (PRAVDA) pouze tehdy, pokud jsou všechny násobené podmínky PRAVDA (1). Pokud je kterákoli podmínka NEPRAVDA (0), výsledek celého násobení je 0 (NEPRAVDA). Funkce FILTR pak vrátí pouze ty řádky, kde je výsledná hodnota podmínek 1 (PRAVDA).
  • Mohu přidat více kritérií pro filtrování? Ano, stačí přidat další podmínku do funkce FILTR pomocí * (další_podmínka). Například pokud chcete přidat filtr na pobočku (v buňce E4), upravíte část vzorce takto: ...(data[Datum]>=E1) * (data[Druh produktu]=E2) * (data[Typ obchodu]=E3) * (data[Pobočka]=E4)...
  • Mohu řadit podle jiných sloupců? Ano. Musíte upravit funkci KDYŽ a SEŘADIT. Přidejte další možnosti do rozbalovacího seznamu „Řadit podle“ (např. „Produkt“). Poté upravte vnořené funkce SEŘADIT v rámci KDYŽ, aby správně odkazovaly na pořadové číslo sloupce ve výsledné tabulce (po aplikaci ZVOLIT.SLOUPCE) a případně rozšiřte funkci KDYŽ o další větve pro nové možnosti řazení.
  • Co znamená chyba #PRESAH!? Tato chyba se zobrazí, pokud Excel nemůže vypsat („přetéct“) výsledky dynamického vzorce, protože cílové buňky nejsou prázdné. Ujistěte se, že všechny buňky pod a napravo od buňky s hlavním vzorcem (kromě první buňky) jsou prázdné.

Závěr

Použití dynamických funkcí jako FILTR, ZVOLIT.SLOUPCE a SEŘADIT v Microsoft 365 výrazně zjednodušuje tvorbu interaktivních a automaticky se aktualizujících přehledů. Odpadá nutnost složitých vzorců polí nebo VBA maker. Tímto způsobem můžete rychle a efektivně analyzovat data podle aktuálních potřeb a zvýšit tak svou produktivitu při práci s Excelem.

💡 Vyzkoušej CRM v Microsoft 365 šité na míru úspěšným obchodníkům 🚀

Objevte sílu BOHEMIA CRM