2017年5月9日 星期二

Excel-計算含有部分字串的小計(SUMPRODUCT,LEFT,SUBSTITUTE)

問到如果資料調整為下圖的代碼格式,如何計算各個代碼的加總小計?
Excel-計算含有部分字串的小計(SUMPRODUCT,LEFT,SUBSTITUTE)
【公式設計與解析】
方法一:
儲存格F2:=SUMPRODUCT((1*LEFT(代碼,3)=E2)*金額)
1*LEFT(代碼,3):利用 LEFT 函數將代碼欄位中的代碼取出左邊 3 碼(文字),利用『*1』將其轉換為數字。
1*LEFT(代碼,3)=E2:判斷取出的三碼數字是否和儲存格E2中的內容相同,傳回 TRUE/FALSE 陣列。
(1*LEFT(代碼,3)=E2)*金額:在 SUMPRODUCT 函數中,第 2 個『*』,相當於執行邏輯 AND 運算,會將 TRUE/FALSE 陣列轉換為 1/0 陣列。再計算『乘積和』。

方法二:
儲存格F2:=SUMPRODUCT((SUBSTITUTE(代碼,E2,"")<>代碼)*金額)
SUBSTITUTE(代碼,E2,"")<>代碼:利用 SUBSTITUTE 函數將代碼中的字串以空字串取代儲存格E2的內容,再和儲存格E2比對是否相同,傳回 TRUE/FALSE 陣列。(若是不相同,則傳回 TRUE,表示代碼中含有儲存格E2的內容;若是相同,則傳回 FALSE,表示代碼中沒有儲存格E2的內容。)
((SUBSTITUTE(代碼,E2,"")<>代碼)*金額:在 SUMPRODUCT 函數中,『*』運算過程中會將 TRUE/FALSE 陣列轉換為 1/0 陣列。再計算『乘積和』。

沒有留言:

張貼留言

檢視其他文章

好康東東