LibreOfficeLogo

Calc Guide 7.4

Kapitola 13
Aplikace Calc jako databáze

 

Autorská práva

Tento dokument je chráněn autorskými právy © 2023 týmem pro dokumentaci LibreOffice. Přispěvatelé jsou uvedeni níže. Dokument lze šířit nebo upravovat za podmínek licence GNU General Public License (https://www.gnu.org/licenses/gpl.html), verze 3 nebo novější, nebo the Creative Commons Attribution License (https://creativecommons.org/licenses/by/4.0/), verze 4.0 nebo novější.

Všechny ochranné známky uvedené v této příručce patří jejich vlastníkům.

Přispěvatelé

Pro toto vydání

Skip Masonsmith

Kees Kriek

 

Pro předchozí vydání

Andrew Pitonyak

Barbara Duprey

Jean Hollis Weber

Simon Brydon

Kees Kriek

Zachary Parliman

Steve Fanning

Leo Moons

Felipe Viggiano

Rafael Lima‍

 

 

Zpětná vazba

Jakékoli připomínky nebo návrhy k tomuto dokumentu prosím směřujte do fóra dokumentačního týmu na adrese https://community.documentfoundation.org/c/documentation/loguides/ (registrace je nutná) nebo pošlete e-mail na adresu: loguides@community.documentfoundation.org.

Poznámka

Vše, co napíšete do fóra, včetně vaší e-mailové adresy a dalších osobních údajů, které jsou ve zprávě napsány, je veřejně archivováno a nemůže být smazáno. E-maily zaslané do fóra jsou moderovány.

Datum vydání a verze programu

Vydáno březen 2023. Založeno na LibreOffice 7.4 Community.
Jiné verze LibreOffice se mohou lišit vzhledem a funkčností.

Používání LibreOffice na systému macOS

Některé klávesové zkratky a položky nabídek jsou v systému macOS jiné než v systémech Windows a Linux. V následující tabulce jsou uvedeny nejdůležitější rozdíly, které se týkají informací uvedených v tomto dokumentu. Podrobnější seznam nalezneme v nápovědě k programu a v příloze A (Klávesové zkratky) této příručky.

Windows nebo Linux

Ekvivalent pro macOS

Akce

Výběr v nabídce Nástroje > Možnosti

LibreOffice > Předvolby

Otevřou se možnosti nastavení.

Klepnutí pravým tlačítkem

Control + klepnutí a/nebo klepnutí pravým tlačítkem myši v závislosti na nastavení počítače

Otevře se místní nabídka.

Ctrl (Control)

(Command)

Používá se také s dalšími klávesami.

F11

⌘ + T

Otevře se postranní lišta Styly.

 

Úvod

V mnoha každodenních scénářích lze tabulky Calc použít k agregaci sad dat a provádění jejich analýz. Jelikož jsou data v tabulce rozložena v tabulkovém zobrazení, jasně viditelná a snadno upravitelná nebo rozšiřitelná, někteří uživatelé možná nebudou potřebovat komplexní prostředky relační databáze poskytované komponentou Base LibreOffice. Pro takové uživatele má Calc dostatečné funkce, aby fungoval jako jednoduchá, ale zároveň schopná platforma podobná databázi. V této kapitole je uveden přehled těchto možností.

Pro ty uživatele, kteří se zpočátku rozhodli spravovat svá data v tabulce Calc a následně se rozhodli, že potřebují použít komplexnější databázový systém, je migrace dat z aplikace Calc do aplikace Base přímočará. V opačném směru, pro uživatele aplikace Base, kteří chtějí využít některé funkce aplikace Calc k analýze nebo vizualizaci svých dat, lze Base použít k vytváření propojených datových rozsahů v souborech Calcu, k analýze kontingenčních tabulek nebo jako základ pro grafy. Více informací najdeme v příručce Průvodce aplikací Base.

Dřívější verze této kapitoly obsahovaly několik příkladů s makry LibreOffice Basic. Ty jsou nyní k dispozici na wiki nadace The Document Foundation na adrese https://wiki.documentfoundation.org/Macros/Calc. Většina informací o makrech na těchto stránkách je převzata nebo upravena z knihy Andrewa Pitonyaka OpenOffice.org Macros Explained (OOME) a z odkazu na rozhraní API LibreOffice na adrese https://api.libreoffice.org/docs/idl/ref/index.html.

Databázové základy

V typické databázi jsou související data organizována do tabulek, které mají řádky a sloupce podobné tabulkám. Každý řádek tabulky představuje datový záznam, zatímco každý sloupec představuje pole v jednotlivých záznamech. Každá buňka v poli obsahuje samostatnou datovou položku nebo atribut, jako je např. jméno, zatímco každý záznam obsahuje související atributy, které odpovídají jediné entitě jako je např. osoba. Databázová tabulka má sklon mít pevný počet polí, ale může mít neurčitý počet záznamů.

Zatímco tabulka může mít stovky nebo tisíce řádků, jednotlivé záznamy lze snadno najít, získat a aktualizovat pomocí požadavků na informace, kterým se říká dotazy a které vyhledávají záznamy splňující zadanou sadu kritérií. Právě tento snadný přístup činí databázovou tabulku užitečnější než prosté vyplňování informací v neuspořádané tabulce.

Pro ilustraci tohoto konceptu databázové tabulky uvažujme příklad klasifikačního listu třídy (obrázek 1). V tomto listu představuje každý řádek jednoho studenta, který se účastní výuky, přičemž každý sloupec obsahuje jména a známky studenta. V této tabulce můžeme rychle vyhledávat známky jednotlivých studentů jednoduše pomocí hledání jména studenta a můžeme ručit, kteří studenti prospívají pomocí filtrování záznamů s horším než průměrným skóre.

Image2

Obrázek 1: Příklad klasifikačního listu

Mnoho moderních systémů pro správu databází je založeno na relačním modelu databáze, v němž jsou data a vztahy reprezentovány řadou vzájemně propojených tabulek. Komponenta LibreOffice Base je plně vybavený systém správy relačních databází. Aplikace Calc nepodporuje relační databázový model.

Aplikace Calc jako databázový program

List aplikace Calc je podobný ploché nerelační databázové tabulce a je možné, aby databázová tabulka byla obsažena v listu Calc. Data lze hloubkově analyzovat pomocí široké škály nástrojů a funkcí. Lze je třídit, filtrovat, otáčet a vizuálně prezentovat v 2-D/3-D grafech a grafikách. Calc nenahrazuje plnohodnotnou databázovou aplikaci, ale může být užitečný pro správu dat v mnoha malých osobních nebo profesních kontextech.

Přiřazení jména k oblasti

Pokud chceme nastavit databázovou tabulku v listu aplikace Calc, musíme nejprve nastavit oblast, ve které bude tabulka umístěna. Tento krok je nezbytný, protože některé z databázových funkcí aplikace Calc potřebují pro přístup nebo úpravu znát umístění tabulky. Takovou oblast představuje v aplikaci Calc oblast, což je souvislá skupina jedné nebo více buněk. Aby byla oblast tabulky snadno přístupná, můžeme ji přiřadit smysluplný název. To má čtyři konkrétní výhody:

Aplikace Calc nabízí dva typy pojmenovaných oblastí: databázové oblasti, které ukládají nastavení pro databázové operace a standardní pojmenované oblasti, které ho neukládají.

Pojmenované oblasti

Technicky vzato je pojmenovaná oblast pojmenovaným výrazem vzorce a její obsah je vždy nastaven jako řetězec. Běžně používaným typem výrazu je absolutní oblast buněk, například „$List1.$A$1:$E$15“. Jsou však možné i jiné typy výrazů. Například výraz „$List1.$A$1:$A$4~$List1.$B$1:$B$4“ zahrnuje dvě samostatné oblasti buněk (znak tilda je operátor spojování odkazů). Alternativně lze definovat výraz vzorce, například „PI()*B1*B1“, který vypočítá plochu kruhu s daným poloměrem. Ve zbývající části této kapitoly se budeme zabývat pouze pojmenovanými oblastmi definovanými jako jedna maticová oblast buněk.

Novou pojmenovanou oblast vytvoříme rychle tak, že vybereme příslušné buňky v listu a pak jednoduše začneme psát název do pole Název, které se nachází vlevo od Lišty vzorců. Všimneme si nápovědy „Zadejte název pro oblasti“, která se zobrazí při psaní, a po dokončení psaní stiskneme klávesu Enter.

Pojmenované oblasti jsou také vytvářeny pomocí dialogového okna Definovat oblast (obrázek 2), které otevřeme výběrem List > Pojmenované oblasti a výrazy > Definovat v hlavní nabídce nebo kliknutím na tlačítko Přidat v dialogovém okně Spravovat názvy (obrázek 3).

Image4

Obrázek 2: Dialogové okno Definovat název

Chceme-li vytvořit pojmenovanou oblast, vybereme oblast buněk v listu a otevřeme dialogové okno Definovat název. Poté oblast smysluplně pojmenujeme a klepnutím na Přidat jej přidáme do seznamu pojmenovaných rozsahů aktuálního dokumentu. K těmto oblastem pak můžeme přistupovat a spravovat je pomocí dialogového okna Spravovat názvy (obrázek 3), které otevřeme pomocí volby List > Pojmenované oblasti a výrazy > Spravovat v hlavní nabídce nebo stisknutím kombinace kláves Ctrl + F3 nebo výběrem Spravovat názvy v Poli názvu vlevo na liště Vzorec.

Image16

Obrázek 3: Dialogové okno Spravovat názvy

Pokud chceme omezit psaní při odkazování na pojmenovanou oblast, můžeme dialogové okno Vložit názvy (obrázek 4) otevřít výběrem možnosti Vložit > Pojmenované oblasti a výrazy nebo List > Pojmenované oblasti a výrazy > Vložit v hlavní nabídce. Vybereme položku příslušné pojmenované oblasti a klepnutím na tlačítko Vložit vložíme vybranou pojmenovanou oblast na aktuální pozici kurzoru.

Image5

Obrázek 4: Dialogové okno Vložit názvy

 

Podrobnější informace o vytváření a správě oblastí najdeme v kapitole 6, Tisk, export, odesílání e-mailů a podepisování, a v kapitole 7, Použití vzorců a funkcí.

Vytváření pojmenovaných oblastí pomocí záhlaví řádků a sloupců

Pomocí nástroje Vytvořit názvy, ke kterému se dostaneme pomocí volby List > Pojmenované oblasti a výrazy > Vytvořit v hlavní nabídce (obrázek 5) můžeme ze záhlaví tabulky vytvořit více pojmenovaných oblastí současně. Tato záhlaví lze přebírat z okrajů tabulky – horní a dolní řádky a levé a pravé sloupce – a každý řádek nebo sloupec, kterému odpovídá záhlaví, se použije k vytvoření pojmenovaných oblastí. Pokud se například rozhodneme vytvořit oblasti ze záhlaví obsažených v horním řádku tabulky, bude každá oblast vygenerována z jednoho sloupce odpovídající označení záhlaví.

Poznámka

Buňky záhlaví nejsou zahrnuty do pojmenovaných oblastí generovaných pomocí nástroje Vytvořit názvy. Důvodem je to, že popisky v každé z těchto buněk se používají k pojmenování oblastí.

Image3

Obrázek 5: Dialogové okno Vytvořit názvy

Chceme-li použít nástroj Vytvořit názvy:

  1. V listu vybereme tabulku, ze které chceme vytvořit pojmenované oblasti. Nezapomeňme do výběru zahrnout záhlaví řádků nebo sloupců.

  2. Otevřeme dialogové okno Vytvořit názvy pomocí volby Pojmenované oblasti a výrazy > Vytvořit z hlavní nabídky.

  3. Calc automaticky identifikuje, které řádky nebo sloupce obsahují záhlaví a označí odpovídající zaškrtávací pole – Záhlaví, Levý sloupec, Zápatí, Pravý sloupec. Pokud však chceme tento výběr změnit, můžeme v tomto kroku ručně označit nebo odznačit kteroukoliv volbu.

  4. Klepnutím na tlačítko OK zavřeme dialogové okno a vytvoříme nové pojmenované oblasti.

Tip

Musíme se vyhnout tomu, aby více řádků nebo sloupců mělo stejný popisek, protože oblasti z nich generované by pak sdílely stejný název a mohly by při další práci působit problémy.

Databázová oblast

Ačkoli ji lze používat jako běžnou pojmenovanou oblast, databázová oblast je určena k použití jako databázová tabulka, přičemž každý řádek představuje záznam a každá buňka pole v rámci záznamu. Konkrétně se databázová oblast liší od pojmenované oblasti následovně:

Databázové oblasti lze vytvářet, upravovat a odstraňovat pomocí dialogového okna Definovat databázovou oblast (obrázek 6).

Vytvoření databázové oblasti:

  1. Pokud chceme, aby Calc automaticky určil celou oblast naší databázové tabulky, vybereme jednu buňku v její oblasti. Pokud chceme explicitně definovat oblast databázové tabulky, vybereme všechny příslušné buňky.

  2. Otevřeme dialogové okno Definovat databázovou oblast pomocí Data > Definovat oblast.

  3. Do pole Název zadáme název oblasti. Pro název oblasti můžeme použít pouze písmena, čísla a podtržítka; mezery, spojovníky a další znaky nejsou povoleny.

  4. Klepnutím na symbol rozbalení (obvykle plus nebo trojúhelník) vedle popisku Možnosti rozbalíme tuto část a zobrazíme a vybereme následující možnosti:

  1. Klepnutím na tlačítko Přidat přidáme novou oblast do seznamu oblastí pod polem Název.

  2. Pokud chceme zavřít dialogové okno a uložit databázovou oblast, klepneme na tlačítko OK.

Image6

Obrázek 6: Dialogové okno Definovat databázovou oblast

Úprava existující databázové oblasti:

  1. Otevřeme dialogové okno Definovat databázovou oblast pomocí Data > Definovat oblast.

  2. Vybereme oblast ze seznamu oblastí pod polem Název nebo napíšeme jméno oblasti do pole Název. Tlačítko Přidat se změní na tlačítko Změnit.

  3. Provedeme potřebné úpravy v poli Oblast a v části Možnosti.

  4. Klepnutím na tlačítko Upravit aktualizujeme databázovou oblast.

  5. Pokud chceme zavřít dialogové okno a uložit upravenou databázovou oblast, klepneme na OK.

Odstranění existující databázové oblasti:

  1. Otevřeme dialogové okno Definovat databázovou oblast pomocí Data > Definovat oblast.

  2. Vybereme oblast, kterou chceme smazat ze seznamu v horní části dialogového okna.

  3. Klepneme na tlačítko Odstranit a poté klepneme na tlačítko Ano v zobrazeném potvrzovacím dialogu.

  4. Klepnutím na OK zavřeme dialogové okno Definovat databázovou oblast.

Chceme-li vybrat existující oblast databáze z aktuálního dokumentu, otevřeme dialogové okno Vybrat oblast databáze výběrem možnosti Data > Vybrat oblast v hlavní nabídce (obrázek 7). Dále vybereme oblast v seznamu Oblasti a klepneme na OK. Dalším způsobem, jak vybrat existující oblast databáze, je použití nabídky Navigátor na postranní liště. Aplikace Calc automaticky zvýrazní polohu oblasti v listu, ve kterém je umístěna.

Image7

Obrázek 7: Dialogové okno Vybrat oblast databáze

Chceme-li načíst data ze zdroje dat a vytvořit novou oblast databáze, provedeme následující kroky:

  1. Průzkumníka Zdroje dat otevřeme výběrem možnosti Zobrazit > Zdroje dat v hlavní nabídce nebo stisknutím kláves Ctrl + Shift + F4.

  2. V levém podokně okna Průzkumník zdrojů dat klepneme na symbol rozbalení vlevo od názvu zdroje dat, který vás zajímá. Tato akce otevře strom a zobrazí tabulky nebo dotazy spojené se zdrojem dat.

  3. Klepnutím na požadovanou tabulku nebo dotaz se v pravém podokně okna Průzkumník zdrojů dat zobrazí její složky.

  4. Klepnutím na prázdnou obdélníkovou oblast v levém horním rohu pravého podokna okna Průzkumník zdrojů dat vybereme všechna data v zobrazené tabulce nebo dotazu.

  5. Přetáhneme data do buňky, která má být v levém horním rohu dat v tabulce. Další informace o přetahování dat z Průzkumníka zdrojů dat najdeme v nápovědě pod heslem „drag and drop – data source view“.

  6. Calc automaticky vytvoří nový databázovou oblast s výchozími parametry, zahrnující oblast buněk importovaných dat a s výchozím názvem ve tvaru Import1, Import2 atd.

  7. V případě potřeby přejdeme do dialogového okna Definovat databázovou oblast (obrázek 6) a aktualizujeme nastavení nového rozsahu databáze.

Výběrem možnosti Data > Obnovit oblast v hlavní nabídce obnovíme oblast databáze, jakmile jsou aktualizována data v přidruženém zdroji dat. Údaje v listu se aktualizují tak, aby odpovídaly údajům v externí databázi. Registrace a propojení s externími databázovými zdroji je podrobněji vysvětleno v kapitole 10, Propojení dat.

Třídění

Třídění je proces přeuspořádání dat v oblasti nebo listu podle zadaného pořadí řazení.

Nejjednodušší způsob, jak seřadit databázovou tabulku na základě obsahu jednoho sloupce, je použít nástroje Seřadit vzestupně a Seřadit sestupně, a to následujícím způsobem:

  1. Vybereme libovolnou buňku ve sloupci.

  2. Chceme-li řadit vzestupně, vybereme v hlavní nabídce možnost Data > Řadit vzestupně nebo klepneme na ikonu Řadit vzestupně na Standardní nástrojové liště. Pokud jsme povolili funkci Automatické filtry (viz následující část), můžeme také vybrat Řadit vzestupně v kombinovaném poli Automatické filtry příslušného sloupce.

  3. Chceme-li řadit sestupně, vybereme v hlavní nabídce možnost Data > Řadit sestupně nebo klepneme na ikonu Řadit sestupně na Standardní nástrojové liště. Pokud jsme povolili funkci Automatické filtry, můžeme také vybrat Řadit sestupně v kombinovaném poli Automatické filtry příslušného sloupce.

Při použití nástrojů Seřadit vzestupně a Seřadit sestupně aplikace Calc automaticky identifikuje celý rozsah buněk, který tabulka zabírá, a seřadí celou oblast pouze na základě hodnot v uvedeném sloupci. Zároveň však rozpozná první řádek jako řádek záhlaví a vyloučí jej z třídění.

Pro komplexní třídění použijeme dialogové okno Řadit (obrázek 8), které je dostupné pomocí volby Data > Řadit v hlavní nabídce nebo klepnutím na ikonu Řadit na Standardní nástrojové liště. Pokud před přístupem k dialogovému oknu Řadit vybereme pouze jednu buňku v databázové tabulce, zobrazí se při zobrazení dialogového okna celá tabulka jako vybraná.

Image8

Obrázek 8: Dialogové okno Řadit

Na kartě Seřadit podle dialogového okna můžeme zadat tři úrovně třídění. Data jsou nejprve řazena pomocí hodnot ve sloupce vybraném v rozevíracím seznamu Klíč řazení 1, potom podle hodnot ve sloupci vybraném v rozevíracím seznamu Klíč řazení 2 a nakonec podle hodnot ve sloupci vybraném v rozevíracím seznam Klíč řazení 3. Zaškrtnutím možnosti Záhlaví vyloučíme první řádek (pro třídění podle sloupců) nebo sloupec (pro třídění podle řádků) z třídění se zbytkem dat.

Karta Možnosti dialogového okna Řadit poskytuje další možnosti třídění. Další informace o používání tohoto dialogového okna a jeho možností nalezneme v kapitole 2, Zadávání, úpravy a formátování dat, a v nápovědě.

Filtrování

Filtr je nástroj, který skrývá nebo zobrazuje záznamy v listu na základě sady kritérií filtrování. Podobně jako třídění jsou filtry užitečné pro zkrácení dlouhých seznamů dat, aby bylo možné najít konkrétní datové položky. V aplikaci Calc existují tři typy filtrů:

Pokud chceme odstranit filtrování použité na tabulku databáze, jednoduše vybereme Data > Další filtry > Odstranit filtr v hlavní nabídce.

Filtry jsou také popsány v kapitole 2, Zadávání, úpravy a formátování dat

Automatický filtr

Automatické filtry jsou ze všech tří typů filtrů nejjednodušší. Fungují tak, že umožňují přístup k poli se seznamem prostřednictvím tlačítka se šipkou dolů umístěného v horní části jednoho nebo více datových sloupců (obrázek Chyba: zdroj odkazu nenalezen). Chceme-li přidat Automatický filtr do všech sloupců, klepneme na libovolnou buňku v tabulce a v hlavní nabídce vybereme Data > Automatický filtr, klepneme na ikonu Automatický filtr na Standardní nástrojové liště nebo stiskneme Ctrl + Shift + L. Je také možné přidat Automatický filtr do jednotlivých sloupců pomocí výběru těchto sloupců a následným výběrem Data > Automatický filtr, klepnutím na ikonu Automatický filtr nebo stisknutím Ctrl + Shift + L, ale toto není v databázové tabulce běžně potřeba. Chceme-li otevřít pole se seznamem Automatický filtr pro sloupec, klepneme na tlačítko se šipkou dolů v buňce záhlaví daného sloupce.

Chceme-li odstranit Automatický filtr ze všech sloupců databázové tabulky, klepneme na libovolnou buňku v oblasti tabulky a zvolíme Data > Automatický filtr v hlavní nabídce, vybereme Data > Další filtry > Skrýt automatický filtr v hlavní nabídce, klepneme na ikonu Automatický filtr na Standardní nástrojové liště nebo stisknutím Ctrl + Shift + L. Tlačítka se šipkou dolů v horní části sloupců zmizí.

Tip

Výběrem Data > Automatický filtr, klepnutím na ikonu Automatický filtr a stisknutím Ctrl + Shift + L Automatický filtr zapínáme a vypínáme.

Každé pole se seznamem Automatického filtru nabízí následující možnosti:

Image21

Obrázek 9: Pole se seznamem Automatický filtr

Standardní filtr

Standardní filtry jsou komplexnější než automatické filtry a umožňují definovat až osm filtračních podmínek. Výkonné filtry lze nastavit pomocí regulárních výrazů. Na rozdíl od automatických filtrů používají standardní filtry dialogové okno (obrázek 10), které je přístupné výběrem možnosti Data > Další filtry > Standardní filtr v hlavní nabídce nebo Standardní filtr v poli se seznamem Automatický filtr.

Další informace o použití tohoto dialogového okna a jeho možnostech najdeme v kapitole 2, Zadávání, úprava a formátování dat.

Image20

Obrázek 10: Dialogové okno Standardní filtr

Rozšířené filtry

Kritéria pro rozšířený filtr jsou uložena v listu, nikoliv zadávána do dialogového okna. Před použitím dialogového okna Rozšířený filtr je proto nutné nejprve nastavit rozsah buněk, který obsahuje kritéria (obrázek 11).

Image12

Obrázek 11: Dialogové okno Rozšířený filtr

Pro nastavení oblasti kritérií postupujeme následovně:

  1. Zkopírujeme záhlaví sloupců oblasti, kterou chceme filtrovat, na prázdné místo v listu. Nemusí to být stejný list, kde se nachází zdrojová oblast.

  2. V oblasti kritérií zadáme kritéria filtru pod záhlaví sloupců. Každé jednotlivé kritérium ve stejném řádku je spojeno pomocí AND, zatímco skupiny kritérií jednotlivých řádků jsou spojeny pomocí OR. Prázdné buňky jsou ignorovány. Pro filtr lze definovat až osm řádků kritérií.

Tip

Ačkoli je možné, aby oblast kritérií obsahovala pouze záhlaví sloupců s definovanými kritérii filtru, můžeme se pro zjednodušení rozhodnout zkopírovat do oblasti kritérií všechna záhlaví vaší databázové tabulky.

Po vytvoření oblasti s kritérii nastavíme rozšířený filtr takto:

  1. Vybereme oblast buněk, kterou chceme filtrovat. V případě databázové tabulky můžeme jednoduše klepnout na buňku v oblasti tabulky a Calc automaticky vybere celou tabulku, když otevře dialogové okno v kroku 2) .

  2. Přejdeme na Data > Další filtry > Rozšířený filtr v hlavní nabídce a otevřeme dialogové okno Rozšířený filtr (obrázek 11).

  3. V poli Načíst parametry filtru z zadáme adresu pro pojmenovanou oblast buď výběrem pojmenované oblasti z rozevíracího seznamu, zadáním odkazu nebo výběrem buněk z listu. Při výběru buněk můžeme dočasně dialogové okno zmenšit pomocí tlačítka Zmenšit/Rozšířit.

  4. Klepnutím na tlačítko OK použijeme filtr a zavřeme dialogové okno.

