您好,欢迎访问三七文档
当前位置:首页 > 建筑/环境 > 工程监理 > 用Excel开发水分析实验计算程序
用Excel开发水分析实验计算程序梅州分局吴海斌摘要:本文介绍如何利用Excel强大的函数功能和VBA编程开发水分析实验计算程序。以分光光度法计算程序的开发为例,介绍了曲线拟和和检验、质量控制的Excel实现以及如何保证计算结果的完全正确性。使用本程序进行水分析实验计算能够到达极高的正确率,而且由于添加了完善的信息提示、错误控制和信息反馈机制,实验人员只要会操作Excel,几乎不用培训就能很好地使用该程序。关键字:水质分析,分光光度法,Excel函数,VBAExcel是微软公司开发的一套具有开发功能的办公软件,提供了极其方便数据输入方式,灵活的运用其函数和VBA扩展程序,能够制作出具有相当专业水平的水分析实验计算程序,下面以分光光度法计算程序的开发为例介绍Excel实现水分析实验数据计算程序的方法。本程序采用ExcelXP编制,程序界面如图1所示。图1水分析实验数据处理程序界面一、基本功能的实现分光光度法是利用在一定的测量范围内,当液层厚度不变的时,溶液吸光度与溶液浓度成正比(朗伯-比耳定律)的原理,先配置一系列不同浓度的标准溶液,分别测得其吸光度,然后进行线性回归求其回归方程,并对该方程进行线性检验和截距检验。下面来看看在Excel中如何实现。1.1曲线拟和的实现使用Excel中函数SLOPE(known_y's,known_x's)可以求得线性回归直线的斜率,使用INTERCEPT(known_y's,known_x's)函数可以求得截距,其中参数Known_y's为因变量数据点数组或单元格区域,即浓度系列,Known_x's为自变量数据点集合,即对应的吸光度系列。为了能够直观的看到拟和后的曲线,可以使用Excel的“图表”菜单功能来绘制。选择相应的浓度和吸光度数据系列,再右键点击曲线在“趋势线格式”中选择线性回归拟和,就可以得到回归曲线,如图2所示。图2回归曲线图例1.2线性检验和截距检验线性检验:分光光度法一般要求计算曲线的相关系数来检验曲线的精密度,采用函数CORREL即可以求得其相关系数,具体函数语法为CORREL(array1,array2),Array1即为浓度系列单元格区域,Array2即为吸光度单元格区域。截距检验:用来检验校准曲线的准确度,将截距与0作检验,取95%的置信水平,检验有无显著性差异。具体计算步骤为:首先需要计算出剩余标准差,它反映了回归直线与实测点偏差的程度,,可以采用函数STEYX(known_y's,known_x's)来计算(参数含义同上),然后计算其截距标准差,,则,最后与临界(为自由度,)值相比较,和可以采用常规的Excel四则运算公式来实现,而可以采用函数TINV(probability,degrees_freedom)来求解,Probability为对应于双尾分布的概率,Degrees_freedom为分布的自由度,要注意的是这里的分布为双尾值,而这里要求单尾值,需要通过用两倍概率替换概率求得,即在这里,probability为2×0.05=0.1。算得回归方程后,将检测得到的吸光度值代入,即可求得相应的浓度值。但是由于回归方程数据修约存在问题,这样算出得结果正确性很低,下面来看一下如何来保证结果的完全正确性。二、如何保证结果的完全正确水质分析计算最重要的就是结果要完全正确,如果计算结果不能保证完全正确,即使计算错误率不高,也会使程序的实用价值大打折扣。下面来看看如何运用Excel来保证数据的正确性。2.1数据的正确修约在水质分析计算中水样浓度计算结果保留的小数位数一般和检出限的小数位数相同,对检出限求其对数后再取整就可以得到检出限的小数位数,用Excel公式表示即为:-INT(LOG10(检出限)),而回归方程的斜率和截距则保留多一位小数即可。在Excel中使用函数ROUND(number,num_digits)进行小数位数的修约(Number为需要进行四舍五入的数字,Num_digits则为指定的位数)。但是这个函数是采用四舍五入的方式进行的,不满足“四舍六入五单双”的数据修约原则,有可能使计算结果出错,因此要使用ExcelVBA来编一个能够满足该原则的函数,以消灭错误隐患。注意到“四舍六入五单双”法与四舍五入法当且仅当要修约的尾数恰好是“5”时数据结果才会有不同,如果不是“5”或“5”后面还有数字,则可以采用四舍五入法修约;当尾数恰好为“5”时,判断前一位的数字是奇数还是偶数,再分别处理。具体算法用例子来说明,如0.0325需要保留3位小数时,首先将它乘以(为要保留的小数位数),这里,=3,就得到32.5,再减去对其整数部分,如值恰好为0.5,这表明要修约的尾数恰好是“5”,这就需要判断32.5的整数部分是奇数还是偶数,如为偶数,则只取其整数部分,即不进位;如为奇数,则加1,相当于进一位,再除以,所以0.0325经这样处理后就成了0.032,而如果是0.0295就会进一位,最后结果为0.030。具体程序如下,其函数定义中参数的含义与函数ROUND的定义相同PublicFunctionfuncRound(numberAsSingle,num_digitsAsInteger)AsSingleDimdecAsSingleDimD1AsSingleDimd2AsSingleDimOutputAsSingle’定义结果输出变量d2=Application.WorksheetFunction.Power(10,num_digits)D1=number*d2’乘以dec=D1-Int(D1)Ifdec=0.5Then’即尾数为恰好为“5”时分奇、偶来分别计算IfInt(D1)Mod2=0ThenOutput=Int(D1)/d2ElseOutput=(Int(D1)+1)/d2EndIfElseOutput=Application.WorksheetFunction.Round(number,num_digits)EndIffuncRound=Application.WorksheetFunction.Round(Output,num_digits)EndFunction相关系数是向下舍入数字,可采用Excel函数ROUNDDOWN(number,num_digits)来修约。另外提一下在容量法中是按有效数字来取舍结果,则可以采用下列公式来修约:有效数字个数-1-INT(LOG10(ABS(需修约的数据)))。2.2数据的正确输入1)防止数据输入错误的地方:如果实验人员误将数据输入到通过公式计算的单元格中,那么程序可能会失去计算功能,使用Excel“工具”中的“保护”子菜单下的“保护工作表”可以实现对公式单元格的保护。另外将可以输入数据的单元格背景色改为绿色,以方便实验人员识别。2)防止输入错误的数据:如果实验人员输入错误的数据类型或超出数据允许的范围,那么结果可能无法正确显示,可以在“数据”菜单的“有效性”中进行设置,防止此类错误发生。如将“检出限”有效性设置成“大于0”,则当实验人员输入小于或等于0的数据时,会出现输入错误的提示,另外单元格设有必要的说明信息,方便实验人员了解单元格的数据要求,也可以减少此类错误,图3展示了说明信息提示和错误提示窗口。图3检出限单元格的说明信息和输入“-1”时的错误提示对话框2.3水样浓度计算结果的正确显示当计算出的水样浓度低于检出限的时候结果必须显示“检出限”,要实现这种效果,需要使用条件函数IF(logical_test,value_if_true,value_if_false),其意义是判断logical_test是否成立,如果成立就执行value_if_true,否则执行value_if_false,在运用时将结果单元格写成以下公式:IF(水样浓度检出限,“检出限”,计算结果),即可显示正确得结果(注意这里的公式只是便于说明,具体的程序中的公式与单元格的定位有关)。采取了这些保证正确性的措施以后,就可以极大的提高数据计算结果的正确性。在程序实际运用过程中也没有发生过计算结果错误的问题。三、质量控制计算质量控制是水分析实验数据计算中较重要的一部分,该程序能够计算常用的质量控制项目如空白偏差、平行偏差、加标回收、标准样品偏差等,都采用简单的Excel公式就可计算出,主要难点在于如何让计算机正确判断质控项目并能够准确的定位对应的水样浓度,为此,需要加一个“质量控制标识”列,然后综合使用Excel函数MATCH(lookup_value,lookup_array,match_type)和INDEX(array,row_num,column_num)组合来定位相应的质控水样浓度。如要定位“加标回收”样的浓度,可采用公式:INDEX(水样浓度结果列,MATCH(加标回收,质量控制标识列,0)),含义是在质量控制标识列中查找名为“加标回收”的那一行,然后返回该行对应的水样浓度结果。四、完善的信息反馈和辅助功能水分析实验数据处理中常常会遇到结果数据超出规定范围的情况,如截距大于检出限,水样吸光度超过曲线最高浓度的吸光度,空白偏差大于50%等。这就需要程序能够捕捉到这些情况,并且以明显的提示信息反馈给实验人员。如图4所示当水样吸光度超过曲线最高浓度的吸光度时旁边的单元格会显示红色的提示。要实现这种效果,同样需要使用IF函数,另外还可以使用“格式”菜单中的“条件格式”来使文字变成红色,达到更加醒目的效果。图4水样吸光度超过曲线最高浓度的吸光度时显示的提示信息对于数据的保存可以直接采用Excel的“另存为”的菜单功能,以后打开即可查看。但对于不熟悉Excel的实验人员操作时可能会覆盖原有数据,因此在程序中提供了一个“保存表格”的按钮。当实验人员点击该按钮时,会弹出“另存为”的文件保存窗口,其文件名采用“分析项目名称”加系统时间的方式。这样可以有效的区分各批数据。由于该水分析实验数据处理程序本身是在实践过程中不断修改完善的,所以很多功能都切实贴近实验人员的实际工作需要。实践表明,Excel强大的菜单功能、函数和VBA扩展使得开发人员能够迅速的开发出正确度高且易于操作的水环境计算程序。在水质分析、水质年度资料整编、水环境评价和水资源综合规划中运用Excel设计程序进行计算,能够在保证正确性的基础上,极大地提高工作效率。参考文献:[1]JohnWalkenbach.Excel2002公式与函数应用宝典[M].电子工业出版社,2002.7[2]陈立春.仪器分析[M].中国轻工业出版社,2002.3:14~16.[3]国家环境保护总局水和废水监测分析方法编委会.水和废水监测分析方法[M].中国环境科学出版社.2002.12,第四版:30~31.[4]蔡宝森.环境统计[M].武汉工业大学出版社,2002.6:165~166.
本文标题:用Excel开发水分析实验计算程序
链接地址:https://www.777doc.com/doc-6580801 .html