两个复杂表格数据核对,Vlookup和Match函数组合,也能轻松搞定!

Excel实用案例分享 2024-09-14 13:51:23

今天讲下两个表进行数据核对的事,还是近期的Sap工资测算导出表,需要与线下Excel工资核算表,进行数据比对:每个工资项目线上与线下核算结果的差异;每个人每个工资项目线上与线下核算结果的差异。

先来看下两个表长什么样子:

表一

表二

表一就是我们上篇讲的隐藏0值列后Sap工资测算导出表,表二是线下Excel工资核算表(列数也很多,全部为0值的列也隐藏起来)。因为两表都有唯一值的“SAP编号”字段,那么依据该字段查询两表工资项信息就容易了。

表一“Sap导出数据”sheet的各工资项目就是M2-FC2各项, 表二“线下数据”sheet的工资项目就是J5-DE5各项。

但是我们发现,相同的工资项,在两表中的具体名称是不一致的,这样就没法比对,所以这时我们需要在表二加辅助行,就是第三行,在辅助行将与表二工资项相对应的表一工资项的名称填上,这一辅助行非常关键。添加辅助行后如下图:

方法:复制表一M2-FC2各工资项(仅复制可见单元格)到“辅助表-工资项”sheet,然后使用“数据验证”功能将“线上线下工资项核对”sheetC4单元格设置为可选工资项。想要核对哪一个工资项,可以直接选择该工资项核对。

为便于核对,我们将“线下数据”sheet复制到“线上线下工资项目核对”sheet的G列到DJ列

C6单元格查询的是线下工资表中该工资项(C4)的值,公式说明:

如果表二“线下数据”中的工资项与表一中的工资项是一一对应的,可以用公式INDEX(G6:DJ6,1,MATCH(C$4,$G$3:$DJ$3,0))

MATCH(C$4,$G$3:$DJ$3,0)部分,返回的是C4单元格“养老保险”工资项,在本sheet的G:DJ列的第几列,结果是表示位置的数字

INDEX(G6:DJ6,1,MATCH(C$4,$G$3:$DJ$3,0)),返回本sheetG6:DJ6区域第MATCH(C$4,$G$3:$DJ$3,0)列的值

如果表二“线下数据”中的一个或多个工资小项对应表一中的一个工资,则用公式SUMIFS(G6:DJ6,$G$3:$DJ$3,C$4)

D6单元格查询的是Sap导出数据表中该工资项(C4)的值,公式说明:

=VLOOKUP($B6,Sap导出数据!$D$2:$FC$154,MATCH(C$4,Sap导出数据!$D$2:$FC$2,0),0)

MATCH(C$4,Sap导出数据!$D$2:$FC$2,0) 部分,返回的是C4单元格“养老保险”工资项,在“Sap导出数据”sheet中的D:FC列中的第几列

VLOOKUP($B6,Sap导出数据!$D$2:$FC$154,MATCH(C$4,Sap导出数据!$D$2:$FC$2,0),0)部分,返回B6单元格sap编号为2的员工,在“Sap导出数据”sheet中的“养老保险”值

定位/查询出每个工资项线上导出数值和线下工资表值后,就可以比对了

在“个人核对”sheet中,C2:H3区域用Vlookup函数将个人信息查询出来,这里可以按个人需求,设置要哪些信息

C6:AN7区域的查询公式,参照工资项核对公式,此处不再赘述

本案例两个表格,虽然冗长,其实并不复杂,把核对模板做好,后续再进行测试的时候,只要把导出的数据复制到表一和表二里,仍然能使用,可以说是一劳永逸。

0 阅读:1