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

Excel Tables - analogie tabulek relačních databází

Doplnění učebních textů pro distanční vyučování

doc. Dr. Vladimír Homola, Ph.D.

Ostrava 03 / 2022

 

 

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

Díl IV: Rozšiřující funkčnost Excelu pro práci s analogií DB tabulek

Článek je možno stáhnout jako soubor PDF zde.

Použité konvence, cvičná data

V tomto textu jsou popisovány postupy v prostředí tabulkového procesoru Excel, edice Microsoft Office 2016. Z hlediska uspořádání a ovládání jsou oproti verzi 2010 jen minimální (většinou jen kosmetické) změny, proto pro kompatibilitu se základním textem rozšiřovaných skript (viz [1]) ponecháváme tam uvedené odstavce beze změny. Ve vlastním rozšiřujícím textu už ovšem budou uvedena schémata Excelu 2016.


        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

 

Pro navigaci při jednotlivých dílčích akcích bude použita následující 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í.

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. jeho nastavení.

Problematika je vysvětlována na datech cvičného sešitu, který lze stáhnout z adresy

hom50.cz/CVICGMT.XLSM

Jména, identifikátory

Jména a identifikátory obecně

Autoři projektů (sešitu, prezentace, databáze, programu) především pro svou vlastní orientaci označují části vyvíjeného nebo upravovaného projektu tak, aby jim takové označení na první pohled připomnělo význam a funkci jednotlivých částí. Ke mnemotechnickému pojmenování používají autoři (zde hlavně sešitů) konstrukci od pravěku programování nazývanou Identifikátor. Zopakujme celkem jednoduchou definici:

Identifikátor je posloupnost písmen a číslic začínající písmenem

tedy např. Alfa3, Kilometry - ale nikoliv 3Alfa (nezačíná písmenem) a nikoliv ani Den svatby (obsahuje mezeru, což není ani písmeno ani cifra).

Identifikátor se začal používat pro pojmenování nejen v programech, ale např. i ve vznikajících systémech zpracování dat, které se dopracovaly k dnešním databázím v nejrůznějším pojetí. Zpřístupněním hardware i software víceméně komukoliv se začal projevovat problém právě s identifikátory - zvláště s umožněním komukoliv definovat a plnit třebas právě nějaké datové struktury.

Zatímco Homo Computatrum (česky označovaný jako Ajťák) nevidí v zavádění a používání identifikátorů žádný problém, pak Homo Ordinarius (Člověk Běžný) si zoufá. Tento druh Homa neustále naráží hlavně na dva problémy:

 

  1. Především nechápe, proč by datum z účtenky nemohl nazvat Den nákupu, když to toho dne skutečně nakoupil. I když by nebyl sám autorem nějakého datového zdroje ale jen jeho prohlížitelem, prostě datovou položku Den nákupu tam neuvidí. Nanejvýš třeba DENNAKUPU a to je nečtivé a vůbec ošklivé.
  2. Zvláště v době celosvětové epidemické fyzické izolace jedinců probíhá čilá komunikace příslušnými prostředky. I když zmíněný běžný uživatel má snahu dodržet pravidla pro tvorbu identifikátoru a použije třebas Pořadí, nechápe proč s takovými zaslanými daty má jeho běžná americká přítelkyně problémy.

Protože množina běžných uživatelů je obrovská a je to tedy pro Ajťáky zdroj obrovských příjmů, bylo třeba běžným uživatelům vyjít vstříc.

První vstřícný krok směřoval k tomu, aby uživatel viděl označení dat pro něj rozumně čitelným způsobem. Kromě identifikace dat pomocí jména (name) byl zaveden další atribut, titulek (caption). Zatímco uživatel vidí v grafickém prostředí titulek, uvnitř je položka uložena a programově přístupná pod jménem. Pokud autor dat neurčí nějaké datové položce titulek, uvidí uživatel jméno - to musí být definováno vždy.

Zatímco pro jméno byl stále vyžadován tvar identifikátoru, titulek mohl (a může) obsahovat vcelku libovolný znakový řetězec.

Problém uvedený shora jako ad 2. vznikl doslovnou interpretací definice identifikátoru v americkém (stejně tak v německém, francouzském a jiném) národním prostředí. Angličtina má jak známo 26 písmen: a, b, ..., y, z. Taková podivnost jako je např. „r“ nad kterým je ještě „v“ - to rozhodně v Americe není písmeno.

Postupem času se striktní omezení tvaru jména na identifikátor změkčilo:

Jméno má tvar identifikátoru nebo znakového řetězce uzavřeného mezi hranaté závorky „[“ a „]“.

Zmíněný znakový řetězec může obsahovat víceméně libovolné znaky (tedy nejen písmena a cifry) s výjimkou několika málo speciálně využívaných znaků; velmi často se nepřipouští „*“ nebo „#“ - k tomu viz dokumentace daného programového systému.

Platná jména jsou tedy např. [Den nákupu], [% DPH], Kilometry, [Kilometry] (=Kilometry) nebo [Pořadí] - zde je znak s diakritikou a předejde se tedy zmíněnému problému v ne-českém prostředí. Tím se umožnilo i běžnému uživateli pojmenovávat své objekty pro něj čitelným textem a titulek to v mnoha případech učinilo zbytečným.

Poznámka: Při tvorbě identifikátoru by se měl autor seznámit s „case sensitivity“ (citlivostí na velikost písmen) používaného software. Některé programové systémy jsou citlivé na velikost písmen (X a x jsou dvě různá písmena), některé nejsou (ALFA i ALfa i alfa jsou stejné identifikátory). Excel v případě identifikátorů např. oblastí není citlivý, Vydaje i VYDAJE jsou pro něj stejné.

Pojmenování v Excelu

Bohužel shora uvedené změkčení tvaru jména není dosud platné obecně. Některé programové produkty způsob pojmenování změnily kompletně podle uvedené úpravy, někde naopak k ní nepřešli vůbec. Existují ovšem i produkty (a Excel k nim zatím patří), ve kterých lze v některých kontextech používat ono „měkčí“ pojmenování, jinde je nutno použít původní striktní definici identifikátoru.

Pojmenování svým vlastním názvem se začasté používá již při přípravě sešitu a jeho dat. Pro plynulou a bezkolizní přípravu je ideální dobře znát objektový model sešitu, což evidentně nelze požadovat od běžných uživatelů. Proto v dalším textu bude na vyžadovaný nebo povolený tvar vlastního pojmenování explicitně upozorněno.

Pro představu: Jako jméno tabulky (obecně oblasti) musí být použit identifikátor, zatímco jména sloupců nemusí tento striktní požadavek splňovat.

Prefix identifikátoru

Pro ty autory - zvláště složitějších - sešitů, kteří hodlají používat své vlastní identifikátory, je vhodné uvést doporučení Microsoftu:

Poznámka: Doporučení, jak známo, nejsou závazná.

