Как изменить набор значений ячейки. Защита ячейки 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 введена формула =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. Поиск ячеек с Проверкой данных

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

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

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

Для начала на отдельном листе (это не обязательно) разместим список допустимых значений в одном столбце или одной строке (рис. 1а); см. также Excel-файл, лист «Список».

Рис. 1. Список фамилия: (а) в произвольном порядке; (б) в алфавитном порядке.

Скачать в формате , примеры в формате

Этот перечень фамилий будет появляться в раскрывающемся списке в том порядке, в котором они расположены в этом столбце. Понятно, что для удобства поиска их лучше отсортировать по алфавиту (рис. 1б).

Присвоим нашему списку имя диапазона. Для этого выделим диапазон; в нашем случае это область А2:А21 и введем имя диапазона, как показано на рис. 2; в нашем случае – это «фамилии»:

Рис. 2. Присвоение диапазону имени

Выберем область, в которой будем вводить фамилии (см. Excel-файл, лист «Ввод»). В нашем примере – А2:А32 (рис. 3). Перейдем на вкладку Данные, группу Работа с данными, выберем команду Проверка данных:

Рис. 3. Проверка данных

В диалоговом окне «Проверка вводимых значений» перейдем на вкладку Параметры (рис. 4). В поле «Тип данных» выберем «Список». В поле «Источник» укажем: (а) область ячеек, в которых хранится список; этот вариант подходит в том случае, если список расположен на том же листе Excel; (б) имя диапазона; этот вариант может использоваться как в том случае, когда список расположен на том же листе Excel, так и в том случае, если список расположен на другом листе Excel (как в нашем случае). В обоих случаях следует убедиться, что перед ссылкой или именем стоит знак равенства (=).

Рис. 4. Выбор источника данных для списка: (а) на том же листе; (б) на любом листе

И еще о двух опциях на вкладке «Параметры»:

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

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

Рис. 5. Установка Сообщения для ввода

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

Рис. 6. Установка Сообщения об ошибке

Допустимые типы сообщений об ошибке (рис. 7):

• Останов – предотвращает ввод недопустимых данных; кнопка Повторить позволяет вернуться к вводу, кнопка Отмена Повторить.
• Предупреждение – предупреждает о вводе недопустимых данных, но не запрещает такой ввод; кнопка Да позволяет принять недопустимый ввод; кнопка Нет позволяет продолжить набор (ранее набранное в ячейке значение становится доступным для редактирования); кнопка Отмена очищает ячейку и позволяет начать ввод сначала или перейти к вводу в другие ячейки; по умолчанию выбрана кнопка Нет .
• Сообщение – уведомляет о вводе недопустимых данных; хотя и разрешает их ввести. Этот тип сообщения является самым гибким. При появлении информационного сообщения пользователь может нажать кнопку ОК , чтобы принять ввод недопустимых данных, либо нажать кнопку Отмена , чтобы отменить ввод; по умолчанию выбрана кнопка ОК.

Рис. 7. Выбор типа сообщения об ошибке

Некоторые замечания. 1. Если вы ввели в окне Сообщение вкладки Сообщение об ошибке слишком длинный текст, то окно сообщения об ошибке будет слишком широким (как на рис. 7); используйте перенос строки Shift + Enter в том месте сообщения, где вы хотите разделить строки (рис. 8).

Рис. 8. Окно сообщения об ошибке уменьшенной ширины

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

3. Максимальное число записей в раскрывающемся списке ограничено, правда, не слишком сильно:), а именно числом 32 767.

4. Если вы не хотите чтобы пользователи редактировали список проверки, поместите его на отдельном листе, после чего скройте и защитите этот лист.

Выделите ячейку или целую область, которую Excel должен проверить при вводе данных. Теперь перейдите к ленте меню «Данные | Работа с данными | Проверка данных». В следующем окне установите условия проверки. В поле «Тип данных» выберите между такими опциями, как «Целое число», «Действительное», «Список», «Дата», «Время», «Длина текста» или «Другой».

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

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

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

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

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

Предположим, что Вам надо вводить в определенные ячейки значения строго ограниченного размера. Например, ИНН, который должен состоять из 10 символов.

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

Для этого:

1. Выделяем ячейки (можно целиком строку(и), столбец(ы))

2. В Меню выбираем «Данные - Проверка».

3. В появившемся окне
в графе «Тип данных» выбираем - «длина текста»,
в графе «Значение» - «равно»,
в графе «Длина» указываем - «10» (для ИНН).

