Obsah
Obsah Předmluva Typografická konvence použitá v knize
13 14
1 Modelování, simulace a analýza za použití Excelu Modelování Tabulkový model Netabulkový model
Simulace Analýza Vybrané nástroje pro analýzu a simulaci Různé klouzavé průměry Klouzavý průměr Klouzavý průměr centrální Vážený klouzavý průměr Exponenciální vyrovnání Histogram Generování pseudonáhodných čísel některých rozdělení Pseudonáhodná čísla generovaná funkcemi
Příklady modelů Příklad 1 – Variantní výpočet zisku OSVČ Příklad 2 – Simulace hospodářských výsledků Příklad 3 – Provize obchodní sítě Příklad 4 – Základní analýza kurzu akcií Příklad 5 – Model prodeje výrobků
15 16 18 20
21 22 22 23 26 28 29 30 31 34 37
37 37 38 41 42 43
2 Vybrané partie Práce s verzemi Excelu Soubory s programem Důvěryhodné složky Odstranění doplněných příkazů a panelů nástrojů
Uložení sešitu jako šablony Jednotný design – motivy Adresace buněk a oblastí Názvy buněk a oblastí Vazba mezi buňkami Vazby mezi sešity
Příklady Příklad 1 – Absolutní adresace
45 46 47 48 49
50 52 53 55 58 58
59 59
3
K1609.indd 3
22.4.2009 10:24:58
Obsah
Příklad 2 – Pojmenování buněk Příklad 3 – Pojmenování oblastí
Komentáře Kopírování Kopírování se schránkou Windows Kopírování se Schránkou sady Office
Přemístění dat Formátování čísel Kopírování vzorců a funkcí pro jejich zobrazení Převod vzorců (funkcí) na hodnoty Opakování operace
60 62
63 66 66 70
71 71 75 76 77
3 Vzorce a funkce Vzorce Funkce Vnořené funkce Maticový vzorec (funkce) Maticový vzorec (funkce) s jedním výsledkem Maticový vzorec (funkce) s více výsledky
84 85 85 86
86
Poznámky ke vzorcům a funkcím Výběr užitečných funkcí
88 88
Příklad 1 – Operativní evidence Příklad 2 – Náklady středisek
MAX MIN SMALL PRŮMĚR GEOMEAN HARMEAN Společné poznámky k průměrům Vážený aritmetický průměr Aritmetické průměry s podmínkou AVERAGEIF AVERAGEIFS Příklad – Databáze nábytku – průměry
Další výpočty k průměrným hodnotám PRŮMODCHYLKA TRIMEAN MEDIAN MODE
SUMIF
K1609.indd 4
80 81
Matice konstant (maticová konstanta)
KDYŽ SUMA
4
79
89 92 93 94
95 95 95 96 97 98 98 99 100 100 101 102
103 103 103 104 104
104 Microsoft Excel 2007 pro manažery a ekonomy
22.4.2009 10:24:58
Obsah
SUMIFS Příklad – Náklady na provoz vozidel
SUBTOTAL Příklad 1 – Variantní výpočty Příklad 2 – Výsledky měření Příklad 3 – Výpočty v seznamu Příklad 4 – Výpočty v předdefinované tabulce Excelu
Funkce na výpočet počtu hodnot POČET POČET2 COUNTBLANK COUNTIF COUNTIFS Příklad – Databáze nábytku – počty hodnot
ČETNOSTI POWER HODNOTA HODNOTA.NA.TEXT SOUČIN.SKALÁRNÍ Příklad 1 – Výběr nejlepšího výrobku Příklad 2 – Výpočet spotřeby materiálu Příklad 3 – Investiční model GE
Vyhledání položky SVYHLEDAT Příklad 1 – Plán rozvozu Příklad 2 – Výběr platu podle tarifní stupnice Příklad 3 – Doplňování seznamu z databáze položek Příklad 4 – Racionalizace naceňování rozpočtu stavby
VVYHLEDAT Příklad 5 – Vyhledání normalizovaného rozměru
INDEX Příklad 6 – Stupnice mzdových tarifů
VYHLEDAT Příklad 7 – Varianta plánu rozvozu
POZVYHLEDAT Příklad 8 – Vyhledání pozice
ZVOLIT Příklad 9 – Rozvoz pečiva
Databázové funkce DSUMA DPRŮMĚR DMAX DMIN DPOČET DPOČET2 DZÍSKAT Příklad - Analýza provozu vozidel
106 107
108 109 110 110 111
111 112 112 112 112 113 113
115 116 117 118 119 119 120 121
123 124 125 126 127 127
132 133
133 135
135 137
137 138
138 139
140 141 141 141 141 141 141 142 142
Zaokrouhlování
143
ZAOKROUHLIT
145
5
K1609.indd 5
22.4.2009 10:24:58
Obsah
Příklad 1 – Přehled tržeb
146
ZAOKR.DOLŮ
147
Příklad 2 – Rozvaha k výpočtu ceny
148
ZAOKR.NAHORU
148
Příklad 3 – Objednávka zboží
148
ZAOKROUHLIT.NA.LICHÉ ZAOKROUHLIT.NA.SUDÉ ROUNDDOWN ROUNDUP MROUND
149 149 149 150 150
Příklad 4 – Výpočet ceny
151
ZAOKROUHLIT.NA.TEXT KČ Související funkce
151 152 153
USEKNOUT CELÁ.ČÁST MOD QUOTIENT
153 153 154 154
Předpověď budoucího stavu
154
FORECAST
155
Příklad 1 – Výdaje na reklamu
156
LINTREND
157
Příklad 2 – Výdaje na reklamu 2 Příklad 3 – Odhad vývoje ceny Příklad 4 – Předpoklad tržeb Příklad 5 – Odhad ceny nemovitosti Příklad 6 – Předpoklad prodeje zmrzliny
158 158 159 159 160
LOGLINTREND
161
Příklad 7 – Zavádění nového výrobku Příklad 8 – Předpoklad nákladů na reklamu pro dosažení prodeje
Funkce pro práci s časovými údaji X-funkce, datum před 1. 1. 1900 Aktuální datum DNES NYNÍ DATUM DEN TÝDNE Příklad 1 – Podbarvení sobot a nedělí MĚSÍC WEEKNUM, WeekNum_ISO DenRoku
Výpočty s datovými údaji WORKDAY NETWORKDAYS EDATE EOMONTH YEARFRAC Příklad 2 – Časový plán projektu
6
K1609.indd 6
161 163
164 165 167 167 168 168 170 171 171 172 174
174 174 175 175 176 176 177
Microsoft Excel 2007 pro manažery a ekonomy
22.4.2009 10:24:58
Obsah
Funkce finanční a finanční analýzy Funkce související s konstantní anuitou BUDHODNOTA SOUČHODNOTA POČET.OBDOBÍ PLATBA PLATBA.ZÁKLAD PLATBA.ÚROK ÚROKOVÁ.MÍRA ISPMT
Funkce vracející kumulovanou jistinu a úrok CUMPRINC CUMIPMT
Výpočet budoucí hodnoty jistiny FVSCHEDULE
Odpisové funkce ODPIS.LIN ODPIS.NELIN Naprogramované odpisové funkce Příklad 1 – Rovnoměrné a zrychlené odpisování
Funkce pravidelných plateb peněžního toku (nekonstantní anuity) ČISTÁ.SOUČHODNOTA MÍRA.VÝNOSNOSTI Příklad 2 – Výhodnost varianty investiční akce MOD.MÍRA.VÝNOSNOSTI Příklad 3 – Periodický peněžní tok (nekonstantní anuita)
Funkce neperiodického peněžního toku XNPV XIRR Příklad 4 – Výnosnost investice Příklad 5 – Neperiodický peněžní tok
Funkce přepočítávající úrok
177 178 180 181 182 183 185 185 186 187
188 188 189
189 190
190 191 192 193 194
196 197 198 199 199 200
201 202 202 203 204
204
EFFECT NOMINAL
205 205
Převodní funkce
206
DOLLARDE DOLLARFR
206 207
Operace s dluhovými cennými papíry Úroky z dluhových cenných papírů ACCRINT ACCRINTM
Diskontní sazba dluhopisu DISC INTRATE
Výpočty časových údajů u dluhopisů COUPDAYBS COUPDAYS COUPDAYSNC COUPNCD COUPNUM
207 209 209 210
211 211 211
212 212 213 213 213 214
7
K1609.indd 7
22.4.2009 10:24:58
Obsah
COUPPCD
Další výpočty související s dluhopisy PRICE PRICEDISC PRICEMAT
Výnosy z dluhopisů YIELD YIELDDISC YIELDMAT
Výpočet budoucí hodnoty dluhopisu
214
214 215 216 216
216 217 217 218
218
RECEIVED
218
Výpočet durace
219
DURATION MDURATION
Výpočty ceny a výnosů T-bill TBILLEQ TBILLPRICE TBILLYIELD
Funkce pro zachycení chyb
219 220
221 222 222 223
223
4 Podmíněné formátování Poznámky Příklady Příklad 1 – Hlídání nákladů Příklad 2 – Rozbor produktivity práce
227 229 234 234 237
5 Ověřování vstupních dat Poznámky Příklady Příklad 1 – Zápis dat do seznamu Příklad 2 – Výroba a prodej hraček Příklad 3 – Rozdělení fondu pracovní doby
239 241 249 249 250 252
6 Grafy Poznámky Různé Prvky (objekty) v grafu Výběr prvků grafu Kontextová karta Nástroje grafu
8
K1609.indd 8
255 258 266 266 267 267
Microsoft Excel 2007 pro manažery a ekonomy
22.4.2009 10:24:58
Obsah
Formátování prvků grafu Návrat na výchozí formát Počátek měřítka na ose hodnot Přidání a odstranění prvků grafu Změna typu grafu Kombinované grafy Graf s vedlejší osou Y Popisky osy a zakreslení mřížky Bodový graf Spojnicové grafy s kalendářní osou Grafy s časovou osou Provázání grafu a tabulky Zmrazení grafu Atraktivní grafy Použití šrafování (vzorků)
Tisk grafů Doplnění objektů Doplnění spojnice trendů Doplnění klouzavého průměru Chybové úsečky Spojnice extrémů Sloupce vzrůstu a poklesu Vynášecí čáry
Příklady Příklad 1 – Atraktivní graf – zpráva o prodeji Příklad 2 – Překrývající se sloupce – náklady a výnosy Příklad 3 – Překrývající se pruhy – sklizeň plodin Příklad 4 – Zakreslení tří proměnných – hodnocení středisek Příklad 5 – Ganttův diagram – plán projektu Příklad 6 – Stromečkový diagram – výnosy a náklady Příklad 7 – Graf pro rozbory – zpráva o prodeji Příklad 8 – Úprava grafu pro tisk – zpráva o prodeji Příklad 9 – Předpověď vývoje prodeje – spojnice trendů Příklad 10 – Plán tržeb – chybové úsečky Příklad 11 – Sledování stavu pacienta Příklad 12 – Sledování procesů Příklad 13 – Pracovní rychlost postřikovače Příklad 14 – Magický čtyřúhelník
268 269 269 270 270 270 271 273 273 275 276 276 277 277 279
281 281 281 283 283 284 285 285
285 285 289 291 294 297 300 303 305 307 309 311 313 315 317
7 Hledání řešení Poznámky Příklady Příklad 1 – Zjištění nákladů pro dosažení produktivity Příklad 2 – Vklad pro uspoření požadované částky Příklad 3 – Doba pro zdvojnásobení vkladu
323 325 327 327 327 328
9
K1609.indd 9
22.4.2009 10:24:58
Obsah
8 Citlivostní analýza
331
Citlivostní analýza pro jednu proměnnou
333
Poznámky Příklad 1 – Ukazatelé rentability Příklad 2 – Zjištění ceny Příklad 3 – Určení optimální velikosti dodávky
333 334 334 335
Citlivostní analýza pro dvě proměnné
336
Poznámky Příklad 4 – Analýza mzdových nákladů Příklad 5 – Analýza návrhu ceny Příklad 6 – Analýza dosažení zisku
337 337 338 339
9 Scénáře
341
Poznámky Příklady
343 345
Příklad 1 – Simulace mzdových nákladů Příklad 2 – Simulace plánovaného zisku při různé ceně Příklad 3 – Rozbor produktivity
345 346 347
10 Práce se seznamy
349
Seřazení seznamu
351
Poznámky Příklad 1 – Seřazení seznamu
353 353
Filtrování dat
353
Poznámky Příklady
356 357
Příklad 2 – Vybrané provozy a odpisy, automatická filtrace Příklad 3 – Pět největších položek Příklad 4 – Položky z intervalu hodnot Příklad 5 – Odpisová skupina, filtrace výběrem Příklad 6 – Výběr podle více podmínek, rozšířená filtrace
Vytváření souhrnů Poznámky Příklady Příklad 7 – Struktura vybavenosti provozů Příklad 8 – Výpočty pro typ HIM
Tabulka aplikace Excel Poznámky
10
K1609.indd 10
357 357 358 358 359
360 360 361 361 361
363 363
Microsoft Excel 2007 pro manažery a ekonomy
22.4.2009 10:24:58
Obsah
11 Kontingenční tabulky a grafy Poznámky Příklady Příklad 1 – Vybavenost provozů – I Příklad 2 – Vybavenost provozů – II Příklad 3 – Odepsanost HIM Příklad 4 – Věková struktura vybavenosti HIM
365 369 371 371 372 375 378
12 Vytváření přehledů Poznámky Příklady Příklad 1 – Rozpočet provozovny Příklad 2 – Náběh hodin na zakázky
383 385 386 386 387
13 Vytváření skupin Poznámky Příklady Příklad 1 – Atraktivní nabídka zájezdů Příklad 2 – Objednávka stavebnin
389 390 391 391 394
14 Komplexní příklady I. Vliv struktury výroby na zisk firmy II. Modely finanční analýzy Finanční analýza 1 Finanční analýza 2 Pyramidální model Du Pontova analýza Informace z finančního trhu
397 398 402 405 405 407 408 409
III. Pracovní pomůcky
410
Diář Prezenční listina Měsíční výkaz práce
410 412 413
IV. Odbourávání alkoholu v krvi
414
11
K1609.indd 11
22.4.2009 10:24:58
Obsah
V. Chod firmy Faktura – neplátce DPH Faktura – plátce DPH Kniha jízd – jeden list Kniha jízd – měsíční (pro plátce DPH) Operativní evidence neplátce DPH Operativní evidence plátce DPH Logistika – skladní karta
12
K1609.indd 12
416 417 418 419 419 420 421 423
Microsoft Excel 2007 pro manažery a ekonomy
22.4.2009 10:24:58
Úvod
Předmluva Kniha je orientována na využití poslední verze Microsoft Excelu v práci manažerů a ekonomů. Předpokladem pro její využití je znalost Excelu 2007, a to v rozsahu základní příručky. Nedržíte tedy v ruce příručku k Excelu, ale náměty k využití Excelu v manažerské a ekonomické praxi podložené velkým množstvím ukázkových příkladů v knize a na přiloženém CD-ROM. Příklady jsou vytvořeny v Microsoft Excelu 2007 s instalovaným Service Pack 1 (SP1), pracujícím pod operačním systémem Windows Vista, edice Business. Excel si lze představit jako pomyslnou dílnu, kde máme rozvěšené různé nářadí. Jde jen o to použít pro vyřešení problému vhodný nástroj. Mnoho situací lze řešit více nástroji, některé jsou pracné, jiné efektní, ale jen některé jsou pro danou situaci efektivní. Cílem knihy je ukázat nástroje Excelu v akci. Na základě analogie potom dát návod, jak ten který nástroj využívat. Každá ze čtrnácti kapitol je strukturována podle jednotné osnovy. Začíná se popisem účelu nástroje, provedením akce, bližším vysvětlením „nástroje“ a poznámkami k použití nástroje. Kniha začíná stručným úvodem k tvorbě modelů a simulací za využití tabulkového kalkulátoru. Každý popisovaný nástroj Excelu je vysvětlen na příkladech. Příklady jsou voleny z různých oblastí rozhodování, marketingu, financí, plánování výroby, logistiky, personalistiky atd. Příklady doplňuje množství obrázků a všechny soubory z kapitol najdete i na CD-ROM. Tyto příklady jsou funkční a graficky upravené k prezentaci. Můžete je po doplnění vašich dat využívat, případně je podle potřeby modifikovat. Jsou vám plně k dispozici. Celkem je v přílohách 103 plně funkčních sešitů, příkladů z knihy, které jsou graficky upravené a mnohdy doplněné dalšími konstrukcemi k ukázce kontextu s ostatními možnostmi Excelu a také ke zvýšení přínosu příkladů. V poslední kapitole se nacházejí komplexní příklady, které využívají více nástrojů najednou – až v tomto vzájemném doplnění se projeví síla Excelu. Závěrem děkujeme za cenné rady a doporučení manažerů – odborníků různých stupňů řízení a firem. Náměty na příklady jsme také čerpali z různých knih a časopisů a převedli je do počítačového zpracování. Partie v knize týkající se finančních funkcí byly zpracovány panem doc. Václavem Bezvodou, kterému tímto navíc děkuji za cenné připomínky k ostatním kapitolám. Milan Brož únor 2009 Milan Brož
[email protected]
Václav Bezvoda
[email protected]
13
K1609.indd 13
22.4.2009 10:24:58
Úvod
Typografická konvence použitá v knize V celé příručce je použito toto grafické odlišení: Tučné písmo
Názvy karet v pásu karet, názvy skupin a tlačítek na těchto kartách a nabídky (příkazy) u nich. Názvy dialogových oken, kategorií a karet v těchto oknech a příslušná tlačítka. Názvy sešitů a listů. Zvýraznění výrazů pro upozornění.
Kurziva
Poznámky a vysvětlivky, odborné termíny, vazby na jiné akce atd.
Courier
Klávesy, např. Enter, Esc, Ctrl, Shift, Del. Doplňující poznámky. Tipy na jednodušší nebo jiné provedení akce. Důležitá upozornění.
Operace se potvrzují klávesou Enter, zruší klávesou Esc, případně jejich ekvivalenty v dialogových oknech a rámečcích – pro potvrzení tlačítky s textem OK, resp. pro zrušení Storno nebo Zavřít. Klávesy ani tlačítka nebudou explicitně uváděny. Bez potvrzení nebo zrušení nelze akci dokončit a šlo by tak o plauzibilní doplňování textu. Výjimkou jsou případy, kdy jsou pro zachování celistvosti výkladu tyto závěrečné úkony doplněny. Názvy dialogových oken a jejich karet, názvy rámečků a grafických tlačítek jsou uváděny tučným písmem. Tam, kde nemůže dojít k záměně, používají se zjednodušené názvy. Pro plný název produktu Microsoft Excel používáme jen Excel a analogicky i pro ostatní aplikace MS Office 2007. Nemůže-li dojít k záměně, není výrobce, firma Microsoft, uváděn. Při odkazech je předpokládán disk C:, na kterém je umístěn jak operační systém Windows Vista, tak i sada Microsoft Office 2007. Při konkrétním použití to však může být jiný disk.
V knize použité názvy programových produktů, firem apod. mohou být ochrannými známkami nebo registrovanými ochrannými známkami příslušných vlastníků. Některé sejmuté obrazovky byly upraveny nebo z nich byl pořízen výřez z důvodu plného využití plochy stránek knihy k zobrazení relevantních informací s ohledem na výklad.
14
K1609.indd 14
Microsoft Excel 2007 pro manažery a ekonomy
22.4.2009 10:24:58
