Excel v akci: Řešení reálného příkladu z praxe

V dnešním videu se podíváme na komplexní příklad z praxe, na kterém si ukážeme praktické použití dynamických funkcí, které jsou dostupné pro uživatele Excelu v rámci předplatného Microsoft 365. Ukážeme si, jak v praxi použít funkci LET nebo LAMBDA a jak je spojit dohromady s funkcemi jako SROVNAT.SVISLE nebo SUMIFS. Na toto video rovněž pro členy Akademie navazuje bonusové video, ve kterém si ukážeme, jak do tabulky zapojit i řádek celkového součtu, který bude rovněž reagovat na dynamickou tabulku a měnící se počet řádků v tabulce

Excelový soubor ke stažení

Jako zadání máme dnes příklad z praxe. V excelovém sešitu máme účetní deník společnosti. Tento účetní deník obsahuje informaci o všech nákladech a výnosech společnosti. Tabulka obsahuje datumy za rok 2024, účet a částku MD a DAL, podle toho, zda se jedná o nákladový nebo výnosový účet. Jako poslední dne máme název protistrany, pokud ji známe. Jedná se o obdobu účetního deníku, který si může kdokoliv stáhnout ze svého účetního programu.

Z této zdrojové tabulky chceme na listu Přehled vytvořit malou přehledovou tabulku, která bude dynamicky zobrazovat náklady a výnosy po kvartálech. Cílem je, aby tabulka reagovala na výběr v rozbalovacím seznamu nad tabulkou a podle výběru v rozbalovacím seznamu se vždy v tabulce zobrazily příslušné položky nákladových nebo výnosových účtů a součty pro kvartály. Tabulka přitom vznikla z jedné funkce, což zajišťuje, že se tabulka rozrůstá a smršťuje podle potřeby a rovněž je na tabulku navázaný graf, který rovněž reaguje dynamicky na výběr v rozbalovacím seznamu.

Excel v akci 2

To, jak tuto tabulku a graf vytvořit z jedné funkce si ukážeme v tomto videu.       

Začneme tím, že vytvoříme rozbalovací seznam na listu Přehled. V rozbalovacím seznamu si chceme vybrat ze dvou možností a to buď náklad nebo výnos. Když vybereme náklad, tak potřebujeme, aby se v tabulce zobrazily nákladové položky a pokud vybereme výnos, tak aby se naopak zobrazily výnosové položky.

Excel v akci 4

Na to ale musíme nejprve upravit zdrojovou tabulku a přidat do ní pár pomocných sloupců. Na listů Účty máme pomocnou tabulku s účty z klasické české účetní osnovy. V prvním sloupci máme uvedené kořeny účtů a vedle máme uvedenou kategorii účtu.  Pětkové účty jsou nákladové a šestkové účty jsou výnosové.

Abychom ve zdrojové tabulce dokázali rozlišit účty, tak bychom měli nejprve oddělit z celého čísla účtu první tři číslice, které určí o jakou kategorii účtu se jedná. Vrátíme se tedy do zdrojové tabulky a přidáme do ní pomocný sloupec. Samozřejmě, pokud očekáváte, že se zdrojová tabulka bude rozrůstat o nová data, tak by bylo nejlepší ji ještě změnit na excelovou tabulku, tak aby se po přidání nových dat funkce aktualizovali. My máme účetní data za ukončený rok, takže nová data neočekáváme a proto tabulku necháme v původním formátu.

Vedle sloupce účet přidáme nový sloupec, který nazveme jako Účet kategorie a zde oddělíme první tři číslice z účtu. Použijeme funkce ZLEVA, anglicky funkci LEFT. Ve funkci ZLEVA označíme číslo účtu a chceme oddělit tři první číslice, takže jako parametr znaky vyplníme trojku. Funkci potvrdíme a pošleme ji dolů a tím pádem se nám oddělí první tři číslice z účtu.

