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

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

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

1. Что такое проверка данных в Excel?

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

Некоторые основные варианты использования функции проверки данных:

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

2. Как добавить проверку данных в Excel?

В листе Excel вы можете добавить проверку данных следующим образом:

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

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

• "Значения": Введите числа непосредственно в поля критериев;
• "Ссылка на ячейку": Сошлитесь на ячейку на этом или другом листе;
• "Формулы": Создайте более сложные условия с помощью формул.

В качестве примера я создам правило, разрешающее ввод только целых чисел от100 до1000. Критерии задаются, как показано на скриншоте ниже:

3. После настройки условий можно перейти на вкладку "Ввод сообщения" или "Предупреждение об ошибке", чтобы задать сообщение для ячеек с проверкой данных. (Если не хотите настраивать предупреждение, просто нажмите "ОК" для завершения.)

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

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

Перейдите на вкладку "Ввод сообщения" и выполните следующие действия:

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

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

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

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

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

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

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

Опция "Стоп": нажмите "Повторить", чтобы ввести значение заново, или "Отмена", чтобы отменить ввод.

Опция "Предупреждение": нажмите "Да", чтобы принять неверный ввод, "Нет" — чтобы изменить его, или "Отмена" — чтобы отменить.

Опция "Информация": нажмите "ОК", чтобы принять неверный ввод, или "Отмена", чтобы отменить.

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

3. Базовые примеры проверки данных

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

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

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

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

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

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

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

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

1. Выделите диапазон ячеек, где разрешён только ввод определённых дат или времени, затем нажмите "Данные" > "Проверка данных" > "Проверка данных".

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

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

3. Теперь в выбранных ячейках можно ввести только даты, большие8/20/2021.

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

Если нужно ограничить количество символов, которые можно ввести в ячейку (например, не более10 символов для определённого диапазона), функция "Проверка данных" также поможет.

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

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

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

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

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

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

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

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

• Выберите "Список" в выпадающем списке "Разрешить".
• В поле "Источник" введите элементы списка через запятую. Например, чтобы ограничить выбор тремя вариантами, введите Not Started,In Progress,Completed, или выберите диапазон ячеек с этими значениями.
• Наконец, нажмите кнопку "ОК".

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

Подробнее о раскрывающемся списке…

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

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

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

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

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

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

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите следующую формулу в поле "Формула". ("A2" — первая ячейка выбранного диапазона)

  =ISNUMBER(A2)

• Нажмите кнопку "ОК", чтобы закрыть это окно.

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

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

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

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

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

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите следующую формулу в поле "Формула". ("A2" — первая ячейка выбранного диапазона)

  =ISTEXT(A2)

• Нажмите кнопку "ОК", чтобы закрыть это окно.

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

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

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

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

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

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

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите формулу ниже в поле "Формула".

  =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))

• Нажмите кнопку "ОК", чтобы закрыть это окно.

Примечание: в приведённых формулах "A2" — первая ячейка выбранного диапазона.

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

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

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

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

2. Затем нажмите "Kutools" > "Ограничить ввод" > "Ограничить ввод", как показано на скриншоте:

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

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

4.3 Проверка данных: разрешить текст, начинающийся или заканчивающийся определёнными символами

Если все значения в определённом диапазоне должны начинаться или заканчиваться определённым символом или подстрокой, используйте проверку данных с пользовательской формулой на основе функций EXACT, LEFT, RIGHT или COUNTIF.

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

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

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

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите формулу ниже в поле "Формула".
  Разрешить ввод только текста, начинающегося с CN:

  =EXACT(LEFT(A2,2),"CN")

  Разрешить ввод только текста, заканчивающегося на CN:

  =EXACT(RIGHT(A2,2),"CN")

• Нажмите кнопку "ОК", чтобы закрыть это окно.

Примечание: в приведённых формулах "A2" — первая ячейка выбранного диапазона, число "2" — количество символов, "CN" — текст, с которого начинается или которым заканчивается значение.

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

=COUNTIF(A2,"CN*")

=COUNTIF(A2,"*CN")

Примечание: звёздочка * — это подстановочный знак, который соответствует одному или нескольким символам.

Разрешить текст, начинающийся или заканчивающийся определёнными символами, с несколькими критериями (логика ИЛИ)

Например, если вы хотите, чтобы текст начинался или заканчивался на "CN" или "UK", как показано на скриншоте ниже, добавьте ещё одну функцию EXACT с помощью знака плюс (+). Выполните следующие шаги:

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

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите формулу ниже в поле "Формула".
  Разрешить ввод только текста, начинающегося с CN или UK:

  =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")

  Разрешить ввод только текста, заканчивающегося на CN или UK:

  =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")

