Как создать правила проверки данных в Excel 2016

Отредактировано 2 Несколько недель назад от ExtremeHow Редакционная команда

Microsoft OfficeExcelПроверка данныхПравилаЭлектронная таблицаДанныеВходСогласованностьWindowsMacПродуктивностьБезопасность

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

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

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

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

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

• Ограничение чисел - Укажите, что введенные данные должны быть числами в определенном диапазоне.
• Длина текста - Убедитесь, что введенные данные имеют определенное количество символов.
• Дата и время - Проверьте, чтобы вводились дата/время и, возможно, в определенном диапазоне.
• Списки - Разрешите вводить только предопределенные элементы из списка.
• Пользовательские формулы - Используйте формулы для определения критериев проверки.

Создание правил проверки данных

Теперь перейдем к шагам по созданию правила проверки данных в Excel 2016:

Шаг 1: Выберите ячейку(-и)

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

Шаг 2: Откройте диалоговое окно проверки данных

После того как нужные ячейки выбраны, перейдите на вкладку 'Данные' на ленте. Вы найдете опцию 'Проверка данных' в группе 'Инструменты работы с данными'. Щелкните на опцию 'Проверка данных', и появится диалоговое окно.

Шаг 3: Установите критерии проверки

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

• Целые числа

  Если вы хотите разрешить только целые числа, выбирайте 'Целые числа' из выпадающего меню 'Разрешить'. Вы можете дальше определить минимальные и максимальные числа. Например, чтобы ограничить возраст от 18 до 65 лет, вы установите минимум на 18 и максимум на 65.

• Дробные

  Для дробных чисел выберите 'Дробные' и установите ограничения. Например, если вы имеете дело с денежными значениями, такими как цены, вы можете ограничить ввод от 0,01 до 1000,00. Эти ограничения обеспечивают, что вводятся только реалистичные и разумные данные.

• Список

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

• Дата

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

• Время

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

• Длина текста

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

• Пользовательские

  Для опытных пользователей опция 'Пользовательские' позволяет вводить специфические формулы для проверки. Например, чтобы ограничить ввод только четными числами, можно использовать формулу, такую как =MOD(A1,2)=0. Такие формулы обеспечивают большую гибкость для уникальных сценариев проверки.

Шаг 4: Настройте сообщение для ввода

Вкладка 'Сообщение для ввода' позволяет отображать сообщение, когда пользователь выбирает ячейку. Используйте эту функцию, чтобы дать пользователям понять, какие данные допустимы для ввода, что уменьшит количество ошибок. Например, сообщение может быть таким: "Введите код продукта от 1000 до 9999". Выбор ячейки вызовет всплывающее сообщение, предоставляющее полезные инструкции пользователю.

Шаг 5: Настройка сообщений об ошибках

Вкладка 'Сообщение об ошибке' позволяет указать, что происходит при вводе недопустимых данных. Наиболее распространенными типами являются:

• Остановить

  Это самый строгий тип сообщения. Если кто-то пытается ввести данные, которые не соответствуют вашим правилам, сообщение 'Остановка' полностью блокирует ввод, принуждая пользователя исправить ошибку.

• Предупреждение

  Сообщение 'Предупреждение' информирует пользователя о возможной проблеме с вводом, но позволяет ему продолжить, если он хочет. Это создает промежуточное положение между принуждением и свободой выбора пользователя.

• Информационное

  Сообщение 'Информация' наименее интрузивно. Оно просто уведомляет пользователя с помощью сообщения без применения каких-либо правил, предоставляя полезную обратную связь и позволяя признать ввод.

Настройте сообщение об ошибке с помощью соответствующего объяснения, например, "Ввод превышает максимальный предел 65", объясняющее, почему ввод недопустим.

Расширенная проверка данных — формулы и динамические списки

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

Пример для динамических списков

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

Предположим:
- Ячейка A1: Выбранная страна (США, Канада)
- Категория, именованная для городов США: USACities
- Категория, именованная для городов Канады: CanadaCities

1. Определите имя для списка городов в каждой стране.
2. В B1 (где выбирается город) установите проверку: Тип: Список, Источник: =INDIRECT(A1 & "Cities")

Здесь, если в A1 выбрано 'США', выпадающий список городов в B1 будет использовать источник из 'USACities', в противном случае будет использоваться 'CanadaCities'. Использование именованных диапазонов и функции INDIRECT поддерживает гибкое, динамическое управление списками.

Проверка вашей проверки данных

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

Редактирование и удаление проверки данных

Иногда вам может понадобиться обновить правила проверки данных или удалить их полностью. Для этого:

• Выберите ячейку, содержащую правило проверки, которое вы хотите изменить.
• Откройте диалоговое окно проверки данных через вкладку 'Данные'.
• Внесите необходимые изменения на вкладках 'Настройки', 'Сообщение для ввода' или 'Сообщение об ошибке'.
• Чтобы удалить правило проверки, нажмите "Очистить все" в диалоговом окне проверки данных. Это фактически удалит все настройки проверки из выбранных ячеек.

Заключение

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

  Поделиться этой статьей

Если вы найдете что-то неправильное в содержании статьи, вы можете запросить обновление