Частичная защита листа Excel

Недавно, посетитель сайта задал вопрос: можно ли защитить лист (он содержит сводную и обычные таблицы), но при этом оставить возможность обновлять сводную таблицу? Цель – исключить возможность редактировать ячейки в обычных таблицах.

Поскольку я знал, что Excel предоставляет обширный набор разрешенных действий, даже если используется защита листа Excel, я подумал, что вопрос простой и быстро нашел соответствующую опцию. Откройте приаттаченный к статье Excel-файл. Откройте вкладку Рецензирование, перейдите в область Изменения и кликните Защитить лист. В открывшемся окне Защита листа поставьте галочку напротив Использование отчетов сводных таблиц (рис. 1). Наберите пароль (в моем файле пароль 123), кликните Ok, в открывшемся окне Подтверждение пароля повторите ввод пароля, кликните Ok, и вуаля… лист защищен.

Рис. 1. Окно настройки параметров защиты листа

Скачать заметку в формате Word или pdf, пример в формате Excel (файл содержит код VBA).

К сожалению, как иногда бывает в Excel, функция работала не так, как ожидалось. Защита не позволила обновить сводную таблицу. На рис. 2 видно, что опция Обновить недоступна.

Рис. 2. На защищенном листе невозможно обновить сводную таблицу

Я перешел на официальный сайт Microsoft, чтобы уточнить, как работает опция. [1] И в статье Разблокировка определенных областей защищенного листа прочитал:

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

В этой же статье предлагалось перед установкой флажка выделить ячейки, которые не подлежат блокированию. Подробные шаги очень понятно описаны в разделе Блокирование только определенных ячеек и диапазонов ячеек на защищенном листе. Но и это не спасло – обновление сводной по-прежнему было недоступно. Ну что ж, облом… Всё выполнил верно, но MS не помог.

Тогда я обратился к Excel-сообществу и получил совет использовать макрос, который сначала отключит защиту листа, обновит сводную, и снова включит защиту. Вот VBA-код этого макроса:

Sub Refresh_All()

ActiveSheet.Unprotect Password:="123"

ActiveSheet.PivotTables("СводнаяТаблица3").PivotCache.Refresh

ActiveSheet.Protect Password:="123"

End Sub

Здесь 123 – пароль защиты листа, а СводнаяТаблица3 – название сводной таблицы, требующей обновления.

Примечание. Если вы никогда не вставляли VBA-код в ваши файлы, рекомендую начать с заметки Сумма по цвету ячеек в Excel.

Для удобства использования макроса я создал кнопку, и связал с ней этот VBA-код. Отключите защиту листа, перейдите на вкладку Разработчик в область Элементы управления и кликните Вставить. Выберите элемент Кнопка (рис. 3).

Рис. 3. Вставить кнопку

Как только вы кликните на элементе Кнопка курсор примет форму тонкого крестика. Нарисуйте им в удобном месте листа кнопку нужного размера. Когда вы закончите рисование, появится окно Назначить макрос объекту (рис. 4). Выберите макрос Refresh_All и кликните Ok. Отредактируйте название кнопки напечатав вместо Кнопка 3 более определенное – Обновить.

Примечание. Если вы создаете кнопку первый раз, то название будет Кнопка 1.

Рис. 4. Связь кнопки с макросом

Защитите лист. Файл готов для использования (рис. 5). Поэкспериментируйте с листом исходные данные и последующим обновлением сводной таблицы, а также с изменением данных в области Е7:Н9. Вы добились того, чего хотели: сводная обновляется, а остальные данные защищены от изменений.

Рис. 5. Цель достигнута – сводная обновляется, а остальные данные защищены от изменений

Как часто бывает, придуманная защита не является абсолютной. Опытный пользователь изучит VBA-код и найдет пароль. Это даст ему несанкционированный доступ к защищенной области…

[1] Любопытно, что когда я, ничего не подозревая, набрал в поисковой строке Google защитить лист оставить обновление сводной таблицы, то на первом месте была ссылка на одну из моих заметок – Советы по работе со сводными таблицами.