內容簡介
本書共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
