我需要根据输入的“X”来计算结果“Y”。

只要“X”值不超过 200%，下面我使用的公式就有效。

我该如何编辑公式来纠正这个问题？

Excel 数据与计算

公式：

  • F2: =INDEX($B$2:$B$7,(MATCH(E2,A2:A7,1)))+(E2-INDEX($A$2:$A$7,(MATCH(E2,A2:A7,1) ))))*(索引($B$2:$B$7,(匹配(E2,A2:A7,1))+1)-索引($B$2:$B$7,(匹配(E2,A2:A7) ,1))))/(索引($A$2:$A$7,(匹配(E2,A2:A7,1))+1)-索引($A$2:$A$7,(匹配(E2,A2) :A7,1))))
  • F3: =INDEX($B$2:$B$7,(MATCH(E3,A2:A7,1)))+(E3-INDEX($A$2:$A$7,(MATCH(E3,A2:A7,1) ))))*(索引($B$2:$B$7,(匹配(E3,A2:A7,1))+1)-索引($B$2:$B$7,(匹配(E3,A2:A7) ,1))))/(索引($A$2:$A$7,(匹配(E3,A2:A7,1))+1)-索引($A$2:$A$7,(匹配(E3,A2) :A7,1))))
  • F4: =INDEX($B$2:$B$7,(MATCH(E4,A2:A7,1)))+(E4-INDEX($A$2:$A$7,(MATCH(E4,A2:A7,1) ))))*(索引($B$2:$B$7,(匹配(E4,A2:A7,1))+1)-索引($B$2:$B$7,(匹配(E4,A2:A7) ,1))))/(索引($A$2:$A$7,(匹配(E4,A2:A7,1))+1)-索引($A$2:$A$7,(匹配(E4,A2) :A7,1))))
  • F5: =INDEX($B$2:$B$7,(MATCH(E5,A2:A7,1)))+(E5-INDEX($A$2:$A$7,(MATCH(E5,A2:A7,1) ))))*(索引($B$2:$B$7,(匹配(E5,A2:A7,1))+1)-索引($B$2:$B$7,(匹配(E5,A2:A7) ,1))))/(索引($A$2:$A$7,(匹配(E5,A2:A7,1))+1)-索引($A$2:$A$7,(匹配(E5,A2) :A7,1))))

1

  • #REF!是由于匹配最后一个单元(MATCH(E2, A2:A7, 1)) + 1格时- 因此超出范围（对于）。当您收到错误时，您可以选择公式的某些部分，然后将鼠标悬停在上面以查看所选部分的部分结果。MATCH(E2, A2:A7, 1))+1INDEX($B$2:$B$7


    – 



最佳答案
2

你想要查看数据范围之外的内容，因此它不再是插值，而是外推。

在这种情况下，最简单的处理方法是将x数据范围从缩短A2:A7到A2:A6。对于F2，将出现以下公式：

=INDEX($B$2:$B$7,MATCH(E2,$A$2:$A$6,1))+
(E2-INDEX($A$2:$A$7,MATCH(E2,$A$2:$A$6,1)))*
(INDEX($B$2:$B$7,MATCH(E2,$A$2:$A$6,1)+1)-
INDEX($B$2:$B$7,MATCH(E2,$A$2:$A$6,1)))/
(INDEX($A$2:$A$7,MATCH(E2,$A$2:$A$6,1)+1)-
INDEX($A$2:$A$7,MATCH(E2,$A$2:$A$6,1)))

这种表示法可以通过多种方式缩短和简化，但我不知道选择这种表示法是否不是因为使用了旧版本的 Excel。

在 Excel 2021 或更高版本中，您可以使用如下公式：

    =LET(xw,E2,data,OFFSET(XLOOKUP(xw,$A$2:$A$6,$A$2:$B$6,,-1,2),,,2),
FORECAST.LINEAR(xw,INDEX(data,,2),INDEX(data,,1)))

从统计学的角度来看，插值/外推是通过最佳拟合曲线完成的。我认为 excel 只有几个公式可以直接做到这一点（linest和）。但是在向散点图logest添加趋势线时单击几个选项可以为您提供公式，然后您可以将其插入单元格，因此根据屏幕截图中突出显示的公式，可以将公式插入F2 并向下拖动到 F5。
=-(E2^2*2.4997)+E2*9.8422-6.2991

对于我所展示的内容，有一些保留意见：1）只有 6 个数据点（但由于 R2 很高，所以可能是可以接受的）；2）外推经验规则是避免扩展到超过高/低值的 10%；3）插值意味着平滑假设。

2

  • =INDEX(LINEST($B$2:$B$7, $A$2:$A$7^{1,2}), 1)*E2^2+INDEX(LINEST($B$2:$B$7, $A$2:$A$7^{1,2}), 1, 2)*E2+INDEX(LINEST($B$2:$B$7, $A$2:$A$7^{1,2}), 1, 3)


    – 

  • 如果您想使用线性回归，最好这样：=TREND($B$2:$B$7,$A$2:$A$7^{1,2},E2^{1,2})您可以使用该TREND函数进行预测。


    –