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:

výsledný graf

Pro jeho vytvoření vyjdeme z tohoto účetního deníku (zadání i řešení je možné stáhnout tady).

výchozí tabulka

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:

dopočtený měsíc

Čí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)

doplnění měsíců funkcí INDEX

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.

připravená tabulka

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.

sumifs

Přidáme ještě vhodné podmíněné formátování, které hodnoty vizualizuje.

průběžné pohyby

Do nového sloupce pak spočteme z průběžných změn a z počátečního stavu změny kumulativní. 

výsledná tabulka

A vytvoříme z něj graf.

poslední obrázek

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

Napsat komentář

Vaše emailová adresa nebude publikována.

*

smazat formulářOdeslat komentář