91金融个人金融联盟

财务总监都忍不住点赞的Excel对账方法!超实用~

中华会计网校初级会计职称 2018-11-08 16:37:26

卢子,30万读者支持的清华Excel畅销书作者,每天早上,一篇原创教程,陪你一起成长。不想加班的,关注一下微信公众号Excel不加班,后台回复关键词46,可领取46个Excel视频教程。

请把「Excel不加班」推荐给你的朋友


做财务真的不只是为了拿月薪3万多一点,而是与时俱进,活到老,学到老。


在卢子认识的人中,有不少是做财务的,有些财务经理年薪20-30万,Excel玩得炉火纯青。一句话概括:专业素养加Excel技能,能创造更高价值。


1.再忙也要把这两招对账技能学会!


每年对账,有不少财务都是用最原始最手工的方法,逐笔勾挑的,非常浪费时间。今天,卢子教你两招很实用的对账方法,可以为你省下不少时间。


第一招

现在有两个表,将银行账和手工账进行核对。在实际对账的时候,只有银行的借方金额和手工帐的贷方金额可以核对,其他的信息都是不同的。


银行下载的明细表


手工账的明细表


当金额都是唯一值的时候可以用VLOOKUP函数进行查找核对,但大多数情况下,金额是有可能出现多次的。有重复值的情况下用VLOOKUP函数查找就会出错。


对账要满足两个条件:

01 金额一样

02 金额出现的次数也一样


举个例子,10元在银行这个表出现2次,在手工账这个表也出现2次,证明这个金额是正确的,也就是TRUE,否则就是FALSE。


统计金额的次数,可以用COUNTIF函数,函数语法:

=COUNTIF(条件区域,条件)


在手工账这个表,现在要统计每个贷方金额出现的次数。


统计金额在银行表出现的次数:

=COUNTIF(银行!B:B,G2)


两个公式综合起来:

=COUNTIF(G:G,G2)=COUNTIF(银行!B:B,G2)


将有问题的金额(FALSE)筛选出来,只对这些有问题的进行核对,会减轻很多工作量。


选择任意一个FALSE的单元格,右击,选择“筛选”,单击“按所选单元格的值筛选”。


筛选后的效果。


手工账核对完,银行账也可以用同样方法进行核对。


第二招

系统与手工两个表,必须满足客户名称、金额、日期、出账状态完全相同才是正确的。


系统下载的明细表


手工录入的明细表


思路:将四个条件合并起来,在另外一个表进行计数,次数等于1就是正确。


条件计数的万能公式:

=COUNTIFS(条件区域1,条件1,条件区域2,条件2,条件区域3,条件3,条件区域n,条件n)


在手工表的E2输入公式,并向下填充公式,显示0的就是错误的。

=COUNTIFS(系统!$A$2:$A$20,A2,系统!$B$2:$B$20,B2,系统!$C$2:$C$20,C2,系统!$D$2:$D$20,D2)


用同样的方法,在系统表的E2输入公式,并向下填充公式,显示0的就是错误的。

=COUNTIFS(手工!$A$2:$A$20,A2,手工!$B$2:$B$20,B2,手工!$C$2:$C$20,C2,手工!$D$2:$D$20,D2)


这样就能找出两个表不同的内容,再筛选出0即可。


说明:如果手工表存在空格或者格式跟系统表不一致,必须先进行处理,将手工表和系统表都整理成规范表格再对账。


2.用最快的方法将金额合计为540.00的银行明细找出来


手工记录的时候都是记录每一笔账的总金额,而实际银行明细有的时候是多条记录的,现在要将贷方金额为540.00的银行明细找出来。


如果不懂方法,这种是要花费大量的时间和精力。其实借助规划求解的功能也可以快速帮你实现。


默认情况下,Excel是没有规划求解的功能,需要重新加载才可以。


单击“文件”→“选项”→“加载项”→“转到”。


勾选“分析工具库”和“规划求解加载项”,单击“确定”按钮。


这样就可以在“数据”选项卡的右边看到“规划求解”这个功能。


添加完后,就可以开始操作。


Step 01 在D2输入公式。

=SUMPRODUCT(A2:A67,B2:B67)


Step 02 在“数据”选项卡的最右边单击“规划求解”,在弹出的“规划求解参数”对话框,设置目标为$D$2,目标值为540.00,通过更改可变单元格为$B$2:$B$67,单击“添加”按钮。


Step 03 单元格引用为$B$2:$B$67,选择bin,约束为二进制,单击“确定”按钮。


Step 04 设置完条件,单击“求解”按钮。


Step 05 经过大概1分钟就将目标值计算出来,单击“确定”按钮。


Step 06 这样B列为1的就是满足条件的值,选择任意一个1,右击,选择“筛选”→“按所选单元格的值筛选”。


这样就将所有符合条件的金额筛选出来。


当然规划求解也不是万能的,当数据比较多的时候也是求解不出来,这时需要借助超级复杂的VBA代码。


将明细的金额复制到A列,在B2输入目标值540.00,单击“开始凑金额”按钮。


瞬间就将满足条件的组合值列在F列。


操作动画



用鼠标单击它,在你不经意间,这个动作背后隐藏的付出,只有原创作者本人才知道。



赠送的Excel视频教程:


别忘了关注微信公众号Excel不加班,后台回复关键词46,可领取46个Excel视频教程。


请把「Excel不加班」推荐给你的朋友

友情链接