熊澤本

(荊楚理工學(xué)院,湖北荊門 448000)

實(shí)驗(yàn)數(shù)據(jù)處理的excel函數(shù)解法研究

熊澤本

(荊楚理工學(xué)院,湖北荊門 448000)

回歸分析是一類數(shù)學(xué)模型,其研究對(duì)象是一個(gè)隨機(jī)變量同一個(gè)或多個(gè)非隨機(jī)變量的關(guān)系問題,在提出一元和多元線性回歸的基礎(chǔ)上,通過比較最小二乘法解法和 Excel函數(shù)解法,指出 Excel函數(shù)解法可以簡(jiǎn)化復(fù)雜的數(shù)學(xué)運(yùn)算,并配以圖表,給求解過程帶來方便。

回歸分析;最小二乘原理;Excel函數(shù)解法

在物理、化學(xué)實(shí)驗(yàn)中,很多實(shí)驗(yàn)數(shù)據(jù)的處理都要求研究自變量和因變量之間的關(guān)系,確定由該兩個(gè)變量所決定的曲線和最佳擬合參數(shù)。由一個(gè)或一組隨機(jī)變量來估計(jì)或預(yù)測(cè)另一個(gè)隨機(jī)變量的期望值時(shí)建立的模型及所作的統(tǒng)計(jì)分析,這類問題的研究稱作回歸分析[1]。當(dāng)只有一個(gè)自變量和一個(gè)因變量,且它們是線性關(guān)系時(shí),稱為一元線性回歸:當(dāng)自變量個(gè)數(shù)大于1,且因變量與自變量仍為線性關(guān)系時(shí),稱為多元線性回歸。

1 線性回歸模型及矩陣解法

設(shè)因變量y和自變量x1,x2,…,xk滿足以下線性關(guān)系:

y和x1,x2,…,xk作 n次試驗(yàn),所得數(shù)據(jù)為(yi,xi1,xi2,…,xik)(i=1,2,…n)他們滿足以下線性關(guān)系:

寫成矩陣形式即:

其中

上式中,a0,a1,…,ak是回歸系數(shù),ε1,ε2,…,εn是n個(gè)相互獨(dú)立且服從同一正態(tài)分布n(0,σ)的隨機(jī)變量。

為了總結(jié)回歸分析的計(jì)算結(jié)果,也為了檢驗(yàn)回歸分析的可信程度,通常的做法是列出它的回歸分析表,如表1所示。

表1 方差分析表

2 Excel函數(shù)解法

上述回歸分析過程若用人工計(jì)算則過程繁瑣且計(jì)算量巨大,若借用計(jì)算機(jī)和相關(guān)軟件可使求解過程大為簡(jiǎn)化。Excel是office系列辦公軟件的一個(gè)組件,它提供了大量的函數(shù),運(yùn)用這些函數(shù)可以進(jìn)行統(tǒng)計(jì)管理、線性分析等工作,并且可以利用它強(qiáng)大而豐富的的圖表功能清晰顯示變量之間的關(guān)系。(1)式中若自變量只有一個(gè),即為一元線性回歸模型,對(duì)于一元線性回歸問題可用文獻(xiàn)[3]中的 Excel統(tǒng)計(jì)函數(shù) FORECAST、SLOPE、INTERCEPT,這三個(gè)函數(shù)就能夠簡(jiǎn)單地完成[3]。但是文獻(xiàn)[3]解法仍顯繁瑣,必須調(diào)用 SLOPE、INTERCEPT兩個(gè)函數(shù)才能分別求出表示為Y=a+bx的回歸直線的斜率b和截距a,而且上述兩函數(shù)只能求解一元回歸而不適用于多元回歸。實(shí)際上對(duì)一元或多元回歸問題均可用Excel的統(tǒng)計(jì)函數(shù)LINEST求解。

2.1 回歸分析的函數(shù)解法

LINEST函數(shù)是使用最小二乘法對(duì)已知數(shù)據(jù)進(jìn)行最佳直線擬合,并返回描述此直線的數(shù)組。因?yàn)榇撕瘮?shù)返回?cái)?shù)值數(shù)組,所以必須以數(shù)組公式的形式輸入。

設(shè)回歸方程為:或 y=a0+a1x1+a2x2+…+akxk(多元回歸)式中若 y、x和a是向量。則LINEST函數(shù)返回的數(shù)組為{ak,ak-1,…,a1,a0}。LINEST函數(shù)還可返回附加回歸統(tǒng)計(jì)值。

函數(shù)的語法

數(shù)組known-x’s可以包含一組或多組變量。如果只用到一個(gè)變量,只要known-y’s和knownx’s維數(shù)相同,它們可以是任何形狀的區(qū)域。如果用到多個(gè)變量,則 known-y’s必須為向量(即必須為一行或一列)。

Const 為一邏輯值,用于指定是否將常量a0強(qiáng)制設(shè)為0。

如果const為 TRUE或省略,a0將按正常計(jì)算。

如果const為 FALSE,a0將被設(shè)為0。

Stats 為一邏輯值,指定是否返回附加回歸統(tǒng)計(jì)值。

如果stats為 TRUE,則LINEST函數(shù)返回附加回歸統(tǒng)計(jì)值,這時(shí)返回的數(shù)組列表如表二所示;如果 stats為 FALSE或省略,LINEST函數(shù)只返回斜率a和截距a0。

表2 LINEST函數(shù)返回附加回歸統(tǒng)計(jì)值

2.2 應(yīng)用實(shí)例

為了測(cè)試某種鋼板的屈服強(qiáng)度σs(MPa)與厚度x1(mm)、含碳量百分比x2、含鉻量百分比x3的關(guān)系,對(duì)20塊鋼板進(jìn)行測(cè)試,測(cè)試數(shù)據(jù)如表3所示:求σs對(duì)x1、x2、x3的多元回歸函數(shù)。

表3 試驗(yàn)數(shù)據(jù)表

2.2.1 求解步驟

(1)啟動(dòng) Excel,錄入表三所示數(shù)據(jù)。(2)在Excel中選擇單元格 H1,選擇“插入”菜單中的“插入函數(shù)”選項(xiàng),在彈出的“插入函數(shù)”對(duì)話框中選擇“常用函數(shù)”類型中的“LINEST”函數(shù)。如圖1所示:單擊“確定”按鈕,會(huì)彈出“函數(shù)參數(shù)”對(duì)話框,如圖2所示。(3)在“函數(shù)參數(shù)”對(duì)話框中 Y值輸入?yún)^(qū)域“Known_y’s”中輸入因變量 Y的樣本數(shù)據(jù)區(qū)域,在X值輸入?yún)^(qū)域“Known-x’s”中輸入因變量X的樣本數(shù)據(jù)區(qū)域。Const和Stats兩個(gè)邏輯值均設(shè)為 TRUE。(4)注意公式必須以數(shù)組公式的形式輸入。在將公式輸入到單元格 H1后,選擇以公式單元格開始的區(qū)域 H1∶K5。按F2,再按Ctrl+Shift+Enter。如果公式不是以數(shù)組公式輸入,則返回單個(gè)結(jié)果值467.0440533。當(dāng)作為數(shù)組輸入時(shí),將返回以下回歸統(tǒng)計(jì)值的分析結(jié)果,見表4。

圖1 “插入函數(shù)”對(duì)話框

圖2 “插入函數(shù)”對(duì)話框

表4 LINEST函數(shù)返回附加回歸統(tǒng)計(jì)值表

2.2.2 回歸結(jié)果分析

由表四輸出結(jié)果可得如下結(jié)論:⑴多元線性回歸方程的參數(shù)a3、a2、a1、a0分別為:490.5930346、-168.96223、1.792472、87.2062故所得回歸方程為:

(2)回歸方程的判定系數(shù)R2的值為0.666618,F統(tǒng)計(jì)值為10.66435,因LINEST函數(shù)系統(tǒng)默認(rèn)顯著水平為α=0.05,經(jīng)查表F0.95(3,16)=3.24,很顯然有F＞F0.95,則可認(rèn)為該方程的可信度是95%。(3)由于第一自變量的回歸系數(shù)絕對(duì)值遠(yuǎn)小于第二、第三自變量的回歸系數(shù)的絕對(duì)值,由此可知:第一變量對(duì)Y值影響不大,而第二、第三變量對(duì)Y值影響非常顯著,即:鋼板的屈服強(qiáng)度主要取決于其含碳量和含鉻量,而與其厚度關(guān)系不大[4]。所得分析結(jié)果符合試驗(yàn)客觀實(shí)際。

3 結(jié) 論

從以上分析過程可以看出:利用 Excel的LINEST函數(shù)的回歸分析功能可以簡(jiǎn)化大量的數(shù)據(jù)運(yùn)算和誤差分析過程,又可以利用 Excel強(qiáng)大的圖表功能描繪物理曲線。同時(shí)有利于培養(yǎng)學(xué)生的計(jì)算機(jī)運(yùn)用能力,提高實(shí)驗(yàn)速度,應(yīng)該在實(shí)驗(yàn)中推廣。

[1]中山大學(xué)數(shù)學(xué)系.概率論與數(shù)理統(tǒng)計(jì)[M].2版.北京:高等教育出版社,1988.187-188,237-238.

[2]王文健,許荔,錢海挺,等.試驗(yàn)數(shù)據(jù)分析處理與軟件應(yīng)用[M].北京:電子工業(yè)出版社,2008:64-69.

[3]郭志軍.Excel在線性回歸分析中的應(yīng)用研究[J].張家口職業(yè)技術(shù)學(xué)院學(xué)報(bào),2009,24(3):52-56.

[4]劉天琦,支敏學(xué),朱杰遠(yuǎn),等.雙真空熔煉(VIM+VAR)30CrMnSiNi2A鋼屈服強(qiáng)度的影響因素分析[J].材料工程,2003(5):11-14.

The Applied Research of Excel Functions in Experimental Data Processing

XIONG Ze-ben

(Jingchu College of Science and Technology,Hubei,Jingmen 448000)

Regression analysis is a process of constructing mathematical model which focuses on the relation between stochastic variable and non-stochastic variable.Based on the analysis of linearity return problem,this article works out the solution tOit-least squares method and the Excel solution law.With the comparison of these twOmethods,it can be found that Excel,with the aid of graphs,can clearly display data relations when solving return problems,sOthat office eficiency will be achieved greatly.

linearity regression analysis;least squares method;Excel function

04-39

A

1007-2934(2010)06-0086-04

2010-09-14