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

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

• 1. Что такое проверка данных в Excel?
• 2. Как добавить проверку данных в Excel?
• 3. Основные примеры для проверки данных

• Разрешить ввод только цифр
• Разрешить ввод только текстовых строк
• Разрешить только буквенно-цифровые значения с функцией проверки данных
• Разрешить только буквенно-цифровые значения с замечательной функцией
• Разрешить тексту начинаться или заканчиваться определенными символами только с одним условием
• Разрешить тексты начинаться или заканчиваться определенными символами с несколькими критериями (логика ИЛИ)
• Разрешить записи должны содержать один или один из множества конкретных текстов
• Разрешить записи не должны содержать один или один из множества конкретных текстов.
• Разрешить только уникальные значения с функцией проверки данных
• Разрешить только уникальные значения с кодом VBA
• Разрешить только уникальные значения с помощью удобной функции
• Принудительно использовать только формат номера телефона с функцией проверки данных
• Принудительный формат только номера телефона с полезной функцией
• Принудительный формат только адресов электронной почты с функцией проверки данных
• Принудительный формат только адресов электронной почты с помощью удобной функции
• Принудительный формат только IP-адресов с функцией проверки данных
• Принудительный формат только IP-адресов с кодом VBA
• Форсировать только формат IP-адресов с помощью простой функции
• Разрешить ввести дату текущей недели
• Разрешить вводить дату текущего месяца

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

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

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

Содержание:

• 3.1 Проверка данных для целых и десятичных чисел
• 3.2 Проверка данных для даты и времени
• 3.3 Проверка данных для длины текста
• 3.4 Список проверки данных (раскрывающийся список)
• 4.1 Проверка данных разрешает только числа или текст
  • Разрешить ввод только чисел с функцией проверки данных
  • Разрешить ввод только текстовых строк с помощью функции проверки данных
  • 4.2 Проверка данных допускает только буквенно-цифровые значения
    • Разрешить только буквенно-цифровые значения с функцией проверки данных
    • Разрешить только буквенно-цифровые значения с замечательной функцией
    • Разрешить тексту начинаться или заканчиваться определенными символами только с одним условием
    • Разрешить тексты начинаться или заканчиваться определенными символами с несколькими критериями (логика ИЛИ)
    • Разрешить записи должны содержать один или один из множества конкретных текстов
    • Разрешить записи не должны содержать один или один из множества конкретных текстов.
    • Разрешить только уникальные значения с функцией проверки данных
    • Разрешить только уникальные значения с кодом VBA
    • Разрешить только уникальные значения с помощью удобной функции
    • Принудительно использовать только формат номера телефона с функцией проверки данных
    • Принудительный формат только номера телефона с полезной функцией
    • Принудительный формат только адресов электронной почты с функцией проверки данных
    • Принудительный формат только адресов электронной почты с помощью удобной функции
    • Принудительный формат только IP-адресов с функцией проверки данных
    • Принудительный формат только IP-адресов с кодом VBA
    • Форсировать только формат IP-адресов с помощью простой функции
    • Разрешить ввести дату текущей недели
    • Разрешить вводить дату текущего месяца
    • Удалить проверку данных в выбранном диапазоне с помощью функции проверки данных
    • Удалите проверку данных в выбранном диапазоне с помощью удобной функции
    • Удалите проверку данных со всех листов с кодом VBA

    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) Создавайте содержательные сообщения об ошибках (необязательно):

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

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

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

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

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

      

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

      

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

      

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

      

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

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

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

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

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

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

      

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

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

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

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

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

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

      

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

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

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

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

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

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

      

      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: разрешены только уникальные значения в диапазоне ячеек:

      Внимание: В приведенном выше коде A1: A100 и A: — это ячейки в столбце, дублирование которых вы хотите предотвратить, пожалуйста, измените их по своему усмотрению.

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

      

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

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

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

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

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

      

      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. Выберите список ячеек, в которых разрешен только определенный номер телефона, затем нажмите Кутулс > Предотвратить ввод > Подтвердить номер телефона, см. снимок экрана:

      

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

      

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

      

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

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

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

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

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

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

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

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

      

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

      

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

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

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

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

      

      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-адрес

      Внимание: В приведенном выше коде A2: A10 — это диапазон ячеек, который вы хотите принимать только IP-адресами.

      2. Затем сохраните и закройте этот код, теперь только правильные IP-адреса позволяют вводить в определенные ячейки.

      Форсировать только формат IP-адресов с помощью простой функции

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

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

      1. Выберите ячейки, в которые разрешено вводить только IP-адреса, затем нажмите Кутулс > Предотвратить ввод > Подтвердить 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. И все ячейки с проверкой данных были выбраны сразу на текущем листе.

      

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

      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 кнопку, чтобы закрыть это диалоговое окно. И правило проверки данных, примененное к выбранному диапазону, было сразу удалено.

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

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

      Если у вас есть Kutools for Excel, Его Очистить ограничения проверки данных Функция также может помочь удалить правила проверки данных из выбранного диапазона или всего рабочего листа.

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

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

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

      

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

      

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

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

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

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

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

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

      Трюк №16. Проверка данных на основе списка на другом листе Excel

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

      В этом трюке мы познакомим вас с двумя способами проверки данных на основе списка на другом листе. Первый из них использует преимущество именованных диапазонов Excel (которые подробнее рассматриваются в главе 3), а во втором применяется вызов функции.

      Способ 1. Именованные диапазоны

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

      Выделите ячейку, в которой должен будет появиться раскрывающийся список, а затем выберите команду Данные → Проверка (Data → Validation). В поле Тип данных (Allow) выберите пункт Список (List), а в поле Источник (Source) введите =MyRange . Щелкните на кнопке ОК. Поскольку вы использовали именованный диапазон, ваш список (хотя он и находится на другом листе) теперь можно использовать как список проверки.

      Способ 2. Функция ДВССЫЛ

      Функция ДВССЫЛ (INDIRECT) позволяет ссылаться на ячейку, содержащую текст, представляющий адрес ячейки. Эту ячейку можно использовать как локальную ссылку, даже если она получает данные из другого листа. Можно применять эту возможность для связи с листом, где расположен список.

      Предположим, список находится на листе Sheetl в диапазоне $А$1:$А$8 . Щелкните любую ячейку на другом листе, где должен появиться этот список проверки (список выборки). Затем выберите команду Данные → Проверка (Data → Validation) и в поле Тип данных (Allow) выберите пункт Список (List). В поле Источник (Source) введите следующий код: =INDIRECT(«Sheetl!$А$1:$А$8») , в русской версии Excel =ДВССЫЛ(«Sheetl!$A$1:$A$8») . Удостоверьтесь, что флажок Список допустимых значений (In-Cell) установлен, и щелкните на кнопке ОК. Список на листе Sheetl должен появиться в раскрывающемся списке проверки.

      Если имя листа, на котором расположен список, содержит пробелы, необходимо использовать следующий синтаксис функции ДВССЫЛ (INDIRECT): =INDIRECT(«‘Sheetl’!$А$1:$А$8») , в русской версии Excel =ДВССЫЛ(«‘Sheetl’!$А$1:$А$8») . Различие заключается в том, что здесь после первой кавычки стоит один апостроф, а второй апостроф находится перед восклицательным знаком.
      [stextbox всегда использовать одиночные апострофы, независимо от того, содержит имя пробелы или нет. С апострофами вы все так же сможете ссылаться на листы с именами без пробелов, и это также упростит внесение изменений позже.[/stextbox]

      Преимущества и недостатки обоих способов

      У именованных диапазонов и функции ДВССЫЛ (INDIRECT) есть преимущества и недостатки. Преимущество использования именованного диапазона заключается в том, что изменение названия листа не повлияет на список проверки. Это подчеркивает недостаток функции ДВССЫЛ (INDIRECT): любое изменение названия листа не будет автоматически в ней отражаться. Преимущество функции ДВССЫЛ (INDIRECT): когда из именованного диапазона будет удалена первая ячейка или строка либо последняя ячейка или строка, то именованный диапазон вернет ошибку #REF! . В этом недостаток именованного диапазона — если удалить из него ячейки или строки, изменения не повлияют на список проверки.

      Проверка ввода данных в Excel и ее особенности

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

      Проверка вводимых данных в Excel

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

      У нас имеется лист номенклатуры товаров магазина:

      1. Выделите столбец B и выберите инструмент «Данные»-«Проверка данных».
      2. Заполняем значениями поля на вкладке «Параметры» как показано на рисунке:
      3. На вкладке «Сообщение для ввода» создаем особое примечание, в котором при помещения курсора на ячейку будет отображаться условия для ее заполнения. И жмем ОК.

      Теперь проверим. В ячейку B2 введите натуральное число, а в ячейку B3 отрицательное. Как видно в ячейке B3 действие оператора набора – заблокировано. Отображается сообщение об ошибке: «Введенное значение неверно».

      Примечание. При желании можно написать собственный текст для ошибки на третей закладке настроек инструмента «Сообщение об ошибке».

      Чтобы удалить проверку данных в Excel нужно: выделить соответствующий диапазон ячеек, выбрать инструмент и нажать на кнопку «Очистить все» (указано на втором рисунке).

      Особенности проверки данных

      Данным способом проверяются данные только в процессе ввода. Если данные уже введенные они будут не проверенные. Например, в столбце B нельзя ввести текст после установки условий заполнения в нем ячеек. Но заголовок в ячейке B1 «Цена» остался без предупреждения об ошибке.

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

      Чтобы проверить соответствуют ли все введенные данные, определенным условиям в столбце и нет ли там ошибок, следует использовать другой инструмент: «Данные»-«Проверка данных»-«Обвести неверные данные».

      

      Если значения в столбце B должны соответствовать определенным условиям, но содержит ошибки, то все они будут обведены красным овалом. Этот инструмент очень удобно использовать, когда нужно проверить уже введенные или скопированные данные.

      Конечно, можно выполнить проверку данных в столбце с помощью логической функции Excel – «ЕСЛИ». Или условное форматирование. Но применение инструмента «Проверка данных» – более эффективно, удобно и продуктивно для данной задачи. Особенно если нам нужно одновременно выполнить проверку по нескольким столбцам. В таком случаи более заметна рациональность его использования.

      Как скопировать результат поиска в таблице Excel

      

      Нужно искать через фильтр или условное форматирование.

      Фильтр

      1. Выделяем столбец и жмём «Сортировка и фильтр» → включаем фильтр.

      

      2. Разворачиваем окно фильтра и в поле поиска указываем запрос. Я ищу https:// в столбце URL.

      

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

      

      Форматирование

      1. Выделяем столбец и жмём «Условное форматирование» → Правила выделения ячеек → Текст содержит…

      

      2. Указываем запрос. Я ищу https://

      

      3. Программа подсвечивает светло-красным цветом нужный результат, который можно скопировать.

      Читать:

      Почему не работает заменить траекторию в иллюстраторе