• Нажмите кнопку "ОК", чтобы закрыть это окно.

Примечание: в приведённых формулах "A2" — первая ячейка выбранного диапазона, число "2" — количество символов, "CN" и "UK" — тексты, с которых начинается или которыми заканчивается значение.

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

=COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")

=COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")

Примечание: звёздочка * — это подстановочный знак, который соответствует одному или нескольким символам.

4.4 Проверка данных: разрешить/запретить значения, содержащие определённый текст

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

Разрешить значения, которые должны содержать один или несколько определённых текстов

Разрешить значения, которые должны содержать определённый текст

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

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

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите одну из формул ниже в поле "Формула".
  С учётом регистра:

  =ISNUMBER(FIND("KTE",A2)) 

  Без учёта регистра:

  =ISNUMBER(SEARCH("KTE",A2))

• Нажмите кнопку "ОК", чтобы закрыть это окно.

Примечание: в приведённых формулах "A2" — первая ячейка выбранного диапазона, "KTE" — текст, который должен содержаться в значениях.

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

Разрешить значения, которые должны содержать один из нескольких определённых текстов

Приведённая выше формула работает только для одной строки. Если нужно разрешить любой из нескольких текстов, как показано на скриншоте ниже, используйте функции SUMPRODUCT, FIND и ISNUMBER вместе для создания формулы.

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

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите одну из формул ниже в поле "Формула".
  С учётом регистра:

  =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0

  Без учёта регистра:

  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0

• Нажмите "ОК", чтобы закрыть диалоговое окно.

Примечание: в приведённых формулах "A2" — первая ячейка выбранного диапазона, "C2:C4" — список значений, которые разрешено содержать.

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

Разрешить значения, которые не должны содержать один или несколько определённых текстов

Разрешить значения, которые не должны содержать определённый текст

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

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

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите одну из формул ниже в поле "Формула".
  С учётом регистра:

  =ISERROR(FIND("KTE",A2))

  Без учёта регистра:

  =ISERROR(SEARCH("KTE",A2))

• Нажмите кнопку "ОК", чтобы закрыть это окно.

Примечание: в приведённых формулах "A2" — первая ячейка выбранного диапазона, "KTE" — текст, который не должен содержаться в значениях.

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

Разрешить значения, которые не должны содержать один из нескольких определённых текстов

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

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

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите формулу ниже в поле "Формула".
  С учётом регистра:

  =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0

  Без учёта регистра:

  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0

• Нажмите "ОК", чтобы закрыть диалоговое окно.

Примечание: в приведённых формулах "A2" — первая ячейка выбранного диапазона, "C2:C4" — список значений, которые запрещено содержать.

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

4.5 Проверка данных: разрешить только уникальные значения

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

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

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

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

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите формулу ниже в поле "Формула".

  =COUNTIF($A$2:$A$9,A2)=1

• Нажмите кнопку "ОК", чтобы закрыть это окно.

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

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

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

Следующий VBA-код также поможет предотвратить ввод дубликатов. Выполните следующие действия:

1. Щёлкните правой кнопкой мыши по вкладке листа, где нужно разрешить только уникальные значения, и выберите "Просмотреть код" в контекстном меню. В открывшемся окне "Microsoft Visual Basic for Applications" скопируйте и вставьте следующий код в пустой модуль:

VBA-код: разрешить только уникальные значения в диапазоне ячеек:

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

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

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

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

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

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

2. Затем нажмите "Kutools" > "Ограничить ввод" > "Предотвратить дублирование записей", как показано на скриншоте:

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

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

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

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

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

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите одну из нужных формул в поле "Формула".
  Разрешить только текст в верхнем регистре:

  =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2))

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

  =AND(EXACT(A2,LOWER(A2)),ISTEXT(A2))

  Разрешить только текст с первой заглавной буквой

  =AND(EXACT(A2,PROPER(A2)),ISTEXT(A2))

• Нажмите кнопку "ОК", чтобы закрыть это окно.

Примечание: в приведённой формуле "A2" — первая ячейка столбца, который вы используете.

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

4.7 Проверка данных: разрешить значения, которые есть/нет в другом списке

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

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

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

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

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

  =COUNTIF($C$2:$C$4,A2)>0

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

  =COUNTIF($C$2:$C$4,A2)=0

• Нажмите кнопку "ОК", чтобы закрыть это окно.

