Excel資料庫分析 台灣微軟資深講師 王作桓.

Presentation on theme: "Excel資料庫分析 台灣微軟資深講師 王作桓."— Presentation transcript:

1 Excel資料庫分析 台灣微軟資深講師 王作桓

2 Agenda 資料庫的概念 下拉式選單之設計 自訂排序 資料庫篩選 資料庫的分類統計 報表的合併彙總 樞紐分析 清單管理

3 資料庫的概念 標準資料庫格式 似是而非的資料庫格式 對資料分析的影響 如何訂正資料庫型態

4 標準資料庫格式

5 似是而非的資料庫格式

6 下拉式選單之設計 提昇輸入的效率 結合範圍名稱和驗證 單一資料之選單設計 多重選項之選單設計 自動分類選單之設計 配合函數自動輸入資料

7 提昇輸入的效率

8 結合範圍名稱和驗證

9 單一資料之選單設計

10 多重選項之選單設計

11 自動分類選單之設計

12 配合函數自動輸入資料 =IF(B7="","",VLOOKUP(B7,選單基本資料!$H$2:$J$16,2,FALSE))

13 資料庫的排序 自訂清單之應用 自訂排序之設定 需要先行排序的常用指令 小計 Lookup Frequency

14 自訂清單之應用 提昇輸入效率 用於自訂排序

15 自訂排序之設定

16 資料庫篩選 自動篩選 進階篩選之應用

17 自動篩選

18 進階篩選之應用 用於三個以上的複雜條件

19 資料庫的分類統計 小計之應用 多重分類小計之應用 群組大綱之應用 資料庫函數Dsum()之應用

20 小計之應用 小計的欄位必須先行排序

21 多重分類小計之應用 使用兩個以上的欄位來做分類統計 使用多重欄位排序 小計要分二次來執行 注意事項

22 群組大綱之應用 電腦設備銷售統計表 北部地區 中部地區 南部地區 海外市場 一月 $85,000,000 $42,000,000
$37,000,000 $56,000,000 二月 $91,000,000 $79,000,000 $28,000,000 $43,000,000 三月 $59,000,000 $12,000,000 $82,000,000 第一季 $213,000,000 $180,000,000 $77,000,000 $181,000,000 四月 $81,000,000 $86,000,000 $9,000,000 五月 $84,000,000 $92,000,000 六月 $50,000,000 $89,000,000 $96,000,000 第二季 $220,000,000 $216,000,000 $259,000,000 $197,000,000 上半年 $433,000,000 $396,000,000 $336,000,000 $378,000,000

23 資料庫函數Dsum()之應用 =DSUM(A2:G21,"數學",I10:J11)

24 報表整合 結構相同之報表合併 複製/選擇性貼上/加 合併彙算 樞紐分析 結構 不同之報表合併

25 合併彙算

26 樞紐分析 樞紐分析表製作技巧 樞紐分析與群組之整合應用 計算欄位之應用 計算項目之應用 美化樞紐分析表

27 清單管理 兼具篩選與彙總功能 好用的Subtotal函數 隱藏資料的處理