Excel ako pomocník účtovníkov: Vykreslenie grafu pohybu účtu na základe účtovného denníka

27. 12. 2015 | Ing. Jiří Beran

Tento článok sa venuje tomu, ako z účtovného denníku (alebo obdobného záznamu) vytvoriť graf pohybu na vybranom účte.

Výsledkom našej práce bude tento graf:

Pro jeho vytvorenie budeme vychádzať z tohoto účtovného denníka (zadanie i riešenie je možné stiahnuť tu).

Prvým krokom bude to, že ku každému riadku priradíme mesiac, v ktorom účtovný prípad nastal. K tomu použijeme funkciu MESIAC, ktorou prevedieme dátum na mesiac. Výsledkom je nový stĺpec s číslom mesiaca. 

Čísla od jednotky do dvanástky zmeníme na názvy mesiacov (napr. pomocou funkcie INDEX).

=INDEX($I$5:$I$16;MESIAC(B:B);0)

Pripravíme si bunky, do ktorých vyplníme sledovaný účet, informáciu o tom či je tento účet aktívny alebo pasívny, a počiatočný stav.

A teraz príde to najlepšie- funkcia SUMIFS. Zapíšeme ju takto:

=(SUMIFS(D:D;E:E;$J$2&"*";G:G;I:I)-SUMIFS(D:D;F:F;$J$2&"*";G:G;I:I))*IF($J$3="pasivní";-1;1)

Vysvetlenie:

• Prvý SUMIFS spočíta všetky sumy zo stĺpca D, ktoré majú v stĺpci E (tam sú účty MD) účet začínajúci tým, čo je v bunkee J2, a v stĺpci G, kde sú mesiace, konkrétny sledovaný mesiac.
• Druhá SUMIFS vykoná podobné sčítanie položiek, ktoré majú príslušný účet v stĺpci F (dal). Výsledok tejto druhej funkcie SUMIFS sa od prvej odpočíta.
• Pokiaľ pracujeme s aktívnym účtom, tak prvý SUMIFS sleduje, o koľko sa účet navyšuje, a druhá, o koľko sa účet znižuje. V prípade pasívneho účtu je to naopak - preto tá podmienka s jednotkou a mínus jednotkou.

Pridáme ešte vhodné podmienené formátovanie, ktoré hodnoty vizualizuje.

Do nového stĺpca potom spočítame z priebežných zmien a z počiatočného stavu zmeny kumulatívne. 

A vytvoríme z toho graf.

Graf teraz popisuje vývoj príslušného účtu, ktorý je zadaný do bunky J2. Môže ísť o účet syntetický aj o účet analytický. 

Zadanie aj s riešením je možné stiahnuť tu