Excel內(nèi)部函數(shù)求解界限含水率試驗解析法研究

趙秀紹，莫林利，劉 巍，艾成剛

（1.華東交通大學土木建筑學院，江西南昌 330013；2.華東交通大學軟件學院，江西南昌 330013）

界限含水率試驗是鐵路路基填土工程、承載力計算中不可缺少的重要試驗項目，試驗計算結果的準確與否直接關系到地基承載力計算的準確性及判斷材料能否應用的問題。所以除了在試驗過程中要嚴格控制各項操作的準確性外，數(shù)據(jù)計算的準確性也尤為重要。采用手工作圖法處理界限含水率試驗數(shù)據(jù)，不僅效率低，試驗結果的精度也難以保證，在土工實驗教學中也不方便對學生計算的結果進行判定，因此許多學者提出了多種解法。韓繼軍［1］通過Excel突出的圖表功能，利用圖表法查值獲得的土的液塑限值。李健利［2］巧妙靈活地使用Excel提供的格式化功能，使生成的數(shù)據(jù)表格或圖形更加美觀、清晰。賀濤［3］綜合利用Excel強大的數(shù)據(jù)處理功能和AutoCAD強大的繪圖功能，提出了一種方便快捷而準確的處理數(shù)據(jù)的新方法，且采用AutoLISP進行編程，以提高數(shù)據(jù)處理的速度和精度。呂雅麗［4］依據(jù)（JTG E40—2007）《公路土工試驗規(guī)程》，以Excel為工具，編寫VBA 程序，計算試驗結果及繪圖，使求解效率大為提高，但VBA 程序相對復雜，對一般用戶不易操作。殷春娟［5］提出了用Excel表格處理界限含水率的方法，彭意［6］、馮朝輝［7］、張傳邦［8］提出了公式法計算界限含水率的方法，高盟［9］提出了用樣條函數(shù)求解的方法。這些方法大大地提高了作圖法計算的效率與精度。

研究現(xiàn)狀表明，目前研究多采用軟件編程方式和圖形聯(lián)合方式求解，要求研究者具有較高的計算機水平，因而在工程人員中較難擴廣使用。所以在求解界限含水率試驗中，使計算結果精確而唯一、處理方法簡單而實用是工程中亟待解決的問題。實踐表明，Excel內(nèi)部函數(shù)在處理土工試驗數(shù)據(jù)方面功能強大，可以簡單且完全自動化地計算界限含水率而不依賴于圖形繪制和程序編寫。

1 界限含水率試驗求解的基本原理

依據(jù)《TB10102—2004 鐵路工程土工試驗規(guī)程》［10］或《土工試驗方法標準》［11］，以含水率w 為橫坐標，錐尖入土深度h為縱坐標，在雙對數(shù)坐標紙上繪制log h與log w 曲線，3 組數(shù)據(jù)（a、b、c）應連成一條直線。3點不在一條直線上時，連接ab和ac 并延長（見圖1），與h＝2mm 直線交點為d1、d2，這2個交點的橫坐標之差（wac－wab）稱為塑限差。當塑限差△wp＜2%時，取d1和d2點的中點d，則ad 就是下文中線性關系式（1）所繪制的直線，此直線與2mm 橫線的交點對應的橫坐標即為塑限wp，與10 mm 交點e的橫坐標即為10mm 液限wL。當△wp≥2%時說明試驗差距過大，應重做試驗。

因為鐵路規(guī)范作圖法中是估讀d、e點的橫坐標作為土的塑限和液限，因此不同的試驗者有不同的估讀值，造成土的界限含水率的不確定性。利用Excel內(nèi)部函數(shù)，且不用復雜VBA 編程就實現(xiàn)界限含水率的計算是本文要解決的問題。

2 基于Excel內(nèi)部函數(shù)求解土的界限含水率的原理與設計

2.1 Excel求解的函數(shù)原理

圖1 界限含水率原理圖

在圖1的計算原理圖中，a、b、c各點的坐標分別為（log wa，log ha）、（log wb，log hb）、（log wc，log hc），log（）函數(shù)在Excel中默認為10為底的對數(shù)函數(shù)。d1點就是ab直線與縱坐標為2mm 水平線的交點，也可以認為d1點是直線ab 的插值點，這樣就可以方便地利用Excel中的線性插值函數(shù)Trend（）。Trend（）函數(shù)的語法為

TREND（known＿y’s，known＿x’s，new＿x’s，const）