Poznámka

Pro jednotlivé pojmenované oblasti je možné zaškrtnout políčko Filtr v dialogových oknech Definovat název a Spravovat názvy (obrázky 2 , respektive 3 ). V rozevíracím seznamu v oblasti Načíst parametry filtru z dialogového okna Rozšířený filtr lze vybrat pouze pojmenované oblasti označené pro filtrování tímto způsobem. Databázové oblasti nelze vybrat v rozevíracím seznamu.

Možnosti rozšířeného filtru jsou stejné jako možnost standardního filtru a jsou podrobněji popsány v kapitole 2, Zadávání, úprava a formátování dat.

Obrázek 12 ukazuje příklad oblasti kritérií pro příklad klasifikačního listu na obrázku 1.

Image10

Obrázek 12: Oblast kritérií rozšířeného filtru (v listu 2)

V této oblasti jsou dvě skupiny kritérií: první zobrazuje záznamy studentů, kteří v každém domácím úkolu dosáhli více než 75 %, a druhá zobrazuje záznamy všech studentů se jménem „Ferdinand“. Obrázek 13 zobrazuje výsledek této operace filtrování podle těchto kritérií.

Image11

Obrázek 13: Příklad klasifikačního listu filtrovaného pomocí rozšířeného filtru

Užitečné funkce podobné databázi

