Excel數據透視表應用大全for Excel 365 & Excel 2019

第 1 章 創建數據透視表 ·· 1
1.1 什麼是數據透視表 ··· 1
1.2 數據透視表的數據源 1
1.3 自己動手創建第一個數據透視表 ··· 1
1.4 使用推薦的數據透視表 4
1.5 數據透視表用途 ·· 6
1.6 何時使用數據透視表分析數據 ·· 6
1.7 數據透視表的結構 ··· 6
1.8 數據透視表欄位清單 7
1.8.1 隱藏和顯示【數據透視表字段】列表 ·7
1.8.2 在【數據透視表字段】清單中顯示更多的欄位 ··9
1.8.3 【數據透視表欄位清單】對話方塊中的排序與篩選 10
1.8.4 更改【選擇要添加到報表的欄位】中欄位的顯示順序 ·10
1.8.5 在【數據透視表字段】列表中快速搜索 ···12
1.9 數據透視表中的術語 ·· 12
1.10 【數據透視表工具】··· 13
1.10.1 【數據透視表分析】選項卡的主要 功能 ··13
1.10.2 【設計】選項卡的主要功能15
1.11 創建動態數據透視表 15
1.11.1 定義名稱法創建動態數據透視表 ···16
1.11.2 使用“表格”功能創建動態數據透視表 ···17
1.12 創建複合範圍的數據透視表 ·· 18
1.12.1 利用多重合並計算數據區域將二維數據列表轉換為一維數據列表 ··18
1.13 模型數據透視表 ··· 22
1.13.1 將數據添加到數據模型創建數據透視表 ··22
1.14 普通數據透視表與利用數據模型創建的數據透視表的區別 · 23
1.15 模型數據透視表的局限性 · 26
1.16 將普通數據透視表轉化為模型數據透視表 ··· 26

第 2 章 整理好Excel數據源 ·· 27
2.1 數據管理規範 ··· 27
2.1.1 Excel工作簿名稱中不能包含非法字元 ···27
2.1.2 數據源不能包含空白數據行或數據列 ·28
2.1.3 數據源欄位名稱不能重複 28
2.1.4 數據源不能包含合併儲存格 29
2.1.5 數據源不能包含多層表頭 29
2.1.6 數據源不能包含小計行或總計行 ·29
2.1.7 數據源的數據格式要統一和規範 ·29
2.1.8 數據源不要拆分到多個工作表中 ·29
2.1.9 數據源不要拆分到多個工作簿中 ·29
2.2 對不規範數據的整理技巧 ··· 30
2.2.1 對合併儲存格的處理 ···30
2.2.2 對文本型數字的處理 ···31
2.2.3 快速刪除重複記錄 ···32
2.2.4 對不規範日期的處理 ···33
2.2.5 二維表轉換為一維表的技巧 35
2.2.6 將數據源按分隔符號分列為多欄位數據區域 37
2.2.7 網頁複製數據的整理 ···39

第 3 章 改變數據透視表的佈局 ·· 45
3.1 改變數據透視表的整體佈局 45
3.2 設置數據透視表的默認佈局 47
3.3 數據透視表篩選區域的使用 49
3.3.1 顯示報表篩選欄位的多個數據項 ·49
3.3.2 水準並排顯示報表篩選欄位 50
3.3.3 垂直並排顯示報表篩選欄位 52
3.3.4 顯示報表篩選頁···52
3.4 啟用經典數據透視表佈局 ··· 54
3.5 整理數據透視表字段 ·· 55
3.5.1 重命名欄位 ·55
3.5.2 整理複合欄位 ·57
3.5.3 刪除欄位 ·58
3.5.4 隱藏欄位標題 ·59
3.5.5 活動欄位的折疊與展開 ···60
3.6 改變數據透視表的報告格式 61
3.6.1 改變數據透視表的報表佈局 61
3.6.2 分類匯總的顯示方式 ···64
3.6.3 在每項後插入空行 ···66
3.6.4 總計的禁用與啟用 ···67
3.6.5 合並且居中排列帶標籤的儲存格 ·69
3.7 清除已刪除數據的標題項 ··· 69
3.8 數據透視表的複製和移動 ··· 71
3.8.1 複製數據透視表···71
3.8.2 移動數據透視表···72
3.8.3 刪除數據透視表···74
3.9 影子數據透視表 74
3.10 獲取數據透視表的數據源資訊 ·· 77
3.10.1 顯示數據透視表數據源的所有資訊 ···77
3.10.2 顯示數據透視表某個項目的明細數據 ···78
3.10.3 禁止顯示數據源的明細數據 ··78

第 4 章 刷新數據透視表 79
4.1 手動刷新數據透視表 ·· 79
4.2 打開檔時自動刷新 ·· 80
4.3 刷新連結在一起的數據透視表 81
4.4 刷新引用外部數據源的數據透視表 ·· 81
4.5 定時刷新 ··· 82
4.6 使用VBA代碼設置自動刷新 ··· 83
4.7 全部刷新數據透視表 ·· 85
4.8 共用數據透視表緩存 ·· 86
4.9 延遲佈局更新 ··· 87
4.10 數據透視表的刷新注意事項 ·· 88
4.10.1 海量數據源限制數據透視表的刷新速度 ··88
4.10.2 數據透視表刷新數據丟失 ··88

第 5 章 數據透視表的格式設置 ·· 90
5.1 設置數據透視表的格式 ·· 90
5.1.1 自動套用數據透視表樣式 90
5.1.2 自訂數據透視表樣式 ···93
5.1.3 自訂數據透視表樣式的複製 97
5.1.4 清除數據透視表中已經應用的樣式 ·98
5.1.5 數據透視表刷新後如何保持調整好的列寬 99
5.1.6 批量設置數據透視表中某類項目的格式 ··100
5.1.7 修改數據透視表數據的數字格式 ···102
5.1.8 自訂數字格式·107
5.1.9 設置錯誤值的顯示方式 ··109
5.1.10 處理數據透視表中的空白數據項 ·110
5.2 數據透視表與條件格式 112
5.2.1 突出顯示數據透視表中的特定數據 ···112
5.2.2 數據透視表與數據條 ·114
5.2.3 數據透視表與圖示集 ·115
5.2.4 數據透視表與色階 ·116
5.2.5 修改數據透視表中應用的條件格式 ···117

第 6 章 在數據透視表中排序和篩選 · 120
6.1 數據透視表排序 ··· 120
6.1.1 默認排序 ···120
6.1.2 手動排序 ···120
6.1.3 自動排序 ···122
6.1.4 使用其他排序選項 ·124
6.1.5 對【篩選區域】的欄位排序 133
6.2 數據透視表篩選 ··· 135
6.2.1 利用欄位篩選下拉清單進行篩選 ···135
6.2.2 利用標籤篩選命令進行篩選 ··136
6.2.3 利用值篩選命令進行篩選 ··137
6.2.4 使用日期篩選 ···138
6.2.5 使用欄位篩選列表的搜索框篩選 ···139
6.2.6 在搜索文字方塊中進行多關鍵字搜索 ···140
6.2.7 同時對標籤和值篩選 ·141
6.2.8 篩選時保持總計值不變 ··142
6.2.9 自動篩選 ···143
6.2.10 自動篩選與值篩選的衝突 144
6.2.11 清除數據透視表中的篩選 147

第 7 章 數據透視表的切片器 ··· 149
7.1 切片器結構 · 150
7.2 在數據透視表中插入切片器 ·· 150
7.3 篩選多個欄位項 ··· 152
7.3.1 共用切片器實現多個數據透視表聯動 ···152
7.3.2 清除切片器的篩選器 ·154
7.3.3 在加密的工作表中使用切片器 ···154
7.4 更改切片器的前後顯示順序 ·· 155
7.5 切片器欄位項排序 ··· 156
7.5.1 對切片器內的欄位項進行昇冪或降冪排列 ··157
7.5.2 對切片器內的欄位項進行自訂排序 ···158
7.5.3 不顯示從數據源刪除的項目 ··158
7.5.4 隱藏沒有數據的項 ·160
7.6 設置切片器樣式 ··· 161
7.6.1 多列顯示切片器內的欄位項 ··161
7.6.2 更改切片器內欄位項的大小 ··161
7.6.3 更改切片器的大小 ·162
7.6.4 切片器自動套用格式 ·163
7.6.5 設置切片器的字體格式 ··163
7.6.6 格式切片器 ···165
7.7 更改切片器的標題 ··· 166
7.8 隱藏切片器 · 167
7.8.1 隱藏切片器的標題 ·167
7.8.2 隱藏切片器 ···167
7.9 刪除切片器 · 168

第 8 章 數據透視表的日程表 ··· 169
8.1 插入日程表 · 169
8.2 利用日程表按季度進行篩選數據 ··· 170
8.3 為數據透視表插入兩個時間級別的日程表 ··· 171
8.4 日程表的格式化 ··· 173
8.5 每個欄位允許多個篩選 173
8.6 共用日程表實現多個數據透視表聯動 175
8.7 日程表與創建組的區別 176

第 9 章 數據透視表的專案組合 177
9.1 手動組合數據透視表內的數據項 ··· 177
9.2 自動組合數據透視表內的數值型數據項 · 179
9.2.1 按等距步長組合數值型數據項 ···179
9.2.2 組合數據透視表內的日期型數據項 ···180
9.3 取消專案的自動組合 182
9.3.1 取消專案的自動組合 ·182
9.3.2 取消手動組合 ···183
9.3.3 啟用與禁用自動日期組合 ··184
9.4 在專案組合中運用函數輔助處理 ··· 184
9.4.1 按不等距長自動組合數值型數據項 ···184
9.4.2 按條件組合日期型數據項 ··185
9.5 影響自動組合的因素 186

第 10 章 在數據透視表中執行計算 ·· 189
10.1 對同一欄位使用多種匯總方式 189
10.2 更改數據透視表預設的欄位匯總方式 ·· 192
10.3 非重複計數的值匯總方式 ··· 195
10.4 自訂數據透視表的值顯示方式 ·· 198
10.4.1 數據透視表自訂值顯示方式描述 ·198
10.4.2 “總計的百分比”值顯示方式 ···198
10.4.3 “列匯總的百分比”值顯示方式 200
10.4.4 “行匯總的百分比”值顯示方式 201
10.4.5 “百分比”值顯示方式 ···202
10.4.6 “父行匯總的百分比”數據顯示方式 204
10.4.7 “父級匯總的百分比”數據顯示方式 205
10.4.8 “差異”值顯示方式 ··207
10.4.9 “差異百分比”值顯示方式 ···209
10.4.10 “按某一欄位匯總”數據顯示方式 ··210
10.4.11 “昇冪排列”值顯示方式 ·211
10.4.12 “指數”值顯示方式213
10.4.13 修改和刪除自訂數據顯示方式 ···214
10.5 在數據透視表中使用計算欄位和計算項 ··· 214
10.5.1 創建計算欄位 ··214
10.5.2 修改數據透視表中的計算欄位 ·222
10.5.3 刪除數據透視表中的計算欄位 ·223
10.5.4 計算欄位的局限性···224
10.5.5 創建計算項 ·224
10.5.6 改變計算項的求解次序 233
10.5.7 列示數據透視表計算欄位和計算項的公式 235
10.5.8 運用OLAP工具將數據透視表轉換為公式 235
10.6 數據透視表函數應用 ·· 238
10.6.1 快速生成數據透視表函數公式 ·238
10.6.2 從多個數據透視表中獲取數據 ·240
10.6.3 應用數據透視表函數根據關鍵字彙總 242

第 11 章 利用多樣的數據源創建數據透視表 · 244
11.1 使用文本數據源創建數據透視表 ·· 244
11.2 使用Access數據庫創建數據透視表 250
11.3 使用數據庫創建數據透視表 251
11.4 使用SQL Server Analysis Services OLAP創建數據透視表 · 254
11.4.1 OLAP多維數據庫簡介 ·254
11.4.2 基於OLAP創建數據透視表·256
11.4.3 在數據透視表中進行數據鑽取 ·259
11.5 使用OData數據源創建數據透視表 ·· 260
11.6 使用“Microsoft Query”和導入外部數據創建透視表 ··· 263
11.6.1 匯總同一工作簿中的多個數據列表 ·264
11.6.2 Microsoft Query創建參數查詢數據透視表 ·268
11.7 運用SQL查詢技術創建數據透視表 · 271
11.7.1 導入不重複記錄創建數據透視表 ·271
11.7.2 匯總關聯數據列表中符合關聯條件的指定欄位部分記錄···272

第 12 章 利用Power Query進行數據清洗 · 274
12.1 利用Power Query填充合併儲存格 ·· 274
12.2 利用Power Query刪除重複記錄 · 277
12.3 利用Power Query導入並處理文字檔 ··· 278
12.4 利用Power Query分列、合併數據 ·· 279
12.5 利用Power Query逆透視二維表 · 283
12.6 利用Power Query透視一維數據 · 286
12.7 利用Power Query清洗數據 288

第 13 章 用Power Pivot建立數據模型 · 293
13.1 模型數據透視表 · 293
13.1.1 利用模型數據透視表進行多表關聯 ·293
13.2 Power Pivot與數據透視表·· 297
13.2.1 載入Microsoft Power Pivot for Excel ···297
13.2.2 為Power Pivot準備數據 ··298
13.2.3 利用Power Pivot創建數據透視表 ···301
13.2.4 利用Power Pivot創建數據透視圖 ···303
13.3 在Power Pivot中使用DAX語言 ·· 304
13.3.1 使用DAX創建計算列 ···304
13.3.2 使用DAX創建度量值 ···308
13.3.3 常用DAX函數應用 ··311
13.4 在Power Pivot中使用層次結構 ··· 346
13.5 創建KPI關鍵績效指標 ··· 348
13.6 使用連結回表方式創建計算表 350

第 14 章 利用Power Map創建 3D地圖視覺化數據 ··· 356

第 15 章 Power BI Desktop入門 · 357
15.1 安裝Power BI Desktop應用程式 357
15.2 向Power BI Desktop導入數據 ··· 358
15.2.1 使用Power BI Desktop 獲取網頁上的匯率數據 ·358
15.2.2 導入包含Power Query和Power Pivot的工作簿 ·361
15.3 在Power BI中創建關係 ·· 364
15.3.1 在模型視圖中創建表之間的關係 ·364
15.3.2 使用【管理關係】功能來創建和管理關係 365
15.3.3 表與表之間的關係類型和作用 ·365
15.4 在Power BI中創建視覺化報告 ··· 367
15.4.1 報表視圖的結構與功能 367
15.4.2 在報告中創建視覺化圖表 367
15.5 在Power BI中設置交互層次結構 370
15.5.1 視覺化之間的交互效果 370
15.5.2 調整視覺化圖表的交互效果 370
15.6 在Power BI中導入自訂視覺化圖表 · 372
15.6.1 使用內置商店導入獲取自訂視覺化
效果 372
15.6.2 導入自訂視覺化文件 373
15.7 Power BI報告的常見分享形式 ··· 374
15.7.1 將Power BI 報告匯出為PDF分享 ···374
15.7.2 使用Power BI 雲服務分享報告 375

第 16 章 數據透視表與VBA 377
16.1 數據透視表物件模型 ·· 377
16.2 在Excel功能區中顯示【開發工具】選項卡 · 379
16.3 如何快速獲得創建數據透視表的代碼 ·· 380
16.4 自動生成數據透視表 ·· 386
16.4.1 使用PivotTableWizard方法創建數據透視表 ·386
16.4.2 利用PivotCache物件創建數據透視表 ·389
16.5 在代碼中引用數據透視表 ··· 392
16.6 更改數據透視表中預設的欄位匯總方式 ··· 394
16.7 調整值欄位的位置 · 398
16.8 清理數據透視表字段下拉清單 400
16.9 利用數據透視表快速匯總多個工作簿 ·· 403
16.10 數據透視表緩存 ··· 406
16.10.1 顯示數據透視表的緩存索引和記憶體使用量 ···406
16.10.2 合併數據透視表緩存 ·408
16.11 保護數據透視表 ··· 409
16.11.1 限制數據透視表字段的下拉選擇 ···409
16.11.2 限制更改數據透視表佈局 ··410
16.11.3 禁用數據透視表的顯示明細數據功能 ···412
16.12 選定工作表中的數據透視表區域 414
16.13 多個數據透視表聯動 415
16.14 快速列印據數據透視表 · 418
16.14.1 快速分項列印單篩選欄位數據透視表 ···418
16.14.2 快速分項列印多篩選欄位數據透視表 ···419
16.15 快速創建PowerPivot和添加切片器 ··· 422

第 17 章 智慧數據分析視覺化看板 ·· 427
17.1 銷售管道分析看板 · 427
17.2 利用數據透視圖進行銷售綜合分析 ·· 434
17.3 利用PowerPivot for Excel綜合分析數據 · 439
17.4 人事數據看板 ··· 446
17.5 指定店鋪指定商品促銷達成跟進範本 ·· 452
17.6 店鋪銷售分析看板 · 459
17.7 公司預算達成率跟蹤看板 ··· 466
17.8 切片器控制動態TOP X零售分析 · 473

第 18 章 數據透視表常見問題答疑解惑 · 481
18.1 為什麼創建數據透視表時提示“數據透視表字段名無效”？ ··481
18.2 為什麼在早期版本的Excl中無法正常顯示
Excel 2019 版本創建的數據透視表？ ·· 481
18.3 為什麼Excel 2019 中的切片器、日程表等
功能不可用，按鈕呈灰色狀態？ ·· 482
18.4 如何對數據透視表值區域的數據實現篩選？ ·· 483
18.5 為什麼刷新數據透視表時會提示“數據
透視表不能覆蓋另一個數據透視表”？ 485
18.6 如何將值區域中的數據匯總專案由縱向變為橫向顯示？ ·485
18.7 如何像引用普通表格數據一樣引用數據透視表中的數據？ ···486
18.8 如何像在Excel 2003 版本中一樣使用
數據透視表的欄位拖曳功能？ 487
18.9 如何使數據透視表中的月份欄位顯示1 月~12 月的順序 ·488
18.10 為什麼【計算項】功能命令為灰色不可用狀態？ 489
18.11 為什麼批量選中分類匯總項時失效？ 490
18.12 如何利用現有數據透視表得到原始數據源？ 490
18.13 如何在數據透視表中插入批註？ ··· 491
18.14 為什麼在數據透視表中無法顯示插入的圖片批註？ · 491
18.15 如何不顯示數據透視表中的零值？ 492
18.16 同一數據源創建的多個數據透視表，
一個應用組合，其他也同步應用，如何解決？ 493
18.17 如何快速在普通數據透視表中應用非重複計數的值匯總方式？· 494
18.18 如何在數據透視表的左側顯示列欄位的分類匯總？ · 496
18.19 如何解決數據透視表添加計算欄位後總計值出現的錯誤？ 497
18.20 如何對數據透視表實施快速鑽取？ 498

第 19 章 數據透視表列印技術 ·· 500
19.1 設置數據透視表的列印標題 500
19.1.1 利用數據透視表選項設置列印表頭 ·500
19.1.2 “在每一列印頁上重複行標籤”的應用 502
19.1.3 利用頁面設置指定列印標題 503
19.2 為數據透視表的每一分類專案分頁列印 ··· 504
19.3 根據報表篩選欄位數據項快速分頁列印 ··· 505

附錄 ··· 507
附錄A Excel常用SQL語句解釋 · 507
A.1 SELECT查詢 ···507
A.2 聯集查詢 ··513
A.3 多表查詢 ··514
A.4 內部聯接 ··515
A.5 左外部聯結和右外部聯結 ·516
A.6 子查詢 ·516
A.7 常用函數 ··517
附錄B 高效辦公必備工具——Excel易用寶 521

Excel Home是微軟線上社區聯盟成員，華語Excel資源網站，擁有大量原創技術文章、視頻教程、增益集及範本。Excel Home是一個學習氛圍濃厚的技術交流社區，中國大陸及中國港臺各行各業身懷絕技的Office高手都彙聚于此，目前已有三百多萬辦公人士選擇成為它的註冊會員。Excel Home已精心編寫並出版Office領域的圖書60餘種，截至2020年春節，圖書銷量累計達200多萬冊。