excel剩余金额公式.docx
《excel剩余金额公式.docx》由会员分享,可在线阅读,更多相关《excel剩余金额公式.docx(11页珍藏版)》请在冰豆网上搜索。
excel剩余金额公式
excel剩余金额公式
VIP学员的问题,要在筛选状态下计算余额。
余额我写过好多公式,第一次写这种。
先来回顾一些常用的计算余额方法。
1.最常用的,也就是不考虑其他任何情况。
N是将标题转换成0,这样就可以相加,否则会出现错误值。
=B2-C2+N(D1)
当然,这里也可以采用累加借方再减去累加贷方。
区域采用混合引用,这样下拉的时候逐渐区域变大,从而可以累加区域。
=SUM(B$2:
B2)-SUM(C$2:
C2)
如果你懂数组公式,也就是按Ctrl+Shift+Enter结束的公式,2个SUM还能合并起来。
=SUM(B$2:
B2-C$2:
C2)
2.根据对方名称(同一个账户)分别算余额。
分别计算,也就是按条件,最常用的就是SUMIF-SUMIF。
=SUMIF(E$2:
E2,E2,B$2:
B2)-SUMIF(E$2:
E2,E2,C$2:
C2)
同理,如果会数组公式,可以用SUM简化公式。
SUM可以直接求和,也可以条件求和,数组的用法非常多。
=SUM((E$2:
E2=E2)*(B$2:
B2-C$2:
C2))
扯了一大堆不相关的,现在进入主题,怎么在筛选状态下求余额?
3.筛选状态下求余额
涉及到筛选的,都要用到SUBTOTAL函数,求和是109(9),计数是103(3)。
这个公式就类似于前面2个SUM相减。
=SUBTOTAL(9,B$2:
B2)-SUBTOTAL(9,C$2:
C2)
这样在筛选状态下就可以正常求余额。
前面说过用SUM数组可以简化公式,不过你如果用SUBTOTAL却发现使用不了,这个函数的第二参数只能是区域,跟SUMIF区域类似,限制性太大。
这里可以通过辅助列,判断内容有没被筛选,可见单元格都返回1。
=SUBTOTAL(3,E2)*1
有了这个辅助列以后,就可以用SUM数组。
=SUM((B$2:
B2-C$2:
C2)*(F$2:
F2=1))