Ověření dat aplikace Excel: přidejte, použijte, zkopírujte a odeberte ověření dat v aplikaci Excel

1. Co je validace dat v Excelu?
2. Jak přidat validaci dat do Excelu?
3. Základní příklady pro validaci dat
4. Pokročilá vlastní pravidla pro validaci datn

Ověření dat aplikace Excel: přidejte, použijte, zkopírujte a odeberte ověření dat v aplikaci Excel

V Excelu je Ověření dat užitečnou funkcí, kterou můžete použít k omezení toho, co uživatel může zadat do buňky. Funkce ověřování dat vám například může pomoci omezit délku textových řetězců nebo text začínající / končící konkrétními znaky nebo jedinečné hodnoty, které je třeba zadat atd.

Tento tutoriál, budeme hovořit o tom, jak přidat, použít a odebrat ověření dat v aplikaci Excel, některé základní a pokročilé operace této funkce budou také podrobně ukázány.

Obsah:

1. Co je validace dat v Excelu?

2. Jak přidat validaci dat do Excelu?

3. Základní příklady pro validaci dat

3.1 Ověření dat pro celá čísla a desetinná místa
3.2 Ověření dat pro datum a čas
3.3 Ověření dat pro délku textu
3.4 Seznam ověření dat (rozevírací seznam)

4. Pokročilá vlastní pravidla pro validaci dat

4.1 Ověření dat umožňuje pouze čísla nebo texty
- Povolit zadávání pouze čísel pomocí funkce Ověření dat
- Povolit zadávání pouze textových řetězců pomocí funkce Ověření dat

5. Jak upravit validaci dat v Excelu?

6. Jak najít a vybrat buňky s validací dat v Excelu?

7. Jak zkopírovat pravidlo ověření dat do jiných buněk?

8. Jak použít ověření dat k zakroužkování neplatných záznamů v Excelu?

9. Jak odebrat validaci dat v Excelu?

Odeberte ověření dat ve vybraném rozsahu pomocí funkce ověření dat
Odeberte ověření dat ve vybraném rozsahu pomocí praktické funkce
Odeberte ověřování dat ze všech listů s kódem VBA

1. Co je validace dat v Excelu?

Projekt Ověření dat Tato funkce vám může pomoci omezit vstupní obsah v listu. Normálně můžete vytvořit některá ověřovací pravidla, která zabrání nebo umožní, aby byl do seznamu vybraných buněk vložen pouze nějaký druh dat.

Některá základní použití funkce Ověření dat:

1. Libovolná hodnota: neprovádí se žádné ověření, do zadaných buněk můžete zadat cokoli.
2. Celá hodnota: povolena jsou pouze celá čísla.
3. Desítkové: umožňuje zadávat celá čísla i desetinná místa.
4. Seznam: lze zadat nebo vybrat pouze hodnoty z předdefinovaného seznamu. Hodnoty jsou zobrazeny v rozevíracím seznamu.
5. datum: povolena jsou pouze data.
6. Čas: jsou povoleny pouze časy.
7. Délka textu: umožňuje zadat pouze zadanou délku textu.
8. Vlastní: vytvořte vlastní pravidla vzorců pro ověření vstupu uživatelů.

2. Jak přidat validaci dat do Excelu?

V listu aplikace Excel můžete přidat ověření dat pomocí následujících kroků:

1. Vyberte seznam buněk, kde chcete nastavit ověřování dat, a poté klikněte na Data > Ověření dat > Ověření dat, viz screenshot:

2. V Ověření dat v dialogovém okně pod Nastavení záložku, vytvořte si prosím svá vlastní ověřovací pravidla. do políček kritérií můžete zadat některý z následujících typů:

Hodnoty: Zadejte čísla přímo do políček kritérií;
Odkaz na buňku: Odkaz na buňku v listu nebo jiném listu;
Vzorce: Vytvořte složitější vzorce jako podmínky.

Jako příklad vytvořím pravidlo, které umožní zadávat pouze celá čísla mezi 100 a 1000, zde nastavte kritéria jako na obrázku níže:

3. Po konfiguraci podmínek můžete přejít na Vstup zprávy or Chybové upozornění kartu, jak chcete nastavit vstupní zprávu nebo upozornění na chybu pro buňky ověření. (Pokud nechcete nastavit upozornění, klikněte prosím OK dokončit přímo.)

3.1) Přidat vstupní zprávu (volitelně):

Můžete vytvořit zprávu, která se zobrazí při výběru buňky obsahující ověření dat. Tato zpráva pomáhá uživateli připomenout, co může do buňky zadat.

Přejít na Vstup zprávy kartu a proveďte následující:

Zkontrolovat Zobrazit vstupní zprávu, když je vybrána buňka volba;
Do příslušných polí zadejte požadovaný název a připomenutí;
klikněte OK zavřete toto dialogové okno.

Když nyní vyberete ověřenou buňku, zobrazí se okno se zprávou následovně:

3.2) Vytvářejte smysluplné chybové zprávy (volitelné):

Kromě vytváření vstupní zprávy můžete také zobrazit výstrahy chyb při zadání neplatných dat do buňky s ověřením dat.

Přejít na Chybové upozornění záložka Ověření dat dialogové okno, proveďte prosím toto:

Zkontrolovat Po zadání neplatných údajů zobrazit chybové hlášení volba;
v Styl v rozevíracím seznamu vyberte požadovaný požadovaný typ upozornění:
- Zastavit (výchozí): Tento typ upozornění brání uživatelům ve zadávání neplatných údajů.
- výstraha: Varuje uživatele, že data jsou neplatná, ale nebrání v jejich zadávání.
- Informace: Informuje uživatele pouze o neplatném zadání dat.
Do příslušných polí zadejte požadovaný název a výstražnou zprávu;
klikněte OK zavřete dialogové okno.

A teď, když zadáte neplatnou hodnotu, vyskočí pole s upozorněním na zprávu, jak je uvedeno níže:

Stop možnost: Můžete kliknout Opakovat zadejte jinou hodnotu nebo Zrušit pro odstranění záznamu.

výstraha možnost: Klikněte Ano zadat neplatný záznam, Ne jej upravit, popř Zrušit pro odstranění záznamu.

Informace možnost: Klikněte OK zadejte neplatný záznam nebo Zrušit pro odstranění záznamu.

Poznámka: Pokud nenastavíte vlastní vlastní zprávu v Chybové upozornění pole, výchozí Stop zobrazí se pole s výzvou k upozornění, jak je uvedeno níže:

3. Základní příklady pro validaci dat

Při používání této funkce ověření dat máte k dispozici 8 vestavěných možností, jak nastavit ověření dat. Například: libovolná hodnota, celá čísla a desetinná místa, datum a čas, seznam, délka textu a vlastní vzorec. V této části budeme diskutovat o tom, jak použít některé z předdefinovaných možností v aplikaci Excel?

3.1 Ověření dat pro celá čísla a desetinná místa

1. Vyberte seznam buněk, kde chcete povolit pouze celá čísla nebo desetinná místa, a poté klikněte Data > Ověření dat > Ověření dat.

