VYSOKÁ ŠKOLA BÁŇSKÁ - TECHNICKÁ UNIVERZITA OSTRAVA

Základy tabulkového procesoru Excel

Učební texty pro distanční vyučování

 

doc. Dr. Vladimír Homola, Ph.D.
Ing. Jarmila Drozdová, Ph.D.

Ostrava 2021

 

K ISBN 978-80-248-4145-8
© VŠB-TU Ostrava 2018-2021

Obecně o tabulkových procesorech

Data, jejich elementární typy a jejich fyzický způsob uložení v digitálním prostředí, to vše je obsahem první části těchto textů. V praxi ovšem existuje řada situací reálného světa, jejichž stav nelze popsat jedinou fyzickou hodnotou jediné fyzikální nebo jiné "jednoduché" veličiny. Tvrzení, že venku je 28°, je uprostřed zimního období příjemné. Samo o sobě je však nic neříkající. Kde (na severu Afriky, to je daleko), kdy (v srpnu, to je tam vlastně s touto teplotou chladno), kolik (stupňů Celsia či Fahrenheita?) - už jen tato tři upřesnění potřebují minimálně další tři datové hodnoty. A pro to místo (kde) dnes každý žáček obecné školy použije prvotně souřadnice GPS ze svého mobilku (což jsou v optimálním případě dvě fyzické číselné hodnoty).

Je tedy třeba se zabývat tím, jak jsou jednotlivá "jednoduchá" data "poskládána k sobě" tak, aby tvořila ucelený "informační celek" vypovídající o konkrétním stavu konkrétní sledované veličiny.

Dnes k nejčastěji používaným schématům patří databázová schémata nejrůznějších databázových systémů (naštěstí jich není moc). Na toto téma - zvláště tzv. relačních systémů - však studenti narazí v následujících semestrech.

Účelem těchto textů je popsat nejen způsob organizace uložení dat v tzv. tabulkových procesorech, ale i jejich specifika - a na to navazující problematiku případné vzájemné konverze dat tabulkových procesorů a relačních databází.

Pojem "Spreadsheet"

V anglickém originále jde o složeninu "Spread" a "Sheet". Jako "Sheet" se zde běžně chápe "List", "Arch". Pod pojmem "Spread" se rozumělo "Rozložení", "Roztažení" ve smyslu novinových nebo časopisových položek (textů nebo grafiky) pokrývající dvě strany, které se rozprostírají přes středový okraj a jsou považovány za jeden velký arch - viz např. [8].

Složený pojem "spread-sheet" (tedy doslova "Rozložený arch") znamenal formát, který sloužil k předkládání účetních knih - se sloupci pro kategorie výdajů, s řádky pro jednotlivé faktury a částkou pro každou platbu v té buňce, kde se řádek a sloupec částky protínají. Tento formát byl tradičně "rozložen" na protilehlé strany účetní knihy nebo na velké listy papíru (nazývané "analytický papír") v řádcích a sloupcích; listy byly přibližně dvojnásobně širší než obyčejný papír.

Rozvoj (tehdy ještě sálové) výpočetní techniky přinesl samozřejmě i rozvoj programového vybavení pro zpracování účetních a jim podobných úloh. Kolem roku 1970 byl vyvinut software pro zpracování dat uložených ve formě tabulek (tedy obdobně jako v databázích). To nové však spočívalo v tom, že v buňkách tabulek mohly být umístěny nejen jednoduché číselné a textové hodnoty, ale také vzorce.

Nástupem třídy osobních počítačů (Personal Computer) se přirozeně začaly množit požadavky na vývoj software pro zpracování analogicky koncipovaných tabulek jako u zmíněných sálových počítačů. Ke vzniku prvního takového úspěšného programu se uvádí následující historka [8]:

Dan Bricklin, student Harvard Business School, pozoroval svého univerzitního profesora vytvářejícího na tabuli tabulku dat a výsledků výpočtů. Když profesor zjistil chybu, musel opatrně vymazat a přepsat řadu po sobě jdoucích záznamů v tabulce. To přivedlo Bricklina k myšlence replikace tohoto procesu na počítači, přičemž tabule by byla použita pro zobrazení výsledků použitých vzorců. Spolu s kolegou Bobem Frankstonem implementoval Bricklin myšlenku do programu VisiCalc v roce 1979 na Apple II a v roce 1981 na IBM PC. VisiCalc se stal první aplikací, která přeměnila osobní počítač z hobby pro nadšence počítačů na obchodní nástroj.

VisiCalc byl zároveň prvním software, který kombinoval všechny základní funkce moderních tabulkových procesorů. Šlo např. o interaktivní uživatelské rozhraní WYSIWYG (What You See Is What You Get), automatické přepočítávání, stavové a vzorové řádky, kopírování s relativními a absolutními odkazy, výběry odkazovaných buněk aj.

Od té doby vznikla řada více či méně úspěšných aplikací určených pro zpracování dat zadaných ve formátu tabulek spolu se vzorci, které tato data používají a bezprostředně zobrazují výsledek - např. památný Lotus-123. Z těch, které se dosud vyvíjí a vznikají nové verze, lze uvést např.

 

a další.

Ovšem Spread-sheet (nebo nyní častěji Spreadsheet) je pojem, na který je třeba dát v anglických textech pozor - a to např. i na Wikipedii. Jak bylo shora popsáno, původně měl pojem význam především účetní knihy. Rozšířením počítačových aplikací zpracovávajících účetní a podobná data, došlo k tomuto:

 

Pojem "spreadsheet" proto jednou může označovat aplikaci (tedy program), jednou soubor (tedy data). Záleží jen na obratnosti autora anglického textu, zda čtenář z kontextu pozná, o co vlastně jde.

V českých odborných textech je situace jednodušší:

Každý program zpracovávající spreadsheet může mít svůj vlastní formát datového souboru. Protože však jedním z nejrozšířenějších programů této třídy je MS Excel a logika jeho datových souborů simuluje data v sešitu, vžilo se označení "sešit" obecně pro soubory obsahující spreadsheet (= data). Korektně by se však mělo používat označení např. "sešit Excelu". I jiné programy této třídy a téměř každý aplikační software však dovede soubory typu "sešit Excelu" minimálně číst, proto zkrácené označení "sešit" není příliš na újmu obecnosti.

Závěrem: zvykli jsme si - a to je dodrženo v těchto učebních textech - na toto označení:

 

Sešit

I český pojem "sešit" zavedený výše v sobě skrývá nebezpečí dvojznačnosti. Jednak se používá pro označení logické organizace dat, jednak pro fyzický soubor obsahující data právě s touto organizací. Z hlediska běžného uživatele však toto dvojí chápání splývá (zvláště vidí-li uživatel na obrazovce vizuální podobu logické organizace dat fyzicky uložených v souboru), a není tedy na závadu.

Sešit a používaná terminologie je pro názornost vysvětlována na tom zobrazení, které používá grafické rozhraní tabulkového procesoru Excel z edice MS Office 2010 (viz obrázek níže). Grafické rozhraní procesoru Excel 2016 je až na nepodstatné (většinou barevné) odchylky totožné.

Sešit (workbook) se chápe jako jednotka uložení dat. Logicky lze nazírat jako na skutečný "papírový" sešit, který na začátku obsahuje několik vodorovně i svisle nalinkovaných listů (worksheets). Na každém listě je tedy osnovou vodorovných a svislých linek vytvořena soustava buněk (cells).

