Урок Создание сводных таблиц в MS Excel

Практическая работа

Работа со сводными таблицами

Цель работы: освоить навыки создания, редактирования и анализа данных на основе сводных таблиц.

Задание.

Построить сводную таблицу для расчета месячной заработной платы рабочих при повременной форме оплаты труда, начисления премии и учета удержаний. Премия дифференцирована по разрядам: 2 разряд 20%, 3 разряд 30%, 4 разряд 40% к тарифу, 5 разряд – 50%. Удержания берутся со всех видов начислений (зарплата, премия) и составляют 13% от суммы начислений.

Методика выполнения работы

1. Открыть новую книгу.

2. Переименовать лист в Картотека.

3. Подготовить исходные данные (см. табл. 1)

Таблица 1.

4. Установить курсор в список, выполнить команду меню Вставка→ Сводная таблица для вызова Мастера сводных таблиц и диаграмм.

5. Указать тип источника – Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel. Выбрать вид создаваемого отчета → Сводная таблица.

6. Выполнить проверку диапазон выделенных ячеек списка. Диапазон включает имена столбцов и все заполненные строки таблицы.

7. Разместить поля в макете сводной таблицы:

Фильтр отчета –  Профессия,  Названия строк –  ФИО,  Названия столбцов –

Разряд работающего, Значения –  Тариф, Операция –  Сумма.

Макет сводной таблицы представлен на рисунке 1.

Рис 1. Макет сводной таблицы

8. На ленте Конструктор выполнить команды: Общие итоги → Включить по столбцам; Выбрать стиль сводной таблицы. На ленте Параметры: Сводная таблица → Параметры. В открывшемся окне задать Для пустых ячеек отображать – пробел; Сохранять форматирование ячеек. Нажать кнопку ОК.

В сводной таблице (рис. 2) представлен список всех работающих. Для каждого работающего указан только один тариф, соответствующий его разряду.

Для преобразования сводной таблицы следует:

1.                      Установить курсор в область сводной таблицы.

2.                      Выполнить команду Параметры (Анализ – MS Excel 2013) → Формулы → Вычисляемое поле

для создания вычисляемого поля.

Рис. 2.  Сводная таблица.

3. На рис. 3 представлено диалоговое окно для формирования вычисляемого поля. Имя поля – Зарплата, Формула вычисления: =Тариф*168.

(Коэффициент 168 зависит от количества рабочих часов в текущем учетном периоде.). Для добавления поля в формулу можно воспользоваться кнопкой Добавить.

Рис. 3. Создание вычисляемого поля

4.                      Установить курсор в область сводной таблицы.

5.                      С помощью кнопки Список полей на ленте Параметры откройте макет

сводной таблицы для корректировки.

6.                      Удалить поле Сумма по полю Тариф (простым перетаскиванием мышкой за поле окна).

7.                      Установить курсор в области сводной таблицы на поле Сумма по полю Зарплата.

8.                      На ленте Параметры выполнить команду Активное поле → Параметры поля (рис. 4):

-  Изменить имя поля в сводной таблице – Месячная зарплата. Нажать кнопку Числовой формат и указать формат поля – Денежный.

-  Нажать кнопку ОК.

Рис. 4.  Задание параметров вычисляемого поля

9. Установить курсор в область сводной таблицы на поле Разряд работающего.

10. Создать вычисляемый объект Премия. Премия выплачивается как процент к начисленной заработной плате, дифференцируется по разрядам: 2 разряд – 20%, 3 разряд – 30%, 4 разряд – 40%, 5 разряд – 50%.

-  На ленте Параметры выполнить команду Формулы→Вычисляемый объект (рис. 5). Указать имя объекта – Премия.

-  Для построения формулы в окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы

-  Формула: = ‘2’*0,20+’3’*0,30+’4’*0,4+’5’*0,50

-  Нажать кнопку Добавить.

-  Закрыть окно – кнопка ОК.

Рис. 5. Создание вычисляемого объекта

11. Установить курсор в область сводной таблицы на поле Разряд работающего. Создать вычисляемый объект Вычеты, сумма вычетов это 13% от суммы заработка и премии.

 Выполнить команду Формулы → Вычисляемый объект. Указать имя объекта – Вычеты (рис. 6).

Рис. 6. Создание вычисляемого объекта

 В окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы для построения формулы вида:

=                        –0,13*(’2’+’3’+’4’+’5’+Премия)

-  Нажать кнопку Добавить.

-  Закрыть окно –  кнопка ОК.

12. Выполнить команду Параметры→Формулы→Вывести формулы для просмотра выражений вычисляемых полей и объектов (рис. 7).

Если потребуется изменить нормативы (количество отработанных часов, % премии, % вычетов), следует отредактировать вычисляемые поля и объекты – команда меню Формулы→Вывести формулы, вызывать поле/объект, внести изменения

Рис. 7.  Вывод формул

13. Переименовать лист, содержащий сводную таблицу, присвоив имя, Сводная таблица 1.

14. Поставить курсор внутрь сводной таблицы и на ленте Конструктор выполнить команду Макет отчета. Выбрать тип отчета.

15. Поставить курсор внутрь сводной таблицы и щелкнуть на ленте Параметры кнопку Сводная диаграмма.

16. В готовой диаграмме перетащить Разряд работающего в область Поле ряда. Выбирая вид профессии просмотреть данные по различным профессиям.

17. Сохранить рабочую книгу.