Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » data » overeni-dat-ms-excel-2010
Microsoft Excel logo

Ověření dat - MS Excel 2010

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak v Microsoft Excelu ověřit zadávaná data a nepovolit zadání jiných (neplatných)? Doporučení jak ukládat odkazy (Excel 2007 - Excel 2010)

Doplněno 2.12.2015

Co je cílem tohoto článku

Excel - Ověření dat

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

MS Excel 2010 - Logo

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á.

Zamykání buněk - které s ověření souvisí jsem sepsal v článku Zamykání buněk - Excel 2010.


Zpět na seznam kapitol.

Data v buňce


Imigration officer: "Sex?"
MAN:"Yes, every day."
Imigration officer:"No, no - I mean Male or female?
MAN:"Male, female, sometimes camel."

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).

Podrobněji v samostatné článku Co mohou buňky obsahovat.


Zpět na seznam kapitol.

Jak ověřit data v Excelu?

Jednoduše - Ověření dat

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

MS Excel 2010 - Karta data - ověření dat

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

MS Excel 2010 - Karta data - 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

MS Excel 2010 - ověření dat - formulář

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

MS Excel 2010 - ověření dat - formulář

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

MS Excel 2010 - ověření dat - formulář

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.

MS Excel 2010 - ověření dat - formulář

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.

MS Excel 2010 - ověření dat - formulář

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

MS Excel 2010 - ověření dat - chybova hlaska

Zpět na seznam kapitol.

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

Neplést s volbami...

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

MS Excel 2010 - Karta data - 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í.

MS Excel 2010 - neplatna data

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í)

Problémy s "mizením" ověření dat při souběžné práci v Excel 2007 a 2010. Pokud je ověření dat na dalším listu a používáte tabulku ve verzi Microsoft Excel 2010 a Microsoft Excel 2007.

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 :).

Doporučení: Odkazy z jiných listů vkládat jako pojmenovanou oblast.

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í ;)

MS Excel 2010 - ověření dat - formulář

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

Excel - ověření dat - které jsou označeny

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:

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

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). 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 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře


Bulva

Přidáno: 28.04.12 17:26

Super článek.

Pavel Lasák

Přidáno: 22.10.12 21:13

To Bulva: Jsem rád že pomohl.

radek

Přidáno: 16.07.13 12:06

vývojářům v microsoftu bych utrhl koule i s kořenem. Nejen ty jejich podělaný win8, ale už i excel? Díky Vám hoši!! Díky!! Jen tak dál

Hektor

Přidáno: 11.02.14 22:20

Super, aktuálně jsem daný problém řešil, stačí juknout na tyto stránky a je vyřešeno. Díky

Pavel Lasák

Přidáno: 12.02.14 17:42

To Hektor: Gratulace. Jsem rád že stránky pomáhají.

Martin

Přidáno: 27.03.14 09:10

neumím si poradit s tímto problémem: Mám tabulku sotrimentu, kde je skupina sortimentu a konkrétní sortiment (např. šrouby, M20; matice, M10 atd) Na jiném listu jsem si udělal pole s ověřením dat dle seznamu - skupina sortimentu, kde vyberu skupinu např. šrouby. Dá se vytvořit pomocí ověření dat seznam ukazující z první tabulky jenom šrouby (tedy sortiment, který má tu skupinu kterou jsem zvolil)?

Petr

Přidáno: 09.07.14 18:35

Dobrý den, zkusím se zeptat, zda lze nějakým způsobem použít ověření dat se 3mi podmínkami. Popis: na jedné buňce chci ověřovat datum podmínkou je, že zadané datum musí být v rozmezí aktuálního roku (to bych měl vyřešeno) ale potřeboval bych doplnit podmínku, aby zadávané datum nesmělo být větší než dnešní datum. Pro upřesnění když den kdy zadávám hodnoty je 1.7.2014, tak můžu zadat datum v rozmezí od 1.1.2014 do 1.7.2014, ale když budu zadávat hodnoty 5.1.2015, tak rozmezí bude 1.1.2014 do 31.12.2014. Předem děkuji za případnou radu. Radu uvítám i na mém emailu petr.jouza@outlook.com Přeji hezký den. Petr Jouza

Petr

Přidáno: 09.07.14 18:45

Tak jsem kapku zapřemýšlel a již jsem to vymyslel. Použil jsem vedlejší buňky s funkcemi DNES a podmíněnou funkci když větší nebo menší. :)

Milan Prucek

Přidáno: 26.08.14 17:51

