入职第一天,老板就和我说,Excel核对数据一定要会

Excel函数 2021-12-18 18:18:20

进行繁杂的对账工作常常是财务人员头疼的事情，不仅仅因为数据量比较大，在实际对账的过程中，可能会遇到各种各样的情况，说起来都是对账，但处理的方法可能有很大的区别，因此今天为大家整理出了一些比较常遇到的问题，都是可以运用EXCEL瞬间完成的，一起来看看都是哪些折磨人的问题吧。

单条件对账

数据说明：左侧为系统订单数据，右侧是手工数据(一般为供货商提供或者文员手工录入登记)，系统数据是完整的，现在需要核对还有哪些订单是缺少手工数据的。

使用VLOOKUP函数查找订单号所对应的手工数据，按照VLOOKUP(查找值，查找范围，查找内容在第几列，精确查找)这个格式代入公式，查找值是系统订单号(A3)，查找范围是手工数据(E:F)，订单号在手工数据的第二列，精确查找时第四参数为0，就有了公式：=VLOOKUP(A3,E:F,2,0)

使用这个公式得到的数据中会出现一些#N/A，表示没有找到对应的数据，也就是系统数据中存在而手工数据中不存在的内容，需要筛选出来查找原因。

这是最常用的一种核对数据的方法，有时候我们不仅仅要核对数据是否存在，还要核对订单金额是否存在差异，这时候使用VLOOKUP就不方便了，需要用到另一个函数SUMIF。

思路是利用SUMIF函数按照系统订单号对手工数据的订单金额求和，再与系统的订单金额相减，根据结果是否为0 差异所在，在D3单元格输入公式：

=SUMIF(E:E,A3,F:F)-B3，双击填充公式，具体效果如图所示：

SUMIF函数的格式为：SUMIF(条件区域，条件，求和区域)，本例中条件区域是手工订单号(E列)，条件是系统订单号(A3)，求和区域是手工订单金额(F列)。

差异为0的就是系统数据与手工数据吻合，差异不为零的数据中有两种情况，一种是没有对应手工数据的情况，还有一种是手工数据存在但是金额不一致，这个结合之前VLOOKUP的结果就很容易看出来。

比如上图中的C9单元格没有出现#N/A错误，但是D9单元格值不为零，说明该订单数据录入错误。

对于比较规范的数据，核对起来也很方便，通常使用VLOOKUP和SUMIF函数就能解决，但在实际工作中，会遇到一些不那么规范的数据，继续来看。

多条件对账

如下所示，右侧为系统数据，只保留了四列，实际上可能是很多列，在核对的时候可以将无用的列剔除。左侧是手工登记的数据，只有三列。

对于系统数据没什么好说的，有些系统比较完善，导出的数据就比较规范，本例的系统数据要挑毛病的话只能说这个费用类型里登记的过于简单，基本没什么有用的信息。

再看手工数据，问题就比较明显了，有两个问题：

第一、日期格式不规范，使用小数点作为日期中年月日分隔符估计是很多小伙伴的习惯，但是这样的格式Excel并不会当做日期来处理;

第二、日期列登记不完整，或许是为了偷懒，有很多空单元格，估计空的是和上面单元格的日期一致，这同样是很多小伙伴的录入习惯吧。

拿到这样的数据，首先要对A列进行处理，处理方法为：选中数据区域，按F5或者Ctrl G打开定位，定位空值后确定，输入=，按一下方向键↑，按着Ctrl键回车完成填充;再选择数据区域，复制粘贴为数值后，点击分列，直接在第三步选择日期格式，完成即可，具体操作看动画演示。

数据处理规范后，就该核对差异了，在这个例子中，需要判断同一个日期下金额存在差异的数据是哪些，这就包含了两个条件：日期、金额。因此考虑用SUMIFS函数，基本结构为SUMIFS(求和范围，条件范围1，条件1，条件范围2，条件2)，还是以系统数据为基础来核对手工数据，在I3单元格输入公式为：

=SUMIFS(B:B,A:A,E3,B:B,H3)-H3，双击填充。

差异为零的表示数据完全吻合，不为零的就需要筛选出来查找差异原因。

因为数据不多，可以看出来有两笔8000的是出现在同一个日期，我们使用SUMIFS进行求和时，会把这两笔进行汇总，实际上并不是真的有差异。对于这种日期一致金额一致但是具体用途不同的，在核对时直接用公式判断比较麻烦，可以考虑借助辅助列来进行重复性判断：

在手工数据后面使用公式：

=COUNTIFS($A$2:A3,A3,$B$2:B3,B3)，意思是对日期与金额相同的进行计数，注意在选择范围的时候，对范围的起始位置要加$进行锁定，这样公式在下拉的时候范围就会递增，当有重复数据出现时，结果也是递增的。

同理，对系统数据也按照这个方法处理，公式为：

=COUNTIFS($E$2:E3,E3,$H$2:H3,H3)

完成了两个辅助列之后，核对金额的公式就变成了三个条件：

=SUMIFS(B:B,A:A,E3,B:B,H3,D:D,I3)-H3，双击填充可看到结果，出现负数就表示手工数据中没有录入该项。

今天用了两个例子来分析数据核对的常用思路，在进行更为复杂的核对工作时，只要掌握VLOOKUP、SUMIF、SUMIFS、COUNTIF和COUNTIFS这几个函数，同时善于使用辅助列的话，基本都是可以很快就找到差异的。