自动核对数据模板-企业银行余额核对表
今天给大家推荐一份我压箱底的核对数据模板,可以自动核对二组数据的差异,哪怕同时出现好几个重复的数据,也能找出差异! 而你需要做的只是把二组数据复制黏贴!
该表中A1单元格表示银行明细账与银行日记账之间有三个数据没有匹配。
“I列”显示银行明细账中一个数据重复几次(表中54321重复了2次,12345重复了3次)。
“J列”用于找出重复的数据是第几次出现(第7、8、9行分别显示12345第1次第2次第3次出现)
“K列”用于银行明细与银行日记账的匹配,匹配成功显示1,未匹配显示0。
最后“A列”用于显示二表之间有几个差异!
第30行为数据截止行!
一:对比银行明细与银行日记账差异
图表左边为银行明细,右边为银行日记账,可以看到二张表分别有三个数据没有配比成功!
补充说明:一般情况下银行收到货款时记到“贷方”,而企业收到货款时记到“借方”,正好相反!
二:匹配后如何在银有账无/银无账有中查询
A1单元格提示银行明细与银行日记账有3个数据不匹配,选中第四行下拉后会发现大于3的数据会被一致重复,故把多余的数据删除即可!
B1单元格一直到H1单元格的数字对应的是银行明细账的第2列、第3列...第10列
另外一张表也是相同的操作!
最后把“银有账无”以及“银无账有”二张表一起核对就能找出二张表中未匹配的数据!
点击视图-新建窗口-全部重排-垂直并排(一个工作簿打开二个sheet)
三:内含函数公式原理
二表匹配用到的函数原理主要分为二层
首先B3单元格输入{=sum(if(A3=$A$3:A3,1))}用于计算同一个数据一共出现几次,注意$A$3:A3的第一个A3单元格需要按F4锁定单元格,这样公式往下拉的时候,出现第二个相同数据的时候就会变成1+1=2,以此类推。
其次C3单元格输入{=SUM(IF(A3=$E$3:$E$7,1)*IF(B3=$F$3:$F$7,1))}
这个函数的原理就是在二表中双条件查找数据,只有A表中的123=B表中的123且A表中第1次出现=B表中第1次出现,匹配时1*1=1,不匹配时1*0=0。
注意{ }不是输入的,等公式完成后同时按Ctrl+Shift+Enter,表示此函数为数组函数!
四:如何使用该模板来进行数据核对
1.在模板中间插入空格,选中一行以后按Shift,鼠标出现上下箭头时下拉就可以增加空行,空行数大于复制的银行明细
2.复制银行明细账后,删除多余行数,一直到灰色行,将A列、I-K列数据下拉
3.银行日记账也是相同操作,最后在“银有账无”以及“银无账有”中核对数据
注意事项:复制黏贴时注意列数的核对,模板中将多余不重要的列已删除,插入空行务必在中间插入,这样公式会自动改变!
如果大家觉得还不错,关注下我的公众号:财税Excel笔记
下载今天分享的银行明细核对模板链接!