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

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

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

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

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

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

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

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



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

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

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

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

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

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

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

Чтобы создать
правило проверки, откройте диалоговое
окно Проверка


вводимых
значений


в меню Данные

,
пункт Проверка

.

Диалоговое окно
Проверка
вводимых значений


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

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

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


все в левом нижнем углу диалогового
окна Проверка


вводимых значений па вкладке Параметры

.

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

Перейдите на Лист
«I

_неделя

»

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

В меню Данные


выберите пункт Проверка

.
Появится диалоговое окно Проверка


вводимых


значений

,
открытое на вкладке Параметры

.

В строке Тип
данных


нажмите кнопку списка и выберите Целые
числа

.
Под строкой Значение


появятся поля Минимум


и Максимум

.

Нажмите кнопку
списка в строке Значение


и выберите Между

.

В строке Минимум


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


введите 200.

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

.

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


для ввода.

В строке Заголовок


наберите Введите
показание

.

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


введите .

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

.

В строке Вид


выберите Предупреждение

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

В строке Заголовок


введите Ошибка
.

Совет.

Если оставить поле Сообщение
об ошибке


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

?»

Нажмите ОК

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

В ячейку С8 введите
2501
и
нажмите (Enter

).
Появится
предупреждающее сообщение с заголовком
Ошибка

и текстом
по умолчанию.

Нажмите Повторить


и введите корректное значение от 0 до
200.

B панели инструментов
Стандартная


нажмите кнопку Сохранить

.

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

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

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

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

В качестве примера укажем любое целое число не больше 100000.

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

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

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

А вот так выглядит сообщение, выдаваемое при попытке ввода данных, не подходящих под заданное условие.

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

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

И если уж так случилось, что пользователям все таки удалось ″накосячить″, есть возможность выделить неправильно введенные данные. Сделать это можно, выбрав в меню «Проверка данных» пункт «Обвести неверные данные».

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

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

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

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

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

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

Получили форму от вышестоящей организации для заполнения отчетности. Но заполнить её не можем!
Постоянно, даже после ввода одного любого символа выдает сообщение: «ВВЕДЕННОЕ ЗНАЧЕНИЕ НЕ ВЕРНО. НАБОР ЗНАЧЕНИЙ, КОТОРЫЕ МОГУТ БЫТЬ ВВЕДЕНЫ В ЯЧЕЙКУ ОГРАНИЧЕН»

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

ПОДСКАЖИТЕ КАК ИСПРАВИТЬ ЭТУ БЕДУ?

Автор: rf-webmaestro · Опубликовано 11.05.2018 · Обновлено 08.02.2019

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

Условие проверки

В редакторе Excel существует возможность ограничить ввод информации по каким-нибудь критериям. Процесс происходит следующим образом.

1. Нажмите на любую клетку.
2. Перейдите на вкладку «Данные».
3. Кликните на выделенный инструмент.
4. Выберите пункт «Проверка данных».

1. После этого появится окно, в котором можно будет выбрать формат информации для проверки.

Рассмотрим эти форматы более внимательно.

Любое значение

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

Целое число

Выбрав этот формат, вы сможете отредактировать дополнительную настройку во втором поле.

Например, если выбрать вариант «между», то вы увидите, что в этом окне появятся два дополнительных поля: «Минимум» и «Максимум».

Если выбрать «больше» какой-то величины, то вам нужно будет ввести минимальный допустимый порог. Именно поэтому вы увидите только одно поле – «Минимум», поскольку «Максимум» неограничен.

Действительное

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

Список

Этот формат наиболее интересный.

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

1. Заполните чем-нибудь несколько клеток. Неважно чем.

1. Нажмите на какую-нибудь клетку. Перейдите на знакомую вам вкладку. Кликните на иконку «Работа с данными». Выберите выделенный инструмент.

1. В поле «Тип данных» выберите вариант «Список». Кликните в графу «Источник». Затем выделите нужный диапазон клеток. Так намного удобнее, чем редактировать ссылку вручную. Для продолжения нажмите на «OK».

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

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

Время

Аналогично и тут. Только здесь указывается одно время (без даты).

Длина текста

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

Вариантов довольно много. Этот способ используется при заполнении каких-нибудь бланков или анкет.

Другой

Данный формат отличается от всех остальных. Здесь вы можете указать какую-нибудь формулу для проверки соответствия информации какому-либо условию.

Сообщение об ошибке

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

1. Повторите описанные выше шаги по вызову окна «Проверка вводимых значений».
2. Выберите какой-нибудь тип данных. В качестве примера мы укажем, что необходимо вводить «Действительное» число, которое больше 10.

1. Теперь переходим на вкладку «Сообщение об ошибке».
2. Выбираем вид отображения:
3. «Останов»;
4. «Предупреждение»;
5. «Сообщение».
6. Указываем заголовок и текст уведомления.
7. Для сохранения проверки ввода значений нажмите на «OK»

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

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

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

Если сделать тип «Останов», то ввести неверное значение не удастся.

А теперь попробуйте убрать введенные настройки и оставить пустые поля.

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

Но обратите внимание на то, что текст сообщения отличается!

Отличие версий Microsoft Excel

Описанная выше инструкция подходит для современных редакторов 2010, 2013 и 2016 годов. По сравнению со старыми программами существуют некоторые отличия.

Откройте этот же файл в Экселе 2007. Введите неправильное значение. И вы увидите эту ошибку. То есть в современных редакторах текст сообщения по умолчанию был изменён. Привычный всем вид существовал до 2007 года.

В старом Excel 2003 ошибка точно такая же.

Сообщение для ввода данных

Чтобы у пользователей после ввода информации подобных ошибок не возникало, необходимо указывать такие подсказки заранее. Делается это очень просто.

1. Кликните на любую клетку, для которой вы хотите задать какие-нибудь правила.
2. Откройте в меню раздел «Данные».
3. Выберите инструмент «Работа с данными».
4. Нажмите на иконку «Проверка данных».

1. В появившемся окне перейдите на вкладку «Сообщение для ввода».
2. Укажите любой текст в указанных полях.
3. Для продолжения нажмите на «OK».

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

Как отключить эту ошибку

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

1. Выберите ячейку, в которой вы не можете указать нужное вам значение.
2. Перейдите на панели инструментов на вкладку «Данные».
3. Нажмите на инструмент «Работа с данными».
4. Кликните на иконку «Проверка данных».

1. Для того чтобы убрать все настройки, достаточно нажать на кнопку «Очистить всё».
2. Сохраняем изменения кликом на «OK».

1. Теперь можно вносить любые данные, словно вы открыли пустой файл и никаких настроек там нет.

Примеры от компании Microsoft

Если вы не совсем понимаете, каким образом нужно указывать условия для ввода значений, рекомендуется открыть официальный сайт компании Майкрософт. Там вы сможете найти файл примеров. В нём указаны абсолютно все типы данных и различные случаи составления документов и отчётов.

Заключение

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

Если у вас что-то не получается, значит, вы что-то упускаете из виду или выбираете не тот формат данных. Попробуйте повторить эти действия более внимательно.

Видеоинструкция

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

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

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

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

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

В качестве примера укажем любое целое число не больше 100000.

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

• Останов — останавливает операцию, предотвращая ввод некорректных данных;
• Предупреждение — при вводе некорректных данных выдается предупреждение, но пользователю дается возможность продолжить операцию;
• Сообщение — пользователь уведомляется о том, что введенные данные недопустимы, однако ввод данных разрешен.

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

А вот так выглядит сообщение, выдаваемое при попытке ввода данных, не подходящих под заданное условие.

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

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

И если уж так случилось, что пользователям все таки удалось ″накосячить″, есть возможность выделить неправильно введенные данные. Сделать это можно, выбрав в меню «Проверка данных» пункт «Обвести неверные данные».

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

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

При настройке рабочей книги для пользователей часто может потребоваться контролировать ввод информации в определенные ячейки, чтобы убедиться, что все введенные данные точны и непротиворечивы. Кроме того, вы можете захотеть разрешить в ячейке только определенный тип данных, например числа или даты, или ограничить числа определенным диапазоном, а текст — заданной длиной. Возможно, вы даже захотите предоставить заранее определенный список допустимых значений, чтобы исключить возможные ошибки. Проверка данных Excel позволяет выполнять все эти действия во всех версиях Microsoft Excel 365, 2019, 2016, 20013, 2010 и более ранних версиях.

Что такое проверка данных в Excel?

Проверка данных Excel — это функция, которая ограничивает (проверяет) пользовательский ввод на рабочем листе. Технически вы создаете правило проверки, которое контролирует, какие данные можно вводить в определенную ячейку.

Вот лишь несколько примеров того, что может сделать проверка данных в Excel:

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

Например, вы можете настроить правило, которое ограничивает ввод данных 3-значными числами от 100 до 999. Если пользователь вводит что-то другое, Excel покажет предупреждение об ошибке, объясняющее, что было сделано неправильно:

Как сделать проверку данных в Excel

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

1. Откройте диалоговое окно «Проверка данных».

Напомним, где находится кнопка проверки данных в Excel. Выбрав одну или несколько ячеек для проверки, перейдите на вкладку «Данные» > группа «Работа с данными» и нажмите кнопку «Проверка данных».

2. Создайте правило проверки Excel.

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

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

В качестве примера создадим правило, разрешающее пользователям вводить только целое число от 100 до 999:

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

