如何在Google Sheets中用Array Formula配合XIRR批量计算E列各行XIRR值

Google Sheets 批量计算逐行XIRR的实现方案 #

XIRR函数本身不支持原生数组批量输出，直接套用基础ARRAYFORMULA会出现逻辑偏差，可通过Google Sheets内置的MAP+LAMBDA函数组合实现全列自动计算，即使数据量超过5000行也无明显性能损耗，仅需在E2单元格输入一次公式即可自动覆盖所有数据行，无需逐行下拉复制。

适配累计现金流场景的公式（匹配8行示例的常见计算逻辑） #

这类场景的计算逻辑为：逐行汇总从数据起始行到当前行的全部现金流与对应日期，计算当前累计区间的XIRR值。在E2单元格输入以下公式，可根据实际的现金流、日期所在列调整引用：

=MAP(ROW(B2:B), LAMBDA(r, IF(COUNTA(OFFSET(B2,0,0,r-1,1))<2,, XIRR(OFFSET(B2,0,0,r-1,1), OFFSET(C2,0,0,r-1,1)))))

• 逻辑说明：
  • 逐行遍历所有数据行，自动识别当前行的相对位置
  • 增加前置判断：当累计到当前行的非空现金流不足2笔时，返回空值，不触发计算，避免无意义的错误弹出
  • 自动匹配对应区间的日期范围完成XIRR计算
  • 新增数据行时，公式会自动向下延伸计算范围，无需手动调整选区大小

适配独立行计算场景的公式 #

如果每行的XIRR对应本行固定列数的现金流与日期（无累计逻辑），可在E2单元格输入以下简化版公式实现批量计算：

=MAP(B2:B, C2:C, LAMBDA(cash_row, date_row, IF(OR(cash_row="",date_row=""),, XIRR(cash_row, date_row))))

避坑提示 #

• 禁止直接书写=ARRAYFORMULA(XIRR(B2:B,C2:C))，该写法会把整列所有现金流作为单组数据计算，完全不符合逐行计算的需求
• 若公式返回#NUM!错误，需检查对应区间的现金流是否同时包含负值（初始投资）和正值（回款），XIRR对现金流方向的合规性有强制要求
• 该数组组合公式的运行效率高于逐行下拉XIRR的模式，在5000行规模下不会出现明显卡顿

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