學不完．教不停．用不盡

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

【公式設計與解析】

選取儲存格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. 利用資料剖析工具剖析後再運算

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