如图所示,现需要统计每个班级的学生名单,以“、”分隔。Excel目前没有合并单元格并保留所有内容的直接功能,如果序号合并的数量比较少,可以采用传统办法手工一一合并,或者是使用著名的Excel插件:方方格子来实现。
方方格子是一款优秀的Excel插件,集成了很多我们日常使用的小功能,这些功能通过Excel原生方法也可以实现,但是使用它们能够提高我们的工作效率,更快地完成任务,该插件的大多功能是免费的,如果你喜欢也可以付费使用他的高级功能,价格并不是很贵。
解决方案
方方格子
使用方方格子合并单元格并保留其内容的操作很简单:
首先选中需要合并的区域,选中方方格子工具选项卡中的“合并转换”。
选择“合并且保留全部内容”
选择“全部合并”和“其他字符”,并在其后面输入“、”分隔符,如果合并的目标区域有空单元格而这不是期待的结果,可以选中“忽略空白单元格”
合并成功,按照同样办法依次合并其他区域
通过以上操作可以发现,虽然使用该功能可以简化我们的操作步骤,但是如果需要合并的项目很多,其实非常麻烦,下面我们将使用Excel自带的Power Querry来更好地实现类似功能。
Power Query
Power Querry的打开方式,可以在选项卡Data
下面找到,From Text/CSV
是从csv等格式的文件中导入,From Web
是从web页面导入,From Table/Range
是从工作表区域中导入,这是我们处理excel中的文件最常用的的选项。
仍以上面的场景为例,使用Power Querry实现相同功能的操作如下:
选中目标单元格区域,点击主导航栏下的“From Table/Range”。
确认需要创建表格的区域,一般情况下点击OK确认即可
点击确认后可进入如下界面
Power Query主界面
扩展
平时的学习和使用可以多注意观察公式代码区域,很多复杂操作需要手动修改M函数代码。
= Table.TransformColumnTypes(Source,{{"班级", type text},
{"学生姓名", type text}})
这种代码语言称为“M函数”,在Excel和Power BI等微软家的软件中广泛使用。
点击选中第一列数据,再点击上方的“Group By”,出现步骤3所示的窗口。
按如下步骤选中分组选项参数
选中1处的“Basic”,关于高级分组,我们将在日后介绍;
在2处点击下拉框,选中“班级”,此处我们按班级分组;
在3处填写我们要新建的列名,用来存放合并后的结果;
在4处点击下拉框选择“Average”,我们之后将修改此处的M函数代码,来实现我们的目标功能;
在5处填写我们需要合并的列名
操作就绪后,点击“OK”确认键,得到有错误提示的如下结果:
修改M函数代码
原来的代码:
= Table.Group(#"Changed Type", {"班级"}, {{"学生姓名合并", each List.Average([学生姓名]), type text}})
修改成如下代码:
= Table.Group(#"Changed Type", {"班级"}, {{"学生姓名合并", each Text.Combine([学生姓名], "、"), type text}})
推荐分隔符使用换行符
,从而便于阅读理解。#(lf)
修改完毕回车,可以看到已经得到我们的目标结果。
点击“Close & Load,将结果上载到Excel Worksheet中,合并完毕
最后需要注意的是,如何在分组合并的过程中需要去掉重复项,则应该按照如下操作:
= Table.Group(更改的类型, {"数据一级分类"}, {{"合并", each Text.Combine(List.Distinct([数据二级分类]),"、"), type nullable text}})