Ověření dat v Excelu | Data validation v Excelu

V dnešním videu se podíváme na praktické využití excelového nástroje Ověření dat. Většina z vás využívá tento nástroj pro tvorbu rozbalovacího seznamu v buňce, nicméně ověření dat skrývá daleko více praktických využití než jen pouhou tvorbu rozbalovacího seznamu. Ověření dat je excelový nástroj, který omezuje nebo ověřuje data. Prakticky tak pomocí Ověření dat vytvoříte pravidlo, jaké hodnoty mohou být do excelových buněk zadány a pouze pokud hodnota splní tyto parametry, tak ji budete moc do buňky vepsat. Pokud kritéria splněná nebudou, Excel nedovolí hodnotu zapsat a na chybu vás upozorní.

Excelový soubor ke stažení

Ověření dat v Excelu | Data validation v Excelu

Excelový nástroj Ověření dat je skvělým pomocníkem pro všechny, kteří si chtějí ukrátit čas kontroly v Excelu. Představte si, že někomu posíláte Excel k vyplnění. Může se jednat o vyplnění docházky, prodejních dat, cen, kódů, vlastně čehokoliv. Potřebujete, aby vám dotyční lidé vyplnili excelový soubor nejen správnými daty, ale i ve správném formátu. V opačném případě jinak strávíte hodně času opravami a čištěním vyplněných dat. Instrukce pro vyplnění sice můžete napsat do poznámek a komentářů, ale buďme realisti, lidé je většinou nečtou nebo se je mohou rozhodnout ignorovat.  A právě Ověření dat vám s tímto úkolem pomůže.

Dnešní video má i bonusové pokračování s praktickými příklady, které najdete na našem webu Akademie Excelu. V tomto bonusovém videu si ukážeme, jak zajistit, že se nepřekročí celkové součty, že se vyplní kódy ve správném formátu nebo že vám do buněk někde nevyplní nadbytečné mezery nebo třeba víkendové datumy. Členství na webu Akademie Excelu je samozřejmě zdarma a můžete ho kdykoliv zrušit. 

Teď si ale už pojďme ukázat, jak ověření dat v Excelu funguje na praktických příkladech.

Nástroj Ověření dat se nachází na liště Data, pod záložkou Ověření dat. 

Ověření dat v Excelu 1
Obrázek č.1 Ověření dat v Excelu

Na liště Data vybereme Ověření dat. Rozklikneme možnosti v okně Povolit a podíváme se na to, jaké možnosti nám nástroj Ověření dat nabízí. Máme zde na výběr celé číslo, desetinné číslo, seznam, což nám dovoluje vytvořit rozbalovací seznam v buňce. Dále zde máme možnost ověřit data u datumu a času a nebo dle délky textu. Poslední možnost Vlastní dovoluje ověřit a omezovat data pomocí funkcí, což si ukazujeme v bonusovém navazujícím videu.

Ověření dat v Excelu 2
Obrázek č.2 Možnosti Ověření dat v Excelu

Celé číslo

Řekněme, že potřebujeme zajistit, aby se do následující buňky vložily pouze hodnoty v rozmezí od 1 do 20. Klikneme do buňky, na kterou chceme uplatnit toto omezení, a na kartě Data vybereme Ověření dat a v rozbalovacím seznamu Povolit vybereme celé číslo. Rozbalí se nám další možnosti, jak můžeme ověřit data.

V okně Rozsah můžeme vybrat, jaká celá čísla dovolíme vyplnit. Můžeme si vybrat celá čísla v rozsahu od do, čísla, která se rovnají nebo naopak nerovnají jinému číslu, nebo například celá čísla, která jsou větší nebo menší než jiný parametr. V našem příkladu jsme chtěli omezit vstupy na celá čísla mezi hodnotami 1 a 20. Vybereme tedy možnost je mezi a jako minimální hodnotu vybereme číslo 1 a jako maximální hodnotu číslo 20. Výběr potvrdíme. Teď zkusíme do buňky napsat číslici mezi 1 a 20. Nic se nestalo, jelikož jsme vyplnili číslici ve správném rozsahu. Pokud bychom ale číslici přepsali, řekněme na hodnotu 25, tak na nás v Excelu vyskočí chybová hláška, že zadaná hodnota neodpovídá povolenému rozsahu a nedovolí nám hodnotu vepsat.  

Ověření dat v Excelu 3
Obrázek č.3 Ověření dat - Celé číslo

Desetinné číslo

V podstatě stejné možnosti nabízí i varianta s desetinnými čísly. V další buňce máme ověřit, že do buňky budou zapsaná jakákoliv čísla, mimo interval 0,05-5,55. Vybereme tedy Ověření dat a tentokrát musíme vybrat možnost Desetinná čísla. V rozsahu vybereme možnost není mezi. Do buňky minimum napíšeme hodnotu 0,05 a do buňky maximum hodnotu 5,55. Potvrdíme a zkusíme do buňky vepsat hodnotu, která je v nedovoleném intervalu, například 3,5. Potvrdíme a Excel nás opět upozorní, že hodnota je nesprávná. Zkusíme hodnotu mimo zadaný interval a vše je v pořádku.

