Excel在单元分层及常规土工试验资料整理中的应用研究

1、Excel在单元分层及常规土工试验资料整理中的应用研究作 者：白华民 杨荣三 白红芬（河南华兴地质工程有限公司，河南 南阳473000）摘要：运用Excel强大的制作表格、处理数据、分析数据等功能，可简便快速输入土工试验数据、自动完成各单元层物理力学性质指标统计工作，分层快速准确、统计计算结果精度高，大幅提高了勘察质量和工作效率，缩短了勘察资料整理周期，实用性强，值得推广应用。关键词：Excel表；取样深度；常规土工试验项目；单元层物理力学性质；精度；效率中图分类号：TV16 文献标志码：AApplied Research of Excel in Unit Hierarchy and Rout

2、ine Soil Test DocumentationBai Huamin(HeNan HuaXing Geological Engineering Co., Ltd., He Nan, Nan Yang, Postcode 473000)Summary: By using of Excel to make table descriptions, data processing and analysis, the index of statistics can be completed automatically for each unit hierarchys physical and me

3、chanical properties by simply entering the soil test data. The hierarchy can be done quickly and accurately, with accurate statistical calculation results, which is largely improving the quality and the efficiency of the survey, as well as reducing the time for survey data sorting. It is practical a

4、nd worthy of promotion.Keyword: Excel; Sampling depth; Routine soil test project; Unit hierarchys physical and mechanical properties; Accuracy; Efficiency大型调水工程、病险水库以及工业与民用建筑群等岩土工程勘察中，需取数十个、数百个、数千个甚至数万个岩（土）样做土工试验，以便划分地层、统计计算岩（土）体的物理力学指标及强度指标。若采用传统方法计算，不易剔除人工误差等数据，计算速度慢且不直观，既费时费力，计算精度不高。为提高工效和计算精确度，本

5、文利用Excel中功能强大的数据统计和表格制作软件创建土工试验成果表及各单元层主要物理力学性质统计表，结果表明划分地层简便、统计计算简单、计算过程直观、计算精确得到大幅提高，节约了大量计算时间。1 土工试验成果表（模板）的构建启动Excel 2003应用程序，按照土工试验成果表的格式创建表格如图1所示。图1中灰色单元格所示为可实现简便快速输入大量土工试验基本数据项。图1 Excel 2003应用程序构建的土工试验成果表Fig.1 Soil Test Result by Excel 20031.1 简便快速输入取样深度如图1所示，表中B列为取样深度，样品长度为0.2 m，在B7单元格中输入取样顶

6、深1.0 m按Enter即回车键即可实现1.01.2 m的输入。这个功能是由Excel嵌入语言VBA编制的可执行代码实现的。其代码的编译步骤如下。步骤1 选择【工具】【宏】【Visual Basic编辑器】菜单项（如图2）。图2 Visual Basic编辑器菜单Fig.2 Visual Basic Editor Menu步骤2 随即会弹出【Microsoft Visual Basic土工试验及力学性质统计表.xls】对话框（如图3）。图3 Visual Basic编辑器窗口Fig.3 Visual Basic Editor Window步骤3 选择对话框中【工程资源管理器】【sheet1（土

7、工试验）】【查看代码】菜单项（如图4）。图4 查看当前工作表代码窗口Fig.4 View the Current Worksheet Code Window步骤4 随即会弹出一空白代码窗口（如图5）。图5 空白工作表代码窗口Fig.5 Blank Worksheet Code Window步骤5 在空白代码空口添加以下代码（如图6）：Private Sub Worksheet_Change(ByVal Target As Range) Dim myRange As Range On Error GoTo myError Application.EnableEvents = False For

8、Each myRange In Target If myRange.Column = 2 And myRange.Text <> "" Then myRange.Value = Format(myRange.Value, "0.0 - ") & Format(myRange.Value + 0.2, "0.0") End If Next myRangemyError: Application.EnableEvents = TrueEnd Sub图6 添加代码后的窗口Fig.6 Window after Enteri

9、ng Code步骤6 单击按钮将输入的代码保存，然后关闭该程序。执行以上操作后，即可实现B列取样深度项的快速输入。1.2 快速输入常规土工试验指标因孔隙比、饱和度、干重度、含水比、塑性指数、液性指数、土的定名和压缩模量等指标均为计算项目，故根据各自的计算公式，采取在单元格中赋予相应的函数公式，并采用为函数公式命名法；当输入函数公式名称时，即可以自动执行公式，显示计算结果。图1中E列固定为孔隙比、F列固定为饱和度、G列固定为干重度、I列固定为含水比、L列固定为塑性指数、M列固定为液性指数、N列固定为土的定名和S列固定为压缩模量值。以孔隙比项为例，可在单元格E7中输入“孔隙比”，按回车键，即可在E

10、7单元格中得到一个数值，此数值即为此样品的孔隙比值；然后运用拖动当前单元格填充柄来实现自动计算并填充整列的孔隙比项数值。具体步骤如下。步骤1 选中土工试验成果表中E7单元格。步骤2 选择【插入】【名称】【定义】菜单项（如图7）。图7 定义菜单窗口Fig.7 Defined Menu Window步骤3 随即会弹出【定义名称】对话框，在对话框中的“在当前工作簿中的名称（w）：”文本框中输入公式名称“孔隙比”，在“引用位置（R）：”文本框中输入函数公式“=ROUND(H7/(G7*0.1)-1,3)”，然后点击按钮（如图8）。图8 定义名称窗口Fig.8 Defined Name Window步骤

11、4 在E7单元格中输入“孔隙比”，按回车键，即可在此单元格中显示计算结果（如图9）。图9 输入计算名称后显示的计算结果Fig.9 Computing Result after Entering the Calculation Name步骤5 拖动当前单元格（E7）填充柄实现自动填充全部样品的孔隙比数据。饱和度、干重度、含水比、塑指、液指、土的定名、压缩模量的公式（函数）分别为：ROUND(C7*H7/E7,0)、ROUND(D7/(1+C7*0.01),1)、=ROUND(C7/J7,3)，ROUND(J7-K7,1)、ROUND(C7-K7)/L7,2)、IF(L7>17,"

12、粘土",IF(L7>10,"粉质粘土",IF(L7< =10,"粉土")、ROUND( (1+E7)/Q7,2)。同理，可分别在F7、G7、I7、L7、M7、N7、S7单元格中输入“=饱和度”、“=干容重”、“=含水比”、“=塑指”、“=液指”、“土的定名”、 “压缩模量”，即可得到相对应样品的项目数值，再运用拖动单元格填充柄进行自动计算并填充全部样品的项目数据。这样就减少了因手工计算再输入数值的重复性工作，从而也减少了出错几率。2 各单元层物理力学性质统计表（模板）的构建在同一工作薄下依照各单元层主要物理力学性质统计表的格式创建表

13、格如图10所示。图10 Excel 2003应用程序构建的物理力学性质统计表Fig.10 Physical and Mechanical Properties Statistics by Excel 2003采取为每个所需统计数值的单元格定制相对应的函数公式的方法，以实现自动统计各单元层物理力学性质指标。如图9，将A7、B7单元格作为始终引用单元格，即在其单元格中指定所需统计样品在土工试验表中的位置，即行号。如若需统计土工试验表中第7行到第13行样品的数值，只需在图9中的A15单元格输入数字“7”，在B15单元格中输入数字“13”，即可立即得到物理力学性质统计表中的全部数值。3 结语excel是大家非常熟悉的应用软件，具有强大的制作表格、处理数据、分析数据、创建图表等功能，其使用非常方便。基于此构建的土工试验成果表（模板）和物理力学性质统计表（模板），可直接输出用于成果报告，大幅度降低了人员劳动强度，且分层统计计算简单、计算过程直观、计算数据精确，节约了大量计算时间，在很大程度上提高了工作效率，也在一定意义上节省了工程成本，提高了经济效益。参