2. V Ověření dat v dialogovém okně pod Nastavení na kartě proveďte následující operace:

Vyberte odpovídající položku Celé číslo or Desetinný v povolit rozevírací seznam.
A pak vyberte jedno z kritérií, která potřebujete v Data box (V tomto příkladu volím mezi volba).
Tipy: Kritéria obsahují: mezi, není mezi, rovná, není rovná, větší než, menší než, větší nebo rovná, menší nebo rovná.
Potom zadejte příkaz Minimální a Maximum hodnoty, které potřebujete (chci čísla mezi 0 a 1 00).
Konečně klikněte na tlačítko OK .

3. Nyní lze do vybraných buněk zadat pouze celá čísla od 0 do 100.

3.2 Ověření dat pro datum a čas

Chcete -li ověřit konkrétní datum nebo čas, který chcete zadat, je snadné to použít Ověření dat, postupujte následovně:

1. Vyberte seznam buněk, kde chcete povolit pouze konkrétní data nebo časy, a poté klikněte Data > Ověření dat > Ověření dat.

2. V Ověření dat v dialogovém okně pod Nastavení na kartě proveďte následující operace:

Vyberte odpovídající položku Datum or Čas v povolit rozevírací seznam.
A pak vyberte jedno z kritérií, která potřebujete v Data box (zde vybírám větší než volba).
Tipy: Kritéria obsahují: mezi, není mezi, rovná, není rovná, větší než, menší než, větší nebo rovná, menší nebo rovná.
Potom zadejte příkaz Datum zahájení potřebujete (chci data větší než 8).
Konečně klikněte na tlačítko OK .

3. Nyní lze do vybraných buněk zadat pouze data větší než 8/20/2021.

3.3 Ověření dat pro délku textu

Pokud potřebujete omezit počet znaků, které lze zadat do buňky. Chcete -li například omezit obsah na maximálně 10 znaků pro určitý rozsah, postupujte takto Ověření dat také vám může udělat laskavost.

1. Vyberte seznam buněk, kde chcete omezit délku textu, a poté klikněte Data > Ověření dat > Ověření dat.

2. V Ověření dat v dialogovém okně pod Nastavení na kartě proveďte následující operace:

vybrat Délka textu z povolit rozevírací seznam.
A pak vyberte jedno z kritérií, která potřebujete v Data box (V tomto příkladu volím méně než volba).
Tipy: Kritéria obsahují: mezi, není mezi, rovná, není rovná, větší než, menší než, větší nebo rovná, menší nebo rovná.
Potom zadejte příkaz Maximum číslo, které musíte omezit (chci, aby délka textu nepřesáhla 10 znaků).
Konečně klikněte na tlačítko OK .

3. Nyní vybrané buňky umožňují zadat pouze textový řetězec kratší než 10 znaků.

3.4 Seznam ověření dat (rozevírací seznam)

S tímto silným Ověření dat funkci, můžete také rychle a snadno vytvořit rozevírací seznam v buňkách. Postupujte prosím takto:

1. Vyberte cílové buňky, kam chcete vložit rozevírací seznam, a poté klikněte Data > Ověření dat > Ověření dat.

2. V Ověření dat v dialogovém okně pod Nastavení na kartě proveďte následující operace:

vybrat Seznam z povolit rozbalovací seznam.
v Zdroj textové pole, zadejte položky seznamu oddělené čárkami. Chcete-li například omezit vstup uživatele na tři možnosti, zadejte Nespuštěno, Probíhá, Dokončeno, nebo můžete vybrat seznam buněk obsahujících hodnoty, do kterých chcete vložit rozevírací seznam.
Konečně klikněte na tlačítko OK .

3. Nyní byl rozevírací seznam vytvořen do buněk, jak je uvedeno níže:

Kliknutím zobrazíte podrobnější informace z rozevíracího seznamu…

4. Pokročilá vlastní pravidla pro validaci dat

V této části představím, jak vytvořit pokročilá pravidla ověřování vlastních dat, která vyřeší vaše problémy, například: vytvoření ověřovacích vzorců, které povolí pouze čísla nebo textové řetězce, pouze jedinečné hodnoty, pouze zadaná telefonní čísla, e -mailové adresy atd. .

4.1 Ověření dat umožňuje pouze čísla nebo texty

Povolit zadávání pouze čísel pomocí funkce Ověření dat

Chcete -li povolit pouze čísla v rozsahu buněk, postupujte takto:

1. Vyberte rozsah buněk, do kterého chcete zadat pouze čísla.

2, klikněte Data > Ověření dat > Ověření dat, ve vyskakovacím okně Ověření dat v dialogovém okně pod Nastavení na kartě proveďte následující operace:

vybrat Zvyk z povolit rozbalovací seznam.
A pak zadejte tento vzorec: = ISNUMBER (A2) do Vzorec Textové pole. (A2 je první buňka vybraného rozsahu, který chcete omezit)
klikněte OK zavřete toto dialogové okno.

3. Od nynějška lze do vybraných buněk zadávat pouze čísla.

Poznámka: Tento ČÍSLO funkce umožňuje libovolné číselné hodnoty v ověřených buňkách, včetně celých čísel, desetinných míst, zlomků, dat a časů.

Povolit zadávání pouze textových řetězců pomocí funkce Ověření dat

Chcete -li omezit položky buňky pouze na text, můžete použít Ověření dat funkce s vlastním vzorcem založeným na ISTEXT funkci, proveďte prosím toto:

1. Vyberte rozsah buněk, do kterého chcete zadat pouze textové řetězce.

2, klikněte Data > Ověření dat > Ověření dat, ve vyskakovacím okně Ověření dat v dialogovém okně pod Nastavení na kartě proveďte následující operace:

vybrat Zvyk z povolit rozbalovací seznam.
A pak zadejte tento vzorec: = ISTEXT (A2) do Vzorec Textové pole. (A2 je první buňka vybraného rozsahu, který chcete omezit)
klikněte OK zavřete toto dialogové okno.

3. Při zadávání dat do konkrétních buněk lze nyní povolit pouze data ve formátu textu.

4.2 Ověření dat umožňuje pouze alfanumerické hodnoty

Pro některé účely chcete povolit zadávání abeced a číselných hodnot, ale omezit speciální znaky jako ~,%, $, mezera atd., Tato část vám představí několik triků.

Povolte pouze alfanumerické hodnoty s funkcí Ověření dat

Chcete -li zabránit speciálním znakům, ale povolit pouze alfanumerické hodnoty, měli byste do pole vytvořit vlastní vzorec Ověření dat funkce, postupujte následovně:

1. Vyberte rozsah buněk, do kterého chcete zadat pouze alfanumerické hodnoty.

2, klikněte Data > Ověření dat > Ověření dat, ve vyskakovacím okně Ověření dat v dialogovém okně pod Nastavení na kartě proveďte následující operace:

vybrat Zvyk z povolit rozbalovací seznam.
A pak zadejte níže uvedený vzorec do Vzorec Textové pole.
=IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))
klikněte OK zavřete toto dialogové okno.

