Как защитить ячейки в excel от редактирования
На лист можно поставить защиту, при которой все могут просматривать содержание листа, но нельзя ничего в нем изменять. Так же можно заблокировать все, кроме диапазона ячеек для ввода данных. А иногда можем просто скрыть формулы или источники данных.
Как поставить защиту в Excel на лист
В таблице данных защитим ячейки листа от изменений, данных в шапке и строке итоговых значений.
- Выделите диапазон ячеек B2:B6 и вызовите окно «Формат ячеек» (CTRL+1). Перейдите на вкладку «Защита» и снимите галочку на против опции «Защищаемая ячейка». Нажмите ОК.
- Выберите инструмент «Рицензирование»-«Защитить лист».
- В появившемся диалоговом окне «Защита листа» установите галочки так как указано на рисунке. То есть 2 опции оставляем по умолчанию, которые разрешают всем пользователям выделять любые ячейки. А так же разрешаем их форматировать, поставив галочку напротив «форматирование ячеек». При необходимости укажите пароль на снятие защиты с листа.
Как видно на рисунке, в окне «Защита листа» содержится большое количество опций, которыми можно гибко настраивать ограничение доступа к данным листа.
Как скрыть формулу в ячейке Excel
Часто бывает так, что самое ценное на листе это формулы, которые могут быть достаточно сложными. Данный пример сохраняет формулы от случайного удаления, изменения или копирования. Но их можно просматривать. Если перейти в ячейку B7, то в строке формул мы увидим: «СУММ(B2:B6)» .
Теперь попробуем защитить формулу не только от удаления и редактирования, а и от просмотра. Решить данную задачу можно двумя способами:
- Запретить выделять ячейки на листе.
- Включить скрытие содержимого ячейки.
Рассмотрим, как реализовать второй способ:
- Если лист защищенный снимите защиту выбрав инструмент: «Рецензирование»-«Снять защиту листа».
- Перейдите на ячейку B7 и снова вызываем окно «Формат ячеек» (CTRL+1). На закладке «Защита» отмечаем опцию «Скрыть формулы».
- Включите защиту с такими самыми параметрами окна «Защита листа» как в предыдущем примере.
Теперь переходим на ячейку B7 и убеждаемся в том, что в строке формул ничего не отображается. Даже результат вычисления формулы.
Примечание. Закладка «Защита» доступна только при незащищенном листе.
Как скрыть лист в Excel
Допустим нам нужно скрыть закупочные цены и наценку в прайс-листе:
- Заполните «Лист1» так как показано на рисунке. Здесь у нас будут храниться закупочные цены.
- Скопируйте закупочный прайс на «Лист2», а в место цен в диапазоне B2:B4 проставьте формулы наценки 25%: =Лист1!B2*1,25.
- Щелкните правой кнопкой мышки по ярлычке листа «Лист1» и выберите опцию «Скрыть». Рядом же находится опция «Показать». Она будет активна, если книга содержит хотя бы 1 скрытый лист. Используйте ее, чтобы показать все скрытие листы в одном списке. Но существует способ, который позволяет даже скрыть лист в списке с помощью VBA-редактора (Alt+F11).
- Для блокировки опции «Показать» выберите инструмент «Рецензирование»-«Защитить книгу». В появившемся окне «Защита структуры и окон» поставьте галочку напротив опции «структуру».
- Выделите диапазон ячеек B2:B4, чтобы в формате ячеек установить параметр «Скрыть формулы» как описано выше. И включите защиту листа.
Внимание! Защита листа является наименее безопасной защитой в Excel. Получить пароль можно практически мгновенно с помощью программ для взлома. Например, таких как: Advanced Office Password Recovery – эта программа позволяет снять защиту листа Excel, макросов и т.п.
Полезный совет! Чтобы посмотреть скрытые листы Excel и узнать их истинное количество в защищенной книге, нужно открыть режим редактирования макросов (Alt+F11). В левом окне «VBAProject» будут отображаться все листы с их именами.
Но и здесь может быть закрыт доступ паролем. Для этого выбираем инструмент: «Tools»-«VBAProjectProperties»-«Protection» и в соответствующих полях вводим пароль. С другой стороны, если установленные пароли значит, книга скрывает часть данных от пользователя. А при большом желании пользователь рано или поздно найдет способ получить доступ этим к данным. Об этом следует помнить, когда Вы хотите показать только часть данных, а часть желаете скрыть! В данном случае следует правильно оценивать уровень секретности информации, которая предоставляется другим лицам. Ответственность за безопасность в первую очередь лежит на Вас.
Примечание. Группировка при защите листа Excel – не работает при любых настройках. Сделать доступную в полноценном режиме функцию группировки на защищенном листе можно только с помощью макросов.
При работе с Excel достаточно часто приходится сталкиваться с защищенными от редактирования ячейками. Хорошо бы было их экспонировать на фоне ячеек доступных для редактирования и ввода данных, чтобы не тратить время на бессмысленные попытки. Благодаря условному форматированию эта задача решается быстро и в автоматическом режиме.
Как определить защищенные ячейки в Excel
Для примера возьмем таблицу, у которой защищены все значения кроме диапазона первой позиции B2:E2.
Но лист может содержать несколько незащищенных ячеек. Чтобы быстро найти и определить незащищенные ячейки доступные для редактирования в таблице, сначала определим – защищенные. Для этого делаем следующее:
- Создаем второй лист и на нем в ячейке A1 вводим такую формулу:
- Теперь выделяем диапазон A1:E5 на этом же (втором) листе размером сопоставим с исходной таблицей так чтобы активной ячейкой осталась А1 (с формулой). И жмем клавишу F2.
- Нажимаем комбинацию горячих клавиш CTRL+Enter и получаем результат:
Там, где у нас появились нули, там находятся незащищенные ячейки в исходной таблице. В данном примере это диапазон B2:E2, он доступен для редактирования и ввода данных.
Как автоматически выделить цветом защищенные ячейки
Внимание! Данный пример можно применить только в том случаи если лист еще не защищен, так как после активации защиты листа инструмент «Условное форматирование» – недоступен!
- Выделяем диапазон всех ячеек c числовыми данными в исходной таблице B2:E5, которые следует проверить.
- Выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило».
- В разделе данного окна «Выберите тип правила:» выберите опцию «Использовать формулу для определения форматированных ячеек:».
- В поле ввода вводим формулу:
- Нажимаем на кнопку формат и переходим на вкладку «Заливка». В разделе «Цвет фона:» указываем – желтый. И жмем ОК на всех окнах.
Результат формулы автоматического выделения цветом защищенных ячеек:
Внимание! Перед использованием условного форматирования правильно выделяйте диапазон данных. Например, если Вы ошибочно выделили не диапазон таблицы с данными B2:E5, а всю таблицу A1:E5 тогда следует изменить формулу таким образом: =ЯЧЕЙКА("защита";A1)=1
Как определить и выделить цветом незащищенные ячейки
Если нужно наоборот выделить только те ячейки которые доступны для редактирования нужно в формуле изменить единицу на ноль: =ЯЧЕЙКА("защита";B2)=0.
При создании правила форматирования для ячеек таблицы мы использовали функцию ЯЧЕЙКА. В первом аргументе мы указываем нужный нам тип сведений о ячейке –"защита". Во втором аргументе мы указываем относительный адрес для проверки всех ячеек диапазона. Если ячейка защищаемая функция возвращает число 1 и тогда присваивается указанный нами формат.
Включаем защиту ячеек
К сожалению, в Excel не предусмотрена отдельная функция, которая выполняет блокировку ячеек с целью их защиты, однако, для этих целей можно воспользоваться защитой всего листа. Сделать это можно по-разному.
Метод 1: используем меню “Файл”
Для включения защиты выполняем следующие действия:
- Сначала нужно выделить все содержимое листа. Для этого щелкаем по прямоугольнику на пересечении координатных панелей. Также можно нажать комбинацию клавиш Ctrl+A (один раз, если выбрана ячейка за пределами заполненной таблицы, два раза – если выбрана ячейка внутри нее).
Метод 2: применяем инструменты вкладки “Рецензирование”
Второй метод включения защиты ячеек предполагает использование инструментов вкладки “Рецензирование”. Вот как это делается:
-
Выполняем шаги 1-5, описанные в методе 1, т.е. снимаем защиту со всего листа и обратно устанавливаем только для выделенных ячеек.
Примечание: При сжатых размерах окна программы (по горизонтали) блок инструментов “Защита” представляет собой кнопку, нажатие которой раскроет список доступных команд.
Снимаем защиту
Для снятие блокировки необходимо ввести пароль:
Заключение
Несмотря на то, что в Excel нет специальной функции, предназначенной для защиты определенных ячеек от редактирования, сделать это можно через включение защиты всего листа, предварительно установив требуемые параметры для выбранных ячеек.
Для того, чтобы защитить данные как от посторонних лиц, так и от собственных случайных действий, пользователи могут установить защиту на документы Эксель. Увы, не все знают, как снять такую защиту, чтобы получить доступ к информации, в т.ч., иметь возможность ее редактировать. И что делать, если файл получен от другого пользователя, который забыл передать нам пароль, или мы случайно забыли (потеряли) его? Давайте разберемся подробнее.
Отметим, что заблокировать документ Excel можно двумя способами: защитить лист или книгу. Соответственно, от этого будет зависеть то, какие действия нужно предпринять для разблокировки.
Снимаем защиту с книги
Снимаем защиту с листа
Пароль для защиты может быть установлен не только для всего документа, но и в отношении конкретного листа. В данном случае, пользователь сможет видеть содержимое листа, но у него не будет возможности редактировать информацию.
Для снятия защиты листа выполняем следующие действия.
Меняем код файла для снятия защиты листа
Данный метод нужен в тех случаях, когда пароль был утерян или не передан вместе с файлом от другого пользователя. Работает он только в отношении тех документов, которые защищены на уровне отдельных листов, а не всей книги, т.к. нам нужно попасть в меню “Файл”, что невозможно при защите паролем всего документа.
Чтобы снять защиту, необходимо выполнить следующую последовательность действий:
-
Сразу переходим к пункту 4, если расширение файла – XLSX (Книга Excel). Если формат документа – XLS (Книга Excel 97-2003), нужно сначала его пересохранить с нужным расширением. Для этого переходим в меню “Файл”.
Сторонние программы для удаления паролей
Чтобы удалить пароль, можно воспользоваться программами сторонних разработчиков. При этом стоит помнить о возможном риске, связанном со скачиванием, установкой и использованием нестандартных инструментов операционной системы и программы Excel.
Если вы, все же, решили воспользоваться такой возможностью, можно обратить внимание на достаточно популярную программу Accent OFFICE Password Recovery.
Обращаем ваше вниманием на то, что для получения доступа ко всем функциям программы нужно приобрести лицензию. Для ознакомления с приложения доступна демо-версия, однако, она не позволяет удалять пароли.
Заключение
Защита книги или отдельного листа – крайне полезные функции программы Excel, когда нужно защитить информацию от посторонних лиц или, например, обезопасить себя от случайных изменений важных данных, предназначенных только для чтения. Но иногда возникает обратная необходимость – снять ранее установленную защиту. Сделать это можно по-разному, в зависимости от того, каким образом она была установлена. И даже если вы забыли пароль, блокировку можно снять, правда, только в том случае, когда код был установлен в отношении отдельных листов, а не всей книги.
Читайте также: