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

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