Excel 2013數據透視表大全

《Excel 2013數據透視表大全》是2015年出版的圖書，作者是黃朝陽。

基本介紹

• 書名：Excel 2013數據透視表大全
• 作者：黃朝陽
• 譯者：黃朝陽
• 出版時間：2015-07

內容簡介

本書共15章，包含251個疑難案例，每個疑難案例均給出一個或多個解決方案，並融入一個或多個知識點講解，貼切實際，深入淺出；在解決問題的同時，引導讀者學習數據透視表套用的原理和知識擴展。

本書適合各個層次的Excel用戶，既可以作為初學者的入門教材，又可作為中、高級用戶的參考手冊。書中案例多數來源於各行各業中的實際案例，讀者可以直接套用於實際工作中。閱讀本書後，可以幫助讀者快速提高數據處理能力和工作效率。

前言

自 序 尋，是一種尺度。古時八尺為一尋，王安石詩云“飛來峰上千尋塔，聞說雞鳴見日升”，用以形容飛來峰上塔之極高。 尋，是一種技巧。現今的網路為自學者提供了非常巨大的資源平台，但缺乏技巧的查詢和求助，往往“眾里尋他千百度”卻難有“驀然回首”的頓悟。而善用關鍵字搜尋，則常可使人體會到“燈火闌珊”之美景。 尋，是一種追求。網路上活躍著這么一群人，他們曾經也是懵懂的菜鳥。

目 錄

第1章 認識數據透視表 1

第1節 數據透視表技巧 1

疑難1 如何創建一個簡單數據透視表 1

※ 插入數據透視表 ※ 2

※ 利用嚮導創建數據透視表 ※ 3

※ 數據透視表的發展歷程 ※ 4

疑難2 如何用含有合併單元格的數據來創建數據透視表 4

※ 培養良好的數據錄入習慣 ※ 6

疑難3 如何使用推薦的數據透視表 7

※ 使用推薦的數據透視表 ※ 8

疑難4 如何重命名數據透視表 8

※ 重命名數據透視表 ※ 9

疑難5 如何移動數據透視表位置 9

※ 移動數據透視表 ※ 10

疑難6 如何複製數據透視表 10

※ 複製數據透視表 ※ 11

疑難7 如何刪除數據透視表 12

※ 刪除數據透視表 ※ 12

疑難8 如何創建非共享快取的數據透視表 12

※ 創建非共享快取的數據透視表 ※ 13

※ 共享快取與非共享快取的區別 ※ 15

第2節 數據透視表結構 15

疑難9 如何匯總各銷售經理各區域銷售總額 16

※ 創建多條件的數據透視表 ※ 16

※ “數據透視表欄位”窗格說明 ※ 17

疑難10 如何顯示/隱藏數據透視表欄位窗格 17

※ 顯示/隱藏數據透視表欄位窗格 ※ 18

疑難11 如何隱藏數據透視表中行欄位的“+/-”按鈕 19

※ 顯示/隱藏“+/-”按鈕 ※ 19

疑難12 如何在數據透視表欄位窗格中顯示更多欄位 20

※ 改變數據透視表欄位窗格布局 ※ 21

疑難13 如何隱藏數據透視表的欄位標題和篩選按鈕 22

※ 隱藏欄位標題和篩選按鈕 ※ 22

疑難14 如何升序排列欄位列表中的欄位 24

疑難15 如何快速選取所有的標籤並標註為灰色底紋 25

疑難16 如何快速選定數據透視表的匯總行並添加灰色底紋 27

※ 快速選定數據透視表元素 ※ 28

※ 選定行標籤所在項父級的所有數據 ※ 29

疑難17 如何得到數據透視表中某匯總行的明細數據 29

※ 顯示明細數據 ※ 30

第3節 練習與思考 31

第2章 布局和列印 32

第1節 拖動欄位 32

疑難18 如何在數據透視表中使用滑鼠拖放 32

※ 設定經典數據透視表布局 ※ 33

疑難19 如何將值欄位從行區域移到列區域 34

※ 拖動值欄位 ※ 34

疑難20 如何將行欄位中的項目拖動到指定行顯示 36

※ 改變行欄位項目顯示順序 ※ 36

疑難21 如何快速拖動改變行欄位的排列順序 37

※ 改變行欄位排列順序 ※ 37

疑難22 如何刪除數據透視表中的某個欄位 38

※ 移除欄位 ※ 39

第2節 調整布局 40

疑難23 如何隱藏數據透視表欄位的分類匯總 40

※ 隱藏所有欄位的分類匯總 ※ 40

※ 在底部/頂部顯示分類匯總 ※ 41

※ 添加或隱藏某個欄位的分類匯總 ※ 41

疑難24 如何為一個欄位添加多種分類匯總方式 43

※ 一個欄位的多種分類匯總 ※ 43

疑難25 如何禁用數據透視表的總計行/列 44

※ 禁用總計行和總計列 ※ 45

※ 刪除總計行或總計列 ※ 46

※ 總計行與列總計、總計列與行總計的關係 ※ 46

疑難26 如何在數據透視表頂部顯示列總計數據 46

※ 在頂部顯示列總計 ※ 47

疑難27 如何以表格形式顯示數據透視表 48

※ 以表格形式顯示報表 ※ 48

※ 報表布局的三種顯示形式及其區別 ※ 50