Často při koncipování sešitu autor navrhuje několik jeho komponent odlišného typu, které však z hlediska řešené problematiky spadají do stejné logické oblasti - např. list ZAMĚSTNANCI, tabulka ZAMĚSTNANCI, graf ZAMĚSTNANCI atd. Aby se autor vyhnul kolizi v případě duplicitních identifikátoru (i když by nemusela nastat), doporučuje se jako první jeden až tři znaky identifikátoru (tzv. prefix) zvolit písmena mnemotechnicky vyjadřující typ pojmenovávaného objektu. Konec konců i pro autora samotného je výhodné, když se po delší době k sešitu vrátí, že mu prefix připomene kontext použití daného objektu. Např.

 

Počet písmen prefixu i písmena samotná volí autor podle svého uvážení a vůbec nemusí korespondovat s pojmenovávaným objektem.

Znovu podotýkáme, že jde pouze a jen o doporučení. Na některých místech je však v tomto článku i doprovodném sešitu použito - tak aby čtenář věděl, že nejde o překlepy J

Tabulky databáze obecně a v programu Excel

Excel sám je chápán jako tabulkový procesor - tedy program zpracovávající data pojímaná jako tabulka - ovšem v původním pojetí označovaném jako Spreadsheet. Běžný uživatel si tedy pod pojmem Tabulka představí list mající R řádků x S sloupců, kde S a R jsou rozměry listu. Zde dochází k nepochopitelnému zmatení pojmů autory Excelu, kdy původně celkem příhodně nazvaný Seznam (= List, List Of Records) debilně přejmenovali na Tabulku, tedy pojmu používanému v nejrůznějších kontextech. Aby to praštilo (podle jejich mínění) do očí, zavedli pojem Tabulka Excelu. Pilný pronikatel do tajů Excelu teď tedy musí pečlivě rozlišovat mezi Tabulkou a Tabulkou Excelu. Nedej Bože, že tím pronikatelem je tupý akademický pracovník, celoživotní pracovník v oblasti IT databází. Dlouho, velmi dlouho musí hledat ve zcela zmateně přístupných Helpech Microsoftu, aby zjistil „vocogou“. Teskně vzpomíná na jednoduchost své habilitační, doktorské a jiné práce ...

Tabulka relační databáze

Poznámka: V tomto odstavci vůbec nejde o význam dat, ale pouze o jejich formální uspořádání. Pokud by se však nad nimi chtěl čtenář zamýšlet, autoři doufají, že zvolili příklad názorný, i když zvláště z hlediska hodnot ve druhém sloupci - v dlouhodobém kontextu hodnot sloupce prvního - smutný.

Ačkoliv je celá myšlenka relačních databází založená na matematickém aparátu známého z teorie množin, relačních operací a dalších, pro potřeby běžného uživatele postačí náhled na element uspořádání dat (což je ona relace) jako na zdánlivě jednoduchou a běžnou tabulku, např. nějakého seznamu svých výdajů:

 

DATUM KC CO MNOZSTVI JEDNOTKA DRUH OBCHOD JEDCENA
05.01.2021 15,00 Chléb normální 0,600 kg CH D  
05.01.2021 19,00 Rohlíky 10,000 ks CH D  
05.01.2021 41,00 Máslo 0,250 kg TU D  
05.01.2021 50,00 Kapucino 0,143 kg KA D  
06.01.2021 129,00 Bohemia Brut 0,750 l AL A  
06.01.2021 11,40 Rohlíky 6,000 ks CH A  
06.01.2021 16,40 Rajčata 0,222 kg ZE A  
07.01.2021 9,00 Řezy z matjesa 0,040 kg RD D  
07.01.2021 33,80 Hermelín 0,120 kg ML D  
... ... ... ... ... ... ...  

 

Ona jednoduchost má však svá omezení a uživatel z nich obecně při přípravě dat nesmí vybočit:

 

 

Tabulka relační databáze jako Tabulka Excelu

Autoři Excelu po delší době začali řešit absenci databázových nástrojů pro data v listech sešitu, které autor sešitu uspořádá dle výše uvedených pravidel. Ta jsou však poměrně striktní, proto Excel akceptuje jisté odchylky:

 

Pokud uživatel připraví data právě uvedeným způsobem a vybere (označí) alespoň jednu buňku takového obdélníkového uskupení dat, Excel sám je schopen rozpoznat celou tabulku za předpokladu, že vlevo a vpravo je alespoň jeden volný sloupec (nebo jsou data zapsána od sloupce A) a že nad a pod je alespoň jeden volný řádek (nebo jsou data zapsána hned od prvního řádku listu). Stačí tedy jako aktuální buňku učinit kteroukoliv buňku oblasti, a následné činnosti týkající se databázového zpracování budou probíhat v takto rozpoznané oblasti. Výběr (označení) "ručně" lze provést také klávesová zkratka Ctrl/A. Toto platí obecně, je to však málo.

Data připravená podle předchozího odstavce sice splňují požadavky pro databázové zpracování, ovšem zvláště při periodickém přidávání řádků v reálném provozu jen toto je nedostačující. Vždyť pro následné zpracování takových dat bývají připravené vzorce, navazující (např. kontingenční) tabulky, grafy apod. Při změně oblasti dat by se všechny tyto objekty musely minimálně přeadresovávat nebo často nově polohovat.

Dále: uživatel už jen pro prostou kontrolu potřebuje na připravovaná data nazírat různým způsobem - např. je vhodně řadit nebo na chvíli nějak odfiltrovat. Takových obvyklých požadavků je řada a skutečné databázové programy je svým uživatelům nabízí automaticky.

Autoři Excelu se dopracovali k objektové třídě, kterou nazvali shora kritizovaným pojmem Tabulka. Autor sešitu vytvoří instanci této třídy (tj. funkční tabulku v pojetí Excelu) z jediné (!) obdélníkové oblasti, ve které předem připravil svá data podle pravidel uvedených shora. Poté aktivuje posloupností

Vložení / Tabulky / Tabulka

 


 

dotaz na oblast s daty a informaci, zda má první řádek považovat za řádek s nadpisy sloupců (a důrazně se doporučuje připravit své nadpisy):

 


 

Výsledkem bude v zadaném umístění vytvořená Excelem pojmenovaná oblast opatřená v prvním řádku rozvíjecími seznamy (combo box) s nabídkami pro řazení, filtrování a vyhledávání. Oblast bude většinou divoce strakatě naformátována, což se dá zrušit běžným postupem

Nástroje tabulky / Návrh / Styly tabulky /   / Vymazat

Důležitější je však vlastní pojmenování tabulky, které musí mít tvar identifikátoru. Excel při jejím pojmenování generuje nekolidující identifikátor (většinou tvaru např. Tabulka24), který každý soudný uživatel bezprostředně po vytvoření změní postupem

Nástroje tabulky / Návrh / Vlastnosti / Název tabulky:

a tam vepíše svůj vlastní identifikátor (klidně třeba ABCD - ovšem s rizikem, že si za měsíc už vůbec nevzpomene, která že data takhle pojmenoval):

 


 

Na hořejším obrázku je rovněž (modře) vyznačeno tlačítko, kterým lze případně později oblast prohlášenou za tabulku Excelu převést zpět na "normální" data.

Kromě nástrojů pro řazení a filtrování však takto vytvořená oblast disponuje doposud chybějící vlastností: pokud uživatel začne zapisovat bezprostředně za poslední řádek dat, pojmenovaná oblast se automaticky rozšíří směrem dolu o jeden řádek - přesněji o jednořádkový blok vedle sebe stojících buněk v počtu rovném šířce tabulky.

Některé možnosti tabulky Excelu

Filtrování a řazení

Po převodu obdélníkové oblasti na tabulku Excelu je implicitně aktivována možnost filtrování a řazení, i pro „normální“ data dostupná pomocí

Data / Seřadit a filtrovat / Filtr

Projeví se zobrazením tlačítek  rozvíjecích seznamů v řádku nadpisů sloupců. Seznamy obsahují stejné nabídky se stejnými možnostmi jako pro data nepřevedená na tabulku Excelu. Stejně tak lze tlačítkem Filtr na kartě Seřadit a filtrovat zobrazení rozvíjecích tlačítek vypnout resp. znovu zapnout.

Poznámka: Pro označení řádku s nadpisy sloupců tabulky Excelu zvolili autoři termín Headers.

Stejně jako pro „normální“ data pracuje nástroj pro obecné řazení

Data / Seřadit a filtrovat / Seřadit

Ve formuláři pro nastavení kritérií řazení se však vždy nabízí názvy sloupců (tedy už není možnost volit písmeno sloupce listu), protože tabulky Excelu vždy obsahují záhlaví tabulky s názvy sloupců (ať explicitně určené autorem tabulky nebo implicitně vytvořené Excelem.

Novější verze MS Office nabízí pro filtrování vztažené k Tabulkám Excelu a Kontingenčním Tabulkám další nástroj - Slicers, v českých lokalizacích překládán jako Průřezy. Informace o tomto nástroji je poměrně obsáhlá a je uvedena jako samostatná pod-kapitola níže.

Agregační funkce v řádku souhrnů (Totals)

Stejně jako mnohé databázové programy umožňuje Excel pod posledním řádkem dat tabulky Excelu zapínat a vypínat zobrazení řádku, do jehož jednotlivých buněk má autor dat možnost umísťovat vzorce obsahující především tzv. agregační funkce (součet, počet, průměr a další). Provede to postupem

Nástroje tabulky / Návrh / Možnosti stylů tabulek / Řádek souhrnů

 


 

Vložený řádek souhrnů posune všechna data pod tabulkou (v šířce rovné šířce tabulky) o jeden řádek dolů. Aktivací některé buňky tohoto řádku bude zobrazena nabídka předvolených agregačních funkcí pracujícími nad všemi daty aktuálního sloupce tabulky:

 

 


 

Pod první sloupec se v řádku souhrnů standardně umístí text Celkem, který lze jednak přepsat jiným textem, jednak lze do této buňky namísto textu umístit vzorec s agregační funkcí.

Filtrování pomocí průřezů (Slicers)

Excel ve vyšších verzích Office rozšiřuje možnost filtrování dat v Tabulkách Excelu (Excel Tables), v Kontingenčních tabulkách (Pivot Tables), a v datových zdrojích dostupných buď pomocí připojení nebo pomocí datového modelu.

Z důvodu zaměření této publikace budou popsány jen průřezy filtrující data Tabulek Excelu a Kontingenčních tabulek aktivního sešitu. Z hlediska uživatele jde o grafický nástroj s řadou tlačítek připojený ke zdroji dat, kterým je tedy jeden ze dvou uvedených typů tabulek. Následující příklad ukazuje použití dvou průřezů připojených ke stejné Tabulce Excelu:

 



Příklad dvou různých průřezů připojených ke stejnému zdroji

 

V uvedeném příkladu filtruje první průřez měsíc Březen (vybrány jsou jen březnová data), druhý průřez filtruje obchod Hornbach (vybrána jsou jen data Hornbachu). Pokud jsou různé průřezy připojeny ke stejnému zdroji dat, jsou filtry jako logické podmínky vázány operací "a současně". Uvedený příklad tedy vybírá data z března a současně z Hornbachu (kostrbatě matematicky) = březnová data z Hornbachu (uhlazeně třeba pro moji babičku J).

Vytvoření pro tabulku Excelu

Základem jsou "normální" data, ovšem zaznamenaná podle pravidel relační databáze (viz) - tedy obdélníková oblast tvořená několika sloupci s nadpisy a daty v každém sloupci stejného typu. Formátování písma je irelevantní, stejně tak např. ohraničení buněk, jejich výplň a jiné. Oblast může být pojmenována (zde dtStaveb):

 



Výchozí data připravená pro převod na Tabulku Excelu

 

Postupem uvedeným shora se data převedou na Tabulku Excelu. Doporučuje se ji hned pojmenovat (zde tbStaveb) - toto pojmenování tabulky (Table) nemá žádný vztah k pojmenování oblasti v listu s daty (Range):

 



Data převedená na Tabulku Excelu

 

Další krok již směřuje k vlastnímu vytvoření jednoho nebo více průřezů. Excel je vytváří pro ten datový zdroj, v němž se nachází aktivní buňka nebo oblast (Selected Range). Je tedy zapotřebí nejprve aktivovat alespoň jednu buňku uvnitř příslušného datového zdroje a zadat posloupnost příkazů

Vložení / Filtry / Průřez

nebo

Nástroje tabulky / Návrh / Nástroje / Vložit průřez

 



Příkaz pro vložení průřezu

 

Pomocí následně předloženého formuláře se sdělí, pro které sloupce jsou požadovány průřezy:

 



Volba průřezů a jejich propojení na sloupce zdroje

 

Po volbě OK vytvoří Excel příslušný počet průřezů v implicitní poloze s implicitním nastavením, např. průřez pro data sloupce MESIC:

 



Implicitně navržený tvar průřezu

 

Průřezy je možno ihned začít používat. Následující text se bude zabývat jen jedním průřezem, pro ostatní je postup adekvátní.

Použití průřezů

Průřez jako grafický ovládací prvek je vázaný na konkrétní list, ale nikoliv na buňku v něm. Uvedeným postupem ho Excel vloží na list se zdrojovými daty. Vytvořený průřez lze přemístit na jiný list běžnou operací Vyjmout (Cut) + Vložit (Paste).

Průřez jako ovládací prvek může být ve stavu "Připraven k použití" nebo "Vybrán pro editaci". Výběr pro editaci se provede nejjednodušeji myší obvyklým l-click kamkoliv dovnitř volné plochy (nikoliv na tlačítka s popisem hodnot sloupce). Vizuálně je výběr pro editaci znázorněn orámováním obdélníkem, v jehož rozích a středech stran jsou zobrazeny úchyty pro tažení:

 



Průřez vybraný pro editaci

 

Příprava k použití (tj. zrušení režimu editace) se provede rovněž obvyklou aktivací nějaké - nejlépe prázdné - buňky listu.

Je-li průřez vybrán pro editaci, je hlavní menu doplněno o položku

Nástroje průřezu / Možnosti

jejíž aktivací je získán přístup k nastavení řady vlastností. Nejdůležitější jsou karty

 

 

pro zadání vlastního titulku (Caption), počtu sloupců tlačítek, jejich výšku a šířku, a výšku a šířku celého průřezu. Milovníci barviček se pomocí tohoto pásu karet mohou barevně vyřádit a dovést průřez k úžasné strakatosti.

Nastavení průřezu a jeho vlastností lze také pomocí r-click dovnitř volné plochy průřezu, a z předloženého kontextového menu zvolit jednu ze dvou posledních nabídek:

 

 

Po volbě nabídky Velikost a vlastnosti jest uživateli ukotven na pravou stranu panel Formát průřezu:

 



Formulář pro grafické nastavení průřezu

 

Jednotlivá pole jsou vcelku pochopitelná, jen k pozici: počátek [0,0] je v levém horním roku buňky A1, osa X směřuje vpravo, osa Y dolů. Pozici lze rovněž přibližně nastavit přemístěním celého průřezu tažením. Velikost výšky a šířky celého průřezu lze přibližně nastavit také tažením za úchyty průřezu vybraného k editaci.

Počet sloupců a výšku tlačítka (resp. všech tlačítek stejně) je nutno zadat „ručně“, šířka tlačítka (resp. všech tlačítek stejně) se pak automaticky přizpůsobuje šířce celého průřezu. Rovněž jsou svázány položky velikosti (výška a šířka celého průřezu) v délkových jednotkách a v procentech.

Po volbě nabídky Nastavení průřezu jest uživateli předložen formulář Nastavení průřezu:

 



Formulář pro nastavení průřezu

 

Název zdroje (Source Name - zde MESIC) v prvním řádku informuje o jménu sloupce, ke kterému je průřez připojen. Jde o vlastnost objektové třídy SlicerCache (přesněji o klíč do kolekce SlicerCaches), který generuje automaticky Excel. Vlastnost je ReadOnly a tedy nelze měnit (ani programově). Při požadavku na změnu připojení je nutno odstranit celý průřez a vytvořit nový.

Datové pole Název obsahuje identifikátor, pod kterým bude průřez dostupný jako grafický objekt. Týká se především programového přístupu, běžný uživatel Excelu ho snad ani nevyužije. Jde o klíč do kolekce Shapes listu a současně do kolekce Slicers instance SlicerCache.

Poslední část formuláře „Řazení a filtrování položek“ se týká tlačítek (Slicer Items) resp. textových popisků v nich. Excel nejprve interně generuje množinu všech různých hodnot z připojeného datového sloupce. Průřez pak bude mít tolik tlačítek, kolik je těchto různých hodnot (ne všechna tlačítka však musí být zobrazena). Popisek každého tlačítka je jedna z generovaných různých hodnot.

V této třetí části formuláři lze ovlivnit pořadí zobrazených tlačítek podle jejich popisků. Pokud není zaškrtnuta volba „Při řazení použít vlastní seznamy“, pak se uplatní řazení podle hodnoty; pokud se do číselného sloupce připletou textové hodnoty, jsou tato tlačítka za všemi číselnými „dle abecedy“. V tom případě je význam voleb „Vzestupně“ a „Sestupně“ zřejmý.

Pokud však volba „Při řazení použít vlastní seznamy“ zaškrtnuta je, pak záleží na tom, zda a jak má uživatel ve své instalaci Excelu naplněn svůj vlastní seznam - viz nastavení

Soubor / Možnosti / Upřesnit / Obecné / Upravit vlastní seznamy

 

 

V případě použití hodnot z vlastního seznamu se řadí skutečně podle pořadí v tomto seznamu, volby řazení (vzestupně a sestupně) se vztahují k tomuto pořadí, nikoliv k hodnotám dat.

 

Nyní drobné poznámky pro používání průřezu.

Datová tlačítka (Slicer Items) v průřezu mají různou barvu. V tomto textu je ponecháno barevné provedení tak, jak ho implicitně při vytvoření generuje Excel (uživatel má možnost barvičky posléze změnit). V případě průřezů nejsou barvy tlačítek samoúčelné. Barva tlačítka je totiž přiřazena podle stavu filtru dle této hodnoty a mění se při změně tohoto stavu. Pro uživatele jsou asi nejpodstatnější dva stavy:

 

 

V pravém horním rohu průřezu jsou umístěna dvě tlačítka:

 

 



Nástroje v pravém horním rohu

 

Zcela vpravo je tlačítko pro odstranění filtru pro toto datové pole; po jeho stisknutí tedy budou opět zpracovány všechny hodnoty tohoto pole.

Vlevo od něj je aretační tlačítko, jehož nápověda (Tooltip) říká: Vícenásobný výběr (Alt-S). I jen trochu zběhlý uživatel grafického uživatelského prostředí (GUI) ví, že „vybrat“ více položek myší najednou lze pomocí Ctrl/l-Click - a to funguje i u průřezů, tak načpak tohle tlačítko pro vícenásobný výběr, že. Autor tohoto článku se domnívá, že je to určeno těm, kteří pro ovládání mají k disposici nanejvýš jednu ruku (protože ve druhé drží pohárek se vzpružujícím nápojem, např.s  kávou). Přesně tlačítko funguje takto:

 

 

Jak již bylo zmíněno v úvodu této kapitoly, vícenásobný výběr definuje logickou operaci „nebo“. Následující filtr

 



Filtr ve významu NEBO

 

tedy podle definice matematické operace vybírá jen ty řádky, kde hodnota v buňce sloupce MĚSÍC je rovna LEDEN nebo BŘEZEN nebo OBOJÍ - což však v jedné buňce Excelu současně být nemůže.

 

Použití průřezů pro kontingenční tabulky (Pivot Tables)

Pro vytvoření a připojení průřezu k jedné kontingenční tabulce je postup shodný jako pro připojení k Tabulce Excelu (viz shora): Aktivovat jednu nebo více buněk kontingenční tabulky, vykonat posloupnost příkazů

Vložení / Filtry / Průřez

nebo

Nástroje kont. tabulky / Analýza / Filtr / Vložit průřez

a upravit vlastností a vzhled průřezu.

Na rozdíl od Tabulek Excelu však lze jeden nebo více průřezů připojit k více kontingenčním tabulkám (tj. aktivací položek jednoho průřezu budou filtrována data současně ve více kontingenčních tabulkách).

Důležitá podmínka: Má-li jeden (nebo více) průřezů fungovat pro více kontingenčních tabulek, musí všechny tyto kontingenční tabulky čerpat data ze stejného datového zdroje!

Pro ukázku mějme jako datový zdroj stejnou Tabulku Excelu jako shora (tj. pro účely výuky tabulku pojmenovanou tbStaveb).

Poznámka: Na rozdíl od průřezů pro Tabulky Excelu může být zdrojem pro kontingenční tabulky jakákoliv oblast, která by měla splňovat podmínky pro tabulky relační databáze (to pro případ maximálního využití síly tohoto nástroje); pro Průřezy stačí fakt obdélníková oblast s nadpisy sloupců.

Vytvořme první kontingenční tabulku, nezdržujme se formátováním jednotlivých buněk. Pojmenujme ji nápaditě třebas ktStavebMZ (jako Kontingenční tabulka Stavebnin pro Měsíce versus Zboží):

 



Kontingenční tabulka MĚSÍC x ZBOŽÍ

 

Vytvořme druhou kontingenční tabulku ze stejného zdroje, tu pojmenujme třebas ktStavebOJ (jako Kontingenční tabulka Stavebnin pro Obchody versus Jména):

 



Kontingenční tabulka OBCHOD x JMÉNO

 

Vytvořme nyní pro první kontingenční tabulku průřezy pro datová pole MESIC a OBCHOD (tj. stejné jako pro Tabulku Excelu shora). Průřezy lze hned vyzkoušet a sledovat vliv na první kontingenční tabulku.

Přiřazení stejných průřezů (všech nebo jen některých) druhé kontingenční tabulce zajistí po aktivaci některé buňky druhé´kontingenční tabulky posloupnost příkazů

Nástroje kont. tabulky / Analýza / Filtr / Připojení filtru

mající za následek předložení formuláře

 



Formulář pro připojení existujících průřezů

 

ve kterém se zvolí, které logicky přípustné existující průřezy se mají k této kontingenční tabulce rovněž připojit.

Aktivace jednoho nebo více tlačítek v jednom nebo více z průřezů pak mají vliv na data zpracovávaná všemi kontingenčními tabulkami, které tyto průřezy mají připojeny.

Automatická úprava vzorců

V tabulce tbVydaje je připraven sloupec tabulky JEDCENA (= část sloupce H listu) pro vypočítávané hodnoty jednotkové ceny, zatím s prázdnými buňkami dat. Jednotková cena je rovna podílu zaplacené ceny (data ve sloupci B = KC) a množství (data ve sloupci D = MNOZSTVI). Jednotkovou cenu pro první řádek dat (= 2. řádek listu) tedy spočte vzorec zadaný do buňky H2:

= B2 / D2

Bezprostředně po umístění tohoto vzorce do buňky H2 Excel vzorec zkopíruje do ostatních buněk tohoto sloupce tabulky (nahoru i dolů) a automaticky upraví (automatically adjusts) adresy operandů pro každý řádek. Pokud v cílových buňkách už byl nějaký obsah, bude přepsán - jinak řečeno nebylo-li tam nic, budou tam vzorce vytvořeny.

Odkazy na data tabulky Excelu (Excel Table Data)

Poznámka: Praktické postupy jsou v této kapitole demonstrovány na datech listu lVydaje cvičného sešitu uvedeného v úvodu. Dále se v této kapitole rovněž předpokládá vytvořená tabulka tbVydaje podle předchozího odstavce.

Z nejrůznějších (většinou subjektivních) důvodů je mnoha uživateli požadováno od programu Excel něco, k čemu vůbec nebyl určen a k čemu se naprosto nehodí - totiž suplování databázových programů. Aby alespoň částečně takovým uživatelům autoři Excelu vyhověli, rozšířili kdysi nejprve množinu funkcí (!), a to ne vždy šťastným způsobem - viz např. funkce SVYHLEDAT a podobné. Poté, pro umožnění alespoň trochu inteligentního vkládání dat do pojmenované oblasti, zavedli mechanizmus tzv. seznamu, nyní nazývaného tabulka (viz předchozí kapitola).

V novější verzi programu Excel se jeho autoři také pokusili řešit jeden ze základních nástrojů relačních databází, a to logické vazby mezi tabulkami známými z databázové terminologie jako relace (z angl. relationship, vztah, zkráceně relation;  nezaměňovat ovšem s matematickým pojmem stejného znění!). Tomu je věnována jedna z následujících kapitol.

Data listu lVydaje cvičného sešitu simulují reálný provoz nějaké pečlivé domácnosti. Data jsou záznamem skutečnosti, kdy a za kolik se čeho nakoupilo. Dále pro pozdější generování informací tam je i údaj, ve kterém obchodě to bylo (pro informaci, jak ve kterém obchodě je to či ono drahé). A také údaj o druhu zboží (informace, jakého druhu kupujeme nejvíc: pokud zeleniny a ovoce, budem zdraví jak řípa).

Na rozdíl od předchozích odstavců už tedy začíná nabývat na důležitosti i význam dat. Analýza struktury tabulky však překračuje záměr tohoto článku; pro zájemce viz literatura [2]. Zde jediná připomínka pro konstrukci demonstrační tabulky v Excelu: Jistě nás bude zajímat jednotková cena. I když ta je dnes uvedena na každém paragonu z pokladny, určitě ji z paragonu nebudeme ručně opisovat do sešitu resp. listu resp. sloupce. Od toho má Excel možnost zadávat nejen vlastní data, ale i vzorce s daty pracujícími (zde vydělit cenu množstvím). Musí být ale kam ty vzorce zadávat. Připravíme proto v tabulce pro jednotkovou cenu zatím prázdný sloupec, ovšem s vyplněným nadpisem.

Explicitní odkazy

Motivační otázka: máme-li data v tabulce tbVydaje, kolik jsme utratili celkem? Jaké byly jednotkové ceny?

Pro celkové výdaje je zapotřebí sečíst čísla ve sloupci B počínaje řádkem 2 a konče řádkem 33 (na hořejším obrázku není vidět, že zrovna ten je poslední). To vyřeší i začátečníkům známý vzorec (používáme identifikátory „počeštěných“ funkcí) zapsaný kdekoliv do prázdné buňky mimo oblast dat:

= Suma ( B2 : B33 )

Pokud je třeba zjistit výdaje jen počínaje desátou položkou, jde o vzorec

= Suma ( B11 : B33 )

Pro jednotkové ceny stačí do buňky H2 vložit vzorec

= B2 / D2

a roz-kopírovat ho dolů podél všech řádků dat.

Odkazy na buňky s daty v předchozích vzorcích jsou označovány jako explicitní odkazy (výslovné, přímé, přímo vyjádřené) a to bez ohledu na to zda, zda obsahují nebo neobsahují písmena sloupců a čísla řádků relativně nebo absolutně („s dolarem $“).

Nyní praktické připomínky:

 

  1. Nákupy budou přibývat. To máme po každém přidání měnit rozsah dat pojmenovaných naším identifikátorem tbVydaje?
  2. Nákupy budou přibývat. To máme po každém přidání dat měnit vzorec?
  3. Občas by se mohla změnit struktura tabulky (např. za první sloupec s datumem vložit sloupec s údajem, kdo z rodiny to koupil). Opět: To máme při každé restrukturalizaci dat měnit vzorec?
  4. Data budou mít časem 10 000 řádků a 87 sloupců. Když budeme mít uvedený vzorec „daleko“ od dat, budeme si pamatovat, co je vlastně ve sloupci B resp. v oblasti B2:B10001?

 

V některých případech je Excel schopen uvedené připomínky vyřešit automaticky. Pokud např. vložíme nový sloupec před existující druhý, Excel automaticky změní vzorce pro jednotkové ceny (obsahují jen explicitní relativní odkazy). Problém by však mohl nastat v některých vzorcích, které obsahují odkazy relativní i absolutní současně.

Pravidla, kdy Excel něco zajistí automaticky a něco ne, jsou pro běžného uživatele mírně řečeno nepřehledná. Je-li třeba vzorec pro celkovou cenu nákupů = Suma ( B2 : B33 ) alespoň tři řádky pod posledním řádkem pojmenované oblasti nebo tabulky Excelu, pak při zadání nového řádku dat bezprostředně za současný poslední Excel automaticky rozšíří pojmenovanou oblast o jeden řádek směrem dolů a upraví vzorec pro celkovou cenu. Pokud by však šlo o vzorec sčítající jen některá data = Suma ( B11 : B33 ), Excel sice pojmenovanou oblast rozšíří, ale vzorec neupraví.

Strukturované odkazy

Připomínky zmíněné v předchozím odstavci částečně řeší Tabulky Excelu zavedením strukturovaných odkazů. Strukturovaný odkaz je v prvním přiblížení tvořen kombinací jmen tabulky a sloupce. Je-li ve cvičných datech tabulka námi pojmenovaná tbVydaje a v ní druhý sloupec KC, pak celkové výdaje spočte vzorec vložený mimo tabulku

 

vzorec s explicitními odkazy
umístěný na listu s daty
    vzorec se strukturovanými odkazy
umístěný kdekoliv v sešitu
= SUMA ( B2 : B33 )     = SUMA ( tbVydaje [KC] )

 

K připomínkám v předchozím odstavci v kontextu Tabulky Excelu:

Ad 1: Při vložení byť jediné hodnoty do prvního volného řádku pod posledním řádkem tabulky rozšíří Excel pojmenovanou oblast tabulky automaticky o jeden řádek směrem dolů. Pokud by se „narazilo“ na již existující data pod tabulkou, k rozšíření nedojde. Stejně tak rozšíří Excel pojmenovanou oblast o jeden sloupec vpravo, zapíše-li se byť jediná hodnota do prvního volného sloupce vpravo vedle tabulky. Nejde-li o zápis do prvního řádku (tedy nadpisu), vytvoří Excel jméno tohoto nového sloupce sám; uživateli se doporučuje co nejdřív toto jméno změnit podle svých představ.

Ad 2: Při každé změně struktury tabulky Excel rovněž aktualizuje jednak odkaz na oblast dat příslušející jednotlivým jménům, jednak hodnoty vzorců obsahující strukturované odkazy.

Ad 3: Na závěr zřejmě nejpodstatnější vlastnost tabulek Excelu - přesněji grafického rozhraní Excelu při práci s tabulkami Excelu. Každá uživatelem vytvořená tabulka Excelu má - autorem dat nebo Excelem - pojmenované sloupce. Doporučuje se ve vzorcích místo explicitních odkazů používat odkazů strukturovaných. Při zápisu vzorce způsobem „ukazováním na operandy“ (kterými jsou oblasti dat tabulky) totiž pak sám Excel do vzorce dosadí nikoliv explicitní odkazy jako při použití „běžných“ dat, ale strukturované odkazy na základě jmen sloupců a kontextu řádku (řádků).

Ad 4: To je vyřešeno už tím, že se ve vzorcích vyskytují namísto odkazů explicitních odkazy strukturované, používající označení vytvořená autorem (a proto by ta označení měla být rozumně vypovídající o významu, ne tvaru např. BFLM).

Syntaktická pravidla strukturovaného odkazu

Označme:

 

Označení Význam Příklad
JmTabulky Identifikátor ve smyslu názvu tabulky tbVydaje
ČástTabulky Specifikace části tabulky (viz výčet možností níže) [#Headers] nebo [#All]
ČástiTabulky Prázdná nebo ČástTabulky; nebo ČástTabulky ČástiTabulky Nic nebo [#Headers]; nebo [#Headers];[#Data];
NázSloupce Text ze záhlaví sloupce ve smyslu názvu sloupce.
Nemá-li tvar identifikátoru, musí být uzavřen mezi [ a ]
DATUM nebo [% sazba daně]
Oblast NázSloupce nebo NázSloupce:NázSloupce DATUM nebo DATUM:DRUH

 

Upozornění: V předchozí tabulce pozor na středníky a jejich umístění. Zvláště programátoři jsou zvyklí používat jako oddělovače v seznamech čárky.

Specifikací části tabulky se rozumí konstrukce podle následujícího výčtu (v prvním sloupci je za pomlčkou uvedený tvar v CZ mutaci, který je v CZ instalaci MS Office nutno používat):

 

Specifikace
části tabulky
Odkazuje na:
[#Headers] - [#Záhlaví] Pouze řádek záhlaví.
[#Data] - [#Data] Pouze řádky dat.
[#Totals] - [#Součty] Pouze řádek souhrnů. Pokud není, odkaz je Null.
[#All] - [#Vše] Celou tabulka: záhlaví + data + souhrny (pokud existují).
[#This Row] - [#Tento řádek]
@
@[NázSloupce]
Pouze buňky toho řádku, ve kterém je umístěn vzorec
(viz také poznámka níže).

 

Poznámka: V poslední uvedené specifikaci jsou všechny tři možnosti ekvivalentní. Tuto specifikaci však nelze (na rozdíl od ostatních) kombinovat s jinými. Dále: pokud „ručně“ zapíšeme do vzorce specifikaci [#This Row], Excel ji sám změní na kratší @ - ale jen tehdy, má-li tabulka alespoň dva řádky dat. Pokud v jednořádkové tabulce dat zůstane specifikace [#This Row], upozorňují sami autoři Excelu na možné „unexpected calculation results“ (neočekávané výsledky výpočtu). Proto doporučují vkládat vzorce se strukturovanými odkazy až tehdy, mají-li data více řádků.

Strukturovaný odkaz bez kvalifikátoru má tvar:

[ČástiTabulky  Oblast]

Strukturovaný odkaz s kvalifikátorem má tvar:

JmTabulky [ ČástiTabulky  Oblast ]

Použije-li se strukturovaný odkaz ve vzorci uvnitř tabulky, může být bez kvalifikátoru. Použije-li se strukturovaný odkaz ve vzorci vně tabulky, musí být s kvalifikátorem.

Příklad: vzorec pro výpočet jednotkové ceny rohlíku (3. řádek listu) zapsaný do buňky H3 může mít tvar

=[@KC]/[@MNOZSTVI]

nebo jen

=[KC]/[MNOZSTVI]

protože je zapsán uvnitř tabulky. Vzorec pro výpočet téhož zapsaný do buňky J3 musí mít tvar

=tbVydaje[@KC]/tbVydaje[@MNOZSTVI]

nebo jen

=tbVydaje[KC]/tbVydaje[MNOZSTVI]

protože je zapsán vně tabulky.

Operátory nad strukturovanými odkazy

Jak známo, Excel pracuje s uživatelem vybranými (selected) oblastmi trojího typu, vizuálně znázorněné např. takto:

 


Poznámka: Pro jednu spojitou obdélníkovou oblast jsme ponechali objektový název „Range“, chápaný jako „Oblast variace mezi horní mezí (= levým horním rohem) a dolní mezí (=pravým dolním rohem) na konkrétní matici“. Těžko se totiž hledá odpovídající český termín, který by nekolidoval se stejným termínem, ale v jiném významu.

Na obrázku jsou rovněž zapsány explicitní odkazy. Je z nich patrné, že oblasti jsou výsledkem binárních operací nad dvěma operandy, kterými jsou oblasti. Na tomto místě však jde o zápis pomocí strukturovaných odkazů. Názorně ukazují operace konkrétní příklady; všechny používají cvičnou tabulku tbVydaje:

 

Operátor Odkazuje na: Strukturovaným odkazem Explicitním odkazem:
: (dvojtečka) Všechny buňky dvou nebo více sousedních sloupců.
Může to být formálně i jeden sloupec [KC]:[KC], funguje
to, ale na kompletní strukturovaný zápis je to složité.
tbVydaje [ [DATUM] : [CO] ] A2 : C39
, (čárka) Sjednocení dvou oblastí tbVydaje[JEDNOTKA] , tbVydaje[OBCHOD] E2 : E33 , G2 : G33
(mezera) Průnik dvou oblastí tbVydaje[[DATUM]:[KC]] _ tbVydaje[[KC]:[MNOZSTVI]]
(znak _ zde znázorňuje MEZERU, nikoliv podtržítko!)
B2 : B33

 

Příklady strukturovaných odkazů

Níže je uvedeno několik příkladů zápisu strukturovaného odkazu. Všechny příklady používají cvičnou tabulku tbVydaje. Pokud se použijí v CZ mutaci MS Office, je nutno příslušně zaměnit specifikaci části tabulky (viz shora Syntaktická pravidla).

 

 

Strukturovaný odkaz: Odkazuje na: Explicitním odkazem:
tbVydaje [ [#All]; [MNOZSTVI] ] Všechny buňky ve sloupci MNOZSTVI včetně řádku záhlaví a řádku souhrnů (existuje-li) D1 : D33
tbVydaje [ @ [KC] ] Buňka v průsečíku řádku s tímto zápisem a sloupce KC. V tomto tvaru lze zapsat i mimo tabulku. Je-li odkaz na řádku 5 listu, pak B5
tbVydaje [ [#Headers]; [KC] ] Pouze buňka záhlaví sloupce KC B1
tbVydaje [ [#Totals]; [KC] ] Existuje-li řádek souhrnů (např. jako řádek 34 listu), pak odkaz vrací hodnotu souhrnu dat ve sloupci [KC]. Neexistuje-li, je zápis hodnocen jako neplatný odkaz. B34
tbVydaje [ [#Headers]; [KC] : [DRUH] ] Pouze buňky záhlaví sloupců KC až DRUH B1 : F1
tbVydaje [ [#Totals]; [#Data]; [KC] ] Pouze data a hodnota řádku souhrnu ve sloupci KC B3 : B33

Vazby mezi tabulkami

Praktické postupy jsou demonstrovány na datech stejného cvičného sešitu uvedeného v úvodu tohoto článku, a to na listech lVydaje, lDruhy a lObchody.

Vazby mezi tabulkami

Data zmíněných listů cvičného sešitu simulují reálný provoz nějaké domácnosti. Především to jsou data poznamenávající, kdy a za kolik se toho nakoupilo (tedy jednotlivé VÝDAJE). V nich je pro pozdější generování informací i údaj, ve kterém obchodě to bylo, a jakého druhu to zboží bylo:

 


 

Zatímco údaj o nakoupeném zboží (chléb žitný, chléb vesnický, mléko plnotučné, mléko kefírové z Olešnice, mléko kefírové z ValMezu) je zde evidentně uveden jako otevřený text, údaj o druhu zboží se může skládat z hodně dlouhého označení a tedy druh jako otevřený text jistě nebude mnohokrát stejně zapisován přímo do výdajů (problém tzv. redundantních údajů), ale na jediném místě v jiné tabulce. Dalším podstatnou skutečností je vztah druhu zboží a sazby DPH, kterou je konkrétní druh zdaňován (článek je psán v březnu 2021; čtenář se v roce 2023 nebude stačit divit):

 


 

Teď ovšem nastane problém: když těch kódů druhů bude hodně, kdo si má při prohlížení výdajů pamatovat, že OB je Obuv a ne Oblečení? Ručně to je jednoduché: podívám se v daném řádku výdajů do sloupce Druh a v tabulce druhů si to ve sloupci Kód najdu. Tuto činnost však lze od databázového software očekávat už automatizovanou. V Excelu to nejprve řešili (a dodnes to lze použít) vyhledávacími funkcemi. Zde ukázka použití funkce SVyhledat:

 


 

V databázové problematice jde o klasické čerpání dat nikoliv z jediného datového zdroje, ale ze dvou samostatných zdrojů, které jsou logicky propojeny nějakou přesně definovatelnou hodnotou společnou oběma zdrojům - zde hodnotou ve sloupci DRUH na straně výdajů a hodnotou ve sloupci KÓD na straně tabulky druhů zboží. Ke každému řádku tabulky VÝDAJE se logicky připojí ten jeden odpovídající řádek v tabulce DRUHY:

 


 

Při zpracování konkrétního řádku tabulky VÝDAJE tak lze čerpat data i z logicky připojeného řádku tabulky DRUHY.

Relace v Excelu 2016

Pro realizaci vazby z předchozího obrázku a  hlavně jejího využití má Excel 2016 několik postupů. Zde popíšeme, jak vazbu využít v kontingenčních tabulkách. Nechť jest zpracovat částky uvedené ve výdajích jako součty v kontingenční tabulce s řádkovým kritériem z tabulky Druhy a sloupcovým kritériem z tabulky Obchody.

Především v tomto případě je celý mechanismus založen na jednotlivých pojmenovaných oblastech obsahujících data ve formátu tabulky relační databáze (viz shora). Ve fázi definování relace uživatelem však popisovaný nástroj předpokládá data nejen pouze v pojmenovaných oblastech, ale přímo jako tabulky Excelu (viz výše). Pokud tomu tak nebude, nástroj Excelu si je sám na tabulky převede, ale za cenu několika relativně nepřehledných kroků navíc. Proto je vhodné, aby si je uživatel připravil (a hlavně pojmenoval) sám jako další přípravný krok. Níže je tedy ukázka tohoto výchozího stavu. Aby bylo jasné, že jde o tabulky Excelu, bylo ponecháno strakaté formátování; pojmenování tabulek uživatelem je zvýrazněno:

 

 


 

 


 

Následuje vlastní definování logického vztahu (relationship, relace) mezi dvěma datovými zdroji, zde tabulkami Excelu. Zajistí to aktivace příkazu Relace posloupností

Data / Datové nástroje / Relace

 


 

následkem čehož se otevře formulář Spravovat relace pro vytvoření, změn a odstranění konkrétní relace (může jich být samozřejmě více podle povahy úlohy). Zde evidentně požadujeme vytvořit relaci novou:

Spravovat relace / Nová

 


 

a nyní již (zatím prázdný) formulář pro jednu konkrétní relaci:

 


 

Demonstrovaný příklad požaduje vytvořit vazbu z tabulky námi nazvané tVýdaje vycházející ze sloupce DRUH (viz schéma nahoře) do související tabulky tDruhy směřující na sloupec KÓD:

 


 

Po odeslání formuláře jest do seznamu správce relací vložen nový řádek vytvořenou relaci popisující:

 


 

Ve cvičném sešitu jsou data připravena pro ukázku více vazeb. Jednak v datech tabulky VÝDAJE je to sloupec s OBCHOD kódem obchodu, kde se daný nákup uskutečnil. Dále jsou na samostatném listu lObchody data ve smyslu číselníku obchodů (v posledním sloupci OBCHODU je fiktivní počet provozoven v republice). Stejně jako v minulém kroku byla vytvořena vazba z Výdajů do Druhů, vytvořme analogicky vazbu z Výdajů do Obchodů - tentokrát přes sloupec OBCHOD ve VÝDAJÍCH do sloupce KÓD v OBCHODECH.

Nejprve tedy je třeba definovat oblast dat obchodů jako tabulku Excelu (nazvěme ji třeba tObchody):

 

 

Nyní stejným postupem jako shora lze definovat další relaci, která se zobrazí ve správci relací:

 

 

Stručně o doplňku PowerPivot

V popisovaném rozšíření Excelu je možno používat několik nových doplňků (Add-in); jedním z nich je doplněk nazvaný PowerPivot. Jeho aktivaci zajistí posloupnost

Data / Datová nástroje / Spravovat datový model

 

 

následkem které se aktivuje hlavní karta PowerPivot a jí příslušný pás karet:

 

 

Poznámka: Text "Spravovat datový model" u shora zakroužkovaného příkazu se zobrazí jen při dostatečné šířce obrazovky (přesněji aktivního okna). Nápověda (tool-tip) k tomuto tlačítku bývá "Přejít do okna Power Pivot".

Hned první příkaz zleva

Power Pivot / Datový model / Spravovat

spustí paralelně k Excelu doplněk prezentovaný následujícím prostředím:

 

 

Tato kapitola se nezabývá podrobně doplňkem PowerPivot, ukazuje jen možnost grafické prezentace relací.

Na hlavní kartě Domů doplňku PowerPivot je vpravo skupina příkazů Zobrazit (viz předchozí obrázek). Prostředí PowerPivot zobrazuje při aktivní kartě Domů buď data nebo diagram. Mezi těmito dvěma zobrazeními se přepíná příkazy Zobrazení dat (modře zakroužkované) a Zobrazení diagramu (červeně zakroužkované). Při aktivním zobrazení dat se zobrazí vždy data jednoho datového zdroje; záložkami vlevo dole se určuje, kterého (azurové zakroužkování).

Při zobrazení diagramu jsou vazby mezi datovými zdroji znázorněné graficky tak, jak je zvykem u databázových programů:

 

 

Jde o jednodušší způsob zobrazení vazby. Např. program Microsoft Access informuje navíc trvale polohou konců spojovacích čar o datových polích, která se vazby účastní. V PowerPivotu si s tím až tak moc nehráli, udělali to takto: pokud uživatel najede myší na některou spojovací čáru (červeně se zakroužkuje), v obou datových zdrojích se zeleně dočasně označí datová pole, na kterých je relace založena: Pokud uživatel tuto čáru označí (click), bude označení trvalé až do doby odznačení nebo označení čáry jiné.

 

 

Double-click na spojovací čáru otevře formulář, ve kterém je možno konkrétní relace upravit. Jak již však bylo řečeno, podrobnější popis doplňku PowerPivot je nad rámec této publikace.

 

Použití relací v kontingenčních tabulkách

Shora je uvedena cvičná tabulka VÝDAJE, jejíž rozložení a data odpovídají definici relační databáze. Budeme směřovat ke kontingenční tabulce obsahující zpracovaná data (agregační funkce SUM) v maticovém tvaru:

 

 

Řádkovým kritériem je tedy DRUH, sloupcovým je OBCHOD (obojí jsou kódy z tabulky VÝDAJE), součtovanou položkou jsou KČ (rovněž z tabulky VÝDAJE). Vytvoření takové tabulky zajistí běžně používaný známý postup.

Pokud jsme autory dat my sami, je nám význam kódu druhu ZE nebo kódu obchodu K důvěrně známý. Pokud však takovou tabulku předložíme někomu jinému, bude mírně řečeno zmaten. Jeho zmatek evidentně vyřeší náhrada kódů  v záhlavích řádků a sloupců plnými texty. Tyto plné texty jsou však v jiných oblastech sešitu, přičemž dřívější verze Excelu umožňovaly jako zdroj dat buď jedinou oblast, nebo externí zdroj dat. Ona jediná oblast v sešitu mohla (a pořád může) být i výsledkem databázového dotazu, pro onen externí zdroj dat musí být předem definováno připojení. Obojí je podle mínění autora tohoto textu nad schopnosti běžného uživatele. V popisované verzi Excelu je však nabízena třetí možnost, tou je definovaný datový model. Příklad jeho definice včetně vytvoření vazeb mezi třemi datovými zdroji je obsahem předchozích odstavců, a tento datový model bude použit.

Vytvoření kontingenční tabulky (v ukázce v existujícím prázdném listu cvičného sešitu) začíná běžnou posloupností příkazů

Vložení / Kontingenční tabulka

následkem čehož je uživateli předložen formulář - na následujícím obrázku už připraven k odeslání, požadavek na použití datového modelu je vyznačen:

 

 

V panelu pro výběr polí kontingenční tabulky jsou v jeho levé části všechny dostupné datové zdroje datového modelu, u každého všechna jeho datová pole. Ta, která budou použita v ukázce, jsou zvýrazněna:

 

 

Nyní stačí běžným způsobem přetáhnout požadovaná pole do požadovaných oblastí:

 

 

s očekávaným výsledkem:

 

 

Bezprostředně poté je v cílové oblasti generována požadovaná kontingenční tabulka:

 

 

Tu je pak možno formátovat a jinak upravovat běžnými postupy definovanými pro kontingenční tabulky.

 

 

 

 

Literatura a další výukové zdroje

[1] Základy použití tabulkových procesorů in: DROZDOVÁ, J., HOMOLA, V.: Informatika pro Geovědní a montánní turismus, část Informatika. VŠB - TU Ostrava, 2018. ISBN 978-80-248-4144-1.

[2] Databáze prakticky in: DROZDOVÁ, J., HOMOLA, V.: Databáze pro Geovědní obory. VŠB - TU Ostrava, 2019. ISBN 978-80-248-4266-0.

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

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