Цель работы: изучить способы и получить практические навыки защиты книг паролем в табличном процессоре MS Excel 2010.
Порядок выполнения работы
1. Изучить теоретическую часть.
2. Выполнить задания.
3. Представить файлы для проверки преподавателю.
В MS Excel 2010 предусмотрено несколько уровней защиты, позволяющих управлять доступом к документам (рис. 1.2.1):
— пометить как окончательный. Это помогает пользователю сообщить о том, что он предоставляет для совместного использования окончательную версию документа. Кроме того, это позволяет предотвратить внесение в документ случайных изменений рецензентами или читателями;
— зашифровать паролем. Это позволяет ограничить доступ к документу, предоставив его только «доверенным» пользователям.
Пароль — способ ограничения доступа к книге, листу или части листа. В MS Excel длина пароля не должна превышать 255 букв, цифр, пробелов и других символов. При вводе пароля учитывается регистр букв;
— защитить текущий лист. Это позволяет включить защиту паролем, чтобы разрешить или запретить пользователям выделять, форматировать, вставлять, удалять, сортировать и редактировать области таблицы;
— защитить структуру книги. Это позволяет заблокировать структуру книги, чтобы пользователи не могли добавлять или удалять листы или отображать скрытые листы. Это также позволяет запретить пользователям изменять размер или положение окон листа. Защита структуры и окна книги распространяется на всю книгу;
Рис. 1.2.1. Вкладка Файл / Сведения
— ограничить разрешения для пользователей. Для ограничения разрешений Excel позволяет использовать идентификатор Windows Live ID или учетную запись MS Windows;
— добавление цифровой подписи. Цифровые подписи используются для проверки подлинности цифровых данных, например документов, сообщений электронной почты и макросов, с помощью криптографии. Они создаются путем ввода или на основе изображения и позволяют обеспечить подлинность, целостность и неотрекаемость.
Все уровни защиты являются не взаимоисключающими, а скорее взаимодополняющими друг друга.
Задания
1. Построить таблицу вычисления зарплаты с учетом прогрессивного налога и числа детей. Положим, что с месячного дохода до 6000 руб. налог не удерживается, от 6000 руб. удерживается в сумме 13%, от 20 000 руб. — 15%, от 50 000 руб. — 20%, от 100 000 руб. — 30%.
Налог удерживается не со всей суммы заработка, а с величины, меньшей на один минимальный размер оплаты труда (МРОТ) на каждого ребенка (иждивенца):
SumObl = D — KolD ´ MROT,
где SumObl — сумма обложения, D — доход, KolD — количество детей, MROT — минимальный размер оплаты труда (МРОТ в России с 1 января 2015 г. составляет 5965 руб.).
Примечание: в каждом регионе России устанавливается региональныйМРОТ, его величина приближена к общероссийскому, но в некоторых субъектах РФ он значительно выше. По данным на 2014 г. в Москве он составляет 12 200 руб., на Сахалине — 11 490 руб., в Ямало-Ненецком автономном округе — 11 171 руб.
Если доход мал, а детей много, то сумма обложения может оказаться отрицательной. В этом случае она принимается равной 0, и налог не удерживается. Процент налога находится с помощью функции поиска ГПР(), определяющей ближайшее меньшее к облагаемой сумме. Тогда
Nalog = PrNalog ´ SumObl,
где Nalog — величина налога, PrNalog — процент налога, SumObl — сумма обложения.
Справка: функция ГПР() выполняет поиск значения в первой строке таблицы или массива (Массив. Объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.) значений и возвращает значение, находящееся в том же столбце в заданной строке таблицы или массива. Функция ГПР() используется, когда сравниваемые значения расположены в первой строке таблицы данных, а возвращаемые — на несколько строк ниже. Если сравниваемые значения находятся в столбце слева от искомых данных, следует использовать функцию ВПР. Буква Г в аббревиатуре «ГПР» означает «горизонтальный».
Синтаксис функции ГПР():
ГПР (искомое_значение, таблица, номер_строки, [интервальный_просмотр]).
Для решения поставленного задания выполните действия:
1. Запустите Excel. Сохраните файл под именем «Ваша фамилия2_1», например Иванов2_1.
2. Переименуйте Лист1, дав ему имя Прогрессивный подоходный налог.
3. Составьте таблицы, представленные на рис. 1.2.2.
Рис. 1.2.2. Исходные таблицы (задание 1)
Особенности форматирования:
— ячейки B1:G1 и A5:H5 объединены. В ячейках C6, E6, F6 и H6 включен режим Перенос по словам;
— в ячейках C2:G2, C4 и D7:D17 установлен денежный формат;
— в ячейках C3:G3 установлен процентный формат, число десятичных знаков — 0;
— для ячеек B2:B4 и A6:H6 выбрана заливка Светло-коричневый.
4. Для нахождения Суммы обложения в ячейку E7 введите формулу =ЕСЛИ (D7<C7*$C$4; 0; D7-C7*$C$4).
5. Скопируйте формулу из E7 в ячейки E8:E16.
6. Для нахождения Процентной ставки в ячейку F7 введите формулу =ГПР (E7; $C$2:$G$3; 2).
7. Скопируйте формулу из F7 в ячейки F8:F16.
8. Для нахождения Налога в ячейку G7 введите формулу =D7*F7.
9. Скопируйте формулу из G7 в ячейки G8:G16.
10. Для нахождения Суммы к выдаче в ячейку H7 введите формулу =D7-G7.
11. Скопируйте формулу из H7 в ячейки H8:H16.
12. Для нахождения Итога в ячейку C17 введите формулу =СУММ(C7:C16).
13. Аналогичным образом заполните ячейки D17, E17, G17 и H17. Результаты вычислений представлены на рис. 1.2.3.
Рис. 1.2.3. Результаты вычислений (задание 1)
Особенности форматирования
— в ячейках E7:E17, G7:H17 установлен денежный формат;
— в ячейках F7:F16 установлен процентный формат, число десятичных знаков — 0.
2. Защитите книгу паролем.
Для этого выполните действия:
1. Для открытой книги выберите вкладку Файл / Сведения / Защитить книгу / Зашифровать паролем.
2. В окне Шифрование документа (рис. 1.2.4) введите пароль. Нажмите кнопку ОК.
Примечание: при вводе пароля следует строго следить за регистром и раскладкой клавиатуры нажатие на одни и те же клавиши клавиатуры в русской и английской раскладке вводит различные символы. Убедитесь в том, что при первом вводе пароля не нажата клавиша [CAPS LOCK].
Рис. 1.2.4. Ввод и подтверждение пароля
3. В окне Подтверждение пароля (рис. 1.2.4) введите пароль еще раз и нажмите кнопку ОК.
Примечание: пароль начнет действовать после сохранения и закрытия файла. В случае утраты пароля приложению MS Excel не удастся восстановить данные. При открытии защищенного файла или снятии защиты выводится окно для ввода пароля, в котором необходимо ввести пароль. В случае неправильного ввода пароля выводится соответствующее сообщение. Следует нажать кнопку ОК и попытаться ввести правильный пароль.
4. Сохраните файл и закройте MS Excel.
5. Откройте текстовый редактор Блокнот, введите придуманный вами пароль. Сохраните файл под именем «Ваша фамилия21_пароль», например, Иванов21_пароль. Закройте Блокнот.
6. Проверьте успешность защиты книги паролем. Для этого:
— откройте вашу книгу и введите неверный пароль в окне Введите пароль (рис. 1.2.5);
Рис. 1.2.5. Окно Введите пароль
— в ответ на ввод неправильного пароля проявить ошибка «Неверный пароль» (рис. 1.2.6). Нажмите кнопку ОК и закройте книгу;
Рис. 1.2.6. Сообщение об ошибке
— откройте вашу книгу и введите верный пароль в окне Введите пароль (рис. 1.2.5). При правильности ввода книга будет успешно открыта.
3. Снимите пароль, установленный через Защиту документа.
Для этого выполните действия:
1. Для открытого документа выберите вкладку Файл / Сохранить как.
2. Введите имя файла «Ваша фамилия2_2», например Иванов2_2.
3. Для открытого документа с именем «Ваша фамилия2_2» выберите вкладку Файл / Сведения / Защитить книгу / Зашифровать паролем.
4. В окне Шифрование документа (рис. 1.2.4) очистите поле пароля. Нажмите кнопку ОК.
Примечание: отказ от пароля начнет действовать после сохранения и закрытия файла.
5. Сохраните файл и закройте MS Excel.
4. Установите пароль на книгу при сохранении файла.
Для этого выполните действия:
1. Откройте файл с именем «Ваша фамилия2_2», например Иванов2_2. Файл должен открывать без ввода пароля.
2. Выберите вкладку Файл / Сохранить как.
3. В окне Сохранение документа нажмите кнопку Сервис и выберите команду Общие параметры (рис. 1.2.7).
Рис. 1.2.7. Окно Сохранение документа
4. В окне Общие параметры (рис. 1.2.8) в поле пароль для открытия введите пароль для открытия документа.
Рис. 1.2.8. Окно Общие параметры
Примечание: если вы хотите запретить свободное редактирование документа, в поле Пароль для изменения введите пароль для редактирования документа.
5. Установите флажок Рекомендовать доступ только для чтения для ограничения доступа к файлу (рис. 1.2.9). Нажмите кнопку ОК.
Рис. 1.2.9. Окно Общие параметры
6. В окне Подтверждение пароля (рис. 1.2.10) введите пароль еще раз и нажмите кнопку ОК.
Рис. 1.2.10. Окно Подтверждение пароля
7. Нажмите Сохранить в окне Сохранение документа. Нажмите кнопку Да в окне Подтвердить сохранение в виде (рис. 1.2.11).
Рис. 1.2.11. Окно Подтвердить сохранение в виде
8. Закройте MS Excel.
9. Откройте текстовый редактор Блокнот, введите придуманный вами пароль. Сохраните файл под именем «Ваша фамилия22_пароль», например, Иванов22_пароль. Закройте Блокнот.
10. Проверьте успешность защиты книги паролем. Для этого:
— откройте вашу книгу и введите верный пароль в окне Введите пароль (рис. 1.2.12);
Рис. 1.2.12. Окно Введите пароль
— при открытии документа выйдет запрос на способ открытия (рис. 1.2.13). Нажмите кнопку Нет, чтобы открыть файл в полнофункциональном режиме.
Рис. 1.2.13. Запрос на способ открытия
Примечание: для открытия файла в режиме только для чтения необходимо нажать кнопку Да.
5. Снимите пароль, установленный при сохранении файла.
Для этого выполните действия:
1. Для открытого документа выберите вкладку Файл / Сохранить как.
2. Введите имя файла «Ваша фамилия2_3», например Иванов2_3.
3. Для открытого документа с именем «Ваша фамилия2_3» выберите вкладку Файл / Сохранить как.
4. В окне Сохранение документа нажмите кнопку Сервис и выберите команду Общие параметры (рис. 1.2.7).
5. В окне Общие параметры очистите поля Пароль для открытия и Пароль для изменения (если он был установлен), снимите флажок Рекомендовать доступ только для чтения (рис. 1.2.9). Нажмите кнопку ОК.
6. Сохраните файл с заменой уже существующего (рис. 1.2.11) и закройте MS Excel.
7. Представьте преподавателю на проверку файлы с именами:
«Ваша фамилия2_1», например, Иванов2_1.xlsx;
«Ваша фамилия2_2», например, Иванов2_2.xlsx;
«Ваша фамилия2_3», например, Иванов2_3.xlsx;
«Ваша фамилия21_пароль», например, Иванов21_пароль.txt;
«Ваша фамилия22_пароль», например, Иванов22_пароль.txt.
Контрольные вопросы
1. Какие уровни защиты, позволяющие управлять доступом к документам, предусмотрены в MS Excel 2010?
2. Что такое пароль и каковы правила его формирования в MS Excel 2010?
3. Какое максимальное количество символов может содержать пароль в MS Excel 2010?
4. Как защитить структуру книги в MS Excel 2010?
5. Как защитить книгу от редактирования в MS Excel 2010?