Rodinný rozpočet realita versus plán| Interaktivní přehled v Excelu

V dnešním videu tematicky navážeme na dva interaktivní dashboardy, které jsme v Excelu na Akademii Excelu vytvořili. První interaktivní dashboard byl přehled osobních financí v Excelu, kde jsme v Excelu vytvořili přehled skutečných příjmů a výdajů. Díky tomuto přehledu jsme získali náhled na skutečné příjmy během roku a rovněž strukturu výdajů. V nedávném videu jsme zase vytvořili roční plán rodinných financí a jejich přehled. V dnešním videu si vytvoříme další interaktivní přehled založený na srovnání skutečných příjmů a výdajů a ročním plánu osobních financí.

Excelové soubory ke stažení

Rodinný rozpočet realita vs plán – excelový soubor k dokončení

Pro stažení hotového přehledu vyplňte prosím jméno a email.

Rodinný rozpočet realita versus plán

Po dokončení bude přehled zobrazovat rozdíly mezi plánovanými příjmy a výdaji se skutečnými příjmy a výdaji, zobrazí vám rovněž v jakých výdajových kategoriích utrácíte více než máte stanoveno v plánu a co jsou tři nejproblematičtější položky z pohledu dodržování plánu. Celý přehled bude interaktivní, takže si v něm budete moci filtrovat po jednotlivých měsících. Získáte tak skvělý nástroj na kontrolu svých rodinných financí. 

Rodinný rozpočet realita versus plán 1
Obrázek č.1 Hotový interaktivní přehled rodinných financí skutečnost versus plán

V Excelu k dokončení máme předpřipraveno 5 listů. Na listu Přehled budeme tvořit interaktivní přehled srovnání reality a plánu rodinných financí. Na listu výpočty shromáždíme všechny výpočty, které budou zdrojem přehledu, přičemž zdrojem přehledu budou kontingenční tabulky. Listy Realita a Plán budou obsahovat tabulky, které budou zdrojem výpočtů. Tyto tabulky propojíme tak, aby se z nich mohl vytvořit přehled. Na listu Nová data mám nakonec schovaná nová data, pomocí kterých ověříme, že přehled bude plně dynamický a bude reagovat na nově přidaná data do tabulek.

A vrhneme se rovnou na tvorbu zdrojových tabulek a začneme na listu Plán.

Plán rodinných financí

Aby pro nás bylo vyplňování příjmů a výdajů jednodušší, tak k vyplnění příjmů a výdajů vytvoříme pomocnou tabulku. V této pomocné tabulce si nejprve rozmyslíme, jaké příjmy a jaké výdaje budeme chtít během roku sledovat. 

Do pomocné tabulky cvičně vyplníme několik prvních položek. Začneme s příjmy. Naše fiktivní domácnost má dva příjmy, takže jako položku vyplníme Příjem 1 a Příjem 2. Mzda patří do kategorie, kterou nazveme třeba Mzda. A typ je příjem. Řekněme, že každý rok dostáváme ještě roční bonusy. S tímto bonusem počítáme i v nadcházejícím roce, takže vyplníme jako položku Bonus, kategorie může být rovněž Mzda a typ je Příjem. S žádnými jinými příjmy během roku nepočítáme. Jako další položky vyplníme náklady. Řekněme, že platíme hypotéku. Jako položku tedy vyplníme hypotéku. Přidáme kategorie, v tomto případě zařadíme hypotéku do nákladové kategorie Bydlení a typ je Výdaj. U bydlení zůstaneme a rovnou vyplníme i zálohu na elektřinu a plyn, které rovněž souvisí s bydlením. Takže jako položky vyplníme elektřina a plyn a zařadíme je do kategorie Bydlení a typ Výdaj. 

Takto postupně vyplníme celou pomocnou tabulku, s to se všemi příjmy a výdaji, které během roku hodláme sledovat.

Rodinný rozpočet realita versus plán 2
Obrázek č.2 Pomocná tabulka na listu Plán

Hlavním účelem pomocné tabulky je rozmyslet si, co všechno chcete sledovat a následně tabulku použít jako návod při dalším vyplňování tabulek, a to zejména proto, abychom na žádné příjmy a výdaje nezapomněli.

Teď můžeme přistoupit k vyplnění zdrojové tabulky s plánem. Nejprve ale změníme tabulku na oficiální excelovou tabulku. Označíme záhlaví tabulky a první řádek (A2:E3) a zmáčkneme klávesovou kombinaci CTRL+T, potvrdíme, že tabulka má záhlaví a nezapomeneme tabulku pojmenovat, nazveme ji třeba Plán. 