Poznámka: Ve výše uvedených vzorcích A2 je první buňka vybraného rozsahu, který chcete omezit.

3. Nyní je povoleno zadávat pouze abecedy a číselné hodnoty a speciální znaky budou omezeny při psaní podle obrázku níže:

Povolte pouze alfanumerické hodnoty s úžasnou funkcí

Možná je výše uvedený vzorec pro nás složitý na pochopení a zapamatování, zde představím užitečnou funkci - Zabraňte psaní of Kutools pro Excel, s touto funkcí můžete tuto úlohu rychle vyřešit snadno.

Po instalaci Kutools pro Excel, udělejte prosím toto:

1. Vyberte rozsah buněk, do kterého chcete zadat pouze alfanumerické hodnoty.

2. Potom klepněte na tlačítko Kutools > Zabraňte psaní > Zabraňte psaní, viz screenshot:

3. Ve vyskočené Zabraňte psaní dialogové okno vyberte Zabraňte zadávání zvláštních znaků možnost, viz screenshot:

4. Potom klepněte na tlačítko Ok tlačítko a v následujících polích výzev klikněte na Ano > OK k dokončení operace. Nyní jsou ve vybraných buňkách povoleny pouze abecedy a číselné hodnoty, viz snímek obrazovky:

4.3 Ověření dat umožňuje textům začínat nebo končit konkrétními znaky

Pokud by všechny hodnoty v určitém rozsahu měly začínat nebo končit konkrétním znakem nebo podřetězcem, můžete použít validaci dat pomocí vlastního vzorce založeného na funkci PŘESNÉ, LEVÉ, PRAVÉ nebo COUNTIF.

Povolit textům začínat nebo končit konkrétními znaky s jedinou podmínkou

Například chci, aby texty při zadávání textových řetězců do konkrétních buněk začínaly nebo končily „CN“, postupujte takto:

1. Vyberte rozsah buněk, které povolují pouze texty, které začínají nebo končí určitými znaky.

2. Potom klepněte na tlačítko Data > Ověření dat > Ověření dat, ve vyskakovacím okně Ověření dat v dialogovém okně pod Nastavení na kartě proveďte následující operace:

vybrat Zvyk z povolit rozbalovací seznam.
A pak zadejte níže uvedený vzorec do Vzorec Textové pole.
Begin with: =EXACT(LEFT(A2,2),"CN")
End with: =EXACT(RIGHT(A2,2),"CN")
klikněte OK zavřete toto dialogové okno.

Poznámka: Ve výše uvedených vzorcích A2 je první buňka vybraného rozsahu, číslo 2 je počet znaků, který jste zadali, CN je text, kterým chcete začít nebo skončit.

3. Od nynějška lze do vybraných buněk zadávat pouze textový řetězec, který začíná nebo končí zadanými znaky. V opačném případě vyskočí varovná výstraha, která vám připomene následující obrázek obrazovky:

Tip: Výše uvedené vzorce rozlišují velká a malá písmena, pokud nepotřebujete rozlišovat velká a malá písmena, použijte prosím následující vzorce CONTIF:

Begin with (non case sensitive): =COUNTIF(A2,"CN*")
End with (non case sensitive): =COUNTIF(A2,"*CN")

Poznámka: Hvězdička * je zástupný znak, který odpovídá jednomu nebo více znakům.

Povolit textům začínat nebo končit konkrétními znaky s více kritérii (NEBO logika)

Pokud například chcete, aby texty musely začínat nebo končit „CN“ nebo „UK“, jak ukazuje níže uvedený snímek obrazovky, musíte přidat další instanci EXACT pomocí znaménka plus (+). Proveďte prosím následující kroky:

1. Vyberte rozsah buněk, který povoluje pouze texty, které začínají nebo končí více kritérii.

2. Potom klepněte na tlačítko Data > Ověření dat > Ověření dat, ve vyskakovacím okně Ověření dat v dialogovém okně pod Nastavení na kartě proveďte následující operace:

vybrat Zvyk z povolit rozbalovací seznam.
A pak zadejte níže uvedený vzorec do Vzorec Textové pole.
Begin with: =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")
End with: =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")
klikněte OK zavřete toto dialogové okno.

Poznámka: Ve výše uvedených vzorcích A2 je první buňka vybraného rozsahu, číslo 2 je počet znaků, který jste zadali, CN a UK jsou konkrétní texty, kterými chcete začít nebo skončit.

3. Nyní lze do vybraných buněk zadat pouze textový řetězec, který začíná nebo končí zadanými znaky.

Tip: Chcete-li ignorovat velká a malá písmena, použijte níže uvedené vzorce CONTIF:

Begin with (non case sensitive): =COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")
End with (non case sensitive): =COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")

Poznámka: Hvězdička * je zástupný znak, který odpovídá jednomu nebo více znakům.

4.4 Povolení ověřování údajů musí obsahovat / nesmí obsahovat konkrétní text

V této části budu hovořit o tom, jak použít ověření dat, aby hodnoty mohly obsahovat nebo nesmí obsahovat jeden konkrétní podřetězec nebo jeden z mnoha podřetězců v aplikaci Excel.

Povolené položky musí obsahovat jeden nebo jeden z mnoha konkrétních textů

Povolené položky musí obsahovat jeden konkrétní text

Chcete -li povolit položky, které obsahují například konkrétní textový řetězec, například všechny zadané hodnoty by měly obsahovat text „KTE“, jak ukazuje níže uvedený snímek obrazovky, můžete validaci dat použít pomocí vlastního vzorce založeného na funkcích NAJÍT a ISNUMBER. Postupujte prosím takto:

1. Vyberte rozsah buněk, který povoluje pouze texty, které obsahují určitý text.

vybrat Zvyk z rozevíracího seznamu Povolit.
A poté zadejte jeden z níže uvedených vzorců do Vzorec Textové pole.
=ISNUMBER(FIND("KTE",A2)) (Case sensitive)
=ISNUMBER(SEARCH("KTE",A2)) (Non case sensitive)
klikněte OK zavřete toto dialogové okno.

Poznámka: Ve výše uvedených vzorcích A2 je první buňka vybraného rozsahu, text KTE je textový řetězec, který položky musí obsahovat.

3. Nyní, když zadaná hodnota neobsahuje navržený text, vyskočí pole s upozorněním.

Povolené položky musí obsahovat jeden z mnoha konkrétních textů

Výše uvedený vzorec funguje pouze pro jeden textový řetězec, pokud potřebujete, aby byl v buňkách povolen některý z mnoha textových řetězců, jak ukazuje následující snímek obrazovky, měli byste společně použít funkce SUMPRODUCT, FIND a ISNUMBER k vytvoření vzorce.

1. Vyberte rozsah buněk, který povolí pouze texty, které obsahují některou z mnoha položek.

vybrat Zvyk z povolit rozbalovací seznam.
Poté zadejte do pole jeden z níže uvedených vzorců Vzorec Textové pole.
=SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0 (Case sensitive)
=SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0 (Non case sensitive)
A pak klikněte OK zavřete dialogové okno.

