Защита данных в Microsoft Excel

177678 21.10.2012 Скачать пример

Microsoft Excel предоставляет пользователю несколько, условно выражаясь, уровней защиты - от простой защиты отдельных ячеек до шифрования всего файла шифрами крипто-алгоритмов семейства RC4. Разберем их последовательно...

Уровень 0. Защита от ввода некорректных данных в ячейку

Самый простой способ. Позволяет проверять что именно пользователь вводит в определенные ячейки и не разрешает вводить недопустимые данные (например, отрицательную цену или дробное количество человек или дату октябрьской революции вместо даты заключения договора и т.п.) Чтобы задать такую проверку ввода, необходимо выделить ячейки и выбрать на вкладке Данные (Data) кнопку Проверка данных (Data Validation). В Excel 2003 и старше это можно было сделать с помощью меню Данные - Проверка (Data - Validation). На вкладке Параметры из выпадающего списка можно выбрать тип разрешенных к вводу данных:

protection1.png

Соседние вкладки этого окна позволяют (при желании) задать сообщения, которые будут появляться перед вводом - вкладка Сообщение для ввода (Input Message), и в случае ввода некорректной информации - вкладка Сообщение об ошибке (Error Alert):

protection2.png  

 Уровень 1. Защита ячеек листа от изменений

Мы можем полностью или выборочно запретить пользователю менять содержимое ячеек любого заданного листа. Для установки подобной защиты следуйте простому алгоритму:

  1. Выделите ячейки, которые не надо защищать (если таковые есть), щелкните по ним правой кнопкой мыши и выберите в контекстном меню команду Формат ячеек (Format Cells). На вкладке Защита (Protection) снимите флажок Защищаемая ячейка (Locked). Все ячейки, для которых этот флажок останется установленным, будут защищены при включении защиты листа. Все ячейки, где вы этот флаг снимете, будут доступны для редактирования несмотря на защиту. Чтобы наглядно видеть, какие ячейки будут защищены, а какие - нет, можно воспользоваться этим макросом.
  2. Для включения защиты текущего листа в Excel 2003 и старше - выберите в меню Сервис - Защита - Защитить лист (Tools - Protection - Protect worksheet), а в Excel 2007 и новее - нажмите кнопку Защитить лист (Protect Sheet) на вкладке Рецензирование (Reveiw). В открывшемся диалоговом окне можно задать пароль (он будет нужен, чтобы кто попало не мог снять защиту) и при помощи списка флажков настроить, при желании, исключения:

protection3.png

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

Уровень 2. Выборочная защита диапазонов для разных пользователей

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

Чтобы сделать это выберите на вкладке Рецензирование (Review) кнопку Разрешить изменение диапазонов (Allow users edit ranges). В версии Excel 2003 и старше для этого есть команда в меню Сервис - Защита - Разрешить изменение диапазонов (Tools - Protection - Allow users to change ranges):

protection4.png

В появившемся окне необходимо нажать кнопку Создать (New) и ввести имя диапазона, адреса ячеек, входящих в этот диапазон и пароль для доступа к этому диапазону:

protection5.png

Повторите эти действия для каждого из диапазонов разных пользователей, пока все они не окажутся в списке. Теперь можно нажать кнопку Защитить лист (см. предыдущий пункт) и включить защиту всего листа.

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

Уровень 3. Защита листов книги

Если необходимо защититься от:

  • удаления, переименования, перемещения листов в книге
  • изменения закрепленных областей ("шапки" и т.п.)
  • нежелательных изменений структуры (сворачивание строк/столбцов при помощи кнопок группировки "плюс/минус")
  • возможности сворачивать/перемещать/изменять размеры окна книги внутри окна Excel

то вам необходима защита всех листов книги, с помощью кнопки Защитить книгу (Protect Workbook) на вкладке Рецензирование (Reveiw) или - в старых версиях Excel - через меню Сервис - Защита - Защитить книгу (Tools - Protection - Protect workbook):

protection7.png

Уровень 4. Шифрование файла

При необходимости, Excel предоставляет возможность зашифровать весь файл книги, используя несколько различных алгоритмов шифрования семейства RC4. Такую защиту проще всего задать при сохранении книги, т.е. выбрать команды Файл - Сохранить как (File - Save As), а затем в окне сохранения найти и развернуть выпадающий список Сервис - Общие параметры (Tools - General Options). В появившемся окне мы можем ввести два различных пароля - на открытие файла (только чтение) и на изменение:

protection6.png

Ссылки по теме


