Třetí cvičení

Zadání:

  1. Na pomocné stránce  získejte data z měření chladnutí tělesa, vyneste je do grafu (X-Y bodový) a dle následujícího zadání upravte:
    Podúlohy:
    1. je třeba zvolit správný typ grafu (XY - bodový).
    2. graf umístěte jako objekt stávajícího listu, posuňte jej a zvětšte tak, aby zabíral většinu použitelné plochy (nebyl moc malý).
    3. po zadání grafu zadejte, že vykreslená značka je vodorovný křížek (+); před zadáním tvaru značky a její velikosti (7 až 10 bodů) je třeba správně zvolit barvu značky i její pozadí (např. červenou na žluté).
    4. přidejte k datům spojnici trendu - lze tak učinit pravým tlačítkem na některém bodu grafu. Správný typ křivky je exponenciální. Upravte spojnici trendu na tenkou čáru a vhodnou barvu.
    5. zobrazte rovnici regrese (pravé tlačítko na spojnici trendu, zaškrtnout)
    6. pokud pracujete v OpenOffice nebo Libre Office, vyberte jeden bod, který se nejvíce liší od proložené exponenciály, a vyznačte jej jinou barvou značky.
    7. doplňte popis os a také doplňte správné jednotky - měření proběhlo v sekundách a stupních Celsia.
    8. obě osy se nemohou jmenovat stejně - čas je proto třeba označit  τ  a teplotu nejlépe ϑ - viz obr. 2 dole.
    9. zkontrolujte, zda v grafu jsou obě mřížky (svislá i vodorovná), případně je zapněte. Vodorovná mřížka má být po 10 °C.

  2. přiloženém souboru (verze pro Openoffice.org)  jsou smyšlené hodnoty nakupovaného zboží (v některých verzích souboru se počty kusů při každé změně v listu nebo při stisku klávesy F9 náhodně generují, aby šlo zkontrolovat správné řešení některých úkolů). Všechny výpočty i graf musí být provedeny tak, že pokud se změní čísla ve žlutě vyplněných buňkách, projeví se tyto změny na všech dotčených místech (zejména hodnota 5%, resp. 2% u výše slevy). Vyzkoušejte.
    Podúlohy:
    1. doplňte do světle modré části vhodná čísla a vzorce, aby vznikl rozpis nakupovaného zboží.
    2. formátováním zobrazte hodnoty ve vhodném formátu (měna, včetně znaku €, resp. Kč).
    3. vyplňte také samostatnou buňku F48, kde bude aplikována příslušná sleva - pokud bude dosažena velikost navrženého nákupu podle buňek E45 až F45.
    4. do buňky F49 vypočtěte konečnou cenu včetně DPH po zaokrouhlení. Z čistě cvičných důvodů prosím zpracujte zaokrouhlení na celé padesátníky (tento obrat má řadu podobných užití, například zaokrouhlování na celá balení).
    5. modře vyplněnou plochu nezapomeňte doplnit vhodnou mřížkou (ohraničení buněk).
    6. podíl jednotlivých položek na celkovém nákupu vyneste do výsečového nebo sloupcového grafu (sloupec s názvy zboží bude použit jako popisky, sloupec s cenami po ponásobení množstvím jako hodnoty).
    7. pomocí kontingenční tabulky a kontingenčního grafu zjistěte a zobrazte, kolik průtokoměrů a jakých typů bylo objednáno na jednotlivých pobočkách.

    Poznámky ke způsobu vypracování:
    • sloupec s počtem nakupovaných kusů můžete také vyplnit vzestupnou řadou.
    • po kliknutí na odkaz soubor uložte (nejlépe do svého adresáře na server fsh, tedy v MS Windows na N:), rozhodně nepracujte přímo v prohlížeči.

  3. Do grafu vyneste graf funkce zadané vyučujícím na cvičení zápisem matematického vzorce na tabuli v uvedeném rozsahu. Pokud úlohu řešíte v předstihu, vyneste graf funkce
    y
    =5 sin 3x - 3 sin 5x   v rozsahu -90° až 450° s hlavní mřížkou a popisem osy po 90° a vedlejší mřížkou po 30°.

  4. Pomocí maticových výpočtů Libre Office/OpenOffice Calc nebo MS Excel vyřešte následující soustavu tří rovnic o třech neznámých:
    5 x −3 y +   z = 5
    −4 x  +3 y  +2 z  =  9
    6 x −7 y +3 z = 3
    Výsledek zkontrolujte.

Úkoly lze řešit v MS Excel, Libre Office nebo Openoffice.org Calc. Openoffice je opět v mnohém jednodušší a má logičtější ovládání, ale například vložení stojatého křížku je poněkud komplikovanější. Je to tím, že zadání je vytvořeno "na míru" Excelu.

Návod pro řešení první úlohy v OpenOffice, druhá uloha (velmi stručně - platí návod pro Excel).

Následující text popisuje řešení v MS Excel:

Poznámka. Sylabus předpokládá graf vytvořit již ve Wordu. Nedoporučuji, ve spolupráci s Excelem můžete lépe ovládat data. Doporučuji hotový objekt "graf" přenést do Wordu.

Pro případ potíží s pomocnou stránkou nabízíme příklad této stránky - použijte jej ovšem jen na výslovný pokyn učitele, na pomocné stránce totiž každý získá svá data.

Studenti, kteří již Excel viděli, ale neznají řešení dílčích problémů, mohou přeskočit úvod.

Následující úvod popisuje řešení podobné úlohy, jako je druhá zadaná. Nechť slouží úplným začátečníkům. Postupy při řešení zadaných úloh budou obdobné.

Předpokládám, že najít na ploše program MS Excel nebude problém. Pokud jej vidíte prvně, podívejte se na označení řádků a sloupců v tabulce; řádky jsou očíslovány, sloupce jsou označeny písmeny. Označení jednotlivé buňky pak bude kombinací obojího, např. A1, D7 a podobně. Tímto způsobem se pak na buňku budeme také odkazovat. Každá buňka má text (vzorec) a hodnotu, danou výpočtem tohoto vzorce. V listu před vámi se zobrazuje hodnota; v řádce vzorců (řádka nad tabulkou) se zobrazuje vzorec (také lze stisknout klávesu F2 a doplnit jej přímo do tabulky). Kopírování probíhá obvyklým způsobem, označíme si buňku (skupinu buněk) myší, vložíme ji kombinací kláves Ctrl+C do schránky, vybereme počáteční buňku cílové oblasti, a použijeme Ctrl+V. Pokud zkopírujeme buňku s výpočtem, opraví se odkazy na ostatní buňky - jsou totiž relativní k buňce. Aby se při kopírování neměnily, je třeba je změnit na absolutní. Absolutní odkaz z relativního uděláme přidáním znaků $, nejsnáze tak, že umístíme kursor na odkaz a několikrát stiskneme klávesu F4. Souřadnice označená dolarem je absolutní (nemění se při kopírování). Kopírovat lze i myší - po vybrání lze oblast přetáhnout, nebo (držíte-li současně klávesu Ctrl) zkopírovat, jako ve Wordu. Pojmenovat lze i skupinu buňek (např. za účelem součtu funkcí SUM), pak se uvede jméno levé horní buňky v pravoúhlé oblasti, dvojtečka, a jméno pravé dolní buňky, např. B7:F15 . Pokud není oblast pravoúhlá, je třeba ji popsat jako několik obdélníků a oddělit středníky - B7:G9;D2:E3;J11:J15 . Místo psaní lze na buňku kliknout, v posledním případě vybrat první oblast, podržet klávesu Ctrl, a vybírat další (používá se občas u grafů, když vstupní data pro graf nejsou vedle sebe).

Výše uvedené vám postačí pro základní seznámení. Tyto poznatky však nemusí vždy platit. Podívejte se do textů pro zrušený předmět ZPP1 ( \\Fsh\studium\u12110-3\old\cviceni\excel1.doc, \\Fsh\studium\u12110-3\old\cviceni\graf1.doc), kde je podrobnější výklad.

Pokud potřebujeme psát výpočet, začínáme nejlépe znakem "=" (rovnítko). K našemu příkladu.

