Tento návod vás krok za krokem provede tvorbou dynamické souhrnné tabulky a grafu v Excelu, které budou automaticky reagovat na výběr v rozbalovacím seznamu. Ukážeme si praktické využití dynamických funkcí LET, LAMBDA, FILTER, UNIQUE, HSTACK, IF, MONTH, LEFT a dalších dostupných v rámci Microsoft 365. Příklad je inspirován reálným scénářem v účetnictví.

---

Zadání

Máme účetní deník společnosti, který obsahuje informace o nákladech a výnosech za rok 2024. Z těchto dat vytvoříme dynamickou přehledovou tabulku, která:

1. Reaguje na výběr mezi náklady a výnosy pomocí rozbalovacího seznamu.
2. Zobrazuje součty za jednotlivé kvartály.
3. Automaticky se rozšiřuje nebo smršťuje podle zvolených dat.
4. Má propojený dynamický graf, který se automaticky mění podle výběru v tabulce.

---

Postup

1. Příprava zdrojové tabulky

Na začátku upravíme zdrojovou tabulku přidáním několika pomocných sloupců, které nám umožní správné filtrování dat.

a) Přidání sloupce „Kategorie účtu“

1. Vedle sloupce Účet přidejte nový sloupec, který nazveme Kategorie účtu.
2. Použijte funkci ZLEVA (LEFT) pro získání prvních tří číslic z čísla účtu: excelCopy=ZLEVA([@Účet]; 3)
3. Výslednou hodnotu převedeme na číslo vynásobením jedničkou: excelCopy=ZLEVA([@Účet]; 3)*1
4. Funkci aplikujte na celý sloupec.

b) Vyhledání kategorie účtu

1. Přidejte nový sloupec, který nazveme Kategorie.
2. Použijte funkci XLOOKUP (X.VYHLEDAT) pro přiřazení kategorie podle účetní osnovy: excelCopy=X.VYHLEDAT([@Kategorie účtu]; Účty[Kořen účtu]; Účty[Kategorie])
3. Ujistěte se, že hledané hodnoty i tabulka jsou ve stejném formátu (číslo vs. text).

c) Rozlišení nákladů a výnosů

1. Přidejte nový sloupec Typ účtu.
2. Použijte funkci KDYŽ (IF) k rozlišení, zda účet začíná číslem 5 (náklad) nebo 6 (výnos): excelCopy=KDYŽ(ZLEVA([@Účet]; 1)*1 = 5; "Náklad"; "Výnos")

d) Přidání kvartálů

1. Přidejte nový sloupec Kvartál.
2. Použijte funkci KDYŽ (IF) v kombinaci s funkcí MĚSÍC (MONTH), abyste rozdělili data podle kvartálů: excelCopy=KDYŽ(MĚSÍC([@Datum]) <= 3; "1Q"; KDYŽ(MĚSÍC([@Datum]) <= 6; "2Q"; KDYŽ(MĚSÍC([@Datum]) <= 9; "3Q"; "4Q")))

---

2. Vytvoření rozbalovacího seznamu

1. Na listu Přehled vyberte buňku, kde chcete mít rozbalovací seznam.
2. Vytvořte pomocný seznam hodnot pomocí funkce UNIQUE: excelCopy=UNIQUE(ZdrojováTabulka[Typ účtu])
3. Použijte funkci Ověření dat:
   • Na kartě Data vyberte Ověření dat > Seznam.
   • Jako zdroj zadejte odkaz na pomocný seznam: excelCopy=PomocnýSeznam#

---

3. Tvorba dynamické souhrnné tabulky

a) Použití funkce LET

Funkce LET umožňuje pojmenovat jednotlivé části výpočtu, což zvyšuje přehlednost a zrychluje výpočty. Tabulku vytvoříme z jedné buňky.

1. Klikněte do první buňky tabulky na listu Přehled.
2. Vložte následující funkci (popis jednotlivých částí níže): excelCopy=LET( Položky; UNIQUE(FILTER(ZdrojováTabulka[Kategorie]; ZdrojováTabulka[Typ účtu]=RozbalovacíSeznam)); Hodnota_Náklad; SUMIFS(ZdrojováTabulka[Má dáti]; ZdrojováTabulka[Kategorie]; Položky; ZdrojováTabulka[Kvartál]; Přehled!Záhlaví); Hodnota_Výnos; SUMIFS(ZdrojováTabulka[Dal]; ZdrojováTabulka[Kategorie]; Položky; ZdrojováTabulka[Kvartál]; Přehled!Záhlaví); KDYŽ( RozbalovacíSeznam = "Náklad"; HSTACK(Položky; Hodnota_Náklad); HSTACK(Položky; Hodnota_Výnos) ) )

b) Vysvětlení kroků

• Položky: Jedinečný seznam kategorií, které odpovídají výběru v rozbalovacím seznamu.
• Hodnota_Náklad: Součet nákladů podle kategorií a kvartálů.
• Hodnota_Výnos: Součet výnosů podle kategorií a kvartálů.
• KDYŽ: Rozlišuje, zda počítat náklady nebo výnosy podle výběru v rozbalovacím seznamu.
• HSTACK: Spojuje jednotlivé části tabulky (kategorie a hodnoty) horizontálně.

---

4. Vytvoření dynamického grafu

1. Klikněte na tabulku.
2. Na kartě Vložení vyberte Sloupcový graf.
3. Graf automaticky reaguje na změny v tabulce podle rozbalovacího seznamu.

---

Časté dotazy

1. Proč funkce nefungují správně?

• Zkontrolujte, zda máte aktivní předplatné Microsoft 365. Některé funkce, jako LET, nejsou dostupné ve starších verzích Excelu.

2. Jak přidat řádek celkového součtu?

• Řádek celkového součtu můžete zahrnout přímo do funkce LET pomocí dalších výpočtů a přidáním funkcí jako VSTACK.

3. Jak zahrnout více než čtyři kvartály?

• Pokud potřebujete jiný způsob dělení dat, například měsíční přehledy, upravte funkci pro výpočet kvartálů na dělení podle měsíců.