2017年9月21日 星期四

Excel-調整金額顯示格式(TEXT,MID)

參考下圖,網友問到:如何在 Excel 中將一個原始金額調整其格式如下圖右?
在下圖中,要將一個數值轉換為金額的表示方式,其中億為 2 位數、萬為 4 位數、元為 4 位數,並且要套用千分位符號。
調整金額顯示格式(TEXT,MID)

【公式設計與解析】
儲存格C2:
=IF(A2>10^8,TEXT(MID(TEXT(A2,"0000000000"),1,2),"##")&"億","")&
IF(A2>10^4,TEXT(MID(TEXT(A2,"0000000000"),3,4),"#,###")&"萬","")&
TEXT(MID(TEXT(A2,"0000000000"),7,4),"#,###")&"元"
複製儲存格C2,貼至儲存格C2:C11。
(1) IF(A2>10^8,TEXT(MID(TEXT(A2,"0000000000"),1,2),"##")&"億","")
TEXT(A2,"0000000000"):將儲存格A2的內容補足 10 位元。
MID(TEXT(A2,"0000000000"),1,2):取出補足 10 位元的儲存格A2的 1~2 位元。
在 IF 函數中判斷條件:A2>10^8 是否成立(是否超過 1 億),再執行該段公式。

(2) IF(A2>10^4,TEXT(MID(TEXT(A2,"0000000000"),3,4),"#,###")&"萬","")
MID(TEXT(A2,"0000000000"),3,4):取出補足 10 位元的儲存格A2的第 3~6 位元。
在 IF 函數中判斷條件:A2>10^4 是否成立(是否超過 1 萬),再執行該段公式。
在 TEXT 函數中使用參數『#,###』,用以設定顯示千分位格式。

(3) TEXT(MID(TEXT(A2,"0000000000"),7,4),"#,###")&"元"
MID(TEXT(A2,"0000000000"),7,4):取出補足 10 位元的儲存格A2的第 7~10 位元。
在 TEXT 函數中使用參數『#,###』,用以設定顯示千分位格式。

沒有留言:

張貼留言

檢視其他文章

好康東東