Dobrý den , nevím si rady s vytvořenou tabulkou kde jsou různé vzorce. při přeposlání a otevření sešitu v jiném pc , zadám kupříkladu dvě hodnoty do různých buněk a v další buňce je vzorec se součtem, kde se tyto dvě hodnoty mají sečíst,ale tak se nestane. Vzorec je v pořádku se všemi náležitostmi,nEvím co s tím , neznáte náhodou tentoproblém ? Děkuji prucek@live.com

Pavel Lasák

Přidáno: 17.11.14 13:16

To Milan Prucek: Nezadávate jinak desetiné číslo, místo s čárkou tak tečkou. V jiných jazykových verzích Excel může být místo čárky tečka.

Lukáš

Přidáno: 19.11.14 10:16

Dobrý deň, dá sa vytvoriť niečo takého typu, že mám jeden list, kde mám nejakú databázu materialov s vlastnostami, napriklad v riadku mam hutny beton, ktory ma objemovu hmotnost a pevnost, potom mam iny hutny beton s inou objemovou hmotnostou a inou pevnostou, teraz by som chcel na inom liste vytvorit tabulku kde bude urobený zoznam betonov cez overenie udajov a to tak ze ked vyberiem dany beton, ostatne tak sa mi do tabulky prenesú aj ostatné udaje z predchadzajucej databazy.

Jojo

Přidáno: 21.11.14 09:04

To Lukáš: -- ak bude databáza na Liste1 v stĺpcoch A,B,C, tak keď sa na List2 zadá do A2 hľadaný betón, tak do B2 zadať: -- =SVYHLEDAT(A2;List1!A:C;2;NEPRAVDA) -- a do C2 zadať: -- =SVYHLEDAT(A2;List1!A:C;3;NEPRAVDA) -- (POZOR názov betónu sa smie v databáze vyskytovať iba 1x. Pri rovnakom názve treba pridať napr.poradové číslo alebo ďalšie slovo)

Lukáš

Přidáno: 26.11.14 10:47

Ďakujem veľmi pekne za radu, a mám ešte jednu otázku, dúfam, že to dobre opíšem :). Chcel by som mať bunku, v ktorej bude zoznam kategórií materiálov, a to tak, že keď si vyberiem danú kategóriu, tak mi vo vedľajšej bunke vytvorí zoznam s materiálmi, ktoré sú v danej kategórií.

Jojo

Přidáno: 01.12.14 09:04

To Lukáš: -- Použiť filter, kontingenčnú tabuľku alebo makro

Václav

Přidáno: 03.12.14 19:21

Ahoj, mohu se zeptat, jak v kartě "Nastavení" odkázat na ověřovací seznam, který si připravím do vlastního UserFormu? (ten může být obecně např. vícesloupcový, s hlavičkou, dynamicky filtrovaný...). Za nápady předem díky.

Stoupa

Přidáno: 29.01.15 19:46

Tak jsem tady na webu zase objevil popis/řešení mého problému :) Po zavření a otevření souboru mi mizelo v Office2010 Ověření. Koukal jsem na to jak "puk". Samozřejmě, jak jinak ... mám tam vzorec na jiný list. Tedy velké díky za "nasměrování" ... :)

Petr

Přidáno: 05.03.15 15:35

Dobrý den, prosím o radu zda lze a případně jak nastavit ověření dat, kdy uživatel může zadat do buňky jakýkoliv text složený z omezené množiny znaků. Tedy je akceptován jakýkoliv text, který má 8 znaků, kdy text může obsahovat pouze znaky 0-9 v libovolném pořadí, znaky se mohou libovolně opakovat. Př 00000000 je v pořádku, 12359876 je v pořádku, 0102at35 není povoleno. Moc díky

Petrs

Přidáno: 09.04.15 11:28

Zůstal bych u povolené číselné řady celých čísel 0 - 99999999. Do formátu buňky lze zadat 0 před číselnou řadu nižší než 100000000. tzn. 123 se bude zobrazovat jako 00000123.

Jirka

Přidáno: 06.05.15 17:31

Dobrý den, snažím se zadat do ověření dat podmínku, aby do buňky šlo zapsat pouze tehdy, budou li předchozí dvě buňky na stejném řádku prázdné. Např. pro buňku D1 zadám do ověřovacího kritéria =A(B1="";C1="")=PRAVDA Takto zadané kritérium ale nefunguje, přestože jako vzorec je to funkční. Díky za radu.

Karel

Přidáno: 20.07.15 16:35

Super, už po několikáte jsem řešení našel na těchto stránkách. Stejně jako teď s nefunkčním ověřováním.

PavelO

Přidáno: 08.09.15 14:12