Funkce kategorie databáze

Přehled

Dvanáct funkcí v kategorii Databáze nám má pomoci analyzovat jednoduchou databázi, která zabírá obdélníkovou plochu tabulky tvořenou sloupci a řádky, přičemž data jsou uspořádána tak, že každý záznam má jeden řádek. V buňce záhlaví každého sloupce se zobrazuje název sloupce a tento název obvykle odráží obsah každé buňky v daném sloupci.

Funkce v kategorii Databáze přijímají tři následující argumenty:

  1. Databáze. Oblast buněk databáze.

  2. Pole databáze. Sloupec obsahující data, která se mají použít při výpočtech funkce.

  3. Kritéria vyhledávání. Oblast buněk samostatné oblasti tabulky obsahující kritéria vyhledávání.

Tyto argumenty jsou podrobněji popsány níže.

Všechny funkce mají stejnou jednoduchou koncepci ovládání. Prvním logickým krokem je použití zadaných Vyhledávacích kritérií k určení podmnožiny záznamů v Databázi, které mají být použity při následných výpočtech. Druhým krokem je získání hodnot dat a provedení výpočtů spojených s konkrétní funkcí (průměr, součet, součin atd.). Zpracovávají se hodnoty ve sloupci Pole databáze vybraných záznamů.

