許小健，張金輪

(1.蕪湖市勘察測(cè)繪設(shè)計(jì)研究院有限責(zé)任公司,安徽 蕪湖 241000;2.安徽工程大學(xué) 建筑工程學(xué)院,安徽 蕪湖 241000)

內(nèi)摩擦角和粘聚力是土的抗剪切強(qiáng)度指標(biāo)。抗剪強(qiáng)度對(duì)地基土承載力確定、基坑支護(hù)設(shè)計(jì)、土坡穩(wěn)定性分析、數(shù)值模擬等具有重要的工程意義[1-4]。目前，巖土工程勘察工作主要依據(jù)《土工試驗(yàn)方法標(biāo)準(zhǔn)》(GB/T 50123—2019)中的直接剪切試驗(yàn)來(lái)確定抗剪強(qiáng)度參數(shù)，即在獲得基本試驗(yàn)數(shù)據(jù)后，采用 “視測(cè)直線”(即人工作圖法)確定試驗(yàn)結(jié)果。該方法的優(yōu)點(diǎn)是過(guò)程直觀，便于理解，容易被工程技術(shù)人員所接受；缺點(diǎn)是作圖過(guò)程因采用“視測(cè)”的方式，數(shù)據(jù)處理過(guò)程易受人為經(jīng)驗(yàn)影響[5]，處理效率不高，特別在面臨工程土樣多、任務(wù)緊迫、繪圖工作繁瑣、難以自動(dòng)化的情況下，結(jié)果往往會(huì)不盡如人意[6-7]。

工程技術(shù)人員和眾多學(xué)者經(jīng)過(guò)大量研究，提出了斜截式計(jì)算法[8]、最小二乘擬合法[9-10]、線性回歸法[11-12]、Excel圖表法[13-14]、Excel規(guī)劃求解法[14]、VB處理法[15]等數(shù)據(jù)處理方法。但以上方法或因不能直觀作圖，或因不能直接了解試驗(yàn)數(shù)據(jù)情況，存在方法通用性不強(qiáng)、數(shù)據(jù)處理和實(shí)現(xiàn)繪圖工作不能批量自動(dòng)化[4，15-16]等弊端，使得工程上的實(shí)際困難仍然不能得到有效解決。因此，本文給出了一種利用VBA(Visual Basic for Applications)編程快速批量確定直剪試驗(yàn)結(jié)果和實(shí)現(xiàn)自動(dòng)化繪圖的解決方案。該方案利用最小二乘原理，將人工作圖問(wèn)題轉(zhuǎn)化為數(shù)學(xué)求解問(wèn)題，再利用VBA編程結(jié)合Excel內(nèi)置工作表函數(shù)直接計(jì)算土的內(nèi)摩擦角和粘聚力，同時(shí)實(shí)現(xiàn)批量自動(dòng)化繪制試驗(yàn)成果曲線圖，使得整個(gè)處理過(guò)程高效、便捷，結(jié)果直觀、可靠。

1 基于最小二乘原理的規(guī)范作圖法解析

《土工試驗(yàn)方法標(biāo)準(zhǔn)》(GB/T 50123—2019)中規(guī)定直接剪切試驗(yàn)數(shù)據(jù)的處理方法為作圖法，即：以抗剪強(qiáng)度(S)為縱坐標(biāo)，垂直單位壓力(p)為橫坐標(biāo)，繪制S與p的關(guān)系曲線，再根據(jù)圖上試驗(yàn)數(shù)據(jù)點(diǎn)，繪制一視測(cè)的直線。所得直線的傾角為土的內(nèi)摩擦角(φ)，直線在縱坐標(biāo)上的截距為土的粘聚力(c)。

基于最小二乘原理[9-10]，將規(guī)范作圖法“視測(cè)直線”工作轉(zhuǎn)換為數(shù)學(xué)求解問(wèn)題，并利用VBA編程實(shí)現(xiàn)試驗(yàn)數(shù)據(jù)的批量自動(dòng)計(jì)算處理。由直剪試驗(yàn)過(guò)程，設(shè)每組試驗(yàn)n個(gè)(一般n=4)土樣的試驗(yàn)數(shù)據(jù)依次為(p1,S1)，(p2,S2),…,(pi,Si),…, (pn,Sn)。一般情況下，很難遇到試驗(yàn)點(diǎn)位于一條直線的特殊情況，但p和S的分布呈較強(qiáng)的線性關(guān)系，故庫(kù)倫公式用一條直線近似地反應(yīng)p和S的函數(shù)關(guān)系,即S=c+ptanφ。當(dāng)殘差平方和(SSE)達(dá)到最小值時(shí)，φ和c就是目標(biāo)所求值。

(1)

根據(jù)求極值方法，分別對(duì)c和φ求偏導(dǎo)數(shù)，并令其等于0，即可求得c和φ。

(2)

(3)

為解決土樣數(shù)據(jù)量大、任務(wù)緊迫、繪圖工作繁瑣、難以自動(dòng)化等問(wèn)題，可以通過(guò)自行編程實(shí)現(xiàn)式(2)和式(3)的求解。然而，對(duì)于非計(jì)算機(jī)專業(yè)的工程技術(shù)人員來(lái)說(shuō)，用C，C++，F(xiàn)ortran等語(yǔ)言進(jìn)行編程會(huì)比較困難，特別是在軟件界面與數(shù)據(jù)可視化編程方面。而VBA是一種簡(jiǎn)單易用的編程語(yǔ)言，提供了一種可視界面的設(shè)計(jì)方法。因此，用戶可以直接使用其控件設(shè)計(jì)應(yīng)用程序，極大地提高開發(fā)效率。

2 基于VBA批量自動(dòng)化處理數(shù)據(jù)

2.1 處理方法

考慮到Excel提供了豐富的工作表函數(shù)，可以方便快捷地進(jìn)行求解。為簡(jiǎn)化編程，本文在最小二乘編程部分采用VBA直接調(diào)用LINEST工作表函數(shù)求解φ和c。這樣可以最大限度地利用VBA編程的優(yōu)勢(shì)，節(jié)省精力、降低編程的復(fù)雜度。

LINEST 工作表函數(shù)使用最小二乘法計(jì)算與現(xiàn)有數(shù)據(jù)最佳擬合的直線，并返回描述此直線的數(shù)組。其函數(shù)調(diào)用語(yǔ)法為L(zhǎng)INEST(yValues,[xValues],[const],[stats])。函數(shù)調(diào)用參數(shù)如下：①yValues為必需參數(shù)，xValues為可選參數(shù)，分別為直線關(guān)系表達(dá)式y(tǒng)=mx+b中已知的y值和x值的集合，對(duì)于直剪試驗(yàn)來(lái)說(shuō)，相當(dāng)于S和p的集合。②const為可選參數(shù)，用于指定是否將常量b強(qiáng)制設(shè)為0。如果const為TRUE或被省略，b將按通常方式計(jì)算；如果const為FALSE，b將被設(shè)為0，并同時(shí)調(diào)整m值。若在試驗(yàn)中因存在受人為因素影響、土的不均勻性等特殊情況而出現(xiàn)c為負(fù)值時(shí)[6,14]，可以強(qiáng)制c=0。③stats為可選參數(shù)，用于指定是否返回附加回歸統(tǒng)計(jì)值。如果stats為TRUE，則LINEST函數(shù)返回附加回歸統(tǒng)計(jì)值的數(shù)組，包括斜率、截距、判定系數(shù)r2(為回歸平方和/殘差平方和)、殘差平方和SSE等。

2.2 程序?qū)崿F(xiàn)

VBA內(nèi)嵌于Excel中，與Excel界面的風(fēng)格統(tǒng)一，且其與Excel LINEST工作表函數(shù)的交互極為方便，通過(guò)Application.WorksheetFunction.LinEst(sValues,pValues,False,True)即可實(shí)現(xiàn)調(diào)用，避免了最小二乘法的自行編程，極大地方便了技術(shù)人員。

程序運(yùn)行時(shí)，在Excel的菜單欄形成一個(gè)“直接剪切試驗(yàn)”菜單項(xiàng)，并項(xiàng)包含若干功能選項(xiàng)。由于Office軟件自帶應(yīng)用程序開發(fā)工具VBE(Visual Basic Editor)，即編寫、運(yùn)行和存放VBA程序的容器，故可以降低編程開發(fā)門檻。在VBE界面代碼框內(nèi)，執(zhí)行相應(yīng)源程序，即可實(shí)現(xiàn)相應(yīng)功能。

