EXCEL实现自动数值修约

     EXCEL实现自动数值修约          摘要：随着办公自动化软件的普及，运用MicrosoftExcel处理试验数据，能极大地提高我们的工作效率，可是，Excel没有数值修约的内置函数。作者运用Excel现有的一些内置函数构造了四舍六入单进双舍的数值修约式子。关键词：EXCEL；四舍六入；数值修约1概述在试验工作中对数据处理采取四舍六入单进双舍的数值修约方法，与常见的四舍五入法有一定差别。Excel中的ROUND函数对数值修约采取的是四舍五入法。所以常常会出现直接用ROUND函数对数值修约的结果与人工数值修约的结果不一致的现象。随着试验检测工作的发展，为了提高工作效率必须更多地借助于计算机进行数据处理。要使Excel能够更好地为数据处理服务就必须解决好ROUND函数不能够四舍六入单进双舍的数值修约问题。但是值得注意的是：在使用Excel进行数据处理时需要注意的是：在Excel中太大和太小的数据都无法正常显示，Excel2000可以表示的数值在-1×10^307与1×10^307之间，若超出了上述范围就会出现＃NUM！错误。Excel2003中，单元格中可键入的最大数值：9.99999999999999E+307，最大正数：1.79769313486231E+308，最小负数：-2.2251E-308，最小正数：2.229E-308最大负数：-2.2250738585073E-308，若超出了上述范围就会出现＃NUM！错误。国内的各种期刊曾多次介绍如何利用Excel自带公式来实现自动四舍六入单进双舍的数值修约方法。但笔者认为都比较复杂，不便于使用。下面笔者介绍一种非常简单但又科学有效地利用Excel公式来实现四舍六入单进双舍的数值修约四舍六人五化偶的方法。2数值修约规则通过省略原数值的最后若干位数字，调整所保留的末位数字，使最后得到的值最接近原数值的过程称为“数值修约”。[1]，它所遵循的规则称为“数字修约规”。根据《数值修约规则与极限数值的表示和判定（GB/T8170-2008）》中的规定，数据取用精度位数后一位数字，采用“四舍六入”的方法进行取舍。即取用精度位数后一位数字小于五者则舍，大于五者则入，等于五时若其后有非零尾数仍入，无非零尾数则视取用的末位数字的奇偶取舍，为奇则入，为偶则舍。具体规则如下：１．拟舍弃的数字的最左一位数字小于5，则舍去，保留其余各位数不变。例：将12.1498修约到个位数，得12；将12.1498修约到一位小数，得12.1２．拟舍弃的数字的最左一位数字大于5，则进一，即保留数字的末位数字加1。例：将1268修约到“百”数位，得13×103（特定场合可写为1300）。３．拟舍弃的数字的最左一位数字是5时，且其后有非0数字时进一，即保留数字的末位数字加1。例：将10.5002修约到个数位，11。４．拟舍弃的数字的左一位数字为5，且其后无数字或皆为0时，若所保留的末位数字为奇数（1，3，5，7，9）则进一，即保留数字的末位数字加1；若保留的末位数字为偶数（0，2，4，6，8）时，则舍去。例1：修约间隔为0.1（或10-1）拟修约数值修约值1.05010×10-1（特定场合可写成为1.0）0.354×10-1（特定场合可写成为0.4）例2：修约间隔为1000（或103）拟修约数值修约值25002×103（特定场合可写成为2000）35004×103（特定场合可写成为4000）５．拟修约数字应在确定修约或指定修约数位后一次修约获得结果，不得多次连续修约。[1]3为何要进行数值修约数值修约（俗称“四舍六入单进双舍”，“四舍六入逢五奇进偶舍”，“四舍六入五看齐”，“四舍六入，五看奇偶”或“四舍六入取单双”等）从理论上讲，“四舍六入”将前位数字的奇偶作为参考，使数位为“5”尾数为“0”的进位几率变为50％，使进位更合理。从数理统计的角度来看，数值修约的规则比“四舍五入”的规则优越。因此数值修约是一种比较精确比较科学的计数保留法。从统计学的角度分析，“四舍六入单进双舍”比“四舍五入”要更科学，在进行大量的运算时，它使舍入后结果的误差平均值趋向于零，而不是像四舍五入那样逢五就进或者进得多了，那么会使得结果偏大，导致出现误差积累进而产生系统误差，数值修约使得量测的结果没有受舍入影响太大。例如：1.15+1.25+1.35+1.45=5.2，若按常规的四舍五入取一位小数计算：1.2+1.3+1.4+1.5=5.4按数值修约计算，1.2+1.2+1.4+1.4=5.2，舍入后的结果更能反映实际结果。4EXCEL编写自动数值修约式子4.1数值修约式子的设计思路从数值修约的规则来看，数值修约与常见的四舍五入的差别主要在保留数后边（右边）的一个数字等于５时处理情况不同。自然地，我想到了用改良四舍五入的办法来编写数值修约的式子。以下部分是数值修约与四舍五入的不同之处：拟舍弃的数字中，保留数后边（右边）的一个数字等于５时，５后面的数字全部为0时，数值修约分两种情况：若保留的末位数字为奇数时，则进一；若保留的末位数字为偶数时，则舍去。因此，我将拟舍弃的数字四舍五入后，判断是否保留数后边（右边）的一个数字等于５且５后面的数字全部为0，若是则进行舍运算来补偿之前的入运算。数值修约流程图4.2数值修约式子结合实例应用及说明4.2.1.保留两位小数的自动数值修约假如在EXCEL中，要进行数值修约的单元格为“A1"，需要保留两位小数，则计算公式为：=ROUND（A1，2）-（MOD（A1*10^3，20）=5）*10^（-2）（保留小数点后二位）ROUND（A1，2）的解释：ROUND（A1，2）是对A1中的数取小数点后二位，且当小数点后第三位遵守四舍五入的规律。这样的效果就是五入的时候，可能在小数点后第二位奇或偶的时候进了一位，而小数点后第二位为偶数的时候进了一位是不正确的，有必要减去这个0.01。1.0149经round（A1，2）处理后为1.01；经数值修约后为1.01。1.0249经round（A1，2）处理后为1.02；经数值修约后为1.02。1.0150经round（A1，2）处理后为1.02；经数值修约后为1.02。1.0250经round（A1，2）处理后为1.03；经数值修约后为1.02。1.0151经round（A1，2）处理后为1.02；经数值修约后为1.02。1.0252经round（A1，2）处理后为1.03；经数值修约后为1.03。1.0348经round（A1，2）处理后为1.03；经数值修约后为1.03。1.0349经round（A1，2）处理后为1.03；经数值修约后为1.03。1.0350经round（A1，2）处理后为1.04；经数值修约后为1.04。1.0351经round（A1，2）处理后为1.04；经数值修约后为1.04。1.0151经round（A1，2）处理后为1.04；经数值修约后为1.04。1.0352经round（A1，2）处理后为1.04；经数值修约后为1.04。（MOD（A1*10^3，20）=5）*10^（-2）的解释：（MOD（A1*10^3，20）=5）*10^（-2）就是当A1*100后，（保证第三位的计入）除以20的模（MOD）=5时。这样减去0.01，这里只能是除以20才能保证奇偶不同。下面再来看看MicroSoftEXCEL对相关函数的解释。ROUND函数：round（number，num_digits），按指定的位数对数值进行四舍五入。MOD函数：Mod（number，pisor）返回两数相除的余数。如：mod（100，20）=5，返回FALSE；mod（325，20）=5，返回TRUE4.2.2．保留一位小数的自动数值修约EXCEL公式为：=ROUND（A1，1）-（MOD（LEFT（（A1*100），LEN（A1*100）），20）=5）*10^（-1）这里用了Left和Len函数是为了解决拟保留数的整数部分为两位数保留一位小数时，MOD函数出现的取模错误。4.3.3．保留三位小数的自动数值修约EXCEL公式为：=ROUND（A1，3）-（MOD（A1*10^4，20）=5）*10^（-3）4.4.4．保留N（N>1）位小数的自动数值修约EXCEL公式为：=ROUND（A1，n）-（MOD（A1*10^（n+1），20）=5）*10^（-n）；n>15.结语本文提出的对数值修约“四舍六入单进双舍”的程序设计处理思想，不同于以往的设计思路。以往的设计思路大多将舍入条件分得过细，因此式子容易变得冗长；另外以往大多是利用“IF”函数和“TRUNC”函数来实现数值修约规则[2]，“IF”函数也容易使得式子变得冗长，而本文提出了一种全新的思路，用MOD函数的逻辑来实现舍入要显得更精简。参考文献：[1]数值修约规则与极限数值的表示和判定GB/T8170-2008，3～4，2008.[2]章克来.如何在Excel中实现四舍六入五留双（J），IT与计量2008，（8）：1.[3]何欣，再谈Excel中实现四舍六入五留双（J），IT与计量2009，（1）：1～2. -全文完-