Ограничение ввода с помощью проверки данных

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

Можно разрешить вводить в ячейку только целые числа, только даты в заданном диапазоне или только значения из утверждённого списка.

• Отлавливает опечатки до того, как они испортят данные.
• Направляет пользователей с помощью подсказок и предупреждений.
• Работает и в Excel, и в Google Таблицах.

В каждой программе эта функция находится немного в другом месте.

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

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

Распространённое правило ограничивает содержимое ячейки числами. В диалоговом окне Excel выберите Целое число или Десятичное число, а затем задайте условия, например между 1 и 100.

Если теперь кто-то введёт abc или 150, значение будет отклонено.

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

Можно также разрешить в ячейке только допустимые даты в определённом периоде. Выберите правило Дата и задайте, например, даты между сегодняшним днём и концом года.

Это особенно удобно для таблиц бронирования или сроков, где даты в прошлом не имеют смысла.

• Отклоняйте даты до =TODAY(), чтобы запретить ввод задним числом.
• Укажите начальную и конечную даты, чтобы ограничить период проекта.

Электронная таблица автоматически проверяет каждое введённое значение по этому правилу.

Проверка данных может контролировать и текст. В Excel есть правило Длина текста, полезное для таких полей, как двухбуквенный код страны или ID фиксированной длины.

• Требуйте длину равную 2 для кодов регионов.
• Требуйте длину меньшую или равную 50 для коротких заметок.

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

Если встроенные правила не позволяют выразить нужное условие, используйте правило Пользовательское (Excel) или Пользовательская формула (Google Таблицы). Напишите формулу, результатом которой должно быть TRUE, чтобы значение было разрешено.

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

=EXACT(A2,UPPER(A2))

Если введённое значение содержит не только прописные буквы, формула возвращает FALSE, и ввод блокируется.

Одно из популярных пользовательских правил запрещает повторяющиеся значения в столбце. Выберите A2:A100 и используйте пользовательскую формулу:

=COUNTIF($A$2:$A$100,A2)=1

Это означает, что только что введённое значение должно встречаться в диапазоне один раз. Если оно уже существует, количество вхождений равно 2 и правило не выполняется.

• Отлично подходит для уникальных ID, электронных адресов или номеров счетов.
• Обратите внимание на абсолютный диапазон и относительную ссылку A2.

Excel позволяет отображать Сообщение для ввода — небольшую подсказку, которая появляется при выборе ячейки, до начала ввода.

Используйте её, чтобы объяснить правило простыми словами, например: Введите целое число от 1 до 100.

• Заранее направляет пользователей.
• Уменьшает количество отклонённых значений и связанных с этим затруднений.

В Google Таблицах аналогичная подсказка отображается в справочном тексте правила проверки данных.

Когда данные нарушают правило, вкладка Excel Оповещение об ошибке определяет строгость проверки:

• Остановить — полностью отклоняет значение.
• Предупреждение — сообщает о нарушении, но позволяет пользователю продолжить.
• Информация — только уведомляет пользователя.

В Google Таблицах есть похожий выбор: Отклонить ввод или Показать предупреждение. Выбирайте «Остановить», когда недопустимые данные неприемлемы, и «Предупреждение», когда решение можно оставить на усмотрение пользователя.

Правила можно просмотреть или удалить в любое время.

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

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

Стоит упомянуть ещё один тип правила, поскольку он ведёт к следующему уроку: правило Список.

Вместо любого числа или даты правило «Список» ограничивает содержимое ячейки набором конкретных вариантов, например Open, Closed или Pending.

• Это основа раскрывающегося меню.
• Варианты можно ввести вручную или указать диапазон ячеек.

В следующем уроке Вы превратите этот список в удобный раскрывающийся список.

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

Вы научились управлять вводом с помощью проверки данных:

• Открывайте её через вкладку Данные (Excel) или Данные > Проверка данных (Google Таблицы).
• Ограничивайте ввод целыми числами, десятичными числами, датами или длиной текста.
• Создавайте пользовательские формулы, например =COUNTIF($A$2:$A$100,A2)=1, для сложных правил.
• Добавляйте сообщения для ввода и выбирайте варианты «Остановить» или «Предупреждение» для ошибок.

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