疑難28 如何調整壓縮形式顯示下的縮進字元數 51

※ 設定行標籤字元縮進量 ※ 51

疑難29 如何重複顯示行欄位的項目標籤 52

※ 重複所有項目標籤 ※ 52

※ 重複活動欄位項目標籤 ※ 53

※ 重複所有項目標籤與重複活動欄位項目標籤的區別 ※ 53

疑難30 如何在數據透視表項目間插入空行 54

※ 在每個項目後插入空行 ※ 54

※ 在指定項目標籤後插入空行 ※ 54

※ 刪除每個項目後的空行 ※ 56

疑難31 如何在組的頂部顯示分類匯總 56

※ 在組的頂部顯示分類匯總 ※ 56

疑難32 如何在數據透視表中使用合併單元格標誌 57

※ 合併居中顯示項目標籤 ※ 57

疑難33 如何水平並排顯示報表篩選區域的欄位 58

※ 水平並排顯示報表篩選區域欄位 ※ 59

※ 設定每行/列報表篩選欄位的數量 ※ 59

疑難34 如何設定手動更新數據透視表的布局 60

※ 推遲布局更新 ※ 61

※ “推遲布局更新”的影響 ※ 61

疑難35 如何在受保護工作表中改變數據透視表布局 61

※ 在受保護工作表中允許使用數據透視表和數據透視圖 ※ 62

第3節 列印 63

疑難36 如何設定重複列印標題行 63

※ 設定列印標題行 ※ 63

※ 設定列印頂端標題行 ※ 64

※ 數據透視表選項與頁面設定在設定列印標題行時的區別 ※ 64

疑難37 如何按公司名稱分頁列印銷售業績匯總表 65

※ 行欄位分類項目分頁列印 ※ 65

疑難38 如何按地區分頁列印銷售人員的業績報表 66

※ 按頁欄位數據項分頁列印 ※ 67

第4節 練習與思考 68

第3章 設定數據透視表格式 69

第1節 套用樣式 69

疑難39 如何為數據透視表套用樣式 69

※ 自動套用數據透視表樣式 ※ 70

※ 套用表格格式 ※ 70

※ 啟用/關閉實時預覽 ※ 71

※ 套用並清除格式與套用並保留格式的區別 ※ 71

疑難40 如何自動設定數據透視表的框線 72

※ 設定數據透視表框線 ※ 72

疑難41 如何設定鑲邊行的數據透視表樣式 73

※ 鑲邊行與鑲邊列 ※ 74

疑難42 如何新建數據透視表樣式 74

※ 新建數據透視表樣式 ※ 74

※ 自定義數據透視表樣式中格式的優先順序 ※ 76

※ 行副標題與第一列的區別 ※ 76

※ 第一行條紋、第二行條紋與鑲邊行的關係 ※ 76

※ 清除數據透視表元素的格式設定 ※ 77

疑難43 如何修改數據透視表樣式 78

※ 修改數據透視表樣式 ※ 78

※ 刪除自定義數據透視表樣式 ※ 80

第2節 自定義格式 80

疑難44 如何隱藏數據透視表中的錯誤值 81

※ 設定錯誤值顯示為空 ※ 81

疑難45 如何將數據透視表中的空單元格顯示為“-” 82

※ 設定空單元格顯示為“-” ※ 82

※ 取消空單元格自定義顯示 ※ 84

※ 典型的自定義單元格格式 ※ 84

疑難46 如何去掉數據透視表中的“（空白）”數據 85

※ 查找替換去除“（空白）” ※ 85

※ 重命名去除“(空白)” ※ 86

※ 自動更正去除“（空白）” ※ 86

疑難47 如何使欄位標題不顯示“求和項：”等統計標識 87

※ 查找替換去除“求和項：” ※ 88

※ 重命名去除“求和項：” ※ 88

※ 計數項與求和項 ※ 88

疑難48 如何將指定項下所有數據標記為紅色 89

※ 突出顯示指定項所有數據 ※ 90

疑難49 如何將測量平均值保留三位小數 90

※ 四捨五入保留兩位小數 ※ 91

※ 單元格格式與數字格式的區別 ※ 92

疑難50 如何將不達標數據顯示為紅色“不達標” 92

※ 標記不達標數據 ※ 93

※ 在單元格格式中可以設定哪些顏色 ※ 93

第3節 條件格式 93

疑難51 如何套用數據條直觀顯示數據 94

※ 通過“格式按鈕”為數據透視表套用數據條 ※ 94

※ “格式選項”按鈕的作用 ※ 95

※ 通過“條件格式”為數據透視表套用數據條 ※ 95

疑難52 如何突出顯示最高及最低的整行數據 97

※ 突出顯示最大值/最小值所在數據行 ※ 97

※ 以不同顏色突出顯示最大值與最小值 ※ 98

疑難53 如何突出顯示銷量增長最多的銷售員 99

※ 突出顯示銷量增長最多的銷售員 ※ 99

疑難54 如何按班級交替填充背景色 100

※ 按班級交替填充背景色 ※ 101

※ 條紋尺寸與條件格式 ※ 101

疑難55 如何將百分成績顯示為等第成績 102

※ 按等級級別顯示成績 ※ 102

※ 條件格式的優先權 ※ 104

※ 調整條件格式的優先權 ※ 104

第4節 練習與思考 104