Na základě těchto prvních tří čísel jsme schopní teď přiřadit k jednotlivým účtům kategorie. Přidáme tedy nový sloupec, který nazveme jako Kategorie, kde pomocí vyhledávací funkce XLOOKUP najdeme kategorii účtu. Ve funkci XLOOKUP nejprve označíme co hledáme, což je první trojčíslí, následuje parametr, kde hledáme, což je první sloupec v tabulce na listu Účty a jako poslední parametr, je co chceme vrátit, což je sloupec s kategoriemi. Ukončíme funkci XLOOKUP a potvrdíme ji. A funkce XLOOKUP vrátila chybu. Víte proč?

Důvodem je nesoulad ve formátu. Funkce ZLEVA vrátila první tři číslice z účtu ve formátu textu, což mimo jiné poznáte podle zarovnání čísla v buňce. Ale funkce XLOOKUP vyhledává v tabulce s účty podle formátu čísla. Řešením je vrátit se k funkci ZLEVA a vynásobit ji jedničkou. To textovou hodnotu převede na číslo. Po potvrzení funkce XLOOKUP začne fungovat. Opravenou funkci ZLEVA pošleme dolů a teď můžeme poslat dolů i funkci XLOOKUP, která správně dohledá kategorie účtů. 

Poslední, co ve zdrojové tabulce zatím doplníme je, že ještě rozlišíme nákladové a výnosové účty. Přidáme ještě jeden sloupec, který nazveme jako Účet typ a v něm použijeme podmínkovou funkci KDYŽ, anglicky funkci IF. Podmínka je, že pokud účet začíná pětkou, tak že se jedná o náklad a pokud šestkou, tak se jedná o výnos. Takže v podmínce ve funkci KDYŽ funkce ZLEVA, kde chceme vrátit první číslo, nicméně víme, že se opět nevrátí číslo, ale text, takže funkci ZLEVA rovnou vynásobíme jedničkou, a podmínka je, že se bude číslo rovnat pětce. Pokud bude podmínka splněná, tak se jedná o náklad a pokud nebude, tak se jedná o výnos. Funkci potvrdíme a pošleme ji dolů a teď máme rozlišeno, zda se jedná o náklad nebo výnos.

Konečně můžeme vytvořit rozbalovací seznam na listu Přehled. Jelikož ale v rozbalovacím seznamu nemůžeme napřímo použít funkci UNIQUE, tak si někde v ústraní na listu vytvoříme pomocný seznam. Funkce UNIQUE, ve které označíme sloupec Účet typ. To nám vrátí seznam, který můžeme použít jako zdroj rozbalovacího seznamu.

Klikneme do buňky, karta Data, Ověření dat a seznam. Ve zdroji seznamu napíšeme rovná se a označíme první buňku pomocného seznamu a jelikož seznam vznikl pomocí dynamické funkce, tak se na zbytek seznamu odkážeme pomocí křížku. Potvrdíme seznam a zkontrolujeme ho v buňce.

A můžeme se vrhnout na tvorbu tabulky. Ještě než to ale uděláme, tak budeme potřebovat ještě jeden pomocný sloupec ve zdrojové tabulce. Souhrnná tabulka má součty po kvartálech, nicméně ve zdrojové tabulce máme klasické datumy. Vytvoříme tedy ještě jeden pomocný sloupec, který nazveme Kvartály a zde použijeme vícenásobnou podmínku KDYŽ v kombinaci s funkcí MĚSÍC neboli funkcí MONTH. Podmínka bude, že pokud funkce MĚSÍC vrátí číslo menší nebo rovno 3, tak se jedná o 1Q. Následuje druhá funkce KDYŽ, kde podmínka bude, že pokud funkce MĚSÍC vrátí hodnotu menší nebo rovnou 6, tak se jedná o 2Q, další podmínka ve funkci KDYŽ bude, že pokud funkce MĚSÍC vrátí hodnotu nižší nebo rovnou 9, že se jedná o 3Q a pokud ani jedna podmínka splněná nebude, tak se jedná o 4Q. Ukončíme funkce a potvrdíme a funkci pošleme dolů.

