Практическая работа № 3. Форматирование и защита таблицы.

Цели работы: форматирование таблицы, защита таблицы от редактирования, подготовка таблицы к печати, освоение приемов работы с поименованными ячейками, примечания в ячейках.

1. Откройте таблицу Задача2.xls и сразу сохраните ее под новым именем Задача3.xls.

2. Добавьте пустую строку перед строкой «шапка» таблицы. «Шапка» таблицы это строка с заголовками столбцов таблицы: Таб.номер, Фамилия, Зарплата и т.д.

3. Сделайте две строки 2:2 (так обозначается вся вторая строка в таблице) и 3:3, содержащие процент подоходного налога В2 и отчисления в пенсионный фонд В3, невидимыми. Для этого выделите их и выполните команду Формат / Строки / Скрыть.

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

Форматирование таблицы должно проводиться с учетом ее вывода, как на экран, так и на принтер.

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

5. Установите курсор в ячейку, в которой записан текст «Расчетная ведомость за январь». Выделите диапазон ячеек, начиная с этой ячейки до последнего столбца таблицы (A1:I1). Отцентрируйте заголовок в пределах этого диапазона (кнопка Объединить и поместить в центре панели инструментов Форматирование). Установите для заголовка жирный шрифт размером 12 пунктов, цвет – красный (используйте инструменты панели Форматирование).

6. Замените в «шапке» таблицы сокращения на полные слова.

7. Выделите диапазон А5:I5 и выполните команду Формат / Ячейки / Выравнивание / По горизонтали - По центру  и По вертикали - По центру, установите флажок Переносить по словам. Установите для «шапки» шрифт, размером 8 или 9 пунктов, цвет - красный. Измените цвет фона «шапки», например, сделайте ее светло-серой (кнопка Цвет заливки Панели форматирование или команда Формат / Ячейки / Вид) При необходимости измените ширину некоторых столбцов, чтобы таблица "смотрелась". Для изменения ширины столбца установите курсор на границу с названиями колонок (A,B,C,D и т.д.), курсор примет вид: «||», и, не отпуская левую кнопку мыши, перемещайте границу столбца влево или вправо.

8. Увеличьте высоту итоговой строки. Измените цвет символов итоговой строки на красный.

9. Выровняйте данные в ячейках. При выравнивании данных обычно соблюдают следующие правила:

• столбцы, содержащие текст, выравниваются по левой границе;

• столбцы, содержащие числа, выравниваются по правой границе;

• текст в «шапке» документа выравнивается по центру.

Выравнивание можно выполнить кнопками панели Форматирование или командой Формат / Ячейки / Выравнивание.

10. Установите для диапазона ячеек С6:D22 формат числа - целое (команда Формат / Ячейки / Число / Числовой - 0 разрядов), а всех остальных столбцов - числа с двумя разрядами после запятой. Для этого выделите фрагмент таблицы и выполните команду Формат / Ячейки / Число. Для столбца К выдаче примените Денежный формат. Для этого можно воспользоваться и кнопкой Денежный формат панели Форматирование. Если при этом ширина некоторых столбцов окажется недостаточной, измените ее.

11. Выполните команду Сервис / Параметры / вкладка Вид и сбросьте флажок у элемента сетка (вертикальные и горизонтальные линии должны исчезнуть).

12. Используя кнопку Границы панели Форматирование или команду Формат / Ячейки / Граница, расчертите таблицу горизонтальными и вертикальными линиями. Используйте жирные, тонкие линии и двойные линии. Перед нанесением границ фрагмент таблицы нужно выделить.

13. Рис.9

    Выполните условное форматирование. Необходимо обеспечить вывод суммы подоходного налога, рассчитанного по прогрессивной шкале, другим цветом, например, синим, т.е. сравнивать нужно значение налогооблагаемой базы, а окрашивать в цвет - значение налога. Для этого установите курсор на ячейку с налогом первого сотрудника (H6), выберите команду Формат / Условное форматирование и сформируйте следующее условие: значение НОБ первого сотрудника > 30000. Кроме того, в условии адрес не должен быть абсолютным, т.е. не должно быть знаков $ (рис.9). Затем нажмите кнопку Формат…, в окне Формат ячеек выберите вкладку Шрифт и задайте нужный цвет и начертание символов, нажмите ОК.

14. Установите заработную плату первого сотрудника равной 35 000 и убедитесь, что значение налога окрасилось установленным цветом.

15. Скопируйте путем протаскивания формулу для всех сотрудников. Установите еще нескольким сотрудникам соответствующую заработную плату и проверьте действие условного форматирования.

16. Подготовьте таблицу к выводу на печать. Выведите таблицу на экран в режиме предварительного просмотра, нажав на кнопку Предварительный просмотр Стандартной панели инструментов или выполнив команду Файл / Предварительный просмотр). Измените Масштаб (увеличивать и уменьшать вид таблицу). Вернитесь в обычный режим, нажав на кнопку Закрыть.

    Рис. 10	Рис. 11

17. Воспользуйтесь командой Файл / Параметры страницы. В диалоговом окне Параметры страницы выберите вкладку Страница (рис.10): поменяйте ориентацию листа бумаги, увеличьте распечатку по отношению к оригиналу в 1,5 раза (150%). Выберите вкладку Поля (рис.11): измените отступы от границ листа, проведите центрирование таблицы. Выберите вкладку Колонтитулы (рис.12): создайте верхний колонтитул, указав в нем свою фамилию, и повторите просмотр таблицы перед выводом на печать, нажав на кнопку Просмотр. Обратите внимание на то, как повлияли ваши изменения на расположение таблицы на листе бумаги.