Argumenty funkce databáze

Následující definice argumentů platí pro všechny funkce kategorie Databáze:

Argument databáze

Databáze určuje rozsah buněk obsazených databázovou tabulkou. První řádek oblasti obsahuje názvy polí a další řádky jsou záznamy s odpovídajícími hodnotami polí.

Jedním ze způsobů, jak definovat rozsah buněk, je zadat odkaz na horní levou buňku, následovaný dvojtečkou (:) a poté odkaz na spodní pravou buňku. Příkladem může být A1:E10.

Argument Databáze lze zadat také předáním názvu pojmenované oblasti nebo oblasti databáze. Použití smysluplného názvu pro definici oblasti buněk může zlepšit čitelnost vzorců a údržbu dokumentu. Pokud se název neshoduje s názvem definované oblasti, Calc zobrazí chybu #NAME?.

Další chyby, které mohou být hlášeny v důsledku neplatného argumentu Databáze, jsou #VALUE! a Err:504 (chyba v seznamu parametrů).

Argument Pole databáze

Pole databáze určuje sloupec, který funkce použije pro své výpočty po použití kritérií vyhledávání a výběru datových řádků. Nesouvisí s kritérii vyhledávání.

Argument Pole databáze zadáme některým z následujících způsobů:

Argument Pole databáze je pro funkce DCOUNT a DCOUNTA nepovinný, ale pro ostatních deset Databázových funkcí je vyžadován.