1)“數(shù)據(jù)計(jì)算”模塊程序代碼及注釋如下。

Private Sub DirectCompute(control As IRibbonControl)

Dim sValues():Dim arrLs():Dim pValues()

DimiAs Integer,nRows As Integer,kAs Single,cAs Single

Constpi=3.14159

nRows=Worksheets("DirectShearTest").UsedRange.Rows.Count

Fori=2 To nRows Step 2

pValues=Range("C" &i& ":F" &i).Value

sValues=Range("C" &i+ 1 & ":F" &i+ 1).Value

'直接通過(guò)VBA調(diào)用Excel工作表函數(shù)LinEst函數(shù),返回附加回歸統(tǒng)計(jì)值到arrLs數(shù)組

arrLs=Application.WorksheetFunction.LinEst(sValues,pValues,，True)

'斜率和截距

k=Application.WorksheetFunction.Index(arrLs,1,1)

c=Application.WorksheetFunction.Index(arrLs,1,2)

'粘聚力為負(fù)值處理

Ifc<0 Then

arrLs=Application.WorksheetFunction.LinEst(sValues,pValues,False,True)

End If

'內(nèi)摩擦角度數(shù)

Range("G" &i).Value=Format(Atn(k)*180/pi,"0.00")

'粘聚力(直線截距)

Range("H" &i).Value=Format(Application.WorksheetFunction.Index(arrLs,1,2), "0.00")

失禁性皮炎是一種皮膚炎性反應(yīng)，因皮膚在尿液和(或)糞便中的長(zhǎng)期暴露而形成，輕則皮膚發(fā)紅糜爛，出現(xiàn)水泡滲液，重則皮膚出現(xiàn)二重感染，失禁、營(yíng)養(yǎng)不良、制動(dòng)、肌松鎮(zhèn)靜藥使用等情況，普遍存在使ICU患者成為失禁性皮炎高危人群[1-5]。出現(xiàn)失禁性皮炎，不但使壓瘡、感染等并發(fā)癥風(fēng)險(xiǎn)顯著上升，而且還會(huì)增加患者身心痛苦程度[6]。2018年1～6月，我們對(duì)收治的40例ICU護(hù)理單元患者實(shí)施皮膚護(hù)理可行性處置策略，效果滿意?，F(xiàn)報(bào)告如下。

'判定系數(shù)r^2

Range("I" &i).Value=Format(Application.WorksheetFunction.Index(arrLs,3,1),"0.0000")

'殘差平方和SSE

Range("J" &i).Value=Format(Application.WorksheetFunction.Index(arrLs,5,2),"0.00")

Range("K" &i)="數(shù)據(jù)離散性偏大,建議重做試驗(yàn)"

End If

Next

End Sub

2)“批量導(dǎo)出剪切試驗(yàn)成果圖”功能模塊主要程序代碼及注釋如下。

Private Sub ExportPNG(control As IRibbonControl)

DimiAs Integer,nRows As Integer

Constpi=3.14159

nRows=Worksheets("DirectShearTest").UsedRange.Rows.Count

'批量調(diào)整數(shù)據(jù)源并自動(dòng)化繪制關(guān)系圖

Fori=2 To nRows Step 2

With Worksheets("DirectShearTest").ChartObjects("shearTestChart").Chart

.ChartTitle.Text="試樣編號(hào):"& Range("A" &i).Value

.Axes(xlValue).MinimumScaleIsAuto=True

.Axes(xlValue).MaximumScale=300

'繪制試驗(yàn)數(shù)據(jù)點(diǎn)

.SeriesCollection(1).XValues=Range("C"&i&":F"&i).Value

.SeriesCollection(1).Values=Range("C" &i+1&":F"&i+1).Value

'繪制關(guān)系直線,也即"視測(cè)直線"

.SeriesCollection(2).XValues=Array(0,Range("F"&i).Value+50)

.SeriesCollection(2).Values=Array(Range("H"&i).Value,Range("H"&i).Value+_

(Range("F"&i).Value+50)*Tan(Range("G"&i).Value*pi/180))

