Работа со штатным расписанием фирмы

 

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

Предлагается, пользуясь встроенными функциями Excel:

1) рассчитать численность сотрудников отдела (всех представленных в задаче отделов);

2) рассчитать численность сотрудников, занимающих определенную должность (всех представленных в задаче должностей);

3) пользуясь таблицей поправочных коэффициентов, пересчитать размер оклада сотрудников;

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

Исходные данные для задания 6 представлены в таблицах 1.6-1.7.

Для расчетов понадобятся следующие функции Excel:

· СЧЁТЕСЛИ() – для расчета выборки по указанному критерию;

· ЕСЛИ() – для осуществления выборки по указанному критерию;

· И() – для формирования критерия выборки;

· ДОЛЯГОДА() – – для нахождения количества лет от даты поступления на работу до даты начисления премии.

ПРИМЕЧАНИЕ: Порядок использования функций – см. в мастере функций, который находится на листе Excel в закладке Вставка в подзакладке Функция (f_x).

Таблица 1.6

Наименова-ние отдела	Должность	ФИО	Оклад, руб.	Работает с даты	Коэфф.
контроля	начальник	Чепоров В.В.	2000	15.05.89	0,80
контроля	аудитор	Фатеева Н.В.	1800	05.05.09	0,75
реализации	начальник	Ермоленко Г.Г.	2000	15.05.87	1,00
реализации	менеджер	Абибуллаев М.С.	1800	16.05.99	0,70
реализации	менеджер	Куссый М.Ю.	1800	07.05.97	0,70
реализации	менеджер	Нехайчук Ю.С.	1800	02.02.07	0,70
реализации	секретарь	Деркач Ю.В.	1000	22.08.03	0,60
снабжения	начальник	Канов А.А.	2000	17.08.91	0,90
снабжения	менеджер	Боднер Г.Д.	1800	25.05.05	0,70
снабжения	экспедитор	Друзин Р.В.	1000	18.07.09	0,65

 

Таблица 1.7

Стаж работы, лет	% оклада на премию
Менее 1	Не начисляется
От 1 до 3	10
От 3 до 5	20
От 5 до 10	30
Свыше 10	40

Алгоритм расчетов

Расчет численности сотрудников отдела будем осуществлять в Excel с помощью формулы (см. рис. 8):

=СЧЁТЕСЛИ(A$3:A$12;G3),

где A$3:A$12 – диапазон выборки (наименование отдела) в Excel;

G3 – критерий выборки в Excel.

Расчет численности сотрудников, занимающих определенную должность, будем осуществлять в Excel с помощью формулы (см. рис. 8):

=СЧЁТЕСЛИ(B$3:B$12;G8),

где B$3:B$12– диапазон выборки (должность) в Excel;

G8 – критерий выборки в Excel.

ПРИМЕЧАНИЕ: в качестве критерия можно не выбирать содержимое какой-нибудь ячейки, а назначить критерий вручную, с внесением нужного критерия выборки в кавычки (например, "=секретарь" или ">0"; см. рис. 8).

Расчет общей численности сотрудников, занимающих определенную должность, будем осуществлять в Excel с помощью формулы (см. рис. 8):

=СЧЁТЕСЛИ(D3:D12;">0"),

где D3:D12 – диапазон выборки (Оклад) в Excel;

">0" – критерий выборки в Excel. Данный критерий был избран, т.к. у работающих в фирме должностных лиц оклад должен быть ненулевым.

Перерасчет размера оклада сотрудников (по предлагаемому в задаче правилу перерасчета оклад всех сотрудников привязывается к окладу сотрудника, коэффициент перерасчета у которого равен 1) осуществляем в Excel по формуле:

=D$5*C15,

где D$5 – ячейка Excel, в которой хранится значение оклада сотрудника, коэффициент перерасчета у которого равен 1;

C15 – ячейка Excel, в которой хранится значение коэффициента перерасчета сотрудника, для которого осуществляется перерасчет оклада.

Расчет размера премии к выплате сотруднику осуществляем в Excel 2 этапа (такой порядок расчетов объясняется тем, что в Excel уровень вложения функций, используемых в формуле, не может быть более 7 функций).

1) вычисляем стаж работы сотрудника в фирме по формуле в Excel:

=ДОЛЯГОДА(E3;G$15),

где E3 – ячейка Excel, в которой хранится значение даты, начиная с которой сотрудник работает в фирме;

G$15 – ячейка Excel, в которой хранится значение даты, по состоянию на которую начисляется премия;

2) вычисляем размер премии к выплате сотруднику фирмы в Excel по формуле (формула дана построчно; в каждой строке проверяется один из диапазонов лет стажа работы сотрудника в фирме из табл. 1.7 – в порядке уменьшения стажа):

=ЕСЛИ(B27>10;B15*F$20/100;

ЕСЛИ(И(B27<=10;B27>5);B15*F$19/100;

ЕСЛИ(И(B27<=5;B27>3);B15*F$18/100;

                               ЕСЛИ(И(B27<=3;B27>1);B15*F$17/100;0)))),                      

где B27 – ячейка Excel, в которой хранится значение стажа работы сотрудника в фирме, вычисленного на первом этапе расчетов;

F$20 – ячейка Excel, в которой хранится значение процента начисления премии при стаже работы в фирме свыше 10 лет;

Рис. 8. Алгоритм расчетов по 6 заданию

F$19 – ячейка Excel, в которой хранится значение процента начисления премии при стаже работы в фирме от 5 до 10 лет;

F$18 – ячейка Excel, в которой хранится значение процента начисления премии при стаже работы в фирме от 3 до 5 лет;

F$17 – ячейка Excel, в которой хранится значение процента начисления премии при стаже работы в фирме от 1 года до 3лет;

B15 – ячейка Excel, в которой хранится значение размера текущего оклада сотрудника, полученного после перерасчета величины оклада.

По этим алгоритмам расчеты осуществляются для всех сотрудников фирмы (см. рис. 8).