Примечание: в приведённой формуле "A2" — первая ячейка столбца, который вы используете, "C2:C4" — список значений, которые нужно разрешить или запретить.

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

4.8 Проверка данных: разрешить ввод только в формате номера телефона

При вводе информации о сотрудниках компании один из столбцов должен содержать номер телефона. Чтобы обеспечить быстрый и точный ввод номеров, можно задать проверку данных для номеров телефонов. Например, разрешить только формат (123)456-7890. В этом разделе представлены два быстрых способа решения задачи.

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

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

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

2. В диалоговом окне "Формат ячеек" на вкладке "Число" выберите "Пользовательский" в списке "Категория" слева, затем введите нужный формат номера телефона в поле "Тип". Например, используйте формат "(###) ###-####", как показано на скриншоте:

3. Затем нажмите "ОК", чтобы закрыть диалоговое окно.

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите следующую формулу в поле "Формула".

  =AND(ISNUMBER(A2),LEN(A2)=10)

• Нажмите кнопку "ОК", чтобы закрыть это окно.

Примечание: в приведённой формуле "A2" — первая ячейка столбца, в котором нужно проверить номер телефона.

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

Примечание: если введённое число не состоит из10 цифр, появится предупреждение, как показано на скриншоте:

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

Функция "Проверка номера телефона" в Kutools для Excel также позволяет разрешить ввод только номеров телефона нужного формата всего за несколько кликов.

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

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

3. После выбора или настройки формата номера телефона нажмите "ОК". Теперь разрешён только ввод номеров с этим форматом, иначе появится предупреждение, как показано на скриншоте:

4.9 Проверка данных: разрешить ввод только адресов электронной почты

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

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

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

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

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите следующую формулу в поле "Формула":

  =ISNUMBER(MATCH("*@*.?*",A2,0))

• Нажмите кнопку "ОК", чтобы закрыть это окно.

Примечание: в приведённой формуле "A2" — первая ячейка столбца, который вы используете.

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

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

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

1. Выделите ячейки, в которых разрешён только ввод адресов электронной почты, затем нажмите "Kutools" > "Ограничить ввод" > "Можно вводить только адреса электронной почты". Как показано на скриншоте:

2. Теперь разрешён только ввод адресов электронной почты, иначе появится предупреждение, как показано на скриншоте:

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

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

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

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

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

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите формулу ниже в поле "Формула".

  =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))

• Нажмите кнопку "ОК", чтобы закрыть это окно.

Примечание: в приведённой формуле "A2" — первая ячейка столбца, который вы используете.

3. Теперь при вводе некорректного IP-адреса появится предупреждение, как показано на скриншоте ниже:

Разрешить ввод только в формате IP-адреса с помощью VBA-кода

Следующий VBA-код также поможет разрешить только ввод IP-адресов и запретить другие значения. Выполните следующие действия:

1. Щёлкните правой кнопкой мыши по вкладке листа и выберите "Просмотреть код" в контекстном меню. В открывшемся окне "Microsoft Visual Basic for Applications" скопируйте приведённый ниже код VBA.

VBA-код: разрешить ввод только IP-адресов в ячейки

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

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

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

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

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

1. Выделите ячейки, в которых разрешён только ввод IP-адресов, затем нажмите "Kutools" > "Ограничить ввод" > "Можно вводить только IP-адреса". Как показано на скриншоте:

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

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

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

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

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите формулу ниже в поле "Формула".

  =SUM($B$2:$B$7)<=18000

• Нажмите кнопку "ОК", чтобы закрыть это окно.

Примечание: в приведённой формуле "B2:B7" — диапазон ячеек, в которых нужно ограничить значения.

3. Теперь при вводе значений в диапазон B2:B7, если сумма меньше $18000, проверка пройдёт успешно. Если сумма превысит $18000, появится предупреждение.

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

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

Для решения этой задачи выполните следующие действия:

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

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите формулу ниже в поле "Формула".

  =$C$1="Yes"

• Нажмите кнопку "ОК", чтобы закрыть это окно.

Примечание: в приведённой формуле "C1" — ячейка с нужным текстом, а "Yes" — текст, на основе которого ограничивается ввод. Измените их по необходимости.

3. Теперь, если в ячейке C1 текст "Yes", в диапазоне A2:A9 можно ввести любые значения. Если в C1 другой текст, ввод будет запрещён, как показано в примере ниже:

