Ověřování dat

Ověřování dat je užitečný nástroj Excelu. Umožňuje kontrolovat údaje zapisované z klávesnice do buněk takovým způsobem, aby zadaná hodnota vyhovovala předem zadané podmínce nebo pravidlu. Pomocí ověření můžete například zajistit, aby do určeného sloupce buněk bylo možno zapisovat pouze:

• kladná čísla nebo nuly
• celočíselné hodnoty v zadaném rozmezí
• datumové hodnoty z posledního čtvrtletí letošního roku
• předem určené texty, jako jsou pobočky nebo názvy oddělení ve firmě
• datumové hodnoty, které jsou v porovnání s datumy ve vedlejším sloupci větší aspoň o určitý počet dnů
• atd.

Příklad:

Přejeme si do buněk zadávat například pouze kladné peněžní částky. Postupujte takto:

• Označte buňku nebo oblast buněk.
• Na kartě Data klepněte na tlačítko Ověření dat.
• Na prvé kartě Nastavení použijte seznam Povolit a vyberte volbu Desetinné číslo.
• V seznamu Rozsah vyberte volbu je větší než a do políčka Minimum zapište nulu.
• Na kartě Zpráva při zadávání vyplňte nadpis a text zprávy. Tato zpráva se zobrazí při výběru buňky s nastaveným ověřením.

• Na kartě Chybové hlášení vyplňte políčka Nadpis a Chybové hlášení.
• Potvrďte tlačítkem OK.

Při pokusu o zápis záporného čísla, nuly nebo textové hodnoty se zobrazí chybové hlášení se dvěma možnostmi:

• Klepnutím na tlačítko Opakovat pokračujete v zápisu do buňky a můžete údaj opravit.
• Tlačítko Storno zápis stornuje a v buňce zůstane původní údaj.

Ověření buněk se však uplatní pouze při zápisu údaje z klávesnice, nikoliv při kopírování nebo plnění buněk.

Údaje na kartách Zpráva při zadávání a Chybové hlášení vyplnit nemusíte. Při zadání chybného údaje se pak zobrazí univerzální zpráva o chybě.

Ověřování číselných a datumových údajů

Pro kontrolu číselných a datumových údajů zvolíte v seznamu Povolit tyto volby:

• Desetinné číslo (umožňuje zápis celého nebo desetinného čísla),
• Celé číslo (umožňuje pouze zápis celočíselné hodnoty),
• Datum (umožňuje zápis datumové hodnoty).

Pro kontrolu zadávaných údajů máte v seznamu Rozsah tyto možnosti:

• je mezi (výchozí hodnota),
• není mezi,
• je rovno,
• není rovno,
• je větší než,
• je menší než,
• je větší nebo rovno,
• je menší nebo rovno.

Podle zvoleného rozsahu se na kartě Nastavení zobrazí jedno nebo dvě textová políčka, do kterých zapíšete potřebný rozsah hodnot nebo hodnotu pro porovnání.

Ověřování textových údajů

Pro zápis textových hodnot nabízí ověření dat dvě možnosti: kontrolu délky zadávaného textu nebo kontrolu předem vytvořeným seznamem.

Při kontrole délky textu zvolte v seznamu Povolit volbu Délka textu a nastavte potřebný rozsah této délky stejným způsobem, jako u číselných hodnot.

Kontrolu údajů podle seznamu nastavíte takto:

• Do oblasti buněk pod sebou nebo vedle zapište texty voleb. Oblast může být na jiném listu, než jsou buňky s ověřením.
• Na kartě Data klepněte na tlačítko Ověření dat.
• Na prvé kartě Nastavení použijte seznam Povolit a vyberte volbu Seznam.
• Klepněte do políčka Zdroj a vyznačte rozsah buněk se seznamem.
• Podle potřeby vyplňte údaje na kartách Zpráva při zadávání a Chybové hlášení a potvrďte tlačítkem OK.

Při výběru takto nastavené buňky se napravo od buňky zobrazí šipka. Klepnutím na šipku zobrazíte jednotlivé volby a výběr některé z voleb provedete klepnutím myší. Do buňky můžete zapisovat také z klávesnice. V tomto případě musí zapsaný text souhlasit s některou položkou ze seznamu a při kontrole se nerozlišují malá a velká písmena.

Nastavení typu kontroly

Na kartě Chybové hlášení můžete také nastavit způsob, jakým bude Excel reagovat při zadávání nepovolených údajů. To provedete v seznamu Styl. V seznamu je jako výchozí nastavena volba Stop, která neumožňuje za žádných okolností nepovolený údaj do buňky zadat.

V seznamu styl můžete nastavit ještě další dvě volby, které umožní zadat do buňky i nepovolené údaje a zobrazí upozornění.

Volba Varování vyvolá okno se třemi tlačítky:

• tlačítko Ano znamená uložení údaje,
• tlačítko Ne znamená pokračování v zápisu do buňky s možností údaj opravit,
• tlačítko Storno ponechá v buňce původní údaj.

Volba Informace vyvolá okno se dvěma tlačítky:

• tlačítko OK znamená uložení údaje,
• tlačítko Storno ponechá v buňce původní údaj.

Ověření pomocí vzorce

Hodnoty zadávané do buněk můžete také kontrolovat podle obsahu jiné buňky, která zpravidla obsahuje vzorec, využívající kontrolovanou buňku. Postupujete tak, že v dialogovém okně pro ověření vytvoříte vzorec, který má charakter podmínky a vrací hodnotu PRAVDA nebo NEPRAVDA. Pokud je vrácena hodnota PRAVDA, údaje v buňkách se považují za správné, u hodnoty NEPRAVDA za nesprávné.

Např. chcete vytvořit ověření částek ve sloupci B. Ve sloupci C je zapsáno procento srážky, ve sloupci D vypočtená srážka, která nesmí překročit hodnotu 100. Postupujete takto:

• Označte buňky ve sloupci B, kde se má ověření vytvořit.
• Zobrazte si dialogové okno pro tvorbu ověření.
• V seznamu Povolit nastavte volbu Vlastní.
• Klepněte do políčka Vzorec.
• Do políčka zapište rovnítko, klepněte na první buňku se vzorcem ve sloupci D a připíšete „<1000“.
• Vyplňte údaje na kartách Zpráva při zadávání a Chybové hlášení.
• Klepněte na tlačítko OK.

Jako druhý příklad si můžeme uvést porovnání dvou buněk mezi sebou. Ve sloupci B (od třetího řádku) jsou uvedeny datumy fakturace a ve sloupci C datum splatnosti. Minimální lhůta pro splatnost faktury je deset dnů. U tohoto označíte buňky ve sloupci C, v seznamu Povolit nastavíte opět volbu Vlastní a v řádku Vzorec vytvoříte výraz „=C3>B3+9“.

Poněkud odlišný postup musíte použít v případě, kdy se ověření u celé oblasti vztahuje k jediné buňce se vzorcem. Typickým příkladem je oblast několika buněk (např. B2:B10), jejichž součet nesmí přesáhnout určenou hodnotu. Při tvorbě ověření musíte v políčku Vzorec ještě klepnout do odkazu na buňku s vytvořeným součtem s pomocí klávesy F4 upravit odkaz na absolutní (např. =$B$14<10000). Tento krok zajistí, že se ověření všech buněk v oblasti vztáhne na jedinou společnou buňku, a bude tedy fungovat u všech buněk stejně.

Úprava, odstranění a kopírování ověření

Vytvořené ověření dat odstraníte takto:

• Označte potřebné buňky.
• Zobrazte si dialogové okno pro ověření dat.
• Klepněte na tlačítko Vymazat vše.
• Potvrďte tlačítkem OK.

Při úpravě již vytvořeného otevření dat je někdy problém provést úpravu u všech buněk, kde bylo toto ověření použito. Proto zvolte některou buňku s upravovaným ověřením, v dialogovém okně pro tvorbu ověření označte volbu Použít tyto změny u všech ostatních buněk se stejným nastavením, proveďte potřebné úpravy a potvrďte tlačítkem OK. Provedené změny se přenesou do všech buněk se stejným ověřením. Tento přenos však funguje pouze v rámci jednoho listu.

Při kopírování buněk se přenesou všechny údaje včetně ověření. Platí to však i naopak: při kopírování údajů z neověřované buňky do buňky s nastaveným ověřením se ověření se vymaže a vkládaná hodnota se proto nekontroluje. Totéž platí i pro plnění buněk.

Jestliže použijete příkaz Vložit jinak a do buňky s ověřením vložíte pouze hodnotu, ověření zůstane, ale kontrola vkládané hodnoty se neprovede.