Finanèní gramotnost. Výpoèty v Excelu. Zbynìk Bárta

Finanèní gramotnost Výpoèty v Excelu Zbynìk Bárta

Finanční gramotnost Výpočty v Excelu

Finanční gramotnost Výpočty v Excelu

Ing. Zbyněk Bárta

Vzor citace: BÁRTA, Z. Finanční gramotnost – Výpočty v Excelu. Praha: Wolters Kluwer, a. s., 2014. 372 s.

KATALOGIZACE V KNIZE – NÁRODNÍ KNIHOVNA ČR Bárta, Zbyněk Finanční gramotnost: výpočty v Excelu / Zbyněk Bárta. – Praha: Wolters Kluwer, 2014. – 372 s. – (Řízení školy) ISBN 978-80-7478-483-5 336.7 * 37.03:336 * 004.42Excel – osobní finance – finanční gramotnost – Microsoft Excel – příručky 336.7 - Finance [4]

© Wolters Kluwer, a. s., 2014

ISBN 978-80-7478-483-5 (brož.) ISBN 978-80-7478-484-2 (pdf)

Obsah

Obsah Úvod . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Seznam použitých zkratek . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 1.

Tabulkový kalkulátor MS Excel . . . . . . . . . . . . . . . . . . . . . . . 13 1.1 Rekapitulace aplikace Excel . . . . . . . . . . . . . . . . . . . . . . . . . . 1.2 Pracovní prostředí . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.2.1 Úprava pracovního prostředí . . . . . . . . . . . . . . . . . . . . 1.2.2 Zadávání vzorců . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.2.3 Kopírování a přesun vzorců . . . . . . . . . . . . . . . . . . . . . 1.2.4 Tvorba řad v Excelu . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.2.5 Zadávání – používání funkcí Excelu . . . . . . . . . . . . . . 1.2.6 Nástroj Ověření dat . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.2.7 Podmíněné formátování . . . . . . . . . . . . . . . . . . . . . . . 1.2.8 Zadávání poznámek do aplikace . . . . . . . . . . . . . . . . . 1.2.9 Spuštění dodatečných nástrojů Excelu . . . . . . . . . . . . 1.2.10 Ovládací prvky formuláře . . . . . . . . . . . . . . . . . . . . . .

2.

13 13 14 20 23 30 31 41 43 47 48 49

Matematické vzorce a vztahy ve finančních výpočtech . . . 54 2.1 2.2 2.3 2.4 2.5

Průměry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .55 Jednoduché úročení . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 Složené úročení . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 Smíšené úročení . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 Využití jednoduchého úročení . . . . . . . . . . . . . . . . . . . . . . . . . 70

3.

Inflace, cenová hladina, nominální a reálné finance . . . . . 75

4.

Základní přehled finančních funkcí . . . . . . . . . . . . . . . . . . . 90

5.

Rodinný rozpočet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 5.1 Rodinný rozpočet – vztah příjmů a výdajů . . . . . . . . . . . . . . . 94 5.2 Rodinný rozpočet – jednotlivé kategorie a složky . . . . . . . . . 97 5.2.1 Konkrétní složení příjmů a výdajů . . . . . . . . . . . . . . . 99 5.3 Analýza výdajů rodiny . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 5.4 Praktické sestavení rozpočtu . . . . . . . . . . . . . . . . . . . . . . . . . 105 5.4.1 Jednodušší forma rozpočtu . . . . . . . . . . . . . . . . . . . . 105 5.4.2 Komplexní forma rozpočtu . . . . . . . . . . . . . . . . . . . . 116 5.5 Finanční rozhodování rodiny . . . . . . . . . . . . . . . . . . . . . . . . . 133

5

Finanční gramotnost – výpočty v Excelu

5.6 Rozhodování strategická . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5.6.1 Problematika řešení bydlení . . . . . . . . . . . . . . . . . . . 5.6.2 Rozhodování o pořízení automobilu . . . . . . . . . . . . 5.6.3 Rozhodování o zajištění v důchodu . . . . . . . . . . . . . 5.7 Rozhodování běžná . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5.8 Rodinné finance – celkový pohled . . . . . . . . . . . . . . . . . . . . .

136 136 148 155 166 168

6.

Výpočty spojené s půjčováním . . . . . . . . . . . . . . . . . . . . . . 170

7.

Výpočty časové hodnoty peněz . . . . . . . . . . . . . . . . . . . . . 191 7.1 Časová hodnota peněz . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7.2 Zohlednění inflace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7.3 Příklad výpočtu ČHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7.3.1 Výpočet sestavením vzorce . . . . . . . . . . . . . . . . . . . . 7.3.2 Výpočet pomocí funkce . . . . . . . . . . . . . . . . . . . . . . . 7.4 Základní výpočty časové hodnoty peněz . . . . . . . . . . . . . . . 7.4.1 Časování finančních částek . . . . . . . . . . . . . . . . . . . . 7.4.2 Využití funkcí Excelu pro ČHP . . . . . . . . . . . . . . . . . . 7.5 Sestavení tabulky hodnot Umořovatele . . . . . . . . . . . . . . . . 7.5.1 Různé způsoby vyjádření Umořovatele . . . . . . . . . . 7.5.2 Tabulka Umořovatele sestavená vzorcem . . . . . . . . 7.5.3 Tabulka Umořovatele sestavená nástrojem Excelu „Tabulka dat“ . . . . . . . . . . . . . . . . . . . . . . . . .

8. 9.

191 192 192 193 193 195 197 200 213 213 213 215

Výpočet roční procentní sazby nákladů (RPSN) . . . . . . . . . 218 Výpočty spojené se spořením a investováním . . . . . . . . . 243 9.1 Hodnocení výnosů investice . . . . . . . . . . . . . . . . . . . . . . . . . . 251

Přílohy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281 Seznam příloh . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366 Rejstřík . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367

6

Úvod

Úvod Rozvoj tržního hospodářství vytvořil v naší republice nové ekonomické podmínky, na které není veřejnost připravena. Opakovaně prováděné průzkumy a šetření potvrzují skutečnost, že finanční gramotnost (FG) obyvatel ČR je na nízké úrovni a lidé neumí se svými penězi příliš dobře hospodařit. Důsledkem je zvyšující se zadluženost jednotlivců, domácností a značný nárůst osobních bankrotů a exekucí. Stále agresivnější praktiky bank i finančních institucí ještě zdůrazňují nutnost a potřebu věnovat se zvyšování FG u co nejširší veřejnosti. Pro lepší pochopení tématu lze FG charakterizovat jako soubor znalostí, dovedností a hodnotových postojů občana nezbytných k tomu, aby finančně zabezpečil sebe a svou rodinu v současné společnosti a aktivně vystupoval na trhu finančních produktů a služeb. Finančně gramotný občan se orientuje v problematice peněz a cen a je schopen odpovědně spravovat osobní (rodinný) rozpočet, včetně správy finančních aktiv a finančních závazků s ohledem na měnící se životní situace.1 Publikace je zaměřena především na podporu řešení základních modelových příkladů, výpočtů a situací, které prolínají problematikou finanční gramotnosti v prostředí tabulkového kalkulátoru MS Excel. K jeho aplikaci není zapotřebí žádných složitých znalostí, ale pouze základní uživatelské dovednosti rozšířené o nové informace uvedené v této publikaci. Jednotlivé kapitoly seznamují čtenáře s elementárními vlastnostmi tabulkového kalkulátoru potřebného pro jednoduché a snadné sestavování vzorců. Dále ukazují aplikaci implementovaných, vestavěných a naprogramovaných funkcí pro provádění celé řady praktických výpočtů. Dochází zde k propojení matematiky na středoškolské úrovni, znalostí ze základů finanční gramotnosti a vlastního využití tabulkového kalkulátoru ve výpočtech FG. Z uvedeného vyplývá, že záběr toho, co zařazujeme do FG, je nesmírně široký a rozsáhlý. Znalosti a využívání nástrojů Excelu zjednodušuje celou tuto problematiku. Právě s použitím Excelu lze provádět a sestavovat celou řadu výpočtů, kalkulací, propočtů, šetření, provádět popis závislostí, a konečně i analýzu a prezentaci zaměřenou do oblasti finančních a bankovních transakcí. Umožňuje tak porozumět různým souvislostem a vazbám ve finančních výpočtech, a tak zvýšit atraktivnost a porozumění

1

Národní strategie finančního vzdělávání, htpp://www.mfcr.cz/cps/rde/xbcr/mfcr/Narodni_strategie_Financniho_vzdelavani_MF2010.pdf.

7


v oblasti FG. Současně tak pomůže snížit neznalosti a eliminovat problémy v chápání základních výpočtů FG. Z pochopitelných důvodů je značná pozornost věnována rodinnému rozpočtu, jeho sestavení a práci s ním. V případě půjčování peněz se opakovaně objevuje pojem roční procentní sazba nákladů (RPSN), jehož zevrubné vysvětlení je provedeno na nemalém počtu stran. Úrokové procento, způsob úročení a doba splatnosti půjčky (úvěru), popřípadě vlastní velikost půjčky jsou v knize popsány, objasněny a je zdůrazněna jejich důležitost. Autor zvolil produkt MS Office Excel pro jeho širokou rozšířenost především na školách a dá se říci i uživatelskou přívětivost aplikace. Počínaje verzí MS Office 2007 došlo k řadě „vylepšení“. Namátkou vzpomeňme podmíněné formátování (více podmínek a lepší grafické ztvárnění prvků), jednodušší práce s tabulkou, lepší vizuální provedení všech grafů, vkládání funkcí přímo zápisem do buňky s online nápovědou a tak bychom mohli pokračovat. Nic ale nebrání tomu, aby se předkládané úlohy, výpočty a problémy řešily v jiných takto zaměřených SW produktech, například ve free verzi produktu LibreOffice Calc. Obsahuje relativně stejné či podobné postupy při zadávaní vzorců, sestavování grafů, nástroje a funkce v našem případě finanční. Kupříkladu názvy funkcí vychází z anglických termínů a argumenty jsou obdobné jako v aplikaci Excel: MS Excel

LibreOffice Calc

SOUČHODNOTA

PV

Počítá, zjišťuje současnou hodnotu investice.

XIRR

IRR

Počítá, zjišťuje vnitřní výnosnost pro harmonogram peněžních toků.

BUDHODNOTA

FV

Počítá, zjišťuje budoucí hodnotu investice.

A některé finanční funkce používané v obou aplikacích jsou označeny shodným názvem: ACCRINTM

ACCRINTM

EFFECT

EFFECT

RECEIVED

RECEIVED

Počítá, zjišťuje nahromaděný úrok z cenného papíru, ze kterého je úrok placen k datu splatnosti. Počítá, zjišťuje efektivní roční úrokovou sazbu. Počítá, zjišťuje částku obdrženou k datu splatnosti plně investovaného cenného papíru.

Cílem knihy je seznámit širokou veřejnost, studenty a žáky s příklady, úlohami či problémy, se kterými se mohou setkat ve FG. Není vlastní učebnicí

8

Úvod

v pravém slova smyslu, neboť na trhu je v současnosti řada titulů, které tuto úlohu již plní. Ale žádná z nich není zaměřena na využití tabulkového kalkulátoru v oblasti finanční gramotnosti. Celou publikaci doplňuje a provází řada grafů, schémat a vysvětlení proto, aby čtenář mohl snadno jednotlivé úlohy sestavit, zkontrolovat, popřípadě upravit podle svých potřeb. Modelové příklady jsou, pokud je to vhodné a smysluplné, řešeny více možnými způsoby tak, aby čtenář lépe pochopil význam, rozměr a logiku prováděných výpočtů, analýz, závěrů a doporučení. Kniha by mohla být vhodnou pomůckou a zdrojem užitečných informací pro studium a výuku FG nejen pro oblast školství, ale i pro širokou veřejnost.

Doporučení pro práci s knihou Komentář provázející čtenáře knihou je postaven na vysvětlení potřebných a nutných základů. Těžiště knihy spočívá v efektivním postupu sestavení, kontrole a řešení modelových příkladů. Protože může být úroveň znalostí Excelu u čtenáře různá, je to nejdůležitější pro jeho použití zopakováno a vysvětleno v kapitole č. 1. Obsahuje základní elementární zásady pro práci v tabulkovém kalkulátoru. Počínaje strukturou aplikace a konče využitím připravených finančních funkcí a nástrojů. Přílohy obsahují zajímavé a ilustrativní informace související s finančními transakcemi a údaje o cenách, spotřebě a výdajích, inflaci v ČR atd. Pro snazší orientaci je v příloze č. 3 proveden stručný výčet všech připravených finančních funkcí ve verzi MS Excel 2007.

9


Použité piktogramy:

!

Důležité a stěžejní informace, závěry či definice jsou označeny v textu symbolem „vykřičník“.

4

Symbolem „zaškrtávátko“ je označena část textu, většinou úloha, určená pro čtenáře k tomu, aby si ji sám v první fázi zkusil vyřešit, tj. tedy samostatně provedl následující kroky: n n n n n

analyzoval zadání, připravil potřebný model, našel odpovídající nástroje v aplikaci Excel, dosadil vstupní data, ověřil správnost zjištěných výsledků.

V druhé fázi s využitím textu publikace provedl: n kontrolu vlastního řešení nebo n správně sestavil celé řešení.

10

Seznam použitých zkratek

Seznam použitých zkratek BH

–

budoucí hodnota

CČ

–

celková částka

CF

–

Cash Flow (hotovostní, peněžní tok)

ČHP

–

časová hodnota peněz

ERR

–

Economical Rate of Return (ekonomické výnosové procento)

EUM

–

efektivní úroková míra

FG

–

finanční gramotnost

IRR

–

Internal Rate of Return (vnitřní výnosové procento)

LT

–

levé tlačítko myši

NPV

–

Net Present Value (čistá současná hodnota)

p.a.

–

per annum (ročně, za rok)

PT

–

pravé tlačítko myši

PV

–

Present Value (současná hodnota)

RPSN –

roční procentní sazba nákladů

RUP

–

reálné úrokové procento

SH

–

současná hodnota

11

Tabulkový kalkulátor MS Excel

1 Tabulkový kalkulátor MS Excel Je pravda, že méně bývá někdy více. U peněz to však neplatí.

1.1

Rekapitulace aplikace Excel

Ve stručnosti si zopakujeme nejdůležitější pravidla a zásady pro používání tabulkového kalkulátoru. V dalších kapitolách je pak budeme bez velkých problémů moci aplikovat. Tabulkový kalkulátor – na toto téma existuje množství rozsáhlé literatury i dostatečné informace na internetu. Jako vše, s čím začínáme, vyžaduje osvojení jeho ovládání spoustu času a trpělivosti. Nutno říci, že obojí v dostatečném množství. Naučit se ho zvládnout a používat je spíše otázka týdnů a měsíců. A samozřejmě i zde platí, že „opakování je matka moudrosti“. Pokud nebudeme aplikaci soustavně používat, lze se domnívat, že naše znalosti se budou neustále zmenšovat. Dále předpokládáme základní znalosti, resp. ovládání PC a operačního systému. Prostředí, ve kterém budeme pracovat, je MS Office, resp. Excel verze 2007. Samozřejmě uvedené postupy a nástroje můžeme použít i ve verzi vyšší, tj. 2010 a další. Pokud by se konkrétní model/situace ve verzích hodně odlišovaly, zmíníme a zdůrazníme toto v textu.

