Защита ячейки в Excel от ввода некорректных данных
Файлы Microsoft Excel довольно часто используются для хранения данных, с которыми работает несколько различных пользователей. Иногда пользователи ошибаются и вводят некорректные данные, из за чего возникают проблемы различной степени сложности, в зависимости от важности данных. Однако подобной ситуации можно легко избежать.
Специально на такой случай в Excel есть возможность проверять корректность данных, записываемых в определенные ячейки. При вводе недопустимых данных Excel будет выдавать предупреждение или запрет на продолжение операции.
Для включения проверки надо выделить защищаемые ячейки, затем перейти на вкладку «Данные» и выбрать пункт «Проверка данных».
В открывшемся окне на вкладке «Параметры» сначала выбираем тип данных, которые могут быть записаны в выбранные ячейки, а затем задаем ограничения для выбранного типа данных. Вариантов довольно много, например, в качестве ограничения можно указать определенный диапазон чисел, промежуток времени\даты, текст определенной длины или указать файл со списком допустимых значений. Также возможно задать формулу для вычисления допустимых значений на основании содержимого других ячеек.
В качестве примера укажем любое целое число не больше 100000.
На вкладке «Сообщение об ошибке» выбираем действие, которое должно произойти при неверном вводе. Выбрать можно один из трех вариантов:
• Останов — останавливает операцию, предотвращая ввод некорректных данных;
• Предупреждение — при вводе некорректных данных выдается предупреждение, но пользователю дается возможность продолжить операцию;
• Сообщение — пользователь уведомляется о том, что введенные данные недопустимы, однако ввод данных разрешен.
Также здесь можно добавить заголовок и текст сообщения, который увидит пользователь при вводе неправильного значения. Это должно помочь пользователю понять, что он делает не так.
А вот так выглядит сообщение, выдаваемое при попытке ввода данных, не подходящих под заданное условие.
В качестве дополнительной помощи на вкладке «Сообщение для ввода» есть возможность оставить подсказку.
Эта подсказка будет появляться рядом с ячейкой при ее выборе и оставаться на экране до тех пор, пока вы не перейдете к другой ячейке.
И если уж так случилось, что пользователям все таки удалось ″накосячить″, есть возможность выделить неправильно введенные данные. Сделать это можно, выбрав в меню «Проверка данных» пункт «Обвести неверные данные».
Подобные несложные действия облегчат жизнь пользователям и помогут избежать многих проблем при совместной работе с данными в excel.
Как установить ограничение на ввод данных в ячейку в excel
Сообщений: 525 Регистрация: 01.01.1970
18.06.2010 16:21:42
Доброго времени суток, форумчане! Вопрос такого плана: необходимо ограничить ввод цифр в ячейке,т.е. вводимые числа должны быть в строгом диапазоне от 1 до 50.Условное форматирование не ограничивает это, в нем только можно подкрасить ячейку превышающую диапазон! а необходимо чтоб кроме чисел «в диапазоне» ничего не вводилось». это возможно реализовать?
Как ограничить ввод в ячейку?
Всем добрый день. У меня появилась такая проблема. Мне нужно чтобы в ячейку можно было вводить только цифры и одну запятую. Пробовал через формулы, но так и ничего не получилось, а если пробовать через макрос, то можно макрос отключить, и тогда пользователь может ввести, что ему захочется. Прошу, помогите.
Будет очень хорошо, если всё будет через формулы реализовано с использованием метода проверки данных в ячейке.
То есть шаблон содержимого ячеек должен быть только таким (*,*) это значит ввод хоть сколько цифр и только одной запятой, равно и прочее недопустимо.
Мне это нужно для того чтобы человек смог только ввести данные в числовом формате с использованием только одной запятой если он хочет разделить до копеек и всё. Данный метод распространяется ну на одну ячейку, а на несколько.
Прошу помогите.
- Вопрос задан более трёх лет назад
- 1896 просмотров
Комментировать
Решения вопроса 1
Денис Машанов @LoneRay Автор вопроса
Начинающий кодировщик.
Сделал как и говорил 2 пункте DS28.
Установить для листа.
Private Sub Worksheet_Calculate()
ActiveSheet.Range(Target.Address).NumberFormat = «@»
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range(«B4:G54»)) Is Nothing Then
Application.EnableEvents = False
If Not ActiveSheet.Range(Target.Address).NumberFormat = «@» Then
MsgBox «Необходимо использовать формат ячейки [Текстовый]», 48, «Ошибка»
Target = «»
Else
If Not IsNumeric(Target) And ActiveSheet.Range(Target.Address).NumberFormat = «@» Then
MsgBox «Разрешается ввод используя шаблон [1234,1234]», 48, «Ошибка»
Target = «»
End If
End If
End If
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Range(Target.Address).NumberFormat = «@»
End Sub
Применение проверки данных к ячейкам
Используйте проверку данных, чтобы ограничить тип данных или значения, которые пользователи вводит в ячейку, например раскрывающийся список.
Проверьте, как это работает!
- Выделите ячейки, для которых необходимо создать правило.
- Выберите Data >Data Validation (Проверка данных >).
- На вкладке Параметры в разделе Разрешить выберите параметр:
- Целое число — ограничивает ячейку только целыми числами.
- Decimal — ограничивает ячейку только десятичными числами.
- Список — для выбора данных из раскрывающегося списка.
- Date — ограничивает ячейку только датой.
- Time — ограничивает ячейку только временем.
- Длина текста — для ограничения длины текста.
- Custom — для настраиваемой формулы.
- В разделе Данные выберите условие.
- Задайте другие обязательные значения в зависимости от того, что вы выбрали для параметра Разрешить и Данные.
- Перейдите на вкладку Входное сообщение и настройте сообщение, которое пользователи увидят при вводе данных.
- Установите флажок Показывать входное сообщение при выборе ячейки , чтобы отобразить сообщение, когда пользователь выбирает выбранные ячейки или наносит указатель мыши на нее.
- Перейдите на вкладку Оповещение об ошибке , чтобы настроить сообщение об ошибке и выбрать стиль.
- Нажмите кнопку ОК. Теперь, если пользователь попытается ввести недопустимое значение, появится предупреждение об ошибке с настроенным сообщением.
Скачивание примеров
Если вы создаете лист, на котором пользователи должны вводить данные, может потребоваться ограничить ввод определенным диапазоном дат или чисел либо разрешить ввод только положительных целых чисел. Excel может ограничить ввод данных определенными ячейками с помощью проверка данных, предложить пользователям ввести допустимые данные при выборе ячейки и отобразить сообщение об ошибке, когда пользователь вводит недопустимые данные.
Ограничение ввода данных
- Выделите ячейки, для которых нужно ограничить ввод данных.
- На вкладке Данные выберите Проверка данных >Проверка данных.
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.
Примечание: Поля, в которые вы вводите ограничивающие значения, помечаются на основе выбранных данных и условий ограничения. Например, если в качестве типа данных выбрана дата, можно ввести ограничивающие значения в полях с минимальными и максимальными значениями с метками «Дата начала » и «Дата окончания«.
Запрос для пользователей на ввод допустимых значений
Когда пользователи выбирают ячейку с требованиями к вводу данных, можно отобразить сообщение с описанием допустимых данных.
- Выделите ячейки, в которых для пользователей нужно отображать запрос на ввод допустимых данных.
- На вкладке Данные выберите Проверка данных >Проверка данных.
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.
Отображение сообщения об ошибке при вводе недопустимых данных
При наличии ограничений для данных и при вводе пользователем недопустимых данных в ячейку можно отображать сообщение об ошибке.
- Выберите ячейки, в которых нужно отображать сообщение об ошибке.
- На вкладке Данные щелкните Проверка данных >Проверка данных .
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в разделе Защита книги .
Добавление проверки данных в ячейку или диапазон ячеек
Примечание: Первые два действия, указанные в этом разделе, можно использовать для добавления любого типа проверки данных. Действия 3–7 относятся к созданию раскрывающегося списка.
- Выделите одну или несколько ячеек, к которым нужно применить проверку.
- На вкладке Данные в группе Средства обработки данных выберите Проверка данных.
- На вкладке Параметры в поле Разрешить выберите Список.
- В поле Источник введите значения списка, разделенные запятыми. Например, введите Low, Average, High.
- Убедитесь, что выбран раскрывающийся список В ячейке проверка. В противном случае вы не увидите стрелку раскрывающегося списка рядом с ячейкой.
- Чтобы указать способ обработки пустых (NULL) значений, установите или снимите флажок Игнорировать пустой проверка.
- Протестируйте проверку данных, чтобы убедиться, что они работают правильно. Попробуйте ввести в ячейку сначала допустимые, а потом недопустимые данные и убедитесь, что параметры проверки применяются, как вы хотите, а ваши сообщения появляются в нужный момент.
- После создания раскрывающегося списка убедитесь, что он работает так, как нужно. Например, можно проверить, достаточно ли ширины ячеек для отображения всех ваших записей.
- Удалить проверку данных . Выберите ячейку или ячейки, содержащие проверку, которую требуется удалить, перейдите в раздел Проверка данных > данные и в диалоговом окне проверки данных нажмите кнопку Очистить все, а затем нажмите кнопку ОК.
Использование других типов проверки данных
В таблице перечислены другие типы проверки данных и указано, как применить их к данным на листе.
Разрешить вводить только целые числа из определенного диапазона
- Выполните действия 1–2, указанные выше.
- В списке Разрешено выберите Целое число.
- В поле Данные выберите нужный тип ограничения. Например, чтобы задать верхний и нижний пределы, выберите между.
- Введите минимальное, максимальное или определенное разрешенное значение. Можно также ввести формулу, которая возвращает числовое значение. Например, допустим, что вы проверяете значения в ячейке F1. Чтобы установить минимальное ограничение на количество дочерних элементов в ячейке в два раза, выберите больше или равно в поле Данные и введите формулу =2*F1 в поле Минимум .
Разрешить вводить только десятичные числа из определенного диапазона
- Выполните действия 1–2, указанные выше.
- В поле Разрешить выберите Десятичный.
- В поле Данные выберите нужный тип ограничения. Например, чтобы задать верхний и нижний пределы, выберите между.
- Введите минимальное, максимальное или определенное разрешенное значение. Можно также ввести формулу, которая возвращает числовое значение. Например, чтобы установить максимальное ограничение для комиссий и бонусов в размере 6 % от заработной платы продавца в ячейке E1, выберите значение меньше или равно в поле Данные и введите формулу =E1*6% в поле Максимальное значение.
Примечание: Чтобы разрешить пользователю вводить процентные значения, например 20 %, выберите Десятичный в поле Разрешить , выберите тип ограничения, которое требуется в поле Данные , введите минимальное, максимальное или определенное значение в виде десятичного разряда, например .2 , а затем отобразите ячейку проверки данных в виде процента, выбрав ячейку и щелкнув процента в группе Число на вкладке Главная .
Разрешить вводить только даты в заданном интервале времени
- Выполните действия 1–2, указанные выше.
- В поле Разрешить выберите Дата .
- В поле Данные выберите нужный тип ограничения. Например, чтобы разрешить даты после определенного дня, выберите больше.
- Введите начальную, конечную или определенную разрешенную дату. Вы также можете ввести формулу, которая возвращает дату. Например, чтобы задать интервал времени между сегодняшней датой и 3 днями с сегодняшней даты, выберите между в поле Данные , введите =TODAY() в поле Дата начала и введите =TODAY()+3 в поле Дата окончания .
Разрешить вводить только время в заданном интервале
- Выполните действия 1–2, указанные выше.
- В поле Разрешить выберите Время.
- В поле Данные выберите нужный тип ограничения. Например, чтобы разрешить время до определенного времени суток, выберите меньше.
- Укажите время начала, окончания или определенное время, которое необходимо разрешить. Если вы хотите ввести точное время, используйте формат чч:мм. Например, предположим, что у вас есть ячейка E2, настроенная со временем начала (8:00) и ячейка F2 со временем окончания (17:00), и вы хотите ограничить время собрания между этим временем, затем выбрать между ними в поле Данные , введите =E2 в поле Время начала , а затем введите =F2 в поле Время окончания .
Разрешить вводить только текст определенной длины
- Выполните действия 1–2, указанные выше.
- В поле Разрешить выберите Длина текста.
- В поле Данные выберите нужный тип ограничения. Например, чтобы разрешить до определенного количества символов, выберите значение меньше или равно.
- В этом случае нам нужно ограничить ввод до 25 символов, поэтому выберите значение меньше или равно в поле Данные и введите значение 25 в поле Максимум .
Вычислять допустимое значение на основе содержимого другой ячейки
- Выполните действия 1–2, указанные выше.
- В поле Разрешить выберите нужный тип данных.
- В поле Данные выберите нужный тип ограничения.
- В поле или полях под полем Данные выберите ячейку, которую нужно использовать, чтобы указать, что разрешено. Например, чтобы разрешить записи для учетной записи, только если результат не превышает бюджет в ячейке E1, выберите Разрешить >целое число, Данные, меньше или равно, и Максимальное >= =E1.