我试图求解图 (A) 中 x 和 y 分量达到的最小值。我知道您可以使用 IgorPro 执行此操作,但我想知道如何使用 Excel 求解。
该图是使用以下数据点构建的:
110 0.177
115 0.093
120 0.033
125 0.006
130 0.007
135 0.04
140 0.101
145 0.186
150 0.272
如您所见,存在最小 x 和 y 数据点。但是,我想求解多项式拟合达到的最小点。
答案1
给出二次趋势线方程的最小值坐标,其y(x)=a*x^2+b*x+c
计算公式为:
x=-b/2/a
,y=c-b^2/4/a
。
提供的数据点的图如下所示
图上的趋势函数方程Excel
为:
y=0.0005*x^2-0.1383*x+8.8197
,因此看来a=0.0005
,b=-0.1383
和c=8.8197
。那么我们来找出最小值。计算结果为:
x=138.3, y = -0.743745000.
等等,什么?但趋势线显然看起来更高,而且全是正值!发生了什么事?!让我们画出这个函数的图表:
它向下移动了,完全丢失了数据点!最小值的坐标是正确的,但对于这个移位函数来说,它不是我们需要的。
问题的根源就隐藏在细节中。图表中显示的公式的系数已四舍五入。四舍五入到小数点后四位。
"Four decimal places would be enough for everyone (c)", right?
显然不是,尤其是当,x>1
不谈论x>100
小数点后 9 位的系数为:
a=0.000542468, b=-0.138278225, c=8.819723377,
情节看起来不错:
并且计算出的最小坐标看起来也是合理的:
x = 127.4528866, y = .007743909.
好多了!
但是我们如何才能得到这个更准确的最小值呢?
这是一种简单的方法,基于矩阵函数MINVERSE()
和MMULT()
。
假设X
,Y
在中B7:B15
,让我们3x4
在中填充一个矩阵E7:H9
,
从三个单元格开始
E7
:=COUNT(B7:B15)
,F7
:=SUM(B7:B15)
,H7
:=SUM(C7:C15)
。
接下来五个单元格中的每个公式 ,,,,,,G7
都G8
需要输入为 G9
H8
H9
数组公式(输入++Ctrl而 不是仅仅):ShiftEnterEnter
G7
:
=SUM(B7:B15^2)
G8
:
=SUM(B7:B15^3)
G9
:
=SUM(B7:B15^4)
H8
:
=SUM(B7:B15*C7:C15)
H9
:
=SUM(B7:B15^2*C7:C15)
Block完成了中的E8:F9
对称矩阵:3x3
E7:G9
E8
:
=F7
,抄送E8
填写E8:F9
。
选择F11:F13
并输入以下内容数组公式
=MMULT(MINVERSE(E7:G9),H7:H9)
计算出所需的更精确的值
c= 8.81972337662,
b=-0.13827822511,
a= 0.00054246753.
附注:最好先将原始m<=X<=M
范围转换为单位间隔0<=x<=1
,找到最小值的坐标,然后再转换x
回X
。