Excel ako pomocník účtovníkov pri analýze účtovných dát - funkcia SUMIFS

Tento návod popisuje niekoľko možností, ako v programe Excel analyzovať informácie z účtovných dát. Je zameraný predovšetkým na použitie funkcie SUMIFS, ktorá je jednou z nejdôležitejších funkcií pri vypĺňaní výkazov.

V našej ukážke budeme vychádzať z nasledujúcej tabuľky, ktorá reprezentuje modelový výber dát z účtovného softvéru. (Pokiaľ si chcete výpočty výskušať, môžete si tabulku stiahnuť tu. Sú v nej aj hotové výpočty).

Nejedná sa samozrejme o reálne dáta, dôležitá je predovšetkým štruktúra stĺpcov.

Riešené úlohy

Našou úlohou bude zistiť z výberu dát informácie, ktorá nás zaujímajú. Konkrétne sa bude jednať o pohyby na analytických účtoch, na zodpovedajúcich syntetických účtoch ako aj na skupinách účtov, a to aj s ďalšími obmedzeniami - napr. na určitý mesiac.

Zistenie celkového obratu jedného analytického účtu (napr. 548000) podľa strany MD

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

Vysvetlenie:

Stĺpec E obsahuje sumu, ktorú spočítame. Prvým argumentom funkcie SUMIFS je teda vždy oblasť, ktorá obsahuje spočítané dáta. V stĺpci F sa vyskytujú čísla účtov. Pri funkcii SUMFS sa táto oblasť nazýva oblasťou kritérií. 548000 je konkrétny účet, pre ktorý hľadáme obrat. Táto hodnota sa samozrejme musí vyskytovať niekde v oblasti kritérií. Excel teda postupuje tak, že prechádza stĺpec F a keď v ňom nájde hodnotu 548000, pozrie sa na rovnakom riadku do stĺpca E. A všetky hdonoty nájdené týmto spôsobom v stĺpci E potom spočíta do výslednej hodnoty. 

Zistenie celkového obratu jedného syntetického účtu (napr. 548)

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

Od predchádzajúceho prípadu sa líši tým, že nepracujeme s konkrétnou položkou, ale so všetkými položkami, ktoré začínajú na "548". Znak "hviezdička" zastupuje akýkoľvek počet akýchkoľvek znakov - preto výraz "548*" je možné preložiť ako "čokoľvek čo začína "548".

Zistenie celkového obratu všetkých nákladových účtov (všetkých účtov začínajúcich päťkou)

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

Obdoba predchádzajúcich dvoch prípadov, ale hľadáme všetko, čo začína päťkou - teda náklady. 

Zistenie počtu položiek, ktoré majú na strane MD účet 548000

=COUNTIFS(F:F;"548000")

V tomto prípade nepoužijeme funkciu SUMIFS ale funkciu COUNTIFS. Tá funguje na podobnom príncípe, ale namiesto hľadania súčtu určuje počet položiek. Logicky potom u nej chýba prvý argument, oblasť súčtu, pretože nie je čo spočítať. 

Obrat jedného syntetického účtu za vybrané časové obdobie

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

V tomto prípade pridávame nové kritérium - mesiac. V našom prípade mesiac číslo dva, teda február. Musíme samozrejme pridať aj príslušnú oblasť kritérií - stĺpec C. 

Počet položiek, ktoré v popise obsahujú slovo "nájom"

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

Týmto zápisom zistíme, v koľkých popisoch účtovného prípadu sa vyskytuje slovo "nájom". Podobne môžme dohľadať napr. počty položiek alebo súčty súm pri jednotlivých dodávateľoch, atď. Obdobne ako pri predchádzajúcom postupe hľadáme pomocou hviezdičiek všetky hodnoty, ktoré obsahujú slovo "nájom", pričom môže byť aj čokoľvek pred ním alebo za ním.