一次性学懂 Excel 中的 Power Query 和 Power Pivot 使用

传统的 Excel 单表虽然可以有 100 万行数据的承载量，但是在实际分析时，20 万行的数据就已经让传统的 Excel 非常吃力了。

但是，如果使用 Excel 中的 Power Query 和 Power Pivot 商务智能组件，即使是上百万行数据，也可以在短时间内快速完成处理和分析。

Power Query 在 Excel 和 Power BI Desktop 中都是内置组件，并且管理界面和知识体系保持了高度一致。

其实，Power BI 中的 Power Query 和 Power Pivot 最早是以 Excel 为载体的。

换句话说，Power BI 中的 Power Query 和 Power Pivot 组件的功能和语法等方面都是一样的。

Power Query 是用来做数据的获取和清洗的组合，Power Pivot 是用来做数据建模与计算分析的。

这两个组件分别对应强大的函数式语言：M 语言和 DAX 语言。

这两个组件在 Excel 和 Power BI 中是通用的，正是这两个内置组件，使得 Excel 这个传统的分析工具越来越商务化，越来越智能化。

相对于 Power BI，在 Excel 中使用 Power Query 和 Power Pivot 的应用场景更加灵活，人群更广、效率更高，能适应更多的应用场景。

那么，有没有一本书可以一次性讲解 Power Query 和 Power Pivot 在 Excel 中的使用呢？

答案是：《Excel 商务智能：Power Query 和 Power Pivot 数据清洗、建模与分析实战》。

通过学习这本书，你可以：

获得 1 种技能：智能高效的数据分析技能

掌握 2 种核心知识：M 函数和 DAX 函数

掌握 2 个分析工具：Excel 和 Power BI

内容简介

本书主要介绍 Excel 商务智能组件 Power Query 和 Power Pivot 在数据分析方面的应用。

全书共 11 章：

• 第 1 章介绍 Excel 中的 Power Query 和 Power Pivot 两大商务智能组件及其功能；

• 第 2 章至第 6 章介绍如何使用 Power Query 来获取数据并进行处理，主要包含 Power Query 的基本操作、M 函数和 M 公式的基础知识、常用的 M 函数，以及数据处理的综合案例；

• 第 7 章至第 11 章介绍如何使用 Power Pivot 进行数据建模和分析，主要包含 Power Pivot 的基本操作、数据模型的建立、多维数据集函数与 Power Pivot 数据模型、DAX 表达式的基础知识和进阶知识、常用的 DAX 函数，以及数据分析的综合案例。

本书紧贴实际应用场景，深入浅出地介绍了 Excel 商务智能组件强大的数据处理和建模分析能力。

为了方便读者学习，本书针对一些综合性强及存在难点的章节录制了配套视频。

通过阅读本书，读者可以更加高效地进行数据分析工作。

本书特色

01. 紧贴实际应用场景，介绍 Power Query 和 Power Pivot 在数据分析方面的应用（50+个实例）

02. 针对综合性强和难点章节配有视频（超过 25 节共 1G+视频讲解）

03. 免费入本书交流群，与作者互动。

根据本书封底“读者服务”提示，即可加入本书读者交流群，不懂随时问，作者免费在线为您答疑。

本书目录

<向上滑动查看更多详细目录>

第 1 章  Excel：你的职场生产力工具

1.1 你所不知道的 Excel 分析“利器”

1.2 从 Excel 到 Power BI，只需要一步

第 2 章  认识 Power Query 编辑器

2.1 初识 Power Query

2.2 编辑器管理界面介绍

2.3 创建查询的方法

2.4 数据源路径的修改与设置

2.5 数据上载与刷新

第 3 章  Power Query 的基本操作实例

3.1 入门基础知识

3.1.1 数据类型的设置

3.1.2 标题的升降设置

3.1.3 “转换”与“添加列”选项卡中的功能

3.2 删除行或列操作

3.2.1 选择列与删除列

3.2.2 删除行与保留行

3.2.3 通过筛选器删除行

3.3 添加列操作

3.3.1 简单快速地添加条件列

3.3.2 为行添加自定义序号

3.3.3 添加自定义列

3.4 拆分列与合并列操作

3.4.1 实例 1：按分隔符拆分列

3.4.2 实例 2：按字符数拆分列

3.4.3 实例 3：按位置拆分列

3.4.4 实例 4：其他拆分列的方法

3.4.5 合并列常用的方法

3.5 透视列与逆透视列操作

3.5.1 一维表和二维表

3.5.2 实例 1：一维表转二维表

3.5.3 实例 2：二维表转一维表

