第三章 实验曲线的数据拟合

4阶多项式拟合
y = -1E-06x 4 + 0.0003x 3 - 0.0322x 2 + 1.3697x - 2.5556 R 2 = 0.9937
溅射率与能量关系 25 20
溅射率（%）
溅射率与能量关系 25 20
溅射率（%）
15 10 5 0 0 20 40 60 能量（KeV） 80 100
2、设置 “回归”对话 框中的参数；
按住鼠标左键不放，拖拽鼠标选中相应的数据列，将数据 表中的“x=”列设为Y值；将“p=”和“p1=”列设为X值。
参数：a=0.01618022 b=0.04422551
（2）非线性参数估计示例：
往DBS水溶液中投入活性炭，在等温下放置达到吸附 平衡，DBS的平衡浓度c与投入活性炭吸附量q之间的关系 如下表所示：
关于“规划求解”的一些提示
二、在本例中，假定必须满足对每种产品的需求，那么就必须将 需求约束从 D3:I3 <= D9:I9 更改为 D3:I3 >= D9:I9。当单击 “求解”时，您将会看到“规划求解找不到可行的解决方案”消 息。该消息意味着使用我们有限的资源，无法满足对所有产品的 需求。但实际上我们的模型并没有错！规划求解只是要告诉我们， 如果我们想要满足对每种产品的需求，我们就需要增加更多的人 工、更多的原材料或两者都要增加。 三、本例中如果我们允许对每种药品无限制的需求，并且允许每 种药品的产量为负（清除“假定非负”复选框 ），那么我们将看 到 “‘设置目标单元格’的值未收敛”，这表示所用的模型有错 误。 四、在计算利润时用到了SUMPRODUCT函数，公式 SUMPRODUCT(D10:I10,$D$3:$I$3)相当于计算： D10*D3+E10*E3+F10*F3+G10*G3+H10*H3+I10*I3，这要比手动输入 简单得多！ 五、“$D$3”中的两个“$”共同表示对D3单元格的绝对引用。而
15 10 5 0 0 20 40 60 能量（KeV） 80 100
源文件：利润最大化规划求解示例.xls
“规划求解”结果
通过生产 1800件的产品5而不生产任何其他产品，我们的金属制 品公司每月可获得最高利润6,840.00 美元！我们无法确定通过其 他方法是否可以获得6,840.00美元的最高利润。但我们可以确定， 在我们有限的资源和需求条件下，这个月的利润根本不可能超出 6,840.00美元。
所添加的三阶多 项式趋势线
ቤተ መጻሕፍቲ ባይዱ1、选中数据列
2、绘制散点图或曲线
3、对准数据点（或曲线）点击右键， 在弹出式菜单中选中“添加趋势线”
1
2
对准趋势线，右击鼠标键，在弹出 式菜单中选中“趋势线格式（0）”， 在弹出的“趋势线格式”对话框中 可以对趋势线类型和格式进行修改。
由3阶改 为 4阶
溅射率与能量关系 25 20
平衡浓度c 1.6 4.52 6.8 8.16 11.5 12.7 18.2 29 38.9 57.3 吸附量q 170.7 228.1 258 283.7 321.3 335.4 378.6 434.6 401.3 429
请应用非线性最小二乘法估计下式中的参数a、b和β。
q bc / (1 ac )
假定我们在一家金属制品公司工作，这家公司可以在他们的工厂生 产六种产品。生产每种产品都需要人工和原材料。
下一页所示图片中数据表的第 4 行（行四）显示了生产一件的每种 产品所需的人工小时数，第 5 行显示了生产一件的每种产品所需的 原材料的公斤数。例如，生产一件的产品 1 需要 6 小时人工和 3.2 公斤原材料。
利用公式计算功能计算出 第一个单元格对应的P的 平方根 通过复制公式计算出其他单 元格对应的P的平方根
在“工具”菜单中选中”加载宏（I）”，然后 在弹出的加载宏对话框中选中“分析工具库”， 以便将相关分析工具添加到“工具”菜单栏。
在“工具”菜单中出 现了新添加的菜单项。
1、点击“工具”菜单栏中 的“数据分析（D）”菜单 项，然后在弹出的“数据 分析”对话框中选中“回 归”；
0 0 20 40 60 能量（KeV） 80 100
（二）用Excel进行参数估计
线性参数估计可以直接利用Excel的分析工具库中的回归计 算工具实现。对于非线性参数估计则可以通过适当的构造
，将问题转化成一个使误差最小的规划问题进行解决。 （1）线性参数估计示例： SO2对水的溶解度实验式可以表示为：
源文件：利润最大化规划求解示例.xls
一个不可行的方案
本页中显示的方案是不可行的，因为要实现目标利润 （7723.00），需要的人工（6012.5）和原材料（2019.5） 均超出了可用的值（可用人工4500、可用原材料1600）。
源文件：利润最大化规划求解示例.xls
本页中显示的方案是可行的（但可能不是最优的），因为 要实现目标利润（ 7723.00），需要的人工（ 6012.5）和 原材料（2019.5）均未超出了可用的值（可用人工4500、 可用原材料1600）。
第 6 行显示了每种产品单件的价格，第 7 行显示了单件产品的成本， 第 9 行显示单件产品可带来的利润。例如，产品 2 的价格是每件 11.00 美元，每件的单位成本是 5.70 美元，每件的利润就是 5.30 美元。 第 8 行显示了该月对每种产品的需求。例如，对产品 3 的需求为 1041件。 该月可提供 4500 人工工时和 1600 公斤的原材料。该公司如何最 大化它每月的利润？
15 10 5 0 0 20 40 60 能量（KeV） 80 100
15 10 5 0 0 20
线性拟合
y = 0.116x + 11.833 R 2 = 0.6401
对数拟合
y = 5.1909Ln(x) - 1.7029 R 2 = 0.8841
对拟合效果好坏的评判：肉眼观察 & 拟合R 值综 合判断。一般情况下， R2值越大（即越接近于1）， 溅射率与能量关系 溅射率与能量关系 拟合越精确。
源文件：利润最大化规划求解示例.xls
关于“规划求解”的一些提示
一、本例中选择“采用线性模型”的原因是该问题是一种称为线 性模型的特殊规划求解问题。基本上，在以下情况下，规划求解 模型都是线性模型： （1）目标单元格是通过将表单的条件（可变单元格）*（约束） 相加进行计算的。 （2）每种约束都满足线性模型要求。这意味着每种约束都是通过 将表单的条件（可变单元格）*（约束）相加，然后将这些总和与 某个常量进行比较来评估的。 如果规划求解模型是线性的，并且我们选择“采用线性模型”， 则规划求解保证可以找到规划求解模型的最佳解决方案。如果规 划求解模型不是线性的，则规划求解可能可以找到最佳解决方案， 也可能找不到。 如果规划求解模型是线性的，并且我们选择“采用线性模型”， 规划求解将使用一种非常高效的算法（单工方法）来找到该模型 的最佳解决方案。如果规划求解模型是线性的，并且我们不选择 “采用线性模型”，规划求解将使用一种非常低效的算法（ GRG2 方法），并且可能很难找到该模型的最佳解决方案。

