2017年3月1日 星期三

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

讀者留言問到 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)

沒有留言:

張貼留言

好康東東