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

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

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

• 01.01.2001;
• 01/01/2001;
• 1 января 2001 года и т.д.

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

Где находится?

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

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

Настройка условия проверки

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

• Целое число;
• Действительное число;
• Список;
• Дата;
• Время;
• Длина текста;
• Другой.

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

Самым необычным видом является выпадающий список .

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

Всплывающая подсказка ячейки Excel

Функционал проверки данных в Excel позволяет настраивать всплывающие подсказки для ячеек листа. Для этого следует перейти на вторую вкладку окна проверки вводимых значений – «Сообщение для ввода».

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

Пример всплывающей подсказки в Excel:

Вывод сообщения об ошибке

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

Существует три варианта сообщений, отличающихся по поведению:

• Останов;
• Предупреждение;
• Сообщение.

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

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

Сообщение выводить ошибку в виде простой информации и дает возможность отменить последнее действие.

Рассмотрим, как защитить отдельные ячейки в Excel . Есть несколько способов. Можно вставить в ячейку выпадающий список. Пользователь будет выбирать нужное слово из появившегося списка. Какими способами сделать раскрывающийся список, смотрите в статье "Выпадающий список в Excel" .
Второй способ, чтобы защитить ячейки Excel от ввода неверных данных - это функциия «Проверка данных». Это нужно для правильного заполнения анкеты, заявки, для работы с таблицей, где сотрудника постоянно отвлекают, др.
Сначала отмечаем ячейку или диапазон ячеек, куда нужно установить ограничения по вводу данных. Как выделить диапазон ячеек по его имени, смотрите в статье « Диапазон в Excel ».
Теперь заходим на закладку «Данные» в раздел "Работа с данными", нажимаем на кнопку «Проверка данных». Выйдет окно «Проверка вводимых значений». На вкладке " Параметры " в строке «Тип данных» будет стоять тип - «Любое значение».
Нам нужно здесь установить свое значение. Мы установили функцию «Целое число». Указали самое маленькое и самое большое числа, которые можно вводить.
Например, в анкете написано «возраст». Нужно указать полное количество лет, а пишут дату рождения.
Заполнили диалоговое окно так.
Нажимаем "ОК". Теперь проверяем, вводим разные цифры, а когда ввели цифру 1234 (это больше 100) и нажали «Enter», появилось такое окно предупреждения.

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

Чтобы убрать из ячейки эту настройку, заходим в функцию «Проверка данных» и нажимаем кнопку «Очистить все», затем - «ОК».
Можно настроить ячейки таблицы, бланка для заполнения так., чтобы не могли написать только дату рабочего дня. подробнее о такой настройки ячеек, смотрите в статье "Проверка даты в Excel" .
Можно настроить таблицу так, чтобы нельзя было ввести одно название. слово, код, значение дважды. Об этом читайте "Запретить вводить повторяющиеся значения в Excel".
Для контроля за сроками реализации продуктов, за сроками оплаты счетов, за разницей в сумме, т.д., можно установить функцию "Условное форматирование". Например, если сумма в ячейке будет больше 6000 рублей, то ячейка окрасится цветом.
Смотрите, как установить эту функцию, в статье "

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

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

Для этого:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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



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

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

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

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

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

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