Argument Kritéria vyhledávání

Kritéria vyhledávání určují oblast buněk obsahujících kritéria vyhledávání. Stejně jako Databáze jsou jeho první řádek také názvy polí a další řádky jsou podmínky pro související pole. Oblasti DatabázeKritéria vyhledávání nemusí sousedit nebo dokonce být na stejném listu.

Jedním ze způsobů, jak definovat oblast buněk, je zadat odkaz na horní levou buňku, následovaný dvojtečkou (:) a poté odkaz na spodní pravou buňku; například A13:B14. Oblast buněk lze zadat také předáním názvu definované pojmenované oblasti nebo oblasti databáze. Pokud se název neshoduje s názvem definované oblasti, Calc zobrazí chybu #NAME?.

Err:504 (chyba v seznamu parametrů) může být také hlášena jako důsledek neplatného argumentu Kritéria vyhledávání.

Obsah oblasti Kritéria vyhledávání je podrobněji popsán v následující části.

Definování kritérií vyhledávání

Počet sloupců, které zabírá oblast Kritéria vyhledávání, nemusí být stejný jako šířka oblasti Databáze. Všechny nadpisy, které se objeví v prvním řádku Kritéria vyhledávání, musí být totožné s nadpisy v prvním řádku Databáze. Ne všechny nadpisy v Databázi se však musí objevit v prvním řádku Kritéria vyhledávání, zatímco nadpis v Databázi se může v prvním řádku Kritéria vyhledávání objevit vícekrát.

Kritéria vyhledávání se zadávají do buněk druhého a následujících řádků oblasti Kritéria vyhledávání pod řádek obsahující nadpisy. Prázdné buňky v oblasti Kritéria vyhledávání jsou ignorovány.

