问题:
我在做考勤的时候,需要为每个人的加班/请假情况进行备注,数据透视表虽然有汇总的功能,但是无法汇总成大家能看懂的备注信息(文本)。
经过改进后,我采取把数字信息直接数据透视表+vlookup到考勤表,只要保证全部录入,就能保证数字的准确性,然后再根据透视表汇总的内容,手动添加备注。
如果数字能批量导入了,备注能不能也批量制作呢?此时我就想到了制作模板,批量将信息汇总成文本的方式,减少每个月重复的动作,进一步简化制作考勤,也就是从图1→图2。
图1:数据透视表
图2:备注
以下是我的思路:
一、加班类备注
将加班类的表单数据透视一下,分成三类:技术人员平时加班、技术人员周末加班、总部人员加班。
然后对原表格做一些处理:
蓝色部分为复制下来的列,黄色部分为添加的辅助列,绿色部分为添加的辅助类文字(最终会用上其中的字句)。
通过这个表头,我们再进行下面的处理:
①分类列的处理
就是让加班明细的行数有相应姓名,汇总行没有对应姓名
这可以通过复制“姓名”栏→定位空值填充→筛选并删除“汇总”行的姓名实现。
②文本日期与连接符的添加
首先筛选出明细行→文本日期运用TEXT公式转换(日期→文本),E6公式:=TEXT(B6,“m/d”)→连接符这里是中文“;”
③连续文本的编辑
这里主要用到文本连接符的公式:CONCATENATE
P.S.注意,绿色辅助文字的部分要用绝对引用。
④单人情况汇总
这里是难点,要用到IFERROR和VLOOKUP函数
上图的公式存在一些错误,下图才是正确的:
怎么样,看出来区别了吗?
上图vlookup公式选择的范围下限没有固定,如果下面是空白的,不会影响结果;但假如11行以后还有内容,就会影响统计的结果了。
另:记得范围多选择一行(这里到12行),否则最底下的一行会有错误
--------------------------------------------------------------------------------------------这里主要参考了公众号: Excel应用之家—《VLOOKUP函数竟然也可以合并同类项,快来看看吧!》
⑤生成备注
自此,这里就很容易了,只需要运用CONCATENATE公式将文本拼凑起来即可。
再简单筛选一下,就得到了很清晰的备注和加班情况汇总,vlookup到考勤表即可。
至此,加班部分的备注完成录入。
二、请假类备注
- 请假类的备注就复杂很多,涉及有薪假期、年假和调休/事假几类。
- 由于有些假别只有1-3条,因此这里需要抓大放小,批量的部分只制作调休、事假和年休假。
- 由于调休和事假经常会混淆,另外临近年底,需要给大家先调休年假,因此在制作之前需要进行预计算。
①预计算
- 先把所有假期(除了产假、陪产假、病假)混在一起,通过数据透视表得到每个人请假的总天数;
- 筛选出留存有年假的人及其天数,和请假天数对比,对部分写错的进行纠正;
- 筛选出不够调休的同事,对写错的部分进行纠正;
②添加备注
- 做完以上几步以后,就和添加加班备注一样了,模板如下图所示:
至此,备注就已经全部添加完毕了。