從原始數(shù)據(jù)到分析報(bào)告：Excel數(shù)據(jù)透視表高效達(dá)人養(yǎng)成記

目 錄Contents
PART 01 數(shù)據(jù)透視表篇
CHAPTER 01 制作數(shù)據(jù)透視表的準(zhǔn)備工作
1.1 不規(guī)范表格結(jié)構(gòu)的重新搭建..........................................................................3
1.1.1 把多行標(biāo)題的數(shù)據(jù)區(qū)域轉(zhuǎn)換為數(shù)據(jù)清單（案例1-1）......................3
1.1.2 將二維表格轉(zhuǎn)換為數(shù)據(jù)清單（案例1-2）........................................6
1.1.3 將多列文字描述轉(zhuǎn)換為一個(gè)列表清單（案例1-3）.........................8
1.2 數(shù)據(jù)分列........................................................................................................9
1.2.1 利用分隔符對(duì)數(shù)據(jù)分列（案例1-4、案例1-5）.............................10
1.2.2 利用固定寬度對(duì)數(shù)據(jù)分列（案例1-6）..........................................11
1.2.3 利用文本函數(shù)對(duì)數(shù)據(jù)分列（案例1-7）..........................................12
1.2.4 利用全角字符和半角字符特征對(duì)數(shù)據(jù)分列（案例1-8）................13
1.2.5 利用文本和數(shù)字特征對(duì)數(shù)據(jù)分列（案例1-9）...............................14
1.2.6 利用關(guān)鍵詞對(duì)數(shù)據(jù)分列（案例1-10）............................................14
1.3 清除數(shù)據(jù)中的垃圾.......................................................................................16
1.3.1 快速刪除文本中的空格...................................................................16
1.3.2 快速刪除數(shù)據(jù)中眼睛看不見的特殊字符（案例1-11）..................16
1.4 修改非法日期和非法時(shí)間...........................................................................17
1.4.1 使用分列工具快速修改非法日期（案例1-12）.............................17
1.4.2 使用函數(shù)處理非法時(shí)間（案例1-13）............................................18
1.4.3 考勤數(shù)據(jù)的快速處理（案例1-14）................................................18
1.5 快速填充空單元格.......................................................................................19
1.5.1 快速填充上一行數(shù)據(jù)（案例1-15）................................................20
1.5.2 快速填充下一行數(shù)據(jù)（案例1-16）................................................21
1.5.3 快速往空單元格填充數(shù)字0 ............................................................22
1.5.4 快速取消合并單元格并填充數(shù)據(jù)....................................................23
CHAPTER 02 制作數(shù)據(jù)透視表的基本方法
2.1 以一個(gè)表格數(shù)據(jù)創(chuàng)建數(shù)據(jù)透視表................................................................25
2.1.1 以一個(gè)固定的數(shù)據(jù)區(qū)域制作數(shù)據(jù)透視表（案例2-1）....................25
2.1.2 以一個(gè)變動(dòng)的數(shù)據(jù)區(qū)域制作數(shù)據(jù)透視表.........................................27
2.2 以多個(gè)二維表格數(shù)據(jù)創(chuàng)建數(shù)據(jù)透視表.........................................................28
2.2.1 創(chuàng)建單頁(yè)字段的多重合并計(jì)算數(shù)據(jù)區(qū)域透視表（案例2-2）........29
2.2.2 創(chuàng)建多頁(yè)字段的多重合并計(jì)算數(shù)據(jù)區(qū)域透視表（案例2-3）........33
2.3 以多個(gè)多維表格數(shù)據(jù)創(chuàng)建數(shù)據(jù)透視表.........................................................37
2.3.1 利用現(xiàn)有連接+SQL數(shù)據(jù)查詢匯總多個(gè)多維工作表（案例2-4）.37
2.3.2 小知識(shí)：關(guān)于SQL語(yǔ)句..................................................................40
2.3.3 利用現(xiàn)有連接+SQL數(shù)據(jù)查詢創(chuàng)建數(shù)據(jù)透視表應(yīng)注意的問題.......42
2.4 以多個(gè)關(guān)聯(lián)工作表數(shù)據(jù)創(chuàng)建數(shù)據(jù)透視表.....................................................43
2.4.1 使用Query 工具（案例2-5）.........................................................43
2.4.2 注意事項(xiàng).........................................................................................47
2.5 以工作表部分?jǐn)?shù)據(jù)創(chuàng)建數(shù)據(jù)透視表（案例2-6）.......................................47
2.6 在不打開工作簿的情況下創(chuàng)建數(shù)據(jù)透視表.................................................50
2.6.1 利用現(xiàn)有連接工具創(chuàng)建數(shù)據(jù)透視表................................................50
2.6.2 利用Query 工具創(chuàng)建數(shù)據(jù)透視表....................................................51
2.7 以數(shù)據(jù)庫(kù)數(shù)據(jù)創(chuàng)建數(shù)據(jù)透視表....................................................................51
2.7.1 以Access數(shù)據(jù)庫(kù)的一個(gè)數(shù)據(jù)表制作數(shù)據(jù)透視表（案例2-7）......51
2.7.2 以Access數(shù)據(jù)庫(kù)的多個(gè)數(shù)據(jù)表制作數(shù)據(jù)透視表（案例2-8）......53
2.7.3 以Access數(shù)據(jù)庫(kù)的部分?jǐn)?shù)據(jù)表制作數(shù)據(jù)透視表...........................54
2.8 以文本文件數(shù)據(jù)直接創(chuàng)建數(shù)據(jù)透視表（案例2-9）...................................54
2.9 查看/更改數(shù)據(jù)源.........................................................................................58
2.9.1 以一個(gè)Excel表格制作的數(shù)據(jù)透視表.............................................58
2.9.2 以多個(gè)Excel表格制作的數(shù)據(jù)透視表（現(xiàn)有連接方法）...............58
2.9.3 以數(shù)據(jù)庫(kù)或文本文件制作的數(shù)據(jù)透視表（Query方法）...............58
CHAPTER 03 布局?jǐn)?shù)據(jù)透視表
3.1 數(shù)據(jù)透視表字段窗格...................................................................................60
3.2 數(shù)據(jù)透視表布局的幾種方法........................................................................61
3.3 恢復(fù)經(jīng)典的數(shù)據(jù)透視表布局方式................................................................61
3.4 數(shù)據(jù)透視表工具..........................................................................................62
3.4.1 “分析”選項(xiàng)卡中各功能組介紹....................................................63
3.4.2 “設(shè)計(jì)”選項(xiàng)卡中各功能組介紹....................................................64
CHAPTER 04 數(shù)據(jù)透視表的設(shè)計(jì)與美化
4.1 設(shè)計(jì)透視表的樣式.......................................................................................66
4.1.1 套用數(shù)據(jù)透視表樣式（案例4-1）..................................................66
4.1.2 細(xì)雕數(shù)據(jù)透視表樣式.......................................................................67
4.1.3 刪除默認(rèn)的數(shù)據(jù)透視表樣式............................................................67
4.2 設(shè)計(jì)透視表的布局.......................................................................................68
4.2.1 以壓縮形式顯示..............................................................................68
4.2.2 以大綱形式顯示..............................................................................68
4.2.3 以表格形式顯示..............................................................................69
4.2.4 重復(fù)、不重復(fù)項(xiàng)目標(biāo)簽（案例4-2）..............................................70
4.2.5 在每個(gè)項(xiàng)目后插入、刪除空行........................................................71
4.2.6 顯示、隱藏報(bào)表的行總計(jì)和列總計(jì)................................................72
4.3 設(shè)置透視表的選項(xiàng).......................................................................................72
4.3.1 合并字段的項(xiàng)目標(biāo)簽單元格............................................................72
4.3.2 處理透視表中的錯(cuò)誤值（案例4-3）..............................................73
4.4 設(shè)置字段......................................................................................................74
4.4.1 修改值字段名稱..............................................................................74
4.4.2 設(shè)置值字段的匯總依據(jù)...................................................................74
4.4.3 設(shè)置值字段的數(shù)字格式...................................................................74
4.4.4 設(shè)置值字段的條件格式（案例4-4）..............................................75
4.4.5 顯示、隱藏字段的分類匯總............................................................76
4.4.6 對(duì)分類字段項(xiàng)目進(jìn)行自動(dòng)排序........................................................77
4.4.7 對(duì)分類字段項(xiàng)目進(jìn)行手動(dòng)排序（案例4-5）...................................77
4.4.8 對(duì)分類字段項(xiàng)目進(jìn)行自定義排序（案例4-6）...............................78
4.4.9 顯示沒有數(shù)據(jù)的分類字段項(xiàng)目（案例4-7）...................................80
4.4.10 顯示、隱藏分類字段標(biāo)題.............................................................81
4.5 數(shù)據(jù)透視表的其他設(shè)置...............................................................................81
4.5.1 刷新數(shù)據(jù)透視表..............................................................................81
4.5.2 顯示、隱藏折疊/展開按鈕..............................................................82
4.5.3 顯示、隱藏?cái)?shù)據(jù)透視表字段窗格....................................................82
4.5.4 選擇數(shù)據(jù)透視表..............................................................................82
4.5.5 復(fù)制數(shù)據(jù)透視表..............................................................................83
4.5.6 移動(dòng)數(shù)據(jù)透視表..............................................................................83
4.5.7 清空數(shù)據(jù)透視表..............................................................................83
4.5.8 將數(shù)據(jù)透視表轉(zhuǎn)換為普通報(bào)表........................................................83
CHAPTER 05 自定義計(jì)算字段和計(jì)算項(xiàng)
5.1 自定義計(jì)算字段..........................................................................................85
5.1.1 添加計(jì)算字段（案例5-1）.............................................................85
5.1.2 修改計(jì)算字段..................................................................................86
5.1.3 刪除計(jì)算字段..................................................................................86
5.1.4 列示出所有自定義字段信息............................................................87
5.1.5 可以改變計(jì)算字段的匯總方式嗎....................................................87
5.2 自定義計(jì)算項(xiàng)..............................................................................................87
5.2.1 添加計(jì)算項(xiàng)（案例5-2）.................................................................87
5.2.2 自定義計(jì)算項(xiàng)的幾個(gè)重要說明........................................................89
5.2.3 修改自定義計(jì)算項(xiàng)...........................................................................89
5.2.4 刪除自定義計(jì)算項(xiàng)...........................................................................89
5.2.5 列示出所有自定義計(jì)算項(xiàng)信息........................................................90
5.3 添加計(jì)算字段和計(jì)算項(xiàng)的幾個(gè)注意問題.....................................................90
5.3.1 什么時(shí)候添加計(jì)算字段，什么時(shí)候添加計(jì)算項(xiàng)..............................90
5.3.2 同時(shí)添加計(jì)算字段和計(jì)算項(xiàng)的幾個(gè)問題（案例5-3）....................90
5.3.3 哪些情況下不能添加自定義計(jì)算字段和計(jì)算項(xiàng)..............................91
5.3.4 自定義計(jì)算字段是否能使用工作簿函數(shù).........................................91
5.3.5 自定義計(jì)算字段是否能使用單元格引用和名稱..............................91
5.4 綜合應(yīng)用案例..............................................................................................92
5.4.1 預(yù)算與實(shí)際執(zhí)行情況的對(duì)比分析（案例5-4）...............................92
5.4.2 動(dòng)態(tài)進(jìn)銷存管理（案例5-5）.........................................................93
CHAPTER 06 通過設(shè)置字段顯示方式分析數(shù)據(jù)
6.1 占比分析......................................................................................................97