Excel商務智能：Power Query和Power Pivot數(shù)據(jù)清洗、建模與分析實戰(zhàn)

第1章 Excel：你的職場生產(chǎn)力工具 / 1
1．1 你所不知道的Excel分析“利器” / 1
1．2 從Excel到Power BI，只需要一步 / 2
第2章 認識Power Query編輯器 / 4
2．1 初識Power Query / 4
2．2 編輯器管理界面介紹 / 5
2．3 創(chuàng)建查詢的方法 / 7
2．4 數(shù)據(jù)源路徑的修改與設置 / 9
2．5 數(shù)據(jù)上載與刷新 / 11
第3章 Power Query的基本操作實例 / 14
3．1 入門基礎知識 / 14
3．1．1 數(shù)據(jù)類型的設置 / 14
3．1．2 標題的升降設置 / 16
3．1．3 “轉換”與“添加列”選項卡中的功能 / 16
3．2 刪除行或列操作 / 18
3．2．1 選擇列與刪除列 / 18
3．2．2 刪除行與保留行 / 20
3．2．3 通過篩選器刪除行 / 24
3．3 添加列操作 / 25
3．3．1 簡單快速地添加條件列 / 25
3．3．2 為行添加自定義序號 / 27
3．3．3 添加自定義列 / 29
3．4 拆分列與合并列操作 / 30
3．4．1 實例1：按分隔符拆分列 / 30
3．4．2 實例2：按字符數(shù)拆分列 / 33
3．4．3 實例3：按位置拆分列 / 34
3．4．4 實例4：其他拆分列的方法 / 35
3．4．5 合并列常用的方法 / 36
3．5 透視列與逆透視列操作 / 38
3．5．1 一維表和二維表 / 38
3．5．2 實例1：一維表轉二維表 / 39
3．5．3 實例2：二維表轉一維表 / 41
3．5．4 實例3：含有多重行/列表頭的數(shù)據(jù)清洗 / 42
3．6 提取文本值中指定字符的操作 / 45
3．6．1 實例1：按指定的長度提取文本值中指定的字符 / 45
3．6．2 實例2：按分隔符的位置提取文本值中指定的字符 / 47
3．7 數(shù)學運算和分組統(tǒng)計 / 49
3．7．1 聚合運算的操作 / 49
3．7．2 實例1：活用“選擇性粘貼”功能處理考試成績 / 50
3．7．3 實例2：使用分組統(tǒng)計功能快速計算各部門的數(shù)據(jù) / 53
3．8 追加查詢與合并查詢 / 54
3．8．1 實例1：使用追加查詢批量合并多個Excel工作表數(shù)據(jù) / 54
3．8．2 認識合并查詢的6種類型 / 56
3．8．3 實例2：使用合并查詢完成各種數(shù)據(jù)匹配 / 61
第4章 M函數(shù)和M公式基礎入門 / 65
4．1 M函數(shù)和M公式介紹 / 65
4．1．1 M函數(shù)和M公式 / 65
4．1．2 主要的M函數(shù)類型 / 66
4．1．3 常用的數(shù)據(jù)類型 / 67
4．1．4 運算符 / 68
4．1．5 如何查看函數(shù)幫助 / 69
4．2 三大數(shù)據(jù)結構 / 72
4．2．1 列表 / 72
4．2．2 記錄 / 74
4．2．3 表 / 74
4．2．4 數(shù)據(jù)結構的組合和深化 / 75
4．2．5 數(shù)據(jù)結構的擴展 / 77
4．3 數(shù)據(jù)結構之間的相互轉換 / 79
4．3．1 List和Record之間的轉換 / 79
4．3．2 Table和List之間的轉換 / 80
4．3．3 Table和Record之間的轉換 / 83
4．4 M公式中常用的語句 / 84
4．4．1 let…in…語句 / 84
4．4．2 條件分支語句 / 85
4．4．3 容錯語句try…otherwise… / 88
4．4．4 each _與(x)=＞的關系 / 89
4．4．5 為公式添加注釋 / 92
第5章 常用的M函數(shù)實戰(zhàn)詳解 / 94
5．1 各種數(shù)據(jù)類型之間的相互轉換 / 94
5．1．1 將值轉換為文本 / 94
5．1．2 將值轉換為數(shù)值 / 95
5．1．3 將值轉換為日期 / 96
5．2 List和Table的批量轉換實戰(zhàn) / 96
5．2．1 批量轉換函數(shù)List．Transform的實際應用 / 96
5．2．2 批量轉換函數(shù)Table．TransformColumns的實際應用 / 97
5．3 獲取和刪除各種數(shù)據(jù)實戰(zhàn) / 99
5．3．1 使用Table．Skip函數(shù)和Table．SelectRows函數(shù)篩選行 / 99
5．3．2 獲取和刪除指定文本值中的指定字符 / 101
5．3．3 獲取和刪除列表中的元素 / 102
5．4 各種數(shù)據(jù)結構的拆分、合并、截取和替換實戰(zhàn) / 104
5．4．1 實例1：表的拆分與合并應用 / 104
5．4．2 實例2：列表的拆分與合并應用 / 106
5．4．3 實例3：拆分和提取文本值中的數(shù)值并求和 / 108
5．4．4 對文本值進行截取的函數(shù) / 112
5．4．5 實例4：批量替換和有條件地批量替換文本值 / 113
5．4．6 實例5：使用List．Zip函數(shù)批量更換標題及制作工資條 / 117
5．5 判斷文本值和列表中是否包含指定的內容 / 120
5．5．1 實例1：對任意組合的條件值求和 / 120
5．5．2 實例2：根據(jù)標準答案計算多選題的得分 / 123
5．6 分組函數(shù)Table．Group及其應用 / 125
5．6．1 Table．Group函數(shù)和常規(guī)分組計算 / 125
5．6．2 實例：條件分組計算和數(shù)據(jù)清洗整理獲獎數(shù)據(jù) / 129
5．7 參數(shù)與自定義函數(shù) / 132
5．7．1 參數(shù)的設置方法 / 132
5．7．2 實例：創(chuàng)建和調用自定義函數(shù)將一列拆分為多列 / 134
第6章 Power Query綜合實戰(zhàn) / 140
6．1 數(shù)據(jù)獲取綜合實戰(zhàn) / 140
6．1．1 實例1：獲取并合并Excel工作簿中的多個工作表的數(shù)據(jù) / 140
6．1．2 實例2：獲取并合并多個文件夾下的Excel工作簿中的數(shù)據(jù) / 144
6．1．3 實例3：獲取網(wǎng)頁中的表格數(shù)據(jù) / 147
6．1．4 實例4：獲取CSV或TXT文件數(shù)據(jù) / 151
6．1．5 實例5：實時獲取數(shù)據(jù)庫中的數(shù)據(jù) / 153
6．2 數(shù)據(jù)轉換綜合實戰(zhàn) / 155
6．2．1 實例1：將復雜的二維調薪表轉換為一維明細表 / 155
6．2．2 實例2：高效快速地清洗零亂的考勤數(shù)據(jù) / 158
6．2．3 實例3：同時拆分組合的供應商中文名稱和英文名稱 / 164
6．2．4 實例4：批量提取Excel工作簿中不規(guī)則的防疫數(shù)據(jù) / 167
第7章 認識Power Pivot與DAX / 170
7．1 Power Pivot介紹 / 170
7．1．1 認識Power Pivot / 170
7．1．2 從數(shù)據(jù)透視表的不重復計算說起 / 171
7．1．3 在Excel中加載Power Pivot / 173
7．1．4 認識Power Pivot的管理界面 / 174
7．2 Power Pivot的數(shù)據(jù)獲取方式 / 175
7．2．1 從表格/區(qū)域和Power Query導入數(shù)據(jù) / 176
7．2．2 從Excel文件導入數(shù)據(jù) / 177
7．2．3 從文本文件導入數(shù)據(jù) / 178
7．2．4 從剪切板導入數(shù)據(jù) / 179
7．2．5 從數(shù)據(jù)庫導入數(shù)據(jù) / 180
7．3 認識數(shù)據(jù)分析表達式DAX / 182
7．3．1 常用的DAX函數(shù)類型 / 182
7．3．2 DAX中的數(shù)據(jù)類型與運算符 / 184
7．3．3 創(chuàng)建DAX表達式時表和列的引用方式 / 185
第8章 Power Pivot和DAX基礎知識 / 187
8．1 理解計算列與度量值 / 187
8．1．1 依附于數(shù)據(jù)表的計算列 / 187
8．1．2 能適應各種環(huán)境的度量值 / 189
8．1．3 度量值與數(shù)據(jù)透視表的計算字段 / 191
8．1．4 如何選擇度量值與計算列 / 193
8．1．5 管理度量值 / 193
8．2 數(shù)據(jù)模型與表間關系 / 195
8．2．1 理解Power Pivot的數(shù)據(jù)模型 / 195
8．2．2 多表操作時表間關系的建立和管理 / 196
8．2．3 表、列和度量值的隱藏 / 199
8．2．4 LOOKUPVALUE函數(shù)介紹 / 200
8．2．5 RELATED函數(shù)與RELATEDTABLE函數(shù)介紹 / 201
8．3 DAX的基礎函數(shù) / 203
8．3．1 以SUMX為代表的結尾帶有X的聚合函數(shù) / 203
8．3．2 篩選函數(shù)FILTER和邏輯運算符 / 205
8．3．3 DAX中最重要的CALCULATE函數(shù) / 207
8．3．4 CALCULATE函數(shù)的篩選器的選擇 / 209
8．3．5 VALUES函數(shù)和DISTINCT函數(shù) / 211
8．3．6 初識ALL函數(shù)和ALLEXCEPT函數(shù) / 213
8．4 初識計值上下文 / 214
8．4．1 初識篩選上下文 / 215
8．4．2 創(chuàng)建篩選上下文 / 216
8．4．3 初識行上下文 / 219
8．4．4 行上下文轉換 / 220
8．5 CALCULATE函數(shù)的調節(jié)器 / 224
8．5．1 刪除篩選器的ALL函數(shù) / 224
8．5．2 追加篩選的KEEPFILTERS函數(shù) / 227
8．5．3 激活關系的USERELATIONSHIP函數(shù) / 228
第9章 DAX進階知識和常見應用 / 231
9．1 Power Pivot和數(shù)據(jù)透視表 / 231
9．1．1 實例1：在數(shù)據(jù)透視表中使用自定義排序：按列排序 / 231
9．1．2 實例2：在數(shù)據(jù)透視表中創(chuàng)建KPI規(guī)則――設置“條件格式” / 233
9．2 在DAX中使用VAR變量 / 236
9．2．1 關于VAR變量 / 236
9．2．2 使用變量時應該避免的錯誤 / 237
9．3 常見的DAX函數(shù)和實際案例應用 / 238
9．3．1 實例1：使用CONCATENATEX函數(shù)進行文本值透視 / 238
9．3．2 實例2：使用ALLSELECTED函數(shù)動態(tài)地計算各類占比 / 241
9．3．3 實例3：使用RANKX函數(shù)動態(tài)地計算各類排名 / 244
9．3．4 實例4：自定義數(shù)據(jù)透視表標題行完成復雜的報表 / 248
9．4 DAX作為查詢工具的實際應用 / 251
9．4．1 數(shù)據(jù)查詢和EVALUATE / 251
9．4．2 實例1：使用ADDCOLUMNS函數(shù)建立查詢表 / 254
9．4．3 實例2：使用SELECTCOLUMNS函數(shù)建立查詢表 / 256
9．4．4 實例3：使用SUMMARIZE和SUMMARIZECOLUMNS函數(shù)分組匯總數(shù)據(jù) / 257
9．5 Power Pivot數(shù)據(jù)模型與多維數(shù)據(jù)集函數(shù) / 259
9．5．1 認識CUBE類函數(shù) / 260
9．5．2 實例1：使用CUBEVALUE函數(shù)提取Power Pivot數(shù)據(jù)模型中的數(shù)據(jù) / 262
9．5．3 實例2：使用“切合器+CUBEVALUE函數(shù)”動態(tài)提取Power Pivot數(shù)據(jù)模型中的數(shù)據(jù) / 266
第10章 時間智能計算 / 269
10．1 認識時間智能函數(shù)和日期表 / 269
10．1．1 時間智能函數(shù)與日期函數(shù) / 269
10．1．2 日期表的創(chuàng)建與標記 / 270
10．1．3 與時間智能函數(shù)相關的常用計算指標 / 272
10．2 常見的時間智能計算 / 273
10．2．1 實例1：年初、季初與月初至今計算 / 273
10．2．2 實例2：各類同比與環(huán)比的計算 / 275
10．2．3 實例3：動態(tài)移動平均分析模型 / 280
第11章 Power Pivot綜合實戰(zhàn) / 283
11．1 實例1：TOP-N門店銷售和利潤貢獻度分析模型 / 283
11．2 實例2：折扣比例分組（分區(qū)間）分析模型 / 286
11．3 實例3：動態(tài)ABC分類分析模型（帕累托分析模型） / 288
11．4 實例4：RFM客戶價值分析模型 / 291
11．5 實例5：員工在職、入職、離職和離職率的計算模型 / 296