OfficeExcel（5）

OfficeExcel（5）——Powerquery

---

一、Power Query，可以搜索数据源，创建连接，可调整数据（例如删除列、更改数据类型或合并表格）用于创建报表。

1、一个小案例快速了解Power Query
需求：将一店和二店两个工作簿的数据整合到一张工作表，并统计出各销售代表每个月的业绩。
①新建查询——从文件——从工作簿——导入一店——编辑

②新建源——文件——Excel——导入二店

③这样一店和二店的数据都导入进来了，再进行数据处理

④删除前两行空行

⑤将第一行用作标题
数据就处理好了

⑥将二店的数据追加到一店上，新表名（门店销售记录）

⑦关闭并上载至

• 源数据发生更改，结果也会发生更改。（打开一店，更改数据，关闭保存，在回到门店销售记录表【数据——显示查询】，可看到门店销售记录表也发生更改）
• 

---

二、追加和行列功能

例：1、删除空行

2、删除间隔行

关闭并上载至现有工作表，这样，可以刷新数据

更改数据

例：删除间隔行——将第一行用作标题得到如图所示

再向下填充

---

三、数据格式的转换

标注黄色的异常单元格。
1、处理异常行高：格式——修整（只能在文本格式下处理前后空格）

2、统一出生年份：先把数据格式统一改为文本

再进行替换

3、English Name：拆分列——按分隔符——修整——合并列——格式——每个字词首字母大写

• 注意合并列时选择的顺序
• 注意事项
  1.大小写：Power Query对英文字母的大小写敏感
  2.修整：在所选列的每个文本单元格中删除前导空格和尾随空格
  使用“修整”功能时需注意，尽量不要对整张表格进行一次性修整，由于数据格式不同，容易出现报错
  3.清除：清除所选列中的非输入字符（例如：回车）
  4.添加前缀/后缀：在所选列单元格的前/后增加指定的字符，相当于合并

---

四、数据的拆分合并

1、身份证号数据改为文本
2、添加列菜单下，重复列

3、提取姓、名：转换菜单下，拆分列，按字符拆分

4、获取出生年份：添加列菜单下，选中身份证号，提取——范围——再添加后缀（年）

• 注意起始索引

5、判断性别：选中身份证号，提取——范围，对提取出来的列数据格式改为数字整数，转换——提取（利用奇偶性判断）
奇数为男，偶数为女！

!

再数据类型改为文本：

再进行替换：

6、添加尊称：重复性别列，把男替换为先生，女替换为女士，再合并姓、和此重复的列

7、首要兴趣爱好：对兴趣爱好列进行重复列，再按字符（\）拆分

---

五、删除重复项

原理：在排序后，我们看到的排序效果是前台显示出来的，但后台运行的并不是排序后的结果，真正后台运行的结果是清除排序后我们所看到的。因此，需要在排序后，进行转换菜单下的数据类型检测。
1、客户首次购买分析
步骤1：对下单日期进行升序排列

步骤2：检测数据类型

步骤3：对客户名称删除重复项

2、客户最大订单分析
同需求1.步骤1改为对金额进行降序排列，其他步骤不变

3、多次购买客户分析
步骤：对客户名称 ，保留重复项

---

六、转置和透视、逆透视

1、转置
步骤1：导入表之后，把标题作为第一行

步骤2：转置

步骤3:把第一行作为标题，此时月份数据格式发生变化。删除更改的类型这一步!

或者，对月份列进行数据类型转换!

之后得到结果

2、逆透视（二维表转一维表）
源数据如图：

选中1月到12月共12列，点击逆透视

3、透视（一维表转二维表）

选中月份，列透视

得到二维表

同理，可透视项目列

例子： 对下表进行透视

步骤

结果