Poznámka: Velmi by to připomínalo čtverečkovaný papír, kdyby ty buňky byly skutečně čtverečky. Protože rozestup linek (jak svislých tak vodorovných) lze měnit, jsou buňky zobrazeny obecně jako obdélníky.

Vodorovnými linkami jsou vymezeny řádky buněk (rows), svislými linkami jsou vymezeny sloupce buněk (columns). Každý řádek je opatřen záhlavím řádku (row header), které obsahuje jedinečné číslo řádku (row number). Každý sloupec je opatřen záhlavím sloupce (column header), které obsahuje jedinečné písmenné označení sloupce (column name).

Na plochu takových listů (ne do jejich buněk) lze vkládat grafy, obrázky a další grafické objekty.

Poznámka 1: K označení sloupců se používá 26 písmen anglické abecedy (tj. bez diakritiky). Označení prvních 26 sloupců je A až Z. Označení dalších sloupců je tvořeno dvěma písmeny (AA, AB, AC až ZZ), následují označení třemi písmeny (AAA, AAB atd).

Poznámka 2: Počty řádků, sloupců a listů jsou dány implementací konkrétního tabulkového procesoru. Například v tabulkovém procesoru Excel 2010 je počet řádků 1 048 576 (=220) a počet sloupců 16 384 (=214) - poslední dostupný sloupec má tedy písmenné označení XFD. Počet listů je shora omezen pouze dostupnou pamětí, zdola je omezen na 1 list (sešit tedy musí obsahovat alespoň jeden list). Zatímco počet řádků a počet sloupců je pevně dán uvedenými hodnotami, listy lze ve stanoveném intervalu libovolně přidávat nebo ubírat.

Poznámka 3: Počet řádků a počet sloupců je pevně dán uvedenými hodnotami. Tabulkové procesory mají sice funkci "odstranění" několika řádků resp. několik sloupců. V tom případě se ale do listu zespodu (při odstranění řádků) resp. zprava (při odstranění sloupců) "nasunou" nové prázdné řádky resp. sloupce v počtu odstraněných.

Kromě listů s buňkami může sešit obsahovat listy, na každém z nichž je umístěn jeden (a vždy jen jeden) graf.

Každý list je jednoznačně pojmenován (worksheet name). V grafickém prostředí jsou listy znázorněny záložkami (tabs), v nichž jsou jména listů zobrazena. Jména listů jsou při vytvoření nového sešitu vytvořena tabulkovým procesorem, uživatel sešitu je může posléze měnit - musí ovšem zachovat jedinečnost jmen.

Každá buňka má především svou adresu (cell address) tvořenou písmenným označením sloupce následovaným číslem řádku (např. G12). Každá buňka má dále svůj obsah (cell content). Tento obsah může být prázdný (null - pozor, nikoliv nulový!), může to být hodnota  (value) nějakého typu (value type), a konečně může být obsahem buňky i vzorec (formula). Obsah je v grafickém prostředí tabulkovým procesorem v buňce zobrazen, a to implicitním nebo zvoleným formátem (cell format).

Tabulkové procesory pracují většinou se třemi typy hodnot: s čísly (number), se znakovými řetězci (character string) a s logickými hodnotami (logical). Znakové řetězce se často zjednodušeně označují také jako text.

Buňky lze obsahem naplnit buď interaktivně v grafickém rozhraní tabulkového procesoru (např. z klávesnice), nebo importem z různých zdrojů. Pokud se zadává hodnota prostřednictvím grafického rozhraní přímo zápisem do buňky, zadává se do tzv. aktivní buňky aktivního listu. Adresa aktivní buňky je zobrazena v poli názvů, obsah aktivní buňky je zobrazen v poli vzorců. V buňce samotné je vždy zobrazena nějakým formátem hodnota obsahu: je-li obsahem např. číslo, je zobrazeno toto číslo; je-li však obsahem vzorec, je zobrazen "výsledek výpočtu".

 


        Sešit otevřený v prostředí programu Excel
  1. Pole názvů
  2. Záhlaví sloupců
  3. Záhlaví řádků
  4. Aktivní buňka
  5. Záložky listů
  6. Pole vzorců
  7. Lupa

 

Konvence použité při popisu činností

V tomto textu  jsou popisovány postupy v prostředí tabulkového procesoru Excel, edice Microsoft Office 2010 (Excel edice 2016 se liší jen nepodstatnými, většinou barevnými) odchylkami. Tomu odpovídá i terminologie:

 


          K použité terminologii
  1. Hlavní karty - dříve Hlavní menu, zde aktivní hlavní karta Domů
  2. Skupina příkazů - zde skupina příkazů Schránka
  3. Tlačítko pro Nástroje skupiny
  4. Příkaz - zde příkaz Vyjmout

 

Tlačítko z bodu c. předchozího seznamu bude v textu zobrazováno symbolem . Pro další ovládací prvky je použita standardní terminologie resp. zobrazení (tlačítko, zaškrtávací pole resp. , rozvíjecí seznam aj).

Pro vyznačení posloupnosti aktivací návazných ovládacích prvků je použit zápis

Hlavní karta / Skupina příkazů / Příkaz

Značí tedy zápis

Domů / Schránka / Vyjmout

(viz situaci na předchozím obrázku) požadavek na aktivaci hlavní karty Domů, tam vyhledání skupiny Schránka, a v ní použít příkaz Vyjmout.

Výjimkou je položka Soubor hlavního menu, kde na druhém místě je uvedena položka levého panelu a na třetím místě skupina parametrů.

Některé příkazy ve skupině mají pod ikonou nebo vedle textu ještě tlačítko . Pokud to nevyplyne z kontextu, bude výslovně řečeno, zda se má stisknout ikona nebo toto tlačítko pod (vedle) ní.

Tento styl textu je ve článku použit pro pokyny čtenáři při práci se cvičným sešitem (viz níže), případně pro popis objektů a činností týkající se výhradně cvičného sešitu.

V celém následujícím textu se polohovacím zařízením rozumí klasická myš s implicitním nastavením tlačítek jako "levé" a "pravé". Pokud čtenář na svém zařízení používá jiné polohovací mechanismy (dotykovou obrazovky, touch-pad apod.) nebo opačného nastavení tlačítek, nechť pojmy myš, tlačítko, klik a další transformuje vzhledem ke svému zařízení resp. nastavení.

Cvičný sešit

Pro výuku je připraven cvičný sešit naplněný daty použitými při demonstraci jednotlivých kapitol. Studenti si jej před vlastní výukou stáhnou - např. do DOKUMENT-ů, na plochu ap. podle výukového prostředí. Jde o sešit s názvem CVICGMT.XLSX zde ke stažení.

Pokud se studenti hlásí jako uživatelé Student na učebně J-409 VŠB TU Ostrava, pak po přihlášení mají cvičný sešit v dokumentech uživatele Student (t.j. ve "svých" dokumentech) v adresáři EXCEL.

Typy dat

Ačkoliv v sešitu mohou být uloženy podprogramy a funkce uživatele využívající kompletní nabídky datových typů programovacího jazyka VBA (Visual Basic for Application), Excel sám využívá hodnotu (Value) buňky jako proměnnou typu Variant takto:

 