18. Рис. 12

    Сохраните таблицу.

19. Заключительным этапом разработки таблицы является ее защита от случайных и преднамеренных изменений. Это очень важный раздел работы, который часто недооценивается пользователями, его следует выполнить особенно внимательно. Можно защитить всю таблицу или ее часть, причем при защите таблицы может быть установлен пароль.

20. Защитите таблицу от изменений (пароль задавать не надо). Для этого выполните команду Сервис / Защита / Защитить лист. Попробуйте изменить какие-либо ячейки таблицы, у вас это не должно получиться. Снимите защиту с таблицы.

21. Защитите от редактирования все ячейки таблицы за исключением фамилий, размера заработной платы и табельного номера. Для этого предварительно установите другой, например, зеленый цвет для ячеек, которые разрешено редактировать А6:С16. Защита выполняется в два этапа: 1 этап: сброс флажка защиты с тех ячеек, которые можно редактировать. Выделите диапазон ячеек А6:С16, войдите в меню Формат / Ячейки / вкладка Защита, снимете флажок и нажмите Enter или ОК (предварительно все ячейки таблицы считаются защищаемыми). 2 этап: защита таблицы: защитите таблицу с паролем (команда Сервис / Защита / Защитить лист). Задайте пароль - QSW.

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

23. Научитесь снимать защиту с ячеек. Повторите эти действия несколько раз, оставив в конечном итоге формулы защищенными без пароля.

24. Сохраните таблицу под прежним именем (Задача3.xls).

25. Снимите защиту с листа, выполнив команду Сервис / Защита / Снять. Таблица должна быть похожа на таблицу, показанную на рисунке 13.

26. Рис. 13

    Обратите внимание, что строки номер 2 и 3 скрыты. Для их вывода нужно выделить две строки, между которыми есть скрытые строки, и выполнить команду Формат / Строки / Отобразить.

27. Изменим условие задачи. Предположим, что все сотрудники получают премию, равную 25% от заработной платы. Процент премии запишем в ячейке В4, значение - в ячейке С4. Самостоятельно исправьте прежние формулы в столбце Премия. Не забудьте об абсолютной адресации (см. практическую работу№2)! Т.е. формула расчета премии для первого сотрудника выглядит так: =C6*$C$4. И эту формулу растиражируйте для всех сотрудников.

28. Рис.14

    К ячейке, содержащей значение процента премии, сделайте примечание (рис.14): выполните команду Вставка / Примечание. Обратите внимание, что в правом верхнем углу ячейки появился красный треугольник, который свидетельствует о наличии примечания.

29. Создайте примечания еще к двум-трем ячейкам.

30. Научитесь корректировать примечания и удалять их. Для этого используйте правую кнопку мыши и пункты появляющегося при этом контекстного меню: Изменить примечание, Удалить примечание, Отобразить примечание, Скрыть примечание.

31. Сохранить таблицу под новым именем Задача4.xls и еще под одним имеем Задача4А.xls. Файл Задача4.xls закрываем (этот файл будет использоваться в дальнейших практических работах), а мы продолжаем работу с файлом Задача4А.xls.

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

33. Выполните присваивание имени ячейке. 1 способ: для этого выделите ячейки, содержащие заработную плату сотрудников (без названия столбца и итоговой строки). Выполните команду Вставка / Имя / Присвоить и присвойте выделенным ячейкам имя: Зарплата.

34. Выполните аналогичные действия для других столбцов вашей таблицы. Имена диапазонам придумайте самостоятельно, пробелы в именах не используйте.

35. 2 способ (более простой): для этого нужно выделить нужный диапазон, а затем в самой левой части строки формул, где обычно высвечиваются координаты расположения курсора, ввести имя.

36. Создайте имя ячейки. Для этого установите курсор в ячейку С2 и выделите эту ячейку и ячейку, содержащую текст Ставка Налога. Выполните команду Вставка / Имя / Создать. Укажите, что имя создается на основе ячейки, расположенной левее значения налога. Оно будет занесено в список имен.

37. Аналогично создайте имя для процента премии и ставки удержания в пенсионный фонд.

38. Обдумайте, когда рационально применять способ создания имени, а когда способ присваивания имени?

39. Выполните подстановку имен в формулы. Для этого установите курсор на итоговую строку колонки Зарплата, содержащую формулу суммирования. Выполните команду Вставка / Имя / Применить. Проверьте, как изменилась формула. Обратите внимание на замену диапазона ячеек на имя. Если формула не поменялась - проверьте правильность указания диапазонов для соответствующих имен.

40. Аналогичную работу выполните для столбцов, где вычисляются Премия и Налог. Обратите внимание на изменение формул.

41. Удалите любую строку из диапазона 6:6 –16:16 и проверьте, как изменились диапазоны строк в списке имен окна Присвоение имени (команда Вставка / Имя / Присвоить), верните строку и проанализируйте список имен (диапазоны должны поменяться автоматически).

42. Научитесь удалять имена из списка и изменять для них диапазоны.

43. Вставьте в конец таблицы, например, начиная с 23:23 строки, список созданных имен, выполнив команду Вставка / Имя / Вставить / Все имена.

44. Сохраните таблицу под именем Задача4А.xls.

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

1. Как выполнить форматирование ячейки?

2. В чем заключается Условное форматирование?

3. Как создать собственный колонтитул?

4. Как защитить только отдельные ячейки?

5. Как скрыть строки, столбцы?

6. Как создать примечание?

7. Для чего используются Имена диапазонов?

8. Каковы способы создания Имен диапазонов?