Rodinný rozpočet realita versus plán 3
Obrázek č.3 Vyplněná tabulka plánu na listu Plán

Na ukázku zkusíme vyplnit několik prvních údajů. Využijeme pomocnou tabulku a zkopírujeme hodnoty z pomocné tabulky a vložíme je do tabulky. Doplníme datum do sloupce Datum. Všechny tyto položky plánujeme nejprve pro leden, takže vyplníme lednové datum. Je jedno jaké datum zde vyplníte, pokud se bude jednat o lednové datum, kontingenční tabulka bude pracovat s měsícem a nikoliv s přesnými dny. 

Vyplníme tedy třeba 1.1.2023. Nechceme zde ale datum v tomto formátu, bylo by lepší zobrazit ho jako měsíc a rok. Jelikož tento formát budeme chtít v celém sloupci, tak neklikneme na datum, ale na záhlaví sloupce (A2), až se z kurzoru myši stane černá šipka a teď teprve klikneme pravým tlačítkem myši a vybereme Formát buněk. 

Vybereme vlastní formát a do pole napíšeme mmmm rrrr. Formát potvrdíme a datum se změnilo na měsíc a rok. Teď nakopírujeme toto datum pro všechny tyto položky.

Rodinný rozpočet realita versus plán 4
Obrázek č.4 Nastavení vlastního formátu ve sloupci Datum

Teď zbývá pro každou položku plánu vyplnit hodnotu. Řekněme, že plánujeme první příjem ve výši 35 000 Kč (E3) a druhý příjem ve výši 45 000 Kč (E4). Následuje roční bonus, ten dostáváme každoročně v prosinci, takže v lednu bude bonus ve výši nula (E5). Splátku hypotéky máme ve výši 15 000 Kč (E6) a zálohy na elektřinu a plyn třeba ve výši 2 000 Kč (E7) a 3 500 Kč (E8). 

Stejným stylem bychom vyplnili všechny hodnoty pro leden.  

Když máme leden kompletně vyplněný, tak můžeme tyto hodnoty opět vzít, zkopírovat, vložit je pod leden a celý proces vyplnění opakovat pro únor. Vidíme, že funkce se doplnila automaticky do všech řádků. Musíme ale vyplnit správné datum. Vyplňujeme hodnoty pro únor, takže napíšeme 1.2.2023 a i na toto datum se přenesl vlastní formát. Datum pošleme dolů a vyplníme částky, které plánujeme i únorových položek.

Takto bychom postupně vyplnili plán pro všech 12 měsíců. 

Skutečné příjmy a výdaje

Když máme hodnoty na listu plán vyplněné, tak se přepneme na list Realita. Stejně jako tabulka plánu je koncipována i tabulka realita. Akorát s tím rozdílem, že tuto tabulku budete vyplňovat měsíčně, a to na základě vašich reálných příjmů a výdajů. Na ukázku zkusíme vyplnit tři první měsíce, abychom mohli vytvořit přehled a viděli jste, jak bude srovnání vypadat. Až bude přehled hotový, tak tyto hodnoty budete moci smazat a doplnit skutečnými hodnotami.

Opět nejprve z tabulky vytvoříme oficiální excelovou tabulku. Označíme záhlaví a první řádek (A2:F3) a zmáčkneme CTRL+T a potvrdíme. Tabulku pojmenujeme jako realita. Teď můžeme začít s vyplňováním. Začneme lednem, využijeme pomocné tabulky na listu Plán, zkopírujeme pomocné hodnoty a vložíme je do tabulky realita. Opět budeme chtít u každého měsíce datum a to ve stejném formátu jako v plánu, takže opět klikneme do záhlaví a vybereme Formát buněk a zde vlastní formát. Zde opět napíšeme mmmm rrrr a potvrdíme.

Na rozdíl od tabulky plánu zde máme ještě jeden sloupec navíc, který mám pojmenovaný jako Měsíc (G2). Jelikož víme, že přehled budeme tvořit pomocí kontingenčních tabulek, tak tento sloupec slouží k tomu, abychom v něm datum převedli na slovní označení měsíce, jelikož v kontingenční tabulce budeme chtít zobrazit měsíce slovy a ne čísly.

Do sloupce Měsíc do buňky G3 vložíme funkci HODNOTA.NA.TEXT, kde nejprve označíme datum a následuje formát. V kontingenční tabulce budeme chtít zobrazit měsíce slovy, takže vybereme formát „mmmm“ a funkci potvrdíme. 

= HODNOTA.NA.TEXT (A3; “mmmm”)

