利用Excel對(duì)圍巖量測(cè)數(shù)據(jù)分析處理一、回歸分析的原理簡(jiǎn)介：1、量測(cè)數(shù)據(jù)進(jìn)行回歸分析的意義在現(xiàn)場(chǎng)監(jiān)控測(cè)量中，由于測(cè)量條件，外界環(huán)境的影響，加之操作時(shí)的人為因素，給量測(cè)數(shù)據(jù)帶來(lái)了偶然誤差，實(shí)測(cè)值需要經(jīng)過一定的數(shù)學(xué)處理才能加以應(yīng)用，因此必須進(jìn)行數(shù)學(xué)上的回歸分析，找出時(shí)間(t)和位移（u）兩個(gè)變量之間相互關(guān)系的函數(shù)關(guān)系式，從而獲得較準(zhǔn)反應(yīng)實(shí)際情況的典型線性，修正位移-時(shí)間（u-t）曲線中按照實(shí)測(cè)數(shù)據(jù)所描繪的散點(diǎn)分布，找出位移隨時(shí)間的變化規(guī)律，并推算出位移的極限值，為監(jiān)控設(shè)計(jì)提供重要信息。2、回歸分析的基本數(shù)學(xué)原理：①一元線性回歸一元線性回歸是研究被測(cè)物理量隨時(shí)間呈線性變化的規(guī)律，即通常說的配直線問題，若令被測(cè)物理量（如位移）為y，觀測(cè)時(shí)間為x我們?cè)O(shè)法找出一直線函數(shù)式來(lái)表示兩個(gè)變量y和x的關(guān)系，即：y=a+bx這條直線稱y對(duì)x的回歸線。實(shí)測(cè)數(shù)據(jù)散點(diǎn)一般都不在一條直線上，要選擇的直線與實(shí)際散點(diǎn)相差最小，最有代表性，需要用最小二乘法原理來(lái)判別。若自變量x取最小值xi時(shí)，對(duì)應(yīng)的實(shí)測(cè)值y為而用回歸計(jì)算所得的yi′值為yi。則：yi=a+bxi。最小二乘法的原理就是：已有幾個(gè)試驗(yàn)點(diǎn)（xi,yi）,i=1,2,3...n,時(shí)平方和∑（yi-yi′）2=∑(yi-a-bxi)2達(dá)到最小時(shí)的回歸線是最好的,帶入模型，便可以確定回歸方程。②一元非線性回歸在現(xiàn)場(chǎng)測(cè)試與工程試驗(yàn)中，兩個(gè)變量間多數(shù)不是線性關(guān)系，而是某種曲線關(guān)系，可進(jìn)行一元非線性回歸分析，其方法步驟如下：選擇能代表兩變量x和y之間關(guān)系的函數(shù)模型；求出兩變量x與y相關(guān)函數(shù)中的未知參數(shù)。欲求非線性函數(shù)關(guān)系中的未知參數(shù)，首先是通過把非線性的函數(shù)關(guān)系轉(zhuǎn)換成線性是函數(shù)關(guān)系，然后按照線性函數(shù)求未知參數(shù)的方法求出未知參數(shù)，再由參數(shù)變換式求得選定的曲線函數(shù)的未知參數(shù)，而得到曲線函數(shù)的回歸方程；根據(jù)《規(guī)定》要求目前工程試驗(yàn)、觀測(cè)數(shù)據(jù)回歸分析常采用一下函數(shù)作為回歸函數(shù)：對(duì)數(shù)函數(shù)：U=A+B㏑(t+1)指數(shù)函數(shù)：U=Ae-B/t雙曲函數(shù)：U=t/(A+Bt)式中：U——變形值（mm）；A，B——回歸系數(shù)t——量測(cè)時(shí)間（d）；二、采用指數(shù)方程模型即U=Ae-B/t進(jìn)行回歸分析的兩種方法：寇以拉脊山隧道社工程所測(cè)拱頂顛下沉一組數(shù)據(jù)嬸為例，進(jìn)行回碧歸分析來(lái)判斷金隧道拱頂圍巖冬穩(wěn)定情況：測(cè)福量數(shù)據(jù)如往表駁1繳所示：私表社1才拱頂下沉測(cè)量鉤數(shù)據(jù)匙時(shí)間館（腸d擾）析拱頂下園沉盯▲貝h菊（昌m摔m當(dāng)）醋1助2惱2燦5軋3俯5斃4鞠6升5縱7春6腫7拜7辰7捎方法一細(xì)：速LOGES匹T準(zhǔn)函數(shù)犁在回歸分析中腿，計(jì)算最符合識(shí)數(shù)據(jù)的指數(shù)回脊歸擬合曲線，頭并返回描述該仰曲線的數(shù)值數(shù)今組。因?yàn)榇撕钄?shù)返回?cái)?shù)值數(shù)季組，故必須以恨數(shù)組公式的形蠢式輸入。此曲留線的公式為：升y=bm旋x分其中因變過量費(fèi)y忘懸是自變恒量象x魯蔑的函數(shù)值粱。皂m觸灰值是各指堅(jiān)數(shù)撈x持起的底，培而供b冤值是常量值講，鋼LOGEST義妻函數(shù)返回的數(shù)胖組窩為勉{m,b}堪以及一些附加弱值。語(yǔ)法狡LOGEST夠(known測(cè)_y's,k守nown_x扭's,con個(gè)st,sta組ts)撓Known_簽y's

喊芽滿足指數(shù)回歸奪擬合曲線顛y=bm午x緞燙的一組已知剃的勢(shì)y夾俘值。診Known_司x's

元賊滿足指數(shù)回歸翁擬合曲確線器那y=bm銜x甘的一組已知巨的探x粘胳值為可選參數(shù)榮。毯Const

施辭為一邏輯值，臨用于指定是否紗將常滾數(shù)勵(lì)b洲卷強(qiáng)制設(shè)也為乎嘩1總。上如面果伍const津腸為幅TRUE伯草或省略潑，竄b嗓廳將按正常計(jì)算滅。墻如撤果動(dòng)const蔽誘為因FALS抓E組，則常猜量貢b群咳將設(shè)朝為在流1拴，粒而率m戀舞的值滿足公梯式齒y=m迷x規(guī)。撕Stats

兵段緣為一邏輯值，捆指定是否返回那附加回歸統(tǒng)計(jì)確值。餡如碧果趙stats蓮陽(yáng)為斥TRU遭E久，函抽數(shù)富LOGES石T介誤將返回附加的膊回歸統(tǒng)計(jì)值，允因此返回的數(shù)液組例為狼{m去，棍b;吩se1,se襖b;r秧2,sey;晝F,df;銅ssreg,投ssresi廟d娃}歉。臉如自果撿stats鉤粥為琴FALSE源陶或省略，則函儲(chǔ)數(shù)齊LOGSE律T掠生只返回系芽數(shù)收m崖汗和常曠量微b允。衫附加回歸統(tǒng)計(jì)隸值如銷表支2役：表2客統(tǒng)計(jì)值盡說明嬌se1閣系唯數(shù)痛m互的標(biāo)準(zhǔn)誤差值孤seb霧常構(gòu)量丸b史典的標(biāo)準(zhǔn)誤差值虧（護(hù)當(dāng)謊const捏鍋為滑F(xiàn)ALS甜E屬時(shí)愿，哨seb=交#N/孫A筐）泊r2麥判定系數(shù)技。張Y靠梅的估計(jì)值與實(shí)穿際值之比，范出圍州在兩0坐皺到酬1律紀(jì)之間。

免如果慣為輛啞1它，則樣本有很賣好的相關(guān)性駛，前Y篩濟(jì)的估計(jì)值與實(shí)怪際值之間沒

寸有差別。如果列判定系數(shù)傷為和疊0扯，則回歸公式奮不能用來(lái)預(yù)游測(cè)粱Y扮紡值。撇sey么Y服渡估計(jì)值的標(biāo)準(zhǔn)岸誤差。星F凈F廣片統(tǒng)計(jì)燙或殺F酬嫁觀察值。使獸用鼻F昨?yàn)a統(tǒng)計(jì)可以判斷均因變量和自變乓量之間

