多语言展示
当前在线:1275今日阅读:6今日分享:31

EXCEL散点图曲线交叉点坐标的计算方法

EXCEL的散点图上的平滑曲线,在EXCEL系统中是使用三次贝塞尔曲线来绘制的,可简单理解为:excel的散点图本来就是曲线拟合绘制的(使用的曲线是三次贝塞尔曲线),交点的位置并不是很准确的,并不是实际中的交点。在这个前提下,本人原创一个求解的方法,供有兴趣的朋友参考;以下举例说明:
工具/原料

EXCEL

方法/步骤
1

【例题】电梯运行时有不同载重的电流曲线,电流值为Y轴,载重比为X轴,电梯上行时30%、40%、45%、50%、60%载重的电流生成一条曲线,电梯下行时30%、40%、45%、50%、60%载重的电流生成一条曲线,然后两条曲线会有一个交点,需在Excel单元格内计算该成交点的X轴值。如图:计算交点的x轴的值。

2

从图上直观的可以看出交叉点在载荷(40-50)之间——上图的黄色区域,保留红色方框内黄色区域的数据,其他删除,这一步是锁定一个比较小的范围,降低求解难度;删除后的图形如下:

3

添加拟合曲线(实际也是用高次方程根据点子拟合的原理来求解),添加方法如图:

4

添加趋势线,选择多项式,显示公式,目的是用多项式来拟合原有的曲线

5

同理,给另外一条线也添加多项式的趋势性,并显示公式

6

此时,能得到一个方程组:y = 0.2x - 6……(1)y = 0.02x^2 - 2x + 52……(2)用excel对方程组(1)(2)求解,即可计算出拟合曲线的交点位置,也就是“平衡系数”。

【Excel求解方程组】
1

1)方程组(1)(2),整理为方程式:0.02x2 - 2.2x + 58 = 02)选中B4单元格,在名称框(注意是左上角的“名称框”),输入X,如图:

2

3)在B5单元格输入公式=0.02*X^2-2.2*X +58

3

4)求解目标单元格:B5目标值:0可变单元格:B4点击“确定”,求得平衡系数为43.82

注意事项
1

excel散点图曲线本身是用函数计算拟合出来的,因此交点的位置也并不是很准确的,并不是实际中的交点。

2

这个求解实际计算的是拟合曲线的交点,可以看成是在一定程度上,能逼近实际交点的。

3

计算的精确性,取决于使用的拟合曲线的拟合程度,拟合程度高,那么精确性也高。

4

以上方法本人原创,未做更多的验证计算。仅作参考。

推荐信息