WPS表格怎么制作成绩分析表

手头有一份全年级八个班、八个学科的成绩总表，数据都在“原始数据”工作表里。面对这样一份表格，如何快速、直观地查询任意班级在任意学科上的总分、平均分、及格率、优秀率呢？今天，我们就来一步步拆解，用WPS表格打造一个动态的班级成绩查询与分析工具。

▲ 图1：原始成绩数据表

第一步：搭建查询界面

首先，我们需要一个清晰、易用的控制面板。在Sheet2工作表标签上右键，将其重命名为“班级项目查询”。

接着，在A1单元格输入“查询班级”，A2单元格输入“查询项目”。关键步骤来了：点击B1单元格，找到菜单栏的“数据→有效性”（或“数据验证”），在弹出的对话框中，将“允许”条件设置为“序列”，并在“来源”框里输入“1,2,3,4,5,6,7,8”。这里有个细节要注意，数字和逗号都必须在英文半角状态下输入。

用同样的方法，设置B2单元格的数据有效性，其来源设置为“总分,平均分,及格率,优秀率”。这样一来，B1和B2单元格就变成了下拉菜单，查询时只需点选即可，既方便又避免了手动输入可能带来的错误。

第二步：构建后台数据表

界面有了，后台的数据处理框架也得跟上。回到“原始数据”工作表，在P3:X13区域，建立如图2所示的汇总表格框架。

▲ 图2：后台数据汇总表框架

这个表格是动态查询的核心。在Q3单元格录入公式“=班级项目查询!B1”，用于关联前面选择的班级；在Q4单元格录入公式“=班级项目查询!B2”，用于关联选择的查询项目。至此，前后台的桥梁就搭建好了。

第三步：让数据“活”起来

现在，让我们在“班级项目查询”工作表中，随意选择一个班级（比如1班）和一个项目（比如总分）。

然后切换到“原始数据”表，点击Q5单元格，录入核心公式之一：=SUMIF($B:$B,$Q$3,D:D)。这个公式的意思是：在B列（班级列）中，寻找与Q3单元格（即选择的班级）相同的行，并对这些行对应的D列（语文成绩）进行求和。回车，该班级的语文总分立刻就计算出来了。

平均分怎么算？在Q6单元格输入=Q5/COUNTIF($B:$B,$Q$3)。用总分除以该班级的人数（即B列中等于指定班级的单元格个数），结果就是平均分。

及格率和优秀率的计算稍微复杂一点，但思路清晰。以语文科为例：

• 在Q7单元格输入公式：=SUMPRODUCT(($B:$B=$Q$3)*(D:D>=60))/COUNTIF($B:$B,$Q$3)
  这个公式巧妙利用了SUMPRODUCT函数：它先判断两个条件是否同时成立（属于指定班级 且 成绩≥60分），得到一组1和0的数组，然后求和，就得到了及格人数。再除以班级总人数，及格率就出来了。
• 在Q8单元格输入公式：=SUMPRODUCT(($B:$B=$Q$3)*(D:D>=85))/COUNTIF($B:$B,$Q$3)
  原理同上，只是将及格线60分换成了优秀线85分。

最妙的一步来了：选中Q5:Q8这个刚刚写好公式的区域，将鼠标移动到选区右下角的填充柄上，按住向右拖动，一直拖到X列。你会发现，所有学科（从语文到最后一个学科）的总分、平均分、及格率、优秀率全部自动填充完毕！这是因为公式中的列引用（如D:D）是相对引用，拖动时会自动调整为E:E、F:F……。最后，别忘了给这些计算结果统一设置一下数字格式，比如保留两位小数。

第四步：生成最终查询结果

后台全科数据都有了，我们还需要一个简洁的表格来展示最终查询结果。这就是图2中下方那个表格（Q11:X13区域）的作用。

在Q13单元格输入另一个核心公式：=VLOOKUP($Q$11,$P$5:$X$8,COLUMN()-15,FALSE)。

这个公式的妙处在于：Q11单元格是我们要查询的项目（比如“平均分”），它会在P5:X8区域的首列（P列，即项目名称列）中进行精确查找。找到后，利用COLUMN()-15这个动态部分来确定返回第几列的数据——当公式在Q列时，COLUMN()返回17，17-15=2，即返回查找区域的第2列（语文）；当公式被拖动到R列时，就自动变成返回第3列（数学），以此类推。这样，我们只需要在Q13输入一次公式，然后向右拖动填充，所有学科对应的“平均分”数据就整齐地排列好了。效果如图3所示。

▲ 图3：动态查询结果展示

第五步：用图表让数据说话

数字看累了？让图表来直观展示吧。回到“班级项目查询”工作表，找个空白单元格，比如C4，输入公式：="期末考试"&B1&"班"&B2&"图表"。这个公式会把我们选择的班级和项目动态组合成图表的标题。

接下来，点击“插入→图表”，选择“簇状柱形图”。在关键的“源数据”设置步骤（如图4），需要手动指定三个部分：

▲ 图4：图表数据源设置

• 系列名称：输入=原始数据!$Q$11（即查询的项目，如“平均分”）。
• 值：输入=原始数据!$Q$13:$X$13（即我们刚刚用VLOOKUP得到的那一行结果数据）。
• 分类(X)轴标志：输入=原始数据!$Q$12:$X$12（即各学科的名称）。

点击下一步，在“数据标志”选项卡中勾选“值”，让柱形图上直接显示数字。最后点击完成，并将生成的图表拖放到合适位置。最终效果见图5。

▲ 图5：最终生成的动态分析图表

至此，整个工具就大功告成了。现在，你只需要在“班级项目查询”工作表的B1和B2单元格中，轻松下拉选择想要查询的班级和项目，下方的数据表格和柱形图就会立刻同步更新，所有分析一目了然。