贊助廠商

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

搜尋本部落格文章資料

廣告贊助

讀者留言問到 Excel 的問題:在下圖左中有一多個項目的收入/支出清單,如何能分別計算收入/支出的各部門之健保費和勞保費小計金額(如下圖右)?

Excel-將含有字串中的文字者計算總和(SUMPRODUCT)


【公式設計與解析】

選取儲存格A1:C49,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、收入、支出。

1. 計算各部門健保費/勞保費的收入

儲存格F3:=SUMPRODUCT((SUBSTITUTE(項目,$E3,"")<>項目)*(SUBSTITUTE(
項目,F$2,"")<>項目)*收入)

複製儲存格F3,貼至儲存格F3:G10。

(1) SUBSTITUTE(項目,$E3,"")

利用 SUBSTITUTE 函數將在項目陣列中包含儲存格E3的內容(例如:工程部)以空白取代。

(2) SUBSTITUTE(項目,$E3,"")<>項目

判斷第(1)式的傳回陣列中是否和原來項目陣列不相同,若結果傳回 TRUE,表示該項目包含了儲存格E3的內容(例如:工程部);若結果傳回 FALSE,表示該項目沒有包含儲存格E3的內容。

(3) SUBSTITUTE(項目,F$2,"")<>項目

利用 SUBSTITUTE 函數將在項目陣列中包含儲存格F2的內容(例如:健保費)以空白取代,再判斷項目陣列中是否包含儲存格F2的內容。

(4) (SUBSTITUTE(項目,$E3,"")<>項目)*(SUBSTITUTE(項目,F$2,"")<>項目)

SUMPRODUCT 函數中使用雙條件,其中『*』運算子相當於執行邏輯 AND 運算。

(5) SUMPRODUCT(第(4)式*收入)

SUMPRODUCT 函數中,將第(4)式乘以『收入』陣列,即可得到答案。


2. 計算各部門健保費/勞保費的支出

儲存格F14:=SUMPRODUCT((SUBSTITUTE(項目,$E3,"")<>項目)*(SUBSTITUTE(
項目,F$2,"")<>項目)*支出)

複製儲存格F14,貼至儲存格F14:G21。

公式原理和1.相同。


3. 利用資料剖析工具剖析後再運算

如果你的資料可以使用資料剖析工具,先行處理如下圖左的結果。則公式可以變的很簡單!

Excel-將含有字串中的文字者計算總和(SUMPRODUCT)

文章標籤
創作者介紹

學不完.教不停.用不盡

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


留言列表 (2)

發表留言
  • 悄悄話
  • 悄悄話
找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