Teď můžeme vyplnit cvičné hodnoty pro leden. Ve skutečnosti, až budete mít přehled připravený na toto místo vyplníte reálné příjmy a výdaje, které můžete spočítat třeba na základě bankovního výpisu. Vyplníme tedy reálné hodnoty a vyplníme ještě únor a březen. Opět položky zkopírujeme pod sebe, doplníme únorové datum, stáhneme datum pro všechny položky. Funkce pro měsíc se v oficiální excelové tabulce doplnila, takže můžeme vyplnit únorové částky. To samé zopakujeme i pro březen.

To nám bude pro ukázku tvorby přehledu stačit. Dvě zdrojové tabulky máme hotové.

Rodinné finance plán versus realita 2
Obrázek č.5 Vyplněná tabulka Skutečnost

Příprava zdrojových dat pro kontingenční tabulku

Ještě než se vrhneme do tvorby kontingenčních tabulek, tak musíme udělat jeden důležitý krok. Standardní kontingenční tabulka dokáže pracovat pouze s jednou zdrojovou tabulkou. My zde máme ovšem dvě tabulky, jednu pro plán a druhou pro realitu. Abychom mohli v kontingenční tabulce pracovat s oběma, musíme tabulky buď propojit datovým modelem, což není předmětem tohoto videa a nebo spojit tabulky do jedné. Nejjednodušší, jak to v tom případě udělat, je připojit hodnoty z plánu do tabulky realita. Tabulka realita bude následně zdrojem kontingenční tabulky. I proto jsme obě tabulky strukturovali stejně, aby se nám to teď lépe spojovalo. Do tabulky Realita tedy přidáme ještě jeden sloupec, který nazveme Plán. Abychom v kontingenční tabulce rozeznali sloupce, tak sloupec částka ještě přejmenujeme na Realita. Ve sloupci Plán dohledáme plánované příjmy a výdaje pro jednotlivé měsíce a to na základě vyhledávacích funkcí INDEX & POZVYHLEDAT.

Začneme s funkcí INDEX, kde označíme, co hledáme. Snažíme se přiřadit plánované příjmy a výdaje, takže označíme na listu Plán sloupec s částkou. Jelikož máme tabulku ve formátu oficiální tabulky, tak nemusíme označovat celý sloupec, ale stačí kliknout na záhlaví tabulky a celý sloupec se označí. Následuje funkce POZVYHLEDAT, anglicky funkce MATCH, kde musíme spojit dvě kritéria dohromady, a to je datum a položku. Proč? Protože funkce POZVYHLEDAT najde vždy jen první položku, v případě, že máte v seznamu duplicity. My v seznamu duplicity máme, jelikož se každá položka v plánu vyskytuje dvanáctkrát. Pokud bychom použili funkci POZVYHLEDAT bez spojení měsíce a položky, tak by se nám všude vrátili pouze lednové hodnoty z plánu.

Ve funkci POZVYHLEDAT nejprve označíme první položku, kterou hledáme, napíšeme ampersand a označíme datum, které hledáme. Teď tedy hledáme první příjem v lednu. A kde tyto hodnoty hledáme? Hledáme je na listu Plán. Musíme zde dodržet pořadí označení v parametru hledat. Nejprve jsme označili hledanou položku, takže označíme sloupec s položkami, ampersand a sloupec s datumy. A hledáme přesnou shodu, takže nulu. Ukončíme závorky a funkci potvrdíme. Namátkově zkontrolujeme, zda funkce reaguje. Na listu plán zkusíme změnit příjem v únoru na nějaké velké číslo, třeba 100 000 Kč. Přepneme se na Realitu a vidíme, že funkce reaguje správně. Vrátíme se na list Plán a hodnotu změníme nazpět.

Teď máme zdrojová data připravená a můžeme začít tvořit přehled.

Rodinný rozpočet realita versus plán 6
Obrázek č.6 Vyhledání hodnot plánu do tabulky Skutečnost pomocí vyhledávacích funkcí INDEX & POZVYHLEDAT

Přehled

Začneme hned prvním grafem a kontingenční tabulkou.

Sloupcový graf – srovnání příjmů, výdajů a úspor

Jako první máme na přehledu sloupcový graf, ve kterém porovnáváme skutečné příjmy, výdaje a úspory s plánem. Vytvoříme tedy první kontingenční tabulku. Klikneme na list Realita, klikneme do zdrojové tabulky a na kartě Vložení vybereme kontingenční tabulka. Jako zdroj zde máme správně uvedený název oficiální excelové tabulky a umístění vybereme na listu Výpočty. Potvrdíme.

