Проверка данных — первая линия обороны от некорректного ввода
Если таблица Excel предназначена для заполнения коллегами, задайте допустимые числа, даты и варианты ответов. Это поможет обнаружить ошибки при ручном вводе.
Защита листа ограничивает изменение заблокированных ячеек с формулами. Но что делать с ячейками, в которые данные вводить нужно и можно, но строго в определённом формате? Именно здесь работает Проверка данных (Data Validation).
Этот инструмент задаёт правила для ячейки: что туда можно вводить, а что нельзя. Для ручного ввода можно включить сообщение об ошибке: оно либо запрещает недопустимое значение, либо позволяет принять его после предупреждения. Подсказка при выборе ячейки настраивается отдельно и сама ничего не блокирует.
В настольном Excel 2016–2024 и Microsoft 365 для Windows инструмент находится на вкладке Данные → Проверка данных. Он также доступен на Mac и в Excel для Интернета; расположение параметров и названия элементов могут отличаться. Ниже описан диалог Windows.
Типы проверок и их применение
| Тип | Что разрешает | Пример условия | Применение |
|---|---|---|---|
| Целое число | Только целые числа в диапазоне | От 1 до 100 | Количество, возраст, баллы |
| Десятичное | Числа, в том числе целые, по заданному условию | От 0 до 1 | Проценты, коэффициенты |
| Дата | Только даты в заданном диапазоне | После 01.01.2020 | Даты договоров, событий |
| Список | Только значения из заданного списка | Да; Нет; Не определено | Статусы, категории, выборы |
| Длина текста | Значения с длиной, соответствующей условию | Не более 10 символов | Коды, идентификаторы |
| Другой (формула) | Значение, удовлетворяющее формуле | Формула, возвращающая ИСТИНА для допустимого значения | Сложные составные правила |
Выпадающий список — самый популярный инструмент
Тип «Список» заслуживает отдельного внимания: он превращает ячейку в выпадающий список, из которого пользователь выбирает готовое значение. Выбор готового варианта снижает риск опечаток. Чтобы отклонять недопустимые значения при ручном вводе, включите сообщение об ошибке типа «Остановка» (Stop). Сам список не гарантирует единый регистр и не контролирует все способы вставки данных.
Создание выпадающего списка:
- Выделите ячейку или диапазон, где нужен список.
- Данные → Проверка данных → тип «Список».
- В поле «Источник» введите варианты с разделителем, принятым в региональных настройках; в типичной русской настройке это точка с запятой:
Да;Нет;В работе— или укажите диапазон ячеек с готовым справочником, например =$H$2:$H$4. - Включите отображение списка в ячейке, настройте сообщение об ошибке типа «Остановка» и нажмите ОК. Проверьте допустимое и недопустимое значение.
Ссылка на справочник позволяет менять варианты в исходных ячейках. Обычный фиксированный диапазон, например $H$2:$H$4, не включает новую строку H5 автоматически: расширьте источник проверки. Для автоматического расширения можно использовать справочник в таблице Excel, как описано в справке Microsoft.
Три режима реагирования на ошибку
В настройках проверки данных можно задать, как Excel реагирует на некорректный ввод (вкладка «Сообщение об ошибке»):
| Режим | Что делает | Когда использовать |
|---|---|---|
| Остановка | При включённом сообщении об ошибке отклоняет недопустимый ручной ввод | Поля, где ошибка недопустима (суммы, коды) |
| Предупреждение | Показывает предупреждение, но пользователь может игнорировать | Желательный, но не обязательный формат |
| Информационное сообщение (Information) | Сообщает об ошибке; пользователь может принять значение кнопкой ОК | Мягкое уведомление о нарушении правила |