入职第一天,老板就跟我说Excel一定要善于查数据。

复杂的对账工作往往是财务人员比较头疼的事情,不仅因为数据量大,而且在实际对账过程中,可能会出现各种情况,都是对账,但是处理方式可能会有很大的不同。所以今天我们为大家整理了一些常见的问题,这些问题都可以用EXCEL瞬间完成。让我们来看看所有令人痛苦的问题。

一个

单一条件协调

数据描述:左侧为系统订单数据,右侧为手工数据(一般由供应商提供或业务员手工录入登记)。系统数据完整。现在,需要检查哪些订单缺少人工数据。

入职第一天,老板就和我说,Excel核对数据一定要会-大盘站插图

用VLOOKUP函数找到订单号对应的手工数据,按照VLOOKUP的格式(搜索值,搜索范围,搜索内容是哪一列,精确搜索)代入公式。搜索值是系统订单号(A3),搜索范围是手动数据(E:F),订单号在手动数据的第二列。精确搜索时,第四个参数为0,于是有了公式:= vlookup (A3,A3)。

入职第一天,老板就和我说,Excel核对数据一定要会-大盘站插图1

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

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

其思路是利用SUMIF函数根据系统订单号对人工数据的订单金额求和,然后从系统订单金额中减去。根据结果是否为0,在D3单元格中输入公式:

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

入职第一天,老板就和我说,Excel核对数据一定要会-大盘站插图2

SUMIF函数的格式为SUMIF(条件区、条件和求和区)。在本例中,条件区域是手动订单号(E列),条件是系统订单号(A3),合计区域是手动订单金额(F列)。

如果差值为0,则系统数据与手动数据一致。如果差额不为零,有两种情况,一种是没有对应的手工数据,另一种是有手工数据但金额不一致。这个组合之前VLOOKUP的结果很容易看出来。

例如,上图中的单元格C9没有#N/A错误,但单元格D9的值不为零,这表明订单数据输入不正确。

对于比较规范的数据,查起来也很方便。通常可以使用VLOOKUP和SUMIF函数来解决。但是在实际工作中,会遇到一些不太规范的数据。继续找。

2

多条件协调

如下图,右边是系统数据,只保留四列,但实际可能有很多列,验证时可以剔除无用的列。左边只有三列手动注册的数据。

入职第一天,老板就和我说,Excel核对数据一定要会-大盘站插图3

系统数据没什么好说的。有些系统比较完善,导出的数据也比较标准。如果要挑这个例子中系统数据的毛病,只能说这个费用类型中的登记太简单了,基本没有什么有用的信息。

看一下手册资料,问题比较明显,有两个问题:

第一,日期格式不规范。估计用小数点做日期的分隔符是很多小伙伴的习惯,但是这种格式Excel不会把它当成日期;

二是日期栏登记不全,可能是偷懒,空格多。估计空单元格与上述单元格日期一致,这也是很多小伙伴的录入习惯。

要得到这样的数据,第一步是处理a列,处理方法是:选中数据区域,按F5或Ctrl G打开定位,定位空白值后确定,回车=,按箭头键↑,按Ctrl回车完成填充;再次选择数据区,复制粘贴为数值,点击列表,直接选择第三步的日期格式,然后完成。具体操作见动画演示。

入职第一天,老板就和我说,Excel核对数据一定要会-大盘站插图4

数据处理标准化后,就该检查差异了。在这个例子中,需要判断哪些数据在同一天有金额差异,这包括两个条件:日期和金额。因此,可以考虑使用SUMIFS函数,其基本结构为SUMIFS (sum range,condition range 1,condition 1,condition range 2,condition 2),或者根据系统数据检查手动数据,在单元格I3中输入公式如下:

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

入职第一天,老板就和我说,Excel核对数据一定要会-大盘站插图5

如果差值为零,说明数据完全一致;如果不是零,就需要筛选出来,找到差异的原因。

因为数据不多,可以看出有两支8000的笔出现在同一个日期。当我们用SUMIFS做求和的时候,会把这两笔进行求和,但实际上并没有真正的区别。如果日期一致,金额一致但具体用途不同,在验证时直接用公式判断比较麻烦。你可以考虑使用辅助栏来进行重复判断:

入职第一天,老板就和我说,Excel核对数据一定要会-大盘站插图6

手动数据后使用公式:

= COUNTIFS ($ a $2: A3,A3,$ b $2: B3,B3),这意味着对相同的日期和金额进行计数。注意,在选择范围时,要通过加$,锁定范围的起始位置,这样公式下拉时范围会增大,出现重复数据时结果也会增大。

同样,系统数据也按此方法处理,公式为:

= COUNTIFS($2美元:E3 E3,$ 2美元:H3 H3)

入职第一天,老板就和我说,Excel核对数据一定要会-大盘站插图7

完成两个辅助栏后,检查金额的公式变成三个条件:

= SUMIFS (B: B,A: A,E3,B: B,H3,D: D,i3)-H3。双击填充以查看结果。负数表示此项未输入人工数据。

入职第一天,老板就和我说,Excel核对数据一定要会-大盘站插图8

今天用两个例子来分析数据对账的常见思路。在进行比较复杂的对账时,如果掌握了VLOOKUP、SUMIF、SUMIFS、COUNTIF、COUNTIFS的功能,并且善于使用辅助列,基本上可以很快发现差异。