文/王乐生
以前使用excel都是处理一般的数据，今天老板交了一个任务给我—让我使用指纹打卡机里面的数据做出考勤表出来。当时一听到这个任务，我是懵逼的，不信你看。
PS：在网上找不到类似的解决办法，我在思考为什么，是不是另有玄机或者更好的解决办法而我不知道？？？
这是指纹打卡机原始数据：
指纹打卡机原始数据
老板要求做出来的效果：
老板要求做出来的效果
其实做出这样的效果最简单的就是——手工输入。但是我嫌太麻烦而且又太慢了（眼睛疼），所以就想办法用excel做出这样子。
首先我想到的是上网查，结果大家都是简单粗暴只有上下班，没有像老板要求的这么复杂的例子。无奈。。。
好啦，经过两个多小时辛苦的探索，我做出来的效果图如下：
最终做出来的效果图
做的时候真的是眼睛疼，而且又紧张，不过还好老天眷顾+脑瓜子灵光，终于也算是做出来了，可能没有那么好看，可是我这个版本数据很齐全对不对！
好啦，现在开始说步骤。
第一步，把打卡机数据ctrl+A全选，ctrl+c复制，打开一个excel文档，ctrl+v粘贴。
把行列对齐
初步的效果图如下：
把数据粘贴到excel中的效果
好了，现在我们要做的就是先给name（姓名）一列排序，先随便选中name（姓名）一列的某个单元格（注意：有姓名的单元格）数据—>排序—>A-Z
排序
接下来选中name（姓名）一列，ctrl+c复制，粘贴在DateTime（打卡时间）后面。现在效果如图：
复制名字列
删除空行
接下来我们要从DateTime（打卡时间）里面取出日期和时间
取好列名
在日期的第一格使用=INT(G2)公式，取出日期
使用int函数取出日期
回车后的效果图
截取日期的效果图
在这里为什么结果会是数字，因为这是取出来的时间戳，时间戳都是用数字表示，我们将这一列单元格设置成日期格式
设置日期列单元格格式
设置好之后的效果图
单元格格式设置好的效果图
接着，把鼠标放在这个单元格的右下角，当鼠标变成黑色的实心的十字架时，将整列填充完毕
日期列效果图
接下来取时间
使用=G2-I2公式，取出时间
使用=G2-I2取时间公式示例
记得要给时间列设置单元格格式
给时间列设置单元格格式
使用鼠标十字架填充时间列
取出日期和时间效果图
接下来很重要的一步，通过时间分辨出每个时间段上下班的标志，在这里我们要分辨两次。
因为第一次是分辨准确的上下班时间段，第二次则是分辨出上午下午晚上就OK了
先取好列名
取好列名
使用 =IF(J2<--"8:10:00","上午上班",IF(J2<--"12:20:00","中午下班",IF(J2<--"13:40:00","下午上班",IF(J2<--"17:40:00","晚上下班",IF(J2<--"18:10:00","加班上班","半夜下班"))))) 函数分辨出上下午上下班时间
使用公式效果图
使用鼠标十字架填充好上下午上下班时间这一列
接下来我们来使用 =IF(J2<--"8:10:00","上午",IF(J2<--"12:10:00","上午",IF(J2<--"13:40:00","下午",IF(J2<--"17:40:00","下午",IF(J2<--"18:10:00","晚上","晚上"))))) 函数分辨出上午下午晚上
使用公式分辨上午下午晚上
使用鼠标十字架填充好上下午这一列
分辨时间段的效果图
现在选中name（姓名）、日期、时间、上下午上下班时间、上下午这五列
选中需要的五列
点击数据——>数据透视表
选择数据透视表
弹出来的对话框
点击弹出来的对话框——>确定按钮
然后会出现以下页面，重点在右边→_→
重点看右边
点击透视表区域上面的复选框，按以下效果图排列
必须要是一模一样的，否则后面的效果做不出来。
行列字段可以拖动
行列排列的效果图
现在点击值——>计数项：时间旁边的黑色小三角
点击小三角出来的效果
选择“值字段设置”
在弹出来的对话框选择“最小值”—>点击确定
选择最小值
然后在数据区域设置时间格式
效果图如下
效果图
现在汇总太多看起来有点麻烦
选中“上午汇总”单元格右击，将分类汇总“上下午”的√去掉
去掉分类汇总上下午
因为现在在数据透视表里面，所以里面的数据我们现在还不能随意的修改和删除，现在我们复制所有的数据，粘贴到另一个excel文件里面
粘贴到新的excel文件中
粘贴好后，现在我们就可以随意的对数据进行修改、添加和删除了
注意：出现######号是因为日期格式不对，设置一下日期格式就好了
接下来，我们删除没用的日期时间，调整上下午的位置，然后在汇总的前面插入一行来计算每天加班的时间
效果图
使用 =INT((HOUR(E7-E6)*60+MINUTE(E7-E6))/30)/2 公式来计算加班的时间
注意，加班这一行的单元格格式要是数值型的，加班汇总那一行的单元格格式也要是数值型的
加班汇总所使用的函数：sum（）；
用完公式后的效果图如下：
效果图
在这里要注意的是，如果单元格有数据空白则计算不出来数据
如果有多个员工，那么计算加班的方法差不多，公式也只要修改行号就好了
最终效果图
附：我们应该怎么知道单元格的地址
怎么知道当前单元格的地址
在上图的左上角红色框框内的E8就是当前（绿色被选中的单元格）单元格的地址。
以上。
网友评论