这天，雷哥找到星爷诉苦，因为他碰到了一个无比棘手的问题：使用Excel做最简单的求和计算时，得出的结果总是不对，每次都是错0.01的样子，如下图所示。 图1：简单的加法运算 其实Excel具有15位有效数字的计算精度，对于上图中的两位小数来说，应该手到擒来才对，出现这样的错误确实让人奇怪。 这个问题要分两种情况讨论。 情况1：浮点运算误差，这种情况确实是Excel的责任，我们应该讨伐Excel； 情况2：是我们使用Excel的姿势不对导致的错误，我们应该自我检讨。 Part 1 浮点运算误差 浮点运算误差通常出现在使用小数运算，或运算过程中包含小数的情况中，在减法和除法中比较多见，这算误差的出现是随机的，并不一定总能碰到。但是，以目前的科技，这种误差总是可观存在的。 1-1 出现的原因： ①由于计算机内部以二进制保存，所以十进制的有限位的小数，在计算机内部会是一个无限位的小数。 例如：十进制的0.9虽然只有一位小数，转成2进制是无限循环小数0.1110011001100110011... ②软件保存浮点数的精度有限，Excel可以保存15位有效数字，有效数字以后的就被忽略了。 1-2 浮点运算案例 如下图所示，在C3单元格中写入简单的计算=5.1-5.2+1，计算结果毫无争议应该是0.9，但是如果设置足够多的小数位数，会发现Excel的计算结果并不等于0.9。 本案例来自：《Excel这么用就对了》 这虽是一个很小的误差，但是它毕竟不等于真实的数值。如果出现在工程中，甚至可能造成重大的安全事故。 1-3 解决方法 Excel 提供了多种函数来弥补舍入误差，通常使用ROUND 函数强制四舍五入即可。 如图所示，使用ROUND函数矫正误差。 Part 2 人为误差 从前文可以看到，浮点运算产生的误差非常小，而且不是随机出现，像图1中出现0.01这么大的误差，通常不是浮点运算造成的，而是我们被Excel欺骗了。 2-1 出现的原因： 通常是在实际的使用过程中，为了显示上的需要，对单元格数值进行了格式设置，使其只显示一部分小数位数。 比如：单元格中的数值是49.995，在【设置单元格格式】→【数字】→【数值】中设置小数位数为2位。
设置小数位数为2位
知道了这个细节，再来看一下雷哥出错的数据，立刻就能找到错误的根源的。当我们鼠标点到A1单元格时，发现在单元格中显示的数据是50.00，但是在编辑栏中，实际的数值却是49.995。
当我们鼠标点到A2单元格时，发现在单元格中显示的数据是149.99，但是在编辑栏中，实际的数值却是149.985。
也就是说，因为对数值进行了格式设置，只能显示小数点后两位，所以原来三位小数被“四舍五入”为两位小数，注意，这个四舍五入是假的，只是表面的四舍五入。 如果是“五入”，那么显示的数值就比实际数值大；如果是“四舍”，那么显示的数值就比实际数值小。雷哥的两个数值恰好都是“五入”，所以导致显示的两个数值的和就比实际数值的和大了0.01。 2-2 解决方法 方法 1：以显示精度为准 ！！事先声明：此方法要慎重使用！！ 因为问题出在“显示的数值”和“Excel实际认为的数值”不一致，因此可以设置Excel“以显示精度为准”选项来防止四舍五入错误。 如下图，在【选项】→【高级】→【计算此工作簿时】中勾选“将精度设为所显示的精度”，此选项会强制将工作表中每个数字的值成为显示的值。 设置之后，如果在“设置单元格格式中”选择显示两位小数的数字格式，则在保存工作簿时，所有超出两位小数的精度均将会丢失。 ！！！注意：此选项将影响整个工作簿，而且无法撤消此选项和恢复丢失的数据，因此属于暴力、强制性的舍去位数，建议在启用此选项之前先备份工作簿。 方法 2：四舍五入函数进行位数取舍 还可以使用四舍五入函数对数值进行“正确的四舍五入”，此类型的函数较多，放在第三部分详细讲解。 Part 3 数值位数取舍函数 使用函数进行数值位数取舍不仅改变了数据的显示式样，同时也改变了数据本身。根据位数取舍函数的作用不同，可以分为三类。 3-1 简单粗暴的取整取整最常使用的就是INT函数。 INT函数是将数值向下取整为最接近的整数。 因为INT函数是向下取整，所以会返回比原始数据小但最接近的数值。 3-2 按部就班的四舍五入另一类对函数位数取舍的方法是四舍五入，这样取舍的方式，在很大程度上也是能满足精度要求的，相比直接舍去小数部分的取整，四舍五入的方式更接近真实情况。 能够实现四舍五入的函数，以ROUND为基础，总共可以延伸出三个不同的类型的函数。
1）ROUND函数对数值四舍五入ROUND函数是取舍函数中使用率最高的函数之一，使用它可以方便地对数值进真正数学意义上的四舍五入。通常用在嵌套中，在计算过程中，对数值取舍为想要的精度。
=ROUND (number, digits) ①Number：要取舍的数值 ②Digits：要保留的小数位数
注：如果round函数只有参数number,等同于digits 等于 0。
ROUND函数使用案例：
参数二大于等于0的情况，比较容易理解。下面详细讲解小于零的情况：
-1，就是将6.231四舍五入为最接近的 10 的倍数 -2，就是将50.35四舍五入到最接近的100的倍数 …… 2）ROUNDUP对数值进行向上取舍对数值进行取舍时，有时我们需要直接向上取舍，即无论要舍去的数是几，都要向前一位进1，这时可以使用ROUNDUP函数，如图所示。
3） ROUNDDOWN对数值进行向下取舍与ROUNDUP函数相反，不管要舍去的数是几，如果想将它们直接舍去而无需向前一位进1，可以使用ROUNDDOWN函数，如图所示。
3-3 灵活多变的截位截位指的是，直接将小数部分的某一位或多位直接舍掉，而不进行任何进位。 TRUNC函数能够实现这一功能，它的作用是直接将数字的小数部分多余位数截去，保留指定的位数。
TRUNC函数语法： TRUNC（number，num_digits） ①number：要舍位的数值；②num_digits：保留的小数位数
经过TRUNC计算之后，无论小数有几位，都被截为指定的位数，并且没有进行四舍五入，完全直接舍去。 可以看出，当TRUNC的第二个参数为0时，TRUNC函数同样可以保留数值的整数部分，但是TRUNC在进行取舍时，不考虑数值的正负，直接舍掉小数部分，只保留整数。 这种情况下，TRUNC和INT函数的区别如下表。
掌握了这些技能，还会被Excel欺骗么？ End. |
|
|
来自： 爺↘傷憾 > 《EXCEL电子表格》
