使用场景
如图所示,现需要统计每个班级的学生名单,以“、”分隔。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确认即可
- 点击确认后可进入如下界面
扩展
平时的学习和使用可以多注意观察公式代码区域,很多复杂操作需要手动修改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}})
修改完毕回车,可以看到已经得到我们的目标结果。
- 点击“Close & Load,将结果上载到Excel Worksheet中,合并完毕