②软件保存浮点数的精度有限，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函数语法：

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.

相关阅读：

返回搜狐，查看更多