基于Excel的直剪试验数据处理方法

2015-03-13莫林利赵秀绍

铁道建筑 2015年9期

关键词：黏聚力土样抗剪

莫林利，赵秀绍，王旭，郑伟

(1.华东交通大学软件学院，江西南昌 330013;2.华东交通大学土木建筑学院，江西南昌 330013)

基于Excel的直剪试验数据处理方法

莫林利1，赵秀绍2，王旭2，郑伟2

(1.华东交通大学软件学院，江西南昌 330013;2.华东交通大学土木建筑学院，江西南昌 330013)

回归分析中的最小二乘法是处理直剪试验数据误差最小的方法，但计算工作量大且容易出错。应用Excel软件给出可用于直剪试验计算的5种方法，可显著减少计算工作量并降低出错率。实际工程数据计算对比表明，除基于点线距的规划求解法外，其余4种方法均为基于拟合误差的计算方法，两种原理的计算结果差距可忽略不计。对于无黏性土情况，除INTERCEPT函数与SLOPE函数联合求解法无法实现强制黏聚力为0外，其余方法均可实现。实际应用表明Excel图解法、TREND函数法及截距和斜率函数联合求解法操作简单，易于应用。

直剪试验 Excel内部函数最小二乘法规划求解

边坡土的抗剪强度是铁路路基边坡稳定性定量评价和计算滑坡推力的重要参数，有时内摩擦角相差1°～2°，滑坡推力就可能成倍增加［1］。马显春等［2］采用正交分析法对滑坡稳定性的影响因子进行了分析，得出滑坡影响因素敏感性顺序为内摩擦角＞黏聚力＞地下水位＞重度＞坡高。可见合理确定岩土材料的抗剪强度指标对于工程设计、施工等十分重要［3-5］。

直剪试验的数据处理实际上是求出土的抗剪强度参数，即黏聚力 c和内摩擦角 φ。斜截式计算法是在试验中测得N，M两点的垂直压应力σN，σM，其对应的抗剪强度为τN，τM。通过 N，M的直线方程可以获得土的抗剪强度指标［6］。此法的不足之处是非常明显的，计算时仅能使用2组数据。《土工试验技术手册》［7］建议的方法为绘制抗剪强度与垂直压力关系曲线，直线的倾角为内摩擦角φ，纵坐标的截距为黏聚力c。但是在绘库仑破坏直线时，一般是绘图者根据经验和直观判断，尽量让落在直线两边的点数大致相等，然后求出截距和斜率。由于它是人为地根据经验和直观判断得出的，c，φ值往往带有一定的人为性和不确定性。采用最小二乘法对试验数据进行拟合求解［8-11］，计算量大且容易出错。本文探讨如何巧用Excel的各项功能简化计算过程，并考虑直剪试验中c值为负或强制c=0(无黏性土)的情况，使数据处理达到全自动化。

1 最小二乘法求解原理

为了便于说明各种求解方法的原理与实现方法，以江西抚州八景镇火车站路基工程一组土样实测数据(见表1)为例进行分析。

表1 抚州八景镇路基土样测试数据

直剪试验求解中，当绘制测点的破坏包线时，由于土样不均匀，试验读数误差等原因，往往绘制的直线不能通过所有的测点，总是存在一些误差，根据这些误差的平方和为最小，可以确定一条近似的破坏包线。设直剪试验中每组n个土样的垂直应力、抗剪强度分别为σi，τi，利用库仑公式 τ=σtanφ+c进行拟合，其中c，φ被视为待定系数。以偏差+c)］2最小为条件来确定c，φ值。运用高等数学的多元函数求极值的方法即可得到当 M为最小时的 c，φ值。分别对c，φ求一阶偏导并令其等于0得［8］

根据式(1)，式(2)解得

如果用上述数学法求解，计算工作量大且易出错。Excel软件中有丰富的回归分析函数和规划求解方法，只要挖掘其功能，就可以简单有效地完成上述计算。

2 直剪试验的Excel求解方法

2.1 Excel图解法

Excel软件有强大的绘图工具，先把试验数据填入数据表格中，然后插入图表，并为数据添加趋势线和显示公式，就可以得到直剪试验图。具体实现方法为:选择正应力和剪应力数据，选择“插入”→“图表”→“散点图”，然后选择图形中的点后“添加趋势线”，在“选项”栏中选择“显示公式”，可直接得到拟合公式 y= 0.430 6x+3.260 9，进而得 c=3.260 9 kPa，tanφ= 0.430 6，即φ=23.297 1°。

2.2 TREND函数求解法

2.2.1 TREND函数求解的基本原理

由于直剪试验各测点存在线性关系，这样就可以方便地利用 Excel中的线性插值函数 TREND。TREND函数用途:返回一条线性回归拟合线的一组纵坐标值(y值)。即找到适合给定的数组known_y's和known_x's的直线，并返回指定数组new_x's值在直线上对应的y值。TREND函数的语法为TREND(known _y's，known_x's，new_x's，const)。其中known_y's为已知的τ=σtanφ+c=σk+c中的τi值集合，known_x's为已知关系τ=σk+c中可选的σi值的集合，new_x's为需要函数TREND返回对应新σ值的新τ值，const参数为逻辑值指明是否强制常数项c为0。当拟合发现c值为负时，由于黏聚力不可能为负值，因此可以强制c值为0。

TREND函数求解原理如图1所示，很显然，对于抗剪强度包线τ=σtanφ+c，当new_x's=σ=0时，其返回的函数值为τ0=c，即为黏聚力值。当new_x's= σ=1时，函数返回值 τ1=1×tanφ+c，根据图1中的几何关系可得tanφ=(τ1－τ0)/1=τ1－τ0。

2.2.2 TREND函数求解法的实现

将表1中的原始数据填入Excel表中，如图2(屏幕截图)中所示。

图1 TREND函数求解原理

图2 TREND函数求解抗剪强度指标示例

在C2和D2单元格中，分别输入“=TREND(B2: B5，A2:A5，0)”，“=ATAN(TREND(B2:B5，A2:A5，1)－TREND(B2:B5，A2:A5，0))*180/PI()”，即可求得该土样的黏聚力c和内摩擦角φ。

TREND(B2:B5，A2:A5，0)在σ=0时可得τ0=c; TREND(B2:B5，A2:A5，1)在 σ=1 kPa时可得 τ1=c +k;TREND(B2:B5，A2:A5，1)－TREND(B2:B5，A2: A5，0)为系数k。

当土样为无黏性土或计算出的黏聚力c值为负值时，可以强制TREND()函数中const项参数为0。对表1的测试数据求解结果为:c=3.260 9 kPa，φ= 23.297 1°。

2.3 INTERCEPT函数与SLOPE函数联合求解法

2.3.1 INTERCEPT函数与SLOPE函数联合求解原理

在Excel软件中，函数INTERCEPT和SLOPE利用现有的x值和y值计算直线与y轴的截距和斜率。其中INTERCEPT(known_y's，known_x's)函数可以利用现有的σ值和τ值计算直线与τ轴的截距，即抗剪强度指标黏聚力 c;SLOPE(known_y's，known_x's)函数可以利用现有的σ值和τ值计算直线的斜率 k，即抗剪包线斜率tanφ。known_y's表示因变的观察值或数据集合;known_x's表示自变的观察值或者数据集合。

2.3.2 INTERCEPT函数与 SLOPE函数联合求解法的实现

设数据在 Excel表中的排列仍如图2所示，则在C2和D2单元格中分别输入“=INTERCEPT(B2:B5，A2:A5)”，“=ATAN(SLOPE(B2:B5，A2:A5))* 180/PI()”。求解出的结果为 c=3.260 9 kPa，φ= 23.297 1°。

2.4 基于点线距的规划求解法

