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.
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.
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.
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.
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í.
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.
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í.
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.
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
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 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.
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.
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.
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.
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.
Jedna odpověď
👍