АВТОМАТИЗАЦИЯ РАСЧЕТА ЗАРАБОТНОЙ ПЛАТЫ СРЕДСТВАМИ EXCEL

Название работы: АВТОМАТИЗАЦИЯ РАСЧЕТА ЗАРАБОТНОЙ ПЛАТЫ СРЕДСТВАМИ EXCEL

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

Предметная область: Информатика, кибернетика и программирование

Описание: ПРАКТИЧЕСКАЯ РАБОТА АВТОМАТИЗАЦИЯ РАСЧЕТА ЗАРАБОТНОЙ ПЛАТЫ СРЕДСТВАМИ EXCEL Цель работы: Автоматизировать рабочее место по созданию формы Расчет заработной платы на базе табличного редактора MS Excel. Обеспечить старт приложения с главной странички Диалог. На

Дата добавления: 2013-07-05

Размер файла: 3.59 MB

Работу скачали: 437 чел.

АВТОМАТИЗАЦИЯ РАСЧЕТА ЗАРАБОТНОЙ ПЛАТЫ СРЕДСТВАМИ EXCEL

Цель работы. Автоматизировать рабочее место по созданию формы «Расчет заработной платы», на базе табличного редактора MS Excel.

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

Создать приложение из 20 таблиц. В главной таблице – «Сотрудники» произвольно ввести данные для пяти человек. Таблицы связать между собой ссылками по отношению к первой таблице «Сотрудники» для возможности ввода и изменений данных о сотрудниках, тарифной ставке, больничных листах, отпусках и других данных в расчетных таблицах. Автоматизировать расчет заработной платы, организовав связи с применением формул, для корректировки сумм, участвующих в расчете.

На отдельной странице составить структуру схемы «Выполнение расчета ЗП». На отдельной странице составить диаграмму «Изменение фонда заработной платы».

Ознакомиться с кодом программы.

1. Загрузите среду MS Excel и создайте файл с названием «Зарплата».

2. Создайте в документе 20 листов, один из них назовете «Диалог», а остальные в соответствии с надписями расположенных на рис.1. Для этого станьте на Лист 1 и правой кнопкой мышки в диалоговом окне выберите - «Добавить лист», затем переименуйте его.

Предусмотрите кнопку для возврата с каждого листа на главную страницу (рис.1).

Рис. 1. Главный интерфейс – страница «Диалог»

3. Для быстрого просмотра приложения по каждому из перечисленных пунктов установите кнопку управления, при нажатии на которую будет открыт лист, согласно его описанию или по нажатию запрограммированных клавиш, см. рис.2.

Рис. 2. Интерфейс главной таблицы

4. Организовать автоматический переход между главной страницей и другими листами возможно с помощью макросов, см. рис.3.

Сервис - макрос — начать запись.

Рис. 3. Настройка макроса

После выбора «Начать запись» будет предложено окно, в котором следует назвать макрос и присвоить ему для дальнейшего запуска горячие клавиши (рис.4).

Рис. 4. Запись макроса

Затем следует с документом выполнить те действия, которые за вас в дальнейшем выполнит макрос (в нашем случае открыть нужный лист). По завершению работ с документом следует остановить макрос (рис.5).

Рис. 5. Остановка макроса

Кнопки управления на главной странице следует разместить, вызвав панель элементов управления (рис.6).

Рис. 6. Панель инструментов

Затем каждой кнопке, присвойте ранее созданные макросы (рис.7,8.). Для этого следует нажать правой кнопкой мыши по элементу управления и в появившемся меню выбрать – Назначить макрос. В появившемся окне следует выбрать макрос, который вы хотите назначить этому объекту.

5. На отдельном листе нарисуйте блок - схему расчета заработной платы (рис.9).

Рис. 9. Блок-схема расчета ЗП

6. Создайте таблицы «Список сотрудников» (рис.10), «Календарные и рабочие» (рис.11) и «Таблица архива» (рис.12).

Рис. 10. Интерфейс «Список сотрудников»

Рис. 11 Интерфейс «Календарные и рабочие дни»

Рис. 12 Интерфейс «Таблица архива»

7. Таблицу «Список сотрудников» сделайте основной для ввода и корректировки данных, поэтому во всех таблицах, где применяются столбцы «Табельный номер», «ФИО» организовать ссылки, чтобы данные брались из таблицы «Сотрудники», применив формулы:

=Согрудники!В4 (для столбца «Табельный номер»);

