当前位置: > 热博

这也许是Excel对账的最快速度,仅需1秒搞定!

时间:2022-04-22 20:22:40 热博 我要投稿

对账是永恒的话题,很多会计为了几毛钱加班一两天的事经常发生。下面卢子来一场对账大总结。

好消息先放前面,让你高兴一下。如你所愿,将对账模板进一步改善,现在实现凑金额和单笔混合的对账。

1.入门级别

这种属于理论上的对账,实际很少遇到这么简单的。

01 顺序一样,核对数据

选择区域,按快捷键Ctrl+,这样就选中工资不同的,再填充颜色即可。

02顺序一样,多行多列核对数据

选择上面区域的内容复制,点B12,右键,选择性粘贴,减,确定,不是0就是有差异的。

03顺序不一样,核对数据

选择A1,点数据→高级筛选,这样默认就选好了列表区域,再选条件区域,确定。筛选出来的就是一样的。

对筛选出来的结果填充颜色,再清除筛选,这样有颜色的就是一样,没有颜色就是有差异的。

2.普通级别

这种在工作上经常遇到,脱离了理论,回到实际。

01汇总跟明细核对

左边是科目余额表,数据都已经汇总。右边是台账,数据是明细的,人员都有多条记录。现在要核对两边每个人员的总金额。

根据人员进行条件求和,也就是SUMIF函数。

=SUMIF(F:F,A3,G:G)

SUMIF语法:

=SUMIF(条件区域,条件,求和区域)

再对金额相减,如果是0证明数据没问题。

=SUMIF(F:F,A3,G:G)-B3

在实际工作中,很少碰到这种全是0的,或多或少总会出现点问题。

假如台账的数据要来核对科目余额表,也就是反过来。

先对台账按人员进行汇总。

=SUMIF(F:F,F3,G:G)

再查找科目余额表对应的金额,查找除了用VLOOKUP函数,这里也可以用SUMIF函数。

=SUMIF(A:A,F3,B:B)

最后,两个SUMIF相减。

=SUMIF(F:F,F3,G:G)-SUMIF(A:A,F3,B:B)

像这种两边核对金额的,还有一个很好用的方法,就是合并计算。

在使用之前有一个注意点:两边的金额名称改成不一样,比如金额1、金额2,这样合并后就会变成两列。如果名称一样,就变成一列没法核对。

选择一个空单元格如G2,点数据,合并计算,依次添加两个表的区域,勾选首行、最左列,确定。

这样就按人员统计了两边的金额。

写上标题,再进行相减就可以。

=H3-I3

02 明细跟明细核对

企业跟银行对账,金额不对,就显示红色填充色。

卢子输入几个金额,来演示效果。两边的金额出现同样次数,就颜色自动消失,只有一边有出现的金额,立即显示红色填充色,这样真的一目了然。

原理说明:

在B2输入公式,下拉。

=IF(A2="","",A2&"|"&COUNTIF(A$2:A2,A2))

COUNTIF(A$2:A2,A2)的作用就是累计每个金额出现的次数。

A2&"|"&COUNTIF(A$2:A2,A2)的作用就是金额连接次数,这样就能识别同一个金额出现多次。比如99,一共出现2次,分别显示99|1,99|2,这样全部内容都是唯一值。

同理,D列也进行同样的设置。

=IF(C2="","",C2&"|"&COUNTIF(C$2:C2,C2))

有问题的数字,自动填充颜色又是如何实现的呢?

其实借助条件格式就可以轻松搞定,默认就有重复值和唯一值设置颜色的功能。

按住Ctrl键,选择B列和D列的区域,点条件格式→突出显示单元格规则→重复值。

下拉选择唯一值,确定。唯一值就是有问题的,这样就搞定了。

3.进阶级别

01 凑金额

从一堆金额中,找到总金额为一个固定值,这种也确实让人头痛。如现在有一堆金额,要找到目标值为270572.63的组合。

将一堆金额放在A列,目标值放在B2,点开始凑数,瞬间找到满足条件的组合+135800+76377.73+58394.9。

可点击文章进去下载:

别弄丢!年底都会用到的7个Excel模板,10秒钟搞定半天工作

02凑金额(多个数字)

精准凑金额

允许误差凑金额

先在H2手工输入允许误差的金额,比如1。

这样相差1元的都可以凑出来。

可点击文章进去下载:

收藏!支持精准和允许误差对账,凑金额V2.1版

4.高手级别

也是汇总跟明细核对,但是比前面的更加高级,更符合实际情况。点一下就核对完成,是不是很爽?

截图看效果更清晰,企业500就跟银行的E10(500)对上,企业的1500就跟银行的E4 (300)+E6 (200)+E9 (1000)对上。正确的核对完都会有背景色,没有颜色的就证明有问题。

操作步骤:

点对账按钮,就激活这个流水对账标识对话框,点清空所有区域,将你的金额复制粘贴到A列和E列,E列有多少行,最大差异行数就写多少,准备工作做完,点开始核对,就可以。

这个模板来之不易,文末点赞后再来领取吧。

思想有多远,Excel就能走多远,Excel实在太强大了,没想到这种需求也能实现。这就是学Excel的意义,将你以前需要花几小时甚至几天的工作,变成几秒钟。

模板链接:

https://pan.baidu.com/s/1r_iJlxvItqFqvg2rOJrrNg?pwd=r3to

提取码:r3to

应粉丝需求,重新推出终生VIP会员,这个是永久,还是3年前的价格。888元那个是一年期的,也就是多几百元就从一年期变成终生,更划算。

陪你学Excel,一生够不够?

一次报名成为VIP会员,所有课程永久免费学,永久答疑,仅需1500元,待你加入。

报名后加卢子微信chenxilu2019,发送报名截图邀请进群。

恭喜粉丝:梅雯雯、Szl_牛奶仔伦、魔幻熊คิดถึง,获得书籍《Excel 跟卢子一起学 早做完,不加班》,加卢子微信chenxilu2019

推荐:这是我用过最好用的Excel对账模板,没有之一

上篇:年度数据统计,超级透视表显神威

对于银行和企业的流水对账,你见过什么好用的方法?

作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)