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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

12. Рис.9

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

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

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

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

Рис. 10	Рис. 11

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

17. Рис. 12

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

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

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

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

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

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

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

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

25. Рис. 13

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

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

27. Рис.14

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

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

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

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

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

32. Выполните присваивание имени ячейке. 1 способ: для этого выделите ячейки, содержащие заработную плату сотрудников (без названия столбца и итоговой строки). Выполните команду вкладка Формула/ раздел Определенные имена/ кнопка Диспетчер имени / Создать присвойте выделенным ячейкам имя: Зарплата; область применения: Книга; диапазон оставьте неизменным.

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

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

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

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

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

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

39. Удалите любую строку из диапазона 6:6 –14:14 и проверьте, как изменились диапазоны строк в списке имен окна (команда вкладка Формула/ раздел Определенные имена/ кнопка Диспетчер имени), верните строку и проанализируйте список имен (диапазоны должны поменяться автоматически).

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

41. Вставьте в конец таблицы, например, начиная с 23:23 строки, список созданных имен, выполнив команду вкладка Формула/ раздел Определенные имена/ выпадающий список Использовать в формуле/ выберите пункт Вставить имена.

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

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

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

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

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

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

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

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

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

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