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

Как защитить ячейки в Excel от редактирования (запрет ввода ошибочных данных)

Д оброго времени!

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

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

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

Собственно, об этом и будет сегодняшняя заметка. 👇

Примечание: все скрины в статье сделаны в MS Office 2019. Если у вас нет возможности использовать официальный продукт от Microsoft, можете воспользоваться его аналогами: https://ocomp.info/chem-zamenit-word-i-excel.html

Защищаем ячейки в Excel

ШАГ 1

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

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

ШАГ 2

Далее в меню «Защита» нужно снять галочку рядом с пунктом «Защищаемая ячейка». Нажать OK.

ШАГ 3

После перейти в меню «Рецензирование» и активировать инструмент «Защитить лист» . Меню Excel приведено на скрине ниже.

Далее укажите пароль и отметьте галочками, что пользователи могут делать с листом (можно оставить все по умолчанию).

ШАГ 4

Собственно, на этом всё!

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

ШАГ 5

Дополнительно Excel позволят сделать так, что ввести в ячейку можно будет только определенное значение. Например, число от 1 до 100! (дополнительная защита от «дурака»).

Покажу на простом примере как это делается.

Сначала нужно выделить строку/столбец (или просто ячейки), перейти в меню «Данные» и кликнуть по инструменту «Проверка данных» (как он выглядит — показано на скриншоте ниже).

Проверка данных на этапе ввода

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

Указание диапазона верных значений

После этого, когда поставите курсор в «нашу ячейку» Excel сразу же подскажет, что ввести можно от 1 до 100 (а если кто-то введет что-то отличное — выскочит ошибка 👇).

Как работает проверка

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

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

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

Иногда при работе с таблицами в редакторе 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 выпадающий в ячейке список сделать контекстным, например, зависящим от содержания ячейки, находящейся слева от ячейки со списком (рис. 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.

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

Защита ячеек в Excel от изменения, редактирования и ввода ошибочных данных

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

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

Защита выделенного диапазона от изменения

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

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

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

В следующем диалоговом окне переходим на вкладку «Защита» и снимаем галочку с пункта «Защищаемая ячейка» . Нажмите «ОК» .

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

После этого поставим ограничения для изменений. Для примера давайте запретим редактирование блоков, которые находятся в диапазоне B2:D7 . Выделяем указанный диапазон, кликаем по нему правой кнопкой мыши и выбираем из меню «Формат ячеек» . Дальше перейдите на вкладку «Защита» и поставьте галочку в поле «Защищаемая…» . Нажмите «ОК» .

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

После этого, любой пользователь сможет работать с информацией на странице. В примере введены пятерки в Е4 . Но при попытке изменить текст или числа в диапазоне В2:D7 , появится сообщение, что ячейки защищены.

Ставим пароль

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

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

Сделаем так, чтобы другие пользователи могли редактировать всё на листе, кроме диапазона B2:D7 . А Вы, зная пароль, могли редактировать и блоки в B2:D7 .

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

Теперь нужно выделить диапазон, для которого будет установлен пароль, в примере это B2:D7 . Потом опять зайдите «Формат ячеек» и поставьте галочку в поле «Защищаемая…» .

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

Затем переходим на вкладку «Рецензирование» и нажимаем кнопочку «Разрешить изменение диапазонов» . Откроется соответствующее диалоговое окно. Нажмите в нем кнопочку «Создать» .

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

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

Теперь нужно установить пароль для листа. На вкладке «Рецензирование» нажимаем кнопочку «Защитить лист» . Введите пароль и отметьте галочками, что можно делать пользователям. Нажмите «ОК» и подтвердите пароль.

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

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

Защищаем блоки от неверных данных

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

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

Выделяем нужный диапазон ячеек таблицы – С3:С7 , переходим на вкладку «Данные» и кликаем по кнопочке «Проверка данных» .

В следующем диалоговом окне на вкладке «Параметры» в поле «Тип…» выберите из списка «Целое число» . В поле «Минимум» введем «1» , в поле «Максимум» – «11» .

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

На вкладке «Сообщение об ошибке» введем сообщение, которое будет появляться, если пользователь попробует ввести неправильную информацию. Нажмите «ОК» .

Теперь если выделить что-то из диапазона С3:С7 , рядом будет высвечиваться подсказка. В примере при попытке написать в С6 «15» , появилось сообщение об ошибке, с тем текстом, который мы вводили.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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