贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

有網友問到:在 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 陣列。再計算『乘積和』。

創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(2) 人氣()


留言列表 (2)

發表留言
  • 徵信社
  • 版主好大方喔,這樣做教學
    Excel在工作中真得很常用到
    學起來,以備不時之需
    感謝版主無私分享
  • zzchenzz
  • 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

    vincent 於 2016/01/12 23:41 回覆

找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