仓位计算公式新高考等级赋分excel设计创新研究

2020年9月30日发(作者：祁峻)
新高考等级赋分设计创新研究
广东教育东城中心 胡广
【前言】本设计是目前网络尚未出现先例的原创作品，该作品思路清 晰，图文并茂直
指核心，对公式进行详细说明并提供原始公式以便复制粘贴，零基础实现等级赋分exc el
制作的完成与优化，适合全国目前公布的各个新高考赋分方案。本设计是针对很多省份新
高 考改革后，各学校迫切需要对选择性科目进行赋分评价而设计。完全切合班主任、科任
教师、年级及学校 教学管理者进行新高考赋分及综合素质合格性评价的操作需要。是学校
进行周测、月考、联考赋分模拟统 计的最佳利器。通过对该框架设计，excel公式运用的
深入浅出式解读，让普通教学工作者都能轻松 掌握等级赋分统计模板的制作及运行。本设
计实现了各省市新高考要求及具体班级、学科、学校评价的需 要，制作出适合自己的新高
考选择性科目的赋分和合格性考试的评价模板。下图为3+1+2模板赋分后 的输出效果。

一、新高考等级赋分的原理（初步了解过程就行，操作步骤中有详细说明 ）
1、根据《各省市新高考赋分办法》的比例，将考生分数分成A、B、C、D、E五个等
级（或 8个等级、21个等级）；选科模式及赋分比例统计如下：
选科类别
3+1(物或历)+2（4选2）
3+1（物或历）+2（4选
2）
3+3（6选3）
3+3（6选3）
3+3（6选3）
3+3（7选3）
赋分等级及比例
五个等级等比例赋分（17%；33%；33%；15%；2%）
五个等级等比例赋分（15%；35%；35%；13%；2%）
五个等级等比例赋分（15%；35%；35%；13%；2%）
八个等级等比例赋分（3%；7%；16%；24%；24%；16%；7%；
3%）
21个等级非等比例赋分（1%；2%；3%；......1%)
21个等级非等比例赋分（1%；2%；3%；......1%)
1

按各省的规定，确定每个等级总体赋分区间【左端，右端】的端点值；如以湖南等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,
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,
))))”,赋值学科等级 ；
2

“=IF(K2=0,非零式排名,与条件计数公式实现
“等级”及“人数”、“学科名次”显示等。

二、具体设计步骤（非常重要）：
（一）新建Excel文档（2007 以上），在文档中再插入1个工作表，连同原有的3个
共有4个工作表，分别进行重命名：“学生成绩原 始分输入模板”、“学生成绩赋分输出”、
“第一公式”、“第二公式”。如下图：


（二）在工作表”学生成绩原始分输入模板”内的第一行输入以下栏目，成绩分数随意
输入（建议凑齐1000人，方便后面公式模块的识别）。
3



（三）设置工作表” 学生成绩赋分输出”
1、在工作表” 学生成绩赋分输出”中，输入与” 学生成绩原始分输入模板”相对应的
栏目，注意顺序不能乱；如下图：


2、然后将” 学生成绩赋分输出”中的A-J列全部链接成“学生成绩原始分输入模板”
中的 数据，后面的“化学、生物、政治、地理原始会”也类似链接成“学生成绩原始分输
入模板”中的相应数 据（不能搞乱顺序）；也是凑齐1000人，方便公式模块识别。

（四）设置工作表”第一公式”
1、第一行的栏目设置成以下内容，并将A- N列的数据链接成”学生成绩原始分输入模板”
的相应数据（注意也是凑齐1000人）；
4



（五）设置工作表”第二公式”，输入”等级”等信息，其中各学科” 区间左端与区间
右端”暂时空白；如下图所示：

（六）”第一公式”核心模块运用
1、在第一公式化学左端单元格O2中输入以下确定5个等级的区间范围：
=IF(K2> 0,LOOKUP(IF(K2>0,RANK(K2,K$$2:K$$1001))COUNTIF(K$$2:K $$1001,
5;0.5;0.85;0.98},第二公式!$$D$$2:$$D$$6),IF(K2=0 ,0))；
在第一公式生物左端P2中输入：
=IF(L2>0,LOOKUP(IF(L 2>0,RANK(L2,L$$2:L$$1001))COUNTIF(L$$2:L$$1001,
5;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,
5;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))CO UNTIF(N$$2:N$$1001,
5;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))
5

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=$$D 2,第一公
式!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)-VLOO KUP(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,R OUNDUP(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))(VLOO KUP(P2,第二公式!$$D$$2:$$Q$$6,6,0)-VLOOKUP(P2,第二公
6

式!$$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)-VLOO KUP(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,R OUNDUP(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))(VLOO KUP(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,
))
3、在单元格Q2中输入：
7

=IF(P2>=86,
))
4、在单元格U2中输入：
=IF(T2>=86,
))
5、在单元格Y2中输入：
=IF(X2>=86,
))
6、在单元格N 2中输入：=IF(K2=0,
7、在单元格R2中输入：=IF(O2=0,
8、在单元格V 2中输入：=IF(S2=0,
9、在单元格Z2中输入：=IF(W2=0,
最后将以上单元 格分别往下进行填充就大功告成！
读者通过实践几次就能感悟到以上公式的具体作用，如确定“等级” 、“学科排名”等；
其中比较难的是的赋分公式，需要一定的感悟能力，它是计算机扫描寻找条件符合后 回到
第二公式中的某个值，看懂了这个公式，其它公式就是小儿科。当读者明白其中的诀窍后
就 可以动手对“3+3”等模式进行编程了。还可以对达标性等级评价就易如反掌了，祝君
工作顺利！

2019年11月

8