新高考等级赋分excel操作.docx

新高考等级赋分excel操作 PAGE 1 新高考等级赋分Excel操作 【前言】本设计是目前网络尚未出现先例的原创作品，该作品思路清晰，图文并茂直指核心，对公式进行详细说明并提供原始公式以便复制粘贴，零基础实现等级赋分excel制作的完成与优化，适合全国目前公布的各个新高考赋分方案。本设计是针对很多省份新高考改革后，各学校迫切需要对选择性科目进行赋分评价而设计。完全切合班主任、科任教师、年级及学校教学管理者进行新高考赋分及综合素质合格性评价的操作需要。是学校进行周测、月考、联考赋分模拟统计的最佳利器。通过对该框架设计，excel公式运用的深入浅出式解读，让普通教学工作者都能轻松掌握等级赋分统计模板的制作及运行。本设计实现了各省市新高考要求及具体班级、学科、学校评价的需要，制作出适合自己的新高考选择性科目的赋分和合格性考试的评价模板。下图为3+1+2模板赋分后的输出效果。 一、新高考等级赋分的原理（初步了解过程就行，操作步骤中有详细说明 ） 1、根据《各省市新高考赋分办法》的比例，将考生分数分成A、B、C、D、E五个等级（或8个等级、21个等级）；选科模式及赋分比例统计如下： 选科类别 赋分等级及比例 3+1(物或历)+2（4选2） 五个等级等比例赋分（17%；33%；33%；15%；2%） 3+1（物或历）+2（4选2） 五个等级等比例赋分（15%；35%；35%；13%；2%） 3+3（6选3） 五个等级等比例赋分（15%；35%；35%；13%；2%） 3+3（6选3） 八个等级等比例赋分（3%；7%；16%；24%；24%；16%；7%；3%） 3+3（6选3） 21个等级非等比例赋分（1%；2%；3%；1%) 3+3（7选3） 21个等级非等比例赋分（1%；2%；3%；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,">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人，方便公式模块识别。 （四）设置工作表”第一公式”