Ověření dat v Excelu – Jak na validaci vkládaných dat?

Představte si situaci, že máte v Excelu vkládat do tabulek pouze určité typy dat. Například čísla z určitého intervalu, datum z konkrétního rozsahu nebo například pouze údaje z předdefinovaného seznamu. Ke všem těmto scénářům lze v Excelu využít funkci Ověření dat, která umožní definovat pravidla vkládaných dat.

Je nutno však upozornit, že jde pouze o ručně vkládaná data, nevztahuje se výpočty ze vzorců!

Varovná zpráva při nepovolené hodnotě

Na začátku označíme oblast, kterou chceme „chránit“ před zápisem nepovolených hodnot. Poté v Excelu najdeme funkci Ověření dat, pomocí které definujeme pravidla. Nejlépe to ukáže obrázek s příkladem.

Zadání k ověření dat

Máme 3 sloupce – do prvního povolíme pouze celá kladná čísla, v druhém se může vyskytovat datum pouze z let 2005 až 2015 a ve třetím povolíme pouze hodnoty ze seznamu.

Označíme první sloupec. V prvním případě v dialogovém okně zvolíme ze seznamu kritérium Celé číslo a poté Rozsah na je větší nebo rovno a napíšeme 0. Tím nastavíme, že bude možno zapsat pouze kladná celá čísla ≥ 0.

Zadání kritérií pro ověření

Pokud zapíšeme zápornou hodnotu, Excel zobrazí varovnou zprávu. Tu můžeme sami přizpůsobit. Je možné nastavit zprávu při zadávání (bublinu u buňky) a chybové hlášení při pokusu zadání jiné, než povolené hodnoty. Například takto:

Bublina a zpráva ověření vstupu dat

Není nutno nastavit ani bublinu, ani zprávu a Excel nebude nijak varovat, čímž však funkce poněkud pozbývá účelu.

Obdobná situace bude u druhé případu s datem. Místo kritéria celých čísel však zvolíme v dialogovém okně Datum a zadáme rozsah, viz obrázek.

Ověření dat

Můžeme nastavit i omezení na datum

A opět lze nastavit chybové hlášení. U chybových hlášení máme 3 možnosti:

  • Informace – pouze zobrazí informaci při zadání neplatné hodnoty

  • Varování – varování, zda opravdu chceme zapsat neplatnou hodnotu

  • Stop – nepovolí zapsat neplatnou hodnotu vůbec

Poslední možnost zobrazí pouze dialog bez možnosti zadání neplatné hodnoty.

Lze zadat pouze datum od roku 2005

Ve třetím případě povolíme pouze hodnoty z určitého seznamu. V našem případě označíme sloupec C a v dialogu Ověření dat zadáme kritérium Seznam. Excel vyzve k označení oblasti, v níž jsou povolené hodnoty, v tomto případě se nacházejí v oblasti I2:I7.

Jako kritérium ověření lze zadat seznam, tedy oblast buněk

Bude možno zadat pouze hodnoty ze seznamu.

Zakroužkování neplatných dat

Funkce má ještě jednu možnost. Pokud při chybovém hlášení zvolíme Informace nebo Varování, bude možno přesto neplatnou hodnotu zadat. Je však možnost neplatné hodnoty červeně zakroužkovat, viz na obrázku.

I když povolíme zadání neplatných hodnot, můžeme je nechat zakroužkovat

Tudíž hodnoty, které neodpovídají seznamu, budou označeny jako chybné a na první pohled viditelné, podobně jako například při použití podmíněného formátování.

Ověření dat nejen proti zbrklosti

Ověření dat je praktickou funkcí pro omezení vkládaných dat. Nejen proti překlepům a zbrklosti, ale může mít význam pro zajištění správnosti vkládaných dat, která potřebujeme a proti zadání nesmyslných hodnot, které třeba poté dále exportujeme do databáze či s nimi jinak pracujeme.

Reagovat na příspěvek