V minulém pokračování našeho pojednání o možnostech využití Excelu (Účetnictví v praxi č. 8/2013 str. 32) jsme se uvedli do problematiky podmíněného součtu, a to konkrétně řešením úloh součtu s jednou podmínkou. Následující výklad na tuto problematiku navazuje a přináší rozšiřující a prohlubující informace včetně dalších užitečných návodů.
Využití Excelu v praxi
Milan
Řezníček
Součet s více podmínkami
V praktickém životě se velmi často dostáváme do situací, kdy potřebujeme provést podmíněný součet se zohledněním dvou a více kritérií, přičemž ne vždy se jedná o kritéria, která se slučují (tedy mají parametr "a zároveň"), ne zřídka potřebujeme do součtu dostat podmínku "nebo", popřípadě podmínky vzájemně kombinovat. Pokud se kritéria (podmínky) součtu nacházejí ve více různých oblastech (sloupcích, řádcích), je možné v zásadě použít tyto vestavěné funkce:
1.
SUMIFS
2.
SOUČIN.SKALÁRNÍ
3.
SUMA jako maticový vzorec
4.
DSUMA - databázová funkce pro podmíněný součet
SUMIFS
+-----------------+---------------------------------------------------------+-----------------------------------------------+ | Funkce | Syntaxe | Poznámka | +-----------------+---------------------------------------------------------+-----------------------------------------------+ | SUMIFS | =SUMIFS(oblast_součtu; oblast_kritérií1;kritérium1...) | Sečte buňky v oblasti, která splňuje více | | | | kritérií | +-----------------+---------------------------------------------------------+-----------------------------------------------+
Tato funkce je velmi užitečná a najde si své využití v řadě případů. V rámci kritérií funkce SUMIFS lze zadávat odkazy, hodnoty, vzorce, avšak
všechny
podmínky musí být pro danou buňku splněny současně
(i tato limitace je v prostředí Excelu řešitelná a ukážeme si jak). Určitým nepříjemným omezením bezpochyby je, že funkce SUMIFS se objevuje až ve verzi E2007, starší verze Excelu ji neumí vyhodnotit! Naopak předností funkce je fakt, že umí sčítat na základě až 127 kritérií, což je počet v běžné účetní praxi téměř nevyčerpatelný, a není třeba při jejím použití znát pravidla pro maticové vzorce.PŘÍKLAD
Podmíněný součet za 2 kritéria z různých oblastí s využitím zástupného znaku
Vrátíme se k úloze řešené pomocí funkce SUMIF (sčítali jsme dle kritéria subjekt "Dcera s.r.o.") a k tomuto kritériu si přidáme kritérium z jiného sloupce tabulky účetního deníku -
kritérium2
- všechny zakázky začínající písmeny "ZA". Je třeba dát pozor na odlišnou syntaxi proti funkci SUMIF, nejprve je nutno zadat oblast součtu (C3:C7), následně oblast pro kritérium1 (H3:H7) a poté samotné kritérium1. Pro další podmínku součtu je nejprve nutno opět zadat oblast2 (D3:D7) a teprve pak kritérium2 atd. Oblast součtu a oblast kritérií musí mít stejnou velikost, jinak Excel funkci nevyhodnotí a vrátí chybovou hlášku #HODNOTA! Text v rámci kritérií je vždy nutné zadat do uvozovek. Máme-li v úmyslu Excel přinutit vyhodnotit v parametru pouze část textového řetězce, jako v tomto příkladu, dosadíme do parametru zástupný znak "*
" ("ZA*"). Hvězdičku v souvislosti s textem Excel chápe jako libovolný a libovolně dlouhý textový řetězec.
V tomto příkladu jsme "nakousli" tzv. zástupné znaky, doplníme si tedy pro úplnost informaci o této užitečné funkčnosti:
Znak "*"
nám umožní vyhledání textového řetězce bez omezení délky a umístění. V našem případě Excel počítá se všemi zakázkami, které začínají "ZA" a připojí k nim jakýkoli následující řetězec znaků. Pokud bychom zadali do vzorce "Z*12*", zahrnul by do součtu veškeré zakázky začínající Z a obsahující číslici 12 (tedy ZA1201, ZS12155, ZZ1206). Pokud by některá ze zakázek obsahovala např. znaky ZA12XY01, byla by do podmíněného součtu zahrnuta také s ohledem na již zmiňovanou absenci délky textového řetězce.Znak "?"
naproti tomu umožňuje vyhledat určitý konkrétní znak na určité pozici v textovém řetězci. Pokud bychom chtěli pomocí tohoto znaku vyhledat všechny zakázky začínající "ZA", které mají celkem přesně 6 znaků, zadali bychom parametr takto "ZA????". Z výpočtu by byla zakázka s číslem ZA12XY01 vyloučena, kvůli omezení délky textového řetězce.SOUČIN.SKALÁRNÍ
Jedná se nepochybně o nekorunovaného krále mezi funkcemi pro zdolávání úloh podmíněného součtu v Excelu a nutno dodat, že jeho použití se neomezuje pouze na podmíněný součet. I přes zmíněnou univerzálnost mnozí uživatelé tuto funkci neznají, anebo ji neumí aplikovat. Při správném pochopení její vnitřní logiky, lze pomocí této funkce provádět podmíněné součty s velmi komplikovanými kritérii a ušetřit si tak značné množství "ruční práce". V kombinaci s dalšími funkcemi je pak v prostředí tabulkového procesoru prakticky nedostižná - poradí si totiž s úlohami, které jsou řešitelné jinými postupy buď značně obtížně (např. za pomoci dalších mezivýpočtů v pomocných oblastech dat), nebo bychom je nebyli schopni provést vůbec.
+-----------------+---------------------------------------------------------+-----------------------------------------------+ | Funkce | Syntaxe | Poznámka | +-----------------+---------------------------------------------------------+-----------------------------------------------+ | SOUČIN.SKALÁRNÍ | =SOUČIN.SKALÁRNÍ(Pole1; [Pole2];[Pole3]...) | Vrátí součet součinů odpovídajících oblastí | | | | nebo matic, Pole1,Pole2... je 2 až 255 matic. | +-----------------+---------------------------------------------------------+-----------------------------------------------+
Syntaxe této funkce běžným uživatelům příliš neodhalí. Podstata tkví v pochopení principu funkce založeném na převodu zadaných kritérií podmíněného součtu na pravdivostní hodnoty PRAVDA/NEPRAVDA odpovídající číselným hodnotám 1/0. Další faktor, který je třeba vzít v úvahu, je respektování pravidel pro použití matic v Excelu. Součin.skálární není klasickým maticovým vzorcem, pracuje však na stejném principu. Matice v Excelu zjednodušeně znamenají určité oblasti hodnot, se kterými provádíme výpočty jako s celkem. Hlavní pravidlo, které musíme při zápisu funkce dodržet, je stejná velikost a poloha oblastí, ve kterých ověřujeme kritéria s polohou a velikostí oblasti konečného součtu. Existuje více způsobů zápisu této funkce, pro naše účely si vystačíme se zápisem pomocí výpočtového operátoru "*". Zobecnění zápisu pomocí operátoru "*" vypadá takto:
SOUČIN.SKALÁRNÍ ((matice1=kritérium1)*(mati-ce2=kritérium2)*(maticeX))
1V následujících příkladech si praktickou aplikaci ukážeme podrobněji, včetně vysvětlení transformace pravdivostních kritérií na číselné hodnoty.
PŘÍKLAD
Součet součinu množství a ceny prodaného zboží
Začněme základním použitím funkce (zatím bez kritérií součtu). Jsou zadány 2 sloupce (ve sloupci A je množství, ve sloupci B je cena za jednotku). Naším cílem je provést vyčíslení celkové tržby. Bez znalosti využití funkce SOUČIN.SKALÁRNÍ, případně maticových vzorců, bychom úlohu řešili:
*
buď vytvořením pomocného sloupce C s násobením množství a ceny v daném řádku a následným prostým součtem hodnot v tomto sloupci,
*
nebo zápisem součtu součinů jednotlivých buněk obsahujících hodnoty, tedy A2*B2+A3*B3+A4*B4 = 1*10+2*10+3*10 = 60 Kč.
Oba postupy však můžeme v případě většího množství dat označit za neefektivní. Přestože se zde nejedná o podmíněný součet, je zde použití funkce SOUČIN.SKALÁRNÍ zcela namístě - jde o součet součinů buněk v určené oblasti, který funkce provede automaticky, s nutností pouze zadat oblasti součinu do argumentů ohraničených závorkami a za použití operátoru "*".
Nyní si předvedeme, jakým způsobem vpravit do argumentů výběrová kritéria součtu, jejichž pomocí zajistíme ověření, zda hodnota v matici argumentu funkce splňuje či nesplňuje námi zadané kritérium - a Excel jí tedy přiřadí hodnotu PRAVDA (1) nebo NEPRAVDA (0). Jak bylo uvedeno výše, jednotlivé argumenty funkce se nejdříve mezi sebou vynásobí a následně se výsledky součinů sečtou.
Princip podmíněného součtu u této funkce je založen na skutečnosti, že za vyhovující kritérium vstoupí do součinu hodnota 1, za nevyhovující 0.
Například ověřuje-me-li, zda hodnota v matici kritéria je větší než námi požadovaná hodnota, bude Excel testovat hodnotu v řádku matice námi zadaným kritériem a podle výsledku dosadí buď jedničku, nebo nulu. Jakmile bude v součinu nula (hodnota nevyhověla zadanému kritériu), bude celý součin roven nule a do součtu součinů vstoupí s nulovou hodnotou (neovlivní ho). Konkrétní příklad potvrdí zmíněnou funkčnost.PŘÍKLAD
Součet součinu množství a ceny prodaného zboží s jednou podmínkou
Zůstaneme u našeho příkladu, avšak přidáme si jednu podmínku součtu. Zadání úlohy by klidně mohlo znít tak, že potřebujeme znát celkovou tržbu za zboží, kterého se prodalo více než jeden kus. Jinými slovy podmiňujeme součet kritériem - (hodnota množství > 1). Zápis vzorce do buňky C5 provedeme takto:
=SOUČIN.SKALÁRNÍ((A2:A4>1)*(A2:A4)*(B2:B4))
Funkce vyhodnotí výsledky způsobem, který nám objasňuje následující obrázek:
Jak již bylo zmíněno v předchozím textu, jde o součet součinů - funkce tedy provedla výpočet s pomocí následujícího postupu převodu pravdivostních hodnot na číselné hodnoty:
*
v řádku 2 ověřila, zda hodnota v buňce A2(1)>1, s výsledkem NEPRAVDA (0), máme-li v součinu nulu, celý součin je roven nule - tedy 0 * 1* 10 = 0,
*
v řádku 3 ověřila, zda hodnota v buňce A3(2)>1, s výsledkem PRAVDA (1), součin hodnot násobený jedničkou je roven hodnotě součinu - tedy 1 * 2 * 10 = 20,
*
v řádku 4 ověřila, zda hodnota v buňce A4(3)>1, s výsledkem PRAVDA (1), výpočet součinu tedy odpovídá hodnotám 1 * 3 * 10 = 30,
*
a provedla součet uvedených součinů, tj. 0 + 20 + 30, s výsledkem 50.
Právě transformace pravdivostních hodnot (PRAVDA; NEPRAVDA) na hodnoty číselné (0;1) tak umožňuje, spolu s použitím dalších funkcí vnořených do argumentů funkce SOUČIN.SKALÁRNÍ, řešit i velmi složitá kritéria podmíněného součtu. V našem následujícím praktickém příkladu si provedeme ukázku komplikovanějšího zadání, které nám právě zmíněná funkce umožní vyřešit.
PŘÍKLAD
Podmíněný součet - náklady za různá střediska, účty či jejich skupiny a období
Všemožné vyhodnocování nákladů zachycených ve finančním účetnictví je častým praktickým problémem řady účetních jednotek. Důvody mohou být různé (interní - analytická, kontrolní a rozborová činnost, externí - rozmanité výkaznictví, kterým jsou účetní jednotky zatíženy).
Zadání naší úlohy spočívá v součtu osobních a materiálových nákladů za období prosinec 2012, a to pouze za střediska 150 a 390. Jak bylo zmíněno, důvodem může být např. realizace výzkumného projektu napříč různými útvary a následná potřeba zjištění celkových nákladů projektu. Obvykle účetním nezbyde nic jiného než si vyjet výsledovky středisek podle analytických účtů a náklady ručně posčítat. My si ukážeme, jak nám může pomoci Excel a funkce SOUČIN.SKALÁRNÍ.
Definujme si kritéria součtu, než začneme psát vzorec:
Období
= "2012.12" —>
zadáme jako textový řetězec (matice1 = "2012.12").Středisko
= 150; 390 —>
zadáme jako číselnou hodnotu ((matice2 = 150) + (matice2 = 390)), přičemž "+" odpovídá logickému NEBO, argument nutno celý uzavřít do závorky - Excel bude sčítat hodnoty ve sloupci "Stř." rovnající se hodnotě 150 nebo 390.Účetní skupina; syntetický účet
= 52; 501 —>
protože ve sloupci "Účet" exportovaného účetního deníku jsou pouze analytické účty, oddělíme si v rámci vzorce pomocí textové funkce ZLEVA první 2 znaky a pomocí funkce HODNOTA si výsledný textový řetězec "52" převedeme zpět na číslo 52, a to samé provedeme pro získání hodnoty 501, pouze pro oddělení použijeme první 3 znaky. Obě kritéria opět musí být doplněna o podmínku NEBO, protože sčítáme za účty začínající 52 NEBO 501; ve vzorci zapíšeme ((HODNOTA(ZLEVA(MATICE3;2))=52)+(HODNOTA(ZLEVA(MATICE3;3))=501))Celý vzorec tedy obecně bude vypadat takto:
=SOUČIN.SKALÁRNÍ((MATICE1="2012.12")*((MATICE2=150)+(MATICE2=390))*((HODNOTA (ZLEVA(MATICE3;2))=52)+(HODNOTA(ZLEVA(MATICE3;3))=501) )*(MATICE4))
Konkrétní zápis funkce s výsledným podmíněným součtem nám odhalí následující obrázek:
Na první pohled je zřejmé, že jediný vzorec může ušetřit desítky minut i hodiny práce. Navíc v případech, kdy dochází k dodatečným opravám v účetních zápisech, lze opět pouze exportovat účetní deník do Excelu a výpočet máme ihned k dispozici. Nemůžeme opomenout, jak důležité je naučit se používat tuto funkci v kombinaci s dalšími funkcemi, protože se nám tím otevírají nepřeberné možnosti použití. Důkazem toho budiž následující příklad.
PŘÍKLAD
Podmíněný součet za část textového řetězce
Nezřídka se orosí nejedno účetními trampotami sužované čelo, když přijde od vedení požadavek na zjištění výše určitých nákladů, které jsou ve finančním účetnictví zachyceny v rámci tzv. "splachovacího" analytického účtu, kam se účtuje všemožné v rámci druhového členění nákladů. Finanční ředitel tak může např. požadovat zjištění nákladů za nájem z analytického účtu 518900, kam se účtuje vše možné od nájmů až po poradenské služby. Navíc může text účetního zápisu obsahovat slova nájem, podnájem, pronájem, nájemné apod. I takovou situaci lze v Excelu řešit, a to např. takto:
Co vlastně Excel kombinací těchto funkcí provedl? Funkce JE.ČISLO ověří, zdali zadaný argument je číslo nebo ne, a dosadí přesně to, co SOUČIN.SKALÁRNÍ potřebuje - pravdivostní hodnotu PRAVDA; NEPRAVDA podle toho, zda je v argumentu funkce číslo nebo ne. Funkce HLEDAT je textová funkce, která vrací číslo prvního výskytu hledaného textového řetězce (v tomto případě slova "nájem") - tedy v rámci vzorce zadaného v našem příkladu provede funkce SOUČIN.SKALÁRNÍ následující vnitřní vyhodnocení:
Funkce HLEDAT v buňce B4, B5, B8 najde počátek textového řetězce "nájem" na 4., resp. 10., resp. 13. pozici, přičemž tyto číselné hodnoty vrátí jako výsledky, pokud v některé buňce zmíněný řetězec nenalezne, vrátí chybovou hlášku #HODNOTA!
Nabídněme další řešení pomocí jiných funkcí:
=SUMIFS(E3:E8;B3:B8;"*nájem*")
nebo
=SUMIF(B3:B8;"*nájem*";E3:E8)
ČÍHAŘ, J:
Excel Asistent magazín PREMIUM 02/2005,
2005, ročník 3, č. 2, str. 4, ISSN 1801 - 2361, .