這天，雷哥找到星爺訴苦，因?yàn)樗龅搅艘粋€(gè)無(wú)比棘手的問(wèn)題：使用Excel做最簡(jiǎn)單的求和計(jì)算時(shí)，得出的結(jié)果總是不對(duì)，每次都是錯(cuò)0.01的樣子，如下圖所示。

圖1：簡(jiǎn)單的加法運(yùn)算

其實(shí)Excel具有15位有效數(shù)字的計(jì)算精度，對(duì)于上圖中的兩位小數(shù)來(lái)說(shuō)，應(yīng)該手到擒來(lái)才對(duì)，出現(xiàn)這樣的錯(cuò)誤確實(shí)讓人奇怪。

這個(gè)問(wèn)題要分兩種情況討論。

情況1：浮點(diǎn)運(yùn)算誤差，這種情況確實(shí)是Excel的責(zé)任，我們應(yīng)該討伐Excel；

情況2：是我們使用Excel的姿勢(shì)不對(duì)導(dǎo)致的錯(cuò)誤，我們應(yīng)該自我檢討。

Part 1

浮點(diǎn)運(yùn)算誤差

浮點(diǎn)運(yùn)算誤差通常出現(xiàn)在使用小數(shù)運(yùn)算，或運(yùn)算過(guò)程中包含小數(shù)的情況中，在減法和除法中比較多見(jiàn)，這算誤差的出現(xiàn)是隨機(jī)的，并不一定總能碰到。但是，以目前的科技，這種誤差總是可觀存在的。

1-1 出現(xiàn)的原因：

①由于計(jì)算機(jī)內(nèi)部以二進(jìn)制保存，所以十進(jìn)制的有限位的小數(shù)，在計(jì)算機(jī)內(nèi)部會(huì)是一個(gè)無(wú)限位的小數(shù)。

例如：十進(jìn)制的0.9雖然只有一位小數(shù)，轉(zhuǎn)成2進(jìn)制是無(wú)限循環(huán)小數(shù)0.1110011001100110011...

②軟件保存浮點(diǎn)數(shù)的精度有限，Excel可以保存15位有效數(shù)字，有效數(shù)字以后的就被忽略了。

1-2 浮點(diǎn)運(yùn)算案例

如下圖所示，在C3單元格中寫(xiě)入簡(jiǎn)單的計(jì)算=5.1-5.2+1，計(jì)算結(jié)果毫無(wú)爭(zhēng)議應(yīng)該是0.9，但是如果設(shè)置足夠多的小數(shù)位數(shù)，會(huì)發(fā)現(xiàn)Excel的計(jì)算結(jié)果并不等于0.9。

本案例來(lái)自：《Excel這么用就對(duì)了》

這雖是一個(gè)很小的誤差，但是它畢竟不等于真實(shí)的數(shù)值。如果出現(xiàn)在工程中，甚至可能造成重大的安全事故。

1-3 解決方法

Excel 提供了多種函數(shù)來(lái)彌補(bǔ)舍入誤差，通常使用ROUND 函數(shù)強(qiáng)制四舍五入即可。

如圖所示，使用ROUND函數(shù)矯正誤差。

Part 2

人為誤差

從前文可以看到，浮點(diǎn)運(yùn)算產(chǎn)生的誤差非常小，而且不是隨機(jī)出現(xiàn)，像圖1中出現(xiàn)0.01這么大的誤差，通常不是浮點(diǎn)運(yùn)算造成的，而是我們被Excel欺騙了。

2-1 出現(xiàn)的原因：

通常是在實(shí)際的使用過(guò)程中，為了顯示上的需要，對(duì)單元格數(shù)值進(jìn)行了格式設(shè)置，使其只顯示一部分小數(shù)位數(shù)。

比如：?jiǎn)卧裰械臄?shù)值是49.995，在【設(shè)置單元格格式】→【數(shù)字】→【數(shù)值】中設(shè)置小數(shù)位數(shù)為2位。

設(shè)置小數(shù)位數(shù)為2位

但是，使用自定義格式只是改變了數(shù)據(jù)的顯示式樣，數(shù)據(jù)本身并沒(méi)有發(fā)生變化，這就是導(dǎo)致表面上看起來(lái)的數(shù)值與實(shí)際數(shù)值不一致的原因。

知道了這個(gè)細(xì)節(jié)，再來(lái)看一下雷哥出錯(cuò)的數(shù)據(jù)，立刻就能找到錯(cuò)誤的根源的。當(dāng)我們鼠標(biāo)點(diǎn)到A1單元格時(shí)，發(fā)現(xiàn)在單元格中顯示的數(shù)據(jù)是50.00，但是在編輯欄中，實(shí)際的數(shù)值卻是49.995。

當(dāng)我們鼠標(biāo)點(diǎn)到A2單元格時(shí)，發(fā)現(xiàn)在單元格中顯示的數(shù)據(jù)是149.99，但是在編輯欄中，實(shí)際的數(shù)值卻是149.985。

也就是說(shuō)，因?yàn)閷?duì)數(shù)值進(jìn)行了格式設(shè)置，只能顯示小數(shù)點(diǎn)后兩位，所以原來(lái)三位小數(shù)被“四舍五入”為兩位小數(shù)，注意，這個(gè)四舍五入是假的，只是表面的四舍五入。