3. Подсказка по вводу (необязательно).

Если вы хотите отобразить сообщение, объясняющее пользователю, какие данные разрешены в данной ячейке, откройте соответствующую вкладку  и выполните следующие действия:

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

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

4. Отображение предупреждения об ошибке (необязательно)

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

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

Чтобы настроить пользовательское сообщение об ошибке, перейдите на вкладку «Сообщение об ошибке» и задайте следующие параметры:

• Установите флажок Выводить сообщение об ошибке (обычно установлен по умолчанию).
• В поле Вид выберите нужный тип оповещения.
• Введите заголовок и текст сообщения об ошибке в соответствующие поля.
• Нажмите ОК.

И теперь, если пользователь введет недопустимые значения, Excel отобразит специальное предупреждение с объяснением ошибки (как показано в начале этого руководства).

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

Как настроить ограничения проверки данных Excel

При добавлении правила проверки данных в Excel вы можете выбрать один из предопределенных параметров или указать новые критерии на основе собственной формулы. Ниже мы обсудим каждую из встроенных опций.

Как вы уже знаете, критерии проверки определяются на вкладке «Параметры» диалогового окна «Проверка данных» (вкладка «Данные» > «Проверка данных»).

В первую очередь нужно настроить проверку типа записываемых данных.

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

• Равно или не равно указанному числу
• Больше или меньше указанного числа
• Между двумя числами или вне, чтобы исключить этот диапазон чисел

Например, вот как выглядят ограничения по проверке данных Excel, которые допускают любое целое число больше 100:

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

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

Точно так же, чтобы проверить время, выберите Время в поле Значение, а затем определите необходимые критерии.

Например, чтобы разрешить только даты между датой начала в B1 и датой окончания в B2, примените это правило проверки даты Excel:

Разрешить только будни или выходные

Чтобы разрешить пользователю вводить даты только  будних или выходных дней, настройте пользовательское правило проверки на основе функции ДЕНЬНЕД (WEEKDAY).

Если для второго аргумента установлено значение 2, функция возвращает целое число в диапазоне от 1 (понедельник) до 7 (воскресенье). Так, для будних дней (пн-пт) результат формулы должен быть меньше 6, а для выходных (сб и вс) — больше 5.

Таким образом, разрешить только рабочие дни:

=ДЕНЬНЕД( ячейка ; 2)<6

Разрешить только выходные :

=ДЕНЬНЕД( ячейка ; 2)>5

Например, чтобы разрешить ввод только рабочих дней в ячейки C2:C8, используйте следующую формулу:

=ДЕНЬНЕД(A2;2)<6

Проверить даты на основе сегодняшней даты

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

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

• Выберите Дата в поле Тип данных
• Выберите в поле Значение – между
• В поле Начальная дата введите выражение    =СЕГОДНЯ()
• В поле Конечная дата  введите   =СЕГОДНЯ() + 6

Аналогичным образом вы можете ограничить пользователей вводом дат до или после сегодняшней даты. Для этого выберите меньше или больше, чем в поле Значение, а затем введите =СЕГОДНЯ() в  поле Начальная дата или Конечная дата соответственно.

Проверка времени на основе текущего времени

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

В поле Тип данных выберите Время .

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

В поле Время окончания или Время начала (в зависимости от того, какие критерии вы выбрали на предыдущем шаге) введите одну из следующих формул:

Чтобы проверить дату и время на основе текущей даты и времени:

=ТДАТА()

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

=ВРЕМЯ(ЧАС(ТДАТА());МИНУТЫ(ТДАТА());СЕКУНДЫ(ТДАТА()))

Проверка длины текста

Чтобы разрешить ввод данных определенной длины, выберите Длина текста в поле Тип данных и укажите критерии проверки в соответствии с вашей бизнес-логикой.

Например, чтобы ограничить ввод до 15 символов, создайте такое правило:

Примечание. Параметр «Длина текста» ограничивает количество символов, но не тип данных. Это означает, что приведенное выше правило разрешает как текст, так и числа до 15 символов или 15 цифр соответственно.

Список проверки данных Excel (раскрывающийся список)

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

1. Откройте диалоговое окно «Проверка данных» (вкладка «Данные» > «Проверка данных»).
2. На вкладке «Настройки» выберите «Список» в поле «Тип данных».
3. В поле Источник введите элементы списка проверки Excel, разделенные точкой с запятой. Например, чтобы ограничить пользовательский ввод тремя вариантами, введите Да; Нет; Н/Д.
4. Убедитесь, что выбрана опция Список допустимых значений, чтобы стрелка раскрывающегося списка отображалась рядом с ячейкой.
5. Нажмите ОК.

