首页 / 教程

用wps算班级名次 | 用WPS表格数组公式分析班级学生成绩

2022-11-27 05:54:00

1.如何用WPS表格数组公式分析班级学生成绩

学校对于某年级各教学班的考试成绩分析，一般要统计每个班的参加考试人数、及格率、平均分（或每班前若干名学生的平均分）、学生个体在本教学班中的名次等，如果仅用WPS表格的内置函数计算，则需要一个班一个班的分块后计算，非常麻烦。

笔者通过巧妙组合WPS表格的内置函数编写出数组公式，简捷方便地完成了以上各种特征量数计算（如图1所示）。图1 班级学生成绩统计 1、每个班参加考试人数假设某年级有4个班，学生总数不大于260名，班号在A列（用01、02、03…表示各班号），从261行开始为汇总行。

要统计的是语文学科（C 列）的各班参加考试人数（每个班有得分的学生数，即非空格数），则在要计算各班参加考试人数的纵向连续的第一个单元格C262中输入公式： =SUM((A$2:A$260=0&ROW(A1))*(C$2:C$260"")）按Ctrl+Shift+Enter组合键确认输入后，向下拖动填充到全部班级单元格C262 :C265，即可得到各班的该学科参加考试人数。上述公式为数组公式，在编辑栏输入这种公式后，必须按Ctrl+Shift+Enter组合键确认，确认后在公式两边自动加上花括号“{}”（如图2）。

图2 学科实考统计公式解释：将班号列所在区域A$2:A$260为“某班”（0&ROW(A1)）和某学科考分列所在区域C$2:C$260为非空单元格（""）两个条件做乘法运算。当两个条件都成立时返回1(TRUE* TRUE=1)，否则返回0(TRUE* FALSE=0、FALSE *TRUE =0、FALSE * FALSE=0)。

逻辑判断的结果组成0、1数组，通过函数SUM（)将所有的0、1值求和，得出该学科参加考试人数。 2、每个班所有考生的总平均分假设E列为“总分”列，在要计算各班总平均分的纵向连续的第一个单元格E262中输入数组公式： =AVERAGE(IF(A$2:A$260=0&ROW(A1),E$2:E$260））按Ctrl+Shift+Enter组合键确认后向下拖动填充到全部班级单元格E262 :E265，得到每个班所有考生的平均分（如图3）。

图3 每个班的平均分公式解释：首先通过IF（)函数，在总分所在区域E$2:E$260中筛选满足条件为“某班”的一组数值，然后通过AVERAGE（)对所筛选的数值求平均值。 3、每个班前若干名学生的平均分为了分层测试教学效果，往往按截取一部分学生的成绩作为样本计算。

