Excel内部函数求解界限含水率试验解析法研究

2013-12-23赵秀绍莫林利艾成刚

实验技术与管理 2013年6期

赵秀绍，莫林利，刘巍，艾成刚

（1.华东交通大学土木建筑学院，江西南昌 330013；2.华东交通大学软件学院，江西南昌 330013）

界限含水率试验是铁路路基填土工程、承载力计算中不可缺少的重要试验项目，试验计算结果的准确与否直接关系到地基承载力计算的准确性及判断材料能否应用的问题。所以除了在试验过程中要严格控制各项操作的准确性外，数据计算的准确性也尤为重要。采用手工作图法处理界限含水率试验数据，不仅效率低，试验结果的精度也难以保证，在土工实验教学中也不方便对学生计算的结果进行判定，因此许多学者提出了多种解法。韩继军［1］通过Excel突出的图表功能，利用图表法查值获得的土的液塑限值。李健利［2］巧妙灵活地使用Excel提供的格式化功能，使生成的数据表格或图形更加美观、清晰。贺涛［3］综合利用Excel强大的数据处理功能和AutoCAD强大的绘图功能，提出了一种方便快捷而准确的处理数据的新方法，且采用AutoLISP进行编程，以提高数据处理的速度和精度。吕雅丽［4］依据（JTG E40—2007）《公路土工试验规程》，以Excel为工具，编写VBA 程序，计算试验结果及绘图，使求解效率大为提高，但VBA 程序相对复杂，对一般用户不易操作。殷春娟［5］提出了用Excel表格处理界限含水率的方法，彭意［6］、冯朝辉［7］、张传邦［8］提出了公式法计算界限含水率的方法，高盟［9］提出了用样条函数求解的方法。这些方法大大地提高了作图法计算的效率与精度。

研究现状表明，目前研究多采用软件编程方式和图形联合方式求解，要求研究者具有较高的计算机水平，因而在工程人员中较难扩广使用。所以在求解界限含水率试验中，使计算结果精确而唯一、处理方法简单而实用是工程中亟待解决的问题。实践表明，Excel内部函数在处理土工试验数据方面功能强大，可以简单且完全自动化地计算界限含水率而不依赖于图形绘制和程序编写。

1 界限含水率试验求解的基本原理

依据《TB10102—2004 铁路工程土工试验规程》［10］或《土工试验方法标准》［11］，以含水率w 为横坐标，锥尖入土深度h为纵坐标，在双对数坐标纸上绘制log h与log w 曲线，3 组数据（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%时说明试验差距过大，应重做试验。

因为铁路规范作图法中是估读d、e点的横坐标作为土的塑限和液限，因此不同的试验者有不同的估读值，造成土的界限含水率的不确定性。利用Excel内部函数，且不用复杂VBA 编程就实现界限含水率的计算是本文要解决的问题。

2 基于Excel内部函数求解土的界限含水率的原理与设计

2.1 Excel求解的函数原理

图1 界限含水率原理图

在图1的计算原理图中，a、b、c各点的坐标分别为（log wa，log ha）、（log wb，log hb）、（log wc，log hc），log（）函数在Excel中默认为10为底的对数函数。d1点就是ab直线与纵坐标为2mm 水平线的交点，也可以认为d1点是直线ab 的插值点，这样就可以方便地利用Excel中的线性插值函数Trend（）。Trend（）函数的语法为

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

参数:known＿y’s为已知关系y＝mx＋n 中的y值集合，known＿x’s为已知关系y＝mx＋n 中可选的x 值的集合，new＿x’s为需要函数TREND 返回对应y 值的新x 值，const为逻辑值指明是否强制常数项n为0。

TREND（）函数用途:返回一条线性回归拟合线的一组纵坐标值（y 值）。即找到适合给定的数组known＿y’s和known＿x’s的直线，并返回指定数组new＿x’s值在直线上对应的y 值。

根据TREND（）函数的语法与界限含水率求解问题，令含水率的对数值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（）函数直接求得结果为log wab，故有

式中｛log wa，log wc｝为Excel数组表达形式。

函数参数known＿y’s和known＿x’s必须为一个引用集或数组，因此要实现以上算法，必须使用辅助单元格。

2.2 Excel求解土的界限含水率的设计

如表1所示，辅助单元格为B1—B6 及C1—C6。B列和C列放入各测点的对数值（若通过输入公式计算“log（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的单元格中。根据求塑限的方法，用TREND（）函数可求得与10mm 和17mm 横线的交点，从而求得10mm液限与17mm 液限，具体公式参照表1中单元格D11和D12。

表1 Excel求解的相关公式

表1中的公式是完全参照规范法得到的，而且可以通过塑限差△wp是否超过2%来判断试验的正确性。

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

根据文献［12］可得:在液限和塑限联合测定的试验中，锥尖入土深度h（mm）与含水率w（%）之间存在双对数线性关系:

式中m 和n 是由试验确定的常数。

根据以上原理，a、b、c3点的对数值应处于一条直线上。根据以往的研究经验，一般采用的方法是在Excel中用直线拟合出a、b、c3点，拟合时对话框选择“显示公式”和“相关系数”，在实际应用时还需人工从拟合直线图中读取公式中的m、n 才能进行界限含水率的计算。

Excel内部函数提供了最小二乘法回归计算的方法，与此相关的函数为截距函数INTERCEPT（）可求得n值，SLOPE（）函数可求得m 值，CORREL（）函数可求得相关系数。根据公式（1）可得任意锥尖入土深度h对应的含水率公式为

式中:n为拟合直线的截距，用INTERCEPT（logh 数组或引用域，log w 数组或引用域）求解；m 为拟合直线的斜率，用SLOPE（logh 数组或引用域，logw 数组或引用域）求解。

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

表2 直线拟合法求解界限含水率公式设计

直线拟合法是根据界限含水率的原理得出的，其采用了最小二乘法回归分析，可以把试验点的误差降到最小，但无法完成规范中的超差计算，虽然有相关系数，但相关系数不能作为试验偏差的判断依据。直线拟合法也有其优点，其计算点可以超过3个，只要修改公式中的引用范围就可拟合更多的数据点，如改成INTERCEPT（A2:A6，B2:B6）即要计算5个点，从而减小由于a点的偏差引起的偶然误差。

4 两种方法的求解对比及工程应用

为了验证Excel界限含水率的公式设计和对比规范法和拟合法的差别，选取江西南昌市孔目湖取土点和江西高安市八景镇取土点两地土样作为典型实例，其中孔目湖取土样进行了平行试验，其计算结果如表3和表4所示。表3和表4中的Δwp为规范法结果，R2为拟合法得到的相关系数平方值，201、202、204组合为平行试验。

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

表4 江西高安八景镇取土点界限含水率计算

根据表3和表4的计算结果看，其塑限和液限与作图法求得的数值相符，验证了Excel公式设计的正确性。

根据表3和表4，在塑限和液限的差值比较时，10 mm 液限差值最小，一般可以忽略，塑限引起的偏差最大。当塑限差△wp＜2时，规范法和拟合法求得的塑限最大差值为0.577，可以满足要求，因此两种方法均可使用。当塑限差△wp≥2时，规范法和直线拟合法计算的塑限差值可达1.158，此时最好依据塑限差重新做试验。