第4章 數據透視表排序 105

第1節 自動排序 105

疑難56 如何設定數據透視表的行欄位降序排序 105

※ 按行欄位項目降序排序 ※ 106

※ 數據透視表中行欄位排序的幾種方法 ※ 106

疑難57 如何按分類匯總值降序排序 107

※ 按分類匯總升序排序 ※ 107

疑難58 如何設定多個值欄位降序排序 108

※ 多關鍵字排序 ※ 109

※ 多關鍵字排序的先後順序 ※ 110

※ “每次更新報表時自動排序”複選框的作用 ※ 110

疑難59 如何按多個行欄位排序 110

※ 多個行欄位按值欄位排序 ※ 111

第2節 自定義排序 112

疑難60 如何手動排序行欄位的項 112

※ 手動排序欄位項目 ※ 113

疑難61 如何按筆劃升序排序行欄位項目 114

※ 按筆劃升序排序 ※ 114

※ 筆劃排序規則 ※ 114

疑難62 如何對數據透視表中的局部數據進行排序 115

※ 局部排序 ※ 115

疑難63 如何按行降序排列不合格分類數量 116

※ 按行從左到右排序 ※ 116

疑難64 如何按照學歷高低排序 117

※ 添加自定義序列排序 ※ 118

※ 啟用排序時使用自定義列表 ※ 119

第3節 練習與思考 119

第5章 數據透視表篩選 120

第1節 自動篩選 120

疑難65 如何篩選成績大於或等於60的學生名單 120

※ 值篩選及格名單 ※ 121

※ “值篩選”的篩選條件 ※ 121

※ 在數據透視表欄位列表任務窗格中設定值篩選 ※ 122

※ 清除“值篩選”的幾種方法 ※ 122

※ 為什麼數據透視表有些新的篩選功能不可用 ※ 123

疑難66 如何獲得高於平均值的銷量數據列表 124

※ 自動篩選高於平均值數據 ※ 124

※ “值篩選”與數值區域執行自動篩選的區別 ※ 125

疑難67 如何篩選不同產地單價最高的2種商品 125

※ 篩選前N個最大值 ※ 125

※ 篩選的排位規則 ※ 126

※ 項、百分比、求和 ※ 126

疑難68 如何篩選包含“桃”字的商品信息 127

※ 通過標籤篩選包含指定字元的數據 ※ 127

※ 通過搜尋篩選器篩選包含指定字元的數據 ※ 128

※ 標籤篩選與搜尋篩選器篩選的區別 ※ 129

※ 篩選條件中的通配符 ※ 129

疑難69 如何篩選本周的銷量數據 129

※ 通過日期篩選本周數據 ※ 130

疑難70 如何篩選銷售額大於10 000的指定商品信息 131

※ 同一欄位的多重篩選 ※ 131

※ 每個欄位允許多個篩選 ※ 133

※ 搜尋篩選器中的4種篩選方式 ※ 133

疑難71 如何為同一欄位設定多個篩選條件 133

※ 同一欄位的疊加篩選 ※ 133

※ 同一欄位執行多次篩選 ※ 134

疑難72 如何對行列欄位同時執行篩選 134

※ 多欄位並列條件篩選 ※ 135

疑難73 如何按省份分頁顯示銷售數據 135

※ 顯示報表篩選頁 ※ 136

疑難74 如何在篩選狀態下包含數據源中的新增信息 137

※ 在手動篩選中包含新項目 ※ 137

疑難75 如何在欄位篩選狀態下顯示無數據的項目 138

※ 顯示無數據項目 ※ 139

疑難76 如何隱藏欄位下拉列表中多餘的項目 140

※ 隱藏欄位下拉列表中多餘項目 ※ 140

第2節 切片器篩選 141

疑難77 如何通過切片器篩選分公司的銷售報表 141

※ 插入切片器 ※ 141

※ 刪除切片器 ※ 142

※ 隱藏切片器 ※ 143

※ 調整切片器大小 ※ 143

疑難78 如何設定切片器的字型格式 144

※ 修改切片器樣式 ※ 145

※ 如何新建切片器樣式 ※ 147

※ 切片器元素中各部分含義 ※ 147

疑難79 如何隱藏切片器的標題 149

※ 不顯示切片器頁眉 ※ 149

疑難80 如何通過切片器篩選多項 150

※ 切片器篩選多個項目 ※ 150

※ 切片器與篩選器的優缺點 ※ 150

※ 在切片器中選擇項目的方法 ※ 151

疑難81 如何通過切片器篩選兩個數據透視表 151

※ 通過切片器設定報表連線 ※ 152

※ 通過數據透視表設定篩選器連線 ※ 153

疑難82 如何用切片器實現多維度分析 153

※ 多個切片器聯動篩選 ※ 154

疑難83 如何在被保護的工作表中使用切片器 154

※ 在受保護的工作表中使用切片器 ※ 155

疑難84 如何在切片器中隱藏數據源中刪除的項目 155

※ 切片器不保留數據源刪除的項目 ※ 156

※ 隱藏沒有數據的項 ※ 156

疑難85 如何使交叉篩選的切片器不顯示無效選項 157

※ 隱藏切片器的無效選項 ※ 159

疑難86 為什麼刪除切片器後還保留其篩選結果 159

※ 刪除切片器同時清除數據透視表篩選 ※ 160

