Excel проверка вводимых значений

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заголовок и сообщение заполняются по Вашему желанию.

Пример вывода одной и той же ошибки, но под разными видами:

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

У Вас недостаточно прав для комментирования.

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

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

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

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

Прибыль – это естественно доход минус расход. Допустим нам нужно проверить, в какие дни прибыль падала ниже 40$. Решение следующее:

1. Выделите данные в диапазоне D2:D6 и выберите инструмент: «Данные»-«Работа с данными»-«Проверка данных».
2. В появившемся окне: «Проверка вводимых значений» установите такие же настройки как показано на рисунке. И нажмите ОК.
3. Теперь выберите инструмент из выпадающего списка: «Данные»-«Проверка данных»-«Обвести неверные данные». И обратите внимание на результат:
4. При необходимости можете удалить красные обводки, выбрав инструмент «Удалить обводку неверных данных».

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

Окно контрольного значения в Excel

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

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

На листе ИТОГО должно быть посчитано, сколько денег нужно для приобретения каждой группы товаров по отдельности. И какая общая сумма расходов.

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

1. Создайте книгу с листами, так как указано выше на рисунке и на каждом листе добавьте по несколько позиций разным количеством.
2. На листе: «ИТОГО» поставьте формулы как указано выше на рисунке и перейдите в ячейку B4.
3. Перейдите на закладку «Формулы» и выберите инструмент «Окно контрольного значения»
4. В появившемся окне нажмите кнопку «Добавить контрольное значение» и в нем указываем адрес ячейки, за значением которого мы будем следить: =ИТОГО!$B$4. Жмите добавить.

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

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

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

Проверка данных в MS EXCEL

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

Инструмент Проверка данных ( Данные/ Работа с данными/ Проверка данных ) не был бы столь популярным, если бы его функциональность ограничивалась бы только собственно проверкой. Ниже кратко перечислены основные приемы работы с этим инструментом.

A. Проверка введенных значений

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

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

• Целое число. В ячейку разрешен ввод только целых чисел, причем принадлежащих определенному диапазону;
• Действительное. В ячейку разрешен ввод только чисел, в том числе с десятичной частью (нельзя ввести текст, дату ввести можно);
• Дата. Предполагается, что в ячейку будут вводиться даты начиная от 01.01.1900 до 31.12.9999. Подробнее о формате Дата — в статье Как Excel хранит дату и время
• Время. Предполагается, что в ячейку с Проверкой данный этого типа будет вводиться время. Например, на рисунке ниже приведено условие, когда в ячейку разрешено вводить время принадлежащее только второй половине дня, т.е. от 12:00:00 до 23:59:59. Вместо утомительного ввода значения 12:00:00 можно использовать его числовой эквивалент 0,5. Возможность ввода чисел вместо времени следует из того, что любой дате в EXCEL сопоставлено положительное целое число, а следовательно времени (т.к. это часть суток), соответствует дробная часть числа (например, 0,5 – это полдень). Числовым эквивалентом для 23:59:59 будет 0,99999.

• Длина текста. В ячейку разрешен ввод только определенного количества символов. При этом ограничении можно вводить и числа и даты, главное, чтобы количество введенных символов не противоречило ограничению по длине текста. Например, при ограничении количества символов менее 5, нельзя ввести дату позднее 13/10/2173, т.к. ей соответствует число 99999, а 14/10/2173 — это уже 100000, т.е. 6 символов. Интересно, что при ограничении, например, менее 5 символов, вы не сможете ввести в ячейку формулу =КОРЕНЬ(2) , т.к. результат =1,4142135623731 (в зависимости от заданной в EXCEL точности), а вот =КОРЕНЬ(4) – сможете, ведь результат =2, а это только 1 символ.
• Список. Наверное, самый интересный тип данных. В этом случае ввод значений в ячейку можно ограничить ранее определенным списком. Например, если в качестве источника указать через точку с запятой единицы измерения товара шт;кг;кв.м;куб.м, то ничего другого, кроме этих 4-х значений из списка вам выбрать не удастся. В источнике можно указать диапазон ячеек, содержащий заранее сформированный список или ссылку на Именованную формулу. Пример приведен в статье Выпадающий (раскрывающийся) список
• Другой. В ячейку разрешен ввод значений удовлетворяющих более сложным критериям. Для задания критериев необходимо использовать формулу. Рассмотрим это условие подробнее.

