動手學Excel數據分析與可視化

第1章 數據分析概述 1
1.1 數據分析簡介 1
1.1.1 什麼是數據分析 1
1.1.2 數據分析的方法 2
1.1.3 數據分析的流程 5
1.2 開始使用Excel 5
1.2.1 初識工作簿與工作表 6
1.2.2 快速錄入數據的技巧 8
1.2.3 數據重複錄入的驗證 14
1.3 Excel快速操作技巧 16
1.3.1 一鍵快速分析數據 16
1.3.2 對比工作表的變化 17
1.3.3 Excel常用快速鍵 18
1.4 動手練習：查看工作表之間的關係 22
1.5 實操練習 24

第2章 Excel連接數據源 25
2.1 導入本地離線數據 25
2.1.1 導入Excel工作簿文件 25
2.1.2 導入文本/CSV檔 26
2.1.3 JSON文件 27
2.2 導入關聯式數據庫中的數據 29
2.2.1 Excel連接SQL Server數據庫 29
2.2.2 Excel連接Access數據庫 31
2.2.3 Excel連接MySQL數據庫 32
2.3 讀取其他數據源的數據 34
2.3.1 使用“自表格/區域”讀取數據 34
2.3.2 讀取Web網站的數據 36
2.3.3 通過ODBC連接數據源 38
2.4 多表合併查詢 39
2.4.1 多表合併的應用場景 39
2.4.2 不同工作簿的多表合併 39
2.4.3 同一工作簿的多表合併 42
2.5 動手練習：讀取國家統計局的Web數據 44
2.5.1 連接到Web數據源 44
2.5.2 調整和清洗源數據 45
2.6 實操練習 51

第3章 使用公式與函數 52
3.1 公式與函數基礎 52
3.1.1 認識Excel函數 52
3.1.2 公式及其輸入方法 53
3.1.3 輸入Excel內置函數 54
3.2 Excel儲存格引用 55
3.2.1 儲存格的相對參照 56
3.2.2 儲存格的絕對引用 56
3.2.3 儲存格的混合引用 57
3.3 必會Excel新函數 59
3.3.1 多條件判斷 59
3.3.2 文本連接函數 60
3.3.3 日期處理函數 61
3.4 Excel函數舉例 63
3.4.1 與文本相關的函數 64
3.4.2 日期和時間處理函數 66
3.4.3 數值計數與求和函數 68
3.5 動手練習：函數嵌套計算職位薪資 73
3.6 實操練習 75

第4章 Excel日常數據分析 76
4.1 Excel數據排序 76
4.1.1 按單個欄位排序 76
4.1.2 按多個欄位排序 77
4.1.3 自訂數據排序 78
4.2 Excel條件格式 80
4.2.1 突出顯示儲存格 80
4.2.2 自訂條件格式 81
4.2.3 儲存格數據添加圖示 82
4.3 Excel數據分類匯總 83
4.3.1 按單個欄位分類匯總 83
4.3.2 按多個欄位分類匯總 84
4.4 Excel數據篩選 85
4.4.1 自動篩選數據 85
4.4.2 自訂篩選數據 85
4.5 動手練習：Excel在學生管理中的運用 86
4.6 實操練習 90

第5章 Excel數據透視表 91
5.1 數據透視表入門 91
5.1.1 創建數據透視表 91
5.1.2 更改和刷新數據源 92
5.1.3 為數據透視表插入切片器 94
5.2 數據透視表佈局 95
5.2.1 設置透視表報表分類匯總 96
5.2.2 設置透視表報表佈局形式 96
5.3 數據透視表計算 98
5.3.1 在透視表中添加計算欄位 98
5.3.2 在透視表中添加計算項 100
5.4 數據透視表顯示 101
5.4.1 在數據透視表中添加貨幣符號 101
5.4.2 透視表欄位清單的設置 102
5.5 動手練習：財政稅收收入結構分析 103
5.5.1 union語句在財政歷年稅收收入結構分析中的應用 104
5.5.2 Excel數據透視表與SQL結合應用的注意事項 106
5.5.3 小結 106
5.6 實操練習 107

第6章 Excel數據清洗 108
6.1 重複數據檢測與處理 108
6.1.1 重複數據的檢測 108
6.1.2 重複數據的處理 110
6.2 缺失數據檢測與處理 110
6.2.1 缺失數據的檢測 110
6.2.2 缺失數據的處理 111
6.3 異常數據的檢測與處理 112
6.3.1 異常數據的檢測 112
6.3.2 異常數據的處理 113
6.4 數據清洗的其他方法 113
6.4.1 快速實現數據分列 113
6.4.2 更改文本的大小寫 114
6.5 動手練習：Excel指紋考勤數據處理 116
6.6 實操練習 120

第7章 Excel基本統計分析 121
7.1 描述性統計 121
7.1.1 描述性統計概述 121
7.1.2 主要描述性指標 122
7.1.3 描述性統計分析實例 124
7.2 相關分析 125
7.2.1 皮爾遜相關係數 126
7.2.2 斯皮爾曼相關係數 127
7.2.3 肯德爾相關係數 128
7.3 模擬分析 129
7.3.1 單變數求解類比 129
7.3.2 雙變數類比運算 130
7.4 方差分析 133
7.4.1 單因素方差分析 133
7.4.2 雙因素方差分析 135
7.5 動手練習：企業投資決策敏感性分析 138
7.5.1 投資決策敏感性分析的分類 139
7.5.2 敏感性分析的主要範本設計 139
7.5.3 利用“單變數求解”功能分析各因素的臨界值 140
7.5.4 案例小結 141
7.6 實操練習 142

