Практическая работа 1. Расчет сдельной заработной платы

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

Сдельная заработная плата рассчитывается на основании документа «Сдельный наряд», в котором указывается объем и расценка выполненных работ, ведется табель рабочего времени для членов бригады, работавших по наряду.

СДЕЛЬНЫЙ НАРЯД № _________ ДАТА ВЫПИСКИ НАРЯДА_________

РАБОТЫ ПО НАРЯДУ (лицевая сторона наряда)

№ п/п	Наименование работы	Единица измерения	Трудоемкость	Расценка	Объем	Сумма заработка

 

ТАБЕЛЬ РАБОЧЕГО ВРЕМЕНИ (оборотная сторона наряда)

№ п/п	ФИО	Разряд работающего	Тариф	Количество часов	Зарплата по тарифу	КТУ	Зарплата по наряду

Рисунок 1 - Форма документа "Сдельный наряд"

Наряд на сдельную зарплату предназначен для учета сдельных работ и расчета зарплаты работников, занятых их выполнением. При сдельной форме оплаты труда сумма заработка по наряду зависит от объема выполненных работ и расценки за единицу работы:

Где i – индекс (порядковый номер) работы по наряду;

∑ - символ суммирования по всем работам наряда.

Трудоемкости и расценки сдельных работ приводятся в нормативных справочниках выполняемых работ. Объемы выполненных работ по наряду указывается в принятых единицах измерения.

Различают индивидуальные и бригадные наряды, в зависимости от числа занятых работников. В бригадных нарядах указывается состав бригады, количество отработанных часов по наряду каждого члена бригады, а также коэффициент трудового участия (КТУ) для распределения сдельной зарплаты по наряду. В индивидуальных нарядах учитывается только рабочее время работника.

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

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

Сведения о сдельной заработной плате каждого работающего накапливаются для вычисления налогов. Для выполнения расчетов сдельной заработной платы по нарядам создается рабочая книга следующей структуры (табл. 1).

Таблица 1 - Структура рабочей книги

Лист	Назначение листа
Тарифы	Тарифные разряды и ставки для сдельщиков
Работы	Расценки для сдельных работ
Картотека	Кадровый состав рабочих-сдельщиков
Шаблон	Шаблон листа нарядов
Копия шаблона	Защищенный лист-копия шаблона
Наряд №1, Наряд №2, …	Серия рабочих листов нарядов
Зарплата	Итоги по сдельной заработной плате работающих
Налоги	Нормативы подоходных налогов на заработную плату
Диаграмма	Диаграмма структуры заработной платы работающих

ПОДГОТОВКА НОРМАТИВНО-СПРАВОЧНОЙ ИНФОРМАЦИИ

Нормативно-справочная информация для расчета сдельной зарплаты хранится на листах Тарифы, Картотека и Работы.

Лист Тарифы

Лист Тарифы содержит перечень рабочих разрядов и соответствующих им тарифных коэффициентов и ставок. Так как ставка 1 разряда может периодически изменяться, то должен быть обеспечен автоматический пересчет ставок всех разрядов. Ставка разряда рассчитывается по формуле: коэффицент*Ставка_1_разряда.

Таблица 2 – Таблица тарифных ставок

	А	В	С
1.	Ставка 1 разряда	36,70
2.
3.	Разряд	Коэффициент	Ставка
4.			36,70
5.		1,35	49,55
6.		1,57	57,62
7.		1,85	67,90
8.		2,12	77,80
9.		2,57	94,32

Информационная технология создания листа Тарифы

1. Заполнить на листе Тарифы таблицу ставок (столбцы Разряд и Коэффициент), ввести сумму ставки 1 разряда.

2. Ввести формулу расчета тарифной ставки для 1 разряда: = B4*$C$1,

где В4 - адрес ячейки, содержащий тарифный коэффициент 1 разряда;

$C$1 - абсолютный адрес ячейки для ставки 1 разряда.

3. Скопировать формулу тарифной ставки 1 разряда в ячейки столбца Ставка.

4. Создать именованные блоки ячеек для столбцов Разряд, Коэффициент, Ставка - команда Формулы, Определенные имена, Присвоить имя (названия столбцов не входят в состав блоков).

