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

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

Для тех, кто еще не сталкивался с проверкой данных в Excel, скажу, что этот инструмент расположен на вкладке «Данные» в группе «Работа с данными».

Data_validation_1

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

Data_validation_2

Тип данных

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

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

Data_validation_3

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

Второй способ – создание выпадающего списка на основе внешнего источника данных, т.е. ранее подготовленного перечня значений (в приложенном примере такой перечень размещен на листе «Списки»).

Data_validation_4

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

 Data_validation_5

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

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

Data_validation_6

Тип данных «Дата» используется для определения ограничений на ввод дат. Тип ограничений устанавливается в поле «Значение:». Начальную и/или конечную даты можно задавать различными способами: указать абсолютное значение, указать ссылку на ячейку, использовать формулу. Например, в четвертом столбце таблицы используется два из перечисленных способов: в качестве конечной даты укажем абсолютное значение, а в качестве начальной даты – формулу СЕГОДНЯ()-2. Такой вариант записи позволяет ограничить возможность ввода дат поставки материалов интервалом времени не позднее двух дней от текущей даты и до конца 2013 года.

Data_validation_7

Тип данных «Время» аналогичен типу «Дата», с его помощью в пятом столбце ограничивается время поступления материалов рабочим временем.

Data_validation_8

Тип данных «Целое число» используется в нашем примере в столбце «Количество». В результате, в ячейки этого столбца нельзя будет ввести текст, отрицательные числа или указать нецелое число, т.к. предполагается что количество поставленных материалов будет измеряться в штуках, килограммах, ящиках и т.д.

Data_validation_9

Тип данных «Действительное число» используется в столбце «Цена», что позволяет вводить цену поставки с точностью до сотых. Для цены логично установить ограничение минимальным значением ноль, что позволит избежать ввода отрицательных чисел, а максимальное значение можно, например, ограничить с помощью формулы =ВПР(D9;Списки!$D$8:$E$11;2;ЛОЖЬ). Это еще один пример установки динамических ограничений, фактическое значение которого будет зависеть от значения, выбранного в столбце «Материал».

Data_validation_10

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

Диалоговые окна

Вкладка «Сообщение для ввода»

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

Data_validation_11

В результате, при активации любой ячейки в поле «Поставщик» будет появляться подсказка, как правильно заполнить ячейку.

Data_validation_12

Вкладка «Сообщение об ошибке»

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

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

Data_validation_13

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

Data_validation_14

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

Data_validation_15

Результат имеет следующий вид:

Data_validation_16

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

Data_validation_17

Результат имеет следующий вид:

Data_validation_18

Дополнительные элементы управления

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

Data_validation_19

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

Data_validation_20

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

Data_validation_21

Пример таблицы данных можно скачать по ссылке:

Проверка данных
Размер файла (53.5 KiB)
Загружено 53 раз

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

Поделиться с друзьями