Прескочи на основното съдържание
 

Валидиране на данни в Excel: добавяне, използване, копиране и премахване на валидиране на данни в Excel

Автор: Xiaoyang Последна промяна: 2022-12-15

В excel проверката на данните е полезна функция, която можете да използвате, за да ограничите това, което потребителят може да въведе в клетка. Например, функцията за валидиране на данни може да ви помогне да ограничите дължината на текстови низове или текст, започващ/завършващ със специфични знаци, или уникални стойности, които трябва да бъдат въведени и т.н.

В този урок ще говорим за това как да добавите, използвате и премахнете валидирането на данни в Excel, някои основни и разширени операции на тази функция също ще бъдат демонстрирани подробно.

Съдържание:

1. Какво представлява валидирането на данни в Excel?

2. Как да добавя валидиране на данни в Excel?

3. Основни примери за валидиране на данни

4. Разширени персонализирани правила за валидиране на данни

5. Как да редактирам валидирането на данни в Excel?

6. Как да намерите и изберете клетки с валидиране на данни в Excel?

7. Как да копирам правилото за валидиране на данни в други клетки?

8. Как да използвате проверка на данни, за да оградите невалидни записи в Excel?

9. Как да премахнете валидирането на данни в Excel?

1. Какво представлява валидирането на данни в Excel?

- Потвърждаване на данните може да ви помогне да ограничите въведеното съдържание във вашия работен лист. Обикновено можете да създадете някои правила за валидиране за предотвратяване или разрешаване само на някакъв вид данни да бъдат въведени в списък с избрани клетки.

Някои основни употреби на функцията за проверка на данни:

  • 1. Всяка стойност: не се извършва проверка, можете да въведете всичко в посочените клетки.
  • 2. Цяла стойност: разрешени са само цели числа.
  • 3. Десетични: позволява да се въвеждат цели числа, както и десетични знаци.
  • 4. Списък: само стойности от предварително дефинирания списък могат да бъдат въвеждани или избирани. Стойностите се показват в падащ списък.
  • 5. Дата: разрешени са само дати.
  • 6. път: разрешени са само времена.
  • 7. Дължина на текста: позволява да се въведе само определена дължина на текста.
  • 8. По поръчка: създайте персонализирани правила за формула за валидиране на въведеното от потребителите.

2. Как да добавя валидиране на данни в Excel?

В работен лист на Excel можете да добавите валидиране на данни със следните стъпки:

1. Изберете списък с клетки, където искате да зададете проверката на данните, и след това щракнете Дата > Потвърждаване на данните > Потвърждаване на данните, вижте екранна снимка:

2. В Потвърждаване на данните диалогов прозорец, под Настройки моля, създайте свои собствени правила за валидиране. в полетата за критерии можете да посочите някой от следните типове:

  • Ценности: Въведете директно числа в полетата за критерии;
  • Референтна клетка: Препратка към клетка в работния лист или друг работен лист;
  • формули: Създайте по-сложни формули като условия.

Като пример, ще създам правило, което позволява да се въвеждат само цели числа между 100 и 1000, тук задайте критериите, както е показано на екранната снимка по-долу:

3. След като конфигурирате условията, можете да отидете на Input съобщение or Предупреждение за грешка раздела, за да зададете съобщението за въвеждане или предупреждението за грешка за клетките за валидиране, както желаете. (Ако не искате да зададете предупреждението, моля, щракнете OK за да завършите директно.)

3.1) Добавете съобщение за въвеждане (по избор):

Можете да създадете съобщение, което се появява, когато изберете клетка, съдържаща валидиране на данни. Това съобщение помага да се напомни на потребителя какво може да въведе в клетката.

Отидете в Input съобщение и направете следното:

  • Проверете Показване на съобщение за въвеждане, когато клетката е избрана опция;
  • Въведете заглавието и съобщението за напомняне, което искате в съответните полета;
  • Кликнете OK за да затворите този диалогов прозорец.

Сега, когато изберете валидирана клетка, ще се покаже поле за съобщение, както следва:

3.2) Създайте смислени съобщения за грешка (по избор):

В допълнение към създаването на съобщение за въвеждане, можете също да показвате предупреждения за грешка, когато в клетка са въведени невалидни данни с валидиране на данни.

Отидете в Предупреждение за грешка раздела на Потвърждаване на данните диалогов прозорец, моля, направете следното:

  • Проверете Показване на предупреждение за грешка след въвеждане на невалидни данни опция;
  • в Стил падащ списък, изберете желания тип сигнал, от който се нуждаете:
    • Стоп (по подразбиране): Този тип предупреждение не позволява на потребителите да въвеждат невалидни данни.
    • предупреждение: Предупреждава потребителите, че данните са невалидни, но не предотвратява въвеждането им.
    • Информация: Информира потребителите само за въвеждане на невалидни данни.
  • Въведете заглавието и предупредителното съобщение, което искате в съответните полета;
  • Кликнете OK за да затворите диалоговия прозорец.

И сега, когато въведете невалидна стойност, полето за предупреждение за съобщение ще се появи, както е показано на екранната снимка по-долу:

Спиране опция: Можете да щракнете Опитайте отново за да въведете друга стойност или Отказ за премахване на записа.

предупреждение опция: Щракнете Да за въвеждане на невалиден запис, Не да го модифицирате, или Отказ за премахване на записа.

Информация опция: Щракнете OK за въвеждане на невалиден запис или Отказ за премахване на записа.

Забележка: Ако не зададете собствено персонализирано съобщение в Предупреждение за грешка кутия, по подразбиране Спиране ще се покаже полето за предупреждение, както е показано по-долу:

3. Основни примери за валидиране на данни

Когато използвате тази функция за валидиране на данни, има осигурени 8 вградени опции, за да зададете валидирането на данните. Като например: всяка стойност, цели числа и десетични знаци, дата и час, списък, дължина на текста и персонализирана формула. В този раздел ще обсъдим как да използвате някои от вградените опции в Excel?

3.1 Проверка на данните за цели числа и десетични знаци

1. Изберете списък с клетки, където искате да разрешите само цели числа или десетични знаци, и след това щракнете Дата > Потвърждаване на данните > Потвърждаване на данните.