疑難87 如何通過日程表篩選指定年份的數據 161

※ 插入日程表 ※ 161

※ 日程表與自動分組功能區別 ※ 162

※ 格式化日程表 ※ 163

第3節 練習與思考 163

第6章 數據透視表項目分組 164

第1節 自動分組 164

疑難88 如何按年齡分組統計員工人數 164

※ 等距步長自動組合數值型數據項 ※ 165

疑難89 如何按小時分組匯總行車數量 165

※ 按小時自動分組 ※ 166

疑難90 如何按周分組匯總產量 166

※ 按周自動分組 ※ 167

※ 改變周起始日 ※ 168

疑難91 如何按“旬”分組匯總數據 168

※ 按旬自動分組 ※ 168

※ 不等距步長自動分組 ※ 169

疑難92 如何按年、季度、月份分組匯總銷售額 169

※ 按年、季、月自動分組 ※ 169

疑難93 為什麼提示“選定區域不能分組” 170

※ 選定區域不能分組的原因 ※ 173

第2節 手動組合 174

疑難94 如何製作賬齡分析表 174

※ 不等距步長手動組合數值型數據項 ※ 174

※ 組選擇與組欄位的區別 ※ 175

疑難95 如何按半年匯總產量數據 175

※ 按半年分組匯總 ※ 176

疑難96 如何按學年匯總校運動會報名人數 177

※ 手動組合文本型數據項 ※ 177

疑難97 如何按行政區域匯總銷量 178

※ 按關鍵字分組匯總 ※ 178

疑難98 如何查看美洲入境遊人數的分布 180

※ 全局取消組合與局部取消組合 ※ 181

第3節 練習與思考 181

第7章 更新數據源 182

第1節 刷新 182

疑難99 如何手動刷新數據透視表 182

※ 手動刷新數據透視表 ※ 183

疑難100 如何在打開工作簿時自動刷新數據透視表 184

※ 打開檔案自動刷新數據 ※ 184

疑難101 如何在更新數據透視表時保持原格式和列寬不變 185

※ 刷新時保留原有格式和列寬 ※ 185

疑難102 如何批量刷新數據透視表 186

※ 批量刷新數據透視表 ※ 186

※ “數據透視表工具”的“全部刷新”與“數據”選項卡的“全部刷新”

有何區別※ 187

疑難103 如何定時刷新數據透視表 187

※ 定時刷新數據透視表 ※ 188

疑難104 為什麼刷新數據透視表時數據會丟失 189

疑難105 如何清除欄位下拉列表中已刪除數據源的項目 190

※ 不保留從數據源中刪除的項目 ※ 191

疑難106 如何更改現有數據透視表的數據源 192

※ 更改數據透視表數據源 ※ 192

第2節 動態數據源 193

疑難107 如何利用定義名稱創建動態數據透視表 193

※ 定義名稱法創建動態數據透視表 ※ 193

※OFFSET函式語法※ 195

※ COUNTA函式語法 ※ 196

疑難108 如何利用表功能創建動態數據透視表 196

※ 利用表功能創建動態數據透視表 ※ 196

疑難109 如何通過編輯OLE DB查詢創建動態數據透視表 198

※ 編輯OLE DB查詢創建動態數據透視表※ 198

第8章 數據透視表的計算 201

第1節 值匯總方式 201

疑難110 如何按賬齡匯總客戶的實際應收款 201

※ 條件求和匯總 ※ 201

※ 數據透視表對欄位類型的自動識別 ※ 204

※ 數據透視表的計算原理 ※ 204

疑難111 如何按性別和學歷統計各部門人數 204

※ 條件計數 ※ 205

※ 值欄位的匯總方式 ※ 206

疑難112 如何統計各班級平均分與最高分 206

※ 條件極值均值 ※ 207

※ 對同一欄位使用多種匯總方式 ※ 208

※ 改變值欄位匯總方式的5種方法 ※ 208

疑難113 為什麼數據透視表的匯總方式默認為計數 208

※ 更改默認匯總方式 ※ 209

第2節 值顯示方式 211

疑難114 如何統計各營業部電器的銷售額占比 211

※ 總計的百分比 ※ 211

※ 15種值顯示方式及其功能 ※ 212

疑難115 如何統計全球手機品牌市場占有率 213

※ 列匯總的百分比 ※ 213

疑難116 如何統計各班學生男女比例 214

※ 行匯總的百分比 ※ 214

疑難117 如何計算定基動態比率 215

※ 百分比 ※ 216

疑難118 如何統計銷售額占父行匯總的百分比 217

※ 父行匯總的百分比 ※ 217

※ 父行匯總百分比與列匯總百分比的異同 ※ 218

疑難119 如何統計各車型的銷量占分類匯總的百分比 218

※ 父級匯總的百分比 ※ 219

疑難120 如何統計服裝銷量與上個月的差異 220

※ 差異 ※ 220

疑難121 如何統計商品房成交均價環比增長率 222

※ 差異百分比 ※ 222

疑難122 如何按月份累計發貨金額 223

※ 按某一欄位匯總 ※ 224

疑難123 如何統計等第成績人數累計百分比 225

※ 按某一欄位匯總的百分比 ※ 225

疑難124 如何按地區統計平板電視的銷量排名 226

※ 升序排列 ※ 227

疑難125 如何按報考職位統計考生的成績排名 228

※降序排列※ 228

