Excel 與 Power BI 數據分析從新手到高手

宋翔，2010-2015年連續6界微軟全球z有價值專家（MVP），微軟TechNet Webcast講師，微軟TechNet中文論壇的版主，在微軟產品以及圖形圖像、網絡和程序開發等多個領域一直進行著不斷地研究、設計與開發，為政府部門和大中型企業開發過管理系統、商用軟件、網絡佈線安防與安全系統工程，還為很多企業和廣告公司設計過企業標識、產品包裝、海報招貼、戶外廣告、企業內部刊物和雜誌的封面與版式等，並為這些公司的員工進行過多場平面設計與版式設計方面的培訓。曾在程序開發、圖形圖像設計、辦公軟件等不同類型的多個論壇擔任版主，解決網友遇到的技術與設計方面的問題。

目錄
第1章　數據分析的基本原理和工具 1
1.1　數據分析的基本原理 1
1.1.1　為什麽要進行數據分析 1
1.1.2　數據分析的基本概念 1
1.1.3　數據分析的基本流程 2
1.2　Excel中的數據分析工具 3
1.2.1　基本分析工具——排序、篩選、分類匯總、數據透視表 3
1.2.2　高級分析具——模擬分析、單變量求解、規劃求解、分析工具庫 4
1.2.3　可視化工具——圖表、迷你圖 5
1.2.4　商業智能分析工具——Power BI 6
1.2.5　公式和函數 6

第2章　創建基礎數據 7
2.1　輸入數據 7
2.1.1　瞭解Excel中的數據類型 7
2.1.2　輸入數據的基本方法 10
2.1.3　輸入序列數字 11
2.1.4　輸入超過15位的數字 12
2.1.5　輸入日期 13
2.1.6　換行輸入 14
2.1.7　提高輸入效率的技巧 14
2.2　導入數據 16
2.2.1　導入文本文件中的數據 16
2.2.2　導入Access數據庫中的數據 18
2.2.3　使用Microsoft Query導入數據 19
2.3　編輯數據 22
2.3.1　修改數據 22
2.3.2　移動和復制數據 23
2.3.3　使用不同的粘貼方式 24
2.3.4　刪除數據 26
2.4　保存數據 26
2.4.1　Excel支持的工作簿類型 27
2.4.2　將數據保存為Excel工作簿 27
2.4.3　設置保存工作簿的默認格式 28
2.4.4　設置自動恢復工作簿的保存時間間隔和位置 29

第3章　規範化數格式 30
3.1　使用數據驗證功能讓數據輸入規範化 30
3.1.1　瞭解數據驗證 30
3.1.2　只允許用戶從列表中選擇選項來輸入 31
3.1.3　限制輸入的數值和日期範圍 32
3.1.4　禁止輸入重復內容 33
3.1.5　檢查並圈釋無效數據 35
3.1.6　管理數據驗證 35
3.2　修復格式不規範的數據 36
3.2.1　使用分列功能拆分復雜數據 36
3.2.2　更正使用小數點分隔的日期 38
3.2.3　轉換不正確的數據類型 38
3.3　設置數據格式 39
3.3.1　設置字體格式 39
3.3.2　設置數字格式 40
3.3.3　設置數據在單元格中的對齊方式 42
3.3.4　使用單元格樣式快速設置多種格式 43