Následně jsou stručně zmíněna jen některá specifika.

Číselný typ

Protože jde o hodnoty typu Double (racionální čísla ve dvojnásobné přesnosti), nelze do buňky vložit třebas Ludolfovo číslo (mající nekonečný rozvoj) s přesností na 35 desetinných míst (3.14159 26535 89793 23846 26433 83279 50288). Při pokusu o to Excel přijme a uloží jen 3.14159 26535 8979. Jak je zde vidět, shora zmíněných 17 (desítkových) cifer je skutečně maximum.

Na druhé straně lze bez problémů provádět číselné operace bez ohledu na formátování. Je-li číselná buňka formátována datumově (a uživatel vidí např. 30/12/2017), zřejmě nemá valný význam zjišťovat druhou odmocninu tohoto data (=207,603). Jistě však má význam zjistit hodnotu o 20 menší (=10/12/2017).

Textový typ

Pro práci s textovými hodnotami disponuje Excel sice jen jedinou operací (obvykle nazývanou zřetězení, operátorem je znak & - jde o připojení druhého operandu za první), zato nabízí řadu funkcí pro práci s hodnotami typu Text. Buď některé parametry těchto funkcí nebo jejich výsledek je typu Text. Příkladem může být funkce ČÁST (vrací vnitřní část textu definovanou počáteční pozicí a počtem znaků), VELKÁ (vrací textový parametr, v němž jsou všechna písmena převedena na velká) a nejméně 22 dalších funkcí.

Logický typ

Velmi jednoduchý datový typ, z neznámého důvodu uživateli nepoužívaný, opomíjený ba až nenáviděný - kdykoliv uživatel vidí v buňce zobrazeno PRAVDA nebo NEPRAVDA, raději její obsah co nejdříve smaže. Přitom hodnoty tohoto typu slouží k tak důležitým nástrojům, jako je řízení podmíněného výpočtu (viz funkce KDYŽ), nastavení nejrůznějších indikátorů apod. Trochu nepříjemné pro zkušenějšího pracovníka v oboru IT je to, že je zvyklý na logické operátory typu AND, OR, NOT a další. V Excelu je nutno tyto operace realizovat pomocí funkcí A, NEBO, NENÍ a dalších.

Datum resp. čas

Obecně viz shora Číselný typ. Je třeba mít neustále na paměti, že interně jde o hodnotu racionálního čísla; má tedy celou a necelou část. Celá část má význam pořadového čísla dne s počátkem (nulou) na časové ose dne 31/12/1899 (tj. den číslo 1 je 1/1/1900). Necelá část číselné hodnoty je proto zlomek dne tak, že 1,00 je jeden celý den = 24 hodin = 1 440 minut = 86 400 vteřin atd. Je-li tedy den č. 36 525 dnem 31/12/1999, pak hodnota 36 525,25 je čtvrtina tohoto dne, tj. 6:00 hodin ráno dne 31/12/1999.

Zobrazení datumu v buňce je v Excelu otázkou formátování číselné hodnoty. Pozor však: Excel neumí nulu a záporné hodnoty formátovat datumově! Škoda L

Vkládání dat

Aktivní list, aktivní buňka

Sešit je komplexní dokument, sestávající z mnoho objektů. Při vkládání dat jsou ovlivňovanými objekty jednotlivé buňky, přesněji jejich hodnota event. vzorec (angl. Value resp. Formula). Nikoliv však na všech listech, ale pouze na těch, které určí uživatel - na tzv. aktivních listech (nejčastěji bývá při práci se sešitem zvolen jediný aktivní list). Aktivní list je indikován bílou záložkou na panelu záložek v levé dolní části pracovní plochy programu Excel. List aktivuje levý klik na jeho záložku. Lze určit více listů aktivních: další levý klik se stisknutou klávesou CTRL aktivuje další list. Na následujícím obrázku jsou aktivní tři listy: lCviceni, lTeplota a lVydaje.

 

 

Vkládat data - rozumí se "ručně", např. z klávesnice - lze v jednom okamžiku pouze do jedné buňky (tzv. aktivní buňky, např. C3). Tato buňka je však aktivní na všech aktivních listech. Jedním vložením lze zadat hodnoty více fyzickým buňkám právě jen v případě více aktivních listů - je-li aktivní buňkou např. C3, bude jedním vložením ovlivněn obsah buněk C3 na všech aktivních listech.

Vkládání a editace jednotlivých hodnot

Mechanismus vkládání dat z klávesnice do aktivní buňky je obecně znám, na úrovni těchto učebních textů zmíníme jen některá specifika.

Především se důrazně doporučuje ukončovat zadání každé hodnoty klávesou ENTER; teprve tím je zadaná hodnota rozpoznána a umístěna do buňky. V mnoha případech, zejména při zadávání vzorců, totiž Excel chápe použití např. myši nebo kurzorových kláves jako ukazování na operandy a výsledek rozhodně nebude odpovídat představě zadávajícího.

Po ukončení zadávání hodnoty program Excel upraví zobrazení zadané hodnoty s případným formátováním takto (předpokládá se zadání do buňky, která ještě nebyla explicitně ani implicitně formátována):

 

V některých případech požaduje zadavatel zapsat údaj, který by při běžném zápise byl rozpoznán jako číslo nebo datum nebo logická hodnota, nebo text začínající rovnítkem (tj. znaky za rovnítkem se nemají chápat jako vzorec). Může to být např. požadavek zápisu textu obsahující cifry v nějakém atypickém formátu apod. V tom případě se požadovaný text zapíše jako vzorec obsahující zápis textové konstanty tvořené požadovaným textem, např.

= "= moje sestra"

Rovněž editace (úprava) obsahu buňky by měla být obecně známa. Proto jen pro připomenutí: Požadovaná buňka se učiní aktivní, a pak

 

Vkládání množiny dat

V těchto učebních textech (což není referenční manuál programu Excel !) jsou uvedeny postupy, které nejsou obecně známy, nebo u kterých je vhodné upozornit na některé jejich aspekty. Většinu popisovaných cílů lze dosáhnout i nástroji z hlavního menu resp. pásů karet, zde se však zabýváme pouze postupy zprostředkovanými myší (obecně polohovacím zařízením).

Po označení (výběru) buňky nebo spojité oblasti buněk je celý výběr orámován tučně a jeho plocha barevně odlišena - kromě buňky, kterou započal výběr; ta barevně odlišena nebude a stává se aktivní buňkou. Pravý dolní roh orámování je zvýrazněn a je důležitým ovládacím prvkem. Při práci s vybranou oblastí je orientací pro uživatele tvar kurzoru myši v různých situacích. Pro přehlednost je na následujících obrázcích označena (vybrána) jen jedna buňka (A1) a ta je tedy aktivní.

 



Stahování směrem dolů za pravý dolní roh 		Nejčastější činností při popisované přípravě dat je použití pravého dolního rohu stylem drag-and-drop = uchopením myší a táhnutím. Na obrázku vlevo je naznačeno stažení směrem dolů, lze však táhnout libovolným ze čtyřech směrů.  

Tvar kurzoru myši po přiblížení dolnímu okraji se stisknutou klávesou CTRL 		Rovněž okraj označené oblasti je ovládacím prvkem, který lze uchopit a táhnout. Výsledek tažení závisí na dalších okolnostech. Na obrázku vlevo je znázorněna ta okolnost, že při pohybu myši je na klávesnici nejprve stisknuta a držena klávesa CTRL.

 



