Interaktivní přehled osobních financí v Excelu je inteligentní přehled vytvořený z kontingenčních tabulek a průřezů. Nejen, že vám poskytne dokonalý přehled o vašich osobních financích, ale ještě si ho za pomoci kontingenčních tabulek zvládnete vytvořit za 25 minut a po přidání nových dat se na jedno kliknutí přehled aktualizuje.
Excelový soubor ke stažení:
Interaktivní přehled osobních financí
Cvičný interaktivní přehled osobních financí tvoříme z cvičných zdrojových dat, nicméně stejný přehled lze vytvořit i z reálných dat, které si stáhnete z vaší banky ve formátu excelového souboru.
Interaktivní přehled osobních financí je rozdělený do několika částí. V záhlaví přehledu jsou informace o příjmech a výdajích, úsporách a výdajích v jednotlivých kategoriích. Samotný přehled je tvořen osmi grafy, které jsou napojené na průřezy uprostřed přehledu.
Interaktivní přehled osobních financí vytvoříme za pomoci kontingenčních tabulek.
Příprava zdrojových dat
Na listu Data se nachází zdrojová data, která poslouží jako základ tvorby přehledu. K jednotlivým výdajům a příjmům na listu data jsou pomocí funkce SVYHLEDAT přiřazené kategorie a typy platby z listu Pomocná. Díky funkci SVYHLEDAT se k jednotlivým položkám přiřadí kategorie, podkategorie a typ platby, což bude základem pro filtraci dat pomoci průřezů.
Do tabulky ještě přidáme sloupec s měsíci. Ve zdrojové tabulce máme totiž pouze přesné datum, kdy příslušná platba proběhla. Jelikož bychom ale chtěli v grafech ukazovat měsíce, které se nebudou zobrazovat jako číslice, ale jako text, tak vytvoříme sloupec měsíce (sloupec B na listu Data), kde použijeme funkci HODNOTA.NA.TEXT a převedeme hodnoty datumů na názvy měsíců. Ve funkci HODNOTA.NA.TEXT označíme v parametru hodnota datum a v parametru formát uvedeme formát „mmmm“, což znamená, že chceme vrátit název příslušného měsíce.
Nakonec zdrojovou tabulku na listu Data převedeme na oficiální excelovou tabulku. Tabulku převedeme na oficiální excelovou tabulku tak, že tabulku označíme, buď manuálně nebo za pomoci klávesové kombinace CTRL+A a na liště Vložení vybereme Tabulka nebo použijeme další klávesovou kombinaci CTRL+T, což převede obyčejnou tabulku na oficiální excelovou tabulku.
List Přehled je velmi jednoduše naformátovaný. Záhlaví má tmavě modré pozadí, kde jsou jednotlivé části od sebe oddělené bílými čárami, což jsou obrazce. Tyto tvary vložíte do přehledu tak, že na liště Vložení vyberete Obrazce a zde vyberete Obdélník. Změníte u obrazce barevnou výplň na bílou s šířkou 1,67 cm. Dále jsou na Přehledu vložené ikony rovněž z karty Vložení. Pod záložkou Ikony si můžete vybrat z obrovského množství ikon, kterými můžete ozvláštnit interaktivní přehled osobních financí.
Pro konzistentní vzhled Přehledu můžeme na kartě Rozložení stránky vybrat možnost Barvy, kde si můžeme vybrat přednastavenou paletu barev nebo vytvořit vlastní paletu barev, ve které se bude přehled zobrazovat. Na přehledu ke stažení je vybrána přednastavená paleta Modrá II.
Záhlaví přehledu
Prvním údajem v záhlaví je informace o celkových příjmech. Vytvoříme jednoduchou kontingenční tabulku, kde ve filtru bude sloupec platba, kde vybereme příjmy. Do pole hodnoty přesuneme sloupec Příjem. Tím se v kontingenční tabulce zobrazí celkový příjem od počátku roku. Změníme formát na měnu bez desetinných míst. Nesmíme zapomenout kontingenční tabulku pojmenovat. V záložce Analýza kontingenční tabulky najdeme název tabulky a tabulku pojmenujeme Celkové příjmy.
Dalším údajem v záhlaví je informace o celkových výdajích. Vytvoříme další kontingenční tabulku, kde na filtru opět vložíme sloupec platba, kde tentokrát vybereme výdaje a do pole hodnoty vložíme sloupec Výdaj. Opět změníme formát čísla na měnu bez desetinných míst. Nezapomeneme pojmenovat tabulku na Celkové výdaje.
Když máme tyto dvě jednoduché kontingenční tabulky hotové, můžeme provázat buňky na přehledu s kontingenčními tabulkami. Klikneme do buňky H2 na přehledu, napíšeme rovná se a přepneme se na list Analýza, kde označíme buňku celkových příjmů v kontingenční tabulce (A4). Tím se buňky propojí. To samé provedeme i pro celkové výdaje v buňce H3 na listu Přehled. Klikneme do buňky H3, napíšeme rovná se a přepneme se na list Analýza a označíme buňku E4, tedy celkové výdaje v kontingenční tabulce.
Hodnotu celkem uspořeno získáme tak, že v buňce K2 od sebe odečteme hodnotu celkových příjmů a celkových výdajů (H2-H3). Hodnota uspořeno v % je prostě vydělení hodnot uspořeno/příjmy (K2/H2).
Dále je v záhlaví ještě přehled celkových výdajů, a to dle jednotlivých kategorií. Základem bude kontingenční tabulka, kde na řádcích bude sloupec Kategorie, ve filtru bude sloupec Platba, kde jako filtr vybereme Výdaj a do hodnot vložíme sloupec Výdaje. Kontingenční tabulku pojmenujeme Výdaje Kategorie a formát čísla změníme na měnu bez desetinných míst.
K jednotlivým ikonám v záhlaví na listu Přehled teď přiřadíme příslušné výdaje. Opět propojíme buňky na listu Přehled s hodnotami v kontingenční tabulce. Vedle máme ještě procentuální vyjádření, kolika procenty se kategorie výdajů podílí na celkových výdajích. K tomu nepotřebujeme speciální kontingenční tabulku, ale stačí nám pouze jednotlivé hodnoty vydělit buňkou H3 na Přehledu, což je hodnota celkových výdajů.
Záhlaví přehledu je hotové.
Interaktivní přehled osobních financí – grafy
Jako základ jednotlivých grafů na interaktivním přehledu osobních financí poslouží opět kontingenční tabulky.
Sloupcový graf celkových příjmů
Sloupcový graf celkových příjmů je založený na kontingenční tabulce, kde na řádcích jsou uvedené měsíce a v hodnotách příjmy. Sloupec měsíců je sloupec, který byl vytvořený jako pomocný s funkcí HODNOTA.NA.TEXT. Ve filtru kontingenční tabulky je vložený sloupec Platba, kde jsme tentokrát vybrali jako filtr Příjmy. V poli hodnoty je sloupec Příjmy. Kontingenční tabulku jsme pojmenovali na Příjmy sloupcový graf a formát čísla jsme změnili na měnu bez desetinných míst.
Klikneme do kontingenční tabulky a na liště Analýza kontingenční tabulky vybereme Kontingenční graf a vložíme jednoduchý sloupcový graf. Graf podle potřeby zformátujeme. Skryjeme ovládají tlačítka, smažeme legendu grafu, změníme nadpis grafu, odstraníme pomocné vodicí čáry, rozšíříme sloupce grafu a vložíme popisky dat. Když jsme s úpravou grafu hotoví, vyjmeme graf za pomocí klávesové zkratky CTRL+X a vložíme graf na Přehled.
Sloupcový graf celkových výdajů
Stejný postup zopakujeme i pro sloupcový graf celkových výdajů. Graf je založený na kontingenční tabulce, která má ve filtru sloupec Platba, kde jsou vybrané Výdaje. Na řádcích sloupec Měsíce a v hodnotách Výdaje. Kontingenční tabulka je pojmenována jako Výdaje sloupcový graf a formát čísla je měna bez desetinných míst. Opět klikneme do kontingenční tabulky a vybereme vložit kontingenční graf. Provedeme požadované úpravy a formát grafu a když jsme s úpravami hotoví, vyjmeme graf a vložíme ho na Přehled.
Sloupcový graf celkem uspořeno
Poslední sloupcový graf je graf, který zobrazuje celkové množství spořených peněz. Pro tento graf vytvoříme kontingenční tabulku, kde na řádcích bude opět sloupec Měsíce, ale v hodnotách bude tentokrát sloupec Částka, což je sloupec ze zdrojové tabulky, kde se od sebe odečítají příjmy a výdaje. Kontingenční tabulku pojmenujeme jako Celkem uspořeno. Opět vložíme sloupcový kontingenční graf, graf zformátujeme, vyjmeme a vložíme na Přehled.
Plošný graf celkem uspořeno kumulativně
Jako základ kontingenčního plošného grafu poslouží kontingenční tabulka, která bude mít na řádcích měsíce a v hodnotách sloupec Částka. V grafu chceme zobrazit kumulativní nárůst v úsporách. Na sloupec Částka v poli Hodnoty tedy klikneme a vybereme možnost Nastavení polí hodnot. Ve vyskakovacím okně vybereme možnost Zobrazit hodnoty jako. V rozbalovacím seznamu vybereme Mezisoučet v a jako základní pole Měsíc. Tím se z částek úspor v jednotlivých měsících stane kumulativní součet těchto částek.
Kontingenční tabulku pojmenujeme jako Naspořeno kumulativně a formát čísla změníme na měnu bez desetinných míst. Klikneme do kontingenční tabulky a vybereme vložit kontingenční graf plošný. Opět graf podle potřeb zformátujeme a hotový graf vložíme na Přehled.
Prstencový graf struktury výdajů
Dalším grafem na přehledu je prstencový graf, který zobrazuje strukturu výdajů. Základem tohoto grafu je kontingenční tabulka, kde na řádcích je vložený sloupec Kategorie, ve filtru sloupec Platba, kde jsou vybrané výdaje a v poli hodnoty je sloupec Výdaje. V grafu potřebujeme zobrazit procentuální podíl výdajů na celkových výdajích, takže klikneme na pole Výdaje v poli hodnoty a vybereme Nastavení polí hodnot, kde v Zobrazit hodnoty jako vybereme % z celkového součtu.
Kontingenční tabulku pojmenujeme jako Struktura výdajů, a formát čísla změníme na procenta s dvěma desetinnými místy. Klikneme do kontingenční tabulky a vybereme vložit prstencový kontingenční graf. Graf podle potřeby zformátujeme, vyjmeme a vložíme na Přehled.
Stromový graf struktura výdajů dle kategorií
Vedle prstencového grafu je na Přehledu dále graf Stromová mapa. Tento graf zobrazuje jednotlivé výdaje podle kategorií a podkategorií, kde plocha pole je přímo úměrná velikosti výdaje. Základem tohoto grafu je kontingenční tabulka, která je strukturovaná tak, že má v řádcích sloupec Kategorie i sloupec Podkategorie. Ve filtru je sloupec Platba, kde jsou vybrány Výdaje a v poli hodnoty je sloupec Výdaje. Abychom z tabulky mohli vytvořit stromovou mapu, tak musíme změnit rozložení kontingenční tabulky z kompaktního rozložení na tabulkové rozložení. Rozložení tabulky změníme z karty Návrh a Rozložení sestavy, kde vybereme Zobrazit ve formě tabulky. Následně ještě zobrazíme název kategorie na každém řádku tím, že vybereme Rozložení sestavy a Opakovat všechny popisky položek. Mezisoučty v kontingenční tabulce za jednotlivé kategorie nepotřebujeme, takže je vypneme na kartě Návrh, Souhrny a Nezobrazovat souhrny. Kontingenční tabulku pojmenujeme na Stromový graf.
Graf Stromová mapa nelze vytvořit přímo z kontingenční tabulky. Proto si vytvoříme pomocnou mezitabulku. Pokud nemáte v plánu data v Přehledu a ve zdrojové tabulce aktualizovat, tak můžete kontingenční tabulku označit, zkopírovat a vložit jako hodnoty vedle kontingenční tabulky. Pokud máte v plánu data aktualizovat, tak je lepší vytvořit si vedle tabulku, která se bude odkazovat na původní kontingenční tabulku. Z této pomocné mezi tabulky už graf Stromová mapa půjde vytvořit. Označíme mezitabulku a na kartě Vložení vybereme vložit graf Stromová mapa. Graf dle potřeby zformátujeme a vložíme na Přehled.
Spojnicový graf výdaje dle kategorií
Dalším grafem na Přehledu je spojnicový graf, který zobrazuje vývoj výdajů v čase. Základem je kontingenční tabulka, která má na řádcích sloupec Měsíce, ve filtru sloupec Platba, kde jsou vybrané Výdaje. V poli sloupce je sloupec Kategorie a v poli hodnoty kontingenční tabulky je sloupec Výdaje. Kontingenční tabulka je pojmenována jako Výdaje spojnicový graf a formát čísla je měna bez desetinných míst. Z této tabulky vložíme spojnicový kontingenční graf, který podle potřeby zformátujeme a vložíme na Přehled.
Vodopádový graf příjmy a výdaje
Posledním grafem na Přehledu je vodopádový graf, který zobrazuje příjmy a výdaji pro jednotlivé kategorie, včetně celkového součtu, což je hodnota celkových úspor. Základem je kontingenční tabulka, která má na řádcích sloupec Kategorie a v poli hodnoty sloupec Částka. Kontingenční tabulku pojmenujeme jako Vodopádový graf.
Vodopádový graf rovněž nelze přímo vytvořit z kontingenční tabulky. I zde si proto pomůžeme pomocnou mezi tabulkou. Opět, pokud nemáme v plánu data aktualizovat, tak stačí kontingenční tabulku zkopírovat a vložit vedle jako hodnoty. Pokud máme v plánu data aktualizovat, je lepší provázat mezi tabulku s původní kontingenční tabulkou. Následně mezitabulku označíme a na kartě Vložení vybereme Vodopádový graf. Graf zformátujeme a vložíme na Přehled.
Grafová část přehledu je hotová.
Průřezy
Průřezy do Přehledu vložíme tak, že klikneme do libovolné kontingenční tabulky na listu Analýza a na kartě Analýza kontingenční tabulky vybereme Vložit průřez. Objeví se okno, ve kterém si můžeme vybrat, jaké průřezy chceme vložit. Pro účely tohoto přehledu vložíme průřez z Kategorie a Měsíce. Průřezy označíme, vyjmeme a vložíme na Přehled. Abychom ušetřili místo, tak můžeme položky v průřezu zobrazit ve dvou sloupcích. Klikneme na průřez a na kartě Průřez vybereme Sloupce a změníme sloupce na 2.
Aktualizace dat
Poslední, co zbývá je ověřit, že se interaktivní přehled osobních financí bude po přidání nových dat automaticky aktualizovat. Na kartě Nová data jsou vložená nová data, která můžeme zkopírovat a vložit na list Data do zdrojové tabulky. Jelikož pracujeme s oficiální excelovou tabulkou, tak se nová data okamžitě zahrnou do tabulky. Teď stačí kliknout na jakoukoliv kontingenční tabulku a na liště Analýza kontingenční tabulky vybrat možnost Aktualizovat vše. Nová data se automaticky zahrnou do tabulek a zobrazí se na Přehledu.
2 komentáře
Dobrý podvečer!
Nejdříve dovolte abych Vám popřál do Nového roku 2022 hodně štěstí,lásky, splněných přání a zdraví!
Je mi 73 let a v důchodu jsem se začal podrobněji zajímat o Excel hlavně o možnost vedení osobního účetnictví. Postupně jsem se obracel na řadu internetových odborníků, ale jste první člověk který perfektně podává možnosti vedení účetnictví včetně řídícího panelu.
A tak mám k Vám prosbu byla byste ochotna mne pomoci při vylepšování mého účetnictví a případně poskytnou rady !? Základní struktura se skoro schoduje s tou Vaší, jen mám malý problém? Kontigenčky při práci s datem pracuji ( snad pochopíte o co mi jde) s datumem měsíce napřiklad leden 1.1-31.1. Já bych ale potřeboval aby i pracovali s datem pro leden 7.1-6.2. atd v dalších měsících podobně. Zatím jsem nepřišel na to jak toto nastavit v kontingečkách. Máte nějakou radu?!
Kdybych Vám někdy poslal základní soubor excelu mého účetnictví byla byste ochotna se na něj podívat a poradit vylepšení?!
Děkuji předem za pochopení a rady!
S Pozdravem
Oldřich Svoboda senior
Moc děkuji za Váš vřelý komentář, poslala jsem Vám email, na který mi můžete zaslat ukázkový soubor a určitě něco vymyslíme 🙂