Obsah
Obsah Úvod Komu je kniha určena Uspořádání knihy
Typografická konvence použitá v knize
11 11 11
12
1 Vybraná témata z Excelu pro techniky Vzorce a funkce pro techniky Vytvoření jednoduchého vzorce Vytvoření technického vzorce s využitím funkcí Relativní, absolutní a smíšená adresace buněk ve vzorcích a funkcích Vložení funkce v Excelu Vybrané funkce a jejich použití Funkce SUMA Funkce PRŮMĚR Funkce MIN Funkce POČET Funkce POČET2 Funkce COUNTIF Funkce COUNTIFS Funkce ABS Funkce ODMOCNINA Funkce PI Funkce SIN Funkce COS Funkce ZAOKROUHLIT Funkce ROK Funkce SUMIF Funkce SUBTOTAL Funkce KDYŽ Funkce SVYHLEDAT Funkce VVYHLEDAT Funkce INDEX Funkce POSUN Funkce VAR Funkce SMODCH Funkce PRŮMODCHYLKA Funkce COVAR Funkce CORREL Funkce LINREGRESE Funkce SOUČIN.SKALÁRNÍ Maticové vzorce Ověřování vstupních dat Podmíněné formátování
13 14 14 15 15 16 18 18 18 18 19 19 19 19 20 20 20 20 21 21 21 21 22 22 23 23 24 25 26 26 26 26 27 27 27 28 29 29
3
K1172.indd 3
2.12.2008 13:12:45
Obsah
Nástroje pro analýzu dat Hledání řešení Řešitel Scénáře Kovariance Korelace Regrese
Jednotný vzhled sešitu a práce se seznamy Motivy Použití motivu Barvy motivů Znaková sada motivů Efekty motivů Uložení a odstranění vlastního motivu Styly tabulky Práce se seznamy
Základy maker a VBA Vytvoření makra Vytvoření vlastní funkce ve VBA Deklarace funkce Název funkce Parametry funkcí Aktivace Editoru Visual Basicu
30 30 30 33 35 35 35
35 36 36 36 37 38 38 38 39
40 40 42 42 43 43 43
2 Grafická znázornění dat Typy grafů Zásady tvorby grafů Terminologie
Tvorba technických grafů Jednoduchý graf Graf výrobního plánu Přidání dat do jednoduchého grafu Kombinování různých typů grafů Časová řada Přerušená datová řada Graf s vedlejší (druhou) osou Y Histogram Srovnávací histogram Grafická analýza naměřených dat Zrychlení na nakloněné rovině Měření rezonanční křivky sériového rezonančního obvodu Klouzavý průměr Chybové úsečky Zobrazení průběhu matematické funkce Zobrazení funkce dvou proměnných Směs ve výsečovém grafu
4
K1172.indd 4
45 46 48 49
50 50 50 51 52 52 53 54 55 57 58 59 60 62 62 63 66 67 Microsoft Excel pro techniky a inženýry
2.12.2008 13:12:45
Obsah
Máme hodně hodnot v grafu Dynamické zobrazení nejnovějších dat z tabulky Vytvoření odkazů na buňku Vložení odkazu do názvu grafu
Formátování grafů Změna kompozice grafu Úprava velikosti měřítka Stupnice grafu Styly Automatické vytváření grafů Víceúrovňový popis kategorií Mřížka grafu Datové řady Zmrazení grafu Viditelnost grafů na listu
69 70 72 72
74 75 76 77 77 78 79 80 81 81 81
3 Technické výpočty v Excelu Výpočty povrchů, objemů a hmotnosti technických těles Vzorový příklad na výpočet povrchu, objemu a hmotnosti obdélníkového bazénu Interpretace výsledků Vytvoření vlastní funkce pro výpočet objemu bazénu ve VBA Vytvoření vlastní funkce pro výpočet povrchu bazénu ve VBA Výpočet objemu a povrchu bazénu pomocí vlastních funkcí Výpočet hloubky bazénu pomocí nástroje Hledání řešení Interpretace výsledků Analýza výsledků s pomocí scénáře Interpretace výsledků Příklad výpočtu povrchu, objemu a hmotnosti zkoseného obdélníkového bazénu Interpretace výsledků Vytvoření vlastní funkce pro výpočet objemu bazénu ve VBA Vytvoření vlastní funkce pro výpočet povrchu bazénu ve VBA Výpočet objemu a povrchu bazénu pomocí vlastních funkcí Výpočet délky bazénu pomocí nástroje Hledání řešení Interpretace výsledků Analýza výsledků pomocí scénáře Interpretace výsledků Příklad výpočtu povrchu, objemu a hmotnosti kruhového bazénu Interpretace výsledků Vytvoření vlastní funkce pro výpočet objemu bazénu ve VBA Vytvoření vlastní funkce pro výpočet povrchu bazénu ve VBA Výpočet objemu a povrchu bazénu pomocí vlastních funkcí Výpočet průměru bazénu pomocí nástroje Hledání řešení Interpretace výsledků Analýza výsledků pomocí scénáře Interpretace výsledků Příklad výpočtu povrchu, objemu a hmotnosti železné koule
83 84 84 87 87 88 89 90 90 91 93 93 96 96 97 97 98 99 99 100 100 102 103 103 104 105 105 105 106 107
5
K1172.indd 5
2.12.2008 13:12:46
Obsah
Interpretace výsledků Vytvoření vlastní funkce pro výpočet objemu koule ve VBA Vytvoření vlastní funkce pro výpočet povrchu koule ve VBA Výpočet objemu a povrchu koule pomocí vlastních funkcí Výpočet průměru koule pomocí nástroje Hledání řešení Interpretace výsledků Analýza výsledků pomocí scénáře Interpretace výsledků
108 108 109 110 111 111 111 112
Technologické výpočty
112
Vzorový příklad technologických výpočtů pro provoz a údržbu zkoseného obdélníkového bazénu Interpretace výsledků Vytvoření vlastní funkce pro výpočet množství tepla k ohřátí vody ve VBA Vytvoření vlastní funkce pro výpočet množství přísady na úpravu pH vody ve VBA Výpočet objemu, povrchu, množství tepla k ohřátí vody a množství přísady pro úpravu pH pomocí vlastních funkcí Výpočet teploty vody pomocí nástroje Hledání řešení Interpretace výsledků Analýza výsledků pomocí scénáře Interpretace výsledků Hlídání doporučených hodnot pomocí podmíněného formátování
Konstrukční výpočty
113 118 118 119 120 121 122 122 123 124
125
Vzorový příklad konstrukčních výpočtů pro zhotovení činky na posilování Výpočet povrchu a hmotnosti kotouče Výpočet povrchu a hmotnosti hřídele Výpočet povrchu a hmotnosti činky Interpretace výsledků Vytvoření vlastní funkce pro výpočet hmotnosti kotouče ve VBA Vytvoření vlastní funkce pro výpočet hmotnosti hřídele ve VBA Výpočet hmotnosti kotouče a hřídele pomocí vlastních funkcí Výpočet průměru kotouče pomocí nástroje Hledání řešení Interpretace výsledků Analýza výsledků pomocí scénáře Interpretace výsledků
125 127 128 128 130 130 131 131 132 133 133 134
4 Evidence technických dat Evidence majetku Vzorový příklad vytvoření evidence majetku Vyhledávání údajů v databázovém seznamu Seřazení údajů v databázovém seznamu Vytváření souhrnů Interpretace výsledků Vytváření skupin Vytváření součtů ve skupinách Vytvoření kontingenční tabulky Vytvoření kontingenčního grafu
6
K1172.indd 6
135 137 137 139 140 141 143 145 146 148 150
Microsoft Excel pro techniky a inženýry
2.12.2008 13:12:46
Obsah
Výběr záznamů pomocí automatického filtru Vytvoření součtu vybraných záznamů pomocí funkce SUBTOTAL Vytvoření součtu pomocí funkce SUMIF Zaokrouhlování číselných hodnot Označení číselných hodnot pomocí podmíněného formátování Zjištění počtu záznamů pomocí funkce COUNTIF Vyhledávání údajů pomocí funkce SVYHLEDAT Vyhledávání údajů pomocí funkce VVYHLEDAT Aktualizace kontingenční tabulky a grafu pomocí maker
Evidence zakázek Vzorový příklad vytvoření evidence zakázek Vyhledávání údajů v databázovém seznamu Seřazení údajů v databázovém seznamu Vytváření souhrnů Interpretace a analýza výsledků Interpretace a analýza výsledků Vytvoření kontingenční tabulky Vytvoření kontingenčního grafu Využití automatického filtru a funkce SUBTOTAL Vytvoření součtu pomocí funkce SUMIF Zjištění počtu záznamů pomocí funkce COUNTIFS Sledování plnění zakázek pomocí funkce KDYŽ
Evidence technické dokumentace Vzorový příklad vytvoření evidence technické dokumentace Vyhledávání údajů v databázovém seznamu Seřazení údajů v databázovém seznamu Výběr záznamů pomocí automatického filtru Vytvoření kontingenční tabulky Zjištění počtu záznamů pomocí funkce COUNTIFS Sledování zápůjček pomocí funkce KDYŽ
152 154 156 157 159 160 162 167 171
172 173 174 174 175 176 180 180 183 184 186 186 188
190 190 190 191 192 192 194 195
5 Statistické zpracování dat Statistické zpracování naměřených dat Vzorový příklad statistického zpracování naměřených dat Výpočet průměrné (střední) hodnoty Výpočet průměrné (střední) hodnoty absolutních odchylek Výpočet rozptylu Výpočet směrodatné odchylky Interpretace a analýza výsledků Grafické znázornění rozptylu naměřených hodnot kolem průměrné hodnoty Příklad porovnání dvou dávkovačů cementu Výpočet průměrné (střední) hodnoty Výpočet průměrné (střední) hodnoty absolutních odchylek Výpočet rozptylu Výpočet směrodatné odchylky Interpretace a analýza výsledků
199 200 201 202 204 204 205 206 206 207 208 209 209 209 210
7
K1172.indd 7
2.12.2008 13:12:47
Obsah
Grafické znázornění rozptylu naměřených hodnot kolem průměrné hodnoty Výpočet základních statistických charakteristik pomocí běžných funkcí a vzorců Výpočet průměrné hodnoty Výpočet rozdílu, absolutní hodnoty a druhé mocniny Výpočet průměrné (střední) hodnoty absolutních odchylek Výpočet rozptylu Výpočet směrodatné odchylky Výpočet rozdílu, absolutní hodnoty a druhé mocniny pomocí maticových vzorců
Zjišťování závislostí mezi několika naměřenými soubory Vzorový příklad zjišťování závislostí naměřených dat Výpočet kovariance Výpočet korelace Interpretace a analýza výsledků Grafické znázornění závislosti naměřených dat Zjišťování závislosti naměřených dat pomocí běžných funkcí a vzorců Výpočet průměrné hodnoty a směrodatné odchylky Výpočet kovariance Výpočet korelace Výpočet rozdílu a součinu pomocí maticových vzorců Příklad vyšetření závislosti spotřeby topného oleje Výpočet kovariance Výpočet korelace Interpretace a analýza výsledků Grafické znázornění závislosti spotřeby topného oleje na teplotě Grafické znázornění závislosti spotřeby topného oleje na velikosti obytné plochy Řešení pomocí nástroje Analýza dat
Proložení experimentálních dat odpovídající funkcí
K1172.indd 8
215 217 218 218 219 219 220 220 222 222 223 223 224 225 225 225 226 226
228
Vzorový příklad proložení experimentálních dat odpovídající funkcí Výpočet kovariance a korelace Interpretace výsledků Grafické znázornění závislosti Y na X Proložení naměřených dat odpovídající funkcí Interpretace a analýza výsledků Grafické znázornění rozptylu naměřených hodnot Výpočet hodnoty funkce a druhé mocniny rozdílu pomocí maticových vzorců Řešení pomocí funkce LINREGRESE Interpretace výsledků Řešení pomocí nástroje Analýza dat Interpretace výsledků Příklad na vyšetření závislosti spotřeby paliva u automobilu na jeho rychlosti Výpočet kovariance a korelace Interpretace výsledků Grafické znázornění závislosti Y na X Proložení naměřených dat odpovídající funkcí Interpretace a analýza výsledků Grafické znázornění rozptylu naměřených hodnot Výpočet hodnoty funkce a druhé mocniny rozdílu pomocí maticových vzorců
8
210 212 212 213 214 214 214 214
229 230 231 231 231 234 235 236 236 238 239 240 240 241 242 242 243 245 246 247
Microsoft Excel pro techniky a inženýry
2.12.2008 13:12:47
Obsah
6 Matematické modelování v technické praxi Modely výrobních procesů Formulace modelu výrobního procesu Vzorový příklad vytvoření matematického modelu výroby Ekonomický model Matematický model a jeho řešení Ekonomická interpretace a analýza výsledků Analýza výsledků za pomocí scénáře Řešení pomocí funkce KDYŽ Řešení pomocí maticových vzorců
Modely přepravy zboží Vzorový příklad vytvoření matematického modelu přepravy zboží Ekonomický model Matematický model a jeho řešení Ekonomická interpretace a analýza výsledků Řešení pomocí funkce KDYŽ Řešení pomocí skalárního součinu
Optimalizační modely Formulace optimalizačního modelu Vzorový příklad optimalizace výroby Ekonomický model Matematický model a jeho řešení Ekonomická interpretace a analýza výsledků Analýza výsledků pomocí scénáře a uložení nastavení Řešitele Řešení pomocí maticových vzorců Kapacitní úloha s vnitřní vazbou Ekonomický model Matematický model a jeho řešení Ekonomická interpretace a analýza výsledků Řešení pomocí maticových vzorců
249 250 251 252 254 256 262 264 266 267
268 269 269 270 279 279 281
282 282 284 284 285 289 291 294 295 295 296 301 305
7 Optimalizace v Excelu Kapacitní úlohy Úlohy o optimálním dělení materiálu Směšovací úlohy Distribuční úlohy
Optimalizace výroby Úloha na dosažení minimálních nákladů na výrobu Ekonomický model Matematický model a jeho řešení Ekonomická interpretace a analýza výsledků Řešení pomocí maticových vzorců
307 308 308 308 308
309 309 310 311 320 321
9
K1172.indd 9
2.12.2008 13:12:47
Obsah
Optimální rozřezání tyčí a prken na požadované rozměry Úloha na optimální rozřezání tyčí Ekonomický model Matematický model a jeho řešení Ekonomická interpretace a analýza výsledků Řešení pomocí maticových vzorců
Optimální namíchání různých směsí Úloha na sestavení krmné směsi Ekonomický model Matematický model a jeho řešení Ekonomická interpretace a analýza výsledků Řešení pomocí maticových vzorců
Optimalizace přemísťování objektů Úloha na přemístění strojů Ekonomický model Matematický model a jeho řešení Ekonomická interpretace a analýza výsledků Řešení pomocí skalárního součinu
Optimalizace přepravy zboží Úloha na přepravu písku Ekonomický model Matematický model a jeho řešení Ekonomická interpretace a analýza výsledků Řešení pomocí skalárního součinu
Rejstřík
10
K1172.indd 10
322 323 323 324 330 332
332 333 333 334 339 340
340 341 341 342 348 348
349 349 350 351 357 358
361
Microsoft Excel pro techniky a inženýry
2.12.2008 13:12:48
Úvod
Úvod Excel je jeden z nejrozšířenějších programů, který je využíván ve firmách i pro soukromou potřebu. V Excelu jsou zpracovávány různé databázové seznamy, statistické a technické výpočty apod. Znalost Excelu je vyžadována téměř při všech výběrových řízeních pracovníků na ekonomické a technické profese. Tato kniha je napsaná pro Microsoft Excel 2007. U nižších verzí Excelu budou odlišnosti pouze ve způsobu používání nabídek.
Komu je kniha určena Kniha je určena zejména technikům, kteří chtějí Excel využívat nejen pro tvorbu databázových seznamů, ale také pomocí Excelu provádět technické výpočty a statistické zpracování dat. Kniha obsahuje kapitoly, které mohou posloužit i čtenářům, kteří nejsou technicky zaměřeni (například manažerům). Použití vybraných funkcí a nástrojů Excelu je vysvětleno na praktických příkladech.
Uspořádání knihy Kniha je členěna do jednotlivých kapitol, které tvoří samostatný celek. Pouze kapitola 7 navazuje na kapitolu 6. Kapitoly 1 a 2 lze chápat jako úvodní kapitoly, ve kterých jsou vybraná témata z Excelu pro techniky. Některá témata v těchto kapitolách byla zpracována na základě knih o Excelu, jejichž autorem je Milan Brož, kterému patří poděkování za inspiraci k napsání prvních dvou kapitol. V první kapitole naleznete popis všech funkcí a analytických nástrojů, které jsou využívány při řešení praktických úloh. Jsou to zejména: vzorce a funkce, analytické nástroje, práce se seznamy, šablony motivů tabulek, základy maker a VBA. Druhá kapitola se zabývá grafickým zobrazením dat. Je zaměřena zejména na tvorbu technických grafů, volbu měřítek a formátování. Tvorba grafů je vysvětlena na praktických příkladech. Ve třetí kapitole jsou na příkladech z technické praxe ukázány možnosti provádění technických výpočtů v Excelu. Při technických výpočtech jsou využívány vzorce, funkce a analytické nástroje. Pro pokročilejší uživatelé je ukázáno využití vlastních funkcí ve VBA. Čtvrtá kapitola se věnuje evidenci technických dat. V každé firmě se evidují data, která jsou pro ni důležitá. Malé a střední firmy používají často k evidenci dat Excel, který poskytuje velké množství prostředků pro práci s databázovými seznamy. V této kapitole je na praktických příkladech ukázáno využití důležitých databázových funkcí a nástrojů. V páté kapitole je ukázána možnost statistického zpracování naměřených dat. Jsou v ní vysvětleny: základní statistické charakteristiky pro zpracování technických dat, základní statistické charakteristiky pro zjišťování závislostí mezi několika naměřenými soubory, základní statistické charakteristiky pro proložení experimentálních dat odpovídající funkcí. Tyto statistické charakteristiky jsou využity při řešení praktických příkladů.
11
K1172.indd 11
2.12.2008 13:12:48
Typografická konvence použitá v knize Úvod
Šestá kapitola se zabývá využitím matematických modelů v technické praxi. Pomocí matematických modelů lze získávat nezbytné informace o pracovním procesu a na základě těchto informací se správně rozhodovat. Tvorba matematických modelů je ukázána na příkladech z praxe. Sedmá kapitola navazuje na kapitolu šestou a je zaměřena na optimalizační modely. Pomocí optimalizačních modelů lze: najít optimální výrobní program, optimálně nařezat materiál pro výrobu (například tyče), optimálně namíchat různé směsi, optimálně přepravit stroje na nová stanoviště, optimálně přepravit zboží od dodavatelů k odběratelům. V této kapitole jsou řešeny praktické příklady ze všech výše uvedených oblastí. Předpokládané znalosti. Kniha se nezabývá výukou základů Excelu – je určená technikům, kteří mají základní znalost Excelu. Z oblasti matematiky jsou předpokládány znalosti ze střední školy. Poděkování patří spoluautorovi Pavlu Simrovi, který vypracoval druhou kapitolu a vyhotovil schémata k praktickým příkladům. I přes péči, která byla věnována tvorbě této publikace, nelze vyloučit možnost výskytu chyb. Autor proto nepřebírá žádné záruky ani právní odpovědnost za využití uvedených informací a z toho plynoucích důsledků. Veškeré osoby a jména uvedená v této knize jsou pouze ilustrativní a fiktivní a jakákoliv podobnost s osobami žijícími je čistě náhodná. V knize jsou použity zjednodušené praktické příklady, které mají výukový charakter. V příkladech jsou použita modelová data. Jiří Barilla
Typografická konvence použitá v knize V celé příručce je použito toto grafické členění: Tučné písmo
Prvky grafického uživatelského rozhraní (příkazy, tlačítka apod.)
Kurzíva
Důležité výrazy v rámci textu.
Speciální symboly: Poznámky Tipy Důležitá upozornění a varování
12
K1172.indd 12
Microsoft Excel pro techniky a inženýry
2.12.2008 13:12:49