Do prvních dvou řádek napiště nějaké záhlaví. Např. do druhé řádky napište (buňky A2, B2, C2, D2, E2, F2) "název", "typ", "cena/ks", "kusů", "celkem", "s DPH". Najeďte myší mezi označení záhlaví sloupců A a B a zvětšte šířku sloupce A. Pak vyplňte několik položek, podle obrázku, který je na konci tohoto textu (zatím jen první čtyři sloupce). Do pátého a šestého sloupce napište výpočty. V buňce E3 to například bude vzorec
=C3*D3
a v buňce F3 vzorec
=E3*1,22
popř.
=C3*D3*1,22
Ostatní buňky nevyplňujte, obsah do nich nakopírujte. Bylo by možné například vybrat myší buňku E3, stisknout Ctrl+C, pak vybrat buňky např. E4 až E9, a stisknout Ctrl+V (nebo dokonce Enter). Takto se postupovalo ve velmi starých verzích. Nyní je jednodušší kliknout na buňku E3, podívat se na pravý dolní roh vzniklého označení od myši, a za tento malý černý čtvereček obsah buňky roztáhnout do celé požadované oblasti.

Přesvědčte se, že pokud změníte nějakou položku, vše se přepočítá. Excel ve skutečnosti přepočítá jen buňky, které obsahují odkaz na měněnou hodnotu, a následně řetězově dále. Pokud je v nějaké buňce údaj, který se mění, a Excel o tom neví, lze si přepočet vynutit klávesou F9 (např. některá buňka obsahuje hodnotu aktuálního času).

Funkce

Klikněte na buňku pod sloupcem s celkovými součty. Klikněte na ikonku funkce Autosum. Klikněte na Enter. Výpočet součtu by se měl vložit sám. Jiné funkce musíte vkládat přes ikonku na vložení funkce (pokud si ovšem jejich jména nepamatujete), nebo v hlavím menu najít Vložit -> Funkce. Funkce má parametry v závorkách; prohlédněte si jejich seznam. Dnes se jim nebudeme věnovat.

Grafy

Vyberte oblast, ze které chcete udělat graf (u nás úsek sloupce F). V menu najděte Vložit -> Graf, nabo stiskněte ikonku Průvodce grafem. U takhle jednoduchého grafu nemůžete zabloudit. Pokud má někdo zájem, mohu opět jen doporučit texty k předmětu ZPP1 (v tomto případě \\Fsh\studium\u12110-3\old\cviceni\graf1.doc). Na následujícím obrázku je ukázka grafu v Excelu:

Řešení jednotlivých dílčích úloh

Výběr správné značky pro graf naměřených hodnot

V hotovém grafu klikneme na zvolený bod (pokud je více křivek a více méně se překrývají, je možné tu správnou zvolit tlačítky pro pohyb kursoru nahoru a dolů, jednoltivý bod tlačítky pro pohyb doleva a doprava). Pak na něj kliknutím pravého tlačítka vyvoláme lokální menu, v něm Formát datové řady. Na kartě (záložce) Vzorky pak zvolíme, že čára = žádná (budeme místo ní přidávat spojnici trendu) a u značky nejprve zvolíme barvy, protože jinak se může stát, že pozadí nebude bílé a bude se zbytečně tisknout. Značka u grafu by měl být (stojatý?) křížek, tedy znaménko "plus", zvětšené alespoň na 7 bodů. Z grafu, vytištěného z počítače, ovšem stejně nejde odčítat hodnoty. Pokud je barva značky i jejího pozadí stejná, není tvar značky vidět. Pokud není vybrána celá řada, ale jen jeden bod, měníme jen tento bod a jemu předcházející spojnici.

Poznámka: Ve Formátu spojnice trendu na kartě Možnosti lze zvolit, že bude zobrazena rovnice regrese.

Doplnění popisu os a řecká písmenka

Nejvhodnější řešení je zvolit textové pole, protože si je můžeme umístit kamkoli. Nejprve je třeba ručně trochu zmenšit graf, například kliknutím do zobrazované oblasti a tažením za její roh (levý dolní - místo bude třeba vyrobit nalevo a dole). Pak si zobrazíme panel nástrojů Kreslení (ikonka Kreslení je vidět, když jsou zobrazena "tlačítka ve dvou řadách"). Na něm nalezneme textové pole a vyznačíme místo, kde má být. Řecká písmenka můžeme vložit tak, že napíšeme odpovídající písmenko z normálního fontu, a po dopsání celého textu jej samostatně vybereme a zvolíme pro něj font Symbol. V našem případě písmenu  τ  odpovídá malé t, psacímu písmenku  ϑ  odpovídá písmeno J. Místo značky pro ° (je součásí sady Unicode) lze dvakrát (se šiftem!) stisknout klávesu mezi klávesami Esc a Tab, která slouží k zápisu kroužku nad U. Přebytečné kolečko umažeme (správný postup je místo druhého stisknout mezerník). Pokud takto nelze vložit kroužek, protože píše procenta, je možné buď vložit malé "o" a následně je přeformátovat jako horní index, nebo (v buňce, nikoli v grafu) lze použít funkci Vložit - Symbol. Pokud je zobrazovaný text přebrán do grafu odkazem, je tato poslední možnost jediná možná, protože převzetím se odstraní veškeré formátování. Pokud jsou však všechny znaky ze sady Unicode, převezmou se správně. V sadě Unicode se některé znaky těžko hledají, například psací písmeno theta (ϑ), ale kdo hledá, zpravidla i najde.

