Použití ověření dat u buněk

Ověření dat se používá k omezení typů dat nebo hodnot, které uživatelé zadávají do buňky. Jedním z nejběžnějších způsobů ověření dat je vytvoření rozevíracího seznamu. Podívejte se na toto video, které vám ve stručnosti ověření dat vysvětlí.

Stažení příkladů

Ukázkový sešit s veškerými příklady ověření dat uvedenými v tomto článku si můžete stáhnout. Postupujte podle těchto příkladů nebo si vytvořte své vlastní scénáře ověření dat.

Stáhnout příklady ověření dat v Excelu

Přidání ověření dat do buňky nebo oblasti

Poznámka : První tři kroky v této části slouží pro přidání jakéhokoli typu ověření dat. Kroky 4 až 8 jsou určené speciálně pro vytváření rozevíracího seznamu.

  1. Vyberte buňky, u kterých chcete nastavit ověření.

  2. Na kartě Data klikněte ve skupině Datové nástroje na Ověření dat.

    Ověření dat se nachází na kartě Data ve skupině Datové nástroje
  3. Na kartě Nastavení vyberte v poli Povolit položku Seznam.

    Karta Nastavení v Dialogovém okně Ověření dat
  4. V poli Zdroj zadejte hodnoty seznamu a oddělte je středníky. Příklady:

    1. K omezení odpovědí na dvě možnosti (například u otázky „Máte děti?“) zadejte Ano;Ne.

    2. K omezení hodnocení kvality obchodníka na tři úrovně zadejte Nízká;Průměrná;Vysoká.

      Poznámka : Tyto kroky se obecně doporučují jenom pro položky seznamu, které se nejspíš nikdy nebudou měnit. Pokud máte seznam, který by se časem mohl změnit, nebo budete potřebovat přidávat a odebírat položky, je lepší se držet níže uvedeného doporučeného postupu.

      Doporučený postup: Položky seznamu je možné vytvořit také pomocí odkazu na oblast buněk v jiné části sešitu. Nejefektivnějším způsobem je vytvořit seznam a potom ho naformátovat jako excelovou tabulku. (Na kartě Domů vyberte Styly > Formátovat jako tabulku > zvolte styl tabulky, který vám nejvíc vyhovuje.) Potom v této tabulce vyberte jenom data (seznam) bez záhlaví (v tomto případě Oddělení) a do pole Název nad sloupcem A zadejte smysluplný název.

      Do pole Název zadejte smysluplný název tohoto seznamu.

    Teď můžete místo zadávání hodnot seznamu do pole Zdroj pro ověření dat přidat název, který jste právě definovali. Dejte před něj znaménko rovná se (=).

    Před název tabulky dejte znaménko =

    Nejlepší věcí na používání tabulky je, že když položky do seznamu přidáte nebo je z něj odeberete, bude se váš seznam ověření dat automaticky aktualizovat.

    Poznámka : Tyto seznamy je vhodné umístit na samostatný list (v případě potřeby může být skrytý), aby je nikdo nemohl upravovat.

  5. Zkontrolujte, zda je zaškrtnuto políčko Rozevírací seznam v buňce. Pokud není, nebude zobrazena šipka rozevíracího seznamu vedle buňky.

    Rozevírací seznam v buňce, který je zobrazený vedle buňky
  6. Pokud chcete určit způsob zpracování v případě prázdných hodnot, zaškrtněte nebo zrušte zaškrtnutí políčka Přeskakovat prázdné buňky.

    Poznámka : Jestliže jste povolili určité hodnoty na základě oblasti buněk s definovaným názvem a v kterémkoli místě této oblasti se vyskytuje prázdná buňka, tak v případě, že je políčko Přeskakovat prázdné buňky zaškrtnuté, můžete do ověřované buňky zadat libovolnou hodnotu. To platí také u buněk, na které odkazují vzorce ověřování: Jestliže je odkazovaná buňka prázdná a políčko Přeskakovat prázdné buňky je zaškrtnuté, můžete do ověřované buňky zadat libovolnou hodnotu.

  7. Vyzkoušejte nastavené ověření dat a ujistěte se, že funguje správně. Zkuste do buněk zadat platná i neplatná data a přesvědčte se, zda nastavení funguje požadovaným způsobem a zda se v předpokládaných okamžicích zobrazují definované zprávy.

Poznámky : 

  • Po vytvoření rozevíracího seznamu se ujistěte, že všechno funguje tak, jak má. Například je dobré zkontrolovat, jestli jsou buňky dost široké na to, aby se do nich vešly všechny položky.

  • Pokud se váš seznam položek pro rozevírací seznam nachází na jiném listu a vy nechcete, aby ho ostatní uživatelé viděli nebo ho mohli upravovat, můžete ten list skrýt nebo zamknout. Další informace o zámku listu najdete v článku Zamknutí buněk.

  • Odebrání ověření dat: Vyberte buňku nebo buňky obsahující ověřování, které chcete odstranit, potom přejděte na Data > Ověření dat a v dialogovém okně ověření dat klikněte na tlačítko Vymazat vše a pak na OK.

Následující tabulka obsahuje přehled dalších typů ověření dat a způsob jejich přidání do sešitů.

Akce

Postup nastavení

Omezení zadávaných dat na celá čísla v určených mezích

  1. Postupujte podle kroků 1 až 3 uvedených nahoře v části Přidání ověření dat do buňky nebo oblasti.

  2. V seznamu Povolit vyberte Celé číslo.

  3. V rozevíracím seznamu Data vyberte požadovaný typ omezení. Pokud například chcete nastavit horní a spodní limit, vyberte položku Je mezi.

  4. Zadejte minimální, maximální nebo konkrétní hodnotu, kterou chcete povolit.

    Dialogové okno Ověřovací kritéria

    Můžete taky vložit vzorec, který vrátí číselnou hodnotu.

    Pokud chcete například určit ověření dat v buňce F1 a nastavte minimální mez odpočtů na dvojnásobek počtu dětí v buňce F1, vyberte v rozevíracím seznamu Data položku Je větší než nebo rovno a do pole Minimum zadejte vzorec =2*F1.

Omezení zadávaných dat na desetinná čísla v určených mezích

  1. Postupujte podle kroků 1 až 3 uvedených nahoře v části Přidání ověření dat do buňky nebo oblasti.

  2. V rozevíracím seznamu Povolit vyberte položku Desetinné číslo.

  3. V rozevíracím seznamu Data vyberte požadovaný typ omezení. Pokud například chcete nastavit horní a spodní limit, vyberte položku Je mezi.

  4. Zadejte minimální, maximální nebo konkrétní hodnotu, kterou chcete povolit.

    Můžete taky vložit vzorec, který vrátí číselnou hodnotu. Když chcete třeba nastavit maximální mez provizí a odměn na 6 % platu obchodníka v buňce E1, vyberte v rozevíracím seznamu Data položku Je menší než nebo rovno a do pole Maximum zadejte vzorec =E1*6%.

    Poznámka : Pokud chcete uživateli umožnit zadávat procentuální hodnoty, jako například 20 %, klikněte v rozevíracím seznamu Povolit na položku Desetinné číslo, v rozevíracím seznamu Rozsah vyberte požadovaný typ omezení, zadejte minimální, maximální, případně konkrétní hodnotu jako desetinné číslo, například 0,2, a potom zobrazte buňku s ověřením dat ve formátu procent, a to výběrem požadované buňky a kliknutím na tlačítko Styl procent  Obrázek tlačítka ve skupině Číslo na kartě Domů.

Omezení zadávaných dat na kalendářní data v určeném rozmezí

  1. Postupujte podle kroků 1 až 3 uvedených nahoře v části Přidání ověření dat do buňky nebo oblasti.

  2. V rozevíracím seznamu Povolit vyberte položku Datum.

  3. V rozevíracím seznamu Data vyberte požadovaný typ omezení. Pokud například chcete povolit data následující po určitém dnu, vyberte položku Je větší než.

  4. Zadejte počáteční, koncové nebo konkrétní datum, které chcete povolit.

    Můžete taky vložit vzorec, který vrátí datum. Pokud třeba chcete nastavit časové rozmezí mezi aktuálním datem a datem za tři dny, vyberte v rozevíracím seznamu Data položku Je mezi, do pole Datum zahájení zadejte vzorec =DNES() a do pole Datum ukončení zadejte vzorec =DNES()+3.

    Nastavení ověřovacích kritérií, které omezí datový záznam na určité časové rozmezí

