Защита элементов листа от просмотра

 

Скрытие строк и столбцов. Столбцы и строки листа можно скрыть. Однако, этого недостаточно для защиты этих диапазонов от просмотра. После скрытия столбцов и/или строк необходимо защитить лист от изменений. Среди защищаемых параметров обязательно должны быть указаны: выделение заблокированных ячеек; выделение незаблокированных ячеек; форматирование столбцов; форматирование строк. Данные, находящиеся в скрытых столбцах и строках, остаются доступны для вычислений.

Отображение строк и столбцов. Перед отображением скрытых столбцов или строк необходимо снять защиту листа. Далее скрытые столбцы/строки отображаются обычным порядком.

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

1. Выделите ячейки со скрываемыми формулами.

2. Нажмите кнопку Формат в группе Ячейки вкладки Главная и выберите команду Формат ячейки или щелкните правой кнопкой мыши по выделенным ячейкам и в контекстном меню выберите команду Формат ячеек.

3. Во вкладке Защита окна Формат ячеек установите флажок Скрыть формулы.

Однако реально скрыты от просмотра формулы будут только на листе, защищенном от изменений. Уровень защиты листа значения не имеет.

Отображение формул. Для отображения формул достаточно снять защиту листа от изменений.

 

Защита от несанкционированного изменения

 

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

1. Нажмите кнопку Office, а затем выберите команду Сохранить как.

2. В окне Сохранение документа нажмите кнопку Сервис выберите команду Общие параметры.

3. В окне Параметры сохранения введите пароль в поле Пароль для изменения и нажмите кнопку ОК.

4. В окне подтверждения пароля введите его еще раз.

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

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

Снятие пароля. Снять пароль можно только после открытия файла.

1. Нажмите кнопку Office, а затем выберите команду Сохранить как.

2. В окне Сохранение документа нажмите кнопку Сервис выберите команду Общие параметры.

3. В окне Параметры сохранения очистите поле Пароль для изменения и нажмите кнопку ОК.

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

 

Защита листа

 

Защита всех ячеек листа. По умолчанию все ячейки листа являются защищаемыми. Однако перед установкой защиты следует проверить защищаемость ячеек.

1. Выделите все ячейки листа.

2. Нажмите кнопку Формат в группе Ячейки вкладки Главная и выберите команду Формат ячейки или щелкните правой кнопкой мыши по выделенным ячейкам и в контекстном меню выберите команду Формат ячеек.

3. Во вкладке Защита окна Формат ячеек должен быть установлен флажок Защищаемая ячейка.

Можно защитить как данные на листе, так и структуру листа.

1. В группе Изменения вкладки Рецензирование нажмите кнопку Защитить лист.

2. В окне Защита листа в поле Пароль для отключения защиты листа введите пароль, а в списке Разрешить всем пользователям этого листа установите флажки, разрешающие определенные действия. Перед установкой разрешений необходимо установить флажок Защитить лист и содержимое защищаемых ячеек и нажмите кнопку ОК.

3. В окне подтверждения пароля введите его еще раз.

При попытке выполнения на листе неразрешенных действий, появится окно сообщения.

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

1. Выделите ячейки, не подлежащие защите.

2. Нажмите кнопку Формат в группе Ячейки вкладки Главная и выберите команду Формат ячейки или щелкните правой кнопкой мыши по выделенным ячейкам и в контекстном меню выберите команду Формат ячеек.

3. Во вкладке Защита окна Формат ячеек снимите флажок Защищаемая ячейка.

4. Установите защиту лист обычным порядком.

 

Снятие защиты листа:

 

1. В группе Изменения вкладки Рецензирование нажмите кнопку Снять защиту листа.

2. В окне Снять защиту листа введите пароль и нажмите кнопку ОК.

Защита книги. Можно защищать от изменений структуру книги и окно книги. Под защитой структуры книги от изменений понимается запрещение действий с листами книги. Под защитой окна книги понимается запрет перемещения, изменения размеров, скрытие, показ и закрытие окон.

1. В группе Изменения вкладки Рецензирование нажмите кнопку Защитить книгу и выберите команду Защита структуры и окон.

2. В окне Защита книги установите флажок структуру для защиты структуры книги и флажок окна для защиты окон книги. В поле Пароль введите пароль и нажмите кнопку ОК.

3. В окне подтверждения пароля введите его еще раз.

При попытке выполнить с книгой недопустимое действие выйдет соответствующее сообщение.

 

Снятие защиты книги:

 

1. В группе Изменения вкладки Рецензирование нажмите кнопку Защитить книгу и выберите команду Защита структуры и окон.

2. В окне Снять защиту книги введите пароль и нажмите кнопку ОК.

 

 

Задание 2

1. Откройте файл Lesson_09.

2. Установите пароль на открытие файла.

3. Закройте файл Lesson_09.

4. Откройте файл Lesson_09. При открытии воспользуйтесь заданным паролем.

5. Перейдите к листу Лист 2.

6. Скройте лист от просмотра. Защитите книгу от изменений.

7. Перейдите к листу Лист 3.

8. Скройте от просмотра столбцы E и F. Защитите все ячейки листа от изменений.

9. Перейдите к листу Лист 4.

10.Все ячейки листа, за исключением ячейки В24 защитите от изменений. В ячейку В24 введите число 68.

Закройте Microsoft Excel 2007.

Упражнение 4

1. Запустите Microsoft Excel 2007.

2. Создайте новый файл.

3. Перейдите к листу Лист 1 (табл.39).

Таблица 39

4. Создайте диаграмму типа "гистограмма" для всей таблицы.

5. Перейдите к листу Лист 2. Скопируйте таблицу с листа Лист 1 на Лист 2, Лист 3, Лист 4.

6. На Листе 2 создайте диаграмму типа "гистограмма" для поставок за третий и четвертый кварталы.

7. Перейдите к листу Лист 3.

8. Создайте круговую диаграмму для поставок за второй квартал.

9. Перейдите к листу Лист 4.

10.Создайте график поставок товара "Джели" по кварталам.

11.Перейдите к листу Лист 2.

12.Добавьте на диаграмму название диаграммы, линии сетки, легенду и подписи данных. В названии диаграммы укажите Поставки 2010.

13.Переместите диаграмму к левому краю листа. Увеличьте размер диаграммы по горизонтали примерно в 1,5 раза.

14.Переместите легенду в левый верхний угол области диаграммы. Увеличьте высоту области построения так, чтобы верхняя граница области была максимально приближена к верхнему краю области диаграммы.

15.Для области диаграммы выберите один из стилей Слабый эффект. Для области построения установите заливку бледно-синим цветом. Для легенды установите темно-синий цвет шрифта и размер шрифта 12 пт. Для горизонтальной оси установите толщину линии 2,25 пт. Для горизонтальной оси установите синий цвет линии. Для вертикальной оси установите максимальное значение 100.

16.Сохраните файл под именем Lesson_11.

17.Закройте Microsoft Excel 2007.

Контрольные вопросы

 

1. Дать определение понятию «условное форматирование».

2. Какие виды условного форматирования вам известны?

3. Форматирование с использованием гистограмм.

4. Форматирование с использованием трехцветной шкалы.

5. Форматирование с использованием набора значков.

6. Какое меню содержит команду Условное форматирование?

7. Что такое фильтр?

8. Какие условия отбора используют в окне Пользовательский автофильтр?

9. Что такое примечание к таблице?

10. Какая вкладка используется для работы с примечаниями?

11. На чем основана защита информации от просмотра и изменения?

12. Существует ли в Excel стандартные средства для восстановления забытых паролей?

13. В чем заключается защита файлов?

14. В чем заключается защита листов?

15. В чем заключается защита отдельных элементов листа?

16. В чем заключается защита книг?

 

 

Список рекомендуемой литературы

 

