我需要根据输入的“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
最佳答案
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
函数进行预测。
–
|
#REF!
是由于匹配最后一个单元(MATCH(E2, A2:A7, 1)) + 1
格时- 因此超出范围(对于)。当您收到错误时,您可以选择公式的某些部分,然后将鼠标悬停在上面以查看所选部分的部分结果。MATCH(E2, A2:A7, 1))
+1
INDEX($B$2:$B$7
–
|