Excel之Power Query

Power Query是一个Excel插件，某些版本也可能自带了这个功能，我们使用power query处理数据的大致流程是新建查询-处理数据-上载至工作表，支持实时更新，我们的学习重点在处理数据的具体流程这一块！！

案例一:多工作表汇总

材料:一个多工作表带合并的工作簿,一个新建工作簿

步骤:

打开新建工作簿
数据-获取数据-工作簿-导入多工作表的工作簿
随便点一个表-点击转换数据
把之前的步骤删了直到只有一个源
把公式倒数第二个参数改为true(使用标题行)
点击data域展开-不勾选使用原始列名作为前缀
完成并上载

注意是单向的对新工作簿的修改并不会影响原来的文件

案例二:多工作簿汇总

材料:一个多工作簿文件夹,一个新建工作簿

步骤:

打开新建工作簿
数据-获取数据-文件夹-导入文件夹
此时会有binary文件一个binary对应一个工作簿
转换数据-为了区分文件进行适当的拆分列-单元格格式转换-替换-操作
点开binary-如果有n个数据表就显示n个字段-弹出代码Excel-Workbook(Content)其实这是打开工作簿的函数
我们退到上一级-自定义列-对列调用函数Excel-Workbook(Content)-这样就对每个binary对象调用了此函数也就是所有工作簿都被打开-数据都进来了
此时table都进来了-table扩展-转换-点击将第一行作为标题
适当命名-上载即可

案例三:自网站获取数据

材料:一个工作簿

步骤:

打开新建工作簿
数据-获取数据-自网站
可能会有多个table进来-我们选中我们想要的table转换数据
筛选数据-处理-上载

案例四:分组统计

材料:一个工作簿

生活中遇到的求某组最大值、最小值、求和、平均等等这种使用分组聚合的案例都可以通过这个功能实现

类似excel函数maxif,countif,minif,sumif,averageif

步骤:

打开新建工作簿
数据-获取数据-导入工作簿
转换-分组依据-如果是多条件比如在某地某时-则需要高级设置
选择合适的聚合函数(一次可以聚合多个)
完成-上载

案例五:筛选模块

Power Query中的筛选模块比较大

一、按范围保留行

主页-保留行/删除行即可实现

二、间隔删除

主页-删除行-删除间隔行 它的参数很有意思从第几行开始-删除几行-保留几行这是一个循环比如第1行开始删除1行保留1行，剩下的就是偶数列

三、基础筛选(注意excel中and优先级比or高)

文本筛选
1. 点击列旁边的筛选器
2. 选择文本筛选器

text.startswith([列名],“”)可以返回列名中以指定字符串开头的项
text.length([列名])返回长度

日期筛选
1. 点击列旁边的筛选器
2. 选择日期筛选器

高级筛选

本质上是许多条件的组合用and,or来组成的高级筛选

对于筛选的内容如果不用一定要去筛选器，否则下一次筛选默认在上一次筛选的数据集上筛选的

获取每个数据的第一条

导入
排序(如果这时候用删除重复项的方式企图取出第一条，是从源开始变所以没有效果)
添加索引列(从1开始)
删除重复项
上载

快速分箱

添加列--从所选内容(限定区域-选整个表也行但是区域太大不方便)--适当输入可以减少提示范围

选中的内容如果是数字可以输入"0-50"这样就五十一组分箱了

案例六:追加查询

PowerQuery自带追加查询，导入要合并的表，追加查询----一种是在已有查询的基础上合并也就是选中的表吞并其他表，另一种是新查询，构建一个新的表容纳了三个表

如果是新查询方式，那么最后应该会有要合并的表+1个表，直接选择新查询对应的表上载即可

右键连接-加载到即可

案例七:合并单元格一键填充

如果导入的表中有合并单元格，那么直接导入---我们可以发现合并单元格被自动拆分开了----选择有合并单元格的列，填充-向下就ok了

案例八:制作工资条

Excel中的做法

1.表头*n
2.工资条复制到n个表头下
3.1---2n构建索引列
4.排序索引列

在Power Query中也是类似的

1.把工资表导入
2.复制一份用来做表头
3.表头的表-提取-从第100个字符提取1个(显然提取不到所以为"")-但这个不是null-我们替换为null-这样以后就可以填充-向下-完成表头的绘制(如果有必要的话-需要将标题转化为第一行)-构建索引行-1开始2为步长
4.工资表-构建索引行-2开始2为步长
5.新建查询，构建新查询-追加以后上载

这里提一句，如果直接上载会把所有表都上载-我们可以先创建连接-再把需要的表对应的连接-导入到合适的地方即可

案例九:透视列一维转二维

进入PowerQuery透视列，如果要把y项放在上面就选中y列透视-其实本质也是聚合

But 如果选择了不要聚合，那么就要求每个列标题和行标题能唯一确定一个值，因为不聚合超过两个值就放不下了

所以这种功能面向的主要是3列n行的表格，一列作x轴，一列作y轴，另一列在(x,y)位置填充值，这个关系要弄清楚。要用n列填充，就选中y列填充，选择填充内容字段为n即可

必要时自己要添加索引辅助列------最后使用合并列合并-----然后进行格式-修整------进行替换

案例十:透视列一列转n列

1.导入数据
2.构建索引列1-n，假定n能转化为a*b的表格
3.添加列---标准---取模---1-n关于b取模 这里索引就对应列号了
4.添加列---标准---除法---1-n除以a    这里索引就对应行号了
5.删除1-n那一列，透视列
6.我们要把原始内容填充进去所以选中原始内容对应的字段，不聚合
7.上载