V kontingenční tabulce chceme vidět srovnání po měsících, takže na řádky vložíme sloupec s měsíci. Jelikož ale chceme vidět měsíce slovy a nikoliv čísly, tak musíme na řádky vložit náš pomocný sloupec Měsíce, který jsme vytvořili pomocí funkce HODNOTA.NA.TEXT. Do pole sloupce musíme vložit typ a do pole hodnoty vložíme nejprve Realitu a následně sloupec Plán. 

Vytvořila se kontingenční tabulka, kde vidíme skutečný příjem a vedle něho příjem, který jsme měli v plánu a vedle toho skutečné výdaje a souhrnné výdaje, které jsme plánovali. A to pro jednotlivé měsíce a celkem. V grafu máme nicméně i sloupce pro úspory. Což je v podstatě pouze rozdíl mezi příjmy a výdaji. V tomto případě nemáme způsob, jak v kontingenční tabulce spočítat rozdíl mezi těmito dvěma položkami, takže si pomůžeme pomocnou tabulkou.

Obrázek č.7 Kontingenční tabulka pro sloupcový graf pro srovnání příjmů, výdajů a úspor

Vedle kontingenční tabulky vytvoříme pomocnou tabulku, kde na řádcích bude příjem a výdaj a úspora, ve sloupcích bude Realita, Plán a Rozdíl. Teď vyplníme tuto pomocnou tabulku napojením na kontingenční tabulku. Klikneme do buňky příjem realita (H7), napíšeme rovná se a označíme buňku celkového příjmu v kolonce realita (B9). Musíme označit buňky celkového součtu, aby to fungovalo. 

Všimněte si, že se neoznačila pouze buňka v kontingenční tabulce, ale vytvořila se celá funkce ZÍSKATKONTDATA (buňka H7). Tato funkce zajistí, že s tím, jak se bude měnit tvar kontingenční tabulky, tak že se vždy do této pomocné tabulky propíšou správná data. Pokud po kliknutí do kontingenční tabulky tuto funkci nevidíte, tak ji můžete zapnout tím, že kliknete do kontingenční tabulky a na kartě Analýza kontingenční tabulky vyberete Možnosti a zde zaškrtnete Generovat data kontingenční tabulky.

Stejně tak provážeme ještě buňky příjmu plán a výdajů pro skutečnost a plán. 

Zbývá dopočítat pole pro úspory, což je příjem mínus výdaj, jak pro realitu, tak plán. To samé uděláme i pro sloupec rozdíl. Zde odečteme Realitu mínus plán pro příjmy a výdaje. 

Pomocná tabulka je hotová a můžeme vytvořit graf. 

Rodinné finance plán versus realita 1
Obrázek č.8 Pomocná tabulka napojená na kontingenční tabulku

Pro vytvoření prvního grafu nám stačí označit tuto pomocnou tabulku mimo sloupec rozdíl (G6:I9). Označíme tabulku a na kartě Vložení vybereme sloupcový graf. Graf se vložil, akorát ho potřebujeme na listu přehled, takže ho označíme a vyjmeme ho pomocí kláves CTRL+X. Vložíme graf na list Přehled a upravíme jeho velikost.

Poslední, co zbývá je upravit formát grafu. Název grafu změníme na Příjmy, výdaje a úspory. Písmo zmenšíme a celý název grafu posuneme do levého horního rohu. Smažeme pomocné vodicí čáry. Klikneme na sloupce označující realitu a změníme barevnou výplň. Vybereme barvu, kterou budeme označovat všechny reálné výdaje, třeba tmavě modrou. Teď označíme druhou sadu sloupců, označující plán a vybereme třeba světle modrou. Teď klikneme na sloupce a vybereme Formát datové řady. Rozšíříme sloupce, a když máme sloupce značené, tak rovnou vložíme popisky dat. Tím, že máme popisky dat, tak můžeme smazat osu y.

Rodinný rozpočet realita versus plán 8
Obrázek č.9 Sloupcový graf srovnání příjmů, výdajů a úspor

Sloupcový graf – rozdíl mezi plánovanými a skutečnými příjmy, výdaji a úsporami

Druhý grafem na Přehledu je sloupcový graf, který zobrazuje rozdíl mezi skutečnými příjmy, výdaji a úsporami. K vytvoření tohoto grafu nám poslouží v minulém kroku vytvořená pomocná tabulka, kde jsme v posledním sloupci již tento rozdíl spočítali (L6:L9). Navíc ale chceme, aby graf zobrazoval kladné částky zeleně a záporné částky červeně. Musíme k této tabulce tedy dodělat podmíněné formátování do grafu

