為什么超過15位后的數(shù)字都顯示為0？

為什么超過15位數(shù)字后，后續(xù)位數(shù)都顯示為0？

官方答案：Excel 遵循有關如何存儲和計算浮點數(shù)的 IEEE 754 規(guī)范。 因此，Excel 在數(shù)字中僅存儲 15 個有效位數(shù)，并將第十五位后的數(shù)字更改為零。

解讀：IEEE 754采用二進制存儲和運算，雙精度下有效位數(shù)是53位，換成十進制（lg 2^53），最大有效位數(shù)就是15位。

你可能會問：難道0就不算有效位數(shù)了？

還真是！

在計算機浮點運算規(guī)則中，數(shù)字前方的0和后方的0都不算有效位數(shù)，只有位于1~9之間的0才算有效位數(shù)。

所以，不只是平常你看到的超過15位的整數(shù)其后面位數(shù)變成0，小數(shù)也是——只不過小數(shù)最后的0被省略了。

譬如：

17位的帶小數(shù)1234567890.1234567，輸入后自動變成1234567890.12345，最后的6和7被0取代然后丟掉了；；

19位的純小數(shù)0.012345678901234567，輸入后自動變成0.0123456789012345，最后的6和7被0取代然后丟掉了。

下面匯總Excel中數(shù)據(jù)在常規(guī)格式下的顯示規(guī)則。

顯示規(guī)則1：最大規(guī)則——只保留15位有效數(shù)字

15位有效數(shù)字右側的數(shù)字都自動變成0。

對帶小數(shù)而言，整數(shù)位數(shù)增加，小數(shù)位數(shù)就會減少，直到?jīng)]有小數(shù)。從這個意義上說對超過15位整數(shù)的數(shù)字設置小數(shù)保留位數(shù)毫無意義。

規(guī)避規(guī)則的方法：如果要輸入超過15位的數(shù)字而不變0

方法1，提前設置單元格格式為文本，然后輸入

方法2，首先輸入英文的單引號’，再輸入數(shù)字。

顯示規(guī)則2：整數(shù)和帶小數(shù)只要整數(shù)部分超過11位就采用科學計數(shù)

譬如，輸入12位數(shù)123456789012，自動顯示為1.23457E+11。

規(guī)避規(guī)則的方法：

自定義單元格格式，格式代碼為“0”或者“0.00”。

譬如下圖A1:A3中的數(shù)字都是123456789012.123，常規(guī)格式采用科學計數(shù)顯示，自定義格式則取消科學計數(shù)。

顯示規(guī)則3： >=0.0001的純小數(shù)，不論多少位都不采用科學計數(shù)

不過：

（1）單元格中最多按四舍五入顯示9位小數(shù)

（2）編輯欄中最多保留15位有效數(shù)

顯示規(guī)則4：<0.0001且小數(shù)位數(shù)大于9位的小數(shù)，采用科學計數(shù)

小于<0.0001，且小數(shù)位數(shù)小于等于9的，按實際顯示；小數(shù)位數(shù)大于9的，采用科學計數(shù)，小數(shù)部分最多四舍五入保留5位小數(shù)。

規(guī)避規(guī)則的方法：

根據(jù)需要設置單元格格式為“數(shù)值”，并設置需要顯示的小數(shù)位數(shù)。

顯示規(guī)則5：常規(guī)下的科學計數(shù)，小數(shù)部分最多按四舍五入保留5位數(shù)字

規(guī)避規(guī)則的方法：

如果需要科學計數(shù)的小數(shù)位數(shù)只保留2位或3位，則更改格式位“科學計數(shù)”，并修改小數(shù)位數(shù)。

本文配套的練習課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育Excel精品好課任你選擇！

掃下方二維碼關注公眾號，可隨時隨地學習Excel：

相關推薦：

Excel浮點運算誤差

自定義格式掃盲

用公式自定義條件格式

VLOOKUP經(jīng)典用法12例

版權申明：

本文作者小窩；部落窩教育享有稿件專有使用權。若需轉載請聯(lián)系部落窩教育。