Ověření dat

Při zadávání dat přímo z klávesnice je poměrně vysoké procento chybovosti. Ať už jde o překlepy nebo zadání dat mimo předpokládaný rozsah. Stejně tak může docházet k zadání jiných dat než je žádoucí v případě, že někdo tabulku připravuje s určitou představou a někdo jiný pak do tabulky data vyplňuje. Pokud pracujeme např. se seznamy, pak můžeme nastavit pro jednotlivé buňky (sloupce) určitá omezení toho co smí uživatel do buňky zadat, případně upozornit na zadání údaje mimo předpokládaný rozsah. Abychom omezili procento chybovosti a získali data v požadované formě a rozsahu, můžeme využít možnosti ověření dat.

Standardně můžeme do buňky zadat téměř cokoliv.

Do buňky lze zadat až 32 767 znaků (to odpovídá textu v rozsahu více jak osmnácti normostran)
Hodnoty kladných čísel v rozsahu 2,229E-308 až 1,79769313486231E308
Hodnoty záporných čísel v rozsahu -2,2250738585072E-308 až -1E-307
Datum v rozsahu 1. ledna 1900 - 31. prosince 9999 (resp. data od 1. ledna 1904, jestliže používáme systém dat začínající rokem 1904)

Pozn.: Do buňky lze z nějakého důvodu zadat i neexistující datum 0.1.1900. Excel sice text zadaný jako 0.1.1900 nebo 0/1/1900 na datum automaticky nepřevede, ale při převedení nuly na formát data zobrazí v buňce datum 0.1.1900, se kterým jde dále provádět výpočty stejně jako s běžnými daty.

Pomocí ověření dat můžeme můžeme omezit délku textu, rozmezí hodnot, omezit rozsah data nebo času, popřípadě můžeme přímo omezit zadávané údaje na určitou množinu hodnot.

Ověření dat je tedy částečnou kontrolou správnosti nově zadávaných dat.

Postup

Vybereme buňku nebo oblast, kde chceme ověření dat nastavit.
Z nabídky Data vybereme příkaz Ověření...
V dialogovém okně Ověření dat nastavíme omezující podmínky případně zprávu při zadávání a chybové hlášení.

Dialogové okno Ověření dat má tři karty:

Na kartě Nastavení máme možnost nastavit podmínku zápisu - ověřovací kritéria. Standardně můžeme zadat jakoukoli hodnotu (s omezením viz výše). Možnosti omezení:

Celé číslo - uzavřený nebo otevřený interval hodnot omezený jednostranně nebo oboustranně, vynucení konkrétní hodnoty nebo naopak omezení, která čísla jsou nežádoucí.
Desetinné číslo - obdobně jako u celého čísla.
Seznam - taxativně vybrané hodnoty, které lze do buňky zapsat. Ostatní hodnoty jsou považovány za chybně zadané. Seznam hodnot můžeme zadat do pole Zdroj s tím, že jednotlivé položky oddělíme středníkem (v anglické verzi Excelu se jako oddělovač používá čárka) nebo můžeme do pole Zdroj zadat adresu nebo název oblasti, kde se seznam nachází. Pouze při zvolení seznamu jako ověřovacího kritéria máme možnost povolit Rozvírací seznam v buňce. Uživatel, který pak data zadává má možnost po výběru buňky hodnotou zapsat nebo kliknout na šipku vpravo a hodnotu vybrat.
Datum - uzavřený nebo otevřený interval datumů omezený jednostranně nebo oboustranně, vynucení konkrétního datumu nebo naopak omezení, které datumy jsou nežádoucí.
Čas - uzavřený nebo otevřený interval času omezený jednostranně nebo oboustranně, vynucení konkrétního času nebo naopak omezení, které časy jsou nežádoucí.
Délka textu - ověření stejné jako u ověřovacích kritérií výše s tím, že podmínka platí nejen ne text, ale stejně tak na čísla. Pokud pak zadáváme do buňky vzorec nebo funkci, pak se ověřuje délka výsledku, který vrací nikoliv délku zadáveného vzorce resp. funkce.
Vlastní podmínka - vzorec nebo funkce, která vrací hodnotu PRAVDA nebo NEPRAVDA.

Zpráva při zadávání

Jestliže chceme osobě, která bude údaje vyplňovat napovědět, jaký údaj do buňky patří, pak můžeme napsat krátkou zprávu do zprávy na kartě Zpráva při zadávání. Při vstupu do buňky se text této zprávy objeví pod vyplňovanou buňkou (případně se zobrazí Pomocník Office).

Chybové hlášení

Pokud uživatel zadá údaj, který nesplňuje námi zadané ověřovací kritérium, kterou jsme pro ověření dat definovali, pak máme volbu ze čtyř následujících akcí.

