EXCEL通过插值计算出新的曲线
1、 问题描述
坐标上有一系列数据点,组成的曲线,比如:
X 100 98 92 86 75 54 50 0
Y 615 590 560 525 489 420 410 320
我需要利用差值计算新的曲线,其X坐标分别为
X 100 95 87 78 69 57 52 0
当然,点的个数可以不一样。
当然,可以手动来计算,当你要重复进行同样的工作的时候,就可以创建一个EXCEL模板来代替你的计算器。
操作方法
- 01
介绍一下插值的原理:我们利用差值公式来计算,想利用差值计算,就需要知道差值的上限和下限。比如上面提到的问题,要计算95对应的Y值,其上限就是98对应的点,下限就92对应的点,95就需要利用这两个点来进行差值运算。
- 02
首先我们需要将这些数据复制两组,一组按降序排列,一组按升序排列(后面会用到)。
- 03
计算下限: 在计算下限中,我们利用公式LOOKUP,这个公式中三个变量分别为LOOKUP(目标值,你参考的数据库,输出选取位置的其他区域同一位置的值) 此公式的意思是,在数据库中寻找你的目标值,如果寻找到了,就输出该值所在位置的同一位置其他区域的值(即第三个参数,可以与第一个参数相同,也可以不同,看你所需),但是如果寻找不到目标值,则选取小于该值的最大值。注意:此处的库数据必须为 升序排列,否则会出错。
- 04
I11单元格输入公式=lookup(H11,$F$11:$F$18,$F11:$F$18) J11单元格输入公式=lookup(H11,$F$11:$F$18,$G$11:$G$18) 其中:I11单元格内的第三个参数,$F11:$F$18表示,输出X值所在的区域 J11单元格内的第三个参数,$G$11:$G$18表示,输出Y值所在的区域 注意:此处的数据库和输出选取区域都必须为绝对引用,因为目标值变的时候,数据库是不跟随改变的。 然后将单元格公式拖至I18、J18 此时,下限点的X、Y坐标已经计算出来。
- 05
计算上限: 因为下限使用了公式lookup,我就想,那有没有lookdown呢?事实是,没有! 这里我们就需要用到另一个公式:match(目标值,数据库,-1) 目标值,数据库同上,第三个参数的-1,表示的是,如果查询不到目标值,则选取大于该值的最小值。 但是与lookup不同的是,此公式输出的并不是这个单元格的数值,也不是与这个数值位置一样的其他单元格的数值。此公式输出的是选取的那个值在数据库中的行数。另外需注意的是此处需要用的数据库是降序排列,这就是为什么开始要弄一个升序的一个降序的原因了,因为这两个公式一个需要升序,一个需要降序,真蛋疼! 再说到match函数输出的为数据库中的行数,怎么将行数转变成具体的数值呢? 这里就需要用到另外一个公式:index(数据库,行数,列数) 这里注意,index公式选择数据库的时候,就要与match函数的数据库要相同,这样行数才能对应一起,这里的行数参数就选择match输出的值;另外,因为数据只有一列,所以列数中,就填写1。 上面计算出来的是上限的X值,还有上限Y值呢? 同样适用index函数,后两个参数不变,只是数据库变为了相同位置Y值的数据库了。 注意:此处注意,数据库的使用仍然要使用绝对引用。
- 06
L11单元格输入公式:=INDEX($D$11:$D$18,K11,1) M11单元格输入公式:=INDEX($E$11:$E$18,K11,1) 同样,拖下去,这样,上限和下限的数值已经选取好了 但是这里如果注意观察,会发现,如果目标值和库中的值有相等的。然后就可以使用差值计算来计算了,公式很简单,我没有找到直接套用的公式来计算,就自己编写了一个公式。
- 07
同样是N11单元格,输入公式:=J11+(H11-I11)*(M11-J11)/(L11-I11) 这样会发现如果上限和下限有相等的,就出提示出错。 没关系,我们使用if函数 在O11单元格输入公式:=if(L11=I11,J11,N11),至此,完全搞定。
- 08
因为对这几个公式也不是特别的熟悉,所以暂时还不能确定完全正确,如果在输入数据的时候,明显有错误的,那就返回去再修正。 虽然不能保证正确,但是这里也是给出了一种方法。由于因为我也刚刚开始接触excel,对于其中很多的公式不是很了解,所以可能会有更简单的方法,这样,就不要嘲笑我班门弄斧了,同时,也希望能留言告知,万分感谢!