Выпадающий список проверки данных Excel будет выглядеть примерно так:

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

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

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

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

Во всех этих случаях вы просто записываете соответствующую ссылку на диапазон либо элемент таблицы в поле Источник.

Разрешить только числа

В дополнение к встроенным правилам проверки данных Excel, обсуждаемым в этом руководстве, вы можете создавать собственные правила с собственными формулами проверки данных. 

Удивительно, но ни одно из встроенных правил проверки данных Excel не подходит для очень типичной ситуации, когда вам нужно ограничить пользователей вводом только чисел в определенные ячейки. Но это можно легко сделать с помощью пользовательской формулы проверки данных, основанной на функции ЕЧИСЛО(), например:

=ЕЧИСЛО(C2)

Где C2 — самая верхняя ячейка диапазона, который вы хотите проверить.

Примечание. Функция ЕЧИСЛО допускает любые числовые значения в проверенных ячейках, включая целые числа, десятичные дроби, дроби, а также даты и время, которые также являются числами в Excel.

Разрешить только текст

Если вы ищете обратное — разрешить только текстовые записи в заданном диапазоне ячеек, то создайте собственное правило с функцией ЕТЕКСТ (ISTEXT), например:

=ЕТЕКСТ(B2)

Где B2 — самая верхняя ячейка выбранного диапазона.

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

Если все значения в определенном диапазоне должны начинаться с определенного символа или подстроки, выполните проверку данных Excel на основе функции СЧЁТЕСЛИ с подстановочным знаком:

=СЧЁТЕСЛИ(A2; » текст *»)

Например, чтобы убедиться, что все идентификаторы заказов в столбце A начинаются с префикса «AРТ-», «арт-», «Aрт-» или «aРт-» (без учета регистра), определите пользовательское правило с этой проверкой данных.

=СЧЁТЕСЛИ(A2;»АРТ-*»)

Формула проверки с логикой ИЛИ (несколько критериев)

В случае, если есть 2 или более допустимых префикса, добавьте несколько функций СЧЁТЕСЛИ, чтобы ваше правило проверки данных Excel работало с логикой ИЛИ:

=СЧЁТЕСЛИ(A2;»АРТ-*»)+СЧЁТЕСЛИ(A2;»АБВ-*»)

Проверка ввода с учетом регистра

Если регистр символов имеет значение, используйте СОВПАД  (EXACT) в сочетании с функцией ЛЕВСИМВ, чтобы создать формулу проверки с учетом регистра для записей, начинающихся с определенного текста:

=СОВПАД(ЛЕВСИМВ(ячейка; число_символов); текст)

Например, чтобы разрешить только те коды заказов, которые начинаются с «AРТ-» (ни «арт-», ни «Арт-» не допускаются), используйте эту формулу:

=СОВПАД(ЛЕВСИМВ(A2;4);»АРТ-«)

В приведенной выше формуле функция ЛЕВСИМВ извлекает первые 4 символа из ячейки A2, а СОВПАД выполняет сравнение с учетом регистра с жестко заданной подстрокой (в данном примере «AРТ-«). Если две подстроки точно совпадают, формула возвращает ИСТИНА и проверка проходит успешно; в противном случае возвращается ЛОЖЬ и проверка завершается неудачно.

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

Чтобы разрешить ввод значений, которые содержат определенный текст в любом месте ячейки (в начале, середине или конце), используйте функцию ЕЧИСЛО (ISNUMBER) в сочетании с НАЙТИ (FIND) или ПОИСК (SEARCH) в зависимости от того, хотите ли вы совпадение с учетом регистра или без учета регистра:

Проверка без учета регистра:

ЕЧИСЛО(ПОИСК( текст ; ячейка ))

Проверка с учетом регистра:

ЕЧИСЛО(НАЙТИ( текст ; ячейка ))

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

Без учета регистра:

=ЕЧИСЛО(ПОИСК(«ар»;A2))

С учетом регистра:

=ЕЧИСЛО(НАЙТИ(«АР»;A2))

Формулы работают по следующей логике:

Вы ищете подстроку «AР» в ячейке A2, используя НАЙТИ или ПОИСК, и оба возвращают позицию первого символа в подстроке. Если текст не найден, возвращается ошибка. Если поиск успешен и «АР» найден в ячейке, мы получаем номер позиции в тексте, где эта подстрока была найдена. Далее функция ЕЧИСЛО возвращает ИСТИНА, и проверка данных проходит успешно. В случае, если подстроку не удалось найти, результатом будет ошибка и ЕЧИСЛО возвращает ЛОЖЬ. Запись не будет разрешена в ячейке.

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

В ситуациях, когда определенный столбец или диапазон ячеек не должны содержать дубликатов, настройте пользовательское правило проверки данных, разрешающее только уникальные записи. Для этого мы можем использовать классическую формулу СЧЁТЕСЛИ для выявления дубликатов :