3.5.4 实例 3：含有多重行/列表头的数据清洗

3.6 提取文本值中指定字符的操作

3.6.1 实例 1：按指定的长度提取文本值中指定的字符

3.6.2 实例 2：按分隔符的位置提取文本值中指定的字符

3.7 数学运算和分组统计

3.7.1 聚合运算的操作

3.7.2 实例 1：活用“选择性粘贴”功能处理考试成绩

3.7.3 实例 2：使用分组统计功能快速计算各部门的数据

3.8 追加查询与合并查询

3.8.1 实例 1：使用追加查询批量合并多个 Excel 工作表数据

3.8.2 认识合并查询的 6 种类型

3.8.3 实例 2：使用合并查询完成各种数据匹配

第 4 章  M 函数和 M 公式基础入门

4.1 M 函数和 M 公式介绍

4.1.1 M 函数和 M 公式

4.1.2 主要的 M 函数类型

4.1.3 常用的数据类型

4.1.4 运算符

4.1.5 如何查看函数帮助

4.2 三大数据结构

4.2.1 列表

4.2.2 记录

4.2.3 表

4.2.4 数据结构的组合和深化

4.2.5 数据结构的扩展

4.3 数据结构之间的相互转换

4.3.1 List 和 Record 之间的转换

4.3.2 Table 和 List 之间的转换

4.3.3 Table 和 Record 之间的转换

4.4 M 公式中常用的语句

4.4.1 let…in…语句

4.4.2 条件分支语句

4.4.3 容错语句 try…otherwise…

4.4.4 each _与(x)=>的关系

4.4.5 为公式添加注释

第 5 章  常用的 M 函数实战详解

5.1 各种数据类型之间的相互转换

5.1.1 将值转换为文本

5.1.2 将值转换为数值

5.1.3 将值转换为日期

5.2 List 和 Table 的批量转换实战

5.2.1 批量转换函数 List.Transform 的实际应用

5.2.2 批量转换函数 Table.TransformColumns 的实际应用

5.3 获取和删除各种数据实战

5.3.1 使用 Table.Skip 函数和 Table.SelectRows 函数筛选行

5.3.2 获取和删除指定文本值中的指定字符

5.3.3 获取和删除列表中的元素

5.4 各种数据结构的拆分、合并、截取和替换实战

5.4.1 实例 1：表的拆分与合并应用

5.4.2 实例 2：列表的拆分与合并应用

5.4.3 实例 3：拆分和提取文本值中的数值并求和

5.4.4 对文本值进行截取的函数

5.4.5 实例 4：批量替换和有条件地批量替换文本值

5.4.6 实例 5：使用 List.Zip 函数批量更换标题及制作工资条

5.5 判断文本值和列表中是否包含指定的内容

5.5.1 实例 1：对任意组合的条件值求和

5.5.2 实例 2：根据标准答案计算多选题的得分

5.6 分组函数 Table.Group 及其应用

5.6.1 Table.Group 函数和常规分组计算

5.6.2 实例：条件分组计算和数据清洗整理获奖数据

5.7 参数与自定义函数

5.7.1 参数的设置方法

5.7.2 实例：创建和调用自定义函数将一列拆分为多列

第 6 章  Power Query 综合实战

6.1 数据获取综合实战

6.1.1 实例 1：获取并合并 Excel 工作簿中的多个工作表的数据

6.1.2 实例 2：获取并合并多个文件夹下的 Excel 工作簿中的数据

6.1.3 实例 3：获取网页中的表格数据

6.1.4 实例 4：获取 CSV 或 TXT 文件数据

6.1.5 实例 5：实时获取数据库中的数据

6.2 数据转换综合实战

6.2.1 实例 1：将复杂的二维调薪表转换为一维明细表

6.2.2 实例 2：高效快速地清洗零乱的考勤数据

6.2.3 实例 3：同时拆分组合的供应商中文名称和英文名称

6.2.4 实例 4：批量提取 Excel 工作簿中不规则的防疫数据

第 7 章  认识 Power Pivot 与 DAX

7.1 Power Pivot 介绍

7.1.1 认识 Power Pivot

7.1.2 从数据透视表的不重复计算说起

7.1.3 在 Excel 中加载 Power Pivot

7.1.4 认识 Power Pivot 的管理界面

7.2 Power Pivot 的数据获取方式

7.2.1 从表格/区域和 Power Query 导入数据

7.2.2 从 Excel 文件导入数据

7.2.3 从文本文件导入数据

7.2.4 从剪切板导入数据

7.2.5 从数据库导入数据

7.3 认识数据分析表达式 DAX