3、
2、
1、
1、输入/导入数据 2、确定其他相关单元格内容： C列为模型计算值，D列为误差 平方值，F1~F4分别为参数a、 b、β和误差平方和 3、输入相关单元格的计算公式 （见下页）
C2~C11单元格公式： =$F$2*A2/(1+$F$1*A2^$F$3) D2~D11单元格公式： =(C2-B2)^2 F4单元格公式： =SUM(D2:D11)
25 20
溅射率（%）
40 60 能量（KeV） 2
80
100
25 20
溅射率（%）
15 10 5 0 0 20 40 60 能量（KeV） 80 100
15 10 5 0 0 20 40 60 能量（KeV） 80 100
3阶多项式拟合
y = 7E-05x 3 - 0.0146x 2 + 0.9276x + 0.673 R 2 = 0.9856
（三）利用Origin进行数据拟合
1、在数据表中选中 数据； 2、绘制散点图； 3、在“Analysis” 主菜单中选择合适的 拟合方式：如Fit Linear（线型拟合）、 Fit Polynomial（多 项式拟合）、 Fit Sigmoidal（S曲线 拟合）等。
多项式拟合参数设置
4阶多项式拟合结果
第三章 实验曲线的数据拟合
• • • • • （一）利用Microsoft Excel 进行数据拟合 （二）用Excel进行参数估计 （三）利用Origin进行数据拟合 （四）利用Origin进行复杂曲线解谱 （五）其他数据拟合软件
（一）利用Microsoft Excel 进行数据拟合
2
1
3
4
1、选中数据列 2、绘制散点图或曲线 3、对准数据点（或曲线）点击右键， 在弹出式菜单中选中“添加趋势线”类 型 4、在“添加趋势线”对话框中选择合 适的回归分析类型
q bc / (1 ac )

