2015年6月10日 星期三

Excel-對一個資料表執行多個運算(SUMPRODUCT,SUBTOTAL)

在 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 函數來執行多種運算,觀察一下,當你輸入函數名稱後,其第一個參數和第二個參數,用以選取要執行何種運算。
image
所以,在儲存格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),改成中文名稱。

沒有留言:

張貼留言

檢視其他文章

好康東東