根据 Transaction 类型计算正负值与 running balance

2026-10-24支付阿杰

结论

可以。Amount 列中的金额保持为正数，Balance 列根据 Transaction 类型判断加减，再加上上一行的余额。

按示例中的余额变化：

Payment、Credit Issued：欠款减少，从余额中减去。
Rent Incurred、Fee Issued、Payment Reversed、Copier Usage：欠款增加，加到余额中。

这里的正负表示交易对余额的影响，并不需要修改 Amount 单元格中的金额。D2 用于手工输入金额，不能同时存放计算公式。

基本公式

假设各列为：

C 列：Transaction
D 列：Amount
E 列：Balance

在第一条数据对应的余额单元格 E2 中输入：

=IF(OR(C2="Payment",C2="Credit Issued"),-ABS(D2),ABS(D2))

从第二条数据开始，在 E3 中输入：

=E2+IF(OR(C3="Payment",C3="Credit Issued"),-ABS(D3),ABS(D3))

然后将 E3 的公式向下填充。

使用 ABS(D3) 后，即使金额被误填为负数，公式仍会根据交易类型确定正确的加减方向。

使用一个公式直接向下填充

如果想从 E2 开始使用同一个公式并向下填充，可以输入：

=IF(OR(C2="",D2=""),"",N(E1)+IF(OR(C2="Payment",C2="Credit Issued"),-ABS(D2),ABS(D2)))

其中：

N(E1) 会将标题等文本转换为 0，所以第一条记录也能正常计算。
填充到后续行后，N(E1)、N(E2) 等会读取上一行的余额。
如果交易类型或金额为空，余额也会留空。

严格检查所有交易类型

上面的公式会把 Payment 和 Credit Issued 之外的所有交易都当作欠款增加。如果以后在下拉列表中添加了新类型，或者交易类型出现拼写错误，余额可能会算错。

可以改用 SWITCH，明确列出每一种交易类型：

=IF(OR(C2="",D2=""),"",
 N(E1)+SWITCH(
   C2,
   "Payment",-ABS(D2),
   "Credit Issued",-ABS(D2),
   "Rent Incurred",ABS(D2),
   "Fee Issued",ABS(D2),
   "Payment Reversed",ABS(D2),
   "Copier Usage",ABS(D2),
   NA()
 ))

如果交易类型不在清单中，NA() 会返回错误，防止无效数据被直接计入余额。

按 agent 分别计算余额

示例中的所有交易共用一个总余额。如果需要分别显示每位 agent 的欠款余额，可以按 agent 汇总截至当前行的交易：

=SUMPRODUCT(
 ($B$2:B2=B2)*
 IF(
   (($C$2:C2="Payment")+($C$2:C2="Credit Issued"))>0,
   -ABS($D$2:D2),
   ABS($D$2:D2)
 )
)

将公式放入 E2，然后向下填充。它只累计 B 列中与当前行 agent 相同的记录，因此每位 agent 都会有单独的 running balance。

常见错误

原公式有三个主要问题：

IF 的正确结构是 IF(condition, value_if_true, value_if_false)。
判断多个条件时应使用 OR(...)，不能直接连接多个字符串。
running balance 应引用上一行的 Balance，例如 E2，而不是交易类型列中的 C1。

如果电子表格所在地区使用分号分隔参数，需要将公式中的逗号替换为分号。交易类型文本也必须与下拉列表完全一致，包括空格和拼写。