去壹人事APP浏览
加入HR交流社群,
50万HR人在这里

Excel整理不规范的考勤数据,3分钟搞定1天的工作量!

来源:壹人事 阅读量:2880 时间:2021-01-11

今天跟大家分享下如何快速的整理考勤数据,这也是一个粉丝问到的问题,下图就是他们公司的打卡数据,表格中存在一人重复多次打卡的情况,想要提取出第一次打卡以及最后一次的打卡时间,来计算迟到早退,之前都是一个一个提取,非常的耗费时间,今天跟大家分享一种简单的方法,点点鼠标即可轻松搞定,话不多说,让我们直接开始吧。


一、加载数据到powerquery

首先我们需要将前两行表头删除掉,然后我们点击表格的任何一个单元格,点击【数据】功能组,然后在右侧找到【来自表格/区域】随后在跳出的窗口中勾选【表包含标题】然后点击确定,这样的话就会进入到powerquery的编辑界面。


二、转换数据格式

首先我们按住ctrl键选择前三行数据,然后点击【转换】找到【逆透视列】再选择【逆透视其他列】着这样的话就日期与打卡时间都会变为竖向显示。


随后我们点击值这一列,也就是打卡时间,然后点击【转换】找到【格式】选择【清除】将表格中的非打印字符都删除掉,然后我们再点击【替换值】在查找的值中输入一个空格,其余的什么都不要输入然后点击全部替换。

Excel整理不规范的考勤数据,3分钟搞定1天的工作量

这样的话所有的打卡时间都会聚集在一起,但是每5个字符就是一个打卡时间,这样的话就可以利用拆分将其拆分开,点击【转换】然后找到【拆分列】选择为按字符数拆分,将字符数设置为5个,选择重复点击确定,这样的话就会将数据拆分数据,这样的话就整理完毕了,只需要【主页】点击【关闭并上载】将数据加载到Excel即可。

Excel整理不规范的考勤数据,3分钟搞定1天的工作量


三、计算迟到早退

因为时间本质上也是数值的一种,可以利用min函数与max函数来提取当天打卡时间的最大值与最小值,也就是第一次打卡时间与最后一次打卡时间。

第一次打卡时间:=MIN(E2:H2)

最后一次打开时间:=MAX(E2:H2)

提取出来后我们就可以利用if函数来计算是否存在迟到早退的情况,这里需要根据自己的实际情况来设置,大家可以动手做一下。


在这里我们利用了powerquery,这个功能是需要版本支持的,需要2016或者版本以上才可使用。






壹人事目前可提供企业社保代缴社保账户托管电子工资条薪资代发节日福利员工花名册智能入职员工商保等一站式人力资源服务。咨询壹人事“在线客服”或致电400-189-1900了解更多,也可以点击官网顶部【注册】,体验壹人事的各项产品。

热门总榜

热门总榜

换一换
关闭