Как создать проверку данных в Microsoft Excel?

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

По теме: Как исправить медленный Excel и ускорить работу?

1 Определение правил валидации

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

Давайте возьмем пример для проверки ведомости успеваемости студента.

• Ячейки под столбцом «Тема 1» должны быть числовыми.
• Допустимое значение от 0 до 100.
• Это не может быть текст, меньше нуля или больше 100.
• Excel должен автоматически отклонить все остальные записи.

Вы можете определить правила проверки данных в Excel, выполнив следующие шаги.

• Чтобы установить эти проверки, сначала выберите диапазон ячеек, значения которых вы хотите ограничить. В нашем примере мы выбираем ячейки с D4 по D7.
• Перейдите на вкладку «Данные» и выберите параметр «Проверка данных» в группе «Инструменты для работы с данными».

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

• Откроется диалоговое окно «Проверка данных». Вы можете настроить разрешенные записи для выбранных ячеек на вкладке «Настройки» диалогового окна.
• Щелкните раскрывающийся список под «Разрешить:» и выберите желаемое значение. В нашем примере мы выберем для отметок целое число.

Настройки проверки данных Excel

• В поле со списком «Данные» вы можете определить условия действия. Это ограничивает возможности ввода, и мы выбираем вариант «между», поскольку нам нужно разрешить только значения от 0 до 100. Помните, что параметры будут меняться в зависимости от вашего выбора. Например, если вы выберете «больше», вы увидите только текстовое поле для ввода «минимального» значения.
• Для условия «между» вы можете определить возможный диапазон в полях «Минимум» и «Максимум». Мы введем минимум 0 и максимум 100.

Нажмите кнопку «ОК», и теперь, когда вы успешно настроили проверку данных для выбранных ячеек.

2 Предупреждение об ошибке правила проверки

Используя правило проверки, вы можете ограничить допустимые значения в ячейке. Когда вы попытаетесь ввести значение 110 в ячейку D4, Excel покажет вам сообщение об ошибке и ограничит вас.

Данные не разрешены из-за неверных записей

Однако в сообщении об ошибке не отображается допустимый диапазон от 0 до 100. Чтобы сделать сообщение об ошибке более значимым, вы можете создать настраиваемое сообщение об ошибке, которое дает четкие инструкции при возникновении ошибки.

• Выберите ячейку или диапазон ячеек и перейдите в «Данные> Проверка данных», как описано выше. Мы рекомендуем вам выполнить этот шаг вместе, чтобы вы могли настроить проверку и сообщение об ошибке за один шаг.
• Когда вы находитесь в диалоговом окне «Проверка данных», перейдите на вкладку «Предупреждение об ошибке».
• В поле «Заголовок» введите заголовок ошибки.
• Введите сведения о правиле на панели «Сообщение об ошибке».

Предупреждение об ошибке проверки данных

Важная часть – установить стиль сообщения об ошибке. Щелкните раскрывающийся список «Стиль», и вы увидите три варианта – «Остановить», «Предупреждение» и «Информация». В зависимости от ваших потребностей вы можете полностью ограничить значение ячейки или разрешить пользователям выводить предупреждение или информационное сообщение. Например, когда пользователь вводит 110, в нашем случае вы получите следующие ошибки для разных стилей предупреждений.

Связанный: Советы по Excel для повышения вашей продуктивности.

2.1. Остановить предупреждение об ошибке:

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

Остановить оповещение об ошибке

2.2. Предупреждение об ошибке:

Это простое информационное предупреждение, и Excel позволит вам продолжить с недопустимым значением, когда вы нажмете кнопку «ОК».

Предупреждение об ошибке информации

2.3. Предупреждение об ошибке:

Это предупреждение, и Excel предложит вам варианты продолжения или отмены.

Предупреждение об ошибке

3 Сообщение о вводе правила проверки

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

• Выберите ячейку или диапазон ячеек и перейдите в «Данные> Проверка данных», как описано выше.
• Когда вы находитесь в диалоговом окне «Проверка данных», перейдите на вкладку «Входное сообщение».
• В поле ввода «Заголовок» введите заголовок сообщения об ошибке.
• Введите сведения о правиле на панели «Входное сообщение».

Входное сообщение проверки данных

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

Сообщение о вводе правила проверки

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

По теме: Как исправить ошибки формул в Excel?

4 Правила проверки существующих данных

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

4.1. Расширение проверки данных

Когда вы выбираете ячейки вне правил проверки и щелкаете по опции «Проверка данных», Excel выдаст вам сообщение.

Расширение проверки данных

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

4.2. Маркировка существующих недопустимых значений

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

Циркуляция неверных данных

4.3. Удаление метки с неверных данных

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

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

5 Список правил валидации

В качестве альтернативы правилам проверки вы можете использовать список, содержащий все допустимые значения. Таким образом, каждый пользователь может просто выбрать между доступными значениями и вставить правильные данные. Другими словами, вы можете ограничить выбор пользователя в пределах указанных значений. В нашем примере вы можете разрешить только такие отметки, как 50, 60, 70, 80 и 90, и ограничить любой другой ввод данных пользователем.

Когда вы находитесь во всплывающем окне «Проверка данных», перейдите на вкладку «Настройки»:

• Выберите запись «Список» в раскрывающемся списке «Разрешить».
• Выберите вариант «Раскрывающийся список в ячейке», чтобы отображать значения в раскрывающемся списке.
• Установите или снимите флажок «Игнорировать пустое место» в зависимости от ваших потребностей.
• Введите допустимые значения в текстовое поле «Источник» через запятую. Если требуемые значения уже существуют в таблице или на других листах, после щелчка в поле «Источник» выберите соответствующую область, чтобы принять значения. Убедитесь, что источник значений доступен в той же книге Excel, чтобы он работал, когда вы делитесь Excel с другими.
• Нажмите кнопку «ОК», чтобы сохранить изменения.

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

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

Выберите значения из списка

Вы также можете настроить входное сообщение и предупреждение об ошибке, как описано выше.

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