Tvar kurzoru myši po přiblížení pravému dolnímu rohu bez stisknuté klávesy CTRL. 		Výsledek tažení za pravý dolní roh se významně liší podle stavu klávesy CTRL.  

Tvar kurzoru myši po přiblížení pravému dolnímu rohu se stisknutou klávesou CTRL 		Protože výsledek tažení je určen stavem klávesy CTRL, je vhodné sledovat tvar kurzoru myši.

 

 

Následují ukázky výsledků stažení výchozích dat. Pokud nebude výslovně uvedeno, stahuje se LEVÝM tlačítkem myši. V některých případech pozor na upozornění stažení PRAVÝM tlačítkem myši. Je nutno pozorně sledovat stahování BEZ stisknutého nebo SE stisknutým tlačítkem CTRL.

 

P1

Výchozí data 		 

Stažení dolů bez stisknuté klávesy CTRL 		 

Stažení dolů se stisknutou klávesou CTRL
P2

Výchozí data 		 

Stažení dolů bez stisknuté klávesy CTRL 		 

Stažení dolů se stisknutou klávesou CTRL
P3

Výchozí data 		 

Stažení dolů bez stisknuté klávesy CTRL 		 

Stažení dolů se stisknutou klávesou CTRL
P4

Výchozí data 		 

Stažení dolů bez stisknuté klávesy CTRL 		 

Stažení dolů se stisknutou klávesou CTRL
P5

Výchozí data 		 

Stažení dolů bez stisknuté klávesy CTRL 		 

Stažení dolů se stisknutou klávesou CTRL

 

Znovu je třeba upozornit na odlišné chování BEZ stisknuté klávesy CTRL a SE stisknutou klávesou CTRL.

Vkládání aritmetické posloupnosti

Příklady P1 a P4 ukazují vkládání aritmetické posloupnosti. V případě P1 jde o posloupnost s diferencí vždy 1 s prvním členem daným výchozí datovou hodnotou (zde v buňce A1 hodnota 1). V případě P4 jde o posloupnost s prvním členem daným první výchozí datovou hodnotou (zde v buňce A1 hodnota 2) a diferencí danou rozdílem druhé a první výchozí datové hodnoty (zde 5-2=3). Třetí člen (i=3) je tedy 2+(3-1)x3=8, sedmý člen (i=7) je roven 2+(7-1)x3=20.

Uvedený postup platí pro práci s myší BEZ stisknuté klávesy CTRL. Při stisknuté klávese CTRL jde o pouhou kopii dat.

Vkládání posloupnosti datumů resp. časů

Datum resp. čas je interně uloženo jako racionální číslo ve dvojnásobné přesnosti. Nástroje Excelu však jsou podle formátování číselné buňky schopny rozeznat specifika datumového údaje - např. číslo měsíce apod. Příklad P3 ukazuje totožný mechanismus při generování aritmetické posloupnosti jako v případech P1 a P4 - diference je rovna 1 (=1 den = 24 hodin), ovšem z naprosto neznámého důvodu je prohozeno chování klávesy CTRL při akci "stahování" - viz P1 a P4. Teprve v případě stisknuté klávesy CTRL jde o prostou kopii, jinak jde o vytvoření aritmetické posloupnosti.

Poznámka: Zda jde o datum, o čas nebo o datum a čas - to je dáno formátováním buňky s výchozími daty.

Vkládání posloupnosti textů s číselnou částí

Při vytváření aritmetické posloupnosti je Excel schopen rozpoznat zápis čísla i "odzadu" textového údaj - viz P2 (s Petrem) nebo P5 (s Janem). V těchto případech je chování s ohledem na klávesu CTRL stejné jako při datumových datech - a tedy "převrácené" oproti číselným datům.

Poznámka: Zápis čísla musí tvořit koncovou část textového údaje a musí to být celé číslo. Pro číselné hodnoty v textu typu "Petr 23 Pavel"  se inkrementace neprovádí.

Použití lineární regrese

Silným nástrojem Excelu je možnost generování datových hodnot na základě stanovení lineární regrese výchozích dat. Jako základní příklad vezměme následující příklad P6. Výchozí data D (= A1 : A4) byla sestavena tak, aby zhruba odpovídala závislosti D=2xJ+3, kde J je pořadí hodnoty D (tedy J = 1, 2, 3, 4).

 

P6

Výchozí data 		 

Stažení dolů bez stisknuté klávesy CTRL 		 

Stažení dolů se stisknutou klávesou CTRL

 

Výchozími daty jsou v tomto případě hodnoty buněk A1 až A4. Nechť tvoří výběr (označení). Označme jejich počet n (v uvedeném příkladu tedy n=4). Jejich stažením SE stisknutou klávesou CTRL je generována jejich kopie. Ovšem jejich stažením BEZ klávesy CTRL o k řádků dolů (v uvedeném příkladu k=4) jsou vloženy hodnoty zjištěné následovně:

 

V uvedeném příkladu P6 má regresní přímka koeficienty a=2.04 a b=3.05, dopočtené hodnoty tedy vyhovují předpisu 2.04x+3.05 pro x=5, 6, 7 a 8.

Pomocí transformace dat lze řešit lineární regrese i některými nelineárními funkcemi, jak ukazuje následující příklad P7:

 

P7

Výchozí data 		 

Kontextové menu po stažení dolů
bez stisknuté klávesy CTRL, avšak
PRAVÝM tlačítkem myši 		 

Doplnění po volbě Geometrický trend

 

Výchozími daty jsou stejně jako v předchozím případě hodnoty buněk A1 až A4. Nechť tvoří výběr (označení). Stejně jako v předchozím případě označme jejich počet n (v uvedeném příkladu tedy n=4). Jejich stažením BEZ klávesy CTRL, avšak PRAVÝM tlačítkem myši o k řádků dolů (v uvedeném příkladu k=4) je nejprve zobrazeno kontextové menu. Volbou Geometrický trend jsou pak vloženy hodnoty zjištěné následovně:

 

V uvedeném příkladu P6 má regresní křivka koeficienty a=0.269 a b=3.973, dopočtené hodnoty tedy vyhovují předpisu 3.973 . e 0.269 . x pro x=5, 6, 7 a 8.

Představu o popisovaném mechanismu dá následující obrázek:

 


          Dopočet geometrickým trendem
  • Kroužkem jsou označena výchozí data.
  • Následuje zjištění koeficientů regresní funkce.
  • Touto funkcí jsou dopočteny hodnoty této funkci vyhovující.
  • Na grafu jsou dopočtené hodnoty označeny trojúhelníkem.

 

Vkládání specielní řady datumů

Stahováním pravým tlačítkem myši má za následek zobrazení kontextového menu, jehož obsah je však různý podle typu stahovaných dat. Excel zde rozlišuje číselnou hodnotu formátovanou jako číslo a číselnou hodnotu formátovanou jako datum. Jedné z nabídek kontextového menu pro datumově formátované hodnoty lze využít pro řešení následující úlohy:

Vytvořte řadu datumů posledních dnů v měsíci.

Jako příklad P8 uveďme takovou řadu datumů, která obsahuje jak únor přestupného, tak únor nepřestupného roku:

 

P8

Výchozí data 		 

Kontextové menu po stažení dolů
bez stisknuté klávesy CTRL, avšak
PRAVÝM tlačítkem myši 		 

Doplnění po volbě Vyplnit měsíce

 

Zatímco roky 2015 a 2017 nebyly přestupné, rok 2016 přestupný byl.

Když už je předložena tato úloha, můžeme na jejím výsledku demonstrovat řešení následující (obecné) úlohy:

Co to bylo za dny? Pondělky, čtvrtky ...

Pro kontrolu vytvořme pro následující ukázku P9 kopii datumových údajů. Po označení těchto nových dat zvolme

Domů / Číslo

a v následně předloženém formuláři zvolme Druh=Vlastní a vyplňme Typ: písmeny ddd.

 

P9

Příprava dat 		 

Nastavení formátu druhému sloupci dat 		 

Jiný z datumových formátů číselných hodnot

Formátovací znak d se používá takto:

Bližší výklad o formátovacích symbolech a jejich použití (např. znaku m pro měsíce) však překračuje rozsah těchto učebních textů.

Vzorce, odkazy a názvy

Vzorce

Objektový model definovaný v knihovně Excel používá třídu Range (doslova přeloženo "Množina různých prvků stejného obecného typu", volně chápáno jako rozsah, výběr, oblast) jako reprezentanta buňky nebo skupiny buněk (angl. Cells). Běžný uživatel si pod tímto pojmem může zhruba představit jednu nebo více vybraných (označených) buněk. Každá buňka má řadu vlastností - způsob výplně, font použitý pro zobrazení obsahu apod.

Z hlediska diskutované problematiky patří mezi dvě nejdůležitější vlastnosti vzorec (angl. Formula) a hodnota (angl. Value). Tyto vlastnosti jsou navzájem svázány. Vztah mezi nimi by se dal vyjádřit takto: Vzorec (Formula) je předpis pro výpočet hodnoty (Value) nebo také naopak - hodnota je vypočítaný vzorec. V buňce je pak uložen vzorec, uživateli zobrazena jeho hodnota. Jde-li o aktivní buňku, pak je vzorec zobrazen nahoře nad záhlavími sloupců v řádku vzorců.

Pokud uživatel vloží do některé buňky "ručně" (z klávesnice) data některého z výše uvedených typů, budou vzorcem přímo tato data a pak Formula = Value. Jde-li o aktivní buňku, je v tomto případě obsah buňky i obsah řádku vzorců stejný.

Pokud uživatel vkládá do buňky vzorec, povinně musí být prvním znakem znak rovnítka (=). Za tímto znakem následuje tzv. výraz (angl. Expresion). Pro výraz platí striktní syntaktická pravidla, která musí být dodržena, jinak bude obsah buňky označen za chybný. Níže je uvedena definice výrazu. Výrazem je pak jedna z následujících konstrukcí (značí přitom symbol Ψ libovolný výraz, symbol ⊗ operační znaménko):

 

  Konstrukce Popis Příklad
1 Konstanta Zápis konkrétní datové hodnoty Číselná konstanta: 125 nebo -19,825 nebo 19/4/2017
Textová konstanta: "Ahoj" nebo "Jan Novák"
Logická konstanta: NEPRAVDA nebo PRAVDA
2 Proměnná Adresa buňky nebo její název (hodnotu buňky lze měnit, odtud proměnná) Adresa: B19 nebo GB$120
Název: SazbaDPH nebo Den_Nákupu
3 Operace ⊗ Ψ (unární operace)
Ψ ⊗ Ψ (binární operace) 		Unární operace: -A3
Binární operace: Den_Nákupu+14
4 Volání funkce Hodnota vrácená provedením knihovní funkce Excelu nebo vlastní naprogramované funkce. Značí-li F jméno nějaké funkce, pak volání funkce má obecně tvar F(Ψ ; Ψ ; ... ; Ψ) ZAOKROUHLIT (A3 ; 3)
5 (Ψ) Libovolný výraz uzavřený v kulatých závorkách (A3 * sin (2 + Beta))

 

Uvedená definice pojmu Výraz je tzv. definicí rekurzivní: už při definici pojmu se používá definovaný pojem. Jde o poměrně komplexní způsob zavádění nových pojmů, který však může - setká-li se s ním čtenář poprvé - působit jisté problémy. Proto pro procvičení nechť čtenář rozmyslí, zda níže uvedená konstrukce je nebo není výrazem:

3 * A1 - 4 - C9 + sin (2 * G5 + 1)

Nápověda: má-li být uvedené výrazem, musí to být podle předchozí tabulky buď 1 nebo 2 nebo 3 nebo 4 nebo 5.

Pro unavené: stačí v prázdném listu do buněk A1, C9 a G5 vložit jedničky a do některé jiné vzorec

= 3 * A1 - 4 - C9 + sin (2 * G5 + 1)

Pokud po vložení tohoto vzorce bude zobrazena hodnota -1,85888 (obecně nebude zobrazena chyba), shora uvedená konstrukce je jistě výrazem. I pro unaveného čtenáře však zbývá otázka: proč?

Odkazy

Pomocí odkazů se ve výrazech (viz výše) odkazuje na operandy operací a parametry funkcí. Odkazy jsou v Excelu především proměnné, tj. adresy buněk nebo jejich názvy (o názvech viz následující podkapitola). Nejčastěji používanou adresou buňky je písmenné označení sloupce, za kterým následuje číslo řádku (tzv. notace A1), např. GB27. V Excelu lze odkazovat i na jednu nebo více oblastí (tedy jedním odkazem na více buněk), a to i nespojitých (angl. Areas). Např. odkaz B2:D5 odkazuje na obdélníkovou oblast dvanácti buněk, v jejímž levém horním rohu je buňka B2 a v pravém dolním rohu buňka D5.

V tomto odstavci je diskutován pouze odkaz na jedinou buňku. Důvodem je výklad chování Excelu při kopírování (nikoliv přesunu!) vzorců obsahující odkazy typu adresa buňky.

Uvažujme jednoduchou situaci: Pro několik nákupů zboží se známou cenou bez 21% DPH je třeba zjistit DPH i celkovou cenu s DPH. Připravme tedy nejprve první řádek tabulky. Součet ceny bez DPH a DPH v buňce E2 je bez problémů, tedy výpočet DPH v buňce D2:

 

 

Kopírováním buňky D2 dolů pomocí jejího pravého dolního rohu budou doplněny vzorce pro výpočet DPH do dalších buněk:

 

 

Za povšimnutí stojí důležitý mechanismus použitý při kopírování buňky D2 dolů: číslo řádku adresy C2 (tedy 2) ve vzorci bylo zvětšeno o tolik, kolik činil počet řádků, o kolik byl vzorec zkopírován. Např. při zkopírování vzorce o 2 řádky dolů na adresu D4 byla původní adresa C2 změněna o 2 řádky na C4.

Za takovou úpravu vzorců Excel velmi chválíme: pochopil, že když na druhém řádku počítáme DPH z ceny v C2, tak na třetím řádku budeme chtít počítat DPH z ceny v C3 atd.

Nyní však nastane problém: s novou vládou přijdou nové daně. DPH už nebude příjemných 21%, ale velmi nepříjemných 34% (poznámka: modelová situace není z domácího prostředí!). To ovšem znamená zásah do všech dříve vytvořených vzorců obsahujících hodnotu 21. Proto hned na začátku sazbu DPH raději umístíme do samostatné buňky (např. F2) a při změně sazby budeme měnit tuto jedinou buňku:

 

 

Kopírováním buňky D2 dolů pomocí jejího pravého dolního rohu budou doplněny vzorce pro výpočet DPH do dalších buněk, avšak s nenadálým výsledkem:

 

 

Příčinou je shora popsaný mechanismus použitý při kopírování buňky D2 dolů: číslo řádku adresy C2 (tedy 2) ve vzorci bylo zvětšeno o tolik, kolik činil počet řádků, o kolik byl vzorec zkopírován. Např. při zkopírování vzorce o 2 řádky dolů na adresu D4 byla původní adresa C2 změněna o 2 řádky na C4. Stejný mechanismus byl však uplatněn i u adresy F2: i ona byla změněna o 2 řádky na F4 (a tam není nic, což se chápe jako nula).

Ovšem za tuto druhou změnu už Excel nechválíme. Stojíme tedy před otázkou: jak dát Excelu na vědomí, že některá čísla řádků si přejeme zvětšovat, zatímco jiná ne?

Autoři Excelu (stejně jako autoři jiných tabulkových procesorů dávno před Excelem) to zajistili vložením znaku $ (dolar) před to číslo řádku, které si nepřejeme zvětšovat:

 

 

Uvedený příklad popisoval kopírování vzorce směrem dolů. Pokud by se kopíroval směrem nahoru, číslo řádku by se zmenšovalo. Při kopírování ve směru vodorovném by se analogicky měnilo písmeno sloupce (zvětšovalo by se nebo zmenšovalo). Pro zabránění této změny je třeba znak $ (dolar) umístit před písmeno sloupce.

Protože vzorec může být principiálně kopírován i vertikálně i horizontálně, existují 4 možnosti tvaru odkazu typu adresa buňky. Příklad: F11, F$11, $F11, $F$11. Znak $ (dolar) bývá na národních rozložení klávesnic obtížně přístupný, proto autoři Excelu pomáhají takto: umístěním textového kurzoru při zápisu vzorce na adresu (např. pro F11 nejlépe mezi F a 11) dochází postupným stiskem klávesy F4 k cyklické změně mezi uvedenými 4 možnostmi adres se znakem $ (dolar).

Obsahuje-li adresa ve vzorci znak $ (dolar), bývá nazývána adresou absolutní (protože odkazuje na absolutně stejné místo), kdežto adresy bez znaku $ (dolar) bývají nazývány adresy relativními (protože se mění o relativní posun).

Názvy

Pomocí adresy se lze odkazovat na jednu nebo více buněk. V případě odkazu na více buněk může jít např. o řadu nespojitých oblastí, navíc na jiných listech nebo v jiných sešitech. Adresa bývá v tom případě složitá a nepřehledná. V rozsáhlém sešitu s velkou řadou vzorců s odkazy se navíc ztrácí orientace v tom, co vlastně v buňce s konkrétní adresou je - zvláště když je vzorec např. na řádku 5000 a adresa odkazuje na buňku na řádku 2 a není tedy z řádku 5000 vidět.

Excel nabízí ve svých sešitech možnost zavádět pro jednoduché i složité adresy jejich pojmenování = názvy buněk nebo celých oblastí.

Přidělit vybrané (označené) buňce nebo oblasti název lze nejjednodušeji vepsáním tohoto názvu do pole názvů:

 

 

Důsledek se projeví hned při zápise vzorce, který obsahuje odkaz na pojmenovanou buňku:

 

 

Ještě příjemnějším důsledkem je to, že při kopírování takového vzorce se autor sešitu nemusí příliš zabývat rozlišováním relativních a absolutních adres; "hned napoprvé" se povede vytvořit správné výpočty:

 

 

Je to dáno tím, že Excel při popsaném postupu daným názvem pojmenovává absolutní adresy, a to v celém sešitu - pojmenovaná adresa obsahuje nejen znaky $ (dolar) před písmenem sloupce i před číslem řádku, ale obsahuje i označení listu. Pokud se tedy uvede takový název ve vzorci kdekoliv v sešitu, vždy odkazuje do listu, kde se pojmenovaná oblast nachází.

Pokud by uvedený způsob adresace nevyhovoval, lze postupem

Vzorce / Definované názvy / Správce názvů

otevřít formulář Správce názvů, v němž ve spodní části v textovém poli Odkaz na se adresa příslušně upraví.

Důležitá poznámka: Každý název musí být v sešitu jedinečný (nemohou existovat dva stejné názvy). Dále: název musí mít tvar identifikátoru; musí to být posloupnost písmen a cifer začínající písmenem. Nesmí tedy obsahovat zvláště mezeru (!), může však obsahovat znak podtržítka (_), který se počítá mezi písmena.

 

Grafy

Tato kapitola popisuje práci s grafy především na obecné úrovni. Z konkrétních typů grafů se zabývá pouze sloupcovými, spojnicovými a XY bodovými, a to jen s ohledem na některá specifika jejich použití.

O grafech obecně

Terminologie

Graf, nazývaný také v jiných kontextech diagram, je znázornění především číselných dat, ve kterém jsou data reprezentována grafickými symboly. V prostředí tabulkových procesorů bývají těmito symboly obdélníkové nebo válcové sloupce, kruhové výseče, prstence, bubliny apod. Program Excel nabízí řadu grafů používajících různou symboliku s různou informační schopností. Jednotlivé nabízené typy grafů včetně ukázek jsou v použité verzi programu Excel následující:

 

 

 

Ačkoliv se grafy podle typu různí, některé jejich vlastnosti jsou společné. Na často používaném sloupcovém grafu uveďme používanou terminologii.

 

 


        Graf vložený na plochu listu
  1. Ohraničení
  2. Oblast grafu
  3. Zobrazovaná oblast
  4. Datová řada
  5. Datový bod
  6. Legenda
  7. Osa X a její popis
  8. Osa Y a její popis
  9. Název grafu
  10. Vodorovná mřížka

 

Ohraničení z bodu 1. předchozího obrázku je určeno ke změně rozměrů (úchytky v rozích a středech stran) nebo k přemístění (tažením za ohraničení uchopením kdekoliv jinde než ve zmíněných úchytkách). Vodorovná mřížka z bodu 10. předchozího obrázku má ekvivalent ve svislé mřížce, která však na obrázku znázorněna není.

Podle typu mají grafy nejčastěji dvě osy (sloupcový, spojnicový, pruhový, plošný, XY bodový, burzovní, bublinový), žádnou osu (prstencový, výsečový, paprskový) a podle podtypu grafu osy dvě nebo tři (povrchový). Osy jsou dvojího typu:

 

Na předchozím obrázku je jako ad 7. zobrazena osa X, která je v tomto případě osou kategorií (obsahuje tři kategorie), kdežto jako ad 8. je zobrazena osa Y jako osa hodnot v intervalu <0; 14.000>.

Důležitá poznámka: Popisky kategorií jsou vždy chápány textově, i kdyby měly mít jako zdroj čísla - v tom případě bude textový popisek tvořen potřebným počtem znaků, kterými jsou jednotlivé číslice. Toto může být kritický moment při vytváření grafů: chybná volba typu grafu může způsobit zcela mylnou představu o chování sledovaných veličin.

Umístění grafu

Graf může být v sešitu umístěn na "běžném" listu (v tom případě může být na takovém listu grafů více), nebo na samostatném "grafickém" listu (v tom případě je na takovém listu graf jediný). Umístění grafu lze po jeho vytvoření kdykoliv změnit: přesunout graf z běžného listu na nový grafický, nebo naopak odstranit grafický list a graf z něj přesunout na některý list s daty. Pokud je graf na některém listu s daty, pak mu lze kdykoliv změnit polohu, velikost i obsah. Pokud tvoří graf samostatný grafický list, lze mu měnit jen obsah.

Existující graf lze umístit jeho výběrem (označením, aktivací), pak:

Nástroje grafu / Návrh / Umístění / Přesunout graf

a v následně zobrazeném formuláři vybrat jednu z možností:

 

V prvním případě se jako parametr zadá (jedinečné) jméno nově vytvářeného "grafického" listu, do kterého bude graf přemístěn. Ve druhém případě se vybere z rozvíjecí nabídky požadovaný (existující) list, do kterého bude graf přemístěn.

Vytvoření grafu

Graf lze vytvořit principiálně dvěma způsoby:

 

  1. Nejprve se v sešitu označí data, pak se vloží graf - ten již bude zobrazovat vybraná data.
  2. Nejprve se vloží prázdný graf - až posléze se mu přiřadí data, která mají být zobrazena.

První postup je jednodušší a rychlejší, druhý postup je obecnější. Níže jsou popsány oba postupy - směřují k vytvoření základu grafu "Výdaje v domácnosti" (viz bezprostředně předcházející obrázek).

Vytvoření grafu s již zobrazenými daty

Na listu lDomácnost se vybere (označí) oblast A1 : C4. Pak:

Vložení / Grafy / Sloupcový / Dvojrozměrný sloupcový / Skupinový sloupcový

 

Vytvoření prázdného grafu s následným určením dat

Na listu lDomácnost se aktivuje prázdná buňka nesousedící se žádnými daty (!). Pak:

Vložení / Grafy / Sloupcový / Dvojrozměrný sloupcový / Skupinový sloupcový

Na list bude vložen prázdný graf. Pak:

 

Poznámka: Zkopírovat do schránky a vložit do grafu lze rovněž běžným způsobem pomocí kontextových menu (pravý klik).

Popsaný způsob je při tomto postupu zřejmě nejrychlejší. Obecnějším způsobem je volba položky Vybrat data ... z kontextového menu oblasti grafu (pravý klik na oblast grafu):

 


Kontextové menu oblasti grafu

 

Následně je zobrazen formulář Vybrat zdroj dat. Zdrojová data ze zadají buď do textového pole Oblast dat grafu, nebo komplexněji tlačítkem Přidat v levém seznamu Položky legendy (řady).

Úprava (editace) grafu

Graf je poměrně komplexní objekt obsahující řadu komponent, kterými jsou zejména (viz také shora legendu u obrázku Graf vložený na plochu listu)

 

 

Podle typu resp. podtypu grafu nemusí graf obsahovat všechny vyjmenované komponenty nebo naopak může obsahovat další, zde neuvedené součásti.

Upravovat (editovat) lze vybraný (aktivní, označený) graf - přesněji takový, ve kterém je vybrána (aktivní) některá jeho komponenta. Není-li graf vložený na plochu běžného listu aktivní, vybere se kliknutím kamkoliv do něj; tím se současně vybere jeho příslušná komponenta. Tvoří-li graf obsah samostatného "grafického" listu, aktivuje se výběrem tohoto listu. Další postup je společný oběma umístěním.

Vybrat (aktivovat) některou komponentu lze nejjednodušeji kliknutím na ni (levým tlačítkem myši). U některých "složených" komponent však pozor na posloupnost označování. Např. datová řada je množina datových bodů. Pro výběr datového bodu je zapotřebí nejprve vybrat příslušnou datovou řadu a teprve při vybrané datové řadě označit požadovaný datový bod. Analogicky např. při výběru jedné položky legendy, která je součásti legendy jako takové.

Po výběru některé komponenty grafu je hlavní menu programu Excel rozšířeno o tři položky z karet nástrojů grafu: Návrh, Rozložení a Formát. Zkušený uživatel dobře seznámený s terminologií (a zejména českými překlady) může pomocí těchto třech položek přidávat, odebírat a upravovat v podstatě kteroukoliv komponentu grafu.

Formát vybrané (aktivní) komponenty lze nastavit daleko jednodušeji než náhodným putováním po zmatených kartách. Pravý klik na vybranou komponentu XY totiž zobrazí kontextové menu této komponenty, kde poslední položka je vždy Formát komponenty XY. Její volbou je předložen formulář Formát XY pro nastavení všech vlastností této komponenty.

Pro milovníky zmíněných karet lze uvést i postup karty využívající: po výběru příslušné komponenty grafu aplikovat

Formát / Styly obrazců /

Po stisknutí tlačítka    je zobrazen stejný formulář Formát XY jako postupem popsaným výše.

Některé aspekty použití grafů

Sloupcové grafy

Sloupcové grafy jsou samy o sobě celkem fádní, patří však mezi hojně používané. Přesto je poměrně málo znám velmi jednoduchý způsob jejich zpestření:

 


      Běžný sloupcový graf
      Sloupcový graf s obrázkem
      Sloupcový graf se skládaným obrázkem

 

Výplň všech (nebo i jednotlivých) sloupců datové řady lze samozřejmě zajistit z formuláře pro formát datové řady nebo datového bodu klasickým (a tedy poměrně zdlouhavým) vyhledáním souboru. I to je jedno z omezení - výplň musí tvořit soubor některého obrázkového typu. Daleko jednodušší a obecnější je využití faktu, že substituci výplně označených (aktivních) datových bodů provede vložení obsahu schránky. Celý postup je pak následující:

 

Pokud je na závadu vertikální deformace, nastaví se ve formátu výplně datové řady nebo bodu při volbě Obrázková nebo texturová požadavek Skládat.

Grafy XY vs. grafy spojnicové

Jde o klasickou situaci: student finišující na své diplomové práci pojme úmysl vložit k textu své práce čárové zobrazení např. průběhu teploty v jistém dni. Data má připravená v tabulce:

 

HODINA

TEPLOTA

4

5

5

6

8

8

10

14

11

16

15

17

16

17

19

15

23

9

 

Podle zaběhnutého schématu data označí a začne vkládat graf. První nabídnutý jsou sloupce, to nechce, ovšem hned druhý nabídnutý jsou nějaké čáry - to je ono, šup tam s ním. Sice místo jedné čáry teploty uvidí ještě jednu čáru navíc, ale to DELETE spraví. Rovněž popis vodorovné osy X čísly od 1 do 9 mu dá zabrat, ale nakonec zvítězí:

 


      Graf teploty spojnicový

 

Ovšem zvítězil-li student nad grafem, rozhodně nezvítězil nad oponenty své práce. Studentem předložený graf totiž deformuje představu o skutečném průběhu teploty ten den. Už jen pohled závěr dne: podle spojnicového grafu teplota viditelně strmě klesala - což je v kontrastu se skutečným průběhem, jak dokumentuje správně zvolený typ grafu - XY bodový:

 


      Graf teploty XY bodový

 

Rovněž skutečný průběh teploty do osmé hodiny se diametrálně liší od průběhu znázorněném spojnicovým grafem.

Pochybení spočívá ve špatné volbě typu grafu. Spojnicový graf totiž používá vodorovnou osu X jako osu kategorií, zatímco graf XY používá vodorovnou osu X jako osu hodnot. Ve spojnicovém grafu jsou tedy hodiny 4, 5, 8 atd. chápány nikoliv jako hodnoty, ale jako kategorie (viz např. sloupcový graf) - proto jsou datové bodu od sebe vodorovně vzdáleny stejně, jsou ve stejném "rozestupu". Naopak graf XY umístí horizontálně datový bod podle hodnoty souřadnice X (zde hodiny).

Pozor na grafy funkcí

Grafy v Excelu znázorňující průběh funkce jsou založeny nikoliv na analytickém předpisu funkce, ale na množině dat tvaru [xi,{yi1,i2,...}]. Za "funkční čáru" je pak vydávána lomená (nebo "hladká") čára spojující jednotlivé body. Právě to může nevhodnou volbou bodů funkce zcela zkreslit představu o průběhu funkce.

Modelová funkce

Jako modelovou funkci pro ukázku zmíněné citlivosti na volbu bodů funkce zvolme rovnici tlumeného harmonického oscilátoru
 

                 (1)

s parametry A0=3;   d=0,03;   W=3;   j=0.

Poznámka: Čtenáři se nemusí děsit ani fyziky, ani matematiky. Stačí zavěsit kuličku s ouškem na špagátek, postrčit ji a koukat, jak se kýve, kýve, kýve ... A případně zapsat uvedený vztah jako vzorec v Excelu, to by mělo být obsahem učiva na střední škole.

Sledujme chování funkce (1) pro časový interval <0,100>. V něm je skutečný průběh funkce následující:
 


Obr. 1: Skutečný průběh funkce (1)

Dvě mylné představy

Pro znázornění průběhu funkce grafem XY je zapotřebí vytvořit tabulku funkčních hodnot ("tabelovat funkci"). Nejprve měňme nezávisle proměnnou (=čas) t s nějakým krokem; od 0 do 100 je daleko, nejdříve tedy člověka napadne hodnota kroku 1. Dobrá tedy, tabulka funkčních hodnot je
 

t y
0 3,00000
1 -2,88220
2 2,71276
3 -2,49813
4 2,24529
... ...
97 -0,06404
98 0,04094
99 -0,01835
100 -0,00330

Tab. 1: Tabelace funkce s krokem 1

 

Graf XY takto tabelovaných hodnot je na následujícím obrázku:
 


Obr. 2: Graf hodnot z tabulky 1 - jen čáry

 

Je pravdou, že oko nematematika, nefyzika je potěšeno - graf je pohledný, harmonický, pěkně se vlní kolem osy X (hle, osciluje!) a dokonce čím dál míň, jako by ho tlumila únava. A zmíněný nematematik, nefyzik odchází s dobrým pocitem, že už ví, co to je tlumený harmonický oscilátor.

Nechť nyní jiný nematematik, nefyzik si nechá stejné hodnoty zobrazit ne spojnicemi bez bodů, ale body bez spojnic:
 


Obr. 3: Graf hodnot z tabulky 1 - jen body

 

Ovšem tento druhý nematematik, nefyzik má zcela jinou představu než první. Především jeho oko vidí ne jednu, ale dvě čáry (mimochodem s celkem správným zobrazením, nikoliv však s danými parametry) s celkem pochopitelným průběhem - také jsou pohledné, harmonické, také se vlní a také tlumeně. Ale jeho mysl je zmatena; z hloubi své paměti doluje útržky informací spojených s oscilátorem - snad kulička (jedna), závaží na pružině (jedno) nebo tak něco. Kde se tedy vzalo to druhé a co to je? Tento druhý nematematik, nefyzik už s tak dobrým pocitem neodchází.

Zdroj problému

Zdroj problému spočívá ve volbě kroku. V uvedeném případě byl zvolen krok roven 1; pro správnou - byť mnohdy hrubou - představu je zapotřebí volit krok menší než čtvrtina intervalu mezi dvěma "sousedními" inflexními body, což zde splněno není. Nastalo zde tedy toto: body tabelované s krokem 1 samozřejmě leží na funkční křivce, ale takto:
 


Obr. 4: Poloha tabelovaných bodů na křivce

 

Objekt třídy "Graf", který Excel používá, pak jen mechanicky spojí úsečkou dva sousední body z množiny zadané jako "Zobrazovaná data" (to mimochodem vyžaduje, aby data pro smysluplný XY graf byla seřazena vzestupně dle X). Výsledkem je pak místo správného (modrého) průběhu nesprávný (červený):
 


Obr. 5: Mylný průběh vzhledem ke skutečnému

 

Závěr

Odstranit problém se zdá jednoduché: stačí zjemnit krok. Je však zapotřebí si uvědomit, že Excel je široce používaný program právě zmíněnými ne-matematiky a ne-fyziky, kteří se občas ve svém oboru dostanou k nutnosti "podívat" se na funkční závislost. Tito uživatelé rozhodně neprovádí nejdřív analýzu funkce. A ruku na srdce, i mnohý matematik a fyzik při potřebě rychle získat představu o nějaké funkci se dlouho přípravou dat nezabývá.

Zde zmíněné jádro problému tuší dobře ti, kdo se pokusili jednou řadou vykreslit velmi jednoduchou čáru - hyperbolu y = 1 / x. V nule tabulková hodnota spočíst nejde, a místo aby graf zobrazil známé dvě křivky asymptoticky se přibližující ose Y, spojí poslední záporný a první kladný bod nesmyslnou úsečkou.

Je proto třeba mít uvedené skutečnosti na paměti a není-li průběh funkce z jejího předpisu obecně znám, udělat několik pokusů s různými kroky a nejlépe na různých intervalech.

 

 

 

 

Literatura a další výukové zdroje

[1] Informatika. In: Wikipedie: Otevřená encyklopedie [online]. Wikimedia Foundation, 2003. [cit. 25.1.2021]. Dostupné z: https://cs.wikipedia.org/wiki/Informatika

[2] Spreadsheet. In: Wikipedia: The free encyclopedia [online]. Wikimedia Foundation, 2003. cit. 25.1.2021]. Dostupné z: https://en.wikipedia.org/wiki/Spreadsheet

[3] International System of Units (SI): Prefixes for binary multiples. National Institute of Science and Technology. [cit. 25.1.2021]. Dostupné z: https://physics.nist.gov/cuu/Units/binary.html

[4] Rounding. In: Wikipedia: The free encyclopedia [online]. Wikimedia Foundation, 2003. [cit. 25.1.2021]. Dostupné z: https://en.wikipedia.org/wiki/Rounding

[5] Endianness in: TANNENBAUM, A. S., AUSTIN, T.: Structured Computer Organization. 6th edition. Boston: Pearson, 2013. ISBN 9780132916523.

[6] Institute of Electrical and Electronics Engineers: IEEE Standard 754-1985 for Binary Floating-Point Arithmetic, IEEE 1987. Reprinted in SIGPLAN 22, 2, 9-25.