Захист листів і осередків в MS Excel

1. Пошук по мітках

Наша взаимовыгодная связь https://banwar.org/

Якщо ви працюєте (або працювали) в більш-менш великій компанії, то 100% стикалися з ситуацією, коли необхідно переслати свій файл іншого співробітника і при цьому заборонити йому змінювати якісь дані (найчастіше це осередки з формулами, які користувач ні в жодному разі не повинен міняти, а в ідеалі навіть бачити. бачити він повинен тільки результат обчислень цих формул). У той же час треба залишити можливість при необхідності змінити будь-які осередки або дані, але виключно після введення пароля. Все це можна зробити за допомогою стандартної захисту листів в Excel.

Установка захисту листів
Щоб захистити лист необхідно перейти на вкладку Рецензування (Review) -група Зміни (Changes) - Захистити лист (Protect sheet).
в Excel 2003 - Сервіс - Захист - Захистити лист.
для версій Excel 2010 і вище так само можна натиснути правою кнопкою миші на ярлику потрібного аркуша і вибрати Захистити лист (Protect sheet)
Після натискання з'явиться вікно:

У першому полі Пароль для відключення захисту аркуша необхідно вказати пароль, який буде використовуватися для зняття захисту з аркуша в разі потреби. Якщо поле залишити порожнім, то для зняття захисту з листа не потрібно вводити пароль і захист зможе зняти кожен, хто знає як знімати захист. Захист без пароля як правило застосовується у випадках, коли файлом користуються виключно компетентні користувачі, які віддають собі звіт в тому, які клітинки треба змінювати, а які не чіпати. Захист в цьому випадку призначена лише від випадкових невірні дії.
Друге поле кілька хитріший і має безліч пунктів, які можна відзначити галочками. Пункти перераховують дії, які будуть дозволені користувачеві після установки захисту на лист. Це означає, що якщо встановити галочку на пункт "вставку стовпців", то навіть після встановлення захисту на аркуші буде доступна можливість додавання нових стовпців без зняття захисту з аркуша. За замовчуванням при першій установці захисту галочки встановлені тільки на перших двох пунктах (виділення заблокованих осередків і виділення незаблокованих осередків). Більшість пунктів досить красномовні і не потребують докладне пояснення, але на деяких варто зупинитися трохи докладніше. Після установки захисту користувачеві так само буде дозволено (якщо навпроти пункту встановлено прапорець):

