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

2021年10月28日09:29:58当Excel公式参数动态变化时,怎样改造公式,让公式可以随意填充已关闭评论 53 views
微信公众号 【EXCEL880】 QQ群【165159540】 Excel课程咨询 请加郑广学老师微信EXCEL880B

这篇文章的中心有两个:

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

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

如图,左侧是原始数据表,当你修改绿色部分的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)
当Excel公式参数动态变化时,怎样改造公式,让公式可以随意填充

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

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

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

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

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

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

二维线性内插值

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

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

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

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

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

  • G4单元格表示要计算的x值,公式向下填充时,x值逐行发生变化,而向右填充时,则维持不变,所以,需将公式中的G4必须变成$G4。
当Excel公式参数动态变化时,怎样改造公式,让公式可以随意填充
  • 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。
当Excel公式参数动态变化时,怎样改造公式,让公式可以随意填充
  • 同理,A$2:A$188需修改成$A$2:$A$188。

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

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

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

步骤③ :拖动填充公式。

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

建议

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

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

 

表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页