贊助廠商

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

搜尋本部落格文章資料

網友根據這篇文章:Excel-計算含有部分字串的小計(SUMPRODUCT)

問到如果資料調整為下圖的代碼格式,如何計算各個代碼的加總小計?

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 陣列。再計算『乘積和』。

文章標籤
創作者介紹

學不完.教不停.用不盡

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


留言列表 (2)

發表留言
  • 訪客
  • 老師您好~~我想請問如果我的檔案~~裡面有A客戶工作表、B客戶工作表、C客戶工作表.....然後我是一個月存成一個檔案,有辦法將1月-12月的A客戶工作表合併成一個新的檔案嗎?並且如果修改原來的檔案,合併的檔案也會自動更新!
    謝謝!!
  • 悄悄話

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