2014年12月5日 星期五

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

網友想要在 Excel 中,將下圖的每個人員之請假狀況做成下下圖的摘要表,其中儲存格標示為請假的假別和請假的時數小計,該如何處理呢?

這是一個很多人都會用到的題目,但要分離每個儲存格中的文字和數字再運算並不容易!幸好,運用文字相關的函數和 SUMPRODUCT 函數,即可輕鬆來完成!

儲存格V2:=SUMPRODUCT((LEFT(B2:S2,1)="病")*VALUE(0&RIGHT(B2:S2,1)))

LEFT(B2:S2,1)="病":取出每個儲存格中最左邊一個字為「病者」,傳回 TRUE/FALSE 陣列。本例為:{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE; …}

RIGHT(B2:S2,1):取出每個儲存格中最右邊一個字(數),傳回 TRUE/FALSE 陣列,本例為:{"4";"";"4";"";"";"8";"";"8";""; … }

VALUE(0&RIGHT(B2:S2,1)):透過 VALUE 函數,將數字構成的文字轉換為真實的數字。

其中先串接一個「0」是一個好用的技巧,因為上式中有些儲存格為空字串:「""」,套用到 VALUE 函數會產錯誤訊息,所以先串接一個「0」,可以將空字串變為 0,而原先是數字者,例如:4 會變成 04,轉換為數字時仍為 4。

複製儲存格V2,貼至儲存格V2:V9。

同理:

儲存格W2:=SUMPRODUCT((LEFT(B2:S2,1)="事")*VALUE(0&RIGHT(B2:S2,1)))

儲存格X2:=SUMPRODUCT((LEFT(B2:S2,1)="休")*VALUE(0&RIGHT(B2:S2,1)))

沒有留言:

張貼留言

檢視其他文章

好康東東