excel查找数值范围并内插计算 A=1.4，B=10.25，返回内插值，公式应该怎么写呀？

Question

你好！请问可以帮忙看看在A12中求横行为1.4和纵列为10.26，表中对应的数值的公式怎么写？

Answer 1

本问题有些难度，且内插有两种不同的算法，结果会有些差别。

第一种算法，根据行和列两个方向分别在行和列找到在哪两个数据中间，取4个数据进行内插，一般的手工计算法就是这样的，但按这种算法，在Excel中却比下面第二种算法要复杂得多。

公式：

B13=TREND(CHOOSE({1;2},TREND(CHOOSE({1;2},INDEX(B2:C10,MATCH(A13,A2:A10),MATCH(B12,B1:C1)),INDEX(B2:C10,MATCH(A13,A2:A10)+1,MATCH(B12,B1:C1))),SUBTOTAL(9,OFFSET(A1,ROW(INDIRECT(MATCH(A13,A2:A10)&":"&MATCH(A13,A2:A10)+1)),)),A13),TREND(CHOOSE({1;2},INDEX(B2:C10,MATCH(A13,A2:A10),MATCH(B12,B1:C1)+1),INDEX(B2:C10,MATCH(A13,A2:A10)+1,MATCH(B12,B1:C1)+1)),SUBTOTAL(9,OFFSET(A1,ROW(INDIRECT(MATCH(A13,A2:A10)&":"&MATCH(A13,A2:A10)+1)),)),A13)),SUBTOTAL(9,OFFSET(A1,,ROW(INDIRECT(MATCH(B12,B1:C1)&":"&MATCH(B12,B1:C1)+1)))),B12)

同时按Ctrl+Shift+Enter三键输入数组公式

第二种算法，把二维数据视为两个方向都是线性的，直接用Excel的回归函数来计算，相对于第一种算法要简单很多，当然也是有难度的。本算法更为合理，因为它兼顾全部数据影响。

公式：

B14=TREND(CHOOSE({1,2},TREND(OFFSET(A2:A10,,MATCH(B12,B1:C1)),A2:A10,A13),TREND(OFFSET(A2:A10,,MATCH(B12,B1:C1))+1,A2:A10,A13)),B1:C1,B12)

同样数组公式。

第二种方法的值大很多，是因为数据源A列值越大，对应的数据增加越快，所以数据参与插值回归计算时，加大了下面数值较大的权重。

PS：上面两种计算方法的公式都可根据A列或第行数据的多少来修改公式，如第1行还有多列时，只要修改公式中的C列到最后的列标号，A列数据也一样，只要修改公式中的行号。

以如此难度的公式，加上对内插计算的讲解，也该加些悬赏吧。如果当面传授，周末这顿晚饭也应该没问题吧，哈哈。

追问

你好！谢谢，我就想要第一种算法请问可以把第一种算法公式发下么？你之前发那个我输进去出了问题。求1.6和10.55对应的数值。我现在在北海，如果你也在北海出来吃饭绝对没问题！没什么赏金所以赏金比较少。

追答

要用来做插值的数据最好按数据源相同的维度方向来输入，这样不仅看起来舒服，也便于编写公式，不容易出错，如10.55，应该对应A列的行方向，1.6对应第1行的列方向，且按数据源方式，错一行输入。即10.55输入在A16，1.6输入在B15，下面的公式就按这个约定。
B16B13=TREND(CHOOSE({1;2},TREND(CHOOSE({1;2},INDEX(B2:E10,MATCH(A16,A2:A10),MATCH(B15,B1:E1)),INDEX(B2:E10,MATCH(A16,A2:A10)+1,MATCH(B15,B1:E1))),SUBTOTAL(9,OFFSET(A1,ROW(INDIRECT(MATCH(A16,A2:A10)&":"&MATCH(A16,A2:A10)+1)),)),A16),TREND(CHOOSE({1;2},INDEX(B2:E10,MATCH(A16,A2:A10),MATCH(B15,B1:E1)+1),INDEX(B2:E10,MATCH(A16,A2:A10)+1,MATCH(B15,B1:E1)+1)),SUBTOTAL(9,OFFSET(A1,ROW(INDIRECT(MATCH(A16,A2:A10)&":"&MATCH(A16,A2:A10)+1)),)),A16)),SUBTOTAL(9,OFFSET(A1,,ROW(INDIRECT(MATCH(B15,B1:E1)&":"&MATCH(B15,B1:E1)+1)))),B15)
你输入公式出错，极有可能就是没注意行与列的对应关系。

Answer 2

由于不知道你的1.4、10.26输入在哪个单元格，所以，只好直接用数据了，公式为：
=INDEX($B$2:$E$7,MATCH(10.25,$A$2:$A$7),MATCH(1.4,$B$1:$E$1))
若是A15输入1.4、B15输入10.25，则可在C15输入公式：
=INDEX($B$2:$E$7,MATCH(B15,$A$2:$A$7),MATCH(A15,$B$1:$E$1))
即可自动核算A15、B15对应数据的取值了追问

你好，不仅是查找数值还要内插求值。大概意思是第一步查找单元格，1.6在1.5和1.7之间，10.55在10.5和10.6之间，所以查找到图一所示四个单元格；第二步内插法得出图二所示1.5对应数2个值；第三步再用10.55内插得出相应数值，即得到了横行为1.5纵列为10.55的数值。请问公式怎么写？非常感谢！！！！！！



追答

你这样的数据结构重新调整的思路根本就没有办法用公式操作，实现不了的！ 另寻出路吧……

追问

你好，还是谢谢你了！

来自：求助得到的回答

Answer 3

Excel怎样查找表格纵横向两值A、B值相应值

Answer 4

=SUMPRODUCT(IF({1;0},(A14-$B$1)/($C$1-$B$1),1),MMULT(((B14-LOOKUP(B14,$A$2:$A$10))/SUM(LOOKUP(B14+{0;0.1},$A$2:$A$10)*{-1;1})*MMULT({-1,1},OFFSET($B$1,MATCH(B14,$A$2:$A$10),,2,2))+VLOOKUP(B14,$A$2:$C$10,{2,3}))*{1,1;-1,0},{-1;1}))

追问

你好非常感谢！想请问！如果多了些数据后公式怎么改写？第一步查找单元格，1.6在1.5和1.7之间，10.55在10.5和10.6之间，所以查找到图一所示四个单元格；第二步内插法得出图二所示1.5对应数2个值；第三步再用10.55内插得出相应数值，即得到了横行为1.5纵列为10.55的数值。



追答

=SUMPRODUCT(IF({1;0},(A14-LOOKUP(A14,$B$1:$E$1))/SUMPRODUCT(OFFSET($A$1,,MATCH(A14,$B$1:$E$1),,2)*{-1,1}),1),MMULT(((B14-LOOKUP(B14,$A$2:$A$10))/SUM(LOOKUP(B14+{0;0.1},$A$2:$A$10)*{-1;1})*MMULT({-1,1},OFFSET($A$1,MATCH(B14,$A$2:$A$10),MATCH(A14,$B$1:$E$1),2,2))+OFFSET($A$1,MATCH(B14,$A$2:$A$10),MATCH(A14,$B$1:$E$1),,2))*{1,1;-1,0},{-1;1}))