Obrázek č.4 Ověření dat - Desetinné číslo

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

Aby jste tomu, kdo bude Excel vyplňovat trochu ulehčili práci, tak můžete v Ověření dat nastavit i zprávu při zadávání. Ne vždy totiž můžete jasné zadání pro obsah buňky napsat do buňky tak, jako to máme zde. Zprávou při zadávání vytvoříte jasné instrukce, kterých se mají ostatní držet. Pro vytvoření zprávy klikneme do buňky, kde máme nastavené pravidlo a vrátíme se do Ověření dat. Překlikneme se z Nastavení na Zprávu při zadávání. Zde máte možnost vyplnit nadpis a zprávu při vyplňování. Napíšeme tedy jako nadpis Instrukce pro vyplnění a jako zprávu napíšeme třeba, Prosím vyplňte hodnoty mezi 1 až 20. Zprávu potvrdíme. Když teď klikneme do buňky, tak se zobrazí zpráva s instrukcemi pro vyplnění.

Ověření dat v Excelu 5
Obrázek č.5 Ověření dat - Zpráva při zadávání

Chybové hlášení

Upravit můžete i chybové hlášení, které se zobrazí po zadání nedovolených hodnot. Opět označíme nejprve buňku s pravidlem a vrátíme se ještě jednou k Ověření dat. Přepneme se do posledního pole Chybové hlášení. Zde máte na výběr několik trochu odlišných možností. Jednotlivé ikony mají svůj význam a trochu se od sebe liší.

Stop ikona

Stop ikona nedovolí zadat jinou hodnotu, než kterou jste specifikovali v ověření dat. Vybereme nejprve stop ikonu a vyplníme nejprve nadpis jako Chyba a jako chybové hlášení napíšeme Prosím zadejte pouze hodnoty dle pokynů. Potvrdíme a když se do buňky pokusíme zadat jinou než povolenou hodnotu, tak na nás vyskočí tato hláška a Excel nám nedovolí jinou než povolenou hodnotu zadat.

Ověření dat v Excelu 6
Obrázek č.6 Ověření dat - Chybové hlášení Stop ikona

Varování ikona

Vrátíme se k nastavení chybové hlášky a podíváme se na ostatní ikony. Kromě ikony stop můžeme vybrat i ikonu Varování. Vybereme tedy Varování a hlášku necháme stejnou. Potvrdíme. Rozdíl oproti ikoně stop je v tom, že toto je pouze varování. Pokud zadáme hodnotu mimo povolené rozpětí, tak nás Excel varuje, ale dovolí nám hodnotu vložit. Pokud tedy vybereme Ano u chceme pokračovat, tak nás nechá zadat hodnotu i mimo povolení rozpětí.  

Ověření dat v Excelu 7
Obrázek č.7 Ověření dat - Chybové hlášení Varování ikona

Informace ikona

Poslední možností v Ověření dat u chybových hlášek je ikona Informace. Vybereme Informaci a hlášení necháme stejné. Potvrdíme a když zadáme hodnotu mimo povolený rozsah, tak na nás vyskočí okno s informací, pokud ale potvrdíme, tak nás Excel nechá hodnotu mimo povolený rozsah zadat.

Ověření dat v Excelu 8
Obrázek č.8 Ověření dat - Chybové hlášení Informace ikona

Rozbalovací seznam

Do další buňky máme vložit rozbalovací seznam, ve kterém bude na výběr ze dvou možností, buď ano nebo ne. Seznam možností Ano a ne máme vedle v buňkách, takže klikneme do buňky, do které chceme rozbalovací seznam vložit a vybereme Ověření dat a v možnostech vybereme Seznam. Jako zdroj označíme tyto dvě možnosti a výběr potvrdíme. Do buňky se tím vložil rozbalovací seznam, ve kterém tak máme na výběr pouze ze dvou možností. O rozbalovacích seznamech máme na Akademii Excelu několik videí:

Rozbalovací seznam pro začátečníky

Jak vytvořit vyhledávací rozbalovací seznam

Jak v Excelu vytvořit závislé rozbalovací seznamy

Ověření dat v Excelu 9
Obrázek č.9 Ověření dat - Rozbalovací seznam

Datum

V další buňce potřebujeme, aby se do buňky vyplňovaly pouze datumy, které jsou po 1.10.2022. Mohli bychom samozřejmě použít komentář nebo poznámku u záhlaví sloupce, ale nemáme jistotu, že si lidé instrukce přečtou. Takže použijeme Ověření dat. Označíme buňku a na kartě Data vybereme Ověření dat. Zde vybereme možnost Datum a v seznamu vybereme je větší než. Jako počáteční datum vybereme datum, které jsme si určili, v našem příkladu to bylo 1.10.2022. Potvrdíme. Zkusíme napsat jakékoliv datum po 1.10 a vše je v pořádku, když ale vyplníme datum 1.10 , tak na nás vyskočí chyba. A je to proto, jelikož jsme vybrali možnost větší než, v této možnosti se počáteční datum nezahrnulo do výběru. Pokud byste chtěli výběr omezit na větší než včetně počátečního datumu, tak se musíte k Ověření dat vrátit a vybrat možnost větší něž nebo rovno. Potvrdíme a teď už nás Excel nechá zadat i datum 1.10.2022.