賠是否偶爾發(fā)生副過可觀察到的除關(guān)系。薄df較自由度。用于勿在統(tǒng)計(jì)表上查關(guān)找劇F播鏟臨界值納ssreg枯回歸平方和?？辳sresi極d押殘差平方和。天表陸3座顯示了附加回算歸統(tǒng)計(jì)值返回艙的順序。激表施3龜m叼b基se1凍seb您r2胖sev輛F距df憐ssreg騎ssresi墓d說明：稈1烤、事回歸分析時(shí)松，敗Excel開計(jì)計(jì)算每一點(diǎn)怒的胖y套步的估計(jì)值和實(shí)助際值的平方差輪。這些平方差貓之和稱為殘差禮平方樹和必(ssre供sid社)街。然鑒后長(zhǎng)Micro快softE容xcel儀祥計(jì)算總平方劫和汪(ssto炊tal裂)纏。魯當(dāng)企const如=TRU計(jì)E曠飲或被刪除時(shí)，恐總平方和繼是三y筑舌的實(shí)際值和平非均值的平方差聞之和。曾當(dāng)樸const環(huán)=FAL憶SE哈篩時(shí)，總平方和雕是底逆y佛血的實(shí)際值的平孤方和（不需要解從每債個(gè)聚y幣開值中減去平均零值）?；貧w平患方墓和皮(ssre術(shù)g)供宰可通過公棕式唇ssreg角=sst叫otal-提ssres腔id由中計(jì)算出來(lái)。殘耐差平方和與總閉平方和的比值況越小，判定系大數(shù)唱r2困先的值就越大奶，烤r2開意是表示回歸分室析公式的結(jié)果想反映變量間關(guān)窗系的程度的標(biāo)渡志墾。宰r2擠腔等煎于守ssreg筋/sstot瞇a必l在。遵2絞、碧df旬扛的計(jì)算方法，企如下所示（沒召有仍X獅姜列由于共線而霧從模型中被刪引除）：如果存攏在書known龜_x's陰咐的可k巡虛列溜和截const勿=TRU需E辭套或被刪除，那盒么障df=展n旁者–謎k忌纖–克緞1這。如表果橋const僚=FAL攝S幸E死，那牌么戰(zhàn)df=努n-眼k警。在這兩種情灶況下，每次由縱于共線而刪除村一企個(gè)午X烤糞列都會(huì)陣使泥df守缸加睜甩1熟。離用辱此登方法對(duì)上述測(cè)忘量數(shù)據(jù)進(jìn)行回奮歸分析，具體航操作如下：會(huì)1挨、將時(shí)電間蹤t怨換算停成歲-1/降t芽（對(duì)時(shí)間取負(fù)趨倒數(shù)既）屈,奶結(jié)果如肚表往4迅表創(chuàng)4拳紫道座委拱頂下沉測(cè)量何數(shù)據(jù)擋時(shí)間老（狐d貿(mào)）銳拱頂下瞧沉雀▲重h蘭（追m宋m棗）淺(喬-1/t)緒1席2雖-1.000落0所2者5副-0.500很0皂3靈5嘉-0.333失3籮4理6思-0.250鎮(zhèn)0扇5逗7掃-0.200細(xì)0個(gè)6礎(chǔ)7旁-0.166嗎7轟7蹈7泄-0.142折9慧2鋼、在當(dāng)前工作凡表中任意選崖?lián)衽?姻行敞2翁列的區(qū)域，在如喊A1催3呆：催B17索3丸、在菜單選相擇烏“府插饑入著/蛛函霜數(shù)就/赤統(tǒng)鈴計(jì)甚/LOGES德T倚”悶，然隆后押“衰確紀(jì)定賞”醬彈結(jié)出揪“扭函數(shù)參鍬數(shù)暢”飯對(duì)話框；吐4追、米“秋Known_酬y's

它”羨選擇表久中騙“升拱頂下匪沉犧▲排h僑（培m閣m江）沉”景欄暑“豈B忘3應(yīng)：恢B犁9資”符；承尿陳“車Known_坦x's

消”爺選擇表殃中干“蝦(植-1/t)嗽”珠欄占“妖C誘3奉：危C逆9禿”渾；旺“墳Const惹犧”隱中輸士入鈴“丸TRU羨E牲”認(rèn)；勒“糾Stats

君徑”邀歇中輸臺(tái)入岔“恒TRU筆E成”董；歡5采、留示例中必須以摸數(shù)組公式的形概式輸入，所以輛先姓按輛F殖2鴉，再說按祖Ctrl+S野hift+E件nte聾r搭。雖就會(huì)得到停表歡5錘：否則，只輸砌出單祖值胡“害4.2770扁1緞1絡(luò)”逼。仍表帽5賽中數(shù)據(jù)和共表裳6披的參數(shù)呈一一乒對(duì)應(yīng)關(guān)系。摟表匠5襯撿哄蛋桿偉鳥查緒表繭6與4.2770弄1104勻8.9092姓03621賊擺m造b勻0.1160于7089徹0.0539紋41256蘿m騙標(biāo)準(zhǔn)誤差共b傲標(biāo)準(zhǔn)誤差具0.9690魯9011圖0.0861駛88528己判定系當(dāng)數(shù)腔r2廟Y屯估計(jì)值的標(biāo)準(zhǔn)依誤廢差午sey鐘156.76眾05灰5享F在觀察值怎d冬f頭自由度鑒1.1644何8948妙0.0371午42312墨ssre甘g丙回歸平方和抵ssresi賢d姐殘差平方和失由此我們可以趣得出，在唱LOGES漲T德模型覺“轎y=bm閑x姥”努中礦m=4.27可701104廳b營(yíng)=8.909藏204蒸,典所以回歸方程贏為其y=焰8.9092渣04組*合4.2770蹤1104捎x微而在工程實(shí)際兄中我們通常使右用款“聯(lián)U=Ae破-B/t掠”答模型，在此我敵們做相應(yīng)轉(zhuǎn)換初，失即慈-1/t=x糟,U=y,A噸=8.909等204,eB蒙=4.277敏01104擠,耳所踢以慧B=柔1.4532紛5挪；男最后得到相應(yīng)蔑回歸方剩程斃U魯=8.909究204*e需-1.453草25/t種通過回歸方程誼我們可以得出芽t佩→拘∞眾時(shí)，拱頂收斂幼穩(wěn)定于薄8.9092嶼04mm鮮。時(shí)靈間迅t涌已知，可以在揀工作表中算句出伸U全值，最后根據(jù)邀時(shí)惱間康t炕和反U援值得到回歸曲姿線圖。（詳見念附碼件扭1方法二：羅運(yùn)用此方法進(jìn)矛行回歸分析時(shí)搞先應(yīng)該將非線歌性的回歸方程溪U=Ae牧-B/t孩轉(zhuǎn)化成線性方立程，對(duì)啟方程億U=Ae馬-B/t火兩邊取對(duì)數(shù)得過到慧㏑宵U被=叛㏑犯A-B/t住,有然后輝令然y=嫌㏑殖U醫(yī)，勤a冶=插㏑富A,b=B,欺x=-1/t睜,姥則搭有蠢y=a+b煌x風(fēng)。同時(shí)應(yīng)該對(duì)扣量測(cè)數(shù)據(jù)進(jìn)行射相應(yīng)處理，結(jié)滑果如表貝7經(jīng)表桂7面拱頂下沉測(cè)量迅數(shù)據(jù)倦時(shí)間榴（沉d拉）柱拱頂下都沉能▲車h潮（許m你m變）蝕x=(-1/政t)淘y睛=齊㏑模h原1拉2瞞-1.000雷0共0.6931攏47181證2謎5栗-0.500污0滅1.6094掏37912最3監(jiān)5啦-0.333她3登1.6094份37912抓4揉6慌-0.250崇0宋1.7917即59469窩5帖7稱-0.200湊0挑1.9459翁10149急6各7惰-0.166荒7齡1.9459裙10149桌7供7裂-0.142融9疲1.9459坡10149黃到此我們已將徑非線性的回歸仗方程轉(zhuǎn)化為線養(yǎng)性的回歸方程選，然后針對(duì)此匯線性方程進(jìn)行娃回歸分析，具犯體操作如下：罵1梢、在菜單欄中活選圈擇輸“題工魚具標(biāo)/冊(cè)數(shù)據(jù)分商析攪/鳴回蜂歸券/覽確濟(jì)定腐”伶。陳注：欺若價(jià)“虎工仍具景”起菜單中沒褲有芳“瀉數(shù)據(jù)分脫析閘”御項(xiàng)就在本菜單偽中較選軍“塊加載廉宏覺/罰分析工扣具輸/告確序定恐”虧，錢則優(yōu)“荷數(shù)據(jù)分梢析厘”雄項(xiàng)出現(xiàn)辣在拖“蝴工蔑具搶”率菜單中。嶄2傾、帝在鴿“止回燃?xì)w波”仁對(duì)話框中一次瞎選蒙擇辭“勉Y班值輸入雖域刑”報(bào)“鎮(zhèn)X鵝值輸入翅域率”殘以及六“書輸出區(qū)洗域泉”晴，（可以根據(jù)拐需要同時(shí)輸不出餃“逗殘舌差氏”唇“三正襲態(tài)記概率罪圖印”臣，本例秘葵中未選）并。漏“描確哭定積”盈3咽、在輸出的數(shù)客據(jù)中，旨Coeffi心cients林（系數(shù)）欄所驚對(duì)應(yīng)的孕Interc庭ept凱=釋斬2.1870昆848571肌1902掌選，翁雷XVari索able1銀=踏乖1.4532拆544102甘5219時(shí)分別對(duì)應(yīng)線性答方廢程憶y=a+b藝x霜中的系股數(shù)待a舟和扔b骨，即勵(lì)a礦=指㏑丸A葛=2.18緒7,b=B=畫1.453借,冒由此可戀得評(píng)A=行甲EXP奏（拋a翼）浴=列拍8.9092藝04幣,B=1.償453舅,倒從而得到非線甘性方程出U獨(dú)=8.909藥204*e頁(yè)-1.453愧2