求助财务合并报表中的合并抵销公式

zhouxiao

各位同学|老师: 好,附件的抵销公式大概写了个公式,较手工填写效果尚有差距,请抽空帮忙看看,公式怎么设计更好,谢谢

规则:
1)根据客户名称对资产与负债求和,比较同客户名称(或近似客户名称)的资产合计与负债合计
2)当同一客户名称同时有资产和负债合计金额时,才抵销
3)当资产合计>负债合计,G列写入科目类别为[负债]对应的[F列]子报表金额
4)当资产合计<负债合计,G列写入科目类别为[资产]对应的[F列]子报表金额

更正一下附件举例的表述:
客户名称为A或A1,资产金额24548361.43<88040000,则抵销24548361.43,返回第1行和第3行的子报表金额
第17行也抵销24548361.43,返回第1行和第3行的子报表合计金额

感谢藍色DE流光兄的关注，最终用9楼的公式实现结果
=IF((COUNTIFS(A$2:A2,A2,E$2:E2,LEFT(E2)&"*")>1)*(TEXT(SUM(SUMIFS(F:F,A:A,{"资产","负债"},E:E,LEFT(E2)&"*")*{1,-1}),"负债;资产")<>A2),"",IF(TEXT(SUM(SUMIFS(F:F,A:A,{"资产","负债"},E:E,LEFT(E2)&"*")*{1,-1}),"负债;资产")=A2,F2,SUMIFS(F:F,A:A,"<>"&A2,E:E,LEFT(E2)&"*")))

详见附件

合并抵销.zip (10.28 KB, 下载次数: 189)

2020-2-5 14:39 上传

点击文件名下载附件

zhouxiao

数据量不多,就几十行,设计出数组公式来解决,应该不难
以前看到过类似求助的公式帖,无奈搜索不到了.
我的数组公式功底较差,目前只是用SUMIFS做了一个类似的.
较手工填写效果还有点差异,望各位给予指导.

zhouxiao

不要被求助标题吓到,附件实质上就是一个很简单的问题.
数组公式功底好的同学,解决起来应该是分分钟的事情.

藍色DE流光

不是财务工作者看不懂资产负债表，H1下拉试试看啊，屏蔽0值

=IF(TEXT(SUM(SUMIFS(F:F,A:A,{"资产","负债"},E:E,LEFT(E2)&"*")*{1,-1}),"负债;资产")=A2,F2,SUMIFS(F:F,A:A,"<>"&A2,E:E,LEFT(E2)&"*"))

zhouxiao

藍色DE流光 发表于 2020-2-5 15:47
不是财务工作者看不懂资产负债表，H1下拉试试看啊，屏蔽0值

=IF(TEXT(SUM(SUMIFS(F:F,A:A,{"资产","负债 ...

兄弟谦虚了。
感谢关注

zhouxiao

藍色DE流光 发表于 2020-2-5 15:47
不是财务工作者看不懂资产负债表，H1下拉试试看啊，屏蔽0值

=IF(TEXT(SUM(SUMIFS(F:F,A:A,{"资产","负债 ...

兄弟的公式写的很巧妙，套用公式后，第18行的结果同第17行结果重复，手工操作中第18行应该是没有结果的。请再帮忙看看，公式如何调整一下。

藍色DE流光

刚才也考虑到这个问题，因为需要判断的比较多，所以公式有点长

zhouxiao

藍色DE流光 发表于 2020-2-5 16:25
刚才也考虑到这个问题，因为需要判断的比较多，所以公式有点长

非常感谢！
如果把第3行的客户名称B更改为A时，那第4行的的金额就会重复。
请再看看公式还能做优化嘛？

藍色DE流光

zhouxiao 发表于 2020-2-5 17:11
非常感谢！
如果把第3行的客户名称B更改为A时，那第4行的的金额就会重复。
请再看看公式还能做优化嘛？ ...

因为E列A和A1的缘故，用通配符可解决
=IF((COUNTIFS(A$2:A2,A2,E$2:E2,LEFT(E2)&"*")>1)*(TEXT(SUM(SUMIFS(F:F,A:A,{"资产","负债"},E:E,LEFT(E2)&"*")*{1,-1}),"负债;资产")<>A2),"",IF(TEXT(SUM(SUMIFS(F:F,A:A,{"资产","负债"},E:E,LEFT(E2)&"*")*{1,-1}),"负债;资产")=A2,F2,SUMIFS(F:F,A:A,"<>"&A2,E:E,LEFT(E2)&"*")))

zhouxiao

藍色DE流光 发表于 2020-2-5 17:56
因为E列A和A1的缘故，用通配符可解决
=IF((COUNTIFS(A$2:A2,A2,E$2:E2,LEFT(E2)&"*")>1)*(TEXT(SUM(SUMI ...

感谢藍色DE流光兄！
今后还要多向兄弟学习。问题解决