2014年12月14日 星期日

Excel-分離儲存格中的文字和數字並計算總和(進階2)(SUMPRODUCT)

在 Excel 中的問題,延續先前二篇文章:

Excel-分離儲存格中的文字和數字並計算總和(SUMPRODUCT)

Excel-分離儲存格中的文字和數字並計算總和(進階)(SUMPRODUCT)

如果資料中含有小數的數字,該如何計算其總和呢?(參考下圖)

這個問題,建議使用輔助表格來簡化公式,簡單易懂易維護。

(1) 分離病病、事假、休假的數值:(可能為2位數以上、1位數、小數點以下)

儲存格B12:=VALUE(IFERROR(RIGHT(B2,LEN(B2)-1),0))

RIGHT(B2,LEN(B2)-1):將儲存格B2中的字串取出第一個字以後的字串,這個字數由數字構成(例如儲存格B2為:"0.5")。

IFERROR(RIGHT(B2,LEN(B2)-1),0):如果上式的結果中為空字串,會傳回錯誤訊息,利用 IFERROR 函數將其轉換為 0。

最後再以 VALUE 函數將數字組成的字串,轉換為真實的數值。在儲存格B2中,將「"0.5"」轉換為「0.5」。

複製儲存格B12,貼至儲存格B12:R19。

 

(2) 利用 SUMPRODUCT 函數,分別計算病病、事假、休假的總和:

儲存格P2:=SUMPRODUCT((LEFT($B2:$M2,1)="病")*$B12:$M12)

儲存格Q2:=SUMPRODUCT((LEFT($B2:$M2,1)="事")*$B12:$M12)

儲存格R2:=SUMPRODUCT((LEFT($B2:$M2,1)="休")*$B12:$M12)

複製儲存格P12:R12,貼至儲存格P2:R9。

沒有留言:

張貼留言

好康東東