设定规划求解参数： 1、将F4单元格设为目标单元格； 2、使目标值求“最小值”； 3、将F1、F2、F3设为可变单元格; 4、约束条件为空； 5、按“求解”开始运算。
拟合计算结果
（3）Excel规划求解
什么是 Excel 规划求解工具？
当您想要寻找做某件事的最佳方法时，使用的就是规划求 解。或者，更正规的说法就是，当您想要在电子表格的某些 单元格中得到优化（最大化或最小化）某个目标的值时，使 用的就是规划求解。
溅射率与能量关系
注意：当 R2 值大到一 定程度后，有可能 R2 值较大的拟合曲线并 不是最佳的。例如： 本例中4阶多项式拟合 要优于6阶拟合。
25 20
溅射率（%）
15 10
6阶多项式拟合
3.3606x - 12.667 R 2 = 0.997
6 5 4 3 2 5 y = -2E-09x + 6E-07x - 7E-05x + 0.0047x - 0.1684x +
溅射率（%）
15 10 5 0 0 20 40 60 80 100 能量（KeV）
y = -1E-06x + 0.0003x - 0.0322x + 1.3697x - 2.5556 2 R = 0.9937
4 3 2
溅射率与能量关系 25 20
溅射率（%）
溅射率与能量关系 25 20
溅射率（%）
15 10 5 0 0 20 40 60 能量（KeV） 80 100
3阶多项式拟合
y = 7E-05x 3 - 0.0146x 2 + 0.9276x + 0.673 R 2 = 0.9856
4阶多项式拟合
y = -1E-06x 4 + 0.0003x 3 - 0.0322x 2 + 1.3697x - 2.5556 R 2 = 0.9937
优化模型包括三部分：目标单元格、可变单元格和约束。 · 目标单元格代表目的或目标。例如，最大化每月利润。
· 可变单元格是电子表格中我们可以进行更改或调整以优化 目标单元格的单元格。例如，每月每种产品的产量。
· 约束是置于可变单元格中的限制条件。例如，使用的资源 不能超标，并且不能生产过剩的产品。
Excel利润最大化规划求解示例
源文件：利润最大化规划求解示例.xls
利用“规划求解”工具优化方案
约束条件： 1 ）使用的人工和原材料不能超标 。也就是说，单元格 D15:D16（所 用资源）必须小于或等于单元格 F15:F16 中的值（可用资源）。 2 ）生产的产品不能超过需求数量 。也就是说，单元格 D3:I3（生产 的每种产品的磅 3 ）不能生产任何产量为负的产品 。
x ap b P
式中P是SO2的分压，x是水溶液中SO2的摩尔分数。
实验实测得到的数据如下表所示，利用Excel的回归计算工 具求出参数a和b的值： 溶解度x 0.02 0.05 0.1 0.15 0.2 分压p 0.3 0.8 2.2 3.8 5.7 0.3 0.5 0.7 1 44
10 19.3 28
拟合结果数据表
Sigmoidal(Boltzmann) 拟合结果
提示：由于采用算法不同，Excel和Origin中相同名称 拟合方法得到的拟合曲线有可能是有差异的，故不宜 完全照搬套用。
21
溅射率与能量关系 25 20
溅射率（%）
20 19 18 17 16
溅射率 y
Polynomial Fit of A_y