Excel jako pomocník účetních při analýze účetních dat - funkce SUMIFS

17. 11. 2015 | Ing. Jiří Beran

Tento návod popisuje několik možností, jak v Excelu analyzovat informace z účetních dat. Je zaměřený především na použití funkce SUMIFS, která je jednou z nejdůležitějších funkcí při vyplňování výkazů.

V naší ukázce vyjdeme z následující tabulky, která reprezentuje modelovou sjetinu z účetního softwaru. (Pokud si chcete výpočty vyzkoušet, můžete si tabulku stáhnout tady. Jsou v ní i hotové výpočty).

Nejedná se samozřejmě o reálná data, důležitá je především struktura sloupců.

Řešené úkoly

Naším úkolem bude zjistit ze sjetiny informace, které nás zajímají. Konkrétně se bude jednat o pohyby na analytických účtech, na odpovídajících syntetických účtech i na skupinách účtů, a to i s dalším omezením – např. na určitý měsíc.

Zjištění celkového obratu jednoho analytického účtu (např. 548000) podle strany MD

=SUMIFS(E:E;F:F;"548000")

Vysvětlení:

Sloupec E obsahuje částku, kterou sčítáme. Prvním argumentem funkce SUMIFS je tedy vždy oblast, která obsahuje sčítaná čísla.

Ve sloupci F se vyskytují čísla účtů. U funkce SUMIFS se tato oblast nazývá oblastí kritérií.

548000 je konkrétní účet, pro který hledáme obrat. Tato hodnota se samozřejmě musí vyskytovat někde v oblasti kritérií.

Excel tedy postupuje tak, že prochází sloupec F, a když v něm najde hodnotu 548000, podívá se na stejném řádku do sloupce E. A všechny hodnoty nalezené tako ve sloupci E pak sečte do výsledné hodnoty.

Zjištění celkového obratu jednoho syntetického účtu (např. 548)

=SUMIFS(E:E;F:F;"548*")

Od předchozího případu se liší tím, že nepracujeme s konkrétní položkou, ale se všemi položkami, které začínají „548“. Znak „hvězdička“ zastupuje jakýkoliv počet jakýchkoliv znaků – proto výraz „548*“ lze přeložit jako „cokoliv co začíná „548“.

Zjištění celkového obratu všech nákladových účtů (všech účtů začínajících pětkou)

=SUMIFS(E:E;F:F;"5*")

Obdoba předchozích dvou, ale hledáme vše, co začíná pětkou – tedy náklady.

Zjištění počtu položek, které mají na straně MD účet 548000

=COUNTIFS(F:F;"548000")

Tentokrát nepoužijeme funkci SUMIFS, ale funkci COUNTIFS. Ta funguje na podobném principu, ale místo hledání součtu určuje počet položek. Logicky pak u ní chybí první argument, oblast součtu, protože prostě není co sčítat.

Obrat jednoho syntetického účtu za vybrané časové období

=SUMIFS(E:E;F:F;"548*";C:C;2)

V tomto případě přidáváme nové kritérium  - měsíc. V našem případě měsíc číslo dva, tedy únor. Musíme samozřejmě přidat i příslušnou oblast kritérií – sloupec C.

Počet položek, které v popisu obsahují slovo "nájem"

=COUNTIFS(D:D;"*nájem*")

Tímto zápisem zjistíme, v kolika popisech účetního případu se vyskytuje slovo „nájem“. Podobně můžeme dohledávat např. počty položek nebo součty částek u jednotlivých dodavatelů atd. Obdobně jako u předchozích postupů hledáme pomocí hvězdiček všechny hodnoty, které obsahují slovo „nájem“, přičemž může být i cokoliv předtím nebo za tím.