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

АвторСяоянДата изменения2025-04-15

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

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

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

Оглавление:

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

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

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

3,1 Проверка данных для целых чисел и десятичных дробей
3,2 Проверка данных для даты и времени
3,3 Проверка данных для Длина текста
3,4 Проверка данных в виде списка (Раскрывающийся список)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Выделите диапазон ячеек, для которых нужно настроить проверку данных, и выберите «Данные» > «Проверка данных» > «Проверка данных» (см. снимок экрана):

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

«Значения»: введите числа непосредственно в поля критериев;
«Ссылка на ячейку»: укажите ссылку на ячейку текущего или другого листа;
«Формулы»: создавайте более сложные формулы в качестве условий.

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

3. После настройки условий перейдите на вкладку «Сообщение для ввода» или «Сообщение об ошибке», чтобы задать соответствующее сообщение для ячеек с проверкой — по вашему усмотрению. (Если предупреждение задавать не требуется, нажмите «OK», чтобы завершить настройку.)

3,1) Добавление сообщения для ввода (необязательно):

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

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

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

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

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

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

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

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

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

Вариант «Остановить»: нажмите «Повторить», чтобы ввести значение снова, или «Отмена», чтобы прервать ввод.

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

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

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

Снимок экрана стандартного диалогового окна предупреждения «Остановить» при проверке данных в Excel

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. В диалоговом окне «Проверка данных» перейдите на вкладку «Параметры» и выполните следующие действия:

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

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

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

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

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

4,1 Проверка данных, разрешающая только числа или текст

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

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

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

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

Выберите «Пользовательский» в раскрывающемся списке «Тип данных».
Затем введите следующую формулу в текстовое поле «Формула». («A2» — первая ячейка выбранного диапазона, которую необходимо ограничить.)
```
=ISNUMBER(A2)
```
Нажмите кнопку «ОК», чтобы закрыть это диалоговое окно.

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

Примечание. Функция «ISNUMBER» распознаёт любые числовые значения в проверяемых ячейках, включая целые числа, десятичные и обыкновенные дроби, а также даты и время.

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

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

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

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

Выберите «Пользовательский» в выпадающем списке «Тип данных».
Затем введите следующую формулу в текстовое поле «Формула». («A2» — первая ячейка выбранного диапазона, которую необходимо ограничить.)
```
=ISTEXT(A2)
```
Нажмите кнопку «ОК», чтобы закрыть это диалоговое окно.

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

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

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

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

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

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

Выберите «Пользовательский» в выпадающем списке «Тип данных».

Затем введите приведённую ниже формулу в поле «Формула».

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

Нажмите кнопку «ОК», чтобы закрыть это диалоговое окно.

Примечание. В приведённых выше формулах «A2» — это первая ячейка выбранного диапазона, которую необходимо ограничить.

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

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

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

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

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

2. Затем нажмите «Kutools» > «Ограничить ввод» > «Ограничить ввод» (см. скриншот):

3. В появившемся диалоговом окне «Ограничить ввод» выберите опцию «Запретить ввод специальных символов» (см. скриншот).

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

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

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

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

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

=COUNTIF(A2,"CN*")

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

=COUNTIF(A2,"*CN")

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

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

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

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

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

Совет: чтобы игнорировать регистр, используйте приведённые ниже формулы с функцией СЧЁТЕСЛИ:

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

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

Разрешить ввод только текста, заканчивающегося на CN или 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. Выделите диапазон ячеек, в которые можно вводить только текст, содержащий хотя бы один из нескольких заданных элементов.

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

Выберите «Пользовательский» в выпадающем списке «Тип данных».
Затем введите одну из приведённых ниже формул в текстовое поле «Формула».
С учетом регистра:
```
=ISERROR(FIND("KTE",A2))
```
Не С учетом регистра:
```
=ISERROR(SEARCH("KTE",A2))
```
Нажмите кнопку «ОК», чтобы закрыть это диалоговое окно.

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

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

Разрешить ввод, не содержащий ни одного из нескольких специфических текстов

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

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

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