1.2

Pracovní prostředí

Tvůrci pro práci v tabulkovém kalkulátoru připravili pracovní plochu, kterou jistě známe. Zvýrazněny jsou základní prvky. Ve většině případů použití Excelu můžeme dospět k témuž cíli různými cestami. Je zde ponecháno pouze na uživateli, jakou formu si zvolí. Nejjednodušší způsob ovládání aplikace je prostřednictvím myši. Při tomto způsobu si dávejme pozor především na tvar kurzoru, který nás svým tvarem informuje o tom: 1)

co můžeme udělat, bývá zobrazeno „normálně“; naopak to, co nemůžeme, resp. je nám zakázáno, je nevýrazné (šedě podbarvené) a nelze vybrat, označit apod.,

13


2)

k čemu je připraven (kopírování, přesun, zahájení výpočtu atd.).

Obrázek 1-1 Pracovní plocha Excelu

Dále důsledně rozlišujeme levé tlačítko myši (v dalším textu budeme označovat zkratkou LT) a pravé tlačítko (PT). V následující části bychom zdůraznili stručně pravidla, zásady, informace či postupy pro použití aplikace MS Excel.

1.2.1

Úprava pracovního prostředí

Při vlastní práci jistě přivítáme několik následujících doporučení. Každý uživatel si může částečně (v určitých mezích) Excel „přizpůsobit k obrazu svému“, a to doplněním a přemístěním panelu nástrojů Rychlý přístup. Tento panel najdeme vlastně v každé samostatné aplikaci „balíku“ Microsoft Office (Word, Power Point, Acces, Publisher) a slouží k tomu, aby se výrazně zproduktivnila práce v konkrétní aplikaci. Na ni si můžeme umístit libovolné tlačítko z pásu karet Domů / Vložení / Rozložení stránky / Vzorce / Data / Revize / Zobrazení.

14


Obrázek 1-2 Nástroj – panel Rychlý přístup

Panel Rychlý přístup je na obrázku 1-2 umístěn „pod pásem karet“, tedy nejblíže k pracovní ploše. Doporučuji jej takto umístit pro efektivní práci v aplikaci. Jinak ho zpravidla najdeme úplně nahoře vlevo (takto je ve výchozím postavení po instalaci) vedle kulatého tlačítka s logem Tlačítko Office. Nejjednodušeji přemístíme zvolené tlačítko tak, že na něj klikneme PT. Pokud to lze provést, objeví se nám vedle tlačítka okno s volbou Přidat na panel nástrojů Rychlý přístup.

Obrázek 1-3 Přizpůsobit panel Rychlý přístup

15


V opačném případě, nelze-li toto provést nebo konkrétní příkaz nemá tlačítko na příslušné kartě (existuje velká množina příkazů označená jako Příkazy mimo pás karet), si ukážeme obecný postup pro přidání jakéhokoliv tlačítka. Provedeme to takto: Klikneme LT na symbol Přizpůsobit panel nástrojů Rychlý přístup (poslední tlačítko vpravo na této liště) na panelu Rychlý přístup, objeví se dialogové okno s volbami, které vidíme na obrázku 1-3. Nejběžnější příkazy, které jsou zde nabízeny, můžeme zaškrtnout dle vlastního uvážení. Pro další příkazy si zvolíme volbu Další příkazy a v následujícím okně se dostaneme do okna umožňujícího přizpůsobit panel nástrojů Rychlý přístup. Nyní doporučuji v sekci Zvolit příkazy přepnout si záložku na Všechny příkazy. Následně se nám pod touto volbou objeví všechny příkazy, nástroje, tlačítka či funkce sestupně seřazené.

Obrázek 1-4 Úprava panelu Rychlý přístup

16


Nyní klikneme-li dovnitř tohoto okna a zadáme první písmenko, Excel v seznamu zaroluje na příslušný první výskyt příkazu s tímto písmenem. Alternativně můžeme též použít posuvník se šipkou vpravo. Nalezneme-li, co hledáme, klikneme na zvolený příkaz a přidáme jej tlačítkem do pravého seznamu. Ten ukazuje, která tlačítka jsou aktuálně na našem panelu Rychlý přístup. Šipkami vpravo můžeme tlačítka libovolně přesouvat (nahoru či dolů, ve vlastním záhlaví Excelu vlevo nebo vpravo). Jen upozorňuji, že překlad některých tlačítek je zavádějící. Například pro matematickou operaci Násobení se jmenuje odpovídající tlačítko Krát, kulaté závorky jsou označovány jako Otevírací a Zavírací závorka. Na tento pás si doplníme dle vlastního uvážení důležitá tlačítka, která: n používáme velmi často, n jsou uložena na nějaké „spodní“ kartě nebo je obtížnější je vyhledat, n příkazy mimo pás karet (příkazy nejsou na základních kartách), n v případě práce na notebooku se „hůře“ vkládají, n pokud sestavujeme často vzorce, doporučuji si zde umístit i všechny matematické operátory. Panel může například obsahovat následující tlačítka:

Pro snazší umístění na panel použijeme názvy dle jejich uspořádání zleva doprava. Názvy najdeme v pravé části obrázku 1-4. Podle těchto je pak můžeme snáze najít v kategorii Všechny příkazy. Stručně popíši význam či použití několika z nich, které nejsou tak známé či používané, přesto jsou užitečné a mohou nám usnadnit práci. Příkaz/tlačítko: Fotoaparát – jak již název říká, dokáže „ofotit“ určitou pracovní plochu sešitu, kterou jsme schopni vybrat kurzorem, vložit ji jako snímek/obrázek do Excelu. Objekt/obrázek je grafickým objektem „plujícím“ nad stránkou a můžeme s ním pracovat jako s každým jiným obrázkem. Jeho výhodou je to, že je neustále „propojen“ se zdrojem. Provedeme-li nějakou změnu v rozsahu dříve vybraných buněk, promítnou se tyto i do vlastního obrázku. Postup jeho vytvoření je následující:

17


1) 2)

vybereme rozsah toho, co potřebujeme ofotit, klikneme LT na nástroj Fotoaparát, výběr se označí a současně se změní tvar kurzoru, umístíme kurzor na místo v sešitě, kam chceme obrázek vložit, a klikněme LT.

3)

Zobrazit vzorce – tlačítko je velmi užitečné v případě, pokud pracujeme se sešitem, kde jsou čísla-konstanty a současně i vzorce. Na první pohled nejsme schopni rozlišit, co je co, pokud nejsme přímo v konkrétní buňce. Následující obrázek zobrazuje možnou situaci. V uvedeném listu vidíme jen konkrétní čísla. A

B

C

D

2

E 10 000

3 4

1%

2%

3%

4%

5

1

-10 100,00 Kč

-10 200,00 Kč

-10 300,00 Kč

-10 400,00 Kč

6

2

-5 075,12 Kč

-5 150,50 Kč

-5 226,11 Kč

-5 301,96 Kč

7

3

-3 400,22 Kč

-3 467,55 Kč

-3 535,30 Kč

-3 603,49 Kč

8

4

-2 562,81 Kč

-2 626,24 Kč

-2 690,27 Kč

-2 754,90 Kč

9

5

-2 060,40 Kč

-2 121,58 Kč

-2 183,55 Kč

-2 246,27 Kč

Po přepnutí se celý sešit „roztáhne“ a v buňkách se objeví skutečný obsah. Současně se dočasně „odebere“ i nastavené formátování. Po dalším kliknutí na Zobrazit vzorce se vše vrátí do původního stavu. A

B

C

2

D

E

10000

3 4

0,01

0,02

0,03

0,04

5

1

=PLATBA(B$4;$A5;$D$2) =PLATBA(C$4;$A5;$D$2) =PLATBA(D$4;$A5;$D$2) =PLATBA(E$4;$A5;$D$2)

6

2


7

3


8

4


9

5


Tlačítko je umístěno na kartě Vzorce a funguje způsobem zapnuto (podbarveno oranžově, zdůrazněno) / vypnuto (nezdůrazněno).

18


Toto tlačítko může být i jednou z příčin toho, že nám sešit, resp. Excel „nepočítá“. Pokud se nám to stane, je potřeba ho odkliknout čili vypnout. Přepnout okna – máme-li v Excelu otevřeno více souborů, resp. sešitů, je dost obtížné mít o nich přehled a přechod mezi nimi je komplikovaný. Díky tomuto tlačítku je práce s otevřenými soubory snadná.

Kukátko – je užitečný nástroj umožňující si nastavit pohled na detaily, resp. obsahy buněk, které chceme sledovat. Tímto způsobem pak můžeme mít k dispozici potřebné informace pro jednotlivé závislosti ve výpočtech mezi otevřenými sešity. V kukátku máme možnost kliknutím řadit údaje podle jednotlivých sloupců. Strukturu údajů kukátka vidíme na obrázku 1-5. Dokud ho nevymažeme, údaje s detaily máme stále k dispozici. Zvolené údaje kukátka se ukládají do konkrétního sešitu – souboru.

Obrázek 1-5 Údaje kukátka

19


Práce s ním je jednoduchá. Vybereme si buňky, které chceme mít pod kontrolou, a poté klikneme na nástroj Kukátko. Rozsah buněk v kukátku můžeme libovolně měnit – přidávat a odstraňovat. K tomu slouží volba Přidat kukátko nebo po výběru konkrétního záznamu v kukátku se zpřístupní volba Odstranit kukátko. Zobrazované buňky mohou být i z různých sešitů. Obsah kukátka se ukládá do sešitů, které jsou v něm obsaženy, kdykoliv je otevřeme, kukátko nám informace nabídne. Chceme-li přidat několik buněk, podržíme klávesu Ctrl a vybereme buňky, které chceme do kukátka přidat. Stejně tak vybíráme buňky v kukátku pro odstranění, kdy použijeme klávesu Ctrl, chceme-li jich vybrat více, pro souvislý výběr Shift klávesu. Zvláštní kapitolou jsou dvě poslední tlačítka, která dokumentují další způsoby využití panelu Rychlý přístup. Čtyři barevné čtverce umístěné v tlačítku jsou uživatelsky přidány k makru, které bylo zaznamenáno pomocí stisku kláves, zde například zjednodušuje vyvolání vstupního okna pro naplnění nástroje Histrogram. Takto si můžeme upravit spouštění libovolného nástroje v aplikaci Excel. Poslední tlačítko slouží pro vyvolání kalendáře, který je součástí staženého doplňku (Add-in) z internetu (v tomto případě RJ Tools od autora Radka Jurečka; http://www.rjurecek.cz/). Na Internetu je spousta dalších rozšiřujících doplňků aplikace Excel. Jen dáme velký pozor, abychom si do svého PC „nezanesli“ nějakou „havěť“.

1.2.2

Zadávání vzorců

Jedním z nejdůležitějších úkonů při práci v tabulkovém kalkulátoru je tvorba vzorců. Protože je to zásadní činnost, věnujme se tomuto tématu trochu více. Vzorce opět, jako další činnosti, můžeme sestavovat různými způsoby. Aby aplikace (tj. Excel) byla schopna rozpoznat, co má provádět, musí být vzorce zadány jednoznačně tak, aby poté mohl proběhnout vlastní výpočet. Doporučeným znakem pro zápis vzorce je znak = (Rovná se). Ukončení zápisu vzorce potvrdíme klávesou Enter. Na tuto akci zprvu při zadávání vzorce uživatelé zapomínají. Po stisknutí klávesy Enter se provede i kontrola správnosti sestaveného vzorce a kurzor se posune o jednu buňku dolů (implicitně je tento pohyb takto nastaven, ale v možnostech aplikace Excel můžeme nastavit jiný pohyb). Nejčastěji ve vzorci bývá zadána konstanta-číslo nebo adresa buňky (např. G3) a samozřejmě příslušný operátor. Na obrázku vidíme stav před ukončením zápisu vzorce. Sestavený vzorec vynásobí obsah buňky G3

20


hodnotou 1000 a výsledek zobrazí v buňce E3. Adresy buňky zapisovat nemusíme, jednodušší je použít LT myši a kliknout na příslušnou buňku. Tímto dojde k přenosu vlastní adresy (např. E3) do vzorce. Dále si všimněme zobrazení vzorce přímo v buňce, ale i v horním editačním řádku.

Opětovně do vzorce vstoupíme dvojklinutím v konkrétní buňce nebo klávesou F2, „stojíme-li“ na ní. Úpravy vzorce můžeme dle potřeby provádět přímo v buňce nebo v editačním řádku. V dalším vidíme vzorec sestavený v buňce E3, který vynásobí obsah buněk C3 a G3 (zvýrazněno barevným rámečkem kolem každé buňky).

Při sestavování složitějších vzorců musíme dle logiky výpočtu použít kulaté závorky pro správné oddělení jednotlivých členů, a tím zajistit to, aby výpočet proběhl správně. Kromě toho musíme dodržet i základní pravidlo závorek – že počet levých a pravých závorek v buňce musí být stejný, jinak nám Excel oznámí chybu, my ji musíme opravit a aplikace nám nedovolí výpočet ukončit.

21


Jestliže se ve vzorci „ztratíme“ a potřebujeme ho opustit, použijeme klávesu Esc (Escape – návrat zpět bez uložení změn). Rozdílné výsledky stejných členů (čísel) a operátorů jinak opatřených závorkami vidíme na obrázku. I když pro ilustraci je příklad velmi jednoduchý, vzorce dávají různé výsledky. Proto je potřeba dávat velký pozor na vkládání závorek při sestavování vzorců. Vzorec

Výsledek

=1/2+2*3

=

6,5

=1/(2+2)*3

=

0,75

=1/(2+2*3)

=

0,125

=1/2+(2*3)

=

6,5

Uvedeme si ale složitější zápis a jeho přepis do správné podoby pro výpočet. Do buněk B3 a B4 zadáme hodnoty 250 a 985 jako veličiny A a B. Nyní si zkusíme sami vytvořit vzorec pro výpočet zlomku ve tvaru: B 2 ( A + B) - 2

4

Pro operaci umocnění použijeme symbol ^ („stříška“, vložíme na anglické klávesnici Shift+6 nebo přes menu Vložit/Symbol). Různé vzorce (dobré E sloupec i špatné D sloupec), které nám mohou vzniknout, pak vidíme zde: B 2

C

D

E

Špatně

Dobře

3

250

983,00

0,00064581

4

985

=B4/(B3+B4)*(B3+B4)-2

=B4/((B3+B4)*(B3+B4)-2)

5

-1,99935

0,00064581

6

=B4/((B3+B4)^2)-2

=B4/((B3+B4)^2-2)

7

-1,99935

0,00064581

8

=B4/(B3+B4)^2-2

=(B4/((B3+B4)*(B3+B4)-2))

9

0,00016

10

=B3/((B3+B4)^2-2)

11

0,00016

12

=B3/((B3+B4)^2-2)

13

-1,99899

14

=B4/(B3+B4^2)-2

22

Tabulkový kalkulátor MS Excel B

C

D

E

15

0,00101

16

=B4/((B3+B4^2)-2)

17

Správný vzorec zapsaný do buňky musí odpovídat zápisu B/((A+B)^2-2. Poznámka: Místo klávesy Enter můžeme pro ukončení zápisu použít i tlačítko „fajfku“ (ikona Zadat P), která je aktivní pouze v čase, kdy sestavujeme příslušný vzorec. Má v sobě zapojenu i následující funkčnost: 1) 2) 3)

