Úvod Úvodem si stručně připomeneme některé pro matematické aplikace užitečné nástroje Excelu, které budeme dále využívat. Předpokládáme, že účastníci kurzů P-Mat se základům ovládání Excelu naučili již v kurzech Z a P0. Nazývání Jedním z nástrojů Excelu, který můžeme využít, je Název. Slouží k označení množiny hodnot jedním názvem. Nejčastější využití je v případě sloupce či řádku. Např. máme-li proměnné k, l, m a jejich hodnoty zapsané ve třech sloupcích a chceme vypočítat výraz k + l – m, museli bychom tento výraz zapsat pomocí odkazu na příslušné buňky. Můžeme si ovšem také jednotlivé sloupce nazvat. Sloupec s hodnotami k jako k, sloupec s hodnotami l jako l a totéž i s m.
Ve sloupci A máme hodnoty proměnné k. V buňce A1 je symbol proměnné k, v buňce A2 je její první hodnota 3, v buňce A3 další hodnota atd. Totéž platí pro sloupec B a proměnnou l a sloupec C s proměnnou m. Budeme-li nyní chtít ve sloupci D vyjádřit výraz k + l – m, jednou z možností je, že zapíšeme do buňky D2 vzorec =A2+B2-C2. Do buňky D3 vzorec =A3+B3-C3 atd. (Tento vzorec samozřejmě můžeme do dalších buněk rozkopírovat.)
Další možností jsou názvy proměnných. Označíme sloupec A a spustíme volbu Vložit - Název - Definovat... Objeví se dialogové okno Definovat název, ve kterém se již předvyplnily některé položky.
První předvyplněnou položkou je název proměnné (v našem případě k), kterou lze v případě potřeby přepsat názvem jiným. Druhou položkou je Odkaz na obsahující odkaz na buňky, které budou názvem zastřešeny. Po stisknutí OK je název definován. Stejně nazveme i hodnoty v sloupci B a C. (Někdy můžeme při definování názvu (např. c) narazit na problém, který se projeví upozorněním ‚Tento název není platný‘, neboť některé názvy již mají v Excelu svůj význam a nelze je použít k jiným účelům.)
Když nyní chceme vyjádřit výraz k + l – m ve sloupci E, zapíšeme do buňky E2 vzorec =k+l-m, do buňky E3 vzorec =k+l-m atd. (Vzorec samozřejmě do dalších buněk rozkopírujeme.)
Za hodnoty k, l, m se automaticky dosadí příslušné hodnoty ze sloupců A, B, C. Takto zapsaný vzorec je pak výrazně přehlednější než vzorec zapsaný odkazem na jednotlivé buňky. Poučení: Názvy zpřehledňují zápis vzorců. Některé názvy jsou blokované.
Rozkopírovávání buněk Máme-li v buňce nějakou hodnotu, můžeme tuto hodnotu překopírovat do dalších buněk. 1. Stačí buňku označit, pomocí volby Úpravy – Kopírovat zkopírovat. Tím se údaje z buňky přenesou do schránky. Nyní označíme buňku nebo buňky, do kterých chceme kopírovaný údaj přenést, a volbou Úpravy – Vložit vložíme kopii do označených buněk. Ovšem vzorce se při kopírování chovají různě. 2. Další metodou je „roztahování“ hodnoty buňky. Chceme-li např. vyplnit do sloupce A řadu čísel od 1 do 20, napíšeme do buňky A1 číslo 1.
Pak uchopíme buňku za malý čtverčík v pravém dolním rohu a táhneme myší směrem k buňce A20. Při tažení se kurzor myši změnil na černý křížek, vedle kterého se zobrazuje hodnota informující o budoucím obsahu buňky. V našem případě je vedle křížku neustále jednička. Pokud bychom nyní pustili tlačítko myši na buňce A20, ve všech předchozích buňkách bude zkopírovaná hodnota 1.
My jsme ale chtěli číselnou řadu 1 až 20, proto se ještě vrátíme k držení tlačítka myši a stlačíme klávesu CTRL. To se projeví změnou čísla vedle černého křížku a k černému křížku přibude malé +. Pokud nyní pustíme na buňce A20 tlačítko myši při stlačené klávese CTRL, vyplní se do předchozích buněk vzestupná číselná řada a buňce A20 bude číslo 20. Podobným způsobem lze vyplňovat jiné řady (lze je nadefinovat v Nástroje – Možnosti… – Vlastní seznamy), přičemž klávesa CTRL slouží jako přepínač mezi kopírováním buňky či vyplňováním řad.
Pro kopírování či „roztahování“ vzorců platí následující pravidlo: Pokud ve vzorci používáme odkaz na buňku ve formátu ‚sloupec|řádek‘ (A1; G3; W20 apod.), bude se vzorec dynamicky měnit podle polohy kopírované buňky vůči buňce, do které vzorec vkládáme. Například máme v buňce A1 vzorec =A2+A3. Pokud tento vzorec zkopírujeme do buňky B1, tedy o jeden sloupec vedle, změní se i vkládaný vzorec =B2+B3. Pokud týž vzorec zkopírujeme do buňky A3, posune se vzorec o dva řádky dolů, tedy na tvar =A4+A5. Takže určitě již víte, co se objeví za vzorec v buňce C4 (řešení: =C5+C6).
Někdy se nám tato vlastnost tzv. relativního adresování nehodí. Například máme v jedné buňce údaj, na který chceme odkazovat ve všech vzorcích. K tomu slouží znak $, který funguje jako zámek příslušné souřadnice buňky (tzv. absolutní adresování). Pro buňku A1 tedy může dojít ke třem stavům $A$1, A$1 a $A1 (mezi jednotlivými tvary lze přepínat při editování vzorce klávesou F4).
Příklad: 1⎛ a⎞ a : xn +1 = ⎜ xn + ⎟ . 2⎝ xn ⎠ Do buňky A2 zapíšeme libovolné číslo (např. 49) a do buňky B2 zapíšeme pro kontrolu vzorec pro výpočet odmocniny =ODMOCNINA(A2), který v našem případě dá výsledek 7. Hodnota x1 je libovolné číslo, které si zvolíme v buňce B4 (např. 60) Do buňky B5 zapíšeme rekurzivní vzorec =(B4+A2/B4)/2. Pokud tento vzorec rozkopírujeme, zjistíme, že je něco špatně. Ve vzorečku je hodnota a, která je uložena v buňce A2. Proto musí všechny kopie ukazovat stále na buňku A2 potažmo $A$2. Správný vzorec tedy bude =(B4+$A$2/B4)/2.
Pro výpočet druhé odmocniny existuje rekurzivní vzoreček:
Poznámka: Nyní je zajímavé pozorovat různou rychlost konvergence pro různé počáteční hodnoty x1. Existuje podobný rekurzivní vzoreček pro výpočet třetí odmocniny: 1⎛ a ⎞ 3 a : xn +1 = ⎜ 2 xn + 2 ⎟ . Ale to už si vyzkoušejte sami. xn ⎠ 3⎝ Poznámka: Oba rekurzivní vzorce dostaneme z Newtonovy metody tečen pro řešení rovnice f(x) = 0 specializací f(x) = x2 – a, resp.f(x) = x3 – a. Poučení: Pozor při tvorbě vzorce na správné nastavení adresace buňky.
Správná volba grafu Při tvorbě grafu je velice důležité zvolit správný typ. Excel nabízí několik typů, ale pro graf jako množinu bodů se hodí jen dva typy: spojnicový a XY bodový.
Na příkladu si ukážeme, jaký je mezi nimi rozdíl. Máme funkci f(x): x 2 − x − 6 . Určíme si definiční obor 〈-10; 10〉 a dopočítáme příslušné hodnoty f(x). Do sloupce A napíšeme vybrané hodnoty x a do sloupce B f(x).
Označíme buňky A1:B22 a spustíme nástroj Vložit – Graf… Zobrazí se dialogové okno Průvodce grafem. Nejprve vybereme Spojnicový typ ‚Spojnicový. Zachycuje trend za určitou dobu nebo pro různé kategorie.‘ a klepneme na Dokončit.
Jako druhý graf vložíme XY bodový typ ‚Bodový s datovými body spojenými pomocí hladkých spojnic a bez značek‘.
Teď můžeme porovnávat oba získané grafy. V prvním případě se nevytvořil jeden graf, ale grafy dva. Přibyl jakýsi graf funkce x. I graf funkce f(x) je podivný, neboť z grafu to vypadá, že pro x = 9 je f(x) přibližně 0, ale podle výpočtu vychází hodnota 66. V čem je tedy problém? Graf XY bodový bere vždy první sloupec jako hodnoty osy x, jako definiční obor. Zbývající sloupce pak zobrazuje jako množiny bodů [x; f(x)]. Naproti tomu Spojnicový graf bere už první sloupec jako funkční hodnoty a x-ová souřadnice je dána pořadím hodnot. Dalším výstižným vyjádřením rozdílu mezi Spojnicovým a XY bodovým grafem je následující příklad převzatý z [1].
Poučení: Při vykreslování grafu funkce používat graf XY bodový. Malá konečná množina bodů určující graf může vést k podstatnému zkreslení (např. nespojitost, vynechání nějaké významné kličky apod.)
Hledání řešení rovnic Excel nabízí přibližné hledání řešení rovnic. Máme-li číslo, které vstupuje jako proměnná do nějakých vzorců, můžeme Excelu zadat, aby měnil toto číslo tak dlouho, dokud výsledek nedosáhne nějaké námi zadané hodnoty, nebo hodnoty, která se zadané velmi blíží. Jako příklad si můžeme vzít rovnici 3x2 − 1 = 0. Do buňky A1 napíšeme libovolnou hodnotu pro x, např. 2. Do buňky B2 zapíšeme levou stranu naší rovnice =3*A1*A1-1, vyjde 11.
Nyní spustíme nástroj Nástroje - Hledání řešení... Objeví se dialogové okno Hledání řešení. My chceme, aby Excel měnil tak dlouho hodnotu buňky A1, dokud nebude buňka B2 rovna 0. Proto do políčka Nastavená buňka zadáme B2, do políčka Cílová hodnota číslo 0 a do kolonky Měněná buňka A1.
Po spuštění se objeví okno Stav hledání řešení, ve kterém vidíme, že Excel došel v buňce B2 k hodnotě 0,00011054, což je číslo velmi blízké 0.
A v buňce A1 se objevilo číslo 0,577382175446515. Víme ale, že naše rovnice má kořeny dva, a to ± 13 (tj. ± 0,5773502691). Excel hledá řešení v okolí měněného čísla (v našem případě čísla v buňce A2) a už se nezajímá o to, zda existuje i jiné řešení. Druhý kořen bychom touto metodou získali vhodným zvolením čísla v buňce A1. (Zkuste do buňky zadat číslo -2.) Dalším nedostatkem je přesnost řešení. V našem řešení je výsledek správný do čtvrtého desetinného místa. Poučení: Tento způsob není moc přesný, slouží spíše k hrubé orientaci.
Příklady Řešení soustavy dvou rovnic o dvou neznámých Řešte v množině reálných čísel soustavu rovnic y = x 2 − x − 6 a y = 4x − 134 . Ukážeme si dvě možnosti řešení. První řešení bude grafické a druhé využije nástroje Hledání řešení. Grafické řešení Máme rovnice y = x 2 − x − 6 a y = 4x − 134 a chceme najít jejich společné řešení. Zobrazíme si
graf funkce f1(x): x 2 − x − 6 a f2(x): 4x − 134 a jejich průsečíky budou hledanými hodnotami. Řešení rozdělíme do několika kroků: - Odhadnutí definičního oboru funkce. - Dopočítání příslušných hodnot na ose y. - Vytvoření grafů pomocí nástroje Průvodce grafem. - Odečtení hodnot. Jako definiční obor si zvolíme interval 〈-10; 10〉. Napíšeme hodnoty do aktivního listu. Do buňky A1 napíšeme x a do buněk A2 – A22 hodnoty -10 až 10 s krokem 1. Pomocí nástroje Definovat název nazveme tyto hodnoty x. (Označíme buňky A1 až A22 a v nabídce Vložit vybereme Název – Definovat…, nebo použijeme klávesovou zkratku CTRL+F3.) Nyní se na tyto hodnoty můžeme odkazovat jako na x, čehož využijeme při počítání hodnot na ose y. Do buňky B1 vepíšeme f1(x) a do buňky B2 již zapíšeme vzorec pro výpočet =x*x-x-6. Tento vzorec rozkopírujeme do ostatních buněk B3 – B22. Do buňky C1 vepíšeme f2(x) a do následujících buněk C2 – C22 zapíšeme vzorec =4*x-13/4. Teď již můžeme sestrojit graf.
Označíme buňky A1:C22 a spustíme Průvodce grafem. Vybereme správný graf (více viz Správná volba grafu), tedy XY bodový a jeho poddruh ‚Bodový s datovými body spojenými pomocí hladkých spojnic a bez značek‘. Po klepnutí na Dokončit získáme hledané grafy funkcí.
V této chvíli již můžeme přibližně odečíst souřadnice průsečíků grafů. Lepšího výsledku ale dosáhneme, když sestrojíme graf rozdílu obou funkcí. Průsečíky tohoto třetího grafu s osou x budou hledaná x-ová řešení. Do buňky D1 proto napíšeme f1(x) - f2(x) a do buňky D2 vzorec =B2-C2, který rozkopírujeme do buněk D3 – D22.
Sestrojíme graf pouze této funkce označením buněk A1:A22 a D1:D22 a použitím nástroje Průvodce grafem - XY bodový. Nyní jsme lépe schopni odečíst hledané hodnoty jako průsečíky grafu s osou x. (Vypadá to na dvě řešení v případě prvního zhruba -1 a případě druhého zhruba 6.)
Řešení pomocí nástroje Hledání řešení
K přesnějšímu zjištění hodnot x1 a x2 nám poslouží nástroj Hledání řešení v nabídce Nástroje. (Více viz Použití nástroje Hledání řešení). Ve sloupci D najdeme buňky, kde se kladná hodnota mění na zápornou a naopak. Tedy místo kdy graf funkce přechází z oblasti nad osou x pod osu x a tudíž (ve většině případů) ji protíná. V našem případě se jedná o buňky D11, D12 a D17, D18. Řešení jsou tedy dvě.
Hledáme tedy takové x, jehož hodnota f1(x) - f2(x) je rovna 0. Spustíme nástroj Nástroje − Hledání řešení a do políčka Nastavená buňka zadáme D11. Jako cílovou hodnotu buňky D11 zadáme 0 a za měněnou buňku dáme příslušnou hodnotu x tedy buňku A11.
Po stlačení OK se spustí hledání. Po chvíli je nalezeno přibližné řešení -0,500000646349848 (buňka A11) a hodnota funkce f1(x) - f2(x) je nyní 3,8781E-06. (Když v buňce A11 přepíšeme hodnotu na -0,5, bude hodnota funkce f1(x) - f2(x) rovna 0, neboť -0,5 je opravdu hledaným řešením.) Totéž provedeme i pro buňku D17, kde po zadání do nástroje Hledání řešení získáme x = 5,49997356845444 a tím hodnotu funkce -0,000158589, tedy číslo blízké nule. (Ano, druhým řešením je 5,5.)
Řešením soustavy rovnic y = x 2 − x − 6 a y = 4x − 134 je [-0,5; − 214 ] a [5,5; Poučení: Než začnete kreslit graf, zjistěte si jeho definiční obor. Výsledky takto získané jsou většinou jen přibližné.
75 4
]
Pascalův trojúhelník
V následujícím příkladu si ukážeme několik způsobů získání Pascalova trojúhelníku. Postupy lze vyjádřit následovně: 1. faktoriál:
n! ; k !( n − k ) !
⎛n⎞ 2. kombinační číslo: ⎜ ⎟ ; ⎝k ⎠ +b . 3. součtová metoda: a{ c Do buňky A1 napíšeme n a do buněk A2 – A22 vyplníme řadu čísel 0 – 20 (viz Rozkopírovávání buněk). Do buňky B1 napíšeme k a buňky C1 – W1 vyplníme také řadou čísel 0 – 20. Sloupec A nazveme n (viz Nazývání) a řádek 1 nazveme k. Do buňky C2 napíšeme vzorec =FAKTORIÁL(n)/ FAKTORIÁL(k)/ FAKTORIÁL(n-k) [nebo pro druhý případ =KOMBINACE(n;k)]. Tyto oba vzorce ale mají smysl pouze pro k ≤ n. Proto doplníme vzorec na =KDYŽ(k<=n;FAKTORIÁL(n)/ FAKTORIÁL(k)/ FAKTORIÁL(n-k);"") [pro druhý případ =KDYŽ(k<=n;KOMBINACE(n;k);""]. Tento vzorec rozkopírujeme do celé oblasti C2:W22. Tím získáme Pascalův trojúhelník. Je dobré nyní celý list označit a využít volbu Formát − Sloupec – Přizpůsobit čímž nejlépe využijeme místo k zobrazení trojúhelníka.
V Pascalově trojúhelníku platí, že součet řádku n je roven 2n. O tom se můžeme přesvědčit. Využijeme k tomu sloupce X a Y. Do buňky X2 zapíšeme vzorec =SUMA(C2:W2), který rozkopírujeme až k buňce X22. Do buňky Y2 napíšeme druhý vzorec, tedy =2^n. Ten také rozkopírujeme až buňce Y22. Hodnoty v obou sloupcích se skutečně shodují.
Další zajímavostí je, že všechny prvky n-tého řádku Pascalova trojúhelníka (kromě krajních 1) jsou dělitelné prvočíslem p, právě když n = p k . Např. p = 2 by pro všechny prvky řádků n = 2; 4; 8; 16… by měly být sudé (kromě krajních 1).
Pro p =3 by měly být prvky řádků 3, 9, 27… dělitelné 3.
Třetí metoda je nejčastěji používána na získání trojúhelníka, kde další kombinační číslo je 1 1 1 1 2 1 1 3 3 1 4 6 4 1 . Pokuzískáno jako součet dvou čísel ležících těsně nad ním: 1 síme se takový trojúhelník v Excelu napodobit. První překážkou, na kterou narazíme je, že v Excelu máme údaje v řádcích a sloupcích jinak uspořádány. Toto vyřešíme "natočením" 1 1 1 1 1 1 2 3 4 1 3 6 1 4 . Do buňky B2 napíšeme 1. To bude vrchol trojúhelníka. Další trojúhelníka: 1 údaj v buňce B3 získáme jako součet jeho předchůdců, tedy vzorec =A3+B2 (zde využíváme toho, že prázdné buňky v řádku 1 a sloupci A mají implicitně hodnotu 0, proto jsme umístili vrchol až do buňky B2). Vzorec v buňce C2 bude mít tvar =B2+C1.
Nyní vidíme, že vzorec se opakuje a můžeme ho rozkopírovat do celé oblasti B2:U22, ovšem bez buňky B2. Po rozkopírování vzorce oblast ještě jednou označíme a volbou Formát − Sloupec – Přizpůsobit nastavíme přiměřenou šířku sloupců.
Tak a máme trojúhelník, abychom ho ještě více přiblížili „typickému“ Pascalovu trojúhelníku, otočíme hodnoty v buňkách o 45° označením buněk, spuštěním volby Formát – Buňky…, vybráním záložky Zarovnání a nastavením Orientace buňky na 45 stupňů. Pokud se nyní podíváme na obrazovku s hlavou skloněnou nalevo, vidíme typický Pascalův trojúhelník.
V takto sestrojeném trojúhelníku platí několik zajímavých pravidel: - součet členů řádku až k libovolnému členu je roven číslu pod posledním členem;
-
součet členů sloupce až k libovolnému členu je roven číslu vpravo od posledního členu;
-
součet všech členů v obdélníkové oblasti ohraničené levým horním rohem (B2) a libovolným členem je o jedničku menší než číslo vpravo dole od posledního členu.
Poznámka: Součet (jakož i minimum, maximum, nebo průměr) označených buněk nalezneme v pravé části stavového řádku. Další zajímavost Pascalova trojúhelníka se týká lichosti a sudosti jeho prvků. Pomocí Podmíněného formátování odlišíme sudé prvky od lichých. Označíme buňku B2 našeho aktuálního Pascalova trojúhelníka, spustíme nástroj Formát – Podmíněné formátování…
V okně Podmíněné formátování zadáváme podmínky, které chceme v buňce sledovat. Při splnění podmínky je buňka naformátována zvoleným formátem. My chceme sledovat sudost buňky, čehož dosáhneme např. vzorcem =CELÁ.ČÁST(B2/2)*2=B2. Pokud je tento vzorec splněn, je číslo v buňce B2 sudé a je na ni aplikován formát, který nastavíme v dialogovém okně Formát buněk po stlačení tlačítka Formát… My si nastavíme na záložce Vzorky oranžové stínování.
Odsouhlasíme všechny změny a vrátíme se do buňky B2. Nic se nestalo, neboť podmíněný formát je zatím nastaven pouze v této buňce a její hodnota je 1, tedy číslo liché. Nyní potřebujeme formát rozkopírovat do ostatních buněk. Zkopírujeme buňku B2, označíme oblast B2:U22 a vložíme nástrojem Úpravy – Vložit jinak... – Formáty nastavený formát.
Teď už se věci dějí. Vypadá to zajímavě, ale chtělo by to vidět větší část Pascalova trojúhelníka.
Ještě trochu upravíme formátovací podmínku, neboť nás nezajímá, jaké konkrétní číslo je v buňce, ale pouze zda je liché či sudé. Označíme buňku B2 a spustíme Podmíněné formátování. Klikneme na Formát… a na záložce Písmo nastavíme barvu písma na oranžovou. Vrátíme se do okna Podmíněné formátování a po klepnutí na tlačítko Přidat >> přidáme podmínku pro lichost =CELÁ.ČÁST(B2/2)*2<>B2.
U této podmínky nastavíme barvu písma i barvu stínování na bílou. Takto aktualizovaný formát rozkopírujeme do celé oblasti B2:U22 a celá oblast se změní pouze na bílé a oranžové buňky. Teď už jen zbývá rozkopírovat Pascalův trojúhelník do větší oblasti (např. B2:BW50). Dále zmenšíme velikost buněk tak, že označíme celý list a nastavíme šířku sloupce na 0,83 (Formát – Sloupec – Šířka…) a výšku řádku na 7,5 (Formát – Řádek – Výška…).
Tím získáme pohled na větší Pascalův trojúhelník.
Pravděpodobně nás zaujme počáteční trojúhelníková pravidelnost, která ovšem v pravé dolní části mizí. Rozložení sudých a lichých prvků v Pascalově trojúhelníku je opravdu tak pravidelné, jak naznačují oranžové trojúhelníky. Problém nastává u čísel delších než 15 číslic, což je maximální přesnost Excelu. U větších čísel již dochází k zaokrouhlování a tím k nezachování sudosti resp. lichosti čísla. Poučení: Pozor na přesnost výsledků u velkých čísel. Nápověda MS Excel: V aplikaci Excel jsou čísla uložena s přesností 15 platných číslic a s touto přesností se také provádějí výpočty.
Házení kostkou
Excel je vhodným prostředkem ke zpracovávání dat. Jedním z příkladů je hod kostkou. V této úloze: 1. nasimulujeme hod kostkou; 2. zjistíme četnosti jednotlivých hodnot; 3. a zobrazíme si graf četností Ke generování náhodných hodů užijeme funkci NÁHČÍSLO(), které vrací náhodné číslo z intervalu 〈0; 1). My ale chceme čísla 1 až 6. Proto získané náhodné číslo vynásobíme 6. Ještě z něj uděláme číslo celé, čímž získáme čísla 0 až 5, proto k číslu ještě přičteme jedničku. Výsledný vzorec má tvar =CELÁ.ČÁST(NÁHČÍSLO()*6)+1. Zapíšeme ho do buňky A1 a rozkopírujeme třeba až do buňky A7000. Tím jsme získali 7000 náhodných hodů kostkou. Zde je důležité upozornit na užitečnou vlastnost funkce NÁHČÍSLO(). Pokud se cokoliv na aktivním listu změní, vygenerují se nové náhodné hodnoty. Jestliže chceme vygenerované hodnoty zmrazit, označíme celý sloupec A, zkopírujeme ho a pomocí volby Úpravy – Vložit jinak… – Vložit – Hodnoty vložíme např. do sloupce B.
K zobrazení četností hodnot použijeme funkci ČETNOSTI(sledovaná data; sledované hodnoty). Do buněk C3 až C8 vypíšeme hodnoty 1, 2, 3, 4, 5, 6. Do buněk D3 až D8 chceme vypsat, kolikrát se příslušné číslo v náhodném hodu vyskytlo. Proto vzorec zapíšeme jako matici. Označíme oblast D3:D8 a napíšeme vzorec =ČETNOSTI(B:B;C3:C8) a stlačíme kombinaci kláves CTRL+SHIFT+ENTER. U pomalejších počítačů se až po chvíli objeví počet výskytů jednotlivých čísel. Pro kontrolu si můžeme v buňce D9 sečíst počty výskytů =SUMA(D3:D8), vyjde 7000.
Dále můžeme sledovat v buňce D10 maximum (=MAX(D3:D8)) a v buňce D11 minimum (=MIN(D3:D8)), nebo v buňce D12 průměrnou hodnotu všech hodů (=PRŮMĚR(B:B)), která by se měla blížit číslu 3,5.
Sestrojíme si graf četností. Označíme oblast D3:D8 a spustíme Průvodce grafem. Vhodným grafem bude např. Sloupcový skupinový.
Získáme sloupcový graf, na kterém nás pravděpodobně při povrchním pohledu zarazí, že jsou tak veliké rozdíly mezi četnostmi jednotlivých hodnot. Podle teorie pravděpodobnosti by rozdíly neměly být tak velké. Je to způsobeno měřítkem grafu. Při pohledu na osu y vidíme, že nezačíná od nuly. Toto odstraníme klepnutím pravým tlačítkem myši na osu y a vybráním položky Formát osy… Zde vybereme záložku Měřítko a hodnotu v okně Minimum přepíšeme na 0.
Nyní již graf odpovídá očekávání.
Poučení: Při zobrazování grafu může dojít ke zkreslení nevhodným nastavením měřítka os.
Fibonacciho čísla
Tato čísla jsou definovaná jako číselná řada a0 , a1 , a2 K , kde an = an −1 + an − 2 , a0 = 0, a1 = 1. Vytvoříme mezi buňkami A2 až A47 číselnou řadu čísel od 0 do 45. V buňce A2 tedy bude číslo 0 a v buňce A47 číslo 45. V buňce B2 bude prvek a0 , číslo 0, v buňce B3 bude prvek a1 , číslo 1 a do buňky B4 již můžeme zapsat vzorec =B2+B3, do buňky B5 vzorec =B3+B4. Vzorec rozkopírujeme až do buňky B47. Tím jsme získali řadu tzv. Fibonacciho čísel.
n
n
⎛ 1+ 5 ⎞ ⎛ 1− 5 ⎞ ⎜ ⎟ −⎜ ⎟ 2 2 ⎠ ⎝ ⎠ ⎝ . Ihned ho Jednotlivá čísla můžeme také vypočítat pomocí vzorce Fn = 5 vyzkoušíme. V buňce H4 si zvolíme n a do buňky H5 napíšeme následující vzorec =(((1+ODMOCNINA(5))/2)^H4-((1-ODMOCNINA(5))/2)^H4)/ODMOCNINA(5). Pokud změníme hodnotu čísla n, tedy hodnotu buňky H4, vidíme, že výsledná hodnota v buňce H5 souhlasí v příslušným číslem ve sloupci B.
Jednu z vlastností Fibonacciho čísel lze zapsat vztahem ( Fn +1 ) − Fn Fn + 2 = −1n . Jeho pravdivost vyzkoušíme ve sloupci C. Do buňky C2 proto zapíšeme vzorec =B3*B3-B2*B4. Do buňky C3 vzorec =B4*B4-B3*B5 atd. 2
Rozkopírujeme ho až po buňku C45. Vypadá to, že to opravdu vychází, ale pozor, od buňky C37 se už nestřídá -1 a 1, ale objevuje se hodnota 0.
Problém je v přesnosti výpočtů Excelu. Excel počítá s přesností na 15 číslic a pro větší n se 2 zaokrouhlovací chyby projevují na posledních číslicích velkých hodnot ( Fn +1 ) , resp. Fn Fn + 2 . Další vlastností Fibonacciho čísel vyjadřuje vztah Fn Fn +1 = F12 + F2 2 + K + Fn 2 . Tuto rovnost ověříme ve sloupcích D, E a F. Ve sloupci D vypočítáme levou stranu, a ve sloupci F stranu pravou. Sloupec E použijeme jako pomocný pro výpočet čtverců hodnot pravé strany. Do buňky D2 vepíšeme vzorec =B2*B3, do buňky D3 vzorec =B3*B4 atd. Do buňky E2 vepíšeme vzorec =B2*B2, do buňky E3 vzorec =B3*B3 atd. A nakonec do buňky F2 vepíšeme vzorec =SUMA($E$2:E2), do buňky F3 vzorec =SUMA($E$2:E3) atd. Všechny tři vzorce rozkopírujeme až na úroveň 46. řádku.
Protože je sloupec E pouze pomocný a zbytečně nám zabírá místo, skryjeme ho. Klikneme pravým tlačítkem myši na buňku E a ze zobrazené nabídky vybereme Skrýt.
Nyní se můžeme začít věnovat porovnávání hodnot ve sloupcích D a F. Pro kontrolu můžeme ve sloupci G vypočítat rozdíl sloupců D a F (nemusím připomínat, že by měla vyjít 0).
Poučení: Pozor na přesnost výsledků u velkých čísel. Nápověda MS Excel: V aplikaci Excel jsou čísla uložena s přesností 15 platných číslic a s touto přesností se také provádějí výpočty.
Literatura [1]
Brož. M.: Mistrovství v Microsoft Excel 2000 a 2002, Computer Press, Praha, 2002