=СЧЁТЕСЛИ( диапазон ; самая верхняя_ячейка )<=1

Например, чтобы убедиться, что в ячейки с A2 по A8 вводятся только уникальные идентификаторы заказов, создайте настраиваемое правило со следующей формулой проверки данных:

=СЧЁТЕСЛИ($A$2:$A$8; A2)<=1

При вводе уникального значения формула возвращает ИСТИНА, и проверка проходит успешно. Если такое же значение уже существует в указанном диапазоне (счетчик больше 1), функция СЧЁТЕСЛИ возвращает ЛОЖЬ, и вводимые данные не проходят проверку.

Обратите внимание, что мы фиксируем диапазон абсолютными ссылками на ячейки (A$2:$A$8) и используем относительную ссылку для верхней ячейки (A2), чтобы формула корректно изменялась для каждой ячейки в проверяемом диапазоне.

Как отредактировать проверку данных в Excel

Чтобы изменить правило проверки Excel, выполните следующие действия:

1. Выберите любую из проверенных ячеек.
2. Откройте диалоговое окно «Проверка данных» (вкладка «Данные» > «Проверка данных»).
3. Внесите необходимые изменения.
4. Установите флажок Применить эти изменения ко всем другим ячейкам с теми же параметрами, чтобы скопировать внесенные вами изменения во все остальные ячейки с исходными критериями проверки.
5. Нажмите OK, чтобы сохранить изменения.

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

Как скопировать правило проверки данных Excel в другие ячейки

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

Чтобы скопировать правило проверки в Excel, выполните следующие 4 быстрых шага:

1. Выберите ячейку, к которой применяется правило проверки, и нажмите Ctrl + С , чтобы скопировать его.
2. Выберите другие ячейки, которые вы хотите проверить. Чтобы выделить несмежные ячейки, нажмите и удерживайте клавишу Ctrl при выборе ячеек.
3. Щелкните выделенный фрагмент правой кнопкой мыши, выберите «Специальная вставка» и выберите параметр «Условия на значения».

Либо используйте комбинацию клавиш Ctrl + Alt + V,  и затем — Н.

1. Нажмите ОК.

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

Как найти ячейки с проверкой данных в Excel

Чтобы быстро найти все проверенные ячейки на текущем листе, перейдите на вкладку «Главная» > группа «Редактирование» и нажмите «Найти и выделить» > «Проверка данных» :

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

Как убрать проверку данных в Excel

В целом, есть два способа удалить проверку в Excel: стандартный подход, разработанный Microsoft, и метод без мыши, разработанный фанатами Excel, которые никогда не отрывают руки от клавиатуры без крайней необходимости (например, чтобы выпить чашку кофе:)

Способ 1: Обычный способ отключить проверку данных

Обычно, чтобы удалить проверку данных на листах Excel, выполните следующие действия:

1. Выберите ячейку (ячейки) с проверкой данных.
2. На вкладке «Данные» нажмите кнопку «Проверка данных».
3. На вкладке «Настройки» нажмите кнопку «Очистить все», а затем нажмите «ОК».

Советы:

1. Чтобы удалить проверку данных из всех ячеек на текущем листе, используйте инструмент «Найти и выделить», чтобы выбрать все ячейки с проверкой.
2. Чтобы удалить определенное правило проверки данных, выберите любую ячейку с этим правилом, откройте диалоговое окно «Проверка данных», установите флажок «Применить эти изменения ко всем другим ячейкам с такими же настройками» и нажмите кнопку «Очистить все».

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

Способ 2: Удалить правила проверки данных при помощи Специальной вставки

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

1. Выберите пустую ячейку без проверки данных и нажмите Ctrl + С, чтобы скопировать ее в буфер обмена.
2. Выберите ячейки, в которых вы хотите отключить проверку данных.
3. Нажмите Ctrl + Alt + V, и затем Н. Эта комбинация клавиш вызовет «Специальная вставка» > «Проверка данных».
4. Нажимаем ОК.

Советы по проверке данных в Excel

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

Проверка данных на основе другой ячейки

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

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

Например, чтобы разрешить любое целое число, находящееся между 100 и 999, выберите критерии «минимум» и «максимум» в вкладке «Параметры» и введите в них адреса ячеек с этими числами:

Рис18

Вы также можете ввести формулу в ячейку, на которую указывает ссылка, и Excel проверит ввод на основе этой формулы.

Например, чтобы запретить пользователям вводить даты после сегодняшней даты, введите формулу =СЕГОДНЯ() в какую-нибудь ячейку, скажем, B1, а затем настройте правило проверки даты на основе этой ячейки:

Или вы можете ввести формулу =СЕГОДНЯ() непосредственно в поле Конечная дата, что будет иметь тот же эффект.

Правила проверки на основе формул

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

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

=МИН($С$2:$С$10)

=МАКС($С$2:$С$10)

Обратите внимание, что мы фиксируем диапазон с помощью знака $ (абсолютная ссылка на ячейки), чтобы наше правило проверки Excel работало правильно для всех выбранных ячеек.

Как найти неверные данные на листе

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

Чтобы найти недействительные данные, которые попали в ваши рабочие листы до того, как вы добавили проверку данных, перейдите на вкладку «Данные» и нажмите «Проверка данных» > «Обвести неверные данные».

Это выделит все ячейки, которые не соответствуют критериям проверки.

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

Как поделиться книгой с проверкой данных

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

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

Почему проверка данных Excel не работает?

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

Проверка данных не работает для скопированных данных

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

Хотя нет способа отключить клавиши копирования/вставки (кроме использования VBA), вы можете, по крайней мере, предотвратить копирование данных путем перетаскивания ячеек. Для этого выберите «Файл» > «Параметры» > «Дополнительно» > «Параметры редактирования» и снимите флажок «Включить маркер заполнения и перетаскивания ячеек».

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

Кнопка «Проверка данных» не активна (выделена серым цветом), если вы вводите или изменяете данные в ячейке. Закончив редактирование ячейки, нажмите Enter или Esc, чтобы выйти из режима редактирования, а затем выполните проверку данных.

Проверка данных не может быть применена к защищенной или общей книге

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

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

При проверке данных на основе формул в Excel необходимо проверить три важные вещи:

• Формула проверки не возвращает ошибок.
• Формула не ссылается на пустые ячейки.
• Используются правильные ссылки на ячейки.

Ручной пересчет формул включен

Если в Excel включен режим ручного расчета, невычисленные формулы могут помешать правильной проверке данных. Чтобы снова изменить параметр расчета Excel на автоматический, перейдите на вкладку «Формулы» > группу «Расчет», нажмите кнопку «Параметры расчета» и выберите «Автоматически».

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

Для начала скопируйте формулу проверки в какую-нибудь ячейку, чтобы убедиться, что она не возвращает ошибку, такую ​​как #Н/Д, #ЗНАЧ или #ДЕЛ/0!.

Если вы создаете пользовательское правило , формула должна возвращать логические значения ИСТИНА и ЛОЖЬ или приравненные к ним значения 1 и 0 соответственно.

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

Формула проверки данных не должна ссылаться на пустую ячейку

Если вы активируете опцию «Игнорировать пустые ячейки» при определении правила (обычно выбрано по умолчанию), то следите, чтобы ваши формулы или условия не ссылались на пустую ячейку.

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

Абсолютные и относительные ссылки на ячейки в формулах проверки

При настройке правила проверки Excel на основе формулы помните, что все ссылки на ячейки в вашей формуле относятся к верхней левой ячейке в выбранном диапазоне.

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

Чтобы лучше проиллюстрировать это, рассмотрим следующий пример.

Предположим, вы хотите ограничить ввод данных в ячейках с A2 по A8 целыми числами от 100 (минимальное значение) до 999. Чтобы проще было при необходимости изменить критерии проверки, используем ссылки на ячейки с этими значениями, как показано на скриншоте ниже:

Это правило будет корректно выполняться только для первой ячейки диапазона – A2. Для ячейки A3 критерии максимума и минимума изменятся на E3 и F3. Поскольку эти ячейки пусты, то условие ввода теперь – любое число больше либо меньше нуля. Правда, символьные значения и дробные числа вы вводить не сможете, так как продолжает действовать ограничение на тип данных – целые числа.

Чтобы исправить формулу, просто введите «$» перед ссылками на столбцы и строки, чтобы зафиксировать их: =$E$2 и $F$2. Или используйте клавишу  F4 для переключения между различными типами ссылок.

Вот как вы можете добавить и использовать проверку данных в Excel. Я благодарю вас за чтение и надеюсь ещё увидеть вас в нашем блоге!

Дополнительные сведения о проверке данных

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

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

Ситуации, в которых полезно использовать проверку данных

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

Ограничение данных предопределенными элементами списка: например, можно ограничить выбор отдела значениями «Бухгалтерия», «Финансовый отдел», «Отдел кадров» и т. д.

Ограничение использования чисел за пределами указанного диапазона: например, можно указать максимальный процент годового роста зарплаты сотрудника (например, 3 %) или разрешить только целое число от 1 до 100.

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

Ограничение времени, которое находится за пределами определенного промежутка времени: например, вы можете указать, что собрания должны проводиться в период между 8:00 и 17:00.

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

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

Подсказки и сообщения об ошибках

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

