Excel數(shù)組公式在區(qū)分度計(jì)算中的應(yīng)用

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