=Сотрудники!С4 (для столбца «Сотрудники»).

В «Таблице архива» дайте полную справку о размере заработной платы любого из сотрудников. Представьте информацию, как по месяцам, так и в столбце «Среднемесячная», рассчитайте среднюю зарплату за последние 6 месяцев, применив формулу:

=( H 5+ I 5+ J 5+ K 5+ L 5+ M 5)/6.

В столбце с именем «Итого» произвести суммирование столбца пользуясь автосуммой:

В таблице «Рабочие и календарные дни» укажите, сколько рабочих, и сколько календарных дней содержится в каждом месяце.

8. Создайте таблицы «Данные о больничных листах» (рис.13) и «Данные об отпусках» (рис.14).

Рис. 13. Интерфейс «Данные о больничных листах»

Рис. 14. Интерфейс «Данные об отпусках»

Поставьте выборочно некоторым сотрудникам количество дней по болезни и процент оплаты по больничному листу, а 2-3 человека отправьте в отпуск.

9. Создайте таблицы «Ведомость начисления аванса» (рис.15) и «Ведомость начисления премии» (рис.16).

Произведите расчет аванса в размере 40% от оклада, применив формулу:

Начисление премии производится в процентном соотношении с окладом:

Рис. 15. Интерфейс «Ведомость начисление аванса»

10. Создайте «Ведомость начисления больничных листов» (рис.17). Суммарная оплата по больничному листу составляет:

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

=(С7/'Календар_рабочие дни'!С10)* D 7*Е7/100.

Рис. 16. Интерфейс «Ведомость начисления премии»

Рис. 17. Интерфейс «Ведомость начисления больничных»

Создайте «Ведомость начисления сумм по отпускным» (рис.18), применив формулу:

где С6- ='Таблица архива'!'N7/12;

D 6- ='Данные об отпусках'!С6;

Е6- КРД по отпуску.

Рис. 18. Интерфейс «Начисление сумм по отпускным»

11. Создайте «Ведомость начисленных сумм» (рис.19).

12. Создайте «Ведомость расчета отчислений в пенсионный фонд» (рис.20).

Рис. 19. Интерфейс «Ведомость начисленных сумм»

Рис. 20. Интерфейс «Ведомость отчислений в пенсионный фонд»

13. Создайте «Ведомость расчета подоходного налога» (рис.21).

Рис. 21. Интерфейс «Ведомость расчета подоходного налога»

и «Ведомость сумм отчислений в фонд занятости» (рис.22).

Рис. 22. Интерфейс «Ведомость сумм отчислений в фонд занятости»

14. Создайте «Ведомость расчета сумм отчислений по алиментам» (рис. 23).

Рис. 23. Интерфейс «Ведомость расчета сумм отчислений по алиментам»

15. Создайте «Выборку сумм по больничным листам» (рис.24), используйте «Автофильтр» (рис.25).

Рис. 24. «Выборка сумм по больничным листам»

Рис. 25. Пользовательский автофильтр

16. Создайте «Расчетную ведомость заработной платы» (рис.26).

Рис. 26. Интерфейс «Расчетная ведомость заработной платы»

17. Создайте «Платежную ведомость» (рис.27).

Рис. 27. Интерфейс «Итоговая платежная ведомость».

20. С помощью «Мастера диаграмм» составьте диаграмму – «Изменение фонда заработной платы», см. рис.31.

Рис. 31. Изменение фонда заработной платы

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

Коэффициент трудового участия: применение и расчет в Excel

Коэффициент трудового участия показывает меру трудового участия отдельного работника в общем результате бригады, производственного коллектива. Это обобщенная количественная оценка трудового вклада отдельного специалиста. Использование КТУ предупреждает субъективное оценивание вклада каждого работника в общее дело, возникновение конфликтов по поводу начисления и выплаты зарплаты.

Базовое значение КТУ – 1 или 100. Такая величина применяется к начислениям работникам, которые в расчетном периоде:

• выполнили поставленную задачу;
• следовали требованиям к технологии производства, качеству, охране труда;
• соблюдали трудовую дисциплину;
• соблюдали должностные обязанности, рабочие инструкции.

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

В дальнейшем ведется ежедневный учет показателей и рассчитывается общий КТУ.

Критерии, которые могут снижать размер коэфициента:

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

Показатели, повышающие величину КТУ:

• проявление инициативы, творческой активности при выполнении производственной, организационно-технической задачи;
• решение сложной и ответственной задачи;
• выполнение работы в сжатые сроки;
• наставничество и т.п.

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

С применением КТУ могут распределяться такие доплаты, как:

• премия за трудовые достижения (за выполнение сверхнормы);
• единовременное вознаграждение в связи с изменением нормативов;
• экономия по зарплате, связанная с высвобождением персонала.

Надтарифная зарплата, распределяемая по коэффициенту, относится на себестоимость продукции.

Фактический показатель труда = базовый КТУ + СК (сумма повышающих / понижающих критериев).

Базовый – 1 или 100. Понижающие критерии оценки трудового вклада вычитаются из базового показателя труда. Повышающие – прибавляются.

Задача для примера. В деревообрабатывающем цеху работает бригада из 4 сотрудников. В их распоряжении 3 вида оборудования. Заработная плата начисляется с использованием КТУ. Разработана система повышающих и понижающих критериев.

Для расчета индивидуального КТУ составляется отдельная таблица:

Для каждого отчетного периода заполняется отдельная таблица. Итоговый показатель – сумма базового и повышающих / понижающих критериев.

Чтобы понять, как начисляется заработная плата с использованием КТУ, распределим надтарифную часть ФОТ между членами бригады. Предположим, на май этой бригаде установлена сумма в размере 65 000 рублей. Эту цифру нужно разделить на сумму КТУ по всем сотрудникам и умножить на индивидуальный коэффициент трудового участия.

Добавим в таблицу строку «Итого» и посчитаем сумму коэффициентов все работников (функция СУММ):

Премию за трудовые достижения рассчитаем с помощью формулы:

К этой цифре остается добавить тарифную часть, рассчитанную по отработанному времени и часовым тарифным ставкам.

Начисление зарплаты в экселе образец

В формулах правильно расставить виды адресов.

Установить защиту на блоки ячеек с формулами.

Представить результаты расчета графически, используя деловую графику.

Вариант 1. Создать в EXCEL для небольшого трудового коллектива (10-12 чел.) ведомость для начисления зарплаты. В формулах использовать логические функции.

Исходные данные (первые столбцы таблицы ): Фамилия, Имя, Должность, Тарифный коэффициент, Стаж работы.

Расчетныеданные (столбцы таблицы с формулами ): Начислено, Надбавки, Налог, К выдаче (На руки), Подпись.

Тарифный коэффициент зависит от должности и находится в пределах от 1 до5.

Известна зарплата для тарифного коэффициента 1(например, 1000руб.).

Надбавка начисляется за стаж работы по правилу:

при стаже до года надбавки нет,

при стаже от 1 года до 3 лет – 10%,

при стаже свыше 3 лет – 20%,

Величина налога 13%

Вариант 2. Создать в EXCEL для бригады строителей (10-12 чел.) ведомость для начисления зарплаты. В формулах использовать логические функции.

Исходные данные (первые столбцы таблицы ): Фамилия, Имя, Рабочие дни, Сверхурочные часы.

Расчетныеданные (столбцы таблицы с формулами ): Начислено, Налог, К выдаче (На руки), Подпись.

Известна ставка рабочего дня и цена сверхурочного часа (Ваши цифры),

Ставку налога принять 12% и 20% в зависимости от заработанной суммы (Ваше условие).

Вариант 3. Создать в EXCEL для небольшого трудового коллектива (10-12 чел.) ведомость для начисления зарплаты.

Исходные данные (первые столбцы таблицы ): Фамилия, Имя, Должность, Тарифный коэффициент, % удержаний.

Расчетныеданные (столбцы таблицы с формулами ): Начислено, Удержано, Налог, К выдаче (На руки), Подпись.

Тарифный коэффициент зависит от должности и находится в пределах от 1 до10.

Известна зарплата для тарифного коэффициента 1(например, 1000руб.).

Величина налога 13%

Предусмотреть итог по всем видам начислений.

Вариант 4. Создать в EXCEL ведомость для начисления зарплаты коллектива розничных торговцев (10-12 чел.) В формулах использовать логические функции.

Исходные данные (первые столбцы таблицы ): Фамилия, Имя, Выручка, Стаж работы.

Расчетныеданные (столбцы таблицы с формулами ): Начислено, Надбавки, Налог, К выдаче (На руки), Подпись.

