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

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

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

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

Первый пункт меню «Проверка данных…» откроет окно с выбором настроек для выделенных ячеек.

Возможные варианты типов данных:

– любое значение: Excel не контролирует вводимые данные;

– целое число: числа без дробной части, обязательно нужно задать диапазон значений (меньше, больше, не равно, между, вне и т.п.);

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

– дата или время: произвольном формате, также с диапазоном;

– длина текста: ограничение минимального и максимального количества символов в ячейке (например, штрих-код – 13 цифр);

– формула: самый гибкий вариант – любая формула, которая принимает значение ИСТИНА (верное значение) или ЛОЖЬ (неверное значение).

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

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

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

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

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

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

Наиболее часто встречающиеся проверки:

– даты о прошедших событиях должны быть меньше или равны сегодняшней дате (<=СЕГОДНЯ()), а о планах на будущее – больше или равны сегодняшней (>=СЕГОДНЯ());

– время должно попадать в рабочие часы;

– количество, цены и денежные суммы обычно неотрицательные (>= 0);

– количества обычно целые;

– оценки имеют фиксированный диапазон (0-5, 0-10, 0-100 и т.п.);

– месяцы, дни недели, кварталы – это фиксированные списки текстовых значений;

– оптовая цена, цена по акции, цена со скидкой и т.п. должна быть не выше (<=) обычной розничной цены.

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