2011年6月23日 星期四

Excel-模擬二進制、四進制、八進制(SUMPRODUCT、INDIRECT)

在 Excel 2010 中有進制轉換的函數:BIN2DEC、BIN2HEX、BIN2OCT、DEC2BIN、DEC2HEX、DEC2OCT等,非常方便好用。這次來自行以公式模擬將二進制、四進制、八進制轉成十進制,來練習SUMPRODUCT、INDIRECT等函數。

儲存格B2:=SUMPRODUCT(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),2^(LEN(A2)-ROW(INDIRECT("1:"&LEN(A2)))))

LEN(A2):取得儲存格A2中字串長度。(本例以字串長度6來練習)

INDIRECT("1:"&LEN(A2)):依儲存格A2中字串長度,產生一段列的範圍。

ROW(INDIRECT("1:"&LEN(A2)):例如儲存格A2中字串長度為6,則產生ROW(1:6)。

MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1):相當於MID(A2,1,1)、MID(A2,2,1)、…、MID(A2,6,1)。即可以將儲存格A2中的每一個字取出,變成一個6個元素的陣列。

2^(LEN(A2)-ROW(INDIRECT("1:"&LEN(A2)))):可以產生2的5次方、2的4次方、…、2的0次方。

透過SUMPRODUCT函數,將儲存格A2中的第1個數X2的5次方、第2個數X2的4次方、…、第6個數X2的0次方,並且全部加總即為十進制的結果。

同理:

儲存格D2:=SUMPRODUCT(--MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1),4^(LEN(C2)-ROW(INDIRECT("1:"&LEN(C2)))))

儲存格F2:=SUMPRODUCT(--MID(E2,ROW(INDIRECT("1:"&LEN(E2))),1),8^(LEN(E2)-ROW(INDIRECT("1:"&LEN(E2)))))

詳細函數說明,請參閱微軟網站:

INDIRECThttp://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx

INDIRECT:傳回文字串所指定的參照位址。

語法:INDIRECT(ref_text,[a1])

ref_text:單一儲存格的參照位址,其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱,或定義為字串的儲存格參照位址。

a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。

 

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

沒有留言:

張貼留言

檢視其他文章

好康東東