Начисленная сумма равна 10% от выручки. Величина налога 13%. Надбавка начисляется за стаж работы по правилу:

при стаже до 2 месяцев надбавки нет,

при стаже от 2месяцев до1 года– 10% ,

при стаже свыше 1 года – 20%,

Вариант 5. Создать в EXCEL таблицу расчета квартплаты 12-ти квартирного жилого дома.

Исходные справочные данные (отдельная таблица) Цена 1кв.м.площади, тариф за холодную воду, тариф за горячую воду.

Исходные данные (первые столбцы таблицы ): Номер квартиры, Кол-во жильцов, Площадь.

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

Предусмотреть итог по всем видам начислений.

Вариант 6. Создать в EXCEL для небольшого трудового коллектива (10-12 чел.) ведомость выплат зарплаты с учетом удержаний алиментов. В формулах использовать логические функции.

Исходные данные (первые столбцы таблицы ): Фамилия, Имя, Начислено, Количество детей.

Расчетныеданные (столбцы таблицы с формулами ): Удержано, Налог, К выдаче (На руки), Подпись.

Величина налога 12%. Удержания производятся по правилу:

при наличии одного ребенка – 25%,

при наличии двух детей – 33%,

при наличии трех детей – 50%,

Вариант 7. Создать в EXCEL таблицу расчета части квартплаты многоквартирного жилого дома. В формулах использовать логические функции.

Исходные справочные данные (отдельная таблица) Цена 1кв.м. площади с учетом этажности, тариф за газ.

Исходные данные (первые столбцы таблицы ): Номер квартиры, Кол-во жильцов, Площадь, Этаж.

Расчетныеданные (столбцы таблицы с формулами ): Квартплата за жилье, Начислено за газ, Всего к оплате.

Предусмотреть итог по всем видам начислений.

Вариант 8. Создать в EXCEL таблицу расчета повременной оплаты телефона. В формулах использовать логические функции.

Исходные справочные данные (отдельная таблица) Тарифы с учетом типов льгот.(Ваши варианты).

Исходные данные (первые столбцы таблицы ): Номер телефона, Фамилия, Тип льготы, Продолжительность разговоров в часах.

Расчетныеданные (столбцы таблицы с формулами ): Начислено, Льгота, К оплате.

Предусмотреть итог по всем видам начислений.

Вариант 9. Создать в EXCEL ведомость оплаты за отгруженный оптовикам товар (10-12 операций). В формулах использовать логические функции.

Исходные данные (первые столбцы таблицы ): Дата, Покупатель, Количество товара (ед.изм.), Цена за ед.изм..

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

При покупке товара от10 упаковок до 20 упаковок скидка – 2%,

При покупке товара от21 упаковок до 40 упаковок скидка – 6%,

При покупке товара свыше 41 упаковки скидка – 10%.

Предусмотреть итог по всем видам начислений.

Вариант 10. Определение коэффициента трудового участия КТУ.

На таком производстве, где невозможно оценить индивидуальное количество и качество труда часто, принято по итогам месяца или недели оценивать их при помощи КТУ, которое в дальнейшем влияет на оплату. Чтобы исключить необъективность оценок, все члены бригады анонимно заполняют анкету, где выставляют КТУ (в диапазоне от 0 до 1) для каждого из своих коллег. Необходимо как-то обработать эти данные и сформировать обобщенные значения КТУ. В таблице в столбцах В:Е зафиксированы оценки для каждого из четырех членов бригады, в F они просуммированы, в G ? вычисляется собственно КТУ по формуле:

<КТУ работника>=<Всего КТУ работника>/<Сумма КТУ>.

Эти значения (в таблице столбец G) уже можно использовать для оценки результатов труда, однако более удобно, если они находятся в том же диапазоне, что и исходные оценки, т.е. в диапазоне от 0 до 1 (столбец Н).

<Результирующее КТУ>=<КТУ работника>/МАКС(КТУ работников).

Рабочие формулы для первого работника приведены ниже:

Вариант 11. Расчет сдельной зарплаты: ЕСЛИ().

Построить таблицу вычисления сдельной (т.е. пропорциональной количеству произведенной продукции) зарплаты, а также налога и суммы на руки. Зарплата работника определяется числом обработанных деталей, умноженным на стоимость обработки. Если работник допустил брак и испортил деталь, ее стоимость вычитается из заработка.

<зарплата>=<обработано деталей>*<стоимость работы>–<деталей брака>*<стоимость детали>.

Таким образом, Зарплата первого работника будет определяться формулой: D6=B6*C2-C6*C3. Полагаем, что налог исчисляется в зависимости от зарплаты: если она выше 20000 – налог составляет 20%, если нет – 13%. Иными словами: <сумма налога>=<зарплата>*ЕСЛИ(<зарплата> больше 20000, то 13%, иначе 20%).

Может случиться, что если брака окажется много, зарплата станет отрицательной (работник должен компенсировать нанесенный ущерб). В этом случае, конечно, никакой налог не удерживается и, следовательно, в формуле для налога следует предусмотреть выявление отрицательности заработка. С учетом вышесказанного уточним значение Е6:

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

Представить результаты расчета графически, используя деловую графику, (2-3 типа, круговую диаграмму обязательно).

Вариант 12. Повременная зарплата:ГПР().

Построить таблицу вычисления повременной (т.е. зависящей от длительности рабочего времени) заработной платы работников на некотором производстве. Последняя зависит от числа отработанных дней в месяце, разряда рабочего и премии. Для вычисления собственно зарплаты (область D7:D9) нужно число дней умножить на тариф, зависящий от разряда

<зарплата>=<дней>*<тариф по разряду>.

Для розыска разрядного тарифа нам понадобится функция горизонтального поиска ГПР(). Например, для Иванова должна быть использована формула D7=ГПР(С7;В2:F4;2;0)*В7. Премия также зависит от разряда Е7=ГПР(С7;В2:F4;3;0). Всего работнику будет начислена суммаF7=E7+D7.

Вариант 13. Расчет зарплаты: СУММПРОИЗВ(), ГПР().

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

Положим, заказчик оценил всю работу в 10000 руб. и выдал аванс в размере 4000 руб. который и был распределен между людьми произ­вольным образом (кто сколько попросил). Наша задача состоит в том, чтобы по завершении работы распределить остальную часть заработанного (6000 руб.).

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

<итогоКТУ>=1*1,4+2*1,2+3*1=3,7 или D9=СУММПРОИЗВ(С6:С8;D6:D8).

Надбавки за класс:

Распределение заработанной платы

Теперь можно определить причитающуюся рабочему сумму:

<заработано>=<всего>/<итого КТУ>*<коэфф. за класс>*<КТУ>

для первого рабочего это F6=$B$1/$D$9*C6*D6.

Коэффициент за класс

Поскольку работник уже получил аванс, ему предстоит выдать (а может быть и взыскать с него, если аванс не был "отработан") сумму

Правильность наших расчетов подтверждает совпадение значений F9=B1 иG9=F1.

Вариант 14. Распределение премии:СУММ(),ЕСЛИ().

Положим, отделу выделена недельная премия-помощь в размере 8000 руб. Ее нужно поделить между сотрудниками следующим образом – малооплачиваемым (считаем таковыми тех, у кого недельный заработок менее пяти минимальных зарплат) делается доплата до 5 минимальных зарплат, а остаток делится между всеми пропорционально зарплате. Сформируем необходимые выражения.

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

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

Если зарплата < 5*мин. зарплата

5*мин. зарплата – зарплата

Она будет таковой, если общая сумма помощи достаточна. Если денег выделено меньше, то помощь уменьшается до выделенного значения и делится пропорционально планируемым максимальным суммам помощи.

Помощь фактическая вычисляется:

Если общая максимальная помощь < премии

иначе помощь максимальная *премия/общая максимальная помощь

Остаток премии (если есть) делится уже между всеми работниками (включая и малооплачиваемых) пропорционально их зарплате:

Если общая фактическая помощь < премии

помощь – общая фактическая помощь/общая зарплата*зарплата + помощь

Правильность расчетов подтверждает то, что В1=Е9.

Вариант 15. Динамическое исчисление налогов. СУММЕСЛИ().

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

В рассматриваемом примере для упрощения выкладок налоговая сетка имеет всего две строчки – для дохода до и свыше 20000. Cчитаем, что налог со всей суммы от 20000 составляет 20%. Наша задача – научиться определять налог в каждом из 12 месяцев, таким образом, чтобы учесть как тарифную сетку, так и уже уплаченные в предыдущих месяцах налоги. Для этого следует выполнить вычисления:

Налог в текущем месяце:

сумма всех доходов к дате расчета*процент налога – сумма ранее уплаченных налогов.