29.11.2013 19:37:12
Добрый вечер!
Как избежать взлома защищенных листов в ексель?
Спасибо
01.02.2014 13:12:57
Никак. Абсолютной защиты не существует в принципе. А в Excel, вдобавок, используются весьма слабые алгоритмы защиты листов - ломается на раз.
03.03.2014 13:41:57
Как вариант из розряда "костылей" - сохранять ексель-файл в winrar с сложным паролем (чтобы исключить наличие пароля в библиотеке взломщика).
взломать будет труднее
24.03.2014 13:19:27
Как вариант (как оказалось) очень хорошо защищает сохранение в .xlsb - ни одна программа не поддерживает взлом и один из наших хакеров такой файл пытается неделю взломать и никак, а всего навсего пароль на открытие.
24.03.2014 14:12:47
Видимо, это потому, что xlsb - это бинарный внутренний формат Excel, своего рода "скомпилированный" файл. Остальные программы его читают плохо.
Спасибо за уточнение - будем пользоваться :)
12.04.2016 18:48:24
а пересохранить xlsb в xlsx?
или имеется ввиду пароль на сам файл?
31.01.2014 20:47:26
День добрый!
Пара вопросов по Уровню 0. Защита от ввода некорректных данных в ячейку.
Вопрос 1. Есть поле, в которое должен вводиться пользователем почтовый индекс. Т.е. целое число, длиной 6 символов. Однако кроме типа данных Эксель просит указать еще и диапозон верных значений. Можно было бы задать диапозон 100000-999999, но в него не попадут индексы, начинающиеся с нуля. Как правильно задать значения?
Вопрос 2. Редко кто вводит индекс с клавиатуры, обычно предпочитают копировать-вставить. Как защитить ячейку от вставки некорректного числа и изменения формата (на формат ячейки-источника)?
01.02.2014 13:19:37
1. Тут только макросом можно. Посмотрите http://www.planetaexcel.ru/techniques/7/97/
2. Тоже только макросом - перехватывать копирование-вставку.
25.06.2014 15:02:30
А можете привести пример макроса?
10.02.2014 22:51:14
После установки защиты листа невозможно использование функции "группировать". Возможно "полечить" функцию или может быть можно защитить лист как-то иначе что бы возможно было пользоваться группировкой?
18.07.2014 15:56:21
Просто, красиво и со вкусом! Со вкусом знаний! ))) Красавец! ;)
22.07.2014 19:20:16
Спасибо за урок, Николай. Попытался включить защиту клеток с формулами (включая весь столбец, содержащий формулы), используя «умную таблицу». Она перестала растягиваться, и даже протянуть нумерацию в порядке возрастания не представляется возможным. А хотелось бы предоставить пользователям право редактировать файл, без доступа к формулам. Существует ли решение данной проблемы. Заранее благодарю.
23.11.2014 09:09:19
К сожалению, умные таблицы с защитой не взаимодействуют. Т.е. надо заранее снимать флажок Защищаемая ячейка для пустых ячеек, куда умная таблица будет расширяться. И защитить столбец с формулами внутри умной таблицы не получится, к сожалению. Или писать макрос, который будет перед изменением таблицы снимать защиту листа, а потом ставить обратно.
20.11.2014 12:37:04
Добрый день!
Воспользовался вашим скриптом для внесения текущей даты в ячейку при внесении изменений в предыдущую ячейку. Пробую защитить лист книги по этому методу. Но скрипт теперь не работает на заблокированных ячейках. Как можно исправить ситуацию.
Задача такая: есть выпадающий список в ячейке. При выборе значения ячейки автоматически вставляется дата, время и имя компьютера в соседних ячейках. Но значения этих ячеек изменять нельзя. Как можно реализовать данный алгоритм.
Буду очень благодарен если поможете!!!
23.12.2014 08:45:59
Добрый день,
забыла пароль снятия защиты общей книги excel, как снять пароль, что можно сделать в таких ситуациях?
спасибо!
19.02.2015 11:51:20
Посмотрите вот эту статью - должно помочь
18.02.2015 22:56:40
Пример, увы, скачать не удается. Проблемная ссылка.
19.02.2015 11:49:54
В этом приеме просто нет примера. Да и какой тут можно приложить пример? Файл с установленной защитой? :)
19.02.2015 12:36:01
ну понятно, я купился на особенности дизайна: ссылка на пример есть, а примера нет :)
10.04.2015 15:42:19
Очень нужна помощь. Нужно защитить всю строку от изменения всеми пользователями, кроме того, имя которого выбрано в выпадающем списке столбца "Автор". Например: есть лист, в нем список из 300 наименований в одном столбце, второй столбец - "автор", третий столбец - что угодно, четвертый - что угодно. И так. Открывает пользователь таблицу, в выпадающем меню "Автора" выбирает свой логин и изменяет все что ему угодно в отфильтрованных строках. Соответственно другой юзер не изменит ничего что отфильтровано не по его логину на сервере! Такое возможно? При этом число в 300 строк может измениться. Т.е 301-ю строку добавить может кто угодно, и если добавил ее автор1, то никто кроме автора1 уже ее не изменит.
Очень надеюсь на вашу помощь )
29.05.2015 12:54:54
Можно попробовать реализовать такое макросом, но звучит малореально. Мой вам совет: лучше сделайте отдельные файлы для каждого пользователя с персональными правами для них и собирайте потом макросом или ссылками оттуда данные в общий файл. ИМХО, так проще будет.
04.06.2015 11:55:05
Добрый день, Николай!
Столкнулся с одной проблемой при защите ячеек в "умных таблицах". Дело в том, что в таких таблицах данная защита не работает, точнее работает, но только за переделами диапазона ячеек "умной таблицы". Подскажите, как мне заблокировать то, что я хочу?
05.11.2015 22:55:51
Здравствуйте Николай.
Спасибо за урок по защите. Подскажите, как сделать, чтобы определенные защищенные ячейки при копирование в той же книге и листе сохранялись защищенные. Спасибо.
19.01.2016 12:33:28
Никак, кроме как макросом :(
18.01.2016 16:36:44
Здравствуйте, есть такой вопрос.Иемеется несколько десятков файлов (книг) каждую из них нужно запаролить и разослать адресатам.Подсакжите возможно ли запаролить все эти листы (у каждого листа будет свой уникальный пароль)сразу не заходя в каждый лист?
19.01.2016 12:32:36
Александр, запаролить оптом несколько листов (или книг) точно без макроса не получится. Массовая защита листов есть в надстройке PLEX, а вот оптовую защиту файлов придется программировать на VBA.
19.01.2016 20:40:33
Ясно,Николай, а вы можете сюда пример макроса вписать?
30.01.2016 15:32:37
Николай, добрый день! Если возможность сделать, так, что бы файл нельзя было изменить после определенной даты, но при этом, изменения можно делать, допустим зная пароль? Заранее благодарен за ответ и помощь.
21.03.2016 21:43:14
Здравствуйте. Подскажите, пожалуйста, как разрешить определенным (выборочным из некоторых) пользователям доступ к некоторым листам книги только в определенный день (например текущий и два предыдущих) месяца и при этом сделать остальные листы невидимыми ? Например :
есть 4-ро пользователей. Первый - главный (может всегда видеть и редактировать все), второй - менеджер (может видеть также всегда только часть листов и только их редактировать), третий - бухгалтер ( может видеть все и всегда, но ничего редактировать не может), четвертый - регистратура (доступ только к формированию первичных документов СЕГОДНЯШНЕГО дня и видит предыдущие два дня. и постоянный доступ без редактирования к некоторой базе данных)

Спасибо
31.03.2016 15:40:42
Добрый день!
Подскажите пожалуйста:
есть таблица с данными, часть таблицы нужно оградить от видимости, я сгруппировала данные и запоролила их.
но теперь группировкой по строкам пользоваться я тоже не могу.
можно ли поставить защиту на группировку столбцов, но сохранить возможность пользования группировкой по строкам:?:
заранее спасибо
08.06.2016 10:38:01
Насколько я знаю, нет :(
14.05.2016 19:20:47
Здравствуйте,
в столбце таблицы есть формула, которая протягивается на весь столбец, за исключением некоторых ячеек с константами, которые нельзя менять.
Но если я меняю формулу в одной из ячеек, мне предлагает изменить её и во всём столбце ("Заполнить все ячейки данного столбца указанной формулой"), в том числе и в тех ячейках с константами. Как это можно предотвратить, как их защитить от автозамены?
10.06.2016 09:25:21
Повторю свой вопрос:
Есть колонка в таблице, в которой стоит одинаковая формула во всех ячейках за исключением некоторых, в которых должны стоять константы. Эти некоторые ячейки защищены от изменения.
Теперь, когда я изменяю формулу в одной из ячеек, мне предлагает "Заполнить все ячейки данного столбца указанной формулой", на что у меня есть два варианта действий:
1. Защищаю лист от изменений и соглашаюсь заполнить ячейки формулой - выдаёт ошибку (предупреждение) о защите, и изменения не происходят.
2. Не защищаю лист от изменений и соглашаюсь заполнить ячейки формулой - меняет на новую формулу во всём столбце, в том числе и в защищённых ячейках, изменяя константу на формулу.

Протяжка формулы также не помогает на защищённом листе.


Как можно автозаполнять столбец формулой, не изменяя защищённые ячейки???
17.05.2016 08:34:28
Хочу сказать СПАСИБО за интересные уроки !!!
08.06.2016 10:38:16
Всегда пожалуйста :)
03.06.2016 11:14:21
Очень нужна помощь!
Вопрос следующий. Есть сводная таблица с несколькими фильтрами. В одном из них директора регионов и их показатели. Можно как-то защитить фильтр, чтобы они не могли смотреть сводные данные других коллег, а могли фильтром выбрать только себя?
08.06.2016 10:37:22
Людмила, можно сделать разные копии сводной на разных листах для каждого директора отдельно (вкладка Анализ - Параметры - Отобразить страницы фильтра отчета), а потом превратить каждую сводную в значения (копированием и вставкой значений или с помощью макроса).
В любом другом случае гарантии никакой - защита в Excel слабоватая.
Добрый день. Есть ли возможность на защищенном листе пользоваться фильтрами?
08.06.2016 10:32:38
Наталья, когда включаете защиту установите флажок Использование автофильтра. Ну, и сам фильтр не забудьте включить заранее (Данные - Фильтр).
Николай,спасибо.Все получилось. Вот правда,когда знаешь,все просто)).
19.07.2016 08:22:18
10.08.2016 18:00:58
Добрый день!
Во-первых, хочу поблагодарить за сайт, мастер-класс, PLEX и всё остальное!:)

