© Сорокин Б.Б. 2016 г.

Защита ячеек

Пару дней назад получил вопрос.

"Задача поставлена так, в ячейку забиваю число "1" и блокируется строка и данные забитые в строке изменить нельзя, а если в ячейку забиваю число "0" то в строке можно работать и изменять данные. Как это можно сделать?"

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

Рассмотрим сначала, как в принципе создать такую защиту.

Я для этого использую стандартную процедуру проверки вводимых значений. Стоя на определенной ячейке выбрать пункт меню Данные → Проверка, откроется окно "Проверка вводимых значений". На вкладке параметры установить условия проверки согласно рисунка.

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

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

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

Чтобы управлять этой защитой необходимо найти способ изменения значения параметра "Максимум"=0. Это можно сделать при помощи функции ЕСЛИ(), образец на рисунке ниже.

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

Логика формулы следующая.

=ЕСЛИ(B1=1;0;257)

Если значение ячейки B1 равно "1" то формула возвращает "0", т.е. длина текста задается меньше нуля - ячейка A1 заблокирована для ввода. Если значение ячейки B1 не равно "1" т.е имеет любое другое значение в том числе "0" и "Пусто", то формула возвращает "257", т.е. длина текста вводимого в ячейку A1 меньше 257 знаков и ячейка разблокируется. Проверяем.

блокировка ячейки

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

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

специальная вставка

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

Архив с файлом примера можно скачать.

P.S. Кстати, этот вопрос напомнил мне одну старинную тему разработки еще 2006 года - управление списками.