Graf vývoje účtu na základě účetního deníku.
S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.
Tento článek se věnuje tomu, jak z účetního deníku (nebo obdobného záznamu) vytvořit graf pohybu na vybraném účtu.
Výsledkem naší práce bude tento graf:
Pro jeho vytvoření vyjdeme z tohoto účetního deníku (zadání i řešení je možné stáhnout tady).
Prvním krokem bude to, že ke každému řádku přiřadíme měsíc, kdy se případ stal. K tomu použijeme funkci MĚSÍC, kterou převedeme datum na měsíc. Výsledkem je nový sloupec s číslem měsíce:
Čísla od jedničky do dvanáctky změníme na názvy měsíců (např. pomocí funkce INDEX).
- =INDEX($I$5:$I$16;MĚSÍC(B:B);0)
Připravíme si buňky, do kterých vyplníme sledovaný účet, informaci o tom jestli je tento účet aktivní nebo pasivní, a počáteční stav.
A teď přijde to nejlepší - funkce SUMIFS. Zapíšeme ji takto:
=(SUMIFS(D:D;E:E;$J$2&"*";G:G;I:I)-SUMIFS(D:D;F:F;$J$2&"*";G:G;I:I))*KDYŽ($J$3="pasivní";-1;1)
Vysvětlení:
- První SUMIFS vysčítá všechny částky ze sloupce D, které mají ve sloupci E (tam jsou účty MD) účet začínající tím, co je v buňce J2, a ve sloupci G, kde jsou měsíce, konkrétní sledovaný měsíc.
- Druhá SUMIFS provede obdobné vysčítání položek, které mají příslušný účet ve sloupci F (dal). Výsledek této druhé funkce SUMIFS se od první odečte.
- Pokud pracujeme s aktivním účtem, tak první SUMIFS sleduje, o kolik se účet navyšuje, a druhá, o kolik se účet snižuje. V případě pasivního účtu je to obráceně - proto ta podmínka s jedničkou a mínus jedničkou.
Přidáme ještě vhodné podmíněné formátování, které hodnoty vizualizuje.
Do nového sloupce pak spočteme z průběžných změn a z počátečního stavu změny kumulativní.
Graf teď popisuje vývoj příslušného účtu, který je zadán do buňky J2. Může jít o účet syntetický i o účet analytický.
Zadání i s řešením je možné stáhnout tady
S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.