如何使用Power Pivot建立模型处理考勤数据
Excel使用PowerPivot设计考勤数据模型
HR一直都希望,能够有一种简单快捷,同时又准确无误的处理方法,来处理每个月的考勤工作。
以往使用Excel公式进行统计,人数多的时候,公式就会变得很卡,毕竟数组公式再处理大量数据时,效率太低了。
如何才能提高处理效率,PowerPivot是非常好的选择,我们就尝试使用PowerPivot建立一个处理考勤的数据模型。
我们的目标是通过导出的考勤数据作为数据源,可以直接输出统计结果,甚至是分析报告。
我们先来做个简单的:
1、考勤工作:8:30-17:00
2、漏打卡默认为上下班时间,来计算工作时长
第一步:做数据准备
1、考勤记录:要整理成这个样子的,三个字段是必须的:工号、日期、签到
2、工作日历:主要是把法定假日及调休搞清楚,方便计算加班
工作日历可以使用PowerPivot来制作,然后添加节假日,处理一下工作日即可,这个我们在前面有文章介绍过:
第二步:建立模型
1、新建文件考勤数据模型,导入考勤记录,链接日历表
2、日历表设为日期表,
3、建立日历表Date与考勤记录日期的一对多表关系
第三步:设计度量值
1、时间点:要判断迟到早退就要有时间节点,当然直接用Time函数也行,不过要每次输入很麻烦,直接在日期表中把时间节点写出来,方便在后续计算中直接引用。
2、时长相关的度量值:
上班打卡:=CALCULATE(MIN([签到]),FILTER('考勤记录',[签到]=[上班时间]))
下班打卡:=CALCULATE(max([签到]),FILTER('考勤记录',[签到]=[下班时间]))
迟到打卡:=CALCULATE(MIN([签到]),FILTER('考勤记录',[签到][上班时间][签到][时间中点]))*[迟到次数]
早退打卡:=CALCULATE(max([签到]),FILTER('考勤记录',[签到]=[时间中点][签到][下班时间]))*[早退次数]
上班打卡时间调整:=if([下班打卡]0[上班打卡]=BLANK(),max([迟到打卡],[上班时间]),[上班打卡])
下班打卡时间调整:=if([上班打卡]0[下班打卡]=BLANK(),min(IF([早退打卡]=BLANK()||[早退打卡]=0,[下班时间],[早退打卡]),[下班时间]),[下班打卡])
时长:=([下班打卡时间调整]-[上班打卡时间调整])*24
3、打卡次数相关的度量值:
上班打卡次数:=CALCULATE(COUNTROWS(VALUES('工作日历'[Date])),FILTER('考勤记录',[签到][上班时间]))
下班打卡次数:=CALCULATE(COUNTROWS(VALUES('工作日历'[Date])),FILTER('考勤记录',[签到][下班时间]))
迟到次数:=CALCULATE(COUNTROWS(VALUES('工作日历'[Date])),FILTER('考勤记录',[签到][时间中点]))-[上班打卡次数]
早退次数:=CALCULATE(COUNTROWS(VALUES('工作日历'[Date])),FILTER('考勤记录',[签到]=[时间中点]))-[下班打卡次数]
有效打卡天数:=CALCULATE(COUNTROWS(VALUES('工作日历'[Date])),FILTER('考勤记录',[签到][时间起点]))
漏打卡:=2*[有效打卡天数]-[上班打卡次数]-[下班打卡次数]-[迟到次数]-[早退次数]
上班漏打卡:=[有效打卡天数]-[上班打卡次数]
下班漏打卡:=[有效打卡天数]-[下班打卡次数]
加班天数:=CALCULATE(COUNTROWS(VALUES('工作日历'[Date])),FILTER('工作日历',[是否工作日]=0),FILTER('考勤记录',[签到]0))
第四步:统计打卡次数
就是建立个透视表,把关于次数的度量值放进去就可以了:
第五步:时长的统计
要多一步查询,把所有的考勤打卡时间罗列出来,然后再统计
1、通过现有链接建立查询,
2、编辑查询的DAX公式:
3、建立透视表统计工作时长:
保存数据模型
这个模型基本上能够实现我们的目标,每个月只要替换数据源,就可以生成新的考勤统计。