Ověření dat - MS Excel 2010

Co je cílem tohoto článku

Cílem tohoto článku je seznamení s možností jak ověřovat zadávaná data do buněk Excelu. Při zadávání dat dochází poměrně často chybám (překlep, neplatný rozsah napříkald u data, vložení do jiné buňky). Pro omezení chybovosti je nanejvýš vhodné využít ověření dat.

Co se dozvíte

Pro přehlednost seznam kapitol:

• Proč ověřovat data v Excelu
• Data v buňce - co může obsahovat buňka
• Jak ověřit data v Excelu? (verze 2010 /2007)
• Ověření dat - Formulář - jak vše správně nastavit
• Ověření zda funguje - kontrola - funguje to co je nastaveno?
• Zakroužkovat - označit nesprávná data
• Ověření dat - problémy (zmizí) - problémy při otevírání sešitu v Excel 2001 a Excel 2007
• Vzorce pro ověření - pokročilé možností
• Vymazat ověření dat
• Označit buňky s ověřením dat
• Další navazující články - ověření dat -> Excel 97, 2003

Proč ověřovat data v Excelu

Tvoříte-li složitější aplikace, je vhodné kontrolovat, co uživatelé zadávají do buněk. Nespoléhejte, že uživatel si přečte návod (ten čte, až když něco nefunguje). Pokud neomezíte vhodně zadávání dat, budou Vám volat, že Excel nepočítá. Už neřeknou, že místo poplatku zadávali jméno.

Rozsáhlejší výpočty si nedokáži představit bez ověřování zadávaných dat do buňky. Příklad: V elektrotechnice jsou průřezy vodičů odstupňovány v nějaké řadě (není nic jednoduššího než zadávané průřezy omezit). Proto už při návrhu zpracovatelských programu v Excelu je vhodné s ověřením dat kalkulovat.

Je až s podivem, že tuto užitečnou a zajímavou funkci v Excelu téměř nikdo nevyužívá.

Zpět na seznam kapitol.

Data v buňce

Jelikož nám jde o vkládání hodnot a ověření správného zadání (pokud chci číslo, aby bylo zadáno číslo). Hodnoty mohu být například:

• Číslo - číselné hodnoty - kladné čísla, záporná čísla (celá, desetiná)
• Text - Jakýkoliv text, omezení jen délkou pro jednotlivou buňku.
• Datum - což je číslo - které má změněný formát
• Čas - opět se jedná o desetiné čísl, které má změněn formát
• Speciálnosti - maticové zápisy, komplexní čísla, hypertextový odkaz, PSČ, procenta, ....

Dále se naučíme jak zkontrolovat, aby nám někdo na do buňky která odpovídá na otázku

Dále může buňka obsavat (pro informaci):

• Vzorec (funkce) - v buňce se provede operace, nejen matematické, k dispozici jsou funce statistické, finanční, textové,... Místo správného výpočtu může být obsahem chybová hodnota.
• Formát (automaticky, vlastní, podmíněný) - například barva, ohraničení (obsahem buňky může být něco jiného než je zobrazeno například vzorec, datum čas, ...)
• Hypertextový odkaz - například klikatelnýá odkaz na web office.lasakovi.com :)
• Komentář - komentář (klasický žlutý lístek) můžete upravovat (přiřadit mu tvar barvu)
• Minigraf - od Verze Excel 2010 může být v buňce i graf (minigraf).

Zpět na seznam kapitol.

Jak ověřit data v Excelu?

Na kartě Data v sekci Datové nástroje tlačítko Ověření dat

Zvolíme Ověření dat...

Další možnosti Zakroužkovat neplatná data, Vymazat kroužky ověření proberu v dalším textu.

Zpět na seznam kapitol.

Ověření dat - dialogové okno

Zobrazí se vám následující okno skládající se ze 3 záložek

K dispozici jsou tři záložky

• Nastavení
• Zpráva při zadávání
• Chybové hlášení

Nastavení - Ověření dat

V první záložce Nastavení nastavujeme ověřovací kritéria

Ověřovací kritéria:

• Povolit
  • jakoukoli hodnotu - standartní nastavení buňky
  • celé číslo
  • desetinné číslo
  • seznam
  • datum
  • čas
  • délku textu
  • vlastní
• Rozsah
  • je mezi
  • menší
  • větší
  • rovno
  • ...
• Minimum - lze vložit i vzorec
• Maximum - lze vložit i vzorec
• Přeskakovat prázdné buňky - volba ANO/NE (checkbox)
• Použít tyto změny u všech ostatních buněk se stejným nastavením - volba ANO/NE (checkbox)

Poznámka: V případě volby seznamu je odkaz na buňky z nichž jsou položky seznamu čerpány.

Zpráva při zadávání - Ověření dat

Protože jsme profesionálové, je vhodné uživatele informovat co má do buňky napsat. Uživatel obdrží při kliku na buňku informační zprávu. Vyplnit zle:

