從邏輯思路到實戰應用，輕鬆做Excel數據分析（簡體書）

本書從Excel的基本理念開始，讓每個讀者都有一個對Excel的正確認識，然後是Excel常用工具的靈活使用。本書共分為五部分：數據采集與匯總；數據查詢與提取；數據分析與挖掘；分析結果可視化等。

韓小良，國內實戰型Excel培訓講師和應用解決方案專家。出版了40餘部關於Excel企業管理應用方面的實用書籍。從事Excel研究及應用近20年，有著豐富的Excel高級應用培訓和諮詢實戰經驗，具有豐富的實際管理經驗和極強的Excel應用開發能力；已經為數千家國內外大中型企業進行了Excel培訓和應用方案解決諮詢服務，在業界享有很高的評價，深得客戶認可。韓小良老師從管理和決策的視角闡釋和應用Excel，注重實際問題的解決思路，為企業提供解決方案。通過案例教學，緊貼實際工作，今天學習，明天應用，真正授人以漁！授課詼諧幽默，用幽默通俗的語言把枯燥的Excel技能展現出來，課堂氣氛好，互動性強。於峰：實戰型財務培訓師、諮詢師，曾任某財務軟件公司高管、某外企高管，現任江蘇某上市公司財務總監。有著豐富的財務培訓和諮詢經驗，對財務經營分析理念、方法、實務、以及財務工具Excel應用、業財融合分析儀錶盤等，有著較深的研究和獨特的認識，在工作中積累了豐富的財務管理和企業經營數據分析經驗。

目錄
第 1 部分 數據采集與匯總
第 1 章 快速匯總大量的一維表單 ................................................................... 3
1.1 “現有連接”命令 +SQL 語句 ............................................................................ 3
1.1.1
1.1.3
1.1.4
1.1.5
1.1.6
1.2.1
1.2.3
SQL 語句的基本知識 ......................................................................................... 3
匯總全部列、部分行數據 ................................................................................... 8
匯總部分列、全部行數據 ................................................................................... 9
匯總部分列、部分行數據 ................................................................................... 9
匯總不同工作簿裡的工作表 ............................................................................. 10
匯總當前工作簿裡的多個工作表 ...................................................................... 10
匯總不同工作簿裡的多個工作表 ...................................................................... 12
1.1.2 匯總全部列數據 ................................................................................................. 5
1.2 VBA 方法 ........................................................................................................ 10
1.2.2 匯總不同工作簿裡的一個工作表 .......................................................................11
1.2.4 VBA 基本知識概述 ........................................................................................... 14
1.3 Power Query 方法 .......................................................................................... 24
1.3.1
匯總當前工作簿的多個工作表 .......................................................................... 24
1.3.2 匯總多個工作簿，每個工作簿僅有一個工作表................................................. 28
1.3.3 匯總多個工作簿，每個工作簿有多個工作表 .................................................... 34
1.3.4 匯總多個工作簿裡滿足條件的數據................................................................... 38
第 2 章 快速匯總大量二維表格 ..................................................................... 39
2.1 靈活匯總結構完全相同的工作表 ..................................................................... 39
2.1.1
使用 SUM 函數快速匯總 .................................................................................. 39
2.1.2 使用合併計算工具快速匯總 ............................................................................. 40
2.1.3 使用 INDIRECT 函數 ....................................................................................... 42
2.2 大量二維表格的快速匯總 ................................................................................ 43
2.2.1
2.2.3
利用多重合並計算數據區域透視表................................................................... 44
利用 Power Query ........................................................................................... 48
I
2.2.2 利用 INDIRECT 函數 ....................................................................................... 47

從邏輯思路到實戰應用，輕鬆做 Excel 數據分析
第 3 章 關聯工作表的快速匯總 ..................................................................... 52
3.1 Microsoft Query 工具 ...................................................................................... 52
3.1.1
Microsoft Query 匯總的基本步驟 ..................................................................... 52
3.1.2 Microsoft Query 匯總的注意事項 ..................................................................... 55
3.2 Power Query 工具 .......................................................................................... 57
3.2.1
建立基本查詢 ................................................................................................... 57
3.2.2 合併已有查詢 ................................................................................................... 58
3.2.3 添加自定義列 ................................................................................................... 61
3.2.4 加載為數據模型，便於以後隨時調用 ............................................................... 62
第 4 章 個數不定工作表的自動滾動匯總 ...................................................... 64
4.1 單獨使用 INDIRECT 函數 ............................................................................... 64
4.2 INDIRECT 函數與查找函數聯合使用 .............................................................. 65
4.3 INDIRECT 函數與分類匯總函數聯合使用 ....................................................... 66
第 5 章 獲取其他類型文件的數據 ................................................................. 68
5.1 從文本文件中獲取數據 ................................................................................... 68
5.2 從數據庫中獲取數據 ....................................................................................... 70
5.3 從網頁上獲取數據........................................................................................... 72
第 2 部分 數據查詢與提取
第 6 章 從一個工作表獲取部分數據 ............................................................. 77.
6.1 利用透視表快速製作明細表 ............................................................................ 77
6.1.1
每次製作一個明細表 ........................................................................................ 77
6.1.2 一次批量製作多個明細表 ................................................................................. 79
6.2 利用函數製作自動化明細表 ............................................................................ 81
6.2.1
製作滿足一個條件的明細表 ............................................................................. 81
6.2.2 製作滿足多個條件的明細表 ............................................................................. 83
6.2.3 綜合練習 .......................................................................................................... 84
6.3 利用 VBA 一鍵完成明細表 .............................................................................. 84
6.3.1
通過錄制宏將普通的篩選 / 複製 / 粘貼工作自動化 ........................................... 85
6.3.2 使用 ADO 技術快速篩選查詢數據 .................................................................... 86
6.3.3 在當前工作簿中批量製作明細表 ...................................................................... 87
6.3.4 批量製作明細表工作簿..................................................................................... 88
6.4 製作與源數據鏈接的動態明細表 ..................................................................... 89
第 7 章 從大量工作表中提取部分數據 .......................................................... 90
7.1 從一個工作簿的多個工作表中提取數據 .......................................................... 90
II

目 錄
7.1.1
多個工作表的合併查詢..................................................................................... 90
7.1.2 其他查詢操作 ................................................................................................... 95
7.2 從多個工作簿的多個工作表中提取數據 .......................................................... 96
7.2.1
多個工作簿的合併查詢..................................................................................... 97
7.2.2 其他查詢操作 ................................................................................................. 102
第 8 章 從其他類型文件中提取部分數據 .................................................... 103
8.1 從文本文件中提取數據 ................................................................................. 103
8.1.1
8.1.2
利用 Microsoft Query ..................................................................................... 103
利用 Power Query ......................................................................................... 106
8.2 從其他數據庫中提取數據 .............................................................................. 108
第 3 部分 數據分析與挖掘
第 9 章 數據分析是對數據的思考 ............................................................... 111
9.1 案例剖析：毛利預算分析 ...............................................................................111
9.1.1
9.1.2
9.1.3
9.1.4
9.1.5
9.1.6
9.2.1
9.2.2
9.2.3
9.2.4
9.2.5
9.2.6
9.2.7
失敗的分析報告 ..............................................................................................111
醒目標識需要特別關注的異常數據..................................................................112
用儀錶盤直觀表達出整體預算完成情況 ..........................................................112
分析各個產品毛利預算偏差影響 .....................................................................112
分析各個產品銷量、單價、成本對毛利預算偏差影響 .....................................113
進一步分析問題產品 .......................................................................................113
價值樹的原理 ..................................................................................................113
營業利潤價值樹分析 .......................................................................................114
毛利價值樹分析――依照計算邏輯流程 ..........................................................114
毛利價值樹分析――依照業務單元(詳細).....................................................115
毛利價值樹分析――依照業務單元(簡略).....................................................116
淨利潤率價值樹分析 .......................................................................................116
淨資產收益率價值樹分析 ................................................................................117
9.2 利用價值樹分析企業經營 ...............................................................................113
第 10 章 管理者駕駛艙，一目了然的經營分析與決策報告 ......................... 118
10.1 經營成果分析駕駛艙儀錶盤示例 ..................................................................118
10.1.1
10.1.2
10.2.1
10.2.2
10.2.3
預算執行分析儀錶盤 .....................................................................................119
同比分析儀錶盤 ............................................................................................119
使用數據透視表建立簡單的銷售分析儀錶盤 ................................................ 120
使用函數建立自動化銷售分析儀錶盤 ........................................................... 121
聯合使用函數和透視表建立銷售分析儀錶盤 ................................................ 121
10.2 銷售分析駕駛艙儀錶盤示例 ........................................................................ 120
III

