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

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

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

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

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

5、實(shí)筒+土重(g)、擊實(shí)筒體積(cm3)、土的比重等。其他表項(xiàng)如圖依次類推。制作完后輸入本試驗(yàn)中的常量，如筒的重量、體積，水的密度等，再輸入試驗(yàn)所得土的比重，不同調(diào)配含水量及其相應(yīng)的擊實(shí)筒+土重。然后根據(jù)公式：TOC o 1-5 h z濕密度=(擊實(shí)筒+土重)-擊實(shí)筒重)/擊實(shí)筒體積(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)表格項(xiàng)可按住Ctrl鍵用鼠標(biāo)拖動(dòng)的辦法依次產(chǎn)生或輸入類似公式。

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

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

8、密度和最優(yōu)含水率的經(jīng)驗(yàn)，不難發(fā)現(xiàn)該曲線在坐標(biāo)系中成鐘式分布。我們?cè)谠囼?yàn)中忽略其他因素如試驗(yàn)儀器、方法、擊實(shí)作工等不計(jì)，理想狀態(tài)下的曲線（在峰值附近）應(yīng)為:曲線f（x）在值為a,b的區(qū)間滿足二項(xiàng)式y(tǒng)=f（x）=ax2+bx+c（其中a0）。在此基礎(chǔ)上，我們可在曲線上點(diǎn)鼠標(biāo)右鍵選取添加趨勢(shì)線（R）（圖8）,在類型子菜單中選多項(xiàng)式（P）階數(shù)為2（圖9）；選項(xiàng)子菜單中分別設(shè)定趨勢(shì)線名稱（自定如擊實(shí)試驗(yàn)曲線趨勢(shì)方程或選自動(dòng)設(shè)置）（圖10）；趨勢(shì)預(yù)測(cè)中前（后）推單位可在0.5個(gè)單位調(diào)整，如果單位變動(dòng)，縱坐標(biāo)也會(huì)隨之調(diào)整。單位調(diào)整后圖形“胖瘦”會(huì)發(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選擇坐標(biāo)類型圖4典線類型圖a圖5典線類型圖b7選擇工作表E?SJduJpMi伽mF?SNS甲eWMZ口IS盤環(huán)KgIfulMLBLSMiyhWOI_31BtHE里IUdMifiMSiMidID圖8添加趨勢(shì)線圖9選多項(xiàng)式階數(shù)點(diǎn)擊確定即生成飽和曲線圖(圖11)。圖形生成后在圖表區(qū)

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

11、項(xiàng)式的規(guī)律，此時(shí)峰值處有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兩個(gè)單元格中。在圖10截距(S)一欄中，其選擇值會(huì)影響圖形中擊實(shí)曲線及其趨勢(shì)線峰值處的擬合程度。本例中若取截距0.5，圖形峰值處的擬合會(huì)好一點(diǎn)，生成的結(jié)果分別如下：y=-0.0054x2+0.154x+0.7179(趨勢(shì)方程)(7)R2=0.9844(相關(guān)系數(shù))最優(yōu)含水量(%)sop=1426最大干密度(g/cm3)p

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

13、取系列子項(xiàng)目，設(shè)置好曲線名稱、X值和Y值的區(qū)域(可用鼠標(biāo)拖選或輸入絕對(duì)路徑)，如圖13所示。經(jīng)過上述各步驟，我們基本完成了擊實(shí)試驗(yàn)曲線圖、趨勢(shì)線及飽和曲線的制作，并擬合出兩種曲線的趨勢(shì)方程，完成對(duì)最大干密度、最優(yōu)含水量的求解，最終生成的試驗(yàn)結(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擊實(shí)試驗(yàn)成果打印圖表4小結(jié)擊實(shí)試驗(yàn)由于資料處理煩瑣，一向被試驗(yàn)人員所頭疼。利用Excel可以充分發(fā)揮其強(qiáng)大的制表、公式計(jì)算和繪圖功能（由于其圖形具有矢量化性能，從而為進(jìn)一步微觀查看圖表參數(shù)、修正試驗(yàn)誤差和補(bǔ)充試驗(yàn)數(shù)據(jù)提供了方便），通過圖形反推出趨勢(shì)線及趨勢(shì)方程，從而把圖解功能轉(zhuǎn)化為數(shù)解模塊，為試驗(yàn)人員準(zhǔn)確處理試驗(yàn)數(shù)據(jù)提供可行性，同時(shí)也減少了試驗(yàn)人員間的可比性和試驗(yàn)室誤差。而且用本方法所建立的表格系統(tǒng)完全可以模板化，試驗(yàn)人員僅需將所得原始數(shù)據(jù)，填入相應(yīng)的表格中即可生成圖表。生成的圖表只需稍作格式調(diào)整，即可以清晰方便地瀏覽并存檔，解決了長期以來困擾試驗(yàn)人員的一大難題。5注意事項(xiàng)由于擬合的二次方程僅在一定的