Omezení zadávaných dat na čas v určeném časovém rozmezí

  1. Postupujte podle kroků 1 až 3 uvedených nahoře v části Přidání ověření dat do buňky nebo oblasti.

  2. V rozevíracím seznamu Povolit vyberte položku Čas.

  3. V rozevíracím seznamu Data vyberte požadovaný typ omezení. Pokud například chcete povolit časy předcházející určitému času, vyberte položku Je menší než.

  4. Zadejte počáteční, koncový, případně konkrétní čas, který chcete povolit. Pokud chcete zadat konkrétní čas, použijte formát hh:mm.

    Máte třeba buňku E2 s počátečním časem (8:00) a buňku F2 s koncovým časem (17:00) a chcete těmito časovými body vymezit časy schůzek. V tom případě vyberte v rozevíracím seznamu Data položku Je mezi, do pole Počáteční čas zadejte vzorec =E2 a potom do pole Koncový čas zadejte vzorec =F2.

    Nastavení ověření, které omezí časový záznam na určité časové rozmezí

Omezení zadávaných dat na text určité délky

  1. Postupujte podle kroků 1 až 3 uvedených nahoře v části Přidání ověření dat do buňky nebo oblasti.

  2. V rozevíracím seznamu Povolit vyberte položku Délku textu.

  3. V rozevíracím seznamu Data vyberte požadovaný typ omezení. Pokud například chcete povolit maximálně určitý počet znaků, vyberte položku Je menší než nebo rovno.

  4. V tomto případě chceme omezit zadávání na 25 znaků, takže v seznamu Rozsah vybereme Je menší než nebo rovno a do pole Maximum zadáme 25.

    Příklad ověření dat s omezenou délkou textu

Výpočet povolené hodnoty na základě obsahu jiné buňky

  1. Postupujte podle výše uvedených kroků 1 až 3 Přidání ověření dat do buňky nebo oblasti. V rozevíracím seznamu Povolit vyberte požadovaný typ dat.

  2. V rozevíracím seznamu Data vyberte požadovaný typ omezení.

  3. V polích umístěných pod rozevíracím seznamem Data klikněte na buňku, kterou chcete použít k určení povolené hodnoty.

    Když chcete třeba pro účet povolit zadání pouze v případě, že výsledek nepřekročí rozpočet zadaný v buňce E1, vyberte Povolit > Celé číslo, Data, Je menší než nebo rovno a Maximum >= =E1.

    Nastavení ověření pro výpočet, který je založený na obsahu jiné buňky

Poznámka : V následujících příkladech se využívá možnost Vlastní, při které nastavujete vlastní podmínky pomocí vzorců. Nemusíte se ohlížet na to, co je v seznamu Rozsah, protože při použití možnosti Vlastní se tento seznam deaktivuje.

Funkce

Vzorec

Buňka obsahující ID produktu (C2) vždy začíná řetězcem „ID-“ a má délku nejméně 10 znaků (více než 9).

=A(ZLEVA(C2;3)="ID-";DÉLKA(C2)>9)

Příklad 6: Vzorce ověření dat

Buňka obsahující název produktu (D2) obsahuje jenom text.

=JE.TEXT(D2)

Příklad 2: Vzorce ověření dat

Buňka obsahující něčí narozeniny (B6) ukazuje na věk vyšší než číslo zadané v buňce B4.

=KDYŽ(B6<=(DNES()-(365*B4));PRAVDA;NEPRAVDA)

Příklad ověření dat s omezením minimálního věku

Všechna data v oblasti buněk A2:A10 obsahují jedinečné hodnoty.

=COUNTIF($A$2:$A$10;A2)=1

Příklad 4: Vzorce ověření dat

Poznámka : Nejdřív musíte zadat vzorec ověření dat v buňce A2 a potom můžete zkopírovat A2 do A3:A10 tak, aby druhý argument COUNTIF odpovídal aktuální buňce. To znamená, že část A2)=1 se změní na A3)=1, A4)=1 a tak dále.

Další informace

Zajištění, aby e-mailová adresa zadaná v buňce B4 obsahovala symbol @

=JE.ČISLO(NAJÍT("@";B4))