'導(dǎo)出垂直壓力與抗剪強(qiáng)度試驗(yàn)關(guān)系圖為PNG文件

.Export ThisWorkbook.Path&"/"&Range("A"&i).Value &".png"

End With

Next

End Sub

3 算例

3.1 算例1

各土樣的原始試驗(yàn)數(shù)據(jù)及計(jì)算成果見表1。打開Excel后，默認(rèn)自動(dòng)選擇Excel功能區(qū)的“直接剪切試驗(yàn)”選項(xiàng)卡，“初始化”后，在Excel電子表格中按表1格式在A～F列中輸入由直剪試驗(yàn)所測(cè)得的原始試驗(yàn)數(shù)據(jù)，其中第1行在“初始化”中自動(dòng)生成；點(diǎn)擊“數(shù)據(jù)計(jì)算”，程序會(huì)自動(dòng)批量完成數(shù)據(jù)處理，并在Excel表格G～J列中自動(dòng)填充結(jié)果。經(jīng)過(guò)對(duì)比，soil1，soil2試驗(yàn)數(shù)據(jù)的處理成果與文獻(xiàn)[5]和文獻(xiàn)[14]一致；soil3，soil4的處理成果也與項(xiàng)目試驗(yàn)報(bào)告結(jié)果一致，驗(yàn)證了本方法的有效性。

表1 各土樣的原始試驗(yàn)數(shù)據(jù)及計(jì)算成果

點(diǎn)擊“批量導(dǎo)出剪切試驗(yàn)成果圖”，程序會(huì)自動(dòng)批量繪制并保存曲線圖。各土樣試驗(yàn)成果圖如圖1所示。整個(gè)操作過(guò)程實(shí)現(xiàn)了一次性輸入，一次點(diǎn)擊，一次批量輸出結(jié)果，便捷、高效，無(wú)需人工進(jìn)行調(diào)整，排除了人為因素干擾。

(a) soil1 (b) soil2 (c) soil3 (d) soil4

3.2 算例2

再以文獻(xiàn)[12]中的5組土樣為例，通過(guò)程序輸出結(jié)果，與作圖法和回歸計(jì)算法進(jìn)行比較，各方法求得結(jié)果比較見表2。從表2可以看出，對(duì)于1-2土樣，通過(guò)本文VBA編程LINEST工作表函數(shù)實(shí)現(xiàn)了c為負(fù)值情況的強(qiáng)制處理，結(jié)果與作圖法接近；對(duì)于7-1土樣，3種方法的計(jì)算結(jié)果差異稍大，但本文VBA法所得SSE=395.7270，小于文獻(xiàn)[12]作圖法所得SSE=478.3963和回歸計(jì)算法所得SSE=415.5681。除1-2土樣和7-1土樣以外，本文所得結(jié)果與回歸計(jì)算法近乎一致，比作圖法偏差較小，而且比回歸計(jì)算法更為客觀，無(wú)人為因素干擾的影響。

表2 各方法求得結(jié)果比較

4 結(jié)論

1)利用最小二乘原理，將作圖法轉(zhuǎn)換為數(shù)學(xué)求解問(wèn)題，利用Excel電子表格的快速數(shù)據(jù)輸入、VBA編程的批量自動(dòng)化繪制成果圖等特點(diǎn)，克服了人為因素的影響，改變了人工作圖法數(shù)據(jù)處理效率較低且繁雜的劣勢(shì)。

2)實(shí)現(xiàn)了與Excel風(fēng)格統(tǒng)一的可視化程序界面，程序界面美觀、方便易用；實(shí)現(xiàn)了通過(guò)簡(jiǎn)單點(diǎn)擊操作就可以批量自動(dòng)化處理數(shù)據(jù)和繪制成果圖。

3)基于VBA的巖土直剪試驗(yàn)數(shù)據(jù)處理方法是一種精確、高效、直觀的數(shù)據(jù)處理和圖表繪制解決方案，對(duì)于提高試驗(yàn)和工程技術(shù)人員的工作效率有較好的作用。相較于傳統(tǒng)編程語(yǔ)言，VBA編程門檻較低，易于工程技術(shù)人員掌握和推廣應(yīng)用。