Vytvoříme kritéria v buňkách oblasti Kritéria vyhledávání pomocí operátorů porovnání <, <=, =, <>, >= a >. = se předpokládá, pokud buňka není prázdná, ale nezačíná operátorem porovnání.

Pokud napíšeme několik kritérií do jednoho řádku, jsou spojena pomocí AND. Pokud napíšeme několik kritérií do různých řádků, jsou spojeny pomocí OR.

Kritéria lze vytvářet pomocí zástupných znaků, pokud jsou zástupné znaky povoleny pomocí možnosti Povolit zástupné znaky ve vzorcích v dialogovém okně Nástroje > Možnosti > LibreOffice Calc > Výpočty. Pokud je pro naši tabulku důležitá interoperabilita s programem Microsoft Excel, měla by být tato možnost povolena.

Ještě silnější kritéria lze vytvořit pomocí regulárních výrazů, pokud jsou regulární výrazy povoleny pomocí možnosti Povolit regulární výrazy ve vzorcích v dialogovém okně Nástroje > Možnosti > LibreOffice Calc > Výpočty.

Tip

Při použití funkcí, kde řetězec kritéria vyhledávání může být regulárním výrazem, se nejprve provede převod řetězce kritéria na čísla. Například ".0" se převede na 0.0 a tak dále. V případě úspěchu se nebude jednat o regulární výraz, ale o číselnou shodu. Při přepnutí na lokální prostředí, kde desetinný oddělovač není tečka, však převod regulárního výrazu funguje. Chceme-li vynutit vyhodnocení regulárního výrazu namísto číselného výrazu, použijeme nějaký výraz, který nelze chybně přečíst jako číselný, například ".[0]" nebo ".\0" nebo "(?i).0".

Dalším nastavením, které ovlivňuje způsob zpracování kritérií vyhledávání, je volba Kritéria vyhledávání = a <> musí platit pro celé buňky v dialogovém okně Nástroje > Možnosti > LibreOffice Calc > Výpočet. Tato možnost určuje, zda se kritéria vyhledávání nastavená pro funkce Databáze musí přesně shodovat s celou buňkou. Pokud je pro naši tabulku důležitá interoperabilita s programem Microsoft Excel, měla by být tato možnost povolena.

Příklad použití funkce databáze

Image9

Obrázek 14 – Příklad použití funkce Databáze

Obrázek 14 představuje jednoduchý příklad použití jedné z funkcí v kategorii Databáze. Vzorec ve vybrané buňce E15 je vidět na liště vzorců a obsahuje volání funkce DCOUNT. Argumenty volání této funkce jsou následující:

Mnoho dalších příkladů nalezneme po vyhledání „databázových funkcí“ v systému nápovědy nebo na příslušné stránce každé funkce ve Wiki o funkcích Calc na adrese https://wiki.documentfoundation.org/Documentation/Calc_Functions.

Seznam funkcí databáze

Poznámka

Calc bude při výpočtech pomocí těchto funkcí považovat data a logické hodnoty (například TRUE nebo FALSE) za číselné.

DAVERAGE

Pro všechny řádky (záznamy databáze), které odpovídají zadaným kritériím vyhledávání, vypočítá DAVERAGE průměr číselných hodnot v buňkách (polích) zadaného sloupce. Nečíselné hodnoty v těchto buňkách jsou ignorovány.

Vrací chybu #DIV/0! , pokud zadaným kritériím vyhledávání neodpovídají žádné záznamy nebo pokud v buňkách zadaného sloupce nejsou žádné číselné hodnoty pro odpovídající záznamy.

Syntaxe: DAVERAGE(Databáze; Pole databáze; Kritéria databáze)

DCOUNT

Pro všechny řádky (záznamy databáze), které odpovídají zadaným kritériím vyhledávání, spočítá DCOUNT počet buněk (polí) zadaného sloupce, které obsahují číselné hodnoty. Pokud však není zadán žádný sloupec, vrátí DCOUNT počet všech záznamů, které odpovídají zadaným vyhledávacím kritériím, bez ohledu na jejich obsah.

Syntaxe: DCOUNT(Databáze; [Pole databáze]; Kritéria vyhledávání)

DCOUNTA

Pro všechny řádky (záznamy databáze), které odpovídají zadaným kritériím vyhledávání, spočítá DCOUNTA počet buněk (polí) zadaného sloupce, které nejsou prázdné. Prázdné buňky zadaného sloupce se nezapočítávají. Pokud však není zadán žádný sloupec, vrátí DCOUNTA počet všech záznamů, které odpovídají zadaným vyhledávacím kritériím, bez ohledu na jejich obsah.

Syntaxe: DCOUNTA(Databáze; [Pole databáze]; Kritéria vyhledávání)

DGET

DGET vrátí obsah buňky (pole) zadaného sloupce pro jediný řádek (záznam databáze), který odpovídá zadaným kritériím vyhledávání.

Calc hlásí Err:502 (neplatný argument), pokud je nalezeno více shod, nebo chyba #VALUE! (nesprávný datový typ), pokud není nalezena žádná shoda. Chyba #VALUE! je hlášena také v případě, že je nalezena jediná shoda, ale příslušná buňka je prázdná.

Syntaxe: DGET(Databáze; Pole databáze; Kritéria vyhledávání)

DMAX

Pro všechny řádky (záznamy databáze), které odpovídají zadaným kritériím vyhledávání, vypočítá DMAX maximální hodnotu v buňkách (polích) zadaného sloupce, které obsahují číselné hodnoty. Prázdné buňky nebo buňky obsahující jiné než číselné znaky se nezapočítávají.

Vrátí hodnotu 0, pokud nebyla nalezena žádná shoda nebo pokud v buňkách zadaného sloupce nejsou žádné nenulové číselné hodnoty pro odpovídající záznamy.

Syntaxe: DMAX(Databáze; Pole databáze; Kritéria vyhledávání)

DMIN

Pro všechny řádky (záznamy databáze), které odpovídají zadaným kritériím vyhledávání, vypočítá DMIN minimální hodnotu v buňkách (polích) zadaného sloupce, které obsahují číselné hodnoty. Prázdné buňky nebo buňky obsahující jiné než číselné znaky se nezapočítávají.

