2016年1月12日 星期二

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

有網友問到:在 Excel 中有一個如下圖的清單,其中的代碼欄位含有區域碼,如何根據代碼小計各區域的總和?
以下圖為例,每個人的代碼中含區域碼,如果在不另行取出區域碼的做法下,如何直接計算各區域的小計?
image

【公式設計與解析】
選取儲存格B1:C30,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:代碼、金額。

1. 區域碼在相同位置
以上圖為例,區域碼都位於代碼的前二碼。
儲存格F2:=SUMPRODUCT((LEFT(代碼,2)=E2)*金額)
LEFT(代碼,2)=E2:利用 LEFT 函數取出代碼中的前二碼,再和儲存格E2比對是否相同,傳回 TRUE/FALSE 陣列。(相同傳回 TRUE,不相同傳回 FALSE)
(LEFT(代碼,2)=E2)*金額:在 SUMPRODUCT 函數中,『*』運算過程中會將 TRUE/FALSE 陣列轉換為 1/0 陣列。再計算『乘積和』。

2. 區域代碼在不同位置
如果區域碼位於代碼中的不固定位置,則公式修改如下:
儲存格F2:=SUMPRODUCT((SUBSTITUTE(代碼,E2,"")<>代碼)*金額)
SUBSTITUTE(代碼,E2,"")<>代碼:利用 SUBSTITUTE 函數將代碼中的字串以空字串取代儲存格E2的內容,再和儲存格E2比對是否相同,傳回 TRUE/FALSE 陣列。(不相同傳回 TRUE,表示代碼中含有儲存格E2的內容;相同傳回 FALSE,表示代碼中沒有儲存格E2的內容。)
((SUBSTITUTE(代碼,E2,"")<>代碼)*金額:在 SUMPRODUCT 函數中,『*』運算過程中會將 TRUE/FALSE 陣列轉換為 1/0 陣列。再計算『乘積和』。

沒有留言:

張貼留言

檢視其他文章

好康東東