Excel在GM(1，1)沉降預(yù)測數(shù)據(jù)處理中的應(yīng)用

黃維騰

(茂名市粵能電力股份有限公司設(shè)計(jì)院，廣東茂名 525000)

Excel在GM(1，1)沉降預(yù)測數(shù)據(jù)處理中的應(yīng)用

黃維騰?

(茂名市粵能電力股份有限公司設(shè)計(jì)院，廣東茂名 525000)

GM(1，1)模型在沉降預(yù)測中已經(jīng)廣泛應(yīng)用，然而其計(jì)算過程復(fù)雜，在數(shù)據(jù)量較多的情況下計(jì)算量很大。本文以某沉降監(jiān)測數(shù)據(jù)為例，提出利用Excel進(jìn)行數(shù)據(jù)處理的方法，過程簡單，結(jié)果準(zhǔn)確，極大方便了計(jì)算工作。

Excel；GM(1，1)；沉降預(yù)測；數(shù)據(jù)處理

1 前 言

隨著現(xiàn)代化進(jìn)程的不斷加快，城市各類高層建筑日益增多。由于建筑物的增高，荷載的增加，在地基基礎(chǔ)和上部結(jié)構(gòu)的共同作用下，建筑物可能發(fā)生不均勻沉降，輕者將使建筑物產(chǎn)生傾斜或裂縫，影響正常使用，重者將危及建筑物的安全，因此，必須對(duì)其進(jìn)行沉降觀測。

高層建筑沉降監(jiān)測的目的不僅僅是觀測其沉降值，更為重要的是根據(jù)已觀測的量值，通過建立一定的模型來預(yù)測其在未來某一時(shí)刻的可能沉降值，進(jìn)而分析其安全性，將可能的損失消除在萌芽狀態(tài)或最大限度地減輕損失。沉降預(yù)測有回歸分析法、確定函數(shù)法、時(shí)序分析法等，但這些方法通常要求有大量樣本，而且要求具有典型的概率分布，這在實(shí)際工作中往往難以滿足。沉降預(yù)測中既包含已知信息，又有未知信息而且處于變化之中，這樣預(yù)測沉降量未來變化的問題，實(shí)質(zhì)上可以看作一個(gè)灰色問題[1，2]。

2 GM(1，1)預(yù)測模型簡介

GM(1，1)模型是灰色理論中最常用的模型之一。它的建模是對(duì)生成數(shù)列的建模，它對(duì)原始數(shù)據(jù)沒有大樣本的要求，只要原始數(shù)列有4個(gè)以上的數(shù)據(jù)就可以通過變換來建立模型。目前GM(1，1)模型在建筑物沉降預(yù)測中有著廣泛的應(yīng)用，并取得了良好的效果[3，4]。

GM(1，1)計(jì)算過程如下:

(2)利用此序列生成緊鄰均值序列z(1)(k)為

(3)建立灰色GM(1，1)模型的一級(jí)白化微分方程

(4)灰色GM(1，1)模型參數(shù)列A＝[a，b]T的最小二乘估計(jì)為

將計(jì)算求得的參數(shù)a，b帶入式(1)中求微分方程，取x(1)(0)＝x(0)(0)，可得到灰色GM(1，1)預(yù)測模型為:

(5)對(duì)此式再做一階累減還原計(jì)算得到原始序列x(0)的灰色GM(1，1)預(yù)測模型為:

3 基于Excel的GM(1，1)數(shù)據(jù)處理[5]

3.1 數(shù)據(jù)準(zhǔn)備

某高層建筑沉降觀測要求每月監(jiān)測一次，共計(jì)觀測16次，取得了豐富的數(shù)據(jù)。為了便于計(jì)算，選擇一個(gè)有代表性的沉降觀測點(diǎn)說明，數(shù)據(jù)如圖1所示。

在數(shù)據(jù)錄入后，在C2單元格中輸入公式“＝B2”；在C3單元格中輸入公式“＝B3＋C2”，回車。將光標(biāo)指向C3單元格的右下角，待其變成細(xì)小的黑十字后雙擊或下拉至C17，即可得到全部累加生成結(jié)果，如圖1所示。

在D3單元格中輸入計(jì)算公式“＝－AVERAGE (C2:C3)”，回車，將光標(biāo)指向D3單元格的右下角，待其變成細(xì)黑十字后雙擊或者下拉至D17，即可得到全部移動(dòng)平均的負(fù)值，如圖1所示。

在E3－E17添加一列數(shù)字1，這些1與移動(dòng)平均的負(fù)值并排，構(gòu)成用于估計(jì)參數(shù)的矩陣B。然后將原始數(shù)據(jù)從B3－B17復(fù)制并粘貼到B矩陣的右側(cè)，如圖1所示，這樣就完成了數(shù)據(jù)預(yù)備工作。

圖1 數(shù)據(jù)預(yù)備圖

3.2 最小二乘運(yùn)算

(1)計(jì)算矩陣BTB及其逆矩陣(BTB)－1。選定一個(gè)2?2的單元格區(qū)域，如G3:H4，然后借助Excel的矩陣乘法函數(shù)mmult和矩陣轉(zhuǎn)置函數(shù)transpose，輸入計(jì)算公式

同時(shí)按住Ctrl＋Shift鍵，回車，即可得到BTB的計(jì)算結(jié)果，如圖2所示。

選定一個(gè)2?2的單元格區(qū)域，如G7:H8，借助Excel的矩陣求逆函數(shù)minverse，輸入計(jì)算公式:

同時(shí)按住Ctrl＋Shift鍵，回車，即可得到(BTB)－1的計(jì)算結(jié)果，如圖2所示。

(2)計(jì)算向量BTY。選定一個(gè)2?1單元格區(qū)域，如G11:G12，然后輸入計(jì)算公式:

同時(shí)按住Ctrl＋Shift鍵，回車，即可得到BTY的計(jì)算結(jié)果，如圖2所示。

(3)計(jì)算向量(BTB)－1BTY。選定一個(gè)2?1的單元格區(qū)域，如G15:G16，然后輸入計(jì)算公式:

同時(shí)按住Ctrl＋Shift鍵，回車，即可得到(BTB)－1BTY的計(jì)算結(jié)果，如圖2所示。這個(gè)向量給出了GM (1，1)模型的參數(shù)估計(jì)值:

圖2 參數(shù)計(jì)算圖

3.3 建設(shè)模型

將計(jì)算得到的參數(shù)估計(jì)結(jié)果賦予GM(1，1)模型即可。

3.4 預(yù)測與檢驗(yàn)

(1)重新整理數(shù)據(jù)，將觀測時(shí)間變?yōu)閺牧汩_始的時(shí)序，并輸入表格中，同時(shí)將參數(shù)復(fù)制到數(shù)據(jù)附近，如圖3所示。

(2)計(jì)算累計(jì)數(shù)據(jù)的預(yù)測值。根據(jù)GM(1，1)模型及參數(shù)，在D2單元格中輸入計(jì)算公式:

然后將鼠標(biāo)光標(biāo)指向單元格D2的右下角，待其變成細(xì)黑十字，雙擊或下拉，即可得到累加數(shù)據(jù)預(yù)測值的計(jì)算結(jié)果，如圖3所示。如果將時(shí)序繼續(xù)延伸，即可繼續(xù)預(yù)測。

圖3 預(yù)測結(jié)果圖

(3)累加數(shù)據(jù)預(yù)測值的遞減還原。在E2單元格中輸入計(jì)算公式“＝D2”，回車；在E3單元格中輸入“＝D3－D2”，然后將鼠標(biāo)光標(biāo)指向單元格E3的右下角，待其變成細(xì)黑十字，雙擊或下拉，即可得到累加數(shù)據(jù)預(yù)測值的遞減還原結(jié)果，如圖3所示。

(4)計(jì)算絕對(duì)誤差與相對(duì)誤差。

利用Excel計(jì)算絕對(duì)誤差與相對(duì)誤差過程比較簡單，本文不再敘述，計(jì)算結(jié)果，如圖4所示。

圖4 絕對(duì)誤差與相對(duì)誤差計(jì)算圖

4 Excel在數(shù)據(jù)處理中的優(yōu)勢

(1)數(shù)據(jù)處理方便

利用Excel可以很方便的準(zhǔn)備整理原始數(shù)據(jù)，且整理后的數(shù)據(jù)能夠直接進(jìn)行計(jì)算。

(2)計(jì)算快捷

在GM(1，1)模型中參數(shù)運(yùn)算中需要最小二乘運(yùn)算，計(jì)算量很大，利用Excel的矩陣乘法函數(shù)、矩陣轉(zhuǎn)置函數(shù)、矩陣求逆函數(shù)可以快速的得到計(jì)算結(jié)果，且數(shù)據(jù)準(zhǔn)確，無人為計(jì)算誤差。

(3)可以方便的檢驗(yàn)結(jié)果

可以直接利用Excel的計(jì)算結(jié)果進(jìn)行檢驗(yàn)，計(jì)算出絕對(duì)誤差與相對(duì)誤差。

5 結(jié) 論

利用GM(1，1)模型進(jìn)行沉降預(yù)測數(shù)據(jù)量較大，使用傳統(tǒng)方法進(jìn)行計(jì)算費(fèi)時(shí)費(fèi)力且容易出錯(cuò)，利用軟件編程計(jì)算對(duì)普通工作者有一定的難度，利用Excel軟件進(jìn)行計(jì)算可以方便地進(jìn)行數(shù)據(jù)整理，借助Excel函數(shù)計(jì)算快捷易用，并可方便地進(jìn)行誤差檢核，在實(shí)際工作中有一定的應(yīng)用價(jià)值。

[1] 鄧聚龍.灰色系統(tǒng)基本方法[M].武漢:華中理工大學(xué)出版社，1987

[2] 李日云，王利，張雙成.灰色預(yù)測模型在高層建筑物沉降預(yù)測中的應(yīng)用研究[J].地球科學(xué)與環(huán)境學(xué)報(bào)，2005，27 (1):84～87

[3] 何濤，劉玉財(cái)，羊遠(yuǎn)新.機(jī)場沉降監(jiān)測數(shù)據(jù)處理的探討[J].城市勘測，2009(4):134～137

[4] 馮錦明，李炳芳.灰色預(yù)測模型在建筑物沉降監(jiān)測中的應(yīng)用[J].地礦測繪，2008，24(2):7～9

[5] 陳彥光.基于Excel的地理數(shù)據(jù)分析[M].北京:科學(xué)出版社，2010

Application of Excel to the Data Processing in the GM(1，1) Settlement Predication

Huang WeiTeng
(Maoming Yueneng Electrlc Power Co.，Ltd.Maoming 525000，China)

GM(1，1)model has widely used in the settlement predication.As its complex calculating process，the amount of calcuation is very great under the condation of a large quantity of data.Taking an settlement monitoring data as an example，this article puts forward the mean of data handling by Excel.The result shows that it has the advantages of simplicity and accuracy and it is a great convenience for calculation.This mean also has a certain application value in the GM(1，1)settlement predication.

Excel；GM(1，1)；Settlement Predication；Data Handling

1672－8262(2011)01－113－03

P209

B

2010—07—12

黃維騰(1979—)，男，工程師，主要從事工程測量、電力測量工作。