Excel數(shù)據透視表應用大全：數(shù)據高效匯總與分析

01 實際案例剖析 —— 低效率與高效率　1
案例 1-1　一個工作簿內多個工作表匯總分析　2
案例 1-2　多個工作簿內多個工作表匯總分析　4
案例 1-3　歷年銷售數(shù)據分析　5
02 創(chuàng)建數(shù)據透視表的準備工作——數(shù)據整理與清洗　7
2.1　什么樣的表格才能創(chuàng)建數(shù)據透視表　8
2.1.1　表格結構必須規(guī)范　8
2.1.2　表格數(shù)據必須規(guī)范　8
2.2　快速整理表格數(shù)據實用技能與技巧　9
2.2.1　清除數(shù)據中的垃圾　9
2.2.2　修改非法日期　12
2.2.3　將編碼類的純數(shù)字轉換為文本型數(shù)字　15
2.2.4　將文本型數(shù)字轉換為能夠計算的純數(shù)字　16
2.2.5　空單元格填充數(shù)據　18
2.2.6　快速刪除表單中的空行　21
2.2.7　快速刪除小計行　23
2.2.8　刪除重復數(shù)據　24
2.2.9　數(shù)據分列　25
2.2.10　將二維表格轉換為一維表單　30
03 以一個一維表單制作數(shù)據透視表　3
3.1　以一個一維表單的全部數(shù)據制作數(shù)據透視表　38
3.1.1　數(shù)據區(qū)域固定的情況——最常見也最簡單　38
3.1.2　數(shù)據區(qū)域變動的情況——使用動態(tài)名稱　41
3.1.3　數(shù)據區(qū)域變動的情況——使用現(xiàn)有連接工具　46
3.2　以一個一維工作表的部分數(shù)據制作數(shù)據透視表　49
3.2.1　任何一個版本都可以使用的 Microsoft Query 方法　50
3.2.2　在 Excel 2016 版本中使用的 Power Query+ Power Pivo方法　53
3.3　在不打開工作簿的情況下制作數(shù)據透視表　59
3.3.1　現(xiàn)有連接方法　59
3.3.2　Microsoft Query 方法　59
3.3.3　Power Pivot 方法　59
04 以多個一維表單制作數(shù)據透視表　6
4.1　以一個工作簿里的多個一維工作表制作數(shù)據透視表　64
4.1.1　現(xiàn)有連接 +SQL 語句方法　64
4.1.2　SQL 基本知識簡介　68
4.1.3　Power Query+Power Pivot 方法　70
4.1.4　僅抽取每個表都有的幾列數(shù)據制作透視表　76
4.2　以多個工作簿里的多個一維工作表制作數(shù)據透視表　77
4.2.1　VBA 方法　77
4.2.2　Power Query 方法　79
05 以二維表格制作數(shù)據透視表　91
5.1　以一個二維表格數(shù)據制作數(shù)據透視表　92
5.2　以多個二維表格數(shù)據制作數(shù)據透視表　94
5.2.1　創(chuàng)建單頁字段的數(shù)據透視表　94
5.2.2　創(chuàng)建多頁字段的數(shù)據透視表　100
5.3　以多個工作簿的二維表格制作數(shù)據透視表　105
5.4　結構不一樣的二維表格制作數(shù)據透視表　109
5.5　大量二維表格的透視分析　111
5.5.1　每個表格只有一列數(shù)字　111
5.5.2　每個表格有多列數(shù)字　113
06 以多個關聯(lián)工作表制作數(shù)據透視表　116
6.1　利用 Microsoft Query 工具　118
6.2　利用 Power Pivot 工具　124
6.2.1　在當前工作簿上制作數(shù)據透視表　124
6.2.2　不打開源數(shù)據工作簿，在新工作簿上制作數(shù)據透視表　127
07 以其他類型文件制作數(shù)據透視表　131
7.1　以文本文件數(shù)據制作數(shù)據透視表　132
7.1.1　使用外部數(shù)據源　132
7.1.2　自文本工具　137
7.1.3　現(xiàn)有連接工具　138
7.1.4　Power Pivot 工具　139
7.1.5　Microsoft Query 工具　140
7.2　以數(shù)據庫數(shù)據制作數(shù)據透視表　144
08 制作數(shù)據透視表方法總結　145
8.1　制作數(shù)據透視表的普通方法　146
8.1.1　制作方法總結　146
8.1.2　如何查看或修改數(shù)據源　146
8.2　利用現(xiàn)有連接 +SQL 語句制作數(shù)據透視表方法　148
8.2.1　制作方法總結　148
8.2.2　如何查看或修改數(shù)據源　149
8.3　多重合并計算數(shù)據區(qū)域數(shù)據透視表方法　150
8.3.1　制作方法總結　150
8.3.2　如何查看及編輯數(shù)據源　151
8.4　Power Query+Power Pivot 制作數(shù)據透視表方法　151
8.4.1　制作方法總結　151
8.4.2　如何查看或編輯數(shù)據源　152
09 布局數(shù)據透視表　153
9.1　“數(shù)據透視表字段”窗格　154
9.1.1　改變“數(shù)據透視表字段”窗格布局　154
9.1.2　字段列表　155
9.1.3　篩選　155
9.1.4　行　157
9.1.5　列　158
9.1.6　值　159
9.1.7　“數(shù)據透視表字段”窗格與普通報表的對應關系　160
9.2　數(shù)據透視表的布局　161
9.2.1　布局基本方法　161
9.2.2　布局的快速方法　161
9.2.3　存在大量字段時如何快速找出某個字段　161
9.2.4　直接套用常見的數(shù)據透視表布局　162
9.2.5　延遲布局更新　162
9.2.6　恢復經典的數(shù)據透視表布局方式　163
9.3　數(shù)據透視表工具　164
9.3.1　“分析”選項卡　164
9.3.2　“設計”選項卡　167
9.4　數(shù)據透視表的快捷菜單命令　169
9.5　顯示或隱藏透視表右側的“數(shù)據透視表字段”窗格　170
10 數(shù)據透視表的設置與美化　171
10.1　設計透視表的樣式　172
10.1.1　套用一個現(xiàn)成的樣式　172
10.1.2　清除樣式是最常見的設置　173
10.2　設計報表布局　174
10.2.1　以壓縮形式顯示　175
10.2.2　以大綱形式顯示　176
10.2.3　以表格形式顯示　176
10.3　修改字段名稱　177
10.3.1　在單元格直接修改字段名稱　177
10.3.2　在字段設置對話框里修改　177
10.4　顯示 / 隱藏字段的分類匯總　178
10.4.1　設置某個字段的分類匯總　179
10.4.2　設置所有字段的分類匯總　179
10.5　顯示 / 隱藏行總計和列總計　180
10.5.1　列總計和行總計的定義　180
10.5.2　顯示或隱藏列總計和行總計　181
10.6　合并 / 取消合并標簽單元格　182
10.6.1　合并標簽單元格　183
10.6.2　取消合并標簽單元格　184
10.7　顯示 / 隱藏字段無數(shù)據的項目　184
10.8　對行字段和列字段的項目進行重新排序　185
10.8.1　手工排序　185
10.8.2　自定義排序　187
10.9　設置值字段的匯總依據　190
10.10　設置值字段的數(shù)字格式　191
10.10.1　設置常規(guī)數(shù)字格式　191
10.10.2　設置自定義數(shù)字格式　191
10.11　數(shù)據透視表的其他設置　192
10.11.1　重復項目標簽　192
10.11.3　更新數(shù)據透視表時不自動調整列寬　195
10.11.4　在每個項目后面插入空行　195
10.11.5　將篩選字段垂直或水平布局排列　195
10.11.6　顯示 / 不顯示“折疊 / 展開”按鈕　197
10.11.7　刷新數(shù)據透視表　198
11 利用數(shù)據透視表分析數(shù)據的實用技能　　199
11.1　對數(shù)據透視表進行重新布局　200
11.1.1　示例數(shù)據　200目　　錄
11.1.2　制作基本的分析報告　201
11.2　排序篩選找出前 N 大（小）項目　203
11.2.1　制作基本的數(shù)據透視表　203
11.2.2　數(shù)據排序做排名分析　204
11.2.3　數(shù)據篩選尋找最好（最差）的幾個項目　204
11.2.4　快速篩選保留選中的項目　206
11.2.5　清除排序和篩選　207
11.3　設置字段匯總依據　207
11.3.1　設置字段匯總依據的基本方法　208
11.3.2　應用案例 1——員工工資分析　208
11.3.3　應用案例 2——員工信息分析　210
11.3.4　應用案例 3——銷售分析　211
11.4　設置值字段顯示方式　213
11.4.1　占比分析　214
11.4.2　差異分析　222
11.4.3　累計分析　223
11.4.4　恢復默認的顯示方式　227
11.5　組合字段　227
11.5.1　組合日期，制作年、季度、月度匯總報告　227
11.5.2　組合時間，跟蹤每天、每小時、每分鐘的數(shù)據變化　230
11.5.3　組合數(shù)字，分析指定區(qū)間內的數(shù)據——員工信息分析　234
11.5.4　組合數(shù)字，分析指定區(qū)間內的數(shù)據——工資數(shù)據分析　236
11.5.5　組 合 數(shù) 字， 分 析 指 定 區(qū) 間 內 的 數(shù) 據 ——銷售分析　238
11.5.6　對文本進行組合，增加更多的分析維度　240
11.5.7　組合日期時應注意的問題　243
11.5.8　組合數(shù)字時應注意的問題　244
11.5.9　某個字段內有不同類型數(shù)據時不能自動分組　244
11.5.10　頁字段不能進行組合　244
11.5.11　取消組合　245
12 使用切片器快速篩選報表　246
12.1　插入并使用切片器　248
12.1.1　插入切片器的按鈕　248
12.1.2　插入切片器的方法　248
12.1.3　切片器的使用方法　250
12.2　設置切片器樣式　250
12.2.1　套用切片器樣式　250
12.2.2　新建切片器樣式　251
12.2.3　修改自定義切片器樣式　254
12.2.4　設置切片器的項目顯示列數(shù)　254
12.2.5　布局數(shù)據透視表和切片器　255
12.3　用切片器控制數(shù)據透視表（數(shù)據透視圖）　256
12.3.1　多個切片器聯(lián)合控制一個數(shù)據透視表（數(shù)據透視圖）　256
12.3.2　一個或多個切片器控制多個數(shù)據透視表（數(shù)據透視圖）　256
12.3.3　刪除切片器　257
12.4　篩選日期的自動化切片器——日程表　257
12.4.1　插入日程表　258
12.4.2　日程表的使用方法　259
13 為數(shù)據透視表添加自定義計算字段和計算項　　261
13.1　自定義計算字段　262
13.1.1　添加計算字段的基本方法　262
13.1.2　修改計算字段　265
13.1.3　刪除計算字段　265
13.1.4　列示出所有自定義字段信息　265
13.1.5　可以改變計算字段的匯總方式嗎　266
13.2　自定義計算項　266
13.2.1　添加計算項的基本方法　266
13.2.2　自定義計算項的幾個重要說明　269
13.2.3　修改自定義計算項　269
13.2.4　刪除自定義計算項　270
13.2.5　列示出所有自定義計算項信息　270
13.3　添加計算字段和計算項的注意事項　270
13.3.1　分別在什么時候添加計算字段和計算項　270
13.3.2　同時添加計算字段和計算項的幾個問題　271
13.3.3　哪些情況下不能添加自定義計算字段和計算項　271
13.3.4　自定義計算字段能使用工作簿函數(shù)嗎　272
13.3.5　自定義計算字段能使用單元格引用和名稱嗎　272
13.4　綜合應用案例　272
13.4.1　在數(shù)據透視表里進行排位分析　272
13.4.2　兩年同比分析　275
13.4.3　兩年客戶流動分析　276
13.4.4　動態(tài)進銷存管理　278
14 快速制作明細表　282
14.1　每次制作一個明細表　284
14.2　一次批量制作多個明細表　286
15 數(shù)據透視圖與數(shù)據透視表的聯(lián)合應用　　289
15.1　創(chuàng)建數(shù)據透視圖　290
15.1.1　在創(chuàng)建數(shù)據透視表時創(chuàng)建數(shù)據透視圖　290
15.1.2　在現(xiàn)有數(shù)據透視表的基礎上創(chuàng)建數(shù)據透視圖　291
15.1.3　數(shù)據透視圖的結構　291
15.1.4　關于數(shù)據透視圖分類軸　292
15.1.5　關于數(shù)據透視圖的數(shù)據系列　292
15.2　數(shù)據透視圖的美化　293
15.2.1　數(shù)據透視圖的常規(guī)美化　293
15.2.2　數(shù)據透視圖的特殊處理　293
15.3　利用數(shù)據透視圖分析數(shù)據　293
15.3.1　通過布局字段分析數(shù)據　294
15.3.2　通過篩選字段分析數(shù)據　294
15.3.3　利用切片器控制數(shù)據透視圖　295
15.4　數(shù)據透視圖和數(shù)據透視表綜合應用案例　296
15.4.1　一個簡單的二維表格動態(tài)分析　296
15.4.2　一個稍復雜的流水數(shù)據分析　298
16 數(shù)據透視表其他應用案例　302
16.1　生成不重復的新數(shù)據清單　303
16.1.1　在某列中查找重復數(shù)據并生成不重復的新數(shù)據清單　303
16.1.2　在多列中查找重復數(shù)據并生成不重復的新數(shù)據清單　304
16.1.3　從多個工作表中查找重復數(shù)據并生成不重復的新數(shù)據清單　305
16.2　快速核對數(shù)據　305
16.2.1　單列數(shù)據的核對　305
16.2.2　多列數(shù)據的核對　307
16.3　轉換表格結構　309
16.3.1　二維表格轉換為一維表格　309
16.3.2　將多列文字描述轉換為一個列表清單　309