Vedle sloupce rozdílu vytvoříme sloupec kladné (M6), kde použijeme podmínkovou funkci KDYŽ, ve které napíšeme podmínku, že pokud hodnota rozdílu v buňce L7 bude vyšší než nula, že chceme vrátit hodnotu z buňky L7, jinak chceme vrátit funkci NEDEF(). Funkci potvrdíme a stáhneme dolů.

Vedle vytvoříme druhý sloupec, který nazveme Záporné (N6), kde opět ve funkci KDYŽ napíšeme, že pokud tentokrát hodnota v buňce L7 bude nižší než 0, že chceme vrátit hodnotu z buňky L7, jinak chceme vrátit funkci NEDEF(). 

Rodinný rozpočet realita versus plán 9
Obrázek č.10 Kontingenční tabulka a pomocná tabulka pro tvorbu sloupcového grafu

Teď můžeme přistoupit k vytvoření grafu. Nejprve označíme první sloupec, včetně buňky záhlaví (I6:I9), stiskneme klávesu CTRL, držíme klávesu CTRL a označíme druhý sloupec s rozdílem (L6:L9). Klikneme na list Vložení a vybereme sloupcový graf. Teď se vložil základní sloupcový graf.

Nicméně do grafu musíme dostat ještě dva pomocné sloupce pro podmíněné formátování. Klikneme na graf a vybereme Vybrat data. Klikneme na Přidat. Název řady je název ze záhlaví Kladné (M6) a hodnoty jsou hodnoty ve sloupci Kladné (M7:M9). Do grafu se přidala sada dat pro kladné hodnoty. 

To samé uděláme i pro záporné hodnoty. Klikneme na Přidat a znovu jako název označíme záhlaví sloupce Záporné (N6) a jako hodnoty označíme sloupec s hodnotami ve sloupci záporné (N7:N9). Potvrdíme. Klikneme na sloupce a vybereme Formát datové řady, kde vybereme 100% překrytí řad. Tím se sloupce překryjí a teď už stačí jen změnit barvy. Kladné hodnoty chceme zeleně, takže označíme kladné sloupce a vybereme zelenou barvu. Teď označíme záporné hodnoty a vybereme červenou barvu. Teď budou barvy sloupce interaktivně reagovat na podkladové hodnoty.

Rodinný rozpočet realita versus plán 10
Obrázek č.11 Sloupcový graf zobrazující rozdíl mezi plánem a realitou

Graf vyjmeme a vložíme ho na list Přehled pod první graf. Upravíme jeho velikost a nakonec ho zformátujeme.

Průřez

Aby přehled neukazoval pouze souhrn za první tři měsíce, tak na přehled ještě rovnou vložíme i průřez, který nám dovolí filtrovat data. Na listu Výpočty klikneme do kontingenční tabulky a na kartě Analýza kontingenční tabulky vybereme Průřez. Vybereme co chceme, aby průřez filtroval, v tomto případě chceme měsíce. Potvrdíme vložení průřezu a rovnou ho značíme, vyjmeme, a vložíme na Přehled. Pomocí průřezu zjistíme, jak si v jednotlivých měsících stojíme oproti plánu. Pokud filtr z průřezu odebereme, tak přehled zobrazuje srovnání za první tři měsíce.

Aby nám průřez ladil s přehledem, tak ho trochu zformátujeme. Upravíme jeho velikost tak, aby se vešel do záhlaví. Standardně se položky v průřezu zobrazují pod sebou. V přehledu máme ale položky vedle sebe. Klikneme do průřezu a vybereme Průřez a nastavíme počet sloupců. Nastavíme 12, jelikož postupně budeme mít 12 měsíců. Ještě musíme barevně sladit průřez s pozadím záhlaví. Formát nastavíme pomocí Stylu průřezů > Nový styl průřezu. Když máme styl průřezu nastavený, tak můžeme pokračovat s tvorbou grafů.

Obrázek č.12 Vložení průřezu z měsíců

Pruhový graf – zobrazení plánovaných a skutečných výdajů dle kategorie

Dalším grafem na přehledu je pruhový graf, který zobrazuje kategorie výdajů a to realitu a plán. Na listu Výpočty tedy potřebujeme vytvořit druhou kontingenční tabulku. Pomůžeme si tím, že zkopírujeme první tabulku a upravíme pole. Z řádků vyhodíme měsíce a místo toho tam vložíme sloupec Kategorie. Sloupec typ přehodíme ze sloupců do filtru a v poli hodnoty necháme realitu a plán. Ve filtru typ necháme pouze výdaje. V grafu nepotřebujeme řádky a sloupce celkových součtů, takže je vypneme.