疑難126 如何統計各商品在不同區域的相對重要性 229

※指數※ 230

第3節 計算欄位與計算項 231

疑難127 如何為成績表添加總分計算欄位 231

※插入計算欄位※ 231

※ 二維表的總計列 ※ 232

疑難128如何修改數據透視表中的計算欄位公式 232

※ 修改計算欄位公式 ※ 232

※ 刪除計算欄位 ※ 234

疑難129 如何按月統計各時段用電量占比 234

※ 二維表的行4總計占比 ※ 234

疑難130 如何判斷應收金額與已收金額是否平賬 235

※ 數據透視表中使用公式返回#VALUE!錯誤值的原因 ※ 237

※ 計算欄位公式可以使用哪些函式 ※ 237

疑難131 如何計算各商品的加權平均單價 238

※ 計算加權平均單價 ※ 238

疑難132 如何計算蔬菜銷售的毛利 239

※ 再透視法 ※ 239

※ 計算欄位的局限性 ※ 241

疑難133 如何計算商品售罄率 242

※ 插入計算項 ※ 242

※ 計算欄位與計算項的區別 ※ 244

※ 修改計算項 ※ 244

疑難134 如何統計銷售額的年增長率 244

※ 隱藏計算項的無效數據行 ※ 246

※ 計算項的局限性 ※ 246

疑難135 如何統計貨運每日延誤率及總延誤率 246

※ 計算項總計不正確問題 ※ 249

※ 計算項的求解次序 ※ 249

第4節 數據透視表函式 249

疑難136 如何根據多列條件查詢物品單價 249

※ 動態查詢數據透視表數據 ※ 250

※ GETPIVOTDATA函式的3種語法結構 ※ 250

※ 禁用自動創建GETPIVOTDATA 公式 ※ 251

※ GETPIVOTDATA函式特點 ※ 252

疑難137 如何獲取自定義分類匯總數據 252

※ 查詢自定義分類匯總數據 ※ 252

疑難138 如何根據現有數據透視表重置報表格式 253

※ 量身定做數據報表 ※ 253

第5節 練習與思考 254

第9章 多重合併區域數據透視表 255

第1節 多重合併計算 255

疑難139 如何創建單個自定義頁欄位的數據透視表 255

※ 單頁欄位多表合併數據 ※ 256

※ 使用“多重合併計算數據區域”創建數據透視表的工作原理 ※ 257

疑難140 如何創建多個自定義頁欄位的數據透視表 258

※ 多頁欄位多表合併數據 ※ 259

疑難141 如何比較兩張表格相同商品的差異 260

※ 多數據合併比較差異 ※ 260

疑難142 如何合併不同工作簿的新增客戶報表數據 262

※ 多工作簿合併數據 ※ 262

疑難143 如何按店鋪合併不同的指標數據 264

※ 多表合併不同指標數據 ※ 264

疑難144 如何統計不同員工調研結果 265

※ 多表合併不同指標數據 ※ 265

※ 使用“多重合併計算數據區域”創建不具有頁欄位的數據透視表 ※ 266

疑難145 如何按行總計百分比顯示二維表監測數據占比 267

※ 解決二維表行匯總百分比無法顯示的問題 ※ 267

疑難146 如何將二維錶轉換為一維數據列表 269

※ 二維錶轉換為一維表 ※ 269

疑難147 如何創建動態多重合併計算數據區域數據透視表 270

※ 基於“表”創建的數據透視表 ※ 271

※ 基於定義名稱創建動態數據透視表 ※ 272

疑難148 如何創建多個行欄位的多重合併區域數據透視表 272

第2節 練習與思考 274

第10章 獲取外部數據創建數據透視表 275

第1節 使用多維數據集創建數據透視表 275

疑難149 如何使用OLAP多維數據集創建數據透視表 275

※ 使用OLAP多維數據集創建數據透視表 ※ 276

※ 多維數據集模型 ※ 277

※ OLAP與非OLAP源數據之間的功能差異 ※ 278

※ 維度的展開與摺疊 ※ 279

※ 向上/向下鑽取 ※ 280

※ 快速瀏覽 ※ 281

疑難150 如何創建脫機多維數據集 281

※ 創建脫機多維數據集 ※ 282

疑難151 如何利用脫機多維數據集創建數據透視表 284

※ 利用脫機多維數據集創建數據透視表 ※ 284

疑難152 如何利用多維數據集函式創建報表 286

※ 將數據透視錶轉換為公式 ※ 286

※ 利用多維數據集函式創建自定義報表 ※ 287

※ 多維數據集函式語法 ※ 288

※ 多維數據集函式列表 ※ 289

第2節 使用其他外部數據創建數據透視表 289

疑難153 如何從Access資料庫導入數據創建數據透視表 289

※ 導入Access數據表創建數據透視表 ※ 290

疑難154 如何利用文本檔案創建數據透視表 291

※ 利用文本檔案創建動態數據透視表 ※ 292

※ schema.ini檔案詳解 ※ 295

疑難155 如何連線SQL Server資料庫創建數據透視表 296

※ 連線SQL Server資料庫創建數據透視表 ※ 296

疑難156 如何斷開數據透視表的外部數據源連線 298

※ 斷開數據透視表與外部數據源的連線 ※ 298

第11章 數據透視表與SQL 300

第1節 SQL語法基礎 300

疑難157 如何查詢導入外部數據 300

※ 打開OLE DB視窗編寫SQL語句 ※ 301

※ 什麼是SELECT子句 ※ 302

※ 如何查詢單列或多列記錄 ※ 302

疑難158 如何在SQL裡面使用單元格區域 303

※ 選定指定單元格區域的數據作為查詢數據源表 ※ 304

疑難159 如何使用列別名把英文欄位轉換為中文欄位 305

※ 如何使用列別名 ※ 307

疑難160 如何查詢上網流量超過60M的記錄 308

※ WHERE條件查詢子句 ※ 309

※ 帶有括弧的欄位名表示方式 ※ 309

※ 大於、小於等運算 ※ 310

疑難161 如何查詢等級在B至E之間的記錄 310

※ 模糊匹配模式中字元區間的表示 ※ 312

疑難162 如何查詢所有姓“林”的員工的記錄 312

※ 運用LIKE進行模糊匹配 ※ 314

※ 模糊匹配中不確定長度的字元表示 ※ 314

※ 模糊匹配模式中長度為1的字元表示 ※ 314

疑難163 如何計算學生總成績並按總成績降序排列 314

※ 欄位的四則運算 ※ 316

※ 利用ORDERBY子句對記錄進行排序 ※ 316

※ SQL查詢機制步驟詳解 ※ 316

疑難164 如何查詢滿足申請租賃型經適房條件的人員 316

※ 同時滿足多個條件的查詢 ※ 318

※ 滿足多個條件之一的查詢 ※ 318

疑難165 如何分離籍貫欄位中的城市和區縣 319

※ 拆分字元串 ※ 320

※ INSTR函式的使用 ※ 320

※ LEFT函式的使用 ※ 320

※ RIGHT函式的使用 ※ 321

疑難166 如何通過多條件分組統計數據 321

※ AVG函式在SQL語句中的使用 ※ 322

※ SUM函式在SQL語句中的使用 ※ 323

※ GROUP BY子句的使用 ※ 323

第2節 利用OLE DB結合SQL創建數據透視表 323

疑難167 如何通過OLE DB來創建動態的數據透視表 324

※ 使用SQL語句創建動態的數據透視表 ※ 324

疑難168 如何匯總同一個工作簿中多個工作表的數據 326

※ 同一工作簿中多工作表的數據匯總 ※ 326

※ UNION ALL的使用 ※ 327

※ UNION ALL與UNION的區別 ※ 328

疑難169 如何匯總同一個工作簿中不同區域的數據 328

※ 同一個工作簿內不同工作表中不同區域的數據匯總 ※ 330

疑難170 如何匯總不同工作簿中不同工作表的數據 330

※ 不同工作簿中不同工作表的數據匯總 ※ 332

疑難171 如何匯總列數不等的多個工作表數據 333

※ 匯總列數不等的多個工作表數據 ※ 334

※ 0與Null的區別 ※ 335

疑難172 如何通過關聯不同工作表的商品代碼匯總銷售金額 335

※ 工作表名稱自定義別名 ※ 336

※ 多對一連線兩個工作表 ※ 337

疑難173 如何連線兩個以上工作表來創建數據透視表 337

※ 關聯兩個以上的工作表創建數據透視表 ※ 338

疑難174 如何創建進銷存報表的數據透視表 339

※ 創建進銷存的動態數據透視表 ※ 340

疑難175 如何用數據透視表創建日曆 341

※ 獲取日期的年、月、日、星期 ※ 342

※ DatePart函式套用 ※ 343

疑難176 如何將包含空格的借方和貸方金額相減求餘額 343

※ IsNull函式的使用 ※ 344

※ IIF函式的使用 ※ 345

疑難177 如何修改OLE DB查詢路徑 345

第3節 結合Microsoft Query創建數據透視表 347

疑難178 如何利用Microsoft Query匯總單個數據表 347

※ 利用Microsoft Query匯總單個數據表 ※ 348

※ 如何解決數據源無可見表的問題 ※ 351

疑難179 如何利用Microsoft Query統計不重複數據個數 352

※ 利用Microsoft Query統計不重複值個數 ※ 353

※ SELECT DISTINCT語句篩選不重複記錄 ※ 356

疑難180 如何利用Microsoft Query匯總多個數據表 356

※ 利用Microsoft Query匯總多個工作表 ※ 357

疑難181 如何利用Microsoft Query關聯匯總多個工作表 361

※ 左連線原理說明 ※ 365

※ 右連線原理說明 ※ 365

※ 內連線原理說明 ※ 366

疑難182 如何修改“Microsoft Query”查詢的連線路徑 367

※ 修改“Microsoft Query”查詢的連線路徑 ※ 367

第4節 練習與思考 369

第12章 數據透視表與數據模型 370

第1節 使用內置的數據模型創建數據透視表 370

疑難183 如何使用數據模型創建銷售額匯總報表 370

※ 使用數據模型創建數據透視表 ※ 371

※ 數據模型及其特點 ※ 371

疑難184 如何統計諮詢師接診顧客的不重複數量 372

※ 非重複計數 ※ 372

疑難185 如何讓分類匯總值不隨篩選而改變 373

※ 匯總值包含篩選項 ※ 375

疑難186 如何在左側顯示分類匯總 376

※ 創建集 ※ 376

