如何用LET/MakeArray公式按行偏移量右移发票月度收款数据

正确公式 #

=LET(
    rawData, $C$5:$N$9,
    mFactors, $A$5:$A$9,
    totalRows, ROWS(rawData),
    totalCols, COLUMNS($C$19#),
    MAKEARRAY(
        totalRows,
        totalCols,
        LAMBDA(r, c,
            LET(
                delay, INDEX(mFactors, r),
                originalCol, c - delay,
                IF(originalCol < 1 || originalCol > COLUMNS(rawData), 0, INDEX(rawData, r, originalCol))
            )
        )
    )
)

参数说明 #

• rawData：直接引用原始月度金额数据区域（示例中为$C$5:$N$9）
• mFactors：引用每行对应的收款延迟月数列（示例中为$A$5:$A$9）
• totalRows：自动获取原始数据的行数，无需依赖额外溢出区域
• totalCols：从你已计算好的扩展月份溢出表头（$C$19#）中获取总列数
• 核心逻辑：在MAKEARRAY的单元格遍历中，通过c - delay计算原始数据对应的列位置，若该位置超出原始数据范围则返回0，否则取对应金额

原公式问题分析 #

1. 第一个公式：

   • 提前用INDEX(data,height,width)构建datarange的方式冗余且易出错
   • DROP(datarange,,INDEX(mfactor,r))仅删除前delay列，但未处理超出剩余列数时的补0逻辑，导致错误偏移或空值

2. 第二个公式：

   • 在MAKEARRAY的单个单元格逻辑中使用HSTACK，违反了MAKEARRAY每个单元格仅返回单个值的要求，直接导致公式失效返回0

效果验证（示例数据） #

以发票3（Mfactor=2）为例：

• 原始2025/1/1的150会右移至扩展表格的第3列（2025/3/1）
• 扩展表格的第1、2列（2025/1/1、2025/2/1）补0
• 原始后续月份金额依次右移，超出原始数据列数的位置自动补0

内容的提问来源于stack exchange，提问作者Mark S.