Poznámka: Ve výše uvedených vzorcích A2 je první buňka vybraného rozsahu, C2: C4 je seznam hodnot, které chcete povolit, aby položky obsahovaly některou z nich.

3. A nyní lze zadat pouze položky obsahující některou z hodnot v konkrétním seznamu.

Povolené položky nesmí obsahovat jeden nebo jeden z mnoha konkrétních textů

Povolené položky nesmí obsahovat jeden konkrétní text

Chcete -li ověřit, že položky nesmí obsahovat konkrétní text, například pro povolení hodnot, které v buňce nesmí obsahovat text „KTE“, můžete k vytvoření pravidla pro ověření dat použít funkce ISERROR a FIND. Postupujte prosím takto:

1. Vyberte rozsah buněk, který povolí pouze texty, které neobsahují určitý text.

vybrat Zvyk z povolit rozbalovací seznam.
A poté zadejte jeden z níže uvedených vzorců do Vzorec Textové pole.
=ISERROR(FIND("KTE",A2)) (Case sensitive)
=ISERROR(SEARCH("KTE",A2)) (Non case sensitive)
klikněte OK zavřete toto dialogové okno.

Poznámka: Ve výše uvedených vzorcích A2 je první buňka vybraného rozsahu, text KTE je textový řetězec, který položky nesmí obsahovat.

3. Nyní nebude možné zadávat položky, které obsahují konkrétní text.

Povolené položky nesmí obsahovat jeden z mnoha konkrétních textů

Chcete -li zabránit tomu, aby byl jeden z mnoha textových řetězců v seznamu zadán jako na obrázku níže, postupujte takto:

1. Vyberte rozsah buněk, u kterých chcete zabránit některým textům.

vybrat Zvyk z povolit rozbalovací seznam.
Poté zadejte vzorec níže do Vzorec Textové pole.
=SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0 (Case sensitive)
=SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0 (Non case sensitive)
A pak klikněte OK zavřete dialogové okno.

Poznámka: Ve výše uvedených vzorcích A2 je první buňka vybraného rozsahu, C2: C4 je seznam hodnot, kterým chcete zabránit, pokud položky obsahují některou z nich.

3. Od nynějška nebude možné zadávat položky, které obsahují některý z konkrétních textů.

4.5 Ověření dat umožňuje pouze jedinečné hodnoty

Pokud chcete zabránit zadávání duplicitních dat do řady buněk, tato část představí některé rychlé metody řešení tohoto úkolu v aplikaci Excel.

Povolte pouze jedinečné hodnoty s funkcí ověření dat

Normálně vám může pomoci funkce ověření dat s vlastním vzorcem založeným na funkci COUNTIF, proveďte následující kroky:

1. Vyberte buňky nebo sloupec, do kterého chcete zadat pouze jedinečné hodnoty.

vybrat Zvyk z povolit rozbalovací seznam.
A pak zadejte níže uvedený vzorec do Vzorec Textové pole.
=COUNTIF($A$2:$A$9,A2)=1
klikněte OK zavřete toto dialogové okno.

Poznámka: Ve výše uvedeném vzorci, A2: A9 je rozsah buněk, které chcete povolit pouze jedinečné hodnoty, a A2 je první buňka vybraného rozsahu.

3. Nyní lze zadat pouze jedinečné hodnoty a po zadání duplicitních dat se zobrazí varovná zpráva, viz snímek obrazovky:

Povolte pouze jedinečné hodnoty s kódem VBA

Následující kód VBA vám také může pomoci zabránit zadávání duplicitních hodnot, proveďte prosím toto:

1. Klikněte pravým tlačítkem na kartu listu, které chcete povolit pouze jedinečné hodnoty, a vyberte Zobrazit kód z kontextové nabídky ve vyskakovacím okně Microsoft Visual Basic pro aplikace zkopírujte a vložte následující kód do prázdného modulu:

Kód VBA: Povolit pouze jedinečné hodnoty v rozsahu buněk:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
  Dim xRg As Range, iLong, fLong As Long
  If Not Intersect(Target, Me.[A1:A100]) Is Nothing Then
     Application.EnableEvents = False
     For Each xRg In Target
     With xRg
         If (.Value <> "") Then
          If WorksheetFunction.CountIf(Me.[A:A], .Value) > 1 Then
            iLong = .Interior.ColorIndex
            fLong = .Font.ColorIndex
            .Interior.ColorIndex = 3
            .Font.ColorIndex = 6
            MsgBox "Duplicate Entry !", vbCritical, "Kutools for Excel"
            .ClearContents
            .Interior.ColorIndex = iLong
            .Font.ColorIndex = fLong
          End If
       End If
     End With
     Next
     Application.EnableEvents = True
  End If
End Sub

Poznámka: Ve výše uvedeném kódu je A1: A100 a A: jsou buňky ve sloupci, kterým chcete zabránit duplicitě, změňte je podle potřeby.

2. Potom tento kód uložte a zavřete. Nyní, když zadáváte duplicitní hodnotu do buňky A1: A100, vyskočí pole s varovnou výzvou, jak je uvedeno níže:

Povolte pouze jedinečné hodnoty s praktickou funkcí

Pokud máte Kutools pro Excel, S jeho Zabraňte duplikování funkci, můžete nastavit ověřování dat, aby se zabránilo duplicitám pro rozsah buněk pouze několika kliknutími.

Po instalaci Kutools pro Excel, udělejte prosím toto:

1. Vyberte rozsah buněk, kterým chcete zabránit duplicitním hodnotám, ale povolit pouze jedinečná data.

2. Potom klepněte na tlačítko Kutools > Zabraňte psaní > Zabraňte duplikování, viz screenshot:

3. A vyskočí varovná zpráva, která vám připomene, že Ověření dat bude odstraněno, pokud použijete tuto funkci, klikněte Ano a v následujícím okně výzvy klikněte na OK, viz screenshoty:

4. Když nyní zadáte některá duplicitní data do zadaných buněk, zobrazí se výzva k připomenutí, že duplicitní data nejsou platná, viz screenshot:

4.6 Ověření dat umožňuje pouze velká / malá písmena / správná písmena

Tato validace dat je výkonná funkce a může také pomoci uživateli umožnit zadávat do rozsahu buněk pouze velká, malá nebo správná písmena. Proveďte prosím následující kroky:

1. Vyberte rozsah buněk, u kterého chcete zadávat pouze velká, malá nebo správná písmena.

vybrat Zvyk z povolit rozbalovací seznam.
A poté zadejte jeden z níže uvedených vzorců do Vzorec Textové pole.
=AND(EXACT(A2,UPPER(A2)),ISTEXT(A2)) (only allow uppercase text)
=AND(EXACT(A2,LOWER(A2)),ISTEXT(A2)) (only allow lowercase text)
=AND(EXACT(A2,PROPER(A2)),ISTEXT(A2)) (only allow proper case text)
klikněte OK zavřete toto dialogové okno.

Poznámka: Ve výše uvedeném vzorci, A2 je první buňka sloupce, který chcete použít.

3. Nyní budou přijaty pouze položky, které odpovídají pravidlu, které jste vytvořili.