Выберите «Пользовательский» в выпадающем списке «Тип данных».
Затем введите приведённую ниже формулу в поле «Формула».
```
=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 будет появляться предупреждающее окно, как показано на снимке экрана ниже:

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

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

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

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

2. Затем выберите «Kutools» > «Ограничить ввод» > «Предотвратить дублирование записей» (см. снимок экрана):

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

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

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

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

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

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

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

Снимок экрана пункта меню «Просмотреть код» в контекстном меню

Снимок экрана редактора VBA с добавленным кодом проверки IP-адреса на листе

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

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

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

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

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

2. После применения этой функции вводить можно будет только IP-адреса — в противном случае появится окно с предупреждением (см. снимок экрана):

4,11 Проверка данных: ограничение значений, превышающих общую сумму

Представьте, что у вас есть отчёт о ежемесячных расходах, а общий бюджет составляет $18 000. Чтобы сумма расходов в списке не превышала установленный лимит — как показано на снимке экрана ниже, — можно создать правило проверки данных с использованием функции СУММ (SUM), которое запретит ввод значений, превышающих этот лимит.

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

Выберите «Пользовательский» в выпадающем списке «Тип данных».
Затем введите приведённую ниже формулу в поле «Формула».
```
=SUM($B$2:$B$7)<=18000
```
Нажмите кнопку «ОК», чтобы закрыть это диалоговое окно.

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

3. Теперь при вводе значений в диапазон B2:B7 проверка будет пройдена, если их сумма не превысит $18 000. Если ввод какого-либо значения приведёт к превышению этой суммы, появится предупреждающее сообщение.

4,12 Проверка данных: ограничение ввода в ячейки на основе значения другой ячейки

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

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

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

Выберите «Пользовательский» в выпадающем списке «Тип данных».
Затем введите приведённую ниже формулу в поле «Формула».
```
=$C$1="Yes"
```
Нажмите кнопку «ОК», чтобы закрыть это диалоговое окно.

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

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

4,13 Проверка данных: разрешение ввода только будних дней или выходных

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

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

Выберите «Пользовательский» в выпадающем списке «Тип данных».
Затем введите в текстовое поле «Формула» одну из приведённых ниже формул в зависимости от ваших потребностей.
Разрешить только будние дни
```
=WEEKDAY(A2,2)<6
```
Разрешить только выходные дни
```
=WEEKDAY(A2,2)>5
```
Нажмите кнопку «ОК», чтобы закрыть это диалоговое окно.

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

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

4,14 Проверка данных: разрешение ввода даты на основе текущей даты

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

1. Select the list of cells where you want only the future date (date greater than today) для ввода.

Выберите «Пользовательский» в выпадающем списке «Тип данных».
Затем введите приведённую ниже формулу в поле «Формула».
```
=A2>,Today()
```
Нажмите кнопку «ОК», чтобы закрыть это диалоговое окно.

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

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

Совет:

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

=A2<,Today()

2. Чтобы разрешить ввод дат только в заданном диапазоне — например, в течение следующих 30 дней, — введите приведённую ниже формулу в параметры проверки данных:

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

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

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

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

Выберите «Время» в раскрывающемся списке «Тип данных».
Затем в раскрывающемся списке «Данные» выберите «меньше чем», чтобы разрешить только время до текущего момента, или «больше чем» — чтобы разрешить время после него, в зависимости от ваших задач.
Затем в поле «Время окончания» или «Время начала» введите следующую формулу:
```
=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
```
Нажмите кнопку «ОК», чтобы закрыть это диалоговое окно.

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

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

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

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

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

Выберите «Пользовательская» в раскрывающемся списке «Разрешить».
Затем введите приведённую ниже формулу в поле «Формула».
```
=YEAR(A2)=2020
```
Нажмите кнопку «ОК», чтобы закрыть это диалоговое окно.

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

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

Совет:

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

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

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

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

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

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

Выберите «Дата» в раскрывающемся списке «Разрешить».
Затем в раскрывающемся списке «Данные» выберите пункт «между».
В текстовом поле «Дата начала» введите эту формулу:
```
=TODAY()-WEEKDAY(TODAY(),3)
```
В текстовом поле «Конечная дата» введите эту формулу:
```
=TODAY()-WEEKDAY(TODAY(),3)+6
```
Наконец, нажмите кнопку «ОК».

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

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

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

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

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

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

Лучшие инструменты повышения продуктивности в Office

🤖	KUTOOLS AI Помощник: Преобразуйте Анализ данных с помощью:Интеллектуального выполнения \| Генерации кода\| Создания пользовательские формулы \| Анализа данных и построения диаграмм\| Вызова Расширенные функции…
	Популярные функции:Поиск, выделение или Отметить дубликаты \| Удалить пустые строки \| Объединить столбцы или ячеек без потери данных \| Округление без использования формул…
	Супер ПОИСК:VLookup по нескольким критериям \| VLookup по нескольким значениям \| VLookup по нескольким листам \| Распознавание нечетких соответствий…
	Расширенный раскрывающийся список:Быстрое создание выпадающего списка \| Зависимый выпадающий список \| Выпадающий список с множественным выбором…
	Управление столбцами:Добавление заданного количества столбцов\|Перемещение столбцов\|Переключение видимости скрытых столбцов\|Сравнение диапазонов и столбцов…
	Избранные функции:Сетка фокусировки \| Просмотр дизайна \|Улучшенная строка формулы \| Управление рабочими книгами и листами \| Библиотека ресурсов(автотекст)\| Выбор даты \| Объединить листы \| Шифрование/Расшифровать ячейки \| Отправка писем по списку \| Супер фильтр \| Специальный фильтр(Фильтр ячеек с жирным шрифтом/курсив/зачёркивание…) …
	Лучшие наборы инструментов 15:12 Текстовыеинструменты(Добавить текст,Удалить определенные символы, …)\| 50+Типыдиаграмм(Диаграмма Ганта, …)\| 40+ Практические формулы(Рассчитать возраст на основе даты рождения, …)\| 19 Инструментывставки(Вставить QR-код,Вставка изображения по пути, …)\| 12 Инструментыпреобразования(Преобразовать в слова,Конвертация валют, …)\| 7 Объединить и разделитьИнструменты(Расширенное объединение строк,Разделить ячейки, …)\|… и многое другое

Используйте Kutools на предпочитаемом языке — поддержка английского, испанского, немецкого, французского, китайского и ещё 40+ языков!

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

Подробнее о Kutools для Excel…Бесплатная загрузка…

Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу

Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!

Подробнее о Office Tab…Бесплатная загрузка…

Все надстройки Kutools — один установщик

Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.

Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек

Подробнее о Kutools for Office…Бесплатная загрузка…

🤖	KUTOOLS AI Помощник: Преобразуйте Анализ данных с помощью:Интеллектуального выполнения \| Генерации кода\| Создания пользовательские формулы \| Анализа данных и построения диаграмм\| Вызова Расширенные функции…
	Популярные функции:Поиск, выделение или Отметить дубликаты \| Удалить пустые строки \| Объединить столбцы или ячеек без потери данных \| Округление без использования формул…
	Супер ПОИСК:VLookup по нескольким критериям \| VLookup по нескольким значениям \| VLookup по нескольким листам \| Распознавание нечетких соответствий…
	Расширенный раскрывающийся список:Быстрое создание выпадающего списка \| Зависимый выпадающий список \| Выпадающий список с множественным выбором…
	Управление столбцами:Добавление заданного количества столбцов\|Перемещение столбцов\|Переключение видимости скрытых столбцов\|Сравнение диапазонов и столбцов…
	Избранные функции:Сетка фокусировки \| Просмотр дизайна \|Улучшенная строка формулы \| Управление рабочими книгами и листами \| Библиотека ресурсов(автотекст)\| Выбор даты \| Объединить листы \| Шифрование/Расшифровать ячейки \| Отправка писем по списку \| Супер фильтр \| Специальный фильтр(Фильтр ячеек с жирным шрифтом/курсив/зачёркивание…) …
	Лучшие наборы инструментов 15:12 Текстовыеинструменты(Добавить текст,Удалить определенные символы, …)\| 50+Типыдиаграмм(Диаграмма Ганта, …)\| 40+ Практические формулы(Рассчитать возраст на основе даты рождения, …)\| 19 Инструментывставки(Вставить QR-код,Вставка изображения по пути, …)\| 12 Инструментыпреобразования(Преобразовать в слова,Конвертация валют, …)\| 7 Объединить и разделитьИнструменты(Расширенное объединение строк,Разделить ячейки, …)\|… и многое другое