je-li třeba, přidá na konec vzorce závorku (pravou, ukončovací), neposune kurzor o jednu buňku dolů, ukončí zadání vzorce (stejně jako klávesa Enter).

Tím nám „ušetří“ spoustu času oproti standardně používané klávese Enter. Doporučuji vyzkoušet a používat. Zvláště při práci s notebookem, ale i při sestavování složitých vzorců obsahujících funkci (funkce), jistě rádi toto tlačítko přivítáme.

1.2.3

Kopírování a přesun vzorců

Další důležitou zásadou v tabulkovém kalkulátoru je správně sestavit potřebný vzorec a pak ho nejjednodušeji „duplikovat“ (nejčastěji ho zkopírovat popotažením) do dalších buněk. Proto si musíme velmi detailně vysvětlit postup zadávání vzorců a jejich úpravy tak, aby zajišťovaly efektivně to, co potřebujeme zjistit, vypočítat. Ve většině případů si tímto postupem ušetříme spousty času a práce.

23


Sestavíme-li v listu třeba do buňky F4 jednoduchý vzorec (např. =B2+D2) a poté ho zkopírujeme o 6 buněk svisle (kurzor nastavíme na sestavený vzorec, použijeme Ctrl+C /kopírování/, klikneme LT na buňku F10 a použijeme Ctrl+V /vložení/), změní se původní vzorec na nový ve tvaru =B8+D8. O kolik jsme posunuli vzorec, o tolik se posunou a změní adresy vstupních buněk. Tato funkčnost je stejně tak zachována i ve směru vodorovném.

Podle uspořádání dat a způsobu výpočtu je tato vlastnost v některých případech vhodná, ale v jiných nikoliv. Potřebujeme-li „zabránit“ aplikaci v posouvání (změnám ve vzorci, který kopírujeme), pak vložíme do výchozího vzorce znak (y) $. Tuto operaci nazveme jako „zafixování“. Odkazy na původní buňky pak zůstanou vždy zachovány při jakékoliv akci kopírování takto doplněného vzorce. Znak (y) $ a jeho pozice je řízena následujícím pravidlem. Protože pracujeme v dvojrozměrném sešitu (směr pohybu při kopírování – svisle nebo vodorovně), můžeme zafixovat jeden (svislý) či druhý směr (vodorovný) nebo oba. Znaky vkládáme do příslušného vzorce klávesou F4 jejím opakovaným stisknutím. Kurzor ve vzorci nastavíme nejlépe mezi písmenko a číslici. Počet stisknutí (sloupec F4 klávesa) a odpovídající význam je patrný v následující tabulce. Vzorec

F4 klávesa

Zafixování směru:

=A3

–

není

=$A$3

1x

obou

=A$3

2x

vodorovného (řádek)

=$A3

3x

svislého (sloupec)

=A3

4x

výchozí, první stav

24


Tento způsob použití znaku $ se v literatuře označuje jako absolutní a relativní adresování. Pokud chceme efektivně pracovat s aplikací Excel, vyzkoušíme si, otestujeme si, ověříme si a naučíme se tuto zásadu, resp. pravidlo správně používat. K tomu by nám měl posloužit i následující příklad. Tento princip pak můžeme samozřejmě používat u všech dalších obdobných příkladů. Současně si ukážeme i druhý způsob kopírování vzorců, a to prostřednictvím kurzoru myši. Máme tabulku dvou veličin, mezi kterými existuje konkrétní vztah, potřebujeme propočíst vždy údaje třetí veličiny v průsečíku (kříži, proto se někdy tato tabulka označuje jako „křížová“), kde vstupují do výpočtu vždy hodnoty veličiny A a veličiny B.

Při sestavování použijeme, jak už bylo výše zmíněno, i druhý způsob kopírování. Tento výhodně použijeme tehdy, když kopírujeme souvislou oblast a současně přitom chceme (ale nemusíme) vytvořit rostoucí či klesající sérii hodnot. Jinak řečeno vytváříme číselné „řady“. Vzpomeneme-li na matematiku, mluvíme pak např. o aritmetické řadě, geometrické apod. Kopírování – rozvoj řady provedeme následujícím jednoduchým způsobem. U buněk, jsou-li vybrány (označeny) kliknutím, se vpravo dole objeví černý hranatý bod. K tomuto se nyní přiblížíme kurzorem (nejlépe odspodu zprava), až se jeho tvar změní na „nitkový kříž“ tak, jak je zobrazeno na obrázcích. Nyní použijeme LT, klikneme (stále držíme prst na LT) a táhneme příslušným směrem, kterým chceme řadu provést (až nyní pustíme LT).

25


Při sestavení rostoucí (klesající) řady vložíme do sousedních buněk (tak, jak je vidět na obrázku) potřebné údaje (zde 20 a 30), vybereme a popotažením řadu sestavíme.

Excel si zjistí rozdíl prvého a druhé členu (v našem příkladu 30 – 20 = 10, jinak řečeno diferenci, kvocient, v řeči Excelu krok) a o tento pak navyšuje další členy. Takto „šikovně“ můžeme sestavovat libovolné řady hodnot. Při tažení kopírované buňky vidíme vedle kurzoru online i hodnoty, které budou do buněk doplněny. Vybereme-li pouze jednu buňku s hodnotou, pak vytvoříme řadu konstantních hodnot (10,10,10, …). Obdobným způsobem můžeme řadu vytvořit i v druhém rozměru, tj. vodorovně. Pro konkrétní příklad sestavení tabulky s výpočty použijeme z fyziky veličiny dráha (S), čas (T) a rychlost (V); mezi nimi dle logiky existuje operace násobení nebo dělení. Připravíme si údaje pro záhlaví tabulky. Do buňky C3 napíšeme 200 a do D3 hodnotu 250 a následně je obě vybereme. Nyní popotažením zkopírujeme řadu doprava. Celou dobu držíme LT, až se nám v pomocném oknu objeví požadovaný údaj. Zde například 500. Do buněk B4 a B5 zadáme rychlost v km/hod 40 a 45, obdobným způsobem zkopírujeme řadu až do hodnoty 100.

26


Nyní sestavíme v C4 správně vzorec tak, abychom ho mohli pouhým zkopírováním doplnit do jednotlivých průsečíků uvnitř tabulky. Výpočty (vzorce) se vždy musí odkazovat na vstupní řádek číslo 3 (číselné údaje v záhlaví – dráha) a vstupní sloupec B (číselné údaje vlevo – rychlost). Abychom této úpravy dosáhli, musíme ve vzorci „zafixovat“ to, co má zůstat řídící a na co se mají vzorce uvnitř tabulky odkazovat. Vzorec =C3/B4 (tj. dráha/rychlost) tedy upravíme klávesou F4 na =C$3/$B4. Vzorec se musí vždy odkazovat na záhlaví tabulky, tj. řádek č. 3, proto vložíme znak $ před číslici tři a současně se musí jmenovatel odkazovat na levé okrajové údaje, které jsou pro celý obsah tabulky uloženy ve sloupci $B. Nyní vybereme buňku C4 a zkopírujeme vložený vzorec do celého obsahu tabulky. Kopírování provádíme ve dvou krocích svisle a poté vodorovně (Excel neumí kopírovat údaje po diagonále). Směry můžeme samozřejmě prohodit. V druhém kroku pak kopírujeme více buněk (vlastně je to vybraný vektor C4:C16 nebo C4:I4, dle toho, jaký směr jsme zvolili v prvém kroku). Využijeme dále i toho, že máme po skončení kopírování vybrán celý vnitřní obsah tabulky, tj. všechny vzorce v rozsahu C4:I16, a naformátujeme je dle vlastního uvážení (např. jako číslo s 2 desetinnými místy). Výsledná tabulka může vypadat takto: A

B

C

D

E

F

G

H

I

1 2

Dráha v km

3 4 5

Rychlost v km/hod

200

250

300

350

400

450

500

40

5,00

6,25

7,50

8,75

10,00

11,25

12,50

45

4,44

5,56

6,67

7,78

8,89

10,00

11,11

6

50

4,00

5,00

6,00

7,00

8,00

9,00

10,00

7

55

3,64

4,55

5,45

6,36

7,27

8,18

9,09

8

60

3,33

4,17

5,00

5,83

6,67

7,50

8,33

9

65

3,08

3,85

4,62

5,38

6,15

6,92

7,69

10

70

2,86

3,57

4,29

5,00

5,71

6,43

7,14

11

75

2,67

3,33

4,00

4,67

5,33

6,00

6,67

12

80

2,50

3,13

3,75

4,38

5,00

5,63

6,25

13

85

2,35

2,94

3,53

4,12

4,71

5,29

5,88

14

90

2,22

2,78

3,33

3,89

4,44

5,00

5,56

15

95

2,11

2,63

3,16

3,68

4,21

4,74

5,26

16

100

2,00

2,50

3,00

3,50

4,00

4,50

5,00

Obrázek 1-6 Křížová tabulka

27


Tabulka je dvojrozměrná (2D), zjišťuje třetí veličinu ze dvou vstupních. Pokud potřebujeme rozsah formátu 3D, pak použijeme pro třetí rozměr jednotlivé listy sešitu a výpočty provedeme „skrze“ tyto.

! A

B

2

B2

C

D3 C4

5

C5 Veličina B

4

7

E

F

G

H

G3

H3

Veličina A

3

6

D

E3

F3

C6 C7

8

C8

9

C9

Tento detailně popsaný postup využíváme u většiny tabulek pro výpočet třetí veličiny ze dvou řad hodnot logicky „svázaných“ a závislých veličin. V uvedeném příkladu čas, dráha a rychlost. Ukážeme si další postup sestavení výchozího vzorce krok za krokem v případě, kdy ke dvěma veličinám přibude třetí ovlivňující každou buňku uvnitř tabulky. Máme dvě veličiny např. A a B, mezi kterými existuje určitá závislost. Jejich dílčí hodnoty jsou uloženy do buněk D3 až H3 (veličina A) a C4 až C9 (veličina B). K těmto ještě máme konstantu, která je zadána do buňky B2 a souvisí s oběma. Pro výpočet hodnot v tabulce je nejdůležitější sestavit správný vzorec do buňky D4, což je vlastně první počítaná hodnota v naší tabulce. Podíváme-li se na „logiku“ vzorců v tabulce, vidíme, že se mají odkazovat vždy na řádek třetí, tj. ve směru vodorovném a sloupec C ve svislém směru. Vzniklý vzorec musí tyto dvě vazby zahrnovat. Jednoduše sestavíme základní vzorec jako =D3*C4. Nyní si představíme, jak se bude dále vzorec kopírovat směrem doprava a dolů a na jaký řádek a sloupec se budou vzorce uvnitř tabulky opakovaně odkazovat. Tak, jak jsme již řekli, je to třetí řádek (číslice v uvedených buňkách je vždy tři) a sloupec C (opět v adresách zůstává písmeno C). Proto vzorec upravíme prostřednictvím klávesy F4 do tvaru =D$3*$C4. Tím jsme „zafixovali“ odkazy na třetí řádek (to zajišťuje znak $ v prvním členu). Druhý znak $ v členu $C4 „zafixuje“ sloupec C. Do výsledného vzorce vložíme ještě vazbu na konstantu

28

Tabulkový kalkulátor MS Excel A

B

2

B2

C

C4

5

C5 Veličina B

4

7

E

D3

E3

F

G

H

F3

G3

H3

Veličina A

3

6

D

=D$3*$C4*$B$2

C6 C7

8

C8

9

C9

zadanou do buňky B2, kterou se má vynásobit každý člen v tabulce. Zadáme znak pro násobení a klikneme na adresu buňky B2. Tím se sestavovaný vzorec změnil na =D$3*$C4*B2. Adresu konstanty „ošetříme“ tak, aby se odkaz na ni nikdy neměnil. Klávesou F4 vložíme dva znaky do adresy buňky. Výsledný tvar vzorce je uveden na obrázku. Potvrdíme ukončení zadávání vzorce tlačítkem Zadat P nebo klávesou Enter. Nyní již snadno vzorec rozkopírujeme svisle a pak vodorovně do celé tabulky. Jak mají vypadat výsledné vzorce uvnitř tabulky, vidíme na obrázku. A

B

2

B2

C

D

E

F

G

H

G3

H3

Veličina A

3

D3

E3

F3

C4

=D$3*$C4*$B$2 =E$3*$C4*$B$2 =F$3*$C4*$B$2 =G$3*$C4*$B$2 =H$3*$C4*$B$2

5

C5


C6


C7


8

C8


9

C9


6 7

Veličina B

4

Další základní operací se sestaveným vzorcem je jeho přesun, který opět můžeme provést různými způsoby, např. použitím kláves Ctrl+X na buňce se vzorcem a Ctrl+V tam, kam ho chceme přemístit.

29


Jiný způsob přesunu vzorce je použití kurzoru. Při tomto musíme změnit tvar kurzoru na nový, který je uveden na obrázku (dvě oboustranné šipky v kříži společně s bílou šipkou).

Když se přiblížíme k černému a zvýrazněnému okraji vybrané buňky (vybraných buněk), dojde ke změně tvaru kurzoru. Poté klikneme LT, podržíme ho a přesuneme na novou buňku. Jsme-li na buňce se vzorcem, můžeme přesun realizovat pomocí místní kontextové nápovědy přes PT (příkaz Vyjmout, přemístit se kurzorem na novou buňku a poté Vložit). Důležité je si uvědomit, že u přesunu vzorce se adresy v něm obsažené nemění, takže odkazy na buňky zůstávají pořád stejné – neměnné. Pokud někdy potřebujeme sestavený vzorec zobrazit jako text, můžeme to provést tak, že na prvé místo (ještě před znak =) vložíme mezerníkem mezeru (v textu budeme vyznačovat znakem „_“). Vzorec =A3*B3 tímto způsobem změníme na _=A3*B3. Tak se ze vzorce stane text a výpočet samozřejmě neprobíhá. Podle prvého znaku v buňce Excel rozhoduje, jak bude s obsahem buňky následně pracovat. Takto můžeme vždy postupovat, je-li třeba zkopírovat vzorec beze změn v adresách buněk. Opětovnou aktivaci výpočtů v buňce podle sestaveného vzorce zajistíme odebráním prvého znaku, tj. mezery.

1.2.4

Tvorba řad v Excelu

Potřebujeme-li vytvořit souvislou řadu hodnot (z pohledu matematiky hovoříme o posloupnosti), nabízí se v Excelu několik možných způsobů jejich sestavení. Uvedeme čtyři nejjednodušší možné případy: 1)

30