Vrátíme se na list Přehled a začneme tvořit tabulku. Důvodem, proč chceme tvořit souhrnnou tabulku z jedné funkce je to, že na tabulku chceme mít navázaný i graf. Pokud bychom každý sloupec tabulky vytvořili zvlášť, tak by následně po změně výběru v rozbalovacím seznamu graf ukazoval prázdná místa nebo by mu naopak nějaká data chyběla. Pokud tabulka vznikne z jedné funkce, tak se graf bude dynamicky přizpůsobovat počtu řádků v tabulce.

Postup si ukážeme po jednotlivých krocích a i přesto, že se postup bude zdát komplikovaný, tak je jednodušší než si myslíte.

Celou tabulku vytvoříme pomocí funkce LET. Jelikož bude funkce LET delší a na několika řádcích, tak nejprve roztáhneme příkazový řádek, aby se s funkcí lépe pracovalo.

Napíšeme do první buňky funkci LET, což je funkce, která nás v podstatě nechá stanovovat variabilní položky, se kterými následně můžeme počítat. Aby se nám s funkcí LET lépe pracovalo, tak využijeme zápisu, kdy bude každý komponent funkce na vlastním řádku, takže ALT+ENTER a tím se dostaneme na další řádek. Nejprve musíme položku pojmenovat. První pole, které v souhrnné tabulce tvoříme je jedinečný seznam kategorií, takže položku nazveme jako Položky. Napíšeme středník a přepneme se do hodnoty názvu nebo funkce, což je funkce, která reprezentuje položky. Ve sloupci Typ potřebujeme zobrazit jedinečný seznam kategorií v závislosti na výběru v rozbalovacím seznamu. Základem je tedy funkce FILTER, ve které označíme pole, což je oblast, kterou chceme filtrovat, tedy sloupec kategorie ze zdrojové tabulky. A tyto kategorie chceme filtrovat podle toho, zda se sloupec Účet typ rovná výběru v rozbalovacím seznamu. To není všechno, chceme vrátit jedinečný seznam položek, takže funkci FILTER zabalíme do funkce UNIQUE. Ukončíme funkce a napíšeme středník.

Toto je první variabilní složka ve funkci LET. Odskočíme na další řádek pomocí ALT+ENTER.

Následuje samotná oblast hodnot. Zde potřebujeme podle kvartálů a kategorií sečíst účty ze zdrojové tabulky. Ve funkci LET musíme opět nejprve stanovit název pro výpočet. Nicméně zde máme menší chyták. Ve zdrojové tabulce jsou hodnoty rozdělené do dvou sloupců a to podle účtů DAL nebo MD, tedy podle toho, zda se jedná o náklad nebo výnos. Takže to musíme promítnout do výpočtů, ale se při změně v rozbalovacím seznamu počítaly hodnoty ze správného sloupce. 

Začneme s výpočty pro náklady. Takže položku pojmenujeme jako Hodnota_náklad. A následuje výpočet. Hodnoty sečteme pomocí funkce SUMIFS. Ve funkci SUMIFS nejprve označujeme oblast pro součet, což je sloupec MD, kde jsou uvedené náklady. Následuje první oblast kritérií, což je sloupec Kategorie. A jako kritérium musíme vyřešit, že se musíme odkázat na sloupec, který vznikne z této jedné funkce. Takže abychom se na sloupec odkázali, tak se musíme odkázat na sloupec, který jsme vytvořili jako první, tedy sloupec Položky. A jako druhá oblast kritérií bude sloupec s kvartály ve zdrojové tabulce a jako kritérium označíme celou oblast v záhlaví tabulky, kde máme uvedené kvartály. Funkci ukončíme, napíšeme středník a odskočíme pomocí kombinace kláves ALT a ENTER o další řádek níže.    