Zaokrouhlování na celé padesátihaléře

Tohoto cíle nelze dosáhnout formátováním, musíme použít funkci "zaokrouhlit", která ovšem umí jen celý počet desetinných míst. Cenu musíme proto nejprve ponásobit dvěmi, zaokrouhlit na celá čísla, a pak opět podělit. Zobrazíme s formátováním na dvě desetinná místa a jako měnu (se správnou značkou). Příklad: Zobrazení hodnoty součinu buněk B3 a C3:

    = ZAOKROUHLIT(B3*C3*2;0)/2

Nesmíme zapomenout, že oddělovačem seznamu (parametrů funkce) je v českých Windows středník.
Existují jistě i další postupy, jak dosáhnout téhož výsledku (Excel má stovky funkcí), ale tento postup je obecný a stejně se postupuje, i když je třeba "zaokrouhlit" na několik výběrových hodnot např. při psaní programu v jazyku C.

Řešení množstevní slevy pro danou velikost nákupu

Musíme použít funkci Když. Pokud hodnota bez slevy je například v buňce E12 a hodnota, od které se poskytuje sleva v buňce G2 (velikost této slevy v % pak v H2), řešení bude následující:

    = KDYŽ(E12<$G$2;E12;(1-$H$2)*E12)

Poznámka - v novějších verzích Excelovského souboru jsou buňky na jiných adresách, zejména je vyšší číslo řádku.

Hodnotu 5% můžeme od jedničky přímo odečíst, protože se sice zobrazuje jako "5", ale při tomto formátu čísla se počítá se setinou zobrazované hodnoty.

V našem konkrétním případě jsou ale slevové kategorie dvě, takže místo ceny se slevou bude další funkce Když, která posoudí, zda neaplikovat rovnou větší z možných slev. Přesné řešení daného problému zde nebude, protože cílem je, aby je studenti vymysleli sami, nikoli jen opsali.

Obr. 2: Řešení křivky ochlazování

Poznámka. Samozřejmě víme, že Excel proloží exponencielu špatně. Použitá funkce totiž neobsahuje absolutní člen, takže pro nekonečný čas nabývá nulové hodnoty v ose y (naproti tomu skutečná teplota, tedy teplota okolí ochlazovaného tělesa, na které se ve skutečnosti teplota ustálí, je vypsána na pomocné stránce dole pod tabulkou naměřených hodnot; tato hodnota není uměle zatížena chybou). Pokud chceme přesto použít tuto funkci Excelu a nechceme si regresi spočítat sami, můžeme postupovat následovně: 1) vytvoříme další sloupeček hodnot, ve kterém budou teploty snížené o teplotu okolí, 2) vytvoříme graf, necháme proložit exponencielu a zobrazit rovnici regrese, 3) do dalšího sloupečku vypočteme pro vhodný počet bodů (aby křivka nebyla skokovitá) výsledek rovnice regrese, který ale zvětšíme o teplotu okolí, 4) vykreslíme nový bodový graf, 5) vybereme body, a z lokálního menu (pravé tlačítko myši) položku "Zdrojová data", v okně pak záložku řady, a zde vyhledáme tlačítko "Přidat řadu" - je třeba zadat i data pro časovou osu, která mohou být odlišná, viz bod 3, 6) novou křivku upravíme tak, aby neobsahovala body, ale jen čáru, a zaškrtneme volbu "hladká čára".

Jedno z možných řešení třetího úkolu

Vaše řešení bude samozřejmě odlišné, protože vyučující jistě zadá jinou funkci. Zde je uvedeno řešení pro funkci zadanou studentům, řešícím úkoly s předstihem.
Poznámka: pozice svislé osy byla ponechána, aby se zvýrazila nulová hodnota x. Možná by ale bylo přehlednější, kdyby bylo zadáno, že svislá osa protíná vodorovnou v bodě -90.