|
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
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.
Pro přehlednost seznam kapitol:
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.
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:
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):
Podrobněji v samostatné článku Co mohou buňky obsahovat.
Jednoduše - Ověření dat
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.
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
V první záložce Nastavení nastavujeme ověřovací kritéria
Ověřovací kritéria:
Poznámka: V případě volby seznamu je odkaz na buňky z nichž jsou položky seznamu čerpány.
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:
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.
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:
Stačí klik na tlačítko OK a máme hotovo.
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.
Neplést s volbami...
Na kartě Data v sekci Datové nástroje tlačítko Ověření dat
Další možnosti:
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.
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!
Pojmenovat data - definované názvy oblasti. (viz článek Jak na pojmenování dat v Excelu - definované názvy
Ověřovací data mít na stejném listu (pokud jsou data pro ověřovaní na stejném listu funguje jak má).
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.
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.
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í ;)
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.
Chcete využít ověření dat v praxi doporučuji články:
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
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.
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ů.
Přidáno: 28.04.12 17:26
Super článek.
Přidáno: 22.10.12 21:13
To Bulva: Jsem rád že pomohl.
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
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
Přidáno: 12.02.14 17:42
To Hektor: Gratulace. Jsem rád že stránky pomáhají.
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)?
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
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ší. :)
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
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.
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.
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)
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í.
Přidáno: 01.12.14 09:04
To Lukáš: -- Použiť filter, kontingenčnú tabuľku alebo makro
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.
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í" ... :)
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
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.
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.
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.
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).
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
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.
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.
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ěď
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.
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...
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).
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.
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.
Pomohl Vám návod? Sdílejte na Facebooku, G+ |
||
LinkedIn... |
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 |