網友問到 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)式

文章標籤
創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()