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

Data Processing Method of Geotechnical Direct Shear Test based on Excel VBA

为解决《土工试验方法标准》(GB/T 50123-2019)中人工作图法处理数据量大、绘图工作繁琐、易受人为因素影响等问题,给出了一种快速自动化处理土的直接剪切试验数据的方法,即基于最小二乘原理,将作图法转化为数学求解问题,利用VBA编程结合Excel内置LINEST工作表函数直接获得土的内摩擦角和粘聚力,同时实现批量自动化绘制抗剪强度与垂直压力的关系曲线图。结果表明,VBA结合Excel内置LINEST工作表函数可以方便快捷地获得土的内摩擦角和粘聚力,并真正实现快速化、准确化、批量自动化处理数据和绘图,处理结果直观、可靠,工程实用性和可操作性强,具有推广应用价值。

In order to solve such problems as large amount of data processing,complicated drawing work and the influence of human factors in the method of manual drawing in Standard for Geotechnical Test Methods(GB/T 50123-2019),this paper proposes a fast and automatic processing method of direct shear test data of soil.Based on the least square principle,the manual drawing method is transformed into a mathematical solution problem,VBA programming combined with the Excel built-in worksheet function LINEST function is used to directly obtain the internal friction angle and cohesion of soil,and and automatic drawing of relationship graphs of shear strength and vertical pressure in batches can be realized at the same time.The results show that VBA combined with Excel's built-in worksheet LINEST function can easily and quickly obtain the internal friction angle and cohesion of soil,and truly realize rapid,accurate,and automatic processing of data and drawing in batches.With intuitive and reliable processing results,this method has very strong engineering practicability and operability,which can be widely popularized and applied.