學不完．教不停．用不盡

根據前一篇文章：Excel-14個樞紐分析表應用練習，本篇文章要以公式模擬這些樞紐分析的動作。

資料來源是在一個 Excel 工作作的銷售記錄的資料清單中，含有欄位：日期、店名、業務員、產品代碼、機型、單價、數量、銷售額(如下圖)。現在，取用這個資料清單來練習樞分析表的操作，以下使用 Excel 2013 為例，資料來源有 700 筆以上。

【準備工作】

選取資料表中所有資料的儲存格，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、店名、業務員、產品、代碼、機型、單價、數量、銷售額。

在另一個工作表中含有產品及業務員的基本資料。也是先定義名稱：代碼清單、產品清單、機型清單、單價清單、店名清單。

1. 計算各店的銷售總額

儲存格E4：=SUMPRODUCT((店名=D4)*銷售額)

複製儲存格E4，貼至儲存格E4:E10。

2. 計算各分店每個業務員的銷售總額

儲存格G3：=SUMPRODUCT((店名=E4)*(業務員=F4)*銷售額)

複製儲存格G3，往下各列貼上。

3. 可篩選各店中各種機型的銷售總額

儲存格E4：=SUMPRODUCT((機型=D4)*(店名=$E$1)*銷售額)

複製儲存格E4，貼至儲存格E4:E18。

在儲存格E1設定資料驗證，其中的準則為：

儲存格內允許：清單

來源：=店名清單(相當於『仁愛店,民生店,民族店,民權店,和平店,忠孝店,信義店』)

4. 依各產品篩選每個分店各種機型的銷售總額(以矩陣表格顯示)

在儲存格B14中設計產品清單的下拉選單，在資料驗證的準則中設定：

儲存格內允許：清單

來源：=產品清單

儲存格B18：=IF(COLUMN(A:A)<=COUNTIF(基本資料!$B2:$B16,$B$14),OFFSET
(基本資料!$C$1,MATCH($B$14,基本資料!$B2:$B16,0)+COLUMN(A:A)-1,),"")

產品資料置於「基本資料!$B2:$B16」。當選取儲存格B14，則儲存格B17:E17的內容隨之變動。

儲存格18：=SUMPRODUCT((店名=$A18)*(機型=B$17)*銷售額)

複製儲存格B18，貼至儲存格B18:E24。