4.7 Ověření dat umožňuje hodnoty, které existují / neexistují v jiném seznamu

Povolit zadávání hodnot existujících nebo neexistujících v jiném seznamu do řady buněk může být pro většinu z nás bolestivý problém. Ve skutečnosti můžete použít funkci ověření dat s jednoduchým vzorcem založeným na funkci COUNTIF, abyste se s tím vypořádali.

Například chci, aby byly do rozsahu buněk zadány pouze hodnoty v rozsahu C2: C4, jak ukazuje níže uvedený snímek obrazovky. Chcete -li tuto úlohu vyřešit, proveďte prosím toto:

1. Vyberte rozsah buněk, na které chcete použít ověření dat.

vybrat Zvyk z povolit rozbalovací seznam.
A poté zadejte jeden z níže uvedených vzorců do Vzorec Textové pole.
=COUNTIF($C$2:$C$4,A2)>0 (only allow values exist in another column)
=COUNTIF($C$2:$C$4,A2)=0 (prevent values exist in another column)
klikněte OK zavřete toto dialogové okno.

Poznámka: Ve výše uvedeném vzorci, A2 je první buňka sloupce, který chcete použít, C2: C4 je seznam hodnot, kterým chcete zabránit nebo povolit, pokud jsou položky jednou z nich.

3. Nyní mohou položky odpovídat pouze pravidlu, které jste vytvořili, a ostatním bude zabráněno.

4.8 Ověření dat vynutí pouze zadání formátu telefonního čísla

Když zadáváte informace o zaměstnancích vaší společnosti, do jednoho sloupce je třeba zadat telefonní číslo, aby bylo zajištěno rychlé a přesné zadávání telefonních čísel, v tomto případě můžete pro telefonní čísla nastavit ověření dat. Například chci, aby bylo telefonní číslo v tomto formátu (123) 456-7890 povoleno zadávat do listu, tato část představí dva rychlé triky pro řešení tohoto úkolu.

Vynutit pouze formát telefonního čísla pomocí funkce Ověření dat

Chcete -li povolit zadávání pouze konkrétního formátu telefonního čísla, postupujte takto:

1. Vyberte seznam buněk, do kterého chcete zadat konkrétní formát telefonního čísla, a poté klikněte pravým tlačítkem, vyberte Formát buněk z kontextové nabídky viz screenshot:

2. V Formát buněk v dialogovém okně pod Číslo vyberte kartu Zvyk vlevo Kategorie seznamu a poté zadejte požadovaný formát telefonního čísla do textového pole Typ, použiji toto (###) ### - #### formát, viz screenshot:

3. Potom klepněte na tlačítko OK zavřete dialogové okno.

4. Po formátování buněk pokračujte výběrem buněk a poté otevřete soubor Ověření dat dialogové okno kliknutím Data > Ověření dat > Ověření dat, ve vyskakovacím dialogu pod Nastavení na kartě proveďte následující operace:

vybrat Zvyk z povolit rozbalovací seznam.
A pak zadejte tento vzorec = AND (ISNUMBER (A2), LEN (A2) = 10) do textového pole Vzorec.
klikněte OK zavřete toto dialogové okno.

Poznámka: Ve výše uvedeném vzorci, A2 je první buňka sloupce, u kterého chcete ověřit telefonní číslo.

5. Nyní, když zadáte 10místné číslo, bude automaticky převedeno na konkrétní formát telefonního čísla, jak potřebujete, viz screenshoty:

Poznámka: Pokud zadané číslo není 10 číslic, vyskočí okno s varovnou zprávou, které vám připomene, viz screenshot:

Vynutit pouze formát telefonního čísla s užitečnou funkcí

Kutools pro ExcelJe Ověření telefonního čísla Tato funkce vám také může pomoci vynutit zadávání pouze formátu telefonního čísla několika kliknutími.

Po instalaci Kutools pro Excel, udělejte prosím toto:

1. Vyberte seznam buněk, které umožňují pouze konkrétní telefonní číslo, a poté klikněte na Kutools > Zabraňte psaní > Ověření telefonního čísla, viz screenshot:

2. V Telefonní číslo v dialogovém okně vyberte konkrétní formát telefonního čísla, který potřebujete, nebo si můžete vytvořit vlastní formátování kliknutím na přidat tlačítko, viz screenshot:

3. Po výběru nebo nastavení formátování telefonního čísla klikněte na OKNyní lze zadat pouze telefonní číslo s konkrétním formátováním, v opačném případě vyskočí varovná zpráva, která vám připomene, viz snímek obrazovky:

4.9 Ověření údajů vyžaduje pouze zadání e -mailových adres

Předpokládejme, že do sloupce listu musíte zadat více e -mailových adres, aby se zabránilo zadávání nesprávných formátů e -mailových adres, obvykle můžete nastavit pravidlo pro ověření dat, které umožní pouze formátování e -mailových adres.

Vynutit pouze formát e -mailových adres s funkcí ověření dat

Pomocí funkce Ověření dat s vlastním vzorcem můžete vytvořit pravidlo, které zabrání rychlému zadávání neplatných e -mailových adres, postupujte takto:

1. Vyberte buňky, do kterých chcete zadat pouze e -mailové adresy, a poté klikněte Data > Ověření dat > Ověření dat.

2. Ve vyskočené Ověření dat v dialogovém okně pod Nastavení na kartě proveďte následující operace:

vybrat Zvyk z povolit rozbalovací seznam.
A pak zadejte tento vzorec = ISNUMBER (MATCH ("*@*.?*", A2,0)) do Vzorec Textové pole.
klikněte OK zavřete toto dialogové okno.

Poznámka: Ve výše uvedeném vzorci, A2 je první buňka sloupce, který chcete použít.

3. Pokud nyní zadaný text není ve formátu e -mailové adresy, vyskočí pole s upozorněním, které vám připomene, viz snímek obrazovky:

Vynutit pouze formát e -mailových adres s praktickou funkcí

Kutools pro Excel podporuje úžasnou funkci - Ověřte e-mailovou adresuPomocí tohoto nástroje můžete zabránit neplatným e -mailovým adresám jediným kliknutím.

Po instalaci Kutools pro Excel, postupujte následovně:

1. Vyberte buňky, pro které chcete zadat pouze e -mailové adresy, a poté klikněte Kutools > Zabraňte psaní > Ověřte e-mailovou adresu. Viz snímek obrazovky:

2. A pak lze zadat pouze formátování e -mailové adresy, jinak se vyskočí okno s upozorněním, které vám připomene, viz screenshot:

4.10 Ověření dat vynutí pouze zadání IP adres

V této části představím několik rychlých triků, jak nastavit ověřování dat tak, aby přijímalo pouze IP adresy v rozsahu buněk.

Vynutit pouze formát adres IP pomocí funkce ověření dat

Povolit zadávat do konkrétního rozsahu buněk pouze IP adresy, postupujte takto:

1. Vyberte buňky, do kterých chcete zadat pouze IP adresu, a poté klikněte Data > Ověření dat > Ověření dat.

2. Ve vyskočené Ověření dat v dialogovém okně pod Nastavení na kartě proveďte následující operace:

vybrat Zvyk z povolit rozbalovací seznam.
A pak zadejte níže uvedený vzorec do Vzorec Textové pole.
=AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))
klikněte OK zavřete toto dialogové okno.

Poznámka: Ve výše uvedeném vzorci A2 je první buňka sloupce, který chcete použít.

3. Pokud nyní do buňky zadáte neplatnou adresu IP, vyskočí pole s upozorněním, které vám připomene následující obrázek obrazovky:

Vynutit pouze formát IP adres pomocí kódu VBA

Zde také následující kód VBA může pomoci umožnit zadávání pouze IP adres a omezit další zadávání, proveďte prosím toto:

1. Klikněte pravým tlačítkem na kartu listu a klikněte na Zobrazit kód z kontextového menu, v úvodu Microsoft Visual Basic pro aplikace zkopírujte do něj níže uvedený kód VBA.

Kód VBA: ověřte buňky tak, aby přijímaly pouze IP adresy

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by ExtendOffice
Dim xArrIp() As String
Dim xIntIP1, xIntIP2, xIntIP3, xIntIP4 As Integer
If Intersect(Target, Range("A2:A10")) Is Nothing Then
    Exit Sub
Else
    If Target = "" Then
        Exit Sub
    End If
    xArrIp = Split(Target.Text, ".")
    If UBound(xArrIp) <> 3 Then
        GoTo EIP
    Else
    xIntIP1 = CInt(xArrIp(0))
    xIntIP2 = CInt(xArrIp(1))
    xIntIP3 = CInt(xArrIp(2))
    xIntIP4 = CInt(xArrIp(3))
    If (xIntIP1 < 1) Or (xIntIP1 > 255) _
    Or (xIntIP2 < 1) Or (xIntIP2 > 255) _
    Or (xIntIP3 < 1) Or (xIntIP3 > 255) _
    Or (xIntIP4 < 1) Or (xIntIP4 > 255) Then
    GoTo EIP
     End If
    End If
End If
Exit Sub
EIP:
    MsgBox "Please enter correct IP address"
    Target = ""
End Sub

Poznámka: Ve výše uvedeném kódu, A2: A10 je rozsah buněk, který chcete přijímat pouze IP adresy.

2. Poté tento kód uložte a zavřete, nyní lze do konkrétních buněk zadat pouze správné IP adresy.

Vynutit pouze formát IP adres se snadnou funkcí

Pokud máte Kutools pro Excel nainstalováno ve vašem sešitu, jeho Ověřte IP adresu Tato funkce vám také může pomoci vyřešit tento úkol.

Po instalaci Kutools pro Excel, udělejte prosím toto:

1. Vyberte buňky, pro které chcete zadat pouze IP adresy, a poté klikněte Kutools > Zabraňte psaní > Ověřte IP adresu. Viz snímek obrazovky:

2. Po použití této funkce nyní lze zadat pouze IP adresu, v opačném případě vyskočí okno s upozorněním, které vám připomene, viz screenshot:

4.11 Ověření dat omezuje hodnoty, které překračují celkovou hodnotu

Předpokládejme, že máte měsíční zprávu o výdajích a celkový rozpočet je 18000 18000 $, nyní potřebujete, aby celková částka v seznamu výdajů nepřekročila přednastavenou celkovou částku XNUMX XNUMX $, jak ukazuje níže uvedený snímek obrazovky. V tomto případě můžete vytvořit pravidlo pro ověření dat pomocí funkce SUM, abyste zabránili překročení součtu hodnot přednastaveným součtem.

1. Vyberte seznam buněk, kde chcete omezit hodnoty.

2. Pak klikněte na tlačítko Data > Ověření dat > Ověření dat, ve vyskakovacím okně Ověření dat v dialogovém okně pod Nastavení na kartě proveďte následující operace:

vybrat Zvyk z povolit rozbalovací seznam.
A pak zadejte níže uvedený vzorec do Vzorec Textové pole.
=SUM($B$2:$B$7)<=18000
klikněte OK zavřete toto dialogové okno.

Poznámka: Ve výše uvedeném vzorci, B2: B7 je rozsah buněk, které chcete omezit.

3. Nyní při zadávání hodnot v rozsahu B2: B7, pokud je součet hodnot menší než 18000 18000 $, ověření projde. Pokud celková hodnota přesáhne XNUMX XNUMX $, vyskočí vám varovná zpráva, která vám to připomene.

4.12 Ověření dat omezuje vstup buňky na základě jiné buňky

Pokud chcete omezit zadávání dat v seznamu buněk na základě hodnoty v jiné buňce, může tuto úlohu pomoci vyřešit také funkce Ověření dat. Pokud je například buňkou C1 text „Ano“, do rozsahu A2: A9 je možné zadat cokoli, ale pokud je buňka C1 jiným textem, do pole A2: A9 není možné zadat nic, jak je uvedeno níže. :

Chcete -li toto řešení vyřešit, postupujte takto:

1. Vyberte seznam buněk, kde chcete omezit hodnoty.

vybrat Zvyk z povolit rozbalovací seznam.
A pak zadejte níže uvedený vzorec do Vzorec Textové pole.
=$C$1="Yes"
klikněte OK zavřete toto dialogové okno.

Poznámka: Ve výše uvedeném vzorci, C1 je buňka obsahovat konkrétní text, který chcete použít, a text „Ano”Je text, na základě kterého chcete buňky omezit, změňte je prosím podle potřeby.

3. Pokud má buňka C1 text „Ano“, lze cokoli zadat do rozsahu A2: A9, pokud má buňka C1 jiný text, nebudete moci zadat žádnou hodnotu, viz níže ukázka:

4.13 Ověření dat umožňuje zadat pouze pracovní dny nebo víkendy

Pokud potřebujete do seznamu buněk zadat pouze všední dny (od pondělí do pátku) nebo víkendy (sobota a neděle), Ověření dat také vám může pomoci, proveďte prosím následující kroky:

1. Vyberte seznam buněk, do kterých chcete zadat všední nebo všední dny.

vybrat Zvyk z povolit rozbalovací seznam.
A poté zadejte jeden z níže uvedených vzorců do Vzorec textové pole, jak potřebujete.
=WEEKDAY(A2,2)<6 (allow only weekdays)
=WEEKDAY(A2,2)>5 (allow only weekends)
klikněte OK zavřete toto dialogové okno.

Poznámka: Ve výše uvedeném vzorci, A2 je první buňka sloupce, který chcete použít.

3. Nyní můžete do konkrétních buněk zadat pouze datum dne v týdnu nebo víkendu na základě vaší potřeby.

4.14 Ověření dat umožňuje zadané datum na základě dnešního data

Někdy může být nutné povolit v seznamu buněk pouze data větší nebo menší než dnes. The Ověření dat funkce s DNES funkce vám může udělat laskavost. Postupujte prosím takto:

1. Vyberte seznam buněk, do kterého chcete zadat pouze budoucí datum (datum větší než dnes).

