巧用Excel軟件處理擊實試驗數(shù)據(jù)并繪制曲線效果圖

1、巧用Excel軟件處理擊實試驗數(shù)據(jù)并繪制曲線效果圖永利（生產(chǎn)建設(shè)兵團農(nóng)二師勘測庫爾勒市841000）【摘要】應(yīng)用Excel對擊實試驗數(shù)據(jù)成果進行簡化處理，繪制擊實曲線圖，并利用Excel強大的分析計算功能，求解出曲線的趨勢方程及相關(guān)系數(shù)，進一步求解出最優(yōu)含水率和最大干密度，簡化了繪制曲線的煩瑣工作，同時減少手工繪圖的誤差。本文對此作了詳細介紹?！娟P(guān)鍵詞】Excel擊實試驗曲線飽和趨勢方程相關(guān)系數(shù)最大干密度最優(yōu)含水率飽和度1前言土的擊實試驗是衡量土的壓實性的一項很重要指標，在鐵路、公路、橋梁、機場、水利（水庫壩體）等工程建設(shè)的填方地基設(shè)計中都是必不可少的。此外，土的滲透、壓縮、剪切等試驗在一定程

2、度上又是由擊實試驗來控制的。所以擊實試驗的成果對設(shè)計、施工以至于工程造價都會帶來影響。土在外力作用下（碾壓或夯實），孔隙度變小，密度增加，強度提高，壓縮性及滲透性降低，使土的工程性質(zhì)得到改善。而土的壓實性就是指在一定的含水量條件下，以人工或機械的辦法，使土能夠達到某種密實程度的性能。土的壓實強度與含水量、壓實功能和壓實方法有著密切的關(guān)系。當壓實功能和壓實方法不變時，則土的干密度隨含水量的增加而增加；當干密度達到某一最大值后，含水量的繼續(xù)增加反而使干密度減小。此時干密度的最大值稱為最大干密度，其相應(yīng)的含水量稱為最優(yōu)含水量。擊實試驗的目的，就是模擬工地壓實條件，用標準擊實的方法，測定在某種壓實功能

3、下土的含水量和干密度的關(guān)系，確定土的最優(yōu)含水量和相應(yīng)的最大干密度，以求用最小的壓實功能，得到符合工程要求的密實度。*BCB!需常！怎盂！.也1氣尊I*于;M上”rg平s.nli圖1擊實試驗計算和曲線效果圖長期以來，工作在試驗場所的技術(shù)人員面對大量擊實試驗數(shù)據(jù)，采用手工描點，曲線尺繪圖的辦法，不僅工作量大煩瑣，并且極易受個人因素影響，得出的試驗結(jié)果可信度比較差，不同程度地困繞著工程技術(shù)人員。目前計算機已經(jīng)普及，但專業(yè)繪制擊實效果圖的軟件并不多見，且有的軟件在輸入數(shù)據(jù)時相對較為復雜。若使用AutoCAD繪制，由點連線，既不精確也很麻煩。筆者利用Excel處理試驗數(shù)據(jù)，并繪制擊實曲線圖（該圖為矢量圖

4、，可任意放大縮小，便于試驗人員對比觀察），最終求解出試驗結(jié)果。利用Excel表格還可以對試驗中的異常值加以分析補充或剔除。本文介紹了這一方法和操作過程，拋磚引玉，請同行指點。本文實例是采用南實處型擊實儀（定體積法）對粒徑小于5mm的土樣試驗后所得數(shù)據(jù)的分析處理。其他類型土的物理力學數(shù)據(jù)，可采用公式校正的方法，達到試驗?zāi)康摹?數(shù)據(jù)的采集與輸入首先新建Excel文件，制作各種表頭項目，如圖1所示。別圖3選擇曲線類型利用Excel的表格功能輸入表頭“擊實試驗計算&曲線效果圖”，居中后設(shè)定字體大小并合并單元格(以下類似)。在表頭下方各行分別輸入調(diào)配含水量(%)、干密度、土的飽和含水率(%)、濕密度、擊

5、實筒+土重(g)、擊實筒體積(cm3)、土的比重等。其他表項如圖依次類推。制作完后輸入本試驗中的常量，如筒的重量、體積，水的密度等，再輸入試驗所得土的比重，不同調(diào)配含水量及其相應(yīng)的擊實筒+土重。然后根據(jù)公式：TOC o 1-5 h z濕密度=(擊實筒+土重)-擊實筒重)/擊實筒體積(1)如表中B7=(B8-B9)/B10干密度二濕密度/(1+0.01X調(diào)配含水量)(2)如表中B5二B7/(1+B4X0.01)土的飽和含水率=(4C水的密度/土的干密度-1/土樣比重)X100(3)如表中B6=(E10/B51/E9)X100其余的相應(yīng)表格項可按住Ctrl鍵用鼠標拖動的辦法依次產(chǎn)生或輸入類似公式。