Dobrý den, hledám způsob, jak nalézt buňky s nastaveným ověřením dat prostřednictvím vzorce, který obsahuje odkaz na jinou konkrétní buňku/oblast buněk. Jinými slovy potřebuji podobnou funkci, jako je závislost vzorců - "následníci", díky níž lze u konkrétní buňky zjistit, jestli na jejím výsledku závisí jiná buňka/buňky. Tato analýza závislostí totiž nezahrnuje vzorce použité v ověření dat u buněk. Děkuji za případný návod.

Pavel Lasák: Doplněno do článku. I když tak sofistikované řešení jako závislost vzorců to bohužel není (do Excel 2013).

O. Krystof

Přidáno: 14.10.15 15:23

Dobrý den, hledám řešení na následující: Případy, kdy se ověření dat neuplatní nebo mohou být zapsána data nesplňující ověřovací kritérium: - Údaj v buňce je výsledkem funkce nebo vzorce. - Atd.... Je to již Výše zmíněno ale žádný výsledek. Nevíte, jak toto obejít? Případně pište na krystof@valve.cz Mnohokrát děkuji

Štěpán

Přidáno: 08.12.15 21:56

Dobrý den, mám problém ... nemohu otevřít soubor (ME 2010) "Soubor, který chcete otevřít blabla.xls, je v jiném formátu, než určuje přípona souboru. Před otevřením souboru ověřte, zda není poškozen a zda pochází z důvěryhodného zdroje. Chcete nyní soubor otevřít? ... Když kliknu na ANO, tak mám v souboru neplechu ... samé nesmyslné znaky. Poradíte mi prosím někdo? Moc děkuji.

Pavel Lasák

Přidáno: 27.01.16 09:21

To Štěpán: Zkusit otevřít přes google tabulky, nebo v OpenOffice mnohdy pomůže.

Jana

Přidáno: 01.02.16 11:31

Dobrý den, napřed mockrát děkuji za Vaše stránky. Jste úžasní, moc jste mi už pomohli. Mám na vás jeden dotaz - moc se mi líbí rozbalovací seznam - a teď dotaz - můžu si vytvořit rozbalovací seznam (zdrojový seznam) v jednom souboru a používat ho v několika dalších souborech? jde mi o to, že potřebuji, aby ho mohlo více uživatelů používat -aby se nestala chyba (třeba překlep) v dalších filtrech, které potom následují, když dávám data zase dohromady. nebo jestli je na to nějaká jiná finta, díky i za ni. Zdraví Jana Děkuji za odpověď

Šimi

Přidáno: 16.02.16 09:56

Zdravím, jste super stránky. Dotaz: Vytvořil jsem si rozbalovací menu, které mě načítá hodnoty z jiného listu. Počet načítaných hodnot je přes 1000. Jde mi o to jak v tom efektivně vyhledávat. Např. když začnu psát text tak aby mě to krásně filtrovalo položky. Nějak jsme na to stále nepřišel. Při tom v google tabulkách to funguje, ale google tabulky mi jaksi nestačí. Zkoušel jsem podmíněné seznamy ale to stále nic neřeší, protože i tak je vyfiltrovaných položek moc. Díky za případnou pomoc.

David

Přidáno: 22.02.16 16:33

Zdravím, chtěl bych se zeptat, zda-li na listu 1 mohu ve sloupci A ověřovat data na základě dvou (více) seznamů - z listu 2 a z listu 3 (vždy po jednom sloupci). Aby to nebylo tak jednoduché, tak se List1 A:A "šarže" v rozevíracím menu objeví asi pětsetkrát, dat je hodně, s tím jsem si zkusil poradit a do pomocného sloupce P (listů 2 a 3) jsem zkusil počítat (filtrovat) unikátní hodnoty dle =KDYŽ(COUNTIF($C$4:C4;C4)>1;"";C4), což se povedlo, ale když dám data listu ověřit podle sloupce P listu 2 a zaškrtnu "vynechávat prázdné..." tak se stejně seznam nezkrátí. Zkoušel jsem hledat nějakou pomoc na webech, ale nic moc. Makra prosím ne...

Petr Pecháček

Přidáno: 06.04.16 00:09

Ad Jana: Seznam položek mějte v listu zdrojového souboru. Prostými odkazy se v cílových sešitech odkažte na dané položky ve zdroji (neřeším ten proměnný počet položek). Na převzatý seznam se odvolejte v Data/Ověření (pro jistotu přes definovaný název).

Petr Pecháček

Přidáno: 06.04.16 00:17

Ad Šimi: Přes Data/Ověření nijak. Lze to uskutečnit jen vhodným nastavením vlastností ovládacího prvku ActiveX - Pole se seznamem.

Petr Pecháček

Přidáno: 06.04.16 00:18

Ad David: Bez přiloženého sešitu je takové zadání hatlamatla věštění z křišťálové koule.






Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2021 |