基于Excel的多元線性回歸優(yōu)化模型預(yù)測(cè)地表水高錳酸鹽指數(shù)

孫曉蕾

（遼寧省鞍山水文局，遼寧鞍山114039）

基于Excel的多元線性回歸優(yōu)化模型預(yù)測(cè)地表水高錳酸鹽指數(shù)

孫曉蕾

（遼寧省鞍山水文局，遼寧鞍山114039）

本文借助Excel軟件對(duì)小姐廟監(jiān)測(cè)斷面的SS、BOD5、CODCr和CODMn監(jiān)測(cè)數(shù)據(jù)進(jìn)行線性回歸全種類組合擬合，并采用校正R2法優(yōu)化選擇，最終形成一個(gè)三元線性回歸優(yōu)化模型，經(jīng)統(tǒng)計(jì)檢驗(yàn)可良好表達(dá)CODMn與其它三項(xiàng)的線性關(guān)系。將實(shí)際監(jiān)測(cè)數(shù)據(jù)代入模型計(jì)算得出的CODMn數(shù)值與實(shí)際監(jiān)測(cè)值之間的平均相對(duì)誤差為7.35%，在可接受范圍內(nèi)，因此，該方程可作為輔助手段應(yīng)用于實(shí)際監(jiān)測(cè)工作中。

Excel；多元線性回歸；優(yōu)化模型；預(yù)測(cè)；高錳酸鹽指數(shù)；地表水

在物理、化學(xué)實(shí)驗(yàn)或?qū)嶋H經(jīng)濟(jì)問(wèn)題中，很多情況都要求研究自變量和因變量之間存在的關(guān)系，確定由這2個(gè)變量所決定的曲線以及最佳擬合參數(shù)。由一個(gè)或一組隨機(jī)變量來(lái)預(yù)測(cè)另一個(gè)隨機(jī)變量時(shí)建立的模型及所作的統(tǒng)計(jì)分析，統(tǒng)稱作回歸分析[1]。當(dāng)只有一個(gè)自變量和一個(gè)因變量，且它們是線性關(guān)系時(shí)，稱為一元線性回歸；當(dāng)自變量個(gè)數(shù)大于1，且因變量與自變量之間仍為線性關(guān)系時(shí)，稱為多元線性回歸。也就是說(shuō)，多元線性回歸預(yù)測(cè)是利用歷史資料建立多元線性回歸模型，研究某一因變量與兩個(gè)或兩個(gè)以上自變量之間相互關(guān)系的理論和方法。在實(shí)際生產(chǎn)生活中，常常是多個(gè)因素共同影響而產(chǎn)生相應(yīng)現(xiàn)象，因此，由多個(gè)自變量篩選優(yōu)化后建立的多元線性回歸模型，比單一自變量建立的一元線性回歸模型更符合實(shí)際，更能準(zhǔn)確地預(yù)測(cè)因變量的變化，可廣泛應(yīng)用于各領(lǐng)域。

多元線性回歸模型一般采用計(jì)算機(jī)編程或?qū)I(yè)計(jì)算軟件來(lái)進(jìn)行數(shù)據(jù)處理與計(jì)算，處理繁瑣且相關(guān)人員需要編程或軟件運(yùn)用能力。Microsoft Office Excel是一個(gè)普遍使用的辦公軟件，具有強(qiáng)大的數(shù)據(jù)處理與分析能力，操作界面簡(jiǎn)潔、使用方法簡(jiǎn)單、涵蓋函數(shù)廣泛、數(shù)據(jù)處理精確，可快捷、簡(jiǎn)便地實(shí)現(xiàn)多元線性回歸模型的計(jì)算與分析[2]。

1 多元線性回歸模型

1.1 矩陣解法原理

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

寫(xiě)成矩陣形式即：

其中，ε1，ε2，…，εn是n個(gè)相互獨(dú)立且服從同一正態(tài)分布n（0，σ）的隨機(jī)變量。殘差平方和為

為尋找使Q最小的a0，a1，a2，…，ak，則Q分別對(duì)a0，a1，a2，…，ak求偏導(dǎo)數(shù)，并滿足下列方程組：

整理后用矩陣形式表示為：XT=（y-XA）=0

則回歸參數(shù)最小二乘解

所得回歸方程為：

1.2 最優(yōu)模型選擇

1.2.1 原理

1.2.2 方法選擇

自變量?jī)?yōu)化選擇一般采用最優(yōu)子集法，即對(duì)全部自變量進(jìn)行各種不同組合所建立的回歸模型進(jìn)行比較，從中選出一個(gè)“最優(yōu)”的回歸模型，則這個(gè)回歸模型可良好地表達(dá)y與xi之間的關(guān)系，使其預(yù)測(cè)的結(jié)果最為準(zhǔn)確。挑選最優(yōu)回歸模型的方法一般有R2法，校正R2法，Cp統(tǒng)計(jì)量法，AIC、BIC及AICC信息量準(zhǔn)則等等[3]。本文采用校正R2法進(jìn)行最優(yōu)子集的選擇。

R2是回歸平方和與總平方和的比值，反應(yīng)了回歸方程對(duì)y的解釋能力，該值越接近1，模型的擬合程度越高。但自變量個(gè)數(shù)的增加會(huì)引起殘差平方和減小、R2增大，因此，盡管某些自變量與y線性關(guān)系不顯著，將其引入模型后，卻也會(huì)使R2增大。為了避免自變量個(gè)數(shù)對(duì)R2的影響，讓R2單純反應(yīng)回歸模型的擬合程度，則引入了調(diào)整判定系數(shù)R′2。

其中：k——自變量個(gè)數(shù)；n——樣本個(gè)數(shù)。

一般當(dāng)k∶n大于1∶5時(shí)，R2會(huì)高估實(shí)際的擬合優(yōu)度，這時(shí)，宜采用R′2來(lái)說(shuō)明方程的擬合優(yōu)度，也就是自變量對(duì)y的解釋能力。

2 應(yīng)用

2.1 原始數(shù)據(jù)匯總及參數(shù)設(shè)定

鞍山市海城市牛莊鎮(zhèn)的小姐廟監(jiān)測(cè)斷面，從2015年5月至2016年4月的懸浮物（SS）、生化需氧量（BOD5）、化學(xué)耗氧量（CODCr）和高錳酸鹽指數(shù)（CODMn）監(jiān)測(cè)數(shù)據(jù)見(jiàn)表1。設(shè)自變量SS為X1、BOD5為X2、CODCr為X3，因變量CODMn為Y，以X1，X2，X3與Y的變化規(guī)律分別建立線性回歸模型并進(jìn)行各種檢驗(yàn)，進(jìn)而選擇最優(yōu)的回歸模型作為小姐廟監(jiān)測(cè)斷面的高錳酸鹽指數(shù)預(yù)測(cè)模型。

表1 小姐廟監(jiān)測(cè)斷面各項(xiàng)監(jiān)測(cè)數(shù)據(jù)

2.2 各自變量與因變量之間的線性相關(guān)關(guān)系

2.2.1 線性擬合步驟

線性擬合工具采用Microsoft Office Excel 2016專業(yè)版，擬合步驟：

1）新建Excel表格，并輸入原始數(shù)據(jù)（表1）；

2）在“插入”中“圖表”，點(diǎn)擊“散點(diǎn)圖（X，Y）”，選擇橫豎坐標(biāo)數(shù)據(jù)，生成數(shù)據(jù)散點(diǎn)圖；

3）點(diǎn)擊選擇散點(diǎn)圖上的數(shù)據(jù)坐標(biāo)點(diǎn)，鼠標(biāo)點(diǎn)擊右鍵“添加趨勢(shì)線”，選擇“線性”、“顯示公式”、“顯示R平方值”；

4）右鍵點(diǎn)擊工具欄“數(shù)據(jù)”標(biāo)簽，選擇“自定義快速選擇工具欄”，點(diǎn)擊“加載項(xiàng)”并“轉(zhuǎn)到”，然后選擇“分析數(shù)據(jù)庫(kù)”后“確定”，則啟動(dòng)“數(shù)據(jù)”標(biāo)簽的“數(shù)據(jù)分析”宏模塊；

5）點(diǎn)擊“數(shù)據(jù)分析”，選擇橫縱坐標(biāo)的數(shù)值區(qū)域X，Y，并點(diǎn)擊選擇需要計(jì)算呈現(xiàn)的數(shù)值，并“確定”，則在新表格中生成線性回歸分析報(bào)告。

2.2.2 線性擬合

分別對(duì)X1與Y，X2與Y，X3與Y，X1、X2與Y，X1、X3與Y，X2、X3與Y，X1、X2、X3與Y進(jìn)行線性擬合并生成線性回歸分析報(bào)告，如圖1，2所示。

2.2.3 回歸模型優(yōu)化選擇

根據(jù)線性回歸分析報(bào)告結(jié)果匯總表（表2）所示，可知調(diào)整后R2最大的為自變量選擇X1，X2，X3擬合生成的線性回歸模型。則根據(jù)圖2可得小姐廟監(jiān)測(cè)斷面優(yōu)化后的三元線性回歸模型為：

圖1 X3與Y線性關(guān)系

表2 線性回歸分析報(bào)告結(jié)果匯總

2.2.4 三元回歸模型統(tǒng)計(jì)檢驗(yàn)

1）F檢驗(yàn)

F檢驗(yàn)是確定回歸模型自變量與因變量之間是否存在顯著線性關(guān)系的評(píng)定指標(biāo)。根據(jù)置信度查詢F分布表獲得檢驗(yàn)顯著線性的臨界值Fa，如果F＞Fa則回歸線性模型假定有效，自變量與因變量有線性關(guān)系，也就是說(shuō)回歸線性模型中的所有自變量足夠解釋因變量的變化趨勢(shì)；但如果F＜Fa則反之，回歸線性模型顯著不成立[4]。

圖2 X1，X2，X3與Y線性回歸分析報(bào)告

2）t檢驗(yàn)

t檢驗(yàn)是t統(tǒng)計(jì)量對(duì)回歸模型的每個(gè)系數(shù)進(jìn)行逐一檢驗(yàn)，通過(guò)查t分布表得到臨界值ta，若t≥ta，回歸系數(shù)bi有顯著意義，則自變量xi應(yīng)保留在回歸模型內(nèi)；若t＜ta，應(yīng)去掉自變量xi，并重新建立回歸模型。

2.2.5 數(shù)值預(yù)測(cè)

將表1中2016年5月份至10月份的數(shù)據(jù)X1，X2，X3代入三元線性回歸模型，預(yù)測(cè)得出Y值，并與實(shí)測(cè)值Y0計(jì)算相對(duì)誤差，見(jiàn)表3。

3 結(jié)論

1）本文根據(jù)多元線性回歸模型基本原理對(duì)小姐廟斷面的SS，BOD5，CODCr和CODMn進(jìn)行全種類情況線性擬合，采用校正R2法進(jìn)行回歸模型的優(yōu)化選擇，形成擬合度較高的三元線性回歸方程，格管控砂石資源亂用行為，制定合理的采砂規(guī)劃，逐漸恢復(fù)河底自然形態(tài)，保障了洪峰平穩(wěn)過(guò)渡，還有利于恢復(fù)水沙平衡。

4）推廣了水生態(tài)環(huán)境保護(hù)的理念。警示牌和圍欄的設(shè)置，對(duì)沿岸影響水生態(tài)環(huán)境的生活和生產(chǎn)行為起到警示和警告的作用，同時(shí)也宣傳了水生態(tài)環(huán)境保護(hù)的理念，將生態(tài)文明意識(shí)全社會(huì)推廣。

遼寧省水生態(tài)修復(fù)已經(jīng)取得了一定的成效，但距離水生態(tài)環(huán)境健康還有較大差距。因此，在今后的河道治理工作中還需要有兩方面的側(cè)重：

1）通過(guò)加強(qiáng)管理和維護(hù)，鞏固已經(jīng)取得的成果。加強(qiáng)河道監(jiān)管體系的建設(shè)，建立健全河道管理組織機(jī)構(gòu)，逐步完善各項(xiàng)管理制度，實(shí)施有效的管理手段，逐步實(shí)現(xiàn)恢復(fù)河道原貌和生態(tài)，完善河道各項(xiàng)許可程序，規(guī)范河道開(kāi)發(fā)秩序，強(qiáng)化監(jiān)管，嚴(yán)禁各種破壞河道生態(tài)環(huán)境的行為，實(shí)現(xiàn)河道生態(tài)的持續(xù)健康發(fā)展，強(qiáng)化日常管理，確保河道行洪順暢，恢復(fù)河道生態(tài)環(huán)境，維護(hù)河流健康。

2）繼續(xù)深入研究遼寧省水生態(tài)環(huán)境影響主因，完善水生態(tài)修復(fù)理論和方法研究，加強(qiáng)水生態(tài)修復(fù)措施實(shí)施效果。深入剖析影響遼寧省河流健康的主要因素，并以河流自然修復(fù)理論為基礎(chǔ)，平衡河流的自然屬性和社會(huì)、經(jīng)濟(jì)發(fā)展需求的關(guān)系，完善我省河流水生態(tài)修復(fù)理論和治理方法，力爭(zhēng)最大限度地恢復(fù)河流的生態(tài)功能，提出科學(xué)合理的全方位多行業(yè)聯(lián)動(dòng)的水生態(tài)修復(fù)治理措施。

［1］朱黨生，張建永，李揚(yáng)，等.水生態(tài)保護(hù)與修復(fù)規(guī)劃關(guān)鍵技術(shù)［J］.水資源保護(hù)，2011,27（5）：59—64.

［2］李明傳.水環(huán)境生態(tài)修復(fù)國(guó)內(nèi)外研究進(jìn)展［J］.前沿，2007（11）：25—27.

X522

A

1002－0624（2017）04－0012－03

2016-05-25