6、3繪制曲線效果圖干密度和含水率的關(guān)系曲線圖2選取圖形類以含水率為橫坐標，干密度為縱坐標準備繪圖。選取插入f圖表向?qū)Вɑ虺Ｓ霉ぞ邫诘膱D表向?qū)В┴（圖表向?qū)?4步驟之1-圖表類型。如圖2所示）XY散點圖f平滑線散點圖f下一步（圖表向?qū)?4步驟之2-圖表源數(shù)據(jù)。如圖3、圖4所示）f數(shù)據(jù)區(qū)域。在空白區(qū)域中用鼠標框選B4：G5或輸入二Sheet1!$B$4：$G$5；系列產(chǎn)生在選行下一步（圖表向?qū)?4步驟之3-圖表選項圖5）。圖表標題（T）輸入擊實試驗計算&曲線效果圖；數(shù)值x軸（A）輸入含水量3（%）,y軸（V）輸入干密度pd（g/cm3）；坐標軸數(shù)值x軸（A）、數(shù)值y軸（V）多選框中全打V；圖6網(wǎng)格

7、線中數(shù)值（x）軸、數(shù)值（y）軸的主要網(wǎng)格線和次要網(wǎng)格線多選框中可模擬米格紙全打V;圖例中顯示圖例可依據(jù)喜好或圖形位置分別選擇底部、右上角等；數(shù)據(jù)標志中可以選擇散點的Y值或X值或系列名稱或兼而選擇，這主要依據(jù)最后形成圖形的大小。例如以A4圖紙擺放，則最好選Y值或X值中其一為好，其余依據(jù)最終顯示可要可不要。下一步（圖表向?qū)?4步驟之4-圖表位置。如圖7示）選擇，將圖表作為對象，插入前的單選框打0,選擇相應(yīng)的工作表，當然也可作為新工作表插入。以上步驟在圖表生成后,亦可在圖形上選鼠標右鍵相應(yīng)選項修改或添加。曲線趨勢方程的生成與求解綜觀曲線類型（圖4、圖5），結(jié)合長期以來我們用米格紙描圖擬合曲線求最大干

8、密度和最優(yōu)含水率的經(jīng)驗，不難發(fā)現(xiàn)該曲線在坐標系中成鐘式分布。我們在試驗中忽略其他因素如試驗儀器、方法、擊實作工等不計，理想狀態(tài)下的曲線（在峰值附近）應(yīng)為:曲線f（x）在值為a,b的區(qū)間滿足二項式y(tǒng)=f（x）=ax2+bx+c（其中a0）。在此基礎(chǔ)上，我們可在曲線上點鼠標右鍵選取添加趨勢線（R）（圖8）,在類型子菜單中選多項式（P）階數(shù)為2（圖9）；選項子菜單中分別設(shè)定趨勢線名稱（自定如擊實試驗曲線趨勢方程或選自動設(shè)置）（圖10）；趨勢預測中前（后）推單位可在0.5個單位調(diào)整，如果單位變動，縱坐標也會隨之調(diào)整。單位調(diào)整后圖形“胖瘦”會發(fā)生明顯變化。設(shè)置截距(S)中一般不選，顯示公式(E)及顯示R

9、平方值(R)前的多選框打丿(如圖10所示)。ijjlvmj.-railTuqi：iIimaI1-住津bU|口rlimEG3EH.2fJhuri*.1=1-1iiI|卩開。比nilffHH-AffR-1WJ-BtMHiItM颶；茁IBKCfflf-QIMm頊械空Ria合笛殆MiEK?EtfEXE4KUmtnhn戈P(guān)l-r圖6選擇坐標類型圖4典線類型圖a圖5典線類型圖b7選擇工作表E?SJduJpMi伽mF?SNS甲eWMZ口IS盤環(huán)KgIfulMLBLSMiyhWOI_31BtHE里IUdMifiMSiMidID圖8添加趨勢線圖9選多項式階數(shù)點擊確定即生成飽和曲線圖(圖11)。圖形生成后在圖表區(qū)