Prostřednictvím LT, postup je uveden v předchozí části. Do sousedních buněk zapíšeme odpovídající hodnoty dvou sousedních členů. Následně obě buňky vybereme a LT „popotažením“ řadu rozvineme – zkopírujeme. Tvar kurzoru musí být „nitkový zaměřovač“, černý malý křížek. Běžně tvoříme řadu směrem doprava pohybem kurzoru myši, ale existuje možnost sestavit uvedeným způsobem řadu i ve směru „doleva“. Pak její trend bude opačný.


2)

Na kartě Domů/Výplň/Řady je připraven komplexní nástroj pro tvorbu řad. Zde si zvolíme „parametry“ dané řady (posloupnosti) a Excel potřebné údaje dopočítá.

3)

Použitím PT napíšeme pouze první člen řady, vybereme jej a opět „popotažením“ zkopírujeme. Objeví se nám stejné okno pro tvorbu řad jako v případě 2.

4)

Další možností je sestavení „své“ vlastní řady prostřednictvím odpovídajícího vzorce. Ukázku obsahuje tabulka:

3 4

B

C

Hodnota

Vzorec 11

5

16,5 =B4+B4/2

6

24,75 =B5+B5/2

7

37,125 =B6+B6/2

8

55,6875 =B7+B7/2

9

83,53125 =B8+B8/2

10

125,2969 =B9+B9/2

11

187,9453 =B10+B10/2

12

281,918 =B11+B11/2

Samostatnou kapitolou je sestavování řad časových (ročních, měsíčních, denních atd.), které je postaveno na stejných základech jako řady číselné.

1.2.5

Zadávání – používání funkcí Excelu

Velmi rozsáhlým a výkonným aparátem v Excelu jsou předdefinované – naprogramované vzorce určené pro snazší a rychlejší práci v tabulkovém kalkulátoru. Tvůrci je naprogramovali a připravili do uživatelsky příjemné podoby a současně „ošetřili“ i spoustu dalších návazných vazeb. Například při použití fce Součet jsou přeskočeny buňky obsahující text, a do výpočtu tudíž nezasahují, a proto je nemusíme z vybraného pole nějakým způsobem vylučovat. Pokud bychom tento výpočet provedli standardně sestaveným vzorcem (např. =A1+A2+B1+B2), ve kterém by jeden ze sčítanců obsahoval text, tak výsledkem výpočtu bude oznámení #HODNOTA!, které takto indikuje chybu.

31


Výpočty vlastních příkladů tedy můžeme provádět několika způsoby: n vzorcem – přepíšeme matematický zápis problému do podoby vhodné pro výpočet v Excelu, n funkcí – zvolíme podle potřeby odpovídající připravenou funkci, existuje ale i možnost v případě potřeby si vlastní požadovanou funkci sestavit a uložit mezi ostatní, n kombinací obou předchozích možností. Nejčastější způsob využití aplikace Excel ve výpočtech představuje třetí alternativa – viz názorný příklad z oblasti matematiky: Pro řešení kvadratické rovnice: ax 2 + bx + c = 0 používáme postup výpočtem pomocí diskriminantu, který je definován vztahem: D = b 2 - 4ac Následně dosadíme do vztahu pro výpočet kořenů rovnice x1 a x2: x1, 2 =

-b ± b 2 - 4ac 2a

Konkrétně máme kvadratickou rovnici: x 2 + 5x - 14 = 0 1

B

C

2

a=

1

3

b=

5

4

c=

-14

D

E

5 6

Diskriminant

7

81

81

8

=C3^2-4*C2*C4

=POWER(C3;2)-4*C2*C4

9 10

x1 =

2

=KDYŽ(D7>0;(-C3+ODMOCNINA(D7))/(2*C2);"")

11

x2 =

7

=KDYŽ(D7>0;(C3+ODMOCNINA(D7))/(2*C2);"")

32


V příkladu je proveden výpočet diskriminantu prostřednictvím jen matematického vzorce v buňce D7, v buňce E7 je pro porovnání výpočet s využitím připravené funkce POWER (mocnina). V C10 a C11 jsou zjištěny kořeny rovnice x1 a x2. Výpočet proběhne pouze za té podmínky, že diskriminant je větší než nula. Aplikace funkcí (celkem asi 331, po základní instalaci aplikace) je proto velmi jednoduchá, přehledná, rychlá a pokrývá velkou množinu úloh a příkladů z různých oborů. Užívají se samostatně anebo v různě složitých vlastních vzorcích. V některých případech bychom bez nich nebyli schopni konkrétní výpočet provést nebo by to bylo velmi komplikované. Časté je i jejich opakované vkládání do jednoho vzorce (vnořování). Částečně jejich použití komplikuje to, že názvy některých jsou lokalizovány (= přeloženy do češtiny, např. Součet) a některé nikoliv (funkce Rank). Na to je třeba dávat pozor hlavně tehdy, používáme-li jinou jazykovou mutaci MS Office. V příloze č. 3 je uveden přehled funkcí s českým i anglickým názvem pro oblast finančních výpočtů. Nejfrekventovanější funkce jsou přímo vloženy do karty Domů/Automatické shrnutí, kde jsou připraveny funkce pro Součet, Průměr, Počty, Minimum, Maximum a přechod do okna Vložit funkci volbou Další funkce. O jejich důležitosti svědčí i to, že je jim vyhrazena jedna ze základních karet – karta Vzorce. Při jejich zadávání je nutno se řídit určitými pravidly (souladu = konzistence) tak, aby je Excel správně „pochopil“ a mohly nám bez problémů sloužit. Struktura funkce začíná znaménkem rovná se (=), za nímž následuje název funkce, otevírací závorka, argumenty funkce oddělené středníky a uzavírací závorka. Jejich „vyvolání“ a následně práce s nimi může být prováděna různými postupy. Uvedeme zhruba tři základní. Nechť si každý uživatel zvolí ten způsob, který mu nejlépe vyhovuje. V některých případech je efektivní kombinace mezi těmito způsoby zadávání. Vkládání funkcí do buňky: 1)

Shift+F3 – otevře se dialogové okno Vložit funkci. Zde si zvolíme kategorii (Naposledy použité – zobrazeno 10 posledně používaných funkcí, Vše – zde jsou řazeny abecedně, a další ostatní kategorie) a pak vlastní funkci. Vybereme první – zde například funkce Když. Po potvrzení OK se dostaneme do dalšího „průvodce“, který nám již pomůže správně funkci naplnit.

33


34


Při tomto způsobu doplňujeme argumenty (povinná pole) funkce především v průvodci; v buňce sešitu tak vlastně nic nezadáváme. U každého pole – argumentu funkce v průvodci je ve spodní části uvedena stručná nápověda vysvětlující konkrétní argument. Úplně vlevo dole je pak znázorněn online výsledek funkce. Kontrolu výsledku tedy můžeme provést, aniž bychom ukončovali tohoto průvodce funkcí. V průvodci se také ihned indikuje nesprávný údaj červenou barvou, a je tak na první pohled jasno, kde je nějaký problém a čemu je tak třeba se věnovat. Při tomto způsobu zadávání funkce Excel za nás doplňuje všechny povinné závorky a oddělovače, nezadáváme tak ani do buňky první znak uvozující funkci „=“. Pokud potřebujeme u konkrétní funkce pomoci, klikneme na Nápověda k této funkci. Základní struktura funkce: = Název funkce (argument1;argument2) Konkrétně například: =RANK(číslo;odkaz;[pořadí]) nebo =PLATBA(sazba;pper;souč hod;[bud hod];[typ]) Argumenty uváděné v hranatých závorkách jsou nepovinné. Oddělovačem argumentů je vždy středník „;“. Mezery ve funkcích žádné nejsou. 2)

Karta Vzorce – zde jsou funkce rozděleny do několika kategorií a pomocí nich je můžeme vyvolat a vložit.

3)

Postupným zadáním podle názvu konkrétní funkce. Napíšeme znak „=“ a písmenko, kterým funkce začíná.

Popíšeme si tento postup krok za krokem na méně známé funkci Rank. Tuto můžeme dobře využít k vyhodnocení pořadí konkrétní hodnoty. Máme tři studenty, kteří házeli kriketovým míčkem do dálky. Pro jednoduchost volíme malý počet záznamů. Máme zjistit jejich pořadí, když dosáhli následujících výsledků: Jméno

Délka hodu

Ota

16

Pavel

22

Jan

13

Pořadí

35


Jméno zadáme do buňky B4 Ota, do B5 Pavel a do B6 Jan. Sestavíme první vzorec v buňce D4. Zadáme znak = a písmeno r. Excel nám do buňky zobrazí všechny funkce začínající písmenem r.

Zde si zvolíme RANK (jedním kliknutím LT). Vedle každé funkce se pak objevuje stručná nápověda (u RANK například „Vrátí pořadí čísla v seznamu; jeho relativní velikost vzhledem k hodnotám v seznamu“). Dvakrát klikneme LT na RANK, funkce se převede do buňky, vše ostatní zmizí.

Pod funkcí se zobrazí názvy argumentů, dle nichž můžeme doplnění provést. Nyní klikneme LT do buňky C4, vložíme„;“ a vybereme vektor C4 až C6. Do vzorce se přemístí zápis C4:C6. Protože chceme používání zefektivnit (pravidlo jeden vzorec pro vše), buňky „zafixujeme“ klávesou F4 ($C$4:$C$6). Zkontrolujeme, zda skutečně došlo k vložení čtyř znaků $ do adres buněk ohraničujících konkrétní vektor. Pokud bychom to neprovedli, bude se při kopírování sestaveného vzorce vektor hodnot posouvat a funkce bude vyhodnocovat nesprávné hodnoty.

36


Nyní ukončíme zápis argumentů a doplníme pravou (ukončovací) závorku. Taktéž můžeme místo zadání závorky použít i tlačítko Zadat v řádku vzorců (v editačním řádku, symbol P). Tímto jsme dokončili zadání vzorce a můžeme jej zkopírovat pro vyhodnocení pořadí všech účastníků. Sestavené vzorce s výsledky a funkcí RANK vidíme zde: 1

B

C

D

E

3

Jméno

Délka hodu

Pořadí

Použitý vzorec (sl.D)

4

Ota

16

2

=RANK(C4;$C$4:$C$6)

5

Pavel

22

1

=RANK(C5;$C$4:$C$6)

6

Jan

13

3

=RANK(C6;$C$4:$C$6)

2

Sestavená tabulka s funkcí Rank má další výhodu. Totiž každá změna ve velikosti hodu (obecně ukazatele, podle kterého účastníky hodnotíme) se opětovně vyhodnocuje a pořadí se mění, pokud tomu čísla odpovídají. Jednoduchý příklad o třech záznamech byl vybrán úmyslně pro přehledné vysvětlení a sestavení příkladu. Pořadí je zde na první pohled zřejmé, ale pokud pořádáme závod, kde se přihlásí například 50 účastníků, vyhodnocení pořadí nebude již tak snadné. A ještě jedno použití v souvislosti s pořadím může mít tabulka s funkcí Rank. Umí vyhodnocovat i průběžné pořadí, třeba v případě orientačního závodu. Doporučuji předem, pokud to lze zajistit, sestavit si potřebnou tabulku s konkrétními údaji i vzorci a poté jen doplňovat dosahované údaje. Vyhodnocení pořadí může mít jako v tomto případě povahu „Maximalizační“ (čím delší hod, tím lepší pořadí) nebo „Minimalizační“ (u orientačního běhu, čím kratší dosažený čas, tím lepší umístění). V uvedené funkci je proto možné zohlednit tento pohled třetím údajem, a to:

37


n 0 nebo nevyplněno – max., resp. sestupné pořadí, n 1 – min., resp. vzestupné pořadí. U této funkce je třeba si dát „pozor“ jednak na „zafixování“ sloupce nebo řádku, resp. vektoru, kde jsou všechny údaje, a jednak i na to, aby konkrétní hodnocená hodnota byla skutečně z vektoru všech údajů. Pokud sestavíme tuto funkci nesprávně, viz přiložený obrázek, zobrazí aplikace hlášení #N/A, neboť nemůžeme hodnotit údaj mimo vektor B2:B6.

Obrázek 1-7 Chybové hlášení #N/A

Kromě tohoto upozornění jsou v Excelu i další, na která můžeme narazit. Jestliže vzorec nemůže řádně vyhodnotit výsledek, zobrazí se chybová hodnota. Každý typ chyby má jiné příčiny a jiná řešení. Jejich výčet se stručným popisem je v následující tabulce: #DIV/0!

Chyba se zobrazí, jestliže dojde k dělení nulou (0).

#N/A

Chyba se zobrazí, jestliže hodnota není pro vzorec nebo funkci k dispozici.

#NÁZEV?

Tato chyba se zobrazí v případě, že aplikace Microsoft Excel nerozpozná text ve vzorci.

#NULL!

Tato chyba vznikne při zadání průniku dvou oblastí, které se nepřekrývají. Operátorem průniku je mezera mezi odkazy.

#NUM!

Chyba se zobrazí u neplatných číselných hodnot ve vzorci nebo funkci.

#REF!

K této chybě dochází, pokud není odkaz – adresa na buňku (v průniku sloupce B a řádku 3 je B3) platný.

38

Tabulkový kalkulátor MS Excel #HODNOTA!

Chyba se zobrazí při použití chybného typu argumentu (Argument: Hodnoty používané ve funkci k provádění operací nebo výpočtů. Typ používaného argumentu je specifický pro danou funkci. Mezi běžné argumenty, které se používají ve funkcích, patří čísla, text, odkazy na buňky a názvy.) nebo operandu (Operand: Položky ve vzorci na jedné nebo druhé straně operátoru. V aplikaci Excel mohou být operandy hodnoty, odkazy na buňky, názvy, popisky a funkce.).

#####

Tato chyba vznikne v případě, že sloupec není dostatečně široký nebo je použito záporné datum či čas.

Zastavíme se i u druhé již vzpomínané funkce Když. Patří mezi logické funkce nejčastěji používané. Dokáže vyhodnotit logickou podmínku a dle toho provést akci ANO nebo akci NE. Obrazně řečeno pracuje jako výhybka na železnici. Umožňuje „rozvětvit“ postup výpočtu, ale pokud nastane alternativa ANO, nemůže nastat NE. =KDYŽ (Podmínka; ANO; NE)

Na jednoduchém příkladu si ukážeme její použití. Potřebujeme zjistit, zda je v buňce B3 hodnota kladná nebo záporná. Sestavíme proto do C3 vzorec s funkcí Když. V C3 vložíme znaménko = a písmenko k, zvolíme z nabídky funkci KDYŽ.

39


