唸國中的兒子上數學課時,數學老師提到費氏數列(Fibonacci number),回家和我討論到這件事。我想用 Excel 建構這個數列給他看。

參考:https://en.wikipedia.org/wiki/Fibonacci_number(以下部分圖片取自該網頁)

 

數列:0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, ...

Excel-顯示較多位數的費氏數列(Fibonacci number)(RIGHT,LEFT,INT)

根據公式,這個數列其實就是由前二個的數值的和所構成,因此公式很簡單:

儲存格B4:=B2+B3

但是,在 Excel 中受限有效位數15位,在第15位以上時右側數值都只會以0顯示0(參考下圖左)。所以,在 F74 開始,就會顯示不精確的數值了。如何能顯示精確的數值呢?至少可以補足一個國中生的學習欲望!以下就來試試顯示 28 位數正確的數值(參考下圖右)。

Excel-顯示較多位數的費氏數列(Fibonacci number)(RIGHT,LEFT,INT)

 

【公式設計與解析】

主要做法是將超過 14 位數的數值將其分成二半,一半是 1 ~ 14位數,另一半是 15 ~ 28 位數。分開來加總,但是要注意 1 ~ 14位數的加總有可能進位到 15 ~ 28 位數部分再加總。

Excel-顯示較多位數的費氏數列(Fibonacci number)(RIGHT,LEFT,INT)

以下用 F77(儲存格C79)為例,:

(1) 計算F75和F76之 1 ~ 14 位數部分加總不含進位至15位數的和

=RIGHT(RIGHT(C77,14)+RIGHT(C78,14),14)

(2) 計算F75和F76之 1 ~ 14 位數部分加總之進位

=INT((RIGHT(C77,14)+RIGHT(C78,14))/10^14)

(3) 計算F75和F76之 15 ~ 28 位數部分的和

=LEFT(C77,LEN(C77)-14)+LEFT(C78,LEN(C78)-14)

最後,組合公式:

儲存格C79:=IF(AND(LEN(C77)>=15,LEN(C78)>=15), ((3)+(2))&(1),C77+C78)

複製儲存格C79,貼至儲存格C4:C134。

 

【補充說明】

完整公式,儲存格A79:=IF(AND(LEN(C77)>=15,LEN(C78)>=15),LEFT(C77,
LEN(C77)-14)+LEFT(C78,LEN(C78)-14)+INT((RIGHT(C77,14)+RIGHT(C78,14))/
10^14)&RIGHT(RIGHT(C77,14)+RIGHT(C78,14),14),C77+C78)

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(0) 人氣()