Excel在實(shí)驗(yàn)數(shù)據(jù)回歸分析中的應(yīng)用.pdf

《Excel在實(shí)驗(yàn)數(shù)據(jù)回歸分析中的應(yīng)用.pdf》由會員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。

1、中國科技論文實(shí)驗(yàn)技術(shù)與管理Vol.22No.5200582統(tǒng)計(jì)源期刊Excel在實(shí)驗(yàn)數(shù)據(jù)回歸分析中的應(yīng)用高曉林(北京理工大學(xué)化工與環(huán)境學(xué)院,北京100081)摘要:介紹了Excel在數(shù)據(jù)回歸中的應(yīng)用方法,重點(diǎn)介紹了非線性模型回歸的方法,無需編程即可準(zhǔn)確快捷地得到計(jì)算結(jié)果。這種方法可用于教學(xué)和工程計(jì)算。關(guān)鍵詞:Excel;回歸;線性;非線性中圖分類號:TP391113文獻(xiàn)標(biāo)識碼:B文章編號:100224956(2005)05200282041前言在化工實(shí)驗(yàn)數(shù)據(jù)處理中,我們經(jīng)常會遇到對實(shí)驗(yàn)數(shù)據(jù)進(jìn)行回歸分析的問題,即通過一定的經(jīng)驗(yàn)關(guān)聯(lián)式來描述變

2、量之間存在的函數(shù)關(guān)系。處理這類問題,通常采用一定的算法[1]優(yōu)化模型參數(shù),使計(jì)算值與觀測值之間的殘差的平方和最小,即常說的最小二乘法。過去要實(shí)現(xiàn)上述過程,需要有一定的算法和編程背景的專業(yè)人員,而且在程序維護(hù)、圖形化等方面都不太方便。隨著專業(yè)計(jì)算機(jī)軟件的發(fā)展,一些無需任何編程即可實(shí)現(xiàn)過去需要成百上千行代碼才能實(shí)現(xiàn)的功能的軟件已經(jīng)應(yīng)運(yùn)而生。作為MicrosoftOffice套件之一的Excel正是這樣一款功能強(qiáng)大的優(yōu)秀軟件。MicrosoftExcel是目前最優(yōu)秀的電子表格軟件之一,它具有卓越的圖表和數(shù)值計(jì)算功能,廣泛地應(yīng)用于財(cái)務(wù)、統(tǒng)計(jì)、決策

3、等領(lǐng)域,同時(shí)它又是一個集成了許多高等數(shù)學(xué)方法、功能強(qiáng)大的數(shù)據(jù)處理工具。本文筆者根據(jù)自身工作經(jīng)驗(yàn)按照線性模型回歸、非線性模型回歸的分類通過幾個具體的實(shí)例介紹了Excel在實(shí)驗(yàn)數(shù)據(jù)回歸分析中的應(yīng)用,其中重點(diǎn)介紹Excel在非線性回歸中的應(yīng)用。本文所用軟件為MicrosoftExcel2000。2數(shù)據(jù)處理2.1線性回歸線性回歸分析是自變量與因變量呈線性關(guān)系,線性模型的一般式為:y=α+∑βiXi(1)如果因變量只有一個,則稱為一元線性回歸,如果有多個因變量則稱為多元線性回歸。對于一元線性回歸,最簡單的辦法是做出數(shù)據(jù)的散點(diǎn)圖,通過添加趨勢線的辦法

4、得到。其具體步驟和實(shí)例,文獻(xiàn)[3]有詳細(xì)的介紹,這里不再重復(fù)。同時(shí)也可以利用Slope收稿日期:2004207209作者簡介:高曉林(1948—),男,工程師1高曉林:Excel在實(shí)驗(yàn)數(shù)據(jù)回歸分析中的應(yīng)用92[2]和Intercept函數(shù)分別得到回歸直線的斜率和截距。對于多元線性回歸,可以利用linest[2]函數(shù),但需要運(yùn)用數(shù)組計(jì)算,而且結(jié)果不直觀,而數(shù)據(jù)分析工具中的回歸分析工具則顯得更具優(yōu)勢。數(shù)據(jù)分析工具由Excel自帶的加載宏提供,需要單獨(dú)安裝。具體的安裝即調(diào)用步驟見文獻(xiàn)[2]。下面以二元混合溶液的粘度與組分濃度之間的二元線性函數(shù)為

5、例,介紹利用數(shù)據(jù)分析工具回歸過程。待回歸二元函數(shù)為:μ=a0+a1CA+a1CB(2)如圖1所示,在A至C列輸入數(shù)據(jù),數(shù)據(jù)取自文獻(xiàn)[6]。選擇“工具”—“加載宏”,在彈出的對話框中選擇“分析工具庫”,然后再次選擇“工具”—“數(shù)據(jù)分析”,在彈出的對話框中選擇“回歸”,則彈出圖1中右邊的對話框,在Y值區(qū)域中選擇＄C＄2:＄C＄16,在X值區(qū)域中＄A＄2:＄B＄16,在輸出選項(xiàng)中選擇結(jié)果輸出的位置,這里選擇新工作表。確定后就可以得到如圖2所示結(jié)果(a0=22714325,a1=0102371,a2=0140953)。從圖2可以看到,回歸工具給出

6、了明確、全面的結(jié)果,可方便地進(jìn)行進(jìn)一步的顯著性檢驗(yàn)和方差分析。圖1數(shù)據(jù)分析工具回歸多元線性模型的數(shù)據(jù)輸入圖2數(shù)據(jù)分析工具回歸多元線性模型的結(jié)果輸出03實(shí)驗(yàn)技術(shù)與管理212非線性線性回歸與線性回歸相對的是非線性回歸,這在實(shí)際化工數(shù)據(jù)處理過程中更常遇到。一部分b形式較為簡單的非線性模型,如ax,1/(ax+b),可以通過簡單的變量變換,變成線性形式利用前面介紹的線性模型回歸的方法處理。文獻(xiàn)[1]對多種可線性化的非線性模型的處理方法進(jìn)行了介紹,對于那些不能線性化的非線性模型則可以利用Excel中的規(guī)劃求解工具來回歸。與數(shù)據(jù)分析工具一樣,規(guī)劃求解

7、工具也是由Excel自帶的加載宏提供,需[2][4]要單獨(dú)安裝。下面以回歸關(guān)聯(lián)苯甲酸在水中的溶解度的λ-h方程為例說明利用規(guī)劃求解工具回歸非線性模型的過程。λ-h方程形式如方程(3),式中X為苯甲酸的摩爾分?jǐn)?shù),溫度的單位為kelvin,Tm為苯甲酸的熔點(diǎn)(39515K),λ-h為待回歸參數(shù)。1-X11ln1+λ=λh-(3)XTTm如圖(2)首先在A,C列輸入實(shí)驗(yàn)數(shù)據(jù),數(shù)據(jù)取自文獻(xiàn)[5],在B列利用對數(shù)公式和填充柄將攝氏溫度變?yōu)榻^對溫度,在C1至C2處填入?yún)?shù)λ和h的初值1和3000。在D列[2]利用公式(3)得到目前參數(shù)下的計(jì)算值,需要

8、注意的是填入公式必須用絕對地址,以D2為例,它的計(jì)算公式是:=＄C＄1/(EXP(＄C＄13＄C＄23(1/B4-1/39515))-1+＄C＄1)。根據(jù)最小二乘的原理,構(gòu)造如下目標(biāo)函數(shù):N1