Excel jako pomocník účetních: Vykreslení grafu pohybu účtu na základě účetního deníku

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í. 

A vytvoříme z něj graf.

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