Введено недопустимое значение следует ввести число excel. Защита ячейки в Excel от ввода некорректных данных. Просмотр и ввод данных через форму

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

Для начала на отдельном листе (это не обязательно) разместим список допустимых значений в одном столбце или одной строке (рис. 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 . Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав клавиши CTRL+T .

Примечания:

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

На ленте откройте вкладку Данные и нажмите кнопку Проверка данных .

Примечание: Если кнопка Проверка данных недоступна, возможно, лист защищен или является общим. Разблокируйте определенные области защищенной книги или отмените общий доступ к листу, а затем повторите шаг 3.

На вкладке Параметры в поле Тип данных выберите пункт Список .

Щелкните поле Источник и выделите диапазон списка. В примере данные находятся на листе "Города" в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.

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

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

Откройте вкладку Подсказка по вводу .
Откройте вкладку Сообщение об ошибке .
Не знаете, какой параметр выбрать в поле Вид ?

Работа с раскрывающимся списком

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

Скачивание примеров

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

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

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

См. также

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

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

Синтаксис

ИЛИ(логическое_значение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» и сообщение «Введенное значение неверно. Набор значений, которые могут быть введены в ячейку, ограничен.»

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

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

Ограничение ввода данных

Запрос на ввод допустимых записей

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

Вывод сообщения об ошибке при вводе недопустимых данных

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

Добавление проверки данных в ячейку или диапазон ячеек

Примечание: Первые два шага, описанные в этом разделе, предназначены для добавления проверки данных любого типа. Шаги 3-7 предназначены специально для создания раскрывающегося списка.

Выделите одну или несколько ячеек, к которым нужно применить проверку.

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

На вкладке Параметры в разделе Разрешить нажмите кнопку Список .

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

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

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

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

Примечания:

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

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

	Сделайте следующее:
Разрешить вводить только целые числа из определенного диапазона	В списке Разрешить выберите значение Целое число . В поле Данные выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Диапазон . Введите минимальное, максимальное или определенное разрешенное значение. Можно также ввести формулу, которая возвращает числовое значение. Например, допустим, что вы проверяете значения в ячейке F1. Чтобы задать минимальный объем вычетов, равный значению этой ячейки, умноженному на 2, выберите пункт Больше или равно в поле Данные и введите формулу *=2F1 в поле Минимальное значение** .
Разрешить вводить только десятичные числа из определенного диапазона
Разрешить вводить только даты в заданном интервале времени	Выполните действия, описанные в 1-2 выше. В поле Разрешить выберите значение Дата . В поле Данные выберите необходимый тип ограничения. Например, для разрешения даты после определенного дня выберите ограничение Больше . Введите начальную, конечную или определенную разрешенную дату. Вы также можете ввести формулу, которая возвращает дату. Например, чтобы задать интервал времени между текущей датой и датой через 3 дня после текущей, выберите пункт Между в поле Данные , потом введите =СЕГОДНЯ() в поле Дата начала и затем введите =СЕГОДНЯ()+3 в поле Дата завершения .
Разрешить вводить только время в заданном интервале	Выполните действия, описанные в 1-2 выше. В поле Разрешить выберите значение Время . В поле Данные выберите необходимый тип ограничения. Например, для разрешения времени до определенного времени дня выберите ограничение меньше . Укажите время начала, окончания или определенное время, которое необходимо разрешить. Если вы хотите ввести точное время, используйте формат чч:мм. Например, если в ячейке E2 задано время начала (8:00), а в ячейке F2 - время окончания (17:00) и вы хотите ограничить собрания этим промежутком, выберите между в поле Данные , а затем введите =E2 в поле Время начала и =F2 в поле Время окончания .
Разрешить вводить только текст определенной длины	Выполните действия, описанные в 1-2 выше. В поле Разрешить выберите значение Длина текста . В поле Данные выберите необходимый тип ограничения. Например, для установки определенного количества знаков выберите ограничение Меньше или равно . В этом случае мы хотели бы ограничить ввод на 25 знаков, поэтому установите флажок меньше или равно в поле данные и введите 25 в поле максимум .
Вычислять допустимое значение на основе содержимого другой ячейки	Выполните действия, описанные в 1-2 выше. В поле Разрешить выберите необходимый тип данных. В поле Данные выберите необходимый тип ограничения. В поле или полях, расположенных под полем Данные , выберите ячейку, которую необходимо использовать для определения допустимых значений. Например, чтобы допустить ввод сведений для счета только тогда, когда итог не превышает бюджет в ячейке E1, выберите значение Число десятичных знаков в списке Разрешить , ограничение "Меньше или равно" в списке "Данные", а в поле Максимальное значение введите >= =E1 .

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

Примечания:

В примерах ниже при создании формул с условиями используется настраиваемый вариант. В этом случае содержимое поля "Данные" не играет роли.

Снимки экрана в этой статье взяты Excel 2016; но функциональность одинакова в Excel в Интернете.

Чтобы	Введите формулу
Значение в ячейке, содержащей код продукта (C2), всегда начинается со стандартного префикса "ID-" и имеет длину не менее 10 (более 9) знаков.	= И (LEFT (C2; 3) = "ID-"; ДЛСТР (C2) >9)
	=ЕТЕКСТ(D2)
Значение в ячейке, содержащей чью-то дату рождения (B6), было больше числа лет, указанного в ячейке B4.	*=ЕСЛИ(B6<=(СЕГОДНЯ()-(365B4));TRUE,FALSE)**
Все данные в диапазоне ячеек A2:A10 содержали уникальные значения.

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

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

Диалоговое окно Проверка вводимых значений может быть использовано для определения типа данных, которые должны вводиться в определенную ячейку, и затем, в зависимости от выбранного вами типа данных, для установки условий, которым должна отвечать информация для ее отображения в ячейке. На следующем рисунке показано, как сделать, чтобы в ячейку можно было ввести, например, только значения от 1000 до 2000.

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

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

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

Перейдите на Лист «I _неделя »

Выделите группу ячеек C8:Z14.

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

В строке Тип данных нажмите кнопку списка и выберите Целые числа . Под строкой Значение появятся поля Минимум и Максимум .

Нажмите кнопку списка в строке Значение и выберите Между .

В строке Минимум введите 0. В строке Максимум введите 200.

Уберите галочку в строке Игнорировать пустые ячейки .

Перейдите во вкладку Сообщение для ввода.

В строке Заголовок наберите Введите показание .

В строке Сообщение введите .

Перейдите во вкладку Сообщение об ошибке .

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

В строке Заголовок введите Ошибка .

Совет. Если оставить поле Сообщение об ошибке пустым. Excel будет выводить сообщение по умолчанию: "Введенное значение неверно. Набор значений, которые могут быть введены в ячейку, ограничен. Продолжить ?"

Нажмите ОК . Рядом с ячейками С8:Z14 появится подсказка с названием "Введите показание " и текстом "Пожалуйста, введите показание прибора ".

В ячейку С8 введите 2501 и нажмите (Enter ). Появится предупреждающее сообщение с заголовком Ошибка и текстом по умолчанию.