基于Excel VBA的岩土直接剪切试验数据处理方法

许小健，张金轮

(1.芜湖市勘察测绘设计研究院有限责任公司,安徽 芜湖 241000;2.安徽工程大学 建筑工程学院,安徽 芜湖 241000)

内摩擦角和粘聚力是土的抗剪切强度指标。抗剪强度对地基土承载力确定、基坑支护设计、土坡稳定性分析、数值模拟等具有重要的工程意义[1-4]。目前，岩土工程勘察工作主要依据《土工试验方法标准》(GB/T 50123—2019)中的直接剪切试验来确定抗剪强度参数，即在获得基本试验数据后，采用 “视测直线”(即人工作图法)确定试验结果。该方法的优点是过程直观，便于理解，容易被工程技术人员所接受；缺点是作图过程因采用“视测”的方式，数据处理过程易受人为经验影响[5]，处理效率不高，特别在面临工程土样多、任务紧迫、绘图工作繁琐、难以自动化的情况下，结果往往会不尽如人意[6-7]。

工程技术人员和众多学者经过大量研究，提出了斜截式计算法[8]、最小二乘拟合法[9-10]、线性回归法[11-12]、Excel图表法[13-14]、Excel规划求解法[14]、VB处理法[15]等数据处理方法。但以上方法或因不能直观作图，或因不能直接了解试验数据情况，存在方法通用性不强、数据处理和实现绘图工作不能批量自动化[4，15-16]等弊端，使得工程上的实际困难仍然不能得到有效解决。因此，本文给出了一种利用VBA(Visual Basic for Applications)编程快速批量确定直剪试验结果和实现自动化绘图的解决方案。该方案利用最小二乘原理，将人工作图问题转化为数学求解问题，再利用VBA编程结合Excel内置工作表函数直接计算土的内摩擦角和粘聚力，同时实现批量自动化绘制试验成果曲线图，使得整个处理过程高效、便捷，结果直观、可靠。

1 基于最小二乘原理的规范作图法解析

《土工试验方法标准》(GB/T 50123—2019)中规定直接剪切试验数据的处理方法为作图法，即：以抗剪强度(S)为纵坐标，垂直单位压力(p)为横坐标，绘制S与p的关系曲线，再根据图上试验数据点，绘制一视测的直线。所得直线的倾角为土的内摩擦角(φ)，直线在纵坐标上的截距为土的粘聚力(c)。

基于最小二乘原理[9-10]，将规范作图法“视测直线”工作转换为数学求解问题，并利用VBA编程实现试验数据的批量自动计算处理。由直剪试验过程，设每组试验n个(一般n=4)土样的试验数据依次为(p1,S1)，(p2,S2),…,(pi,Si),…, (pn,Sn)。一般情况下，很难遇到试验点位于一条直线的特殊情况，但p和S的分布呈较强的线性关系，故库伦公式用一条直线近似地反应p和S的函数关系,即S=c+ptanφ。当残差平方和(SSE)达到最小值时，φ和c就是目标所求值。

(1)

根据求极值方法，分别对c和φ求偏导数，并令其等于0，即可求得c和φ。

(2)

(3)

为解决土样数据量大、任务紧迫、绘图工作繁琐、难以自动化等问题，可以通过自行编程实现式(2)和式(3)的求解。然而，对于非计算机专业的工程技术人员来说，用C，C++，Fortran等语言进行编程会比较困难，特别是在软件界面与数据可视化编程方面。而VBA是一种简单易用的编程语言，提供了一种可视界面的设计方法。因此，用户可以直接使用其控件设计应用程序，极大地提高开发效率。

2 基于VBA批量自动化处理数据

2.1 处理方法

考虑到Excel提供了丰富的工作表函数，可以方便快捷地进行求解。为简化编程，本文在最小二乘编程部分采用VBA直接调用LINEST工作表函数求解φ和c。这样可以最大限度地利用VBA编程的优势，节省精力、降低编程的复杂度。

LINEST 工作表函数使用最小二乘法计算与现有数据最佳拟合的直线，并返回描述此直线的数组。其函数调用语法为LINEST(yValues,[xValues],[const],[stats])。函数调用参数如下：①yValues为必需参数，xValues为可选参数，分别为直线关系表达式y=mx+b中已知的y值和x值的集合，对于直剪试验来说，相当于S和p的集合。②const为可选参数，用于指定是否将常量b强制设为0。如果const为TRUE或被省略，b将按通常方式计算；如果const为FALSE，b将被设为0，并同时调整m值。若在试验中因存在受人为因素影响、土的不均匀性等特殊情况而出现c为负值时[6,14]，可以强制c=0。③stats为可选参数，用于指定是否返回附加回归统计值。如果stats为TRUE，则LINEST函数返回附加回归统计值的数组，包括斜率、截距、判定系数r2(为回归平方和/残差平方和)、残差平方和SSE等。

2.2 程序实现

VBA内嵌于Excel中，与Excel界面的风格统一，且其与Excel LINEST工作表函数的交互极为方便，通过Application.WorksheetFunction.LinEst(sValues,pValues,False,True)即可实现调用，避免了最小二乘法的自行编程，极大地方便了技术人员。

程序运行时，在Excel的菜单栏形成一个“直接剪切试验”菜单项，并项包含若干功能选项。由于Office软件自带应用程序开发工具VBE(Visual Basic Editor)，即编写、运行和存放VBA程序的容器，故可以降低编程开发门槛。在VBE界面代码框内，执行相应源程序，即可实现相应功能。

1)“数据计算”模块程序代码及注释如下。

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

'直接通过VBA调用Excel工作表函数LinEst函数,返回附加回归统计值到arrLs数组

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

'斜率和截距

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

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

'粘聚力为负值处理

Ifc<0 Then

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

End If

'内摩擦角度数

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

'粘聚力(直线截距)

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

失禁性皮炎是一种皮肤炎性反应，因皮肤在尿液和(或)粪便中的长期暴露而形成，轻则皮肤发红糜烂，出现水泡渗液，重则皮肤出现二重感染，失禁、营养不良、制动、肌松镇静药使用等情况，普遍存在使ICU患者成为失禁性皮炎高危人群[1-5]。出现失禁性皮炎，不但使压疮、感染等并发症风险显著上升，而且还会增加患者身心痛苦程度[6]。2018年1～6月，我们对收治的40例ICU护理单元患者实施皮肤护理可行性处置策略，效果满意。现报告如下。

'判定系数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)="数据离散性偏大,建议重做试验"

End If

Next

End Sub

2)“批量导出剪切试验成果图”功能模块主要程序代码及注释如下。

Private Sub ExportPNG(control As IRibbonControl)

DimiAs Integer,nRows As Integer

Constpi=3.14159

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

'批量调整数据源并自动化绘制关系图

Fori=2 To nRows Step 2

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

.ChartTitle.Text="试样编号:"& Range("A" &i).Value

.Axes(xlValue).MinimumScaleIsAuto=True

.Axes(xlValue).MaximumScale=300

'绘制试验数据点

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

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

'绘制关系直线,也即"视测直线"

.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))

'导出垂直压力与抗剪强度试验关系图为PNG文件

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

End With

Next

End Sub

3 算例

3.1 算例1

各土样的原始试验数据及计算成果见表1。打开Excel后，默认自动选择Excel功能区的“直接剪切试验”选项卡，“初始化”后，在Excel电子表格中按表1格式在A～F列中输入由直剪试验所测得的原始试验数据，其中第1行在“初始化”中自动生成；点击“数据计算”，程序会自动批量完成数据处理，并在Excel表格G～J列中自动填充结果。经过对比，soil1，soil2试验数据的处理成果与文献[5]和文献[14]一致；soil3，soil4的处理成果也与项目试验报告结果一致，验证了本方法的有效性。

表1 各土样的原始试验数据及计算成果

点击“批量导出剪切试验成果图”，程序会自动批量绘制并保存曲线图。各土样试验成果图如图1所示。整个操作过程实现了一次性输入，一次点击，一次批量输出结果，便捷、高效，无需人工进行调整，排除了人为因素干扰。

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

3.2 算例2

再以文献[12]中的5组土样为例，通过程序输出结果，与作图法和回归计算法进行比较，各方法求得结果比较见表2。从表2可以看出，对于1-2土样，通过本文VBA编程LINEST工作表函数实现了c为负值情况的强制处理，结果与作图法接近；对于7-1土样，3种方法的计算结果差异稍大，但本文VBA法所得SSE=395.7270，小于文献[12]作图法所得SSE=478.3963和回归计算法所得SSE=415.5681。除1-2土样和7-1土样以外，本文所得结果与回归计算法近乎一致，比作图法偏差较小，而且比回归计算法更为客观，无人为因素干扰的影响。

表2 各方法求得结果比较

4 结论

1)利用最小二乘原理，将作图法转换为数学求解问题，利用Excel电子表格的快速数据输入、VBA编程的批量自动化绘制成果图等特点，克服了人为因素的影响，改变了人工作图法数据处理效率较低且繁杂的劣势。

2)实现了与Excel风格统一的可视化程序界面，程序界面美观、方便易用；实现了通过简单点击操作就可以批量自动化处理数据和绘制成果图。

3)基于VBA的岩土直剪试验数据处理方法是一种精确、高效、直观的数据处理和图表绘制解决方案，对于提高试验和工程技术人员的工作效率有较好的作用。相较于传统编程语言，VBA编程门槛较低，易于工程技术人员掌握和推广应用。