V řadě případů je důležité, aby vstupní data zadávaná z klávesnice byla určitého typu (číselné údaje, textové řetězce, logické hodnoty,…) nebo aby nabývala hodnot pouze v určitém rozsahu, případně aby byla vybírána z přesně vymezené množiny přípustných hodnot.

K zajištění těchto požadavků slouží příkaz Ověření dat, který volíme z nabídky Data. Zobrazí se dialogové okno Ověření dat, karta Nastavení, kde lze zadat parametry kontroly vstupních dat.

• Povolit – implicitní volba je nastavena na Jakoukoli hodnotu, tzn., že vstupní data nejsou nijak ověřována a do buňky lze zadat libovolný údaj. Typ vstupních dat lze omezit příslušnými volbami na Celé číslo, Desetinné číslo, Datum, čas a Délku textu. Pokud je povolena volba Délku textu, lze zadat do buňky text, číslo (celé i desetinné) nebo vzorec. Zadané číslo (programem je považováno za číslo, nepřevádí se na text) a výsledek vzorce musí mít vyhovující počet znaků. Volby Seznam a Vlastní jsou popsány dále v této kapitole.
• Data – způsob omezení rozsahu hodnot se nastaví výběrem vhodné volby ze seznamu.
• Minimum/Maximum – název polí (Počáteční datum/Koncové datum, Počáteční čas/Koncový čas) pro zadání minimální a maximální hodnoty se mění podle výběru typu dat a způsobu omezení rozsahu hodnot v seznamech Povolit a Data, popř. se zobrazí pouze jediné pole pro zadání konkrétní hodnoty (Hodnota, Datum, Čas). Všechny hodnoty lze zadat trojím způsobem:
• Přímým zápisem hodnoty – při zadání celého čísla lze uvést číslo, ale i datum (např. 13.2.2004), které je programem převedeno na číslo 38030, což je pořadové číslo dne 13.2.2004. Při zadání desetinného čísla lze uvést pouze desetinné číslo, nikoliv čas. Při zadání data se musí uvést i rok. Přípustné způsoby zadání data (např. 13. 2. 2004) jsou následující: 13.2.4; 13.2.04; 13.2.2004; 13.02.4; 13.02.04; 13.02.2004; 38030 – všechny možnosti budou převedeny na datum ve tvaru 13.2.2004. Přípustné způsoby zadání času (např. 12:00:00) jsou následující: 12:0; 12:0:0; 12:00; 12:00:00; 0,5 – všechny možnosti budou převedeny na čas ve tvaru 12:00:00.
• Odkazem na buňku/oblast – do pole lze zadat odkaz na buňku/oblast, která obsahuje požadovanou hodnotu/hodnoty. K zadání využijeme možnost přímého výběru buňky/oblasti myší při zobrazeném dialogovém okně (kurzor má tvar prázdného kříže, kolem vybrané buňky/oblasti bliká rámeček). Odkaz je nutné zapsat ve tvaru vzorce, tj. musí začínat rovnítkem následovaným adresou buňky/oblasti. Pokud je oblast vybrána myší, dosadí se odkaz včetně rovnítka automaticky
• Názvem pojmenované oblasti – do pole lze zadat název pojmenované oblasti, která obsahuje požadovanou hodnotu/hodnoty.

Výběr vstupních dat ze seznamu přípustných hodnot

Potřebujeme-li omezit data, která mohou být zadána do buňky, na určitou množinu přípustných hodnot, vybereme v poli Povolit volbu Seznam. Pokud na kartě Nastavení zaškrtneme volbu Rozevírací seznam v buňce, objeví se po kliknutí na takto ověřovanou buňku přímo v buňce rozevírací seznam, ze kterého lze volit jednotlivé přípustné hodnoty.

Pokud by volba Rozevírací seznam v buňce zaškrtnuta nebyla, bylo by nutné přípustné hodnoty do buňky psát z klávesnice. Množinu přípustných hodnot lze do pole Zdroj zadat těmito způsoby:

