Проверка данных в excel: как сделать, использовать и убрать

План статьи:

1. Введение

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

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

2. Проверка данных

• Проверка формата чисел и дат
• Проверка на наличие ошибок: опечатки, лишние символы, пропущенные значения
• Проверка на допустимые значения: минимальное и максимальное значение, диапазон

3. Использование встроенных функций Excel

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

• Функция ISNUMBER для проверки численных значений
• Функция ISDATE для проверки дат
• Функция IFERROR для обработки ошибок

4. Другие способы проверки данных

Кроме встроенных функций Excel, существуют и другие способы проверки вводимых значений:

• Использование утилиты Data Validation для создания ограничений на значения в ячейках
• Использование макросов для автоматической проверки данных при вводе

5. Заключение

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

Поддержка проверки браузерами

Разработчики браузеров добавляли поддержку проверки в свои продукты по частям, вследствие чего некоторые версии браузеров поддерживают одни возможности валидации, но не обращают внимания на другие. В таблице ниже указаны минимальные версии браузеров, полностью поддерживающих валидацию HTML5:

Поддержка проверки браузерами

Браузер	IE	Firefox	Chrome	Safari	Opera	Safari iOS	Android
Минимальная версия	10	4	10	5	10	—	—

Так как проверка HTML5 не заменяет валидацию на стороне сервера, ее можно рассматривать как второстепенную возможность, когда даже такая несовершенная поддержка лучше, чем отсутствие вообще какой-либо поддержки. В браузерах, не поддерживающих проверку, таких как IE 9, можно отправлять формы с некорректными данными, но эти ошибки можно выявить на стороне сервера и возвратить эту страницу назад браузеру, но с указанными ошибками.

С другой стороны, ваш веб-сайт может содержать сложные формы, в которых можно сделать массу ошибок при вводе данных, и вы не хотите потерять тех IE-пользователей, которые после первой неудачной попытки заполнить вашу форму не предпримут другую. В таком случае у вас есть два пути: разработать и использовать свою систему проверки или же использовать библиотеку JavaScript, чтобы компенсировать умственную отсталость IE. Какой из этих двух подходов выбрать, зависит от объема и сложности проверки.

На странице HTML5 Cross Browser Polyfills можно найти длинный список библиотек JavaScript, которые все, по большому счету, делают то же самое. Одна из лучших среди этих библиотек — это webforms2.

Библиотека webforms2 реализует все рассмотренные на данный момент атрибуты. Для использования библиотеки загрузите все ее файлы в папку своего веб-сайта (а лучше в подкаталог папки веб-сайта) и добавьте в веб-странице ссылку на эту библиотеку.

Библиотека webforms2 хорошо интегрируется с другой заплаткой JavaScript, называющейся html5Widgets. Она реализует поддержку возможностей форм, которые мы рассмотрим далее, таких как ползунок и средства выбора даты и цвета. Обе эти библиотеки предоставляют хорошую общую поддержку для веб-форм, но содержат в своем коде неизбежные пробелы и незначительные ошибки. Качество сопровождения и усовершенствования этих библиотек покажет только время.

Типы текстовых полей

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

Текстовое поле для банковской карты

• Поле «Имя и фамилия владельца карты» должно принимать только буквенные значения.
• Поле «Имя и фамилия владельца карты» не должно принимать числа и специальные символы.
• Поле «Номер карты» должно принимать только числовые значения.
• Поле «Номер карты» не должно принимать специальные символы.
• В поле «Номер карты» должно быть невозможно ввести больше символов, чем содержится в идентификационном номере карты.
• Поле «Номер карты» определяет тип карты по её номеру.

Пример текстового поля «Адрес»

• Поле «Почтовый индекс» должно принимать только буквы и цифры.
• Поле «Адрес» допускает ввод букв, чисел и знаков.
• «Адрес» может быть как обязательным, так и необязательным для заполнения полем.

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

Проверка ввода данных в Excel и ее особенности

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

Проверка вводимых данных в Excel

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

У нас имеется лист номенклатуры товаров магазина:

1. Выделите столбец B и выберите инструмент «Данные»-«Проверка данных».
2. Заполняем значениями поля на вкладке «Параметры» как показано на рисунке:
3. На вкладке «Сообщение для ввода» создаем особое примечание, в котором при помещения курсора на ячейку будет отображаться условия для ее заполнения. И жмем ОК.

Теперь проверим. В ячейку B2 введите натуральное число, а в ячейку B3 отрицательное. Как видно в ячейке B3 действие оператора набора – заблокировано. Отображается сообщение об ошибке: «Введенное значение неверно».

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

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

Особенности проверки данных

Данным способом проверяются данные только в процессе ввода. Если данные уже введенные они будут не проверенные. Например, в столбце B нельзя ввести текст после установки условий заполнения в нем ячеек. Но заголовок в ячейке B1 «Цена» остался без предупреждения об ошибке.

Внимание! Если ячейки будут скопированы, а не введены то их значения так же не будут проверены. Чтобы проверить соответствуют ли все введенные данные, определенным условиям в столбце и нет ли там ошибок, следует использовать другой инструмент: «Данные»-«Проверка данных»-«Обвести неверные данные»

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

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

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Формула ЕСЛИ в Excel – примеры нескольких условий

Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются

Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2

они входят в интервал B2

При написании формулы легко запутаться, поэтому рекомендуется смотреть на всплывающую подсказку.

В конце нужно обязательно закрыть все скобки, иначе эксель выдаст ошибку

Пример проверки данных: создание раскрывающегося списка в Excel

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

Раскрывающиеся списки – отличный способ заставить пользователей выбрать элемент из списка, который вы им предоставляете, вместо того, чтобы вводить свои собственные значения.

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

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

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

1. Выберите ячейку A1 и введите «ИМЯ».
2. В ячейках ниже добавьте кучу имен.
3. Наведите курсор на заголовок столбца (например, A ) и щелкните по нему, когда курсор станет стрелкой. Это выберет весь столбец.
4. Перейдите на вкладку » Вставка» и нажмите » Таблица» .
5. Установите флажок » Моя таблица имеет заголовки» и нажмите » ОК» . Эта опция перечислит верхнюю ячейку (то есть ИМЯ) в качестве заголовка таблицы.

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

1. Выберите ячейку B1 и введите «КУРС».
2. Выберите весь столбец , щелкнув правой кнопкой мыши под именем столбца (как в предыдущей таблице).
3. Перейдите на вкладку «Вставка» и выберите « Таблица», чтобы создать таблицу.
4. Установите флажок » Моя таблица имеет заголовки» и нажмите » ОК» .

Теперь, когда у вас есть таблица курсов, пора составить список курсов.

1. Щелкните значок плюса рядом с Sheet1, чтобы создать новый лист.
2. На новом листе введите название курсов в столбец.

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

1. Выделите ячейки в столбце КУРС.
2. Перейдите на вкладку « Данные » и нажмите « Проверка данных» .
3. На вкладке « Настройки » в разделе « Разрешить» выберите « Список» .
4. Щелкните поле Источник .
5. Перейдите на Sheet2 и выберите ячейки, содержащие названия курсов (A1: A5 в этом примере).

Итак, вы создали правило проверки данных. Если вы хотите сделать это тщательно, вам также следует добавить входное сообщение.

1. В окне «Проверка данных» перейдите на вкладку « Входное сообщение ».
2. Введите заголовок, а затем собственное сообщение.

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

1. В окне «Проверка данных» перейдите на вкладку « Предупреждение об ошибке ».
2. Установите стиль на Stop . Сделав это, пользователь сможет вводить только то, что находится в списке.
3. Введите заголовок, а затем введите сообщение об ошибке. Вы также можете оставить это поле пустым.
4. Когда все будет готово, нажмите « ОК» в окне «Проверка данных».

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

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

Как работает проверка HTML5?

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

Например, допустим, что определенное поле нельзя оставлять пустым, и посетитель должен ввести в него хоть что-то. В HTML5 это осуществляется с помощью атрибута required в соответствующем поле (далее дорабатываем пример из предыдущей статьи):

Даже с применением для поля атрибута required на это требование нет никаких визуальных указателей по умолчанию

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

Проверка выполняется, когда пользователь нажмет кнопку для отправки формы. Если браузер поддерживает формы HTML5, он заметит пустое обязательное для заполнения поле, перехватит вызов отправки формы и отобразит всплывающее сообщение об ошибке:

Хотя официальных требований к оформлению сообщения об ошибках при проверке не существует, во всех браузерах для этой цели применяются всплывающие подсказки. К сожалению, веб-разработчики не могут изменять оформление или текст этого сообщения (без использования JavaScript), по крайней мере в настоящее время.

Возникает еще один вопрос: что будет, если нарушено несколько правил проверки, например, не заполнено несколько обязательных полей?

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

Валидация на стороне сервера

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

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

В каждом языке свои особенности валидации. Если обобщить, то проверка может выглядеть так:

1. Получаем данные от клиента через HTTP-запрос и сохраняем в переменные на сервере.
2. Определяем правила валидации. Проверяем, что данные соответствуют ожидаемому формату, например, что они не пустые.
3. Пишем код для валидации.
4. Обрабатываем результаты валидации. Если данные не проходят проверку, возвращаем ошибку клиенту и даём инструкции по исправлению. Если данные корректны, продолжаем выполнение запроса.
5. Обрабатываем введённые данные. Если валидация прошла успешно, можно сохранить их в базе данных или использовать для выполнения других действий.

При проведении валидации на стороне сервера также важно помнить о безопасности. Например, можно экранировать все входные данные, чтобы предотвратить XSS-атаки

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

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

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

Основа использования проверки данных заключается в том, что вы выбираете ячейки, для которых хотите проверить данные, а затем создаете для них правило проверки данных. Чтобы вызвать диалог проверки данных:

1. Выберите свою ячейку (и).
2. На ленте перейдите на вкладку « Данные ».
3. Щелкните Проверка данных .

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

Выбор из типов данных

В диалоговом окне «Проверка данных» первая вкладка называется «Настройки» и содержит критерии проверки. Здесь вы будете создавать правило проверки данных.

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

Microsoft Excel предлагает множество вариантов на выбор (например, дату, время, текст, целое число, список и т. Д.). Однако вы также можете использовать свою собственную формулу, выбрав Custom в раскрывающемся списке.

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

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

Добавление входного сообщения

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

Создание предупреждения об ошибке

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

Стоп : этот стиль предупреждения не позволяет пользователю вводить недопустимые данные в ячейку. Пользователь может повторить попытку и ввести действительные данные.

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

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

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

Почему проверка данных Excel не работает?

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

Проверка данных не работает для скопированных данных

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

Хотя нет способа отключить клавиши копирования/вставки (кроме использования VBA), вы можете, по крайней мере, предотвратить копирование данных путем перетаскивания ячеек. Для этого выберите «Файл» > «Параметры» > «Дополнительно» > «Параметры редактирования» и снимите флажок «Включить маркер заполнения и перетаскивания ячеек».

Проверка данных Excel не активна

Кнопка «Проверка данных» не активна (выделена серым цветом), если вы вводите или изменяете данные в ячейке. Закончив редактирование ячейки, нажмите Enter или Esc, чтобы выйти из режима редактирования, а затем выполните проверку данных.

Проверка данных не может быть применена к защищенной или общей книге

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

Неправильные формулы проверки данных

При проверке данных на основе формул в Excel необходимо проверить три важные вещи:

• Формула проверки не возвращает ошибок.
• Формула не ссылается на пустые ячейки.
• Используются правильные ссылки на ячейки.

Ручной пересчет формул включен

Если в Excel включен режим ручного расчета, невычисленные формулы могут помешать правильной проверке данных. Чтобы снова изменить параметр расчета Excel на автоматический, перейдите на вкладку «Формулы» > группу «Расчет», нажмите кнопку «Параметры расчета» и выберите «Автоматически».

Проверьте правильность формулы проверки данных

Для начала скопируйте формулу проверки в какую-нибудь ячейку, чтобы убедиться, что она не возвращает ошибку, такую ​​как #Н/Д, #ЗНАЧ или #ДЕЛ/0!.

Если вы создаете пользовательское правило , формула должна возвращать логические значения ИСТИНА и ЛОЖЬ или приравненные к ним значения 1 и 0 соответственно.

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

Формула проверки данных не должна ссылаться на пустую ячейку

Если вы активируете опцию «Игнорировать пустые ячейки» при определении правила (обычно выбрано по умолчанию), то следите, чтобы ваши формулы или условия не ссылались на пустую ячейку.

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

Абсолютные и относительные ссылки на ячейки в формулах проверки

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

Если вы создаете правило для нескольких ячеек и ваши критерии проверки содержат адреса конкретных ячеек , обязательно зафиксируйте ячейку в формуле при помощи абсолютной ссылки (со знаком $, например, $A$1), иначе ваше правило будет правильно работать только для первой ячейки.

Чтобы лучше проиллюстрировать это, рассмотрим следующий пример.

Предположим, вы хотите ограничить ввод данных в ячейках с A2 по A8 целыми числами от 100 (минимальное значение) до 999. Чтобы проще было при необходимости изменить критерии проверки, используем ссылки на ячейки с этими значениями, как показано на скриншоте ниже:

Это правило будет корректно выполняться только для первой ячейки диапазона – A2. Для ячейки A3 критерии максимума и минимума изменятся на E3 и F3. Поскольку эти ячейки пусты, то условие ввода теперь – любое число больше либо меньше нуля. Правда, символьные значения и дробные числа вы вводить не сможете, так как продолжает действовать ограничение на тип данных – целые числа.

Чтобы исправить формулу, просто введите «$» перед ссылками на столбцы и строки, чтобы зафиксировать их: =$E$2 и $F$2. Или используйте клавишу   для переключения между различными типами ссылок.

Вот как вы можете добавить и использовать проверку данных в Excel. Я благодарю вас за чтение и надеюсь ещё увидеть вас в нашем блоге!

Проверка вводимых данных в ячейки Excel

Допустим, что в таблице прайс-листа с розничными ценами на товары, в одном из столбцов указана процентная ставка НДС.

Пример прайс-листа с введенными ошибками в процентных ставках НДС:

Чтобы в Excel сделать проверку вводимых данных в ячейки следует выполнить ряд последовательных действий:



Закладка «Сообщение об ошибке» предоставляет пользователю возможность оформить стиль сообщения об ошибочных вводах значений. Если пользователь вводить в ячейку неправильное значение тогда будет выполнен один из 3-х параметров:

1. Останов – данный параметр разрешает вводить только правильное значение, выбранного из выпадающего списка или отменить ввод оставив пустую ячейку.
2. Предупреждение – этот параметр предупреждает об вводе ошибочного значения и предоставляет попытку отредактировать или выбрать из выпадающего списка.

Сообщение – параметр сообщает о неправильном значении и позволяет проигнорировать ошибку.

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

После заполнения всех параметров в окне «Проверка вводимых значений» нажмите на кнопку ОК.

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

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

Разрешить только значения, содержащие определенный текст

Чтобы разрешить ввод значений, которые содержат определенный текст в любом месте ячейки (в начале, середине или конце), используйте функцию ЕЧИСЛО (ISNUMBER) в сочетании с НАЙТИ (FIND) или ПОИСК (SEARCH) в зависимости от того, хотите ли вы совпадение с учетом регистра или без учета регистра:

Проверка без учета регистра:

Проверка с учетом регистра:

В нашем примере, чтобы разрешить только записи, содержащие текст «AР» в ячейках A2: A8, используйте одну из следующих формул, создав правило проверки в ячейке A2:

Без учета регистра:

С учетом регистра:

Формулы работают по следующей логике:

Вы ищете подстроку «AР» в ячейке A2, используя НАЙТИ или ПОИСК, и оба возвращают позицию первого символа в подстроке. Если текст не найден, возвращается ошибка. Если поиск успешен и «АР» найден в ячейке, мы получаем номер позиции в тексте, где эта подстрока была найдена. Далее функция ЕЧИСЛО возвращает ИСТИНА, и проверка данных проходит успешно. В случае, если подстроку не удалось найти, результатом будет ошибка и ЕЧИСЛО возвращает ЛОЖЬ. Запись не будет разрешена в ячейке.

Функция IsError

Описание функции

Функция IsError возвращает логическое значение, указывающее, является ли аргумент функции значением ошибки, определенной пользователем:

• True — аргумент функции является значением ошибки, определенной пользователем;
• False — в иных случаях.

Пользователь может определить одну или несколько ошибок для своей процедуры или функции с рекомендациями действий по ее (их) исправлению. Возвращается номер ошибки с помощью функции CVErr.

Пример с IsError

Допустим, пользователь определил, что ошибка №25 означает несоответствие аргумента функции Vkuba числовому формату:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

FunctionVkuba(x) IfIsNumeric(x)Then Vkuba=x^3 Else Vkuba=CVErr(25) EndIf EndFunction SubPrimer4() Debug.Print Vkuba(5)’Результат: 125 Debug.Print IsError(Vkuba(5))’Результат: False Debug.Print Vkuba(«пять»)’Результат: Error 25 Debug.Print IsError(Vkuba(«пять»))’Результат: True EndSub

Как сделать проверку данных в excel? — Разбираем подробно

Рубрика Excel

Также статьи о работе с данными в Экселе:

Как сделать раскрывающийся список в Excel?
Как группировать ячейки в Excel?
Способы ввода данных в ячейку Excel
Типы данных в ячейках Excel

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

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

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

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

После выбора типа данных становится возможным выбор условия соответствия вводимого значения в графе «Значение:». Выберем для примера «Между».

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

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

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

Сообщение подсказка.

Вид «Останов».

Вид «Предупреждение».

Вид «Сообщение».

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

Допустим, что в таблице прайс-листа с розничными ценами на товары, в одном из столбцов указана процентная ставка НДС. Как часто бывает в рутинной работе срабатывает человеческий фактор и по ошибке для одной из категорий товаров, вместо ставки НДС 20% была введена старая ставка 19%. Эта небольшая разница в данных – 1% может создать большие проблемы для фирмы с всевозможными последствиями. Чтобы исключить ошибки созданных по причине человеческого фактора, воспользуемся встроенным инструментом Excel для проверки данных, который позволяет контролировать все что вводиться на рабочий лист.