多次成绩汇总到一个表格(汇总成绩表怎么做)
有位读者提了个问题,有一张需要汇总的成绩总表,包含了学生的所有成绩列表,而收集回来的成绩表是每门课的成绩单独一个表,每个任课老师经常将学生成绩排序,导致学生的姓名顺序不一致。
每次需要将各科成绩与学生姓名一一对应汇总到总表中,现在这位读者每次都通过 vlookup 来查找,完事后还要将公式再转化为数值。每次这样操作挺麻烦的,有没有简单的方法批量导入?
当然有,一说到批量操作,老读者门可能就想到了,还是要用强大的 Power Query。
案例:
下图 1、2 是学生各科成绩表,每门学科有一个单独的工作簿,由各任课老师提交,班主任需要将所有的成绩汇总到同一个表格中,如下图 3 所示。
解决方案:
1. 打开一个空的 Excel 文件 --> 选择菜单栏的“数据”-->“新建查询”-->“从文件”-->“从工作簿”
2. 在弹出的对话框中选择需要导入的文件 --> 点击“导入”
3. 选择需要导入的工作表 --> 点击“转换数据”
第一个成绩表已上传至 Power Query。
4. 选择菜单栏的“主页”-->“新建源”-->“文件”--> Excel,将第二个成绩表导入到 Power Query。
5. 重复步骤 4,依次将需要合并的各科成绩文件都导入到 Power Query。
6. 在左边的“查询”导航中选中第一个查询 --> 选择菜单栏的“主页”-->“合并查询”-->“合并查询”
7. 在弹出的对话框中进行如下设置 --> 点击“确定”:
- 选中“Sheet1”的“姓名”列
- 在下拉菜单中选择“Sheet1 (2)”
- 选中“Sheet1 (2)”的“姓名”列
- 在“联接种类”下拉菜单中选择“完全外部(两者中的所有行)”
8. 点击 Sheet1 (2) 右边的展开按钮 --> 选择“展开”--> 仅勾选“数学”--> 取消勾选“使用原始列名作为前缀”--> 点击“确定”
9. 重复步骤 6、7、8,对所有的查询文件都进行合并查询。
10. 选择菜单栏的“主页”-->“关闭并上载”-->“关闭并上载至...”
11. 在弹出的对话框中选择“仅创建连接”--> 点击“加载”
12. 在右侧的“工作簿查询”区域选中 Sheet1 --> 右键单击 --> 在弹出的菜单中选择“加载到...”
13. 在弹出的对话框中选择“表”-->“现有工作表”--> 点击“加载”
合并后的成绩表格就上传到了 Excel。当前的表格是按语文成绩降序排序的,可以根据自己的需要,重新排序。
14. 双击右侧的“工作簿查询”区域中的 Sheet1,回到 Power Query --> 选中“姓名”列 --> 选择菜单栏的“主页”-->“升序排序”
表格就按姓名的拼音字母升序排序了。
15. 选择菜单栏的“主页”-->“关闭并上载”
现在回到了 Excel,合并的表格按人名升序排序。今后任何一门课的 Excel 文件中若有内容更新,只要在绿色区域刷新一下,即可实时更新数据。
很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。
现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。