Лабораторная работа №2 по EXCEL

(план работы)

Цель работы освоение следующих технологических приемов работы:

- Работа с листами рабочей книги (копирование таблиц с листа на лист, вставка новых листов, переименование листов);

- Скрытие и показ листов и столбцов таблицы;

- Просмотр листа в режиме формул;

- Применение автоформатирования;

- Защита ячеек таблицы;

- Одновременный просмотр и работа с двумя листами книги;

- Применение команды «Специальная вставка»;

- Формирование таблиц с общими и частными итогами;

- Формирование сводных таблиц и консолидация данных;

- Транспонирование элементов таблицы;

- Автозаполнение ячеек таблицы;

- Применение функции консолидации

1. Копируя листы, создать ведомости расчета зарплаты за месяцы ФЕВРАЛЬ-ИЮНЬ, аналогично ЯНВАРЮ. Предусмотреть в каждом месяце отличающиеся значения оперативных данных: «отработано дней», «минимальный размер оплаты труда», «процент удержания подоходного налога», «текущая дата» и т.д.

2. Скрыть лист «Справочник». Для этого активизировать этот лист, щелкнув по его ярлыку, и выполнить команду ФОРМАТ/Лист/ Скрыть. Восстановить отображение листа, выполнив команду ФОРМАТ/Лист/ Отобразить.

3. Скрыть столбцы с удержанными суммами расчетной таблицы за январь. Для этого выделить столбцы и выполнить команду ФОРМАТ/Столбец /Скрыть. Восстановить отображение скрытых столбцов. Для этого выделить столбцы, смежные со скрытыми, и выполнить команду ФОРМАТ/Столбец /Отобразить.

4. Просмотр листа в режиме формул. Просмотрите таблицу на листе «январь» в режиме вывода формул. Для этого выполните команду СЕРВИС/Параметры. На вкладке Вид включите флажок «Формулы».

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

- Выделить ячейки, не подлежащие защите, содержащие перечень табельных номеров и данные по отработанному времени. Вызвать контекстное меню и выполнить ко­ман­ду ФОРМАТ/Ячейка.

Примечание. Для выделения несмежных ячеек использовать клавишу Ctrl.

- На вкладке «Защита» снять флажок «Защищенная ячейка». Нажать " Ок".

- Выполнить команду СЕРВИС/Защита/ Защитить лист.

- Проверить действие защиты.

- Отменить защиту, выполнив команду СЕРВИС/Защита/ Снять защиту.

6. Выполнить автоформатирование таблицы за январь. Для этого:

- Выделить все ячейки таблицы, включая заголовок;

- Выполнить команду ФОРМАТ/Автоформат;

- Выбрать на вкладке формат «Цветной 2» и щелкнуть по кнопке «Параметры»

- Снять флажки «выравнивание» и «ширина и высота» и нажать кнопку «Ok».

7. Выполнить одновременный просмотр и работу с двумя листами книги с листом «Справочник» и листом «Январь». Для этого выполнить команды ОКНО/Новое и затем ОКНО/Расположить/ Сверху вниз. Щелкнуть в одном окне по ярлыку «Справочник», в другом окне – по ярлыку «Январь».

8. Формирование таблиц с общими и частными итогами. Ввести в расчетную таблицу за январь для всех суммовых показателей общие итоги и итоги по каждому отделу. Для этого:

- Скопировать таблицу за январь на новый лист, присвоив ему имя «Итоги»;

- Добавить в таблицу столбец «отдел»;

- Рассортировать таблицу по возрастанию номеров отделов, а внутри одного отдела по возрастанию табельных номеров;

- Выделить таблицу и выполнить команду ДАННЫЕ/Итоги.

- В окне «Промежуточные итоги» установить «При каждом изменении отдела» и включить флажки для всех столбцов с суммовыми данными;

- Щелкнуть по кнопке «Ok».

- Просмотреть таблицу с итогами, подключая или отключая итоги разных уровней с помощью кнопок «+» и «-» на левой панели управления итогами.

- Удалить все итоги в таблице. Для этого установить курсор на любую ячейку таблицы и выполнить команду ДАННЫЕ/Итоги. В окне " Промежуточные итоги " Щелкнуть по кнопке " Убрать все"

9. Ввести в таблицу два уровня промежуточных итогов - по отделам и должностям. Для этого предварительно рассортировать исходную таблицу по 2-м полям - по отделам, затем по должностям, затем применить команду ДАННЫЕ/Итоги 2 раза. Первый раз подвести промежуточные итоги по отделам, второй раз - по должностям. В окне " Промежуточные итоги " при подведения итогов по должностям снять флажок " Заменить текущие итоги "

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

- Откройте новый лист и дайте ему имя «Свод»;

- Находясь на листе «Свод», выполните команду ДАННЫЕ/Сводная таблица, появится окно с мастером сводных таблиц;

- На шаге 1 установите флажок «в списке или базе данных Microsoft Excel» и нажмите кнопку «Далее“;

- На шаге 2 необходимо ввести адресные ссылки на исходную таблицу, находящуюся на листе «Справочник». Щелкните по ярлыку «Справочник» и выделите ячейки справочной таблицы, включая заголовки столбцов. Нажмите на кнопку «Далее»;

- На шаге 3 разместите поля сводной таблицы путем перетаскивания их справа в соответствующие области сводной таблицы. Перетащите поле «отдел» в область «столбцы», поле «должность» - в область «строка», поле «член профсоюза» - в область «данные».

- На шаге 4 установите флажок «существующий лист» и нажмите кнопку «Готово».

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

12. Создайте для одного из работников Личную карточку с данными расчетов заработной платы за 6 месяцев. Для этого:

- Откройте новый лист и дайте ему имя по фамилии работника;

- Сформируйте строку с заголовками столбцов

- Скопируйте содержимое ячеек с названиями столбцов, начиная со столбца «оклад» из листа «январь» на лист личной карточки, разместив эти названия в ячейках по вертикали. Копирование производить, используя команду «специальная вставка». Для этого после обычного копирования «в буфер памяти» выделить начальную ячейку и вызвать контекстное меню. В контекстном меню выберите пункт «специальная вставка» и включите флажок «транспонировать».

- Используйте прием автозаполнения ячеек таблицы. Введите в соответствующую ячейку название столбца «январь». Скопируйте содержимое этой ячейки вправо на 11 ячеек, зацепив за «+» в правом нижнем углу рамки выделения. В строке появится список из названий месяцев года;

- Добавьте справа столбец для годовых итогов;

- Транспонируйте строки для выбранного работника из каждого расчетного месяца в столбцы его личной карточки;

- Вычислите данные в столбце «Итого за год».

13. Функция консолидации данных позволяет получать результирующие таблицы, содержащие итоги для данных, расположенных в различных областях одного или нескольких рабочих листах или даже в разных книгах. Консолидируемые данные могут быть динамически связаны с исходными данными таким образом, что при изменении исходных данных автоматически обновляются итоговые данные.

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