從邏輯思路到實戰應用，輕鬆做 Excel 數據分析
10.2.4
利用 VBA 自動對客戶進行排名 .................................................................... 123
10.3 人工成本分析駕駛艙儀錶盤示例 ................................................................. 123
10.4 利用 Power 工具建立經營分析儀錶盤......................................................... 124
第 11 章 智能表格，日常數據的基本分析 .................................................. 125
11.1 智能表格的創建與維護................................................................................ 125
11.1.1
11.1.3
創建智能表格 ............................................................................................... 125
手工調整表格大小 ........................................................................................ 126
11.1.2 智能表格的格式化 ........................................................................................ 126
11.1.4 為表格添加列、插入行 ................................................................................. 127
11.1.5 刪除表格的列或行 ........................................................................................ 128
11.2 利用智能表格快速分析數據 ........................................................................ 128
11.2.1
11.2.3
為表格添加匯總行 ........................................................................................ 128
使用快速分析工具來分析表格 ...................................................................... 130
11.2.2 使用切片器篩選數據 .................................................................................... 129
第 12 章 固定格式報表：函數的綜合運用 .................................................. 131
12.1 示例數據 ..................................................................................................... 131
12.2 從原始數據直接製作底稿 ............................................................................ 132
12.2.1
12.2.2
12.3.1
12.3.2
從原始數據表單中直接匯總計算 .................................................................. 132
快速得到結構相同的匯總表 ......................................................................... 132
分析各個產品指定項目的當月數和累計數 .................................................... 133
分析指定產品、指定項目的各月預算執行情況............................................. 134
12.3 從底稿中直接進行計算分析 ........................................................................ 133
第 13 章 多維匯總分析，數據透視表工具的靈活運用 ................................ 136
13.1 製作數據透視表的常用方法 ........................................................................ 136
13.1.1
13.1.2
13.1.3
13.1.4
13.1.5
以一個表格數據創建數據透視表 .................................................................. 136
以多個二維表格數據創建數據透視表 ........................................................... 138
以多個一維表單數據創建數據透視表 ........................................................... 138
在不打開工作簿的情況下創建數據透視表 .................................................... 139
以表格的部分數據創建數據透視表............................................................... 140
13.2 佈局數據透視表 .......................................................................................... 140
13.2.1 “數據透視表字段”窗格 ............................................................................... 140
13.2.2
13.2.3
13.3.1
13.3.2
13.3.3
數據透視表佈局的幾種方法 ......................................................................... 141
數據透視表工具 ........................................................................................... 141
設計透視表的樣式 ........................................................................................ 141
設計透視表的佈局 ........................................................................................ 142
重複 / 不重複項目標簽 ................................................................................. 143
13.3 美化數據透視表 .......................................................................................... 141
IV