A teď musíme to samé udělat pro výnosy. Položku pojmenujeme jako Hodnota_výnos a opět hodnoty sečteme pomocí funkce SUMIFS. Ve funkci SUMIFS opět nejprve označují oblast pro součet, což je tentokrát sloupec DAL, kde jsou uvedené výnosy. Následuje první oblast kritérií, což je sloupec Kategorie. A jako kritérium musíme opět označit sloupec Položky, který vznikne pomocí funkce LET. A jako druhá oblast kritérií bude sloupec s kvartály ve zdrojové tabulce a jako kritérium označíme celou oblast v záhlaví tabulky, kde máme uvedené kvartály. Funkci ukončíme, napíšeme středník a odskočíme na další řádek.

To, co jsme teď vytvořili je, že jsme definovali tři výpočty. Pomocí prvního výpočtu vznikne jedinečný seznam kategorií v závislosti na výběru v rozbalovacím seznamu. Další dva výpočty sčítají náklady nebo výnosy po kvartálech. Teď to musíme spojit dohromady pomocí funkce SROVNAT.VODOROVNĚ neboli funkce HSTACK.

Zde již nemusíme definovat název, jelikož se jedná o finální výpočet, který se bude skládat z variabilních položek. Nemůžeme ale pouze použít funkci SROVNAT.VODOROVNĚ, jelikož musíme rozlišit výběr v rozbalovacím seznamu. Takže použijeme podmínku KDYŽ, kde stanovíme, že pokud je v rozbalovacím seznamu vybrané slovo Náklad, tak že chceme vrátit funkci SROVNAT.VODOROVNĚ, kde spojíme variabilní pole Položky a Hodnota_náklad. A pokud podmínka splněná nebude a v rozbalovacím seznamu budou vybrané výnosy, tak chceme vrátit ve funkci SROVNAT.VODOROVNĚ pole Položky a Hodnota_výnos. Ukončíme funkci KDYŽ a nezapomeneme ukončit funkci LET.

Funkci potvrdíme a vrátila se z jedné buňky celá souhrnná tabulka, která bude reagovat na výběr v rozbalovacím seznamu. 

Změníme náklady na výnosy a tabulka se přizpůsobí a vrací správné kategorie výnosů a rovněž správné součty. 

Teď stačí kliknout do tabulky a na kartě Vložení vybrat sloupcový graf. Graf se vloží na list, můžeme ho podle potřeby zvětšit a zformátovat. Jelikož tabulka vznikla z jedné funkce, tak pokud změníme výběr v rozbalovacím seznamu, tak se graf dynamicky přizpůsobí bez toho, aby v něm zůstávala prázdná místa nebo aby naopak nějaká data chyběla.

A takto můžete pomocí funkce LET a několika excelových funkcí vytvořit plně dynamickou souhrnnou tabulku a graf. Pro členy Akademie Excelu máme rovněž na webu navazující video, kde příklad ještě vylepšíme a ukážeme si, jak můžeme zahrnout do souhrnné funkce i řádek celkového součtu, a to tak, že bude součástí celé funkce. To znamená, že se vám řádek celkového součtu zobrazí pod souhrnnými daty dole v tabulce, nicméně bude přímo součástí tabulky, to znamená, že se bude měnit jeho pozice v tabulce podle počtu řádků. 

Pokud vás práce s dynamickými poli zaujala, tak pro vás na Akademii Excelu máme celý kurz zaměřený na práci s dynamickými poli a funkcemi – Excel 365 Masterclass

MOHLO BY VÁS ZAJÍMAT

Vodorovná oblast v grafu

Vodorovná oblast v grafu | Excelové grafy

V dnešním videu si ukážeme, jak můžeme v grafu zvýraznit vodorovnou oblast. V předchozích videích jsme si již ukázali, jak můžeme v grafu zvýraznit svislou oblast, což je vhodné

Duplicity v Excelu

Duplicity v Excelu | Jak pracovat s duplicitami

V dnešním videu se podíváme na to, jak v Excelu pracovat s duplicitami. A uděláme si rovnou takový průlet práce s duplicitami z několika různých pohledů. Podíváme se na

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *