V minulé části našeho seriálu (Účetnictví v praxi č. 10/2013, str. 33) jsme začali s pokročilejšími technikami řešení úloh podmíněného sčítání - podmíněný součet s více podmínkami. V tomto pokračování tuto kapitolu dokončíme ukázkou dalších možných postupů, které mohou čtenářům usnadnit získávání požadovaných hodnot z nejrůznějších tabulek účetních dat.
Využití Excelu v praxi
Milan
Řezníček
MATICOVÝ VZOREC SUMA
V předchozím textu jsme již na maticové vzorce narazili. Další variantou, pomocí které můžeme v tabulkovém procesoru řešit podmíněný součet, je právě maticový vzorec s využitím funkce SUMA. Uvedli jsme, že maticové vzorce jsou postaveny na práci s oblastí hodnot jako celkem. Matice může být v řádku (vodorovná), nebo ve sloupci (svislá), anebo smíšená (2D matice). Podstata maticových vzorců spočívá v tom, že provádějí jednu operaci s celou skupinou hodnot v matici a dokáží výpočty vyhodnotit ve vnitřní paměti Excelu. Z toho vyplývá, že pomocí jednoho vzorce lze v Excelu nahradit téměř libovolné množství operací jednotlivých. Slovo „téměř“ je zde namístě, protože nevhodné nebo neúměrné použití maticových vzorců může značně zpomalit celý excelovský sešit. Jejich praktická aplikace tedy může být limitována právě faktorem značného snížení výpočetní rychlosti sešitu. Při vytváření maticových vzorců musíme také respektovat dvě důležité zákonitosti:
1)
Každý maticový argument musí obsahovat stejný počet řádků a sloupců.
2)
Uložení vzorce musíme provést pomocí kombinace kláves CTRL+SHIFT+ENTER.
PŘÍKLAD
Maticový vzorec - součet ceny včetně daně
Jak fungují maticové vzorce, si naznačíme na jednoduchém příkladu s požadavkem výpočtu ceny včetně DPH ze zadaných hodnot bez nutnosti vytvářet pomocný vzorec. Sloupec A obsahuje cenu bez daně, sloupec B příslušnou sazbu DPH. Cenu včetně daně celkem zjistíme pomocí maticového vzorce SUMA takto:
V tomto případě byl pomocí jednoho vzorce nahrazen celý pomocný sloupec C. Uložením vzorce za pomoci kláves CTRL+SHIFT+ENTER se vzorec zapouzdří do složených závorek. Mnohému čtenáři pravděpodobně neuniklo, že daný případ je opět součtem součinů, a proto jiná možná varianta řešení by mohla být:
=SOUČIN. SKALÁRNÍ((A2:A3)*(1+(B2:B3)))
Využití maticových počtů je na samostatnou kapitolu a zdaleka se neomezuje jen na funkci suma, my se ale vrátíme k našemu příkladu s podmíněným součtem z předchozícho dílu a ukážeme si, jak ho vyřešit za pomoci maticové verze funkce SUMA.
PŘÍKLAD
Podmíněný součet - náklady za různá střediska, účty či jejich skupiny a období
Z obrázku dovodíme, že jednotlivé argumenty funkce jsme zadali stejným způsobem jako v případě funkce SOUČIN. SKALÁRNÍ, ovšem s tím rozdílem, že jsme museli do funkce SUMA
vnořit logickou funkci KDYŽ,
pomocí které jsme mohli transformovat jednotlivá kritéria součtu do pravdivostních hodnot, resp. číselných hodnot 1/0, a celou funkci opět bylo nutné uložit za pomoci CTRL+SHIFT+ENTER.V minulých dílech našeho putování světem Excelu v účetní praxi jsme několikrát narazili na pojem
maticové konstanty.
Doplníme tedy výklad k problematice podmíněného součtu za pomoci matic právě o maticové konstanty. Maticová konstanta je hodnota nebo skupina hodnot, která je ve vzorci buď přímo zadaná, nebo ve vzorci na konstantní hodnotu odkážeme. Mohou obsahovat číslo, textové řetězce (nutno zadat do uvozovek) nebo logické hodnoty. Maticovou konstantu poznáme tak, že je ohraničena složenou závorkou stejně jako maticový vzorec, nachází se však uvnitř vzorce.Náš vzorec pro výpočet nákladů z předchozího příkladu bychom mohli vylepšit o maticovou konstantu obsahující ručně zadaná čísla středisek, za které provádíme podmíněný součet:
=SUMA(KDYŽ((A3:A10="2012.12")*(C3:C10=
{150\390}
) *((HODNOTA(ZLEVA(D3:D10;2))=52)+(HODNOTA(ZL EVA(D3:D10;3))=501));(E3:E10))) nebo =SOUČIN. SKALÁRNÍ((A3:A10="2012.12")*(C3:C10= {150\390}
)*((HODNOTA(ZLEVA(D3:D10;2))=52)+ (HODNOTA(ZLEVA(D3:D10;3))=501))*(E3:E10))Nesmíme také zapomenout upozornit, že zadávání maticových konstant bylo bohužel od verze Excelu 2010 změněno, a to následujícím způsobem:
I-----------------I-----------------------------I----------------------------I IVerze ExceluIZadání svislé konstantyIZadání vodorovné konstantyI I-----------------I-----------------------------I----------------------------I I E2003/2007 I {1|2|3} I {1;2;3} I I-----------------I-----------------------------I----------------------------I I E2010/2013 I {1;2;3} I {1\2\3} I I-----------------I-----------------------------I----------------------------I
Při zadávání je proto nutno respektovat pravidla pro verzi Excelu, kterou máme nainstalovánu (v našich příkladech jsou příklady připraveny ve verzi E2010). Pro zadávání složených závorek u maticových konstant bych doporučil kombinaci kláves levý ALT+123 a levý ALT+125.
Využití maticových konstant je možné i v běžných vzorcích, a proto doplníme další možné řešení našeho příkladu, a to tentokrát pomocí funkce SUMIFS, kterou zapouzdříme do funkce SUMA, a právě pomocí maticové konstanty v kritériu sčítání pro středisko a také pro konkrétní analytické účty docílíme požadovaného výsledku.
DSUMA
Tato funkce je databázová, a proto ji nasadíme do svých výpočtů v případech, kdy máme k dispozici obvykle rozsáhlou oblast buněk, která má záhlaví sloupců a datovou oblast. Data ve sloupcích jsou naplněna stejnou informací (tj. odpovídá záhlaví sloupce). Typickou databází pro účetní je exportovaný účetní deník do Excelu (každý sloupec obsahuje určitý konkrétní databázový údaj - datum, období, číslo dokladu, měnu, obrat MD, obrat D atd.). Počínaje verzí E2007 vývojáři Excelu práci s rozsáhlými tabulkami uživatelům výrazně usnadnili. Jednak novým formátem souborů xlsx (starší verze mají soubory s koncovkou xls) a také významným rozšířením počtu řádků na jednom listě, a to z původních cca 65 tis. na více jak 1 mil. řádků. Jak přistoupit k databázové funkci? Začneme syntaxí a následně praktickým příkladem.
I--------I--------------------------------I----------------------------------I IFunkceISyntaxeIPoznámkaI I--------I--------------------------------I----------------------------------I IDSUMAI =DSUMA(databáze;pole;kritéria) I Vypočte součet položek z pole I I I I databáze, jejichž záznam I I I I odpovídá kritériu I I--------I--------------------------------I----------------------------------I
Databáze
je celá oblast buněk (včetně záhlaví sloupců) - např. účetní deník, saldokonto závazků a pohledávek, seznam dlouhodobého majetku apod.Pole
znamená konkrétní sloupec v databázi - např. subjekt nebo středisko z účetního deníku.Kritérii rozumíme takovou oblast buněk, do které zapisujeme podmínky pro součet z databáze - např. středisko = = 150, nebo za subjekt dosadíme „Dcera s.r.o.“)
PŘÍKLAD
Součet nákladů za subjekt
Vzpomeňme si na příklad, který jsme řešili pomocí funkce SUMIF s jedním kritériem, a to subjekt „Dcera s.r.o.“. Základní použití funkce DSUMA si přiblížíme právě na jednom kritériu.
-
Argument databáze obsahuje oblast celé tabulky i se záhlavím sloupců (A2:H7).
-
Sloupec, který sčítáme podle zadaných kritérií má záhlaví v buňce C2 („Částka“).
-
Kritéria součtu jsou ze sloupce H „Subjekt“ a sčítáme za textový řetězec „Dcera s.r.o.“. Jak z příkladu vyplývá,
kritéria se zadávají do samostatné oblasti buněk,
na kterou ve vzorci musíme odkázat.
Podstatné je dodržení stejného názvu sloupce
(popř. sloupců) v oblasti kritérií jako je název sloupce v databázi.Pokusme se vyřešit pomocí DSUMA náš příklad z předchozího výkladu k funkcím SOUČIN. SKALÁRNÍ a maticovému vzorci s funkcí SUMA v kombinaci s funkcí KDYŽ.
PŘÍKLAD
Podmíněný součet - náklady za různá střediska, účty či jejich skupiny a období
Protože DSUMA pracuje na odlišném principu než maticové vzorce, jádro této úlohy bude spočívat ve správném zadání posledního argumentu funkce - kritériích součtu. DSUMA sice pracuje také s databází jako celkem dat, v rámci kritérií však není možné vkládat další funkce, kterými bychom mohli provádět hromadné operace nad databází jako v případě maticových vzorců.
V tomto případě je kritérií součtu více a k tomu je třeba se vypořádat s faktem, že podmínky součtu nemusí být splněny všechny současně - tedy odpovídající logickému „A“, ale máme tu hned dvě podmínky odpovídající naopak logickému „NEBO“. U maticových vzorců jsme tento problém vyřešili pomocí operátoru „+“, resp. použitím maticové konstanty v případě středisek, v případě funkce DSUMA je třeba kritéria součtu odpovídající logickému „NEBO“ napsat do řádků pod sebe. Kdo z čtenářů zná pravidla pro použití ručního filtru v Excelu, má aplikaci kritérií u databázových funkcí značně usnadněnu.
Tento zápis v oblasti kritérií znamená, že součet má být prováděn za všechna střediska větší nebo rovno hodnotě 150 a zároveň menší nebo rovno hodnotě 390, tedy v intervalu <150;390>.
I-------I-------I IStř.IStř.I I-------I-------I I >=150 I <=390 I I-------I-------I
Tento zápis znamená, že součet má být prováděn za středisko 150
nebo
390:I------I IStř.I I------I I 150 I I------I I 390 I I------I
Z uvedených příkladů je vidět, že v rámci podmíněného součtu se můžeme v tabulkách opravdu „vydovádět“. Uveďme si závěrečné desatero pro podmíněný součet, které bychom měli mít na paměti:
1)
Univerzální návod, kterou funkci použít, vzhledem k rozmanitosti úloh, neexistuje.
2)
Pravidla pro zadávání funkcí jsou proti daňovým a účetním předpisům „stojaté vody“, přesto je třeba vnímat odlišnosti některých verzí Excelu (např. některé funkce z novějších verzí nefungují ve verzích starších, popř. se funkce jinak zadávají VLEVO x ZLEVA apod.).
3)
Než začneme psát vzorec, ujasněme si, co chceme sčítat a za jakých podmínek.
4)
Kritéria součtu si rozdělme podle toho, zda mají být splněna současně - podmínka „a zároveň“, popř. zda stačí, aby bylo splněno pouze některé - tedy mají podmínku „nebo“, popř. jak tyto podmínky chceme kombinovat.
5)
Složitější vzorce je vždy vhodné skládat po částech.
6)
U maticových vzorců nezapomínejme na popsaná pravidla ukládání při tvorbě a editaci.
7)
Použití maticových konstant se neomezuje jen na maticové vzorce (pozor na odlišení vodorovné a svislé konstanty a její jiné zadávání pro E2010/2013).
8)
Pozor na čísla v matici, která jsou uložena jako text.
9)
Přílišné použití maticových vzorců může výrazně zpomalit výpočet.
10)
Když se součet nedaří, vyzkoušejme různé varianty.