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

Рубрика: 7. Полезняшки Excel

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

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

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

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

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

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

Рис. 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. Вставить кнопку

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

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

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

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

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

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

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

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

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

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

Комментарии: 8 комментариев

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

Я сделала следующий макрос:

Sub update()
ActiveSheet.Unprotect
ActiveSheet.PivotTables(«СводнаяТаблица3»).PivotCache.Refresh
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True, AllowUsingPivotTables:=True
End Sub

В этом случае у меня все работает. Но если я добавляю пароль к защите (и прописываю его в макросе), то фильтр работать опять перестает. Как сделать так, чтобы работало и с паролем, и с фильтром, и с обновлением сводной, я так и не придумала.

Добрый день! Сергей Викторович много чему научился благодаря Вашему сайту по вопросах Ексель. У меня к Вам один вопрос как поставить защиту или скрыть формулы в «умных» таблицах ексель, стандартным способом защитить лист «умная» таблица перестает работать. Что Вы можете посоветовать? может быть Вы сталкивались с такой проблемой, у меня ексель 2007. Заранее Вам благодарен.

Добрый день!
а как сделать следующее:
файл содержит лист с данными и несколько листов со сводными таблицами.
Цель: изменяться должна сводная таблица (на одном из листов), на всех других листах сводные таблицы должны оставаться без изменений (т.к. они сформированы на определенный момент времени и обновления в них не нужны)

Ирина, предлагаю:
1) создавайте сводные таблицы на основе разного кеша: один кеш для таблиц, которые затем вы защитите, второй кеш для таблиц, которые нужно обновлять (как это сделать, см. Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?)
2) защитите листы с теми сводными, которые не должны обновляться; оставьте незащищенными листы со сводными, которые должны обновляться

Добрый день, подскажите, пожалуйста, а если у меня на листе несколько сводных таблиц, как правильно прописать макрос?

Нина, в предложенный выше код добавьте столько строк, сколько у вас сводных таблиц:
ActiveSheet.PivotTables("СводнаяТаблица3"). PivotCache.Refresh
Вместо "СводнаяТаблица3" вставьте имена ваших таблиц. Если несколько сводных основаны на одном кеше, можно указать только одну из сводных.

Очень помогла статья, спасибо.
Файл с паролем на изменения. Но для отслеживания внесенных изменений в ячейках установил макрос, который автоматически добавляет примечания и записывает в них все изменения (кто, когда, что, сохраняя текст предыдущего примечания). Но с паролем макрос работать не хотел. Дополнительный макрос решил проблему
Sub Refresh_All()
ActiveSheet.Unprotect Password:="123"
ActiveSheet.PivotTables("СводнаяТаблица3").PivotCache.Refresh
ActiveSheet.Protect Password:="123"
End Sub

Добрый вечер Эдуард!
У меня на защищенном листе стоит макрос по Отслеживанию изменений в ячейках (авто примечание) и соответственно как только включаю защиту авто примечания изменений не работают! Увидев ваш ответ очень обрадовался, что наконец то решу эту проблему, но скопировав отсюда ваш код, но со своими вводными (Лист1 и свой пароль), у меня он не работает! Если есть возможность помогите, откликнитесь! Спасибо…


Прокомментировать