击实试验数据的Excel处理方法
击实试验数据的Excel处理方法
李法俊田志峰
(潍坊市公路管理局，山东潍坊 261031)
【摘要】本文对使用Excel进行击实试验数据处理的方法作了详细介绍，并对所采用的插值多项式的性质进行了讨论。应用Excel的高阶多项式插值方法，可以得到精确的多项式插值趋势线，并可以直接在Excel图表坐标系中读出击实曲线对应的最大干密度和最佳含水量。该方法消除了手工绘图的误差，同时避免了程序设计的繁琐，有较高的工程应用价值。
【关键词】 Excel 击实试验数据插值趋势线最大干密度最佳含水量
1 概述
公路工程路基土方施工中，填土的最佳含水量和最大干密度是非常重要的指标，直接影响着路基的工程质量和经济效益。所以必须要保证击实试验结果的准确性，而击实试验数据的插值处理是其中的关键。
目前，击实试验所得到的试件的含水量与干密度的数据，通常的处理方法是在米格纸上手工描点，根据经验手工绘制关系曲线，然后在坐标系下读出最佳含水量和最大干密度。这种方法的优点是简便易行，较为直观。缺点是工作效率低、人为因素影响较大、处理结果精度不高。
为提高击实试验数据的处理精度，人们相继开发了一些程序，有大型的试验室通用数据处理程序，有小型的击实试验专用程序，这些程序使用样条函数插值的方法，或多项式插值的方法，采用了C++语言、Auto -lisp语言等进行开发，计算精度得到了大幅提高。但是这种方法缺点也是很明显的：大型程序需要购买，小型程序的开发需要一定的数学基础和计算机语言基础，形成了该项技术推广的瓶颈，限制了其在工程建设中的应用。
利用Excel对击实试验数据进行高阶多项式插值处理的方法将击实试验数据的中间计算、含水量—干密度关系曲线的绘制及击实试验表格的输出一并实现，该方法简便易行，精确快捷，保留了手工绘图和编程计算的优点，同时避免了二者的缺点，是一种使用方便的、低成本、高效率的方法。2 Excel表格的创建与数据的输入
首先新建Excel文件，参照常用的击实试验记录表制作Excel工作表，然后根据规范规定的含水量、干密度计算公式定义相应单元格的公式，输入各组试验的原始数据（筒加土重、筒重、盒加湿土重、盒加干土重、盒重），Excel可以自动计算出各组试件的干密度和含水量。
3 绘制击实试验数据的插值曲线
3.1首先选取干密度一行的5个（或6个）单元格，然后单击常用工具栏的，选取
“XY散点图”，在选取“子图表类型”中的“平滑线散点图”。如图1：
图1 选取图形类别
3.2单击“下一步”，进入（图表向导-4步骤之2-图表源数据）。如图2：
图2 选择图表源数据（一）
3.3单击“系列”标签，单击“X值”后的数据框，再选取含水量一行的5个（或6个）单元格，如图3：
图3 选择图表源数据（二）
3.4 单击“下一步”，进入（图表向导-4步骤之3-图表选项）。如图4：
图4 调整图表选项
3.5单击“图表向导-4步骤之3-图表选项”的“标题”标签，输入“数值X轴”的标题为“含水量ω（%）”，输入“数值Y轴”的标题为“干密度ρd(g/cm3)”；
单击“图表向导-4步骤之3-图表选项”的“网格线”标签，将数值（X）轴、数值（Y）轴的主要网格线多选框中打√；
单击“图表向导-4步骤之3-图表选项”的“图例”标签，将显示图例前的单选框中取消打√；
其他选项不动。
3.6单击“下一步”，进入“图表向导-4步骤之4-图表位置”。如图5：
图5 设定图表位置
3.7选取“作为其中的对象插入”，然后单击“完成”，生成的图表便插入到当前的Excel工作表中了。生成的图表如图6：
图6 生成的XY散点图
3.8将生成的图表移动位置到试验记录表格的图表预留区并调整其大小使之与试验记录表相协调。
3.9用鼠标右键单击图表上的任一数据点，在弹出的快捷菜单中单击“添加趋势线”，出现如下窗口，单击“多项式”，调整“阶数”为6。如图7
：
图7 选择趋势线格式
3.10单击“确定”，便可得到插值后的趋势线。如图8：
图8 生成的趋势线
3.12用鼠标右键单击图表区任一坐标数值，根据实际情况设置坐标格式，使图表区更加美观。调整后的趋势线如图9：
图9 调整后的趋势线
3.13从图表中趋势线的极大值点便可以读取最佳含水量和最大干密度的数值。也可以按下述方法由趋势线上自动读出极值点的纵横坐标：
鼠标右键单击图表区空白部分，单击“源数据”，单击“添加”，单击“X值”后的数据框，单击表格中需要输出的最佳含水量数值所在的单元格，单击“Y值”后的数据框，删掉其中的内容，然后单击表格中需要输出的最佳干密度数值所在的单元格，单击“确定”，可以看到图表区生成了另一种颜色的的一个坐标点（如果看不到该坐标点，先假定一个近似的最佳含水量值和一个近似的最大干密度值）。生成的新序列散点如图10：
图10 生成新的序列散点
单击新生成的序列散点，当光标变成
时，移动该点至趋势线峰值点处，此时该散点对应的两个单元格的数值就是最佳含水量、最大干密度值。
3.14我们还可以用添加误差线的方法从峰值点处向X、Y坐标轴引出一条水平线和一条铅垂线与坐标轴相交，从而更加方便地读出趋势线峰值点对应的纵、横坐标。步骤为：鼠标右键单击已经位于峰值点处的新的序列散点，在弹出的快捷菜单中单击数据系列格式，单击“误差线X”标签，单击“负偏差”，将“误差量”的“百分比”调整为100%；单击“误差线Y”标签，单击“负偏差”，将“误差量”的“百分比”调整为100%。如图11：
图11 添加误差线
3.15单击“确定”，便由峰值点处引出了一条水平线和一条铅垂线与纵横坐标轴相交，使得在图表中读书更加直观、清晰。
鼠标右键单击误差线，可以调整误差线格式。生成的误差线如图12：
图12 生成的误差线
4 关于插值方程的讨论
根据《公路土工试验规程》的有关规定，在取得干密度、含水量的数据后，绘制一条干密度—含水量关系曲线通过所有的试验数据点，曲线上峰值点的纵、横坐标分别为最大干密度和最佳含水量。
与击实试验规程的规定相对应的数值分析方法就是插值，常用的插值方法为Lagrange多项式插值。
根据Lagrange插值多项式的定义可知其性质为：
Lagrange插值多项式的存在是唯一的。也就是说通过已知的数据点只能做出唯一的一条曲线满足Lagrange插值函数的定义。
Lagrange插值多项式对应的曲线通过所有的离散数据点，相关系数等于1。
用鼠标右键单击图表上的任一数据点，单击弹出的快捷菜单中的“添加趋势线”，再单击“选项”按钮，选择“显示R平方值”可以验证插值多项式的相关系数等于1。
对于击实试验数据来说，Lagrange插值多项式的阶数通常为试验数据的个数减去1，即当试验数据个数为5时，相应的Lagrange插值多项式的阶数为4，这时候干密度—含水量的关系表达式为：
ρ(ω)=a0+a1ω1+a2ω2+a3ω3+a4ω4
其中ρ(ω)为当含水量为ω时的干密
度，ω为含水量，a
0、
a
1、
a
2、
a
3、
a
4
为插值系数。
如前文所述，之所以选择多项式趋势线的阶数为6，是因为该选项的选择上限为6，这时候干密度—含水量的关系表达式为：ρ(ω)=a0+a1ω1+a2ω2+a3ω3+a4ω4+a5ω5+a6ω6
当试验数据的个数为5个时，经Excel
计算得到的a
5
=a
6
=0，干密度—含水量的关系表达式实际上仍然为：
ρ(ω)=a0+a1ω1+a2ω2+a3ω3+a4ω4
用鼠标右键单击图表上的任一数据点，单击弹出的快捷菜单中的“添加趋势线”，再单击“选项”按钮，选择“显示公式”可以验证插值多项式的阶数规律。
5 结语
利用Excel处理击实试验数据，可以充分发挥其强大的制表、公式计算和绘图功能，并通过得到的插值曲线读出最佳含水量和最大干密度，为试验人员精确处理试验数据提供了方便。利用Excel建立的表格还可以作为模板，试验人员仅需将所得的原始数据填入表格中即可立即生成击实试验记录表（如图13）。类似地，利用该方法还可以对其他试验数据进行插值处理。
图13 打印出的击实试验记录表
参考文献
1 陈永利·巧用Excel软件处理击实试验数据并绘制曲线效果图·工程地质计算机应用· 2004年第1期