2015年9月23日 星期三

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

唸國中的兒子上數學課時,數學老師提到費氏數列(Fibonacci number),回家和我討論到這件事。我想用 Excel 建構這個數列給他看。
參考:https://en.wikipedia.org/wiki/Fibonacci_number(以下部分圖片取自該網頁)
 
數列:0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, ...
根據公式,這個數列其實就是由前二個的數值的和所構成,因此公式很簡單:
儲存格B4:=B2+B3
但是,在 Excel 中受限有效位數15位,在第15位以上時右側數值都只會以0顯示0(參考下圖左)。所以,在 F74 開始,就會顯示不精確的數值了。如何能顯示精確的數值呢?至少可以補足一個國中生的學習欲望!以下就來試試顯示 28 位數正確的數值(參考下圖右)。

【公式設計與解析】
主要做法是將超過 14 位數的數值將其分成二半,一半是 1 ~ 14位數,另一半是 15 ~ 28 位數。分開來加總,但是要注意 1 ~ 14位數的加總有可能進位到 15 ~ 28 位數部分再加總。
以下用 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)

沒有留言:

張貼留言

檢視其他文章

好康東東