Bez zadání (odškrtnutí položky Zobrazit chybové hlášení po zadání neplatných dat) - údaj, který nesplňuje ověřovací kritérium se zapíše do buňky a uživatel o tom nebude nijak informován.
Informace - při vyplnění údaje mimo námi definovaný rozsah se zobrazí dialogové okno. Po stisku Ano se údaj do buňky zapíše, po stisku Storno se nezapíše.
Varování - stisknutím tlačítka Ano se hodnota zapíše do buňky, při stisku Ne zůstane buňka v editačním režimu. Po stisku Storno zůstane v buňce původní údaj.
Stop - hodnotu mimo definované ověřovací kritérium do buňky nelze zapsat.

V případě, že zvolíme jako varovné hlášení Informaci, Varování nebo Stop a nezadáme žádný text nadpisu a chybového hlášení, pak Excel při chybném vyplnění zobrazí zprávu "Zadaná hodnota není platná. Určitý uživatel omezil hodnoty, které lze do buňky zadat.".

Příklad

Do tabulky chceme doplnit hodnocení zkoušky studentů. Hodnocení může nabývat pouze hodnot Výborně, Velmi dobře, Dobře, Nevyhověl, Nevyhověla. Do oblasti, kam se bude výsledek zkoušky zapisovat, připravíme ověření dat, kde jako ověřovací kritérium zvolíme Seznam. Jako zdroj dat zadáme Výborně;Velmi dobře;Dobře;Nevyhověl;Nevyhověla nebo se odkážeme na oblast buněk, kde máme hodnocení zapsané - např. =$H$1:$H$5 nebo =hodnoceni_zkousky (kde hodnoceni_zkousky je název oblasti $H$1:$H$5, kde jsou varianty hodnocení zapsány).

Při vstupu do buňky se pak napravo od buňky zobrazí šipka a po kliknutí seznam s možnými hodnotami vyplnění.

Úpravy zvoleného ověření dat

Pokud jsme nastavili nějakým způsobem ověření dat, zprávu při zadávání nebo chybové hlášení a později chceme toto editovat ve všech buňkách se stejným ověřením, pak máme několik možností:

Zvolíme jednu z buněk, ve které změníme parametry ověření na požadovaný stav a pak zaškrtneme políčko "Použít tyto změny u všech ostatních buněk se stejným nastavením".
Z nabídky Úpravy vybereme příkaz Přejít na... a pak zvolíme tlačítko Jinak, označíme přepínač Ověření dat a vybereme jestli chceme označit všechny buňky s ověřením nebo jen buňky se stejným ověřením. Poté již editujeme ověření dat pro všechny vybrané buňky.

Kopírování ověření dat

Ověřovací kritérium je jen jedním z atributů buňky a lze ho podobně jako formát či komentáře kopírovat separátně. Kopírovat ověření dat lze několika způsoby:

Jestliže nejsou v buňce žádné údaje, pak stačí kopírovat běžným způsobem celou buňku. Například klávesovou zkratkou CTRL+C
Kopírovat pouze ověření dat můžeme zkopírováním buňky s ověřením do schránky a poté označením cílové oblasti a vybráním příkazu Vložit jinak... z nabídky Úpravy, kde následně vybereme přepínač Ověření.
Jednoduchý způsob je i označení buněk, do kterých chceme ověření dat nakopírovat a alespoň jedné buňky, u které je již ověřovací kritérium nastaveno. Po zvolení příkazu Ověření dat dostaneme od programu zprávu "Výběr obsahuje některé buňky bez nastavení ověření dat. Chcete ověření dat na tyto buňky rozšířit?" potvrzením dojde ke zkopírování daného ověření.

Případy, kdy se ověření dat neuplatní nebo mohou být zapsána data nesplňující ověřovací kritérium:

Údaj byl do buňky zapsán před nastavením ověřovacího kritéria.
Údaj byl do buňky s ověřením zkopírován nebo přesunut tažením.
Zápis údaje do buňky byl proveden makrem.
Údaj byl do buňky vložen chybně, ale na kartě Chybové hlášení není zaškrtnuto "Zobrazit chybové hlášení po zadání neplatných dat" nebo je použit styl hlášení Varování nebo Informace.
Údaj v buňce je výsledkem funkce nebo vzorce.

Označení nežádoucích údajů

Vzhledem k tomu, že lze údaje zadat i tak, že se ověření dat neuplatní, případně lze toto ověření přeskočit, se nám může hodit možnost označení buněk s údaji, nesplňujícími ověřovací kritérium.

Zobrazíme panel nástrojů Závislosti vzorců.
Kliknutím na ikonu Zakroužkovat neplatná data se označí chybně zadané údaje (jestliže jsme při definici ověřovacího kritéria odškrtli Přeskakovat prázdné buňky, pak se zakroužkují i ty).
Po úpravě údaje na přípustnou hodnotu kroužek okolo buňky zmizí.
Odstranění zakroužkování neplatných dat je možné kliknutím na ikonu Vymazat kroužky ověření .

Při zadání nových údajů nesplňujících kritérium ověření se kroužky samy neobjeví - je potřeba znovu kliknout na ikonu Zakroužkovat neplatná data . Při uložení souboru se všechny kroužky vymažou. Současně může být v Excelu zakroužkováno maximálně 255 buněk.

Nahoru