Методические указания для выполнения практического задания № 7 «Организация зашиты файлов средствами МS Excel»

Цель работы: освоить технологию организации защиты файлов средствами электронной таблицы MS Excel.

Задание: Создать книгу Excel, со­держащую данные о сотрудниках, подразделениях организации и статистическую информацию по ним, при этом:

• внесение изменений и просмотр данных о денежных начислениях разрешенных только пользователям с высоким уровнем доступа (HIGH);

• просмотр прочих детальных данных о сотрудниках и подразделениях доступен пользователям со средним уровнем доступа (MEDIUM);

• пользователи с низким уровнем доступа (LOW) имеют доступ лишь к статистической информации о подразделениях организации.

Microsoft Excel обладает следующими возможностями за­щиты:

• Ограничение доступа к отдельным элементам листа.

• Ограничение возможности изменений для всей книги.

• Ограничение доступа к книге с помощью пароля, запрашивае­мого при открытии или сохранении книги, либо открытие кни­ги посторонними в режиме «только для чтения».

Задание 1. Создать незащищенную книгу Excel.

1. Переименовать Лист 1 в "Статистика", а Лист 2 в "Ведомость". Удалить Лист 3. Для этого щелкнуть правой кнопкой мыши по названию листа и выбрать соответствующее действие из контекстного меню.

2. На листе "Ведомость" создать таблицы следующего вида:

• В интервале ячеек А1:D11 создать таблицу, содержащую сведения о сотрудниках:

Сводная ведомость
Фамилия	Разряд	Код подразделения	Начислено
Алексеева
Иванов
Петров
Сидоров
Смирнова
Кукушкин
Белова
Давыдов
Семенов

• Высчитать начисления в зависимости от разряда. Базовому разряду (десятому) начисляется 1300 руб. За каждую разрядную единицу сверх 10 начисляется по 110 руб. От полученной суммы берется 71 процент. Таким образом, для первой строки данных формула для расче­та начислений будет иметь вид: =0,71*(1300+110*(ВЗ-10)). Занести эту формулу в ячейку DЗ. Скопировать ее на диапазон D3:D11

• В диапазоне ячеек А13:С17 создать таблицу, содержащую данные о подразделениях организации:

Данные о подразделениях
Код	Наименование	Руководитель
	Общий отдел	Иванов
	Отдел снабжения	Сидоров
	Бухгалтерия	Белова

3. На листе «Статистика» создать форму вывода статистических данных.

· В ячейку ВЗ занести текст "Ведите код отдела:", в ячейку В6 -"Наименование подразделения:", в ячейку В8 -"Руководитель:", в ячейку В10 - "Численность сотрудников:". Текст ячеек В6, В8 и B10 выделить жирным.

· В ячейку ЕЗ будет заноситься код подразделения. Если код не введен или определен неправильно, в ячейке В4 будет выдаваться подсказка "(может быть равен 1, 2 или 3)". Для этого в ячейку В4 занести формулу:

=ЕСЛИ(ИЛИ(ЕЗ=1;ЕЗ=2;ЕЗ=3);"";"(может быть равен 1, 2 или 3)")

· Если код подразделения указан верно, в ячейке Е6 будет выдаваться наименование подразделения. Для этого ввести в ячейку Е6 формулу:

=ЕСЛИ(ИЛИ(ЕЗ=1;ЕЗ=2;ЕЗ=3);ВПР(ЕЗ; Ведомость! А15:С17;2;ЛОЖЬ)””)

· Если код подразделения указан правильно, то в ячейке Е8 будет выдаваться фамилия руководителя подразделения. Для этого ввести в ячейку Е8 формулу:

=ЕСЛИ(ИЛИ(ЕЗ=1;ЕЗ=2;ЕЗ=3);ВПР(ЕЗ; Ведомость! А15:С17;3; ЛОЖЬ);””)

· Если код подразделения указан правильно, то в ячейке Е10 будет выдаваться численность сотрудников подразделения. Для этого ввести в ячейку Е10 формулу:

=ЕСЛИ(ИЛИ(ЕЗ=1;ЕЗ=2;ЕЗ=3);СЧЁТЕСЛИ(Ведомость! СЗ:С10;Е3);””)

· Выделить ячейки Е6, Е8 и Е10 жирным курсивом, шрифт 11 кегль.

· Выделить диапазон ячеек А5:С11 желтым цветом, диапазон А1:С4 - зеленым, ячейку ЕЗ - оранжевым.

· Сохранить книгу под именем "Организация".

