《EXCEL数据拟合及图形处理》由会员分享，可在线阅读，更多相关《EXCEL数据拟合及图形处理（67页珍藏版）》请在金锄头文库上搜索。
1、第四章第四章 EXCEL EXCEL数据拟合及图形处理数据拟合及图形处理 主要内容主要内容主要内容主要内容4.1 线性回归4.1.1一元线性回归4.1.2 用“数据分析”线性拟合4.1.3 线性回归统计4.2 多元线性回归4.3 非线性回归4.4 Excel初步图形处理回归分析简介回归分析简介回归分析简介回归分析简介 实验结果中，各物理量之间的关系极其重实验结果中，各物理量之间的关系极其重 要要 欧美学派：重视理论推导与探索；欧美学派：重视理论推导与探索； 前苏联学派：重视实验数据拟合。前苏联学派：重视实验数据拟合。 通常用通常用y计算计算f(x)近似代表实验数据点近似代表实验数据点（xi ，
2、yi）之间的关系。为此必须找到与数据之间的关系。为此必须找到与数据 （xi，yi）吻合最好的函数系数，这一过程称吻合最好的函数系数，这一过程称 为曲线拟合。为曲线拟合。回归分析简介回归分析简介回归分析简介回归分析简介 最佳拟合的判据是实验数据点与拟合曲线的偏差的最佳拟合的判据是实验数据点与拟合曲线的偏差的最佳拟合的判据是实验数据点与拟合曲线的偏差的最佳拟合的判据是实验数据点与拟合曲线的偏差的平方和最小（最小二乘法），即：平方和最小（最小二乘法），即：平方和最小（最小二乘法），即：平方和最小（最小二乘法），即：用最小二乘法求最佳拟合参数的过程称为回归分析。用最小二乘法求最佳拟合参数的过程称为回归
3、分析。用最小二乘法求最佳拟合参数的过程称为回归分析。用最小二乘法求最佳拟合参数的过程称为回归分析。回归分析简介回归分析简介回归分析简介回归分析简介回归分析是一种统计技术，用以定量表达实验变量回归分析是一种统计技术，用以定量表达实验变量回归分析是一种统计技术，用以定量表达实验变量回归分析是一种统计技术，用以定量表达实验变量之间的关系和相关程度。之间的关系和相关程度。之间的关系和相关程度。之间的关系和相关程度。 目的之一是根据已知的体系变量间的函数关系（数目的之一是根据已知的体系变量间的函数关系（数目的之一是根据已知的体系变量间的函数关系（数目的之一是根据已知的体系变量间的函数关系（数学模型）已知
4、，回归出系数；学模型）已知，回归出系数；学模型）已知，回归出系数；学模型）已知，回归出系数； 另一目的是获得回归参数的另一目的是获得回归参数的另一目的是获得回归参数的另一目的是获得回归参数的标准偏差标准偏差标准偏差标准偏差和和和和相关系数相关系数相关系数相关系数以以以以确定模型是否适合实验数据。确定模型是否适合实验数据。确定模型是否适合实验数据。确定模型是否适合实验数据。 4.1 4.1 线性回归线性回归线性回归线性回归4.1 .1 4.1 .1 一元线性回归一元线性回归将一组数据拟合成一直线最简单：将一组数据拟合成一直线最简单： y=ax+b在化工实验中最常见的应用是分析产物组成在化工实验中
5、最常见的应用是分析产物组成时作标准曲线。时作标准曲线。示例：示例：在萃取分离乙苯和辛烷时，要用气相在萃取分离乙苯和辛烷时，要用气相色谱分析产品（乙苯色谱分析产品（乙苯- -辛烷混合溶液）中乙苯辛烷混合溶液）中乙苯的含量。首先配制好一系列已知浓度的乙苯的含量。首先配制好一系列已知浓度的乙苯- -辛烷标准溶液，用色谱逐个分析，得到峰面辛烷标准溶液，用色谱逐个分析，得到峰面积数据，与已知浓度拟合得到标准曲线。积数据，与已知浓度拟合得到标准曲线。4.1 .1 4.1 .1 一元线性回归一元线性回归一元线性回归一元线性回归1.1.用函数用函数SLOPE()SLOPE()和和INTERCEPT()INTE
6、RCEPT()SLOPE(ySLOPE(y值数列，值数列，x x值数列），返回线性回归值数列），返回线性回归直线的斜率直线的斜率aINTERCEPT(yINTERCEPT(y值数列，值数列，x x值数列），返回截距值数列），返回截距b 4.1 .1 4.1 .1 一元线性回归一元线性回归一元线性回归一元线性回归2.2.用函数用函数LINEST()LINEST()LINESTLINEST可处理的线性方程的通式为：可处理的线性方程的通式为： y=ax+b 或或 y=a1x1+a2x2+amxm+bLINESTLINEST的语法为：的语法为：LINEST(yLINEST(y值数列值数列,x,x值数列
7、值数列, ,常数常数_ _逻辑逻辑, ,统计统计_ _逻辑逻辑) )常数常数_ _逻辑是一逻辑值，指明是否强制使常数逻辑是一逻辑值，指明是否强制使常数b b为为0 0。如果它是如果它是TRUETRUE、1 1或被省略，回归参数包括截距或被省略，回归参数包括截距b b；如果它是如果它是FALSEFALSE或或0 0，则拟合不包括，则拟合不包括b b，即得到过原点，即得到过原点的直线的直线y=ax。4.1 .1 4.1 .1 一元线性回归一元线性回归一元线性回归一元线性回归2.2.用函数用函数LINEST()LINEST()统计统计_ _逻辑是一逻辑值，指明是否返回附加的逻辑是一逻辑值，指明是否返
8、回附加的回归统计值。回归统计值。如果它是如果它是TRUETRUE或或1 1，LINESTLINEST除给出回归系数除给出回归系数ai和和b b外，还给出回归统计数组。外，还给出回归统计数组。3. LINEST中的线性回归分析设有设有设有设有N N个数据点（个数据点（个数据点（个数据点（x xi i，y yi i），总平方和为：），总平方和为：），总平方和为：），总平方和为：3. LINEST3. LINEST中的线性回归分析中的线性回归分析 残差平方和残差平方和残差平方和残差平方和SSSS残差残差残差残差反映测量值反映测量值反映测量值反映测量值y yi i与按拟合函数计算值与按拟合函数计算值与
9、按拟合函数计算值与按拟合函数计算值y y计算计算计算计算的偏差，残差平方和越小，表明拟合越好。的偏差，残差平方和越小，表明拟合越好。的偏差，残差平方和越小，表明拟合越好。的偏差，残差平方和越小，表明拟合越好。回归平方和反映在回归平方和反映在回归平方和反映在回归平方和反映在y y的总平方和中由的总平方和中由的总平方和中由的总平方和中由x x和和和和y y的线性关系的线性关系的线性关系的线性关系引起的引起的引起的引起的y y的变化，其数值越大越好。的变化，其数值越大越好。的变化，其数值越大越好。的变化，其数值越大越好。拟合好坏取决于拟合好坏取决于拟合好坏取决于拟合好坏取决于SSSS回归回归回归回归
10、在在在在SSSS中所占比例，因此定义中所占比例，因此定义中所占比例，因此定义中所占比例，因此定义判定系数：判定系数：判定系数：判定系数：3. LINEST3. LINEST中的线性回归分析中的线性回归分析R R2 2表示回归分析方程的结果反映变量间关系的程度表示回归分析方程的结果反映变量间关系的程度表示回归分析方程的结果反映变量间关系的程度表示回归分析方程的结果反映变量间关系的程度的标志，若的标志，若的标志，若的标志，若R R2 2 0.900为为为为正相关，直线的斜率为正；正相关，直线的斜率为正；正相关，直线的斜率为正；正相关，直线的斜率为正； R R00为负相关，直线的为负相关，直线的为负
11、相关，直线的为负相关，直线的斜率为负。斜率为负。斜率为负。斜率为负。R R=0=0为不相关，为不相关，为不相关，为不相关，x x与与与与y y无线性关系。无线性关系。无线性关系。无线性关系。3. LINEST中的线性回归分析在实际工作中也经常用到在实际工作中也经常用到在实际工作中也经常用到在实际工作中也经常用到F F- -检验：检验：检验：检验：自由度自由度自由度自由度dfdfN-kN-k，一元线性回归参数，一元线性回归参数，一元线性回归参数，一元线性回归参数k k为为为为2 2。F F- -统计统计统计统计用以判定设计的关系式是否有效。用以判定设计的关系式是否有效。用以判定设计的关系式是否有
12、效。用以判定设计的关系式是否有效。测量精度用测量精度用测量精度用测量精度用y y值的标准误差值的标准误差值的标准误差值的标准误差SE(SE(y y) )来估计：来估计：来估计：来估计：SE(SE(y y) )越小，根据拟合直线预测的越小，根据拟合直线预测的越小，根据拟合直线预测的越小，根据拟合直线预测的y y值越准确。值越准确。值越准确。值越准确。3. LINEST3. LINEST中的线性回归分析中的线性回归分析y y是随机的，因此由实验数据得到的回归参数是随机的，因此由实验数据得到的回归参数是随机的，因此由实验数据得到的回归参数是随机的，因此由实验数据得到的回归参数a a、b b也也也也是
13、随机的，即同一实验做若干次或不同人做同一实是随机的，即同一实验做若干次或不同人做同一实是随机的，即同一实验做若干次或不同人做同一实是随机的，即同一实验做若干次或不同人做同一实验，每次实验得到的验，每次实验得到的验，每次实验得到的验，每次实验得到的a a、b b值也不相同。统计上可以值也不相同。统计上可以值也不相同。统计上可以值也不相同。统计上可以用用用用a a、b b的标准偏差来衡量的标准偏差来衡量的标准偏差来衡量的标准偏差来衡量a a、b b数值的波动：数值的波动：数值的波动：数值的波动：3. LINEST3. LINEST中的线性回归分析中的线性回归分析SESE（a a）、）、）、）、SE
14、SE（b b）的波动性与标准偏差）的波动性与标准偏差）的波动性与标准偏差）的波动性与标准偏差SESE（y y）的）的）的）的大小有关，也与大小有关，也与大小有关，也与大小有关，也与x x值的波动性有关，值的波动性有关，值的波动性有关，值的波动性有关，x x值越离散（也值越离散（也值越离散（也值越离散（也就是说就是说就是说就是说x x取值区间越大），取值区间越大），取值区间越大），取值区间越大）， SESE（a a）、）、）、）、SESE（b b）的值）的值）的值）的值越小。此外，越小。此外，越小。此外，越小。此外， SESE（b b）还与测量点数）还与测量点数）还与测量点数）还与测量点数N N
15、有关，测量有关，测量有关，测量有关，测量次数越多，次数越多，次数越多，次数越多， SESE（b b）越小。）越小。）越小。）越小。3. LINEST中的线性回归分析示例：乙苯辛烷标准曲线的回归示例：乙苯辛烷标准曲线的回归示例：乙苯辛烷标准曲线的回归示例：乙苯辛烷标准曲线的回归4.1.2 使用“数据分析”线性拟合4.1.2 使用“数据分析”线性拟合Excel“数据分析数据分析”步骤：步骤：以标准曲线制作为例以标准曲线制作为例1）打开）打开“工具工具”“数据分析数据分析”，出现，出现“数数据分析据分析”对话框。选其中的对话框。选其中的“回归回归”，出现，出现“回归回归”对话框。对话框。2）在）在“
16、输入输入”区域，分别在区域，分别在“Y值输入区域值输入区域”和和“X值输入区域值输入区域”内输入内输入y数据区域的引用：数据区域的引用：$K$2: $K$12和和x数据区域引用数据区域引用$J$2: $J$123) 选取选取“标志标志”(注意与注意与Y、X输入区域一致！输入区域一致！)和和“置信度置信度”复选框，如果要强制回归直复选框，如果要强制回归直线过原点，则选中线过原点，则选中“常数为零常数为零”，本例不选。，本例不选。4.1.2 使用“数据分析”线性拟合4）在在在在“ “输出选项输出选项输出选项输出选项” ”区有区有区有区有3 3 3 3个单选框，用来指定回归分个单选框，用来指定回归分
17、个单选框，用来指定回归分个单选框，用来指定回归分析数据输出的位置。析数据输出的位置。析数据输出的位置。析数据输出的位置。 若选中若选中若选中若选中“ “新工作表组新工作表组新工作表组新工作表组” ”，则可在当前工作簿中，则可在当前工作簿中，则可在当前工作簿中，则可在当前工作簿中插入新工作表，并由新工作表的插入新工作表，并由新工作表的插入新工作表，并由新工作表的插入新工作表，并由新工作表的A1A1A1A1单元格开始粘单元格开始粘单元格开始粘单元格开始粘贴计算结果。如果需要给新工作表命名，可在右贴计算结果。如果需要给新工作表命名，可在右贴计算结果。如果需要给新工作表命名，可在右贴计算结果。如果需要
18、给新工作表命名，可在右侧的编辑框中键入名称。侧的编辑框中键入名称。侧的编辑框中键入名称。侧的编辑框中键入名称。 若选中若选中若选中若选中“ “新工作簿新工作簿新工作簿新工作簿” ”，则可创建一个新工作簿，则可创建一个新工作簿，则可创建一个新工作簿，则可创建一个新工作簿，并在新工作簿中的新工作表中粘贴结果。并在新工作簿中的新工作表中粘贴结果。并在新工作簿中的新工作表中粘贴结果。并在新工作簿中的新工作表中粘贴结果。 若选中若选中若选中若选中“ “输出区域输出区域输出区域输出区域” ”，则计算结果粘贴在原工，则计算结果粘贴在原工，则计算结果粘贴在原工，则计算结果粘贴在原工作表上。本例选中此项，要求在
19、右边的编辑框内作表上。本例选中此项，要求在右边的编辑框内作表上。本例选中此项，要求在右边的编辑框内作表上。本例选中此项，要求在右边的编辑框内输入输入输入输入“ “汇总输出表汇总输出表汇总输出表汇总输出表” ”左上角单元格的位置。左上角单元格的位置。左上角单元格的位置。左上角单元格的位置。4.1.2 使用“数据分析”线性拟合4.1.2 使用“数据分析”线性拟合5）单击确定，给出汇总输出表单击确定，给出汇总输出表单击确定，给出汇总输出表单击确定，给出汇总输出表(SUMMARY OUTPUT),(SUMMARY OUTPUT),(SUMMARY OUTPUT),(SUMMARY OUTPUT),其其
20、其其中包括回归统计、方差分析和回归系数及统计中包括回归统计、方差分析和回归系数及统计中包括回归统计、方差分析和回归系数及统计中包括回归统计、方差分析和回归系数及统计4.1.3 线性回归统计1. 1. 1. 1. 自由度自由度自由度自由度dfdfdfdf的概念的概念的概念的概念在方差分析表中有在方差分析表中有在方差分析表中有在方差分析表中有3 3 3 3种自由度：种自由度：种自由度：种自由度：回归平方和回归平方和回归平方和回归平方和SSSS回归回归回归回归的自由度：的自由度：的自由度：的自由度：dfdfdfdf回归回归回归回归m m m mk k k k-1-1-1-1残差平方和残差平方和残差平
21、方和残差平方和SSSS残差残差残差残差的的的的自由度：自由度：自由度：自由度：dfdfdfdf残差残差残差残差N N N N- - - -k k k k总平方和总平方和总平方和总平方和SSSS的的的的自由度：自由度：自由度：自由度：dfdfdfdfSSSSSSSSN N N N-1-1-1-1m m m m：自变量：自变量：自变量：自变量x x的系数数目，的系数数目，的系数数目，的系数数目，k k k k：线性回归参数的数目，：线性回归参数的数目，：线性回归参数的数目，：线性回归参数的数目，N N N N：实验点的数目。：实验点的数目。：实验点的数目。：实验点的数目。回归方差回归方差回归方差回
22、归方差MSMSMSMS回归回归回归回归= = = = SSSS回归回归回归回归/ df/ df/ df/ df回归回归回归回归残差方差残差方差残差方差残差方差MSMSMSMS残差残差残差残差= = = = SSSS残差残差残差残差/ df/ df/ df/ df残差残差残差残差4.1.3 线性回归统计2. 2. 2. 2. 回归统计的作用回归统计的作用回归统计的作用回归统计的作用回归统计可决定回归系数的值及其有效性，例如用最回归统计可决定回归系数的值及其有效性，例如用最回归统计可决定回归系数的值及其有效性，例如用最回归统计可决定回归系数的值及其有效性，例如用最小二乘法拟合的曲线是否有效，回归参
23、数的精度及置小二乘法拟合的曲线是否有效，回归参数的精度及置小二乘法拟合的曲线是否有效，回归参数的精度及置小二乘法拟合的曲线是否有效，回归参数的精度及置信区间，回归方程拟合好坏等。信区间，回归方程拟合好坏等。信区间，回归方程拟合好坏等。信区间，回归方程拟合好坏等。残差平方和在一定程度上反映了拟合好坏。然而在检残差平方和在一定程度上反映了拟合好坏。然而在检残差平方和在一定程度上反映了拟合好坏。然而在检残差平方和在一定程度上反映了拟合好坏。然而在检验回归线有无意义、因变量验回归线有无意义、因变量验回归线有无意义、因变量验回归线有无意义、因变量y y与自变量与自变量与自变量与自变量x x间是否真符合间
24、是否真符合间是否真符合间是否真符合回归方程所示的函数关系、需要定量反映拟合好坏时回归方程所示的函数关系、需要定量反映拟合好坏时回归方程所示的函数关系、需要定量反映拟合好坏时回归方程所示的函数关系、需要定量反映拟合好坏时，还要经常用到相关系数，还要经常用到相关系数，还要经常用到相关系数，还要经常用到相关系数R R和和和和F F- -统计。统计。统计。统计。4.1.3 4.1.3 线性回归统计线性回归统计3 3 3 3 相关系数相关系数相关系数相关系数R R R R和和和和F F- -统计统计统计统计从相关系数从相关系数从相关系数从相关系数R R检验表中查到在某一自由度检验表中查到在某一自由度检验
25、表中查到在某一自由度检验表中查到在某一自由度dfdfdfdf和指定置和指定置和指定置和指定置信水平信水平信水平信水平下的相关系数临界值下的相关系数临界值下的相关系数临界值下的相关系数临界值R Rdf,df, ，若计算得到的，若计算得到的，若计算得到的，若计算得到的相关系数相关系数相关系数相关系数R R大于大于大于大于R Rdf,df, ， 则则则则x x和和和和y y显著相关显著相关显著相关显著相关。一般。一般。一般。一般R R R R应在应在应在应在0.990.990.990.99以上。以上。以上。以上。F F- -统计也有类似的统计也有类似的统计也有类似的统计也有类似的F F检验表，根据给
26、定的置信度可以检验表，根据给定的置信度可以检验表，根据给定的置信度可以检验表，根据给定的置信度可以查得临界值查得临界值查得临界值查得临界值F F(df(df(df(df回归回归回归回归，dfdfdfdf残差残差残差残差），若计算的），若计算的），若计算的），若计算的F F F F值大于值大于值大于值大于F F F F检验表临界值，则检验表临界值，则检验表临界值，则检验表临界值，则x x和和和和y y显著相关。显著相关。显著相关。显著相关。4.1.3 4.1.3 线性回归统计线性回归统计4 4 4 4 t t- -检验检验检验检验t t- -检验常用于检验常用于检验常用于检验常用于比较两条比较两
27、条比较两条比较两条回归曲线。同样，有现成的回归曲线。同样，有现成的回归曲线。同样，有现成的回归曲线。同样，有现成的t t 检验表，表中列出在某一自由度检验表，表中列出在某一自由度检验表，表中列出在某一自由度检验表，表中列出在某一自由度dfdf和指定置信水平和指定置信水平和指定置信水平和指定置信水平下的下的下的下的t tdf,df, 。以适当的方法计算。以适当的方法计算。以适当的方法计算。以适当的方法计算t t值，与查得的临界值，与查得的临界值，与查得的临界值，与查得的临界值进行比较，若计算值大于等于临界值，则比较的值进行比较，若计算值大于等于临界值，则比较的值进行比较，若计算值大于等于临界值，
28、则比较的值进行比较，若计算值大于等于临界值，则比较的两个量有显著差别，否则无显著差别。两个量有显著差别，否则无显著差别。两个量有显著差别，否则无显著差别。两个量有显著差别，否则无显著差别。注：上述检验必须在指定的置信度范围内进行，最注：上述检验必须在指定的置信度范围内进行，最注：上述检验必须在指定的置信度范围内进行，最注：上述检验必须在指定的置信度范围内进行，最常用的置信度为常用的置信度为常用的置信度为常用的置信度为9595。若置信度太高（如。若置信度太高（如。若置信度太高（如。若置信度太高（如9999），），），），一些有用的数据可能被排除在外；若太低，则一些一些有用的数据可能被排除在外；若
29、太低，则一些一些有用的数据可能被排除在外；若太低，则一些一些有用的数据可能被排除在外；若太低，则一些无意义的数据将被作为有效数据加以考虑。无意义的数据将被作为有效数据加以考虑。无意义的数据将被作为有效数据加以考虑。无意义的数据将被作为有效数据加以考虑。4.2 4.2 多元线性回归多元线性回归LINESTLINESTLINESTLINEST和和和和“ “数据分析数据分析数据分析数据分析” ”的的的的“ “回归回归回归回归” ”可以对多个自变可以对多个自变可以对多个自变可以对多个自变量量量量x xi i的的的的函数函数函数函数y=a1x1+a2x2+amxm+b进行线性拟合，也可以对进行线性拟合，
30、也可以对进行线性拟合，也可以对进行线性拟合，也可以对x x的多项式进行拟合。的多项式进行拟合。的多项式进行拟合。的多项式进行拟合。4.2.1 4.2.1 4.2.1 4.2.1 多元线性回归多元线性回归多元线性回归多元线性回归示例：烃类溶质在离子液体中无限稀释活度系数的示例：烃类溶质在离子液体中无限稀释活度系数的示例：烃类溶质在离子液体中无限稀释活度系数的示例：烃类溶质在离子液体中无限稀释活度系数的定量结构相关（定量结构相关（定量结构相关（定量结构相关（QSPRQSPR）模型。）模型。）模型。）模型。两种方法：两种方法：两种方法：两种方法：LINEST()LINEST()函数和函数和函数和函数
31、和“ “数据分析数据分析数据分析数据分析” ”工具工具工具工具4.2.1 4.2.1 4.2.1 4.2.1 多元线性回归多元线性回归多元线性回归多元线性回归室温离子液体简介室温离子液体简介室温离子液体简介室温离子液体简介4.2.1 4.2.1 4.2.1 4.2.1 多元线性回归多元线性回归多元线性回归多元线性回归LinestLinestLinestLinest法：法：法：法：先选中五行连先选中五行连先选中五行连先选中五行连续单元格（列续单元格（列续单元格（列续单元格（列数由系数的个数由系数的个数由系数的个数由系数的个数确定），输入数确定），输入数确定），输入数确定），输入linest(Yl
32、inest(Ylinest(Ylinest(Y数列，数列，数列，数列，多个多个多个多个X X X X数列数列数列数列,1,1),1,1),1,1),1,1)，按按按按Ctrl+ShiftCtrl+ShiftCtrl+ShiftCtrl+Shift+ + + +EnterEnterEnterEnter键即可。键即可。键即可。键即可。4.2.1 4.2.1 4.2.1 4.2.1 多元线性回归多元线性回归多元线性回归多元线性回归计算计算计算计算Y Y Y Y值时注意代入参数次序与回归参数是相反的！值时注意代入参数次序与回归参数是相反的！值时注意代入参数次序与回归参数是相反的！值时注意代入参数次序与
33、回归参数是相反的！4.2.1 4.2.1 4.2.1 4.2.1 多元线性回归多元线性回归多元线性回归多元线性回归数据分析工具：工具数据分析工具：工具数据分析工具：工具数据分析工具：工具- - - -数据分析数据分析数据分析数据分析- - - -回归回归回归回归4.2 4.2 多元线性回归多元线性回归4.2.2 4.2.2 4.2.2 4.2.2 多元线性回归系数的相关性多元线性回归系数的相关性多元线性回归系数的相关性多元线性回归系数的相关性对烃类溶质在离子液体中无限稀释活度系数的对烃类溶质在离子液体中无限稀释活度系数的对烃类溶质在离子液体中无限稀释活度系数的对烃类溶质在离子液体中无限稀释活度
34、系数的定量结构相关（定量结构相关（定量结构相关（定量结构相关（QSPRQSPR）模型，可由其）模型，可由其）模型，可由其）模型，可由其t- t-统计看出：统计看出：统计看出：统计看出：4.2.2 4.2.2 4.2.2 4.2.2 多元线性回归系数的相关性多元线性回归系数的相关性多元线性回归系数的相关性多元线性回归系数的相关性多元回归系数的多元回归系数的多元回归系数的多元回归系数的t t t t-Stat-Stat-Stat-Stat均大于均大于均大于均大于1 1 1 1，说明这些系数预测，说明这些系数预测，说明这些系数预测，说明这些系数预测无限稀释活度系数无限稀释活度系数无限稀释活度系数无限
35、稀释活度系数 的有效性。其中水合能的的有效性。其中水合能的的有效性。其中水合能的的有效性。其中水合能的t t t t- - - -StatStatStatStat最大，说明它在预测最大，说明它在预测最大，说明它在预测最大，说明它在预测 上更有效。上更有效。上更有效。上更有效。多元线性拟合的两个或多个自变量间可能相关，即多元线性拟合的两个或多个自变量间可能相关，即多元线性拟合的两个或多个自变量间可能相关，即多元线性拟合的两个或多个自变量间可能相关，即一个自变量是另外一个自变量的线性函数。如果两一个自变量是另外一个自变量的线性函数。如果两一个自变量是另外一个自变量的线性函数。如果两一个自变量是另外
36、一个自变量的线性函数。如果两个自变量完全相关，则可能在单元格中出现个自变量完全相关，则可能在单元格中出现个自变量完全相关，则可能在单元格中出现个自变量完全相关，则可能在单元格中出现#NUM!#NUM!符号。符号。符号。符号。“ “数据分析数据分析数据分析数据分析” ”中有中有中有中有“ “相关系数相关系数相关系数相关系数” ”程序，用它程序，用它程序，用它程序，用它可以可以可以可以检查自变量的相关性。检查自变量的相关性。检查自变量的相关性。检查自变量的相关性。4.2.2 4.2.2 4.2.2 4.2.2 多元线性回归系数的相关性多元线性回归系数的相关性多元线性回归系数的相关性多元线性回归系数
37、的相关性4.2.2 4.2.2 4.2.2 4.2.2 多元线性回归系数的相关性多元线性回归系数的相关性多元线性回归系数的相关性多元线性回归系数的相关性由下图可见，这是一个对称矩阵，对角元素为自变由下图可见，这是一个对称矩阵，对角元素为自变由下图可见，这是一个对称矩阵，对角元素为自变由下图可见，这是一个对称矩阵，对角元素为自变量与自身的相关系数，因此均为量与自身的相关系数，因此均为量与自身的相关系数，因此均为量与自身的相关系数，因此均为1 1。非对角元素是两。非对角元素是两。非对角元素是两。非对角元素是两个不同自变量间的相关系数。如果该值接近于个不同自变量间的相关系数。如果该值接近于个不同自变
38、量间的相关系数。如果该值接近于个不同自变量间的相关系数。如果该值接近于1 1时，时，时，时，说明两自变量间有显著相关性。说明两自变量间有显著相关性。说明两自变量间有显著相关性。说明两自变量间有显著相关性。4.2.3 4.2.3 多项式拟合多项式拟合有时实验数据表现为一曲线，相应的拟合函数未知有时实验数据表现为一曲线，相应的拟合函数未知有时实验数据表现为一曲线，相应的拟合函数未知有时实验数据表现为一曲线，相应的拟合函数未知，需要一种普适的函数拟合曲线。常用方法之一就，需要一种普适的函数拟合曲线。常用方法之一就，需要一种普适的函数拟合曲线。常用方法之一就，需要一种普适的函数拟合曲线。常用方法之一就
39、是用多项式拟合。原则上任何连续函数均可用多项是用多项式拟合。原则上任何连续函数均可用多项是用多项式拟合。原则上任何连续函数均可用多项是用多项式拟合。原则上任何连续函数均可用多项式展开：式展开：式展开：式展开：若将变量进行变换：若将变量进行变换：若将变量进行变换：若将变量进行变换： 则多项式化为多元一次函数：则多项式化为多元一次函数：则多项式化为多元一次函数：则多项式化为多元一次函数：4.2.3 4.2.3 多项式拟合多项式拟合可用可用可用可用LINESTLINEST和和和和“ “回归回归回归回归” ”求多项式的参数求多项式的参数求多项式的参数求多项式的参数b b、a a1 1、a a2 2a
40、an n及其回归统计。及其回归统计。及其回归统计。及其回归统计。通常到三次方就有中等精度。在实际工作中，在满通常到三次方就有中等精度。在实际工作中，在满通常到三次方就有中等精度。在实际工作中，在满通常到三次方就有中等精度。在实际工作中，在满足拟合精度的前提下多项式的阶数要尽可能的低。足拟合精度的前提下多项式的阶数要尽可能的低。足拟合精度的前提下多项式的阶数要尽可能的低。足拟合精度的前提下多项式的阶数要尽可能的低。对于对于对于对于N N个数据点，用于拟合的多项式最高阶数为个数据点，用于拟合的多项式最高阶数为个数据点，用于拟合的多项式最高阶数为个数据点，用于拟合的多项式最高阶数为N N-1-14.
41、2.3 4.2.3 4.2.3 4.2.3 多项式拟合多项式拟合多项式拟合多项式拟合示例：示例：示例：示例：1-1-1-1-己烯的分压己烯的分压己烯的分压己烯的分压P P与其在离子液体与其在离子液体与其在离子液体与其在离子液体BMIMPFBMIMPFBMIMPFBMIMPF6 6 6 6 中的溶解度中的溶解度中的溶解度中的溶解度x x间为非直线关系，试用一多项式经验间为非直线关系，试用一多项式经验间为非直线关系，试用一多项式经验间为非直线关系，试用一多项式经验公式表示公式表示公式表示公式表示P P x x的函数关系。的函数关系。的函数关系。的函数关系。4.3 4.3 4.3 4.3 非线性回归
42、规划求解法非线性回归规划求解法非线性回归规划求解法非线性回归规划求解法1. 1. 操作过程操作过程操作过程操作过程1 1）在工作表上端适当单元格内输入待定系数的初始）在工作表上端适当单元格内输入待定系数的初始）在工作表上端适当单元格内输入待定系数的初始）在工作表上端适当单元格内输入待定系数的初始值（可变单元格）。值（可变单元格）。值（可变单元格）。值（可变单元格）。2 2）输入需要拟合的实验数据：自变量）输入需要拟合的实验数据：自变量）输入需要拟合的实验数据：自变量）输入需要拟合的实验数据：自变量x x数列和因变数列和因变数列和因变数列和因变量量量量y y实验值实验值实验值实验值数列。数列。数
43、列。数列。3 3）添加）添加）添加）添加y y计算值计算值计算值计算值数据列。它们是用拟合函数计算所得数据列。它们是用拟合函数计算所得数据列。它们是用拟合函数计算所得数据列。它们是用拟合函数计算所得的数值，公式中含的数值，公式中含的数值，公式中含的数值，公式中含x x值和一个或数个待定系数。值和一个或数个待定系数。值和一个或数个待定系数。值和一个或数个待定系数。4 4）另一列填入每一数据点的残差平方）另一列填入每一数据点的残差平方）另一列填入每一数据点的残差平方）另一列填入每一数据点的残差平方( ( y y实验值实验值实验值实验值- - y y计算值计算值计算值计算值) )2 25 5）选定一
44、单元格，在此计算残差平方和）选定一单元格，在此计算残差平方和）选定一单元格，在此计算残差平方和）选定一单元格，在此计算残差平方和( (目标单元格目标单元格目标单元格目标单元格) )6 6）用）用）用）用“ “规划求解规划求解规划求解规划求解” ”改变拟合函数的待定系数（可变单改变拟合函数的待定系数（可变单改变拟合函数的待定系数（可变单改变拟合函数的待定系数（可变单元元元元格），使得残差平方和的值极小。格），使得残差平方和的值极小。格），使得残差平方和的值极小。格），使得残差平方和的值极小。4.3 4.3 4.3 4.3 非线性回归规划求解法非线性回归规划求解法非线性回归规划求解法非线性回归规划
45、求解法注意：注意：注意：注意：1 1）“ “规划求解规划求解规划求解规划求解” ”是一搜索程序，若设置的初始值接近是一搜索程序，若设置的初始值接近是一搜索程序，若设置的初始值接近是一搜索程序，若设置的初始值接近最最最最终值，则它能以最快和最有效的方式找到解。反之，终值，则它能以最快和最有效的方式找到解。反之，终值，则它能以最快和最有效的方式找到解。反之，终值，则它能以最快和最有效的方式找到解。反之，若设置的初始值偏离最终目标值太远，若设置的初始值偏离最终目标值太远，若设置的初始值偏离最终目标值太远，若设置的初始值偏离最终目标值太远，“ “规划求解规划求解规划求解规划求解” ”可可可可能得不到方
46、程的解。能得不到方程的解。能得不到方程的解。能得不到方程的解。2 2）为保证）为保证）为保证）为保证“ “规划求解规划求解规划求解规划求解” ”得到的是全局性解而不是区域得到的是全局性解而不是区域得到的是全局性解而不是区域得到的是全局性解而不是区域性性性性解，最好用几套不同的初始值求解。解，最好用几套不同的初始值求解。解，最好用几套不同的初始值求解。解，最好用几套不同的初始值求解。3 3）“ “规划求解规划求解规划求解规划求解” ”得到的最小二乘法拟合系数可能会因得到的最小二乘法拟合系数可能会因得到的最小二乘法拟合系数可能会因得到的最小二乘法拟合系数可能会因起起起起始值不同而略有差别。始值不同
47、而略有差别。始值不同而略有差别。始值不同而略有差别。4.3 4.3 4.3 4.3 非线性回归规划求解法非线性回归规划求解法非线性回归规划求解法非线性回归规划求解法示例：示例：示例：示例：1-1-己烯在离子液体中活度系数的己烯在离子液体中活度系数的己烯在离子液体中活度系数的己烯在离子液体中活度系数的WilsonWilson模型关联模型关联模型关联模型关联: :实验测得实验测得实验测得实验测得283.15K283.15K时不同时不同时不同时不同x x己烯己烯己烯己烯下己烯在离子液体中的活下己烯在离子液体中的活下己烯在离子液体中的活下己烯在离子液体中的活度系数值度系数值度系数值度系数值 己烯己烯己
48、烯己烯，试用，试用，试用，试用WilsonWilson模型关联实验结果，得模型关联实验结果，得模型关联实验结果，得模型关联实验结果，得出两个相互作用参数。出两个相互作用参数。出两个相互作用参数。出两个相互作用参数。4.3 4.3 4.3 4.3 非线性回归规划求解法非线性回归规划求解法非线性回归规划求解法非线性回归规划求解法规划求解过程：规划求解过程：规划求解过程：规划求解过程：1 1）在）在）在）在A A、B B两列列出两列列出两列列出两列列出x x己烯己烯己烯己烯和和和和 己烯己烯己烯己烯的实验结果的实验结果的实验结果的实验结果，并给出，并给出，并给出，并给出两个相互作用参数两个相互作用参
49、数两个相互作用参数两个相互作用参数 1212 和和和和 2121的初始值。的初始值。的初始值。的初始值。2 2）在）在）在）在C C列根据列根据列根据列根据WilsonWilson模型得出模型得出模型得出模型得出 己烯己烯己烯己烯的计算值的计算值的计算值的计算值: := =-LN(A4+$D$1*(1-A4)+(1-A4)*($D$1/(A4+$D$1*(1-LN(A4+$D$1*(1-A4)+(1-A4)*($D$1/(A4+$D$1*(1-A4)-$D$2/(1-A4+$D$2*A4)A4)-$D$2/(1-A4+$D$2*A4)。根据残差平方的定义，在根据残差平方的定义，在根据残差平方的
50、定义，在根据残差平方的定义，在D D列得到残差平方：列得到残差平方：列得到残差平方：列得到残差平方：=(B4-C4)2=(B4-C4)2，并在，并在，并在，并在D26D26单元格求出残差平方和：单元格求出残差平方和：单元格求出残差平方和：单元格求出残差平方和：=SUM(D4:D25)=SUM(D4:D25)。4.3 4.3 4.3 4.3 非线性回归规划求解法非线性回归规划求解法非线性回归规划求解法非线性回归规划求解法规划求解过程：规划求解过程：规划求解过程：规划求解过程：3 3）打开）打开）打开）打开“ “工具工具工具工具” ”菜单，选菜单，选菜单，选菜单，选“ “规划求解规划求解规划求解规
51、划求解” ”，出现规划求，出现规划求，出现规划求，出现规划求解对解对解对解对话框。话框。话框。话框。“ “设置目标单元格设置目标单元格设置目标单元格设置目标单元格” ”选残差平方和单元格位置选残差平方和单元格位置选残差平方和单元格位置选残差平方和单元格位置“ “$D$26$D$26。根据最小二乘法，残差平方和应为极小，因。根据最小二乘法，残差平方和应为极小，因。根据最小二乘法，残差平方和应为极小，因。根据最小二乘法，残差平方和应为极小，因此在此在此在此在” ”等于等于等于等于“ “的三个单选框中选的三个单选框中选的三个单选框中选的三个单选框中选“ “最小最小最小最小” ”。“ “可变单可变单可
52、变单可变单元格元格元格元格” ”选相互作用参数数值所在单元格：选相互作用参数数值所在单元格：选相互作用参数数值所在单元格：选相互作用参数数值所在单元格：$D$1:$D$2$D$1:$D$2。4 4）不要填不要填不要填不要填“ “约束约束约束约束” ”栏。用最小二乘法进行数据拟合时，栏。用最小二乘法进行数据拟合时，栏。用最小二乘法进行数据拟合时，栏。用最小二乘法进行数据拟合时，不要对解做任何约束，否则求得的残差平方和不不要对解做任何约束，否则求得的残差平方和不不要对解做任何约束，否则求得的残差平方和不不要对解做任何约束，否则求得的残差平方和不是是是是“ “全局最小全局最小全局最小全局最小” ”。
53、5 5）单击）单击）单击）单击“ “求解求解求解求解” ”4.3 4.3 4.3 4.3 非线性回归规划求解法非线性回归规划求解法非线性回归规划求解法非线性回归规划求解法规划求解界面规划求解界面规划求解界面规划求解界面1 1：4.3 4.3 4.3 4.3 非线性回归规划求解法非线性回归规划求解法非线性回归规划求解法非线性回归规划求解法规划求解界面规划求解界面规划求解界面规划求解界面2 2：4.3 4.3 4.3 4.3 非线性回归规划求解法非线性回归规划求解法非线性回归规划求解法非线性回归规划求解法运算结果报告：运算结果报告：运算结果报告：运算结果报告：4.3 4.3 4.3 4.3 非线性
54、回归规划求解法非线性回归规划求解法非线性回归规划求解法非线性回归规划求解法初始值选择的重要性：初始值选择的重要性：初始值选择的重要性：初始值选择的重要性：上例中相互作用参数的初始值选上例中相互作用参数的初始值选上例中相互作用参数的初始值选上例中相互作用参数的初始值选100100，100100，则会给出，则会给出，则会给出，则会给出出错信息：出错信息：出错信息：出错信息：4.4 Excel4.4 Excel4.4 Excel4.4 Excel初步图形处理初步图形处理初步图形处理初步图形处理ExcelExcel提供了提供了提供了提供了1515种类型的二维图形和三维图表，每种类型的二维图形和三维图表
55、，每种类型的二维图形和三维图表，每种类型的二维图形和三维图表，每种类型都有几种不同的变化。种类型都有几种不同的变化。种类型都有几种不同的变化。种类型都有几种不同的变化。化工中最常用的是散点图，它显示数值的化工中最常用的是散点图，它显示数值的化工中最常用的是散点图，它显示数值的化工中最常用的是散点图，它显示数值的x xy y关关关关系，系，系，系，x x是自变量，图象中是横轴，是自变量，图象中是横轴，是自变量，图象中是横轴，是自变量，图象中是横轴，y y是因变量，图是因变量，图是因变量，图是因变量，图象中为纵轴。有时为了同时比较多组数据，可以象中为纵轴。有时为了同时比较多组数据，可以象中为纵轴。
56、有时为了同时比较多组数据，可以象中为纵轴。有时为了同时比较多组数据，可以有多组因变量。有多组因变量。有多组因变量。有多组因变量。4.4.1 XY4.4.1 XY4.4.1 XY4.4.1 XY散点图的绘制散点图的绘制散点图的绘制散点图的绘制XYXY散点图的基本组成：散点图的基本组成：散点图的基本组成：散点图的基本组成：4.4.1 XY4.4.1 XY4.4.1 XY4.4.1 XY散点图的绘制散点图的绘制散点图的绘制散点图的绘制绘制步骤绘制步骤绘制步骤绘制步骤1 14.4.1 XY4.4.1 XY4.4.1 XY4.4.1 XY散点图的绘制散点图的绘制散点图的绘制散点图的绘制绘制步骤绘制步骤绘
57、制步骤绘制步骤2 24.4.1 XY4.4.1 XY4.4.1 XY4.4.1 XY散点图的绘制散点图的绘制散点图的绘制散点图的绘制绘制步骤绘制步骤绘制步骤绘制步骤3 34.4.1 XY4.4.1 XY4.4.1 XY4.4.1 XY散点图的绘制散点图的绘制散点图的绘制散点图的绘制绘制步骤绘制步骤绘制步骤绘制步骤3 34.4.1 XY4.4.1 XY4.4.1 XY4.4.1 XY散点图的绘制散点图的绘制散点图的绘制散点图的绘制绘制步骤绘制步骤绘制步骤绘制步骤3 3绘制步骤绘制步骤绘制步骤绘制步骤4 44.4.2 XY4.4.2 XY4.4.2 XY4.4.2 XY散点图的修改散点图的修改散点
58、图的修改散点图的修改1 1、“ “图表区图表区图表区图表区” ”格式格式格式格式1 1）图案）图案）图案）图案边框、区域边框、区域边框、区域边框、区域均选择均选择均选择均选择“ “无无无无” ”，或者区域选或者区域选或者区域选或者区域选自动同时填自动同时填自动同时填自动同时填充效果选白充效果选白充效果选白充效果选白色。色。色。色。4.4.2 XY4.4.2 XY4.4.2 XY4.4.2 XY散点图的修改散点图的修改散点图的修改散点图的修改1 1、“ “图表区图表区图表区图表区” ”格式格式格式格式2 2）字体）字体）字体）字体去掉去掉去掉去掉“ “自动自动自动自动缩放缩放缩放缩放” ”4.4
59、.2 XY4.4.2 XY4.4.2 XY4.4.2 XY散点图的修改散点图的修改散点图的修改散点图的修改1 1、“ “图表区图表区图表区图表区” ”格式格式格式格式3 3）属性）属性）属性）属性一般不用一般不用一般不用一般不用4.4.2 XY4.4.2 XY4.4.2 XY4.4.2 XY散点图的修改散点图的修改散点图的修改散点图的修改2 2、“ “绘图区绘图区绘图区绘图区” ”格式格式格式格式边框选自动边框选自动边框选自动边框选自动区域选区域选区域选区域选“ “无无无无” ”4.4.2 XY4.4.2 XY4.4.2 XY4.4.2 XY散点图的修改散点图的修改散点图的修改散点图的修改3
60、3、修改、修改、修改、修改“ “图表标题图表标题图表标题图表标题” ”单击选中，再单击进入修改。修改在单击选中，再单击进入修改。修改在单击选中，再单击进入修改。修改在单击选中，再单击进入修改。修改在ExcelExcel工具栏中进工具栏中进工具栏中进工具栏中进行，也可去掉行，也可去掉行，也可去掉行，也可去掉4.4.2 XY4.4.2 XY4.4.2 XY4.4.2 XY散点图的修改散点图的修改散点图的修改散点图的修改4 4、修改、修改、修改、修改“ “图例图例图例图例” ”双击选中，双击选中，双击选中，双击选中，图案里边图案里边图案里边图案里边框和区域框和区域框和区域框和区域均选择无均选择无均选
61、择无均选择无4.4.2 XY4.4.2 XY4.4.2 XY4.4.2 XY散点图的修改散点图的修改散点图的修改散点图的修改4 4、修改、修改、修改、修改“ “图例图例图例图例” ”字体设置字体设置字体设置字体设置4.4.2 XY4.4.2 XY4.4.2 XY4.4.2 XY散点图的修改散点图的修改散点图的修改散点图的修改4 4、修改、修改、修改、修改“ “图例图例图例图例” ”位置设置，位置设置，位置设置，位置设置，一般不在此一般不在此一般不在此一般不在此处设置，单处设置，单处设置，单处设置，单击选中后按击选中后按击选中后按击选中后按住鼠标左键住鼠标左键住鼠标左键住鼠标左键可任意拖拽可任意
62、拖拽可任意拖拽可任意拖拽移动位置。移动位置。移动位置。移动位置。4.4.2 XY4.4.2 XY4.4.2 XY4.4.2 XY散点图的修改散点图的修改散点图的修改散点图的修改5 5、修改、修改、修改、修改“ “数值数值数值数值(X)(X)轴标题轴标题轴标题轴标题” ”和和和和“ “数值数值数值数值(Y)(Y)轴标题轴标题轴标题轴标题” ”单击选中，再单击，在单击选中，再单击，在单击选中，再单击，在单击选中，再单击，在ExcelExcel工具栏、编辑栏修改工具栏、编辑栏修改工具栏、编辑栏修改工具栏、编辑栏修改4.4.2 XY4.4.2 XY4.4.2 XY4.4.2 XY散点图的修改散点图的修
63、改散点图的修改散点图的修改6 6、修改、修改、修改、修改“ “数值数值数值数值(X)(X)轴轴轴轴” ”和和和和“ “数值数值数值数值(Y)(Y)轴轴轴轴” ”双击选中，在对话框内修改。双击选中，在对话框内修改。双击选中，在对话框内修改。双击选中，在对话框内修改。修改图案格式，修改图案格式，修改图案格式，修改图案格式，主要是根据期主要是根据期主要是根据期主要是根据期刊、论文的要刊、论文的要刊、论文的要刊、论文的要求修改刻度线求修改刻度线求修改刻度线求修改刻度线类型。类型。类型。类型。4.4.2 XY4.4.2 XY4.4.2 XY4.4.2 XY散点图的修改散点图的修改散点图的修改散点图的修改
64、6 6、修改、修改、修改、修改“ “数值数值数值数值(X)(X)轴轴轴轴” ”和和和和“ “数值数值数值数值(Y)(Y)轴轴轴轴” ”修改修改修改修改“ “刻度刻度刻度刻度” ”，主，主，主，主要是更改最小值要是更改最小值要是更改最小值要是更改最小值和最大值使图形和最大值使图形和最大值使图形和最大值使图形紧凑一些紧凑一些紧凑一些紧凑一些4.4.2 XY4.4.2 XY4.4.2 XY4.4.2 XY散点图的修改散点图的修改散点图的修改散点图的修改6 6、修改、修改、修改、修改“ “数值数值数值数值(X)(X)轴轴轴轴” ”和和和和“ “数值数值数值数值(Y)(Y)轴轴轴轴” ”字体、数字、字体
65、、数字、字体、数字、字体、数字、对齐均可修改。对齐均可修改。对齐均可修改。对齐均可修改。右图是修改右图是修改右图是修改右图是修改Y Y轴轴轴轴的数字类型。的数字类型。的数字类型。的数字类型。4.4.2 XY4.4.2 XY4.4.2 XY4.4.2 XY散点图的修改散点图的修改散点图的修改散点图的修改7 7、修改曲线、修改曲线、修改曲线、修改曲线双击选中，在对话框内修改双击选中，在对话框内修改双击选中，在对话框内修改双击选中，在对话框内修改修改修改修改修改“ “图案图案图案图案” ”，主，主，主，主要是对线形和数要是对线形和数要是对线形和数要是对线形和数据标记进行修改。据标记进行修改。据标记进行修改。据标记进行修改。
