V minulých dílech našeho seriálu o využití Excelu v účetní praxi (Účetnictví v praxi č. 4/2013 str. 32 a č. 6/2013 str. 37) jsme se zabývali vzorci (možnosti zápisu vzorců a základní výpočetní možnosti, absolutní, relativní a smíšené odkazy a názvy buněk a oblastí) a první částí vestavěných funkcí - funkcí pro zaokrouhlování. V dnešní části seriálu se zaměříme na další vestavěné funkce, a to funkce logické a podmíněný součet.
Využití Excelu v praxi
Milan
Řezníček
Logické funkce - opravdu je účetní nepoužije?
Logikou nám zákonodárci ve veřejném právu mnohdy šetří. Excel je především o logice, a proto jsou tyto funkce v zásadě pro naše výpočty nepostradatelné - řeší totiž rozhodovací podmínky. Uvedeme si funkce
A, NEBO
a KDYŽ.
Celá řada uživatelů s nimi umí pracovat, budeme tedy velmi struční. Funkce A a NEBO nám vrací logickou hodnotu PRAVDA, NEPRAVDA (Excel chápe jako číslo 1; 0 - s logickými hodnotami pracují zejména maticové vzorce, o kterých budeme ještě hovořit). Konečně funkce KDYŽ pak sama o sobě umí vyhodnotit rozhodovací podmínku, anebo v kombinaci s dalšími logickými funkcemi (zejména A a NEBO) různá rozhodovací schémata, na která lze vázat další výpočty.+---------+----------------------------------+--------------------------------------------+ | Funkce | Syntaxe | Poznámka | +---------+----------------------------------+--------------------------------------------+ | A | =A(logická1; [logická2]; ...) | Výsledek je PRAVDA, NEPRAVDA, lze zadat až | | | | 30 argumentů | | NEBO | =NEBO(logická1; [logická2];...) | Výsledek je PRAVDA, NEPRAVDA, lze zadat až | | | | 30 argumentů | | KDYŽ | =KDYŽ(podmínka;ano;ne) | Lze vnořit do sebe až 7 krát funkci "KDYŽ" | | | | jako argumenty PRAVDA nebo NEPRAVDA. | +---------+----------------------------------+--------------------------------------------+
PŘÍKLAD
Funkce A, NEBO, KDYŽ základní princip
Zapišme do těchto funkcí dva argumenty, z nichž jeden bude PRAVDA a druhý NEPRAVDA a podívejme se, jaký výsledek funkce vrátí:+---------------------+-----------+------------------------------------------+ | Vzorec | Výsledek | Komentář | +---------------------+-----------+------------------------------------------+ | =A(1+1=2; 2>3) | NEPRAVDA | Jeden z argumentů nemá hodnotu NEPRAVDA. | | =NEBO(1+1=2); 2>3) | PRAVDA | Jeden z argumentů má hodnotu PRAVDA. | | =KDYŽ(1+1>0;3-2;0) | 1 | Podmínka vrátí hodnotu PRAVDA, provede | | | | výpočet ANO tedy 3 – 2 = 1. | | =KDYŽ(1+1<0;3-2;0) | 0 | Podmínka vrátí hodnotu NEPRAVDA, dosadí | | | | NE tedy hodnotu 0. | | | | | +---------------------+-----------+------------------------------------------+
PŘÍKLAD
Rozhodovací podmínka - pro upomínky k pohledávkám po splatnosti
Vytvořme si k seznamu pohledávek, který např. exportujeme ze salda do Excelu, rozhodovací podmínku, která určí, že upomínku zašleme jen těm dlužníkům, kteří jsou po splatnosti a mají pohledávku nad 1 000 Kč,
avšak dlužníky, kteří jsou po splatnosti déle jak 10 dní,
chceme upomenout bez ohledu na výši pohledávky.
Řešení
V těchto případech není na škodu si rozhodovací schéma nejdříve nakreslit na papír a poté začít psát vzorec:
A nyní si toto rozhodovací schéma vyjádříme vzorcem v tabulce se seznamem exportovaných pohledávek:
Všimněme si, že rozhodovací podmínku, zda je částka pohledávky po splatnosti
vyšší
než 1 000 Kč NEBO
je dlužník po splatnosti déle než 10 dnů, jsme použili jako druhý argument pro funkce A
[je po splatnosti - první argument; NEBO (pohledávka > 1 000; po splatnosti > 10 dnů)
- druhý argument], tedy vnořili jsme funkci NEBO do argumentu funkce A, čímž jsme zajistili větvení z našeho rozhodovacího schématu ve vzorci.Pro naše účely ještě doplníme 2 statistické funkce, které sice nepatří do logických, ale použití je velmi snadné, a mohou nám mj. pomoci právě při rozhodování při výběru hodnot. Jde o funkce MIN (vrací nejmenší hodnotu ze zadané oblasti) a MAX (vrací největší hodnotu ze zadané oblasti), přičemž přeskakují logické hodnoty (PRAVDA, NEPRAVDA) a případně se vyskytující text. Stejně jako funkce SUMA.
+---------+--------------------------+----------------------------------------------+ | Funkce | Syntaxe | Poznámka | +---------+--------------------------+----------------------------------------------+ | MIN | =MIN(číslo1; číslo2;...) | Nalezení minimální hodnoty z vybrané oblasti | | | | buněk, může mít 1-255 argumentů. | | MAX | =MAX(číslo1; číslo2;...) | Nalezení minimální hodnoty z vybrané oblasti | | | | buněk, může mít 1-255 argumentů. | +---------+--------------------------+----------------------------------------------+
+-----------------+ | Ukázka: | +-----------------+ | =MIN(-1;1) = –1 | | =MAX(-1;1) = 1 | +-----------------+
Ukážeme si praktické použití výše zmíněných funkcí posledním návratem k našemu výpočtu čisté mzdy, opustíme však původní koncepci výpočtu v jedné buňce a použijeme vhodnější variantu s mezivýpočty v jednotlivých řádcích za použití výše popsaných funkcí.
PŘÍKLAD
Výpočet čisté mzdy - zaokrouhlování a rozhodovací podmínky výpočtu
Vyzbrojeni předchozími informacemi začneme zohledňovat zákonné požadavky na výpočet jednotlivých částí mzdy. Budeme tedy zaokrouhlovat pojistné na celé Kč nahoru, základ daně vyšší než 100 Kč zaokrouhlíme na stokoruny směrem nahoru, menší nebo roven 100 Kč na celé Kč matematicky. Dále zohledníme ve výpočtu možnost uplatnit slevu na poplatníka maximálně do výše vypočtené daně a naopak u daňového bonusu vyplývajícího z možnosti uplatnění slevy na vyživované děti horní hranici 5 025 Kč měsíčně. Zohledníme také novinku vyplývající z § 16a zákona č. 586/1992 Sb., o daních z příjmů, ve znění pozdějších předpisů (dále jen "ZDP") - solidární zvýšení daně u poplatníků, jejichž hrubý příjem převýší částku 103 536 Kč. Pro snazší orientaci uvádíme v příkladu ty částky, které mají snižující nebo zvyšující vliv na konečný příjem zaměstnance (pojistné, daň z příjmů po slevách) záporným nebo kladným znaménkem, a porovnáme si tři opačné extrémy (nízkou, vysokou a extrémně vysokou hrubou mzdu), abychom si ověřili správnost výpočtu.
*
V řádku 8
"Zaokrouhlený základ daně jsme kombinací funkce KDYŽ, ZAOKROUHLIT a ROUNDUP s odpovídajícími parametry ošetřili dvojí způsob zaokrouhlování v závislosti na výši základu daně.*
V řádku 10
"Solidární daň 7%"
nám funkce KDYŽ zajistí správný výpočet daně sazbou 7 % z hrubého příjmu převyšujícího částku 103 536 Kč.*
V řádku 14
"Daň po slevě poplatník" nám funkce MIN s parametry (součet daně a slevy;0) zajistí, že v případě, kdy sleva převýší zde záporně vyjádřenou daň, dosadí Excel hodnotu 0 —>; podmínka uplatnění slevy maximálně do výše vypočtené daně je tím splněna. Pokud bychom otočili znaménka, museli bychom použít funkci MAX. Pozorný čtenář si jistě všiml, že bychom zde mohli použít i funkci KDYŽ v tomto tvaru: *
KDYŽ(SUMA(B11:B12)>0;0;SUMA(B11:B12)) a dosáhli bychom delším vzorcem stejného výsledku. Toto je jen potvrzení úvodních slov, že v Excelu téměř vždy existují variantní řešení.
*
V řádku 16
pomocí funkce MIN opět zajistím, že poplatník neuplatní vyšší daňový bonus
podle § 35c ZDP, než povolených 5 025 Kč měsíčně (60 300 Kč ročně).Naši tabulku bychom jistě mohli vylepšit např. o definované názvy, absolutní a smíšené odkazy, vše v závislosti na konkrétních potřebách.
Podmíněný součet - nejlepší přítel účetních
Zamyslíme-li se nad prací profesních účetních, zjistíme, že poněkud zjednodušeně řečeno podmíněný součet je vlastně výsledkem jejich celoroční práce. Účetní výkazy totiž nejsou nic jiného než podmíněné součty konečných stavů (resp. zůstatků) a celá řada doplňujících informací v příloze účetní závěrky má podobu tabulek, opět zejména s podmíněnými součty. Pochopitelně, že tento podmíněný součet je výsledkem náročné práce aplikace značného množství účetních pravidel a tak nelze práci účetních bagatelizovat přirovnáním k podmíněnému sčítání, pouze se nasměrujeme na určitý způsob myšlení při využití Excelu. Na účtárnách se tak velmi často potýkáme s problémy, jak vyčíslit hodnotu tržeb do zahraničí podle jednotlivých teritorií, popř. segmentů, jaké náklady měla různá střediska, zakázky (nebo jejich skupiny), kolik protelefonovali zaměstnanci soukromě, jak rozpočítat poskytnutou dotaci na více dlouhodobých majetků a jistě bychom mohli jmenovat nespočet dalších problémů, kde sbíráme informace pomocí součtu za určité podmínky, častěji však za více podmínek. Jak již bylo naznačeno, Excel je v tomto přímo brilantní pomocník, o čemž nás přesvědčí následující řádky.
Součet s jednou podmínkou
Můžeme řešit více způsoby, my začneme funkcí SUMIF, která je přímo pro tento případ v Excelu připravená.+---------+----------------------------------+---------------------------------------------+ | Funkce | Syntaxe | Poznámka | +---------+----------------------------------+---------------------------------------------+ | SUMIF | =SUMIF(oblast; kritéria; součet) | Provede součet oblasti buněk podle zadaného | | | | kritéria. | +---------+----------------------------------+---------------------------------------------+
K syntaxi doplňme praktické doplnění:
*
oblast
musí mít stejnou velikost jako součet,
*
kritériem
může být i odkaz na buňku obsahující číslo, text, logickou hodnotu, datum nebo vzorec, pokud v kritériu chceme uplatnit odkaz na jinou buňku, je nutné použít operátor &
viz jednoduchý příklad: =SUMIF(B1:B2;">"&
A1;C1:C2)
Součet všech hodnot ve sloupci C za podmínky, že datum ve sloupci B je větší než 1. 1. 2012.
PŘÍKLAD
Podmíněný součet - tržby od dceřiné společnosti
Saldo
hodnoty závazků nebo pohledávek za určitý subjekt umí většina účetních softwarů, s výsledkovými účty už to může (i nemusí) být problém. Proto např. pokud uvádíme do přílohy účetní závěrky informaci o tržbách za spojenými osobami, můžeme tento problém snadno řešit s pomocí Excelu nad exportovaným účetním deníkem takto:
Povšimněme si, že na rozdíl od předchozího příkladu jsme do argumentu funkce jako kritérium součtu, zadali přímo textový řetězec "Dcera s.r.o.", stejného výsledku bychom dosáhli, pokud bychom se na textový řetězec odkázali: =SUMIF(H3:H7;"="
&
C1;C3:C7)Pokud máme v oblasti kritérií další podmínku pro součet odpovídající logickému NEBO (např. potřebujeme sečíst všechny tržby za Dcera s.r.o. nebo Obchodní a.s.), můžeme tento problém vyřešit velmi jednoduše prostým součtem dvou výsledků stejné funkce pokaždé s jiným argumentem v jedné buňce: =SUMIF(H3:H7;"
Dcera s.r.o.
";C3:C7)+SUMIF(H3:H7; "Obchodní a.s.
";C3:C7) nebo zapouzdříme jednoduše do argumentů funkce SUMA: =SUMA(SUMIF(H3:H7;"Dcera s.r.o.
";C3:C7); SUMIF(H3:H7; "Obchodní a.s.
";C3:C7)) Již několikrát zmiňovanou mimořádnou variabilitu Excelu dokládáme dalším způsobem zápisu této funkce, která vrátí stejný výsledek (jde o zapouzdření do funkce SUMA s použitím maticové konstanty, kterou poznáme podle složené závorky v argumentu funkce): =SUMA(SUMIF(H3:H7;{"Dcera s.r.o."/"Obchodní a.s."}; C3:C7))Podmíněný součet s jedním kritériem, můžeme také velmi efektivně využít i v situacích, kdy potřebujeme nalézt jedinečnou
číselnou
hodnotu v závislosti právě na jedinečném kritériu z určité oblasti. Lze tím nahradit vyhledávací funkci SVYHLEDAT, která činí uživatelům potíže a je pomalejší hlavně v sešitech s velkým množstvím použití této funkce. Následující příklad nám ukáže, jak na to v praxi:PŘÍKLAD
Podmíněný součet - přepočet kursů pomocí SUMIF
Účetní software v drtivé většině umí spočítat a zaúčtovat kursové rozdíly k závazkům a pohledávkám v cizí měně k rozvahovému dni automaticky. Tuto povinnost nám ukládá § 24 zákona č. 563/1991 Sb., o účetnictví, ve znění pozdějších předpisů. Většina účetních chce mít jistotu, že takto automaticky vypočtené a zaúčtované kursové rozdíly jsou v pořádku, protože ovlivňují jak správné vykázání aktiv a pasiv v rozvaze, tak vstupují do účetního výsledku hospodaření, z něhož se vychází při stanovení základu daně z příjmů. Náš příklad naznačí, jak jednoduše lze v Excelu právě pomocí funkce SUMIF kontrolní výpočet řešit:
Jak je z obrázku patrné, funkce SUMIF nám zde posloužila pro vyhledání kursu měny uvedeného u pohledávky nebo závazku, a to hned dvakrát:
*
jednou pro vyhledání kursu - sečte všechny kursy ve sloupci E, u kterých je ve sloupci D textový řetězec "EUR" nebo "RUB" a následně,
*
pro zajištění kursu na 1 Kč nám stejným způsobem sečte množství podle stejného parametru součtu - tedy "EUR" nebo "RUB".
Protože víme, že kursovní lístek obsahuje vždy jen jeden kurs pro jednu měnu, součet nám zde nahradí vyhledávací funkci, neboť vždy vrátí hodnotu 25,80 nebo 61,759 Kč a ještě zajistí, že kurs, který vstoupí do výpočtu, bude přepočtený na 1 Kč, protože kurs podělí množstvím. Nalezení správného přepočítacího kursu k rozvahovému dni je tedy v tomto případě v Excelu řešeno jako podíl dvou podmíněných součtů (součet kursů dle kódu měny: součet množství dle kódu měny = přepočítací kurs k rozvahovému dni). Pokud tímto způsobem připravíme excelovský sešit s oblastí dat obsahující přepočítací kurs (např. zkopírováním z webových stránek ČNB www.cnb.cz/cs/financni_trhy/devizovy_trh/kurzy_devizoveho_trhu/denni_kurz.jsp) a s oblastí účetních dat obsahující závazky a pohledávky (exportem z účetního systému), lze pomocí takového řešení ověřit správnost přepočtu účetním systémem automaticky účtovaných kursových rozdílů ke každému dni, ke kterému účetní jednotka sestavuje účetní závěrku, velmi rychle a téměř automatizovaně.
Poznámka:
Stejným způsobem bychom si mohli např. nadefinovat název "EUR" pomocí funkce SUMIF použité přímo v definici názvu. Pomocí tohoto názvu, jenž by si nesl hodnotu 25,8, bychom mohli provádět výpočty kdekoli v sešitu, aniž bychom si museli kurs pamatovat či na něj neustále odkazovat.Poznámka redakce: Na další díl seriálu o využití Excelu v praxi se můžete těšit v časopisu Účetnictví v praxi č. 10/2013.