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.