1. Каменский П.А., Ремин А.А. Word 2007, Excel 2007 и электронная почта. Установка, настройка и результативная работа. Технический бестселлер, Издательство Триумф, М.: 2009.

2. Стоцкий Ю., Васильев А., Телина И. Office 2007. Изучаем самостоятельно, ПИТЕР, 2008.

3. Спиридонов О.А. Работа в Microsoft Excel 2007, ИНТУИТ.ру, 2008.

4. Информатика. Под редакцией Н.В.Макаровой. М.: «Финансы и статистика», 2007.

5. Комиссаров Д.А., Станкевич С.И. Персональный учитель по персональному компьютеру, М.: «Солон – Р», 2006.

 

Приложение 1

Итоговое проверочное задание

 

1. Создать новый файл в Microsoft Excel, ввести в него данные по одному из вариантов приложения 1 (см. ниже).

2. Выполнить форматирование данных.

3. Выполнить форматирование таблицы.

4. Произвести расчеты по финансовым функциям.

5. Произвести операции по текстовым функциям.

6. Произвести операции с функциями просмотра и ссылок.

7. Применить условное форматирования.

8. Выполнить фильтрацию данных.

9. Построить и отредактировать диаграммы.

10.Выполнить операции по защите от несанкционированного просмотра и изменения.

11.Распечатать выполненные задания для отчета.

12.Составить отчет по выполненному проверочному заданию (см. Приложение 2) и защитить его у преподавателя.

 

 

Вариант № 1

Самостоятельно произвести вычисления столбцов: ”Начислено”, ”Социальный налог 13%”, ”Сумма к получению ” по следую­щим формулам:

J4 = F4 * I4 /H4 K4= J4 * 0,13 L4 = J4 – K4.

С использованием автозаполнения заполните ячейки J5:J11; K5:K11; L5:L11.

Скопируйте таблицу на Лист 2. Вставьте диаграмму по собственному выбору.

Добавьте на диаграмму название диаграммы, линии сетки, легенду и подписи данных.

Переместите диаграмму к левому краю листа. Увеличьте размер диаграммы по горизонтали примерно в 2 раза.

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

Для области диаграммы выберите один из стилей Слабый эффект. Для области построения установите заливку бледно-синим цветом. Для легенды установите темно-синий цвет шрифта и размер шрифта 12 пт. Для горизонтальной оси установите толщину линии 1,5 пт. Для горизонтальной оси установите синий цвет линии. Для вертикальной оси установите максимальное значение 25000.

Защитите книгу от изменений.

Вариант № 2

 

Выполнить расчеты по следующим формулам: F2=D2*E2; скопируйте формулу для ячеек F3:F10; G2=(F2+F2*H2)/1000; скопируйте формулу для ячеек G3:G10;

G11=(F11+F11*H11)/1000; скопируйте формулу в ячейку G12. I2=F2/1000*C2; скопируйте формулу в ячейки I3:I12. I13=E13*C13; скопируйте формулу в ячейки I14:I17. J2=I2+I2*H2; скопируйте формулу в ячейки J3:J12. J13=I13; скопируйте формулу в ячейки J14:J17. В ячейке I18 рассчитайте итоговую цену.

В начало таблицы вставьте строку, объедините ячейки А1:J1. Введите заголовок «Расход материалов». Цвет шрифта – красный, цвет заливки для заголовка – зеленый, цвет линий для всей таблицы – темно-синий. Шрифт – Times New Roman, размер – 11пт. Для ячеек A2:J2 – стиль Заголовок2.

Создайте диаграмму по своему выбору.

Защитите лист от изменений

Вариант № 3

Выполнить расчеты по следующим формулам: F2=D2*E2; скопируйте формулу для ячеек F3:F11; G2=(F2+F2*H2)/1000; скопируйте формулу для ячеек G3:G11;

