2012年1月28日 星期六

Excel-轉換含小數的二進位數為十進位數(陣列)

在 Excel 中如果想要將二進位數、八進位、十六進位數轉換為十進位數,可以利用以下的相關函數:

但是,如果要轉換含小數的二進位數為十進制數,則會產生「#NUM!」錯誤訊息。這次來練習如何達成轉換工作。

 

【模擬轉換過程】

(1) 儲存格B3:B14分別表示2^(-1)、2^(-2)、2^(-3)、2^(-4)、2^(-5)、…。

(2) 儲存格C3:=MID($B$1,ROW(3:3),1),複製儲存格C3,貼至儲存格C3:C14。取出小數點後的每一位數字(1或0)

(3) D欄為B欄和C欄的相乘積。

(4) 儲存格D15:{=SUM(IFERROR(D3:D14,0))},這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

公式是要將非錯誤值的儲存格相加,透過 IFERROR 函數來判斷若為錯誤值則視為0。

 

【以陣列公式運算】

如果以陣列公式來處理,則只要一個公式即可做完上述所有的動作。

{=SUM(MID(B1,ROW(INDIRECT("3:"&LEN(B1))),1)*1/2^(ROW(INDIRECT("1:"&LEN(B1)-2))))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

LEN(B1):找出儲存格B1中二進位數的字元數(包含「0.」),公式自動判斷二進位數的長度。

ROW(INDIRECT("3:"&LEN(B1))):利用 INDIRECT 函數來轉換為位址,本例可得ROW(3:12),在陣列公式中代表3, 4, 5…, 12。

MID(B1,ROW(INDIRECT("3:"&LEN(B1))),1):取得儲存格B1中的第3個字至最後的每個字元(不包含「0.」),,本例為「1100110011」。

ROW(INDIRECT("1:"&LEN(B1)-2)):利用 INDIRECT 函數來轉換為位址,本例可得ROW(1:10),在陣列公式中代表1, 2, 3, …, 10。

1/2^(ROW(INDIRECT("1:"&LEN(B1)-2))):本例可得1/2, 1/4, 1/8, …, 1/(2)^10。

將上述二式相乘後加總即為答案。

沒有留言:

張貼留言

好康東東