这篇博文题目很拗口，主要是因为这次做的表实在有点奇葩，很难用一两句话表达清楚。最近做个了个学生成绩表，不是真实存在的成绩，而是生成出来的，为了模拟得更像真的，费了不少力气。下面总结一下，备忘。
闲话少说，还是看看要求吧：
学生成绩由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，直到“错误”消失。
至此，所有分数已成功生成。完美！
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/250972.html