第4章　使用公式和函數處理不同類型的數據 45
4.1　公式和函數基礎 45
4.1.1　公式的組成 45
4.1.2　運算符及其優先級 45
4.1.3　輸入和修改公式 46
4.1.4　移動和復制公式 47
4.1.5　更改公式的計算方式 48
4.1.6　在公式中輸入函數及其參數 48
4.1.7　在公式中引用其他工作表或工作簿中的數據 52
4.1.8　創建數組公式 54
4.1.9　處理公式錯誤 55
4.2　處理文本 56
4.2.1　提取指定數量的字符 57
4.2.2　計算文本長度 57
4.2.3　查找文本 58
4.2.4　替換文本 58
4.3　處理數值 59
4.3.1　數據求和 60
4.3.2　對滿足條件的數據求和 60
4.3.3　統計數量 63
4.3.4　對滿足條件的數據統計數量 64
4.3.5　求最大值和最小值 65
4.3.6　求第k個最大值和最小值 66
4.3.7　對數據進行多種類型的匯總統計 67
4.3.8　數據排名 68
4.4　處理日期和時間 69
4.4.1　返回當前系統日期和時間 69
4.4.2　創建日期 69
4.4.3　返回日期中的年、月、日 70
4.4.4　計算兩個日期之間的時間間隔 71
4.4.5　計算相隔幾個月位於同一天或最後一天的日期 71
4.4.6　計算相隔數個工作日的日期 72
4.5　處理財務數據 74
4.5.1　財務基礎知識 74
4.5.2　借貸和投資 74
4.5.3　計算本金和利息 76
4.6　查詢和引用數據 78
4.6.1　查找特定值的位置 78
4.6.2　返回位於特定行、列位置上的值 79
4.6.3　使用LOOKUP函數查找數據 80
4.6.4　使用VLOOKUP函數查找數據 82
4.7　函數應用綜合案例 83
4.7.1　從身份證號碼中提取出生日期和性別 83
4.7.2　判斷閏年 84
4.7.3　統計不重復人數 84
4.7.4　同時從多列數據中查找信息 85
4.7.5　反向查找數據 85

第5章　使用多種工具分析數據 86
5.1　排序數據 86
5.1.1　單條件排序 86
5.1.2　多條件排序 87
5.1.3　自定義排序 88
5.2　篩選數據 89
5.2.1　進入和退出篩選模式 89
5.2.2　篩選數據 90
5.2.3　高級篩選 92
5.3　分類匯總數據 93
5.3.1　匯總一類數據 93
5.3.2　匯總多類數據 94
5.3.3　刪除分類匯總 95
5.4　使用數據透視表多角度透視分析 95
5.4.1　數據透視表的結構和常用術語 96
5.4.2　創建數據透視表 98
5.4.3　佈局字段 100
5.4.4　重命名字段 102
5.4.5　改變數據透視表的佈局 103
5.4.6　為數據分組 104
5.4.7　設置數據的匯總方式和計算方式 107
5.4.8　創建計算字段 108
5.4.9　創建計算項 110
5.4.10　刷新數據透視表 111
5.5　模擬分析 112
5.5.1　基於一個變量的模擬分析 112
5.5.2　基於兩個變量的模擬分析 113
5.5.3　在多組條件下進行模擬分析 114
5.6　單變量求解 115
5.7　規劃求解 116
5.7.1　加載規劃求解 116
5.7.2　使用規劃求解分析數據 117
5.8　分析工具庫 119
5.8.1　加載分析工具庫 120
5.8.2　分析工具庫中包含的分析工具 120
5.8.3　使用分析工具庫分析數據 121

第6章　使用圖表展示數據 122
6.1　創建和編輯圖表 122
6.1.1　Excel中的圖表類型 122
6.1.2　圖表的組成 123
6.1.3　嵌入式圖表和圖表工作表 124
6.1.4　創建圖表 125
6.1.5　移動和復制圖表 127
6.1.6　更改圖表類型 128
6.1.7　設置圖表的整體佈局和配色 128
6.1.8　設置圖表元素的格式 130
6.1.9　編輯數據系列 131
6.1.10　刪除圖表 133
6.2　使用迷你圖 133
6.2.1　創建迷你圖 133
6.2.2　迷你圖組合 134
6.2.3　更改迷你圖類型 135
6.2.4　編輯迷你圖 135
6.2.5　設置迷你圖格式 135
6.2.6　刪除迷你圖 136

第7章　Power BI快速入門 137
7.1　瞭解Power BI及其組件 137
7.1.1　Power BI的基本概念和組成 137
7.1.2　Power BI的基本工作流程 138
7.1.3　Power BI的基本元素 138
7.1.4　下載和安裝Power BI Desktop 140
7.2　Power BI Desktop中的3種視圖 143
7.2.1　報表視圖 143
7.2.2　數據視圖 146
7.2.3　模型視圖 147
7.3　Power BI Desktop中的查詢編輯器 148
7.3.1　打開查詢編輯器 148
7.3.2　查詢窗格 149
7.3.3　數據區域 150
7.3.4　查詢設置窗格 150
7.3.5　保存工作 151
7.4　使用Power BI Desktop創建報表的基本流程 151
7.4.1　連接並獲取數據 151
7.4.2　整理和轉換數據 152
7.4.3　為數據建模 153
7.4.4　為數據創建視覺對象 153
7.4.5　創建和共享報表 153