4.13 Проверка данных: разрешить ввод только рабочих дней или выходных

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

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

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите одну из формул ниже в поле "Формула".
  Разрешить только рабочие дни

  =WEEKDAY(A2,2)<6

  Разрешить только выходные

  =WEEKDAY(A2,2)>5

• Нажмите кнопку "ОК", чтобы закрыть это окно.

Примечание: в приведённой формуле "A2" — первая ячейка столбца, который вы используете.

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

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

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

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

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите формулу ниже в поле "Формула".

  =A2>Today()

• Нажмите кнопку "ОК", чтобы закрыть это окно.

Примечание: в приведённой формуле "A2" — первая ячейка столбца, который вы используете.

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

=A2<Today()

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

4.15 Проверка данных: разрешить ввод времени на основе текущего времени

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

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

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

• Выберите "Время" в выпадающем списке "Разрешить".
• Затем выберите "меньше", чтобы разрешить только время до текущего, или "больше", чтобы разрешить только время после текущего, в выпадающем списке "Данные".
• Затем в поле "Время окончания" или "Время начала" введите формулу ниже:

  =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

• Нажмите кнопку "ОК", чтобы закрыть это окно.

Примечание: в приведённой формуле "A2" — первая ячейка столбца, который вы используете.

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

4.16 Проверка данных: разрешить ввод даты определённого или текущего года

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

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

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

• Выберите "Пользовательский" в выпадающем списке "Разрешить".
• Затем введите формулу ниже в поле "Формула".

  =YEAR(A2)=2020

• Нажмите кнопку "ОК", чтобы закрыть это окно.

Примечание: в приведённой формуле "A2" — первая ячейка столбца, который вы используете, "2020" — номер года, который нужно ограничить.

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

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

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

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

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

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

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

• Выберите "Дата" в выпадающем списке "Разрешить".
• Затем выберите "между" в выпадающем списке "Данные".
• В поле "Начальная дата" введите формулу:

  =TODAY()-WEEKDAY(TODAY(),3)

• В поле "Конечная дата" введите формулу:

  =TODAY()-WEEKDAY(TODAY(),3)+6

• Наконец, нажмите кнопку "ОК".

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

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

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

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

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

• Выберите "Дата" в выпадающем списке "Разрешить".
• Затем выберите "между" в выпадающем списке "Данные".
• В поле "Начальная дата" введите формулу:

  =DATE(YEAR(TODAY()),MONTH(TODAY()),1)

• В поле "Конечная дата" введите формулу:

  =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1))

• Наконец, нажмите кнопку "ОК".

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

5. Как изменить проверку данных в Excel?

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

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

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

3. Нажмите "ОК", чтобы сохранить изменения.

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

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

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

2. Затем нажмите "Главная" > "Найти и выделить" > "Выделить группу", как показано на скриншоте:

3. В диалоговом окне "Выделить группу" выберите "Проверка данных" > "Все", как показано на скриншоте:

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

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

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

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

1. Выделите ячейку с нужным правилом проверки данных и нажмите "Ctrl + C" для копирования.

2. Затем выделите ячейки, в которые нужно применить правило. Чтобы выбрать несколько несмежных ячеек, удерживайте клавишу "Ctrl" при выборе.

3. Затем щёлкните правой кнопкой мыши по выделенному диапазону и выберите "Специальная вставка", как показано на скриншоте:

4. В диалоговом окне "Специальная вставка" выберите опцию "Проверка данных", как показано на скриншоте:

5. Нажмите кнопку "ОК" — теперь правило проверки данных скопировано в новые ячейки.

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

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

Чтобы обвести неверные данные, сначала примените функцию "Проверка данных" для диапазона. Выполните следующие шаги:

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

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

3. Затем нажмите "ОК", чтобы закрыть диалоговое окно. После установки правила нажмите "Данные" > "Проверка данных" > "Обвести неверные данные" — все неверные значения (меньше500) будут обведены красным овалом. Как показано на скриншотах:

Примечания:

• 1. Как только вы исправите неверное значение, красный круг исчезнет автоматически.
• 2. Функция "Обвести неверные данные" может выделить не более255 ячеек. При сохранении книги все круги будут удалены.
• 3. Эти круги не печатаются.
• 4. Вы также можете удалить красные круги, нажав "Данные" > "Проверка данных" > "Очистить круги проверки данных".

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

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

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

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

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

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

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

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

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

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

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

3. В появившемся окне нажмите "ОК" — правило проверки данных будет удалено.

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

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

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

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

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

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

3. Затем нажмите клавишу "F5" для запуска кода — все правила проверки данных будут немедленно удалены из всей книги.