Excel 進(jìn)階指南：Power Pivot 與 Power Query 實戰(zhàn)

目　　錄
第 1章 從Excel講起 1
1.1 Excel在數(shù)據(jù)處理方面的局限性 1
1.1.1 數(shù)據(jù)處理能力有限 1
1.1.2 數(shù)據(jù)處理透明性不夠 1
1.1.3 數(shù)據(jù)處理緊湊性不足 2
1.2 BI與智能化Excel 2
1.2.1 BI 3
1.2.2 智能化Excel 3
1.3 數(shù)據(jù)庫概念與數(shù)據(jù)模型 4
1.3.1 數(shù)據(jù)庫與數(shù)據(jù)表 4
1.3.2 事實表與維表 4
1.3.3 記錄與字段 4
1.3.4 查詢與連接 5
1.3.5 關(guān)系與數(shù)據(jù)模型 5
第 2章 Power Pivot與數(shù)據(jù)建模 6
2.1 Power Pivot簡介 6
2.2 Power Pivot窗口一覽 7
2.3 Power Pivot數(shù)據(jù)連接類型 9
2.3.1 從關(guān)系數(shù)據(jù)庫導(dǎo)入
數(shù)據(jù) 10
2.3.2 從文本文件導(dǎo)入數(shù)據(jù) 12
2.3.3 從Excel文件導(dǎo)入
數(shù)據(jù) 13
2.3.4 從剪貼板導(dǎo)入數(shù)據(jù) 16
2.3.5 從Power Query中導(dǎo)入
數(shù)據(jù) 16
2.4 多表數(shù)據(jù)模型：表間關(guān)系與跨表
透視 17
2.4.1 為數(shù)據(jù)模型創(chuàng)建Excel
智能表 18
2.4.2 添加智能表到數(shù)據(jù)
模型 18
2.4.3 創(chuàng)建表間關(guān)系 20
2.4.4 管理表間關(guān)系 21
2.4.5 跨表透視 24
2.5 Power Pivot展示窗口：數(shù)據(jù)
透視表與數(shù)據(jù)透視圖 26
2.5.1 Power Pivot與數(shù)據(jù)
透視表 26
2.5.2 Power Pivot與數(shù)據(jù)
透視圖 32
第3章 DAX：萬物始于“篩選” 36
3.1 從隱式度量值講起 36
3.1.1 顯示隱式度量值 36
3.1.2 度量值的創(chuàng)建方法 37
3.1.3 度量值的重要特性：
可復(fù)用性 40
3.1.4 在計算列中使用DAX
函數(shù) 41
3.2 動態(tài)計算的核心：上下文 42
3.2.1 篩選上下文 43
3.2.2 行上下文 44
3.2.3 上下文轉(zhuǎn)換 45
3.2.4 篩選傳遞 46
3.3 數(shù)據(jù)模型的基石：關(guān)系 47
3.3.1 關(guān)系的類型 47
3.3.2 數(shù)據(jù)模型的結(jié)構(gòu) 48
3.3.3 查找表和數(shù)據(jù)表 50
3.4 以SUM()函數(shù)為代表的聚合
函數(shù) 50
3.4.1 基礎(chǔ)聚合函數(shù) 51
3.4.2 與計數(shù)相關(guān)的聚合
函數(shù) 52
3.5 以SUMX()函數(shù)為代表的迭代
函數(shù) 53
3.5.1 SUMX()函數(shù) 53
3.5.2 RANKX()函數(shù) 54
3.5.3 CONCATENATEX()
函數(shù) 55
3.5.4 FILTER()函數(shù) 56
3.6 CALCULATE()函數(shù) 56
3.6.1 增加篩選條件 57
3.6.2 修改篩選條件 57
3.6.3 移除篩選條件 58
3.6.4 CALCULATE()函數(shù)的
兩個核心要點 59
3.7 為什么ALL()函數(shù)可以移除篩選
條件 59
3.8 ALL()函數(shù)與VALUES()函數(shù) 60
3.9 DAX代碼書寫技巧與方法 61
3.9.1 DAX函數(shù)輸入技巧：智能
填充 61
3.9.2 DAX代碼格式化規(guī)則 62
3.9.3 DAX代碼注釋方法 63
3.9.4 在DAX中使用VAR/
RETURN 64
3.10 時間智能函數(shù)與時間智能
計算 64
3.10.1 日期表 64
3.10.2 按列排序 67
3.10.3 時間智能函數(shù)的底層
邏輯 68
3.10.4 時間智能函數(shù)的
分類 70
3.10.5 計算月、季度、年初
至今 70
3.10.6 計算去年同期 71
3.10.7 計算指定時間間隔 72
3.11 數(shù)據(jù)透視表“殺手”：CUBE
函數(shù) 74
3.11.1 一鍵轉(zhuǎn)換為公式 74
3.11.2 CUBE函數(shù)輸入技巧 76
3.11.3 CUBEVALUE()與
CUBEMEMBER()函數(shù) 77
3.11.4 CUBEVALUE()與切片器
聯(lián)動 78
第4章 Power Query與數(shù)據(jù)清洗 80
4.1 Power Query簡介 80
4.2 Power Query編輯器界面一覽 81
4.3 Power Query連接的數(shù)據(jù)類型 83
4.3.1 從文本/CSV 84
4.3.2 自網(wǎng)站 85
4.3.3 來自表格/區(qū)域 86
4.3.4 來自數(shù)據(jù)庫 86
4.4 數(shù)據(jù)清洗實戰(zhàn) 87
4.4.1 數(shù)據(jù)轉(zhuǎn)換 87
4.4.2 數(shù)據(jù)合并 100
4.4.3 數(shù)值計算 109
4.4.4 能Excel所不能 112
4.5 批量合并文件 121
4.5.1 合并多個規(guī)范的
數(shù)據(jù)表 121
4.5.2 合并多個規(guī)范的
工作簿 125
4.5.3 Excel.Workbook()函數(shù) 127
第5章 M語言入門 130
5.1 結(jié)構(gòu)化數(shù)據(jù) 130
5.1.1 列表 131
5.1.2 記錄 131
5.1.3 表 132
5.1.4 列表、記錄與表的關(guān)系 133
5.1.5 查詢引用與深化實戰(zhàn)
案例 137
5.2 數(shù)據(jù)刷新的起點：查詢 138
5.2.1 查詢基本操作 138
5.2.2 查詢與查詢步驟 139
5.2.3 刷新查詢 141
5.3 認(rèn)識M函數(shù) 142
5.3.1 M函數(shù)基本規(guī)范 142
5.3.2 M函數(shù)參數(shù)分解 144
5.3.3 M函數(shù)幫助信息 144
5.4 常用的M函數(shù)應(yīng)用詳解 146
5.4.1 Table類函數(shù) 146
5.4.2 List類函數(shù) 147
5.4.3 Text類函數(shù) 152
5.4.4 批量轉(zhuǎn)換函數(shù) 155
5.5 M函數(shù)輕松學(xué)：移花接木 157
5.6 M函數(shù)輕松學(xué)：拆解參數(shù) 160
5.7 M函數(shù)輕松學(xué)：多層嵌套 163
5.8 M函數(shù)輕松學(xué)：庖丁解牛 164
5.9 M函數(shù)綜合實戰(zhàn)：批量合并指定
位置數(shù)據(jù) 168
5.9.1 Table.Skip()函數(shù)實戰(zhàn)
應(yīng)用 169
5.9.2 Table.SelectColumns()函數(shù)
實戰(zhàn)應(yīng)用 171
5.9.3 #table()函數(shù)實戰(zhàn)
應(yīng)用 173
5.10 M函數(shù)綜合實戰(zhàn)：智能取數(shù)
系統(tǒng) 177
5.10.1 創(chuàng)建映射表 177
5.10.2 加載到Power Query，
篩選非空行 177
5.10.3 選擇列：Table.
SelectColumns() 178
5.10.4 重命名列：Table.
RenameColumns() 179
5.10.5 拉鏈函數(shù)：List.Zip() 179
第6章 M語言進(jìn)階 181
6.1 let ... in ...語句 181
6.2 M語言中的運算符 182
6.2.1 普通運算符 182
6.2.2 特殊運算符 183
6.3 M語言中的條件判斷 183
6.3.1 列篩選條件 184
6.3.2 if... then...語句 184
6.3.3 try... otherwise...語句 185
6.4 M語言中的自定義函數(shù) 186
6.4.1 自定義函數(shù)：（）=> 186
6.4.2 “即插即用”的匿名
函數(shù) 188
6.5 M語言的“語法糖”：each
和 _ 189
6.6 自定義函數(shù)綜合實戰(zhàn)：批量合
并不規(guī)范文件 190
6.7 自定義函數(shù)綜合實戰(zhàn)：表格降維
技巧 194
6.7.1 2×1層級結(jié)構(gòu)化
表格 195
6.7.2 1×2層級結(jié)構(gòu)化表格 197
6.7.3 2×2層級結(jié)構(gòu)化表格 198
6.7.4 N×M層級結(jié)構(gòu)化
表格 201
第7章 Excel BI的進(jìn)階之路 205
7.1 從QAT到Excel BI 選項卡 205
7.2 Excel BI的5個實用小技巧 207
7.2.1 取消類型轉(zhuǎn)換 207
7.2.2 取消自動日期分組 208
7.2.3 減少使用關(guān)系檢測 209
7.2.4 設(shè)置默認(rèn)加載方式 210
7.2.5 修改返回最大記錄數(shù) 210
7.3 查詢分組與度量值表 211
7.3.1 查詢分組 211
7.3.2 度量值表 212