暑假快結束了,馬上要進入新學期了,很多老師希望透過期末成績分析來為學生提供個性化的教學指導,其中一個比較重要的需求就是分析學生的偏科情況,透過訪談各個學校老師以及查閱網上相關資料,我們在這總結了四種比較常用的方法,可以根據實際情況來選擇
由於不同老師對於偏科的理解不同,故我們會在文中說明方法的意義、計算過程、缺點
根據與老師的訪談反饋,老師認為結果較為準確的排序為:方法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的缺點:
由於存在實考人數的差異,對於人數較少的科目,理論上排名會比較靠前,人數較多的科目,排名靠後的機率會更大
以上為四種計算偏科的方法,供參考,如果有問題可以私信我討論,歡迎指正,需要文中附件請留下郵箱[呲牙]