Вы можете настроить сообщение на второй вкладке «Проверка данных».

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

Также можно выводить сообщение об ошибке при вводе пользователем неверных данных.

Существует три типа сообщений об ошибке.

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

У сообщения Остановка есть два параметра: Повторить и Отмена.

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

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

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

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

Советы по использованию проверки данных

Обратите внимание на советы и рекомендации по использованию проверки данных в Excel.

Примечание: Если вы хотите использовать проверку данных в книгах в Службы Excel или Excel Web App, сначала потребуется создать ее в классическом приложении Excel.

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

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

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

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

Чтобы обвести данные, выделите нужные ячейки и выберите Данные > Работа с данными > Проверка данных > Обвести неверные данные.

Чтобы быстро отменить проверку данных в ячейке, выделите ее и выберите Данные > Работа с данными > Проверка данных > Параметры > Очистить все.

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

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

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

Если проверка данных не работает, проверьте указанные ниже условия.

Пользователи не копируют и не вставляют данные. Проверка данных предназначена для отображения сообщений и предотвращения ввода недопустимых данных только в том случае, если пользователь вводит данные непосредственно в ячейку. Если данные копируются или вставляются, сообщения не отображаются. Чтобы предотвратить копирование и вставку данных путем перетаскивания ячеек, выберите Файл > Параметры > Дополнительно > Параметры правки и снимите флажок Разрешить маркеры заполнения и перетаскивание ячеек, а затем защитите лист.

Выключен пересчет вручную. Если включен пересчет вручную, невычисляемые ячейки могут повлиять на правильность проверки данных. Чтобы отключить пересчет вручную, откройте вкладку Формулы, в группе Вычисление выберите Параметры вычислений и щелкните параметр Автоматически.

Отсутствуют ошибки в формулах. Убедитесь, что формулы в ячейках, для которых включена проверка, не являются причиной возникновения ошибок, таких как #ССЫЛКА! или #ДЕЛ/0!. Пока ошибки не будут устранены, приложение Excel будет пропускать проверку данных.

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

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

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

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

Обновление или удаление проверки данных в книге, созданной другим пользователем

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

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

У вас есть вопрос об определенной функции?

Помогите нам улучшить Excel

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

Источник

Набор значений, которые могут быть введены в ячейку Excel, ограничен: решение проблемы

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

Условие проверки

В редакторе Excel существует возможность ограничить ввод информации по каким-нибудь критериям. Процесс происходит следующим образом.

1. Нажмите на любую клетку.
2. Перейдите на вкладку «Данные».
3. Кликните на выделенный инструмент.
4. Выберите пункт «Проверка данных».

1. После этого появится окно, в котором можно будет выбрать формат информации для проверки.

Рассмотрим эти форматы более внимательно.

Любое значение

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

Целое число

Выбрав этот формат, вы сможете отредактировать дополнительную настройку во втором поле.

Например, если выбрать вариант «между», то вы увидите, что в этом окне появятся два дополнительных поля: «Минимум» и «Максимум».

Если выбрать «больше» какой-то величины, то вам нужно будет ввести минимальный допустимый порог. Именно поэтому вы увидите только одно поле – «Минимум», поскольку «Максимум» неограничен.

Действительное

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

Список

Этот формат наиболее интересный.

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

1. Заполните чем-нибудь несколько клеток. Неважно чем.

1. Нажмите на какую-нибудь клетку. Перейдите на знакомую вам вкладку. Кликните на иконку «Работа с данными». Выберите выделенный инструмент.

1. В поле «Тип данных» выберите вариант «Список». Кликните в графу «Источник». Затем выделите нужный диапазон клеток. Так намного удобнее, чем редактировать ссылку вручную. Для продолжения нажмите на «OK».

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

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

Время

Аналогично и тут. Только здесь указывается одно время (без даты).

Длина текста

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

Вариантов довольно много. Этот способ используется при заполнении каких-нибудь бланков или анкет.

Другой

Данный формат отличается от всех остальных. Здесь вы можете указать какую-нибудь формулу для проверки соответствия информации какому-либо условию.

Сообщение об ошибке

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

1. Повторите описанные выше шаги по вызову окна «Проверка вводимых значений».
2. Выберите какой-нибудь тип данных. В качестве примера мы укажем, что необходимо вводить «Действительное» число, которое больше 10.

1. Теперь переходим на вкладку «Сообщение об ошибке».
2. Выбираем вид отображения:
   • «Останов»;
   • «Предупреждение»;
   • «Сообщение».
3. Указываем заголовок и текст уведомления.
4. Для сохранения проверки ввода значений нажмите на «OK»

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

1. Введите любое число. Например, что-нибудь меньше указанного значения. Нажмите на клавишу [knopka]Enter[/knopka]. И вы увидите уведомление о том, что была допущена ошибка ввода данных.

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