例如，计算各班前30%学生的平均分，需在F262单元格中输入数组公式如下： =AVERAGE(LARGE(IF($A$2:$A$260=0&ROW(A1),E$2:E$260),ROW(INDIRECT("1:"&ROUND(COUNTIF(A$2:A$260,0&ROW(A1))*0.3,0）））））然后将此公式向下拖动到F265单元格（如图4）。图4 各班前30%平均分公式解释：首先通过ROUND(COUNTIF(A$2:A$260,0&ROW(A1))*0.3,0）（以下简称为X），计算前30% 的人数有多少，并将其四舍五入到整数；再通过ROW(INDIRECT("1:"&X) （以下简称为Y）由小到大数排列1到前30%的人数的若干个自然数（数组），由LARGE(IF($A$2:$A$260=0& ROW(A1),E$2:E$260),Y）抽取总分所在区域E$2:E$260中某班前30%学生的得分（数组），最后通过AVERAGE（)求选取数值的平均分。

4、每个班的学科及格率：假如语文学科（C列）及格分数为60分，则在要计算各班及格率的纵向连续的第一个单元格G262中输入数组公式： =SUM((A$2:A$260=0&ROW(A1))*(C$2:C$260>=60))/SUM((A$2:A$260=0&ROW(A1))*(C$2:C$260"")）按Ctrl+Shift+Enter组合键确认后向下拖动填充到全部班级单元格G262:G265，得到各班该学科的及格率（如图5）。图5 学科及格率假如语文（C列）、数学（D列）中的学科及格分数分别为60分、72分，则计算每个班这两个学科都及格的人数的数组公式为： =SUM((A$2:A$260=0&ROW(A1))*(C$2:C$260>=60)*(D$2:D$260>=72)) 5、每个学生在本班的名次一个学生在全年级的名次，可用函数RANK（)求出，而该学生在本班内的名次，却没有现成的内置函数可用，我们仍然可以巧用数组公式来解决。

在名次列的第一个学生成绩行所在单元格F2中输入数组公式： =SUM((A$2:A$260=A2)*(E$2:E$260>E2),1）按Ctrl+Shift+Enter组合键确认后向下拖动到最后一行，得到每个学生在本班的名次（如图6）。图6 班内排名次公式解释：此公式是通过求本班大于该学生总分的人数与1的和，得到该学生在本教学班的名次。

如下的两个数组公式与上述公式等价（见G列和H列的名次1和名次2）: =SUM(((A$2:A$260=A2)*E$2:E$260>E2)*1,1) =SUM(IF((A$2:A$260=A2)*E$2:E$260>E2,1))+1 由上面几个例子可见，数组公式可以解决许多有条件的统计问题。为了方便，我们可以根据上面讲到的各数组公式，设置成考试成绩统计模板应用于每次考试，起到一劳永逸的作用。

2.如何用WPS表格来分班级

在新的一学期，分班是一项学校的常规工作。传统方法按一定的顺序进行分班，就是假定按成绩分成4个班，第一轮，把1、2、3、4名次分到1、2、3、4班；第二轮，把5、6、7、8名次分别分到4、3、2、1班，依此类推。这样一来，1班和4班每两轮有最好的和最差的学生。以1班为例，分到1、8、9、16、17、24等名次，相邻两名次之间的差距两次中就有一次为7太大了，另外一次为1，太小了，这样会造成班主任也有一小点意见。

改进方法：假定按成绩分成4个班，第一轮，把1、2、3、4名分别分到1、2、3、4班；第二轮，把5、6、7、8名分别分到2、3、4、1班；第三轮，把9、10、11、12名分别分到3、4、1、2班；第四轮，把13、14、15、16名分别公到4、1、2、3班，依此类推。分析：从上面的分班过程来看，由于下一轮最好的名次分到了下一班级，这样就保证了每个班在四轮中都能分到最好，最差和中间的名次，同样以1班为例，可以分到1、8、11、14、17、24等名次，相邻两名之间差距，四次中只有一次为7，其余三次为3，太小的差距也消除了，也可以对比一下其它班级分到的名次情况，比按顺序分班要好多了。

下面进行实施步骤：

第一步：输入好学生的相关信息，并按成绩排列好顺序。如图。

现在是，就是在班级中，从A2开始，要形成这样的序列：1、2、3、4、2、3、4、1、3、4、1、2、4、1、2、3，如此反复直到最后的学生如图。

可以采用两种方法：

1、可以采用复制粘贴法，就是先输入上述数据，即1、2、3、4、2、3、4、1、3、4、1、2、4、1、2、3然后粘贴到所有的数据，直到最后。这种方法，比较简单，也容易理解。

2、采用函数法：增加一列：编号，分别在下面输入数据：0、1、2、3……直到最后一个学生，然后在A2中输入公式：=mod(int(e2/4)+mod(e2,4),4)+1，拖动填充柄，直到最后一名学生。同样形成这样的序列1、2、3、4、2、3、4、1、3、4、1、2、4、1、2、3……直到最后，如图。

然后选择所有数据，执行“数据”→“筛选”→“自动筛选”

打开班级后面的倒三角符号，选择1，就可以得到1班的学生名册如图，

按所需整理，打印，任务完成。

3.怎样在wps office上把一个班级的成绩平均分,排名给算出来

点击插入，函数，把你的数据选择，以下是所有函数的翻译。

你找对应的就可以了Excel函数大全数据库和清单管理函数DAVERAGE 返回选定数据库项的平均值DCOUNT 计算数据库中包含数字的单元格的个数DCOUNTA 计算数据库中非空单元格的个数DGET 从数据库中提取满足指定条件的单个记录DMAX 返回选定数据库项中的最大值DMIN 返回选定数据库项中的最小值DPRODUCT 乘以特定字段（此字段中的记录为数据库中满足指定条件的记录）中的值DSTDEV 根据数据库中选定项的示例估算标准偏差DSTDEVP 根据数据库中选定项的样本总体计算标准偏差DSUM 对数据库中满足条件的记录的字段列中的数字求和DVAR 根据数据库中选定项的示例估算方差DVARP 根据数据库中选定项的样本总体计算方差GETPIVOTDATA 返回存储在数据透视表中的数据日期和时间函数DATE 返回特定时间的系列数DATEDIF 计算两个日期之间的年、月、日数DATEVALUE 将文本格式的日期转换为系列数DAY 将系列数转换为月份中的日DAYS360 按每年 360 天计算两个日期之间的天数EDATE 返回在开始日期之前或之后指定月数的某个日期的系列数EOMONTH 返回指定月份数之前或之后某月的最后一天的系列数HOUR 将系列数转换为小时MINUTE 将系列数转换为分钟MONTH 将系列数转换为月NETWORKDAYS 返回两个日期之间的完整工作日数NOW 返回当前日期和时间的系列数SECOND 将系列数转换为秒TIME 返回特定时间的系列数TIMEVALUE 将文本格式的时间转换为系列数TODAY 返回当天日期的系列数WEEKDAY 将系列数转换为星期WORKDAY 返回指定工作日数之前或之后某日期的系列数YEAR 将系列数转换为年YEARFRAC 返回代表 start_date（开始日期）和 end_date（结束日期）之间天数的以年为单位的分数DDE 和外部函数CALL 调用动态链接库（DLL）或代码源中的过程REGISTER.ID 返回已注册的指定 DLL 或代码源的注册 IDSQL.REQUEST 连接外部数据源，并从工作表中运行查询，然后将结果作为数组返回，而无需进行宏编程。有关 CALL 和 REGISTER 函数的其他信息工程函数BESSELI 返回经过修改的贝塞尔函数 In(x)BESSELJ 返回贝塞尔函数 Jn(x)BESSELK 返回经过修改的贝塞尔函数 Kn(x)BESSELY 返回贝塞尔函数 Yn(x)xlfctBIN2DEC BIN2DEC 将二进制数转换为十进制数BIN2HEX 将二进制数转换为十六进制数BIN2OCT 将二进制数转换为八进制数COMPLEX 将实系数和虚系数转换为复数CONVERT 将一种度量单位制中的数字转换为另一种度量单位制DEC2BIN 将十进制数转换为二进制数DEC2HEX 将十进制数转换为十六进制数DEC2OCT 将十进制数转换为八进制数DELTA 检测两个值是否相等ERF 返回误差函数ERFC 返回余误差函数GESTEP 检测数字是否大于某个阈值HEX2BIN 将十六进制数转换为二进制数HEX2DEC 将十六进制数转换为十进制数HEX2OCT 将十六进制数转换为八进制数IMABS 返回复数的绝对值（模）IMAGINARY 返回复数的虚系数IMARGUMENT 返回参数 theta，一个以弧度表示的角IMCONJUGATE 返回复数的共轭复数IMCOS 返回复数的余弦IMDIV 返回两个复数的商IMEXP 返回复数的指数IMLN 返回复数的自然对数IMLOG10 返回复数的常用对数IMLOG2 返回复数的以 2 为底数的对数IMPOWER 返回复数的整数幂IMPRODUCT 返回两个复数的乘积IMREAL 返回复数的实系数IMSIN 返回复数的正弦IMSQRT 返回复数的平方根IMSUB 返回两个复数的差IMSUM 返回两个复数的和OCT2BIN 将八进制数转换为二进制数OCT2DEC 将八进制数转换为十进制数OCT2HEX 将八进制数转换为十六进制数财务函数ACCRINT 返回定期付息有价证券的应计利息ACCRINTM 返回到期一次性付息有价证券的应计利息AMORDEGRC 返回每个会计期间的折旧值AMORLINC 返回每个会计期间的折旧值 COUPDAYBS 返回当前付息期内截止到成交日的天数COUPDAYS 返回成交日所在的付息期的天数COUPDAYSNC 返回从成交日到下一付息日之间的天数COUPNCD 返回成交日过后的下一付息日的日期COUPNUM 返回成交日和到期日之间的利息应付次数COUPPCD 返回成交日之前的上一付息日的日期CUMIPMT 返回两个期间之间累计偿还的利息数额CUMPRINC 返回两个期间之间累计偿还的本金数额DB 使用固定余额递减法，返回一笔资产在指定期间内的折旧值DDB 使用双倍余额递减法或其他指定方法，返回一笔资产在指定期间内的折旧值DISC 返回有价证券的贴现率DOLLARDE 将按分数表示的价格转换为按小数表示的价格DOLLARFR 将按小数表示的价格转换为按分数表示的价格DURATION 返回定期付息有价证券的修正期限EFFECT 返回实际年利率FV 返回投资的未来值FVSCHEDULE 基于一系列复利返回本金的未来值INTRATE 返回一次性付息证券的利率IPMT 返回给定期间内投资的利息偿还额IRR 返回一组现金流的内部收益率ISPMT 计算在投资的特定期间内支付的利息MDURATION 返回假设面值 $100 的有价证券的 Macauley 修正期限MIRR 返回正负现金。

4.如何利用wps制作成绩表和统计成绩表

首先建立一个表格框架，录入表格名称，各项名称，然后填入内容

将文字居中时移至方格出现白十字点击右键，点击如图符号，再选择水平居中，就成功居中了，

录入班级时一个一个输入居中太慢，先输入一格，鼠标移至黑框右下角变成黑十字，同时按住ctrl下拉，录入考号时输入开始考号，如：1 直接下拉，

随便录入了些人和分数，然后就是求总分了，总分需要用到求和，以前需要录入复杂函数，现在只需点击开始，在右边点击求和，选择求和，就会出现下图中的函数，（注：在点求和之前，鼠标点击方格出现的黑框位置将会是结果出现的地方），用蓝框框选需要求和的项，然后回车，求和结果就出来了，需要求下面全部，同步骤3，直接下拉， “不用”ctrl结果如图

下面就是求平均值了，点击结果位置方格，点击开始，求和下方的三角，选择平均值，框选一所求的一列数，回车，其它科目总分同求和，横拉黑框，如图，最终成绩表完成了

有时还需要根据成绩排名，框选排序的项目，点击开始，选择排序，自定义排序，选择排序的根据关键字所在的一列，我选的总分那一列，降序排序，（降序就是从大到小），点击确定就行