學不完．教不停．用不盡

在 Excel 中，如果根據一個資料表(如下圖左)，要計算其人員摘要出來的次數和小計(如下圖右)，建議做法是使用 SUMPRODUCT 函數，很方便。

儲存格F2：=SUMPRODUCT(($B$2:$B$21=$E2)*1)

儲存格G2：=SUMPRODUCT(($B$2:$B$21=$E2)*$C$2:$C$21)

複製儲存格F2:G2，貼至儲存格F2:G7。

你也可以選取儲存格B1:C21，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：人員、內容。

儲存格F2：=SUMPRODUCT((人員=$E2)*1)

儲存格G2：=SUMPRODUCT((人員=$E2)*內容)

或是將資料範圍轉換成表格，改用以下公式：

儲存格F2：=SUMPRODUCT((表格1[人員]=$E2)*1)

儲存格G2：=SUMPRODUCT((表格1[人員]=$E2)*表格1[內容])

以上的做法是針對某種運算摘要不同人員的結果，而另一種做法是針對某個人員執行不同運算。

先將資料表設定為篩選狀況，在某一欄位中執行篩選動作，例如人員篩選「丁」：

想要呈現如下圖的結果，即可以選取不同人員的多種運算。即選取人員後，再由下拉式清單中選取某個運算。

在篩選功能中可以使用 SUBTOTAL 函數來執行多種運算，觀察一下，當你輸入函數名稱後，其第一個參數和第二個參數，用以選取要執行何種運算。

所以，在儲存格B23中，使用「資料驗證」的方式設定：

儲存格內允許：清單；來源為=$G$23:$G$33。

再配合公式：

儲存格C23：=SUBTOTAL(MATCH(B23,G23:G33),C2:C21)

MATCH(B23,G23:G33)：透過 MATCH 函數找出儲存格B23的內容在儲存格G23:G33中的第幾個。這個傳回值即用以挑選要執行的運算。

總結做法：

1. 透過篩選工具來篩選人員。

2. 使用資料驗證的清單來製作下拉式清單。

3. 透過 SUBTOTAL 函數來針對篩選結果執行指定運算。

註：你也可以將運算的英文名稱(儲存格G23:G33)，改成中文名稱。