Výpočet Čisté Mzdy Pomocí Vzorců v Excelu

Excel je mocný nástroj, který umožňuje efektivní práci s daty a usnadňuje každodenní účetní praxi. Stejně jako srdce vhání životadárnou krev do celého těla, vzorce v Excelu vdechují život tabulkám. Pochopení principů tvorby vzorců je klíčové pro řešení pracovních problémů pomocí tohoto tabulkového procesoru. Není třeba mít speciální znalosti programovacích jazyků, stačí respektovat logické principy Excelu, základní matematická pravidla a schopnost na těchto znalostech stavět a dále je rozvíjet.

Buňka v listě pracovního sešitu může obsahovat libovolné číslo, textový řetězec, datum nebo vzorec. Jakýkoli vzorec vložíme do buňky na listu pomocí klávesy "=". 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í 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ů.

Možnosti zápisu vzorce a základní výpočetní možnosti

Buňku editujeme dvojklikem myši pomocí řádku vzorců nacházející se nad buňkami, 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.
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.

Příklad: Jednoduché výpočty - úrok z jistiny

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, 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:

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:

=$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)
=$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
=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.

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: *=Č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 pojedná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). 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ý...

Jak se počítá čistá mzda?

Čistá mzda = hrubá mzda - sociální poj. - zdravotní poj. Výpočet čisté mzdy vychází z hrubé mzdy (jedná se z pravidla o základní mzdu uvedenou v pracovní smlouvě + příplatky, prémie, odměny, osobní ohodnocení, náhrady mzdy). Z tohoto základu daně, který je nutno zaokrouhlit na celé koruny nahoru, se vypočte 15% daň z příjmu.

Od této zálohy na daň z příjmu lze odečíst slevy na dani, které může uplatňovat zaměstnanec v pracovním poměru mající podepsané prohlášení poplatníka daně z příjmu fyzických osob ze závislé činnosti, např.: slevu na poplatníka ve výši 2 570 Kč/měsíc, slevu pro invalidní důchod I. a II. Stupně ve výši 210 Kč/měsíc, slevu pro invalidní důchod III. Stupně ve výši 420 Kč/měsíc nebo také daňové zvýhodnění na děti podle počtu nezaopatřených (studujících) dětí, a další. Po uplatnění jakékoliv slevy nebo bonusu je záloha na daň nižší.

V případě exekuce se z čisté mzdy zaměstnance strhává částka až do hranice nezabavitelného minima.

Příklady výpočtu čisté mzdy

Příklad 1: Leona

Fyzioterapeutka Leona si měsíčně vydělá 40 000 Kč hrubého. Je svobodná a bezdětná. Vypočítá si odvody na pojistném. Zdravotní pojištění představuje 4,5 % a sociální pojištění 7,1 % z hrubé mzdy. Vypočítá si daň z příjmu. Z hrubé mzdy vypočítá 15% daň, která činí 6 000 Kč. Vypočítá si daňové slevy a zvýhodnění. Leona nemá děti ani není invalidní. Má proto nárok pouze na základní slevu na dani ve výši 2 570 Kč. Teď to všechno spočítá dohromady. Z 40 000 Kč hrubého odečte 4 640 Kč na pojištění a 3 430 Kč na daň.

Příklad 2: Zuzana

Učitelka Zuzana si měsíčně vydělá 40 000 Kč hrubého stejně jako fyzioterapeutka Leona. Vypočítá si odvody na pojistném. Zdravotní pojištění představuje 4,5 % a sociální pojištění 7,1 % z hrubé mzdy. Vypočítá si daň z příjmu. Z hrubé mzdy vypočítá 15% daň, která činí 6 000 Kč. Vypočítá si daňové slevy a zvýhodnění. Zuzana má nárok na základní slevu na dani ve výši 2 570 Kč a na slevu na první dítě ve výši 1 267 Kč. Celková sleva je 3 837 Kč. Teď to všechno spočítá dohromady. Z 40 000 Kč hrubého odečte 4 640 Kč na pojištění a 2 163 Kč na daň.

Slevu na děti si může uplatnit pouze jeden z rodičů. Kdyby měla Zuzana více dětí, zvýší se daňové slevy a díky tomu dosáhne na vyšší čistou mzdu. Pokud by měla dvě děti, její čistá mzda bude 35 057 Kč.

Příklad 3: Lukáš

Lukáš je manažer, který si měsíčně vydělá 150 000 Kč hrubého. Vypočítá si odvody na pojistném. Zdravotní pojištění představuje 4,5 % a sociální pojištění 7,1 % z hrubé mzdy. To znamená 6 750 Kč a 10 650 Kč. Vypočítá si daň z příjmu. Protože Lukáš vydělává víc než 131 900 Kč hrubého měsíčně, vztahuje se na něj progresivní daň. Vše, co zmíněnou sumu přesahuje, Lukáš zdaní 23 %. Z částky 131 900 Kč tak odvede 15% daň z příjmů - tedy 19 785,15 Kč - a ze zbývajících 18 100 Kč zaplatí 23% sazbu - 4 163 Kč. Vypočítá si daňové slevy a zvýhodnění. Teď to všechno spočítá dohromady. Ze 150 000 Kč hrubého odečte 17 400 Kč na pojištění a 21 378,15 Kč na dani.

Z předchozích příkladů je jasně vidět, že i když všichni dostávají stejnou hrubou mzdu, jejich čistá mzda se liší.

Tabulka: Měsíční slevy na dani

Typ slevy	Částka (Kč/měsíc)
Sleva na poplatníka	2 570
Invalidita 1. nebo 2. stupně	210
Invalidita 3. stupně	420
Daňové zvýhodnění na 1. dítě	1 267
Daňové zvýhodnění na 2. dítě	1 860
Daňové zvýhodnění na 3. a další dítě	2 320

Měsíční slevy na dani se zohledňují už na výplatní pásce. Existují ale ještě roční slevy, které můžete vykázat až na ročním zúčtování daně (požádejte o něj zaměstnavatele), nebo je zohledníte v daňovém přiznání.

tags: #vypocet #ciste #mzdy #vzorec #excel