目 錄
13.3.4
13.3.5
13.3.7
13.3.9
13.3.10
13.3.12
顯示 / 隱藏報表的行總計和列總計 ............................................................... 143
合併項目標簽單元格 .................................................................................... 144
設置值字段的匯總依據................................................................................. 145
顯示 / 隱藏字段的分類匯總 .......................................................................... 145
對分類字段項目自動排序 ........................................................................... 146
顯示沒有數據的分類字段項目 .................................................................... 146
13.3.6 修改值字段名稱 ........................................................................................... 144
13.3.8 設置值字段的數字格式................................................................................. 145
13.3.11 對分類字段項目手動排序 ........................................................................... 146
13.3.13 刷新數據透視表 ......................................................................................... 147
13.4 利用透視表分析數據 ................................................................................... 147
13.4.1
13.4.2
13.4.3
13.4.4
13.4.5
13.4.6
13.4.7
13.4.8
13.4.9
13.5.1
13.5.2
13.5.3
通過設置字段匯總方式分析數據 .................................................................. 148
通過設置字段顯示方式分析數據 .................................................................. 148
通過組合字段分析數據................................................................................. 149
自定義計算字段 ........................................................................................... 151
自定義計算項 ............................................................................................... 152
計算字段和計算項的區別 ............................................................................. 153
透視表的基本數據篩選................................................................................. 154
篩選值最大(最小)的前 N 個項目 .............................................................. 154
使用切片器快速篩選報表 ............................................................................. 156
費用預實分析 ............................................................................................... 157
動態進銷存管理 ........................................................................................... 158
客戶銷售同比分析與流入流出分析............................................................... 160
13.5 透視表綜合應用案例 ................................................................................... 157
第 14 章 Power Pivot 報表，更加強大的數據分析工具 ............................ 162.
14.1 加載 Power Pivot ........................................................................................ 162
14.2 建立單個工作表的 Power Pivot .................................................................. 163
14.3 建立多個關聯工作表的 Power Pivot ........................................................... 167
14.4 以大量工作表數據創建 Power Pivot ........................................................... 171
14.5 以文本文件數據創建 Power Pivot ............................................................... 172
14.6 以數據庫數據創建 Power Pivot .................................................................. 174
第 4 部分 分析結果可視化
第 15 章 一圖抵萬言，數據分析可視化圖表 ............................................... 179
15.1 圖表到底是什麼 .......................................................................................... 179
15.1.1
15.1.2
對圖表的困惑 ............................................................................................... 179
案例剖析 ...................................................................................................... 180
V

從邏輯思路到實戰應用，輕鬆做 Excel 數據分析
15.1.3
15.2.1
15.2.2
15.2.3
15.2.4
15.2.5
15.3.1
15.3.2
15.3.3
15.3.4
15.3.5
15.3.6
15.3.7
15.3.8
數據充滿思想，圖表充滿思考 ...................................................................... 183
閱讀表格是基本功 ........................................................................................ 183
案例剖析舉例之一 ........................................................................................ 184
案例剖析舉例之二 ........................................................................................ 184
案例剖析舉例之三 ........................................................................................ 185
案例剖析舉例之四 ........................................................................................ 186
利用固定數據區域繪圖................................................................................. 186
利用定義名稱繪圖 ........................................................................................ 187
為圖表添加新數據系列................................................................................. 189
修改數據系列 ............................................................................................... 190
刪除數據系列 ............................................................................................... 190
設置數據系列的坐標軸................................................................................. 190
設置數據系列的圖表類型 ............................................................................. 191
圖表的保存位置 ........................................................................................... 192
15.2 從閱讀表格入手 .......................................................................................... 183
15.3 繪製圖表的基本方法 ................................................................................... 186
15.3.9 複製圖表 ...................................................................................................... 192
15.3.10 刪除圖表 .................................................................................................... 192
15.4 格式化圖表 ................................................................................................. 193
15.4.1
15.4.2
15.4.3
15.4.4
15.4.5
15.4.6
15.4.7
15.4.8
圖表結構及主要元素 .................................................................................... 193
為圖表添加元素 ........................................................................................... 195
選擇圖表元素的方法 .................................................................................... 195
設置圖表元素格式的工具 ............................................................................. 196
格式化圖表區 ............................................................................................... 196
格式化繪圖區 ............................................................................................... 197
格式化坐標軸 ............................................................................................... 197
格式化圖表標題、坐標軸標題、圖例 ........................................................... 197
15.4.9 格式化網格線 ............................................................................................... 197
15.4.10 格式化數據系列 ......................................................................................... 198
15.4.11 格式化數據標簽.......................................................................................... 198
15.4.12 突出標識圖表的重要信息 ........................................................................... 199
15.4.13 讓圖表元素顯示為單元格數據 .................................................................... 199
15.4.14 讓數據點顯示為形狀 .................................................................................. 199
15.4.15
15.5.1
15.5.2
15.5.3
15.5.4
VI
簡單是美 .................................................................................................... 199
柱形圖 .......................................................................................................... 199
條形圖 .......................................................................................................... 200
折線圖 .......................................................................................................... 202
XY 散點圖 .................................................................................................... 203
15.5 常見分析圖表及其基本應用案例 ................................................................. 199

