2017年9月22日 星期五

Excel-取出單位前的數值(FIND,MID,SUBSTITUTE)

回答網友提問:如下圖的 Excel 工作表,如何取出『包/盒』和『盒/箱』前的數字?
image

1. 計算『包/盒』數量
儲存格B2:=MID(A2,1,FIND("包",A2)-1)
FIND("包",A2):利用 FIND 函數找尋『包』在儲存格中第 1 次出現的位置。
利用上式的傳回值,透過 MID 函數取得儲存格最前面的數字。

2. 計算『盒/箱』數量
儲存格C2:=MID(SUBSTITUTE(A2,"盒/箱",""),FIND(";",A2)+1,99)
SUBSTITUTE(A2,"盒/箱",""):將儲存格A2中的內容去除字串『盒/箱』(將『盒/箱』以空字串取代)。
FIND(";",A2):利用 FIND 函數找尋『;』在儲存格中出現的位置。
利用上式的傳回值,透過 MID 函數取得儲存格字串『盒/箱』前的數字。其中的參數  99,只是一個很大的隨機數字,用以取得『;』之後的所有字串(不包含『盒/箱』)。

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 函數中使用參數『#,###』,用以設定顯示千分位格式。

2017年9月19日 星期二

Excel-將數字金額轉換為國字金額(TEXT,MID,COLUMN,INT)

網友問到:在 Excel 中如何如下圖將數字金額轉換為國字金額?
例如:將 472 轉換為肆佰柒拾貳元。
Excel-將數字金額轉換為國字金額(TEXT,MID,COLUMN,INT)
【公式設計與解析】
儲存格F1:
=TEXT(MID(TEXT($C1,"000"),INT(COLUMN(A:A)/2)+1,1),"[DBNum2]")
複製儲存格F1,分別貼至儲存格H1和儲存格J1。
(1) TEXT($C1,"000")
不管儲存格C1中的數字為幾位數,全部轉換為 3 位數,前面補 0。
(2) INT(COLUMN(A:A)/2)+1,1)
COLUMN(A:A)=1,向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:CA)=3→....。
INT(COLUMN(A:A)/2)+1,1)=1,向右複製每間隔兩欄貼上,可得 2, 3, 4, …。
(3) TEXT(第(1)式, 第(2)式, "[DBNum2]")
使用參數 [DBNum2],可以將數字顯示為『一、二、三、四、五、六、七、八、九、零』。
公式自動產生,儲存格H1:
=TEXT(MID(TEXT($C1,"000"),INT(COLUMN(C:C)/2)+1,1),"[DBNum2]")
公式自動產生,儲存格J1:
=TEXT(MID(TEXT($C1,"000"),INT(COLUMN(E:E)/2)+1,1),"[DBNum2]")

如果你要增加數值的位元數,則仿照以上的公式即可完成。
例如:將 8615 轉換為捌千陸佰壹拾伍元。
Excel-將數字金額轉換為國字金額(TEXT,MID,COLUMN,INT)
儲存格F1:
=TEXT(MID(TEXT($C1,"0000"),INT(COLUMN(A:A)/2)+1,1),"[DBNum2]")
其實公式完全一模一樣,複製儲存格F1,分別貼至儲存格H1和儲存格J1和儲存格L1。

好康東東