2. В Потвърждаване на данните диалогов прозорец, под Настройки раздел, извършете следните операции:

  • Изберете съответния елемент Цяло число or десетичен в Позволете падаща кутия.
  • И след това изберете един от критериите, от които се нуждаете в Дата кутия (В този пример избирам между опция).
  • Съвети: Критериите съдържат: между, не между, равно на, не е равно на, по-голямо от, по-малко от, по-голямо от или равно на, по-малко от или равно на.
  • След това въведете Минимум намлява Максимален стойностите, от които се нуждаете (искам числата между 0 и 1 00).
  • Най-накрая щракнете OK бутон.

3. Сега само целите числа от 0 до 100 могат да се въвеждат в избраните от вас клетки.

3.2 Проверка на данните за дата и час

За да потвърдите конкретна дата или час, които трябва да бъдат въведени, е лесно, като използвате това Потвърждаване на данните, моля, направете следното:

1. Изберете списък с клетки, където искате да разрешите само конкретните дати или часове, и след това щракнете Дата > Потвърждаване на данните > Потвърждаване на данните.

2. В Потвърждаване на данните диалогов прозорец, под Настройки раздел, извършете следните операции:

  • Изберете съответния елемент Дата or Време в Позволете падаща кутия.
  • И след това изберете един от критериите, от които се нуждаете в Дата кутия (Тук избирам по-голяма от опция).
  • Съвети: Критериите съдържат: между, не между, равно на, не е равно на, по-голямо от, по-малко от, по-голямо от или равно на, по-малко от или равно на.
  • След това въведете Начална дата имате нужда (искам датите, по-големи от 8 г.).
  • Най-накрая щракнете OK бутон.

3. Сега в избраните от вас клетки е разрешено да се въвеждат само дати, по-големи от 8 г.

3.3 Проверка на данни за дължина на текста

Ако трябва да ограничите броя на знаците, които могат да бъдат въведени в клетка. Например, за да ограничите съдържанието до не повече от 10 знака за определен диапазон, това Потвърждаване на данните също може да ви направи услуга.

1. Изберете списък с клетки, където искате да ограничите дължината на текста, и след това щракнете Дата > Потвърждаване на данните > Потвърждаване на данните.

2. В Потвърждаване на данните диалогов прозорец, под Настройки раздел, извършете следните операции:

  • Изберете Дължина на текста от Позволете падаща кутия.
  • И след това изберете един от критериите, от които се нуждаете в Дата кутия (В този пример избирам по-малко от опция).
  • Съвети: Критериите съдържат: между, не между, равно на, не е равно на, по-голямо от, по-малко от, по-голямо от или равно на, по-малко от или равно на.
  • След това въведете Максимален номер, който трябва да ограничите (искам дължината на текста да не надвишава 10 знака).
  • Най-накрая щракнете OK бутон.

3. Сега избраните клетки позволяват само въвеждане на текстов низ, по-малък от 10 знака.

3.4 Списък за валидиране на данни (падащ списък)

С този мощен Потвърждаване на данните можете бързо и лесно да създадете падащ списък в клетките. Моля, направете следното:

1. Изберете целевите клетки, където да вмъкнете падащия списък, и след това щракнете Дата > Потвърждаване на данните > Потвърждаване на данните.

2. В Потвърждаване на данните диалогов прозорец, под Настройки раздел, извършете следните операции:

  • Изберете списък от Позволете падащ списък.
  • в източник текстово поле, въведете елементите от списъка директно разделени със запетаи. Например, за да ограничите въведеното от потребителя до три възможности за избор, въведете Not Started, In Progress, Completed или можете да изберете списък от клетки, съдържащи стойностите, въз основа на които да вмъкнете падащото меню.
  • Най-накрая щракнете OK бутон.

3. Сега падащият списък е създаден в клетките, както е показано на екранната снимка по-долу:

Кликнете, за да научите по-подробна информация за падащия списък...

4. Разширени персонализирани правила за валидиране на данни

В този раздел ще ви представя как да създадете някои усъвършенствани персонализирани правила за валидиране на данни, за да решите вашите проблеми, като например: създаване на формули за валидиране, които да позволяват само числа или текстови низове, само уникални стойности, само посочени телефонни номера, имейл адреси и т.н. .

4.1 Валидирането на данни позволява само числа или текстове

 Разрешаване на въвеждане само на числа с функцията за проверка на данните

За да разрешите само числа в диапазон от клетки, моля, направете следното:

1. Изберете диапазон от клетки, в които искате да се въвеждат само числа.

2. Щракнете Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки моля, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • И след това въведете тази формула: =ISNUMBER(A2) в Формула текстово поле. (A2 е първата клетка от избрания диапазон, който искате да ограничите)
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

3. Отсега нататък в избраните клетки могат да се въвеждат само числа.

Забележка: Това ISNUMBER позволява всякакви числови стойности в валидирани клетки, включително цели числа, десетични знаци, дроби, дати и часове.

 Разрешете въвеждането само на текстови низове с функцията за проверка на данните

За да ограничите записите в клетки само до текст, можете да използвате Потвърждаване на данните функция с персонализирана формула, базирана на ISTEXT функция, моля, направете следното:

1. Изберете диапазон от клетки, в които искате да се въвеждат само текстови низове.

2. Щракнете Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки моля, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • И след това въведете тази формула: =ISTEXT(A2) в Формула текстово поле. (A2 е първата клетка от избрания диапазон, който искате да ограничите)
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

3. Сега, когато въвеждате данни в конкретните клетки, могат да бъдат разрешени само данни в текстов формат.

4.2 Валидирането на данни позволява само буквено-цифрови стойности

За някои цели просто искате да разрешите въвеждането на букви и цифрови стойности, но да ограничите специалните символи като ~,%,$, интервал и т.н., този раздел ще ви представи някои трикове.

 Разрешете само буквено-цифрови стойности с функцията за проверка на данните

За да предотвратите специалните знаци, но да позволите само буквено-цифрови стойности, трябва да създадете персонализирана формула в Потвърждаване на данните функция, моля, направете следното:

1. Изберете диапазон от клетки, в които искате да се въвеждат само буквено-цифрови стойности.

2. Щракнете Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки моля, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • След това въведете формулата по-долу в Формула текстово поле.
  • =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

Забележка: В горните формули, A2 е първата клетка от избрания диапазон, който искате да ограничите.

3. Сега е разрешено да се въвеждат само букви и цифрови стойности, а специалните знаци ще бъдат ограничени при въвеждане, както е показано на екранната снимка по-долу:

 Разрешете само буквено-цифрови стойности с невероятна функция

Може горната формула да е сложна за разбиране и запомняне, тук ще представя една удобна функция – Предотвратяване на писане of Kutools за Excel, с тази функция можете бързо да разрешите тази задача с лекота.

След инсталиране Kutools за Excel, моля, направете следното:

1. Изберете диапазон от клетки, в които искате да се въвеждат само буквено-цифрови стойности.

2, След това кликнете върху Kutools > Предотвратяване на писане > Предотвратяване на писане, вижте екранна снимка:

3. В изскочилото Предотвратяване на писане изберете диалоговия прозорец Предотвратете въвеждането на специални знаци опция, вижте екранна снимка:

4, След това кликнете върху Ok и в следващите подканващи полета щракнете върху Да > OK да завърши операцията. Сега в избраните клетки са разрешени само азбуките и цифровите стойности, вижте екранната снимка:

4.3 Проверката на данните позволява текстове да започват или завършват с определени знаци

Ако всички стойности в определен диапазон трябва да започват или завършват с конкретен знак или подниз, можете да използвате проверка на данни с персонализирана формула, базирана на функцията ТОЧНО, НАЛЯВО, НАДЯСНО или COUNTIF.

 Разрешете текстовете да започват или завършват с конкретни знаци само с едно условие

Например, искам текстовете да започват или завършват с „CN“, когато въвеждате текстовите низове в конкретни клетки, моля, направете следното:

1. Изберете диапазон от клетки, които позволяват само текстове, които започват или завършват с определени знаци.

2, След това кликнете върху Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки моля, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • След това въведете формулата по-долу в Формула текстово поле.
  • Begin with: =EXACT(LEFT(A2,2),"CN")
    End with: =EXACT(RIGHT(A2,2),"CN")
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

Забележка: В горните формули, A2 е първата клетка от избрания диапазон, числото 2 е броят знаци, които сте посочили, CN е текстът, с който искате да започнете или завършите.

3. Отсега нататък в избраните клетки може да се въвежда само текстов низ, който започва или завършва с посочените знаци. В противен случай ще се появи предупредителен сигнал, за да ви напомни, както е показано на екранната снимка по-долу:

Съвет: Горните формули са чувствителни към главни и малки букви, ако не се нуждаете от чувствителни към малки и главни букви, моля, приложете следните CONTIF формули:

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

Забележка: Звездицата * е заместващ знак, който съответства на един или повече символа.

 Разрешаване на текстовете да започват или завършват с конкретни знаци с множество критерии (ИЛИ логика)

Например, ако искате текстовете да започват или завършват с „CN“ или „UK“, както е показано на екранната снимка по-долу, трябва да добавите друго копие на ТОЧНО, като използвате знак плюс (+). Моля, направете следните стъпки:

1. Изберете диапазон от клетки, който позволява само текстове, които започват или завършват с множество критерии.

2, След това кликнете върху Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки моля, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • След това въведете формулата по-долу в Формула текстово поле.
  • Begin with: =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")
    End with: =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

Забележка: В горните формули, A2 е първата клетка от избрания диапазон, числото 2 е броят знаци, които сте посочили, CN намлява UK са конкретните текстове, с които искате да започнете или завършите.

3. Сега само текстовият низ, започващ или завършващ с посочените знаци, може да бъде въведен в избраните клетки.

Съвет: За да пренебрегнете чувствителните към главни и малки букви, моля, приложете следните CONTIF формули:

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

Забележка: Звездицата * е заместващ знак, който съответства на един или повече символа.

4.4 Вписванията за разрешаване на валидиране на данни трябва да съдържат / не трябва да съдържат конкретен текст

В този раздел ще говоря за това как да приложа валидирането на данни, за да позволи стойностите да съдържат или не трябва да съдържат един конкретен подниз или един от много поднизове в Excel.

 Разрешените записи трябва да съдържат един или един от много конкретни текстове

Разрешените записи трябва да съдържат един конкретен текст

За да разрешите записи, които съдържат конкретен текстов низ, например всички въведени стойности трябва да съдържат текста „KTE“, както е показано на екранната снимка по-долу, можете да приложите валидирането на данните с персонализирана формула, базирана на функциите FIND и ISNUMBER. Моля, направете следното:

1. Изберете диапазон от клетки, който позволява само текстове, които съдържат определен текст.

2, След това кликнете върху Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки моля, извършете следните операции:

  • Изберете Custom от падащия списък Разреши.
  • И след това въведете една от формулите по-долу в Формула текстово поле.
  • =ISNUMBER(FIND("KTE",A2))             (Case sensitive)
    =ISNUMBER(SEARCH("KTE",A2))         (Non case sensitive)
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

Забележка: В горните формули, A2 е първата клетка от избрания диапазон, текстът KTE е текстовият низ, който записите трябва да съдържат.

3. Сега, когато въведената стойност не съдържа проектирания текст, ще изскочи предупредително поле с подкана.

Разрешените записи трябва да съдържат един от много конкретни текстове

Горната формула работи само за един текстов низ, ако имате нужда някой от много текстови низове да бъде разрешен в клетките, както е показано на следната екранна снимка, трябва да използвате функциите SUMPRODUCT, FIND и ISNUMBER заедно, за да създадете формула.

1. Изберете диапазон от клетки, който позволява само текстове, които съдържат някой от многото елементи.

2, След това кликнете върху Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки моля, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • След това въведете една от формулите по-долу, както ви трябва в Формула текстово поле.
  • =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0                        (Case sensitive)
    =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0                   (Non case sensitive)
  • И след това щракнете OK за да затворите диалоговия прозорец.