Nyní můžeme postupovat stejně jako u běžného zadávání funkce. Tento postup byl popsán na straně nebo si volbou Shift+F3 vyvoláme průvodce. Průvodce se „hodí“ především tehdy, když začínáme používat novou funkci a nejsme si jisti argumenty pro správné vyplnění, nebo v případě složitějších funkcí. Klikneme do pole Podmínka a sestavíme podmínku. Klikneme na buňku, kterou chceme vyhodnocovat B3, a vložíme < 0 (znak < vložíme nejjednodušeji prostřednictvím pravý Alt + <). Do pole Ano doplníme text Hodnota je záporná a do pole Ne Hodnota je kladná. Potvrdíme OK. Doporučuji si vyzkoušet i druhou alternativu sestavování vzorce. U tohoto postupu však musíme všechny znaky nutné k sestavení funkce zadat z klávesnice. Stejně tak vlastní texty musíme označit „“. V obou případech pak dospějeme k výslednému vzorci, jehož správný zápis je následující: =KDYŽ(B3<0;"Hodnota je záporná";"Hodnota je kladná") Vzorec s funkcí KDYŽ v buňce C3 zajišťuje vyhodnocení zadané hodnoty do buňky C3 a informování uživatele konkrétním textem. Z naznačeného postupu, jak je vidět, vyplývá i další důležité pravidlo, že texty, které funkce „volá“, se zadávají do „“. Excel pozná textovou část a pracuje s ní skutečně jako s textem. Samozřejmě toto pravidlo se týká všech funkcí a sestavovaných vzorců.

40


Je-li třeba hodnotit tři alternativy (možnosti), musíme „vnořit“ dvě funkce KDYŽ do jednoho vzorce. Obecně je možno říci, že ve výpočtech lze kombinovat několik různých funkcí. Náš příklad můžeme rozšířit na další třetí stav, kdy zjišťujeme, zdali se v buňce nachází 0. Do původního vzorce doplníme za první argument část výrazu KDYŽ(B3=0;"Hodnota se rovná 0";. Výsledný vzorec je pak následující: =KDYŽ(B3<0;"Hodnota je záporná";KDYŽ(B3=0;"Hodnota se rovná 0";"Hodnota je kladná")) Obdobně je možno použít dvě funkce KDYŽ na testování diskriminantu, který může nabývat třech stavů, a to <, = nebo > 0. Podle výsledku pak můžeme následně použít tři různé způsoby jeho výpočtu.

1.2.6

Nástroj Ověření dat

Mezi další užitečné nástroje pro usnadnění pořizování a kontrolu vkládaných dat (validace dat) patří nástroj Ověření dat umístěný na kartě Data. Je výhodné ho použít tehdy, když: n opakovaně vkládáme určitou množinu údajů, n potřebujeme při vkládání provést kontrolu správnosti dat, n chceme umožnit vkládání údajů výběrem z předem stanovených hodnot, n při vkládání potřebujeme uživateli sdělit upřesňující informace.

41


Pro vložení slouží dialogové okno se třemi záložkami, jejichž vyplněním nástroj připravíme a aktivujeme. Zadáme si údaje, ze kterých bude uživatel vybírat právě jeden. Zvolíme buňku (vstupní buňka D3) nebo oblast buněk, pro kterou chceme Ověření dat nastavit. Vyplníme záložku nastavení. Zde vybereme „charakter“, resp. typ pravidla, které má být použito při pořizování dat. Zvolíme nejčastěji používané pravidlo Seznam.

Ostatní volby ve výběru se nastavují obdobným způsobem. Dále určíme rozsah, z jaké množiny hodnot bude uživatel vybírat. Tento může být naplněn dvěma způsoby tak, že napíšeme údaje oddělené středníkem přímo do pole Rozsah (v případě malého počtu dat) nebo je vybereme jako oblast buněk v listu. V našem příkladě tedy buňky B3:B5. Na další záložce vyplníme dle potřeby Zprávu při zadávání a Chybové hlášení. Nastavení třetí záložky může mít v případě nesprávného údaje charakter: n Stop – zabránění vložení konkrétní položky. n Varování – informování, ale uživatel rozhodne o následující akci (ano či ne). n Informace – pouze informování uživatele. Po potvrzení v buňce D3 budeme mít tento nástroj nastaven. Rozsah omezení můžeme zadat i pomocí předem definovaného názvu (např. Města) v listu sešitu. Pak do pole Rozsah zadáme výraz =Města. Tímto způsobem mohou být údaje uloženy i na jiném listu sešitu. Jinak musí být vstupní data na stejném listu sešitu, což někdy komplikuje použití.

42


Jedno upozornění na závěr pro použití. Tento nástroj je funkční (kontroluje vstupní údaje) jen tehdy, pokud jsou data vkládána psaním z klávesnice, ne při kopírování prostřednictvím Schránky.

1.2.7

Podmíněné formátování

43


Umožňuje nastavit různá pravidla pro změnu formátu buňky (buněk) podle vložené hodnoty nebo v závislosti na hodnotě v jiné buňce určené konkrétním vzorcem. Sestavíme si demonstrační příklad nastavení podmíněného formátování pro buňku, do které zadáme hodnotu, a budeme chtít barevně rozlišit hodnoty kladné (modrou), nulu (žlutou) a záporné (červenou). Nástroj je na kartě Domů. Zde můžeme využít velkého množství přednastavených způsobů formátování nebo ve spodní části máme možnost si uživatelsky nadefinovat svá vlastní. Klikneme postupně na Domů / Podmíněné formátování / Nové pravidlo. Zde zvolíme Nové pravidlo formátování / Formátovat buňky pouze obsahující a doplníme postupně podmínky pro tři stavy buňky. Výsledek by pak měl například pro buňku F4 vypadat takto:

Kromě klasického zvýraznění buňky (výplň, ohraničení, písmo) lze využít i další formy zobrazení podmíněného formátu – datové čary, barevné škály a sady ikon. Jejich podobu ukazuje následující obrázek. Do tří sloupců byly zadány hodnoty 1 až 10. K nim pak bylo postupně přidáno podmíněné formátování. Nadpis sloupce označuje druh podmíněného formátování použitý ke zvýraznění.

44


V některých případech je třeba zvýraznit na základě jedné hodnoty celý řádek tabulky. Máme tabulku obsahující cenu ve sloupci E. Potřebujeme zvýraznit konkrétní řádek s cenou menší než 44. C

D

E

F

3

Datum

Počet kusů

Cena

Celkem

4

12. 1. 2004

84

99

8 316

6

13. 1. 2004

79

82

6 478

7

14. 1. 2004

79

42

3 318

8

15. 1. 2004

89

89

7 921

9

16. 1. 2004

87

12

1 044

10

17. 1. 2004

92

32

2 944

11

18. 1. 2004

78

65

5 070

12

19. 1. 2004

88

11

968

13

20. 1. 2004

80

33

2 640

14

21. 1. 2004

76

72

5 472

15

22. 1. 2004

90

37

3 330

16

Dosáhneme toho následujícím způsobem. Umístíme kurzor do buňky E4 a zvolíme pravidlo pro nastavení podmíněného formátování Určit buňky k formátování pomocí vzorce. Zde nastavíme vztah =$E4<44 a vybereme příslušný formát pro zvýraznění. Vzorec musí obsahovat znak $ přesně na pozici tak, jak je výše uvedeno.

45


Potvrdíme OK a v následujícím dialogovém okně vybereme oblast, pro kterou má být tento formát nastaven. V našem příkladu je to rozsah C4:F15.

Podmíněné formátování má „přednost“ před obvyklým „statickým“ formátováním přiřazeným konkrétní buňce. Možností, jak nastavit podmíněné formátování, je mnoho a doporučuji si další alternativy použití projít v aplikaci. Určitě se v krátkém čase s dalšími možnostmi sami seznámíte, neboť ovládání nastavení je velmi intuitivní.

46


Nezapomeňme však na pravidlo „střízlivosti“ v barvách (někdy méně je více) a dále také na to, jestli budeme chtít sešit s podmíněným formátováním tisknout, či ne.

1.2.8

Zadávání poznámek do aplikace

V řadě případů potřebujeme k uvedeným výpočtům připojit i vlastní vysvětlující nebo popisující komentář, vzorec či vztah, který jsme ve výpočtech použili, nebo podmínky pro naplnění určitého výpočtu, cíle pro dosažení konkrétní hodnoty atd. To samozřejmě můžeme provést tak, že to napíšeme jednoduše do sousední buňky, ale takto se list zaplní spoustou „balastu“ a stane se nepřehledným. Mnohem elegantnějším řešením je komentář, vysvětlení, vzorec, a dokonce i obrázek umístit do komentáře, který toto zajistí a zobrazí se pouze tehdy, pokud to budeme potřebovat. Jinak zůstává skrytý. Tuto funkci do konkrétní buňky vložíme stisknutím kláves Shift+F2 nebo přes PT Vložit komentář (zde je také možnost i komentář upravit či odstranit). O tom, že daná buňka obsahuje komentář, jsme informováni malým červeným trojúhelníkem umístěným v pravém horním rohu buňky. Komentáře samozřejmě můžeme i následně tisknout.

47


Při vkládání obrázku musíme dodržet následující (i když trochu delší) postup. Máme-li komentář zobrazený vedle buňky, klikneme na něj LT. Jeho obrys se označí a následně klikneme PT. Objeví se kontextová nápověda s volbou Formát komentáře. Zvolíme ji LT a objeví se nám okno se šesti záložkami, kde můžeme nastavovat řadu vlastností komentáře. Pro naši úlohu klikneme postupně na Barvy a čáry / Barva / Vzhled výplně / Obrázek. Zde pak vyhledáme a připojíme příslušný obrázek (grafický soubor).

1.2.9

Spuštění dodatečných nástrojů Excelu (doplňky, add in)

Při instalaci Excelu nedojde k spuštění všech nástrojů a funkcí, ale musíme si je dodatečně spustit (aktivovat), někdy i eventuálně doinstalovat. Doplnění/rozšíření si ukážeme na aktivaci karty Vývojář, která obsahuje například prvky užitečné pro práci s formuláři.

Obrázek 1-8 Aktivace karty Vývojář

48


Klikneme na Tlačítko Office a Možnosti aplikace Excel a zde zatrhneme volbu Zobrazit na pásu kartu Vývojář. Poté se nám přiřadí do menu jako poslední požadovaná karta Vývojář. Potvrdíme tlačítkem OK a od této chvíle ji budeme mít po každém spuštění aplikace k dispozici. Stejně tak jsou „ukryty“ i další utility, které si musíme nejprve aktivovat. Postupujeme jako u zapnutí karty Vývojář, ale zvolíme kategorii Doplňky. Dole na kartě pak Doplňky aplikace Excel a volbu Přejít. Následně se nám objeví okno s doplňky, které si zapneme P nebo (pokud je již nebudete potřebovat) odepneme z aplikace. Doplňky Analytické nástroje a Řešitel se po zaškrtnutí aktivují a jsou k dispozici na kartě Data v pravé části.

1.2.10 Ovládací prvky formuláře Pro zjednodušení, zefektivnění práce v aplikaci, vyloučení nesprávných hodnot, opakovaná šetření atd. jsou v Excelu připraveny určité prvky „formuláře“. Jsou uloženy na kartě Vývojář, kterou musíme mít aktivní – zapnutou. Na ní nalezneme tlačítko Vložit ovládací prvky. Dáme pozor, pod tlačítkem jsou vloženy dvě skupiny nástrojů, a to: 1) 2)

Ovládací prvky formuláře – prvky, jejichž připravenou funkčnost v dalším využijeme. Ovládací prvky ActiveX – slouží pro složitější úlohy, např. pro práci s makry; nebudeme používat.

49


Není mezi nimi na první pohled velký rozdíl, proto se vždy přesvědčíme o tom, že používáme správně první skupinu. Princip práce s prvky formuláře je jednoduchý. Vložíme ho jako samostatný objekt do listu „plovoucí“ nad vlastními buňkami sešitu. Následně pracujeme pouze s LT nebo PT. LT prvek používáme, PT nastavíme jeho vlastnosti, editujeme, měníme velikost atd. Přehled nejběžnějších prvků s jejich názvy vidíme na obrázku.

Obrázek 1-9 Ovládací prvky formuláře Praha Kladno Aš Brno Kostelec Opava Tábor

Pole se seznamem Umožňuje vybírat z předem nadefinovaných textových údajů. Podle vybrané hodnoty pak nastaví odpovídající hodnotu indexu, který souhlasí s pořadím v daném seznamu. Prvek vložíme do listu. Do buněk B3:B9 zadáme seznam měst. Nyní klikneme PT na prvek a zvolíme Formát ovládacího prvku. Zde nám stačí (stejně tak u všech dalších prvků) nastavit pouze záložku Ovládací prvek. Do pole Vstupní oblast zadáme odkaz na buňky oblasti obsahující hodnoty, které se mají zobrazit v rozevíracím seznamu.

50


V našem případě B3:B9. Do pole Propojení s buňkou zadáme odkaz na buňku obsahující číslo odpovídající pořadí v seznamu, například C6. Zvolíme-li dle zadání Aš, v propojené buňce se objeví číslo 3, Opava číslo 6 atd. Na tuto nastavenou hodnotu pak můžeme napojit další vzorce. Pro lepší vizuální efekt zaškrtneme políčko Prostorové stínování. Do tohoto ovládacího prvku nezadáme nic jiného, než co je nadefinováno ve vstupní oblasti. Zaškrtávací políčko Zajišťuje svou funkcí zapsání do propojené buňky hodnoty PRAVDA þ nebo NEPRAVDA ¨ (analogie klasického vypínače, kdy máme dva stavy „ZAPNUTO“ nebo „VYPNUTO“). Dle stavu „zaškrtávátka“ je tedy výsledkem v propojené buňce hodnota PRAVDA nebo NEPRAVDA. Abychom mohli dále na tyto dvě hodnoty jednoduše napojit další výpočty, musíme doplnit tento prvek jednoduchou funkcí N. Patří do kategorie funkcí informačních a jak vyplývá z názvu, informuje nás o výsledku výpočtu v buňce. Jinak řečeno „vrátí“ hodnotu převedenou na číslo, což se nám v tomto případě hodí. Tuto funkci je dobré, pokud bychom pracovali s formuláři častěji, si zapamatovat. Ukázka praktického využití prvku Zaškrtávací políčko je vidět na obrázku 1-10. Do sešitu jsme vložili dvě „zaškrtávátka“. Jedno ve stavu þ a druhé ve stavu ¨. Zaškrtnutím a odškrtnutím prvku dojde v propojené buňce ke změně na hodnotu PRAVDA nebo NEPRAVDA (krok 1). Druhý krok prostřednictvím funkce N převede tyto slovní pojmy na číselnou hodnotu 1 nebo 0. Třetí krok pak aplikuje funkci KDYŽ pro převod čísla na text ANO nebo NE.

Obrázek 1-10 Zapojení prvku Zaškrtávací políčko

51