Если сделать тип «Останов», то ввести неверное значение не удастся.

А теперь попробуйте убрать введенные настройки и оставить пустые поля.

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

Но обратите внимание на то, что текст сообщения отличается!

Отличие версий Microsoft Excel

Описанная выше инструкция подходит для современных редакторов 2010, 2013 и 2016 годов. По сравнению со старыми программами существуют некоторые отличия.

Откройте этот же файл в Экселе 2007. Введите неправильное значение. И вы увидите эту ошибку. То есть в современных редакторах текст сообщения по умолчанию был изменён. Привычный всем вид существовал до 2007 года.

В старом Excel 2003 ошибка точно такая же.

Сообщение для ввода данных

Чтобы у пользователей после ввода информации подобных ошибок не возникало, необходимо указывать такие подсказки заранее. Делается это очень просто.

1. Кликните на любую клетку, для которой вы хотите задать какие-нибудь правила.
2. Откройте в меню раздел «Данные».
3. Выберите инструмент «Работа с данными».
4. Нажмите на иконку «Проверка данных».

1. В появившемся окне перейдите на вкладку «Сообщение для ввода».
2. Укажите любой текст в указанных полях.
3. Для продолжения нажмите на «OK».

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

Как отключить эту ошибку

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

1. Выберите ячейку, в которой вы не можете указать нужное вам значение.
2. Перейдите на панели инструментов на вкладку «Данные».
3. Нажмите на инструмент «Работа с данными».
4. Кликните на иконку «Проверка данных».

1. Для того чтобы убрать все настройки, достаточно нажать на кнопку «Очистить всё».
2. Сохраняем изменения кликом на «OK».

1. Теперь можно вносить любые данные, словно вы открыли пустой файл и никаких настроек там нет.

Примеры от компании Microsoft

Если вы не совсем понимаете, каким образом нужно указывать условия для ввода значений, рекомендуется открыть официальный сайт компании Майкрософт. Там вы сможете найти файл примеров. В нём указаны абсолютно все типы данных и различные случаи составления документов и отчётов.

Заключение

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

Если у вас что-то не получается, значит, вы что-то упускаете из виду или выбираете не тот формат данных. Попробуйте повторить эти действия более внимательно.

Видеоинструкция

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

Источник

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

Причины появления этой ошибки

Для начала напомним вам, что системный реестр — это база данных, заключающая в себе установки для программного и аппаратного обеспечения ПК. Иногда случается так, что реестр выдаёт ошибку. Этот баг может появиться после следующих действий владельца ПК: чистка реестра, удаление утилит, внесение изменений под влиянием вредоносных программ. Конечно, здесь представлен не полный перечень, но это самые вероятные варианты. Следствием является всплывание окна ошибки при попытке запуска платформ.

Способы решения проблемы

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

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

Внимание: обновите базу данных антивируса до сканирования.

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

1. Нажимаем правой кнопкой мыши по иконке приложения и выбираем Свойства в выплывшем окошке.
   
2. Далее находим надпись Общие.
3. Отмечаем галочкой Разблокировать.
   
4. Кликаем Изменить и устанавливаем софт, который запустит элемент.
5. Нажимаем Применить и далее Ок.
   
6. Все открытые меню выключаем и перезапускаем компьютер.

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

Что делать если не открываются файлы изображений в Windows 10

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

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

1. Нажимаем правой кнопкой мыши по любому файлу изображения.
2. Выбираем Открыть с помощью.
   
3. Кликаем Выбрать другое приложение.
4. Теперь пишем программу в появившемся меню и ставим его как постоянное.
   
5. Ок.

В том случае, если этот метод ничего не исправил, переходим к восстановлению реестра.

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

Проводим анализ системы:

1. Кликаем правой кнопкой мыши по Пуск.
2. Командная строка Администратор.
   
3. Печатаем: sfc/scannow/
   
4. Нажимаем кнопку Enter.
   
5. Заканчиваем процедуру и перезапускаем ПК.

Создаём резервную копию реестра (при условии существования бэкап):

1. Win+R.
2. Печатаем regedit.
3. Ok.
   
4. Раскрываем меню Файл.
5. Нажимаем Экспорт.
   
6. Выбираем Весь реестр и помечаем точкой.
7. Указываем где произвести сохранение файла и нажимаем Сохранить.

Финальная операция: реанимация реестра.

Этапы заключения:

1. Пункты с первого по четвёртый предыдущего руководства.
2. Нажимаем Импорт.
   
3. В Обзоре находим резервную копию «бэкапа».
4. Включаем бэкап и выбираем Да.
   
5. Ожидаем окончания операции и перезагружаем ПК.

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