Excel插值公式#REF!错误修正：解决X值超过200%时的计算问题

嘿，我来帮你搞定这个麻烦的#REF!错误！你现在遇到的问题根源很明确：当输入的X超过A列里的最大值（也就是200%）时，公式里的MATCH(E2,A2:A7,1)会返回最后一个数据行的位置，之后你再加1去调用INDEX，就超出了数据范围，自然就报错了。

问题拆解 #

你的原始公式本质是线性插值：Y = Y₁ + (X - X₁) × (Y₂-Y₁)/(X₂-X₁)，其中X₁是小于等于X的最大基准值，X₂是下一个基准值。但当X＞200%时，X₂不存在，这就导致了索引越界。

解决方案 #

根据你的需求，我给你两种常用的处理方式：

方案1：超过200%时沿用最后一段斜率外推

如果希望X超过200%后，按照最后两个基准点的斜率继续计算Y值，可以用这个兼容所有Excel版本的公式：

=IF(E2>MAX($A$2:$A$7),
    INDEX($B$2:$B$7,ROWS($B$2:$B$7)) + (E2-INDEX($A$2:$A$7,ROWS($A$2:$A$7)))*(INDEX($B$2:$B$7,ROWS($B$2:$B$7))-INDEX($B$2:$B$7,ROWS($B$2:$B$7)-1))/(INDEX($A$2:$A$7,ROWS($A$2:$A$7))-INDEX($A$2:$A$7,ROWS($A$2:$A$7)-1)),
    INDEX($B$2:$B$7,MATCH(E2,$A$2:$A$7,1)) + (E2-INDEX($A$2:$A$7,MATCH(E2,$A$2:$A$7,1)))*(INDEX($B$2:$B$7,MATCH(E2,$A$2:$A$7,1)+1)-INDEX($B$2:$B$7,MATCH(E2,$A$2:$A$7,1)))/(INDEX($A$2:$A$7,MATCH(E2,$A$2:$A$7,1)+1)-INDEX($A$2:$A$7,MATCH(E2,$A$2:$A$7,1)))
)

如果你的Excel版本支持LET函数（365/2021及以上），可以用更简洁高效的版本，减少重复计算：

=LET(
    maxX, MAX($A$2:$A$7),
    x, E2,
    matchRow, MATCH(x, $A$2:$A$7, 1),
    IF(x>maxX,
        INDEX($B$2:$B$7,ROWS($B$2:$B$7)) + (x-INDEX($A$2:$A$7,ROWS($A$2:$A$7)))*(INDEX($B$2:$B$7,ROWS($B$2:$B$7))-INDEX($B$2:$B$7,ROWS($B$2:$B$7)-1))/(INDEX($A$2:$A$7,ROWS($A$2:$A$7))-INDEX($A$2:$A$7,ROWS($A$2:$A$7)-1)),
        INDEX($B$2:$B$7,matchRow) + (x-INDEX($A$2:$A$7,matchRow))*(INDEX($B$2:$B$7,matchRow+1)-INDEX($B$2:$B$7,matchRow))/(INDEX($A$2:$A$7,matchRow+1)-INDEX($A$2:$A$7,matchRow))
    )
)

方案2：超过200%时固定返回最后一个Y值

如果你的需求是X超过200%后不再外推，直接用最后一个基准Y值，公式会更简单：

=IF(E2>MAX($A$2:$A$7),INDEX($B$2:$B$7,ROWS($B$2:$B$7)),INDEX($B$2:$B$7,MATCH(E2,$A$2:$A$7,1)) + (E2-INDEX($A$2:$A$7,MATCH(E2,$A$2:$A$7,1)))*(INDEX($B$2:$B$7,MATCH(E2,$A$2:$A$7,1)+1)-INDEX($B$2:$B$7,MATCH(E2,$A$2:$A$7,1)))/(INDEX($A$2:$A$7,MATCH(E2,$A$2:$A$7,1)+1)-INDEX($A$2:$A$7,MATCH(E2,$A$2:$A$7,1))))

重要提醒 #

一定要确保A列的基准X值是升序排列的！因为MATCH函数第三个参数为1时，要求查找区域必须是升序，否则会返回错误的匹配结果。

备注：内容来源于stack exchange，提问作者Andrés