Pro snazší pochopení logiky jsme použití prvku Zaškrtávací políčko ukázali v krocích. Nic nám nebrání v tom, abychom vzorce z kroku dva a tři sloučili do jednoho. Vzorec pak může vypadat takto: =KDYŽ(N(E3)=1;"Ano";"Ne") nebo =KDYŽ(N(E3);"Ano";"Ne"), a dokonce ještě jednodušeji =KDYŽ(E3=PRAVDA;1;0). Dáme pozor na následující skutečnost. Výsledek logické funkce PRAVDA nebo NEPRAVDA není v buňce uložen jako text, ale jako logická hodnota (zarovnáván implicitně na střed, tak ho na první pohled poznáme). Proto nemůžeme v tomto případě použít vzorec =KDYŽ(E3="PRAVDA";1;0), neboť je to vzorec pracující s textem (označen uvozovkami). Číselník Slouží pro rychlé zadávání číselných údajů z určité množiny místo toho, abychom je postupně zapisovali prostřednictvím klávesnice. Jeho použitím zajistíme to, že vstupní hodnoty do dalších výpočtů budou zadávány skutečně jako číslo (minimalizujeme chyby), a můžeme, je-li to vhodné, omezit uživatele na konkrétní hodnoty. Základní nastavení prvku opět nalezneme na záložce Ovládací prvek. Nastavíme zde rozsah a krok pro číselné údaje „obsluhované“ číselníkem. Jedno upozornění v souvislosti s tímto prvkem. Číselník „nemá“ k dispozici záporné hodnoty. Stejně tak „neumí“ desetinná čísla. V těchto případech si „vypomůžeme“ tak, že hodnoty z číselníku budou vstupovat do „pomocného vzorce“, který provede potřebnou úpravu. Potřebujeme-li záporné hodnoty pro výpočty, použijeme následující postup. Propojená buňka obsluhovaná číselníkem nechť je A3. V sousední buňce B3 zadáme vzorec = 50-A3. Volbu konstanty určujeme podle toho, jak velký rozsah záporných čísel potřebujeme. Další navazující výpočty budeme odkazovat pak na buňku s pomocným vzorcem, tj. B3. Obdobně si vypomůžeme tak, že do B3 zadáme =A3/10 nebo= A3/100 atd. Seznam Prvek umožňuje výběr z předem definované množiny záznamů. Pracuje obdobně jako nástroj Ověření dat. Navíc ve zvolené buňce ukazuje pořadí aktuálního zvoleného prvku v seznamu. Přepínač Umožňuje zvolit jedinou možnost z omezené sady vzájemně se vylučujících možností.

52


Obrázek 1-11 Použití prvku Přepínač

Ilustrativní obrázek je ukázkou toho, jak jej vložit a k čemu tento prvek formuláře můžeme použít. Přepínač vložíme jednou a poté ho dvakrát zkopírujeme a propojíme se stejnou buňkou. Posuvník Je obdobným prvkem jako Číselník. Navíc umožňuje nastavit parametr Změna o stránku. Do pole Změna o stránku zadáme číslo, o které se hodnota zvýší nebo sníží. Po kliknutí na oblast mezi jezdcem a jednou ze šipek posuvníku se nastavená hodnota pro změnu použije.

53


2 Matematické vzorce a vztahy ve finančních výpočtech Když jde o peníze, všichni lidé jsou stejného náboženství.

Finanční matematika je určitá oblast „standardní“ matematiky využívaná ve finančních (bankovních) výpočtech. Její zvládnutí je nutné pro provádění vlastních finančních výpočtů tak, abychom je mohli řešit, analyzovat a hodnotit. Jestliže nám budou v zásadě zřejmé a „jasné“, můžeme pro zefektivnění práce samozřejmě využít jako další úroveň „zpracování“ vhodný SW, v tomto případě kancelářský kalkulátor, jako je např. MS Excel. Je ale nutné zdůraznit to, že pro nezbytné osvojení finančních výpočtů je třeba, abychom jim samozřejmě „rozuměli“ po logické stránce, ale i v navazujících souvislostech. Použití Excelu není cíl našeho snažení, ale prostředek (nástroj) pro zefektivnění a urychlení prováděných výpočtů. Matematický aparát používaný v knize odpovídá, resp. je na úrovni středoškolské matematiky. Uvedeme, které základní oblasti jsou ve finančních výpočtech využívány, přičemž výčet samozřejmě může být podle zvoleného záběru a úrovně detailu rozšířen a doplněn: n n n n

průměry, indexy řady, posloupnosti procenta výpočty spojené s úročením – jednoduché – složené – smíšené (kombinované) n časová hodnota peněz a její využití

Při jejich opakování nebudeme zabíhat do větších detailů. Výše uvedené matematické veličiny jsou definovány a vysvětleny v každé učebnici středoškolské matematiky. Základní přehled je uveden například v knize Matematické vzorce (Bartsch, 1971).

54

Matematické vzorce a vztahy ve finančních výpočtech

2.1

Průměry

Průměry jsou pro svoji jednoduchost často používanými ukazateli. Jejich definici najdeme v základech každé matematiky a dále jsou rozváděny ve statistice jako míry polohy. Zvláště aritmetický průměr je používán (někdy i zneužíván) v celé řadě šetření, výpočtů, příkladů atd. Nejspíše je to pro jeho jednoduchost výpočtu. Někdy ale musíme dát velký pozor na jeho správné použití, neboť se nesprávným způsobem aplikace můžeme dopustit školácké chyby. Závěry pak z něho odvozené mohou být víceméně zavádějící, či dokonce nepravdivé. Průměr, a především aritmetický, dobře svoji hodnotou popisuje. Jinak řečeno je dobrým, resp. správným reprezentantem jen za určitého předpokladu. Lze jej bez problémů použít tam, kde je víceméně soubor, u kterého průměr zjišťujeme, homogenní a neobsahuje extrémní (nesourodé) hodnoty. Pokud ano, bývá vhodné doplnit jej i dalšími charakteristikami, jako jsou medián a modus, případně i dalšími. Medián je prostřední číslo ve skupině čísel, kdy má polovina čísel hodnotu vyšší než medián a polovina čísel hodnotu nižší než medián. Pro výpočet použijeme funkci =MEDIAN(čísla). Modus je nejčastěji se vyskytující číslo ve skupině čísel. Pro výpočet použijeme funkci =MODE(čísla). Praktická ukázka výpočtu: Průměr

Median

Modus

Data

2

2

2

2

3

3

3

2

3

3

3

3

5

5

5

125

7

7

7

4

10

10

10

4

5,00

4,00

3,00

median

3,50

průměr

20,5

modus

2

V prvních třech sloupcích je vidět výpočet všech tří ukazatelů pro stejný vzorek čísel. Svojí hodnotou mají dobrou vypovídající schopnost a mohou data (čísla) nahradit. Opačná situace je u čísel ve sloupci data, kde zjištěný průměr je 20,5, přičemž pět hodnot ze šesti tuto hodnotu vůbec nedosahuje.

55


Průměr silně ovlivnila jedna vysoká hodnota vzhledem k ostatním, a to 125. Použití průměru jako reprezentanta by v tomto případě bylo silně matoucí a neodpovídalo by to charakteru dat. V uvažovaném případě nám dobře poslouží místo průměru medián, který má mnohem lepší vypovídací úroveň, neboť není zatížen „extrémní“ hodnotou 125. Aritmetický průměr Nejběžnější z průměrů můžeme jednoduše definovat takto: Součet všech hodnot / počtem hodnot. Pro jeho zjištění použijeme funkci =PRŮMĚR(číslo1; číslo2; číslo3…). Vážený aritmetický průměr Jestliže jednotlivým hodnotám přiřadíme jejich váhy (důležitost), hovoříme pak o váženém aritmetickém průměru. Uvedeme si příklad. Učitel ekonomiky na střední škole přiřazuje jednotlivým výsledkům studentů váhy (důležitosti) následujícím způsobem. Typ hodnocení

Váha

Zkoušení ústní, desetiminutovky

1

Opakovací testy

2

Písemná práce

3

U studenta Nováka má vyučující za 1. pololetí tyto známky: Zkoušení ústní: 2 Desetiminutovky: 3, 2, 1, 3 Opakovací testy: 2, 3, 1 Písemné práce: 3, 2 Určíme výslednou průměrnou známku studenta. Jde o klasický případ užití váženého průměru, kdy význam jednotlivých známek je oceněn jejich vahami.

56

Matematické vzorce a vztahy ve finančních výpočtech A

B

C

D

E

2

Váha

Typ hodnocení

3

1

Zkoušení ústní

2

4

1

Desetiminutovky

3

2

1

5

2

Opakovací testy

2

3

1

6

3

Písemné práce

3

2

F

Známky

3

G

H

Počet

Počet * Váha

1

1

4

4

3

6

2

6

7

17

8

Součet známek váhy 1

11

1

9


6

2

10


5

3

11 12

38

13

2,235294118

Ke zjednodušení výpočtu je v buňce G12 vložen vzorec =SOUČIN.SKALÁRNÍ(G8:G10;H8:H10) a výsledný průměr je zjištěn v buňce H13 vzorcem =G12/H7. Vážený průměr známek studenta je tedy 2,23. Harmonický průměr Harmonický průměr je převrácená hodnota aritmetického průměru převrácených hodnot. Používáme ho pro výpočet průměru v případech, kdy proměnná má charakter části z celku (úlohy o společné práci apod.). Stejná součástka se vyrábí na dvou automatech. Starší z nich vyrobí 1 kus každých 6 minut, nový každé 3 minuty. Jak dlouho trvá v průměru výroba jedné součástky? Jde o typickou úlohu o společné práci. Pro určení průměrné doby výroby součástky proto použijeme harmonický průměr. Použijeme funkci =HARMEAN(čísla…). M

N

Čas

Převrácená hodnota

2

6

0,166666667

3

3

0,333333333

4

4

=HARMEAN(M2:M3)

=1/PRŮMĚR(N2:N3)

57


Geometrický průměr Pracujeme-li s proměnnou představující relativní změny (růstové indexy, tempo inflace, cenové indexy, …), používáme tzv. geometrický průměr, který je definován jako n-tá odmocnina ze součinu hodnot proměnné. V období roku 2002–2008 bylo dosaženo objemu produkce Pi. v podniku A. Zjistěte: 1.

Tempa růstu ri jako bázický (báze = základna = výchozí rok) a řetězový index. Jaké bylo průměrné tempo růstu v uvedeném období?

2. i

Rok

Produkce

0

2002

1550

1

2003

1535

2

2004

1228

3

2005

1105

4

2006

1215

5

2007

1361

6

2008

1525

Průměrné roční tempo růstu musí mít tu vlastnost, že může nahradit jednotlivá dílčí tempa růstu. A

B

C

D

E

F

G

4

i

Rok

Produkce

Index řetězový

Index bázický

Výpočet A

Výpočet G

5

0

2002

1550

6

1

2003

1535

0,990

0,990323

1557,846

1545,805

7

2

2004

1228

0,800

0,792258

1565,732

1541,621

8

3

2005

1105

0,900

0,712903

1573,657

1537,449

9

4

2006

1215

1,100

0,783871

1581,623

1533,288

10

5

2007

1361

1,120

0,878065

1589,629

1529,138

11

6

2008

1525

1,120

0,983871

1597,676

1525

12

Aritmetický p.

1,005

13

Geometrický p.

0,997

58


V D13 je proveden výpočet geometrického průměru =GEOMEAN(D6:D11), jeho hodnota činí 0,997. Aritmetický průměr tempa růstu je zjištěn v buňce D12 a činí 1,005. Ve sloupci F a G je pak pro kontrolu ověřen správný výsledek použití aritmetického (označeno Výpočet A) a geometrického průměru (označeno Výpočet G). Jedině správný je výpočet pomocí geometrického průměru, jak ukazuje stejná hodnota konečné hodnoty produkce v buňce C11 a G11 (1525). V případě použití aritmetického průměru se dopouštíme významné chyby, o čemž svědčí údaj v buňce F11, který je podstatně rozdílný od správné hodnoty 1525. V tabulce je uveden i princip výpočtu indexů v našem případě produkce, ale stejně tak to platí u další řady různých veličin, které potřebujeme hodnotit (inflace, hrubý národní produkt, produktivita práce, cenové indexy atd.). Používáme je k popisu vývoje řady konkrétní veličiny v čase (někdy mluvíme též o časových řadách) v relativní hodnotě nebo v procentech (vynásobíme-li každý stem). V následující tabulce je naznačen způsob výpočtu obou. Ve zlomcích se dosadí pak konkrétní hodnoty veličin, které analyzujeme. Rok

2002

2003

2004

2005

2006

2007

2008

Bázický index

–

2003/2002 2004/2002 2005/2002 2006/2002 2007/2002 2008/2002

Řetězový index

–

2003/2002 2004/2003 2005/2004 2006/2005 2007/2006 2008/2007

Ve finančních výpočtech se v zásadě používají tři základní způsoby výpočtů (tři způsoby úročení) souvisejících veličin:

2.2

Jednoduché úročení

Jedná se o takový způsob výpočtu, kdy se úroky vždy počítají ze stejné základní části. Úročí se stále jen základní jistina = vklad a vyplacené úroky k jistině (kapitálu) se nepřičítají a neúročí. Úroky se počítají stále z původní jistiny, kterou do banky vloží klient = vkladatel.

2.3

Složené úročení

Znamená, že se úroky postupně připisují k základní částce. Ta se postupně navyšuje a úroky v následujícím období se počítají z této zvýšené finanční částky. Lze v podstatě říci, že se počítají úroky z úroků.

59


Jinak řečeno o složené úročení se jedná tehdy, když se úroky připočítávají k původní jistině a v následujícím úrokovacím období spolu s jistinou se dále úročí. Rozdíl mezi oběma způsoby snáze pochopíme na příkladu. Na začátku období vložíme do banky 1 000 Kč při úrokové sazbě 5 % a době spoření 3 roky. Zjistěte, kolik na konci každého roku budete mít při jednoduchém nebo složeném způsobu úročení. Jednoduché úročení: Období

Vklad na začátku období

Úrok připsaný na konci období

Celkem na konci (vklad +úrok)

Efekt

1

1 000

50 = 1 000 x 0,05

1 050

2

1 000

50

1 100

úrok

3

1 000

50

1 150

150

Úrok připsaný na konci období

Celkem na konci (vklad +úrok)

Efekt

Složené úročení: Období

Vklad na začátku období

1

1 000,0

50 = 1 000 x 0,05

1 050,0

2

1 050,0

52,5 = 1 050 x 0,05

1 102,5

úrok

3

1 102,5

55

1 157,6

158

Vidíme, že mezi oběma způsoby výpočtu je samozřejmě rozdíl. Banka nám v prvém případě vyplatí úrok 150 Kč, v druhém 158 Kč. Této „odměně“ (to, co dostaneme navíc k našemu vkladu) za vložené peníze říkáme úrok. Z úroku musíme dále ještě zaplatit daň (z příjmu, nyní 15 %), a proto nám zbude o trochu méně. Úrok se připisuje v uvedených příkladech na konci úrokovacího období, a proto se tomuto způsobu říká polhůtní (roční) složené nebo jednoduché úročení. Opačný způsob předlhůtní úročení nemá v praxi využití, a proto se jím dále nebudeme zabývat. Pro oba způsoby úročení najdeme v příslušných matematických tabulkách potřebné vzorce, pomocí kterých pak jednoduše zjistíme to, co konkrétně potřebujeme.

60


Jednoduché úročení: j n = j 0 *(1 +

p * n) 100

Složené úročení: j n = j 0 *(1 +

p n ) 100