Забележка: В горните формули, A2 е първата клетка от избрания диапазон, C2:C4 е списъкът със стойности, които искате да позволите на записите да съдържат някоя от тях.

3. И сега могат да бъдат въведени само записите, които съдържат някоя от стойностите в конкретния списък.

 Разрешените записи не трябва да съдържат един или един от много конкретни текстове

Разрешените записи не трябва да съдържат конкретен текст

За да потвърдите записите не трябва да съдържат специфичен текст, например, за да позволите стойности, които не трябва да съдържат текста „KTE“ в клетка, можете да използвате функциите ISERROR и FIND, за да създадете правило за валидиране на данни. Моля, направете следното:

1. Изберете диапазон от клетки, който позволява само текстове, които не съдържат определен текст.

2, След това кликнете върху Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки моля, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • И след това въведете една от формулите по-долу в Формула текстово поле.
  • =ISERROR(FIND("KTE",A2))                  (Case sensitive)
    =ISERROR(SEARCH("KTE",A2))                  (Non case sensitive)
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

Забележка: В горните формули, A2 е първата клетка от избрания диапазон, текстът KTE е текстовият низ, който записите не трябва да съдържат.

3. Сега записите, които съдържат конкретния текст, ще бъдат предотвратени за въвеждане.

Разрешените записи не трябва да съдържат един от много конкретни текстове

За да предотвратите въвеждането на един от многото текстови низове в списък, както е показано на екранната снимка по-долу, трябва да направите следното:

1. Изберете диапазон от клетки, които искате някои текстове да бъдат предотвратени.

2, След това кликнете върху Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки моля, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • След това въведете формулата по-долу в Формула текстово поле.
  • =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0                     (Case sensitive)
    =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0                 (Non case sensitive)
  • И след това щракнете OK за да затворите диалоговия прозорец.

Забележка: В горните формули, A2 е първата клетка от избрания диапазон, C2:C4 е списъкът със стойности, които искате да предотвратите, ако записите съдържат някоя от тях.

3. Отсега нататък записите, които съдържат някой от конкретните текстове, ще бъдат забранени за въвеждане.

4.5 Валидирането на данни позволява само уникални стойности

Ако искате да предотвратите въвеждането на дублирани данни в диапазон от клетки, този раздел ще представи някои бързи методи за решаване на тази задача в Excel.

 Разрешете само уникални стойности с функцията за проверка на данните

Обикновено функцията за проверка на данните с персонализирана формула, базирана на функцията COUNTIF, може да ви помогне, моля, изпълнете следните стъпки:

1. Изберете клетките или колоната, в които искате да бъдат въведени само уникални стойности.

2, След това кликнете върху Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки раздел, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • След това въведете формулата по-долу в Формула текстово поле.
  • =COUNTIF($A$2:$A$9,A2)=1
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

Забележка: В горната формула, A2: A9 е диапазонът от клетки, за които искате да разрешите само уникални стойности и A2 е първата клетка от избрания диапазон.

3. Сега само уникални стойности могат да бъдат въведени и ще изскочи предупредително съобщение, когато се въвеждат дублирани данни, вижте екранна снимка:

 Разрешете само уникални стойности с VBA код

Следният VBA код също може да ви помогне да предотвратите въвеждането на дублиращи се стойности, моля, направете следното:

1. Щракнете с десния бутон върху раздела на листа, който искате да разрешите само уникални стойности, и изберете Преглед на кода от контекстното меню, в изскачащия Microsoft Visual Basic за приложения прозорец, моля, копирайте и поставете следния код в празния модул:

VBA код: Позволете само уникални стойности в диапазон от клетки:

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

Забележка: В горния код, A1: A100 намлява A:A са клетките в колоната, които искате да предотвратите дублиране, моля, променете ги според вашите нужди.

2. След това запазете и затворете този код, сега, когато въвеждате дублираща се стойност в клетка A1: A100, се появява предупредително поле за подкана, както е показано на екранната снимка по-долу:

 Разрешете само уникални стойности с удобна функция

Ако имате Kutools за Excel, Със своята Предотвратяване на дублиране можете да зададете валидиране на данни, за да предотвратите дублиране за диапазон от клетки само с няколко кликвания.

След инсталиране Kutools за Excel, моля, направете следното:

1. Изберете диапазона от клетки, които искате да предотвратите дублиращи се стойности, но да разрешите само уникални данни.

2, След това кликнете върху Kutools > Предотвратяване на писане > Предотвратяване на дублиране, вижте екранна снимка:

3. И ще изскочи предупредително съобщение, за да ви напомни, че проверката на данните ще бъде премахната, ако приложите тази функция, щракнете Да и в следното подканващо поле щракнете OK, вижте екранни снимки:

4. Сега, когато въведете някои дублирани данни в посочените от вас клетки, се показва поле за подкана, за да ви напомни, че дублиращите се данни не са валидни, вижте екранна снимка:

4.6 Проверката на данните позволява само главни/малки/правилни букви

Тази проверка на данните е мощна функция, тя също може да помогне да се позволи на потребителя да въвежда само главни, малки или правилни записи в диапазон от клетки. Моля, направете следните стъпки:

1. Изберете диапазона от клетки, в които искате да се въвеждат само главни, малки или правилни букви.

2, След това кликнете върху Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки раздел, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • И след това въведете една от формулите по-долу, която ви трябва в Формула текстово поле.
  • =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)
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

Забележка: В горната формула, A2 е първата клетка от колоната, която искате да използвате.

3. Сега само записите, които отговарят на създаденото от вас правило, ще бъдат приети.

4.7 Валидирането на данни позволява стойности, които съществуват/не съществуват в друг списък

Да се ​​позволи на стойностите да съществуват или не съществуват в друг списък, за да бъдат въведени в диапазон от клетки, може да бъде болезнен проблем за повечето от нас. Всъщност можете да използвате функцията за валидиране на данни с проста формула, базирана на функцията COUNTIF, за да се справите с нея.

Например, искам само стойностите в диапазона C2:C4 да бъдат въведени в диапазон от клетки, както е показано на екранната снимка по-долу, за да разрешите тази задача, моля, направете следното:

1. Изберете диапазона от клетки, към който искате да приложите валидирането на данните.

2, След това кликнете върху Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки раздел, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • И след това въведете една от формулите по-долу, която ви трябва в Формула текстово поле.
  • =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)
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

Забележка: В горната формула, A2 е първата клетка от колоната, която искате да използвате, C2:C4 е списъкът със стойности, които искате да предотвратите или разрешите, ако записите са една от тях.

3. Сега записите отговарят само на създаденото от вас правило може да бъде въведено, други ще бъдат предотвратени.

4.8 Проверката на данните налага въвеждането само на формат на телефонен номер

Когато въвеждате информацията за служителите на вашата компания, една колона трябва да въведе телефонния номер, за да се гарантира бързото и точно въвеждане на телефонните номера, в този случай можете да зададете проверка на данните за телефонните номера. Например, просто искам телефонният номер като този формат (123) 456-7890 да бъде разрешен за въвеждане в работен лист, този раздел ще представи два бързи трика за решаване на тази задача.

 Принудителен само формат на телефонен номер с функцията за валидиране на данни

За да разрешите въвеждането само на конкретен формат на телефонен номер, моля, направете следното:

1. Изберете списъка с клетки, в които искате да бъде въведен конкретен формат на телефонен номер, след което щракнете с десния бутон и изберете Форматиране на клетки от контекстното меню вижте екранната снимка:

2. В Форматиране на клетки диалогов прозорец, под Телефон за връзка: , изберете Custom вляво категория списъчно поле и след това въведете формата на телефонния номер, от който се нуждаете, в текстовото поле Тип, например, ще използвам това (###) ### - #### формат, вижте екранната снимка:

3, След това кликнете върху OK за да затворите диалоговия прозорец.

4. След като форматирате клетките, продължете да избирате клетките и след това отворете Потвърждаване на данните диалогов прозорец чрез щракване Дата > Потвърждаване на данните > Потвърждаване на данните, в изскачащия диалогов прозорец под Настройки раздел, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • И след това въведете тази формула =И(ЕЧИСЛО(A2),LEN(A2)=10) в текстовото поле Формула.
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

Забележка: В горната формула, A2 е първата клетка от колоната, в която искате да потвърдите телефонния номер.

5. Сега, когато въвеждате 10-цифрен номер, той автоматично ще се преобразува в конкретния формат на телефонен номер, както ви е необходимо, вижте екранните снимки:

Забележка: Ако въведеното число не е 10 цифри, ще изскочи предупредително съобщение, за да ви напомни, вижте екранна снимка:

 Принудителен само формат на телефонен номер с полезна функция

Kutools за ExcelЕ Потвърдете телефонния номер функцията също така може да ви помогне да принудите да въведете само формат на телефонен номер само с няколко кликвания.

След инсталиране Kutools за Excel, моля, направете следното:

1. Изберете списъка с клетки, които позволяват само определен телефонен номер, след което щракнете Kutools > Предотвратяване на писане > Потвърдете телефонния номер, вижте екранна снимка:

2. В Телефон диалогов прозорец, изберете конкретния формат на телефонния номер, от който се нуждаете, или можете да създадете свой собствен формат, като щракнете върху Добави бутон, вижте екранната снимка:

3. След като изберете или зададете форматирането на телефонния номер, щракнете OK, сега може да бъде въведен само телефонният номер със специфичния формат, в противен случай ще изскочи предупредително съобщение, за да ви напомни, вижте екранната снимка:

4.9 Проверката на данните принуждава да се въвеждат само имейл адреси

Да предположим, че трябва да въведете няколко имейл адреса в колона на работен лист, за да предотвратите въвеждането на неправилно форматиране на имейл адреси, обикновено можете да зададете правило за валидиране на данни за разрешаване само на форматиране на имейл адреси.

 Принудително форматиране само на имейл адреси с функция за проверка на данни

Като използвате функцията за проверка на данни с персонализирана формула, можете да създадете правило за предотвратяване на бързото въвеждане на невалидни имейл адреси, моля, направете следното:

1. Изберете клетките, в които искате да се въвеждат само имейл адреси, след което щракнете Дата > Потвърждаване на данните > Потвърждаване на данните.

2. В изскочилото Потвърждаване на данните диалогов прозорец, под Настройки раздел, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • И след това въведете тази формула =ISNUMBER(MATCH("*@*.?*",A2,0)) в Формула текстово поле.
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

Забележка: В горната формула, A2 е първата клетка от колоната, която искате да използвате.

3. Сега, ако въведеният текст не е във формат на имейл адрес, ще се появи поле за предупредително съобщение, за да ви напомни, вижте екранна снимка:

 Принудително форматиране само на имейл адреси с удобна функция

Kutools за Excel поддържа невероятна функция – Потвърдете имейл адреса, с тази помощна програма можете да предотвратите невалидните имейл адреси само с едно щракване.

След инсталиране Kutools за Excel, моля, направете следното:

1. Изберете клетките, в които позволявате да се въвеждат само имейл адреси, след което щракнете Kutools > Предотвратяване на писане > Потвърдете имейл адреса. Вижте екранна снимка:

2. И след това само форматирането на имейл адреса позволява да бъде въведено, в противен случай ще изскочи поле с предупредително съобщение, за да ви напомни, вижте екранна снимка:

4.10 Проверката на данните принуждава въвеждането само на IP адреси

В този раздел ще представя някои бързи трикове за настройка на валидирането на данни да приема само IP адреси в диапазон от клетки.

 Принудително форматиране само на IP адреси с функцията за проверка на данните

Разрешете само IP адреси да бъдат въвеждани в определен диапазон от клетки, моля, направете следното:

1. Изберете клетките, в които искате да се въведе само IP адрес, след което щракнете Дата > Потвърждаване на данните > Потвърждаване на данните.

2. В изскочилото Потвърждаване на данните диалогов прозорец, под Настройки раздел, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • След това въведете формулата по-долу в Формула текстово поле.
  • =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

Забележка: В горната формула, A2 е първата клетка от колоната, която искате да използвате.

3. Сега, ако въведете невалиден IP адрес в клетката, ще изскочи предупредително съобщение, за да ви напомни, както е показано на екранната снимка по-долу:

 Принудително форматиране само на IP адреси с VBA код

Тук следният VBA код също може да помогне да се разреши въвеждането само на IP адреси и да се ограничи друго въвеждане, моля, направете следното:

1. Щракнете с десния бутон върху раздела на листа и щракнете Преглед на кода от контекстното меню, в отварянето Microsoft Visual Basic за приложения прозорец, копирайте долния VBA код в него.

VBA код: проверете клетките да приемат само IP адрес

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

Забележка: В горния код, A2: A10 е диапазонът от клетки, който искате да приемате само IP адреси.

2. След това запазете и затворете този код, сега само правилните IP адреси позволяват да бъдат въведени в конкретните клетки.

 Принудително форматиране само на IP адреси с лесна функция

Ако имате Kutools за Excel инсталиран във вашата работна книга, това е Валидирайте IP адреса може да ви помогне да решите и тази задача.

След инсталиране Kutools за Excel, моля, направете следното:

1. Изберете клетките, в които позволявате да се въвеждат само IP адреси, след което щракнете Kutools > Предотвратяване на писане > Валидирайте IP адреса. Вижте екранна снимка:

2. След като приложите тази функция, сега само IP адресът позволява да бъде въведен, в противен случай ще изскочи предупредително съобщение, за да ви напомни, вижте екранна снимка:

4.11 Проверката на данни ограничава стойностите, които надвишават общата стойност

Да предположим, че имате месечен отчет за разходите и общият бюджет е $18000 18000, сега трябва общата сума в списъка с разходи да не надвишава предварително зададените общи $XNUMX XNUMX, както е показано на екранната снимка по-долу. В този случай можете да създадете правило за валидиране на данни, като използвате функцията SUM, за да предотвратите превишаване на сумата от стойности над предварително зададена обща сума.

1. Изберете списъка с клетки, където искате стойностите да бъдат ограничени.

2, След това кликнете Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки раздел, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • След това въведете формулата по-долу в Формула текстово поле.
  • =SUM($B$2:$B$7)<=18000
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

Забележка: В горната формула, B2: B7 е диапазонът от клетки, в който искате да ограничите записите.

3. Сега, когато въвеждате стойностите в диапазона B2:B7, ако общата сума на стойностите е по-малка от $18000, валидирането преминава. Ако някаква стойност надхвърли общата сума от $18000 XNUMX, ще се появи прозорец с предупредително съобщение, за да ви напомни.

4.12 Проверката на данни ограничава въвеждането на клетка въз основа на друга клетка

Когато искате да ограничите въвеждането на данни в списък с клетки въз основа на стойността в друга клетка, функцията за валидиране на данни може да помогне и за решаването на тази задача. Например, ако клетката C1 е текстът „Да“, диапазонът A2: A9 може да въвежда всичко, но ако клетката C1 е друг текст, нищо не позволява да бъде въведено в диапазона A2: A9, както е показано на екранните снимки по-долу :

За да разрешите това решение, моля, направете следното:

1. Изберете списъка с клетки, където искате стойностите да бъдат ограничени.

2, След това кликнете Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки раздел, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • След това въведете формулата по-долу в Формула текстово поле.
  • =$C$1="Yes"
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

Забележка: В горната формула, C1 дали клетката съдържа конкретния текст, който искате да използвате, и текстът „Да” е текстът, въз основа на който искате да ограничите клетките, моля, променете ги според вашите нужди.

3. Сега, ако клетка C1 има текст „Да“, всичко може да бъде въведено в диапазона A2: A9, ако клетка C1 има друг текст, няма да можете да въведете никаква стойност, вижте демонстрацията по-долу:

4.13 Проверката на данните позволява да се въвеждат само дни от седмицата или почивни дни

Ако имате нужда само от делничните дни (от понеделник до петък) или почивните дни (събота и неделя) да бъдат въведени в списък с клетки, Потвърждаване на данните също може да ви помогне, моля, направете следните стъпки:

1. Изберете списъка с клетки, където искате да бъдат въведени дните от седмицата или делничните дни.

2, След това кликнете Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки раздел, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • И след това въведете една от формулите по-долу в Формула текстово поле, както ви е необходимо.
  • =WEEKDAY(A2,2)<6                      (allow only weekdays)
    =WEEKDAY(A2,2)>5                      (allow only weekends)
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

Забележка: В горната формула, A2 е първата клетка от колоната, която искате да използвате.

3. Сега можете да въведете само датата от делничния ден или уикенда в конкретните клетки въз основа на вашите нужди.

4.14 Проверката на данните позволява въведена дата въз основа на днешната дата

Понякога може да се наложи да разрешите само датите, по-големи или по-малки от днес, да бъдат въведени в списък с клетки. The Потвърждаване на данните функция с ДНЕС функция може да ви направи услуга. Моля, направете следното:

1. Изберете списъка с клетки, където искате да бъде въведена само бъдещата дата (дата, по-голяма от днешната).

2, След това кликнете Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки раздел, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • След това въведете формулата по-долу в Формула текстово поле.
  • =A2>Today()
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

Забележка: В горната формула, A2 е първата клетка от колоната, която искате да използвате.

3. Сега само датите, по-големи от днешната дата, могат да бъдат въведени в клетките, в противен случай ще се появи поле с предупредително съобщение, за да ви напомни, вижте екранната снимка:

Съвет:

1. За да разрешите въвеждането на минала дата (дата, по-малка от днешната), моля, приложете формулата по-долу в проверката на данните:

=A2<Today()

2. Позволете да бъде въведена дата в определен диапазон от дати, като например датите през следващите 30 дни, моля, въведете формулата по-долу в полето за проверка на данните:

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

4.15 Проверката на данните позволява въведено време въз основа на текущото време

Ако искате да потвърдите данните въз основа на текущото време, например, само времена преди или след текущото време могат да бъдат въведени в клетките. Можете да създадете своя собствена формула за валидиране на данни, моля, направете следното:

1. Изберете списъка с клетки, където искате да бъдат въведени само времената преди или след текущото време.

2, След това кликнете Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки раздел, извършете следните операции:

  • Изберете Време от Позволете падащ списък.
  • След това изберете по-малко от за да разрешите само времена преди текущия час, или по-голяма от за да разрешите времена след текущото време, колкото ви е необходимо от Дата падане надолу.
  • И тогава, в Край or Началният час въведете формулата по-долу:
  • =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

Забележка: В горната формула, A2 е първата клетка от колоната, която искате да използвате.

3. Сега в конкретните клетки могат да се въвеждат само времената преди или след текущото време.

4.16 Валидиране на данните датата на конкретна или текуща година

За да разрешите въвеждането само на дати от определена година или текуща година, можете да използвате валидиране на данни с персонализирана формула, базирана на функцията YEAR.

1. Изберете списъка с клетки, където искате да бъдат въведени само датите от определена година.

2, След това кликнете Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки раздел, извършете следните операции:

  • Изберете Custom от Позволете падащ списък.
  • След това въведете формулата по-долу в Формула текстово поле.
  • =YEAR(A2)=2020
  • Кликнете OK бутон, за да затворите този диалогов прозорец.

Забележка: В горната формула, A2 е първата клетка от колоната, която искате да използвате, 2020 е номерът на годината, който искате да ограничите.

3. След това могат да бъдат въведени само датите през 2020 г., ако не, ще изскочи предупредително съобщение, както е показано на екранната снимка по-долу:

Съвет:

За да разрешите само дати в текущата година, можете да приложите формулата по-долу в проверката на данните:

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

4.17 Проверка на данните датата в текущата седмица или месец

Ако искате да позволите на потребителя да може да въвежда датите от текущата седмица или месец в конкретни клетки, този раздел ще въведе някои формули за справяне с тази задача в Excel.

 Позволете да въведете дата от текущата седмица

1. Изберете списъка с клетки, където искате да бъдат въведени само датите от текущата седмица.

2, След това кликнете Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки раздел, извършете следните операции:

  • Изберете Дата от Позволете падащ списък.
  • И тогава изберете между от Дата падане надолу.
  • в Начална дата текстово поле, въведете тази формула: =ДНЕС()-СЕДНИЦА(ДНЕС(),3)
  • в Крайна дата текстово поле, въведете тази формула: =ДНЕС()-ДЕЛИЦА(ДНЕС(),3)+6
  • Най-накрая щракнете OK бутон.

3. След това могат да бъдат въведени само датите в текущата седмица, други дати ще бъдат предотвратени, както е показано на екранната снимка по-долу:

 Позволете да въведете дата от текущия месец

За да позволите да се въвеждат само датите от текущия месец, моля, направете следното:

1. Изберете списъка с клетки, където искате да бъдат въведени само датите от текущия месец.

2, След това кликнете Дата > Потвърждаване на данните > Потвърждаване на данните, в изскочилото Потвърждаване на данните диалогов прозорец, под Настройки раздел, извършете следните операции:

  • Изберете Дата от Позволете падащ списък.
  • И след това изберете между от Дата падане надолу.
  • в Начална дата текстово поле, въведете тази формула: =ДАТА(ГОДИНА(ДНЕС()),МЕСЕЦ(ДНЕС()),1)
  • в Крайна дата текстово поле, въведете тази формула: =ДАТА(ГОДИНА(ДНЕС()),МЕСец(ДНЕС()),ДЕН(ДАТА(ГОДИНА(ДНЕС()),МЕСец(ДНЕС())+1,1)-1))
  • Най-накрая щракнете OK бутон.

3. Отсега нататък само датите от текущия месец позволяват да бъдат въведени в избраните клетки.

5. Как да редактирам валидирането на данни в Excel?

За да редактирате или промените съществуващото правило за валидиране на данни, моля, следвайте стъпките по-долу:

1. Изберете някоя от клетките с правилото за валидиране на данни.

2, След това кликнете върху Дата > Потвърждаване на данните > Потвърждаване на данните за да отидете на Потвърждаване на данните диалоговия прозорец, в полето редактирайте или променете правилата според вашите нужди и след това проверете Приложете тези промени към всички други клетки със същите настройки опция за прилагане на това ново правило към всички други клетки с оригиналните критерии за валидиране. Вижте екранна снимка:

3. Щракнете OK за да запазите промените.

6. Как да намерите и изберете клетки с валидиране на данни в Excel?

Ако сте създали няколко правила за валидиране на данни във вашия работен лист, сега трябва да намерите и изберете клетките, които са приложили правилата за валидиране на данни, Отидете на Special командата може да ви помогне да изберете всички видове валидиране на данни или специфичен тип валидиране на данни.

1. Активирайте работния лист, който искате да намерите, и изберете клетките с проверка на данните.

2, След това кликнете върху Начало > Търсене и избор > Отидете на Special, вижте екранна снимка:

3. В Отидете на Special изберете диалоговия прозорец Потвърждаване на данни > Всички, вижте екранна снимка:

4. И всички клетки с валидиране на данни са избрани наведнъж в текущия работен лист.

Съвети: Ако просто искате да изберете един конкретен тип валидиране на данни, първо изберете една клетка, съдържаща валидирането на определени данни, които искате да разберете, след което отидете на Отидете на Special диалогов прозорец и изберете Потвърждаване на данни > Същото.

7. Как да копирам правилото за валидиране на данни в други клетки?

Да предположим, че сте създали правило за валидиране на данни за списък от клетки и сега трябва да приложите същото правило за валидиране на данни към други клетки. Вместо да създавате правилото отново, можете бързо и лесно да копирате и поставите съществуващото правило в други клетки.

1. Щракнете, за да изберете една клетка с правилото за валидиране, което искате да използвате, и след това натиснете Ctrl + C да го копирате.

2. След това изберете клетките, които искате да потвърдите, за да изберете множество несъседни клетки, натиснете и задръжте Ctrl докато избирате клетките.

3. И след това щракнете с десния бутон върху селекцията, изберете Специално поставяне опция, вижте екранна снимка:

4. В Специално поставяне изберете диалоговия прозорец Утвърждаване опция, вижте екранна снимка:

5. Щракнете OK сега правилото за валидиране се копира в новите клетки.

8. Как да използвате проверка на данни, за да оградите невалидни записи в Excel?

Понякога може да се наложи да създадете правила за валидиране на данни за съществуващи данни, в този случай някои невалидни данни може да се появят в диапазона от клетки. Как да проверите невалидните данни и да ги промените? В Excel можете да използвате Закръглете невалидни данни функция за маркиране на невалидните данни с червен кръг.

За да оградите невалидните данни, от които се нуждаете, трябва да приложите Потвърждаване на данните функция за задаване на правило за диапазона от данни. Моля, направете следните стъпки:

1. Изберете диапазона от данни, който искате да оградите с невалидни данни.

2, След това кликнете върху Дата > Потвърждаване на данните > Потвърждаване на данните, В Потвърждаване на данните диалогов прозорец, задайте правилото за валидиране според вашите нужди, например тук ще проверя стойностите, по-големи от 500, вижте екранна снимка:

3, След това кликнете върху OK за да затворите диалоговия прозорец. След като зададете правилото за проверка на данните, щракнете Дата > Потвърждаване на данните > Закръглете невалидни данни, тогава всички невалидни стойности, които са по-малки от 500, са оградени с червен овал. Вижте екранни снимки:

Забележки:

  • 1. Веднага след като коригирате невалидни данни, червеният кръг ще изчезне автоматично.
  • 2. Това Закръглете невалидни данни само тази функция може да огражда най-много 255 клетки. Когато запишете текущата работна книга, всички червени кръгове ще бъдат премахнати.
  • 3. Тези кръгове не могат да се отпечатат.
  • 4. Можете също да премахнете червените кръгове, като щракнете Дата > Потвърждаване на данните > Изчистване на кръговете за проверка.

9. Как да премахнете валидирането на данни в Excel?

За да премахнете правилата за валидиране на данни от диапазон от клетки, текущия работен лист или цялата работна книга, следните методи могат да ви направят услуга.

 Премахнете валидирането на данни в избран диапазон с функция за валидиране на данни

1. Изберете клетките с проверка на данните, които искате да премахнете.

2, След това кликнете Дата > Потвърждаване на данните > Потвърждаване на данните, в изскачащия диалогов прозорец, под Настройки кликнете върху Изчисти всички бутон, вижте екранната снимка:

3, След това кликнете върху OK бутон, за да затворите този диалогов прозорец. И правилото за валидиране на данни, приложено към избрания диапазон, беше премахнато наведнъж.

Съвети: За да премахнете проверката на данните от текущия работен лист, моля, първо изберете целия лист и след това приложете горните стъпки.

 Премахнете валидирането на данни в избран диапазон с удобна функция

Ако имате Kutools за Excel, неговата Изчистване на ограниченията за валидиране на данни функцията също може да помогне за премахване на правилата за валидиране на данни от избран диапазон или целия работен лист.

След инсталиране Kutools за Excel, моля, направете следното:

1. Изберете диапазона от клетки или целият работен лист съдържа проверката на данните, която искате да премахнете.

2, След това кликнете върху Kutools > Предотвратяване на писане > Изчистване на ограниченията за валидиране на данни, вижте екранна снимка:

3. Щракнете върху изскачащото поле с подкана OKи правилото за валидиране на данни са изчистени според нуждите ви.

 Премахнете валидирането на данни от всички работни листове с VBA код

За да премахнете правилата за валидиране на данни от цялата работна книга, горните методи ще отнемат много време, ако има много работни листове, тук кодът по-долу може да ви помогне да се справите бързо с тази задача.

1. Задръжте натиснат ALT + F11 за да отворите Microsoft Visual Basic за приложения прозорец.

2. След това щракнете Поставете > Модулии поставете следния макрос в Модули прозорец.

VBA код: Премахнете правилата за валидиране на данни във всички работни листове:

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

3. След това натиснете F5 ключ за изпълнение на този код и всички правила за валидиране на данни са изтрити незабавно от цялата работна книга.

 

  • Супер Формула Бар (лесно редактиране на няколко реда текст и формула); Оформление за четене (лесно четене и редактиране на голям брой клетки); Поставяне във филтриран диапазон...
  • Обединяване на клетки/редове/колони и съхраняване на данни; Съдържание на разделени клетки; Комбинирайте дублиращи се редове и сума/средно... Предотвратяване на дублиращи се клетки; Сравнете диапазони...
  • Изберете Дублиран или Уникален редове; Изберете Празни редове (всички клетки са празни); Super Find и Fuzzy Find в много работни тетрадки; Произволен избор...
  • Точно копие Множество клетки без промяна на референтната формула; Автоматично създаване на препратки към множество листа; Вмъкване на куршуми, квадратчета за отметка и други...
  • Любими и бързо вмъкнати формули, диапазони, диаграми и снимки; Шифроване на клетки с парола; Създаване на пощенски списък и изпращайте имейли...
  • Извличане на текст, Добавяне на текст, Премахване по позиция, Премахване на пространството; Създаване и отпечатване на междинни суми за пейджинг; Конвертиране на съдържание и коментари между клетки...
  • Супер филтър (запазване и прилагане на филтърни схеми към други листове); Разширено сортиране по месец/седмица/ден, честота и други; Специален филтър с удебелен шрифт, курсив...
  • Комбинирайте работни тетрадки и работни листове; Обединяване на таблици въз основа на ключови колони; Разделете данните на няколко листа; Пакетно конвертиране на xls, xlsx и PDF...
  • Групиране на обобщена таблица по номер на седмицата, ден от седмицата и други... Показване на отключени, заключени клетки с различни цветове; Маркирайте клетки, които имат формула/име...
kte tab 201905
  • Разрешете редактиране и четене с раздели в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Отваряйте и създавайте множество документи в нови раздели на един и същ прозорец, а не в нови прозорци.
  • Увеличава вашата производителност с 50% и намалява стотици кликвания на мишката за вас всеки ден!
officetab отдолу

 

Comments (1)
No ratings yet. Be the first to rate!
Ghomergher
Ghomergher
This comment was minimized by the moderator on the site
Thanks For Sharing this Great Information. I loved it.
Reply
Report
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations