这篇文章的中心有两个：

• 当公式参数动态变化时，怎样将功能单一的公式变成可以横向/纵向随意填充的公式。
• 如何实现批量线性计算。

除此之外，你将会对绝对引用和相对引用有更深的认识。

如图，左侧是原始数据表，当你修改绿色部分的x、y值时，公式计算的结果会自动更新，你还可以横向纵向随意拖拉公式。

 

当然，如果你对线性内插值不感兴趣的话，也可以忽略上面的文章，阅读以下内容时，只看重如何让公式变得更强大即可。

一维线性内插值

步骤① 分析单一的函数和需求。

再回忆一下一维线性内插值函数TREND的用法：“=TREND(已知相邻y值,已知相邻x值,新x值)”。

所以，当要计算的x值变化时，“相邻y值”和“相邻x值”也会跟着变化。

所以，当你遇到像这样公式参数会动态变化时，就需要改造一下公式，使得公式更加智能。

步骤② 逐一分析函数参数，修改参数。

TREND函数3个参数，最后一个参数固定在D4单元格，所以不需要修改它。

前两个参数和最后一个参数相关，它们取得是最后一个参数相邻处的x、y值，所以可以使用OFFSET函数获得它们。

已知相邻x值：
=OFFSET(B$1,MATCH(D4,A$2:A$188),,2)
已知相邻y值：
=OFFSET(A$1,MATCH(D4,A$2:A$188),,2)

这样，我们就用两个OFFSET函数替换掉TREND函数的前两个参数。公式变长了，但也更智能了。

③ 你可以随意修改x值，它会自动找出相邻的x、y值，并且计算出结果。

④ 你也可以向下批量填充公式。

这就实现了批量一维线性内插值计算。

二维线性内插值

二维线性内插值可以拆分成横向一维内插值和纵向一维内插值。

前面我们已经写出了一维线性内插值公式，它可以让你批量向下填充。怎样让它可以向右填充呢？

步骤① 找出公式中有几个单元格引用，逐一修改它们的引用类型。

如上图所示，TREND公式中有G4、B$1、A$1、A$2:A$188。

• G4单元格表示要计算的x值，公式向下填充时，x值逐行发生变化，而向右填充时，则维持不变，所以，需将公式中的G4必须变成$G4。

• B$1在公式“OFFSET(B$1,MATCH(D4,A$2:A$188),,2)”中，它是我们查找第几列的基准点。公式向下填充时，要查找的y值始终都在同一列，而向右填充时，y值的列号就要发生变化。所以 B$1的引用类型不必修改。
• A$1在公式“OFFSET(A$1,MATCH(D4,A$2:A$188),,2)”中，它是我们查找第几行数据的基准点，不管向下还是向右填充，都不变。所以，需将它修改成$A$1。

• 同理，A$2:A$188需修改成$A$2:$A$188。

这样的公式，既可以向下填充，也可以向右填充。

步骤②：按照前面的方法，写出纵向线性插值计算公式。

注意，我们在写纵向插值公式时，以横向插值公式的结果作为基础数据表。这样纵向插值计算结果就是我们想要的二维线性插值结果啦。

步骤③ ：拖动填充公式。

填写好x、y值后，拖动公式即可获得二维线性插值结果。

建议

① 如果你的原始数据比较多，建议将横向和纵向插值表剪切到单独的工作表中。如果你常常需要线性内插值，我建议你花点时间做一个这样的表格，这以后，每次就只要修改原始数据表，以及要计算的x、y值即可轻松获得结果。

② 不建议你将横向和纵向公式整合成一个公式，虽然网上有人这样做。但是这样公式就会变得超长，而且难以理解，不利于长期维护。