學不完．教不停．用不盡

有網友問到：在 Excel 中有一個如下圖的清單，其中的代碼欄位含有區域碼，如何根據代碼小計各區域的總和？

以下圖為例，每個人的代碼中含區域碼，如果在不另行取出區域碼的做法下，如何直接計算各區域的小計？

【公式設計與解析】

選取儲存格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 陣列。再計算『乘積和』。