当Excel公式参数动态变化时，怎样让公式可以随意填充

这篇文章的中心有两个：

1. 当公式参数动态变化时，怎样将功能单一的公式变成可以横向/纵向随意填充的公式。

2. 如何实现批量线性计算。

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

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

GIF

不了解线性内插值的朋友，可以先阅读以下文章，这样对阅读本文会有所帮助。

《促销物品价格怎么定？我用Excel一维线性插值和二维线性插值》。

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

一维线性内插值

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

再回忆一下一维线性内插值函数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值，并且计算出结果。

GIF

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

GIF

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

二维线性内插值

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

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

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

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

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

GIF

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

3. A$1在公式“OFFSET(A$1,MATCH(D4,A$2:A$188),,2)”中，它是我们查找第几行数据的基准点，不管向下还是向右填充，都不变。所以，需将它修改成$A$1。

GIF

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

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

GIF

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

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

步骤 ：拖动填充公式。

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

GIF

建议

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

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

相关阅读：《WPS Excel 获取动态数据函数offset的基本用法》。