Excel – 将同班同学汇总到同一个单元格中

按类别汇总是一个挺简单的需求，最常规的用 vlookup 就能实现。

但是！如果要把同一类别的文本汇总在同一个单元格中，vlookup 是搞不定的。那怎么办呢？

幸好 Excel 2016 以后有了 PowerQuery。看似挺多步骤，实际跟着操作非常简单。

案例：

将下表 1 汇总为下表 2，要点：

  • 同一班级的所有同学汇总在同一个单元格中，用“、”隔开

  • C 列汇总班级总分

解决方案：

1. 选中表格的任何区域 --> 选中菜单栏的 Data（数据）--> From Table/Range（自表格/区域）

2. 在弹出的对话框中会自动选中整个表格区域，并且包含标题 --> 点击 OK：

3. 接着就会进入数据查询编辑器，选择菜单栏的 Transform（转换）--> Group By（分组依据）

4. 在弹出的对话框中进行以下设置 --> OK：

  • 选择“高级”单选钮

  • 在New column name（新列名）区域分别对“班级”和“高考总分”两列求和，详见红框内的设置

5. 现在可以看到，3 列数据已经汇总，但是人名这一列显示的是错误值。这还是比较容易理解的，对文字求和，自然会出错。

接下来划重点，如何把错误值变成人名汇总：

  • 选中“姓名”列，在上方的公式编辑栏中找到 List.Sum，将它替换成 Text.Combine

  • 同时在 [姓名] 后面加上 '、'，用英文逗号“,”隔开

公式释义：

  • List.Sum 和 Text.Combine 都是 M 函数

  • M 函数是 PowerQuery 专用的函数，它可以帮我们自由灵活地完成数据导入、整合、加工处理等任务

  • 跟 Excel 数据表类似，PowerQuery 的界面操作只能发挥 20% 的能力，剩余 80% 的功能都要通过 M 函数来实现

  • 函数区分大小写

  • List.Sum 的作用是对数据求和

  • Text.Combine 的作用是将字符集 list 列表合并成字符串

6. 最后选择菜单栏的 Home（开始）--> Close & Load（关闭并上载）即已完成

赞 (0)

相关推荐