关于 Power Query,你必须知道的 6 个高效技巧。 【下载方法】: 用 Office 2013 和 2010 版本的同学,可以在后台回复关键词【插件】,下载使用 PQ。 Office 2016 及以上版本的同学,恭喜你们,软件已经自带 PQ 功能啦! Q:下面表格中,需要提取 A 列中不重复的部门名称,变成右边的样子。
传统方法:
=INDEX(A:A,SMALL(IF(MATCH(A$2:A$10,A$2:A$10,0)=ROW($1:$9),ROW($2:$10),4^8),ROW(A1)))&'' 公式完全看不懂。但是结果无法自动更新,有新增部门的时候,还需要重复相同的操作。 Power Query:用 Power Query 点点按钮就可以搞定,还可以动态刷新结果。❶ 数据导入到Power query。
❷ 删除重复值。PS:这里是在 Power Query 中删除重复值,只影响处理的过程,不影响原始数据。❸ 数据上载至Excel。刚才所有的操作都是在 Power Query 中进行的,处理结果还是需要通过「关闭并上载」的形式,传回到 Excel 中。❹ 动态更新。Power Query 最大的特性就是,数据整理的结果和原始的数据是链接的。所以,原始数据更新后,在结果上右键刷新就可以同步更新了。技巧2:拆分单元格到多行
Q:一个单元格里有多行文本,想要拆分到单独的数据行中。
传统方法: Power Query:使用 Power Query 中的「拆分列」功能,可以一键完成。❶ 加载数据到Power Query。同样的,也是要先把数据加载到 Power Query 中。
❷ 根据换行拆分数据到行。Power Query 中的「拆分列」功能,和 Excel 中的「分列」基本一样,只不过它支持更多的拆分选项,比如按回车拆分,拆分数据到行等等。技巧3:合并同类文本
Q:做小组人员统计的时候,需要把同组的人名,合并到一个单元格中。
传统方法:总觉得 Excel 没啥好学的人,这个时候只能一个一个的剪切、粘贴。 Power Query:❶ 加载数据到Power Query。
❷ 使用分组依据功能,合并同类文本。操作方式先选择「求和」,下一步修改公式,完成合并。❸ 修改公式,合并文本。最后把 SUM 函数,改成 Text.Combine 函数,完成文本的合并。= Table.Group(源, {'小组'}, {{'计数', each Text.Combine([姓名],','), type text}})xxxxxxxxxx= Table.Group(源, {'小组'}, {{'计数', each Text.Combine([姓名],','), type text}})公式
技巧4:重复填充数据Q:需要在每一行数据下面,按照对应的数量,重复数据内容? 传统方法:=IFERROR(VLOOKUP(ROW(A1),A:C,3,),E3)&''
Power Query:使用 Power Query,同样点击几下鼠标就可以搞定。❶ 加载数据到Power Query。
❷ 添加辅助列,填充序号。PS:Power Query 中是不能像 Excel 那样,直接在单元格里写公式的;需要像上面一样,通过添加新列的方式,来编写公式。❸ 扩展数据,生成新的行。
|