vybrat Zvyk z povolit rozbalovací seznam.
A pak zadejte níže uvedený vzorec do Vzorec Textové pole.
=A2>Today()
klikněte OK zavřete toto dialogové okno.

Poznámka: Ve výše uvedeném vzorci, A2 je první buňka sloupce, který chcete použít.

3. Nyní lze do buněk zadat pouze data větší než dnešní, v opačném případě vyskočí pole s upozorněním, které vám připomene, viz snímek obrazovky:

Tip:

1. Aby bylo možné zadat minulé datum (datum menší než dnes), použijte při ověřování údajů níže uvedený vzorec:

=A2<Today()

2. Umožněte zadat datum v určitém rozsahu dat, například data v příštích 30 dnech, zadejte do Ověření dat následující vzorec:

=AND(A2>TODAY(),A2<=(TODAY()+30))

4.15 Ověření dat umožňuje zadaný čas na základě aktuálního času

Pokud chcete například ověřit data na základě aktuálního času, lze do buněk zadat pouze časy před nebo po aktuálním čase. Můžete si vytvořit vlastní vzorec pro ověření dat, postupujte takto:

1. Vyberte seznam buněk, do kterého chcete zadat pouze časy před nebo po aktuálním čase.

vybrat Čas z povolit rozbalovací seznam.
Pak zvolte méně než povolit pouze časy před aktuálním časem, popř větší než povolit časy po aktuálním čase, jak potřebujete z Data drop down.
A pak, v Čas ukončení or Začátek zadejte následující vzorec:
=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
klikněte OK zavřete toto dialogové okno.

Poznámka: Ve výše uvedeném vzorci, A2 je první buňka sloupce, který chcete použít.

3. Nyní lze do konkrétních buněk zadat pouze časy před aktuálním časem nebo po něm.

4.16 Validace dat datum konkrétního nebo aktuálního roku

Chcete -li povolit zadávání pouze dat v určitém roce nebo aktuálním roce, můžete použít ověření dat pomocí vlastního vzorce založeného na funkci ROK.

1. Vyberte seznam buněk, do kterého chcete zadat pouze data v určitém roce.

vybrat Zvyk z povolit rozbalovací seznam.
A pak zadejte níže uvedený vzorec do Vzorec Textové pole.
=YEAR(A2)=2020
klikněte OK zavřete toto dialogové okno.

Poznámka: Ve výše uvedeném vzorci, A2 je první buňka sloupce, který chcete použít, 2020 je číslo roku, které chcete omezit.

3. A pak lze zadat pouze data v roce 2020, pokud ne, vyskočí okno s varovnou zprávou, jak ukazuje níže uvedený snímek obrazovky:

Tip:

Chcete -li povolit pouze data v aktuálním roce, můžete do ověření dat použít následující vzorec:

=YEAR(A2)=YEAR(TODAY())

4.17 Ověření dat datum v aktuálním týdnu nebo měsíci

Pokud chcete uživateli umožnit zadávat data aktuálního týdne nebo měsíce do konkrétních buněk, tato část představí některé vzorce pro řešení tohoto úkolu v aplikaci Excel.

Povolit zadat datum aktuálního týdne

1. Vyberte seznam buněk, do kterého chcete zadat pouze data v aktuálním týdnu.

vybrat Datum z povolit rozbalovací seznam.
A pak si vyberte mezi z Data drop down.
v Datum zahájení do textového pole zadejte tento vzorec: = DNES ()-TÝDEN (DNES (), 3)
v Datum ukončení do textového pole zadejte tento vzorec: = DNES ()-TÝDEN (DNES (), 3) +6
Konečně klikněte na tlačítko OK .

3. Poté lze zadat pouze data v aktuálním týdnu, ostatním datům bude zabráněno, jak ukazuje níže uvedený snímek obrazovky:

Povolit zadat datum aktuálního měsíce

Chcete -li povolit zadávání pouze dat aktuálního měsíce, postupujte takto:

1. Vyberte seznam buněk, do kterého chcete zadat pouze data v aktuálním měsíci.

2. Pak klikněte na tlačítko Data > Ověření dat > Ověření dat, ve vyskakovacím okně Ověření dat v dialogovém okně pod Nastavení na kartě proveďte následující operace:

vybrat Datum z povolit rozbalovací seznam.
A pak si vyberte mezi Data drop down.
v Datum zahájení do textového pole zadejte tento vzorec: = DATUM (ROK (DNES ()), MĚSÍC (DNES ()), 1)
v Datum ukončení do textového pole zadejte tento vzorec: = DATE (YEAR (TODAY ()), MONTH (TODAY ()), DAY (DATE (YEAR (TODAY ()), MONTH (DODAY ())+1,1) -1))
Konečně klikněte na tlačítko OK .

3. Od nynějška lze do vybraných buněk zadávat pouze data aktuálního měsíce.

5. Jak upravit validaci dat v Excelu?

Chcete -li upravit nebo změnit stávající pravidlo ověřování údajů, postupujte takto:

1. Vyberte libovolnou z buněk s pravidlem ověření dat.

2. Potom klepněte na tlačítko Data > Ověření dat > Ověření dat přejděte na Ověření dat v dialogovém okně upravte nebo změňte pravidla podle potřeby a poté zaškrtněte Použít tyto změny na všechny ostatní buňky se stejným nastavením možnost použít toto nové pravidlo na všechny ostatní buňky s původními ověřovacími kritérii. Viz screenshot:

3, klikněte OK pro uložení změn.

6. Jak najít a vybrat buňky s validací dat v Excelu?

Pokud jste v listu vytvořili více pravidel pro ověření dat, musíte nyní najít a vybrat buňky, které používaly pravidla pro ověřování dat, Přejít na speciální Příkaz vám může pomoci vybrat všechny druhy ověření dat nebo konkrétní typ ověření dat.

1. Aktivujte list, který chcete najít, a vyberte buňky s ověřením dat.

2. Potom klepněte na tlačítko Domů > Najít a vybrat > Přejít na speciální, viz screenshot:

3. V Přejít na speciální dialogové okno vyberte Ověření dat > Zobrazit vše, viz screenshot:

4. A všechny buňky s validací dat byly vybrány najednou v aktuálním listu.

Tipy: Pokud chcete vybrat pouze jeden konkrétní typ ověření dat, nejprve vyberte jednu buňku obsahující ověření určitých dat, které chcete zjistit, a poté přejděte na Přejít na speciální dialogové okno a vyberte Ověření dat > Stejný.

7. Jak zkopírovat pravidlo ověření dat do jiných buněk?

Předpokládejme, že jste vytvořili pravidlo pro ověření dat pro seznam buněk a nyní musíte stejné pravidlo pro ověření dat použít i pro jiné buňky. Místo toho, abyste znovu vytvořili pravidlo, můžete stávající pravidlo rychle a snadno zkopírovat a vložit do jiných buněk.

1. Klepnutím vyberte jednu buňku s ověřovacím pravidlem, které chcete použít, a poté stiskněte Ctrl + C kopírovat.

2. Poté vyberte buňky, které chcete ověřit, a vyberte více nesousedících buněk stisknutím a podržením tlačítka Ctrl při výběru buněk.