疑難187 如何匯總比較計畫與實際銷售額 378

※ 創建一對多的表關係 ※ 378

※ 關係 ※ 381

※ 列和鍵 ※ 382

※ 表之間的多個關係 ※ 382

※ 表關係的必要條件 ※ 382

※ 表關係的局限性 ※ 382

疑難188 如何導入多個表創建數據透視表 383

※ 導入多個表 ※ 383

※ 管理關係 ※ 385

疑難189 如何連線Windows Azure Marketplace源創建數據透視表 385

※ 連線Windows Azure Marketplace源創建數據透視表 ※ 386

第2節 使用PowerPivot創建數據模型 389

疑難190 如何載入PowerPivot For Excel 2013 390

※ 載入PowerPivot For Excel 2013 ※ 390

※ 什麼是PowerPivot For Excel 2013 ※ 391

※ PowerPivot與Excel的區別 ※ 391

疑難191 如何使用連結表向PowerPivot添加數據 392

※ 創建連結表數據模型 ※ 392

※ 連結表的特點 ※ 393

疑難192 如何複製並貼上數據到PowerPivot 393

※ 複製貼上數據創建PowerPivot表 ※ 394

※ 貼上數據的限制 ※ 395

※ PowerPivot表與Excel工作表之間的差異 ※ 395

疑難193 如何將Access資料庫的表導入PowerPivot 396

※ 使用“表導入嚮導”導入外部數據 ※ 396

疑難194 如何導入滿足指定條件的數據 398

※ 在導入過程添加篩選器 ※ 398

疑難195 如何在PowerPivot中的關係圖視圖創建關係 401

※ 在PowerPivot中的關係圖視圖創建關係 ※ 401

疑難196 如何在PowerPivot中創建層次結構 402

※ 添加計算列 ※ 403

※ 創建層次結構 ※ 404

※ 計算列 ※ 405

※ 什麼是DAX表達式 ※ 406

※ DAX公式輸入小技巧 ※ 406

第3節 在PowerPivot中使用數據透視表 407

疑難197 如何在PowerPivot創建數據透視表匯總銷售額 407

※ 在PowerPivot創建數據透視表 ※ 407

※ PowerPivot數據透視表選項 ※ 408

疑難198 如何根據多列條件匯總比較多表數據 409

※ 添加計算列建立表關係 ※ 409

疑難199 如何計算加權毛利率 412

※ 計算加權毛利率 ※ 413

※ PowerPivot計算欄位 ※ 415

※ 編輯/刪除計算欄位 ※ 416

※ 計算列與計算欄位的區別 ※ 417

疑難200 如何按部門及應發工資分段統計員工人數 417

※ 條件計數 ※ 417

疑難201 如何解決父級匯總的百分比不正確問題 419

※ 引用分類匯總值 ※ 419

疑難202 如何計算加權平均單價 421

※ 計算加權平均單價 ※ 421

疑難203 如何在數據透視表的值區域顯示文本 423

※ 值區域顯示文本 ※ 423

疑難204 如何篩選各商品的最近且最低單價 425

※ 篩選最近且最低單價 ※ 426

※ 邏輯運算符 ※ 428

疑難205 如何統計銷售額的同比和環比增長率 428

※ 同比和環比增長率 ※ 428

※ 時間智慧型函式 ※ 431

疑難206 記賬統計收支餘額表 431

※ 年、月、日收支餘額表 ※ 432

※ 期末餘額 ※ 434

※ 期初餘額 ※ 435

疑難207 如何使用KPI分析銷售數據 435

※ 創建KPI ※ 435

※ PowerPivot 中的關鍵績效指標 (KPI) ※ 438

第4節 練習與思考 439

第13章 數據透視表與VBA 440

第1節 VBA語法基礎 440

疑難208 如何顯示“開發工具”選項卡 440

※ 顯示開發工具選項卡 ※ 441

疑難209 為什麼無法運行檔案中的代碼 441

※ 啟用宏 ※ 442

※ 設定為受信任文檔 ※ 443

※ 在出現安全警告時啟用一次宏 ※ 443

※ 宏的4個安全等級 ※ 444

疑難210 如何執行過程 444

※ 運行宏 ※ 444

※ 運行代碼的多種方式 ※ 445

疑難211 如何在立即視窗中獲得代碼的運算結果 447

※ 查看變數運行結果 ※ 447

※ VBA中幾種視窗的作用 ※ 448

疑難212 如何統計多工作表中的報考人數 449

※ For…Next循環語句 ※ 449

※ For Each…Next循環語句 ※ 451

※ For…Next與For Each…Next區別 ※ 451

※ Do…Loop循環語句 ※ 452

疑難213 如何獲得創建數據透視表的代碼 453

※ 錄製宏 ※ 453

※ 數據透視表常用代碼解析 ※ 455

※ AddDateField與AddFields方法 ※ 456

※ 簡化代碼的方法 ※ 456

※ 數據透視表對象模型 ※ 457

第2節 使用VBA創建數據透視表 458

疑難214 如何基於本地數據源創建數據透視表 458

※ PivotTableWizard方法 ※ 459

疑難215 如何使用VBA創建動態數據透視表 460

※ 行列自動擴展的動態數據透視表 ※ 460

疑難216 如何創建默認匯總方式為求和的數據透視表 461

※ 默認匯總方式為求和的數據透視表 ※ 462

疑難217 如何快速匯總多個工作表 464

※ 多工作表合併計算 ※ 464

※ 二維數組創建多重合併計算區域 ※ 466

疑難218 如何創建多行欄位的多重合併計算數據透視表 466

※ 多行欄位多表合併計算 ※ 467

第3節 使用VBA運算元據透視表 470

疑難219 如何選中數據透視表的元素 470

※ 套用PivotSelect方法選取數據透視表中元素 ※ 471

疑難220 如何隱藏數據透視表中指定的欄位 473

※ 隱藏欄位 ※ 474

疑難221 如何用代碼刷新數據透視表 475

※ 自動刷新數據透視表 ※ 476

疑難222 如何刷新受保護工作表中的數據透視表 478

※ 刷新受保護的數據透視表 ※ 478

疑難223 如何批量更改數據透視表的匯總方式 479

※ 批量更改匯總方式 ※ 479

疑難224 如何在數據透視表中插入切片器 480

※ 插入切片器 ※ 480

※ 獲取切片器中選中的項目 ※ 482

疑難225 如何設定數據透視表不共享快取 483

※ 設定多個數據透視表不共享快取 ※ 484

※ 設定多個數據透視表共享數據快取 ※ 485

※ 新建數據透視表設定非共享快取 ※ 485

疑難226 如何更改數據透視表數值區域的數字 486

※ 啟用值區域編輯功能 ※ 486

疑難227 如何在數據透視表中插入日程表 487

※ 插入日程表 ※ 487

第4節 練習與思考 488

第14章 數據透視圖 489

第1節 創建數據透視圖 489

疑難228 如何同時創建數據透視表和透視圖 489

※ 同時創建數據透視表與透視圖 ※ 490

※ 數據透視表與數據透視圖的關係 ※ 491

疑難229 如何根據數據透視表創建透視圖 493

※ 為現有數據透視表創建透視圖 ※ 494

※ 數據透視圖無法創建的圖表類型 ※ 497

※ 創建數據透視圖的方法比較 ※ 497

疑難230 如何使用PowerPivot創建數據透視圖 498

疑難231 如何通過VBA創建數據透視圖 500

※ VBA創建數據透視圖 ※ 501

※ 數據透視圖中數值欄位的名稱列表 ※ 503

疑難232 如何改變數據透視圖的圖表類型 504

※ 更改數據透視圖的圖表類型 ※ 505

疑難233 如何更改數據透視圖的顯示位置 506

※ 移動數據透視圖 ※ 506

※ 一次性調整數據透視圖的字號 ※ 508

第2節 布局和樣式 508

疑難234 如何隱藏數據透視圖中的欄位按鈕 508

※ 隱藏所有欄位按鈕 ※ 509

※ 隱藏指定欄位按鈕 ※ 509

疑難235 如何切換數據透視圖中數據行/列顯示 510

※ 切換數據行/列顯示 ※ 511

疑難236 如何調整報表篩選欄位至透視圖的圖例欄位 513

※ 調整欄位布局 ※ 513

疑難237 如何使數據透視圖示題與頁欄位同步 515

※ 圖表標題與頁欄位同步 ※ 516

疑難238 如何為數據點添加數據標註 517

※ 設定數據標註 ※ 517

※ 強化的數據標籤功能 ※ 518

※ 更改數據標籤形狀※ 518

第3節 典型套用案例 519

疑難239 如何通過Power View 創建互動式圖表 519

※ 通過Power View創建互動式圖表 ※ 519

※ 第一次使用時可能需要的解決辦法 ※ 523

※ 在互動圖表中設定排序 ※ 524

※ 互動式圖表解釋 ※ 524

疑難240 如何通過切片器顯示各月銷售額 524

※ 通過切片器按月份顯示銷售額 ※ 524

疑難241 如何製作半圓形餅圖 525

※ 數據透視圖中顯示總計 ※ 526

疑難242 如何創建排列圖 527

※ 排列圖的原理及套用 ※ 529

※ 如何保存為模板 ※ 530

疑難243 如何分車型比較不同廠家的銷量 531

※ 分車型比較不同廠家的銷量 ※ 532

※ Power View篩選器的用法 ※ 534

※ 水平比較不同車型的銷量數據 ※ 535

※ 調整螢幕中序列圖顯示數量 ※ 535

第4節 練習與思考 536

第15章 綜合套用實踐 537

第1節 匯總同期利潤對比表 537

疑難244 如何準備數據源 538

※ 利用名稱定義動態數據源 ※ 539

疑難245 如何創建同期利潤對比報表 540

※ 多重合併計算利潤表數據 ※ 540

疑難246 如何調整同期利潤對比報表的項目順序 543

※ 手動排序同期利潤對比報表的項目 ※ 543

疑難247 如何計算利潤率 545

※ 插入利潤率計算項 ※ 545

第2節 費用差異分析報表 547

疑難248 如何按照年月匯總各項費用 548

※ 按年月匯總各項費用 ※ 548

疑難249 如何組合費用科目 550

※ 手工組合費用科目 ※ 551

疑難250 如何進行差異分析計算 552

※ 插入差異和增長比計算項 ※ 553

※ 利用公式計算總計 ※ 554

第3節 收發存匯總報表 555

疑難251 如何利用SQL創建收發存報表 556

※ 利用SQL創建收發存報表 ※ 556