Как ограничить ввод данных в ячейку excel: набор значений которые могут быть введены в ячейку ограничен

Excel набор значений которые могут быть введены в ячейку ограничен

Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору В меню Данные -> Проверка… можно указать, какие данные можно вводить в ячейку. По умолчанию стоит «Любое значение». Можно задать следующие условия: целое или действительное число в определённом диапазоне, список допустимых значений (можно ввести вручную или выделить на листе), дата или время в определённом диапазоне, ограничить длину текста, или вообще задать любую формулу, можно с использованием функций (при выборе типа «Другой»).

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

На третьей вкладке можно задать сообщение об ошибке, которое появится при попытке ввода в ячейку недопустимых значений. Опять задаётся заголовок и текст сообщения об ошибке, но окно с сообщением в этом случае является стандартным диалоговым окном MsgBox. Можно задать один из трёх вариантов сообщения об ошибке: «Останов», «Предупреждение» или «Сообщение». В случае «Останов» недопустимое значение ввести в ячейку невозможно – нужно его исправить или удалить вообще (кстати, на первой вкладке также указывается, нужно ли проверять пустые ячейки). В случаях «Предупреждение» и «Отмена» можно ввести недопустимое значение, просто появляется сообщение о его несоответствии условиям. Отличия между двумя последними вариантами только в том, что «Предупреждение» дополнительно предлагает вернуться и исправить значение в ячейке.

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

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

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

Можно также быстро выделить все ячейки, для которых заданы условия на значения, с помощью команды Правка -> Перейти -> Выделить, установив там переключатель «проверка данных», причём можно выделить как все ячейки, для которых задано какое-нибудь условие, так и только те, для которых условие совпадает с условием в текущей ячейке.

Если осталось ещё что-то непонятное – воспользуйся поиском по справке Excel по словам «проверка значений», «условия на значения» и т.д.

Excel набор значений которые могут быть введены в ячейку ограничен

Создание раскрывающегося списка

​Смотрите также​ Is Nothing And​ список.​Сформируем именованный диапазон. Путь:​ Можно выбрать конкретное.​ ячейку, ограничен. Как​

​ данных, то можно​ принимать значение ИСТИНА,​

​ нельзя впрямую указать​ данных и ввести​ или ссылку на​ Числовым эквивалентом для​Применение проверки данных к​На вкладке​выберите пункт​ запретить пользователям его​Игнорировать пустые ячейки​Чтобы упростить работу пользователей​ Target.Cells.Count = 1​Открываем параметры проверки данных.​​ «Формулы» — «Диспетчер​​Очень удобный инструмент Excel​

​ исправить?​​ использовать инструмент Выделение​

​ для других строк​ ссылку на диапазоны​ формулу. Вторую формулу​ зависящую от нее​ 23:59:59 будет 0,99999.​ ячейкам​Параметры​Список​ просмотр и изменение,​.​

​ с листом, добавьте​ Then Application.EnableEvents =​ В поле «Источник»​ имен» — «Создать».​ для проверки введенных​

​Евгений токарев​ группы ячеек (Главная/​ — ЛОЖЬ вне​

​ другого листа, например,​​ можно использовать для​​ ячейку. Например,​​Длина текста​​Функции Excel (по категориям)​

​в поле​​.​​ скройте и защитите​​Установите флажок​ в ячейки раскрывающиеся​ False If Len(Target.Offset(1,​ вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).​ Вводим уникальное название​ данных. Повысить комфорт​: Встаньте в ячейке,​ Найти и выделить/​

​ зависимости от содержания​​ так =Лист2!$A$1. Позволяют​​ всего столбца​​Чтобы​​. В ячейку разрешен​​При вводе большого количества​​Разрешить​

​Щелкните поле​​ этот лист. Подробнее​​Список допустимых значений​ списки, в которых​ 0)) = 0​Имя файла, из которого​ диапазона – ОК.​ работы с данными​ затем Меню -​ Выделение группы ячеек).​ ячейки.​ обойти это ограничение​А​Введите формулу​

​ ввод только определенного​ информации в ячейки​​выберите пункт​​Источник​

​ о защите листов​​Откройте вкладку​

​ можно выбрать нужный​​ Then Target.Offset(1, 0)​​ берется информация для​

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

​Подсказка по вводу​​ элемент.​​ = Target Else​

​ списка, заключено в​ любой ячейке. Как​ списков: подстановка данных,​ Тип данных -​ инструмента позволяет выделить​​А2​​Если в Проверке данных нужно​ выделить не диапазон,​​Ячейка​​ этом ограничении можно​ ошибку. В EXCEL​.​ листе список допустимых​ Блокировка ячеек.​

​.​Windows macOS Online​​ Target.End(xlDown).Offset(1, 0) =​​ квадратные скобки. Этот​

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

​Если вы хотите, чтобы​ ​ Target End If​ файл должен быть​​ известно. Источник –​​ листа или файла,​

​Вообще, удобная штука​​ проводится проверка допустимости​CTRL+D​ на ячейку​А​содержала только текст​ и даты, главное,​ проверки введенных данных​ таблицу с элементами​Диалоговое окно свернется, чтобы​

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

