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

1、第三章 实验曲线的数据拟合 （一）利用（一）利用Microsoft Excel 进行数据拟合进行数据拟合 （二）用（二）用Excel进行参数估计进行参数估计 （三）利用三）利用Origin进行数据拟合进行数据拟合 （四）利用（四）利用Origin进行复杂曲线解谱进行复杂曲线解谱 （五）其他数据拟合软件（五）其他数据拟合软件1、选中数据列、选中数据列2、绘制散点图或曲线、绘制散点图或曲线3、对准数据点（或曲线）点击右键，、对准数据点（或曲线）点击右键，在弹出式菜单中选中在弹出式菜单中选中“添加趋势线添加趋势线”类类型型4、在在“添加趋势线添加趋势线”对话框中选择合对话框中选择合适的回归分析类型适

2、的回归分析类型1234（一）利用Microsoft Excel 进行数据拟合1、选中数据列、选中数据列2、绘制散点图或曲线、绘制散点图或曲线3、对准数据点（或曲线）点击右键，、对准数据点（或曲线）点击右键，在弹出式菜单中选中在弹出式菜单中选中“添加趋势线添加趋势线”所添加的三阶多所添加的三阶多项式趋势线项式趋势线对准趋势线，右击鼠标键，在弹出式对准趋势线，右击鼠标键，在弹出式菜单中选中菜单中选中“趋势线格式（趋势线格式（0）”，在弹出的在弹出的“趋势线格式趋势线格式”对话框中可对话框中可以对趋势线类型和格式进行修改。以对趋势线类型和格式进行修改。12由由3阶改阶改为为4阶阶溅射率与能量关系y

3、= -1E-06x4 + 0.0003x3 - 0.0322x2 + 1.3697x - 2.5556R2 = 0.99370510152025020406080100能量（KeV）溅射率（%）溅射率与能量关系y = 0.116x + 11.833R2 = 0.64010510152025020406080100能量（KeV）溅射率（%）溅射率与能量关系y = 5.1909Ln(x) - 1.7029R2 = 0.88410510152025020406080100能量（KeV）溅射率（%）溅射率与能量关系y = -1E-06x4 + 0.0003x3 - 0.0322x2 + 1.3697x

4、 - 2.5556R2 = 0.99370510152025020406080100能量（KeV）溅射率（%）溅射率与能量关系y = 7E-05x3 - 0.0146x2 + 0.9276x + 0.673R2 = 0.98560510152025020406080100能量（KeV）溅射率（%）线性拟合线性拟合对数拟合对数拟合3阶多项式拟合阶多项式拟合4阶多项式拟合阶多项式拟合对拟合效果好坏的评判：肉眼观察对拟合效果好坏的评判：肉眼观察 & 拟合拟合R2值综值综合判断。一般情况下，合判断。一般情况下， R2值越大（即越接近于值越大（即越接近于1），），拟合越精确。拟合越精确。注意：当

5、注意：当R2值大到一值大到一定程度后，有可能定程度后，有可能R2值较大的拟合曲线并值较大的拟合曲线并不是最佳的。例如：不是最佳的。例如：本例中本例中4阶多项式拟合阶多项式拟合要优于要优于6阶拟合。阶拟合。溅射率与能量关系y = 7E-05x3 - 0.0146x2 + 0.9276x + 0.673R2 = 0.98560510152025020406080100能量（KeV）溅射率（%）溅射率与能量关系y = -1E-06x4 + 0.0003x3 - 0.0322x2 + 1.3697x - 2.5556R2 = 0.99370510152025020406080100能量（KeV）溅射率

6、（%）溅射率与能量关系y = -2E-09x6 + 6E-07x5 - 7E-05x4 + 0.0047x3 - 0.1684x2 +3.3606x - 12.667R2 = 0.9970510152025020406080100能量（KeV）溅射率（%）3阶多项式拟合阶多项式拟合4阶多项式拟合阶多项式拟合6阶多项式拟合阶多项式拟合线性参数估计线性参数估计可以直接利用可以直接利用Excel的分析工具库中的回归计的分析工具库中的回归计算工具实现。对于算工具实现。对于非线性参数估计非线性参数估计则可以通过适当的构造则可以通过适当的构造，将问题转化成一个使误差最小的规划问题进行解决。，将问题转化成一

