非線性方程的Excel擬合及在沉降評(píng)估中的應(yīng)用

張志新

（長(zhǎng)安大學(xué)公路學(xué)院，西安710064）

在鐵路施工中，為了控制工后沉降，需要進(jìn)行沉降觀測(cè)，并將沉降觀測(cè)的數(shù)據(jù)，擬合成沉降曲線，用于沉降量的預(yù)測(cè)和評(píng)估。控制工后沉降的技術(shù)路線為：沉降觀測(cè)—曲線擬合—預(yù)測(cè)—繼續(xù)觀測(cè)—曲線擬合—預(yù)測(cè)—預(yù)評(píng)估—評(píng)估。工后預(yù)估條件：（1）沉降時(shí)間滿足S(t)/S(t=∞)≥75%，式中：S(t)為預(yù)測(cè)時(shí)的沉降觀測(cè)值；S(t=∞)為預(yù)測(cè)時(shí)的最終沉降值；（2）利用兩次回歸結(jié)果預(yù)測(cè)的最終沉降差值不應(yīng)大于8 mm；（3）曲線回歸相關(guān)系數(shù)不應(yīng)低于0.92。可見(jiàn)，曲線擬合與指標(biāo)計(jì)算是沉降觀測(cè)的重要環(huán)節(jié)[1～2，5]。

沉降曲線一般采用兩種非線性方程：雙曲函數(shù)方程和指數(shù)函數(shù)方程[3～4]。

對(duì)于非線性方程的擬合，處理過(guò)程比較復(fù)雜，一般的工程技術(shù)人員難于掌握。目前也有大型的程序可以完成曲線擬合，但費(fèi)用昂貴，使用也比較復(fù)雜，工程應(yīng)用比較少。

在常用的辦公軟件Excel中，提供了假設(shè)分析命令，該過(guò)程通過(guò)更改單元格中的值來(lái)查看這些更改對(duì)工作表中公式結(jié)果的影響。規(guī)劃求解是假設(shè)分析的組成部分，通過(guò)自動(dòng)調(diào)整所指定的可更改的單元格（可變單元格）中的值，從目標(biāo)單元格公式中求得結(jié)果。利用假設(shè)分析命令中的規(guī)劃求解功能，方便地處理各種復(fù)雜非線性方程的擬合[6]。

本文介紹利用Excel進(jìn)行曲線擬合和預(yù)評(píng)估指標(biāo)的計(jì)算方法。

1 采用Excel擬合曲線的方法

1.1 基本原理

對(duì)于給定擬合曲線方程：S(t)/S∞(1－e-a·t)，式中：S∞—最終沉降量，以開(kāi)始檢測(cè)為時(shí)間零點(diǎn)；a— 沉降擬合曲線的參數(shù)。初步設(shè)定參數(shù)S∞和a，將t代入方程，計(jì)算S(t)，計(jì)算S(t)與觀測(cè)數(shù)據(jù)的差值，累計(jì)差值的平方，反復(fù)迭代計(jì)算，得出累計(jì)平方值最小時(shí)的S∞和a，即曲線方程的參數(shù)。

1.2 采用Excel擬合曲線的計(jì)算步驟

1.2.1 安裝規(guī)劃求解加載宏

用鼠標(biāo)單擊Excel2007左上角office按鈕，在彈出的下拉菜單中依次單擊“Excel選項(xiàng)”、“加載項(xiàng)”命令，在“管理”欄中選“Excel加載項(xiàng)”，單擊“轉(zhuǎn)到”按鈕，在“加載宏”對(duì)話框中選擇“規(guī)劃求解”，單擊“確定”。再次用鼠標(biāo)單擊Excel2007左上角office按鈕，在彈出的下拉菜單中依次單擊“Excel選項(xiàng)”、“加載項(xiàng)”命令，在“加載項(xiàng)”欄中選定“規(guī)劃求解加載項(xiàng)”，單擊“確定”。點(diǎn)擊“數(shù)據(jù)”菜單后，會(huì)出現(xiàn)“規(guī)劃求解”按鈕，如圖1。

圖1 規(guī)劃求解加載宏圖例

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

在Excel中建立工作表，設(shè)有45組觀測(cè)數(shù)據(jù)，時(shí)間范圍0～336天，見(jiàn)圖2。

圖2 數(shù)據(jù)表圖例

1.2.3 建立計(jì)算模型

在C1欄中增加“沉降值”，在D1欄中增加“離差平方值”，在E1欄中增加“參數(shù)”。在C2欄中輸入曲線公式“=$E$2*(1-е^(-$E$3*A2))”（以指數(shù)函數(shù)為例），在D2欄中輸入公式“= (C1-B1)^2”，使用自動(dòng)填充功能填充到最末行；在E4格中輸入公式“=SUM(D2:D46)”，在E2、E3中輸入?yún)?shù)的初設(shè)值，例如10、0.1，如圖3。

圖3 計(jì)算模型圖例

1.2.4 參數(shù)計(jì)算

選取“數(shù)據(jù)”中的“規(guī)劃求解”出現(xiàn)對(duì)話窗，在“設(shè)置目標(biāo)單元格”窗口，輸入“$E$4”，選定“最小值”選項(xiàng)，在“可變單元格”窗口中輸入“$E$2:$E$3”，如圖4。

圖4 參數(shù)計(jì)算圖例

在“規(guī)劃求解參數(shù)”和“規(guī)劃求解選項(xiàng)”對(duì)話框中有多個(gè)選項(xiàng)，根據(jù)“幫助”或?qū)I(yè)知識(shí)做選擇。

單擊“求解”，出現(xiàn)“規(guī)劃求解結(jié)果”對(duì)話框，如圖5。按“確定”，即得出結(jié)果，S∞和a分別為E2和E3中的值（2.502 02和0.007 06）。

1.2.5 形成曲線圖

框選“時(shí)間”、“實(shí)測(cè)曲線”和“沉降值”3列數(shù)據(jù)，然后點(diǎn)擊“插入”中的“散點(diǎn)圖”，選“帶直線和數(shù)據(jù)標(biāo)記的散點(diǎn)圖”，便可形成曲線圖。

圖5 計(jì)算結(jié)果圖例

取3個(gè)月（0～243天）前的實(shí)測(cè)數(shù)據(jù)，共33組，按照上述方法擬合，得出另一曲線參數(shù)S∞和a，分別為2.245 42和0.008 52。將兩次擬合的圖形顯示在同一圖上，如圖6。

圖6 曲線圖

2 用Excel進(jìn)行預(yù)估指標(biāo)的計(jì)算

工后預(yù)估指標(biāo)有：（1）S(t)/S(t=∞)，式中：S(t)為預(yù)測(cè)時(shí)的沉降觀測(cè)值，S(t=∞)為預(yù)測(cè)時(shí)的最終沉降值；（2）利用兩次回歸結(jié)果預(yù)測(cè)的最終沉降差值；（3）曲線回歸相關(guān)系數(shù)。

在Excel中列表計(jì)算，很方便得出上述指標(biāo)。相關(guān)系數(shù)的計(jì)算公式為其中 x和y是樣本平均值 AVERAGE(array1)和 AVERAGE(array2)。利用Excel中的相關(guān)系數(shù)計(jì)算公式計(jì)算，語(yǔ)法：CORREL(array1,array2)，其中Array1為第1組數(shù)值單元格區(qū)域；Array2為第2組數(shù)值單元格區(qū)域。

計(jì)算步驟如下：

（1）在數(shù)據(jù)欄下方輸入表格，如圖7。

圖7 預(yù)測(cè)指標(biāo)的計(jì)算圖例

（2）在B51中輸入“=E3”，在C51中輸入“=E2”，在D51中輸入“=CORREL(B2:B46,C2:C46)”，在F51中輸入“=-C46/C51*100”。

（3） 同樣，可求得0～243天的實(shí)測(cè)數(shù)據(jù)和擬合曲線數(shù)值的相關(guān)系數(shù)以及沉降完成比例值。

（4） 利用兩次的S(t=∞)值相減得出兩次預(yù)測(cè)最終沉降差。

3 數(shù)據(jù)對(duì)比檢驗(yàn)

為了檢驗(yàn)這個(gè)方法的正確性和準(zhǔn)確性，進(jìn)行試算。檢驗(yàn)數(shù)據(jù)采用某鐵路已經(jīng)完成沉降評(píng)估的數(shù)據(jù)，采用與檢驗(yàn)數(shù)據(jù)相同的擬合曲線方程和預(yù)評(píng)估指標(biāo)。共試算20組數(shù)據(jù)，試算結(jié)果和檢驗(yàn)數(shù)據(jù)中的結(jié)果對(duì)照如表1。

表1中， S∞即為S(t=∞)。從表1中看出， S∞最大差值為0.43 mm，主要原因是與檢驗(yàn)數(shù)據(jù)所用雙曲線模型稍有不同，另外算法上可能稍有不同，因無(wú)法分析檢驗(yàn)數(shù)據(jù)所用的程序，故不能做出明確判斷。相關(guān)系數(shù)R差別不大，說(shuō)明此方法與檢驗(yàn)數(shù)據(jù)所用方法差別不大，最大沉降量差別可以接受。

4 工程應(yīng)用效果

某段鐵路大橋利用Excell處理沉降觀測(cè)數(shù)據(jù)，與專用程序結(jié)果對(duì)比顯示，擬合的曲線要素稍有差別，但是預(yù)測(cè)沉降量差別不大，基本都在2 mm之內(nèi)。分析原因，是由于擬合算法以及計(jì)算精度不同所致。此項(xiàng)差別基本不影響沉降評(píng)估結(jié)果。

5 結(jié)束語(yǔ)

利用Excel中的規(guī)劃求解功能，分步實(shí)現(xiàn)沉降觀測(cè)中的曲線方程擬合；利用Excel的表格功能，實(shí)現(xiàn)沉降預(yù)估指標(biāo)的計(jì)算。經(jīng)過(guò)數(shù)據(jù)檢驗(yàn)與實(shí)際應(yīng)用，Excel擬合曲線的方法可以用于沉降評(píng)估與預(yù)測(cè)。用Excel擬合曲線的方法簡(jiǎn)便易行，但在前后處理方面較差。對(duì)于計(jì)算偏差和精度，需作進(jìn)一步研究。

數(shù)據(jù)檢驗(yàn)和實(shí)際應(yīng)用結(jié)果證明，本方法和專用程序相比，計(jì)算結(jié)果有差別，但差別不大。用Excel擬合曲線的方法在前后處理方面仍需要進(jìn)一步完善。在目前專用程序尚未商業(yè)化應(yīng)用的情況下，本方法可作為沉降觀測(cè)初步檢驗(yàn)分析的手段。

[1] 吳明友. 客運(yùn)專線無(wú)碴軌道鐵路工程測(cè)量和鋪設(shè)條件評(píng)估關(guān)鍵技術(shù)[J] .中國(guó)鐵路，2006（10）：28-31.

[2] 鐵道科學(xué)研究院. 客運(yùn)專線鐵路無(wú)碴軌道鋪設(shè)條件評(píng)估技術(shù)指南[S] . 北京：中國(guó)鐵道出版社，2006.

[3] 丁愿文. 京津城際鐵路橋梁沉降觀測(cè)與評(píng)估技術(shù)[J] . 中國(guó)工程科學(xué)，2009，11（1）：43-47.

[4] 薛保國(guó)，武廣客運(yùn)專線橋梁工程沉降觀測(cè)及數(shù)據(jù)分析[J] .鐵道建筑技術(shù)，2009（z1）：9-11.

[5] 客運(yùn)專線無(wú)碴軌道鐵路工程測(cè)量暫行規(guī)定[S] . 北京：中國(guó)鐵道出版社，2006.

[6] 胡亮，羅康碧，李滬萍. Excel的方程求解功能及其化學(xué)化工應(yīng)用[J] . 昆明理工大學(xué)學(xué)報(bào)（理工版），2002（2）.