2.4.1 基本原理

如图3所示，对抗剪强度库仑公式τ=σtanφ+c，令tanφ=k，则任意测点 A(σi，τi)到直线的距离为di［12］，见式(5)。

图3 点线距求解基本原理示意

根据最小二乘法的原理，回归分析时就是令 d2i和达到最小，即

式(6)中有k和c两个变量，实际为线性规划求解问题，求解目标函数的最小值。Excel规划求解一般步骤为建立目标函数f(x)min，确定约束条件将决策变量用数学公式表达出来，使需要优化的目标及评判标准量化为这组变量的函数。

2.4.2 直剪试验的规划求解实现

Excel规划求解首先要设计好一个工作表(如图4所示)，根据建立的数学模型，将决策变量、约束条件、目标函数等依次排列，并在工作表中将相关固定信息初始化，具体实现步骤如下。

图4 基于点线距的规划求解示例

1)打开Microsoft Excel软件后，按图4基于点线距的规划求解所示的格式建立工作表，将有关数据输入工作表中(其中C2和D2为空白，有待计算)，E2单元格输入“=((C2*A2－B2+D2)/SQRT(C2^2+1)) ^2+((C2*A3－B3+D2)/SQRT(C2^2+1))^2+ ((C2*A4－B4+D2)/SQRT(C2^2+1))^2+((C2* A5－B5+D2)/SQRT(C2^2+1))^2”。

2)在“工具”栏下拉菜单中找到“规划求解”，如果没有此选项，可以选择“加载宏”将“规划求解”加载进来。单击“规划求解”，出现“规划求解参数”对话框，设置目标单元格:$E$2，在“等于”选项中选“最小值”，在“可变单元格”中输入$F$2:$G$2，在“约束”中单击“添加”后，逐个输入下式:“$C$2＞=0”，“$D$2＞=0”，最后按“确定”。当需要强制 c值为0时，则条件变为“$C$2＞=0”，“$D$2=0”。求解结果为c=3.254 6 kPa，k=0.430 6，在C3单元格中求得φ=23.298 8°，此时目标函数最小，f(x)min=5.618 9。

2.5 基于测试值与拟合值误差的规划求解

直剪试验数据σi，τi利用库仑公式拟合直剪试验中每组n个土样的试验数据时，c，φ被视为待定系数。以偏差最小为条件从而确定c，k值。这仍为线性规划求解问题，求解目标函数的最小值。其实现方法同2.4中方法类似，仅需要根据公式更改其目标函数为“=(B2－A2*C2－D2)^2+(B3－A3*C2－D2)^2+ (B4－A4*C2－D2)^2+(B5－A5*C2－D2)^2”。

运行规划求解后，选择“保存极限值”即可同时求得c值和k值，然后根据k值可计算出内摩擦角φ值。根据上述方法，表1数据的求解结果为c=3.261 0 kPa，φ=23.297 1°，此时目标函数最小，M=6.660 9。

3 各种直剪试验求解方法的对比

为了对比5种求解方法的有效性，以表1中测试数据作为第1组数据，以福建省福清市江阴镇海边固结淤泥测试数据作为第2组数据，第2组数据如表2所示。

表2 福清江阴镇淤泥土样测试数据

通过在Excel中求解，5种方法求解结果如表3所示。根据表3对2组试验的计算结果，分析如下:

1)常规数据计算结果对比。虽然各种方法均采用了最小二乘法，在不强制黏聚力为0的情况下，除基于点线距的Excel规划求解法外，其余方法求解结果均相同，说明其余4种方法均是基于拟合误差的求解方法。从数据对比可知5种方法计算结果非常接近，

均可作为直剪试验的求解结果。

表3 Excel中5种直剪试验求解方法计算结果对比

2)黏聚力为负值情况处理对比。表3中福州省福清市江阴镇由于取样挠动等原因，多组测试数据测试结果拟合后，黏聚力均为负值，这与理论并不相符。因此对于c值为负的情况或无黏性土的黏聚力为0的情况，此时需要强制黏聚力为0。对于强制c=0的情况，INTERCEPT与SLOPE函数联合求解法无法完成强制c=0，其余方法均可强制c=0。通过对比可得，对于无黏性土或黏聚力出现负值情况，不宜采用INTERCEPT与SLOPE函数联合求解法。

3)操作简易程度对比。从5种方法在数据更换后Excel操作繁简比较，规划求解需要重新求解，特别是试验组数更改后，目标函数应随着更改，而且会生成中间报告，故对计算机操作不太熟悉人员有些难度。其它方法均操作简便，且计算结果会自动完成更新。

4 结论

根据最小二乘法的基本原理及前人的研究成果，提出了基于Excel图解法、INTERCEPT与SLOPE函数联合求解法、TREND函数法、基于点线距的规划求解法和基于拟合误差的规划求解法，重点分析了TREND函数与基于点线距规划求解法2种方法的求解原理，并针对每种求解方法在Excel软件中设计了相应公式或实现方法。通过几种直剪试验的求解方法对比分析，可以得出以下结论:

1)Excel中的 TREND函数求解法，截距函数INTERCEPT与斜率函数 SLOPE联合求解法均为Excel内部函数法，大大地减小了编辑公式的工作量，是一种操作简便计算方法，在无黏性土或计算黏聚力＜0的情况下，TREND函数求解法最为方便。

2)5种求解方法基本原理均采用基于最小二乘法的回归分析，其拟合误差保证其与实测值差值的平方和最小，其精度较采用两点计算的斜截求解法和手工绘图更能反映各测点对试验结果的影响，更能代表土的实际抗剪强度指标。

3)在5种求解方法中，当计算的黏聚力 c值为正值时，各种方法求解结果相近，均可作为试验计算结果。当由于某种原因而引起拟合c值为负值或土样为无黏性土时，c=0才正确。Excel图解法、TREND函数法、两种规划求解法均可实现强制黏聚力为0，而INTERCEPT与SLOPE函数联合求解法无法实现强制截距为0，故不能采用。

［1］李向国.滑带土抗剪强度参数的确定［J］.西部探矿工程，2010(2):37-38.

［2］马显春，王雷，赵法锁.滑坡稳定影响因子敏感性分析及治理方案探讨［J］.地质力学学报，2008，14(4):381-388.

［3］刘远明，刘杰，夏才初.不同节理表面形貌下非贯通节理岩体强度特性直剪试验研究［J］.岩土力学，2014，35(5): 1269-1273.

［4］衡帅，杨春和，曾义金，等.基于直剪试验的页岩强度各向异性研究［J］.岩石力学与工程学报，2014，33(5):874-883.

［5］陈建胜，陈从新，鲁祖德，等.强风化角岩力学—变形特性的直剪试验研究［J］.岩土力学，2010，31(9):2869-2874.

［6］王晓斌，张铁男，董志国.直剪试验中的斜截式计算方法［J］.水利科技与经济，1999，5(2):82-83.

［7］南京水利科学研究院土工研究所.土工试验技术手册［M］.北京:人民交通出版社，2003.

［8］谈云志，王世梅.直剪试验数据处理方法的对比分析［J］.三峡大学学报(自然科学版)，2005，27(2):132-134.

［9］林立军.二次曲线拟合法在无机结合料稳定材料击实试验数据处理中的应用［J］.福建建筑，2012，30(5):83-85.

［10］余东明，姚海林，吴少锋.三轴试验抗剪强度参数值回归分析法的区别与修正［J］.岩土力学，2012，33(10):3037-3042.

［11］陈辉，颜胜才，孟军涛.K30试验数据的曲线拟合分析方法［J］.铁道建筑，2012(11):67-70.

［12］阮波，张向京，彭意.Excel规划求解三轴试验抗剪强度指标［J］.铁道科学与工程学报，2009，6(5):57-60.