目 錄
15.5.5
15.5.6
15.5.7
15.5.8
15.5.9
餅圖 ............................................................................................................. 204
圓環圖 .......................................................................................................... 206
面積圖 .......................................................................................................... 208
雷達圖 .......................................................................................................... 209
氣泡圖 .......................................................................................................... 210
15.5.10 樹狀圖 ........................................................................................................ 212
15.5.11 旭日圖 ........................................................................................................ 213
15.5.12 直方圖 ........................................................................................................ 213
15.5.13 箱形圖 ........................................................................................................ 214
15.5.14 漏斗圖 ........................................................................................................ 214
15.5.15
瀑布圖 ........................................................................................................ 215
15.5.16 組合圖 ........................................................................................................ 216
第 16 章 動態圖表，讓數據分析更靈活 ...................................................... 219
16.1 動態圖表技術：概述 ................................................................................... 219
16.1.1
16.1.2
16.1.3
16.1.4
16.1.5
16.1.6
16.2.1
16.2.2
16.2.3
16.2.4
16.2.5
16.2.6
動態圖的基本原理 ........................................................................................ 219
動態圖表製作方法：輔助區域法 .................................................................. 220
動態圖表製作方法：動態名稱法 .................................................................. 222
動態圖表製作方法：數據透視圖法............................................................... 222
製作動態圖表的六大步驟 ............................................................................. 223
製作動態圖表需要注意的幾個問題............................................................... 223
使用組合框製作動態圖表 ............................................................................. 223
使用列表框製作動態圖表 ............................................................................. 224
使用選項按鈕製作動態圖表 ......................................................................... 225
使用複選框製作動態圖表 ............................................................................. 226
使用滾動條製作動態圖表 ............................................................................. 228
使用數值調節鈕製作動態圖表 ...................................................................... 230
16.2 常見表單控件控制的動態圖表製作 ............................................................. 223
第 17 章 數據透視圖與數據透視表聯合使用，建立個性化的小 BI ............. 232.
17.1 創建數據透視圖 .......................................................................................... 232
17.1.1
17.1.2
17.1.3
17.1.4
17.2.1
17.2.2
17.3.1
創建數據透視圖的基本方法 ......................................................................... 232
關於數據透視圖的分類軸 ............................................................................. 233
數據透視圖的美化 ........................................................................................ 234
利用切片器控制透視表和透視圖 .................................................................. 234
建立多重合並計算數據區域透視表............................................................... 235
聯合使用切片器和數據透視圖進行分析 ....................................................... 236
創建普通的數據透視表................................................................................. 237
VII
17.2 二維表格的透視分析 ................................................................................... 235
17.3 一維表格的透視分析 ................................................................................... 237

從邏輯思路到實戰應用，輕鬆做 Excel 數據分析
17.3.2
17.3.3
17.3.4
分析指定產品的客戶銷售額 ......................................................................... 238
分析指定客戶的產品銷售情況 ...................................................................... 238
分析客戶銷售排名 ........................................................................................ 238
第 18 章 Power View 報表，更加靈活的可視化 ....................................... 240.
18.1 創建報表 ..................................................................................................... 240
18.2 報表的可視化 .............................................................................................. 241
第 5 部分 實戰案例精講
第 19 章 精選案例講解：手工台賬數據管理與分析模板――日常考勤 ............
管理 ............................................................................................ 247
19.1 設計考勤表母版 .......................................................................................... 247
19.2 各月考勤記錄表 .......................................................................................... 248
19.3 統計分析 ..................................................................................................... 248
第 20 章 精選案例講解：基於系統導出數據的分析模板――兩年銷售 ...............
同比分析 ..................................................................................... 251.
20.1 製作分析底稿 .............................................................................................. 251
20.2 兩年銷售額、銷售成本和毛利整體分析 ...................................................... 252
20.3 兩年銷售額、銷售成本和毛利的月度同比分析 ........................................... 253
20.4 分析企業銷售額同比增長原因：產品影響................................................... 253
20.5 分析指定產品銷售額同比增長原因：量價分析 ........................................... 254
20.6 分析企業毛利同比增長原因：產品影響 ...................................................... 257
20.7 分析指定產品毛利同比增長原因：銷量 - 單價 - 成本分析........................... 257
20.8 客戶排名分析 .............................................................................................. 259
20.9 分析客戶流動：新增客戶 ............................................................................ 259
20.10 分析客戶流動：流失客戶 .......................................................................... 260
20.11 分析客戶流動：存量客戶 .......................................................................... 260
VIII