Rodinný rozpočet realita versus plán 12
Obrázek č.13 Kontingenční tabulka pro pruhový graf

Teď máme vedle sebe srovnání skutečných výdajů po kategoriích a plánovaných výdajů. Teď stačí kliknout do kontingenční tabulky a na kartě Vložení vybrat Graf a pruhový graf. Označíme graf, vyjmeme ho a vložíme na Přehled. Graf upravíme a zformátujeme.

Rodinný rozpočet realita versus plán 13
Obrázek č.14 Pruhový graf rozdíl mezi skutečnými a plánovanými výdaji podle kategorie

Pruhový graf – rozdíl v plánovaných a skutečných výdajích dle kategorie

Dalším grafem je pruhový graf, který zobrazuje rozdíl mezi výdaji skutečnými a plánovanými. Na rozdíl od první tabulky, kde nebylo možné od sebe v kontingenční tabulce odečíst příjmy a výdaje, jelikož se jednalo o pole v jednom sloupci, tak zde pole odečíst můžeme, jelikož se jedná o dva různé sloupce Realitu a Skutečnost.

Nejprve vytvoříme kontingenční tabulku. Na řádky vložíme kategorie, do filtru vložíme typ. V poli hodnoty chceme vidět rozdíl mezi Realitou a Plánem. Toto pole ale musíme dopočítat. Klikneme do kontingenční tabulky a na kartě Analýza kontingenční tabulky vybereme Pole, položky a sady. Zde vybereme počítané pole. Počítáme rozdíl ve výdajích, takže pole nazveme Rozdíl výdaje. Klikneme do pole vzorec, smažeme nulu a necháme pouze rovná se. Dole ve výběru Pole vidíme jednotlivé sloupce, se kterými můžeme počítat. My chceme spočítat rozdíl výdajů, takže vybereme sloupec Realita napíšeme mínus a vybereme sloupec Plán. 

Potvrdíme a nově vytvořené pole vložíme do pole hodnoty. 

Obrázek č.15 Kontingenční tabulka a výpočet počítaného pole pro rozdíl ve výdajích

A máme dopočítaný rozdíl mezi jednotlivými položkami výdajů. Odečítáme plán od reality, takže kde máme mínusové hodnoty, tam jsme ušetřili, kde máme plusové hodnoty, tam jsme přesáhli plán a kde je nula, tam jsme plán dodrželi.

Klikneme do tabulky a vložíme pruhový graf.

Graf zformátujeme podle potřeba, vyjmeme ho a vložíme ho na Přehled.

Rodinný rozpočet realita versus plán 15
Obrázek č.16 Pruhový graf zobrazující rozdíl ve výdajích dle kategorií

Koláčový graf – struktura skutečných výdajů dle kategorií

Dalším grafem je koláčový graf, který ukazuje procentuální rozložení skutečných výdajů z celkových výdajů. Vytvoříme tedy ještě jednu kontingenční tabulku, kde na řádcích budou výdajové kategorie a v poli hodnoty bude sloupec skutečné výdaje. 

Rodinný rozpočet realita versus plán 16
Obrázek č.17 Kontingenční tabulka pro strukturu výdajů

Klikneme do tabulky a vložíme koláčový graf. Graf vyjmeme, vložíme ho na list Přehledu, zformátujeme jeho velikost, změníme barevné výplně a graf zformátujeme.

Rodinný rozpočet realita versus plán 17
Obrázek č.18 Koláčový graf struktury výdajů po kategoriích

Pruhový graf – výdaje, které nejvíce přesahují plánované výdaje

Posledním grafem na přehledu je pruhový graf, který zobrazuje tři položky výdajů, které nejvíce přesahují plán. To znamená nejproblematičtější položky, u kterých plán nedodržujeme. Pro tuto tabulku máme spočítané všechny veličiny, takže zkopírujeme tabulku a na řádky vložíme kategorie, do filtru typ, a do pole hodnoty rozdíl, který jsme spočítali. V tabulce chceme zobrazit pouze tři položky, které přečerpáváme, takže klikneme do záhlaví a vybereme Filtry hodnot a prvních 10. Vybereme místo 10 číslo 3 a chceme výdaje zobrazit dle pole rozdílů ve výdajích. 

Rodinný rozpočet realita versus plán 18
Obrázek č.19 Kontingenční tabulka pro pruhový graf přečerpaných výdajů