如果是“五入”，那么顯示的數(shù)值就比實(shí)際數(shù)值大；如果是“四舍”，那么顯示的數(shù)值就比實(shí)際數(shù)值小。雷哥的兩個(gè)數(shù)值恰好都是“五入”，所以導(dǎo)致顯示的兩個(gè)數(shù)值的和就比實(shí)際數(shù)值的和大了0.01。

2-2 解決方法

方法 1：以顯示精度為準(zhǔn)

??！事先聲明：此方法要慎重使用！！

因?yàn)閱?wèn)題出在“顯示的數(shù)值”和“Excel實(shí)際認(rèn)為的數(shù)值”不一致，因此可以設(shè)置Excel“以顯示精度為準(zhǔn)”選項(xiàng)來(lái)防止四舍五入錯(cuò)誤。

如下圖，在【選項(xiàng)】→【高級(jí)】→【計(jì)算此工作簿時(shí)】中勾選“將精度設(shè)為所顯示的精度”，此選項(xiàng)會(huì)強(qiáng)制將工作表中每個(gè)數(shù)字的值成為顯示的值。

設(shè)置之后，如果在“設(shè)置單元格格式中”選擇顯示兩位小數(shù)的數(shù)字格式，則在保存工作簿時(shí)，所有超出兩位小數(shù)的精度均將會(huì)丟失。

?。?！注意：此選項(xiàng)將影響整個(gè)工作簿，而且無(wú)法撤消此選項(xiàng)和恢復(fù)丟失的數(shù)據(jù)，因此屬于暴力、強(qiáng)制性的舍去位數(shù)，建議在啟用此選項(xiàng)之前先備份工作簿。

方法 2：四舍五入函數(shù)進(jìn)行位數(shù)取舍

還可以使用四舍五入函數(shù)對(duì)數(shù)值進(jìn)行“正確的四舍五入”，此類型的函數(shù)較多，放在第三部分詳細(xì)講解。

3-1 簡(jiǎn)單粗暴的取整

取整最常使用的就是INT函數(shù)。

INT函數(shù)是將數(shù)值向下取整為最接近的整數(shù)。

因?yàn)镮NT函數(shù)是向下取整，所以會(huì)返回比原始數(shù)據(jù)小但最接近的數(shù)值。

3-2 按部就班的四舍五入

另一類對(duì)函數(shù)位數(shù)取舍的方法是四舍五入，這樣取舍的方式，在很大程度上也是能滿足精度要求的，相比直接舍去小數(shù)部分的取整，四舍五入的方式更接近真實(shí)情況。

能夠?qū)崿F(xiàn)四舍五入的函數(shù)，以ROUND為基礎(chǔ)，總共可以延伸出三個(gè)不同的類型的函數(shù)。

1）ROUND函數(shù)對(duì)數(shù)值四舍五入

ROUND函數(shù)是取舍函數(shù)中使用率最高的函數(shù)之一，使用它可以方便地對(duì)數(shù)值進(jìn)真正數(shù)學(xué)意義上的四舍五入。通常用在嵌套中，在計(jì)算過(guò)程中，對(duì)數(shù)值取舍為想要的精度。

• ROUND函數(shù)語(yǔ)法：

=ROUND (number, digits)  

①Number：要取舍的數(shù)值  ②Digits：要保留的小數(shù)位數(shù)

• 參數(shù)二有三種不同的設(shè)置

注：如果round函數(shù)只有參數(shù)number,等同于digits 等于 0。

ROUND函數(shù)使用案例：

參數(shù)二大于等于0的情況，比較容易理解。下面詳細(xì)講解小于零的情況：

-1，就是將6.231四舍五入為最接近的 10 的倍數(shù)

-2，就是將50.35四舍五入到最接近的100的倍數(shù)

……

2）ROUNDUP對(duì)數(shù)值進(jìn)行向上取舍

對(duì)數(shù)值進(jìn)行取舍時(shí)，有時(shí)我們需要直接向上取舍，即無(wú)論要舍去的數(shù)是幾，都要向前一位進(jìn)1，這時(shí)可以使用ROUNDUP函數(shù)，如圖所示。

3） ROUNDDOWN對(duì)數(shù)值進(jìn)行向下取舍

與ROUNDUP函數(shù)相反，不管要舍去的數(shù)是幾，如果想將它們直接舍去而無(wú)需向前一位進(jìn)1，可以使用ROUNDDOWN函數(shù)，如圖所示。

3-3 靈活多變的截位

截位指的是，直接將小數(shù)部分的某一位或多位直接舍掉，而不進(jìn)行任何進(jìn)位。

TRUNC函數(shù)能夠?qū)崿F(xiàn)這一功能，它的作用是直接將數(shù)字的小數(shù)部分多余位數(shù)截去，保留指定的位數(shù)。

TRUNC函數(shù)語(yǔ)法：

TRUNC（number，num_digits）

①number：要舍位的數(shù)值；②num_digits：保留的小數(shù)位數(shù)

經(jīng)過(guò)TRUNC計(jì)算之后，無(wú)論小數(shù)有幾位，都被截為指定的位數(shù)，并且沒(méi)有進(jìn)行四舍五入，完全直接舍去。