Проверка данных в Google Таблицах: числа, даты, формулы и сообщения об ошибках

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

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

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

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

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

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

Встроенные критерии

В разделе «Критерии» панели проверки данных находится единый список условий – без деления на предварительные типы. Выбираешь нужное условие, и ниже появляются поля для его настройки.

• Условия для чисел: «Больше», «Больше или равно», «Меньше», «Меньше или равно», «Равно», «Не равно», «Между», «Не между». Подходят для любых числовых столбцов – от количества товара до процентов выполнения плана.

• Условия для дат образуют отдельную группу: «Допустимая дата» (любая корректная дата в любом формате), «Дата до», «В этот день или раньше», «Дата после», «В этот день или позже», «Дата в промежутке», «Дата не в промежутке». Этого набора достаточно для большинства задач с временными диапазонами – от дедлайнов до дат поставок.

• Условия для текста: «Текст содержит», «Текст не содержит», «Текст в точности» – для точного совпадения со строкой. Отдельно стоят «Допустимый адрес электронной почты» и «Допустимый URL» – встроенные шаблоны, которые проверяют базовый формат без написания регулярного выражения вручную. Реальное существование адреса или доступность ссылки они не проверяют.

• «Раскрывающийся список» и «Раскрывающийся список (из диапазона)» – первый принимает значения, введенные вручную через запятую, второй тянет список из указанного диапазона ячеек и обновляется автоматически при изменении источника. Это самый распространенный тип правила для статусных столбцов, категорий и любых полей с ограниченным набором вариантов.

• «Флажок» ограничивает ячейку двумя состояниями: установлен или снят, то есть ИСТИНА и ЛОЖЬ. Кастомные значения тоже поддерживаются – например «Да» и «Нет» или «Открыт» и «Закрыт» вместо стандартных логических значений.

• «Ваша формула» – пользовательское условие на основе произвольной формулы, которая должна возвращать ИСТИНА или ЛОЖЬ. Это самый гибкий вариант, подробнее о нем – в отдельном разделе ниже.

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

Реакция на неверный ввод

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

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

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

Подсказка при выборе ячейки

Кроме сообщения об ошибке, можно настроить подсказку, которая появляется при выборе ячейки еще до того, как пользователь что-то ввел. Она объясняет, какой именно формат или диапазон значений ожидается. Для этого в «Расширенных настройках» включите параметр «Показывать текст справки для выбранной ячейки» и введите нужный текст.

Хорошая подсказка конкретна: «Введите целое число от 1 до 100» работает лучше, чем «Некорректное значение». Если поле принимает дату, укажите ожидаемый формат. Если это список, где значения неочевидны, перечислите допустимые варианты прямо в подсказке. Пользователь, который видит четкую инструкцию при вводе, допускает значительно меньше ошибок.

Пользовательская формула

Пользовательская формула – самый гибкий тип правила. Она подходит для любых условий, которые нельзя выразить встроенными критериями. В поле формулы вводится выражение, которое должно возвращать ИСТИНА или ЛОЖЬ: если формула возвращает ИСТИНА, значение считается допустимым.

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

Ниже несколько практических примеров.

Ограничение числового диапазона с проверкой типа

Встроенный тип «Число» отклоняет нечисловые значения, поэтому текст он не пропустит. Однако если написать пользовательскую формулу только с арифметическими условиями – например, =И(A2>=0;A2<=100) – остается риск пропустить текстовое значение: Google Таблицы автоматически конвертируют некоторые текстовые строки в числа при выполнении математических операций. Добавление ЕЧИСЛО исключает эту ситуацию явно:

=И(ЕЧИСЛО(A2);A2>=0;A2<=100)

ЕЧИСЛО(A2) возвращает ИСТИНА только для настоящего числа, а не для текста, похожего на число. И объединяет это условие с проверкой диапазона. Такой подход также страхует от дат – поскольку даты в Google Таблицах хранятся как числа, они формально проходят арифметические сравнения, но ЕЧИСЛО их тоже пропустит. Если нужно исключить и даты, добавьте НЕ(ЕОШИБКА(ДАТА(A2;1;1))) – либо используйте встроенный тип «Число», который с датами не работает.

Запрет прошедших дат

Удобно для столбцов с плановыми датами выполнения задач или дедлайнами. Формула сравнивает введенную дату с сегодняшней:

=A2>=СЕГОДНЯ()

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

Проверка уникальности

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

=СЧЁТЕСЛИ($A$2:$A$100;A2)<=1

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

Проверка формата через регулярное выражение

Когда нужно убедиться, что текст соответствует определенному шаблону, используется функция REGEXMATCH. Например, проверка, что значение содержит символ «@» и точку после него – упрощенная проверка формата электронной почты:

=REGEXMATCH(A2;"[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}")

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

=REGEXMATCH(A2;"^[A-Za-z]{2}-[0-9]{4}$")

Символ ^ означает начало строки, $ – конец. Это гарантирует, что формат соблюден полностью, а не просто содержит нужные символы где-то внутри.

Управление правилами и снятие проверки

Все правила для листа можно просмотреть в одном месте, открыв «Данные» – «Настроить проверку данных» без предварительного выделения диапазона. Панель покажет список всех активных правил с указанием диапазонов. Каждое правило можно отредактировать или удалить отдельно.

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

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

Заключение

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

Изображение на обложке: Flaticon