• Nadpis
• Zprávu při zadávání:
• Zobrazit zprávu po výběru buňky - volba ANO/NE
• Vymazat vše - tlačítko - vymaže data.

Poznámka: Nevýhoda je že nelze tento text dynamicky měnit (ani u verze 2010). Možným řešením tohoto problému je využít VBA.

Chybové hlášení - Ověření dat

Toto je poslední záložka. Doporučuji vyplnit. Pokud uživatel neposlechne a místo požadované hodnoty zadá jinou bude taktně upozorněn dialogovým oknem.

V této záložce zle vyplnit:

• Styl
  • stop
  • varování
  • informace
• Nadpis
• Chybové hlášení
• Zobrazit chybové hlášení po zadání neplatných dat

Ukončení

Stačí klik na tlačítko OK a máme hotovo.

Zpět na seznam kapitol.

Ověření zda funguje - kontrola

Ověření funkčního ověřování dat, lze provést. Po kliku na buňku obdržíme informaci, co lze zadat do buňky.

Zadáte-li nesprávné data, obdržíte dialogové okno s chybovou hláškou.

Zpět na seznam kapitol.

Zakroužkovat - označit nesprávná data

Na kartě Data v sekci Datové nástroje tlačítko Ověření dat

Další možnosti:

• Zakroužkovat neplatná data
• Vymazat kroužky ověření

Pokud nastavíte pravidla na již vyplněné buňky, Excel v případě že buňka obsahuje nesprávná data, neoznámí chybu. Ověření dat funguje až pro nově zadávanou hodnotu. Budete-li chtít zkontrolovat, zda buňky neobsahují chybná data, stačí použít Zakroužkovat neplatná data a nesprávná data se zakroužkují.

Vymazat kroužky ověření - se kroužku zbavíte, aniž musíte data opravovat.

Zpět na seznam kapitol.

Ověření dat - problémy (zmizí)

Na základě dotazu v komentářích, jsem pátral po problému proč, když se vytvoří ověření dat ve verzi MS Excel 2010 (data pro ověření jsou na dalším listu). Následně dojde k otevření tohoto sešitu ve verzi Microsoft Excel 2007. Ověření dat zmizí (už jej nevidíte, je ztraceno) v této verzi jej neuvidíte (nedá se použít).

Poznámka: Ztracení ověření dat nastává, pokud se v ověření odkazujete na další listy!

Několik možných řešení:

Řešení 1

Pojmenovat data - definované názvy oblasti. (viz článek Jak na pojmenování dat v Excelu - definované názvy

Řešení 2:

Ověřovací data mít na stejném listu (pokud jsou data pro ověřovaní na stejném listu funguje jak má).

Řešení 3

Pořídit všem Excel 2010 (mnohdy nákup licencí přijde na méně než řešení následných problému s nekompatibilitou), nebo chvílí počkat a pořídit si Excel 2013 :).

Zpět na seznam kapitol.

Vzorce pro ověření - pokročilé možností

Do ověření dat lze umísťovat i vzorce, které Vám umožní ještě sofistikovanější kontrolu (například jedinečná data ve sloupci atd.). Postupně doplním, až budu řešit konkrétní případ.

Zpět na seznam kapitol.

Vymazat ověření dat

Vybrat buňku (buňky) ze které potřebujete ověření dat odstranit. Na kartě Data v sekci Datové nástroje tlačítko Ověření dat. V zobrazeném dialogovém okně klik na Vymazat vše. a pak jen klik na OK a buňka je bez ověření ;)

Označit buňky s ověřením dat

Obdržíte sešit a chcete zjistit, zda a kde jsou buňky na kterých je aplikované ověření dat. Bohužel Excel nenabízí podobnou možnost jako Správce názvů. Jednou z možností je využít Najít a vybrat. Jak tedy postupovat?

Na kartě Domů v sekci Úpravy klik na Najít a vybrata z menu zvolit Ověření dat.

Další navazující články

Chcete využít ověření dat v praxi doporučuji články:

• Ověření dat - Excel 97 (2003) - aneb jak na to ve starších verzích
• Propojené (závislé) seznamy - vybere se ovoce a nebo zelenina a na základě výběru lze teprve zvolit příslušné ovoce (jahodu, jablko), nebo zeleninu (mrkev, petržel)
• Dynamická oblast pro graf - vyberte rozsah a automaticky se vygeneruje příslušný graf
• Výběrový seznam bez opakování - položka ze seznamu lze vybrat pouze jednou
• Zobrazení zvoleného obrázku - vyberete název a zobrazí se příslušný obrázek

Zpět na seznam kapitol.

Závěrem

Pokud postrádate nějaké informace o ověření dat, můžete doplnit do komentářů.

Článek byl aktualizován: 19.09.2020 10:56

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od dubna roku 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím 8x za sebou). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 20 let (o Excel píší přes 25). Zdarma je zde přes 1.500 návodu, tipů a triků, včetně přes 350 různých šablon, sešitů a přes 70 taháků v pdf.