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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Нажмите Повторить и введите корректное значение от 0 до 200.

B панели инструментов Стандартная нажмите кнопку Сохранить .

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

Синтаксис

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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



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

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

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

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

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

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