​ элементы раскрывающегося списка,​ при выборе ячейки​На новом листе введите​ Target.ClearContents Application.EnableEvents =​ открыт. Если книга​ имя диапазона: =деревья.​ наличие функции поиска​

​ на самом деле.​ данных (заданная с​. Значение из​А1​Значение в ячейке, содержащей​=ЕТЕКСТ(B2)​ чтобы количество введенных​ сразу после нажатия​ раскрывающегося списка, щелкните​ было видно весь​ см. статью Добавление​

​ появлялось всплывающее сообщение,​ данные, которые должны​ True End If​ с нужными значениями​Снимаем галочки на вкладках​

​ и зависимости.​ Можно делать списки​ помощью команды Данные/​

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

​А1​другого листа, то​ имя кода продукта​В Типе данных нет​ символов не противоречило​ клавиши ENTER –​ поле​ лист.​ и удаление элементов​ установите флажок​

​ отображаться в раскрывающемся​ End Sub​ находится в другой​ «Сообщение для ввода»,​Путь: меню «Данные» -​

​ выбора, можно проверять​ Работа с данными/​скопируется в​ нужно сначала определить​ (​ возможности выбрать тип​ ограничению по длине​ Проверка данных.​Источник​Нажмите клавишу ВВОД или​ раскрывающегося списка.​Отображать подсказку, если ячейка​ списке. Желательно, чтобы​Чтобы выбираемые значения отображались​ папке, нужно указывать​

​ «Сообщение об ошибке».​ инструмент «Проверка данных»​ значения ввода пользователей​ Проверка данных). При​А2​ Имя для этой​B5​

​ Текст, поэтому приходится​ текста. Например, при​Инструмент Проверка данных (Данные/​

​и выделите ячейки,​​ кнопку​​Чтобы удалить раскрывающийся список,​​ является текущей​​ элементы списка содержались​​ в одной ячейке,​​ путь полностью.​​ Если этого не​​ — вкладка «Параметры».​

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

​ в таблице Excel.​​ разделенные любым знаком​​Возьмем три именованных диапазона:​ сделать, Excel не​​ Тип данных –​​ сообщений и т.​​ будут выделены все​​ Проверки данных. Несмотря​

​ сослаться на это​​ стандартного префикса «ID-»​​ Вы можете Проверку​ менее 5, нельзя​ Проверка данных) не​

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

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

​ на то, что​​ имя в правиле​

​ и имело длину​ данных применить прямо​​ ввести дату позднее​​ был бы столь​

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

​ новые значения.​Ввести значения, из которых​Idoktop»petrograd»​ выборе опции Этих​ теперь условие Проверки​​ Проверке данных.​​ не менее 10​​ к ячейке​​ 13/10/2173, т.к. ей​​ популярным, если бы​​ заголовка. Добавьте только​ затем нажмите кнопку​​ с несколькими примерами​​ 225 символов). Если​

​ быстро преобразовать в​Private Sub Worksheet_Change(ByVal​

​ обычный список именованным​Вызываем редактор Visual Basic.​

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

​B2​​ соответствует число 99999,​

​ его функциональность ограничивалась​ ячейки, которые должны​ОК​ проверки данных, аналогичными​ вы не хотите,​ таблицу, выделив любую​ Target As Range)​ диапазоном (с помощью​ Для этого щелкаем​ список, можно разными​

​Миша куць​ те ячейки, для​ значение ЛОЖЬ, никакого​ использовать правила проверки​=И(ЛЕВСИМВ(B5;3)=»ID-«; ДЛСТР(B5)>9)​

​Допустить ввод значения в​ а 14/10/2173 -​ бы только собственно​

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

​On Error Resume​​ «Диспетчера имен»). Помним,​​ правой кнопкой мыши​​ способами:​​: Поставте тип поля​​ которых установлены те​​ предупреждающего сообщения выведено​

​ данных, ссылающиеся на​Проверку данных вводим для​ ячейку​ это уже 100000,​​ проверкой. Ниже кратко​​ списке. Список элементов​Советы:​ статье. Вы можете​ снимите этот флажок.​ нажав клавиши​ Next​ что имя не​ по названию листа​Вручную через «точку-с-запятой» в​ текстовый!​​ же правила проверки​​ не будет. Чтобы​

​ значения на других​

​ ячейки​B1​​ т.е. 6 символов.​​ перечислены основные приемы​

​ также можно ввести​​ ​

​ воспользоваться ими или​​Откройте вкладку​​CTRL+T​

​If Not Intersect(Target,​ может содержать пробелов​ и переходим по​ поле «Источник».​​Claire​​ данных, что и​ убедиться, что данные​ листах. В Excel​B5​только в случае,​ Интересно, что при​ работы с этим​

​ непосредственно в поле​​Значения также можно ввести​​ создать собственные сценарии​

​Сообщение об ошибке​.​ Range(«C2:C5»)) Is Nothing​ и знаков препинания.​ вкладке «Исходный текст».​​Ввести значения заранее. А​​: ну уж не​ для активной ячейки.​​ в ячейках соответствуют​​ 2007 и Excel​При выделении нескольких ячеек,​ если после ввода​ ограничении, например, менее​ инструментом.​

​Источник​​ непосредственно в поле​​ проверки данных. Скачать​

​.​Примечания:​ And Target.Cells.Count =​Создадим первый выпадающий список,​ Либо одновременно нажимаем​ в качестве источника​ текстовый. числовой!​Получили форму от вышестоящей​ условиям определенным в​ 97-2003 проверка данных​ там где нужно,​ значение в ячейке​ 5 символов, вы​Как видно на рисунке​через запятую. Например:​

​Источник​ примеры проверки данных​

​Если вы хотите, чтобы​

Проверка данных в MS EXCEL

​ ​ 1 Then​ куда войдут названия​ клавиши Alt +​ указать диапазон ячеек​Analitik​ организации для заполнения​ Проверке данных, нужно​ этого типа не​

​ не забывайте указывать​D1​ не сможете ввести​ снизу, условия проверки​Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы​через запятую.​ Excel​ при вводе значения,​Почему данные следует поместить​Application.EnableEvents = False​ диапазонов.​

A. Проверка введенных значений

​ F11. Копируем код​ со списком.​: Странно. У меня​ отчетности. Но заполнить​

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

Создание раскрывающегося списка

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

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

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

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

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

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

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

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

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

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

Откройте вкладку Подсказка по вводу.

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

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

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

Не знаете, какой параметр выбрать в поле Вид?

Чтобы отобразить сообщение, не препятствующее вводу данных, которые не содержатся в раскрывающемся списке, выберите вариант Сообщение или «Предупреждение». Если выбрать вариант «Сообщение», сообщение будет отображаться со значком , а если «Предупреждение» — со значком .

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

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

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

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

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

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

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

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

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

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

Выделите ячейки, для которых нужно ограничить ввод данных.

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

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

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

Щелкните поле Источник и выделите на листе список допустимых элементов.

Диалоговое окно свернется, чтобы было видно весь лист.

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

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

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

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

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

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

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

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

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

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

Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы

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

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

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

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

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

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

Нажмите кнопку ОК.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

Автор: 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. Для сохранения проверки ввода значений нажмите на «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. Кроме этого, особое внимание было уделено настройкам различных условий для определения допустимого набора величин.

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

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

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

Защита ячейки в Excel от ввода некорректных данных

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

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

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

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

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

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

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

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

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

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

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

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

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

Как сделать ограничения в excel?

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

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

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

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

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

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

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

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

Целое число

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

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

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

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

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

Список

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

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

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

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

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

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

Дата

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

Время

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

Длина текста

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

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

Другой

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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

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

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

Недавно дочь обратилась с вопросом, нельзя ли в Excel выпадающий в ячейке список сделать контекстным, например, зависящим от содержания ячейки, находящейся слева от ячейки со списком (рис. 1)? Я довольно давно не использовал в работе выпадающие списки, поэтому для начала решил освежить свои знания по вопросу проверки данных в Excel.

Рис. 1. Состав выпадающего списка зависит от содержания соседней ячейки

Скачать заметку в формате Word или pdf, примеры в формате Excel2007

Команда Проверка данных находится на вкладке Данные, область Работа с данными.

Примечание. Иногда команда Проверка данных может быть недоступна:

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

К сожалению, Excel в своем стандарте позволяет делать списки только на основе:

• имени массива
• диапазона ячеек
• прямого перечисления элементов списка (рис. 2).

Примечание. Элементы списка вводите через стандартный разделитель элементов списка Microsoft Windows (в русском Excel по умолчанию это точка с запятой).

Рис. 2. Возможные источники списка: вверху – имя массива; посередине – диапазон ячеек; внизу – элементы списка

Попытка ввести формулу в поле Источник диалогового окна Проверка вводимых значений заканчивается неудачей (рис. 3). Видно, что Excel не воспринял значение ячейки D2 ("цвет"), как имя массива, и просто включил это значение в качестве единственного элемента списка.

Рис. 3. Недопустимый источник списка – формула

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

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

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

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

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

И всё же в Excel есть одна функция непрямого действия. На английском языке у нее говорящее название – INDIRECT. На русском – название функции ни о чем – ДВССЫЛ… В чем же заключается непрямое действие? В отличие от других функций Excel, ДВССЫЛ возвращает не значение, хранящееся в ячейке, а ссылку, хранящуюся в ячейке. Непонятно? Сам «продирался» через это с трудом 🙂 Попробую пояснить. Вот что написано в справке Excel: ДВССЫЛ – возвращает ссылку, заданную текстовой строкой. Ссылки немедленно вычисляются для вывода их содержимого (рис. 4).

Рис. 4. Как работает функция ДВССЫЛ: вверху – формулы, внизу – значения

Понимаю, что если вы впервые столкнулись с функцией ДВССЫЛ, то разобраться сложно. Пробуйте, экспериментируйте, и понимание со временем придет.

Итак, еще раз, ДВССЫЛ возвращает ссылку, а не значение, хранящееся в ячейке. Ссылка немедленно вычисляется, и выводится ее значение (или содержимое). Именно это свойство позволит нам ввести непрямую ссылку на соседнюю ячейку так, что вернется не значение, хранящееся в этой соседней ячейке (как на рис. 3), а ссылка, хранящаяся в ячейке, эта ссылка тут же вычисляется, и получается имя массива (рис. 5).

