Частичная защита листа 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 защитить лист оставить обновление сводной таблицы, то на первом месте была ссылка на одну из моих заметок – Советы по работе со сводными таблицами.

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

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

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

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


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