В этой статье я расскажу как защитить ячейки в Excel от изменений и редактирования. Защита ячеек может заключаться в том, что пользователи, открывшие ваш файл не смогут редактировать значения ячеек, а также видеть формулы.
Прежде чем, мы разберемся как настроить защиту, важно понять принцип работы защиты ячеек в Excel. По-умолчанию, все ячейки в Excel уже заблокированы, но фактически доступ к ним будет ограничен после того, как вы в настройках защиты листа введете пароль и условия по ограничению доступа.
Если вы хотите защитить абсолютно все ячейки в вашем Excel файле от редактирования и изменений, проделайте следующие действия:
Если вы установили пароль, то система попросит вас повторно ввести его.
Теперь, всем пользователям, которые попытаются внести изменения или отредактировать значения ячеек будет отображаться такое сообщение:
Для того чтобы снять установленную защиту, перейдите во вкладку “Рецензирование “, и в разделе “Защита ” кликните по иконке “Снять защиту с листа “. После этого система попросит вас ввести пароль для снятия защиты.
Чаще всего, вам может не потребоваться защита всего листа целиком, а только отдельных ячеек. Как я писал в начале статьи, все ячейки в Excel по умолчанию заблокированы. Для того чтобы блокировка наступила фактически нужно настроить защиту листа и задать пароль.
Для примера рассмотрим простую таблицу с данными о доходах и расходах. Наша задача защитить от изменений ячейки в диапазоне B1:B3 .
Для того чтобы заблокировать отдельные ячейки проделаем следующие действия:
Таким образом, мы отключили настройку Excel для ячеек всего листа, при котором все ячейки готовы к защите и блокировке.
Итак, для начала – как отличить защищенную ячейку от незащищенной? Выделите ячейку/ячейки, нажмите на одну из них правой кнопкой и в контекстном меню выберите “Формат ячеек…”.
Перейдите на вкладку “Защита”. Это самая правая вкладка в окне форматирования ячейки.
Посмотрите на две галочки, которые там есть.
Если верхняя (“Защищаемая ячейка”) отмечена – значит, редактирование данных ячеек будет недоступно, если включить защиту листа. Если же её снять – ее можно будет редактировать даже в режиме защиты.
Если отмечена нижняя (“Скрыть формулы”) – при защите листа нельзя будет увидеть формулу ячейки в строке формул.
Нажмите “ОК”, чтобы закрыть данное окно.
По умолчанию у всех ячеек верхняя отмечена
Выберите в меню сверху пункт “Сервис → Защита → Защитить лист…”.
Вы увидите окно, в котором можно настроить, как именно будет защищен лист. Можно ввести в текстовое поле пароль, который необходимо будет ввести для снятия защиты.
Теперь об основных галочках в рамке “Разрешить всем пользователям листа”. “Выделение заблокированных ячеек” – можно ли выделять ячейки, у которых стоит галочка “Защищаемая ячейка” (см. шаг 2), “Выделение незаблокированных ячеек” – у которых не стоит. “Форматирование ячеек/столбцов/строк” – можно ли формать формат ячеек (цвет, шрифт, границы, …), стобцов (ширина), строк (высота). “Вставку стобцов/строк/гиперссылок” – можно ли добавлять на страницу столбы, строки, вставлять гиперссылки, “Удаление столбцов/строк” – можно ли удалять столбцы и строки. Нажмите “ОК”, чтобы защитить лист.
Теперь Вы не сможете изменить содержимое ни одной из ячеек, у которых стояла галочка “Защищаемая ячейка”. Если попробуете, то увидите окно с сообщением, что Вы не можете этого сделать.
Чтобы снять защиту листа, выберите в меню пункт “Сервис → Защита → Снять защиту листа…”. Если Вы указывали пароль, защищая лист – Excel запросит у Вас пароль.
Теперь к практике. Для начала решите – какие ячейки должны быть защищены, а какие нет. На рисунке два примера – темным отмечены ячейки, которые должны быть заблокированы.
В левом проще сначала заблокировать все ячейки, потом разблокировать прямоугольное поле.
В правом проще сначала разблокировать все, потом заблокировать нужные ячейки.
Выделите все ячейки, нажав на прямоугольник на пересечении разметки столбцов и ячеек. Затем зайдите в уже знакомое нам окно “Формат ячеек” (нажмите правой кнопкой и выберите этот пункт в контекстном меню).
Программа Microsoft Office Excel предназначена для того, чтобы работать с информацией в таблицах. Она представлена в виде сетки, которую образуют столбцы и строки. В некоторых случаях у автора «книги» - документа Excel - возникает необходимость защитить данные от изменения . Вы можете предохранить произвольное количество клеток от введения неправильных данных или редактирования. Это нужно для того, чтобы:
В статье рассмотрены доступные в Excel варианты.
Именно так вы можете предотвратить вмешательство в книгу Excel от случайного или намеренного вмешательства того или иного юзера. Это позволит избежать утери данных при сохранении и закрытии документа, а также попытки восстановить значения, что занимает время и не всегда является возможным.
К сожалению, в Excel нет кнопки, которая мгновенно ограничит доступ к той или иной области. Тем не менее всё можно сделать с помощью возможностей программы, не прибегая к другим. Для того чтобы установить подобную защиту от введения неправильных данных или изменения, воспользуйтесь одним из способов:
Вы также можете запаролить функции сохранности, делая изменение недоступным для людей, не знающих кода доступа. Например, это позволяет разбить книгу на области, в каждой из которых работает один человек. Комбинируя различные функции, вы сможете добиться желаемого типа ограничения прав на редактирование. Ограничение на изменение также можно отменить (если был установлен пароль, потребуется ввести его).
Настройку можно произвести для различного типа защищаемых данных.
Указанный способ - самый простой в применении. С его помощью вы сможете контролировать то, что вы (или другой пользователь) вводите в клетку. Можно отсеивать данные, которые не проходят по определённым критериям, что также можно указать. Таким образом, вы можете предотвратить ввод отрицательной цены, дробного количества человек или неправильной даты определённого события. Для того чтобы установить защиту, нужно:
Добавляем автоматические уведомления для работы с ячейками.
Используя эту функцию, вы можете чётко указать конкретные клетки или группы, которые необходимо уберечь от изменения. Это можно сделать для:
Ограничение всей рабочей области:
Выделяя всю таблицу либо её часть, ограничиваем доступ.
Ограничение прав к клеткам, выбранным вручную:
Ограничение доступа к определённым группам клеток нужно в том случае, если необходимо ограничить редактирование одной или нескольких групп с конкретными свойствами содержимого:
Пример выбора содержимого с определенными свойствами.
Недостаточно просто снять или установить флажки в пунктах об ограничении прав на изменение данных. Защищёнными (или наоборот, если вы сняли отметку) клетки станут после того, как будет активирована функция для всей страницы. Настройки Excel по умолчанию - активированный пункт «Защищаемая ячейка» и выключенный «Скрыть формулы», но вы можете проверить это или внести поправки, если необходимо.
Если вы хотите оставить возможность форматирования всех элементов страницы Excel, отметьте только три первых пункта.
Excel позволяет добавить пароль при защите листа.
В случае, если над документом работает несколько человек, и каждому из них должен быть доступен только определённый участок, необходимо:
При утилизации такой системы функций каждый пользователь перед тем, как редактировать определённый участок таблицы, будет вынужден ввести пароль. Если он не знает секретного кода, внести изменения не получится.
Ограничиваем либо предоставляем доступ к диапазонам ячеек.
В большинстве случаев недостаточно ограничить права к клеткам и их группам по функционалу. Целесообразно запретить редактирование всей страницы Excel либо нескольких. Для этого нужно:
С помощью указанной функции вы сможете избежать не только изменения определённых значений, но и редактирования названия, «шапки» (закреплённых строк и столбцов), форматирования и изменения размеров в окне Excel.
Ограничения, установленные в документе Excel, может отменить любой пользователь, имеющий права (по умолчанию). Для того, чтобы лишить возможности других юзеров отключать функцию предохранения от редактирования или ввода неверных данных, можно установить пароль. Для этого:
После активации функции документ не сможет быть изменён человеком, не знающим кода доступа к файлу. Будьте аккуратнее, так как пароль от Excel восстановить невозможно - он будет утерян со всеми данными.
Этот алгоритм подойдёт как для возврата прав ко всему листу, так и некоторым его элементам, если они - единственные заблокированные. Для этого:
После использования указанных функций документ Excel снова станет доступен для всех юзеров. Для того чтобы отменить защиту только на части книги, сначала сделайте это для всей, после чего выделите элементы, права к которым следует ограничить вновь.
Зная, какие сохранные возможности предлагает Excel, вы можете довольно тонко настроить ограничения доступа. Так можно предостеречь себя от случайного ввода нежелательных данных, а также редактирования книги сторонним пользователем. Вы можете запаролить клетки, а также снять предохранение от нежелательного доступа или поправок полностью или частично. Это самые простые способы ограничения прав , которые могут быть настроены в самом Excel.
Как и любую информацию данные в Экселе иногда приходится защищать от редактирования или случайного удаления. К сожалению, в меню нет такой кнопки, нажатием на которую можно одним действием защитить отдельную ячейку от редактирования. Но зато есть набор различных защит, комбинируя которые можно получить желаемый результат.
Защитить ячейку от изменений в Excel можно только установив защиту всего листа. По умолчанию при включении защиты листа в Экселе будут защищены все ячейки. Если выделить ячейку или несколько, и войти в формат ячеек, то можно на вкладке «Защита» увидеть установленную галочку «Защищаемая ячейка» . Убирая эту галочку мы снимаем защиту с этих ячеек, и они будут всегда доступны для редактирования.
Теперь при включении защиты листа в Экселе на вкладке «Рецензирование» пунктом меню «Защитить лист» , мы защитим от изменений все ячейки листа, для которых не была снята галочка. А ранее редактируемые нами ячейки можно будет изменять. При этом в момент включения защиты появится возможность сделать дополнительные настройки и выбрать, что именно нельзя будет делать с ячейками.
Теперь рассмотрим возможность защиты паролем отдельных ячеек в Excel. Необходимо будет выделить ячейки, которые мы хотим защитить отдельным паролем в Экселе, и на вкладке «Рецензирование» выбрать пункт меню «Разрешить изменение диапазонов» . В появившемся окошке нажимаем «Создать» , при желании вводим имя диапазона, проверяем или вводим новый диапазон ячеек, и указываем пароль.
Но после всех этих настроек защита ячеек в Excel от редактирования еще не работает. Чтобы все заработало, необходимо включить защиту листа, как это было рассмотрено выше. После этого все ячейки будут защищены от изменений, но для выбранных нами ячеек будет действовать отдельный пароль, введя который без разблокировки листа можно будет вносить необходимые изменения.
Зная возможности защиты ячеек в Экселе теперь можно комбинировать различные варианты. Например, выделив все ячейки листа и зайдя в формат ячеек можно на вкладке защиты отменить защиту этих ячеек, затем выделить отдельную группу ячеек и вернуть им защиту. На данном этапе при включении защиты листа заблокированными будут только отмеченные нами ячейки. А если перед этим через меню разрешения изменения диапазонов ввести отдельный пароль для выделенных ячеек, то после включения защиты листа во все ячейки можно будет вносить любые данные, а в заблокированные ячейки можно будет внести изменения только при вводе пароля. Собственно, только таким образом и получится защитить отдельные ячейки Экселя от изменений стандартными средствами.
Иногда вы хотите разрешить пользователям изменять ячейки, содержащие данные, не разрешая менять формулы. Можно заблокировать ячейки, содержащие формулы, не защищая целый лист или книгу.
При создании электронной таблицы большинство из нас используют определенного рода формулы, Иногда, однако, мы не хотим, чтобы другие пользователи портили, удаляли или переписывали любые формулы, присутствующие в таблице, Самый простой и распространенный способ запрещения модификации формул - защитить лист, Однако защита листа не только мешает пользователям изменять формулы, она также подразумевает, что пользователи не смогут ввести или изменить никакие данные, Обычно вы не хотите заходить так далеко, По умолчанию все ячейки на листе заблокированы; однако это не имеет никакого эффекта, пока не применяется защита листа. Далее мы расскажем об очень простом способе защиты листа, когда блокируются и защищаются только ячейки с формулами.
Выберите все ячейки на листе, нажав сочетание клавиш Ctrl/Apple+A или щелкнув кнопку выделения листа (серый квадрат в месте пересечения столбца А и строки 1). Затем выберите команду Формат → Ячейки → Защита (Format → Cells → Protection) и сбросьте флажок Защищаемая ячейка (Locked). Щелкните кнопку ОК.
Теперь выделите любую ячейку, выберите команду Правка → Перейти (Edit → Go To) (Ctrl+G или F5) и щелкните кнопку Выделить (Special). Вы увидите диалоговое окно выделения группы ячеек (рис. 1.13).
В диалоговом окне Go To Special (Выделение группы ячеек) выберите переключатель Формулы (Formulas) и, если необходимо, выберите определенные типы формул, установив соответствующие флажки. Щелкните на кнопке ОК. Будут выделены только ячейки с формулами. Выберите команду Формат → Ячейки → Защита (Format → Cells → Protection) и установите флажок Защищаемая ячейка (Locked). Щелкните на кнопке ОК. Теперь выберите команду Сервис → Защита → Защитить лист (Tools → Protection → Protect Worksheet), чтобы защитить лист и, если необходимо, назначить пароль.
Предыдущий метод определенно экономит много времени и исключает возможные ошибки при поиске формул для защиты. К сожалению, при этом пользователи не смогут воспользоваться несколькими средствами, например, сортировкой, форматированием, выравниванием текста и многими другими, о которых вы можете и не знать, даже в незаблокированной ячейке. Эту проблему можно решить двумя способами.
Первый подход совершенно не использует защиту листа, зато применяет проверку правильности данных.
Проверка правильности данных далека от идеала, когда дело доходит до предотвращения помещения в ячейки непроверенных данных. Пользователи легко могут вставлять любые скопированные данные в проверенную ячейку и, делая это, удалять пометку о проверке из этой ячейки. Если копируемая ячейка содержит пометку о проверке, то в этом случае новая пометка перекроет исходную.
Теперь, когда выбраны только ячейки с формулами, перейдите на вкладку Данные → Проверка → Параметры (Data → Validation → Settings), в поле Тип данных (Allow) выберите Другой (Custom), а в поле Формула (Formula) введите =»» (рис. 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 |
// Листинг 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 и сохранить книгу. Теперь каждый раз, когда вы будете выбирать заблокированную ячейку, ваш лист будет автоматически защищать себя. Как только вы выберете незаблокированную ячейку, лист автоматически снимет с себя защиту.
Этот трюк работает не идеально, хотя обычно вполне сносно. Ключевое слово, используемое в коде, Target, относится только к ячейке, активной на время выбора. Поэтому важно помнить, что, если пользователь выберет диапазон ячеек (где активные ячейки будут не заблокированы), он сможет удалить весь выделенный диапазон, так как целевая ячейка не заблокирована и, следовательно, лист снимает с себя защиту.