Задание 2. Защитить необходимые элементы листов "Статистика" и "Ведомость" от изменений и просмотра.

4. На листе "Ведомость" скрыть столбец с данными о начисле­ниях. Для этого установить курсор на любую ячейку столбца D и выполнить команду Формат |Столбец | Скрыть

5. Защитить лист "Ведомость". Для этого в окне команды Сервис |Защита установить флажки для всех строк группы Защитить.листы в отношении и ввести пароль НIGН. Нажать ОК, еще раз ввести пароль НIGН в появившемся окне подтверждения пароля и нажать ОК.

6. На листе "Статистика" сделать доступным внесение данных в ячейку ЕЗ после установки защиты. Для этого установить курсор на ячейке ЕЗ, в окне команды Формат |Ячейки выбрать вкладку Защита, снять флажок параметра Защищаемая ячейка и нажать ОК.

7. Скрыть формулы. Выделить несмежные ячейки В4, Е6, Е8 и Е10, для чего щелкнуть по ним мышью, удерживая клавишу CTRL. В окне команды Формат |Ячейки выбрать вкладку Защита, установить флажок параметра Скрыть формулы и нажать кнопку ОК.

8. Установить защиту листа "Статистика" с паролем НIGН.

9. Проверить возможность внесения изменений в ячейку ЕЗ и отсутствие такой возможности для остальных ячеек.

Задание 3. Ограничить возможность изменений структуры книги "Организация".

10. Скрыть лист "Ведомость": перейти на лист "Ведомость" и выполнить команду Формат |Лист | Скрыть.

11. Изменить размер окна книги так, чтобы была видна только выделенная цветом часть листа "Статистика". Расположить окно книги в центре экрана.

12. Установить защиту на изменение структуры книги и изменение размеров окна. В окне команды Сервис|3ащита|3ащитить книгу в группе Защитить книгу установить флажки параметров структуру и окна, ввести пароль MEDIUM в группе пароль и в окне подтверждения пароля.

Задание 4. Задать пароль на открытие книги "Организация".

В окне команды Файл |Сохранить как нажать кнопку-меню Сервис и выбрать пункт Общие параметры

• В появившемся окне Параметры сохранения ввести пароли LOW в строке пароль для открытия файла и HIGH в строке пароль разрешения записи соответственно. Нажать ОК.

• В том же порядке ввести пароли в окнах подтверждения.

• В окне Сохранение документа нажать кнопку Сохранить.

• Закрыть книгу"Организация" с помощью команды Файл|3акрыть.

Задание 5. Просмотреть информацию, содержащуюся в защищенной книге "Организация", последовательно применяя пароли разных уровней доступа.

14. Загрузить книгу "Организация" с низким уровнем доступа.

• Открыть книгу "Организация". В окне ввода пароля доступа к защищенной книге ввести пароль LOW.

• В окне ввода пароля для разрешения записи пароль не вводить, а щелкнуть на кнопке Только для чтения.

• Проверить невозможность изменения размеров и местоположения окна книги, невозможность просмотра других листов (команда Формат | Лист | Отобразить не доступна).

15. Просмотреть детальную информацию с листа "Ведомость", используя средний уровень доступа.

• Снять защиту вида окна и структуры книги. Для этого выполнить команду Сервис|3ащита|Снять защиту книги и ввести пароль MEDIUM.

• Развернуть окно книги на весь экран.

• Для просмотра листа "Ведомость" в окне команды Формат | Лист | Отобразить выбрать "Ведомость" и нажать ОК.

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

• Перейти на лист "Ведомость".

• Проверить невозможность изменения содержимого листа, просмотра скрытых данных (столбца с суммами начислений) команда Формат |Столбец | Отобразить не доступна.

• Снять защиту листа, выполнив команду Сервис |Защита |Снять защиту листа и введя пароль HIGH.

• Отобразить скрытый столбец D. Для этого выделить две любые смежные ячейки столбцов С и Е и выполнить команду Формат |Столбец |Отобразить.

17. Сохранить книгу MS Excel. Появится сообщение, что файл был открыт только для чтения и может быть сохранен под другим именем.

• Ввести новое имя "Организация _ защита _ снята",

• нажать кнопку Сервис и выбрать пункт Общие Параметры, в появившемся окне окна "Параметры сохранения" удалить имеющийся пароль на открытие файла, выйти из окна, нажав ОК,

• и сохранить книгу под новым именем, нажав кнопку Сохранить в окне "Сохранение документа"'.

18. Показать книги «Организация» и «Организация _ защита _ снята» преподавателю.