Excel 2013數(shù)據(jù)透視表應(yīng)用大全

第1 章　創(chuàng)建透視表 ......................................1
1.1　什么是數(shù)據(jù)透視表 .............................. 1
1.2　數(shù)據(jù)透視表的數(shù)據(jù)源 .......................... 1
1.3　自己動(dòng)手創(chuàng)建第一個(gè)數(shù)據(jù)透視表 ........ 2
1.4　使用推薦的數(shù)據(jù)透視表 ....................... 4
1.5　數(shù)據(jù)透視表用途 ................................. 5
1.6　何時(shí)使用數(shù)據(jù)透視表分析數(shù)據(jù) ............ 5
1.7　數(shù)據(jù)透視表的結(jié)構(gòu) .............................. 5
1.8　數(shù)據(jù)透視表字段列表 .......................... 6
1.9　數(shù)據(jù)透視表中的術(shù)語 .......................... 9
1.10　數(shù)據(jù)透視表工具 ............................. 10
第2 章　為數(shù)據(jù)透視表準(zhǔn)備好數(shù)據(jù) ............13
2.1　數(shù)據(jù)管理規(guī)范 ................................... 13
2.2　對(duì)不規(guī)范數(shù)據(jù)表格的整理技巧 .......... 17
第3 章　改變數(shù)據(jù)透視表的布局 ................31
3.1　改變數(shù)據(jù)透視表的整體布局 ............. 31
3.2　數(shù)據(jù)透視表篩選器區(qū)域的使用 .......... 33
3.3　啟用經(jīng)典數(shù)據(jù)透視表布局 ................. 37
3.4　整理數(shù)據(jù)透視表字段 ........................ 38
3.5　改變數(shù)據(jù)透視表的報(bào)告格式 ............. 46
3.6　清除已刪除數(shù)據(jù)的標(biāo)題項(xiàng) ................. 54
3.7　數(shù)據(jù)透視表的復(fù)制和移動(dòng) ................. 56
3.8　影子數(shù)據(jù)透視表 ............................... 59
3.9　調(diào)整受保護(hù)工作表中的數(shù)據(jù)透視表布局 ................................................. 61
3.10　獲取數(shù)據(jù)透視表的數(shù)據(jù)源信息 ........ 63
第4 章　刷新數(shù)據(jù)透視表 ............................66
4.1　手動(dòng)刷新數(shù)據(jù)透視表 ........................ 66
4.2　打開文件時(shí)自動(dòng)刷新 ........................ 66
4.3　刷新鏈接在一起的數(shù)據(jù)透視表 .......... 67
4.4　刷新引用外部數(shù)據(jù)的數(shù)據(jù)透視表 ...... 67
4.5　定時(shí)刷新 .......................................... 69
4.6　使用VBA 代碼設(shè)置自動(dòng)刷新 ............ 69
4.7　全部刷新數(shù)據(jù)透視表 ........................ 71
4.8　共享數(shù)據(jù)透視表緩存 ........................ 72
4.9　推遲布局更新 ................................... 74
4.10　數(shù)據(jù)透視表的刷新注意事項(xiàng) ........... 74
第5 章　數(shù)據(jù)透視表的格式設(shè)置 ................76
5.1　設(shè)置數(shù)據(jù)透視表的格式 ..................... 76
5.2　數(shù)據(jù)透視表與條件格式 ..................... 98
第6 章　在數(shù)據(jù)透視表中排序和篩選 ......107
6.1　數(shù)據(jù)透視表排序 ............................. 107
6.2　數(shù)據(jù)透視表篩選 ............................. 121
第7 章　數(shù)據(jù)透視表的切片器 ..................131
7.1　什么是切片器 ................................. 132
7.2　在數(shù)據(jù)透視表中插入切片器 ........... 132
7.3　篩選多個(gè)字段項(xiàng) ............................. 134
7.4　更改切片器的前后顯示順序 ........... 138
7.5　切片器字段項(xiàng)排序 .......................... 139
7.6　設(shè)置切片器樣式 ............................. 142
7.7　更改切片器的名稱 .......................... 147
7.8　隱藏切片器 ..................................... 148
7.9　刪除切片器 ..................................... 149
第8 章　數(shù)據(jù)透視表的日程表 ..................150
8.1　插入日程表 ..................................... 150
8.2　利用日程表按季度進(jìn)行數(shù)據(jù)篩選 ...... 151
8.3　為數(shù)據(jù)透視表插入兩個(gè)時(shí)間級(jí)別的日程表 ............................................ 152
8.4　日程表的格式化 ............................. 154
8.5　每個(gè)字段允許多個(gè)篩選 ................... 154
8.6　共享日程表實(shí)現(xiàn)多個(gè)數(shù)據(jù)透視表聯(lián)動(dòng) ................................................ 155
8.7　日程表與創(chuàng)建組的區(qū)別 ................... 157
第9 章　數(shù)據(jù)透視表的項(xiàng)目組合 ..............158
9.1　手動(dòng)組合數(shù)據(jù)透視表內(nèi)的數(shù)據(jù)項(xiàng) ...... 158
9.2　自動(dòng)組合數(shù)據(jù)透視表內(nèi)的數(shù)值型數(shù)據(jù)項(xiàng) ............................................ 160
9.3　取消項(xiàng)目的組合 ............................. 164
9.4　在項(xiàng)目組合中運(yùn)用函數(shù)輔助處理 ...... 167
9.5　影響自動(dòng)組合的因素 ...................... 169
第10 章　在數(shù)據(jù)透視表中執(zhí)行計(jì)算 .......172
10.1　對(duì)同一字段使用多種匯總方式 ...... 172
10.2　更改數(shù)據(jù)透視表默認(rèn)的字段匯總方式 ............................................. 175
10.3　非重復(fù)計(jì)數(shù)的值匯總方式 ............. 181
10.4　自定義數(shù)據(jù)透視表的值顯示方式 ............................................. 182
10.5　在數(shù)據(jù)透視表中使用計(jì)算字段和計(jì)算項(xiàng) .......................................... 200
第11 章　數(shù)據(jù)透視表函數(shù)綜合應(yīng)用 .......226
11.1　初識(shí)數(shù)據(jù)透視表函數(shù) .................... 226
11.2　數(shù)據(jù)透視表函數(shù)的語法結(jié)構(gòu) ......... 229
11.3　自動(dòng)匯總方法下靜態(tài)獲取數(shù)據(jù)透視表計(jì)算數(shù)值 ...................................... 231
11.4　自動(dòng)匯總方法下動(dòng)態(tài)獲取數(shù)據(jù)透視表數(shù)據(jù) ............................................. 234
11.5　自定義匯總方法下獲取數(shù)據(jù)透視表數(shù)據(jù) ............................................. 239
11.6　數(shù)據(jù)透視表函數(shù)與其他函數(shù)聯(lián)合使用 ............................................. 240
11.7　同時(shí)引用多個(gè)字段進(jìn)行計(jì)算 ......... 242
11.8　從多個(gè)數(shù)據(jù)透視表中獲取數(shù)據(jù) ...... 243
11.9　數(shù)據(jù)透視表函數(shù)綜合應(yīng)用示例 ...... 246
11.10　使用數(shù)據(jù)透視表函數(shù)應(yīng)注意的問題 ........................................... 253
第12 章　創(chuàng)建動(dòng)態(tài)數(shù)據(jù)透視表 ...............256
12.1　定義名稱法創(chuàng)建動(dòng)態(tài)數(shù)據(jù)透視表 .... 256
12.2　使用“表格”功能創(chuàng)建動(dòng)態(tài)數(shù)據(jù)透視表 .......................................... 259
12.3　新字段自動(dòng)進(jìn)入數(shù)據(jù)透視表布局 ............................................. 261
第13 章　創(chuàng)建復(fù)合范圍的數(shù)據(jù)透視表 .....264
13.1　創(chuàng)建多重合并計(jì)算數(shù)據(jù)區(qū)域的數(shù)據(jù)透視表 .......................................... 264
13.2　對(duì)不同工作簿中的數(shù)據(jù)列表進(jìn)行合并計(jì)算 ...................................... 275
13.3　透視不規(guī)則數(shù)據(jù)源 ........................ 278
13.4　創(chuàng)建動(dòng)態(tài)多重合并計(jì)算數(shù)據(jù)區(qū)域的數(shù)據(jù)透視表 ................................... 282
13.5　創(chuàng)建多重合并計(jì)算數(shù)據(jù)區(qū)域數(shù)據(jù)透視表行字段的限制 .................... 287
13.6　解決創(chuàng)建多重合并計(jì)算數(shù)據(jù)區(qū)域數(shù)據(jù)透視表的限制的方法 .................... 288
13.7　利用多重合并計(jì)算數(shù)據(jù)區(qū)域?qū)⒍S數(shù)據(jù)列表轉(zhuǎn)換為一維數(shù)據(jù)列表 ...... 290
第14 章　使用Microsoft Query 數(shù)據(jù)查詢創(chuàng)建透視表 ...............................293
14.1　Microsoft Query 查詢單個(gè)數(shù)據(jù)列表創(chuàng)建數(shù)據(jù)透視表 ........................... 293
14.2　Microsoft Query 查詢多個(gè)數(shù)據(jù)列表創(chuàng)建數(shù)據(jù)透視表 ........................... 298
14.3　數(shù)據(jù)源移動(dòng)后如何修改Microsoft Query 查詢中的連接 .................... 306
第15 章　通過導(dǎo)入外部數(shù)據(jù)源“編輯OLEDB 查詢”創(chuàng)建數(shù)據(jù)透視表 .....309
15.1　導(dǎo)入單張數(shù)據(jù)列表創(chuàng)建數(shù)據(jù)透視表 .......................................... 309
15.2　導(dǎo)入多張數(shù)據(jù)列表創(chuàng)建數(shù)據(jù)透視表 .......................................... 316
15.3　導(dǎo)入不重復(fù)記錄創(chuàng)建數(shù)據(jù)透視表 .......................................... 319
15.4　導(dǎo)入數(shù)據(jù)關(guān)聯(lián)列表創(chuàng)建數(shù)據(jù)透視表 .......................................... 322
15.5　修改OLE DB 查詢路徑 ................ 326
15.6　Excel 2013 OLE DB 查詢的限制 .... 328
第16 章　利用多樣的數(shù)據(jù)源創(chuàng)建數(shù)據(jù)透視表 .......................................329
16.1　使用文本數(shù)據(jù)源創(chuàng)建數(shù)據(jù)透視表 .......................................... 329
16.2　使用Microsoft Access 數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)透視表 ................................... 335
16.3　使用SQL Server 數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)透視表 .......................................... 337
16.4　使用SQL Server Analysis Services OLAP 創(chuàng)建數(shù)據(jù)透視表 ................. 339
16.5　使用OData 數(shù)據(jù)饋送創(chuàng)建數(shù)據(jù)透視表 .......................................... 350
第17 章　Power BI 與數(shù)據(jù)透視表 ...........354
17.1　模型數(shù)據(jù)透視表 ........................... 354
17.2　PowerPivot 與數(shù)據(jù)透視表 ............ 366
17.3　在PowerPivot 中使用DAX 語言 .... 375
17.4　在PowerPivot 中使用層次結(jié)構(gòu) ............................................. 396
17.5　創(chuàng)建KPI 關(guān)鍵績(jī)效指標(biāo)報(bào)告 ......... 400
17.6　使用Power View 分析數(shù)據(jù) .......... 403
17.7　利用Power Query 快速管理數(shù)據(jù) ............................................. 415
17.8　利用Power Map 創(chuàng)建3D 地圖可視化數(shù)據(jù) ................................... 424
第18 章　數(shù)據(jù)透視表與VBA...................426
18.1　數(shù)據(jù)透視表對(duì)象模型 .................... 426
18.2　在Excel 2013 功能區(qū)中顯示【開發(fā)工具】選項(xiàng)卡 ............................... 428
18.3　如何快速獲得創(chuàng)建數(shù)據(jù)透視表的代碼 ............................................. 429
18.4　自動(dòng)生成數(shù)據(jù)透視表 .................... 435
18.5　在代碼中引用數(shù)據(jù)透視表 ............. 441
18.6　更改數(shù)據(jù)透視表中默認(rèn)的字段匯總方式 ...................................... 444
18.7　調(diào)整值字段的位置 ........................ 448
18.8　清理數(shù)據(jù)透視表字段下拉列表 ...... 450
18.9　利用數(shù)據(jù)透視表快速匯總多個(gè)工作簿 .......................................... 453
18.10　數(shù)據(jù)透視表緩存 ......................... 456
18.11　保護(hù)數(shù)據(jù)透視表 ......................... 460
18.12　選定工作表中的數(shù)據(jù)透視表區(qū)域 ........................................... 466
18.13　多個(gè)數(shù)據(jù)透視表聯(lián)動(dòng) .................. 468
18.14　快速打印數(shù)據(jù)透視表 .................. 471
第19 章　發(fā)布數(shù)據(jù)透視表 .......................477
19.1　將數(shù)據(jù)透視表發(fā)布為網(wǎng)頁(yè) ............. 477
19.2　將數(shù)據(jù)透視表保存到Web ............ 480
第20 章　用圖形展示數(shù)據(jù)透視表數(shù)據(jù) .....488
20.1　創(chuàng)建數(shù)據(jù)透視圖 ........................... 488
20.2　移動(dòng)數(shù)據(jù)透視圖 ........................... 494
20.3　數(shù)據(jù)透視圖的結(jié)構(gòu)布局 ................. 497
20.4　編輯美化數(shù)據(jù)透視圖 .................... 503
20.5　快速改變數(shù)據(jù)透視圖的設(shè)置 ......... 510
20.6　刷新和刪除數(shù)據(jù)透視圖 ................. 513
20.7　將數(shù)據(jù)透視圖轉(zhuǎn)為靜態(tài)圖表 ......... 515
20.8　數(shù)據(jù)透視圖的使用限制 ................. 517
20.9　數(shù)據(jù)透視圖的應(yīng)用技巧 ................. 518
20.10　使用切片器控制數(shù)據(jù)透視圖 ....... 527
20.11　在數(shù)據(jù)透視表中插入迷你圖 ....... 531
第21 章　數(shù)據(jù)透視表打印技術(shù) ...............534
21.1　設(shè)置數(shù)據(jù)透視表的打印標(biāo)題 ......... 534
21.2　為數(shù)據(jù)透視表每一分類項(xiàng)目分頁(yè)打印 ...................................... 538
21.3　根據(jù)報(bào)表篩選字段數(shù)據(jù)項(xiàng)快速分頁(yè)打印 ...................................... 540
第22 章　數(shù)據(jù)透視表技術(shù)綜合運(yùn)用 .......542
22.1　利用再透視法根據(jù)平均進(jìn)價(jià)和平均售價(jià)計(jì)算產(chǎn)品利潤(rùn) ........................ 542
22.2　按星期和小時(shí)雙維度透視查看淘寶女包上下架分布 ........................... 547
22.3　使數(shù)據(jù)透視表的多級(jí)切片器選項(xiàng)根據(jù)上級(jí)切片器選擇做相應(yīng)更新 ......... 552
22.4　按年份、季度、月份、旬分層級(jí)查看產(chǎn)品銷售額 ................................... 555
22.5　根據(jù)打卡明細(xì)提取員工的最早、最晚打卡時(shí)間 ............................... 558
22.6　將數(shù)據(jù)按明細(xì)歸類并放置在列字段下方排列 ...................................... 562
22.7　將位于同列的出入庫(kù)數(shù)據(jù)按產(chǎn)品拆分為出庫(kù)和入庫(kù)分列顯示 ......... 567
22.8　根據(jù)多工作表數(shù)據(jù)統(tǒng)計(jì)進(jìn)銷存且支持自適應(yīng)路徑及文件名更改 ............. 570
22.9　利用方案生成數(shù)據(jù)透視表盈虧平衡分析報(bào)告 ............................... 575
22.10　制作帶有本頁(yè)小計(jì)和累計(jì)的數(shù)據(jù)表 ........................................ 579
22.11　利用數(shù)據(jù)透視表進(jìn)行銷售綜合分析 ........................................... 584
22.12　利用PowerPivot for Excel 綜合分析數(shù)據(jù) .................................... 588
第23 章　數(shù)據(jù)透視表常見問題答疑解惑 ..........................................596
23.1　為什么創(chuàng)建數(shù)據(jù)透視表時(shí)提示數(shù)據(jù)透視表字段名無效” ......... 596
23.2　為什么在早期版本的Excel 中無法正常顯示Excel 2013 版本創(chuàng)建的數(shù)據(jù)透視表 ................................... 597
23.3　為什么在Excel 2013 中切片器、日程表等功能不可用，按鈕呈灰色狀態(tài) ...................................... 598
23.4　為什么刷新數(shù)據(jù)透視表時(shí)會(huì)提示“數(shù)據(jù)透視表不能覆蓋另一個(gè)數(shù)據(jù)透視表” ............................... 599
23.5　如何將值區(qū)域中的數(shù)據(jù)匯總項(xiàng)目由縱向變?yōu)闄M向顯示 ........................ 599
23.6　如何像引用普通表格數(shù)據(jù)一樣引用數(shù)據(jù)透視表中的數(shù)據(jù) .................... 600
23.7　如何像在Excel 2003 版本中一樣使用數(shù)據(jù)透視表的字段拖曳功能 ............................................. 601
23.8　為什么添加計(jì)算項(xiàng)功能命令為灰色不可用狀態(tài) ................................... 602
23.9　為什么批量選中分類匯總項(xiàng)時(shí)失效 ............................................. 603
23.10　如何利用現(xiàn)有數(shù)據(jù)透視表得到原始數(shù)據(jù)源 ........................................ 604
23.11　為什么數(shù)據(jù)透視表所在工作表更改名稱后再打開，數(shù)據(jù)透視表無法正常刷新 ............................. 604
23.12　如何在數(shù)據(jù)透視表中插入批注 .... 606
23.13　為什么無法顯示在數(shù)據(jù)透視表中插入的圖片批注 ......................... 607
23.14　如何不顯示數(shù)據(jù)透視表中的零值 ........................................... 607
23.15　數(shù)據(jù)透視表進(jìn)行組合后再?gòu)?fù)制一個(gè)數(shù)據(jù)透視表，如何解決一個(gè)數(shù)據(jù)透視　　　　　 表取消分組，另一個(gè)數(shù)據(jù)透視表也會(huì)取消分組的問題 ......................... 608
23.16　如何在數(shù)據(jù)透視表的左側(cè)顯示列字段的分類匯總 ......................... 609
23.17　如何解決數(shù)據(jù)透視表添加計(jì)算字段后總計(jì)值出現(xiàn)的錯(cuò)誤 .................. 612
23.18　如何對(duì)數(shù)據(jù)透視表實(shí)施快速鉆取 ........................................... 613
附錄　Excel 常用SQL 語句解釋 ..............614
1.1　SELECT 查詢 ................................. 614
1.2　聯(lián)合查詢 ........................................ 620
1.3　多表查詢 ........................................ 621
1.4　內(nèi)部連接 ........................................ 623
1.5　左外部連接和右外部連接 ............... 624
1.6　子查詢 ............................................ 624
1.7　常用函數(shù) ........................................ 625