7、个使误差最小的规划问题进行解决。（1）线性参数估计示例）线性参数估计示例： SO2对水的溶解度实验式可以表示为：对水的溶解度实验式可以表示为：式中式中P是是SO2的分压，的分压，x是水溶液中是水溶液中SO2的摩尔分数。的摩尔分数。 实验实测得到的数据如下表所示，利用实验实测得到的数据如下表所示，利用Excel的回归计算工的回归计算工具求出参数具求出参数a和和b的值：的值：（二）用用Excel进行参数估计进行参数估计Pbapx442819.3105.73.82.20.80.310.70.50.30.20.150.10.050.02分压p溶解度x利用公式计算功能计算出利用公式计算功能计算出第一个单

8、元格对应的第一个单元格对应的P的的平方根平方根通过复制公式计算出其他单通过复制公式计算出其他单元格对应的元格对应的P的平方根的平方根在在“工具工具”菜单中选中菜单中选中”加载宏（加载宏（I）”，然后然后在弹出的加载宏对话框中选中在弹出的加载宏对话框中选中“分析工具库分析工具库”，以便将相关分析工具添加到以便将相关分析工具添加到“工具工具”菜单栏。菜单栏。在在“工具工具”菜单中出菜单中出现了新添加的菜单项。现了新添加的菜单项。1、点击、点击“工具工具”菜单栏中菜单栏中的的“数据分析（数据分析（D）”菜单菜单项，然后在弹出的项，然后在弹出的“数据数据分析分析”对话框中选中对话框中选中“回回归归”；

9、2、设置、设置 “回归回归”对话对话框中的参数；框中的参数；按住鼠标左键不放，拖拽鼠标选中相应的数据列，将数据按住鼠标左键不放，拖拽鼠标选中相应的数据列，将数据表中的表中的“x=”列设为列设为Y值；将值；将“p=”和和“p1=”列设为列设为X值。值。参数：参数：a=0.01618022 b=0.04422551 往往DBS水溶液中投入活性炭，在等温下放置达到吸附水溶液中投入活性炭，在等温下放置达到吸附平衡，平衡，DBS的平衡浓度的平衡浓度c与投入活性炭吸附量与投入活性炭吸附量q之间的关系之间的关系如下表所示：如下表所示：请应用非线性最小二乘法估计下式中的参数请应用非线性最小二乘法估计下式中的参

10、数a、b和和。)1 ( /bqacc（2）非线性参数估计示例）非线性参数估计示例：平衡浓度c1.64.52 6.8 8.1611.512.718.22938.9 57.3吸附量q170.7 228.1 258 283.7 321.3 335.4 378.6 434.6 401.3 4291、输入、输入/导入数据导入数据2、确定其他相关单元格内容：、确定其他相关单元格内容：C列为模型计算值，列为模型计算值，D列为误差列为误差平方值，平方值，F1F4分别为参数分别为参数a、b、和误差平方和和误差平方和3、输入相关单元格的计算公式、输入相关单元格的计算公式（见下页）（见下页）1、2、3、F4单元格公

11、式： =SUM(D2:D11)D2D11单元格公式： =(C2-B2)2C2C11单元格公式：=$F$2*A2/(1+$F$1*A2$F$3)1 ( /bqacc设定规划求解参数：设定规划求解参数：1、将、将F4单元格设为目标单元格；单元格设为目标单元格；2、使目标值求、使目标值求“最小值最小值”；3、将将F1、F2、F3设为可变单元格设为可变单元格;4、约束条件为空；、约束条件为空；5、按、按“求解求解”开始运算。开始运算。拟合计算结果（3）Excel规划求解什么是什么是 Excel Excel 规划求解工具？规划求解工具？ 当您想要寻找做某件事的最佳方法时，使用的就是规划求当您想要寻找做某

12、件事的最佳方法时，使用的就是规划求解。或者，更正规的说法就是，当您想要在电子表格的某些解。或者，更正规的说法就是，当您想要在电子表格的某些单元格中得到优化（最大化或最小化）某个目标的值时，使单元格中得到优化（最大化或最小化）某个目标的值时，使用的就是规划求解。用的就是规划求解。优化模型包括三部分：目标单元格、可变单元格和约束。优化模型包括三部分：目标单元格、可变单元格和约束。 目标单元格目标单元格代表目的或目标。例如，最大化每月利润。代表目的或目标。例如，最大化每月利润。 可变单元格可变单元格是电子表格中我们可以进行更改或调整以优化是电子表格中我们可以进行更改或调整以优化目标单元格的单元格。例

13、如，每月每种产品的产量。目标单元格的单元格。例如，每月每种产品的产量。 约束约束是置于可变单元格中的限制条件。例如，使用的资源是置于可变单元格中的限制条件。例如，使用的资源不能超标，并且不能生产过剩的产品。不能超标，并且不能生产过剩的产品。Excel利润最大化规划求解示例假定我们在一家金属制品公司工作，这家公司可以在他们的工厂生假定我们在一家金属制品公司工作，这家公司可以在他们的工厂生产六种产品。生产每种产品都需要人工和原材料。产六种产品。生产每种产品都需要人工和原材料。下一页所示图片中数据表的第下一页所示图片中数据表的第 4 行（行四）显示了生产一件的每种行（行四）显示了生产一件的每种产品所

14、需的人工小时数，第产品所需的人工小时数，第 5 行显示了生产一件的每种产品所需的行显示了生产一件的每种产品所需的原材料的公斤数。例如，生产一件的产品原材料的公斤数。例如，生产一件的产品 1 需要需要 6 小时人工和小时人工和 3.2 公斤原材料。公斤原材料。第第 6 行显示了每种产品单件的价格，第行显示了每种产品单件的价格，第 7 行显示了单件产品的成本，行显示了单件产品的成本，第第 9 行显示单件产品可带来的利润。例如，产品行显示单件产品可带来的利润。例如，产品 2 的价格是每件的价格是每件11.00 美元，每件的单位成本是美元，每件的单位成本是 5.70 美元，每件的利润就是美元，每件的利

15、润就是 5.30 美元。美元。第第 8 行显示了该月对每种产品的需求。例如，对产品行显示了该月对每种产品的需求。例如，对产品 3 的需求为的需求为 1041件。件。该月可提供该月可提供 4500 人工工时和人工工时和 1600 公斤的原材料。公斤的原材料。该公司如何最该公司如何最大化它每月的利润？大化它每月的利润？源文件：源文件：利润最大化规划求解示例利润最大化规划求解示例.xls一个不可行的方案源文件：源文件：利润最大化规划求解示例利润最大化规划求解示例.xls本页本页中显示的方案是不可行的，因为要实现目标利润中显示的方案是不可行的，因为要实现目标利润（7723.00），需要的人工（），需要

16、的人工（6012.5）和原材料（）和原材料（2019.5）均超出了可用的值（可用人工均超出了可用的值（可用人工4500、可用原材料、可用原材料1600）。）。源文件：源文件：利润最大化规划求解示例利润最大化规划求解示例.xls本页本页中显示的方案是可行的（但可能不是最优的），因为中显示的方案是可行的（但可能不是最优的），因为要实现目标利润（要实现目标利润（7723.00），需要的人工（），需要的人工（6012.5）和）和原材料（原材料（2019.5）均未超出了可用的值（可用人工）均未超出了可用的值（可用人工4500、可用原材料可用原材料1600）。）。源文件：源文件：利润最大化规划求解示例利润

17、最大化规划求解示例.xls利用“规划求解”工具优化方案 约束条件：约束条件： 1）使用的人工和原材料不能超标）使用的人工和原材料不能超标。也就是说，单元格。也就是说，单元格 D15:D16（所所用资源）必须小于或等于单元格用资源）必须小于或等于单元格 F15:F16 中的值（可用资源）。中的值（可用资源）。 2）生产的产品不能超过需求数量）生产的产品不能超过需求数量。也就是说，单元格。也就是说，单元格 D3:I3（生产生产的每种产品的磅的每种产品的磅 3）不能生产任何产量为负的产品）不能生产任何产量为负的产品。源文件：源文件：利润最大化规划求解示例利润最大化规划求解示例.xls通过生产通过生产

18、 1800件件的产品的产品5而不生产任何其他产品，我们的金属制而不生产任何其他产品，我们的金属制品公司每月可获得最高利润品公司每月可获得最高利润6,840.00 美元！我们无法确定通过其美元！我们无法确定通过其他方法是否可以获得他方法是否可以获得6,840.00美元的最高利润。但我们可以确定，美元的最高利润。但我们可以确定，在我们有限的资源和需求条件下，这个月的利润根本不可能超出在我们有限的资源和需求条件下，这个月的利润根本不可能超出6,840.00美元。美元。“规划求解”结果一、本例中选择一、本例中选择“采用线性模型采用线性模型”的原因是该问题是一种称为线的原因是该问题是一种称为线性模型的特

19、殊规划求解问题。基本上，在以下情况下，规划求解性模型的特殊规划求解问题。基本上，在以下情况下，规划求解模型都是线性模型：模型都是线性模型：（1 1）目标单元格是通过将表单的条件（可变单元格）目标单元格是通过将表单的条件（可变单元格）* *（约束）（约束）相加进行计算的。相加进行计算的。（2 2）每种约束都满足线性模型要求。这意味着每种约束都是通过）每种约束都满足线性模型要求。这意味着每种约束都是通过将表单的条件（可变单元格）将表单的条件（可变单元格）* *（约束）相加，然后将这些总和与（约束）相加，然后将这些总和与某个常量进行比较来评估的。某个常量进行比较来评估的。 如果规划求解模型是线性的，

20、并且我们选择如果规划求解模型是线性的，并且我们选择“采用线性模型采用线性模型”，则规划求解保证可以找到规划求解模型的最佳解决方案。如果规则规划求解保证可以找到规划求解模型的最佳解决方案。如果规划求解模型不是线性的，则规划求解可能可以找到最佳解决方案，划求解模型不是线性的，则规划求解可能可以找到最佳解决方案，也可能找不到。也可能找不到。 如果规划求解模型是线性的，并且我们选择如果规划求解模型是线性的，并且我们选择“采用线性模型采用线性模型”，规划求解将使用一种非常高效的算法（单工方法）来找到该模型规划求解将使用一种非常高效的算法（单工方法）来找到该模型的最佳解决方案。如果规划求解模型是线性的，并

21、且我们不选择的最佳解决方案。如果规划求解模型是线性的，并且我们不选择“采用线性模型采用线性模型”，规划求解将使用一种非常低效的算法（，规划求解将使用一种非常低效的算法（GRG2 GRG2 方法），并且可能很难找到该模型的最佳解决方案。方法），并且可能很难找到该模型的最佳解决方案。 关于关于“规划求解规划求解”的一些提示的一些提示二、在本例中，假定必须满足对每种产品的需求，那么就必须将二、在本例中，假定必须满足对每种产品的需求，那么就必须将需求约束从需求约束从 D3:I3 = D9:I9 D3:I3 = D9:I9D3:I3 = D9:I9。当单击当单击“求解求解”时，您将会看到时，您将会看到“

22、规划求解找不到可行的解决方案规划求解找不到可行的解决方案”消消息。该消息意味着使用我们有限的资源，无法满足对所有产品的息。该消息意味着使用我们有限的资源，无法满足对所有产品的需求。但实际上我们的模型并没有错！规划求解只是要告诉我们，需求。但实际上我们的模型并没有错！规划求解只是要告诉我们，如果我们想要满足对每种产品的需求，我们就需要增加更多的人如果我们想要满足对每种产品的需求，我们就需要增加更多的人工、更多的原材料或两者都要增加。工、更多的原材料或两者都要增加。 三、本例中如果我们允许对每种药品无限制的需求，并且允许每三、本例中如果我们允许对每种药品无限制的需求，并且允许每种药品的产量为负（清

23、除种药品的产量为负（清除“假定非负假定非负”复选框复选框 ），那么我们将看），那么我们将看到到 “设置目标单元格设置目标单元格的值未收敛的值未收敛”，这表示所用的模型有错，这表示所用的模型有错误。误。 四、在计算利润时用到了四、在计算利润时用到了SUMPRODUCT函数，公式函数，公式SUMPRODUCT(D10:I10,$D$3:$I$3)SUMPRODUCT(D10:I10,$D$3:$I$3)相当于计算：相当于计算： D10D10* *D3+E10D3+E10* *E3+F10E3+F10* *F3+G10F3+G10* *G3+H10G3+H10* *H3+I10H3+I10* *I3I3，这要比手动输入，这要比手动输入简单得多！简单得多！五、五、“$D$3”中的两个中的两个“$”共同表示对共同表示对D3单元格的绝对引