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

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

  • автор:

КАК ЗАФИКСИРОВАТЬ ВВОД ДАННЫХ В ЯЧЕЙКУ EXCEL

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

Вот как вы можете зафиксировать ввод данных в ячейку Excel:

  1. Выберите ячейку или диапазон ячеек, которые вы хотите защитить.
  2. Щелкните правой кнопкой мыши на выбранной ячейке и выберите «Формат ячейки».
  3. В открывшемся окне «Формат ячейки» перейдите на вкладку «Защита».
  4. Установите флажок рядом с опцией «Заблокировать» для защиты ячейки или выбранного диапазона ячеек.
  5. После того как вы установили защиту для ячеек, необходимо применить защиту к листу Excel:
    • Выберите «Управление листами» в меню «Рецепты» на панели инструментов «Защита листа».
    • Установите флажок рядом с опцией «Защитить лист» и назначьте пароль для защиты листа, если это необходимо.
    • Нажмите «ОК», чтобы применить защиту к листу Excel.

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

Защита ячеек и листов в Microsoft Excel

КАК ЗАКРЕПИТЬ ОБЛАСТЬ В EXCEL

Умная таблица в Excel ➤ Секреты эффективной работы

Ввод данных в ячейку Excel

Видео №134. Excel. Закрепление ячейки в формуле. Оксана ЛУКИНА.

Видео №141. Excel. Закрепить рисунок в ячейке для сортировки. Оксана ЛУКИНА

Ограничение ввода данных в ячейки таблицы Excel по заданным критериям.

Михаил Захаров

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

Вам также может понравиться:

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

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

Браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

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

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

    Выберите Data >Data Validation (Проверка данных >).

  • Целое число — ограничивает ячейку только целыми числами.
  • Decimal — ограничивает ячейку только десятичными числами.
  • Список — для выбора данных из раскрывающегося списка.
  • Date — ограничивает ячейку только датой.
  • Time — ограничивает ячейку только временем.
  • Длина текста — для ограничения длины текста.
  • Custom — для настраиваемой формулы.

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

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

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

  1. Выделите ячейки, для которых нужно ограничить ввод данных.
  2. На вкладке Данные выберите Проверка данных >Проверка данных.

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

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

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

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

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

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

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

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

  1. Выберите ячейки, в которых нужно отображать сообщение об ошибке.
  2. На вкладке Данные щелкните Проверка данных >Проверка данных .

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

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

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

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

  1. Выделите одну или несколько ячеек, к которым нужно применить проверку.
  2. На вкладке Данные в группе Средства обработки данных выберите Проверка данных.
  3. На вкладке Параметры в поле Разрешить выберите Список.
  4. В поле Источник введите значения списка, разделенные запятыми. Например, введите Low, Average, High.
  5. Убедитесь, что выбран раскрывающийся список В ячейке проверка. В противном случае вы не увидите стрелку раскрывающегося списка рядом с ячейкой.
  6. Чтобы указать способ обработки пустых (NULL) значений, установите или снимите флажок Игнорировать пустой проверка.
  7. Протестируйте проверку данных, чтобы убедиться, что они работают правильно. Попробуйте ввести в ячейку сначала допустимые, а потом недопустимые данные и убедитесь, что параметры проверки применяются, как вы хотите, а ваши сообщения появляются в нужный момент.
  • После создания раскрывающегося списка убедитесь, что он работает так, как нужно. Например, можно проверить, достаточно ли ширины ячеек для отображения всех ваших записей.
  • Удалить проверку данных . Выберите ячейку или ячейки, содержащие проверку, которую требуется удалить, перейдите в раздел Проверка данных > данные и в диалоговом окне проверки данных нажмите кнопку Очистить все, а затем нажмите кнопку ОК.

Использование других типов проверки данных

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

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

  1. Выполните действия 1–2, указанные выше.
  2. В списке Разрешено выберите Целое число.
  3. В поле Данные выберите нужный тип ограничения. Например, чтобы задать верхний и нижний пределы, выберите между.
  4. Введите минимальное, максимальное или определенное разрешенное значение. Можно также ввести формулу, которая возвращает числовое значение. Например, допустим, что вы проверяете значения в ячейке F1. Чтобы установить минимальное ограничение на количество дочерних элементов в ячейке в два раза, выберите больше или равно в поле Данные и введите формулу =2*F1 в поле Минимум .

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

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

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

процента в группе Число на вкладке Главная .

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

  1. Выполните действия 1–2, указанные выше.
  2. В поле Разрешить выберите Дата .
  3. В поле Данные выберите нужный тип ограничения. Например, чтобы разрешить даты после определенного дня, выберите больше.
  4. Введите начальную, конечную или определенную разрешенную дату. Вы также можете ввести формулу, которая возвращает дату. Например, чтобы задать интервал времени между сегодняшней датой и 3 днями с сегодняшней даты, выберите между в поле Данные , введите =TODAY() в поле Дата начала и введите =TODAY()+3 в поле Дата окончания .

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

  1. Выполните действия 1–2, указанные выше.
  2. В поле Разрешить выберите Время.
  3. В поле Данные выберите нужный тип ограничения. Например, чтобы разрешить время до определенного времени суток, выберите меньше.
  4. Укажите время начала, окончания или определенное время, которое необходимо разрешить. Если вы хотите ввести точное время, используйте формат чч:мм. Например, предположим, что у вас есть ячейка E2, настроенная со временем начала (8:00) и ячейка F2 со временем окончания (17:00), и вы хотите ограничить время собрания между этим временем, затем выбрать между ними в поле Данные , введите =E2 в поле Время начала , а затем введите =F2 в поле Время окончания .

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

  1. Выполните действия 1–2, указанные выше.
  2. В поле Разрешить выберите Длина текста.
  3. В поле Данные выберите нужный тип ограничения. Например, чтобы разрешить до определенного количества символов, выберите значение меньше или равно.
  4. В этом случае нам нужно ограничить ввод до 25 символов, поэтому выберите значение меньше или равно в поле Данные и введите значение 25 в поле Максимум .

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

  1. Выполните действия 1–2, указанные выше.
  2. В поле Разрешить выберите нужный тип данных.
  3. В поле Данные выберите нужный тип ограничения.
  4. В поле или полях под полем Данные выберите ячейку, которую нужно использовать, чтобы указать, что разрешено. Например, чтобы разрешить записи для учетной записи, только если результат не превышает бюджет в ячейке E1, выберите Разрешить >целое число, Данные, меньше или равно, и Максимальное >= =E1.

Защита данных в Microsoft Excel

Всё о компьютерах

Инструкции

Автор fast12v0_ocomput На чтение 9 мин Просмотров 361 Опубликовано 15.05.2023

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

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

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

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

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

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

Собственно, это все!

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

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

Позвольте мне показать вам, как это сделать, на простом примере…

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

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

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

Примечание: разумеется, вместо моей подборки цифр — вы можете указать другие, или даже позволить себе ввести что-то другое…

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

Уровень 0. Защита от ввода некорректных данных в ячейку

Самый простой способ. Позволяет проверить, что именно пользователь вводит в определенные ячейки и не позволяет вводить неверные данные (например, отрицательную цену или дробное количество человек или дату Октябрьской революции вместо даты заключения договор и т.п.) Чтобы установить такую ​​проверку ввода, необходимо выделить ячейки и нажать кнопку Data Validation на вкладке Data. В Excel 2003 и более поздних версиях это можно было сделать с помощью меню Data — Check (Данные — Проверка). На вкладке Параметры вы можете выбрать тип данных, разрешенных для ввода, из выпадающего списка:

Соседние вкладки в этом окне позволяют (при желании) указать сообщения, которые должны отображаться перед вводом — вкладка Ввод сообщения, а в случае некорректного ввода информации — вкладка Оповещение об ошибке):

Уровень 1. Защита ячеек листа от изменений

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

  1. Выделите ячейки, которые не нужно защищать (если они есть), щелкните по ним правой кнопкой мыши и выберите команду «Формат ячеек» из контекстного меню. Снимите флажок «Заблокировано» на вкладке «Защита». Все ячейки, для которых этот флажок остается установленным, будут защищены при включении защиты листа. Все ячейки, в которых вы снимите этот флажок, будут доступны для редактирования, несмотря на защиту. Чтобы визуально увидеть, какие ячейки будут защищены, а какие нет, вы можете использовать этот макрос.
  2. Чтобы включить защиту текущего листа в Excel 2003 и более поздних версиях, выберите в меню Сервис — Защита — Защитить лист, а в Excel 2007 и более поздних версиях нажмите кнопку Защитить лист на вкладке Вид. В открывшемся диалоговом окне можно ввести пароль (он будет необходим, чтобы никто не смог снять защиту) и с помощью списка флажков настроить при желании исключения:

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

Уровень 2. Выборочная защита диапазонов для разных пользователей

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

Для этого нажмите кнопку Разрешить пользователям редактировать области на вкладке Обзор. В версии Excel 2003 и старше для этого есть команда в меню Сервис — Защита — Разрешить изменение диапазонов (Инструменты — Защита — Разрешить пользователям изменять диапазоны):

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

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

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

Уровень 3. Защита листов книги

Если вам нужно защитить себя от:

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

затем нужно защитить все листы в книге с помощью кнопки Protect Workbook на вкладке Review (Рецензирование) или — в старых версиях Excel — через меню Сервис — Защита — Защитить книгу (Tools — Protection — Protect Workbook):

Уровень 4. Шифрование файла

При необходимости Excel предоставляет возможность зашифровать весь файл книги с помощью нескольких различных алгоритмов шифрования семейства RC4. Данную защиту проще всего установить при сохранении книги, т.е выбрать команду Файл — Сохранить как (File — Save As), а затем в окне сохранения найти и развернуть выпадающий список Сервис — Общие параметры (Tools — General Options) . В появившемся окне можем ввести два разных пароля — для открытия файла (только для чтения) и для смены

Включение блокирования ячеек

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

Способ 1: включение блокировки через вкладку «Файл»

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

  1. Выделите весь лист, нажав на прямоугольник, расположенный на пересечении линий координат Excel. Щелкните правой кнопкой мыши. В появившемся контекстном меню перейдите к пункту «Форматировать ячейки…».
  2. Откроется окно редактирования формата ячейки. Перейдите на вкладку «Защита». Снимите флажок рядом с Защищенная ячейка. Нажмите на кнопку «ОК».
  3. Выберите область, которую хотите заблокировать. Вернитесь в окно «Формат ячеек…».
  4. Во вкладке «Защита» поставьте галочку напротив «Защищаемая ячейка». Нажмите на кнопку «ОК».Но дело в том, что после этого территория еще не охранялась. Это происходит только тогда, когда мы включаем защиту листа. Но при этом нельзя будет изменить только те ячейки, где мы установили галочки в соответствующем элементе, а те, где галочки сняли, останутся редактируемыми.
  5. Перейдите на вкладку «Файл».
  6. В разделе «Подробности» нажмите кнопку «Защитить книгу». В появившемся списке выберите пункт «Защитить текущий лист».
  7. Откроются настройки защиты листа. Рядом с параметром «Защитить лист и содержимое защищенных ячеек» должна стоять галочка. При желании вы можете заблокировать определенные действия, изменив настройки в опциях ниже. Но в большинстве случаев настройки по умолчанию удовлетворяют потребности пользователей в блокировке сайтов. В поле «Пароль для отключения защиты листа» введите любое ключевое слово, которое будет использоваться для доступа к возможностям редактирования. После завершения настроек нажмите кнопку «ОК».
  8. Откроется другое окно, где необходимо повторить пароль. Это сделано для того, чтобы пользователь, впервые введший неверный пароль, не заблокировал себе навсегда доступ к редактированию. После ввода ключа нажмите кнопку «ОК». Если пароли совпадают, блокировка будет завершена. Если они не совпадают, необходимо ввести снова.

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

Способ 2: включение блокировки через вкладку «Рецензирование»

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

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

Разблокировка диапазона

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

  1. Перейдите на вкладку «Обзор».
  2. На ленте в группе инструментов «Изменения» нажмите кнопку «Снять защиту с листа».
  3. Появится окно, в котором необходимо ввести установленный ранее пароль. После входа нажмите на кнопку «ОК».

После этих действий защита со всех ячеек будет снята.

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

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

Предположим, вам нужно ограничить ввод значений в ячейки на основе другого значения ячейки, например, в ячейки в диапазоне B1: E10 разрешено вводить данные, если значение ячейки A1 равно Да, но ограничить ввод данных, если в ячейке A1 существует другое значение. Как этого добиться? Пожалуйста, попробуйте метод, описанный в этой статье.

Ограничить ввод значения в ячейку на основе другого значения ячейки в Excel

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

1. Выберите диапазон B1: E10, затем щелкните Данные > проверка достоверности данных. Смотрите скриншот:

2. в проверка достоверности данных диалогового окна, перейдите к Настройки вкладка, выберите На заказ в Разрешить раскрывающийся список, введите формулу = $ A $ 1 = «да» в Формула поле, затем снимите флажок Игнорировать пустой коробка. И, наконец, нажмите кнопку OK кнопка. Смотрите скриншот:

Внимание: Вы можете изменить формулу в зависимости от ваших потребностей.

С этого момента, когда значение ячейки A1 равно «Да», указанные ячейки диапазона могут вводить данные. Смотрите скриншот:

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

Статьи по теме:
  • Как ограничить доступ к определенному листу в Excel?
  • Как ограничить вставку только значений (запретить форматирование) в Excel?

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *