擊實試驗數(shù)據(jù)的Excel處理方法

PAGEPAGE1擊實試驗數(shù)據(jù)的Excel處理方法李法俊田志峰(濰坊市公路管理局，山東濰坊261031)【摘要】本文對使用Excel進(jìn)行擊實試驗數(shù)據(jù)處理的方法作了詳細(xì)介紹，并對所采用的插值多項式的性質(zhì)進(jìn)行了討論。應(yīng)用Excel的高階多項式插值方法，可以得到精確的多項式插值趨勢線，并可以直接在Excel圖表坐標(biāo)系中讀出擊實曲線對應(yīng)的最大干密度和最佳含水量。該方法消除了手工繪圖的誤差，同時避免了程序設(shè)計的繁瑣，有較高的工程應(yīng)用價值?！娟P(guān)鍵詞】Excel擊實試驗數(shù)據(jù)插值趨勢線最大干密度最佳含水量1概述公路工程路基土方施工中，填土的最佳含水量和最大干密度是非常重要的指標(biāo)，直接影響著路基的工程質(zhì)量和經(jīng)濟(jì)效益。所以必須要保證擊實試驗結(jié)果的準(zhǔn)確性，而擊實試驗數(shù)據(jù)的插值處理是其中的關(guān)鍵。目前，擊實試驗所得到的試件的含水量與干密度的數(shù)據(jù)，通常的處理方法是在米格紙上手工描點，根據(jù)經(jīng)驗手工繪制關(guān)系曲線，然后在坐標(biāo)系下讀出最佳含水量和最大干密度。這種方法的優(yōu)點是簡便易行，較為直觀。缺點是工作效率低、人為因素影響較大、處理結(jié)果精度不高。為提高擊實試驗數(shù)據(jù)的處理精度，人們相繼開發(fā)了一些程序，有大型的試驗室通用數(shù)據(jù)處理程序，有小型的擊實試驗專用程序，這些程序使用樣條函數(shù)插值的方法，或多項式插值的方法，采用了C++語言、Auto-lisp語言等進(jìn)行開發(fā)，計算精度得到了大幅提高。但是這種方法缺點也是很明顯的：大型程序需要購買，小型程序的開發(fā)需要一定的數(shù)學(xué)基礎(chǔ)和計算機(jī)語言基礎(chǔ)，形成了該項技術(shù)推廣的瓶頸，限制了其在工程建設(shè)中的應(yīng)用。利用Excel對擊實試驗數(shù)據(jù)進(jìn)行高階多項式插值處理的方法將擊實試驗數(shù)據(jù)的中間計算、含水量—干密度關(guān)系曲線的繪制及擊實試驗表格的輸出一并實現(xiàn)，該方法簡便易行，精確快捷，保留了手工繪圖和編程計算的優(yōu)點，同時避免了二者的缺點，是一種使用方便的、低成本、高效率的方法。2Excel表格的創(chuàng)建與數(shù)據(jù)的輸入首先新建Excel文件，參照常用的擊實試驗記錄表制作Excel工作表，然后根據(jù)規(guī)范規(guī)定的含水量、干密度計算公式定義相應(yīng)單元格的公式，輸入各組試驗的原始數(shù)據(jù)（筒加土重、筒重、盒加濕土重、盒加干土重、盒重），Excel可以自動計算出各組試件的干密度和含水量。3繪制擊實試驗數(shù)據(jù)的插值曲線3.1首先選取干密度一行的5個（或6個）單元格，然后單擊常用工具欄的，選取“XY散點圖”，在選取“子圖表類型”中的“平滑線散點圖”。如圖1：圖1選取圖形類別3.2單擊“下一步”，進(jìn)入（圖表向?qū)?4步驟之2-圖表源數(shù)據(jù)）。如圖2：圖2選擇圖表源數(shù)據(jù)（一）3.3單擊“系列”標(biāo)簽，單擊“X值”后的數(shù)據(jù)框，再選取含水量一行的5個（或6個）單元格，如圖3：圖3選擇圖表源數(shù)據(jù)（二）3.4單擊“下一步”，進(jìn)入（圖表向?qū)?4步驟之3-圖表選項）。如圖4：圖4調(diào)整圖表選項3.5單擊“圖表向?qū)?4步驟之3-圖表選項”的“標(biāo)題”標(biāo)簽，輸入“數(shù)值X軸”的標(biāo)題為“含水量ω（%）”，輸入“數(shù)值Y軸”的標(biāo)題為“干密度ρd(g/cm3)”；單擊“圖表向?qū)?4步驟之3-圖表選項”的“網(wǎng)格線”標(biāo)簽，將數(shù)值（X）軸、數(shù)值（Y）軸的主要網(wǎng)格線多選框中打√；單擊“圖表向?qū)?4步驟之3-圖表選項”的“圖例”標(biāo)簽，將顯示圖例前的單選框中取消打√；其他選項不動。3.6單擊“下一步”，進(jìn)入“圖表向?qū)?4步驟之4-圖表位置”。如圖5：圖5設(shè)定圖表位置3.7選取“作為其中的對象插入”，然后單擊“完成”，生成的圖表便插入到當(dāng)前的Excel工作表中了。生成的圖表如圖6：圖6生成的XY散點圖3.8將生成的圖表移動位置到試驗記錄表格的圖表預(yù)留區(qū)并調(diào)整其大小使之與試驗記錄表相協(xié)調(diào)。3.9用鼠標(biāo)右鍵單擊圖表上的任一數(shù)據(jù)點，在彈出的快捷菜單中單擊“添加趨勢線”，出現(xiàn)如下窗口，單擊“多項式”，調(diào)整“階數(shù)”為6。如圖7：圖7選擇趨勢線格式3.10單擊“確定”，便可得到插值后的趨勢線。如圖8：圖8生成的趨勢線3.12用鼠標(biāo)右鍵單擊圖表區(qū)任一坐標(biāo)數(shù)值，根據(jù)實際情況設(shè)置坐標(biāo)格式，使圖表區(qū)更加美觀。調(diào)整后的趨勢線如圖9：圖9調(diào)整后的趨勢線3.13從圖表中趨勢線的極大值點便可以讀取最佳含水量和最大干密度的數(shù)值。也可以按下述方法由趨勢線上自動讀出極值點的縱橫坐標(biāo)：鼠標(biāo)右鍵單擊圖表區(qū)空白部分，單擊“源數(shù)據(jù)”，單擊“添加”，單擊“X值”后的數(shù)據(jù)框，單擊表格中需要輸出的最佳含水量數(shù)值所在的單元格，單擊“Y值”后的數(shù)據(jù)框，刪掉其中的內(nèi)容，然后單擊表格中需要輸出的最佳干密度數(shù)值所在的單元格，單擊“確定”，可以看到圖表區(qū)生成了另一種顏色的的一個坐標(biāo)點（如果看不到該坐標(biāo)點，先假定一個近似的最佳含水量值和一個近似的最大干密度值）。生成的新序列散點如圖10：圖10生成新的序列散點單擊新生成的序列散點，當(dāng)光標(biāo)變成時，移動該點至趨勢線峰值點處，此時該散點對應(yīng)的兩個單元格的數(shù)值就是最佳含水量、最大干密度值。3.14我們還可以用添加誤差線的方法從峰值點處向X、Y坐標(biāo)軸引出一條水平線和一條鉛垂線與坐標(biāo)軸相交，從而更加方便地讀出趨勢線峰值點對應(yīng)的縱、橫坐標(biāo)。步驟為：鼠標(biāo)右鍵單擊已經(jīng)位于峰值點處的新的序列散點，在彈出的快捷菜單中單擊數(shù)據(jù)系列格式，單擊“誤差線X”標(biāo)簽，單擊“負(fù)偏差”，將“誤差量”的“百分比”調(diào)整為100%；單擊“誤差線Y”標(biāo)簽，單擊“負(fù)偏差”，將“誤差量”的“百分比”調(diào)整為100%。如圖11：圖11添加誤差線3.15單擊“確定”，便由峰值點處引出了一條水平線和一條鉛垂線與縱橫坐標(biāo)軸相交，使得在圖表中讀書更加直觀、清晰。鼠標(biāo)右鍵單擊誤差線，可以調(diào)整誤差線格式。生成的誤差線如圖12：圖12生成的誤差線4關(guān)于插值方程的討論根據(jù)《公路土工試驗規(guī)程》的有關(guān)規(guī)定，在取得干密度、含水量的數(shù)據(jù)后，繪制一條干密度—含水量關(guān)系曲線通過所有的試驗數(shù)據(jù)點，曲線上峰值點的縱、橫坐標(biāo)分別為最大干密度和最佳含水量。與擊實試驗規(guī)程的規(guī)定相對應(yīng)的數(shù)值分析方法就是插值，常用的插值方法為Lagrange多項式插值。根據(jù)Lagrange插值多項式的定義可知其性質(zhì)為：Lagrange插值多項式的存在是唯一的。也就是說通過已知的數(shù)據(jù)點只能做出唯一的一條曲線滿足Lagrange插值函數(shù)的定義。Lagrange插值多項式對應(yīng)的曲線通過所有的離散數(shù)據(jù)點，相關(guān)系數(shù)等于1。用鼠標(biāo)右鍵單擊圖表上的任一數(shù)據(jù)點，單擊彈出的快捷菜單中的“添加趨勢線”，再單擊“選項”按鈕，選擇“顯示R平方值”可以驗證插值多項式的相關(guān)系數(shù)等于1。對于擊實試驗數(shù)據(jù)來說，Lagrange插值多項式的階數(shù)通常為試驗數(shù)據(jù)的個數(shù)減去1，即當(dāng)試驗數(shù)據(jù)個數(shù)為5時，相應(yīng)的Lagrange插值多項式的階數(shù)為4，這時候干密度—含水量的關(guān)系表達(dá)式為：ρ(ω)=a0+a1ω1+a2ω2+a3ω3+a4ω4其中ρ(ω)為當(dāng)含水量為ω時的干密度，ω為含水量，a0、a1、a2、a3、a4為插值系數(shù)。如前文所述，之所以選擇多項式趨勢線的階數(shù)為6，是因為該選項的選擇上限為6，這時候干密度—含水量的關(guān)系表達(dá)式為：ρ(ω)=a0+a1ω1+a2ω2+a3ω3+a4ω4+a5ω5+a6ω6當(dāng)試驗數(shù)據(jù)的個數(shù)為5個時，經(jīng)Excel計算得到的a5=a6=0，干密度—含水量的關(guān)系表達(dá)式實際上仍然為：ρ(ω)=a0+a1ω1+a2ω2+a3ω3+a4ω4用鼠標(biāo)右鍵單擊圖表上的任一數(shù)據(jù)點，單擊彈出的快捷菜單中的“添加趨勢線”，再單擊“選項”按鈕，選擇“顯示公式”可以驗證插值多項式的階數(shù)規(guī)律。5結(jié)語利用Excel處理擊實試驗數(shù)據(jù)，可以充分發(fā)揮其強大的制表、公式計算和繪圖功能，并通過得到的插值曲線讀出最佳含水量和最大干密度，為試驗人員精確處理試驗數(shù)據(jù)提供了方便。利用Excel建立的表格還可以作為模板，試驗人員僅需將所得的原始數(shù)據(jù)填入表格中即可立即生成擊實試驗記錄表（如圖13）。類似地，利用該方法還可以對其他試驗數(shù)據(jù)進(jìn)行插值處理。圖13打印出的擊實試驗記錄表參考文獻(xiàn)1陳永利·巧用Excel軟件處理擊實試驗數(shù)據(jù)并繪制曲線效果圖·工程地質(zhì)計算機(jī)應(yīng)用·2004年第1期