• виділення заблокованих осередків (Select locked cells) - дозволено виділяти осередки, для яких встановлено атрибут Захищається ланка (права кнопка миші на осередку / діапазоні - Формат ячеек (Format cells) вкладка Захист (Protection) - Захищається ланка (Locked)). Якщо відзначити цей пункт, то пункт виділення незаблокованих осередків буде відзначений автоматично, тому що якщо дозволено виділення заблокованих осередків, то звичайно, повинно бути дозволено виділяти і незаблоковані.
• виділення незаблокованих осередків (Select unlocked cells) - буде дозволено виділяти тільки ті осередки, для яких атрибут Захищається ланка не встановлено. Застосовується разом з відключенням пункту виділення заблокованих осередків, щоб заборонити йому після установки захисту навіть виділяти заборонені до зміни осередку. Таким чином користувач буде змушений переміщатися тільки по тим осередкам, які йому можна змінювати. Детальніше про застосування властивості "Захищається ланка" можна ознайомитися в цій статті: Як дозволити змінювати тільки вибрані комірки?
• форматування осередків (Format cells) - буде дозволено змінювати формати осередків: колір заливки, колір шрифту, розмір шрифту, ім'я шрифту, межі, відступи і т.п.
• форматування стовпців (Format columns) - незважаючи на кшталт зрозумілу назву при установці дозволяє змінювати ширину стовпців. При цьому, якщо пункт форматування осередків не встановлено, то змінювати колір шрифту, заливки і т.п. буде заборонено
• форматування рядків (Format rows) - так само як і у випадку з пунктом форматування стовпців при установці дозволяє змінювати висоту рядків, але при цьому неможливо змінювати колір шрифту, заливки і т.п., якщо пункт форматування осередків не встановлено
• вставку стовпців (Insert columns) - дозволяє вставку цілих стовпців (вставляти окремі осередки при цьому заборонено)
• вставку рядків (Insert rows) - дозволяє вставку цілих рядків (вставляти окремі осередки при цьому заборонено)
• вставку гіперпосилань (Insert hyperlinks) - дозволяє створення гіперпосилань на аркуші ( Що таке гіперпосилання? ). Правда, при цьому створити гіперпосилання можна буде виключно в незаблокованих осередках.
• видалення стовпців (Delete columns) - дозволяє видалення цілих стовпців. При цьому видалення стовпців допускається тільки в тому випадку, якщо стовпець не містить заблокованих осередків. Якщо хоч одна осередок в стовпці з атрибутом "Захищається ланка", то видалення стовпця неможливо. Так само неможливо видаляти окремі осередки всередині стовпців, навіть якщо все вічка не заблоковані
• видалення рядків (Delete rows) - дозволяє видалення цілих рядків. При цьому видалення рядків допускається тільки в тому випадку, якщо рядок не містить заблокованих осередків. Якщо в рядку є хоч один осередок з атрибутом "Захищається ланка", то видалення рядка неможливо. Так само неможливо видаляти окремі осередки всередині рядків, навіть якщо всі осередки в рядку не заблоковані
• сортування (Sort) - один з "хитрих" пунктів. Хоч сам пункт сортування активний і доступний для виклику, сама сортування при цьому дозволена тільки в тому випадку, якщо всі осередки всередині сортується діапазону не заблоковані. Якщо всередині діапазону буде хоч одна заблокована осередок (з атрибутом "Захищається ланка"), то сортування буде неможлива
• використання автофільтра (Use Autofilter) - теж "хитрий" пункт. Як випливає з опису допускається тільки використання інтелектуального фільтра. Це означає, що якщо автофильтр вже встановлений на аркуші, то після захисту його можна буде використовувати для добору даних. Однак якщо фільтр не був встановлений до установки захисту на лист - то встановити фільтр буде вже неможливо без зняття захисту
• використання звітів зведеної таблиці (Use PivotTable reports) - при установці буде можливо використовувати зведену таблицю для аналізу даних: переміщати поля всередині зведеної таблиці, відбирати і фільтрувати дані. Однак неможливо при цьому буде змінити джерело даних, оновлювати зведену, змінювати функції полів, додавати обчислювані поля, прибирати і додавати проміжні підсумки, змінювати макет звіту, стилі і т.п.
• зміна об'єктів (Edit objects) - буде можливо додавати, виділяти і навіть видаляти об'єкти на аркуші, а так само змінювати їх розміри і більшість властивостей (кольору кордонів, заливки, ефекти світіння і стилів та ін.). До об'єктів в даному випадку відносяться Фігури (Shapes), Малюнки (Pictures), об'єкти SmartArt, Діаграми (Charts)
• зміна сценаріїв (Edit scenarios) - якщо до установки захисту були створені сценарії (Дані (Data) - Аналіз Що-якщо (What-If Analysis) - Диспетчер сценаріїв (Scenario manager)), то після установки захисту їх можна буде змінювати.

Після установки потрібних параметрів і натискання ОК:

