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

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