• Přímým zápisem hodnot – mezi jednotlivými hodnotami je středník. Tento způsob nemá žádnou vazbu na list nebo sešit, ale je omezen maximálním počtem znak (255) celého seznamu.
• Odkazem na oblast – pokud máme v listu připravenu oblast se seznamem, lze zdroj zadat přímým výběrem této oblasti myší. Odkaz na oblast je absolutní a opět se automaticky dosadí ve tvaru vzorce.
• Názvem pojmenované oblasti – tato možnost předpokládá v sešitu existenci pojmenované oblasti s přípustnými hodnotami. K zadání názvu do pole zobrazíme seznam všech definovaných názvů v sešitu klávesou a vybereme příslušný název. Název se dosadí ve tvaru vzorce.
• Vzorcem s podmínkou – tuto možnost využijeme v případě, že potřebujeme v buňce podle různých okolností zobrazovat různé seznamy přípustných hodnot, tedy vytvořit podmíněný rozbalovací seznam. Pro tyto účely je vhodné, aby všechny seznamy byly umístěny v oblastech pojmenovaných krátkým názvem, neboť v poli Zdroj jsme omezeni na maximálně 255 znak.

Omezení vstupních dat vzorcem

Při omezování přípustných vstupních dat se mohou vyskytnout takové požadavky, které není možné zajistit žádným z uvedených způsobů nastavení ověřovacího kritéria. Jsou to např. tyto požadavky: aby bylo možné do buňky zadat pouze logické hodnoty, pouze sudá čísla, pouze lichá čísla, aby údaj v buňce mohl nabývat hodnot z více interval apod.

Pokud lze požadavek zformulovat do vzorce, jehož výsledkem je logická hodnota PRAVDA nebo NEPRAVDA, postupujeme takto:

• Vybereme buňku/oblast, jejíž vstupní data potřebujeme omezit.
• Data – karta Nastavení – Povolit – Vlastní.
• Do pole Vzorec zapíšeme ověřovací vzorec. Odkazy na buňku/oblast lze zadávat přímo výběrem myší. Nepříjemná je skutečnost, že není přístupná volba Vložit funkci ani v řádku vzorec ani v menu Vložit. Je tedy nutné znát syntaxi funkcí a vzorce psát z klávesnice.

V ověřovacích vzorcích fungují bez problémů základní funkce, zatímco funkce z doplňku Analytické nástroje jsou vyhodnoceny jako neplatný název. Pro omezení na sudá/lichá čísla může být použita základní matematická funkce MOD(číslo, dělitel), která vrací zbytek po dělení čísla dělitelem, nikoliv doplňková funkce informační analýzy ISEVEN(číslo)/ISODD(číslo), která vrací logickou hodnotu PRAVDA, pokud je číslo sudé/liché.

Modifikace ověření vstupních dat

Pokud potřebujeme modifikovat stávající ověření vstupních dat, postupujeme takto: umístíme kurzor do buňky Data – Ověření dat – provést požadované změny – pokud chceme, aby se provedená modifikace automaticky rozšířila na všechny buňky se stejným typem ověření, zaškrtnout volbu Použít tyto změny u všech ostatních buněk se stejným nastavením. Pokud má být modifikováno ověření pouze té buňky, ve které stál kurzor před vyvoláním dialogového okna Ověření dat, nezaškrtáváme volbu Použít tyto změny u všech ostatních buněk se stejným nastavením.

Odstranění ověření

V dialogovém okně Ověření dat se nachází tlačítko Vymazat vše. Tímto tlačítkem se odstraní veškerá nastavení ve všech třech kartách – Nastavení, Zpráva při zadávání i Chybové hlášení.

Vyhledání buněk s ověřením

Pro získání orientace o tom, které buňky mají nastaveno ověření a které nikoliv, slouží příkaz v hlavní nabídce Úpravy – Přejít na tlačítko Jinak – v dialogovém okně Přejít na zapnout volbu Ověření – zapnout volbu Vše (označí buňky s jakýmkoliv ověřením), resp. Stejné (označí se buňky s ověřením shodným, jako má aktuální buňka). Pokud předem byla/nebyla vybrána oblast, označí se všechny buňky z této oblasti/v celém aktivním listu, které mají nastaveno ověření – buď všechny nebo pouze stejné.

Kontrola údajů v buňkách

Ověření dat lze využít také ke kontrole údajů v již vyplněných buňkách. Použijeme k tomu tlačítko Zakroužkovat neplatná data na panelu Datové nástroje – Ověření dat. Pokud vybereme oblast buněk s nastaveným ověřením, a potom klikneme na toto tlačítko, zakroužkují se (červeně) ty buňky, jejichž obsah nevyhovuje nastavenému ověření.

Pokud je v dialogovém okně Ověření dat zaškrtnuta volba Přeskakovat prázdné buňky, jsou prázdné buňky při testování kritéria ověření ignorovány. V případě, že není zaškrtnuta volba Přeskakovat prázdné buňky, budou i prázdné buňky testovány, a pokud jejich obsah nebude vyhovovat ověřovacím kritériím, budou zakroužkovány jako buňky s neplatnými daty.

Zakroužkování odstraníme tlačítkem Vymazat kroužky ověření.

C1 – Ověření vstupních dat

Na adrese: K:\ProŽáky\Bečvářová Silvie\Podklady\EXCEL je dostupný excelový soubor 07-ko-vstupnich-dat.

• Na záložce Klasifikace nastavte ověření dat pro jednotlivé předměty. Je možné zadat známky v rozmezí 1 – 5. Vytvořte také odpovídající Chybové hlášení a Zprávu při zadávání.
• Doplňte vzorce pro výpočet průměrné známky.
• Na záložce Přijímací zkoušky nastavte ověření dat pro ČJ, AJ, M. Je možné zadat body v rozmezí 0 – 100. Vytvořte také odpovídající Chybové hlášení a Zprávu při zadávání.
• Doplňte vzorec pro sloupec Výsledek:
  • Pokud Celkem bodů >= 150 přijat.
  • Pokud Celkem bodů < 150 nepřijat.

C2 – Ověření vstupních dat – DB zákazníků

Jste administrátoři databáze zákazníků ve firmě a musíte zajistit, aby zaměstnanci zadávali údaje správně. Vaším úkolem je nastavit ověření dat, které zabrání chybám při vkládání.

Vytvořte tabulku s následujícími sloupci a nastavte v nich pravidla pro ověření dat:

ID zákazníka	Jméno a příjmení	Datum narození	Telefonní číslo	E-mail	Počet objednávek	Výše útraty (Kč)
1001	Novák Jan	15.5.1985	777123456	jan.novak@email.cz	5	15 000
1002	Svobodová Eva	8.12.1990	606987654	eva.svobodova@email.cz	3	8 500
1003	Dvořák Petr	21.9.2002	732654321	petr.dvorak@email.cz	12	25 300

• Ověření číselné řady: Omezte ID zákazníka na hodnoty mezi 1000 a 9999 (čtyřmístné číslo).
• Ověření data: Nastavte, aby Datum narození mohlo být pouze mezi 1.1.1900 a 31.12.2010 (zákazník musí mít min. 14 let).
• Ověření délky telefonního čísla: Zajistěte, aby do sloupce Telefonní číslo bylo možné zadat pouze 9místné číslo.
• Ověření počtu objednávek: Omezte hodnotu Počet objednávek na celá čísla od 0 do 100.
• Ověření výše útraty: Nastavte pravidlo, že Výše útraty musí být kladné číslo (větší než 0).

Příklady na ověření dat:

• https://www.zstgmivancice.cz/zaci/vzdelavani/e-vyuka/informatika/informatika-6-9-roc-/9-filtrovani-razeni-a-overeni-dat-v-tabulce.html