G12=(F12+F12*H12)/1000; скопируйте формулу в ячейку G13. I2=F2/1000*C2; скопируйте формулу в ячейки I3:I13. I14=E14*C14; скопируйте формулу в ячейки I15:I20. J2=I2+I2*H2; скопируйте формулу в ячейки J3:J13. J14=I14; скопируйте формулу в ячейки J15:J20. В ячейке I21 рассчитайте итоговую цену.

В начало таблицы вставьте строку, объедините ячейки А1:J1. Введите заголовок «Расход материалов». Цвет шрифта – серый, цвет заливки для заголовка – голубой, цвет линий для всей таблицы – темно-синий. Шрифт – Times New Roman, размер – 11пт. Для ячеек A2:J2 – стиль Заголовок2.

Создайте диаграмму по своему выбору. Защитите лист от изменений.

Вариант № 4

 

Выполнить расчеты по следующим формулам: F9=E9+E9*7%; скопируйте формулу для ячеек F10:F11; F12=E12+E12*2%; скопируйте формулу для ячейки F13; F19=E19+E19*20%; H9=E9*G9; скопируйте формулу для ячеек H10:H18;

I9=H9+H9*7%; скопируйте формулу в ячейки I10:I13. I14=H14; скопируйте формулу в ячейки I15:I18. I19=H19+H19*20%. В ячейке H20 рассчитайте итоговую цену.

Перед таблицей вставьте строку, объедините ячейки А8:I8. Введите заголовок «Калькуляция». Цвет шрифта – красный, цвет заливки для заголовка – зеленый, цвет линий для всей таблицы – темно-синий. Шрифт – Times New Roman, размер – 11пт. Для ячеек A9:J9 – стиль Заголовок3.

Создайте диаграмму по своему выбору.

Защитите книгу от изменений.

Вариант № 5

 

Выполнить расчеты по следующим формулам: F9=E9+E9*7%; скопируйте формулу для ячеек F10:F11 и F17; F12=E12+E12*2%; скопируйте формулу для ячейки F13; F20=E20+E20*20%; H9=E9*G9; скопируйте формулу для ячеек H10:H20; I9=H9+H9*7%; скопируйте формулу в ячейки I10:I13 и I17. I14=H14; скопируйте формулу в ячейки I15;I16;I18;I19. I20=H20+H20*20%. В ячейке H21 рассчитайте итоговую цену.

Перед таблицей вставьте строку, объедините ячейки А8:I8. Введите заголовок «Калькуляция». Цвет шрифта – красный, цвет заливки для заголовка – зеленый, цвет линий для всей таблицы – темно-синий. Шрифт – Times New Roman, размер – 11пт. Для ячеек A9:J9 – стиль Заголовок3.

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

Защитите книгу от изменений.

Вариант № 6

 

Перед таблицей вставьте строку, объедините ячейки A1:H1. Введите запись «Поставки 2010». Для ячеек А1:H1 установите стиль Название. Для ячеек А2:H2 установите стиль Заголовок 2. Для ячеек А3:H6 установите стиль Нейтральный. Измените параметры стиля Нейтральный: цвет шрифта установите темно-синий. Примените к книге тему Городская. Просмотрите остальные листы книги и убедитесь, что оформление таблиц изменилось. Измените цветовое оформление темы. Выберите цветовую схему Официальная.

Создайте примечание к ячейке C6. В примечание введите текст Перезаключить договор. Создайте примечание к ячейке H3: текст Апрель отпуск с 8-го апреля. Установите постоянное отображение примечания к ячейке С21. Текст: Перенести поставку на март. Переместите примечание вправо за пределы таблицы. Скройте примечание к ячейке С19. Текст: Ускорить заключение договора. Создайте примечание к ячейке D4, текст: Увеличить объем.

Защитите книгу от изменений.

Вариант № 7

Выполните расчеты по формулам: F2=B2*0.067; скопируйте формулу в ячейки F3:F13. G2=B2*C2; скопируйте формулу в ячейки G3:G13. Установите фильтр. Примените условное форматирование с использованием гистограмм для ячеек столбца А.

