化工計算機數(shù)據(jù)與圖形處理-2

第四章EXCEL數(shù)據(jù)擬合及圖形處理

主要內(nèi)容4.1線性回歸4.1.1一元線性回歸4.1.2用“數(shù)據(jù)分析”線性擬合4.1.3線性回歸統(tǒng)計4.2多元線性回歸4.3非線性回歸4.4Excel初步圖形處理回歸分析簡介實驗結(jié)果中，各物理量之間的關(guān)系極其重要

歐美學(xué)派：重視理論推導(dǎo)與探索；前蘇聯(lián)學(xué)派：重視實驗數(shù)據(jù)擬合。

通常用y計算＝f(x)近似代表實驗數(shù)據(jù)點（xi

，yi）之間的關(guān)系。為此必須找到與數(shù)據(jù)

（xi，yi）吻合最好的函數(shù)系數(shù)，這一過程稱為曲線擬合。回歸分析簡介

最佳擬合的判據(jù)是實驗數(shù)據(jù)點與擬合曲線的偏差的平方和最?。ㄗ钚《朔ǎ?，即：用最小二乘法求最佳擬合參數(shù)的過程稱為回歸分析?；貧w分析簡介回歸分析是一種統(tǒng)計技術(shù)，用以定量表達實驗變量之間的關(guān)系和相關(guān)程度。

目的之一是根據(jù)已知的體系變量間的函數(shù)關(guān)系（數(shù)學(xué)模型）已知，回歸出系數(shù)；

另一目的是獲得回歸參數(shù)的標準偏差和相關(guān)系數(shù)以確定模型是否適合實驗數(shù)據(jù)。

4.1線性回歸4.1.1一元線性回歸將一組數(shù)據(jù)擬合成一直線最簡單：

