有網友問到：在 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 陣列。再計算『乘積和』。
文章標籤
全站熱搜
版主好大方喔，這樣做教學 Excel在工作中真得很常用到 學起來，以備不時之需 感謝版主無私分享
Vincent 老師您好 我有一難題如下, 再麻煩老師幫忙解答 , 感恩! 狀況1 A B C D 1 0 10 33 ? 狀況2 A B C D 1 20 0 33 ? 求D1欄的計算方式, 有兩種狀況, A1與B1我只會鍵入其中一欄位的值, 另一欄位我會鍵入0 如果A1欄是0, 並且B1欄 我會鍵入值, D1= B1欄 乘以C1值 如果B1欄是0, 並且A1欄 我會鍵入值, D1= A1欄 乘以C1值 from Leo Chen
如果A1和B1二者之一會是0，則公式： 儲存格D1：=(A1+B1)*C1
你好, 有幸見你的分享,最近一直找不到解決的excel問題如能幫忙不勝感激:公式是: -SUM((year=$A15)*(month=$B15)*(code=C$1)*data) data 是一個集合,如data內全是數字,公式能應用,但只要data內某儲存格含有文字,公式就無法計算, 求排除文字的方法. 謝謝
請將 (year=$A15)*(month=$B15)*(code=C$1)*data 改為 (year=$A15)*(month=$B15)*(code=C$1)*IFERROR(data*1,0)