• якщо пароль не вказано, то на лист буде встановлений захист без пароля з зазначеними параметрами
• якщо був вказаний пароль, то перед захистом з'явиться ще одне вікно, в якому буде запропоновано підтвердити пароль. Там єдине поле, в яке треба просто ввести той же пароль, що і в першому вікні. При установці пароля слід пам'ятати, що регістр букв розрізняється (А і а - будуть вважатися різними символами), а якщо вказати пароль російськими буквами, то при відкритті файлу на ПК під управлінням MAC OS можливі помилки перетворення даних і зняти захист встановленим паролем буде неможливо. Тому краще застосовувати символи англійського алфавіту, цифри і доп.сімволи (! @ # $% ^ & *)

Якщо після установки захисту користувач повинен мати можливість виділяти все осередки на аркуші, але так само необхідно заборонити йому доступ до перегляду формул, то перед установкою захисту в потрібних комірках необхідно виконати наступне: виділяємо всі необхідні осередки -права кнопка миші - Формат ячеек (Format cells ) вкладка Захист (Protection). Встановлюємо прапорець на пункті Приховати формули (Hidden) (найчастіше використовується разом з установкою галочки на Захищається ланка (Locked)). Після цього встановлюємо захист.

Так само можна захищати тільки окремі осередки на аркуші: Як дозволити змінювати тільки вибрані комірки? Для чого це потрібно? Як правило застосовується у випадках, якщо у файлі застосовуються формули і розрахунки на підставі введених користувачем даних. При цьому користувач не повинен мати доступ до осередків з формулами, але повинен мати можливість змінювати дані певних осередків, від яких залежать самі формули і їх результат.

Якщо у файлі присутній угруповання або структура (Дані (Data) - Групувати (Group)), то її використання буде неможливо на захищеному аркуші. Вона буде доступна тільки в тому вигляді, в якому була до установки захисту. Хоча тут теж є лазівка, але вже тільки із застосуванням Visual Basic for Applications (VBA - вбудований в MS Office мова програмування): Як залишити можливість працювати з угрупованням / структурою на захищеному аркуші?

Якщо у файлі використовуються макроси або файл передбачається обробляти макросами, то в макросах необхідно передбачити зняття кодом захисту перед будь-якими змінами на аркуші і подальшу установку захисту після всіх необхідних дій. Детальніше можна прочитати в статті: Як захистити лист від користувача, але не від макросу?

Зняття захисту з аркуша
Щоб зняти захист з листа необхідно перейти на вкладку Рецензування (Review) -група Зміни (Changes) - Зняти захист аркуша (Unprotect sheet). Якщо лист був захищений без пароля, то захист буде знята відразу. Якщо лист був захищений із зазначенням пароля, то з'явиться вікно із запитом пароля

в це поле необхідно ввести пароль і натиснути Ок. Якщо пароль невідомий або був забутий, то стандартно зняти захист з листа буде вже неможливо.

Наскільки стійка захист листів в Excel
На жаль або на щастя захист листів в Excel абсолютно не стійка до злому. Захист з листа, якщо пароль не відомий, знімається на раз-два навіть за допомогою VBA. У надбудові MulTEx є спеціальна команда, яка допоможе зняти захист з листа, якщо пароль був забутий або загублений: Зняти захист з аркуша (без пароля) .
Але варто враховувати той факт, що захист листів від самого початку не планувалася як засіб захисту своїх розрахункових алгоритмів і інтелектуальної власності. Захист листів (як і книг) замислювалася як захист "від дурня" - тобто щоб випадково або через невміння дані не були зіпсовані або видалені.
Плюс Microsoft все ж удосконалює Excel і з виходом нових версій відбуваються зміни і в області захисту, що не може не радувати. Наприклад, захист листів і книг починаючи з версії Excel 2013 вже стійкіша (для тих хто в темі: алгоритм SHA-512 в 2013 і вище проти SHA1 в ранніх версіях). Це означає, що простим брутфорсом поламати такий захист хоч і можна, але часу на це піде вже набагато більше. Хоча для зняття захисту з листів у відкритих форматах (.xlsx, .xlsm і їм подібних) можливо і іншими методами.

Також див .:
Захист листів / зняття захисту
Зняти захист з аркуша (без пароля)
Як дозволити змінювати тільки вибрані комірки?
Як захистити лист від користувача, але не від макросу?
Як залишити можливість працювати з угрупованням / структурою на захищеному аркуші?

{ "Bottom bar": { "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1" 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; position: relative; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Пошук по мітках