人力资本层-考勤评估案例

员工信息表

计算员工出勤率：

解读：出勤率（病假不计入出勤）：出勤天数÷规定的月工作日×100％

=B2/NETWORKDAYS(DATE(2019,11,1),DATE(2019,11,30))

NETWORKDAYS函数

功能：返回两个日期之间的完整工作日数

语法结构：

NETWORKDAYS(开始日期,结束日期,[非工作日日期])

计算加班强度比率：

解读：加班强度比率：当月加班时数÷当月总工作时数×100％

=(E2+F2)/(NETWORKDAYS(DATE(2019,11,1),DATE(2019,11,30))*8)

（E2+F2）是平时加班+周末加班=平时加班时数，8 指的是每天工作小时数

计算每日工资：

解读:每日工资=月固定工资/21.75天

=VLOOKUP(A2,员工信息!$B$2:$C$27,2,)/21.75

使用VLOOKUP函数查找到每个员工的月薪，然后除以每个月的工作天数

计算当月有效工作天数：

解读：当月有效工作天数=当月应工作天数–全无薪假期

=NETWORKDAYS(DATE(2019,11,1),DATE(2019,11,30))-D2-IF(G2<=5,0,IF(G2-C2>5,C2,G2-5))

NETWORKDAYS(DATE(2019,11,1),DATE(2019,11,30))为每个月的应工作天数

D2为事假，事假是没有薪水的

IF(G2<=5,0,IF(G2-C2>5,C2,G2-5))：在本案例中公司法定带薪病假每人每年5天，病假分为两种，发薪水和不发薪水，如果YTD累计病假<=5,就不用看当月病假里面的值，所以赋值为0，（YTD累计病假是从1月到11月的累计病假），如果不是的话，就用YTD累计病假-当月病假，可以看出之前有多少病假，如果之前的病假>5,这个病假一定是无薪病假，否则无薪病假就是YTD累计病假-当月病假

计算当月实际天数调整比例：

解读：当月实际工作天数调整比例=21.75天/当月应工作天数

=21.75/NETWORKDAYS(DATE(2019,11,1),DATE(2019,11,30))

计算当月基础工资：

解读:月基础工资=每日工资x当月有效工作天数x当月实际工作天数调整比例

计算平时加班费：

解读：平时加班费=月基础工资÷（21.75天*8小时）×1.5倍×平时加班时数

计算周末加班费：

解读：假日加班费=月基础工资÷（21.75天*8小时）×2倍×假日加班时数

计算月工资：

解读：月基础工资+平时加班费+周末加班费

计算加班费占比：

解读：加班强度比率：当月加班时数÷当月总工作时数×100％

查看员工所属部门：

方法一：VLOOKUP函数(逆向查找)

方法：=VLOOKUP(A2,IF({1,0},员工信息!B2:B27,员工信息!A2:A27),2,0)

解读：

VLOOKUP()

功能：根据指定的查询条件和区域，返回指定列的值。

语法结构：=Vlookup(查询值,数据范围,返回值的相对列数,匹配模式)。

1、此用法为Vlookup函数的特殊用法，逆向查询。

2、公式的参数从总体上还是分为4个部分，“数据范围”部分为：IF({1,0},员工信息!B2:B27,员工信息!A2:A27)，其目的就是重组组成新的数据查询范围，使查询值在左，返回值在右。

方法二：INDEX+MATCH

方法：=INDEX(员工信息!$A$2:$B$27,MATCH(A15,员工信息!$B$2:$B$27,0),1)