巧用excel绘制颗粒级配曲线与自动计算粒组特征参数

摘要：颗粒分析试验为基础土工试验之一，其成果对土样定名及物理力学性质的判断都有着重要意义。由于试验原始数据繁多，处理步骤繁杂，而以往试验室对颗分数据多为人工处理，导致颗粒分析数据处理工作量大，且结果易出错。利用excel的函数和图表功能，可实现颗粒分析试验从原始数据计算、颗粒级配曲线绘制到粒组特征参数计算的数据自动处理，减少人为影响，提高工作效率及计算准确度。

关键字：颗粒级配曲线；粒组特征参数；自动计算

1.颗粒分析试验数据处理的基本流程

颗粒分析是通过测定干土中各粒组所占该土总质量的百分数的方法，借以明了颗粒大小分布情况，供分类土性、判断土的工程性质及选料之用[1]，其基本原理参见文献[1]、[2]。

本文仅讨论筛析法联合密度计法的颗粒分析自动化数据处理。其数据处理分为五个基本步骤：①筛析法计算0.075mm以上颗粒大小及含量②密度计法计算0.075mm以下颗粒大小及含量③绘制颗粒级配曲线④从颗粒级配曲线上读取粒组特征参数和粒径含量的辅助数据（粒径0.05mm,0.005mm,0.002mm所对应的含量百分比）⑤计算粒径含量表。

试验成果包括颗粒级配曲线，粒组特征参数（d10，d30，d50，d60，不均匀系数Cu，曲率系数Cc）及粒径含量表。

2.目前颗粒分析电化处理数据的状况

随着计算机的普及，目前很多试验室已经开始用软件来完成颗粒分析数据的电化处理，常见的主要是办公软件Excel[3][4]和绘图软件AutoCAD[5]。据笔者了解，目前试验室用Excel处理颗粒分析数据的方式还主要局限于常规计算和绘制颗分曲线[3][4]，而粒组特征参数及粒径含量的辅助数据仍采用人工读取，这样的做法导致数据精度因人而异，且结果易发生量级错误；用AutoCAD处理颗粒分析数据，虽已通过编制AtuoLISP程序自动计算出粒组特征参数，但由于AutoCAD的优势在于图形处理而非数据处理，因此还要利用Excel表格进行数据前期准备，然后用AtuoLISP在AutoCAD中进行绘图和简单计算[5]。

3.新的解决思路

Excel能够较为方便地完成步骤①～③的计算及绘图，主要的困难是如何实现第④步的模块化。鉴于图表的数据源是两列相关的数组，因此考虑直接通过寻找两列数组的关系，用数值拟合的方式来自动求解特征参数和辅助数据。

文献[3]提出了用方程法（对数曲线）计算和插值法计算的两种思路，考虑到有些曲线运用对数曲线求解误差较大，且实际试验过程中采集的数据点足够多，颗粒级配曲线上两点间线性关系较好，因此本文依据插值法来编制自动处理颗粒分析数据模块。

本次列举的Excel方案分三个板块：(1)原始数据输入，(2)颗粒大小计算及颗粒级配曲线绘制,(3)粒组特征参数及粒径含量计算。本文仅讨论后两个计算板块。

3.1颗粒大小计算及颗分曲线绘制

主要计算区分三部分，第一部分为筛析法颗粒大小及含量计算；第二部分为密度计法颗粒大小及含量计算，第三部分为粒径含量计算。计算公式可依照参考文献[1]、[2]的基本原理编制，也可以套用参考文献[6]示例的Excel公式，本文不再累叙。建议密度计法所需要的分散剂校正值、温度校正值及液体动力粘滞系数单独建立数据库，计算时用vlookup函数索引。

将筛析法和密度计法的计算结果，即“粒级”及“小于该粒径的总土质量百分数”，通过“插入”-“图表”-“散点图” 绘制曲线，数据X对应“粒径”，数据Y对应“小于该粒径的总土质量百分数”。在“设置坐标格式”中，将X坐标选择为“对数刻度（基底为10）”和“逆序刻度”。

3.2粒组特征参数及辅助数据自动计算

用Excel来实现插值法，笔者的基本思路是：对已知对应的两组数列{X}和{Y}，通过[X]查[Y]，则先利用index、match函数查找[X]前、后数据[X1]、[X2]，以及彼此对应的[Y1]、[Y2]，然后用trend函数插值得[Y]。

例如要自动计算控制性参数d60（即[X]=60%,[Y]表示累计含量为60%时对应的粒径值），颗粒 “粒径”在表格A1:N1区间，其对应“小于该粒径的总土质量百分数”在表格A2:N2区间，则首先可在[X1]对应的表格A3中输入“=INDEX(A2:M2,MATCH(60,A2:M2,1))”，在[X2]对应的表格A4中输入“=INDEX(A2:M2,MATCH(60,A2:M2,1)+1)”；在[Y1]对应的表格A5中输入“=INDEX(A1:M1,MATCH(60,A2:M2,1))”，在[Y2]对应的表格A6中输入“=INDEX(A1:M1,MATCH(60,A2:M2,1)+1)”，最后在[Y]对应的表格A7中输入“=TREND(A5:A6,A3:A4,60)”即可得最终插值结果。

要计算辅参数中粒径0.05mm所对应的含量百分比，则可在[X1]对应的表格B3中输入“=INDEX(A1:M1,MATCH(0.05,A1:M1,1))”，在[X2]对应的表格B4中输入“=INDEX(A1:M1,MATCH(0.05,A1:M1,1)+1)”；在[Y1]对应的表格B5中输入“=INDEX(A2:M2,MATCH(0.05,A1:M1,1))”，在[Y2]对应的表格B6中输入“=INDEX(A2:M2,MATCH(0.05,A1:M1,1)+1)”，最后在[Y]对应的表格B7中输入“=TREND(B5:B6,B3:B4,0.05)”即可得最终插值结果。

控制性参数为最终提交数据，辅数据将被引用到3.1节提到的粒径含量计算中。

4.结语

Excel拥有强大的函数计算和绘图功能，利用其建立的表格系统是完全可以将数据处理模板化，试验人员仅需要通过简单培训，准确填入原始数据，即可获得准确的处理结果，既节省计算时间，又减少人为误差，所以在未来的工作中，可积极推广Excel函数在试验数据处理中的运用。

需注意的是，使用函数自动处理试验数据时，须仔细检验公式的有效性，不可盲目套用，毕竟函数存在适用条件，而试验状况灵活多变，公式编辑不可能一劳永逸。

参考文献

[1]SL237-1999.《土工试验规范》.[S].

[2]GB50123-1999.《土工试验方法标准》.[S].

[3]潘福营.《利用excel自动绘制颗粒级配曲线的方法》.[J].四川水力发电. 2010.V29.NO3.48-49.

[4]罗小芳.《巧用excel软件处理颗粒分析数据并绘制曲线图》.[J].西部探矿工程. 2006.NO126. 319-320

[5]赵从耕.《绘制颗分曲线图和自动计算粒组特征参数》.[J]工程地质计算机应用.2006.NO42.32-33

[6]张龙，郭林涛，洪镝编著.《水利水电工程试验数据计算分析方法》.[M] .中国水利水电出版社发行.2009年9月第1版.159-162.

注：文章内所有公式及图表请以PDF形式查看。