Ověření dat v Excelu 10
Obrázek č.10 Ověření dat - Datum

Ověření dat snese i použití funkcí. V další buňce chceme omezit, že data která budou vložená budou vždy mezi hodnotou 1.1.2022 a dnešním datumem. Dnešní datum se ovšem dynamicky mění, a potřebujeme, aby se vždy jednalo o současné datum, podle toho, kdy se Excel otevře. Klikneme do buňky a na kartě Data vybereme Ověření dat. Zde vybereme možnost datum a jako rozsah vybereme je mezi. Jako počáteční datum vyplníme 1.1.2022 a jako koncové datum použijeme datumovou funkci DNES, která vždy vrátí aktuální dnešní datum, podle otevření Excelu. Potvrdíme a zkusíme do buňky zadat hodnoty ve správném intervalu, vše v pořádku. Pro zkoušku zkusíme zadat zítřejší datum a vrátila se chyba.

Ověření dat v Excelu 11
Obrázek č.11 Ověření dat - Datum

Délka textu

Ověření dat můžete využít i k tomu, že ověříte, zda máte správně vyplněná například kódová označení produktů. Řekněme, že do sloupce potřebujeme doplnit kódová označení produktu, kdy správné označení produktu má pouze 4 znaky. Označíme celý sloupec, kam chceme hodnoty doplňovat a na kartě Data vybereme Ověření dat. V možnostech tentokrát vybereme Délka textu, kde jako rozsah vybereme je rovno. Řekli jsme, že správná délka kódu má vždy 4 znaky, takže jako délku vyplníme číslici 4. Potvrdíme a zkusíme do sloupce vyplnit nějaké čtyřmístné kódy. Jakmile ale zadáme například pěti místní kód, tak už na nás vyskočí chybová hláška.

Ověření dat v Excelu 12
Obrázek č.12 Ověření dat - Délka textu

Zakroužkovat neplatná data

A co když už máte data vyplněná a chcete si zpětně ověřit, zda máte data ve správném formátu? Pokud už máte ve sloupci data zadaná, můžete pro ověření dat použít Zakroužkovat neplatná data. Řekněme, že máte sloupec s kódy produktů, kdy správný formát jsou kódy o délce čtyř znaků. Toto ověření jsme ovšem bohužel dopředu nenastavili. Chceme ale ověřit, které kódy naše pravidlo nesplňují. Označíme nejprve celý sloupec hodnot a musíme pravidlo nejprve nastavit.

Obrázek č.13 Ověření dat - Zakroužkovat neplatná data

Na kartě Data vybereme Ověření dat a potřebujeme ověřit délku textu. Správný kód má vždy 4 znaky, takže vybereme je rovno a napíšeme číslici 4 a potvrdíme. A nic se nestalo. Abychom odhalili chybně zadané hodnoty, tak musím v záložce Ověření dat vybrat Zakroužkovat neplatná data. Tím se prověří každá předchozí hodnota proti zadanému pravidlu a zakroužkuje se ta, která nesplňuje zadané kritérium. Pomocí zakroužkovat neplatná data, tak můžete ověřit správnou jakéhokoliv pravidla, které jste zadali pomocí Ověření dat.

Obrázek č.14 Ověření dat - Zakroužkovat neplatná data

Když jste s kontrolou hotovi, tak tyto kroužky můžete zase smazat pomocí Vymazat kroužky ověření.

Ověření dat skýtá neuvěřitelné možnosti, jak ověřit vkládaná data, což vám ušetří spoustu času a čištění špinavých dat. Nicméně největší síla Ověření dat spočívá v možnosti nastavit si vlastní kritéria a nespoléhat se na pouze omezenou nabídku předdefinovaných možností. O tom, jak pracovat s vlastními kritérii v Ověření dat máme na webu bonusové video pro členy Akademie Excelu – Ověření dat v Excelu pomocí vlastních funkcí. Staňte se členem Akademie Excelu a získejte přístup k bonusovým videím určeným pouze pro členy. Odkaz na video najdete v popisku tohoto videa. A já se zatím budu těšit u dalšího videa.

MOHLO BY VÁS ZAJÍMAT

Dynamický prodejní report v Excelu | Microsoft 365

V dnešním videu se podíváme na to, jak pomocí dynamických funkcí v Excelu vytvořit plně dynamický prodejní report. Dynamické funkce, které budeme dnes pro vytvoření prodejního reportu

Začínáme s Power BI | Excel a Power BI

V dnešním videu se podíváme na základy práce v Power BI. Power BI je nástroj od společnosti Microsoft, který slouží k interaktivní vizualizaci a analýze dat. Specifikem Power

Jedna odpověď

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *