Soubory Microsoft Excel se často používá k ukládání dat sdílených několika různými uživateli. Někdy uživatelé dělají chyby a zadávají nesprávná data, což způsobuje problémy různého stupně složitosti v závislosti na důležitosti dat. Této situaci se však lze snadno vyhnout.

Speciálně pro takový případ má Excel možnost kontrolovat správnost dat zapsaných do určitých buněk. Při zadání neplatné excelová data vydá varování nebo zákaz pokračovat v provozu.

Chcete-li aktivovat ověření, vyberte chráněné buňky, poté přejděte na kartu "Data" a vyberte položku "Ověření dat".

V okně, které se otevře, na záložce "Parametry" nejprve vyberte typ dat, která lze zapisovat do vybraných buněk, a poté nastavte omezení pro vybraný typ dat. Možností je poměrně dost, například jako omezení můžete zadat určitý rozsah čísel, časové období / data, text o určité délce nebo zadat soubor se seznamem platných hodnot. Je také možné nastavit vzorec pro výpočet platných hodnot na základě obsahu jiných buněk.

Jako příklad uveďme libovolné celé číslo ne větší než 100 000.

Na kartě "Chybová zpráva" vyberte akci, která by měla nastat, pokud je zadání nesprávné. Můžete si vybrat jednu ze tří možností:

Stop - zastaví operaci a zabrání nesprávnému zadání dat;
Upozornění - při zadání nesprávných údajů je vydáno upozornění, ale uživateli je dána možnost pokračovat v operaci;
Zpráva – Uživatel je upozorněn, že vstup je neplatný, ale vstup je povolen.

Můžete zde také přidat nadpis a text zprávy, který uživatel uvidí při zadání nesprávné hodnoty. To by mělo uživateli pomoci pochopit, co dělá špatně.

A takto vypadá hláška, která se objeví, když se pokusíte zadat údaje, které neodpovídají dané podmínce.

Tak jako další pomoc na záložce "Zpráva pro zadání" je možnost zanechat nápovědu.

Tato nápověda se zobrazí vedle buňky, když je vybrána, a zůstane na obrazovce, dokud nepřejdete do jiné buňky.

A pokud by se stalo, že se uživatelům přesto podařilo něco pokazit, je zde možnost zvýraznit chybně zadaná data. Můžete to provést výběrem možnosti „Zakroužkovat neplatná data“ z nabídky „Ověření dat“.

Takové jednoduché akce usnadní uživatelům život a pomohou vyhnout se mnoha problémům společná práce s daty v excelu.

Funkce OR vrátí hodnotu TRUE, pokud má alespoň jeden z jejích argumentů hodnotu TRUE; vrátí FALSE, pokud jsou všechny argumenty FALSE.

Syntax

OR(logická hodnota1; boolean_value2; ...)

boolean1, boolean2, ... - 1 až 30 podmínek k testování, které mohou být buď PRAVDA, nebo NEPRAVDA.

Pozornost!

Argumenty musí nabývat booleovských hodnot (TRUE nebo FALSE) nebo musí jít o pole nebo odkazy obsahující booleovské hodnoty. Pole je objekt používaný k získání více hodnot jako výsledek vyhodnocení jednoho vzorce nebo k práci se sadou argumentů umístěných v různých buňkách a seskupených podle řádků nebo sloupců. Rozsah pole používá obecný vzorec; konstanta pole je skupina konstant používaných jako argumenty.

Pokud zadaný interval neobsahuje booleovské hodnoty, vrátí funkce NEBO chybovou hodnotu #HODNOTA!.

Funkci OR můžete použít jako maticový vzorec ke kontrole, zda jsou v poli hodnoty. Chcete-li zadat maticový vzorec, stiskněte CTRL+SHIFT+ENTER.

Příklad

	A	B
1	Vzorec	Popis (výsledek)
2	=NEBO(PRAVDA)	Jeden argument je PRAVDA (PRAVDA)
3	=OR(1+1=1;2+2=5)	Všechny argumenty jsou vyhodnoceny jako FALSE (FALSE)
4	=NEBO(PRAVDA, NEPRAVDA, PRAVDA)	Alespoň jeden argument je PRAVDA (PRAVDA)

Více o Excelu.

Určení údajů, které lze zadávat do buněk

1. Vyberte buňku, kterou chcete zkontrolovat.

2. Vyberte příkaz Ověření z nabídky Data a poté klepněte na kartu Možnosti.

3. Určete požadovaný typ ověření.

Povolit zadávání pouze hodnot ze seznamu

1. V seznamu Typ dat vyberte možnost Seznam.

2. Klikněte do pole Zdroj a proveďte jednu z následujících akcí:

chcete-li lokálně definovat seznam, zadejte hodnoty seznamu oddělené čárkami;

chcete-li použít rozsah buněk, kterému byl přiřazen název, zadejte rovnítko (=) následované názvem rozsahu;

3. Zaškrtněte políčko Seznam platných hodnot.

Povolit zadávání hodnot, které jsou ve stanovených mezích

3. Zadejte minimální, maximální nebo specifickou povolenou hodnotu.

Povolit čísla bez omezení

1. V seznamu Typ dat vyberte Celé číslo nebo Skutečné.

2. V seznamu Hodnota vyberte požadované omezení. Chcete-li například nastavit dolní a horní hranici, vyberte mezi.

3. Zadejte minimální a maximální povolené hodnoty nebo definujte hodnotu.

Povolit data a časy v určitém časovém intervalu

1. V poli Povolit vyberte Datum nebo Čas.

2. V poli Data vyberte požadované omezení. Chcete-li například povolit data po určitém dni, vyberte větší než.

3. Zadejte začátek, konec nebo konkrétní datum či čas.

Povolit text konkrétní délky

1. Vyberte příkaz Délka textu v okně Typ dat.

2. V poli Data vyberte požadované omezení. Chcete-li například nastavit určitý počet znaků, vyberte menší nebo rovno.

3. Zadejte minimální, maximální nebo specifickou délku textu.

Počítejte platné hodnoty na základě obsahu jiné buňky.

1. Vyberte požadovaný typ dat v seznamu Typ dat.

2. V poli Data vyberte požadované omezení.

3. V poli nebo polích pod polem Data vyberte buňku, kterou chcete použít k definování platných hodnot. Chcete-li například povolit zadávání informací o účtu pouze v případě, že součet nepřesahuje rozpočet, vyberte hodnotu Počet desetinných míst v rozevíracím seznamu Typ dat, vyberte hodnotu menší nebo rovnou v rozevíracím seznamu Data a v poli Maximum vyberte buňku obsahující částku rozpočtu.

Použití vzorce k počítání předpokladů

1. V poli Typ dat vyberte jiný typ.

