Трюк №9. Блокировка и защита ячеек Excel, содержащих формулы

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

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

Выберите все ячейки на листе, нажав сочетание клавиш Ctrl/Apple+A или щелкнув кнопку выделения листа (серый квадрат в месте пересечения столбца А и строки 1). Затем выберите команду Формат → Ячейки → Защита (Format → Cells → Protection) и сбросьте флажок Защищаемая ячейка (Locked). Щелкните кнопку ОК.

Теперь выделите любую ячейку, выберите команду Правка → Перейти (Edit → Go To) (Ctrl+G или F5) и щелкните кнопку Выделить (Special). Вы увидите диалоговое окно выделения группы ячеек (рис. 1.13).

Рис. 1.13. Диалоговое окно Выделение группы ячеек

В диалоговом окне Go To Special (Выделение группы ячеек) выберите переключатель Формулы (Formulas) и, если необходимо, выберите определенные типы формул, установив соответствующие флажки. Щелкните на кнопке ОК. Будут выделены только ячейки с формулами. Выберите команду Формат → Ячейки → Защита (Format → Cells → Protection) и установите флажок Защищаемая ячейка (Locked). Щелкните на кнопке ОК. Теперь выберите команду Сервис → Защита → Защитить лист (Tools → Protection → Protect Worksheet), чтобы защитить лист и, если необходимо, назначить пароль.

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

Первый подход совершенно не использует защиту листа, зато применяет проверку правильности данных.
[stextbox id=»warning»]Проверка правильности данных далека от идеала, когда дело доходит до предотвращения помещения в ячейки непроверенных данных. Пользователи легко могут вставлять любые скопированные данные в проверенную ячейку и, делая это, удалять пометку о проверке из этой ячейки. Если копируемая ячейка содержит пометку о проверке, то в этом случае новая пометка перекроет исходную.[/stextbox]
Чтобы увидеть, о чем мы говорим, выделите любую ячейку, выберите команду Правка → Перейти (Edit → Go To) (Ctrl+G или F5) и щелкните кнопку Выделить (Special). В диалоговом окне Выделение группы ячеек (Go To Special) установите переключатель Формулы (Formulas) и, если необходимо, укажите только отдельные типы формул. Щелкните на кнопке ОК.

Теперь, когда выбраны только ячейки с формулами, перейдите на вкладку Данные → Проверка → Параметры (Data → Validation → Settings), в поле Тип данных (Allow) выберите Другой (Custom), а в поле Формула (Formula) введите =»» (рис. 1.14). Щелкните на кнопке ОК.

Рис. 1.14. Формулы проверки

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

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

1
2
3
4
5
6
7
8

// Листинг 1.17
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
	If Target.Locked = True Then
		Me.Protect Password:="Secret"
	Else
		Me.Unprotect Password:="Secret"
	End If
End Sub

Если вы не хотите использовать пароль, уберите строку Password:=«Secret». Если вы указали пароль, измените слово Secret на ваш пароль. Нажмите сочетание клавиш Alt/Apple+Q или щелкните крестик в правом верхнем углу окна, чтобы вернуться в Excel и сохранить книгу. Теперь каждый раз, когда вы будете выбирать заблокированную ячейку, ваш лист будет автоматически защищать себя. Как только вы выберете незаблокированную ячейку, лист автоматически снимет с себя защиту.
[stextbox id=»alert»]Этот трюк работает не идеально, хотя обычно вполне сносно. Ключевое слово, используемое в коде, Target, относится только к ячейке, активной на время выбора. Поэтому важно помнить, что, если пользователь выберет диапазон ячеек (где активные ячейки будут не заблокированы), он сможет удалить весь выделенный диапазон, так как целевая ячейка не заблокирована и, следовательно, лист снимает с себя защиту.[/stextbox]