2016年1月4日 星期一

Excel-取含有文字的儲存格中計算數字總和(SUMPRODUCT,SUBSTITUTE)

有網友問到:在取得的 Excel 報表的人員和時數清單中,其中時數部分的儲存格中含有文字和數字,若要取出數字部分來計算,該如何處理?(參考下圖)
在時數欄位中,想要將儲存格中的『核』字去除,留下數字以方便計算小計。
Excel-取含有文字和數字的儲存格中計算數字總和(SUMPRODUCT,SUBSTITUTE)

【公式設計與解析】
儲存格E2:
=SUMPRODUCT(($A$2:$A$25=D2)*SUBSTITUTE($B$2:$B$25,"核",""))
$A$2:$A$25=D2:判斷在儲存格A2:A25中和儲存格D2是否相符,傳回 TRUE/FALSE 陣列。
SUBSTITUTE($B$2:$B$25,"核",""):利用 SUBSTITUTE 函數將每個儲存格中的『核』字置換為空白。
再利用 SUMPRODUCT 函數將以上二式代入,利用計算『乘積和』的方式,得到每個人員的時數小計。
複製儲存格E2,貼至儲存格E2:E6。

沒有留言:

張貼留言

檢視其他文章

好康東東