2. Do pole Vzorec zadejte vzorec pro výpočet booleovské hodnoty (TRUE pro platná data nebo FALSE pro neplatná data). Chcete-li například povolit zadání hodnoty do buňky pro piknikový účet pouze v případě, že nic není financováno z diskrečního účtu (buňka D6) a celkový rozpočet (D20) je také nižší než přidělených 40 000 USD, můžete zadat =AND (D6=0;D20

4. Určete, zda buňka může zůstat prázdná.

Pokud jsou povoleny prázdné (null) hodnoty, zaškrtněte políčko Ignorovat prázdné buňky.

Pokud nejsou povoleny prázdné (null) hodnoty, zrušte zaškrtnutí políčka Ignorovat prázdné buňky.

Poznámka. Li povolené hodnoty vzhledem k rozsahu buněk s přiřazeným názvem, který obsahuje prázdnou buňku, zaškrtnutí políčka Ignorovat prázdné buňky vám umožní zadat libovolnou hodnotu do kontrolované buňky. To platí také pro všechny buňky, na které odkazují ověřovací vzorce: pokud je kterákoli odkazovaná buňka prázdná, pak se zaškrtnutým políčkem Ignorovat prázdné buňky lze do ověřované buňky zadat libovolnou hodnotu.

5. Chcete-li při výběru buňky zobrazit další vstupní zprávu, přejděte na kartu Zpráva a zaškrtněte políčko Zobrazit nápovědu, pokud je buňka aktuální, poté zadejte název a text zprávy.

6. Určete, jak bude aplikace Microsoft Excel hlásit neplatná data.

Instrukce

1. Klepněte na kartu Chybová zpráva a zaškrtněte políčko Zobrazit chybovou zprávu.

2. Vyberte jednu z následujících možností pro pole Zobrazit.

Chcete-li zobrazit informační zprávu, která vám nebrání zadat nesprávná data, vyberte Podrobnosti.

Chcete-li zobrazit varování, které vám nebrání zadat nesprávná data, vyberte Upozornění.

Chcete-li zabránit nesprávnému zadávání dat, zvolte Stop.

3. Zadejte název a zadejte text zprávy (až 225 znaků).

Poznámka. Pokud nezadáte žádný název a text, výchozí název je „Microsoft Excel“ a zpráva „Zadaná hodnota je nesprávná. Sada hodnot, které lze zadat do buňky, je omezená.“

Poznámka. Použití ověření buňky nezformátuje buňku.

Někdy se při práci s tabulkami v editoru Excel zobrazí upozornění, že „množina hodnot, které lze zadat do buňky, je omezená“. Hlavním problémem je, že kvůli tomu nemůžete zadat informace, které potřebujete. Ve skutečnosti zde není nic špatného. Tuto situaci lze napravit v několika krocích. V tomto článku se blíže podíváme na to, proč se přesně toto okno zobrazuje a co lze v této situaci dělat.

V editoru Excel je možné omezit zadávání informací podle určitých kritérií. Postup je následující.

1. Klikněte na libovolnou buňku.
2. Přejděte na kartu "Data".
3. Klikněte na zvýrazněný nástroj.
4. Vyberte "Ověření dat".

1. Poté se zobrazí okno, ve kterém můžete vybrat formát informací, které mají být zkontrolovány.

Pojďme se na tyto formáty podívat blíže.

V tomto případě můžete do buňky zadat cokoli. Parametr se standardně používá pro libovolnou buňku.

Výběrem tohoto formátu můžete upravovat dodatečné nastavení ve druhém poli.

Pokud například vyberete možnost „mezi“, uvidíte, že se v tomto okně objeví dvě další pole: „Minimum“ a „Maximální“.

Pokud vyberete nějakou hodnotu „větší než“, budete muset zadat minimální povolenou hranici. Proto uvidíte pouze jedno pole – „Minimum“, protože „Maximum“ je neomezené.

V tomto případě je princip fungování úplně stejný jako u celých čísel. Rozdíl je v tom, že tentokrát můžete použít libovolné hodnoty. Včetně těch zlomkových. Pro ty, kteří nevědí, co jsou skutečná čísla, si o nich můžete přečíst více na Wikipedii.

Tento formát je nejzajímavější.

Abyste pochopili jeho schopnosti, musíte provést několik jednoduchých kroků.

1. Vyplňte pár buněk něčím. Je jedno co.

1. Klikněte na libovolnou buňku. Přejděte na záložku, kterou znáte. Klikněte na ikonu „Práce s daty“. Vyberte zvýrazněný nástroj.

1. V poli Typ dat vyberte možnost Seznam. Klikněte na sloupec "Zdroj". Poté vyberte požadovaný rozsah buněk. To je mnohem pohodlnější než ruční úprava odkazu. Pokračujte kliknutím na „OK“.

1. Díky tomu nyní v této buňce můžete vybrat požadované slovo z rozevíracího seznamu. To je mnohem pohodlnější než ruční změna textu.

Tento formát je vhodný pro případy, kdy má buňka obsahovat pouze datum. V tomto případě jsou k dispozici úplně stejné dodatečné podmínky jako u celých a reálných čísel.

Stejně tak zde. Zde je uveden pouze jeden čas (bez data).

Někdy nastávají situace, kdy je potřeba omezit nikoli formát buňky, ale délku obsahu výrazu. To znamená, že díky tomuto nastavení to můžete udělat tak, že do určitých polí nemůžete zadat text více, než je požadovaný počet znaků. Například ve sloupci telefonní číslo nebo název města.

Možností je poměrně dost. Tato metoda se používá při vyplňování jakýchkoli formulářů nebo dotazníků.

Tento formát se liší od všech ostatních. Zde můžete zadat nějaký vzorec pro kontrolu, zda informace splňují určitou podmínku.

Pokud chcete, můžete přidat nějaké upozornění na chybu při zadávání dat do buňky. Chcete-li to provést, musíte provést několik jednoduchých operací.

1. Opakováním výše uvedených kroků otevřete okno Ověřit vstupní hodnoty.
2. Vyberte nějaký typ dat. Jako příklad uvedeme, že musíte zadat „skutečné“ číslo, které je větší než 10.

1. Nyní přejděte na kartu "Chybová zpráva".
2. Vyberte typ zobrazení:
   • "Varování";
   • "Zpráva".
3. Zadejte název a text oznámení.
4. Pro uložení ověření zadaných hodnot klikněte na "OK"

Jako příklad uvedeme následující nastavení.

1. Zadejte libovolné číslo. Například cokoliv menšího než zadaná hodnota. Stiskněte klávesu Enter. A uvidíte upozornění, že došlo k chybě při zadávání dat.

V tento případ v poli "Typ" byl zadán formát "Zpráva". V tomto případě můžete zadat libovolné číslo. Pokaždé ale uvidíte podobné upozornění.

Pokud provedete typ "Stop", nebudete moci zadat nesprávnou hodnotu.

A nyní zkuste odstranit zadaná nastavení a ponechat prázdná pole.

V důsledku toho uvidíte dříve uvedenou chybu. To znamená, že pokud nic nezadáte ručně, editor ve výchozím nastavení zobrazí varování.

Ale všimněte si, že text zprávy je jiný!

Rozdíly ve verzích aplikace Microsoft Excel

Výše popsané pokyny jsou vhodné pro moderní editory z let 2010, 2013 a 2016. Existují určité rozdíly oproti starším programům.

Otevřete stejný soubor v aplikaci Excel 2007. Zadejte nesprávnou hodnotu. A uvidíte tuto chybu. To znamená, že v moderních editorech byl změněn výchozí text zprávy. Známý vzhled existoval až do roku 2007.

Ve starém Excelu 2003 je chyba úplně stejná.

Zpráva o zadávání dat

Aby se uživatelé po zadání informací vyhnuli takovým chybám, je nutné takové rady specifikovat předem. To se provádí velmi jednoduše.

1. Klikněte na libovolnou buňku, pro kterou chcete nastavit nějaká pravidla.
2. Otevřete v nabídce sekci "Data".
3. Vyberte nástroj Data Tools.
4. Klikněte na ikonu "Ověřit data".

1. V okně, které se zobrazí, přejděte na kartu „Vložit zprávu“.
2. Do určených polí zadejte libovolný text.
3. Pokračujte kliknutím na „OK“.

1. Nyní pokaždé, když aktivujete tuto buňku, uvidíte podobnou nápovědu. To výrazně usnadní situaci dalším uživatelům, kteří budou pracovat s vámi vytvořenými soubory.

Jak tuto chybu zakázat

Pokud jste otevřeli cizí tabulku a potřebujete provést nějaké změny, ale při zadávání dat vidíte podobnou chybu, pak není třeba zoufat. Náprava situace je celkem jednoduchá.

1. Vyberte buňku, do které nemůžete zadat požadovanou hodnotu.
2. Přejděte na kartu Data na panelu nástrojů.
3. Klikněte na nástroj Data Tools.
4. Klikněte na ikonu Kontrola dat.

1. Chcete-li odstranit všechna nastavení, stačí kliknout na tlačítko „Vymazat vše“.
2. Uložte změny kliknutím na "OK".

1. Nyní můžete zadat jakákoli data, jako byste otevřeli prázdný soubor a nejsou tam žádná nastavení.

Příklady od Microsoftu

Pokud zcela nerozumíte tomu, jak specifikovat podmínky pro zadávání hodnot, doporučujeme navštívit oficiální web společnosti Microsoft. Zde najdete ukázkový soubor. Obsahuje naprosto všechny typy dat a různé případy sestavování dokumentů a sestav.

Závěr

V tomto článku jsme zkoumali případy, kdy může dojít k chybě při zadávání hodnot Excelové tabulky. Kromě toho byla zvláštní pozornost věnována nastavení různých podmínek pro stanovení přípustného souboru hodnot.

Pokud vám něco nefunguje, pak vám něco chybí nebo jste zvolili špatný formát dat. Zkuste tyto kroky zopakovat opatrněji.

Video návod

Kdybys najednou nemohl pochopit tento manuál, níže je připojeno video, ve kterém jsou všechny kroky popsány ještě podrobněji s dalšími vysvětleními.

Zvážit, jak chránit jednotlivé buňky v excelu . Existuje několik způsobů. Do buňky můžete vložit rozevírací seznam. Uživatel vybere požadované slovo ze seznamu, který se objeví. Způsoby, jak vytvořit rozevírací seznam, naleznete v článku „Rozbalovací seznam v Excelu“.
Druhý způsob ochrany Excel buňky od zadání nesprávných údajů – jedná se o funkci „Kontrola dat“.Toto je potřeba pro správné plnění dotazníky, aplikace, pro práci se stolem, kde je zaměstnanec neustále rozptylován atp.
Nejprve označte buňku nebo rozsah buněk, kde chcete nastavit omezení zadávání dat.Jak vybrat rozsah buněk podle názvu, viz článek "Rozsah v Excelu».
Nyní přejděte na kartu "Data" v části "Práce s daty" klikněte na tlačítko "Ověření dat".Zobrazí se okno Ověřit vstupní hodnoty. Na kartě " Parametry " v řádku "Typ dat" bude typ - "Jakákoli hodnota".
Zde musíme nastavit naši hodnotu. Nastavili jsme funkci "Integer". Zadejte nejmenší a největší číslo, které lze zadat.
Například dotazník říká „věk“. Je třeba uvést plný počet let a napsat datum narození.
Vyplňte dialog takto.
Stiskneme "OK". Nyní kontrolujeme, zadáváme různá čísla, a když jsme zadali číslo 1234 (to je více než 100) a zmáčkli „Enter“, objevilo se takové varovné okno.

Stiskněte tlačítko "Znovu" nebo "Zrušit" a napište správné číslo.
Na kartě Vstupní zpráva v dialogovém okně Ověřit vstupní hodnoty můžete napsat vysvětlení, co přesně je zde potřeba napsat. A když na tuto buňku umístíte ukazatel myši, zobrazí se tato zpráva. Například. V okně "Kontrola vstupních hodnot" na třetí záložce "Chybová zpráva" můžete napsat text zprávy, která se zobrazí, pokud je číslo zadáno špatně.

Chcete-li toto nastavení z buňky odstranit, přejděte do funkce "Ověření dat" a stiskněte tlačítko "Vymazat vše" a poté - "OK".
Buňky tabulky, formulář k vyplnění si můžete nastavit tak, aby nemohly psát pouze datum pracovního dne. Další podrobnosti o tomto nastavení buněk najdete v článku „Kontrola data v Excelu“ .
Tabulku můžete nastavit tak, že nemůžete zadat jediný název. slovo, kód, hodnota dvakrát. Přečtěte si o tom "Zakázat zadávání duplicitních hodnot v Excelu".
Pro kontrolu načasování prodeje produktů, načasování úhrad faktur, rozdílu v částce atd. můžete nastavit funkci "Podmíněné formátování". Pokud je například množství v buňce více než 6000 rublů, buňka bude zbarvena.
Podívejte se, jak nainstalovat tuto funkci v článku "

Při práci s velkým množstvím informací, zejména při jejich zpracování pomocí vzorců nebo maker, je důležité, aby data neobsahovala chyby, které mohou ovlivnit výsledek. Potíže se získáváním správných zdrojů se objevují, když pocházejí z nestrukturovaných zdrojů (špatně navržené databáze, ruční vyplňování tabulek a formulářů lidmi, kteří se o výsledek nezajímají). Obzvláště obtížné je dosáhnout správného návrhu pro konkrétní masku (šablonu). Například datum jako řetězec lze zapsat několika způsoby:

• 01.01.2001;
• 01/01/2001;
• 1. ledna 2001 atd.

Článek popisuje funkce, které zjednodušují proces shromažďování a ověřování dat v Excelu.

Kde je?

Pro konfiguraci parametrů pro kontrolu vstupních hodnot klikněte na záložce "Data" v oblasti "Práce s daty" na ikonu "Ověření dat" nebo vyberte podobnou položku z rozbalovací nabídky:

Na obrazovce se objeví okno s výchozím nastavením, kde lze jako datový typ použít libovolnou hodnotu:

Nastavení testovací podmínky

Zpočátku je třeba vybrat typ dat, která mají být zkontrolována, což bude první podmínka. Celkem je k dispozici 8 možností:

• Celé číslo;
• Reálné číslo;
• Seznam;
• Datum;
• Čas;
• Délka textu;
• Další.

V souladu s vybranou položkou je možné vybrat další podmínky. Pro čísla, data, časy a délky textu je možné určit omezení: menší než, větší než, rozsah atd. Zdroj musí být svázán se seznamem a položka „other“ navrhuje zadat vzorec, který vrací booleovskou hodnotu.

Nejneobvyklejším typem je rozbalovací seznam.

Umožňuje vám vybrat hodnoty zadané jako zdroj a omezit na ně povolené hodnoty. Je také možné vytvářet dynamické rozevírací seznamy.

Nápověda k buňce Excelu

Funkce ověřování dat v aplikaci Excel vám umožňuje přizpůsobit popisky pro buňky listu. Chcete-li to provést, přejděte na druhou kartu okna pro kontrolu vstupních hodnot - „Zpráva pro zadání“.

Obrázek ukazuje příklad možné zprávy pro buňku, která obsahuje celá čísla od 1 do 31 (nastavení karty Možnosti). Název a zprávy jsou uvedeny podle vašeho uvážení, neexistují žádná pravidla pro jejich design. Nezapomeňte zaškrtnout políčko „Zobrazit popisek, pokud je buňka aktuální“, jinak bude zpráva deaktivována.

Příklad popisku v Excelu:

Výstup chybové zprávy

Poslední záložka okna ověření dat umožňuje konfigurovat chování a výstup zpráv při zjištění chybné hodnoty.

Existují tři možnosti zpráv, které se liší chováním:

• Stop;
• Varování;
• Zpráva.

Stop je chybová zpráva a umožňuje pouze 2 akce: zrušit zadání a zopakovat zadání. V případě zrušení bude nová hodnota změněna na předchozí. Re-entry umožňuje opravit novou hodnotu.

Varování je shovívavější než zastavení, protože umožňuje ponechat hodnotu, která neodpovídá ověřovacím kritériím poté, co uživatel potvrdí zadání.

Zpráva zobrazuje chybu jako jednoduchou informaci a dává vám možnost vrátit zpět poslední akci.