Note: The other languages of the website are Google-translated. Back to English
Войти  \/ 
x
or
x
Регистрация  \/ 
x

or
 

Проверка данных Excel: добавление, использование, копирование и удаление проверки данных в Excel

В 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. После настройки условий вы можете перейти к входное сообщение or Предупреждение об ошибке Вкладка, чтобы установить входное сообщение или предупреждение об ошибке для ячеек проверки по своему усмотрению. (Если вы не хотите устанавливать оповещение, нажмите OK заканчивать сразу.)

3.1) Добавить входное сообщение (необязательно):

Вы можете создать сообщение, которое появляется при выборе ячейки, содержащей проверку данных. Это сообщение помогает напомнить пользователю, что он может ввести в ячейку.

Перейдите в входное сообщение tab и сделайте следующее:

  • Проверить Показывать входное сообщение, когда ячейка выбрана вариант;
  • Введите желаемый заголовок и напоминание в соответствующие поля;
  • Нажмите OK чтобы закрыть это диалоговое окно.

Теперь, когда вы выбираете проверенную ячейку, окно сообщения будет отображаться следующим образом:

3.2) Создавайте содержательные сообщения об ошибках (необязательно):

Помимо создания входного сообщения, вы также можете отображать предупреждения об ошибках, когда недопустимые данные вводятся в ячейку с проверкой данных.

Перейдите в Предупреждение об ошибке вкладки проверка достоверности данных диалоговое окно, сделайте следующее:

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

И теперь при вводе недопустимого значения появится окно с предупреждением о сообщении, как показано на скриншоте ниже:

Остановить вариант: вы можете нажать Повторите ввести другое значение или Отмена чтобы удалить запись.

Предупреждение вариант: нажмите Да ввести неверную запись, Нет изменить его, или Отмена чтобы удалить запись.

Информация вариант: нажмите OK ввести неверную запись или Отмена чтобы удалить запись.

Внимание: Если вы не устанавливаете собственное сообщение в Предупреждение об ошибке коробка, по умолчанию Остановить Появится окно с предупреждением, как показано ниже:

3. Основные примеры для проверки данных

При использовании этой функции проверки данных есть 8 встроенных опций, позволяющих настроить проверку данных. Например: любое значение, целые числа и десятичные дроби, дата и время, список, длина текста и настраиваемая формула. В этом разделе мы обсудим, как использовать некоторые встроенные параметры в Excel?

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

1. Выберите список ячеек, в котором вы хотите разрешить только целые числа или десятичные дроби, а затем щелкните Данные > проверка достоверности данных > проверка достоверности данных.

2. В проверка достоверности данных диалоговое окно под Параметры tab выполните следующие операции:

  • Выберите соответствующий пункт Целое число or Десятичная дробь в Разрешить выпадающий список.
  • А затем выберите один из нужных вам критериев в Данные коробка (в этом примере я выбираю между опция).
  • Советы: Критерии содержат: между, не между, равно, не равно, больше, меньше, больше или равно, меньше или равно.
  • Затем введите минимальный и максимальная значения, которые вам нужны (мне нужны числа от 0 до 1 00).
  • Наконец, нажмите OK кнопку.

3. Теперь в выбранные ячейки можно вводить только целые числа от 0 до 100.

3.2 Проверка данных для даты и времени

Чтобы подтвердить конкретную дату или время, которые нужно ввести, легко использовать этот проверка достоверности данных, пожалуйста, сделайте следующее:

1. Выберите список ячеек, в котором вы хотите разрешить только определенные даты или время, а затем щелкните Данные > проверка достоверности данных > проверка достоверности данных.

2. В проверка достоверности данных диалоговое окно под Параметры tab выполните следующие операции:

  • Выберите соответствующий пункт Время or Время в Разрешить выпадающий список.
  • А затем выберите один из нужных вам критериев в Данные коробка (здесь я выбираю больше опция).
  • Советы: Критерии содержат: между, не между, равно, не равно, больше, меньше, больше или равно, меньше или равно.
  • Затем введите Начало вам нужно (я хочу даты больше, чем 8).
  • Наконец, нажмите OK кнопку.

3. Теперь в выбранные ячейки можно вводить только даты, превышающие 8.

3.3 Проверка данных для длины текста

Если вам нужно ограничить количество символов, которые можно ввести в ячейку. Например, чтобы ограничить содержимое не более чем 10 символами для определенного диапазона, это проверка достоверности данных также могу оказать вам услугу.