5. Снять защиту с ячейки ставки 1 разряда - команда Главная, Число, Защита, сбросить флажок Защищаемая ячейка.

6. Скрыть формулы в ячейках блока Ставка - команда Главная, Число, Защита, с указанием Защищаемая ячейка и Скрыть формулы.

7. Защитить лист Тарифы от изменений - команда Рецензирование, Изменения, Защитить лист

Лист Картотека

Лист Картотека содержит сведения о работающих:

Таблица 3- Картотека

Табельный номер	ФИО	Профессия	Разряд работающего	Тариф	Дети
	Иванов А.П.	Кладовщик
	Колесов В.И.	Грузчик
	Крылов А.Р.	Грузчик
	Михайлов П.Р.	Грузчик
	Смирнов И.А.	Кладовщик
	Соколов Р.В.	Уборщик

Информационная технология создания листа Картотека

1. Заполнить шапку таблицы.

2. Создать именованные блоки ячеек столбцов: Табельный_номер; ФИО; Профессия; Разряд_работающего; Тариф; Дети – команда Формулы, Определенные имена, Присвоить имя (названия столбцов не входят в блоки), ориентировочное число строк – 10.

3. Ввод табельных номеров осуществляется в текстовом формате чисел. Перед заполнением табельных номеров следует выделить блок Табельный_номер и выполнить команду Главная, Число, вкладка Число, Текстовый.

4. Выбор разряда работающего производится из ограниченного списка значений ячеек блока Разряд таблицы Тарифы.

Выделить блок ячеек Разряд_работающего, выполнить команду меню Данные, Работа с данными, Проверка данных, Проверка данных на вкладке Параметры указать тип данных Список, поместить курсор в поле Источник, нажать клавишу <F3> и выбрать блок Разряд.

5. Тариф выводится в соответствии с разрядом работающего по формуле:

=ПРОСМОТР(D2; Разряд; Ставка),

где D2 - ссылка на тарифный разряд работающего.

Формулу размножить по всем ячейкам блока Тариф.

6. Количество детей работающих указываются как целые неотрицательные числа.

Выделить блок ячеек Дети, выполнить команду Данные, Работа с данными, Проверка данных, Проверка данных. На вкладке Параметры указать: тип данных – Целое число, Значение – между 0 и 10.

7. Скрыть расчетные формулы в ячейках блока Тариф

8. Отсортировать строки таблицы Картотека по столбцу ФИО.

9. Защитить информацию рабочего листа Картотека.

Лист Работы

Лист Работы содержит нормативные характеристики работ, используемых для оформления сдельных нарядов.

Таблица 4 - Работы

Наименование работы	Единица измерения	Норма времени	Расценка
Погрузка автомашины	машина
Подбор грузов	т	2,12
Разгрузка автомашины	машина
Складирование грузов	т	4,12

Информационная технология создания листа Работы

1. Заполнить шапку таблицы.

2. Создать именованные блоки ячеек столбцов: Наименование_работы; Единица_измерения; Норма_времени; Расценка (названия столбцов не входят в блоки, ориентировочное число строк – 10). Для этого выделить блок данных, включая название столбца, и выполнить команду Формулы, Определенные имена, Создать из выделенного фрагмента; установить флажок в строке выше.

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

Выделить блок ячеек Единица_измерения, выполнить команду Данные, Работа с данными, Проверка данных, Проверка данных. На вкладке Параметры указать тип данных – Список, поместить курсор в поле Источник, ввести через точку с запятой элементы списка значений.

4. Отсортировать строки таблицы Работы по столбцу Наименование работы.

5. Защитить информацию рабочего листа Работы.

Лист Шаблон

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

Информационная технология создания лицевой стороны наряда

1. Ввести постоянный текст документа (рис.2).

2. Создать именованные блоки для ячеек, содержащих итоговые показатели по наряду: Сумма_заработка_по_наряду, Тарифная_зарплата_по_наряду, Приработок, Нормативная_трудоемкость_по_наряду – команда меню Формулы, Определенные имена, Присвоить имя.

3. Установить максимально возможное число строк в табличной части лицевой стороны наряда, например – 4. Создать именованные блоки ячеек столбцов табличной части документа Объем, Трудоемкость, Сумма_заработка (названия столбцов не входят в блоки) – команда меню Формулы, Определенные имена, Присвоить имя.

 

Наряд на сдельную работу №
Дата выписки наряда		Сумма заработка по наряду
	Тарифная зарплата по наряду
Приработок
Нормативная трудоемкость по наряду
№ п/п	Наименование работы	Единица измерения	Трудоемкость	Расценка	Объем	Сумма заработка

Рисунок 2 – Лицевая сторона наряда

4. Выбор наименований работ при заполнении наряда производится из блока Наименование_работ таблицы Работы.

Выделить блок ячеек столбца Наименование работы, выполнить команду Данные, Работа с данными, Проверка данных, Проверка данных, на вкладке Параметры указать тип данных - Список, поместить курсор в поле Источник, нажать клавишу <F3> и выбрать блок Наименование_работы.

5. Для автоматического отображения единицы измерения, трудоемкости и расценки выбранной работы в ячейки столбцов Единица измерения, Трудоемкость и Расценка вводятся формулы. Для первой работы наряда формулы имеют вид:

=ПРОСМОТР(B8;Наименование_работы;Единица_измерения)

=ПРОСМОТР(B8;Наименование_работы;Норма_времени)

=ПРОСМОТР(B8;Наименование_работы;Расценка)

где B8 - адрес ячейки первой строки наряда, содержащей наименование работы.

Введенные формулы первой строки лицевой стороны наряда следует скопировать в остальные строки.

6. Сумма заработка по отдельной работе рассчитывается по формуле:

=объем * расценка

Ввести формулу в первую строку наряда и размножить ее по всем остальным строкам.

7. Сумма заработка по наряду вычисляется по формуле:

=СУММ(Сумма_заработка) итоговая сумма по всем строкам наряда

8. Тарифная зарплата по наряду и Приработок вычисляются после заполнения оборотной стороны наряда.

9. Нормативная трудоемкость по наряду вычисляется по формуле:

=СУММПРОИЗВ(Объем; Трудоемкость)

10. Скрыть от просмотра введенные формулы.

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

Информационная технология создания оборотной стороны наряда

1. Ввести постоянный текст документа (рис.3)

№ п/п	ФИО	Разряд работающего	Тариф	Количество часов	Зарплата по тарифу	КТУ	Зарплата по наряду

Рисунок 3 – Оборотная сторона наряда

2. Создать именованные блоки ячеек столбцов Зарплата_по тарифу, Зарплата по наряду, КТУ - команда Формулы, Определенные имена, Присвоить имя (названия столбцов не входят в блоки).

Установить максимально возможное число строк в табличной части наряда, например – 5.

3. Выбор работающих по наряду осуществляется из фиксированного списка таблицы Картотека.

Выделить блок ячеек столбца ФИО, выполнить команду Данные, Работа с данными, Проверка данных, Проверка данных, на вкладке Параметры указать тип данных - Список, поместить курсор в поле Источник, нажать клавишу <F3> и выбрать блок ФИО.

4. Для отображения разряда работающего и его тарифной ставки в ячейки столбцов Разряд работающего и Тариф вводятся формулы.

Для первой строки табеля формулы вида:

=ПРОСМОТР(B19;ФИО;Разряд_работающего)

=ПРОСМОТР(B19;ФИО;Тариф)

где В19 - адрес ячейки первой строки, содержащей ФИО работающего.

Введенные формулы первой строки табеля скопировать в ячейки столбцов Разряд работающего, Тариф.

5. Количество часов отработанных по наряду и коэффициент трудового участия (КТУ) каждого работающего для распределения суммы приработка вводятся вручную.

6. Вычисляется Зарплата по тарифу по формуле:

=Количество часов* Тариф.

7. Вычисляется итоговое значение тарифной зарплаты всех работающих по наряду по формуле:

Тарифная зарплата по наряду: =СУММ (Зарплата_по_тарифу)

8. Вычисляется итоговое значение приработка (премии) по формуле:

Приработок = Сумма заработка по наряду - Тарифная зарплата по наряду.

9. Для каждого работающего вычисляется Зарплата по наряду по формуле:

