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

arrow
arrow
    文章標籤
    EXCEL SUMPRODUCT
    全站熱搜

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