Potvrdíme a tabulka se omezila na pouze tři výdaje. Abychom v grafu zobrazily tři sloupce od nejvyšší hodnoty po nejnižší, tak musíme v tabulce seřadit hodnoty obráceně, tedy od nejnižších po nejvyšší. Klikneme do tabulky a vložíme pruhový graf. Graf opět vyjmeme, vložíme ho na Přehled u zformátujeme.

Přehled je skoro hotový.

Obrázek č.20 Pruhový graf přečerpaných výdajů

Záhlaví

Poslední, co chybí je dodělat záhlaví. V záhlaví máme interaktivní větu, která nás informuje, zda jsme celkově za plánem nebo ne. Jedná se jen o podmínkovou funkci KDYŽ, která je zkombinovaná s textem. Pro výpočet máme vše co potřebujeme, takže klikneme do buňky D2 a začneme s funkcí KDYŽ. 

Ve funkci KDYŽ bude podmínka, že pokud je buňka úspor v pomocné tabulce na listu Výpočty (J9) vyšší jak nula, tak jsme ušetřili oproti plánu a chceme vrátit větu „Dobrá práce, ušetřil jsi oproti plánu“ a pokud podmínka splněná nebude, tak chceme vrátit větu „Utraceno více oproti plánu“. Funkci potvrdíme.

= KDYŽ (Výpočty!J9>0;”Dobrá práce, ušetřil jsi oproti plánu”; Utraceno více oproti plánu”)

V hotovém přehledu máme ovšem v této větě ještě uvedeno, o kolik jsme buď ušetřili nebo neušetřili. Vrátíme se k podmínce a upravíme funkci KDYŽ o částku. Klikneme do vzorce a částku chceme vložit doprostřed věty za ušetřil jsi. Chceme do této části věty vložit hodnotu, kterou jsme ušetřili, takže musíme buňku spojit pomocí ampersandů. Napíšeme ampersand, označíme buňku s částkou na listu Výpočty (J9) a další ampersand. Ještě musíme ukončit uvozovky u textu, který jsme vložením hodnoty přerušili. Potvrdíme a ve větě se objevila částka. 

=KDYŽ (Výpočty!L9>0;”Dobrá práce, ušetřil jsi “&Výpočty!J9&” oproti plánu”; Utraceno více oproti plánu”)

To ovšem ještě není vše, potřebujeme, aby částka byla ve správném formátu, takže musíme částku z buňky zabalit do funkce HODNOTA.NA.TEXT. Klikneme znovu do funkce a zabalíme tuto buňku do funkce. Necháme ampersand, napíšeme funkci HODNOTA.NA.TEXT, jako buňku necháme tuto buňku a zvolíme si formát, tedy „0 00 Kč“. Za funkcí musí znovu následovat ampersand. Ještě potřebujeme, aby mezi textem a číslem byla mezera, takže za jsi vložíme mezeru, stejně jako před slovo oproti. 

= KDYŽ (Výpočty!L9>0;”Dobrá práce, ušetřil jsi “&HODNOTA.NA.TEXT(Výpočty!J9;” 0 00 Kč”) &” oproti plánu”; Utraceno více oproti plánu”)

Potvrdíme a zkontrolujeme výsledek. Vypadá to dobře, takže se k funkci naposledy vrátíme a upravíme i druhou část funkce, když podmínka splněná nebude. Zkopírujeme celou část včetně ampersandů a funkce HODNOTA.NA.TEXT a vložíme je do věty, kam potřebujeme. Upravíme podle potřeby text a mezery a potvrdíme. 

Obrázek č.21 Interaktiní věta v záhlaví přehledu

Poslední částí přehledu je zobrazení toho, která barva znázorňuje hodnoty plánu a které hodnoty reality. Do záhlaví nakreslíme obrazec, u kterého změníme barevnou výplň na bílo. Dále do sešitu vložíme obrazec kruhu, a abychom kruh nezdeformovali, tak budeme držet klávesu SHIFT a nakreslíme kruh. Změníme barevnou výplň na světle modrou, stejnou barvu, kterou máme v grafech. Kruh zkopírujeme, vložíme a změníme barevnou výplň na tmavě modrou. Kruhy usadíme v obrazci a změníme jejich umístění na vpřed. Vedle kruhů nakreslíme textové pole a ke světlému kruhu napíšeme Plán a k tmavému kruhu napíšeme Skutečnost. Nakonec změníme barvu ohraničení textového pole na bílo. 

Poslední zkouška přehledu. Zkontrolujeme, že máme všechny kontingenční tabulky propojené s průřezem. Klikneme na průřez a vybereme připojení sestavy, u každé tabulky musíme mít zaškrtnuto, že průřez tabulku ovládá. 

