如何在Google Sheets中用数组公式计算含利息与费用的累计投资？

核心思路 #

利用SCAN函数的多值累加器特性，同时跟踪上月累计总额、当月收益与税费，一次性生成所有计算结果，避免循环引用且保证性能。

前置准备 #

在表格空白单元格（如G1）设置月利率（示例为1%），G2设置税率（示例为20%），方便后续公式统一调整。

完整数组公式（一键生成C/D/E列） #

将以下公式粘贴到C1单元格，公式会自动溢出填充C（收益）、D（税费）、E（累计总额）三列：

=LET(
    net_cash, ARRAYFORMULA(A:A - B:B),
    rate, $G$1,
    tax_rate, $G$2,
    results, SCAN(
        {"", "", 0},
        net_cash,
        LAMBDA(acc, curr,
            IF(ISBLANK(curr),,
                IF(ROW(curr)=1,
                    {"0", "0", curr},
                    LET(
                        prev_total, INDEX(acc, 3),
                        earnings, prev_total*rate,
                        taxes, earnings*tax_rate,
                        new_total, prev_total + curr + earnings - taxes,
                        {earnings, taxes, new_total}
                    )
                )
            )
        )
    ),
    INDEX(results, 0, {1,2,3})
)

公式拆解 #

1. LET函数：定义变量简化公式结构，增强可读性
   • net_cash：计算每一行的现金净流入（流入-支出）
   • rate/tax_rate：引用预设的利率与税率
2. SCAN函数：核心迭代计算逻辑
   • 初始累加器{"", "", 0}：对应[收益, 税费, 累计总额]的初始值
   • 第一行特殊处理：收益、税费为0，累计总额直接取当月现金净流入
   • 后续行计算：
     • 从累加器中提取上月累计总额prev_total
     • 计算当月收益：prev_total * rate
     • 计算当月税费：earnings * tax_rate
     • 计算当月新累计总额：上月累计 + 当月净流入 + 收益 - 税费
3. INDEX函数：将SCAN返回的二维结果拆分到C/D/E三列

分列单独设置公式（可选） #

如果需要分别给C/D/E列设置公式，可使用以下写法：

• C列（收益）：

  =ARRAYFORMULA(IF(ROW(A:A)=1, 0, IF(ISBLANK(A:A),, INDEX(E:E, ROW(A:A)-1)*$G$1)))
  

• D列（税费）：

  =ARRAYFORMULA(IF(ROW(A:A)=1, 0, IF(ISBLANK(A:A),, C:C*$G$2)))
  

• E列（累计总额）：

  =SCAN(, ARRAYFORMULA(A:A - B:B), LAMBDA(prev, curr, IF(ISBLANK(curr),, IF(ROW(curr)=1, curr, prev*(1+$G$1*(1-$G$2)) + curr))))
  

验证示例数据 #

代入示例中的数值（A1=1000, B1=200; A2=500, B2=100; A3=300, B3=50; G1=1%, G2=20%），公式将自动生成：

• C2=8, C3=12.064
• D2=1.6, D3=2.413
• E1=800, E2=1206.4, E3=1465.687
  完全匹配预期计算结果。

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