y=ax+b在化工實驗中最常見的應(yīng)用是分析產(chǎn)物組成時作標準曲線。示例：在萃取分離乙苯和辛烷時，要用氣相色譜分析產(chǎn)品（乙苯-辛烷混合溶液）中乙苯的含量。首先配制好一系列已知濃度的乙苯-辛烷標準溶液，用色譜逐個分析，得到峰面積數(shù)據(jù)，與已知濃度擬合得到標準曲線。4.1.1一元線性回歸1.用函數(shù)SLOPE()和INTERCEPT()SLOPE(y值數(shù)列，x值數(shù)列），返回線性回歸直線的斜率aINTERCEPT(y值數(shù)列，x值數(shù)列），返回截距b

4.1.1一元線性回歸2.用函數(shù)LINEST()LINEST可處理的線性方程的通式為：

y=ax+b或y=a1x1+a2x2+…amxm+bLINEST的語法為：LINEST(y值數(shù)列,x值數(shù)列,常數(shù)_邏輯,統(tǒng)計_邏輯)常數(shù)_邏輯是一邏輯值，指明是否強制使常數(shù)b為0。如果它是TRUE、1或被省略，回歸參數(shù)包括截距b；如果它是FALSE或0，則擬合不包括b，即得到過原點的直線y=ax。4.1.1一元線性回歸2.用函數(shù)LINEST()統(tǒng)計_邏輯是一邏輯值，指明是否返回附加的回歸統(tǒng)計值。如果它是TRUE或1，LINEST除給出回歸系數(shù)ai和b外，還給出回歸統(tǒng)計數(shù)組。3.LINEST中的線性回歸分析設(shè)有N個數(shù)據(jù)點（xi，yi），總平方和為：3.LINEST中的線性性回歸分分析殘差平方方和SS殘差反映測量量值yi與按擬合合函數(shù)計計算值y計算的偏差，，殘差平平方和越越小，表表明擬合合越好。。回歸平方方和反映映在y的總平方方和中由由x和y的線性關(guān)關(guān)系引起的y的變化，，其數(shù)值值越大越越好。擬合好壞壞取決于于SS回歸在SS中所占比比例，因因此定義義判定系數(shù)數(shù)：3.LINEST中的線性性回歸分分析R2表示回歸歸分析方方程的結(jié)結(jié)果反映映變量間間關(guān)系的的程度的標志，，若R2<0.9，說明實實驗數(shù)據(jù)據(jù)擬合較較差。R稱為相關(guān)關(guān)系數(shù)。。R越大表明明線性關(guān)關(guān)系越好好。R>0為正相關(guān)，，直線的的斜率為為正；R<0為負相關(guān)關(guān)，直線線的斜率為負負。R=0為不相關(guān)關(guān)，x與y無線性關(guān)關(guān)系。3.LINEST中的線性性回歸分分析在實際工工作中也也經(jīng)常用用到F-檢驗：自由度df＝N-k，一元線性回回歸參數(shù)k為2。F-統(tǒng)計用以判定設(shè)計計的關(guān)系式是是否有效。測量精度用y值的標準誤差差SE(y)來估計：SE(y)越小，根據(jù)擬擬合直線預(yù)測測的y值越準確。3.LINEST中的線性回歸歸分析y是隨機的，因因此由實驗數(shù)數(shù)據(jù)得到的回回歸參數(shù)a、b也是隨機的，即即同一實驗做做若干次或不不同人做同一一實驗，每次實驗驗得到的a、b值也不相同。。統(tǒng)計上可以以用a、b的標準偏差來來衡量a、b數(shù)值的波動：：3.LINEST中的線性回歸歸分析SE（a）、SE（b）的波動性與與標準偏差SE（y）的大小有關(guān)，也也與x值的波動性有有關(guān)，x值越離散（也也就是說x取值區(qū)間越大大），SE（a）、SE（b）的值越小。此外，，SE（b）還與測量點點數(shù)N有關(guān)，測量次數(shù)越多，SE（b）越小。3.LINEST中的線性回歸歸分析示例：乙苯－－辛烷標準曲曲線的回歸4.1.2使用“數(shù)據(jù)分分析”線性擬擬合4.1.2使用“數(shù)據(jù)分分析”線性擬擬合Excel““數(shù)據(jù)分析”步步驟：以標準曲線制制作為例1）打開“工具具”－“數(shù)據(jù)據(jù)分析”，出出現(xiàn)“數(shù)據(jù)分分析”對話框框。選其中的的“回歸”，，出現(xiàn)“回歸歸”對話框。。2）在“輸入””區(qū)域，分別別在“Y值輸入?yún)^(qū)域””和“X值輸入?yún)^(qū)域””內(nèi)輸入y數(shù)據(jù)區(qū)域的引引用：$K$2:$K$12和x數(shù)據(jù)區(qū)域引用用$J$2:$J$123)選取“標志”(注意與Y、X輸入?yún)^(qū)域一致致！)和“置信度””復(fù)選框，如如果要強制回回歸直線過原原點，則選中中“常數(shù)為零零”，本例不不選。4.1.2使用“數(shù)據(jù)分分析”線性擬擬合4）在“輸出選項項”區(qū)有3個單選框，用用來指定回歸歸分析數(shù)據(jù)輸輸出的位置。。若選中“新工工作表組”，，則可在當(dāng)前前工作簿中插插入新工作表表，并由新工工作表的A1單元格開始粘粘貼計算結(jié)果果。如果需要要給新工作表表命名，可在在右側(cè)的編輯輯框中鍵入名名稱。若選中“新工工作簿”，則則可創(chuàng)建一個個新工作簿，，并在新工作作簿中的新工工作表中粘貼貼結(jié)果。若選中“輸出出區(qū)域”，則則計算結(jié)果粘粘貼在原工作作表上。本例例選中此項，，要求在右邊邊的編輯框內(nèi)內(nèi)輸入“匯總總輸出表”左左上角單元格格的位置。4.1.2使用“數(shù)據(jù)分分析”線性擬擬合4.1.2使用“數(shù)據(jù)分分析”線性擬擬合5）單擊確定，給給出匯總輸出出表(SUMMARYOUTPUT),其中包括回歸歸統(tǒng)計、方差差分析和回歸歸系數(shù)及統(tǒng)計計4.1.3線性回歸統(tǒng)計計1.自由度df的概念在方差分析表表中有3種自由度：回歸平方和SS回歸的自由度：df回歸＝m＝k-1殘差平方和SS殘差的自由度：df殘差＝N-k總平方和SS的自由由度度：：dfSS＝N-1m：自自變變量量x的系系數(shù)數(shù)數(shù)數(shù)目目，，k：線線性性回回歸歸參參數(shù)數(shù)的的數(shù)數(shù)目目，，N：實實驗驗點點的的數(shù)數(shù)目目。?；貧w歸方方差差MS回歸歸=SS回歸歸/df回歸歸殘差差方方差差MS殘差差=SS殘差差/df殘差差4.1.3線性性回回歸歸統(tǒng)統(tǒng)計計2.回歸歸統(tǒng)統(tǒng)計計的的作作用用回歸歸統(tǒng)統(tǒng)計計可可決決定定回回歸歸系系數(shù)數(shù)的的值值及及其其有有效效性性，，例例如如用用最最小二二乘乘法法擬擬合合的的曲曲線線是是否否有有效效，，回回歸歸參參數(shù)數(shù)的的精精度度及及置置信區(qū)區(qū)間間，，回回歸歸方方程程擬擬合合好好壞壞等等。。殘差差平平方方和和在在一一定定程程度度上上反反映映了了擬擬合合好好壞壞。。然然而而在在檢檢驗回回歸歸線線有有無無意意義義、、因因變變量量y與自自變變量量x間是是否否真真符符合合回歸歸方方程程所所示示的的函函數(shù)數(shù)關(guān)關(guān)系系、、需需要要定定量量反反映映擬擬合合好好壞壞時時，還還要要經(jīng)經(jīng)常常用用到到相相關(guān)關(guān)系系數(shù)數(shù)R和F-統(tǒng)計計。。4.1.3線性性回回歸歸統(tǒng)統(tǒng)計計3相關(guān)關(guān)系系數(shù)數(shù)R和F-統(tǒng)計計從相相關(guān)關(guān)系系數(shù)數(shù)R檢驗驗表表中中查查到到在在某某一一自自由由度度df和指指定定置置信水水平平α下的的相相關(guān)關(guān)系系數(shù)數(shù)臨臨界界值值Rdf,α，若若計計算算得得到到的的相關(guān)關(guān)系系數(shù)數(shù)R大于于Rdf,α，則則x和y顯著著相相關(guān)關(guān)。一一般般R應(yīng)在在0.99以上上。。F-統(tǒng)計計也也有有類類似似的的F檢驗驗表表，，根根據(jù)據(jù)給給定定的的置置信信度度可可以以查得得臨臨界界值值Fα(df回歸歸，df殘差差），，若若計計算算的的F值大大于于F檢驗驗表表臨臨界界值值，，則則x和y顯著著相相關(guān)關(guān)。。4.1.3線性性回回歸歸統(tǒng)統(tǒng)計計4t-檢驗驗t-檢驗驗常常用用于于比較較兩兩條條回歸歸曲曲線線。。同同樣樣，，有有現(xiàn)現(xiàn)成成的的t檢驗驗表表，，表表中中列列出出在在某某一一自自由由度度df和指指定定置置信信水水平平α下的的tdf,α。以以適適當(dāng)當(dāng)?shù)牡姆椒椒ǚㄓ嬘嬎闼鉻值，，與與查查得得的的臨臨界界值進進行行比比較較，，若若計計算算值值大大于于等等于于臨臨界界值值，，則則比比較較的的兩個個量量有有顯顯著著差差別別，，否否則則無無顯顯著著差差別別。。注：上述述檢驗必必須在指指定的置置信度范范圍內(nèi)進進行，最最常用的置置信度為為95％。若置置信度太太高（如如99％），一些有用用的數(shù)據(jù)據(jù)可能被被排除在在外；若若太低，，則一些些無意義的的數(shù)據(jù)將將被作為為有效數(shù)數(shù)據(jù)加以以考慮。。4.2多元線性性回歸LINEST和“數(shù)據(jù)據(jù)分析””的“回回歸”可可以對多多個自變變量xi的函數(shù)y=a1x1+a2x2+…amxm+b進行線性性擬合，，也可以以對x的多項式式進行擬擬合。4.2.1多元線性性回歸示例：烴烴類溶質(zhì)質(zhì)在離子子液體中中無限稀稀釋活度度系數(shù)的的定量結(jié)構(gòu)構(gòu)相關(guān)（（QSPR）模型。。兩種方法法：LINEST()函數(shù)和““數(shù)據(jù)分分析”工工具4.2.1多元線性性回歸室溫離子子液體簡簡介4.2.1多元線性性回歸Linest法：先選中五五行連續(xù)單元格格（列數(shù)由系數(shù)數(shù)的個數(shù)確定）），輸入入＝linest(Y數(shù)列，多個X數(shù)列,1,1)，按Ctrl+Shift+Enter鍵即可。。4.2.1多元線性性回歸計算Y值時注意意代入?yún)?shù)次序序與回歸歸參數(shù)是是相反的的！！4.2.1多元線性性回歸數(shù)據(jù)分析析工具：：工具-數(shù)據(jù)分析析-回歸4.2多元線性性回歸4.2.2多元線性性回歸系系數(shù)的相相關(guān)性對烴類溶溶質(zhì)在離離子液體體中無限限稀釋活活度系數(shù)數(shù)的定量結(jié)構(gòu)構(gòu)相關(guān)（（QSPR）模型，，可由其其t-統(tǒng)計看出出：4.2.2多元線性性回歸系系數(shù)的相相關(guān)性多元回歸歸系數(shù)的的t-Stat均大于1，說明這這些系數(shù)數(shù)預(yù)測無限稀釋釋活度系系數(shù)γ的有效性性。其中中水合能能的t-Stat最大，說說明它在在預(yù)測γ上更有效效。多元線性性擬合的的兩個或或多個自自變量間間可能相相關(guān)，即即一個自變變量是另另外一個個自變量量的線性性函數(shù)。。如果兩兩個自變量量完全相相關(guān)，則則可能在在單元格格中出現(xiàn)現(xiàn)#NUM!符號?！啊皵?shù)據(jù)分分析”中中有“相相關(guān)系數(shù)數(shù)”程序序，用它它可以檢查自變變量的相相關(guān)性。。4.2.2多元線性性回歸系系數(shù)的相相關(guān)性4.2.2多元線性性回歸系系數(shù)的相相關(guān)性由下圖可可見，這這是一個個對稱矩矩陣，對對角元素素為自變變量與自身身的相關(guān)關(guān)系數(shù)，，因此均均為1。非對角角元素是是兩個不同自自變量間間的相關(guān)關(guān)系數(shù)。。如果該該值接近近于1時，說明兩自自變量間間有顯著著相關(guān)性性。4.2.3多項式擬擬合有時實驗驗數(shù)據(jù)表表現(xiàn)為一一曲線，，相應(yīng)的的擬合函函數(shù)未知知，需要一一種普適適的函數(shù)數(shù)擬合曲曲線。常常用方法法之一就就是用多項項式擬合合。原則則上任何何連續(xù)函函數(shù)均可可用多項項式展開：：若將變量量進行變變換：則多項項式化化為多多元一一次函函數(shù)：：4.2.3多項式式擬合合可用LINEST和“回回歸””求多多項式式的參參數(shù)b、a1、a2…an及其回回歸統(tǒng)統(tǒng)計。。通常到到三次次方就就有中中等精精度。。在實實際工工作中中，在在滿足擬合合精度度的前前提下下多項項式的的階數(shù)數(shù)要盡盡可能能的低低。對于N個數(shù)據(jù)據(jù)點，，用于于擬合合的多多項式式最高高階數(shù)數(shù)為N-14.2.3多項式式擬合合示例：：1-己烯的的分壓壓P與其在在離子子液體體[BMIM][PF6]中的溶溶解度度x間為非非直線線關(guān)系系，試試用一一多項項式經(jīng)經(jīng)驗公式表表示P～x的函數(shù)數(shù)關(guān)系系。4.3非線性性回歸歸－規(guī)規(guī)劃求求解法法1.操作過過程1）在工工作表表上端端適當(dāng)當(dāng)單元元格內(nèi)內(nèi)輸入入待定定系數(shù)數(shù)的初初始值（可可變單單元格格）。。2）輸入入需要要擬合合的實實驗數(shù)數(shù)據(jù)：：自變變量x數(shù)列和和因變變量y實驗值值數(shù)列。。3）添加加y計算值值數(shù)據(jù)列列。它它們是是用擬擬合函函數(shù)計計算所所得的數(shù)值值，公公式中中含x值和一一個或或數(shù)個個待定定系數(shù)數(shù)。4）另一一列填填入每每一數(shù)數(shù)據(jù)點點的殘殘差平平方(y實驗值值-y計算值值)25）選定定一單單元格格，在在此計計算殘殘差平平方和和(目標單單元格格)6）用““規(guī)劃劃求解解”改改變擬擬合函函數(shù)的的待定定系數(shù)數(shù)（可可變單單元格），，使得得殘差差平方方和的的值極極小。。4.3非線性性回歸歸－規(guī)規(guī)劃求求解法法注意：：1）“規(guī)規(guī)劃求求解””是一一搜索索程序序，若若設(shè)置置的初初始值值接近近最終值，，則它它能以以最快快和最最有效效的方方式找找到解解。反反之，，若設(shè)置置的初初始值值偏離離最終終目標標值太太遠，，“規(guī)規(guī)劃求求解””可能得得不不到到方方程程的的解解。。2）為保證“規(guī)規(guī)劃求解”得得到的是全局局性解而不是是區(qū)域性解，最好用幾幾套不同的初初始值求解。。3）“規(guī)劃求解解”得到的最最小二乘法擬擬合系數(shù)可能能會因起始值不同而略略有差別。4.3非線性回歸－－規(guī)劃求解法法示例：1-己烯在離子液液體中活度系系數(shù)的Wilson模型關(guān)聯(lián):實驗測得283.15K時不同x己烯下己烯在離子子液體中的活活度系數(shù)值γ己烯，試用Wilson模型關(guān)聯(lián)實驗驗結(jié)果，得出兩個相互作作用參數(shù)。4.3非線性回歸－－規(guī)劃求解法法規(guī)劃求解過程程：1）在A、B兩列列出x己烯和γ己烯的實驗結(jié)果，并給出兩個相互作用用參數(shù)Λ12和Λ21的初始值。2）在C列根據(jù)Wilson模型得出γ己烯的計算值:=-LN(A4+$D$1*(1-A4))+(1-A4)*($D$1/(A4+$D$1*(1-A4))-$D$2/(1-A4+$D$2*A4))。根據(jù)殘差平方方的定義，在在D列得到殘差平平方：=(B4-C4)^2，并在D26單元格求出殘殘差平方和：：=SUM(D4:D25)。4.3非線性回歸－－規(guī)劃求解法法規(guī)劃求解過程程：3）打開“工具具”菜單，選選“規(guī)劃求解解”，出現(xiàn)規(guī)規(guī)劃求解對話框?！霸O(shè)置置目標單元格格”選殘差平平方和單元格格位置“$D$26。根據(jù)最小二二乘法，殘差差平方和應(yīng)為為極小，因此在”等于““的三個單選選框中選“最最小”?！翱煽勺儐卧瘛薄边x相互作用用參數(shù)數(shù)值所所在單元格：：$D$1:$D$2。4）不要填“約束束”欄。用最最小二乘法進進行數(shù)據(jù)擬合合時，不要對對解做任何約約束，否則求求得的殘差平平方和不是““全局最小””。5）單擊“求解解”4.3非線性回歸－－規(guī)劃求解法法規(guī)劃求解界面面1：4.3非線性回歸－－規(guī)劃求解法法規(guī)劃求解界面面2：4.3非線性回歸－－規(guī)劃求解法法運算結(jié)果報告告：4.3非線性回歸－－規(guī)劃求解法法初始值選擇的的重要性：上例中相互作作用參數(shù)的初初始值選100，100，則會給出出錯信息：4.4Excel初步圖形處理理Excel提供了15種類型的二維維圖形和三維維圖表，每種類型都有幾幾種不同的變變化。化工中最常用用的是散點圖圖，它顯示數(shù)數(shù)值的x～y關(guān)系，x是自變量，圖圖象中是橫軸軸，y是因變量，圖圖象中為縱軸。。有時為了同同時比較多組組數(shù)據(jù)，可以以有多組因變量量。4.4.1XY散點圖的繪制制XY散點圖的基本本組成：4.4.1XY散點圖的繪制制繪制步驟14.4.1XY散點圖的繪制制繪制步驟24.4.1XY散點圖的繪制制繪制步驟34.4.1XY散點圖的繪制制繪制步驟34.4.1XY散點圖的繪制制繪制步驟3繪制步驟44.4.2XY散點圖的修改改1、“圖表區(qū)””格式1）圖案