Разработка конспекта-урока для студентов СПО на тему_ Создание с. Практическая работа Работа со сводными таблицами Цель работы

• Название: Практическая работа Работа со сводными таблицами Цель работы
• Дата: 02.05.2022
• Размер: 0.79 Mb.
• Имя файла: Разработка конспекта-урока для студентов СПО на тему_ Создание с.docx
• Тип: Практическая работа; #507826

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

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

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

1. 
   Открыть новую книгу.
   
2. 
   Переименовать лист в Картотека.
   
3. 
   Подготовить исходные данные (см. табл. 1)
   

Таблица 1.

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

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

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

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

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

Разряд работающего, Значения – Тариф, Операция – Сумма.
Макет сводной таблицы представлен на рисунке 1.



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

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

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

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





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

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

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



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

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

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

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

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

6. 
   На ленте Параметры выполнить команду Активное поле → Параметры поля (рис. 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. Сохранить рабочую книгу.