第1章 零基礎學數(shù)據(jù)透視表必知1

1.1 你真的了解數(shù)據(jù)透視表嗎 2

1.1.1 數(shù)據(jù)透視表到底有什么用 2

1.1.2 哪些數(shù)據(jù)可以用透視表分析 3

1.2 數(shù)據(jù)透視表的數(shù)據(jù)源結(jié)構(gòu)必須規(guī)范3

1.2.1 化繁為簡，去掉多余表頭 3

1.2.2 更改字段排列順序 4

【分析實例】將數(shù)據(jù)表格中的列移動到相應的位置4

1.2.3 刪除數(shù)據(jù)區(qū)域中的空行或空列 5

【分析實例】刪除發(fā)貨記錄表中的空白行5

1.2.4 拒絕多余的合計行 6

【分析實例】刪除北京市人口普查表中的合計行6

1.2.5 拒絕合并單元格 8

1.3 學好數(shù)據(jù)透視表必備的知識儲備8

1.3.1 認識數(shù)據(jù)透視表的結(jié)構(gòu) 8

1.3.2 數(shù)據(jù)透視表的常用術(shù)語 9

1.3.3 認識數(shù)據(jù)透視表的選項卡組 10

1.4 動手創(chuàng)建首個數(shù)據(jù)透視表 12

【分析實例】根據(jù)電子產(chǎn)品銷售表創(chuàng)建數(shù)據(jù)透視表 13

第2章 輕松改變數(shù)據(jù)透視表的整體布局 15

2.1 數(shù)據(jù)透視表頁面區(qū)域的使用16

2.1.1 改變數(shù)據(jù)透視表的整體布局 16

【分析實例】客戶對各商品總購買量分析 17

2.1.2 顯示報表篩選字段的多個數(shù)據(jù)項 18

【分析實例】顯示北京和貴陽銷售額合計 18

2.1.3 水平/垂直并排顯示篩選字段 19

【分析實例】水平并排顯示報表中的篩選字段20

2.1.4 讓報表中的字段更簡潔 21

【分析實例】將銷售人員在各個城市的銷售情況分布在不同的工作表中21

2.1.5 復制和移動數(shù)據(jù)透視表 21

2.2 數(shù)據(jù)透視表的布局設置23

2.2.1 更改報表布局的顯示形式 23

2.2.2 更改分類匯總的顯示方式 26

2.2.3 總計的禁用與啟動 27

【分析實例】統(tǒng)計各月實際開支的總額 28

2.2.4 在每個項目后插入空行 29

2.2.5 顯示或隱藏數(shù)據(jù)透視表的字段或字段項 29

2.2.6 合并且居中帶標簽的單元格 32

2.3 獲取數(shù)據(jù)透視表的數(shù)據(jù)源 33

2.3.1 顯示數(shù)據(jù)透視表數(shù)據(jù)源的所有信息 33

【分析實例】通過數(shù)據(jù)透視表獲取各月各項開支清單33

2.3.2 顯示統(tǒng)計結(jié)果的明細數(shù)據(jù) 34

第3章 數(shù)據(jù)透視表的美化與編輯操作35

3.1 使用樣式美化報表 36

3.1.1 應用數(shù)據(jù)透視表內(nèi)置樣式 36

【分析實例】使用內(nèi)置樣式美化數(shù)據(jù)透視表36

3.1.2 自定義數(shù)據(jù)透視表樣式 37

【分析實例】在現(xiàn)有報表內(nèi)置樣式的基礎上自定義數(shù)據(jù)透視表樣式 38

3.2 處理數(shù)據(jù)透視表單元格41

3.2.1 處理數(shù)據(jù)透視表的空白項 41

【分析實例】對數(shù)據(jù)透視表中的空白項進行處理 41

3.2.2 設置空白單元格和錯誤值的顯示方式 43

【分析實例】將數(shù)據(jù)透視表中的空白單元格替換為未購買文本 43

3.3 在數(shù)據(jù)透視表中應用條件格式 44

3.3.1 通過公式使數(shù)據(jù)結(jié)果突出顯示 44

【分析實例】突出顯示超出預算的實際開支費用 44

3.3.2 通過數(shù)據(jù)條使數(shù)據(jù)大小更清晰 46

【分析實例】以數(shù)據(jù)條對比分析產(chǎn)品訂單金額46

3.3.3 通過圖標集使數(shù)據(jù)等級分明 47

【分析實例】將員工工資分為多個等級并獲取每個員工的工資等級 47

3.3.4 通過色階展示數(shù)據(jù)的分布變化 48

