Excel 進階指南：Power Pivot 與 Power Query 實戰

擁有微軟辦公軟件國際認證（Microsoft.Office.Specialist，MOS）大師級（Master）證書；

擁有10年銀行從業經驗；

公眾號“ExcelBI星球”主理人；

Excel、Power BI相關技術圖書的作者；

喜歡閱讀，熱衷於分享數據分析、數據可視化相關技術。

目　　錄

第 1章 從Excel講起 1

1.1 Excel在數據處理方面的局限性 1

1.1.1 數據處理能力有限 1

1.1.2 數據處理透明性不夠 1

1.1.3 數據處理緊湊性不足 2

1.2 BI與智能化Excel 2

1.2.1 BI 3

1.2.2 智能化Excel 3

1.3 數據庫概念與數據模型 4

1.3.1 數據庫與數據表 4

1.3.2 事實表與維表 4

1.3.3 記錄與字段 4

1.3.4 查詢與連接 5

1.3.5 關系與數據模型 5

第 2章 Power Pivot與數據建模 6

2.1 Power Pivot簡介 6

2.2 Power Pivot窗口一覽 7

2.3 Power Pivot數據連接類型 9

2.3.1 從關系數據庫導入

數據 10

2.3.2 從文本文件導入數據 12

2.3.3 從Excel文件導入

數據 13

2.3.4 從剪貼板導入數據 16

2.3.5 從Power Query中導入

數據 16

2.4 多表數據模型：表間關系與跨表

透視 17

2.4.1 為數據模型創建Excel

智能表 18

2.4.2 添加智能表到數據

模型 18

2.4.3 創建表間關系 20

2.4.4 管理表間關系 21

2.4.5 跨表透視 24

2.5 Power Pivot展示窗口：數據

透視表與數據透視圖 26

2.5.1 Power Pivot與數據

透視表 26

2.5.2 Power Pivot與數據

透視圖 32

第3章 DAX：萬物始於“篩選” 36

3.1 從隱式度量值講起 36

3.1.1 顯示隱式度量值 36

3.1.2 度量值的創建方法 37

3.1.3 度量值的重要特性：

可復用性 40

3.1.4 在計算列中使用DAX

函數 41

3.2 動態計算的核心：上下文 42

3.2.1 篩選上下文 43

3.2.2 行上下文 44

3.2.3 上下文轉換 45

3.2.4 篩選傳遞 46

3.3 數據模型的基石：關系 47

3.3.1 關系的類型 47

3.3.2 數據模型的結構 48

3.3.3 查找表和數據表 50

3.4 以SUM()函數為代表的聚合

函數 50

3.4.1 基礎聚合函數 51

3.4.2 與計數相關的聚合

函數 52

3.5 以SUMX()函數為代表的疊代

函數 53

3.5.1 SUMX()函數 53

3.5.2 RANKX()函數 54

3.5.3 CONCATENATEX()

函數 55

3.5.4 FILTER()函數 56

3.6 CALCULATE()函數 56

3.6.1 增加篩選條件 57

3.6.2 修改篩選條件 57

3.6.3 移除篩選條件 58

3.6.4 CALCULATE()函數的

兩個核心要點 59

3.7 為什麼ALL()函數可以移除篩選

條件 59

3.8 ALL()函數與VALUES()函數 60

3.9 DAX代碼書寫技巧與方法 61

3.9.1 DAX函數輸入技巧：智能

填充 61

3.9.2 DAX代碼格式化規則 62

3.9.3 DAX代碼註釋方法 63

3.9.4 在DAX中使用VAR/

RETURN 64

3.10 時間智能函數與時間智能

計算 64

3.10.1 日期表 64

3.10.2 按列排序 67

3.10.3 時間智能函數的底層

邏輯 68

3.10.4 時間智能函數的

分類 70

3.10.5 計算月、季度、年初

至今 70

3.10.6 計算去年同期 71

3.10.7 計算指定時間間隔 72

3.11 數據透視表“殺手”：CUBE

函數 74

3.11.1 一鍵轉換為公式 74

3.11.2 CUBE函數輸入技巧 76

3.11.3 CUBEVALUE()與

CUBEMEMBER()函數 77

3.11.4 CUBEVALUE()與切片器

聯動 78

第4章 Power Query與數據清洗 80

4.1 Power Query簡介 80

4.2 Power Query編輯器界面一覽 81

4.3 Power Query連接的數據類型 83

4.3.1 從文本/CSV 84

4.3.2 自網站 85

4.3.3 來自表格/區域 86

4.3.4 來自數據庫 86

4.4 數據清洗實戰 87

4.4.1 數據轉換 87

4.4.2 數據合並 100

4.4.3 數值計算 109

4.4.4 能Excel所不能 112

4.5 批量合並文件 121

4.5.1 合並多個規範的

數據表 121

4.5.2 合並多個規範的

工作簿 125

4.5.3 Excel.Workbook()函數 127

第5章 M語言入門 130

5.1 結構化數據 130

5.1.1 列表 131

5.1.2 記錄 131

5.1.3 表 132

5.1.4 列表、記錄與表的關系 133

5.1.5 查詢引用與深化實戰

案例 137

5.2 數據刷新的起點：查詢 138

5.2.1 查詢基本操作 138

5.2.2 查詢與查詢步驟 139

5.2.3 刷新查詢 141

5.3 認識M函數 142

5.3.1 M函數基本規範 142

5.3.2 M函數參數分解 144

5.3.3 M函數幫助信息 144

5.4 常用的M函數應用詳解 146

5.4.1 Table類函數 146

5.4.2 List類函數 147

5.4.3 Text類函數 152

5.4.4 批量轉換函數 155

5.5 M函數輕松學：移花接木 157

5.6 M函數輕松學：拆解參數 160

5.7 M函數輕松學：多層嵌套 163

5.8 M函數輕松學：庖丁解牛 164

5.9 M函數綜合實戰：批量合並指定

位置數據 168

5.9.1 Table.Skip()函數實戰

應用 169

5.9.2 Table.SelectColumns()函數

實戰應用 171

5.9.3 #table()函數實戰

應用 173

5.10 M函數綜合實戰：智能取數

系統 177

5.10.1 創建映射表 177

5.10.2 加載到Power Query，

篩選非空行 177

5.10.3 選擇列：Table.

SelectColumns() 178

5.10.4 重命名列：Table.

RenameColumns() 179

5.10.5 拉鏈函數：List.Zip() 179

第6章 M語言進階 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語言中的自定義函數 186

6.4.1 自定義函數：（）=> 186

6.4.2 “即插即用”的匿名

函數 188

6.5 M語言的“語法糖”：each

和 _ 189

6.6 自定義函數綜合實戰：批量合

並不規範文件 190

6.7 自定義函數綜合實戰：表格降維

技巧 194

6.7.1 2×1層級結構化

表格 195

6.7.2 1×2層級結構化表格 197

6.7.3 2×2層級結構化表格 198

6.7.4 N×M層級結構化

表格 201

第7章 Excel BI的進階之路 205

7.1 從QAT到Excel BI 選項卡 205

7.2 Excel BI的5個實用小技巧 207

7.2.1 取消類型轉換 207

7.2.2 取消自動日期分組 208

7.2.3 減少使用關系檢測 209

7.2.4 設置默認加載方式 210

7.2.5 修改返回最大記錄數 210

7.3 查詢分組與度量值表 211

7.3.1 查詢分組 211

7.3.2 度量值表 212