Google Sheets中用ArrayFormula与Lambda优化员工成本分摊数据转换

问题原因分析 #

你之前用ArrayFormula、WRAPROWS、VSTACK自动迭代失败，核心原因有三点：

1. 自定义函数的单场景限制：TUPLEGENERATION、MULTITUPLEGEN、MONTHGEN是针对单个月份设计的，返回单月份的多行结果。用ArrayFormula批量调用时，函数会将每个月份的结果按列方向展开，而非行方向拼接，直接导致数据错位。
2. 数组维度计算错误：使用WRAPROWS时，若未精准计算总输出行数（总员工数×12），会因行数参数偏差导致拆分后的行列映射混乱；VSTACK嵌套ArrayFormula时，ArrayFormula返回的多维数组结构不符合VSTACK的参数要求（VSTACK需要多个独立数组按行堆叠，而非一个内部结构错位的大数组）。
3. 自定义函数无数组兼容：命名函数内部可能使用了单个单元格引用而非数组引用，批量调用时无法正确遍历所有员工行，导致输出结果缺失或错位。

优化方案 #

方案1：兼容原自定义函数，自动迭代拼接 #

用REDUCE遍历12个月份，自动调用MONTHGEN并堆叠结果，替代手动12次调用：

=REDUCE("",SEQUENCE(12),LAMBDA(acc,month,VSTACK(acc,MONTHGEN(month))))

如果MONTHGEN需要接收列索引（比如C列对应第1月，列号为3），调整为：

=REDUCE("",SEQUENCE(12,1,3),LAMBDA(acc,col,VSTACK(acc,MONTHGEN(col))))

原理：REDUCE从空值开始，遍历12个月份（或对应列号），每次将当前MONTHGEN的结果用VSTACK堆叠到累计结果中，自动完成全量拼接。

方案2：抛弃自定义函数，原生函数一步实现 #

直接用原生函数组合完成宽表转长表，无需依赖自定义函数，更稳定：

=LET(
    员工范围,A2:A,
    成本中心范围,B2:B,
    月度占比范围,C2:N,
    员工数,COUNTA(员工范围),
    月份数,COLUMNS(月度占比范围),
    月份序列,FLATTEN(TRANSPOSE(SEQUENCE(月份数))),
    员工序列,FLATTEN(员工范围),
    成本中心序列,FLATTEN(成本中心范围),
    占比序列,FLATTEN(月度占比范围),
    FILTER(
        HSTACK(月份序列,员工序列,成本中心序列,占比序列),
        员工序列<>""
    )
)

原理：

• 用LET定义变量简化公式，避免重复引用；
• FLATTEN(TRANSPOSE(SEQUENCE(月份数)))生成每个月份重复员工数次的序列，确保和员工行一一对应；
• FLATTEN分别将员工、成本中心、月度占比列转换为长序列；
• HSTACK合并所有列，FILTER过滤空行。

内容的提问来源于stack exchange，提问作者Francesco De Santis