10、點擊鼠標右鍵，可選取數(shù)據(jù)系列格式和趨勢線格式，并進一步設(shè)置圖形線條、類型、顏色等。SJflBiKK圖10設(shè)定趨勢線名稱圖11飽和曲線圖t知l顯wWNBfKMl首i岡玄殲藥aHDI石3；WJW*LWIUMMSI&F(!Uftik廠jj:占畀冊卄Tt雷枕汽!RS圈舄y難n衍血説野:jS叭EHrrw-WTiITUErrtuHMK*|罔u0卻皿1*|nrn1?1KJ|專盂怦亍宅wrtSwnrrHv.o|-xuHii!ehdijMi:爾釦畫Ii.jw1i圖12選取源數(shù)據(jù)圖13飽和曲線參數(shù)設(shè)置表對于生成的多項式方程，參數(shù)a、b、c及方程式分別填入圖1所示的B11、C15、C16、C17等單元格。根據(jù)二次多

11、項式的規(guī)律，此時峰值處有x=-b/2a(5)y=ax2+bx+c=aX(-b/2a)2+bX(-b/2a)+c(6)即在C18、C19單元格中，f(x)二分別為二-D15/(2*C15)和二C15*F15V+D15*F15+E15。最后把得到的結(jié)果值分別到經(jīng)過處理的D16和H16兩個單元格中。在圖10截距(S)一欄中，其選擇值會影響圖形中擊實曲線及其趨勢線峰值處的擬合程度。本例中若取截距0.5，圖形峰值處的擬合會好一點，生成的結(jié)果分別如下：y=-0.0054x2+0.154x+0.7179(趨勢方程)(7)R2=0.9844(相關(guān)系數(shù))最優(yōu)含水量(%)sop=1426最大干密度(g/cm3)p

12、dmax=1.816(不選設(shè)置截距(S)項)y=-0.0065x2+0.1852x+0.5(趨勢方程)(8)R2=0.9456(相關(guān)系數(shù))最優(yōu)含水量(%)sop=1425最大干密度(g/cm3)Pdmax=1.819(選設(shè)置截距(S)項，設(shè)置截距為0.5時)通過比較發(fā)現(xiàn)，不選擇設(shè)置截距(S)與選擇設(shè)置截距(S)所得出的最優(yōu)含水量、最大干密度差異不大，在施工中基本可以忽略，不選設(shè)置截距(S)的相關(guān)系數(shù)也比選設(shè)置截距的相關(guān)系數(shù)要好。當改變截距時，對于峰值的重合性要好一些。制作飽和曲線因為成圖在同一個坐標系中，因此制作飽和曲線時可直接在圖11的基礎(chǔ)上，于圖表區(qū)點擊鼠標右鍵選取源數(shù)據(jù)(圖12)，確定選

13、取系列子項目，設(shè)置好曲線名稱、X值和Y值的區(qū)域(可用鼠標拖選或輸入絕對路徑)，如圖13所示。經(jīng)過上述各步驟，我們基本完成了擊實試驗曲線圖、趨勢線及飽和曲線的制作，并擬合出兩種曲線的趨勢方程，完成對最大干密度、最優(yōu)含水量的求解，最終生成的試驗結(jié)果圖表模擬打印如圖14所示。由或討期Ul述加JTJ：曲田J-J-.l:4I-.、0ii*4，-riAH1W.AXJ_?1BAAC19.fd.i.TU15丄.T1MlXV:Crf1-J.9ji.ii：*H1L.+r.LI_5:fl?JKii.-；zir._-l.tavTllRL“FLVnn*?-.IZtillVfc-；-M-lfT：MiX.UVA-.fc：-

14、T4H171UrflCl1U.1IBri:j-.-j-.-T.-t:AUWLfJ-.41AZ.rJxC1T：-ll-JLT-1：2出M,.10niW44*亠.-WIJr.-ILKipjn.rIJJi.iU.i.iTni.-LI*1.La蟲丄ir.iirPihir11-.rr-hi.tiitl-Mr.-v.*jr:.-r-Pr-.：-.-kMrAA亠J：*rTiriiF.ifiF-.-1iTi*UDCWrr*iJ工氣.JaKIHa:V；MAi：1-1-u-i:“亠.-7.iJir:a.!I-!tfrEFiiIHtti展H.=ED14擊實試驗成果打印圖表4小結(jié)擊實試驗由于資料處理煩瑣，一向被試驗人員所頭疼。利用Excel可以充分發(fā)揮其強大的制表、公式計算和繪圖功能（由于其圖形具有矢量化性能，從而為進一步微觀查看圖表參數(shù)、修正試驗誤差和補充試驗數(shù)據(jù)提供了方便），通過圖形反推出趨勢線及趨勢方程，從而把圖解功能轉(zhuǎn)化為數(shù)解模塊，為試驗人員準確處理試驗數(shù)據(jù)提供可行性，同時也減少了試驗人員間的可比性和試驗室誤差。而且用本方法所建立的表格系統(tǒng)完全可以模板化，試驗人員僅需將所得原始數(shù)據(jù)，填入相應(yīng)的表格中即可生成圖表。生成的圖表只需稍作格式調(diào)整，即可以清晰方便地瀏覽并存檔，解決了長期以來困擾試驗人員的一大難題。5注意事項由于擬合的二次方程僅在一定的