=F19+Приработок*G19/СУММ(КТУ)

где F19, G19 – адреса ячеек строки табеля для первого работающего.

Введенные формулы первой строки табеля скопировать в ячейки столбца Зарплата по наряду для остальных работающих.

10. Скрыть от просмотра введенные формулы.

11. Открыть для изменений ячейки, содержащие количество часов и КТУ.

12. Проверить работоспособность формул, путем ввода исходных данных, после чего удалить все введенные данные.

13. Создать страховую копию листа Шаблон – вызвать контекстно-зависимое меню листа, Переместить/скопировать лист, установить флажок Создавать копию.

14. Переименовать лист-копию в Копия шаблона и защитить лист.

ОФОРМЛЕНИЕ СДЕЛЬНЫХ НАРЯДОВ

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

Новый лист переименовать в лист Наряд №, ввести исходные данные; лишние строки в таблицах лицевой и оборотной стороны наряда следует удалять. Создать четыре наряда.

После завершения ввода данных защитить информацию рабочего листа от изменений.

ИТОГИ ПО СДЕЛЬНОЙ ЗАРПЛАТЕ

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

Информационная технология построения сводной таблицы:

1. Добавьте новый лист Зарплата.

2. Добавьте на панель быстрого запуска кнопку Мастера сводных страниц и диаграмм. Для этого:

§ Щелкните стрелку рядом с панелью инструментов, а затем щелкните пункт Другие команды.

§ В группе "Выбрать команды из" выберите значение "Все команды".

§ В списке выберите пункт Мастер сводных таблиц и диаграмм, нажмите кнопку Добавить, а затем – кнопку ОК.

3. Установить курсор в ячейку листа Зарплата;

4. Выполнить команду Мастер сводных таблиц на панели быстрого доступа;

· На шаге 1: указать Исходные данные находятся в нескольких диапазонах консолидации;

· На шаге 2а: указать Создать поля страницы;

· На шаге 2б:

a) Установить Количество страничных полей сводной таблицы - 1.

b) Перечислить диапазоны, содержащие данные для агрегирования (рис. 4). Каждый диапазон соответствует блоку ячеек оборотной стороны наряда Наряд №. Блок включает имена столбцов и данные строк. Столбец № п/п не входит в диапазон.

Рисунок 4 – Добавление диапазонов консолидации

Для каждого диапазона ячеек, т.е. для каждого нового наряда выполняются следующие действия:

§ Нажать кнопку для определения диапазона;

§ Перейти на рабочий лист Наряд № и выделить ячейки диапазона;

§ Вернуться в окно диапазонов при помощи кнопки возврата

§ Нажать кнопку Добавить;

§ Дать название страничного поля диапазона.

5. Разместить сводную таблицу на листе Зарплата. Нажать Готово.

6. Выполнить настройку макета и отдельных полей. Для этого курсор последовательно устанавливается на поле сводной таблицы в разделе областей: Страница1, Строка, Столбец, Данные:

Имя поля в области Страница1 - Наряды;

Имя поля в области Строка - ФИО;

Имя поля в области Столбец - Сведения; скрыть элементы: КТУ, Разряд работающего; Тариф ( установите курсор на поле сведения в разделе сведения,щелкните на стрелку выпадающего спискаснимите пометки напротив тех элементов, которые нужно исключить из анализа).

Имя поля в области Сумма по полю Значение - Итоги, операция – Сумма, Формат… - числовой, Число десятичных знаков - 2.

7. Настроить параметры сводной таблицы: вычислить общие итоги по столбцам. Для этого на вкладке Конструктор в группе Макет нажмите кнопку Общие итоги и выберите вариант Общие итоги по столбцам.

Полученную сводную таблицу модифицировать. Для этого следует:

§ Добавить в область значений поле Значение ( щелкните имя поля в разделе полей и, удерживая кнопку нажатой, перетащите его в нужную область раздела макета) и выполнить его настройку: имя - Структура, операция - Сумма, вкладка Дополнительные вычисления и выбрать Доля от суммы по столбцу. Окончательный вид сводной таблицы приведен на рис. 5.

Рисунок 5 - Сводная таблица сдельной зарплаты