Excel插值计算净值公式修正及多场景适配求助

修正后的公式 #

适用于Excel 365/2021（动态数组，无需手动数组输入） #

从H2单元格开始输入以下公式，下拉填充即可：

=LET(
    currG, G2,
    prevG, XLOOKUP(TRUE, G$1:G1<>"", G$1:G1, , , -1),
    nextG, XLOOKUP(TRUE, G3:G$100<>"", G3:G$100, , , 1),
    prevRow, XLOOKUP(TRUE, G$1:G1<>"", ROW(G$1:G1), , , -1),
    nextRow, XLOOKUP(TRUE, G3:G$100<>"", ROW(G3:G$100), , , 1),
    totalGap, nextG - prevG,
    totalRows, nextRow - prevRow,
    perRowValue, totalGap / totalRows,
    IF(NOT(ISNUMBER(H1)), currG,
        IF(NOT(ISBLANK(currG)), currG - prevG,
            perRowValue
        )
    )
)

兼容旧版本Excel（需按Ctrl+Shift+Enter数组输入） #

同样从H2开始输入，输入完成后按Ctrl+Shift+Enter确认，再下拉填充：

=IF(NOT(ISNUMBER(H1)), G2,
    IF(NOT(ISBLANK(G2)), G2 - IFERROR(INDEX(G$1:G1, MAX(IF(G$1:G1<>"", ROW(G$1:G1)))), 0),
        IFERROR(
            (INDEX(G:G, MATCH(TRUE, G3:G$100<>"", 0)+ROW()) - INDEX(G:G, MAX(IF(G$1:G1<>"", ROW(G$1:G1)))))
            / (MATCH(TRUE, G3:G$100<>"", 0)+ROW() - MAX(IF(G$1:G1<>"", ROW(G$1:G1)))),
            0
        )
    )
)

场景适配说明 #

• G列单元格有值：自动定位上方最近的非空G值，计算当前G值与该值的差值。比如示例中G7=400，上方最近非空G为G2=90，计算得400-90=310，与示例H7一致。
• G列单元格空白：同时查找上下最近的非空G值，计算总差值后，均匀分配到中间所有空白行。比如示例中G3-G6为空白，上方G2=90、下方G7=400，总差值310，中间含上下非空行共5行，310/5=62，匹配示例H3-H6的结果；G8-G9空白时，(900-400)/(10-7)=500/3≈166.67，与示例H8-H9一致。
• H列上方单元格非数值：当H列上一行是文本（如表头），直接取当前G列的值，比如示例中H2直接取G2=80，符合要求。

示例数据验证 #

应用公式后，结果完全匹配预期示例：

内容的提问来源于stack exchange，提问作者Bar Cohen