【分析實例】通過色階展示銷售變化趨勢 49

3.4 刷新操作確保數(shù)據(jù)始終最新49

3.4.1 手動刷新數(shù)據(jù)透視表的方法 49

3.4.2 如何實現(xiàn)數(shù)據(jù)透視表的自動刷新 50

【分析實例】自動刷新員工信息表數(shù)據(jù) 51

3.4.3 刷新數(shù)據(jù)透視表后保持列寬不變 52

3.4.4 定時刷新外部數(shù)據(jù)源的數(shù)據(jù)透視表 53

3.4.5 推遲布局更新 54

第4章 控制數(shù)據(jù)透視表的顯示順序和內(nèi)容 55

4.1 數(shù)據(jù)透視表的排序 56

4.1.1 手動排序 56

【分析實例】調(diào)整各項開支費用的順序 56

4.1.2 自動排序 57

4.1.3 使用其他排序選項排序 57

【分析實例】將服裝匹配程度按照20歲以下年齡段排序 58

【分析實例】將銷售額按銷售人員姓氏筆畫排序 59

【分析實例】將工廠員工工資按職位高低進行排序 60

4.1.4 通過字段篩選器對標簽排序 62

【分析實例】將員工姓名字段標簽按升序排列 62

4.2 數(shù)據(jù)透視表的篩選 63

4.2.1 利用字段的下拉列表進行篩選 64

4.2.2 利用字段的標簽進行篩選 64

【分析實例】分析以GH開頭的商品訂購情況64

4.2.3 利用值篩選進行篩選 65

【分析實例】查看訂單金額總計超過2 000 000元的商品 66

4.2.4 利用字段的搜索文本框進行篩選 67

4.2.5 自定義篩選 67

【分析實例】在報表中查看除鞋類外其他商品的售賣情況 67

4.3 運用切片器控制數(shù)據(jù)透視表的顯示 68

4.3.1 在數(shù)據(jù)透視表中插入切片器 69

4.3.2 認識切片器結(jié)構(gòu) 70

4.3.3 設置切片器的格式 70

【分析實例】在員工姓名切片器中每行顯示3個按鈕71

4.3.4 使用切片器篩選數(shù)據(jù) 73

4.3.5 共享切片器實現(xiàn)多個數(shù)據(jù)透視聯(lián)動 75

【分析實例】將兩個數(shù)據(jù)透視表連接到同一切片器 75

4.3.6 清除切片器的篩選器 76

4.3.7 對切片器字段項排序 76

4.3.8 隱藏或刪除切片器 77

第5章 對數(shù)據(jù)透視表的項目進行自由組合 79

5.1 根據(jù)實際情況使用不同的分組方式 80

5.1.1 少量或部分數(shù)據(jù)手動分組 80

【分析實例】將報表中的各個城市按地理位置分組 80

5.1.2 大量有規(guī)律數(shù)據(jù)自動分組 82

【分析實例】將銷售記錄以周為單位分組82

【分析實例】以月為單位分析各員工的生產(chǎn)量 83

【分析實例】以1 000步長為單位統(tǒng)計各區(qū)間銷售額銷售次數(shù)和總銷售額 84

5.1.3 如何取消設置的組合 85

5.2 借助函數(shù)對數(shù)據(jù)透視表進行分組 87

5.2.1 根據(jù)自身的特點進行分組 87

【分析實例】分類統(tǒng)計店內(nèi)專營商品和兼營商品 88

5.2.2 按不等距步長組合數(shù)據(jù)項 89

【分析實例】統(tǒng)計5月上、中以及下旬的商品訂購情況 89

第6章 在數(shù)據(jù)透視表中可以進行哪些計算 91

6.1 更改數(shù)據(jù)透視表的匯總方式92

6.1.1 更改匯總依據(jù) 92

6.1.2 對同一字段使用多種匯總方式 93

【分析實例】分析微波爐銷售單價的平均值、最大值以及最小值 94

6.2 相同的數(shù)據(jù)，不一樣的結(jié)果96

6.2.1 總計的百分比 97

【分析實例】分析員工銷售額在公司總銷售額中的占比情況97

6.2.2 行/列匯總的百分比分析 98

【分析實例】分析銷售員各個商品銷售額的占比情況98

6.2.3 百分比、父級匯總的百分比、父列/行匯總的百分比 98

6.2.4 差異分析 100

【分析實例】分析各類商品銷售額與上一月相比的增減情況101

【分析實例】分析各月開支情況增長的百分比103

6.2.5 按某一字段匯總及其匯總百分比 104