第8章 Excel高級統計分析 143
8.1 回歸分析 143
8.1.1 線性回歸簡介 143
8.1.2 線性回歸建模 146
8.1.3 回歸模型案例 147
8.2 時間序列分析 149
8.2.1 時間序列簡介 150
8.2.2 移動平均法及案例 151
8.2.3 指數平滑法及案例 155
8.3 假設檢驗 158
8.3.1 Z檢驗及案例 158
8.3.2 T檢驗及案例 159
8.3.3 F檢驗及案例 160
8.4 線性規劃 161
8.4.1 線性規劃簡介 162
8.4.2 案例：規劃求解問題 163
8.4.3 案例：最短路徑問題 165
8.5 動手練習：活躍用戶數據建模與預測 168
8.5.1 分析思路 168
8.5.2 解決方案 169
8.6 實操練習 172

第9章 Excel數據視覺化 173
9.1 Excel圖表及案例 173
9.1.1 繪製對比型圖表 173
9.1.2 繪製趨勢型圖表 175
9.1.3 繪製比例型圖表 177
9.1.4 繪製分佈型圖表 178
9.1.5 繪製其他類圖表 180
9.2 迷你圖及案例 181
9.2.1 創建迷你圖 182
9.2.2 迷你圖設置 182
9.2.3 企業盈虧分析 185
9.3 數據透視圖及案例 186
9.3.1 如何創建數據透視圖 186
9.3.2 利用篩選器篩選數據 190
9.3.3 數據透視圖樣式設計 193
9.3.4 為透視圖添加切片器 194
9.4 Excel圖表繪製技巧 195
9.4.1 調整圖表格式控制 195
9.4.2 圖表標注突出數據 197
9.4.3 巧用圖表樣式範本 198
9.5 Excel圖表美化方法 199
9.5.1 幾招讓表格不難看 200
9.5.2 如何使圖形更美觀 202
9.6 動手練習：Excel視覺化外掛程式——EasyCharts 205
9.6.1 示例9-1：繪製2021年第四季度銷售額分割面積圖 206
9.6.2 示例9-2：繪製2022年3月份銷售業績子彈圖 206
9.7 實操練習 207

第10章 用儀錶板展示數據 208
10.1 如何製作出高效精美的儀錶板 208
10.1.1 熟悉業務合理規劃 208
10.1.2 利用視圖充分展示 209
10.1.3 完善視圖避免錯誤 209
10.2 製作儀錶板 209
10.2.1 創建數據透視表 210
10.2.2 創建數據透視圖 211
10.2.3 添加圖形切片器 212
10.3 共用儀錶板 213
10.3.1 與他人直接共用 214
10.3.2 通過電子郵件共用 215
10.3.3 通過Power BI共用 217
10.4 動手練習：2021年商品銷售額儀錶板 218
10.5 實操練習 222

第11章 Excel連接Hadoop集群 223
11.1 瞭解Hadoop 223
11.1.1 Hadoop概述 223
11.1.2 Hadoop組件 224
11.1.3 Apache Hadoop發行版本 227
11.2 連接Hadoop Hive 228
11.2.1 啟動Hadoop Hive 228
11.2.2 連接Cloudera Hadoop 229
11.2.3 連接MapR Hadoop 231
11.3 連接Hadoop Spark 232
11.3.1 啟動Hadoop Spark 232
11.3.2 配置SparkODBC 233
11.3.3 連接Hadoop Spark 234
11.4 動手練習：Excel連接Hadoop集群訂單表 235
11.5 實操練習 236

第12章 電商客戶關係管理實戰 237
12.1 RFM分析法 237
12.2 數據分析與建模 238
12.2.1 數據清洗 239
12.2.2 數據整理 240
12.2.3 確定權重 241
12.3 數據分析與視覺化 242
12.3.1 數據分析 242
12.3.2 數據視覺化 244
12.3.3 案例小結 246
12.4 客戶關係管理策略 246
12.5 實操練習 247

第13章 銀行用戶信用評分實戰 248
13.1 數據準備 248
13.1.1 案例數據集介紹 248
13.1.2 描述性統計分析 249
13.2 數據清洗 250
13.2.1 重複值的處理 250
13.2.2 缺失值的處理 251
13.2.3 異常值的處理 251
13.3 客戶特徵分析 251
13.3.1 客戶屬性分析 251
13.3.2 還款能力分析 253
13.3.3 違約風險分析 255
13.4 客戶違約率分析 256
13.4.1 違約率與月收入的關係 257
13.4.2 違約率與年齡的關係 257
13.4.3 違約率與逾期次數的關係 258
13.5 實操練習 258

附錄A Excel主要函數 259
A.1 數學和三角函數 259
A.2 統計函數 262
A.3 邏輯函數 266
A.4 日期和時間函數 267
A.5 查找和引用函數 268
A.6 文本函數 269
A.7 財務函數 270
A.8 工程函數 272
A.9 信息函數 274
A.10 數據庫函數 275
A.11 Web函數 275
A.12 相容性函數 276
A.13 多維數據集函數 277
附錄B Excel快速鍵 278
附錄C Excel宏與VBA 284
C.1 宏及其操作 284
C.2 VBA基礎知識 287
C.3 VBA程式設計案例：冒泡排序演算法實現數據排序 289
附錄D 認識SQL語句 292
D.1 CREATE語句 292
D.2 ALTER語句 293
D.3 INSERT語句 293
D.4 UPDATE語句 294
D.5 SELECT語句 294
D.6 DELETE語句 296

王國平

碩士，戰鬥在一線的數據分析職場老兵，已有十餘年數據分析經驗，擁有數十種數據分析工具的專業技能，出版相關著作多部。