Vrátí hodnotu 0, pokud nebyla nalezena žádná shoda nebo pokud v buňkách zadaného sloupce nejsou žádné nenulové číselné hodnoty pro odpovídající záznamy.

Syntaxe: DMIN(Databáze; Pole databáze; Kritéria vyhledávání)

DPRODUCT

Pro všechny řádky (záznamy databáze), které odpovídají zadaným kritériím vyhledávání, vypočítá DPRODUCT součin všech číselných hodnot v buňkách (polích) zadaného sloupce. Prázdné buňky nebo buňky obsahující jiné než číselné znaky se nezapočítávají.

Vrátí hodnotu 0, pokud nebyla nalezena žádná shoda nebo pokud v buňkách zadaného sloupce nejsou žádné číselné hodnoty pro odpovídající záznamy.

Syntaxe: DPRODUCT(Databáze; Pole databáze; Kritéria vyhledávání)

DSTDEV

Pro všechny řádky (záznamy databáze), které odpovídají zadaným kritériím vyhledávání, vypočítá DSTDEV výběrovou směrodatnou odchylku na základě číselných hodnot v buňkách (polích) zadaného sloupce. Nečíselné hodnoty jsou ignorovány.

Vrací chybu #NUM! , pokud zadaným kritériím vyhledávání vyhovuje přesně jeden záznam nebo pokud je v buňkách zadaného sloupce pro vyhovující záznamy pouze jedna číselná hodnota.

Vrátí hodnotu 0, pokud nebyla nalezena žádná shoda nebo pokud v buňkách zadaného sloupce nejsou žádné číselné hodnoty pro odpovídající záznamy.

Syntaxe: DSTDEV(Databáze; Pole databáze; Kritéria vyhledávání)

DSTDEVP

Pro všechny řádky (záznamy databáze), které odpovídají zadaným kritériím vyhledávání, vypočítá DSTDEVP směrodatnou odchylku populace na základě číselných hodnot v buňkách (polích) zadaného sloupce. Nečíselné hodnoty jsou ignorovány.

Vrací chybu #NUM! , pokud zadaným kritériím vyhledávání neodpovídají žádné záznamy nebo pokud v buňkách zadaného sloupce nejsou žádné číselné hodnoty pro odpovídající záznamy.

Syntaxe: DSTDEVP(Databáze; Pole databáze; Kritéria vyhledávání)

DSUM

Pro všechny řádky (záznamy v databázi), které odpovídají zadaným kritériím vyhledávání, vypočítá DSUM součet všech číselných hodnot v buňkách (polích) zadaného sloupce. Prázdné buňky nebo buňky obsahující jiné než číselné znaky se nezapočítávají.

Vrátí hodnotu 0, pokud nebyla nalezena žádná shoda nebo pokud v buňkách zadaného sloupce nejsou žádné číselné hodnoty pro odpovídající záznamy.

Syntaxe: DSUM(Databáze; Pole databáze; Kritéria vyhledávání)

DVAR

Pro všechny řádky (záznamy databáze), které odpovídají zadaným kritériím vyhledávání, vypočítá DVAR výběrový rozptyl na základě číselných hodnot v buňkách (polích) zadaného sloupce. Nečíselné hodnoty jsou ignorovány.

Vrací chybu #NUM! , pokud zadaným kritériím vyhledávání vyhovuje přesně jeden záznam nebo pokud je v buňkách zadaného sloupce pro vyhovující záznamy pouze jedna číselná hodnota.

Vrátí hodnotu 0, pokud nebyla nalezena žádná shoda nebo pokud v buňkách zadaného sloupce nejsou žádné číselné hodnoty pro odpovídající záznamy.

Syntaxe: DVAR(Databáze; Pole databáze; Kritéria vyhledávání)

DVARP

Pro všechny řádky (záznamy databáze), které odpovídají zadaným vyhledávacím kritériím, vypočítá DVARP populační odchylku na základě číselných hodnot v buňkách (polích) zadaného sloupce. Nečíselné hodnoty jsou ignorovány.

Vrací chybu #NUM! , pokud zadaným kritériím vyhledávání neodpovídají žádné záznamy nebo pokud v buňkách zadaného sloupce nejsou žádné číselné hodnoty pro odpovídající záznamy.

Syntaxe: DVARP(Databáze; Pole databáze; Kritéria vyhledávání)

Další funkce podobné databázi

Program Calc obsahuje více než 500 funkcí, které nám pomohou analyzovat a odkazovat na data. Některé z těchto funkcí jsou určeny pro použití s tabulkovými daty (například HLOOKUP a VLOOKUP), zatímco jiné lze použít v jakémkoli kontextu. Tato část obsahuje seznam některých funkcí, které mohou být užitečné, pokud hodláme pro svou databázi používat tabulky v Calcu. Mnohé z nich budou známé jako typické funkce tabulkového procesoru používané v jiných kontextech, zatímco některé mohou být méně často používané, ale jsou obzvláště užitečné při práci s databázovými tabulkami.

Další referenční materiály ke všem funkcím Calcu najdeme v nápovědě a v oblasti Funkce Calc (Calc Functions) na wiki The Document Foundation na adrese https://wiki.documentfoundation.org/Documentation/Calc_Functions.

Funkce

Kategorie

Popis

AGGREGATE

Matematické

Vrátí celkový výsledek vypočtený použitím vybrané agregační funkce na zadaná data. K dispozici je devatenáct volitelných agregačních funkcí, včetně průměru, počtu, velkého počtu, maxima, mediánu, minima, módu, percentilu, součinu, kvartilu, malého počtu, směrodatné odchylky, součtu a rozptylu.

AVERAGE

Statistické

Vrátí aritmetický průměr zadaných dat, přičemž ignoruje prázdné buňky a buňky obsahující text.

AVERAGEA

Statistické

Vrátí aritmetický průměr zadaných dat, přičemž ignoruje prázdné buňky, ale každé buňce obsahující text přiřadí hodnotu 0.

AVERAGEIF

Statistické

Vrátí aritmetický průměr všech buněk v rozsahu, které splňují dané kritérium.

AVERAGEIFS

Statistické

Vrátí aritmetický průměr všech buněk v rozsahu, které splňují více kritérií ve více rozsazích.

CHOOSE

Sešit

Vrátí jednu hodnotu ze zadaných dat vybranou podle indexu předaného jako argument.

COUNT

Statistické

Vrátí počet číselných hodnot v zadaných datech, přičemž ignoruje prázdné buňky a buňky obsahující text.

COUNTA

Statistické

Vrátí počet číselných a textových hodnot v zadaných datech, přičemž ignoruje prázdné buňky.

COUNTBLANK

Statistické

Vrací počet prázdných buněk v zadaných datech.

COUNTIF

Statistické

Vrátí počet buněk v rozsahu, které splňují dané kritérium.

COUNTIFS

Statistické

Vrátí počet buněk, které splňují více kritérií ve více oblastech.

HLOOKUP

Sešit

Vyhledá zadanou hodnotu v prvním řádku tabulky (často záhlaví sloupce) a vrátí hodnotu získanou ze stejného sloupce, ale z jiného řádku. HLOOKUP znamená horizontální vyhledávání.

INDEX

Sešit

Vrátí obsah jedné buňky tabulky. Pozice této buňky je určena posunem řádku a sloupce. Lze jej použít také v kontextu vzorce pole pro získání dat z více buněk.

INDIRECT

Sešit

Vrací platný odkaz vytvořený ze zadané řetězcové reprezentace odkazu. Tato funkce je výkonná, protože umožňuje uživateli vytvářet dynamické odkazy.

LOOKUP

Sešit

Vyhledá zadanou hodnotu v jednom řádku nebo sloupci a vrátí hodnotu ze stejné pozice v druhém řádku nebo sloupci. Není nutné, aby vyhledávací a výsledková oblast sousedily.

MATCH

Sešit

Vrátí relativní pozici hledané položky v jednom řádku nebo sloupci.

MAX

Statistické

Vrátí maximální hodnotu v zadaných datech, přičemž ignoruje prázdné buňky a buňky obsahující text.

MAXA

Statistické

Vrátí maximální hodnotu v zadaných datech, přičemž ignoruje prázdné buňky, ale každé buňce obsahující text přiřadí hodnotu 0.

MAXIFS

Statistické

Vrátí maximální hodnotu všech buněk v rozsahu, které splňují více kritérií ve více rozsazích.

MEDIAN

Statistické

Vrátí mediánovou hodnotu zadaných dat. Medián konečného seznamu čísel je "prostřední" číslo, pokud jsou tato čísla seřazena od nejmenšího po největší.

MIN

Statistické

Vrátí minimální hodnotu v zadaných datech, přičemž ignoruje prázdné buňky a buňky obsahující text.

MINA

Statistické

Vrátí minimální hodnotu v zadaných datech, přičemž ignoruje prázdné buňky, ale každé buňce obsahující text přiřadí hodnotu 0.

MINIFS

Statistické

Vrátí minimální hodnotu všech buněk v oblasti, které splňují více kritérií ve více oblastech.

MODE

MODE.SNGL

Statistické

Vrací hodnotu režimu zadaných dat. Režim je nejběžnější hodnota v seznamu hodnot. Pokud existuje několik hodnot se stejnou frekvencí, je vrácena nejmenší hodnota.

MODE.MULT

Statistické

Vrací vertikální pole hodnot režimu zadaných dat. Režim je nejběžnější hodnota v seznamu hodnot. Funkce vrací více než jednu hodnotu, pokud existuje více režimů se stejnou četností výskytu.

OFFSET

Sešit

Vrátí upravený odkaz na jednu buňku nebo oblast buněk posunutý o určitý počet řádků a sloupců od daného referenčního bodu.

PRODUCT

Matematické

Vrátí součin číselných hodnot v zadaných datech, přičemž ignoruje prázdné buňky a buňky obsahující text.

STDEV

STDEV.S

Statistické

Vrátí výběrovou směrodatnou odchylku zadaných dat, přičemž ignoruje prázdné buňky a buňky obsahující text.

STDEVA

Statistické

Vrátí výběrovou směrodatnou odchylku zadaných dat, přičemž ignoruje prázdné buňky, ale každé buňce obsahující text přiřadí hodnotu 0.

STDEVP

STDEV.P

Statistické

Vrátí směrodatnou odchylku populace zadaných dat, přičemž ignoruje prázdné buňky a buňky obsahující text.

STDEVPA

Statistické

Vrátí směrodatnou odchylku populace zadaných dat, přičemž ignoruje prázdné buňky, ale každé buňce obsahující text přiřadí hodnotu 0.

SUBTOTAL

Matematické

Vrátí celkový výsledek vypočtený použitím vybrané součtové funkce na zadaná data. K dispozici je jedenáct volitelných součtových funkcí, včetně průměru, počtu, maxima, minima, součinu, směrodatné odchylky, součtu a rozptylu. Tuto funkci použijeme spolu s funkcí Automatický filtr, abychom brali v úvahu pouze filtrované záznamy.

SUM

Matematické

Vrátí součet zadaných dat, přičemž ignoruje prázdné buňky a buňky obsahující text.

SUMIF

Matematické

Vrátí součet všech buněk v oblasti, které splňují dané kritérium.

SUMIFS

Matematické

Vrátí součet všech buněk v oblasti, které splňují více kritérií ve více oblastech.

VAR

VAR.S

Statistické

Vrátí variantu vzorku zadaných dat, přičemž ignoruje prázdné buňky a buňky obsahující text.

VARA

Statistické

Vrátí variantu vzorku zadaných dat, přičemž ignoruje prázdné buňky, ale každé buňce, která obsahuje text, přiřadí hodnotu 0.

VARP

VAR.P

Statistické

Vrátí populační odchylku zadaných dat, přičemž ignoruje prázdné buňky a buňky obsahující text.

VARPA

Statistické

Vrátí populační variaci zadaných dat, přičemž ignoruje prázdné buňky, ale každé buňce, která obsahuje text, přiřadí hodnotu 0.

VLOOKUP

Sešit

Vyhledá zadanou hodnotu v prvním sloupci tabulky (často v záhlaví řádku) a vrátí hodnotu získanou ze stejného řádku, ale z jiného sloupce. VLOOKUP znamená vertikální vyhledávání.

Obsah