7.3.1 常用的 DAX 函数类型

7.3.2 DAX 中的数据类型与运算符

7.3.3 创建 DAX 表达式时表和列的引用方式

第 8 章  Power Pivot 和 DAX 基础知识

8.1 理解计算列与度量值

8.1.1 依附于数据表的计算列

8.1.2 能适应各种环境的度量值

8.1.3 度量值与数据透视表的计算字段

8.1.4 如何选择度量值与计算列

8.1.5 管理度量值

8.2 数据模型与表间关系

8.2.1 理解 Power Pivot 的数据模型

8.2.2 多表操作时表间关系的建立和管理

8.2.3 表、列和度量值的隐藏

8.2.4 LOOKUPVALUE 函数介绍

8.2.5 RELATED 函数与 RELATEDTABLE 函数介绍

8.3 DAX 的基础函数

8.3.1 以 SUMX 为代表的结尾带有 X 的聚合函数

8.3.2 筛选函数 FILTER 和逻辑运算符

8.3.3 DAX 中最重要的 CALCULATE 函数

8.3.4 CALCULATE 函数的筛选器的选择

8.3.5 VALUES 函数和 DISTINCT 函数

8.3.6 初识 ALL 函数和 ALLEXCEPT 函数

8.4 初识计值上下文

8.4.1 初识筛选上下文

8.4.2 创建筛选上下文

8.4.3 初识行上下文

8.4.4 行上下文转换

8.5 CALCULATE 函数的调节器

8.5.1 删除筛选器的 ALL 函数

8.5.2 追加筛选的 KEEPFILTERS 函数

8.5.3 激活关系的 USERELATIONSHIP 函数

第 9 章  DAX 进阶知识和常见应用

9.1 Power Pivot 和数据透视表

9.1.1 实例 1：在数据透视表中使用自定义排序：按列排序

9.1.2 实例 2：在数据透视表中创建 KPI 规则——设置“条件格式”

9.2 在 DAX 中使用 VAR 变量

9.2.1 关于 VAR 变量

9.2.2 使用变量时应该避免的错误

9.3 常见的 DAX 函数和实际案例应用

9.3.1 实例 1：使用 CONCATENATEX 函数进行文本值透视

9.3.2 实例 2：使用 ALLSELECTED 函数动态地计算各类占比

9.3.3 实例 3：使用 RANKX 函数动态地计算各类排名

9.3.4 实例 4：自定义数据透视表标题行完成复杂的报表

9.4 DAX 作为查询工具的实际应用

9.4.1 数据查询和 EVALUATE

9.4.2 实例 1：使用 ADDCOLUMNS 函数建立查询表

9.4.3 实例 2：使用 SELECTCOLUMNS 函数建立查询表

9.4.4 实例 3：使用 SUMMARIZE 和 SUMMARIZECOLUMNS 函数分组汇总数据

9.5 Power Pivot 数据模型与多维数据集函数

9.5.1 认识 CUBE 类函数

9.5.2 实例 1：使用 CUBEVALUE 函数提取 Power Pivot 数据模型中的数据

9.5.3 实例 2：使用“切合器+CUBEVALUE 函数”动态提取 Power Pivot 数据模型中的数据

第 10  章时间智能计算

10.1 认识时间智能函数和日期表

10.1.1 时间智能函数与日期函数

10.1.2 日期表的创建与标记

10.1.3 与时间智能函数相关的常用计算指标

10.2 常见的时间智能计算

10.2.1 实例 1：年初、季初与月初至今计算

10.2.2 实例 2：各类同比与环比的计算

10.2.3 实例 3：动态移动平均分析模型

第 11 章  Power Pivot 综合实战

11.1 实例 1：TOP-N 门店销售和利润贡献度分析模型

11.2 实例 2：折扣比例分组（分区间）分析模型

11.3 实例 3：动态 ABC 分类分析模型（帕累托分析模型）

11.4 实例 4：RFM 客户价值分析模型

11.5 实例 5：员工在职、入职、离职和离职率的计算模型

大咖力荐

20 位 Excel 和 Power BI 大咖联袂力荐，既说明了 Power Query 和 Power Pivot 在数据清洗、建模与分析方面的重要性和前沿性，也说明了各位大佬对本书的内容的认可！

适读人群

本书适合有一定基础的 Excel 用户和 Power BI 用户阅读，可以作为销售、客服、采购、仓储、物流、人力资源、财务、电商等相关岗位职场人士的参考用书，也适合 Excel 爱好者、数据“发烧友”、在校大学生及经常和数据打交道的朋友阅读。

扫码了解本书详情