動手學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