第8章　獲取和整理數據 155
8.1　獲取和刷新數據 155
8.1.1　連接並獲取數據 155
8.1.2　只獲取部分數據 157
8.1.3　刷新數據 158
8.1.4　更改數據源 159
8.1.5　刪除查詢 160
8.2　調整行和列 161
8.2.1　刪除不需要的行和列 161
8.2.2　調整列的位置 163
8.3　修改和轉換數據 163
8.3.1　將第一行數據設置為列標題 164
8.3.2　重命名列標題 164
8.3.3　更改字段的數據類型 165
8.3.4　批量修改特定的值 166
8.3.5　轉換文本格式 166
8.3.6　將二維表轉換為一維表 167
8.4　提取和拆分數據 168
8.4.1　提取字符 168
8.4.2　提取日期元素 169
8.4.3　拆分列 170
8.4.4　使用“條件列”功能返回指定的值 171
8.5　合並數據 172
8.5.1　使用“追加查詢”縱向合並數據 172
8.5.2　使用“合並查詢”橫向合並數據 173
8.5.3　合並文件夾中的文件 175
8.6　排序、篩選和匯總數據 177
8.6.1　排序數據 178
8.6.2　篩選數據 178
8.6.3　分類匯總數據 180

第9章　創建數據模型和新的計算 182
9.1　創建數據模型 182
9.1.1　自動創建關系 182
9.1.2　手動創建關系 184
9.1.3　修改關系 18
9.1.4　刪除關系 188
9.2　創建計算列和度量值 188
9.2.1　瞭解DAX公式 189
9.2.2　創建計算列 190
9.2.3　創建度量值 192

第10章　使用視覺對象展示數據 194
10.1　創建、設置和刪除視覺對象 194
10.1.1　創建視覺對象的3種方法 194
10.1.2　更改視覺對象的類型和字段的位置 197
10.1.3　移動和調整視覺對象的大小 198
10.1.4　設置視覺對象的格式 199
10.1.5　刪除視覺對象 199
10.2　設置在視覺對像上查看和交互數據的方式 200
10.2.1　創建層次結構並鑽取數據 200
10.2.2　使用切片器篩選數據 202
10.2.3　更改視覺對像上的數據交互方式 203
10.2.4　使用焦點模式 204
10.2.5　查看和導出與視覺對象關聯的數據 205

第11章　設計報表 207
11.1　報表頁的基本操作 207
11.1.1　添加報表頁 207
11.1.2　修改報表頁的名稱 208
11.1.3　復制報表頁 208
11.1.4　刪除報表頁 208
11.2　設計報表時的一些有用工具 209
11.2.1　使用參考線快速對齊和定位視覺對象 209
11.2.2　使用頁面篩選和報表篩選 210
11.2.3　使用文本框、形狀和圖片 211
11.2.4　使用主題為報表統一配色 212
11.2.5　聚焦視覺對象 212

第12章　在Excel中使用Power BI分析數據 214
12.1　在Excel中安裝Power加載項 214
12.2　使用Power Query處理數據 217
12.2.1　導入不同來源的數據 217
12.2.2　修改數據源 218
12.2.3　重命名和刪除查詢 218
12.2.4　在Power Query編輯器中整理數據 219
12.2.5　將導入的數據加載到Excel工作表中 221
12.2.6　刷新數據 223
12.3　使用Power Pivot為數據建模 224
12.3.1　將數據添加到Power Pivot中 224
12.3.2　創建關系 227
12.3.3　創建計算列 228
12.3.4　創建度量值 229
12.4　使用Power View展示數據 230

第13章　Power BI數據分析在人力、銷售和財務中的應用 234
13.1　客戶分析 234
13.1.1　計算客戶的銷售額佔比 234
13.1.2　根據銷售額為客戶排名 236
13.1.3　使用餅圖分析客戶銷售額佔比 236
13.1.4　根據銷售額為客戶評級 240
13.1.5　創建客戶等級統計表 241
13.2　銷售額分析 242
13.2.1　製作銷售額提成表 242
13.2.2　分析產品在各個地區的佔有率 244
13.2.3　在餅圖中快速切換顯示不同季度的銷售額 246
13.3　財務報表分析 248
13.3.1　製作利潤匯總表 249
13.3.2　製作月報、季報和年報 252
13.3.3　製作累計報表 256