根据成绩单，逐列计算，以语文科目为例，语文是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的缺点：
- 此处五个等级的划分是否合理
- 人数越少该方法区分性越差
离群值顾名思义，学生各科成绩先要确定理论上的边界在哪里，如果有某科目分数超出了这个理论的边界，那就是脱离了群体，如果是脱离并且是低于群体的异常值，那就是偏科
其实这里借用的是箱型图的概念，感兴趣的可以搜一下，一组数据，根据上下四分位可以框出这组数据的边界，超出边界的就是异常值
- 相对名次=排名/实考人数
- 计算上下四分位差
- 理论最低值是下四分位 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好理解
这个方法比较简单，如果学生的各个单科中，只要有排名在前30%的，那么其他几科，只要排名在后30%，就算偏科
- 相对名次=排名/实考人数
- 优势学科排名在前30%，偏科在后30%
首先计算相对名次，与方法3中的计算方式相同
然后逐个科目计算，横向是否有科目处于前30%，并且当前科目是后30%
=IF(CG244="","",IF(AND(COUNTIF(CG244:CO244,"<=30%")>=1,CG244>70%),DF$13,""))
我们这里借用的是方法3的相对排名计算结果
方法4的缺点：
- 由于存在实考人数的差异，对于人数较少的科目，理论上排名会比较靠前，人数较多的科目，排名靠后的几率会更大
以上为四种计算偏科的方法，供参考，如果有问题可以私信我讨论，欢迎指正，需要文中附件请留下邮箱[呲牙]
