Проверка данных (Data validation) — инструмент для предотвращения ошибок при вводе информации в ячейки. Проверка данных позволяет заранее определить, какие значения можно вводить, а какие нет. Находится на ленте в «Данных» (Data).

Для чего нужна проверка данных? Чтобы исключать типовые ошибки:

• текст вместо чисел;

• отрицательные числа там, где их быть не может;

• числа с дробной частью там, где должны быть целые;

• текст вместо даты;

• разные варианты написания одного и того же значения — например, сокращения («ЭБ» вместо «Электронная библиотека»), лишние пробелы в конце текстового значения или между словами, — всего этого достаточно, чтобы превратить текстовые значения в разные и, соответственно, чтобы они обрабатывались Excel некорректно.

Выделяйте ячейки, к которым хотите применить проверку данных, вызывайте этот инструмент и далее настраивайте параметры проверки в диалоговом окне «Проверка данных».

• Любое значение (Any value), то есть отсутствие проверки данных.

• Целое число (Whole number): можно задать ограничения снизу или сверху (например, числа больше 10 000). Удобно, если у вас натуральный показатель (в штуках) и дробной части быть не может.

• Действительное число (Decimal): тоже можно задавать ограничения. Подойдет, например, для величины сделки. У такого показателя может быть дробная часть (копейки), но может понадобиться ограничение (например, от 0).

• Список (List): ограниченный набор значений, перечень. Например, фамилии менеджеров, клички ваших котов, названия товаров или список городов, где ваша компания работает. Можно делать его с выпадающим списком или без. Главное, что вводить можно только значения из списка. Их можно перечислить прямо в проверке данных через точку с запятой (или запятую — смотря какие у вас региональные настройки и что является разделителем элементов списка) или сослаться на диапазон со значениями (об этом подробнее ниже).

• Дата (Date): можно задать ограничения, например «дата не позже / не раньше определенной или в промежутке».

• Время (Time): аналогично.

• Длина текста (Text length): ограничение на количество символов (можно задать ограничение и снизу, и сверху, и интервал).

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

Если же выбрать «Предупреждение» (Warning) или «Сообщение» (Information), то при попытке ввести неверные данные будет появляться предупреждение, но его можно будет проигнорировать и все равно ввести что угодно.

Если в ячейках с проверкой уже есть неверные данные (например, они были в них до того, как вы настроили проверку, либо у вас проверка с предупреждением, а не остановкой), их можно обвести, чтобы точно увидеть, где есть ошибки. Для этого выберите команду «Обвести неверные данные» (Circle Invalid Data).

Для удаления обводки выберите «Удалить обводку неверных данных» (Clear Validation Circles) в том же выпадающем списке.

В Google Таблицах и в Excel только с 2023 года в рамках подписки Microsoft 365 (опция появилась в начале 2022-го, но ее долго не было у обычных пользователей) в проверке данных можно искать значение, то есть после ввода части текста список фильтруется до тех значений, которые соответствуют введенному фрагменту. Это очень удобно, когда значений в исходном списке тысячи, например это названия товаров.

Часто бывает, что проверка данных формируется на основе диапазона, к которому предполагается добавлять новые значения (список филиалов-городов, который может пополниться; сотрудников — можем нанять новых; и так далее).

Как сделать, чтобы новые значения попадали в выпадающий список автоматически? И чтобы сами списки автоматически появлялись в новых строках вашей таблицы?

Решения могут быть разные в зависимости от того, на разных ли листах данные.

Если данные на том же листе — превращаем справочник в таблицу (Ctrl + T или Ctrl + L, подробнее смотрите ) и ссылаемся на него.

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

Выделяем столбец в таблице на другом листе и присваиваем ему имя (можно ввести его в поле «Имя» слева от строки формул и нажать Enter).

После этого в проверке данных остается нажать F3 и выбрать в появившемся окне «Вставка имени» созданное имя.

Еще один вариант — ссылаться на имя таблицы и столбца в ней через функцию ДВССЫЛ / INDIRECT. Эта функция принимает текст в качестве аргумента и превращает его в ссылку, то есть это такой непрямой способ ссылаться на диапазоны, и она в данном случае помогает обойти ограничение проверки данных.

В Excel 2007 и ранее в проверке данных в принципе нельзя было ссылаться на другой лист, это нужно было делать через ДВССЫЛ / INDIRECT, даже если список не предполагается обновлять.

Подробнее про выпадающие списки с обновлением смотрите в видео: