Svět počítačů prošel za poslední desítky let tak prudkým vývojem, jako málokteré z odvětví lidského počínání. Ruku v ruce s tímto dynamickým rozvojem se velmi rychle zdokonalily i techniky zpracování účetnictví, a tak dnes již drtivá většina subjektů, ať již vedou účetnictví nebo daňovou evidenci, používají specializovaný software (dále jen "SW"). Přestože většina účetního SW dnešní doby je velmi vyspělá a umožňuje pracovníkům účtáren a ekonomických útvarů pracovat s účetními daty účelně a komfortně, je s účetní prací spojena celá řada požadavků, kde nám může vydatně pomoci nástroj pro účetní data jako stvořený - tabulkový procesor. Dominantní aplikací mezi uživateli počítačů v této oblasti je MS Excel, který nechybí v počítači snad žádné účetní/ho. Následující příspěvek je určen zejména uživatelům se základní znalostí prostředí tabulkového procesoru a vzhledem k mimořádné variabilitě Excelu nám naznačí alespoň zlomek možností, které nám tento skutečně mocný nástroj nabízí. Pro profesní účetní je jistě velmi příjemnou změnou, že problematika Excelu stojí na logice, tedy nelze ji zpochybnit žádným "právním" názorem ministerstva či správce daně. Dodejme ještě, že v následujících řádcích vycházíme z verze Excelu 2010, postupy zde uváděné jsou však převážně řešitelné i ve starších verzích, zejména E2007, popř. i E2003, E2000.
Využití Excelu v praxi
Milan
Řezníček
VZORCE - SRDCE EXCELU
Stejně jako naše srdce vhání každým stahem životadárnou krev do celého těla, život našim tabulkám vdechneme právě pomocí vzorců. Jak dobře jejich stavbě porozumíme, takovou "fyzičkou" můžeme disponovat v rámci řešení našich pracovních problémů pomocí tabulkového procesoru. Z tohoto důvodu se budeme zabývat převážně zvládnutím podstaty stavby vzorců. Ukážeme si základní principy a možnosti včetně několika tipů se zaměřením právě na ty vestavěné funkce, které nám umožní vytvářet jednoduché i složitější struktury vzorců, pomocí kterých si můžeme usnadnit každodenní účetní praxi. Jak ukáží následující praktické příklady, jedná se o zajímavý, kreativní, flexibilní a mimořádně efektivní způsob práce s daty, při kterém není třeba mít speciální znalosti programovacích jazyků, postačí nám respektovat logické principy Excelu, základní matematická pravidla a vlastní schopnost na těchto znalostech stavět a dále je rozvíjet.
Možnosti zápisu vzorce a základní výpočetní možnosti
Buňka v listě pracovního sešitu může v prostředí tabulkového procesoru obsahovat libovolné číslo, textový řetězec, datum nebo
vzorec.
Jakýkoli vzorec vložíme do buňky na listu pomocí klávesy "=". Buňku editujeme dvojklikem myši pomocí řádku vzorců nacházejícím se nad buňkami (viz následující obrázky), popř. kopírováním vzorců nebo hodnot z jiné buňky a to za pomoci myši (tažením) nebo klávesnice, kdy využijeme notoricky známých kombinací kláves:*
Ctrl + C ->Ctrl + V pro libovolnou oblast, kopírovaná buňka nemusí být do kopírované oblasti zahrnuta.
Klávesové zkratky pro případy, kdy kopírovaná buňka musí být do kopírované oblasti zahrnuta:
*
Ctrl + C
->
Ctrl + D pro kopírování do sloupce,*
Ctrl + C
->
Ctrl + R pro kopírování do řádku,*
Ctrl + C
->
Ctrl + ENTER pro kopírování vzorce do sousedních řádků i sloupců.Vzorec však musí vždy začínat oním "
=
", čímž dáme Excelu na srozuměnou, že má zápis vyhodnotit právě jako vzorec. Pro doplnění jen uveďme, že chceme-li editovat (upravovat) jakýkoli zapsaný vzorec v buňce, můžeme pro editaci využít buď dvojklik levého tlačítka myši nebo klávesu F2
.Základní výpočty
Pro většinu čtenářů nejde o nic nového, přesto pro jistotu naznačíme základní možnosti výpočtu, od kterých se odrazíme k náročnějším úlohám z praxe. Základní znaménka používaná ve vzorcích jsou základní matematické operátory [+; -; *; /; ^ (mocnění)]. Dále porovnávací operátory [<;>]. Namísto desetinného čísla lze použít i "%" a znak "&" pro spojování textových řetězců.
PŘÍKLAD
Jednoduché výpočty - úrok z jistiny při standardu úročení 30E/360
Vypočtěme úrok 10 % z jistiny 100 000 Kč za 30 dní.
Buňka obsahující vzorec nám zobrazí výsledek, celý zápis vzorce vidíme v řádku vzorců umístěném nad listem buněk. Stojí za povšimnutí, že Excel je postaven na respektování matematických pravidel - každou z buněk lze proto využít jako velmi chytrou a flexibilní kalkulačku.
PŘÍKLAD
Jednoduché výpočty - výpočet čisté mzdy v jedné buňce
Zapíšeme-li do buňky např. tento zápis (viz obrázek), vypočteme čistou mzdu zaměstnance s hrubým měsíčním příjmem 25 000 Kč bez nutnosti použít papír a kalkulačku nebo tvořit tabulku s dílčími mezi-výpočty, výsledek se dostaví ve zlomku vteřiny. Pro zjednodušení abstrahujeme od všech konkrétních zákonných podmínek jako je správné zaokrouhlování, limity pro uplatnění slev na dani apod.:
Potřebujeme-li rychle zjistit, kolik činí daň z příjmů po uplatnění slevy na dani. Označíme kurzorem příslušnou část vzorce, tj. 25000*1,34*0,15-2170:
Excel jednoduše
vyhodnotí příslušnou část vzorce a zobrazí výsledek
(v našem příkladu činí daň z příjmů 2 955 Kč). Tuto funkčnost oceníme zejména, pokud potřebujeme ověřit dílčí výsledky výpočtů. Je však nutné vybrat vždy tu část vzorce, kterou lze samostatně vyhodnotit (tedy se závorkami, argumenty vnořených funkcí apod.). Pro návrat do původního stavu je třeba použít klávesu Esc,
případným potvrzením klávesy Enter
bychom předmětnou část vzorce nahradili vyhodnoceným výsledkem, což není žádoucí.Absolutní, relativní a smíšené odkazy
Ještě než se pustíme do tvorby vzorců pomocí vestavěných funkcí, objasníme si
absolutní, smíšené a relativní odkazy,
pochopení těchto principů má pro vytváření vzorců zásadní význam, a proto je třeba jim věnovat pozornost (správným adresováním předejdeme komplikacím a výskytu chyb při hromadném kopírování vzorců). Organizace dat v Excelu do řádků (očíslovány arabskými číslicemi) a sloupců (značených písmeny abecedy), umožňuje dát každé buňce (popř. oblasti buněk) adresný název, který vychází z její polohy na pracovním listě. První buňka prvního řádku je tedy A1,
oblast prvních dvou buněk na prvním řádku je adresována jako A1:B1.
Odkaz na buňku či oblast buněk má ve vzorci obecně takovouto podobu:
=[Sešit]List!buňka(oblast), např. odkaz na buňku A1 na listě1 v sešitu1.xls z jiného sešitu bude mít tuto podobu: =[Sešit1]List1!A1. Podoba odkazu je vždy odvislá od toho, zda odkazujeme na buňky ve stejném listě, na jiném listě nebo v jiném sešitu. Do vzorce pak můžeme odkaz zapsat takto:
PŘÍKLAD
Odkazy - výpočet čisté mzdy pomocí proměnné hodnoty
Náš příklad, ve kterém jsme pomocí jednoduchého vzorce vypočetli čistou mzdu pracovníka, jehož hrubý měsíční příjem činil 25 000 Kč, pomocí jediné buňky, nyní vylepšíme. Částka 25 000 Kč je však ve vzorci použita jako konstanta. V praxi však obvykle potřebujeme flexibilnější způsob zápisu, velmi často totiž za pomoci jednoho vzorce provádíme i stovky výpočtů z údajů z různých buněk a tak, abychom nemuseli při změně hrubé mzdy vstupující do výpočtu provádět buď:
*
ruční přepis částky 25 000 všude tam, kde se ve vzorci nachází, anebo
*
pomocí funkce Excelu
Najít ->Nahradit
(Ctrl + F a Ctrl + H) provést patřičné nahrazení za částku jinou.Ukážeme si účelnější postup (zejména pro další práci se vzorcem - kopírování). Vezměme tedy případ určité modelace za účelem zjištění čisté mzdy z různých hrubých mezd. Posuneme-li výpočet čisté mzdy do sloupce B (buňky B1) a do sloupce A (buňky A1) zapíšeme hodnotu 25000, můžeme se ve vzorci zapsaném v buňce B1 na hodnotu v buňce A1 jednoduše odkázat, čímž vlastně do našeho vzorce dosadíme místo hodnoty 25000 (konstanty) -
relativní odkaz
na buňku A1,
pročež z buňky B1
tímto uděláme závisle proměnnou hodnotu, jenž se bude měnit v přímé závislosti na hodnotě zapsané do A1. Na následujícím obrázku vidíme podobu zápisu:Řešení
Takto vložený odkaz nazýváme
odkazem relativním
, protože se relativně mění vůči poloze kopírované buňky (odkaz se bude měnit jak při kopírování do řádků, tak do sloupců). Jakmile zkopírujeme vzorec do buňky B2, Excel posune odkaz, odkud si bere hodnotu hrubé mzdy na buňku A2 - tedy o řádek atd. Relativní odkazy jsou tedy vhodné všude tam, kde chceme kopírováním vzorce dosáhnout současnou změnu odkazů v něm použitých - změnu řádku i sloupce.Naproti tomu
absolutní nebo smíšený odkaz
použijeme pro situace, kdy nechceme, aby se poloha buňky (nebo oblasti), na kterou odkazujeme, měnila buď vůbec (absolutní), nebo aby se měnil pouze řádek či sloupec (smíšený). Toho dosáhneme pomocí znaku "$", který zapíšeme do vzorce tak, že do něj vstoupíme, kurzor umístíme na odkaz a stiskneme klávesu F4
(opakovaným stiskem dosáhneme změny odkazu $A$2->$2->A2), zapsaný vzorec se nám konkrétně změní takto:1) Absolutní odkaz
=
$
A$
2-($
A$
2*(0,045+0,065)+$
A$
2*1, 34*0,15-2070) - při kopírování vzorce se nemění ani řádek ani sloupec odkazu na buňku (A1)
2) Smíšený odkaz
a) =
$
A2-($
A2*(0,045+0,065)+$
A2*1,34*0,15-2070)- při kopírování vzorce se nemění pouze sloupec (A), mění se však řádek
b) =A
$
2-(A$
2*(0,045+0,065)+A$
2*1,34*0,15-2070) - při kopírování vzorce se mění sloupec, nemění se však řádek (1)
PŘÍKLAD
Odkazy - variantní výpočet čisté mzdy pomocí jediného vzorce
Pojďme si nyní vyzkoušet všechny tři možné varianty odkazování při pokračování práce s naším příkladem a zadejme si úlohu výpočtu čisté mzdy u poplatníka, který má hrubou mzdu 25 nebo 26 nebo 27 000 Kč, uplatňuje slevu na dani na žádné nebo jedno nebo dvě děti. Prakticky to tedy znamená, že hledáme 9 výsledků (3 x hrubá mzda x 3 možnosti počtu dětí), přičemž poplatník vždy uplatňuje základní slevu na poplatníka ve výši 2 070 Kč. Naším úkolem je zadat vzorec pomocí různých odkazů tak, abychom jej mohli pohodlně rozkopírovat jednou akcí do všech 9 buněk s požadovaným výsledkem (čistá mzda) bez nutnosti dalších dodatečných úprav.
Řešení
Z obrázku je patrné, že odkaz na hrubou mzdu v buňce A4 se při kopírování vzorce do řádků mění podle čísla řádku, zadali jsme smíšený odkaz $A4 (sloupec je absolutní, řádek relativní), odkaz na počet dětí se mění v závislosti na čísle sloupce, zadali jsme B$3 (sloupec je relativní, řádek absolutní) a sleva na poplatníka musí být ve všech variantách stejná, zadali jsme proto $B$1 (sloupec i řádek absolutně - kopírováním se nemění). Jak je vidět, namísto 9 výpočtů můžeme pomocí jediného vzorce v Excelu namodelovat libovolný počet variant během krátké chvíle, změnou vstupních hodnot ve sloupci A si ihned "sáhneme" na požadované výsledky.
Názvy buněk a oblastí
Vývojáři Excelu vdechli základnímu stavebnímu kameni tabulek - buňkám - množství užitečných vlastností, my si nyní předvedeme jednu z nich, a to definici vlastních názvů, což je při tvorbě vzorců a při práci s vestavěnými funkcemi vlastnost Excelu nadmíru užitečná. Použitím vlastních názvů mj. dosáhneme:
*
zpřehlednění zápisu i čtení vzorců a usnadnění orientace ve vzorcích (tvůrcům i uživatelům tabulek),
*
snadné použití absolutních, relativních odkazů a konstant (i maticových), čímž značně zefektivníme zejména kopírování vzorců (typicky v praxi pro sazby daní, odvodů, kurzy měn k rozvahovému dni, úrokové sazby u půjček a úvěrů aj.),
*
přímo do definice názvu lze vkládat vzorce či funkce, otevírají se nám tak další rozsáhlé možnosti výpočtů a jiné nástroje (např. dynamické oblasti dat aj.),
*
snazšího odkazování při ověřování vstupních dat apod.
Vlastní název, který odkazuje na buňku (nebo oblast buněk) vytvoříme nejrychleji tak, že buňku nebo oblast označíme a do pole vedle řádku vzorců, které zobrazuje právě vybranou buňku nebo oblast buněk, klikneme myší a název zapíšeme, jak ukazuje obrázek:
Na buňku A1 tedy v jiném vzorci můžeme odkázat takto:
*
=A1-B1, nebo
*
=Čistá_mzda-B1, přičemž výsledek, který vzorec vrátí do buňky, bude stejný.
Rozdíl mezi oběma zápisy je na první pohled zřejmý, zejména u rozsáhlých tabulek se složitějšími vzorci odkazujícími na množství dalších buněk či oblastí nemusíme složitě pátrat mezi jednotlivými vazbami odkazů, usnadnění spočívá v zápisu i čtení vzorců.
Právě proto, že vlastní názvy jsou bytostně spjaty se vzorci, najdeme jejich správu, tvorbu a editaci ve verzích E2007/E2010 na kartě
Vzorce
->Správce názvů,
v E2003 a nižších verzích v menu Vložit
->Název
->Definovat
. Zde je možné s názvy pracovat dle našich potřeb. Považuji za důležité uvést několik základních pravidel, která je třeba respektovat:?
Názvy můžeme použít pro jednu buňku, souvislou i ne souvislou oblast buněk.
?
Excel
rozlišuje
v názvech diakritiku, pokud tedy ve vzorci napíšeme místo odkazu Čistá_mzda název Cis-ta_mzda, vzorec vrátí chybovou hlášku #NÁZEV?, protože uvedený název neumí identifikovat.?
Excel
nerozlišuje
v použitých názvech velká či malá písmena, je tedy možno napsat do vzorce jak Čistá_mzda,
tak i čistá_mzda.?
Vkládat názvy do vzorců lze pomocí klávesy
F3
nebo nám během ručního zápisu Excel inteligentně název nabídne:
?
Chceme-li rychle přejít kdekoli v sešitu na definovaný název, použijeme klávesu
F5.
*
Název můžeme definovat jako jedinečný buď pro celý sešit anebo pro konkrétní list sešitu (prakticky lze tuto vlastnost opět velmi dobře využít).
*
Namísto mezery při použití více slov použijeme podtr-žítko "_".
*
Název nesmí začínat číslem např. 15Sklad (nutno zapsat jako Sklad15 nebo Sklad_15).
*
Excel automaticky vytváří při definici názvu odkazy absolutní, chceme-li adresovat relativně, je třeba název upravit a je velmi důležité, která buňka je při definici názvu aktivní.
Problematika definice názvů by sama o sobě zasloužila delší pojednání (použití maticových konstant, trojrozměrné názvy aj.), my si však pro tuto kapitolu výklad k problematice názvů uzavřeme konstatováním, že pro absolutní i relativní odkazování lze využít námi definované názvy buněk či celých oblastí. Než si ale provedeme další modifikaci našeho příkladu se mzdami, naznačíme si jednoduché využití absolutního adresování pomocí názvů v následujícím příkladu:
PŘÍKLAD
Definované názvy - pojmenované oblasti ve vzorci SUMA
Pojmenujme si oblast buněk obsahujících výnosy (buňky B3:B5) názvem "Výnosy" a oblast buněk obsahujících náklady (buňky C3:C5) názvem "Náklady" a vytvořme si vzorec pro výpočet výsledku hospodaření z této zjednodušené výsledovky:
V tomto příkladu byla použita vestavěná funkce SUMA, přestože o vestavěných funkcích bude pojed náno až v další kapitole. Vzhledem k tomu, že se jedná o nejpoužívanější funkci, předpokládáme, že je jí použití, a tím i pochopení příkladu, nebude činit potíže.
PŘÍKLAD
Definované názvy - variantní výpočet čisté mzdy pomocí jediného vzorce
Definujme si tedy např. tyto názvy pro různé odkazy vstupující do našeho výpočtu:
?
SlevaPoplatnik
jako absolutní odkaz na buňku B2
se slevou na poplatníka, která obsahuje hodnotu 2070.?
HM
jako absolutní odkaz na oblast buněk B4:B6,
reprezentující hrubé mzdy.*
PocetDeti
jako absolutní odkaz na oblast buněk B3:D3,
která informuje o počtu dětí vstupujících do výpočtu.*
Pojistne
jako konstantu nesoucí hodnotu součtu 0,045 + 0,065 = 0,11 odpovídající součtu sazeb zdravotního a sociálního pojištění vyjádřenou desetinným číslem (stejně tak můžeme zapsat 11 %).*
SlevaDite
jako konstantu mající hodnotu 1 117 pro uplatnění slevy na jedno vyživované dítě.*
Dan
jako další konstantu sazby daně z příjmů vyjádřenou desetinným číslem 0,15.Ukažme si pro úplnost, jak definovat konstantu např.
Pojistne
:
Výpočet hodnot v tabulce za pomoci námi definovaných názvů nám ukazuje následující obrázek (zcela jistě bychom názvy volili jinak pro případ čistě našeho použití - výrazně kratší a jinak pro tabulky používané dalšími uživateli).
Řešení
V označeném řádku vzorců vidíme již místo odkazů na buňky či oblasti buněk nebo na čísla pouze
námi definované názvy,
výsledky se shodují, vzorec lze v této podobě rozkopírovat do všech osmi zbývajících buněk a vrátí shodné hodnoty s předchozím řešením. Excel je natolik chytrý, že i při použití absolutně definovaného názvu jako je část sloupce (v našem příkladu oblast buněk A4:A6) či řádku (v příkladu oblast buněk B3:D3), použije při výpočtu ve vzorci odkazujícím na takový název z oblasti hodnot (např. definovaný název HM je oblastí hodnot [25000;26000;27000]) tu hodnotu, která s buňkou výpočtu polohou souvisí (pro výpočet čistých mezd v řádku oblasti buněk B4:D4 použije hodnotu hrubé mzdy z A4 atd.), můžeme tedy říci, že odkaz definovaný jako řádek nebo sloupec (či jejich část) se chová částečně jako smíšený odkaz zadaný pomocí "$".Poznámka:
V tomto příkladu jsme záměrně vynechali tzv. maticové konstanty, bude o nich pojednáno až v kapitole o maticových vzorcích.