Рис. 5. Формирование списка, зависящего от значения в левой ячейке

Примечание. Ссылка в формуле =ДВССЫЛ(D2) должна быть относительной (D2), а не абсолютной ($D$2). Подробнее об этом см. раздел Тип ссылок на ячейки в формулах для проверки данных заметки Excel. Проверка данных.

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

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

1. Выделите диапазон В2:В8.

Примечание: Ячейка В10 содержит функцию SUM (СУММ), которая вычисляет сумму ячеек диапазона B2:B8.

2. На вкладке Data (Данные) нажмите кнопку Data Validation (Проверка данных).
3. Выберите пункт Custom (Другой) из выпадающего списка Allow (Тип данных).
4. Введите формулу, показанную ниже, в поле Formula (Формула) и нажмите ОК.

   =SUM($B$2:$B$8)


Как ограничить ввод данных в ячейку excel

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

Как часть ограничения ввода данных на число (которое может быть целым числом, десятичной, датой или временем), вы также указываете допустимые значения для этого типа номера (целое число от 10 до 100 или дата между 1 января 2012 года и 31 декабря 2012 года, например).

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

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

Чтобы использовать функцию проверки данных, поместите курсор ячейки в ячейку, где вы хотите ограничить тип ввода данных, который вы можете там сделать, а затем нажмите кнопку «Проверка данных» на вкладке «Данные» ленты (или нажмите Alt + AVV). Откроется диалоговое окно «Проверка данных» с выбранной вкладкой «Настройки».

Затем щелкните раскрывающуюся кнопку, прикрепленную к раскрывающемуся списку Разрешить, и выберите один из следующих элементов:

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

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

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

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

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

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

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

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

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

I ). После выбора типа предупреждения вы затем вводите заголовок для своего диалогового окна в текстовое поле «Заголовок» и вводите текст предупреждающего сообщения в поле «Сообщение об ошибке».

Чтобы применить ограничение, которое вы определяете в диалоговом окне «Проверка данных» для всех других ячеек, отформатированных так же, как и в диапазоне ячеек, отформатированных как таблица, нажмите «Применить эти изменения ко всем другим ячейкам с одинаковыми» Установите флажок, прежде чем нажимать OK.

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

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

На этом рисунке показано, что происходит в электронной таблице после закрытия диалогового окна «Проверка данных». Здесь вы увидите всплывающее меню (со списком городов, взятых из диапазона ячеек A2: A6), как оно появляется, когда вы нажимаете новую всплывающую кнопку ячейки.

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

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

Чтобы найти ячейки, к которым была применена проверка данных, откройте диалоговое окно «Перейти» (Ctrl + G или F5), а затем нажмите кнопку «Специальная» и нажмите кнопку «Проверка данных» в диалоговом окне «Перейти к специальному».

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

Чтобы избавиться от настроек проверки данных, назначенных для определенного диапазона ячеек или ячеек, выберите ячейку или диапазон, откройте диалоговое окно «Проверка данных» (Alt + AVV), а затем нажмите кнопку «Очистить все», прежде чем нажимать «ОК».

Автор: 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. Кроме этого, особое внимание было уделено настройкам различных условий для определения допустимого набора величин.

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

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

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

В этом курсе:

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

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

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

Выберите данные >проверка данных.

На вкладке Параметры в списке Тип данных выберите подходящий вариант:

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

» Дес .», чтобы ограничить ячейки как допустимые только десятичными числами.

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

Дата, чтобы можно было ввести только дату.

Time (время ): Ограничьте ячейки, чтобы она принимала только время.

Длина текста, чтобы ограничить длину текста.

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

В списке Значение выберите условие:

больше или равно

меньше или равно

На вкладке Параметры в списке Тип данных выберите подходящий вариант:

Задайте остальные обязательные значения с учетом параметров Тип данных и Значение. Например, если выбрать значение между, выберите минимум: и максимум: значения в ячейках (ах).

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

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

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

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

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

• Какую версию вы используете?
• Более новые версии
• Office 2011

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

Выделите ячейки, для которых нужно ограничить ввод данных.

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

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

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

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

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

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

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

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

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

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

В поле название введите название сообщения.

В поле введите сообщение, которое вы хотите отобразить.

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

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

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

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

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

На вкладке сообщение об ошибке в поле название введите название сообщения.

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

Выполните одно из следующих действий:

На вкладке Style (стиль ) всплывающее меню выберите

Требовать от пользователей исправить ошибку перед продолжением

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

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

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

Выделите ячейки, для которых нужно ограничить ввод данных.

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

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

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

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

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

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

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

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

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

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

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

В поле название введите название сообщения.

В поле введите сообщение, которое вы хотите отобразить.

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

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

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

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

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

На вкладке сообщение об ошибке в поле название введите название сообщения.

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

Выполните одно из следующих действий:

На вкладке Style (стиль ) всплывающее меню выберите

Требовать от пользователей исправить ошибку перед продолжением

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выполните действия, описанные в 1-2 выше.

В списке Разрешить выберите значение Целое число.

В поле Данные выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Диапазон.

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

Можно также ввести формулу, которая возвращает числовое значение.

