2016年7月10日 星期日

Excel-分離數值和單位(MID,SUBSTITUTE,陣列公式)

如果在 Excel 的工作表中有一個資料清單,其中每一個儲存格是由數值和單位所組成,如何能將數值和單位分離出來?
參考下圖,資料清單中的數值和單位的字元數都不相同,如何分離其中的數值和單位?
Excel-分離數值和單位(MID,SUBSTITUTE,陣列公式)

【公試設計與解析】
(1) 取出數值
假設:資料棈單中數值部分的位元數不超過 9(包含小數點)。
儲存格C2:{=MAX(IFERROR(1*LEFT(A2,ROW($1:$9)),""))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
ROW($1:$9):在陣列公式中可傳回 1, 2, 3, ... , 8, 9。
LEFT(A2,ROW($1:$9)):在陣列公式中由左邊取出儲存格A2內容 1, 2, 3, ..., 8, 9 個字。
1*LEFT(A2,ROW($1:$9)):由於 LEFT 函數傳回的是字串,透過「1*」,將其轉換為數值。若傳回的不是數字組成的字串,則會傳回「錯誤訊息」。
IFERROR(1*LEFT(A2,ROW($1:$9)),""):利用 IFFERROR 函數,將上式中的錯誤訊息轉換為空字串。
最後,利用 MAX 函數將 1 ~ 9 個數值取其最大值。
(2) 取出單位
儲存格D2:=SUBSTITUTE(A2,C2,"")
利用 SUBSTITUTE 函數將儲存格A2中的內容,去除儲存格C2的內容(數值),得到的結果即為「單位」。
最後,複製儲存格C2:D2,貼至儲存格C2:D19。

沒有留言:

張貼留言

好康東東