Во-вторых, вопрос, который, возможно лучше открыть на форуме отдельной темой::?:
В защищенный лист с проверкой данных вставляют скопированные с других листов неверные данные.
"Проверка" их не обводит, сообщение об ошибке не выдаёт, всё потому что лист защищён (собственно, от того, чтобы эту проверку не убрали.
Как исключить ввод неверных данных копированием?
Заранее благодарю!
P. S. в инете искал ответ, не нашёл.
16.09.2016 15:40:25
Добрый день, нужна ваша помощь. Ставлю защиту на лист, а один столбец никак не защищается, что делать?
30.09.2016 17:02:57
Здравствуйте,

такая ситуация: 2 связаных excel таблица1 и таблица2. В таблица1 список контрагентов из нее в таблица2 этот список клиентов обновляется. Потом их из таблицы2 берут их идентификатор и дальше с ним работают в других базах данных. Пользователь может в таблица2 редактировать только один столбец, чтобы случайно не изменил данные контрагента. Лист заблокирован, но тогда не проходит обновление данных из связанной таблицы.

ВОПРОС: как настроить синхронихацию таблиц в данных условиях и если нужно это сделать макросом, то какому событию отвечает нажатие кнопки "Обновить все". Там есть метод WorkBook.RefreshAll, но перед ним нужно разблокировать лист а потом заблокировать снова.

Спасибо
18.10.2016 11:07:12
Здравствуйте!

Возникла такая же проблема при работе с умной таблицей.
При копировании ячеек в диапазон с проверкой данных, проверка данных перестаёт работать, так как вставляемая ячейка вставляется вместо ячейки, которая была с проверкой. Ситуация не улучшается, если копировать только значения.
На сайте support.office есть такой текст:

"Если проверка данных не работает, проверьте указанные ниже условия.
  • Пользователи не копируют и не вставляют данные. Проверка данных предназначена для отображения сообщений и предотвращения ввода недопустимых данных только в том случае, если пользователь вводит данные непосредственно в ячейку. Если данные копируются или вставляются, сообщения не отображаются. Чтобы предотвратить копирование и заполнение данных путем перетаскивания ячеек, выберите Файл > Параметры >Дополнительно > Параметры редактирования, снимите флажок Разрешить маркеры заполнения и перетаскивание ячеек, а затем защитите лист."
1-й Вопрос: что имеется ввиду под "затем защитите лист"? Что именно защитить? Как быть, если ввод данных вручную необходимо сделать возможным, а копирование запретить.
2-й Вопрос: как я понимаю из обсуждения выше в ленте также следует, что проверять copy / paste проверкой данных шансов нет, правильно?
11.01.2017 23:17:41
Подскажите пожалуйста, как решить следующую проблему?
После защиты листа при открытии книги после сохранения не возможно менять цвет шрифта в незащищённых ячейках.
22.01.2017 14:22:16
Здравствуйте!
Как защитить лист от того чтобы не видна было что на нём или запретить и вовсе вход на листе ?
Спасибо.