1. 首頁 > 生活百科 > >

用Excel巧算標準分熱門推薦

excel電腦知識

【用Excel巧算標準分熱門推薦】一、標準分的計算原理
標準分的計算原理是對于每一個分數XI， 先算出這個分數以下的考生數占考生總數的百分比PI， 再查這個PI對應于正態分布表中最接近的數值ZI， 最后通過公式：標準分=ZI*100+500算得的 。 標準分總分的計算方法是：把每位考生各科的標準分相加求和， 再算出這個和以下學生成績的學生數， 除以總人數得到PI值， 最后按以上算各科標準分的方法， 完成標準總分的轉換過程 。
二、有關的函數
1． RANK函數
rank函數返回一個數字在數字列表中的排位， rank函數的使用格式是“rank(number,ref,order)” 。 其中：number是需要找到排位的數字；ref是數字列表數組或對數字列表的引用；order為一數字， 指明排位的方式 。 如果order為零或省略， microsoft excel對數字的排位是基于ref的降序排列 。 如果order不為零， microsoft excel對數字的排位是基于ref的升序排列 。 在這里， 我們利用rank函數找出每位考生的成績， 進行升序排列的名次n， 則在該考生成績之下的學生便有（n-1）位 。 例如一位考生的成績升序排列是第10名， 顯然， 在這位考生前面有9位考生 。 這樣， 我們便能利用rank函數算出每個分數以下考生的人數 。
2． normsinv函數
該函數返回標準正態累積分布函數的反函數 。 該分布的平均值為零， 標準偏差為 1 。 如果probability為非數值型， 函數normsinv返回錯誤值#value! 。 如果 probability小于零或大于1， 函數normsinv返回錯誤值#num! 。 語法normsinv (probability)， 其中：probability是正態分布的概率值 。
3．應用舉例假設該年級學生共有500人， 在第2行至501行中， 在h2單元格單擊， 輸入公式“=100*normsinv(((rank(d2,d$2:d$501,1)-1)/(counta(d$2:d$501))))+500”后回車， 如果輸入無誤， 可看到該單元格按照語文原始分轉化出來的語文標準分 。 由于函數normsinv(0)返回的值是“#num!”（在以上公式中， 就是最低分的考生）， 而標準分最低分是100分， 因此， 進行標準分轉換時， 在顯示 “#num!”的單元格上， 必須手工把它改為100 。
設置h2單元格的“單元格格式”為“數值”、“小數點位數”是“0”， 讓顯示出來的標準分都是整數 。 用鼠標選擇h2單元格， 拖動填充句柄到k2單元格， 這樣便完成了第一位考生各科標準分的轉換工作 。 用鼠標選擇h2:k2單元格， 拖動填充句柄到最后一位考生所在的行， 我們便完成了所有考生單科成績轉換的工作（如附圖所示） 。

用Excel巧算標準分熱門推薦

文章插圖

1列（標準分之和）是每位考生各科成績轉換成標準分后求和， 這是為計算標準分做準備的， 完成1列設置后， 在m2單元格中輸入公式：“=100*normsinv(((rank(l2,l$2:l$501,1)-1)/(counta(l$2:l$501))))+500”后回車， 再選擇這一單元格， 拖動填充句柄到最后一行的學生即可 。

必須注意的問題是， 如果要把學生分開到各個班級中或進行其他操作， 則必須再進行一次粘貼數值的處理才行 。 具體的做法是選擇圖中標準分所在的列（h列 ～m列）， 點擊“復制”， 在n1單元格上單擊， 選擇“編輯→選擇性粘貼”， 在彈出的對話框中“粘貼”選項處， 選擇“數值”， 單擊[確定]按鈕后， 再把原來的g列～m列刪除掉就行了 。

    推薦閱讀