Označení: n jn – jistina na konci n-tého úrokovacího období n j0 – počáteční jistina, základna, vklad n n – počet úrokovacích období (roky, čtvrtletí, pololetí, měsíce, dny), někdy označováno též (t) n p – úroková míra, úrokové % n r – úročitel, r= (1+p/100) n i – úroková sazba, i = p/100 Oba uvedené vztahy jsou samozřejmě z pohledu matematiky „právoplatné“ rovnice, proto z nich můžeme odvodit další používané vztahy. Jestliže známe ostatní veličiny, lze v případě potřeby vypočítat zbývající neznámou. Příkladně tento vztah: r =n

jn j0

odvozený z druhé rovnice, kdy r=(1+p/100). Můžeme použít v úloze typu: Jak velký má být úročitel, abychom dostali za určitý počet let (n) výslednou částku jn,vložíme-li do banky vklad j0 (počáteční jistinu)? Jednoduchý ilustrativní příklad pro objasnění základních pojmů složeného úročení. Vložíme do banky částku 1 000 Kč při úrokové sazbě 6 %. Kolik činí úrok a celková částka, kterou nám banka po roce vrátí?

Na konci roku nám banka vrátí celkem 1060 Kč, z čehož úrok je 60 Kč.

61


Z pohledu finančního (ekonomického) se v uvedených vzorcích veličiny v korunách označují jiným způsobem, a to: n j0 – dnešní, současná hodnota (SH), na kalkulátorech, ve výpočtech a programech P nebo PV – Present Value n jn – budoucí hodnota (BH) na kalkulátorech, ve výpočtech a programech F nebo FV – Future Value Například vzorec pro výpočty složeného úročení pak bude vypadat: p n BH = SH *(1 + ) 100 Rekapitulace základních vztahů mezi jednotlivými veličinami složeného úročení společně s odpovídajícími finančními funkcemi je uvedena v následující tabulce: n let, jedenkrát ročně

n let, m krát ročně

Finanční funkce

Budoucí hodnota

p ö F = P × æç1 + ÷ è 100ø

n

p ö F = P × æç1 + ÷ è 100 × mø

Současná hodnota

p ö P = F × æç1 + ÷ è 100ø

-n

p ö P = F × æç1 + ÷ è 100ø

Úroková sazba [%]

æ F ö p = ç n - 1÷ ×100 è P ø

Počet období

n=

n n n n

log F - log P p ö logæç1 + ÷ è 100ø

n ×m

- n ×m

BUDHODNOTA (sazba;období;splátka; souč_hod;typ) SOUČHODNOTA (sazba;pper;splátka; bud_hod;typ)

ö æ F p = ç n×m - 1÷ × 100 × m è P ø

ÚROKOVÁ.MÍRA (pper;splátka;souč_hod; bud_hod;typ;odhad)

log F - log P p ö æ logç1 + ÷ è 100 × mø

POČET.OBDOBÍ (sazba;splátka;souč_hod; bud_hod;typ)

n×m =

m – počet úročení za rok n – počet období (v Excelu počet period pper) sazba – je úroková sazba dané půjčky nebo spoření typ – je číslo 0 nebo 1 a určuje způsob placení, 0 nebo neuveden znamená na konci období, 1 na začátku období n pper – je počet period, počet období plateb půjčky nebo spoření n splátka – je platba (vynakládaná finanční částka) prováděná v každém období. Nemůže být měněna v průběhu životnosti investice (proto ji taktéž můžeme označovat jako anuitu, konstantní částku), zahrnuje pouze dvě základní složky klasické splátky, a to úmor a úrok, nikoliv jiné poplatky a daně.

62


Důležitým pravidlem u uvedených finančních funkcí je to, že musí být stejná velikost splátek, musí být stejná, a tedy neměnná časová frekvence. Pokud by tomu tak nebylo, nelze uvedené funkce použít a musíme zvolit pro výpočet funkci jinou, která toto „povoluje“. Podrobnosti jsou uvedeny dále. Ve všech funkcích jsou povinné argumenty (hodnoty) zvýrazněny tučně. Ostatní povinné nejsou. Funkce BUDHODNOTA a SOUČHODNOTA je možno použít pro dva různé druhy výpočtu. Podle toho se do nich potřebné hodnoty také zadají. Podrobnosti jsou uvedeny v části 7. Jejich praktické použití si ukážeme na několika vzorových příkladech. Výpočet budoucí hodnoty Stanovíme si, jakou hodnotu bude mít po třech letech vklad 120 000 Kč při složeném úročení, jestliže úrokové období je roční a roční úroková sazba je 4,5 % (daň z úroků ani další poplatky neuvažujeme). 1

B

C

D

E

F

G

Úroková sazba (p.a)

4,50 %

2 3 4

Vklad 120 000 Doba spoření (roky) 3

5

Výpočet vzorcem

6

136 939,9 =D3*(1+G3)^D4

Výpočet funkcí -136 939,94 Kč

=BUDHODNOTA(G3;D4;;D3)

7 8

Vklad je pro nás v tomto případě vynakládaná hodnota nyní, tj. dnes (v současnosti). Je to tedy dnešní (současná) hodnota, a proto ji dosadíme do čtvrtého políčka funkce BUDHODNOTA (argument označen Souč_hod). Třetí argument nebude při této úloze vyplněn. Při používání těchto funkcí je třeba vždy zajistit „soulad“ dvou argumentů, a to pper a sazba. Oba musí vycházet ze stejné časové základny. V našem příkladě to tak je, neboť počet období jsou roky a sazba je roční. Z tohoto důvodu je můžeme právě takto ve funkci použít. Jinak by se jeden či druhý (nebo i oba) musely správně převést na „rozměr“ podle toho, co chceme počítat. Dále si všimneme i toho, že funkce má „přiřazen“ určitý formát včetně záporného znaménka. Modifikace uvedeného příkladu může být v tom, že úrok nebudeme připisovat jednou ročně, ale m-krát v roce. Například úrokové období bude čtvrt roku, doba spoření zůstane stejná 3 roky a úroková sazba bude zadána opět za rok. Pak se použité vzorce mírně změní následujícím způsobem:

63

Finanční gramotnost – výpočty v Excelu 1

B

C

D

E

F

G

Roční sazba

4,50 %

Úrokové období

čtvrtletí

-137 240,93 Kč

=BUDHODNOTA(G3/4;D4*4;;D3)

2 3

Vklad 120 000 Doba spoření 3 (roky)

5

Výpočet vzorcem

6

137 240,93 =D3*(1+4,5/(4*100))^(D4*4)

Výpočet funkcí

7 8

Protože je frekvence úročení vyšší (úročíme v kratších intervalech vícekrát) než v předchozím příkladě, dosáhli jsme většího zhodnocení. V sestavených vzorcích se počet období změnil na 12 = 4 x 3, ale procento úročení se úměrně snížilo 4,50 % / 4. Dále si všimneme ve výpočtu vzorcem počtu (počet levých a pravých závorek, musí být vždy stejný) a umístění závorek ve vzorci. Jiné uspořádání nepovede ke správnému výsledku. Výpočet současné hodnoty Jak vysoká finanční částka vložená před 15 lety vytvořila k dnešnímu datu částku 150 000 Kč? Úročení probíhalo složeným způsobem, připisování úroků bylo pololetní, úroková míra 3,5 % p.a. a daň z úroků je 15 %. Jak by se změnil vklad, pokud by úročení bylo roční, čtvrtletní, měsíční? 1

B

C

D

E

F

G

H

2 3

Finanční částka

4

Doba spoření (roky)

150 000

Roční sazba

3,50 %

Daň z úroků

15

Úrokové období

čtvrtletí

15 %

5 6

Výpočet vzorcem

Výpočet funkcí

7

roční

1

96 630,5 =$D$3*(1+$G$3/C7* (1-$H$4))^(-C7*$D$4)

-96 630,51 Kč =SH($G$3*(1-$H$4)/C7; $D$4*C7;;$D$3)

8

pololetní

2

96 319,6 =$D$3*(1+$G$3/C8* (1-$H$4))^(-C8*$D$4)

-96 319,59 Kč =SH($G$3*(1-$H$4)/C8; $D$4*C8;;$D$3)

9

čtvrtletní

4

96 162,2 =$D$3*(1+$G$3/C9*(1-$ H$4))^(-C9*$D$4)

-96 162,22 Kč =SH($G$3*(1-$H$4)/C9; $D$4*C9;;$D$3)

64

Matematické vzorce a vztahy ve finančních výpočtech 1

B

C

10 měsíční

12

D

E

96 056,6 =$D$3*(1+$G$3/C10*(1$H$4))^(-C10*$D$4)

F

G

H

-96 056,59 Kč =SH($G$3*(1-$H$4)/C10; $D$4*C10;;$D$3)

SH = SOUČHODNOTA

Z výpočtů je vidět, jak působí frekvence úročení na vklad. Čím je četnost úročení vyšší, tím menší částka je potřeba. Pokud porovnáme oba způsoby řešení (sestaveným vzorcem a finanční funkcí), je na první pohled vidět, že je snazší, jednodušší a přehlednější způsob řešení prostřednictvím připravené finanční funkce. Zmíněné klady jsou pádným důvodem pro používání funkcí. Je však nutno přesně vědět význam a závislosti mezi jednotlivými argumenty potřebnými pro konkrétní funkci. Další výhodou při vyplňování argumentů funkce je aplikace průvodce pro naplnění funkce (po zadání konkrétní funkce do buňky, průvodce vyvoláme stiskem Shift+F3). V okně průvodce jsou online vidět všechny potřebné informace i konečný výsledek, popř. chyba ve výpočtu indikovaná červeně. Po ukončení každého výpočtu se přesvědčíme o tom, že zjištěná hodnota „logicky“ odpovídá vstupním údajům. Výpočet úrokové sazby Jakou roční úrokovou sazbou byl úročen kapitál 48 600 Kč, vzrostl-li za 4 roky na 52 000 Kč? Úročení probíhalo složeným způsobem, připisování úroků bylo měsíční. 1

B

C

2

Kapitál (SH)

48 600

3

Doba spoření

4

4 5

D

E

F BH

Výpočet vzorcem 1,69 =((F2/C2)^(1/(4*12))-1)*100*12

52 000

Výpočet funkcí 1,69% =ÚROKOVÁ.MÍRA(C3*12;;-C2;F2)*12

6

Pro tento typ příkladu můžeme pro řešení využít nástroj Hledání řešení, který je schopen opakovanými iteracemi zjistit odpovídající výsledek. K výpočtu použijeme i funkci BUDHODNOTA. Nástroj bude měnit vstupní hodnotu úrokového procenta do doby, než se navýší výchozí hodnota 48 600 na hodnotu 52 000. Výsledné procento pak vynásobíme 12, abychom výsledek dostali v roční výši. Do buňky H11 (nastavená buňka) sestavíme vzorec

65


s funkcí =BUDHODNOTA(F11;C3*12;;-C2). Měněná buňka bude F11. Do nástroje dosadíme následující hodnoty: Nastavená buňka

H11

Cílová hodnota

52 000

Měněná buňka

F11

Po spuštění výpočtu Excel nalezne řešení – 0,001409. Po vynásobení dostáváme výsledek, tj. 1,692 %. Výpočet doby spoření (počtu období) Stanovíme si dobu, po kterou byl při složeném úročení zhodnocen kapitál 150 000 Kč. Úroková sazba činila 4 % p.a., úroky byly připisovány pololetně a budoucí hodnota kapitálu činila 180 000 Kč. Daň z úroků neuvažujeme. 1

B

C

2

Kapitál (SH)

150 000

3

Roční sazba

4,0 %

D

E

F BH

G

180 000

4 5

4,603469 =(LOG(F2)-LOG(C2))/(2* (LOG(1+C3/2)))

6

9,206938 =POČET.OBDOBÍ(C3/2;;-C2;F2) zde jsou pololetí

7 8

zde jsou zjištěny roky

4,603469

9 10

Kontrolní výpočet funkcí BH:

4

roky

11

-180000,00Kč

=BUDHODNOTA(C3/2;C8;;C2)

7

měsíců

7,241626

12

-180000,00Kč

=BUDHODNOTA(C3/2;E8*2;;C2)

7

dnů

0,241626

Všimneme si opět docela komplikovaného zápisu (v buňce C5) při řešení uvedeného příkladu prostřednictvím matematického vzorce a jeho převedení do podoby vzorce pro výpočet v Excelu. Komplexní pohled na využití výše uvedených základních vztahů může být tabulka pro orientační zjištění parametrů úvěru:

66


A

B

2

C

D

E

Úvěrový kalkulátor

3

Název funkce

4

PLATBA()

POČET.OBDOBÍ()

ÚROKOVÁ.MÍRA()

SOUČHODNOTA()

1 000,00 Kč

1 000,00 Kč

1 000,00 Kč

1 000,13 Kč

5

Půjčka v Kč

6

Roční úrokové % (p.a.)

15 %

15 %

15 %

15 %

7

Počet splátek (v měsících)

36

36

36

36

8

Výše splátky (měsíční)

34,67 Kč

34,67 Kč

34,67 Kč

34,67 Kč

9

Pomocná otázka, co potřebujeme zjistit

Jakou částku budu splácet každý měsíc, pokud si vezmu úvěr?

Kolik splátek musím zaplatit pro úhradu úvěru?

Jak vysoké je úrokové %, které banka vyžaduje pro úvěr za těchto podmínek?

Jakou částku si mohu půjčit na základě výše splátky?

D6:

E5:

10

B8:

11

=-PLATBA(B6/12; B7;B5;0;0)

C7: =P.O(C6/12;-C8; C5;0;0)

=Ú.M(D7;-D8;D5; =SH(E6/12;E7;-E8; 0;0)*12 0;0)

V tabulce v podbarvených (vystínovaných) buňkách je vložen odpovídající vzorec řešící odpověď na otázku související s úvěrem. Do této buňky nic nezadáváme. Změnou zbývajících tří hodnot ve sloupci (nezvýrazněných) můžeme různě modifikovat podmínky pro výpočet. Například ve sloupci B počítáme výši splátky prostřednictvím funkce PLATBA, známe-li ostatní hodnoty jako půjčku, roční úrokové % a počet splátek. Obdobným způsobem pracujeme ve zbývajících sloupcích. Vzorce počítající neznámou veličinu v buňkách B8, C7,D6 a E5 jsou vypsány v posledním řádku tabulky. Doposud jsme uvažovali jednodušší situaci, kdy do výpočtu vstupuje pouze jedna hodnota. Příkladně jeden vklad bude v bance úročen po dobu tří let při konkrétní úrokové sazbě. Ale mnohem větší část výpočtů složeného úročení je založena na principu „opakujících“ se transakcí, pro jejichž výpočet se musí základní uvedený vztah upravit. Vyjdeme tedy ze základního vztahu a představíme si situaci, že budeme opakovaně vkládat na začátku každého období stejnou částku po dobu pěti let. Budoucí hodnotu pak zjistíme součtem pěti členů lišících se pouze dobou uložení, a tím i exponentem. První člen bude úročen celou dobu, tj. pět let, druhý pouze čtyři atd. až poslední pouze jeden rok. Částky, které vkládáme, jsou stejně velké, ve vztahu označené A jako anuity. Sestavená rovnice pak vlastně představuje geometrickou řadu.