參數(shù):known＿y’s為已知關系y＝mx＋n 中的y值集合，known＿x’s為已知關系y＝mx＋n 中可選的x 值的集合，new＿x’s為需要函數(shù)TREND 返回對應y 值的新x 值，const為邏輯值指明是否強制常數(shù)項n為0。

TREND（）函數(shù)用途:返回一條線性回歸擬合線的一組縱坐標值（y 值）。即找到適合給定的數(shù)組known＿y’s和known＿x’s的直線，并返回指定數(shù)組new＿x’s值在直線上對應的y 值。

根據(jù)TREND（）函數(shù)的語法與界限含水率求解問題，令含水率的對數(shù)值log w 引用組合作為known＿y’s，log h引用組合作為known＿x’s，利用ab延長線與h＝2mm 的交點來求解new＿x’s＝log（2）對應的含水率，即為d1點的橫坐標值，即已知（log wa，log ha）和（log wb，log hb）求解d1坐標（log wab，log 2）。由TREND（）函數(shù)直接求得結果為log wab，故有

式中｛log wa，log wc｝為Excel數(shù)組表達形式。

函數(shù)參數(shù)known＿y’s和known＿x’s必須為一個引用集或數(shù)組，因此要實現(xiàn)以上算法，必須使用輔助單元格。

2.2 Excel求解土的界限含水率的設計

如表1所示，輔助單元格為B1—B6 及C1—C6。B列和C列放入各測點的對數(shù)值（若通過輸入公式計算“l(fā)og（h）”，其中a、c點和a、b 點分別放在如表1所示的位置。則在D2 單元格輸入公式“＝10＾TREND（C2:C3，B2:B3，log（2））”，其中TREND（C2:C3，B2:B3，log（2））求得d1點的坐標為（log wd1，log 2），D4單元格完整的解釋為直線ab 確定d1點的含水率，同理可求得d2點對應的塑限含水率。則d1和d2含水率的平均值即為土的塑限wp，含水率之差即為塑限差△wp，用于判斷塑限差是否超過2%，超過2%則需要重新試驗。

為了求解10mm 液限或17mm 液限，需要由a、d兩點重新組成單元格集合，放在B6、B7和C6、C7的單元格中。根據(jù)求塑限的方法，用TREND（）函數(shù)可求得與10mm 和17mm 橫線的交點，從而求得10mm液限與17mm 液限，具體公式參照表1中單元格D11和D12。

表1 Excel求解的相關公式

表1中的公式是完全參照規(guī)范法得到的，而且可以通過塑限差△wp是否超過2%來判斷試驗的正確性。

3 回歸分析方法的基本原理

根據(jù)文獻［12］可得:在液限和塑限聯(lián)合測定的試驗中，錐尖入土深度h（mm）與含水率w（%）之間存在雙對數(shù)線性關系:

式中m 和n 是由試驗確定的常數(shù)。

根據(jù)以上原理，a、b、c3點的對數(shù)值應處于一條直線上。根據(jù)以往的研究經(jīng)驗，一般采用的方法是在Excel中用直線擬合出a、b、c3點，擬合時對話框選擇“顯示公式”和“相關系數(shù)”，在實際應用時還需人工從擬合直線圖中讀取公式中的m、n 才能進行界限含水率的計算。

Excel內(nèi)部函數(shù)提供了最小二乘法回歸計算的方法，與此相關的函數(shù)為截距函數(shù)INTERCEPT（）可求得n值，SLOPE（）函數(shù)可求得m 值，CORREL（）函數(shù)可求得相關系數(shù)。根據(jù)公式（1）可得任意錐尖入土深度h對應的含水率公式為

式中:n為擬合直線的截距，用INTERCEPT（logh 數(shù)組或引用域，log w 數(shù)組或引用域）求解；m 為擬合直線的斜率，用SLOPE（logh 數(shù)組或引用域，logw 數(shù)組或引用域）求解。

把h＝2mm、10mm、17mm 代入式（2），即可求得塑限和液限，其相應的計算公式如表2所示。

表2 直線擬合法求解界限含水率公式設計

直線擬合法是根據(jù)界限含水率的原理得出的，其采用了最小二乘法回歸分析，可以把試驗點的誤差降到最小，但無法完成規(guī)范中的超差計算，雖然有相關系數(shù)，但相關系數(shù)不能作為試驗偏差的判斷依據(jù)。直線擬合法也有其優(yōu)點，其計算點可以超過3個，只要修改公式中的引用范圍就可擬合更多的數(shù)據(jù)點，如改成INTERCEPT（A2:A6，B2:B6）即要計算5個點，從而減小由于a點的偏差引起的偶然誤差。

