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

巧用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形式查看。