贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

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

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

【準備工作】

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

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

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

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

 

1. 計算各店的銷售總額

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

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

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

 

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

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

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

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

 

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

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

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

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

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

儲存格內允許:清單

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

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

 

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

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

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

儲存格內允許:清單

來源:=產品清單

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

儲存格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的內容隨之變動。

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

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

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

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

創作者介紹

學不完.教不停.用不盡

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


留言列表 (2)

發表留言
  • 悄悄話
  • 悄悄話
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