Проверка данных и выпадающие списки в Google Таблицах и Excel

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

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

Где найти:

• Google Таблицы: «Данные» → «Настроить проверку данных» → «Добавить правило».
• Excel: вкладка «Данные» → «Проверка данных».

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

Как создать выпадающий список

Google Таблицы. Выделите диапазон, откройте проверку данных и выберите критерий:

• «Раскрывающийся список» — варианты вводятся прямо в правиле, каждому можно задать цвет. Так статусы читаются с одного взгляда.
• «Раскрывающийся список (из диапазона)» — варианты берутся из ячеек, например с отдельного листа «Справочники». Меняете список в одном месте — обновляются все выпадающие списки.

Excel. В окне проверки данных выберите тип данных «Список». В поле «Источник» либо перечислите значения через точку с запятой (или запятую — зависит от региональных настроек), либо укажите диапазон: =Справочники!$A$2:$A$10. Оформите список как умную таблицу или именованный диапазон, чтобы новые пункты подхватывались без правки правила.

Практический совет: держите все справочники (статусы, менеджеры, города) на одном скрытом или защищённом листе. Это единственное место, где команда договаривается о формулировках.

Зависимые списки

Зависимый список меняет варианты в зависимости от другой ячейки: выбрали «Ташкент» в столбце города — в столбце района доступны только районы Ташкента.

В Excel классический способ — именованные диапазоны и INDIRECT (ДВССЫЛ):

1. Разместите каждую группу вариантов в своём столбце и назовите диапазон так же, как родительское значение (например, диапазон Ташкент). В именах нельзя использовать пробелы — заменяйте их подчёркиванием.
2. Для родительского столбца создайте обычный список.
3. Для дочернего столбца создайте список с источником =ДВССЫЛ(A2), где A2 — родительская ячейка.

В Google Таблицах INDIRECT в источнике списка не принимается, поэтому используют вспомогательный диапазон: формула вида =FILTER(Справочники!B:B; Справочники!A:A = A2) выводит подходящие варианты, а выпадающий список берёт значения из этого диапазона. Это удобно для одной строки ввода или листа-формы; для множества строк понадобится вспомогательный диапазон на каждую строку или небольшой Apps Script.

Правила для чисел, дат и текста

Списки — только часть возможностей. Другие полезные критерии:

Своя формула закрывает то, чего нет в стандартных критериях. Несколько примеров для правила, применённого со второй строки:

Без дублей в столбце A:        =COUNTIF($A:$A; A2) = 1
Формат телефона (Google):      =REGEXMATCH(TO_TEXT(B2); "^\+998\d{9}$")
Дата окончания после начала:   =D2 >= C2

Формула должна возвращать TRUE (ИСТИНА) для допустимых значений. Пишите её для первой ячейки диапазона — относительные ссылки сами сдвинутся для остальных строк. В русской версии Excel используйте русские имена функций, например СЧЁТЕСЛИ.

Сообщения об ошибках и подсказки

Стандартное сообщение об ошибке ничего не объясняет. Настройте своё:

• Google Таблицы: в дополнительных параметрах выберите «Запретить ввод данных» или «Показывать предупреждение» и включите текст подсказки, который появляется при выборе ячейки.
• Excel: вкладка «Подсказка по вводу» показывает текст при выделении ячейки, вкладка «Сообщение об ошибке» задаёт вид: «Останов» блокирует значение, «Предупреждение» и «Сообщение» пропускают его после подтверждения.

Хорошее сообщение говорит, что ожидается, а не просто что что-то не так: «Введите дату в формате ДД.ММ.ГГГГ, не раньше сегодняшней» вместо «Недопустимое значение».

Запрет выбирайте для полей, от которых зависят формулы и отчёты: статус, сумма, дата. Предупреждение — там, где исключения допустимы.

Как проверка держит общие таблицы в порядке

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