Например, допустим, что вы проверяете значения в ячейке F1. Чтобы задать минимальный объем вычетов, равный значению этой ячейки, умноженному на 2, выберите пункт Больше или равно в поле Данные и введите формулу =2*F1 в поле Минимальное значение.

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

Выполните действия, описанные в 1-2 выше.

В поле Разрешить выберите значение Десятичный.

В поле Данные выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Диапазон.

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

Можно также ввести формулу, которая возвращает числовое значение. Например, для задания максимального значения комиссионных и премиальных в размере 6% от заработной платы продавца в ячейке E1 выберите пункт Меньше или равно в поле Данные и введите формулу =E1*6% в поле Максимальное значение.

Примечание: Чтобы предоставить пользователю возможность вводить процентные значения, например 20%, в поле Разрешить выберите пункт десятичные , выберите необходимый тип ограничения в поле данные , введите минимальное, максимальное или конкретное значение в десятичном формате, например . 2, а затем Выбери ячейку для проверки данных в процентах, выделяя ячейку и выбирая процентный формат в группе число на вкладке Главная .

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

Выполните действия, описанные в 1-2 выше.

В поле Разрешить выберите значение Дата.

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

Введите начальную, конечную или определенную разрешенную дату.

Вы также можете ввести формулу, которая возвращает дату. Например, чтобы задать интервал времени между текущей датой и датой через 3 дня после текущей, выберите пункт Между в поле Данные, потом введите =СЕГОДНЯ() в поле Дата начала и затем введите =СЕГОДНЯ()+3 в поле Дата завершения.

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

Выполните действия, описанные в 1-2 выше.

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

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

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

Например, если в ячейке E2 задано время начала (8:00), а в ячейке F2 — время окончания (17:00) и вы хотите ограничить собрания этим промежутком, выберите между в поле Данные, а затем введите =E2 в поле Время начала и =F2 в поле Время окончания.

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

Выполните действия, описанные в 1-2 выше.

В поле Разрешить выберите значение Длина текста.

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

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

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

Выполните действия, описанные в 1-2 выше.

В поле Разрешить выберите необходимый тип данных.

В поле Данные выберите необходимый тип ограничения.

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

Например, чтобы допустить ввод сведений для счета только тогда, когда итог не превышает бюджет в ячейке E1, выберите значение Число десятичных знаков в списке Разрешить, ограничение «Меньше или равно» в списке «Данные», а в поле Максимальное значение введите >= =E1.

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

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

Значение в ячейке, содержащей код продукта (C2), всегда начинается со стандартного префикса «ID-» и имеет длину не менее 10 (более 9) знаков.

= И (LEFT (C2; 3) = «ID-«; ДЛСТР (C2) >9)

Ячейки, содержащие название товара (D2), содержат только текст.

Значение в ячейке, содержащей чью-то дату рождения (B6), было больше числа лет, указанного в ячейке B4.

Примечание: Необходимо сначала ввести формулу проверки данных в ячейку A2, а затем скопировать эту ячейку в ячейки A3:A10 так, чтобы второй аргумент СЧЁТЕСЛИ соответствовал текущей ячейке. Часть A2)=1 изменится на A3)=1, A4)=1 и т. д.

Адрес электронной почты в ячейке B4 содержал символ @.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Excel набор значений которые могут быть введены в ячейку ограничен

Создание раскрывающегося списка

​Смотрите также​ Is Nothing And​ список.​Сформируем именованный диапазон. Путь:​ Можно выбрать конкретное.​ ячейку, ограничен. Как​

​ данных, то можно​ принимать значение ИСТИНА,​

1. ​ нельзя впрямую указать​ данных и ввести​ или ссылку на​ Числовым эквивалентом для​Применение проверки данных к​На вкладке​выберите пункт​ запретить пользователям его​Игнорировать пустые ячейки​Чтобы упростить работу пользователей​ Target.Cells.Count = 1​Открываем параметры проверки данных.​​ «Формулы» — «Диспетчер​​Очень удобный инструмент Excel​

   ​ исправить?​​ использовать инструмент Выделение​

   • ​ для других строк​ ссылку на диапазоны​ формулу. Вторую формулу​ зависящую от нее​ 23:59:59 будет 0,99999.​ ячейкам​Параметры​Список​ просмотр и изменение,​.​

   • ​ с листом, добавьте​ Then Application.EnableEvents =​ В поле «Источник»​ имен» — «Создать».​ для проверки введенных​

2. ​Евгений токарев​ группы ячеек (Главная/​ — ЛОЖЬ вне​

3. ​ другого листа, например,​​ можно использовать для​​ ячейку. Например,​​Длина текста​​Функции Excel (по категориям)​

   ​в поле​​.​​ скройте и защитите​​Установите флажок​ в ячейки раскрывающиеся​ False If Len(Target.Offset(1,​ вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).​ Вводим уникальное название​ данных. Повысить комфорт​: Встаньте в ячейке,​ Найти и выделить/​

4. ​ зависимости от содержания​​ так =Лист2!$A$1. Позволяют​​ всего столбца​​Чтобы​​. В ячейку разрешен​​При вводе большого количества​​Разрешить​

5. ​Щелкните поле​​ этот лист. Подробнее​​Список допустимых значений​ списки, в которых​ 0)) = 0​Имя файла, из которого​ диапазона – ОК.​ работы с данными​ затем Меню -​ Выделение группы ячеек).​ ячейки.​ обойти это ограничение​А​Введите формулу​

6. ​ ввод только определенного​ информации в ячейки​​выберите пункт​​Источник​

7. ​ о защите листов​​Откройте вкладку​

8. ​ можно выбрать нужный​​ Then Target.Offset(1, 0)​​ берется информация для​

   • ​Создаем раскрывающийся список в​ позволяют возможности выпадающих​ Данные — Проверка.​Опция Проверка данных этого​​Теперь выделим ячейку​ использование Имен.​​, для этого необходимо​Пояснение​ количества символов. При​ таблицы легко допустить​Список​и выделите на​ см. в статье​

9. ​Подсказка по вводу​​ элемент.​​ = Target Else​

   • ​ списка, заключено в​ любой ячейке. Как​ списков: подстановка данных,​ Тип данных -​ инструмента позволяет выделить​​А2​​Если в Проверке данных нужно​ выделить не диапазон,​​Ячейка​​ этом ограничении можно​ ошибку. В EXCEL​.​ листе список допустимых​ Блокировка ячеек.​

10. ​.​Windows macOS Online​​ Target.End(xlDown).Offset(1, 0) =​​ квадратные скобки. Этот​

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

    • ​Если вы хотите, чтобы​ ​ Target End If​ файл должен быть​​ известно. Источник –​​ листа или файла,​

      ​Вообще, удобная штука​​ проводится проверка допустимости​CTRL+D​ на ячейку​А​содержала только текст​ и даты, главное,​ проверки введенных данных​ таблицу с элементами​Диалоговое окно свернется, чтобы​

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

​ элементы раскрывающегося списка,​ при выборе ячейки​На новом листе введите​ Target.ClearContents Application.EnableEvents =​ открыт. Если книга​ имя диапазона: =деревья.​ наличие функции поиска​

​ на самом деле.​ данных (заданная с​. Значение из​А1​Значение в ячейке, содержащей​=ЕТЕКСТ(B2)​ чтобы количество введенных​ сразу после нажатия​ раскрывающегося списка, щелкните​ было видно весь​ см. статью Добавление​

​ появлялось всплывающее сообщение,​ данные, которые должны​ True End If​ с нужными значениями​Снимаем галочки на вкладках​

​ и зависимости.​ Можно делать списки​ помощью команды Данные/​

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

​А1​другого листа, то​ имя кода продукта​В Типе данных нет​ символов не противоречило​ клавиши ENTER –​ поле​ лист.​ и удаление элементов​ установите флажок​

​ отображаться в раскрывающемся​ End Sub​ находится в другой​ «Сообщение для ввода»,​Путь: меню «Данные» -​

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

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

 

Вопрос от подписчика: poltorak

Всем доброго дня!
Что я уже сделал: Есть две разные ячейки. В 1-й ставиться постоянная дата. А  2-я загорается красным, при наступлении 30-го дня от постоянной даты.
И если ввести в эту 2-ю ячейку слово «продлено».То ячейка становиться белого цвета с текстом продлено.
Сделал формулу: =И(СЕГОДНЯ()-$A$2>=30;$F2<>»продлено»).
Потом я добавил 2-ю формулу : =ЕПУСТО($A$2). Для того чтобы когда 1-я ячейка пустая, то 2-я не загорается красным.
Нужно : чтобы во 2-ю ячейку нельзя было ввести  «продлено» раньше 30 дней(т.е. блокировало ввод).Но когда наступал срок, начинало работать моё 1-е правило.

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

Ответ на вопрос:

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

Макрос защиты нужных ячеек приведен ниже:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) ‘макрос запускается при изменении выделенного диапазона(при клике на новую ячейку)
Dim a As String
ActiveSheet.Unprotect ‘снимаем защиту с листа
a = «Взято с RuExcel.ru»
On Error Resume Next ‘обработка ошибок, на всякий случай
If Not Intersect(Target, Range(«B:B»)) Is Nothing Then ‘прверяем, чтобы срабатывапло только при клике на диапазон B:B
If Cells(ActiveCell.Row, (ActiveCell.Column — 1)).Value < DateAdd(«d», -30, Now) Then ‘если дата соответствует снимает защиту с активной ячейки
Range(ActiveCell, ActiveCell).Locked = False
Else
Range(ActiveCell, ActiveCell).Locked = True
End If

Else: Exit Sub ‘если кликнуть мимо диапазона В:В — выход из макроса
End If
ActiveSheet.Protect ‘защищаем лист
MsgBox (a) ‘выводим сообщение
End Sub

 

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

Скачать наши примеры

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

1. Выберите ячейки, для которых вы хотите создать правило.

2. Выберите Данные> Проверка данных .

   

3. На вкладке Настройки в разделе Разрешить выберите один из вариантов:

   • Whole Number — чтобы ограничить ячейку принимать только целые числа.

   • Десятичное число — чтобы ограничить ячейку приемом только десятичных чисел.

   • Список — выбор данных из выпадающего списка.

   • Дата — для ограничения ячейки принимать только дату.

   • Время — чтобы ограничить ячейку принимать только время.

   • Длина текста — для ограничения длины текста.

   • Custom — для нестандартной формулы.

4. В поле Данные выберите условие:

   • между

   • не между

   • равно

   • не равно

   • больше, чем

   • менее

   • больше или равно

   • меньше или равно

5. На вкладке Настройки в разделе Разрешить выберите один из вариантов:

6. Установите другие обязательные значения в зависимости от того, что вы выбрали для Разрешить и Данные .Например, если вы выберете между , то выберите Минимум : и Максимум : значений для ячеек.

7. Установите флажок Игнорировать пустые , если вы хотите игнорировать пробелы.

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

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

10. Выберите ОК .

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

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

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

1. Выберите ячейки, в которых вы хотите ограничить ввод данных.

2. На вкладке Данные щелкните Проверка данных > Проверка данных .

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

3. В поле Разрешить выберите тип данных, которые вы хотите разрешить, и заполните критерии и значения ограничения.

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

Запрашивать у пользователей действительные записи

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

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

2. На вкладке Данные щелкните Проверка данных > Проверка данных .

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

3. На вкладке Входное сообщение установите флажок Показать входное сообщение , если выбрана ячейка .

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

5. В поле Входное сообщение введите сообщение, которое вы хотите отобразить.

Показать сообщение об ошибке при вводе неверных данных

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

1. Выберите ячейки, в которых вы хотите отобразить сообщение об ошибке.

2. На вкладке Данные щелкните Проверка данных > Проверка данных .

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

3. На вкладке Alert Error Alert в поле Title введите заголовок своего сообщения.

4. В поле Сообщение об ошибке введите сообщение, которое вы хотите отобразить, если введены неверные данные.

5. Выполните одно из следующих действий:

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

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

1. Выберите ячейки, в которых вы хотите ограничить ввод данных.

2. На вкладке Данные в разделе Инструменты щелкните Проверить .

   

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

3. Во всплывающем меню «Разрешить » выберите тип данных, которые вы хотите разрешить.

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

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

Запрашивать у пользователей действительные записи

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

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

2. На вкладке Данные в разделе Инструменты щелкните Проверить .

   

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

3. На вкладке Входное сообщение установите флажок Показать входное сообщение , если выбрана ячейка .

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

5. В поле Входное сообщение введите сообщение, которое вы хотите отобразить.

Показать сообщение об ошибке при вводе неверных данных

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

1. Выберите ячейки, в которых вы хотите отобразить сообщение об ошибке.

2. На вкладке Данные в разделе Инструменты щелкните Проверить .

   

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

3. На вкладке Alert Error Alert в поле Title введите заголовок своего сообщения.

4. В поле Сообщение об ошибке введите сообщение, которое вы хотите отобразить, если введены неверные данные.

5. Выполните одно из следующих действий:

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

,

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

Скачать наши примеры

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

1. Выберите ячейки, для которых вы хотите создать правило.

2. Выберите Данные> Проверка данных .

   

3. На вкладке Настройки в разделе Разрешить выберите один из вариантов:

   • Whole Number — чтобы ограничить ячейку принимать только целые числа.

   • Десятичное число — чтобы ограничить ячейку приемом только десятичных чисел.

   • Список — выбор данных из выпадающего списка.

   • Дата — для ограничения ячейки принимать только дату.

   • Время — чтобы ограничить ячейку принимать только время.

   • Длина текста — для ограничения длины текста.

   • Custom — для нестандартной формулы.

4. В поле Данные выберите условие:

   • между

   • не между

   • равно

   • не равно

   • больше, чем

   • менее

   • больше или равно

   • меньше или равно

5. На вкладке Настройки в разделе Разрешить выберите один из вариантов:

6. Установите другие обязательные значения в зависимости от того, что вы выбрали для Разрешить и Данные .Например, если вы выберете между , то выберите Минимум : и Максимум : значений для ячеек.

7. Установите флажок Игнорировать пустые , если вы хотите игнорировать пробелы.

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

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

10. Выберите ОК .

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

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

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

1. Выберите ячейки, в которых вы хотите ограничить ввод данных.

2. На вкладке Данные щелкните Проверка данных > Проверка данных .

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

3. В поле Разрешить выберите тип данных, которые вы хотите разрешить, и заполните критерии и значения ограничения.

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

Запрашивать у пользователей действительные записи

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

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

2. На вкладке Данные щелкните Проверка данных > Проверка данных .

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

3. На вкладке Входное сообщение установите флажок Показать входное сообщение , если выбрана ячейка .

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

5. В поле Входное сообщение введите сообщение, которое вы хотите отобразить.

Показать сообщение об ошибке при вводе неверных данных

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

1. Выберите ячейки, в которых вы хотите отобразить сообщение об ошибке.

2. На вкладке Данные щелкните Проверка данных > Проверка данных .

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

3. На вкладке Alert Error Alert в поле Title введите заголовок своего сообщения.

4. В поле Сообщение об ошибке введите сообщение, которое вы хотите отобразить, если введены неверные данные.

5. Выполните одно из следующих действий:

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

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

1. Выберите ячейки, в которых вы хотите ограничить ввод данных.

2. На вкладке Данные в разделе Инструменты щелкните Проверить .

   

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

3. Во всплывающем меню «Разрешить » выберите тип данных, которые вы хотите разрешить.

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

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

Запрашивать у пользователей действительные записи

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

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

2. На вкладке Данные в разделе Инструменты щелкните Проверить .

   

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

3. На вкладке Входное сообщение установите флажок Показать входное сообщение , если выбрана ячейка .

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

5. В поле Входное сообщение введите сообщение, которое вы хотите отобразить.

Показать сообщение об ошибке при вводе неверных данных

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

1. Выберите ячейки, в которых вы хотите отобразить сообщение об ошибке.

2. На вкладке Данные в разделе Инструменты щелкните Проверить .

   

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

3. На вкладке Alert Error Alert в поле Title введите заголовок своего сообщения.

4. В поле Сообщение об ошибке введите сообщение, которое вы хотите отобразить, если введены неверные данные.

5. Выполните одно из следующих действий:

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

,

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

Скачать наши примеры

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

1. Выберите ячейки, для которых вы хотите создать правило.

2. Выберите Данные> Проверка данных .

   

3. На вкладке Настройки в разделе Разрешить выберите один из вариантов:

   • Whole Number — чтобы ограничить ячейку принимать только целые числа.

   • Десятичное число — чтобы ограничить ячейку приемом только десятичных чисел.

   • Список — выбор данных из выпадающего списка.

   • Дата — для ограничения ячейки принимать только дату.

   • Время — чтобы ограничить ячейку принимать только время.

   • Длина текста — для ограничения длины текста.

   • Custom — для нестандартной формулы.

4. В поле Данные выберите условие:

   • между

   • не между

   • равно

   • не равно

   • больше, чем

   • менее

   • больше или равно

   • меньше или равно

5. На вкладке Настройки в разделе Разрешить выберите один из вариантов:

6. Установите другие обязательные значения в зависимости от того, что вы выбрали для Разрешить и Данные .Например, если вы выберете между , то выберите Минимум : и Максимум : значений для ячеек.

7. Установите флажок Игнорировать пустые , если вы хотите игнорировать пробелы.

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

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

10. Выберите ОК .

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

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

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

1. Выберите ячейки, в которых вы хотите ограничить ввод данных.

2. На вкладке Данные щелкните Проверка данных > Проверка данных .

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

3. В поле Разрешить выберите тип данных, которые вы хотите разрешить, и заполните критерии и значения ограничения.

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

Запрашивать у пользователей действительные записи

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

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

2. На вкладке Данные щелкните Проверка данных > Проверка данных .

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

3. На вкладке Входное сообщение установите флажок Показать входное сообщение , если выбрана ячейка .

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

5. В поле Входное сообщение введите сообщение, которое вы хотите отобразить.

Показать сообщение об ошибке при вводе неверных данных

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

1. Выберите ячейки, в которых вы хотите отобразить сообщение об ошибке.

2. На вкладке Данные щелкните Проверка данных > Проверка данных .

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

3. На вкладке Alert Error Alert в поле Title введите заголовок своего сообщения.

4. В поле Сообщение об ошибке введите сообщение, которое вы хотите отобразить, если введены неверные данные.

5. Выполните одно из следующих действий:

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

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

1. Выберите ячейки, в которых вы хотите ограничить ввод данных.

2. На вкладке Данные в разделе Инструменты щелкните Проверить .

   

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

3. Во всплывающем меню «Разрешить » выберите тип данных, которые вы хотите разрешить.

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

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

Запрашивать у пользователей действительные записи

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

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

2. На вкладке Данные в разделе Инструменты щелкните Проверить .

   

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

3. На вкладке Входное сообщение установите флажок Показать входное сообщение , если выбрана ячейка .

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

5. В поле Входное сообщение введите сообщение, которое вы хотите отобразить.

Показать сообщение об ошибке при вводе неверных данных

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

1. Выберите ячейки, в которых вы хотите отобразить сообщение об ошибке.

2. На вкладке Данные в разделе Инструменты щелкните Проверить .

   

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

3. На вкладке Alert Error Alert в поле Title введите заголовок своего сообщения.

4. В поле Сообщение об ошибке введите сообщение, которое вы хотите отобразить, если введены неверные данные.

5. Выполните одно из следующих действий:

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

,

Подробнее о проверке данных — Excel

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

Когда проверка данных полезна?

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

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

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

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

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

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

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

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

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

Ваше входное сообщение было настроено на второй вкладке проверки данных.

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

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

Вы можете выбрать один из трех типов предупреждений об ошибках:

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

Советы по работе с проверкой данных

Используйте эти советы и рекомендации для работы с проверкой данных в Excel.

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

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

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

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

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

  

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

  

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

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

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

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

• Если проверка данных не работает, убедитесь, что:

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

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

  • Формулы не содержат ошибок — Убедитесь, что формулы в проверенных ячейках не вызывают ошибок, например #REF! или # DIV / 0 !.Excel игнорирует проверку данных, пока вы не исправите ошибку.

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

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

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

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

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

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

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

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

Опубликовать вопрос на форуме сообщества Excel

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

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

,

No related posts.