Excel 進(jìn)階指南：Power Pivot 與 Power Query 實(shí)戰(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ù)庫(kù)概念與數(shù)據(jù)模型 4
1.3.1 數(shù)據(jù)庫(kù)與數(shù)據(jù)表 4
1.3.2 事實(shí)表與維表 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簡(jiǎn)介 6
2.2 Power Pivot窗口一覽 7
2.3 Power Pivot數(shù)據(jù)連接類型 9
2.3.1 從關(guān)系數(shù)據(jù)庫(kù)導(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 在計(jì)算列中使用DAX
函數(shù) 41
3.2 動(dòng)態(tài)計(jì)算的核心：上下文 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 與計(jì)數(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ù)的
兩個(gè)核心要點(diǎn) 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í)間智能函數(shù)與時(shí)間智能
計(jì)算 64
3.10.1 日期表 64
3.10.2 按列排序 67
3.10.3 時(shí)間智能函數(shù)的底層
邏輯 68
3.10.4 時(shí)間智能函數(shù)的
分類 70
3.10.5 計(jì)算月、季度、年初
至今 70
3.10.6 計(jì)算去年同期 71
3.10.7 計(jì)算指定時(shí)間間隔 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)動(dòng) 78
第4章 Power Query與數(shù)據(jù)清洗 80
4.1 Power Query簡(jiǎn)介 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ù)庫(kù) 86
4.4 數(shù)據(jù)清洗實(shí)戰(zhàn) 87
4.4.1 數(shù)據(jù)轉(zhuǎn)換 87
4.4.2 數(shù)據(jù)合并 100
4.4.3 數(shù)值計(jì)算 109
4.4.4 能Excel所不能 112
4.5 批量合并文件 121
4.5.1 合并多個(gè)規(guī)范的
數(shù)據(jù)表 121
4.5.2 合并多個(gè)規(guī)范的
工作簿 125
4.5.3 Excel.Workbook()函數(shù) 127
第5章 M語(yǔ)言入門 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 查詢引用與深化實(shí)戰(zhàn)
案例 137
5.2 數(shù)據(jù)刷新的起點(diǎn)：查詢 138
5.2.1 查詢基本操作 138
5.2.2 查詢與查詢步驟 139
5.2.3 刷新查詢 141
5.3 認(rèn)識(shí)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ù)綜合實(shí)戰(zhàn)：批量合并指定
位置數(shù)據(jù) 168
5.9.1 Table.Skip()函數(shù)實(shí)戰(zhàn)
應(yīng)用 169
5.9.2 Table.SelectColumns()函數(shù)
實(shí)戰(zhàn)應(yīng)用 171
5.9.3 #table()函數(shù)實(shí)戰(zhàn)
應(yīng)用 173
5.10 M函數(shù)綜合實(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語(yǔ)言進(jìn)階 181
6.1 let ... in ...語(yǔ)句 181
6.2 M語(yǔ)言中的運(yùn)算符 182
6.2.1 普通運(yùn)算符 182
6.2.2 特殊運(yùn)算符 183
6.3 M語(yǔ)言中的條件判斷 183
6.3.1 列篩選條件 184
6.3.2 if... then...語(yǔ)句 184
6.3.3 try... otherwise...語(yǔ)句 185
6.4 M語(yǔ)言中的自定義函數(shù) 186
6.4.1 自定義函數(shù)：（）=> 186
6.4.2 “即插即用”的匿名
函數(shù) 188
6.5 M語(yǔ)言的“語(yǔ)法糖”：each
和 _ 189
6.6 自定義函數(shù)綜合實(shí)戰(zhàn)：批量合
并不規(guī)范文件 190
6.7 自定義函數(shù)綜合實(shí)戰(zhàn)：表格降維
技巧 194
6.7.1 2×1層級(jí)結(jié)構(gòu)化
表格 195
6.7.2 1×2層級(jí)結(jié)構(gòu)化表格 197
6.7.3 2×2層級(jí)結(jié)構(gòu)化表格 198
6.7.4 N×M層級(jí)結(jié)構(gòu)化
表格 201
第7章 Excel BI的進(jìn)階之路 205
7.1 從QAT到Excel BI 選項(xiàng)卡 205
7.2 Excel BI的5個(gè)實(shí)用小技巧 207
7.2.1 取消類型轉(zhuǎn)換 207
7.2.2 取消自動(dòng)日期分組 208
7.2.3 減少使用關(guān)系檢測(cè) 209
7.2.4 設(shè)置默認(rèn)加載方式 210
7.2.5 修改返回最大記錄數(shù) 210
7.3 查詢分組與度量值表 211
7.3.1 查詢分組 211
7.3.2 度量值表 212