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

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

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

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

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

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

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



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

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

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

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

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

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

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

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

• Данные ошибочного типа — например, ввод текстовой строки туда, где необходим ввод числа.
• Данные, выходящие за пределы допустимых значений : например, ввод числа 200 в ячейку, требующую ввода числа от 0 до 100.

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

Для задания такой проверки выполните следующие шаги:

1. Выделите одну или несколько ячеек, к которым необходимо применить правило проверки.
2. Выберите вкладку Данные ленты инструментов, далее нажмите на кнопку Проверка данных . Вы увидите окно Проверка вводимых значений — см. рис. 1.
3. В закладке Параметры в раскрывающемся меню Тип данных вы можете задать различные типы допустимых для ввода данных. Любое значение означает возможность задания любого типа данных. В случае выбора других значений из данного выпадающего списка вы сможете задать также дополнительные параметры, к примеру, указать диапазон, в который должно попадать значение.
4. В случае если ячейка из выделенного диапазона уже имеет правило, будет доступна галочка Распространять изменения на другие ячейки с тем же условием . При ее выборе правила применятся и к остальным выбранным ячейкам.
5. Если вы хотите, чтобы при выборе ячейки на экране появлялось сообщение с указанием, выберите закладку Сообщение для ввода . Здесь вы сможете ввести заголовок и сам текст сообщения.
6. Для появления сообщения об ошибке при вводе некорректных данных, перейдите на закладку Сообщение об ошибке . Здесь, помимо заголовка и текста сообщения, вы можете выбрать стиль ошибки. Только в случае типа Останов (остановка) пользователь не сможет проигнорировать данное сообщение и ему придется ввести правильные данные.
7. Нажмите ОК для подтверждения изменений.

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

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

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

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

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

• Целое число . В ячейку разрешен ввод только целых чисел, причем принадлежащих определенному диапазону;
• Действительное . В ячейку разрешен ввод только чисел, в том числе с десятичной частью (нельзя ввести текст, дату ввести можно);
• Дата. Предполагается, что в ячейку будут вводиться даты начиная от 01.01.1900 до 31.12.9999. Подробнее о формате Дата - в статье
• Время . Предполагается, что в ячейку с Проверкой данный этого типа будет вводиться время. Например, на рисунке ниже приведено условие, когда в ячейку разрешено вводить время принадлежащее только второй половине дня, т.е. от 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-х значений из списка вам выбрать не удастся. В источнике можно указать диапазон ячеек, содержащий заранее сформированный список или ссылку на . Пример приведен в статье
• Другой . В ячейку разрешен ввод значений удовлетворяющих более сложным критериям. Для задания критериев необходимо использовать формулу. Рассмотрим это условие подробнее.

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

Чтобы	Введите формулу	Пояснение
Ячейка B2 содержала только текст	ЕТЕКСТ(B2)	В Типе данных нет возможности выбрать тип Текст , поэтому приходится этого добиваться косвенно. Вы можете Проверку данных применить прямо к ячейке B2
Допустить ввод значения в ячейку B1 только в случае, если после ввода значение в ячейке D1 будет больше 100, в D2 меньше, чем 400	И(D1>100;D2<400)	Проверку данных применяем к ячейке B1 . При этом в ячейке D1 введена формула =B1*2 , а в D2 – формула =B1*3 . Хотя эта формула эквивалентна ограничению Действительное с диапазоном от 50 до 133,33, но при более сложных связях ячеек, этот прием может быть полезен
Значение в ячейке, содержащей возраст работника (С1 ), всегда должно быть больше числа полных лет работы (D1 ) плюс 18 (минимальный возраст приема на работу)	=ЕСЛИ(C1>D1+18;ИСТИНА;ЛОЖЬ)	При заполнении таблицы данными о возрасте и стаже работы можно поставить эту проверку для обеих ячеек (C1 и D1 ). Для этого нужно выделить сразу 2 ячейки, вызвать Проверку данных и немного модифицировать формулу =ЕСЛИ($C1>$D1+18;ИСТИНА;ЛОЖЬ)
Все данные в диапазоне ячеек A1:A20 содержали значения	=СЧЁТЕСЛИ($A$1:$A$20;A1)=1 =ПОИСКПОЗ(A1;$A:$A;0)=СТРОКА(A1)	Необходимо выделить ячейки А1:А20 , вызвать инструмент Проверка данных и ввести формулу. Вторую формулу можно использовать для всего столбца А , для этого необходимо выделить не диапазон, а весь столбец А
Значение в ячейке, содержащей имя кода продукта (B5 ), всегда начиналось со стандартного префикса «ID-» и имело длину не менее 10 знаков.	=И(ЛЕВСИМВ(B5;3)="ID-"; ДЛСТР(B5)>9)	Проверку данных вводим для ячейки B5

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

