新⾼考等级赋分excel操作
新⾼考等级赋分Excel操作
【前⾔】本设计是⽬前⽹络尚未出现先例的原创作品，该作品思路清晰，图⽂并茂直指核⼼，对公式进⾏详细说明并提供原始公式以便复制粘贴，零基础实现等级赋分excel制作的完成与优化，适合全国⽬前公布的各个新⾼考赋分⽅案。本设计是针对很多省份新⾼考改⾰后，各学校迫切需要对选择性科⽬进⾏赋分评价⽽设计。完全切合班主任、科任教师、年级及学校教学管理者进⾏新⾼考赋分及综合素质合格性评价的操作需要。是学校进⾏周测、⽉考、联考赋分模拟统计的最佳利器。通过对该框架设计，excel公式运⽤的深⼊浅出式解读，让普通教学⼯作者都能轻松掌握等级赋分统计模板的制作及运⾏。本设计实现了各省市新⾼考要求及具体班级、学科、学校评价的需要，制作出适合⾃⼰的新⾼考选择性科⽬的赋分和合格性考试的评价模板。下图为
3+1+2模板赋分后的输出效果。
没有基础学编程好学吗⼀、新⾼考等级赋分的原理（初步了解过程就⾏，操作步骤中有详细说明）
1、根据《各省市新⾼考赋分办法》的⽐例，将考⽣分数分成A、B、C、D、E五个等级（或8个等级、21个等级）；选科模式及赋分⽐例统计如下：
按各省的规定，确定每个等级总体赋分区间【左端，右端】的端点值；如以湖南等5省为例：
2、将某学科的所有分数按⽐例分成5个等级，记住将原始分为”0”的赋值为”0”（这个设计完美的解决考试分为0分或缺考科⽬很难赋值或运⾏出错或者错误赋值为”30分”的问题。）；可⽤公式模块
“=IF(K2>0,LOOKUP(IF(K2>0,RANK(K2,K$2:K$1001))/COUNTIF(K$2:K$1001,">0"),{0;0.15;0.5;0 .85;0.98}，第⼆公
式!$D$2:$D$6),IF(K2=0,0))”，
说明：{0;0.15;0.5;0.85;0.98}是各等级前⼏个等级累积和，可根据各省具体⽐例修改。
3、通过组合公式：
“=MIN(IF(第⼀公式!O$2:O$1001=$D2，第⼀公式!K$2:K$1001))”、“=MAX(IF(第⼀公式!O$2:O$1001=$D2，第⼀公
式!K$2:K$1001))”求出每个学科等级中所有分数的最值（最⼤值和最⼩值），构成相应等级的赋分区间；
”第⼀公式”与”第⼆公式”是两个多功能模块⼯作表（相互链接），在”第⼆点中的具体操作”中会按步骤进⾏详细说明。如下图：
4、根据学⽣考试的实际分数所在的区间【左端，右端】，利⽤新⾼考规定的等级等⽐例公式进⾏赋分，公式如下：
学⽣的学科本次赋分值=总体等级赋分区间左端+（实际分数-学科等级赋分区间左端）*（总体等级赋分区间右端-总体等级赋分区间左端）/（学科等级赋分⾼档-学科等级赋分区间左端）。
5、最后通过公式：
“=IF(X2>=86,"A",IF(X2>=71,"B",IF(X2>=56,"C",IF(X2>=41,"D",IF(X2>=30,"E","0")))))”，赋值学科等级；
“=IF(K2=0,"0",IF(K2<>0,RANK(K2,K$1:K$1001,0),))⾮零式排名，与条件计数公式实现“等级”及“⼈数”、“学科名次”显⽰等。
⼆、具体设计步骤（⾮常重要）：
（⼀）新建Excel⽂档（2007以上），在⽂档中再插⼊1个⼯作表，连同原有的3个共有4个⼯作表，分别进⾏重命名：“学⽣成绩原始分输⼊模板”、“学⽣成绩赋分输出”、“第⼀公式”、“第⼆公式”。如下图：
（⼆）在⼯作表”学⽣成绩原始分输⼊模板”内的第⼀⾏输⼊以下栏⽬，成绩分数随意输⼊（建议凑齐1000⼈，⽅便后⾯公式模块的识别）。
（三）设置⼯作表”学⽣成绩赋分输出”
1、在⼯作表”学⽣成绩赋分输出”中，输⼊与”学⽣成绩原始分输⼊模板”相对应的栏⽬，注意顺序不能乱；如下图：
2、然后将”学⽣成绩赋分输出”中的A-J列全部链接成“学⽣成绩原始分输⼊模板”中的数据，后⾯的“化学、⽣物、政治、地理原始会”也类似链接成“学⽣成绩原始分输⼊模板”中的相应数据（不能搞乱顺序）；也是凑齐1000⼈，⽅便公式模块识别。（四）设置⼯作表”第⼀公式”
1、第⼀⾏的栏⽬设置成以下内容，并将A-N列的数据链接成”学⽣成绩原始分输⼊模板”的相应数据（注意也是凑齐1000⼈）；
（五）设置⼯作表”第⼆公式”，输⼊”等级”等信息，其中各学科”区间左端与区间右端”暂时空⽩；如下图所⽰：
（六）”第⼀公式”核⼼模块运⽤
1、在第⼀公式化学左端单元格O2中输⼊以下确定5个等级的区间范围：
=IF(K2>0,LOOKUP(IF(K2>0,RANK(K2,K$2:K$1001))/COUNTIF(K$2:K$1001,">0"),{0;0.15;0.5;0. 85;0.98}，第⼆公
式!$D$2:$D$6),IF(K2=0,0))；
在第⼀公式⽣物左端P2中输⼊：
=IF(L2>0,LOOKUP(IF(L2>0,RANK(L2,L$2:L$1001))/COUNTIF(L$2:L$1001,">0"),{0;0.15;0.5;0. 85;0.98}，第⼆公
式!$D$2:$D$6),IF(L2=0,0))；
在“政治左端”Q2中输⼊：
=IF(M2>0,LOOKUP(IF(M2>0,RANK(M2,M$2:M$1001))/COUNTIF(M$2:M$1001,">0"),{0;0.15;0.5;0. 85;0.98}，第⼆公
式!$D$2:$D$6),IF(M2=0,0))；
在地理左端中R2输⼊：
=IF(N2>0,LOOKUP(IF(N2>0,RANK(N2,N$2:N$1001))/COUNTIF(N$2:N$1001,">0"),{0;0.15;0.5;0. 85;0.98}，第⼆公
式!$D$2:$D$6),IF(N2=0,0))
2、然后选中以上四个，往下填充就好了；以上的赋分⽐例及⼈数确定就由该公式确定（适合各省的赋分⽐例）。
（七）“第⼆公式”中的学科区间的左右端点确定（公式中已设置成1000⼈，其实可以根据需要设置⼈数，但所有⼯作表中必须是都是⼀样的⼈数）
1、在第⼆公式的单元格F2中输⼊: =MIN(IF(第⼀公式!O$2:O$1001=$D2，第⼀公式!K$2:K$1001))；
2、在第⼆公式的单元格G2中输⼊： =MAX(IF(第⼀公式!O$2:O$1001=$D2，第⼀公式!K$2:K$1001))
3、在第⼆公式的单元格H2中输⼊：=MIN(IF(第⼀公式!P$2:P$1001=$D2，第⼀公式!L$2:L$1001))
4、在第⼆公式的单元格I2中输⼊：=MAX(IF(第⼀公式!P$2:P$1001=$D2，第⼀公式!L$2:L$1001))
5、在第⼆公式的单元格J2中输⼊：=MIN(IF(第⼀公式!Q$2:Q$1001=$D2，第⼀公式!M$2:M$1001))
6、在第⼆公式的单元格K2中输⼊：=MAX(IF(第⼀公式!Q$2:Q$1001=$D2，第⼀公式!M$2:M$1001))
7、在第⼆公式的单元格L2中输⼊：=MIN(IF(第⼀公式!R$2:R$1001=$D2，第⼀公式!N$2:N$1001))
8、在第⼆公式的单元格M2中输⼊：=MAX(IF(第⼀公式!R$2:R$1001=$D2，第⼀公式!N$2:N$1001))
然后选中以上8个单位格，向下进⾏填充；如下图：
特别提醒：此时若回车后以上端点值为0（很多⽼师不会处理），则只需单击该单元格，把光标放在上⾯的显⽰栏中公式的最后同时按下：shift+ctrl+enter，即可正常显⽰；
（⼋）”第⼀公式”中的赋分确定：该公式⾮常复杂，是由多重条件语句构成。
1、在第⼀公式的单元格S2中输⼊：
=IF(O2>0,ROUNDUP(VLOOKUP(O2，第⼆公式!$D$2:$Q$6,1,0)+(K2-VLOOKUP(O2，第⼆公式!$D$2:$Q$6,3,0))* (VLOOKUP(O2，第⼆公式!$D$2:$Q$6,2,0)-VLOOKUP(O2，第⼆公式!$D$2:$Q$6,1,0))/(VLOOKUP(O2，第⼆公
式!$D$2:$Q$6,4,0)-VLOOKUP(O2，第⼆公式!$D$2:$Q$6,3,0)),0),IF(O2=0,0))
2、在第⼀公式的单元格T2中输⼊：
=IF(P2>0,ROUNDUP(VLOOKUP(P2，第⼆公式!$D$2:$Q$6,1,0)+(L2-VLOOKUP(P2，第⼆公式!$D$2:$Q$6,5,0))* (VLOOKUP(P2，第⼆公式!$D$2:$Q$6,2,0)-VLOOKUP(P2，第⼆公式!$D$2:$Q$6,1,0))/(VLOOKUP(P2，第⼆公
式!$D$2:$Q$6,6,0)-VLOOKUP(P2，第⼆公式!$D$2:$Q$6,5,0)),0),IF(P2=0,0))
3、在第⼀公式的单元格U2中输⼊：
=IF(Q2>0,ROUNDUP(VLOOKUP(Q2，第⼆公式!$D$2:$Q$6,1,0)+(M2-VLOOKUP(Q2，第⼆公式!$D$2:$Q$6,7,0))* (VLOOKUP(Q2，第⼆公式!$D$2:$Q$6,2,0)-VLOOKUP(Q2，第⼆公式!$D$2:$Q$6,1,0))/(VLOOKUP(Q2，第⼆公
式!$D$2:$Q$6,8,0)-VLOOKUP(Q2，第⼆公式!$D$2:$Q$6,7,0)),0),IF(Q2=0,0))
4、在第⼀公式的单元格V2中输⼊：
=IF(R2>0,ROUNDUP(VLOOKUP(R2，第⼆公式!$D$2:$Q$6,1,0)+(N2-VLOOKUP(R2，第⼆公式!$D$2:$Q$6,9,0))* (VLOOKUP(R2，第⼆公式!$D$2:$Q$6,2,0)-VLOOKUP(R2，第⼆公式!$D$2:$Q$6,1,0))/(VLOOKUP(R2，第⼆公
式!$D$2:$Q$6,10,0)-VLOOKUP(R2，第⼆公式!$D$2:$Q$6,9,0)),0),IF(R2=0,0))
然后，选中以上4个单元格，然后往下填充⾄1001（共1000个学⽣）；在第⼀公式中会显⽰以下内容：
（九）完善⼯作表“学⽣成绩赋分输出”
1、⾸先将⼯作表中的“化学赋分、⽣物赋分、政治赋分、地理赋分”链接成“第⼀公式”的相应赋分值，记得向下填充；
2、在单元格L2中输⼊：
=IF(L2>=86,"A",IF(L2>=71,"B",IF(L2>=56,"C",IF(L2>=41,"D",IF(L2>=30,"E","0")))))
3、在单元格Q2中输⼊：
=IF(P2>=86,"A",IF(P2>=71,"B",IF(P2>=56,"C",IF(P2>=41,"D",IF(P2>=30,"E","0")))))
4、在单元格U2中输⼊：
=IF(T2>=86,"A",IF(T2>=71,"B",IF(T2>=56,"C",IF(T2>=41,"D",IF(T2>=30,"E","0")))))
5、在单元格Y2中输⼊：
=IF(X2>=86,"A",IF(X2>=71,"B",IF(X2>=56,"C",IF(X2>=41,"D",IF(X2>=30,"E","0")))))
6、在单元格N2中输⼊：=IF(K2=0,"0",IF(K2<>0,RANK(K2,K$1:K$1001)))
7、在单元格R2中输⼊：=IF(O2=0,"0",IF(O2<>0,RANK(O2,O$1:O$1001)))
8、在单元格V2中输⼊：=IF(S2=0,"0",IF(S2<>0,RANK(S2,S$1:S$1001)))
9、在单元格Z2中输⼊：=IF(W2=0,"0",IF(W2<>0,RANK(W2,W$1:W$1001)))
最后将以上单元格分别往下进⾏填充就⼤功告成！
读者通过实践⼏次就能感悟到以上公式的具体作⽤，如确定“等级”、“学科排名”等；其中⽐较难的是的赋分公式，需要⼀定的感悟能⼒，它是计算机扫描寻条件符合后回到第⼆公式中的某个值，看懂了这个公式，其它公式就是⼩⼉科。当读者明⽩其中的诀窍后就可以动⼿对“3+3”等模式进⾏编程了。还可以对达标性等级评价就易如反掌了，祝君⼯作顺利！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。