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
上载
之后就能得到想要的结果了
刷新