一个热爱网络的小学教师

Excel按指定比例生成学生总分,并迭代计算出各学科分数

这篇博文题目很拗口,主要是因为这次做的表实在有点奇葩,很难用一两句话表达清楚。最近做个了个学生成绩表,不是真实存在的成绩,而是生成出来的,为了模拟得更像真的,费了不少力气。下面总结一下,备忘。

闲话少说,还是看看要求吧:

学生成绩由A、B、C、D四个学科组成,A学科满分40分,B学科满分50分,C学科满分10分,D学科满分10分,总计110分。

总分90-110分为优秀,75-89分为良好,60-74分为及格,0-59分为不及格。

学生总数为100人,现在指定一个各等级的比例,比如优秀学生数要占总人数的30%左右,良好占40%左右,及格占20%左右,不及格占10%左右。

要求用Excel生成一个包含各学科分数和总分的成绩表。

解决思路:

生成这个成绩表可以分两步来做:首先按照指定比例生成符合要求的总分表,然后再用这个总分表去生成每个学生各学科的分数。

1、按比例生成总分

由于划定了各等级的比例,这个总分表还是很容易生成的。可以使用RANDBETWEEN函数,生成30个90-110分,40个75-89分,20个60-74分,10个0-60分。

但是为了更像真的,还要加一些必要的限制。虽然理论上最低分可以是0分,最高分可以是满分,但实际上0分、极低的分数和满分都是不正常的,所以要排除这些不正常的分数。既然要造,就要造的像样一点。所以我这里指定最低分为51分,最高分为106分。

另外,30%、40%、20%、10%的比例也是明显不正常的,所以要适当做一些调整。

为了容易修改,我这里将最低分、最高分、四个比例值都设置为变量。

新建一个Excel文件,在Sheet1的B1单元格输入最低分51,B2单元格输入最高分106,B3-B6单元格分别输入指定的优秀、良好、及格、不及格的比例值,如28%、41%、24%、7%。

接下来切换到Sheet2,在这个工作表里生成学生总分。

A列填充序号1-100。

B列根据A列的序号和Sheet1的比例变量生成等级,公式如下:

=IF(A1<=100*Sheet1!$B$3,”优秀”,(IF(A1<=100*(Sheet1!$B$3+Sheet1!$B$4),”良好”,(IF(A1<=100*(Sheet1!$B$3+Sheet1!$B$4+Sheet1!$B$5),”及格”,”不及格”)))))

C列根据B列的等级生成随机分数,公式如下:

=IF(B1=”优秀”,RANDBETWEEN(90,Sheet1!$B$2),IF(B1=”良好”,RANDBETWEEN(75,89),IF(B1=”及格”,RANDBETWEEN(60,74),RANDBETWEEN(Sheet1!$B$1,59))))

这样就生成了符合要求的学生总分数,但是明显太假,虽然这个表没有排序,但是按分数段分的清清楚楚,真正的成绩表不应该这样有序,应该是乱序的,所以接下来需要把顺序打乱。

D列用RAND函数生成100个随机数。

E列用RANK函数给D列的这些随机数取一下序号值,获得1-100的序号。一般来讲得到的这个序号是唯一的,不会有重复,因为RAND函数可以精确到小数点后15位。但是就算有重复也不碍事,反而能够增加数据的仿真性。

F列把C列的分数根据E列的序号重新排列,公式如下:

=INDEX(C:C,MATCH(E1,A:A))

这样就完成了学生总分的生成,符合指定比例,限制了最低、最高分,顺序还是乱的。完美。

2、根据总分生成各学科的分数

现在有了学生的总分,也知道各学科最高分,下面需要用随机数函数来生成各科成绩。但是为了让生成的各学科分数加起来等于总分,需要启用迭代计算,在EXCEL的“文件”→“选项”→“公式”窗口勾上“启用迭代计算”即可,为了尽可能一次计算成功,可以将“最多迭代次数”修改为更大的数值,比如1000。

启用迭代计算

生成各学科分数时仍然要注意仿真性。既然要造,就要造的像样一点。以A学科为例,理论上0-40分都行,但是太低的分数是不正常的,所以这里规定最低分不能低于20分。

把Sheet2的F列复制粘贴到Sheet3的A列,只粘贴数值。以下操作都在Sheet3工作表中进行。

B列用RANDBETWEEN函数生成A学科的分数,最低分20分,最高分40分,公式如下:

=IF(B1+C1+D1+E1=A1,B1,RANDBETWEEN(20,40))

C列生成B学科成绩,最低分20分,最高分50分,公式如下:

=IF(B1+C1+D1+E1=A1,C1,RANDBETWEEN(20,50))

D列生成C学科成绩,最低分5分,最高分10分,公式如下:

=IF(B1+C1+D1+E1=A1,D1,RANDBETWEEN(5,10))

E列生成D学科成绩,最低分5分,最高分10分,公式如下:

=IF(B1+C1+D1+E1=A1,E1,RANDBETWEEN(5,10))

由于迭代计算需要反复进行,尽管已经将最多迭代次数设置为1000次,仍然有可能不能一次成功,还需要做一下校验:

F列计算一下生成的各学科分数加起来是否等于总分,公式如下:

=IF(SUM(A3:H3)=I3,” “,”错误”)

如果显示“错误”,说明计算不对,按F9重新计算即可,反复按F9,直到“错误”消失。

至此,所有分数已成功生成。完美!

赞(6) 打赏
转载请标明出处:高海鹏老师的博客 » Excel按指定比例生成学生总分,并迭代计算出各学科分数
分享到: 更多 (0)

留言 抢沙发

Protected by WP Anti Spam
  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