Nakonec smažeme legendy ze všech grafů, jelikož barevné rozlišení máme v záhlaví.

Gratuluji. Váš osobní přehled srovnání plánovaných příjmů a výdajů a reálných příjmů a výdajů je hotový.

Rodinný rozpočet realita versus plán 1
Obrázek č.22 Hotový přehled včetně záhlaví

Poslední co zbývá je zkusit, zda bude přehled reagovat na nově přidaná data. Přepneme se na list Nová data, zkopírujeme nová data a vložíme je na list Realita. Překlikneme se na list Výpočty, označíme libovolnou kontingenční tabulku a vybereme Obnovit. Překlikneme se na list Přehled a data za nový měsíc máme přidaná.

Vše je hotové. Teď stačí nahradit tato cvičná data na listu Realita skutečnými daty a můžete sledovat svůj vlastní rozpočet vůči plánu. Tím, že jako zdroj slouží oficiální excelové tabulky, tak je vše interaktivní a vždy stačí pouze přehled obnovit díky Aktualizovat.

MOHLO BY VÁS ZAJÍMAT

4 komentáře

  1. 👍❤️
    Mohu mít dotaz? Ve svém osobním účetnictví mám dvě složky, které bych rád při zápisu a v přehledech rozdělil na podsložky a je to SIPO ( voda, elektřina, rozhlas, televize, poštovné ) a SPOŘENÍ ( osobní, dítě 1 a 2, vnouče 1 a 2 ). Jak by jste to vyřešila ve svém Interaktivním přehledu osobních financí.
    Děkuji předem za radu.
    Rád bych Vám nějak poděkoval za Vaše náměty, dává vám to jistě zabrat připravovat to vše tak perfektně. Můžete mi poskytnout číslo Vašeho bankovního účtu?!
    S pozdravem
    Oldřich Svoboda.

    1. Dobrý den, předem děkuji za váš dotaz a rovněž vám přeji vše nejlepší do Nového roku 🙂 Nejspíše bych do zdrojové tabulky přidala další sloupec, například Podkategorie, kde bych u vybraných položek dopsala zda se jedná o SIPO, SPOŘENÍ nebo třeba všeobecnou platbu. Na přehled by se následně dal přidat průřez, který by vám dovoloval si přehled filtrovat buď podle kategorie nebo podkategorie. Pokud to máte složitější, tak mi to klidně pošlete a můžeme se na to podívat, email znáte 🙂

  2. Dobrý den!
    K tomuto videu mám jednu drobnou připomínku a dotaz. Při vyplňování staženého souboru dle Vašeho videa jsem narazil na dvě drobnosti. první: Na záložce realita jsem zadal do položky datum datum 7.1.2023 a vyplnil další údaje. Ale položka Plán zůstala prázdná!!! Po řadě pokusů a omylů jsem zjistil, že nemohu zadat reálné datum provedené operace, ale pouze ve formátu 1.1.2023. Zadal jsem tři různá data pro nákup potravin v jednom měsíci a opět položka plán zůstala prázdná. Podle mne chyba. Mělo by se pracovat s reálným datem. Můžete navrhnout jak toho docílit?!
    Druhá drobnost. Například plat ( příjem) se mi zobrazil na přehledu v grafech ve výdajích. Po řadě pátrání jsem zjistil, že ve vašem souboru v kontingenčních tabulkách máte zapnutou položku příjem a nikoliv výdaj. Opravil jsem to, vše pak bylo v pořádku.
    Jsem všetečka co?

    S pozdravem
    Oldřich Svoboda.

    1. Dobrý den, ano máte pravdu, že v tomto zjednodušeném přehledu se pracuje pouze s měsícem, a nikoliv s přesným dnem. S jednotlivými kategoriemi příjmů a výdajů se pracuje na bázi již agregovaných součtů, kdy do přehledu doplníte souhrnnou informaci o výdajích za jídlo, bydlení atd. Přehled byl dělaný na měsíční srovnání, tudíž v tomto provedení reálný den nehraje roli. Ale chápu vaši připomínku. Pokud byste chtěl vytvořit přehled, kde budete reálně doplňovat transakci po transakci, tak by nejspíše bylo nejlepší propojit tabulky přes datový model a nebo si udělat pomocnou tabulku. Ideálně v jedné tabulce zaznamenávat transakce den po dni a následně do druhé tabulky uvést jen agregace pomocí funkcí jako SUMIFS a COUNTIFS a v této tabulce následně dohledat položky z plánu.

Napsat komentář

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

AKADEMIE EXCELU

EXCELOVÝ SOUBOR
KE STAŽENÍ