如何用ARRAYFORMULA实现支持买卖操作的累计求和(RUNNING TOTAL)

需求说明 #

需要在表格中计算累计总额：当A列为“buy”时累加B列数量，为“sell”时扣除B列数量，示例如下：

当前使用的公式（sell部分未完成）：

={"Total", ARRAYFORMULA(IF(LEN(A2:A),IF(A2:A="buy",SUMIF(ROW(B2:B),"<="&ROW(B2:B),B2:B), "NEED CODE FOR SELL" ),))}

要求：不修改B列原始数值（B列需用于其他公式），实现累计总额计算。

解决方案 #

可以通过分别计算累计买入总额和累计卖出总额，再做差值的方式实现，公式如下：

={"Total", ARRAYFORMULA(IF(LEN(A2:A), 
  SUMIF(ROW(A2:A), "<="&ROW(A2:A), IF(A2:A="buy", B2:B, 0)) - 
  SUMIF(ROW(A2:A), "<="&ROW(A2:A), IF(A2:A="sell", B2:B, 0)), 
""))}

公式逻辑 #

• 第一个SUMIF：计算当前行及之前所有行中，操作类型为buy的B列数值累计和
• 第二个SUMIF：计算当前行及之前所有行中，操作类型为sell的B列数值累计和
• 用累计买入额减去累计卖出额，得到当前行的累计总额
• IF(LEN(A2:A))用于过滤空行，避免无操作的行显示0值

内容的提问来源于stack exchange，提问作者Nilson