При выбранном типе Другой, в поле Формула нужно ввести формулу для расчета логического значения. Если оно принимает значение ИСТИНА, то такое значение разрешено вводить в ячейку, если ЛОЖЬ, то ваше значение ввести не получится. В качестве аргумента формулы нужно использовать ссылку на саму ячейку, к которой применяется Проверка данных или ссылку на зависящую от нее ячейку. Например,

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

Очень часто при вводе данных в ячейки электронной таблицы мы совершаем ошибки.

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

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

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

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

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

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

Вот как это будет выглядеть в процессе работы.

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

Для этого надо предварительно ввести все допустимые значения из списка. Затем в поле «Источник» указать диапазон ячеек, где расположены допустимые списочные значения:

• нажмите изображение таблицы, расположенной справа поля;
• выделите диапазон ячеек;
• повторно нажмите на изображение таблицы.

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

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

В начало страницы

В начало страницы

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

Проверка данных является неплохим инструментом Excel для контроля за вносимыми на листах изменениями, не прибегая к помощи VBA. С её помощью можно ограничить ввод в ячейку, разрешив вводить только даты либо время, либо только числа. Да к тому же еще и задать диапазон дат либо предел чисел(к примеру от 1 до 10).Применений, я думаю, можно придумать массу: для корректной работы многих формул требуются корректные исходные данные. Следовательно, мы можем с помощью Проверки данных разрешить пользователю вводить только тот тип и диапазон данных, который может обработать формула, не возвращая значение ошибки. Разберем поподробней.

Сей чудесный инструмент находится: Данные (Data) —Проверка данных (Data Validation) . Должно появиться окно:

Для начала надо выделить ячейку(или диапазон ячеек) для которой необходимо установить проверку. Затем идем в Данные (Data) —Проверка данных (Data Validation) .

Первая вкладка — Параметры (Settings)

В поле Тип данных (Allow) — выбирается непосредственно тип данных, который должен быть записан в ячейке. Всего доступно 8 типов: Любое значение, Целое число, Действительное, Список, Дата, Время, Длина текста, Другой (Any Value, Whole number, Decimal, List, Date, Time, Text lenght, Custom) . Пункт Любое значение (Any Value) установлен по умолчанию, проверка не осуществляется. Подробно про пункт Списки (List) и как их создавать можно посмотреть и почитать в статье Выпадающие списки. Там все подробно и с нюансами расписано про списки в проверке данных, поэтому в данной статье рассмотрим оставшиеся 6 типов. Если кратко, то при выборе пункта Список в ячейке появляется выпадающий список допустимых значений. И ввести можно только то значение, которое присутствует в списке

Остальные типы данных:

• Целое число (Whole number) — говорит само за себя. После установки такой проверки в ячейку можно будет внести только целое число. Т.е. число, не имеющее дробного остатка( 9,1 например уже нельзя будет ввести). Так же нельзя будет ввести произвольный текст. Чаще всего подобная проверка применяется в полях для записи кол-ва штук товара и т.п. Т.е. там, где не может быть дробных значений.
• Действительное (Decimal) — тоже, что и в предыдущем пункте, но ввести можно любое число — хоть целое, хоть дробное, но невозможно будет ввести текст

Примечание: применив данные типы проверок, внести в ячейку можно только число. Занести текст Excel уже не разрешит. Однако стоит помнить, что даты и время Excel воспринимает и хранит именно как ЧИСЛОВЫЕ значения, поэтому ввод даты и времени тоже будет разрешен, если он не противоречит остальным условиям проверки(см.ниже). Но для проверки Целое число разрешен будет ввод только даты, т.к. время Excel хранит как дробное значение(кроме 24:00:00 и 00:00:00, которые Excel воспринимает как 1 и 0 соответственно). Более подробно о восприятии Excel-ем данных читайте в статье Как Excel воспринимает данные?.