4 兩種方法的求解對比及工程應用

為了驗證Excel界限含水率的公式設計和對比規(guī)范法和擬合法的差別，選取江西南昌市孔目湖取土點和江西高安市八景鎮(zhèn)取土點兩地土樣作為典型實例，其中孔目湖取土樣進行了平行試驗，其計算結果如表3和表4所示。表3和表4中的Δwp為規(guī)范法結果，R2為擬合法得到的相關系數(shù)平方值，201、202、204組合為平行試驗。

表3 江西南昌孔目湖取土點界限含水率計算

表4 江西高安八景鎮(zhèn)取土點界限含水率計算

根據(jù)表3和表4的計算結果看，其塑限和液限與作圖法求得的數(shù)值相符，驗證了Excel公式設計的正確性。

根據(jù)表3和表4，在塑限和液限的差值比較時，10 mm 液限差值最小，一般可以忽略，塑限引起的偏差最大。當塑限差△wp＜2時，規(guī)范法和擬合法求得的塑限最大差值為0.577，可以滿足要求，因此兩種方法均可使用。當塑限差△wp≥2時，規(guī)范法和直線擬合法計算的塑限差值可達1.158，此時最好依據(jù)塑限差重新做試驗。

5 結論

Excel軟件易學易用，特別適合于計算機基礎較弱的土工試驗人員使用，同時方便實驗室教師對學生試驗結果的判定。文中詳細介紹了基于Excel內(nèi)部函數(shù)的界限含水率求解方法，通過實踐可得出如下結論:

（1）Excel內(nèi)部線性插值函數(shù)Trend（）、截距函數(shù)INTERCEPT（）、斜率函數(shù)SLOPE（）、相關系數(shù)函數(shù)CORREL（）可以有效地解決界限含水率的求解問題，避免了復雜的程序編寫和圖形繪制，當制作好模板后可以大量減少計算工作量，方便工程人員的應用。

（2）規(guī)范法采用TREND（）函數(shù)實現(xiàn)求解是嚴格按照鐵路規(guī)范的方法進行求解，其塑限差可以作為判斷試驗好壞的依據(jù)，但當a 點有試驗偏差時可能引起整個計算出現(xiàn)偏差。

（3）規(guī)范法和直線擬合法兩種方法求得的液限差值可以忽略，而塑限差值較大。當塑限差△wp＜2時，規(guī)范法和直線擬合法求得的塑限差值較小，可以滿足要求，兩種方法均可使用；當塑限差△wp≥2時，規(guī)范法和直線擬合法計算的塑限差值可超過1%，此時最好依據(jù)△wp的要求重新做試驗。

（

）

［1］韓繼軍.Excel電子表格在土工試驗中的應用［J］.重慶科技學院學報，2010，12（6）:121，134.

［2］李健，陳國強.Excel在土工試驗資料整理中的應用［J］.電力勘測設計，2011，18（2）:8－10.

［3］賀濤，杜勇立.液塑限試驗精度分析及其數(shù)據(jù)處理新方法［J］.公路與汽運，2009，25（3）:104－106.

［4］呂雅麗，曹曄.Excel 2010在界限含水率試驗數(shù)據(jù)處理中的應用［J］.內(nèi)蒙古公路與運輸，2010，35（3）:10－12.

［5］殷春娟，崔永高，鄭大龍.液塑限聯(lián)合測定法的若干問題探討［J］.上海地質，2007，28（1）:64－66.

［6］彭意，李鈾，彭強.提高液塑限聯(lián)合測定試驗精度的方法［J］.土工基礎，2007，21（4）:81－84.

［7］馮朝輝，彭意.聯(lián)合測定法中液塑限含水率的求?。跩］.湖南理工學院學報:自然科學版，2006，19（2）:85－87.

［8］張傳邦.計算法求土的液限及塑限界限含水量［J］.路基工程，2006，23（3）:28－29.

［9］高盟，張遠芳.樣條函數(shù)在液塑限聯(lián)合試驗成果確定中的應用［J］.水利與建筑工程學報，2005，3（3）:8－10.

［10］TB 10102—2004鐵路工程土工試驗規(guī)程［S］.北京:中國鐵道出版社，2004.

［11］GB／T50123—1999土工試驗方法標準［S］.北京:中國建筑工業(yè)出版社，1999.

［12］南京水利科學研究院土工研究所.土工試驗技術手冊［M］.北京:人民交通出版社，2003:27－28.