67


(

p BH = A * 1 + 100

)

n

(

p + A * 1 + 100

)

n -1

(

p + . . . + A * 1 + 100

)

1

Tento vztah dále zjednodušíme vzorcem pro součet geometrické řady (posloupnosti) a dostaneme nový tvar vzorce, který využíváme ve výpočtech: P BH = A * (1 - 100 )*

n (1+100P ) -1 - A * r * rr --11 (1+100P ) -1 n

Tím jsme sestavili výsledný vzorec pro výpočet budoucí hodnoty opakovaných vkladů. Samozřejmě jej lze dohledat v různých učebnicích či matematických tabulkách. Následující tabulka stručně shrnuje základní veličiny, vztahy mezi nimi a vzorce či funkce, které používáme při výpočtech spoření. Horní část obsahuje vstupní údaje, které při výpočtu používáme. V řádcích šest až osm jsou výpočty pro jednorázový vklad, vlevo uvedeny klasickým vzorcem, vpravo pak použitím funkce BUDHODNOTA. V dalších řádcích počítáme spoření v případě opakovaných částek (vkladů, anuit) opět vlevo vzorci a vpravo funkcí BUDHODNOTA. Všimneme si, že vklady mohou být vydávány na začátku nebo na konci období (předlhůtní, polhůtní). U funkce BUDHODNOTA o tom rozhoduje poslední nepovinný argument Typ. 1

B

C

D

E

F

G

H

I

J

2

p=

10

– úroková míra v %

3

=p/100 i =

0,1

– úroková sazba za období (roční, pololetní)

4

r=

1,1

– úrokový faktor, úročitel (1 + i)

K

L

SH =

1000

A=

500

n/t=

4

5 6

Jednorázový

1464,1 =L2*E4^L4

-1 464,10 Kč

7

SH

1464,1 =L2*(1+E3)^L4

=BUDHODNOTA(E3;L4;;L2)

8

Současná hodnota

jeden vklad

10 Opakovaný 11

A

12

Anuita

více vkladů

1464,1 =L2*(1+E2/100) ^L4

Vklad na konci období (polhůtní): 2320,5 =L3*(E4^L4-1)/E3

=BUDHODNOTA(E3;L4;L3)

13

Vklad na začátku období (předlhůtní):

14

2552,55 =L3*(E4^L4-1)/E3 *(1+E3)

15

68

-2 320,50 Kč

vynásobeno (1+i)


B

16

C

D 2552,55

E

F

G

=L3*E4*(E4^L4-1)/(E4-1)

17

H

I

J

K

L

-2 552,55 Kč =BUDHODNOTA(E3;L4;L3;;1)

Zbývající používané vzorce pro různé kombinace jsou uvedeny v kapitole č. 7.

2.4

Smíšené úročení

Vychází z obou předchozích typů úročení. Použije se v případě, kdy dobu splatnosti lze vyjádřit jako součet celočíselného počtu úrokových období a zbytku, který je kratší než jedno úrokové období. Při smíšeném úročení se přes celé časové jednotky (např. roky) úročí pomocí složeného úročení a přes neúplné poslední časové období pomocí jednoduchého úročení.

(

BH = SH * 1 +

p * (1- d ) f

) * (1 + p * (1 - d )* z ) t

n n n n n

BH – budoucí hodnota SH – současná hodnota d – srážková daň z úroku P – úroková míra, úrokové %, úroková sazba f – frekvence úročení (kolikrát jsou úroky připisovány do roka 1/2/4/12/50/360) n t – počet celých úrokových období, po které byl kapitál uložen n z – zbytková doba uložení (vyjádřená v letech, /360)

Uvedený vztah obsahuje možnost započítání i srážkové daně d (nyní je stanovena ve výši 15 %), která samozřejmě snižuje celkovou výnosnost finanční transakce. Takto vypočtená částka se označuje jako čistý výnos. Obdobným způsobem ji můžeme zahrnout do výše uvedených vzorců, jak pro jednoduché, tak i složené úročení. Použití vzorce si názorně ukážeme na jednoduchém příkladu. Na kolik se zúročí 20 000 Kč za 8 let a 3 měsíce při úrokové sazbě 12 % p.a. a ročním úročení? Při ročním úročení se úroky připisují 1x do roka (m = 1), 8 let a 3 měsíce v sobě obsahuje 8 celých let a 3/12 zbytku. Použijeme vzorec pro smíšené úročení a dosadíme správné hodnoty.

69


BH=20000*(1+0,12/1)^8*(1+0,12*3/12)= 51 004,84 Kč Za 8 let a 3 měsíce se vklad 20 000 zúročí na 51 005 Kč. Pokud bychom pro výpočet v tomto případě použili funkci BUDHODNOTA a počet období do funkce uvedli jako 8,25 (8 a 3/12): =BUDHODNOTA(0,12;8,25;;-20000)= 50 942,32 Kč, dospěli bychom k odlišnému, a tedy nesprávnému výsledku.

2.5

Využití jednoduchého úročení

Z hlediska časového lze bankovní produkty členit na krátkodobé, střednědobé a dlouhodobé. Pro krátkodobé produkty je typická doba splatnosti nepřesahující jedno úrokové období, kterým je nejčastěji jeden rok. Z tohoto důvodu jsou takové produkty založeny na principu jednoduchého úročení. Konkrétně se jedná o: n n n n n

běžný účet (bez i s povoleným přečerpáním), krátkodobé úvěry a dluhopisy – např. státní pokladniční poukázky, směnky, produkty pro vybranou klientelu (banky s vybranými klienty), produkty peněžního trhu (velké výrobní a obchodní společnosti a banky), n obchodování na mezibankovním trhu, n mezibankovní trh depozit – DEPO.

Základní vzorec, který jsme pro vysvětlení principu jednoduchého úročení uvedli dříve, se pro praktické využití ve finančních (bankovních) výpočtech nepoužívá. Zpravidla je nahrazen následujícím tvarem, který slouží k výpočtu výše úroku pro časovou jednotku den: p U = SH * 100 *

n n n n

70

td 360

U – úrok SH – současná hodnota (vklad, jistina) p – úroková míra, úrokové %, úroková sazba td – počet dnů


U tohoto vzorce je poslední člen nejednoznačně definovaný, neboť při jeho naplnění vzniká problém s nestejným počtem dnů v jednotlivých obdobích roku (měsíce). Často se jednoduše počítá s tím, že každý měsíc má 30 dní a celý rok 360 dnů. Pro vyjádření doby splatnosti ve dnech se v evropských zemích používají domluvené tzv. standardy: n ACT/365 (anglický standard) znamená, že každý měsíc má skutečný počet dní (ACT = aktuální) a rok má 365 dní v roce, n ACT/360 (francouzský standard) znamená, že každý měsíc má skutečný počet dní (ACT) a rok má 360 dní v roce, n 30E/360 (německý standard) znamená, že každý měsíc má 30 dní a rok má 360 dní v roce, n ACT/ACT. Použití si ukážeme na příkladu: Dne 6. 4. 2001 jsme při úrokové sazbě 4 % uložili 100 000 Kč. Vybrali jsme je 15. 11. 2001 včetně úroků. Nejprve si zjistíme odpovídající počet dnů v uvedeném období. Uvažujeme dvě alternativy podle: n standardu ACT (aktuální, skutečný počet dnů), n standardu 30E (skutečný počet dnů nahrazen 30 dny). Počet dnů Standard

30E

ACT

duben

24

24

květen

30

31

červen

30

30

červenec

30

31

srpen

30

31

září

30

30

říjen

30

31

listopad

15

15

Celkem

219

223

71

Finanční gramotnost – výpočty v Excelu Typ úročení

Počet dnů

Délka roku

Vzorec

Výsledek

ACT/365

223

365

=100 000*0,04*(223)/365

2443,84

ACT/360

223

360

=100 000*0,04*(223)/360

2477,78

30E/360

219

360

=100 000*0,04*(219)/360

2433,33

Úrok bude činit postupně 2 443,84 Kč, 2 477,78 Kč a 2 433,33 Kč. Z hlediska dlužníka (banky) je výhodné použít německý standard 30E/360, protože tak vychází nejnižší úrok. Naopak z hlediska věřitele je nejvýhodnější francouzský standard určení doby splatnosti, neboť tak získáme nejvyšší úrok. Provedený výpočet je jednoduchý, ale je potřeba delší čas na zjištění časových údajů. V Excelu proto najdeme i odpovídající funkci, která to za nás udělá perfektně. Jmenuje se ACCRINTM a jen do ní dosadíme potřebné argumenty. Poslední argument pak udává typ úročení podle hodnoty – viz sloupec B. Funkce striktně vyžaduje zadání data vkladu (emise) a data výběru jako formát čísla datum. 1

B

C

2

Datum vkladu 6. 4. 2010

3

Datum výběru 15. 11. 2010

D

E Úroková sazba: 4,0 % Vklad: 100 000

4

Základna

Typ úročení

Vzorec

Výsledek

5

0 nebo neuveden

US (NASD) 30/360

=ACCRINTM($C$2;$C$3;$E$2;$E$3;0)

2433,3333

6

1

Aktuální/aktuální

=ACCRINTM($C$2;$C$3;$E$2;$E$3;B6)

2443,8356

7

2

Aktuální/360

=ACCRINTM($C$2;$C$3;$E$2;$E$3;B7)

2477,7778

8

3

Aktuální/365

=ACCRINTM($C$2;$C$3;$E$2;$E$3;B8)

2443,8356

9

4

Evropské 30/360

=ACCRINTM($C$2;$C$3;$E$2;$E$3;B9)

2433,3333

Následující příklad ukazuje využití vztahu pro jednoduché úročení při rozhodování o vhodné investici. Cena pozemku, o který máme zájem, je nyní 200 000 Kč nebo za rok 210 000 Kč. Částku 200 000 Kč můžeme nyní investovat na dobu 1 roku při úrokové míře 4,2 % p.a. Pozemek zatím nechceme využívat po dobu alespoň jednoho roku. Která varianta nákupu pozemku je pro nás výhodnější? Budeme tedy uvažovat dvě varianty: 1) 2)

Investovat částku 200 00 Kč na rok při úrokové míře 4,2 % p.a. Zjistíme vytvořený úrok. Za rok je rozdíl v ceně pozemku 210 000 – 200 000 = 10 000 Kč

Porovnáme výsledky 1. a 2. Výhodnější pak vybereme.

72


K výpočtu použijeme základní vztah pro jednoduché úročení a dosadíme správná čísla: Úrok = 200 000 x 4,2/100 x 360/360 = 8 400 Kč. Ke stejnému výsledku dospějeme i při použití funkce ACCRINTM. Pro její použití si zvolíme vhodně první a druhé datum, např. 30. 10. 2012 a 31. 10. 2013. Správně dosadíme odpovídající hodnoty = ACCRINTM(30.10.2012; 31.10.2013;0,042;200 000) a dospějeme ke stejnému výsledku 8 400 Kč. Z toho vyplývá závěr: Nákup pozemku nyní za částku 200 000 Kč je výhodnější, neboť vytvořený úrok za jeden rok je menší než vyšší cena pozemku (8 400 Kč < 10 000 Kč). Další ukázkou aplikace jednoduchého úročení je způsob výpočtu úroku na běžném účtu. Klient banky má na svém účtu úročeném 1,5 % p.a. následující pohyby. Zůstatek z předchozího období ke dni 3. 1. 2006 je ve výši 12 000 Kč. Dne 12. 1. 2006 byla na účet připsána částka 6 000 Kč. Dne 22. 1. 2006 klient zaplatil za nákup v prodejně 3 000 Kč a 26. 1. 2006 obdržel na účet 1 000 Kč. Jaký byl stav účtu ke dni 31. 1. 2006? K výpočtu úroku banka používá standard ACT/360. Všechny potřebné údaje a vzorce ukazuje tabulka: 1

B

C

D

E

F

G

H

Úrok. sazba na účtu

1,50 %

Zůstatek

Úrok ze zůstatku v Kč

Použité vzorce (buňky G4 až G7):

12 000

4,50

=ACCRINTM(C4;C5;$H$2;F4;2)

18 000

7,5


15 000

2,50


16 000

3,33


2 3

Doba úročení ve dnech

4

Datum

Výdej

Příjem

3. 1. 2006

5

9

12. 1. 2006

6

10

22. 1. 2006

7

4

26. 1. 2006

8

5

31. 1. 2006

6 000 3 000 1 000

16 000

9

17,83 daň z úroku 15 %

2,675

73


Opět jsme ke zjištění odpovídajícího úroku použili funkci ACCRINTM. Výše celkového úroku činí 17,83 Kč a daň z úroku je 2,675 Kč. Na konci měsíce bude mít klient na účtu celkem 16 000 + 15,16 =16 015,16 Kč. Následující příklady jsou opět ukázkou aplikace vztahu jednoduchého úročení. Pan Šedivý uvažuje o eskontu směnky znějící na 12 000 Kč při diskontní míře 5,5 % p.a. v době tři, dva a jeden měsíc před dnem její splatnosti. Jinak řečeno vlastník směnky uvažuje o prodeji třetí straně dříve, než je vlastní splatnost, a tím se vlastně „okrádá“ o úrok za konkrétní období. Zvolíme-li vhodně data po měsících, zjistíme prostřednictvím funkce ACCRINTM odpovídající úrok, o který se pan Šedivý připraví při prodeji dříve než v den splatnosti. Výpočet je uveden v tabulce: 1

B

C

D

2

Datum

Úrok

Vyplacená částka

3

3. 8. 2013

165

4

3. 9. 2013

5 6

E

F

G

H

Adresa vzorce

Vzorec

Hodnota

11 835

C7

=ACCRINTM(B7;$B$10;$D$4;$B$4)

165

110

11 890

C8


110

3. 10. 2013

55

11 945

C9


55

3. 11. 2013

0

12 000

C10


0

7

Směnka znějící na částku 100 000 Kč a s datem splatnosti 19. 10. 2005 byla eskontována v bance dne 5. 7. 2005. Jakou částku vyplatí banka držiteli směnky, je-li diskontní míra 5 % p.a. a jestliže banka účtuje eskontní provizi 0,05 % ze směnečné částky? Při výpočtu použijte standard 30E/360. Výpočet provedeme funkcí ACCRINTM. Dosadíme správné hodnoty. Zjistíme „ušlý úrok“ za předčasné splacení =ACCRINTM(5.7.2005;19.10.2005;0,05;100000) = 1 444,4 Kč a tento odečteme od 100 000 Kč, potom dostaneme 98 555,6. Eskontní provize činí 100 000 x 0,0005 = 50 Kč. Držitel směnky obdrží od banky částku 98 505,6 Kč. Využití výpočtů složeného úročení bude vysvětleno a podrobně prezentováno v dalším textu knihy.

74

Toto je pouze náhled elektronické knihy. Zakoupení její plné verze je možné v elektronickém obchodě společnosti eReading.

Finanèní gramotnost. Výpoèty v Excelu. Zbynìk Bárta

Recommend Documents