3. A pak klikněte pravým tlačítkem na výběr, vyberte Vložit jinak možnost, viz screenshot:

4. V Vložit jinak dialogové okno vyberte Potvrzení možnost, viz screenshot:

5, klikněte OK tlačítko, nyní je ověřovací pravidlo zkopírováno do nových buněk.

8. Jak použít ověření dat k zakroužkování neplatných záznamů v Excelu?

Někdy může být nutné pro existující data vytvořit pravidla pro ověření dat, v takovém případě se v oblasti buněk mohou objevit některá neplatná data. Jak zkontrolovat neplatná data a upravit je? V aplikaci Excel můžete použít Zakroužkujte neplatná data funkce pro zvýraznění neplatných dat červeným kruhem.

Chcete -li zakroužkovat potřebná neplatná data, měli byste použít Ověření dat funkce pro nastavení pravidla pro rozsah dat. Proveďte prosím následující kroky:

1. Vyberte rozsah dat, do kterého chcete zakroužkovat neplatná data.

2. Potom klepněte na tlačítko Data > Ověření dat > Ověření dat, V roce Ověření dat v dialogovém okně nastavte ověřovací pravidlo podle potřeby, například zde ověřím hodnoty větší než 500, viz screenshot:

3. Potom klepněte na tlačítko OK zavřete dialogové okno. Po nastavení pravidla ověření dat klikněte na Data > Ověření dat > Zakroužkujte neplatná data, pak byly všechny neplatné hodnoty, které jsou menší než 500, zakroužkovány červeným oválem. Viz screenshoty:

Poznámky:

1. Jakmile opravíte neplatná data, červený kruh automaticky zmizí.
2. To Zakroužkujte neplatná data funkce může zakroužkovat maximálně 255 buněk. Když uložíte aktuální sešit, všechny červené kruhy budou odstraněny.
3. Tyto kruhy nelze tisknout.
4. Červené kruhy můžete také odstranit kliknutím Data > Ověření dat > Vymazat ověřovací kruhy.

9. Jak odebrat validaci dat v Excelu?

Následující metody vám mohou pomoci, pokud chcete odebrat pravidla ověřování dat z řady buněk, aktuálního listu nebo celého sešitu.

Odeberte ověření dat ve vybraném rozsahu pomocí funkce ověření dat

1. Vyberte buňky s ověřením dat, které chcete odebrat.

2. Pak klikněte na tlačítko Data > Ověření dat > Ověření dat, ve vyskakovacím dialogovém okně pod Nastavení klepněte na kartu Vymazat vše tlačítko, viz screenshot:

3. Potom klepněte na tlačítko OK toto dialogové okno zavřete. A pravidlo pro ověření dat aplikované na vybraný rozsah bylo najednou odstraněno.

Tipy: Chcete -li odebrat ověření dat z aktuálního listu, vyberte prosím nejprve celý list a poté použijte výše uvedené kroky.

Odeberte ověření dat ve vybraném rozsahu pomocí praktické funkce

Pokud máte Kutools pro Excel, to je Vymažte omezení ověření dat Funkce také může pomoci odstranit pravidla pro ověření dat z vybraného rozsahu nebo celého listu.

Po instalaci Kutools pro Excel, udělejte prosím toto:

1. Vyberte rozsah buněk nebo celý list obsahuje ověření dat, které chcete odebrat.

2. Potom klepněte na tlačítko Kutools > Zabraňte psaní > Vymažte omezení ověření dat, viz screenshot:

3. Ve vyskakovacím okně výzvy klikněte na OK, a pravidlo ověření dat bylo vymazáno, jak potřebujete.

Odeberte ověřování dat ze všech listů s kódem VBA

Chcete-li odstranit pravidla ověřování dat z celého sešitu, budou výše uvedené metody časově náročné, pokud existuje mnoho pracovních listů. Zde vám níže uvedený kód pomůže rychle se s tímto úkolem vypořádat.

1. Podržte ALT + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.

2. Poté klikněte na Vložit > Modula vložte následující makro do souboru Modul okno.

Kód VBA: Odeberte pravidla ověřování dat ze všech listů:

Sub RemoveDataValidation()
'Updateby Extendoffice
  Dim xwsh As Worksheet
  For Each xwsh In ActiveWorkbook.Worksheets
    xwsh.Cells.Validation.Delete
  Next xwsh
End Sub

3. Poté stiskněte tlačítko F5 klíč ke spuštění tohoto kódu a všechna pravidla pro ověření dat byla okamžitě odstraněna z celého sešitu.

Nejlepší kancelářské nástroje produktivity

Kutools pro Excel řeší většinu vašich problémů a zvyšuje vaši produktivitu o 80%

Super Formula Bar (snadno upravit více řádků textu a vzorce); Rozložení pro čtení (snadno číst a upravovat velké množství buněk); Vložit do filtrovaného rozsahu...
Sloučit buňky / řádky / sloupce a uchovávání údajů; Rozdělit obsah buněk; Zkombinujte duplicitní řádky a součet / průměr... Zabraňte duplicitním buňkám; Porovnat rozsahy...
Vyberte možnost Duplikovat nebo Jedinečný Řádky; Vyberte prázdné řádky (všechny buňky jsou prázdné); Super hledání a fuzzy hledání v mnoha sešitech; Náhodný výběr ...
Přesná kopie Více buněk beze změny odkazu na vzorec; Automaticky vytvářet reference do více listů; Vložte odrážky, Zaškrtávací políčka a další ...
Oblíbené a rychlé vkládání vzorců„Rozsahy, grafy a obrázky; Šifrovat buňky s heslem; Vytvořte seznam adresátů a posílat e-maily ...
Extrahujte text, Přidat text, Odebrat podle pozice, Odebrat mezeru; Vytváření a tisk mezisoučtů stránkování; Převod mezi obsahem buněk a komentáři...
Super filtr (uložit a použít schémata filtrů na jiné listy); Rozšířené řazení podle měsíce / týdne / dne, frekvence a dalších; Speciální filtr tučnou kurzívou ...
Kombinujte sešity a pracovní listy; Sloučit tabulky na základě klíčových sloupců; Rozdělte data do více listů; Dávkový převod xls, xlsx a PDF...
Seskupování kontingenčních tabulek podle číslo týdne, den v týdnu a další ... Zobrazit odemčené, zamčené buňky různými barvami; Zvýrazněte buňky, které mají vzorec / název...

Čtěte více... Plné funkce 30denní bezplatná zkušební verze ... Nákup...

Karta Office - přináší do Office rozhraní s kartami a usnadňuje vám práci

Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!

Čtěte více... Plné funkce 30denní bezplatná zkušební verze ... Nákup...

Comments (1)

No ratings yet. Be the first to rate!

Ghomergher

about 2 years ago

#36199

This comment was minimized by the moderator on the site

Thanks For Sharing this Great Information. I loved it.

Report

There are no comments posted here yet

Please leave your comments in English

Posting as Guest

Name (Required)

Email (Required)

Rate this post:

Reset

0 Characters

changelog

Výukové programy k funkcím

Získejte pomoc?