1. Выберите список ячеек, в которых вы хотите ограничить длину текста, а затем щелкните Данные > проверка достоверности данных > проверка достоверности данных.

2. В проверка достоверности данных диалоговое окно под Параметры tab выполните следующие операции:

  • Выберите Длина текста из Разрешить выпадающий список.
  • А затем выберите один из нужных вам критериев в Данные коробка (в этом примере я выбираю меньше, чем опция).
  • Советы: Критерии содержат: между, не между, равно, не равно, больше, меньше, больше или равно, меньше или равно.
  • Затем введите максимальная число, которое нужно ограничить (хочу, чтобы длина текста не превышала 10 символов).
  • Наконец, нажмите OK кнопку.

3. Теперь выбранные ячейки позволяют набирать только текстовую строку длиной менее 10 символов.

3.4 Список проверки данных (раскрывающийся список)

С этим мощным проверка достоверности данных функцию, вы также можете быстро и легко создать раскрывающийся список в ячейках. Пожалуйста, сделайте так:

1. Выберите целевые ячейки, в которые нужно вставить раскрывающийся список, а затем щелкните Данные > проверка достоверности данных > проверка достоверности данных.

2. В проверка достоверности данных диалоговое окно под Параметры tab выполните следующие операции:

  • Выберите Список из Разрешить выпадающий список.
  • В Источник В текстовом поле введите элементы списка, разделенные запятыми. Например, чтобы ограничить ввод данных пользователем тремя вариантами, введите «Не начато», «Выполняется», «Завершено» или вы можете выбрать список ячеек, содержащий значения, на основе которых следует вставить раскрывающийся список.
  • Наконец, нажмите OK кнопку.

3. Теперь выпадающий список был создан в ячейках, как показано на скриншоте ниже:

Щелкните, чтобы получить более подробную информацию из раскрывающегося списка…

4. Расширенные настраиваемые правила проверки данных.

В этом разделе я расскажу, как создать некоторые расширенные пользовательские правила проверки данных для решения ваших проблем, таких как: создание формул проверки, разрешающих только числа или текстовые строки, только уникальные значения, только указанные номера телефонов, адреса электронной почты и т. Д. .

4.1 Проверка данных разрешает только числа или текст

 Разрешить ввод только чисел с функцией проверки данных

Чтобы разрешить только числа в диапазоне ячеек, сделайте следующее:

1. Выберите диапазон ячеек, в который нужно вводить только числа.

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

  • Выберите На заказ из Разрешить выпадающий список.
  • А затем введите эту формулу: = ЕЧИСЛО (A2) в Формула текстовое окно. (A2 это первая ячейка выбранного диапазона, который вы хотите ограничить)
  • Нажмите OK кнопку, чтобы закрыть это диалоговое окно.

3. С этого момента в выбранные ячейки можно вводить только числа.

Внимание: Это ISNUMBER Функция допускает любые числовые значения в проверенных ячейках, включая целые числа, десятичные дроби, дроби, даты и время.

 Разрешить ввод только текстовых строк с помощью функции проверки данных

Чтобы ограничить записи ячеек только текстом, вы можете использовать проверка достоверности данных функция с настраиваемой формулой на основе ISTEXT функции, сделайте следующее:

1. Выберите диапазон ячеек, в который нужно вводить только текстовые строки.

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

  • Выберите На заказ из Разрешить выпадающий список.
  • А затем введите эту формулу: = ISTEXT (A2) в Формула текстовое окно. (A2 это первая ячейка выбранного диапазона, который вы хотите ограничить)
  • Нажмите OK кнопку, чтобы закрыть это диалоговое окно.

3. Теперь при вводе данных в определенные ячейки можно разрешить данные только в текстовом формате.

4.2 Проверка данных допускает только буквенно-цифровые значения

Для некоторых целей вы просто хотите разрешить вводить алфавиты и числовые значения, но ограничить использование специальных символов, таких как ~,%, $, пробел и т. Д., Этот раздел познакомит вас с некоторыми хитростями.

 Разрешить только буквенно-цифровые значения с функцией проверки данных

Чтобы запретить использование специальных символов, но разрешить только буквенно-цифровые значения, вы должны создать настраиваемую формулу в проверка достоверности данных функции, сделайте следующее:

1. Выберите диапазон ячеек, в который нужно ввести только буквенно-цифровые значения.

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

  • Выберите На заказ из Разрешить выпадающий список.
  • Затем введите приведенную ниже формулу в Формула текстовое окно.
  • =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))
  • Нажмите OK кнопку, чтобы закрыть это диалоговое окно.

Внимание: В приведенных выше формулах A2 - первая ячейка выбранного диапазона, который вы хотите ограничить.

3. Теперь разрешено вводить только алфавиты и числовые значения, а использование специальных символов будет ограничено при вводе, как показано на скриншоте ниже:

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

Возможно, приведенная выше формула сложна для понимания и запоминания, здесь я представлю удобную функцию - Предотвратить ввод of Kutools for Excel, с помощью этой функции вы можете быстро и легко решить эту задачу.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите диапазон ячеек, в который нужно ввести только буквенно-цифровые значения.

2, Затем нажмите Kutools > Предотвратить ввод > Предотвратить ввод, см. снимок экрана:

3. В выскочившем Предотвратить ввод диалоговое окно, выберите Запретить ввод специальных символов вариант, см. снимок экрана:

4, Затем нажмите Ok и в следующих полях подсказок щелкните Да > OK чтобы закончить операцию. Теперь в выбранных ячейках разрешены только алфавиты и числовые значения, см. Снимок экрана:

4.3 Проверка данных позволяет текстам начинаться или заканчиваться определенными символами

Если все значения в определенном диапазоне должны начинаться или заканчиваться определенным символом или подстрокой, вы можете использовать проверку данных с помощью настраиваемой формулы, основанной на функциях ТОЧНО, ВЛЕВО, ВПРАВО или СЧЁТЕСЛИ.

 Разрешить тексту начинаться или заканчиваться определенными символами только с одним условием

Например, я хочу, чтобы тексты начинались или заканчивались на «CN» при вводе текстовых строк в определенные ячейки, сделайте следующее:

1. Выберите диапазон ячеек, в котором разрешены только тексты, начинающиеся или заканчивающиеся определенными символами.

2, Затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, в выскочившем проверка достоверности данных диалоговое окно под Параметры Вкладка, выполните следующие действия:

  • Выберите На заказ из Разрешить выпадающий список.
  • Затем введите приведенную ниже формулу в Формула текстовое окно.
  • 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», как показано на скриншоте ниже, вам необходимо добавить еще один экземпляр EXACT, используя знак плюса (+). Пожалуйста, сделайте следующие шаги:

1. Выберите диапазон ячеек, в котором разрешены только тексты, которые начинаются или заканчиваются несколькими критериями.

2, Затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, в выскочившем проверка достоверности данных диалоговое окно под Параметры Вкладка, выполните следующие действия:

  • Выберите На заказ из Разрешить выпадающий список.
  • Затем введите приведенную ниже формулу в Формула текстовое окно.
  • 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», как показано на скриншоте ниже, вы можете применить проверку данных с помощью настраиваемой формулы на основе функций НАЙТИ и ЕЧИСЛО. Пожалуйста, сделайте так:

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

2, Затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, в выскочившем проверка достоверности данных диалоговое окно под Параметры Вкладка, выполните следующие действия:

  • Выберите На заказ из раскрывающегося списка Разрешить.
  • Затем введите одну из формул ниже в Формула текстовое окно.
  • =ISNUMBER(FIND("KTE",A2))             (Case sensitive)
    =ISNUMBER(SEARCH("KTE",A2))         (Non case sensitive)
  • Нажмите OK кнопку, чтобы закрыть это диалоговое окно.

Внимание: В приведенных выше формулах A2 первая ячейка выбранного диапазона, текст КТЭ текстовая строка, которую должны содержать записи.

3. Теперь, когда введенное значение не содержит заданного текста, появится окно с предупреждением.

Разрешить записи должны содержать один из многих конкретных текстов

Приведенная выше формула работает только для одной текстовой строки, если вам нужно, чтобы в ячейках была разрешена одна из многих текстовых строк, как показано на следующем снимке экрана, вы должны использовать функции СУММПРОИЗВ, НАЙТИ и ЕЧИСЛО вместе для создания формулы.

1. Выберите диапазон ячеек, в котором разрешены только тексты, содержащие любой из множества элементов.

2, Затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, в выскочившем проверка достоверности данных диалоговое окно под Параметры Вкладка, выполните следующие действия:

  • Выберите На заказ из Разрешить выпадающий список.
  • Затем введите одну из приведенных ниже формул по мере необходимости в Формула текстовое окно.
  • =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, Затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, в выскочившем проверка достоверности данных диалоговое окно под Параметры Вкладка, выполните следующие действия:

  • Выберите На заказ из Разрешить выпадающий список.
  • Затем введите одну из формул ниже в Формула текстовое окно.
  • =ISERROR(FIND("KTE",A2))                  (Case sensitive)
    =ISERROR(SEARCH("KTE",A2))                  (Non case sensitive)
  • Нажмите OK кнопку, чтобы закрыть это диалоговое окно.

Внимание: В приведенных выше формулах A2 первая ячейка выбранного диапазона, текст КТЭ текстовая строка, которую записи не должны содержать.

3. Теперь записи, содержащие конкретный текст, не будут введены.

Разрешить записи не должны содержать один из многих конкретных текстов

Чтобы предотвратить ввод одной из многих текстовых строк в список, как показано на скриншоте ниже, вы должны сделать следующее:

1. Выберите диапазон ячеек, в котором вы хотите запретить некоторые тексты.

2, Затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, в выскочившем проверка достоверности данных диалоговое окно под Параметры Вкладка, выполните следующие действия:

  • Выберите На заказ из Разрешить выпадающий список.
  • Затем введите приведенную ниже формулу в Формула текстовое окно.
  • =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.

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

Обычно вам может помочь функция проверки данных с настраиваемой формулой, основанной на функции СЧЁТЕСЛИ. Для этого выполните следующие действия:

1. Выберите ячейки или столбец, в которые нужно ввести только уникальные значения.

2, Затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, в выскочившем проверка достоверности данных диалоговое окно под Параметры tab выполните следующие операции:

  • Выберите На заказ из Разрешить выпадающий список.
  • Затем введите приведенную ниже формулу в Формула текстовое окно.
  • =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: - это ячейки в столбце, дублирование которых вы хотите предотвратить, пожалуйста, измените их по своему усмотрению.

2. Затем сохраните и закройте этот код, теперь при вводе повторяющегося значения в ячейку A1: A100 появляется окно с предупреждением, как показано ниже:

 Разрешить только уникальные значения с помощью удобной функции

Если у вас есть Kutools for Excel, С его Предотвратить дублирование вы можете настроить проверку данных для предотвращения дублирования для диапазона ячеек с помощью всего нескольких щелчков мышью.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите диапазон ячеек, в котором вы хотите предотвратить повторяющиеся значения, но разрешить только уникальные данные.

2, Затем нажмите Kutools > Предотвратить ввод > Предотвратить дублирование, см. снимок экрана:

3. Появится предупреждающее сообщение, напоминающее о том, что проверка данных будет удалена при применении этой функции, нажмите Да и в следующем окне подсказки щелкните OK, смотрите скриншоты:

4. Теперь, когда вы вводите некоторые повторяющиеся данные в указанные ячейки, отображается окно подсказки, чтобы напомнить вам, что повторяющиеся данные недействительны, см. Снимок экрана:

4.6 Проверка данных разрешает только верхний / нижний / правильный регистр

Эта проверка данных - мощная функция, она также может помочь пользователю вводить только прописные, строчные или правильные регистры в диапазон ячеек. Пожалуйста, сделайте следующие шаги:

1. Выберите диапазон ячеек, в который нужно вводить текст только в верхнем, нижнем или правильном регистре.

2, Затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, в выскочившем проверка достоверности данных диалоговое окно под Параметры tab выполните следующие операции:

  • Выберите На заказ из Разрешить выпадающий список.
  • Затем введите одну из следующих формул, которая вам нужна, в Формула текстовое окно.
  • =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 Проверка данных позволяет значениям, которые существуют / не существуют в другом списке

Разрешение значениям существовать или не существовать в другом списке для ввода в диапазон ячеек может быть болезненной проблемой для большинства из нас. Фактически, вы можете использовать функцию проверки данных с простой формулой, основанной на функции СЧЁТЕСЛИ, чтобы справиться с этим.

Например, я хочу, чтобы только значения в диапазоне C2: C4 вводились в диапазон ячеек, как показано на скриншоте ниже, чтобы решить эту задачу, сделайте следующее:

1. Выберите диапазон ячеек, к которому вы хотите применить проверку данных.

2, Затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, в выскочившем проверка достоверности данных диалоговое окно под Параметры tab выполните следующие операции:

  • Выберите На заказ из Разрешить выпадающий список.
  • Затем введите одну из следующих формул, которая вам нужна, в Формула текстовое окно.
  • =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. В Формат ячеек диалоговое окно под Число вкладка, выберите На заказ слева Категория список, а затем введите нужный формат номера телефона в текстовое поле Тип, например, я буду использовать это (###) ### - #### формат, см. снимок экрана:

3, Затем нажмите OK для закрытия диалогового окна.

4. После форматирования ячеек продолжайте выбирать ячейки, а затем откройте проверка достоверности данных диалоговое окно по щелчку Данные > проверка достоверности данных > проверка достоверности данных, в появившемся диалоговом окне под Параметры tab выполните следующие операции:

  • Выберите На заказ из Разрешить выпадающий список.
  • А затем введите эту формулу = И (ЕЧИСЛО (A2); ДЛИНА (A2) = 10) в текстовое поле Формула.
  • Нажмите OK кнопку, чтобы закрыть это диалоговое окно.

Внимание: В приведенной выше формуле A2 - это первая ячейка столбца, в котором вы хотите проверить номер телефона.

5. Теперь при вводе 10-значного числа оно будет автоматически преобразовано в конкретный формат номера телефона по мере необходимости, см. Скриншоты:

Внимание: Если введенный номер не 10 цифр, появится окно с предупреждением, напоминающее вам, см. Снимок экрана:

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

Kutools for ExcelАвтора Подтвердить номер телефона Функция также может помочь вам принудительно ввести только формат номера телефона с помощью всего нескольких щелчков мышью.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите список ячеек, в которых разрешен только определенный номер телефона, затем нажмите Kutools > Предотвратить ввод > Подтвердить номер телефона, см. снимок экрана:

2. В Номер телефона в диалоговом окне выберите нужный формат номера телефона или вы можете создать собственное форматирование, щелкнув значок Добавить кнопку, см. снимок экрана:

3. После выбора или настройки форматирования номера телефона нажмите OK, теперь можно ввести только номер телефона с определенным форматированием, в противном случае появится предупреждающее сообщение, напоминающее вам, см. снимок экрана:

4.9 Проверка данных требует ввода только адресов электронной почты

Предположим, вам нужно ввести несколько адресов электронной почты в столбец рабочего листа, чтобы предотвратить ввод некорректного форматирования адресов электронной почты, обычно вы можете установить правило проверки данных, разрешающее только форматирование адресов электронной почты.

 Принудительный формат только адресов электронной почты с функцией проверки данных

Используя функцию проверки данных с настраиваемой формулой, вы можете создать правило для предотвращения быстрого ввода недействительных адресов электронной почты, пожалуйста, сделайте следующее:

1. Выделите ячейки, в которые нужно ввести только адреса электронной почты, затем нажмите Данные > проверка достоверности данных > проверка достоверности данных.

2. В выскочившем проверка достоверности данных диалоговое окно под Параметры tab выполните следующие операции:

  • Выберите На заказ из Разрешить выпадающий список.
  • А затем введите эту формулу = ISNUMBER (MATCH ("*@*.?*"; A2,0)) в Формула текстовое окно.
  • Нажмите OK кнопку, чтобы закрыть это диалоговое окно.

Внимание: В приведенной выше формуле A2 это первая ячейка столбца, который вы хотите использовать.

3. Теперь, если введенный текст не является форматом адреса электронной почты, появится окно с предупреждением, напоминающее вам, см. Снимок экрана:

 Принудительный формат только адресов электронной почты с помощью удобной функции

Kutools for Excel поддерживает удивительную функцию - Подтвердить адрес электронной почты, с помощью этой утилиты вы можете предотвратить неверные адреса электронной почты одним щелчком мыши.

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите ячейки, в которые можно вводить только адреса электронной почты, затем нажмите Kutools > Предотвратить ввод > Подтвердить адрес электронной почты. Смотрите скриншот:

2. Затем можно ввести только форматирование адреса электронной почты, в противном случае появится окно с предупреждением, чтобы напомнить вам, см. Снимок экрана:

4.10 Проверка данных заставляет вводить только IP-адреса

В этом разделе я расскажу о некоторых быстрых приемах, позволяющих настроить проверку данных на прием только IP-адресов в диапазоне ячеек.

 Принудительный формат только IP-адресов с функцией проверки данных

Разрешите вводить только IP-адреса в определенный диапазон ячеек, сделайте следующее:

1. Выберите ячейки, в которые нужно ввести только IP-адрес, затем нажмите Данные > проверка достоверности данных > проверка достоверности данных.

2. В выскочившем проверка достоверности данных диалоговое окно под Параметры tab выполните следующие операции:

  • Выберите На заказ из Разрешить выпадающий список.
  • Затем введите приведенную ниже формулу в Формула текстовое окно.
  • =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 for Excel установлен в вашей книге, его Подтвердить IP-адрес функция также может помочь вам решить эту задачу.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите ячейки, в которые разрешено вводить только IP-адреса, затем нажмите Kutools > Предотвратить ввод > Подтвердить IP-адрес. Смотрите скриншот:

2. После применения этой функции теперь можно вводить только IP-адрес, в противном случае появится окно с предупреждением, напоминающее вам, см. Снимок экрана:

4.11 Проверка данных ограничивает значения, превышающие общее значение

Предположим, у вас есть ежемесячный отчет о расходах, а общая сумма бюджета составляет 18000 долларов США, теперь вам нужно, чтобы общая сумма в списке расходов не превышала предварительно установленную общую сумму 18000 долларов США, как показано на скриншоте ниже. В этом случае вы можете создать правило проверки данных с помощью функции СУММ, чтобы предотвратить превышение суммы значений предварительно установленной суммы.

1. Выберите список ячеек, в которых вы хотите ограничить значения.

2. Затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, в выскочившем проверка достоверности данных диалоговое окно под Параметры tab выполните следующие операции:

  • Выберите На заказ из Разрешить выпадающий список.
  • Затем введите приведенную ниже формулу в Формула текстовое окно.
  • =SUM($B$2:$B$7)<=18000
  • Нажмите OK кнопку, чтобы закрыть это диалоговое окно.

Внимание: В приведенной выше формуле B2: B7 - это диапазон ячеек, в который нужно ограничить количество записей.

3. Теперь при вводе значений в диапазоне B2: B7, если сумма значений меньше 18000 долларов США, проверка проходит. Если какое-либо значение превысит 18000 долларов, появится окно с предупреждением, напоминающее вам.

4.12 Проверка данных ограничивает ввод ячеек на основе другой ячейки

Если вы хотите ограничить ввод данных в список ячеек на основе значения в другой ячейке, функция проверки данных также может помочь решить эту задачу. Например, если ячейка C1 представляет собой текст «Да», в диапазон A2: A9 разрешено вводить что угодно, но, если ячейка C1 представляет собой другой текст, ничего нельзя вводить в диапазоне A2: A9, как показано ниже. :

Чтобы решить эту проблему, сделайте следующее:

1. Выберите список ячеек, в которых вы хотите ограничить значения.

2. Затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, в выскочившем проверка достоверности данных диалоговое окно под Параметры tab выполните следующие операции:

  • Выберите На заказ из Разрешить выпадающий список.
  • Затем введите приведенную ниже формулу в Формула текстовое окно.
  • =$C$1="Yes"
  • Нажмите OK кнопку, чтобы закрыть это диалоговое окно.

Внимание: В приведенной выше формуле C1 содержит ли ячейка конкретный текст, который вы хотите использовать, а текст «Да»- это текст, на основе которого вы хотите ограничить количество ячеек, пожалуйста, измените их по своему усмотрению.

3. Теперь, если в ячейке C1 есть текст «Да», что угодно можно ввести в диапазон A2: A9, если в ячейке C1 есть другой текст, вы не сможете ввести какое-либо значение, см. Демонстрацию ниже:

4.13 Проверка данных позволяет вводить только будние или выходные дни.

Если вам нужно ввести в список ячеек только рабочие дни (с понедельника по пятницу) или выходные (суббота и воскресенье), проверка достоверности данных также может вам помочь, выполните следующие действия:

1. Выберите список ячеек, в который вы хотите ввести дни недели или дни недели.

2. Затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, в выскочившем проверка достоверности данных диалоговое окно под Параметры tab выполните следующие операции:

  • Выберите На заказ из Разрешить выпадающий список.
  • Затем введите одну из формул ниже в Формула текстовое поле, как вам нужно.
  • =WEEKDAY(A2,2)<6                      (allow only weekdays)
    =WEEKDAY(A2,2)>5                      (allow only weekends)
  • Нажмите OK кнопку, чтобы закрыть это диалоговое окно.

Внимание: В приведенной выше формуле A2 это первая ячейка столбца, который вы хотите использовать.

3. Теперь вы можете вводить только дату буднего или выходного дня в определенные ячейки в зависимости от ваших потребностей.

4.14 Проверка данных позволяет ввести дату на основе сегодняшней даты

Иногда вам может потребоваться разрешить ввод в список ячеек только дат, которые больше или меньше сегодняшнего дня. В проверка достоверности данных особенность с СЕГОДНЯ функция может оказать вам услугу. Пожалуйста, сделайте так:

1. Выберите список ячеек, в который вы хотите ввести только дату в будущем (дату, более позднюю, чем сегодня).

2. Затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, в выскочившем проверка достоверности данных диалоговое окно под Параметры tab выполните следующие операции:

  • Выберите На заказ из Разрешить выпадающий список.
  • Затем введите приведенную ниже формулу в Формула текстовое окно.
  • =A2>Today()
  • Нажмите OK кнопку, чтобы закрыть это диалоговое окно.

Внимание: В приведенной выше формуле A2 это первая ячейка столбца, который вы хотите использовать.

3. Теперь в ячейки можно вводить только даты, превышающие сегодняшнюю, в противном случае появится окно с предупреждением, напоминающее вам, см. Снимок экрана:

Советы:

1. Чтобы разрешить ввод прошлой даты (дата меньше сегодняшней), пожалуйста, примените следующую формулу к проверке данных:

=A2<Today()

2. Разрешите вводить дату в пределах определенного диапазона дат, например даты в следующие 30 дней, введите следующую формулу в проверку данных:

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

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

Например, если вы хотите проверить данные на основе текущего времени, в ячейки можно ввести только время до или после текущего времени. Вы можете создать свою собственную формулу проверки данных, сделайте следующее:

1. Выберите список ячеек, в который вы хотите ввести только время до или после текущего времени.

2. Затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, в выскочившем проверка достоверности данных диалоговое окно под Параметры tab выполните следующие операции:

  • Выберите Время из Разрешить выпадающий список.
  • Затем выберите меньше, чем разрешить только раз до текущего времени, или больше чтобы позволить время после текущего времени, как вам нужно из Данные падать.
  • А затем в Время окончания or Время начала поле введите следующую формулу:
  • =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
  • Нажмите OK кнопку, чтобы закрыть это диалоговое окно.

Внимание: В приведенной выше формуле A2 это первая ячейка столбца, который вы хотите использовать.

3. Теперь в определенные ячейки можно вводить только время до или после текущего времени.

4.16 Проверка данных дата конкретного или текущего года

Чтобы разрешить ввод только дат определенного года или текущего года, вы можете использовать проверку данных с помощью настраиваемой формулы, основанной на функции ГОД.

1. Выберите список ячеек, в который вы хотите ввести только даты определенного года.

2. Затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, в выскочившем проверка достоверности данных диалоговое окно под Параметры tab выполните следующие операции:

  • Выберите На заказ из Разрешить выпадающий список.
  • Затем введите приведенную ниже формулу в Формула текстовое окно.
  • =YEAR(A2)=2020
  • Нажмите OK кнопку, чтобы закрыть это диалоговое окно.

Внимание: В приведенной выше формуле A2 это первая ячейка столбца, который вы хотите использовать, 2020 - номер года, который вы хотите ограничить.

3. И затем можно ввести только даты в 2020 году, в противном случае появится окно с предупреждением, как показано на скриншоте ниже:

Советы:

Чтобы разрешить только даты в текущем году, вы можете применить приведенную ниже формулу для проверки данных:

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

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

Если вы хотите разрешить пользователю вводить даты текущей недели или месяца в определенные ячейки, в этом разделе будут представлены некоторые формулы для решения этой задачи в Excel.

 Разрешить ввести дату текущей недели

1. Выберите список ячеек, в который вы хотите ввести только даты текущей недели.

2. Затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, в выскочившем проверка достоверности данных диалоговое окно под Параметры tab выполните следующие операции:

  • Выберите Время из Разрешить выпадающий список.
  • А затем выберите между из Данные падать.
  • В Начало текстовое поле введите эту формулу: = СЕГОДНЯ () - ДЕНЬ НЕДЕЛИ (СЕГОДНЯ (); 3)
  • В Дата окончания текстовое поле введите эту формулу: = СЕГОДНЯ () - ДЕНЬ НЕДЕЛИ (СЕГОДНЯ (); 3) +6
  • Наконец, нажмите OK кнопку.

3. Затем можно будет ввести только даты в пределах текущей недели, другие даты будут запрещены, как показано на скриншоте ниже:

 Разрешить вводить дату текущего месяца

Чтобы можно было вводить только даты текущего месяца, сделайте следующее:

1. Выберите список ячеек, в который вы хотите ввести только даты текущего месяца.

2. Затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, в выскочившем проверка достоверности данных диалоговое окно под Параметры tab выполните следующие операции:

  • Выберите Время из Разрешить выпадающий список.
  • А затем выберите одно из Данные падать.
  • В Начало текстовое поле введите эту формулу: = ДАТА (ГОД (СЕГОДНЯ ()); МЕСЯЦ (СЕГОДНЯ ()); 1)
  • В Дата окончания текстовое поле введите эту формулу: = ДАТА (ГОД (СЕГОДНЯ ()), МЕСЯЦ (СЕГОДНЯ ()), ДЕНЬ (ДАТА (ГОД (СЕГОДНЯ ()), МЕСЯЦ (СЕГОДНЯ ()) + 1,1) -1))
  • Наконец, нажмите OK кнопку.

3. Отныне в выбранные ячейки можно вводить только даты текущего месяца.

5. Как отредактировать валидацию данных в Excel?

Чтобы отредактировать или изменить существующее правило проверки данных, выполните следующие действия:

1. Выберите любую из ячеек с правилом проверки данных.

2, Затем нажмите Данные > проверка достоверности данных > проверка достоверности данных , чтобы перейти к проверка достоверности данных диалоговом окне, в поле отредактируйте или измените правила в соответствии с вашими потребностями, а затем установите флажок Примените эти изменения ко всем остальным ячейкам с такими же настройками. возможность применить это новое правило ко всем другим ячейкам с исходными критериями проверки. Смотрите скриншот:

3. Нажмите OK для сохранения изменений.

6. Как найти и выделить ячейки с проверкой данных в Excel?

Если вы создали несколько правил проверки данных на своем листе, теперь вам нужно найти и выбрать ячейки, в которых были применены правила проверки данных, Перейти к специальному Команда может помочь вам выбрать все виды проверки данных или конкретный тип проверки данных.

1. Активируйте лист, который вы хотите найти, и выберите ячейки с проверкой данных.

2, Затем нажмите Главная > Найти и выбрать > Перейти к специальному, см. снимок экрана:

3. В Перейти к специальному диалоговое окно, выберите Валидация данных > Все, см. снимок экрана:

4. И все ячейки с проверкой данных были выбраны сразу на текущем листе.

Советы: Если вы просто хотите выбрать один конкретный тип проверки данных, сначала выберите одну ячейку, содержащую определенную проверку данных, которую вы хотите узнать, затем перейдите к Перейти к специальному диалоговое окно и выберите Валидация данных > Одна и та же.

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 for Excel, Его Очистить ограничения проверки данных Функция также может помочь удалить правила проверки данных из выбранного диапазона или всего рабочего листа.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выделите диапазон ячеек или весь лист, содержащий данные проверки, которые вы хотите удалить.

2, Затем нажмите Kutools > Предотвратить ввод > Очистить ограничения проверки данных, см. снимок экрана:

3. Во всплывающем окне подсказки щелкните OK, и правило проверки данных было очищено по мере необходимости.

 Удалите проверку данных со всех листов с кодом VBA

Чтобы удалить правила проверки данных из всей книги, вышеуказанные методы потребуют много времени, если существует много рабочих листов. Здесь приведенный ниже код может помочь вам быстро справиться с этой задачей.

1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Затем нажмите Вставить > Модуль, и вставьте следующий макрос в Модуль окно.

Код VBA: удалить правила проверки данных на всех листах:

Sub RemoveDataValidation () 'Обновление Extendoffice
  Заменить xwsh как рабочий лист для каждого xwsh в ActiveWorkbook.Worksheets xwsh.Cells.Validation.Delete Next xwsh End Sub

3, Затем нажмите F5 ключ для запуска этого кода, и все правила проверки данных были немедленно удалены из всей книги.

  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Избранные и быстро вставляйте формулы, Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Группировка сводной таблицы по номер недели, день недели и другое ... Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя...
вкладка kte 201905
  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Ghomergher · 17 days ago
    Thanks For Sharing this Great Information. I loved it.