Проверка данных Excel. Защита ячейки в Excel от ввода некорректных данных Введенное значение неверно excel как исправить

Рассмотрим, как защитить отдельные ячейки в Excel . Есть несколько способов. Можно вставить в ячейку выпадающий список. Пользователь будет выбирать нужное слово из появившегося списка. Какими способами сделать раскрывающийся список, смотрите в статье "Выпадающий список в Excel" .
Второй способ, чтобы защитить ячейки Excel от ввода неверных данных - это функциия «Проверка данных». Это нужно для правильного заполнения анкеты, заявки, для работы с таблицей, где сотрудника постоянно отвлекают, др.
Сначала отмечаем ячейку или диапазон ячеек, куда нужно установить ограничения по вводу данных. Как выделить диапазон ячеек по его имени, смотрите в статье « Диапазон в Excel ».
Теперь заходим на закладку «Данные» в раздел "Работа с данными", нажимаем на кнопку «Проверка данных». Выйдет окно «Проверка вводимых значений». На вкладке " Параметры " в строке «Тип данных» будет стоять тип - «Любое значение».
Нам нужно здесь установить свое значение. Мы установили функцию «Целое число». Указали самое маленькое и самое большое числа, которые можно вводить.
Например, в анкете написано «возраст». Нужно указать полное количество лет, а пишут дату рождения.
Заполнили диалоговое окно так.
Нажимаем "ОК". Теперь проверяем, вводим разные цифры, а когда ввели цифру 1234 (это больше 100) и нажали «Enter», появилось такое окно предупреждения.

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

Чтобы убрать из ячейки эту настройку, заходим в функцию «Проверка данных» и нажимаем кнопку «Очистить все», затем - «ОК».
Можно настроить ячейки таблицы, бланка для заполнения так., чтобы не могли написать только дату рабочего дня. подробнее о такой настройки ячеек, смотрите в статье "Проверка даты в Excel" .
Можно настроить таблицу так, чтобы нельзя было ввести одно название. слово, код, значение дважды. Об этом читайте "Запретить вводить повторяющиеся значения в Excel".
Для контроля за сроками реализации продуктов, за сроками оплаты счетов, за разницей в сумме, т.д., можно установить функцию "Условное форматирование". Например, если сумма в ячейке будет больше 6000 рублей, то ячейка окрасится цветом.
Смотрите, как установить эту функцию, в статье "

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

Конечно же, нельзя быть абсолютно уверенным, что пользователь введёт именно то, что нужно, поэтому все данные необходимо тщательно проверить.

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

На практике это выливается с довольно длинные последовательности «if-else». Лично мне это жутко не нравилось, так как сложно с первого взгляда определить, какие поля как проверяются и какие сообщения выдаются в случае ошибок. А ведь полей в форме может быть и десять, тогда код проверки вообще затягивается. Вобщем, я задумался над тем, как можно минимизировать объём работ и вот что из этого получилось.

Я представил проверку как преобразование значения одного типа в значение другого типа (например, проверка того, что в поле введено чило, это преобразование строки в число). Тоесть проверка - это некая функция, имеющая сигнатуру делегата System.Converter.

Для проверки значение помещаем в класс обёртку:

public class ValidationStep
{
public T Value { get ; set ; }
}

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

public static class ValidationExtensions
{
Validate(this T value )
{
return new ValidationStep { Value = value };
}
public static ValidationStep Validate(this ValidationStep step,
Predicate predicate, string message)
{
if (predicate(step.Value)) return step;
throw new ValidationException(message);
}
public static ValidationStep Convert(this ValidationStep step,
Converter converter, string message)
{
try { return converter(step.Value).Validate(); }
catch { throw new ValidationException(message); }
}
}

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

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

public static class Validation
{
public static bool Validate(T value , Converter validator,
Action onSuccess, Action onFailure)
{
try
{
var result = validator(value );
onSuccess(result);
return true ;
}
catch (ValidationException e)
{
onFailure(e);
return false ;
}
}
}

А теперь о том, как это использовать. Допустим нам нужно проверить текстовое поле (tb1) и убедиться, что в него введено целое число в диапазоне от 0 до 10. Это можно сделать так:

Validation.Validate(
tb1.Text, value => value.Validate()
.Validate(x => x.Length > 0, "Введите что-нибудь" )
.Convert (x => Convert .ToInt32(x), "Введёное значение не является целым числом" )
.Validate(x => x >= 0, "Число не должно быть меньше нуля" )
.Validate(x => x <= 10, "Число не должно быть больше десяти" ),
v => MessageBox.Show("Введено корректное значение: " + v.Value),
e => MessageBox.Show(e.Message));

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

Ну вот, собственно, и всё: о)

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

Ограничение ввода данных

Ограничение ввода данных

Запрос на ввод допустимых записей

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

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

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

Добавление проверки данных в ячейку или диапазон ячеек

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

Выделите одну или несколько ячеек, к которым нужно применить проверку.

На вкладке Данные в группе Работа с данными нажмите кнопку Проверка данных .

На вкладке Параметры в разделе Разрешить нажмите кнопку Список .

В поле Источник введите значения, разделенные точкой с запятой. Например, введите " низкий", "средний", "высокий ".

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

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

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

Примечания:

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

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

	Сделайте следующее:
Разрешить вводить только целые числа из определенного диапазона	В списке Разрешить выберите значение Целое число . В поле Данные выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Диапазон . Введите минимальное, максимальное или определенное разрешенное значение. Можно также ввести формулу, которая возвращает числовое значение. Например, допустим, что вы проверяете значения в ячейке F1. Чтобы задать минимальный объем вычетов, равный значению этой ячейки, умноженному на 2, выберите пункт Больше или равно в поле Данные и введите формулу =2*F1 в поле Минимальное значение .
Разрешить вводить только десятичные числа из определенного диапазона
Разрешить вводить только даты в заданном интервале времени	Выполните действия, описанные в 1-2 выше. В поле Разрешить выберите значение Дата . В поле Данные выберите необходимый тип ограничения. Например, для разрешения даты после определенного дня выберите ограничение Больше . Введите начальную, конечную или определенную разрешенную дату. Вы также можете ввести формулу, которая возвращает дату. Например, чтобы задать интервал времени между текущей датой и датой через 3 дня после текущей, выберите пункт Между в поле Данные , потом введите =СЕГОДНЯ() в поле Дата начала и затем введите =СЕГОДНЯ()+3 в поле Дата завершения .
Разрешить вводить только время в заданном интервале	Выполните действия, описанные в 1-2 выше. В поле Разрешить выберите значение Время . В поле Данные выберите необходимый тип ограничения. Например, для разрешения времени до определенного времени дня выберите ограничение меньше . Укажите время начала, окончания или определенное время, которое необходимо разрешить. Если вы хотите ввести точное время, используйте формат чч:мм. Например, если в ячейке E2 задано время начала (8:00), а в ячейке F2 - время окончания (17:00) и вы хотите ограничить собрания этим промежутком, выберите между в поле Данные , а затем введите =E2 в поле Время начала и =F2 в поле Время окончания .
Разрешить вводить только текст определенной длины	Выполните действия, описанные в 1-2 выше. В поле Разрешить выберите значение Длина текста . В поле Данные выберите необходимый тип ограничения. Например, для установки определенного количества знаков выберите ограничение Меньше или равно . В этом случае мы хотели бы ограничить ввод на 25 знаков, поэтому установите флажок меньше или равно в поле данные и введите 25 в поле максимум .
Вычислять допустимое значение на основе содержимого другой ячейки	Выполните действия, описанные в 1-2 выше. В поле Разрешить выберите необходимый тип данных. В поле Данные выберите необходимый тип ограничения. В поле или полях, расположенных под полем Данные , выберите ячейку, которую необходимо использовать для определения допустимых значений. Например, чтобы допустить ввод сведений для счета только тогда, когда итог не превышает бюджет в ячейке E1, выберите значение Число десятичных знаков в списке Разрешить , ограничение "Меньше или равно" в списке "Данные", а в поле Максимальное значение введите >= =E1 .

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

Примечания:

В примерах ниже при создании формул с условиями используется настраиваемый вариант. В этом случае содержимое поля "Данные" не играет роли.

Снимки экрана в этой статье взяты Excel 2016; но функциональность одинакова в Excel в Интернете.

Чтобы	Введите формулу
Значение в ячейке, содержащей код продукта (C2), всегда начинается со стандартного префикса "ID-" и имеет длину не менее 10 (более 9) знаков.	= И (LEFT (C2; 3) = "ID-"; ДЛСТР (C2) >9)
	=ЕТЕКСТ(D2)
Значение в ячейке, содержащей чью-то дату рождения (B6), было больше числа лет, указанного в ячейке B4.	=ЕСЛИ(B6<=(СЕГОДНЯ()-(365*B4));TRUE,FALSE)
Все данные в диапазоне ячеек A2:A10 содержали уникальные значения.

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

• 01.01.2001;
• 01/01/2001;
• 1 января 2001 года и т.д.

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

Где находится?

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

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

Настройка условия проверки

Изначально требуется выбрать тип проверяемых данных, что будет являться первым условием. Всего предоставлено 8 вариантов:

• Целое число;
• Действительное число;
• Список;
• Дата;
• Время;
• Длина текста;
• Другой.

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

Самым необычным видом является выпадающий список .

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

Всплывающая подсказка ячейки Excel

Функционал проверки данных в Excel позволяет настраивать всплывающие подсказки для ячеек листа. Для этого следует перейти на вторую вкладку окна проверки вводимых значений – «Сообщение для ввода».

На изображении показан пример возможного сообщения для ячейки, в которую вносятся целые числа от 1 до 31 (настройки вкладки «Параметры»). Заголовок и сообщения указываются по Вашему усмотрению, каких-либо правил к их оформлению нет. Не забудьте установить галочку на поле «Отображать подсказку, если ячейка является текущей», иначе сообщение будет деактивировано.

Пример всплывающей подсказки в Excel:

Вывод сообщения об ошибке

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

Существует три варианта сообщений, отличающихся по поведению:

• Останов;
• Предупреждение;
• Сообщение.

Останов является сообщением об ошибке и позволяет произвести только 2 действия: отменить ввод и повторить ввод. В случае отмены новое значение будет изменено на предыдущее. Повтор ввода дает возможность скорректировать новое значение.

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

Сообщение выводить ошибку в виде простой информации и дает возможность отменить последнее действие.