2017年1月6日 星期五

Excel-依分隔符號取出數字來運算(SUBSTITUTE,LEFT,MID,RIGHT)

網友問到 Excel 的問題:如下圖,如何把原始資料中的每個數字除以 100?
這個例子的原始資料中有三個數字,並且利用 2 個『*』加以隔開。
Excel-依分隔符號取出數字來運算(SUBSTITUTE,LEFT,MID,RIGHT)

【公式設計與解析】
本例將不使用 FIND 函數和 SEARCH 函數來找尋『*』的位置,再取出三組數字。這次將要用特殊的做法,巧妙來取出三組數字。
儲存格C2:
=LEFT(SUBSTITUTE(A2,"*",REPT(" ",20)),6)/100&"*"&MID(SUBSTITUTE(A2,
"*",REPT(" ",20)),15,15)/100&"*"&RIGHT(SUBSTITUTE(A2,"*",REPT(" ",20)),
6)/100
(1)轉換儲存格內容:SUBSTITUTE(A2,"*",REPT(" ",20))
先利用 REPT 函數產生 20 個空格(" "),再利用 SUBSTITUTE 函數將『*』置換成 20 個空格。
Excel-依分隔符號取出數字來運算(SUBSTITUTE,LEFT,MID,RIGHT)

(2) 計算第一組數字:LEFT(SUBSTITUTE(A2,"*",REPT(" ",20)),6)/100
LEFT(SUBSTITUTE(A2,"*",REPT(" ",20)),6):利用 LEFT 函數取出轉換後的儲存格內容最左邊 6 個字元(假設數字加小數點不超過 6 個數)。當這個 6 個字元的字串除以 100 時,Excel 會自動將串轉換為數值(空格會自動消除)。
Excel-依分隔符號取出數字來運算(SUBSTITUTE,LEFT,MID,RIGHT)
(3) 計算第二組數字:MID(SUBSTITUTE(A2,"*",REPT(" ",20)),15,15)/100
MID(SUBSTITUTE(A2,"*",REPT(" ",20)),15,15):利用 MID 函數由第 15 個字取 15 個字元(取出的 15 個字元中必定包含第二組數字)。再藉由將此結果除以 100,得到其中的數字。
Excel-依分隔符號取出數字來運算(SUBSTITUTE,LEFT,MID,RIGHT)
(4) 計算第三組數字:RIGHT(SUBSTITUTE(A2,"*",REPT(" ",20)),6)/100
RIGHT(SUBSTITUTE(A2,"*",REPT(" ",20)),6):利用 RIGHT 函數取出轉換後的儲存格內容最右邊 6 個字元(假設數字加小數點不超過 6 個數)。再藉由將此結果除以 100,得到其中的數字。
Excel-依分隔符號取出數字來運算(SUBSTITUTE,LEFT,MID,RIGHT)
(5) 組合字串:第(2)式&"*"&第(3)式&"*"&第(4)式

沒有留言:

張貼留言

好康東東