Проверка данных в ячейке Excel

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

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

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

Примеры возможностей проверки данных в Excel:

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

Например, можно задать правило, запрещающее вводить числа, не соответствующие диапазону от 100 до 999. Если пользователь вводит неверное значение, Excel покажет сообщение об ошибке, объясняющее причину.

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

Для добавления проверки данных в Excel выполните следующие шаги:

1. Открытие диалогового окна «Проверка данных».

Напомним, где находится кнопка проверки данных в Excel. Выбрав одну или несколько ячеек для проверки, перейдите на вкладку «Данные» > «Работа с данными» и нажмите «Проверка данных».

2. Создание правила проверки.

На вкладке «Настройки» задайте критерии проверки. Вы можете указать:

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

Пример: задайте правило, запрещающее вводить целые числа от 100 до 999.

После настройки правила нажмите «ОК» для закрытия окна или перейдите на другую вкладку для добавления подсказки по вводу и/или сообщения об ошибке.

3. Подсказка по вводу (необязательно).

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

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

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

4. Отображение предупреждения об ошибке (необязательно).

При вводе неверных данных можно настроить одно из следующих предупреждений:

Тип оповещения	Описание
Стоп (по умолчанию)	Самое строгое предупреждение, запрещающее ввод неверных данных. Нажмите «Повторить» для повторного ввода или «Отмена» для удаления записи.
Предупреждение	Сообщает о недопустимых данных, но не препятствует вводу. Нажмите «Да» для ввода, «Нет» для изменения или «Отмена» для удаления.
Информация	Информирует о неверных данных. Нажмите «ОК» для ввода или «Отмена» для удаления.

Для настройки пользовательского сообщения об ошибке:

• Перейдите на вкладку «Сообщение об ошибке».
• Установите флажок «Выводить сообщение об ошибке».
• Выберите тип оповещения в поле «Вид».
• Введите заголовок и текст сообщения.
• Нажмите «ОК».

Теперь, если пользователь вводит неверные данные, Excel отображает специальное предупреждение с пояснением.

Примечание: Если не ввести собственное сообщение, появится стандартное предупреждение со следующим текстом: «Это значение не соответствует ограничениям проверки данных, установленным для этой ячейки»

Ограничения проверки данных в Excel

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

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

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

Пример: ограничения проверки данных в Excel, допускающие ввод целых чисел больше 100.

Более подробно эти вопросы освещены в этой статье: Ограничения проверки данных в Excel.

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

Для изменения правила:

1. Выберите проверяемую ячейку.
2. Откройте диалоговое окно «Проверка данных».
3. Внесите необходимые изменения.
4. Установите флажок «Применить изменения ко всем другим ячейкам с теми же параметрами».
5. Нажмите «ОК».

Например, вы можете отредактировать список, добавив или удалив элементы в поле «Источник» и применив изменения ко всем ячейкам с тем же списком.

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

Для копирования ограничения проверки данных в Excel:

1. Выберите ячейку с правилом и нажмите Ctrl + C.
2. Выберите другие ячейки. Для выделения несмежных ячеек удерживайте Ctrl.
3. Щелкните правой кнопкой мыши, выберите «Специальная вставка» и параметр «Условия на значения». Либо используйте Ctrl + Alt + V, затем — Н.
4. Нажмите «ОК».

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

Как найти ячейки с проверкой данных в Excel

Для быстрого поиска всех проверенных ячеек на текущем листе: Перейдите на вкладку «Главная» > «Редактирование» и нажмите «Найти и выбрать» > «Проверка данных».

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

Как найти неверные данные на листе

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

Для поиска недопустимых данных: Перейдите на вкладку «Данные» и нажмите «Проверка данных» > «Обвести неверные данные».

Будут выделены все ячейки, не соответствующие критериям проверки. Исправленные записи автоматически снимают отметку. Для удаления всех отметок: «Данные» > «Проверка данных» > «Удалить обводку неверных данных».

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

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

Почему проверка данных в Excel не работает?

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

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

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

Для этого перейдите в «Файл» > «Параметры» > «Дополнительно» > «Параметры редактирования» и снимите флажок «Включить маркер заполнения и перетаскивание ячеек».

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

Кнопка «Проверка данных» становится недоступной (выделенной серым цветом), если вы вводите или редактируете данные в ячейке. Завершите редактирование ячейки, нажав Enter или Esc, чтобы выйти из режима редактирования, и затем выполните проверку данных.

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

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

Для этого сначала отмените общий доступ и/или снимите защиту с книги.

Неправильные формулы проверки данных

При использовании формул для проверки данных в Excel необходимо удостовериться в трех важных аспектах:

• Формула проверки не должна возвращать ошибки.
• Формула не должна ссылаться на пустые ячейки.
• Должны использоваться правильные ссылки на ячейки.

Ручной пересчет формул включен

Если в Excel активирован режим ручного расчета, невычисленные формулы могут препятствовать корректной проверке данных. Чтобы изменить параметр расчета на автоматический, перейдите на вкладку «Формулы» > группа «Расчет», нажмите «Параметры расчета» и выберите «Автоматически».

Ошибка в формулах проверки данных

Для начала скопируйте формулу проверки в любую ячейку, чтобы убедиться, что она не возвращает ошибку, такую как #Н/Д, #ЗНАЧ или #ДЕЛ/0!. Если вы создаете пользовательское правило, формула должна возвращать логические значения ИСТИНА или ЛОЖЬ либо соответствующие значения 1 и 0.

Формула проверки данных ссылается на пустую ячейку

Если при установке правила выбрана опция «Игнорировать пустые ячейки» (обычно включена по умолчанию), убедитесь, что ваши формулы или условия не ссылаются на пустую ячейку. Если одна или несколько ячеек, на которые ссылается ваша формула, пусты, то в проверяемой ячейке будет разрешено любое значение.

Абсолютные и относительные ссылки на ячейки в формулах проверки

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

Чтобы лучше это понять, рассмотрим пример. Предположим, вы хотите ограничить ввод данных в ячейки от A2 до A8 целыми числами от 100 до 999. Для удобства изменения критериев используйте ссылки на ячейки с этими значениями, как показано на изображении ниже:

Это правило будет правильно работать только для первой ячейки диапазона – A2. Для ячейки A3 критерии максимума и минимума изменятся на E3 и F3.

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

Чтобы исправить формулу, добавьте знак «$» перед ссылками на столбцы и строки: =$E$2 и =$F$2. Или используйте клавишу F4 для переключения между различными типами ссылок.

Ответы на часто задаваемые вопросы (FAQ)

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