把学生类按单科成绩排序_零基础学Excel VBA-WE016「一键完成学生成绩的收集和排序」...

一、简单演示

本例是从某些表格中收集满足一定条件的数据进行汇总的应用。

二、关键对象/方法的分析

1. Workbooks.Open(FileName)

打开一个工作簿。如果打开失败，尝试 FileName 加上完整的路径。

2. Set ws0 = ActiveSheet

Set wbTarget = Workbooks.Open(fpath & "" & cfile)

Set wsT = wbTarget.Sheets(1)

非常简单的代码。当我们在打开多个工作簿时，首先考虑用变量赋予工作表，这是简化代码，减少出错的好习惯。

三、执行思路及过程分析

【1】需求分析

收集到语文、数学、英语 3 科的成绩表，需要完成：

(1)汇总到一张表格中去；

(2)对每科成绩进行排序，并找出前 5 名；

(3)计算每个人的总分，并进行总分排序，找出前 5 名。

收到的成绩表有如下问题：

• 表格的名字不统一
• 表格的样式不统一

但有如下共性：

• 表格的名字含有本科目的名字
• 记录成绩的子表都是第一个子表
• 表格中都会有 2 列，1 列 学号，1列 成绩，并且第 1 行都是表头

【2】完成数据收集的表格样式

左边是按学号顺序，收集所有学生的成绩；右边是各个科目和总分进行排序的结果展示。

【3】获取成绩表的名字

由于宏在执行时，会打开多个工作簿，所以，定义当前工作簿，方便后面的成绩录入。

fpath 是存放收集到的各科成绩表的目录。

rmax 是本表 A 行最大的行号，这个决定了需要录入成绩的数量。

由于 3 个科目，录入或者排序，都有很多共性，所以用一个 For 循环，对于非共性的地方，我们进行变量定义，比如 KeMu，我们分别对 i 等于 1~3 时，对应 3 个不同科目的名字。

cfile，通过 Dir 和通配符 *，获取到每个科目的成绩表名字。获取到以后，进入 If 语句。

【4】录入成绩

在 WE003 那期，我们采用不打开目标表格的方式，对数据进行提取，但那种情况，仅限于目标表格的格式固定的，并且不需要用一些公式进行数据匹配的时候。

像现在这种情况，打开目标表格进行数据的提取，会更加简便和有效率。

打开成绩表以后，通过 Match 函数，获取成绩表中 学号 和 成绩 的列号，然后获取这张表格的最大行列号。再用 Match 函数，获取对应科目成绩记录在本表中的列号。有了这些数据，就可以用 Vlookup 函数进行数据的提取。

这个公式，用了很多变量，而且还是 R1CI 格式的，看起来很难懂，但其实我们看看它输出的结果，其实就是一个非常普通的 Vlookup 函数。

编写宏的时候，我们先是手动把一个公式写出来，再把公式中的定义了变量的地方，用变量进行替换就可以了。

【5】进行单科成绩排序

首先，科目排序列获取到科目成绩以及学生的名字。

然后，用 Sort 函数，注意排序的范围，只选两列。

排序后，选择成绩列，定义一个条件格式，把前 5 的成绩高亮出来。这个条件格式怎么写，不需要太过关注，要用的时候，我们使用 录制新宏 的功能，可以很容易获取到。

【6】计算总分，并排序

总分的计算，用一个 SUM 函数就可以了。

总分的排序，其实跟每个单科排序一样的，最后，总分排序的颜色，换一个跟单科不一样的，显得更加明显一些。