暑假快结束了，马上要进入新学期了，很多老师希望通过期末成绩分析来为学生提供个性化的教学指导，其中一个比较重要的需求就是分析学生的偏科情况，通过访谈各个学校老师以及查阅网上相关资料，我们在这总结了四种比较常用的方法，可以根据实际情况来选择由于不同老师对于偏科的理解不同，故我们会在文中说明方法的意义、计算过程、缺点根据与老师的访谈反馈，老师认为结果较为准确的排序为：方法2》方法3》方法4》方法1方法1：计算偏差率这个意义在于，通过对比学生每科得分与该科目均分，得到学生真实水平和平均水平的差距，以此来定义学生的偏科情况偏差率=（原始分-均分）/全距，分母不是卷面满分是因为有些科目分数比较集中，有些科目分数比较分散，如果都按满分来算，难免会有偏差，所以通过全距来消除这个偏差，让每科都在自己合适的尺度中进行衡量取各科偏差率前5%的学生首先根据成绩单，逐列计算，以语文科目为例，语文是D列，从14行到344行为成绩区域均分 = AVERAGE(D14:D344)最高分 = MAX(D14:D344)最低分=SMALL(D14:D344,COUNTIF(D14:D344,"0")+1)，最低分这里需要注意，不能用MIN()公式取最小值，因为没有分数的学生肯定是0分，这里靠去掉0分，所以，先用countif函数统计有多少个0分，small函数表示第n小的那个值，统计出了有多少个0分，再加1，那肯定就是除了0之外最小的那个分数了全距=D9-D8，最高分-最低分最终会得出偏差率，按偏差率降序，取各科前5%的学生方法1的缺点：只有各科的纵向对比，没有学生自身的横向对比，对于差生来说，很有可能很多学科都偏科只有一个参数那就是偏差率最大的前5%的学生，参数是否合理还需要根据实际情况调整方法2：对比学业等级其意义在于，如果学生单科的学业等级明显低于总分的学业等级，那就说明学生单科拖了后腿，属于偏科学科按名次比例分为5个等级：15%、35%、35%、13%、2%单科比总分低2个及以上等级根据成绩单，逐列计算，以语文科目为例，语文是BB列，总分是BA列实考人数 =COUNT(BA14:BA344)第一等级的名次起始为1等级的名次结束为=BA10+ROUNDDOWN(COUNT(BA$14:BA$344)*$AZ10,0)-1，意思就是根据人数比例，计算出对应的人数，加上起始的名次，就是结束的名次后面等级名次的起始就是上一个等级结束名次+1然后逐科进行判断，嵌套判断名次是否在对应的名次区间=IF(BB14="","",IF(BB14<=BB$9,1,IF(BB14<=BB$7,2,IF(BB14<=BB$5,3,IF(BB14<=BB3,4,5)))))最终，如果单科等级-总分等级≥2，就是偏科方法2的缺点：此处五个等级的划分是否合理人数越少该方法区分性越差方法3：计算离群值（异常值）离群值顾名思义，学生各科成绩先要确定理论上的边界在哪里，如果有某科目分数超出了这个理论的边界，那就是脱离了群体，如果是脱离并且是低于群体的异常值，那就是偏科其实这里借用的是箱型图的概念，感兴趣的可以搜一下，一组数据，根据上下四分位可以框出这组数据的边界，超出边界的就是异常值相对名次=排名/实考人数计算上下四分位差理论最低值是下四分位+1.5*四分位差低于理论最低值首先计算相对排名，在方法2中我们计算了实考人数，相对名次=排名/实考人数，这里的排名指的是年级排名下浮（下边界，下四分位）=QUARTILE(CG14:CO14,3)，这里CG到CO列是学生各科成绩的相对排名，是算这一组数据的下四分位上浮（上边界，上四分位）=QUARTILE(CG14:CO14,1)，这里CG到CO列是学生各科成绩的相对排名，是算这一组数据的下四分位理论最小值=CP14+1.5(CP14-CQ14)，即下四分位-1.5（上四分位-下四分位）上面这几个指标不懂可以搜一下箱形图的计算，就明白了最后=IF(CG14="","",IF(CG14>$CR14,CU$13,""))，就是将各科的相对排名与理论最小值进行比较，如果小，那就是偏科方法3的缺点：要说这个方法的缺点，那就只有不好解释，因为要铺垫很多其他的内容理论上比方法2准确，但大家更容易接受方法2，因为方法2好理解方法4：前后定位这个方法比较简单，如果学生的各个单科中，只要有排名在前30%的，那么其他几科，只要排名在后30%，就算偏科相对名次=排名/实考人数优势学科排名在前30%，偏科在后30%首先计算相对名次，与方法3中的计算方式相同然后逐个科目计算，横向是否有科目处于前30%，并且当前科目是后30%=IF(CG244="","",IF(AND(COUNTIF(CG244:CO244,"<=30%")>=1,CG244>70%),DF$13,""))我们这里借用的是方法3的相对排名计算结果方法4的缺点：由于存在实考人数的差异，对于人数较少的科目，理论上排名会比较靠前，人数较多的科目，排名靠后的几率会更大以上为四种计算偏科的方法，供参考，如果有问题可以私信我讨论，欢迎指正，需要文中附件请留下邮箱[呲牙]
