Как сделать проверку в экселе. Проверка ввода данных в Excel и ее особенности

При вводе большого количества информации в ячейки таблицы легко допустить ошибку. В 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 введена формула =B12 , а в D2 – формула =B13 . Хотя эта формула эквивалентна ограничению Действительное с диапазоном от 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 правильность написания кода с буквами и числами.
Первый вариант.
Проверка ввода данных в Excel.
У нас такая таблица. В столбце A будем писать коды с буквами и числами.
В этих ячейках установим проверку данных, в соответствии с нашими условиями. Выделяем ячейки столбца А (А31:А36).
На закладке «Данные» в разделе «Работа с данными» нажимаем на кнопку «Проверка данных». Появится диалоговое окно «Проверка вводимых значений».
В этом окне на закладке «Параметры» в разделе «Условия проверки» - «Тип данных» выбираем функцию «Другой».
В строке «Формула» пишем такую формулу.
=И(ЛЕВСИМВ(A31)="Ф";ДЛСТР(A31)=3;ЕЧИСЛО(ЗНАЧЕН(ПРАВСИМВ(A31;2))))
Пояснения к формуле.
Этой формулой мы говорим Excel, что в ячейках столбца А (с ячейки А31) данные должны начинаться на букву «ф» - это часть формулы «ЛЕВСИМВ(А31)="Ф"; .
Всего в ячейке должно быть написано 3 знака – это функция в формуле – ДЛСТР(А31)=3.
А функция ЕЧИСЛО(ЗНАЧЕН(ПРАВСИМВ(A31;2))) говорит, что в ячейке последних 2 знака должны быть числами.
Нажимаем кнопку «ОК». Все проверку данных установили. Проверяем.
В ячейку А34 мы написали первую букву «а». Вышло предупреждающее об ошибке окно.
Если мы введем число меньше или больше 3 знаков, то снова выйдет окно, указывающее на ошибку.
Внимание!
Можно написать сообщение – указать в чем ошибка, или написать подсказку, как правильно написать код. Для этого нужно перейти на закладку «Сообщение об ошибке» или на закладку «Сообщение для ввода».
Как установить сообщения об ошибке или подсказки для заполнения ячейки, читайте в статье «Проверка данных в Excel ».
Второй вариант.
Проверка вводимых данных в Excel.
В формуле можно написать любое количество знаков, любые буквы, несколько букв, т.д. Например, такую формулу.
=И(ЛЕВСИМВ(C33)="Фрукт/";ДЛСТР(C32)=8;ЕЧИСЛО(ЗНАЧЕН(ПРАВСИМВ(C32;2))))
Получился такой код.
Функция проверки данных можно использовать для запрета исправления данных в таблице задним числом. Подробнее о таком способе, читайте в статье «Чтобы не исправляли данные в таблице Excel задним числом» .
Можно запретить вводить повторяющиеся данные. Это способ смотрите в статье «Запретить вводить повторяющиеся значения в Excel» .
В Excel можно сделать саму простую таблицу, можно сделать сложную таблицу, с фильтрами, т.д. Какими способами сделать таблицу в Excel, смотрите в статье "

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

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

Подсказки в MS Excel

Первый способ вставки подсказки

Первым делом разберемся c подсказками. Взгляните на мою табличку. Вроде бы все просто, однако даже при такой простоте можно нагородить солидный огород. К примеру, поле «номер документ» — это №1, 1, или 22.03-1?

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

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

Удалить примечания можно только из контекстного меню правой кнопки мыши

Обратите внимание: нажатие кнопки «Delete» в ячейке не удалит примечание. Избавиться от него (или изменить его текст) можно повторно щелкнув в ячейке правой кнопкой мыши и выбрав пункт «Удалить примечание» или «Изменить примечание».

Второй способ вставки подсказки

Ещё один способ ввести подсказку интересен нам только с точки зрения того, о чем пойдет разговор дальше (фильтрации ввода), тем не менее знать о нем полезно.

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

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

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

Подсказки — это хорошо, но что делать с самыми «прогрессивными» пользователями, которые подсказок не читают? Остается одно — бить по рукам… то есть использовать фильтрацию ввода, я хотел сказать.

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

Первое, что может оказаться полезным, это проверка по длине введенного текста . Выбираем пункт «Длина текста » и указываем любой диапазон ограничений. К примеру, я хочу чтобы текст в поле «Сообщение» был как можно более кратким, но в то же время ячейка не могла оставаться пустой. Значения от 3-х до 25 введенных символов, этого вполне хватит, чтоб вписать что-то вроде «Напоминание об оплате» (21 символ), а при попытке ввести более длинный текст, MS Excel выдаст окно-предупреждение и не даст завершить ввод.

Проверка по числу

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

Выбор из имеющегося списка значений

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

А теперь, сделаем проверку по выпадающему списку в MS Excel. Данные введем вручную, через точку с запятой.

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

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

…или выберем из диапазона

Второй способ более интересен, так как данные «спрятаны» от пользователя, зато набить их больше десятка подряд — проблематично. Первый способ позволяет без проблем задать намного больший список для выбора данных, однако прежде придется этот список разместить где-то в документе (каждое значение на отдельной строке), например на отдельном «служебном» листе в той же книге MS Excel.

Выбор из списка на листе MS Excel. Других данных ввести не удастся.

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

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

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

1. Выделить ячейку или группу ячеек, для которых нужно определить процедуру контроля.
2. На вкладке Данные -> Работа с данными сделать щелчок на кнопке Проверка данных.
3. На вкладке Параметры окна Проверка вводимых значений задать условие проверки данных.

Условие проверки задается путем выбора в раскрывающихся списках Тип данных и Значение соответственно типа данных (целое число, действительное число, дата и др.), которые считаются правильными, и правила сравнения (между, больше, меньше и т.д.), а также границ диапазона допустимых значений (поля Минимум и Максимум). Например, чтобы задать условие, означающее, что в ячейку можно ввести только целое число от 1 до 99, в списке Тип данных надо выбрать Целое число, в списке Значение - вариант между, а в поля Минимум и Максимум ввести, соответственно, 1 и 99.

Если для ячейки определено правило проверки, а значение, которое пользователь ввел в ячейку, неверное, то в результате нажатия клавиши или попытки перейти к другой ячейке каким-либо другим способом (нажатие клавиши или щелчка кнопкой мыши) на экране появляется стандартное сообщение об ошибке. Щелчок на кнопке Повторить в этом окне активизирует процесс редактирования содержимого ячейки, в которой находятся неверные данные, на кнопке Отмена - удаление из ячейки неверных данных.

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

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

Вид сообщения	Значок	Командные кнопки
Остановка		Повторить, Отмена
Предупреждение		Да, Нет, Отмена
Сообщение		OK, Отмена

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

Предупреждающее сообщение заканчивается вопросом "Продолжить?". В результате щелчка на кнопке Да неверные данные записываются в ячейку. Щелчок на кнопке Нет активизирует режим редактирования.

Щелчок на кнопке OK в окне информационного сообщения оставляет неверные данные в ячейке таблицы.