6.2.6 指數(shù) 104

6.3 在報表中使用計算字段 105

6.3.1 插入計算字段 105

【分析實例】計算商品銷售的毛利潤106

【分析實例】計算員工提成金額 108

6.3.2 修改計算字段 109

【分析實例】根據(jù)公司提成規(guī)則修改員工提成比例 110

6.3.3 刪除計算字段 111

6.4 在報表中使用計算項 112

6.4.1 插入計算項 112

【分析實例】分析2019年上半年較2018年上半年的開支增長情況 113

6.4.2 修改與刪除計算項 114

6.4.3 更改計算項的求解次序 115

6.5 管理數(shù)據(jù)透視表中計算字段和計算項 116

6.5.1 獲取計算字段和計算項公式 116

6.5.2 應注意計算字段的計算實質(zhì) 116

6.5.3 計算字段和計算項的使用應謹慎 118

第7章 使用函數(shù)提取報表中的數(shù)據(jù) 119

7.1 初識數(shù)據(jù)透視表GETPIVOTDATA()函數(shù) 120

7.1.1 快速生成數(shù)據(jù)透視表函數(shù)公式 120

7.1.2 數(shù)據(jù)透視表函數(shù)公式舉例 121

7.1.3 數(shù)據(jù)透視表函數(shù)的語法 122

7.2 GETPIVOTDATA()函數(shù)具體應用 123

7.2.1 自動匯總方法下靜態(tài)獲取數(shù)據(jù)透視表數(shù)據(jù) 123

7.2.2 自動匯總方法下動態(tài)獲取數(shù)據(jù)透視表數(shù)據(jù) 125

7.2.3 利用數(shù)據(jù)透視表函數(shù)進行計算 127

【分析實例】利用數(shù)據(jù)透視表函數(shù)計算分類匯總百分比.127

7.2.4 從多個數(shù)據(jù)透視表中獲取數(shù)據(jù) 130

【分析實例】統(tǒng)計分析多個數(shù)據(jù)透視表數(shù)據(jù)130

7.2.5 使用數(shù)據(jù)透視表函數(shù)應注意的問題以及可用的匯總函數(shù) 132

第8章 創(chuàng)建動態(tài)數(shù)據(jù)透視表的方法全掌握133

8.1 通過名稱創(chuàng)建動態(tài)數(shù)據(jù)透視表 134

8.1.1 定義名稱的方法 134

【分析實例】為商品銷售明細統(tǒng)計表中的每列定義一個名稱135

8.1.2 定義名稱的規(guī)則 136

8.1.3 定義名稱的使用 137

8.1.4 定義名稱的修改與刪除 139

8.1.5 定義動態(tài)名稱需要使用的兩個函數(shù) 140

8.1.6 根據(jù)名稱創(chuàng)建動態(tài)數(shù)據(jù)透視表 141

【分析實例】創(chuàng)建商品銷售明細的動態(tài)數(shù)據(jù)透視表 142

8.2 通過列表創(chuàng)建動態(tài)數(shù)據(jù)透視表 144

8.2.1 創(chuàng)建列表 144

8.2.2 根據(jù)列表創(chuàng)建動態(tài)數(shù)據(jù)透視表 145

【分析實例】創(chuàng)建物流訂單分析的動態(tài)數(shù)據(jù)透視表 145

第9章 多區(qū)域的報表如何創(chuàng)建147

9.1 創(chuàng)建多重合并計算數(shù)據(jù)區(qū)域的數(shù)據(jù)透視表 148

9.1.1 制作基于多區(qū)域的數(shù)據(jù)透視表 148

9.1.2 將每個區(qū)域作為一個分組創(chuàng)建數(shù)據(jù)透視表 148

【分析實例】統(tǒng)計分析公司員工4～6月的工資數(shù)據(jù)149

9.1.3 創(chuàng)建自定義字段的多重合并數(shù)據(jù)透視表 152

【分析實例】分別按年和季度分析員工工資152

9.1.4 不同工作簿中的數(shù)據(jù)也可以合并創(chuàng)建數(shù)據(jù)透視表155

【分析實例】匯總不同工作簿的銷售情況 156

9.1.5 創(chuàng)建動態(tài)多重合并計算數(shù)據(jù)區(qū)域的數(shù)據(jù)透視表159

【分析實例】利用列表功能動態(tài)合并各個城市的銷售統(tǒng)計 159

9.2 多列文本的列表創(chuàng)建數(shù)據(jù)透視表 162

9.2.1 像數(shù)據(jù)庫一樣進行列表區(qū)域操作 162

