Excel數(shù)組公式在區(qū)分度計算中的應(yīng)用Excel數(shù)組公式在區(qū)分度計算中的應(yīng)用Excel數(shù)組公式在區(qū)分度計算中的應(yīng)用V:1.0精細整理，僅供參考Excel數(shù)組公式在區(qū)分度計算中的應(yīng)用日期：20xx年X月Excel數(shù)組公式在區(qū)分度計算中的應(yīng)用本文通過對區(qū)分度概念及其計算方法的分析，提出了一個使用Excel數(shù)組來進行區(qū)分度運算的公式。該公式適用于任意考生人數(shù)的試題區(qū)分度計算，具有較強的通用性。區(qū)分度Excel數(shù)組公式計算[中圖分類號] [文獻標識碼] AOntheApplicationofExcelArrayFormulatoCalculateTheDistinctionDegreeTaoying1LinPeiyan21(ZhejiangMedicalCollege,Hangzhou310053，China)2(ZhejiangMedicalCollege,Hangzhou310053，China)Throughtheanalysisofdistinctiondegreeconceptionanditscalculationmethod,thisthesisputsforwardaformulathatadoptsExcelarraytocalculatethedistinctiondegree.Astheformulaisapplicabletocalculatethetestpaperdistinctiondegreeforanynumberofstudents,itcanbewidelyused.DistinctionDegreeExcelArrayFormulaCalculate引言我們常常要對教學(xué)質(zhì)量進行評估，目前普遍采用的手段是考試，通過考試卷面成績來衡量教學(xué)質(zhì)量的高低。為此，我們必須對試卷進行綜合分析，使試卷能準確、全面、科學(xué)地反映考生的實際情況。區(qū)分度的概念及計算方法區(qū)分度的概念試題質(zhì)量分析的一個重要指標是區(qū)分度。所謂區(qū)分度是指試題對不同考生的知識、能力水平的鑒別程度[1]。如果試卷中的試題使水平高的考生都答對，而使水平差的考生都答錯，那么該試題的區(qū)分能力就強。區(qū)分度的計算方法一般我們以考生在一份試卷中各題的總分作為其實際水平指標，用各人該題的得分與各人總分之間的相關(guān)系數(shù)來計算考試中某一題的區(qū)分度，即式(1)。 (1)其中，為某題各人得分，為各人總分，為考生總?cè)藬?shù)。γ的值域為：0≤γ≤1，γ的值越大，表示區(qū)分度越高；γ＝0時，表示試題與考生學(xué)習(xí)水平無關(guān)；γ＝1時，表示試題能把考生的學(xué)習(xí)水平完全區(qū)分開來。國外一般認為γ值在以上時，該試題是優(yōu)秀的試題；~時為良好的試題；~時為合格試題；以下為不合格試題[2]。因此，我們可以根據(jù)區(qū)分度越高試題質(zhì)量越好的原則來組織和分析試卷。然而，式(1)的運算過程過于復(fù)雜，在實際工作中我們通常采用以下方法來對區(qū)分度(記為D)進行計算，具體步驟如下[2]：1。將全體考生總分從高到低順序排列；2。按試卷一定比例(一般采用25%、27%兩種比例)劃分高分組和低分組，即從最高分開始向下取25%(或27%)為高分組(H)，從最低分開始向上取25%(或27%)為低分組(L)；3。分別求出高分組和低分組中該題得分率，即和(其中為高分組的該題平均分，為低分組的該題平均分，為該題的滿分值)；4。算出該題的區(qū)分度，即式(2)。 (2)例如，根據(jù)式(2)求得表1中的區(qū)分度為：表1考試成績分組表序號12345678高分組成績9389858585序號910111213141516中間組成號171819202122中間組成績26867666666序號2324252627282930低分組成績6565656463626055ω100利用式(2)進行區(qū)分度計算，運算過程相對較為簡單，但是需要我們對每道試題得分進行手動分組，再逐個計算，這顯然造成時間上的嚴重浪費。因此，若能有一個更加簡便的方法來實現(xiàn)對區(qū)分度的快速計算，那就可以大大提高試卷質(zhì)量分析的效率。利用Excel數(shù)組公式簡化區(qū)分度計算Excel中有這樣一種公式，它可以比普通計算公式更快速地得到用戶所需要的各種數(shù)據(jù)結(jié)果，這種公式就是數(shù)組公式。其最大的優(yōu)點在于[3]：當(dāng)用戶需要對一組或多組數(shù)據(jù)進行重復(fù)計算時，只需要進行簡單的設(shè)置就可以得到所需要的計算結(jié)果。下面本文將詳細介紹利用數(shù)組公式來實現(xiàn)區(qū)分度快速計算的方法。準備數(shù)據(jù)在Excel中建立兩張工作表，表名設(shè)為“分析表”和“成績”，在“分析表”中建立如圖1所示的試卷情況分析表，在“成績”表第一行建立字段名“學(xué)號”、“各題滿分值”、“題一得分”、“題二得分”……，然后輸入相應(yīng)成績數(shù)據(jù)。圖SEQ圖表\*ARABIC1試卷情況分析表統(tǒng)計成績數(shù)目根據(jù)區(qū)分度計算的方法，計算“成績”表中“題一得分”所在列25%的成績數(shù)目(本文以“題一區(qū)分度”計算為例，并按25%比例對“題一得分”進行分組)，將計算結(jié)果存放于“分析表”C3單元格內(nèi)，具體計算公式為“=ROUND(COUNT(成績!C:C)*25%,0)”。統(tǒng)計高分組和低分組的平均分在“分析表”的D3單元格內(nèi)輸入“=AVERAGE(LARGE(成績!C:C,ROW(INDIRECT("1:"&$C$3))))”，然后按下SHIFT+CTRL+ENTER鍵(這是生成數(shù)組公式的關(guān)鍵環(huán)節(jié))，Excel將自動在公式兩側(cè)加上一對大括號，生成數(shù)組公式，即“{=AVERAGE(SMALL(成績!C:C,ROW(INDIRECT("1:"&$C$3))))}”；同理，在“分析表”的E3單元格中生成公式“{=AVERAGE(SMALL(成績!C:C,ROW(INDIRECT("1:"&$C$3))))}”。對于上述數(shù)組公式詳細說明如下：(1)數(shù)組公式仍從“＝”開始。(2)考慮到試卷分析時考生人數(shù)不盡相同，因此對“成績”表“題一得分”的引用采用“C:C”的形式，這樣公式將自動獲取當(dāng)前列擁有數(shù)據(jù)的區(qū)域。(3)ROW(INDIRECT("1:"&$C$3))函數(shù)用于循環(huán)返回25%的成績數(shù)目所在的行號。(4)LARGE和SMALL函數(shù)用于統(tǒng)計“成績”表“題一得分”列中第N個最大(小)值，其中N由ROW函數(shù)循環(huán)給出。(5)利用數(shù)組公式對循環(huán)得到的多組數(shù)組參數(shù)進行平均分計算。在單元格中數(shù)組公式輸入完成后，不能按“Enter”鍵進行確認，必須按“SHIFT+CTRL+ENTER”鍵確認，Excel將在公式兩邊自動加上“{}”(注意：大括號不能直接用鍵盤輸入)。計算區(qū)分度統(tǒng)計出高分組和低分組的平均分后，我們就可以根據(jù)式(2)進行簡單的區(qū)分度公式編輯。為了“分析表”的美觀，我們在顯示區(qū)分度結(jié)果的單元格內(nèi)設(shè)置了條件函數(shù)：當(dāng)“成績”表中沒有題目成績時，該單元格顯示“0”，若有則顯示區(qū)分度值，具體公式為“＝IF(成績!C2=0,0,(D3-E3)/成績!B2)”。至此，我們利用上述公式模板統(tǒng)計出了任意成績數(shù)目的“題一區(qū)分度”。今后，用戶只需在“成績”表的各題得分列中輸入相應(yīng)的題目得分，Excel將自動在“分析表”中顯示各題的區(qū)分度值。保護工作表最后，為了維護數(shù)據(jù)的安全性，防止非法用戶有意或無意更改公式，我們還可以對處理好的工作表進行公式隱藏和加密保護。首先選擇需要進行公式隱藏的單元格，單擊“格式”菜單→“單元格…”→“保護”，選擇“鎖定”及“保護”；然后單擊“工具”菜單→“保護”→“保護工作表?”，輸入密碼。這樣，公式不但被隱藏了，而且也只有在用戶輸入正確的密碼的前提下才允許被改動。結(jié)論利用數(shù)組公式模板實現(xiàn)對區(qū)分度的快速計算，使任課教師從繁瑣的手工統(tǒng)計數(shù)據(jù)、手工填寫試卷分析表的工作