Создайте примечание к ячейке D2. Установите оформление примечания: размер шрифта 12 пт, цвет шрифта – темно-зеленый, цвет фона примечания – светло-серый, линий рамки – 3 пт, цвет линии – темно-красный. В примечание введите текст Уточнить номер накладной. Установите постоянное отображение примечания. Переместите примечание вправо за пределы таблицы.

Создайте примечание к ячейке Н11. Текст: Передать договор Иванову. Измените оформление примечания: установите размер шрифта 10 пт, цвет шрифта – темно-синий, цвет фона примечания – светло-бирюзовый. Примечание к ячейке D2 скопируйте на ячейки D3, D6, D10.

Защитите ячейки столбцов E,F,H от изменений.

 

Вариант № 8

 

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

Примените условное форматирование с использованием цветовых схем для ячеек столбца C. Примените условное форматирование с использованием значков для ячеек столбца D.

Защитите лист от изменений.

 

Вариант № 9

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

Примените условное форматирование с использованием гистограмм для ячеек столбца C. Примените условное форматирование с использованием значков для ячеек столбца D.

Создайте примечание к ячейке I6. Установите оформление примечания: размер шрифта 12 пт, цвет шрифта – темно-зеленый, цвет фона примечания – светло-серый, линий рамки – 3 пт, цвет линии – темно-красный. В примечание введите текст Уточнить дату пересдачи. Переместите примечание вправо за пределы таблицы. Примечание скопируйте на ячейки I7;I9;I14;I17.

Создайте примечание к ячейке E12. Текст: Максимальное количество отличных оценок. Измените оформление примечания: установите размер шрифта 12 пт, цвет шрифта – темно-синий, цвет фона примечания – голубой. Установите постоянное отображение примечания.

Защитите лист от изменений.

Вариант № 10

 

Подвести итоги по каждому столбцу и каждой строке. Выполнить расчеты с помощью встроенных функций. В столбце G рассчитать удельный вес по каждой строке. В столбец H ввести для каждой фирмы дату с помощью функции.

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

Для области диаграммы выберите один из стилей Слабый эффект. Для области построения установите заливку бледно-синим цветом. Для легенды установите темно-синий цвет шрифта и размер шрифта 10 пт. Для горизонтальной оси установите толщину линии 3 пт. Для горизонтальной оси установите фиолетовый цвет линии. Для вертикальной оси установите максимальное значение 15000.

Защитите книгу от изменений.

 

Вариант № 11

 

Подвести итоги по каждому столбцу и каждой строке. Выполнить расчеты с помощью встроенных функций. В столбце G рассчитать удельный вес по каждой строке. В столбец H ввести для каждой фирмы дату с помощью функции. Округлите точность числовых данных до 2 знаков после запятой. Выполните сортировку с помощью фильтра. С использованием условного форматирования в столбце B выделите ячейки со значениями более 160.

Построить диаграмму. Добавьте на диаграмму название диаграммы, линии сетки, легенду и подписи данных. Переместите диаграмму к правому краю листа. Увеличьте размер диаграммы по горизонтали примерно в 1,5 раза. Переместите легенду в левый нижний угол области диаграммы. Увеличьте высоту области построения так, чтобы верхняя граница области была максимально приближена к верхнему краю области диаграммы. Создайте примечание к ячейке А4. Установите оформление примечания: размер шрифта 10 пт, цвет шрифта – темно-зеленый, цвет фона примечания – светло-серый, линий рамки – 1,5 пт, цвет линии – темно-красный. Текст: Перезаключить договор. Переместите примечание вправо за пределы таблицы. Примечание скопируйте на ячейки А7:A9. Создайте примечание к ячейке E6. Текст: Поставки осуществлять с учетом февраля. Измените оформление примечания: установите размер шрифта 12 пт, цвет шрифта – темно-зеленый, цвет фона примечания – желтый. Установите постоянное отображение примечания. Защитите лист от изменений.

Приложение 2

Пример оформления отчета по лабораторной работе