4. Во вкладке «Сообщение об ошибке» можно задать индивидуальный заголовок и сообщение об этой ошибке. Например:

5. После ввода текста сообщения нажимаем ОК.

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

Причем ввести данные другого размера не получится.

Если ограничение по длине не принципиально, то во вкладке «сообщение об ошибке» выберите пункт «предупреждение», а не «остановка» как по умолчанию.

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

Функция ИЛИ возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Синтаксис

ИЛИ(логическое_значение1; логическое_значение2; ...)

Логическое_значение1, логическое_значение2, ... - от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

Внимание!

Аргументы должны принимать логические значения (ИСТИНА или ЛОЖЬ) или быть массивами или ссылками, содержащими логические значения. Массив - объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.

Если заданный интервал не содержит логических значений, то функция ИЛИ возвращает значение ошибки #ЗНАЧ!.

Можно использовать функцию ИЛИ как формулу массива, чтобы проверить, имеются ли значения в массиве. Чтобы ввести формулу массива, нажмите кнопки CTRL+SHIFT+ENTER.

Пример

	A	B
1	Формула	Описание (результат)
2	=ИЛИ(ИСТИНА)	Один аргумент имеет значение ИСТИНА (ИСТИНА)
3	=ИЛИ(1+1=1;2+2=5)	Все аргументы принимают значение ЛОЖЬ (ЛОЖЬ)
4	=ИЛИ(ИСТИНА;ЛОЖЬ;ИСТИНА)	По крайней мере один аргумент имеет значение ИСТИНА (ИСТИНА)

Еще про Excel.

Определение данных, допустимых для ввода в ячейки

1. Выберите ячейку, которую требуется проверить.

2. Выберите команду Проверка в меню Данные, а затем откройте вкладку Параметры.

3. Определите требуемый тип проверки.

Разрешить ввод только значений из списка

1. В списке Тип данных выберите вариант Список.

2. Щелкните в поле Источник и выполните одно из следующих действий:

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

чтобы использовать диапазон ячеек, которому назначено имя, введите знак равенства (=), а затем - имя диапазона;

3. Установите флажок Список допустимых значений.

Разрешить ввод значений, находящихся в заданных пределах

3. Введите минимальное, максимальное или определенное разрешенное значение.

Разрешить числа без ограничений

1. В списке Тип данных выберите вариант Целое число или Действительное.

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

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

Разрешить даты и время в рамках определенного интервала времени

1. В поле Разрешить выберите Дата или Время.

2. В поле Данные выберите требуемое ограничение. Например, чтобы разрешить даты после определенного дня, выберите значение больше.

3. Введите начальную, конечную или определенную дату или время.

Разрешить текст определенной длины

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

2. В поле Данные выберите требуемое ограничение. Например, чтобы установить определенное количество знаков, выберите значение меньше или равно.

3. Укажите минимальную, максимальную или определенную длину для текста.

Подсчет допустимых значений на основании содержимого другой ячейки.

1. Выберите требуемый тип данных в списке Тип данных.

2. В поле Данные выберите требуемое ограничение.

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

Использование формулы для подсчета допущений

1. Выберите тип Другой в окне Тип данных.

2. В поле Формула введите формулу для расчета логического значения (ИСТИНА для корректных данных или ЛОЖЬ для некорректных данных). Например, чтобы допустить ввод значения в ячейку для счета пикника только в случае, если ничего не финансируется за дискреционный счет (ячейка D6), и общий бюджет (D20) также меньше, чем выделенные 40000 р., можно ввести =AND(D6=0;D20

4. Определите, может ли ячейка оставаться пустой.

Если допускаются пустые (нулевые) значения, установите флажок Игнорировать пустые ячейки.

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

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

5. Чтобы при выделении ячейки отображалось дополнительное сообщение для ввода, перейдите на вкладку Сообщение и установите флажок Отображать подсказку, если ячейка является текущей, после чего укажите заголовок и введите текст для сообщения.

6. Определите способ, которым Microsoft Excel будет сообщать о вводе неправильных данных.

Инструкции

1. Перейдите на вкладку Сообщение об ошибке и установите флажок Выводить сообщение об ошибке.

2. Выберите один из следующих параметров для поля Вид.

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

Для отображения предупреждения, не запрещающего ввод неправильных данных, выберите значение Предупреждение.

Чтобы запретить ввод неправильных данных, выберите значение Стоп.

3. Укажите заголовок и введите текст для сообщения (до 225 знаков).

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

Примечание . Применение проверки вводимых в ячейку значений не приводит к форматированию ячейки.