2011年9月12日 星期一

Excel-數字金額轉國字金額

常有人問到,在 Excel 的工作表中,如果輸入一個阿拉伯數字的金額,希望能自動轉換成國字數字表示法(如下圖),該如何處理?假設位元是固定的(億、仟、佰、拾、萬、仟、佰、拾、元固定),阿拉伯數字要自動轉換成國字數字,而且零要以較淡的灰色表示。

儲存格D2:=TEXT(MID(RIGHT("000000000"&$B2,9),INT(COLUMN(A:A)/2)+1,1),"[DBNum2]")

將儲存格D2複製到儲存格F2, H2, J2, L2, N2, P2, R2, T2。

公式原理解釋:

(1) RIGHT("000000000"&$B2,9)

將9個0的字串和儲存格B2的數字串接,再由右取9個字。(也就是數字未達9個者全部補0。)

(2) INT(COLUMN(A:A)/2)+1

可以在複製儲存格時產生的COLUMN(A:A)=1、COLUMN(C:C)=3、COLUMN(E:E)=5,轉換為1、2、3。

(3) MID(RIGHT("000000000"&$B2,9),INT(COLUMN(A:A)/2)+1,1)

將(1)中的數字字串,分別在儲存格D2, F2, H2, …中取出第 1, 2, 3 …個數字。

(4) TEXT(MID(RIGHT("000000000"&$B2,9),INT(COLUMN(A:A)/2)+1,1),"[DBNum2]")

透過 TEXT 函數和「"[DBNum2]"」參數,即可將取出的一個阿拉伯數字自動轉換成一個國字數字。

接著,將數字為「零」者以較淡的灰色表示。先選取儲存格D2,再透過格式化條件的設定,將儲存格公式設為「=D2="零"」,並且將字型色彩設定為較淡的灰色。

然後,將儲存格D2的「格式」複製到其他表示數字的儲存格。

其實還有其他方式可以完成轉換的公式,不坊大家都來動動腦!

沒有留言:

張貼留言

好康東東