技巧區‎ > ‎初級程度‎ > ‎

比較長數字 [2002年5月]

張貼者:2010年5月29日 上午7:19Carson Cheng   [ 已更新 2010年5月29日 上午7:40 ]
問題
有一位朋友在比較兩個二十個數位的數字時發生錯誤:

    A=12,345,678,901,234,567,891

    B=12,345,678,901,234,567,890

  很明顯,B是比A大的,但經輸入後:



  Excel居然說A和B是相等的,為什麼??


有效數字過多
主要原因是由於這兩個數字的數位太多,也就是說有效數字(Significant figures)過多。在Excel裡,數字的格式有好幾種,單單小數就有Single和Double,有不同的有效數字和exponent大小的限制,也就是說所能代表的數字有一定的範圍:



  可以看到,就算是Double,有效數字也只有(大概)15個,故此當輸入A和B時,兩個數字都會給變為(Excel會自動用上Double):

    1.23456789012345 x 10^19

  自然兩個數字一樣了!

用數字儲存成文字
如果只為儲存或比較,而不用進行運算,我建議把數字用文字方法儲存。

  一個儲存格以儲存32,767個字符(請用「Excel specifications and limits」在說明找有關資料),絕對足夠應付「一般」數字長度。雖然不能進行運算,但可以文字方式做比較。

  怎樣把數字儲存為文字呢?有兩個方法:


  1. 輸入數字時,在前方加上「'」號;
  2. 把儲存格的格式先定義為文字,選「格式|數字|(資料類別)文字」,然後才輸入數字。

  現在你可以如常用「<」、「<=」、「=」、「>=」、 「>」符號來比較那些長數字。



注意:

  1. Excel會把這些數字當作文字,所以不能加千位分隔。 
  2. 文字的比較是從左到右的,如果兩個數字的數位不一致,刖需在較短的數字前方補上 0,如下例,一個數字有18個數位,另一個有20個數位,要比較正常運作,我們需要在18位的數字前補兩個0:



Ĉ
1_3.xls
(20k)
Carson Cheng,
2010年5月29日 上午7:19
Comments