【分析實例】分析近兩月的銷售數(shù)據(jù)163

9.2.2 導入數(shù)據(jù)還可以添加字段 166

【分析實例】統(tǒng)計分析各城市GDP時添加區(qū)域字段166

9.2.3 有選擇性地導入數(shù)據(jù)字段 168

9.2.4 數(shù)據(jù)分析是自動排除重復項 170

9.3 使用Microsoft Query查詢創(chuàng)建數(shù)據(jù)透視表 171

9.3.1 Microsoft Query查詢單個數(shù)據(jù)列表創(chuàng)建數(shù)據(jù)透視表 172

【分析實例】銷售合同匯總分析 172

9.3.2 Microsoft Query查詢多個數(shù)據(jù)列表創(chuàng)建數(shù)據(jù)透視表 174

【分析實例】員工工資數(shù)據(jù)分析 175

9.3.3 記錄不一致可以編輯連接方式來確定誰主誰輔178

【分析實例】統(tǒng)計分析員工缺勤情況178

9.4 其他方式創(chuàng)建數(shù)據(jù)透視表 182

9.4.1 使用文本數(shù)據(jù)源創(chuàng)建數(shù)據(jù)透視表 182

【分析實例】使用文本文件創(chuàng)建數(shù)據(jù)透視表182

9.4.2 使用公式輔助創(chuàng)建數(shù)據(jù)透視表 188

【分析實例】使用公式輔助創(chuàng)建數(shù)據(jù)透視表分析考勤情況 188

第10章 透視表數(shù)據(jù)的圖形化展示 191

10.1 圖形化報表的方法 192

10.1.1 創(chuàng)建數(shù)據(jù)透視圖 192

【分析實例】使用圖表分析各個員工的銷售額192

【分析實例】使用數(shù)據(jù)透視圖展示個人開支情況 194

【分析實例】使用向?qū)��?chuàng)建數(shù)據(jù)透視圖展示報表 195

10.1.2 創(chuàng)建迷你圖 199

【分析實例】使用迷你圖分析商品銷售額 199

10.2 數(shù)據(jù)透視圖的基本操作 201

10.2.1 移動數(shù)據(jù)透視圖 201

10.2.2 調(diào)整數(shù)據(jù)透視圖的大小 202

10.2.3 更改數(shù)據(jù)透視圖的數(shù)據(jù)系列格式 203

【分析實例】同時分析學習費的開支和生活費的變化趨勢 204

10.2.4 設置圖表區(qū)和繪圖區(qū)的底色 205

10.2.5 應用圖表樣式 207

【分析實例】為圖表應用內(nèi)置樣式207

10.2.6 刷新和刪除數(shù)據(jù)透視圖 208

10.3 數(shù)據(jù)透視圖布局的修改 210

10.3.1 數(shù)據(jù)透視圖的結(jié)構(gòu)布局 210

10.3.2 合理使用圖表元素 211

【分析實例】為餅圖添加數(shù)據(jù)標簽213

【分析實例】分析二季度開支變化趨勢 215

10.3.3 應用內(nèi)置圖表布局，方便又專業(yè) 217

10.3.4 數(shù)據(jù)透視表與數(shù)據(jù)透視圖的關(guān)系 217

10.4 數(shù)據(jù)透視圖的應用 219

10.4.1 使用圖表模板 219

【分析實例】使用模板圖表創(chuàng)建分析上半年開支情況221

10.4.2 保留數(shù)據(jù)透視圖的分析結(jié)果 222

10.4.3 數(shù)據(jù)透視圖之動態(tài)分析技巧 224

【分析實例】使用數(shù)據(jù)透視圖動態(tài)分析各個員工生產(chǎn)數(shù)據(jù) 224

第11章 運用Power Pivot可視化進行數(shù)據(jù)分析227

11.1 初識Power Pivot 228

11.1.1 什么是Power Pivot 228

11.1.2 啟用Power Pivot 228

11.2 為Power Pivot準備數(shù)據(jù) 231

11.2.1 為Power Pivot鏈接本工作簿內(nèi)的數(shù)據(jù)231

11.2.2 為Power Pivot獲取外部鏈接數(shù)據(jù)232

【分析實例】獲取家電銷售外部鏈接數(shù)據(jù) 232

11.3 Power Pivot可視化分析的具體操作 234

11.3.1 利用Power Pivot創(chuàng)建數(shù)據(jù)透視表235

【分析實例】根據(jù)數(shù)據(jù)模型用Power Pivot創(chuàng)建數(shù)據(jù)透視表 235

11.