1.在Excel2016及以上版本中,将鼠标放置在任意一行数据上

简介: 1.在Excel2016及以上版本中,将鼠标放置在任意一行数据上,点击【数据】选项卡,选择【自表格/区域】:2.这时就PowerQuery就会直接跳转到查询编辑

前两天我写过一个帖子,讨论Excel表格中,要计算的数据的多个字段在同一个单元格里,该如何处理,详见《Excel中,销售数据都在一个单元格里,该如何统计计算?

在另一篇帖子中,我还用小学生都会的Excel公式分步骤地解决了这个难题,详见《Excel公式越复杂越好?

我们以班级学生为例:很简单的一个需求:每个班有几十名学生,按照规范的Excel数据表格形式填写好了,现在要打印班级学生名单,如果按照左边的表格打印,就太浪费纸张了,一点都不环保,我们需要把同一个班的学生合并到一个单元格,然后进行打印,就像右表中的那样。

企业中的话,也会经常遇到要把规范的部门成员表格打印出名单来,也要用到同样的思路。

抱歉这次我给不出Excel公式了(因为我没找到),有知道的小伙伴麻烦补充下,谢谢。

解决这个问题的最佳工具,我认为还是我最心爱的PowerQuery。

而且它的解决方法也非常优雅,就一行(假设数据源已经引入PowerQuery了):= Table.Group(源, {"班级"}, {{"学生", each Text.Combine(_[学生],",")}})如果把上述公式抽象下,就是:把【班级】换成你的班级所在列、部门所在列、单位所在列,品类所在列、类别所在列,等等之类;把【学生】换成你要合并的数据所在的列。

我们这次假设就引入来自同一个Excel表格(区域)的数据到PowerQuery的源。

1.在Excel2016及以上版本中,将鼠标放置在任意一行数据上,点击【数据】选项卡,选择【自表格/区域】:2.这时就PowerQuery就会直接跳转到查询编辑页面,这也意味着我们引入成功了:3.点击地址栏的【fx】图标,新增一个步骤,把我们前面列的公式输入进去,也可以点击查询编辑窗口的【转换】选项卡,选择【分组依据】,然后如图设置:确定后,再改地址栏中的公式红框部分为:Text.Combine(_[学生],",")因为Table.Group()函数比较复杂,所以刚开始接触PowerQuery,最好是通过界面来实现分组操作,然后根据需要在PowerQuery自动生成的公式基础上进行调整。

Table.Group()函数第一个参数是要进行分组的表格名称(这个例子中叫【源)。

第二个参数是分组依据,就是基于这个列的值进行分组(这个例子中叫【"班级"】),【{}】是PowerQuery中list的符号。

第三个参数具体的分组操作,是一个包含list的list,每一个子list中的第一个参数是列名称(这个例子中是【"学生"】),之后是一个英文逗号【,】;再之后是一个函数,其中【each】代表要对【"学生"】列的每一行进行同样的操作,each后面就是具体的操作函数【 Text.Combine(_[学生],",")】,Text.Combine()是将文本用分隔符连接起来,这个例子中我们用的是中文的逗号【,】。

点击查询编辑窗口【主页】选项卡上的【关闭并上载】按钮,选择将结果加载到当前表格空白位置,完成。

5.以后有同样的数据要合并到同一行,只需要在sheet1中替换掉原始数据,然后点击【数据】选项卡上的【全部刷新】,最新结果就出来了:02总结是不是很简单?

(虽然步骤看起来比较多,这是因为我要详细演示,其实实际操作第一次就一两分钟的事情,以后的数据刷新就是10来秒的事情)我们举一反三一下:假设我们要将同一个单元格的数据进行拆分,怎么办呢?

就拿这个例子中来说,我要把右边的表格变为左边的表格,该如何操作呢?

其实也是简单得不要不要的:在PowerQuery中对右边表格的【学生】列用【,】拆分列:PowerQuery会自动找到用于拆分的符号;当原始数据中符号太多的时候,PowerQuery找的可能不符合需要,这时我们可以自己选择。

接下来选择【班级】列,然后在【转换】选项卡,选择【逆透视】-【逆透视其他列】,就得到了下图中的表格:删除【属性】列,将【值】列重命名为【学生】列(也可以直接修改Table.UnpivotOtherColumns),就还原成我们最初左边的表格了。

如果你想提高Excel处理数据的效率,除了保证原始数据规范之外,PowerQuery一定是你提高工作效率、避免996、无休止加班的利器,建议抽点时间结合实际工作需求演练下,很快就能入门,比Excel简单多了。


以上是文章"

1.在Excel2016及以上版本中,将鼠标放置在任意一行数据上

"的内容,欢迎阅读裹缕时尚健康网的其它文章