Příklad ověření dat s kontrolou, jestli e-mailová adresa obsahuje symbol @

  • Proč není na pásu karet aktivovaný příkaz Ověření dat? Příkaz nemusí být k dispozici z těchto důvodů:

    • Tabulka Microsoft Excelu může být propojená se sharepointovým webem: Do excelové tabulky, která je propojená se sharepointovým webem, nejde přidat ověření dat. Pokud chcete přidat ověření dat, je třeba propojení tabulky zrušit nebo ji převést na oblast.

    • Možná právě zadáváte data: Příkaz Ověření dat na kartě Data není v průběhu zadávání dat do buňky aktivní. Zadávání dat ukončíte stisknutím klávesy ENTER nebo ESC.

    • List může být zamknutý nebo sdílený: Když je sešit zamknutý nebo sdílený, nedá se měnit nastavení ověření dat. Pokud chcete zjistit, jak ukončit sdílení nebo přestat chránit sešit, nahlédněte do tématu Ochrana sešitu.

  • Můžu změnit velikosti písma? Ne, velikost písma je pevná. Jediný způsob, jak změnit velikost zobrazení, je úprava přiblížení vaší obrazovky v pravém dolním rohu okna Excelu. Můžete ale použít ovládací prvek ActiveX Pole se seznamem. Více informací získáte v tématu Přidání seznamu nebo pole se seznamem do listu.

  • Existuje způsob, jak při psaní automaticky vyplnit nebo automaticky vybrat ověření dat? Ne, ale pokud používáte ovládací prvek ActiveX Pole se seznamem, tuto funkci máte.

  • Můžu v seznamu ověření dat udělat vícenásobný výběr? Pokud nepoužíváte ovládací prvek ActiveX Pole se seznamem nebo Seznam, tak ne.

  • Můžu vybrat položku v seznamu ověření dat a vyplnit jí jiný seznam? Ano! Nazývá se to Závislé ověření dat. Další informace najdete v článku Vytvoření závislých rozevíracích seznamů.

  • Jak odeberu veškeré ověření dat na listu? Můžete použít dialog Přejít na > Jinak. Vyberte: karta Domů > Úpravy > Najít a vybrat (nebo na klávesnici stiskněte F5 nebo Ctrl+G), pak zvolte Jinak > Ověření dat a vyberte buď Vše (pokud chcete vyhledat všechny buňky s ověřením dat), nebo Stejné (pokud chcete najít buňky, které odpovídají konkrétnímu nastavení ověření dat).

    Dialog Přejít na – jinak

    Potom vyvolejte dialogové okno ověření dat (karta Data > Ověření dat) a stiskněte tlačítko Vymazat vše a pak tlačítko OK.

  • Můžu pomocí ověření dat někoho donutit, aby do jedné nebo více buněk něco zadal? Ne, ale můžete použít jazyk VBA (Visual Basic for Applications), abyste zkontrolovali, jestli někdo za určitých podmínek něco zadal, například před uložením nebo zavřením sešitu. Pokud nic nezadali, můžete je přerušit a nenechat je pokračovat, dokud něco nezadají.

  • Jak můžu změnit barvu buňky na základě výběru ze seznamu ověření dat? Můžete použít Podmíněné formátování. V tomto případě by bylo vhodné použít možnost Formátovat pouze buňky obsahující.

    Možnost Formátovat pouze buňky obsahující
  • Jak můžu ověřit e-mailovou adresu? Můžete použít metodu Vlastní > Vzorec a zkontrolovat, jestli v zadání existuje symbol @. V tomto případě vypadá vzorec takto =JE.ČÍSLO(NAJÍT("@";D2)). Funkce NAJÍT hledá symbol @, a když ho najde, vrátí číslo jeho pozice v textovém řetězci a zápis umožní. Pokud ho nenajde, funkce NAJÍT vrátí chybu a zápis neumožní.

Máte konkrétní otázku k funkci?

Zadejte dotaz do komunitního fóra Excelu

Pomozte nám vylepšit Excel

Máte nějaké návrhy na zlepšení dalších verzí Excelu? Pokud ano, podívejte se prosím na témata v části Názor uživatele Excelu.

Viz taky

Další informace o ověření dat

Video: Vytváření a správa rozevíracích seznamů

Přidání nebo odebrání položek rozevíracího seznamu

Odebrání rozevíracího seznamu

Rozšiřte své znalosti a dovednosti
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

Děkujeme vám za zpětnou vazbu.

Děkujeme vám za váš názor. Vypadá to, že bude užitečné, když vás spojíme s některým z našich agentů z podpory Office.

×