При использовании инструмента Проверка данных , предполагается, что в ячейку будут вводиться константы (123, товар1, 01.05.2010 и пр.), хотя никто не запрещает вводить и формулы. В этом случае проверяться все равно будет результат вычисления формулы. Вообще вводить формулы в ячейки с проверкой данных не советую – легко запутаться. В этом случае советую использовать .

В. Отображение комментария, если ячейка является текущей.

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

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

С. Вывод подробного сообщения об ошибке.

После ввода ошибочного значения Проверка данных может отобразить подробное сообщение о том, что было сделано не так. Это некий аналог Msgbox() из VBA .

D. Создание связанных диапазонов (списков)

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

В EXCEL 2007 в Проверке данных , как и в нельзя впрямую указать ссылку на диапазоны другого листа, например, так =Лист2!$A$1 . Позволяют обойти это ограничение использование .

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

В Excel 2010, напротив, можно использовать правила проверки данных, ссылающиеся на значения на других листах. В Excel 2007 и Excel 97-2003 проверка данных этого типа не поддерживается и не отображается на листе. Однако все правила проверки данных остаются доступными в книге и применяются при повторном открытии книги в Excel 2010, если они не были изменены в Excel 2007 или Excel 97-2003.

F. Как срабатывает Проверка данных

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

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

Поясним на примере. Предположим, к ячейке А1 применена Проверка данных с условием проверки Другой , где в поле формула введено =СТРОКА(A1)=1 , т.е. для всех ячеек из первой строки условие Проверки данных будет принимать значение ИСТИНА, для других строк - ЛОЖЬ вне зависимости от содержания ячейки.

Теперь выделим ячейку А2 и нажмем CTRL+D . Значение из А1 скопируется в А2 вместе с условием Проверки данных . Несмотря на то, что теперь условие Проверки данных будет принимать значение ЛОЖЬ, никакого предупреждающего сообщения выведено не будет. Чтобы убедиться, что данные в ячейках соответствуют условиям определенным в Проверке данных , нужно вызвать команду меню Обвести неверные данные (). Ячейки с неверными данными будут обведены красными овалами. Теперь опять выделим ячеку А2 и нажмем клавишу F2 (войдем в режим Правки), затем нажмем ENTER - появится окно с сообщением, что введенное значение неверно.

Есть еще один способ обхода проверки данных. Предположим, ввод в ячейку ограничен значениями от 1 до 3. Теперь в любую другую ячейку без Проверки данных введем значение 4. Выделим эту ячейку, в Строке формул выделим значение 4 и скопируем его в Буфер обмена . Теперь выделим ячейку с Проверкой данных и нажмем CTRL+V . Значение вставилось в ячейку! Кроме того, Проверка данных осталась нетронутой в отличие от случая, когда через Буфер обмена , например, вставляется значение из WORD. Чтобы убедиться, что данные в ячейке не соответствуют условиям определенным в Проверке данных , нужно вызвать команду меню Обвести неверные данные (Данные/ Работа с данными/ Проверка данных/ Обвести неверные данные ).

G. Поиск ячеек с Проверкой данных

Если на листе много ячеек с Проверкой данных , то можно использовать инструмент (Главная/ Найти и выделить/ Выделение группы ячеек ).

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

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

А он может! В программу встроен мощный инструмент под названием «Проверка данных», который минимизирует ошибки внесения информации.

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

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

Чтобы подключить инструмент «Проверка данных», выделите проверяемые ячейки и кликните на ленте «Данные – Работа с данными – Проверка данных ». Откроется окно настройки «модератора»:

На вкладке «Параметры» нужно задать условие для проверки в поле «Тип данных». Есть 8 типов условий для проверки:

1. Любое значение – проверка данных отключена
2. Целое число – можно ввести лишь целое число с дополнительным критерием;
3. Действительное число – любое действительное число с дополнительным критерием
4. Список – выбрать значение из заранее заданного списка
5. Дата – ввести только дату, удовлетворяющую дополнительному критерию
6. Время – ввести время с дополнительным условием
7. Длина текста – ввести текст определенной длины
8. Другой – ввести значение, которое удовлетворяет записанному вручную логическому выражению

В этом окне можно установить еще две галки:

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

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

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

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

После этого запишем заголовок и основной текст для окна-предупреждения.

А теперь поговорим детальнее о каждом из видов условий.

Условие на целое число в Excel

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

