|
葛洲坝集团试验检测有限公司 湖北宜昌 443002
摘要：Microsoft Excel作为最常用的日常办公软件，加之其强大的数据处理功能，在试验结果处理过程中被广泛使用，然而试验结果修约作为一种专项规定，其自带数据修约功能不能满足试验检测专业的独特要求，必须通过特定功能编辑才能实现，为了能够利用Excel实现快速准确的试验结果准确修约，提高试验结果处理的效率和准确性，并能使普通试验人员从繁琐的试验数据处理中解放出来，笔者通过对Excel相关公式的长期实践，提出了一种准确可靠的处理方法。
关键词：Excel；函数；试验结果；数据修约；应用
1 引言
Excel作为基本的办公软件被广泛使用于日常办公，在试验结果处理过程中，如何使用Excel函数公式进行试验结果的高效准确处理，并未得到普及，Excel中与修约相关的方法主要有两种，一种是通过编辑Visual Basic for Application语言进行修约，但VBA程序语言对于普通使用者难度较大，不便普遍推广使用；另一种是Excel自带修约公式Round函数，以及其组合函数RoundDown和Roundup，无论是Round还是其组合函数虽都可以实现四舍五入但无法实现分奇偶进行取舍的情况，不能满足试验数据处理需要；本文通过综合分析和实践，提出了一种简单易学的修约方法，IF条件函数以及常用函数的组合，可轻松实现四舍六入五分奇偶修约以及其他倍数类修约，普通使用者通过本文的学习即可轻松掌握，并可通过学习深入举一反三处理更为复杂的试验数据。
2 原理介绍
本文按照GB8170-2008标准规定的试验数据修约要求，采用Excel函数进行数据修约处理，按照基本的数据修约思路将修约步骤用Excel函数进行准确表达，并成功实现规范修约。
首先使用到的是IF条件函数和其多重嵌套功能，例如基本的IF条件函数IF（A＞B，C，D），意义为如果A＞B成立，那么就输出C，否则的话就输出D，其多层嵌套函数比如IF（A＞B，IF（A＜E，C，D）），意为如果A＞B成立，同时A＜E，就输出C，否则的话就输出D，这是最基本的IF条件函数嵌套，最多可嵌套7层；
其次用到了最常用的INT、ABS、MOD和RIGHT基本函数作为辅助，INT函数输出结果为括号内数值截尾的整数，不考虑数值修约，例如INT（32.6）会输出32，ABS函数为输出一个数值的绝对值，主要针对负数情况下的数据修约，例如ABS（-32.5）=32.5，MOD（A，n）函数输出结果为数值A除以n的余数，在判断奇偶的过程中十分有效，例如Mod（A，2）若输出为0则可判断A为偶数，否则为奇数；Right（A，n）函数输出结果为数值A的右侧n位数，在判断奇偶的过程中找出特定小数点位数的数值并判断其奇偶性，例如Right（32.2，1）输出结果为2；将这几种常用的函数进行有效的组合即可轻松实现试验数据的准确修约；
3 逻辑组合思路
对于一个基本数据比如AB.CDEF（字母代表任意数值，下同），比如要修约到小数点后1位小数，按照GB修约规则修约的基本思路是先找出小数点后第2位及之后的数值0.0DEF，然后判定其与0.05比的大小，若大于0.05则直接进位，若小于0.05则直接舍去，这两种情况比较容易处理；复杂的是等于的情况，需要进行进一步的判断，先要选出小数点后第1位，然后判断是奇数还是偶数，再根据奇进偶不进的原则进行取舍；对于熟练的普通使用者而言十分简单，但是对于计算机而言则需要通过编辑带有逻辑规则的公式来一步一步实现选择。 本文以AB.CDEF为例进行说明：
第一步，如果要修约到n位小数，第一步是输出第n位之后剩余小数，用Excel函数输出则可表示为=ABS（AB.CDEF）*10^n-INT（ABS（AB.CDEF）*10^n），例n=1时ABS（AB.CDEF）*10^1-INT（ABS（AB.CDEF）*10^1）=ABC.DEF-ABC=0.DEF，然后判断0.DEF与0.5的大小，如果ABS（AB.CDEF）*10^1-INT（ABS（AB.CDEF）*10^1）=0.DEF=0.5，也即E、F均为0，此时需进行下一步判断；
第二步，判断C的奇偶性，因为修约小数点位数的不同，对计算机而言需要一个通用公式用于找出C（C可能随着修约位数的变化变成D、E等），此时需采用INT函数和Right函数的组合，函数（RIGHT（INT（ABS（AB.CDEF）*10^1），1），意为将AB.CDEF取绝对值乘以10n然后取整数并输出其右侧第一位数字，显然输出为C，然后判断C的奇偶性，众所周知能被2整除则为偶数，否则为奇数，此时需用Mod函数MOD（C，2），将输出C的组合函数带入MOD（C，2），也即Mod（（RIGHT（INT（ABS（AB.CDEF）*10^1），1），2），如果输出结果为0表示能被2整除，则C为偶数，否则为奇数；
第三步，判断出奇偶性后需要进行选择性取舍，此时，若为偶数则舍去，采用只向下修约的ROUNDDOWN（AB.CDEF，n），若为奇数则进位，采用只向上修约的ROUNDUP（AB.CDEF，n）函数；
第四步，等于0.5且奇偶性判断完成后还有两种可能性，即大于0.5和小于0.5，此时需进行第三次判断，若AB.CDEF*10^n-INT（AB.CDEF*10^n）＞0.5，采用只向上修约的ROUNDUP函数，剩下只有一种可能即AB.CDEF*10n-INT（AB.CDEF*10n）＜0.5，采用只向下修约的ROUNDDOWN函数即可。
第五步，IF条件函数和其嵌套函数的组合，前四步只是分步进行部分选择性运算，之后需要通过IF条件函数进行系统的逻辑组合才能完成整体判断和运算，按照四个步骤的先后顺序编辑嵌套的IF条件函数。第一层IF条件函数可理解为：如果第一步成立，则运行第二步和第三步，否则的话运行第四步，即：IF（第一步，（第二步，第三步），第四步），其中第二部分的（第二步、第三步）是判断奇偶并取舍的过程，表达为如果C为偶数则舍去，否则的话进位，即IF（C为偶数，舍去，进位）；第四步也需要判断是＞0.5还是＜0.5（此处已无等于0.5的情况），也需要If条件函数，表达为如果大于0.5则进位，否则的话舍去，即IF（0.DEF>0.5，进位，舍去），将四步分别代入并按照相应的逻辑关系合成之后为：IF（0.DEF=0.5，IF（C为偶数，舍去，进位），IF（0.DEF>0.5，进位，舍去））
第六步，将单独意义的基本函数和其简单组合进行有效逻辑组合，组成Excel默认的完整数值函数运算公式，其通用公式为：=IF（（ABS（AB.CDEF）*10^n-INT（ABS（AB.CDEF）*10^n））=0.5，IF（MOD（RIGHT（INT（（ABS（AB.CDEF）*10^n），1），2）=0，ROUNDDOWN（AB.CDEF*10^n，n）*10^n，ROUNDUP（AB.CDEF*10^n，n）*10^n），IF（（ABS（AB.CDEF）*10^n-INT（ABS（AB.CDEF）*10^n））>0.5，ROUNDUP（AB.CDEF*10^n，n）/*10^n，ROUNDDOWN（AB.CDEF*10^n，n）/*10^n））（其中n取整数）
4其他修约类型
试验数据修约除了基本的四舍六入五分奇偶以外，5倍、0.5倍修约，0.2倍修约等也都是经常用到的修约规则，其基本修约思路是先进行一定的数据转换，转换成可以采用最基本的四舍六入五分奇偶进行修约，然后再恢复至相应倍数修约即可。
0.5倍修约：通常是将拟修约数值乘以2，然后按照四舍六入五奇进偶舍的修约原则修约至整数，之后再除以2即可；对5倍、50倍、5*10n等更多位数的修约，可采用乘以2*10-n，再修约至整数，再除以2*10-n；用Excel函数运算公式表示其通用修约公式为：
=IF（（ABS（AB.CDEF）*2*10^-n-INT（ABS（AB.CDEF）*2*10^-n））=0.5，IF（MOD（RIGHT（INT（ABS（AB.CDEF）*2*10^-n），1），2）=0，ROUNDDOWN（ABS（AB.CDEF）*2*10^-n，0）/（2*10^-n），ROUNDUP（ABS（AB.CDEF）*2*10^-n，0）/（2*10^-n）），IF（（ABS（AB.CDEF）*2*10^-n-INT（ABS（AB.CDEF）*2*10^-n））>0.5，ROUNDUP（ABS（AB.CDEF）*2*10^-n，0）/（2*10^-n），ROUNDDOWN（ABS（AB.CDEF）*2*10^-n，0）/（2*10^-n）））（其中n取整数）
0.2倍修约：通常是将拟修约数值乘以5，然后按照规定的修约位数四舍六入五奇偶的修约原则修约修约至整数，之后再除以5即可；对于2倍、20倍、2×10n等更多位数的修约，可采用乘以5×10-n，再修约至整数，再除以5×10-n，用Excel函数运算公式表示其通用修约公式为：
=IF（（ABS（AB.CDEF）*5*10^-n-INT（ABS（AB.CDEF）*5*10^-n））=0.5，IF（MOD（RIGHT（INT（ABS（AB.CDEF）*5*10^-n），1），2）=0，ROUNDDOWN（ABS（AB.CDEF）*5*10^-n，0）/（5*10^-n），ROUNDUP（ABS（AB.CDEF）*5*10^-n，0）/（5*10^-n）），IF（（ABS（AB.CDEF）*5*10^-n-INT（ABS（AB.CDEF）*5*10^-n））>0.5，ROUNDUP（ABS（AB.CDEF）*5*10^-n，0）/（5*10^-n），ROUNDDOWN（ABS（AB.CDEF）*2*10^-n，0）/（2*10^-n）））（其中n取整数）
5 结语
1.本文只是Excel函数在试验数据修约过程中的一个基本例子，熟悉掌握IF条件函数及其多层嵌套函数后，可以自行编辑多层条件选择公式的嵌套公式，满足更为复杂的多条件运算和复杂数据处理；
2.文中AB.CDEF可以是任意值，也可以是独立的加减乘除幂等基本运算也可以是具有单独意义的运算函数，如AVERAGE（A1：B6）。
参考文献：
[1]GB/T 8170-2008 《数值修约规则与极限数值的表示和判定》，中国标准出版社，2008版
[2]陈剑锋，建筑工程材料检测数据的数值修约在Excel中的实现，中国房地产业，2013年12期
本文以AB.CDEF为例进行说明：
第一步，如果要修约到n位小数，第一步是输出第n位之后剩余小数，用Excel函数输出则可表示为=ABS（AB.CDEF）*10^n-INT（ABS（AB.CDEF）*10^n），例n=1时ABS（AB.CDEF）*10^1-INT（ABS（AB.CDEF）*10^1）=ABC.DEF-ABC=0.DEF，然后判断0.DEF与0.5的大小，如果ABS（AB.CDEF）*10^1-INT（ABS（AB.CDEF）*10^1）=0.DEF=0.5，也即E、F均为0，此时需进行下一步判断；
第二步，判断C的奇偶性，因为修约小数点位数的不同，对计算机而言需要一个通用公式用于找出C（C可能随着修约位数的变化变成D、E等），此时需采用INT函数和Right函数的组合，函数（RIGHT（INT（ABS（AB.CDEF）*10^1），1），意为将AB.CDEF取绝对值乘以10n然后取整数并输出其右侧第一位数字，显然输出为C，然后判断C的奇偶性，众所周知能被2整除则为偶数，否则为奇数，此时需用Mod函数MOD（C，2），将输出C的组合函数带入MOD（C，2），也即Mod（（RIGHT（INT（ABS（AB.CDEF）*10^1），1），2），如果输出结果为0表示能被2整除，则C为偶数，否则为奇数；
第三步，判断出奇偶性后需要进行选择性取舍，此时，若为偶数则舍去，采用只向下修约的ROUNDDOWN（AB.CDEF，n），若为奇数则进位，采用只向上修约的ROUNDUP（AB.CDEF，n）函数；
第四步，等于0.5且奇偶性判断完成后还有两种可能性，即大于0.5和小于0.5，此时需进行第三次判断，若AB.CDEF*10^n-INT（AB.CDEF*10^n）＞0.5，采用只向上修约的ROUNDUP函数，剩下只有一种可能即AB.CDEF*10n-INT（AB.CDEF*10n）＜0.5，采用只向下修约的ROUNDDOWN函数即可。
第五步，IF条件函数和其嵌套函数的组合，前四步只是分步进行部分选择性运算，之后需要通过IF条件函数进行系统的逻辑组合才能完成整体判断和运算，按照四个步骤的先后顺序编辑嵌套的IF条件函数。第一层IF条件函数可理解为：如果第一步成立，则运行第二步和第三步，否则的话运行第四步，即：IF（第一步，（第二步，第三步），第四步），其中第二部分的（第二步、第三步）是判断奇偶并取舍的过程，表达为如果C为偶数则舍去，否则的话进位，即IF（C为偶数，舍去，进位）；第四步也需要判断是＞0.5还是＜0.5（此处已无等于0.5的情况），也需要If条件函数，表达为如果大于0.5则进位，否则的话舍去，即IF（0.DEF>0.5，进位，舍去），将四步分别代入并按照相应的逻辑关系合成之后为：IF（0.DEF=0.5，IF（C为偶数，舍去，进位），IF（0.DEF>0.5，进位，舍去））
第六步，将单独意义的基本函数和其简单组合进行有效逻辑组合，组成Excel默认的完整数值函数运算公式，其通用公式为：=IF（（ABS（AB.CDEF）*10^n-INT（ABS（AB.CDEF）*10^n））=0.5，IF（MOD（RIGHT（INT（（ABS（AB.CDEF）*10^n），1），2）=0，ROUNDDOWN（AB.CDEF*10^n，n）*10^n，ROUNDUP（AB.CDEF*10^n，n）*10^n），IF（（ABS（AB.CDEF）*10^n-INT（ABS（AB.CDEF）*10^n））>0.5，ROUNDUP（AB.CDEF*10^n，n）/*10^n，ROUNDDOWN（AB.CDEF*10^n，n）/*10^n））（其中n取整数）
4其他修约类型
试验数据修约除了基本的四舍六入五分奇偶以外，5倍、0.5倍修约，0.2倍修约等也都是经常用到的修约规则，其基本修约思路是先进行一定的数据转换，转换成可以采用最基本的四舍六入五分奇偶进行修约，然后再恢复至相应倍数修约即可。
0.5倍修约：通常是将拟修约数值乘以2，然后按照四舍六入五奇进偶舍的修约原则修约至整数，之后再除以2即可；对5倍、50倍、5*10n等更多位数的修约，可采用乘以2*10-n，再修约至整数，再除以2*10-n；用Excel函数运算公式表示其通用修约公式为：
=IF（（ABS（AB.CDEF）*2*10^-n-INT（ABS（AB.CDEF）*2*10^-n））=0.5，IF（MOD（RIGHT（INT（ABS（AB.CDEF）*2*10^-n），1），2）=0，ROUNDDOWN（ABS（AB.CDEF）*2*10^-n，0）/（2*10^-n），ROUNDUP（ABS（AB.CDEF）*2*10^-n，0）/（2*10^-n）），IF（（ABS（AB.CDEF）*2*10^-n-INT（ABS（AB.CDEF）*2*10^-n））>0.5，ROUNDUP（ABS（AB.CDEF）*2*10^-n，0）/（2*10^-n），ROUNDDOWN（ABS（AB.CDEF）*2*10^-n，0）/（2*10^-n）））（其中n取整数）
0.2倍修约：通常是将拟修约数值乘以5，然后按照规定的修约位数四舍六入五奇偶的修约原则修约修约至整数，之后再除以5即可；对于2倍、20倍、2×10n等更多位数的修约，可采用乘以5×10-n，再修约至整数，再除以5×10-n，用Excel函数运算公式表示其通用修约公式为：
=IF（（ABS（AB.CDEF）*5*10^-n-INT（ABS（AB.CDEF）*5*10^-n））=0.5，IF（MOD（RIGHT（INT（ABS（AB.CDEF）*5*10^-n），1），2）=0，ROUNDDOWN（ABS（AB.CDEF）*5*10^-n，0）/（5*10^-n），ROUNDUP（ABS（AB.CDEF）*5*10^-n，0）/（5*10^-n）），IF（（ABS（AB.CDEF）*5*10^-n-INT（ABS（AB.CDEF）*5*10^-n））>0.5，ROUNDUP（ABS（AB.CDEF）*5*10^-n，0）/（5*10^-n），ROUNDDOWN（ABS（AB.CDEF）*2*10^-n，0）/（2*10^-n）））（其中n取整数）
5 结语
1.本文只是Excel函数在试验数据修约过程中的一个基本例子，熟悉掌握IF条件函数及其多层嵌套函数后，可以自行编辑多层条件选择公式的嵌套公式，满足更为复杂的多条件运算和复杂数据处理；
2.文中AB.CDEF可以是任意值，也可以是独立的加减乘除幂等基本运算也可以是具有单独意义的运算函数，如AVERAGE（A1：B6）。
参考文献：
[1]GB/T 8170-2008 《数值修约规则与极限数值的表示和判定》，中国标准出版社，2008版
[2]陈剑锋，建筑工程材料检测数据的数值修约在Excel中的实现，中国房地产业，2013年12期
作者简介：
刘洋洋、男，汉族，主要研究方向建筑材料试验与检测；
|
