OfficeExcel（6）

OfficeExcel（6）——Powerquery

---

一、分组依据

透视：将数据按照某一列或几列进行汇总
1、

转换菜单——分组依据

得到根据客户的销售总额

2、基于每名客户的订单进行销售总额的分析

结果：

3、分析订单中有一个产品还是多个产品
转换菜单下——分组依据

得到结果，可点击小箭头查看相关产品个数

• 注意扩展与聚合的区别

4、索引列

添加列

例如：
自定义列

再例如：
自定义列

得到金额列

再例如：
自定义列
先把计数.订单编号的计数列数据类型改为文本

再自定义列，用公式

例：标记出每张订单是单个产品还是多个产品
if [计数.订单编号 的计数]=1 then “单个产品的订单” else “多个产品的订单”

求多个产品订单数量和多个产品订单数量
对上面求得的列，进行分组依据

得到结果

---

二、日期和时间的整理功能说明

功能	说明
年限	现在（now）和所选日期之间的持续时间
仅限日期	提取日期部分
分析	从文本里提取日期
年	提取年
年开始、年结束	提取对应的年份开始日期或结束日期
一个月的某些	此月份共多少天
每周的某一日	周几
一年的某一周	本年度的周数
减去天数	两列日期相减
最早、最新	多列日期中保留最早、最晚的一天

---

三、数学运算

功能	说明
加、减、乘、除
除（整数）	商
取模	余数
绝对值	向上舍入、向下舍入、保留位数
奇数、偶数	奇数偶数判断

---

四、追加查询和文件汇总

1、纵向的追加
把格式一样的数据追加到同一表格里
步骤：①从表格上传第一份数据后，选择仅连接

②两张表如图所示

③追加查询

④追加得到结果

注意：若改变表一的列字段位置

合并后的表也发生变化

若改变金额字段名称（改为收入）

则发生错误。

如何处理？
方法①

方法②

改后得到如图所示结果

• 第一张表，列的顺序决定了追加后列的名称顺序
• 只要列名称一样，顺序不一样没关系，Power query可自动识别，一第一张表的列的顺序为准
• 追加的列名必须一致
• 某张表里独有的列会单独呈现

五、多文件汇总

1、从Excel工作簿中汇总
不需要全部导入，只导入安徽

删除后面三步

扩展所需的内容

删除多余的行

两种方法：①直接在订单编号列，不勾选订单编号

②对销售金额列，数据类型转换成小数，出现错误行，再删除错误行

2、从文件夹中汇总多个Excel文件
步骤：①导入数据

②保留前2列，删除其余列

③利用函数进行扩展

• 注意此函数：Excel.Workbook([Content]) 对大小写敏感

④再扩展列

⑤删除多余列

⑥最后在扩展得到结果

⑦最后删除多余的行

3、从文件夹合并多个CSV文件
步骤同上面，在进行到第三步时，出现错误

此时，只需对content列右上方小箭头双击，确定，得到结果

---

六、合并查询

对表格的横向合并，类似于Excel中的是Vlookup函数
1、基于订单信息表，合并省份区域表
合并查询

扩展区域

结果

2、基于订单信息表，合并折扣表
合并查询
因为每个区域折扣不一样，合并查询要选择两列，注意选择列的顺序

进行扩展，得到结果

3、合并查询里的连接种类
蓝框：邀请的人，黄框：参加的人
6种情况：

①显示ABCDE

②显示DEFG

③显示ABCDEFG

④显示DE

⑤显示ABC

⑥显示FG

---

七、中国式排名

1、复制销售排名表，添加作为辅助表
在***辅助表***中，对销售额列进行降序排序，检测数据类型，删除重复项，添加索引列（从1开始）

2、在销售排名表中，追加查询

扩展索引后得到中国式排名

---

八、分组式中国排名

1、复制销售排名表，添加作为辅助表1，对辅助表1，对部门列进行升序排序，检测数据类型，选中部门列和销售额列，删除重复项，添加索引列

得到每个部门唯一销售额

2、复制辅助表1作为辅助表2，对辅助表2，进行分组依据

3、对辅助表1，进行合并查询

得到如图所示

添加自定义列

在销售表排名基础上合并辅助表1

最后，部门和分组都按升序排列

九、生成笛卡尔积表

1、在月份表中，删除自动的步骤（更改的类型），再添加索引值为1的列

2、在表2中添加和步骤1一样的索引值为1 的列

3、在表2中合并查询，得到结果

---

十、多行属性合并

1、复制表2作为辅助表2（2），对辅助表（2）进行分组依据

2、删除金额列，添加索引列

3、对销售代表列添加前缀""

4、对索引列进行透视

所得结果

5、合并列

6、再拆分列，删除无效列，得到结果

7、在表2中进行合并查询，扩展销售额，得到结果

• 存在问题：刷新后结果不是想要的

原因：在步骤——合并的列，打开高级编辑器

• 如何解决？

出现报错

打开高级编辑器，找出错误位置

其中：M函数 List.Sumg改为Text.Cmbine

上载

之后就能得到想要的结果了

刷新