Excel ako pomocník účtovníkov: Vykreslenie grafu pohybu účtu na základe účtovného denníka
Súvisiace dokumenty ku stiahnutiu v Exceli alebo v PDF
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.
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
web: www.exceltown.com
Potrebujete pomôcť s excelom? Navštívte firemné kurzy, alebo individuálnu výuku či konzultácie v oblasti programu Excel a spracovania ekonomických dát.