• Между минимальным и максимальным значением
• Вне заданного промежутка значений
• Равно определенному значению
• Не равно определенному значению
• Больше заданного значения
• Меньше заданного значения
• Больше или равно значению
• Меньше или равно значению

Условие на действительное значение

Действительные числа – все положительные, отрицательные числа, а так же, ноль. От предыдущего пункта это правило отличается тем, что можно вводить и дробные числа. Все остальные настройки – аналогичны описанным выше.

Список

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

Чтобы задать список, опишите его в поле источник. Это можно сделать несколькими способами:

• Записать перечень в ячейках на листе книги, а в поле «Источник» указать ссылку на этот массив
• Создать именованный массив, в поле «Источник» указать его имя
• Перечислить пункты прямо в поле «Источник» через точку с запятой

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

Дата

Этот фильтр позволит внести в ячейку только дату. Напомню, что дата в Эксель дата – это тоже число, поэтому такое правило перекликается с условием на действительное значение. Тем не менее, ввести можно только дату, с указанием параметров, таких же, как и в первых двух пунктах.

Время

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

Длина текста

С этой проверкой Вы можете ввести и число и текст, но предлагается задать условие на его длину (в символах). Список параметров все тот же: длина между заданными значениями, вне промежутка, равна какой-то величине и т.п.

Другой

Этот способ проверки самый гибкий. В строке «Формула» Можно задать свою логическую формулу для проверки, если ни один из предыдущих способов проверки Вас не устраивает. Например, чтобы в ячейку А1 вносили только коды, начинающиеся с символов “SUV”, и содержащие 12 символов, внесем в поле формулу: =И(ЛЕВСИМВ(A1;3)=»SUV»;ДЛСТР(A1)=12) . Лишь при выполнении этого правила значение будет считаться верным.

Как проверить ячейки Excel на соблюдение условий

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

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

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

Я же отправляюсь писать новый важный пост, на этот раз про . Не пропустите, особенно если Ваши таблицы большие и сложные. До встречи!

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

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

Способ 1. Именованные диапазоны

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

Выделите ячейку, в которой должен будет появиться раскрывающийся список, а затем выберите команду Данные → Проверка (Data → Validation). В поле Тип данных (Allow) выберите пункт Список (List), а в поле Источник (Source) введите =MyRange . Щелкните на кнопке ОК. Поскольку вы использовали именованный диапазон, ваш список (хотя он и находится на другом листе) теперь можно использовать как список проверки.

Способ 2. Функция ДВССЫЛ

Функция ДВССЫЛ (INDIRECT) позволяет ссылаться на ячейку, содержащую текст, представляющий адрес ячейки. Эту ячейку можно использовать как локальную ссылку, даже если она получает данные из другого листа. Можно применять эту возможность для связи с листом, где расположен список.

Предположим, список находится на листе Sheetl в диапазоне $А$1:$А$8 . Щелкните любую ячейку на другом листе, где должен появиться этот список проверки (список выборки). Затем выберите команду Данные → Проверка (Data → Validation) и в поле Тип данных (Allow) выберите пункт Список (List). В поле Источник (Source) введите следующий код: =INDIRECT("Sheetl!$А$1:$А$8") , в русской версии Excel =ДВССЫЛ("Sheetl!$A$1:$A$8") . Удостоверьтесь, что флажок Список допустимых значений (In-Cell) установлен, и щелкните на кнопке ОК. Список на листе Sheetl должен появиться в раскрывающемся списке проверки.

Если имя листа, на котором расположен список, содержит пробелы, необходимо использовать следующий синтаксис функции ДВССЫЛ (INDIRECT): =INDIRECT(""Sheetl"!$А$1:$А$8") , в русской версии Excel =ДВССЫЛ(""Sheetl"!$А$1:$А$8") . Различие заключается в том, что здесь после первой кавычки стоит один апостроф, а второй апостроф находится перед восклицательным знаком.

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

Преимущества и недостатки обоих способов

У именованных диапазонов и функции ДВССЫЛ (INDIRECT) есть преимущества и недостатки. Преимущество использования именованного диапазона заключается в том, что изменение названия листа не повлияет на список проверки. Это подчеркивает недостаток функции ДВССЫЛ (INDIRECT): любое изменение названия листа не будет автоматически в ней отражаться. Преимущество функции ДВССЫЛ (INDIRECT): когда из именованного диапазона будет удалена первая ячейка или строка либо последняя ячейка или строка, то именованный диапазон вернет ошибку #REF! . В этом недостаток именованного диапазона - если удалить из него ячейки или строки, изменения не повлияют на список проверки.