实用EXCEL之考勤数据的表合并

时间:2024-03-30 18:47:19

在日常工作中,从事人事相关的朋友经常需要花很大的时间到考勤数据的合并,即机器导入的出勤数据与手工登记的请休假数据合并,算出一个考勤总表;于是帮忙写了一个公式,简化繁锁与没趣的这部分工作;公式主要使用:VLOOKUP实现;具体使用说明见execl里的说明。


数据A:考勤机导出的数据
实用EXCEL之考勤数据的表合并
实用EXCEL之考勤数据的表合并



数据B:手工登记的数据

实用EXCEL之考勤数据的表合并
实用EXCEL之考勤数据的表合并

数据C:2表去重复及列合并后的数据
实用EXCEL之考勤数据的表合并
实用EXCEL之考勤数据的表合并




说明:
“6月考勤合并”标签中:
第1个表格是OA或机器导出的考勤数据;
第2个表格是手工登记与OA数据合并后的表格结果;
第3个表格是手工登记数据,即调休假、请假等;


前置条件:
1.3个表格的表头要一致;如6月有30天,就有30列;
2.3个表格的列位置要对应一致;


使用说明:
1.把第1个表格数据按自己公司的格式替换;
2.把第3个表格数据按自己公司的格式替换;列数要求也第1个表格一致;
3.把第3个表格的“部门”、“姓名”、“星期”列复制,替换第2个表格的“部门”、“姓名”、“星期”列;
4.在第2个表格的“星期”列右边一列,开始重新拖曵一下,就可以得到第1、第2表格的合并结果;




公式说明:以E90单元格为例
如果E166为空,使用$B90的值(即欧阳勇)到只定范围查找(范围即$B$1:$AI$84),找到返回COLUMN(D1)列号的值(COLUMN(D1)的结果为4,即第4列,就是第1个表格从“姓名”开始算第4列的值),0表示是大致匹配;最后E166不为空显示E166的值;
公式:
=IF(ISBLANK(E166),VLOOKUP($B90,$B$1:$AI$84,COLUMN(D1),0),E166)


函数解析:
IF:判断是否满足某一条件
ISBLANK:检是否引用了空单元格
VLOOKUP:搜索表区域首列满足条件的元素(第1个参数是首列,第2个参数是数据范围,第3个参数是要返回的列号,第4个参数是精确查找或是大致匹配)
COLUMN:返加一引用的列号
$:表示绝对定位,就是拖曳公式也不会变化;
$B$1:表示列、行都绝对定位;