Excel数组公式在区分度计算中的应用 excel公式计算

excel公式计算 Excel数组公式在区分度计算中的应用摘要本文通过对区分度概念及其计算方法的分析，提出了一个使用Excel数组来进行区分度运算的公式。

该公式适用于任意考生人数的试题区分度计算，具有较强的通用性。

关键词区分度 Excel数组公式计算[文献标识码] A [中图分类号] G424.74 On the Application of Excel Array Formula to Calculate The Distinction Degree Tao ying1 Lin Peiyan 2 12(Zhejiang Medical College, Hangzhou 310053，China) (Zhejiang Medical College, Hangzhou 310053，China) Abstract Through the analysis of distinction degree conception and its calculation method, this thesis puts forward a formula that adopts Excel array to calculate the distinction degree. As the formula is applicable to calculate the test paper distinction degree for any number of students, it can be widely used. Keywords Distinction Degree Excel Array Formula Calculate 0 引言我们常常要对教学质量进行评估，目前普遍采用的手段是考试，通过考试卷面成绩来衡量教学质量的高低。

为此，我们必须对试卷进行综合分析，使试卷能准确、全面、科学地反映考生的实际情况。

1 区分度的概念及计算方法 1.1 区分度的概念试题质量分析的一个重要指标是区分度。

所谓区分度是指试题对不同考生的知识、能力水平的鉴别程度[1]。

如果试卷中的试题使水平高的考生都答对，而使水平差的考生都答错，那么该试题的区分能力就强。

1.2 区分度的计算方法一般我们以考生在一份试卷中各题的总分作为其实际水平指标，用各人该题的得分与各人总分之间的相关系数来计算考试中某一题的区分度，即式(1)。

???xy??x?y ?n?x???x???n?y???y??niiii2222iiii(1) 其中，xi为某题各人得分，yi 为各人总分，n为考生总人数。

γ的值域为：0≤γ≤1，γ的值越大，表示区分度越高；γ＝0时，表示试题与考生学习水平无关；γ＝1时，表示试题能把考生的学习水平完全区分开来。

国外一般认为γ值在0.40以上时，该试题是优秀的试题； 0.30~0.39时为良好的试题；
0.20~0.29时为合格试题；0.19以下为不合格试题[2]。

因此，我们可以根据区分度越高试题质量越好的原则来组织和分析试卷。

然而，式(1)的运算过程过于复杂，在实际工作中我们通常采用以下方法来对区分度(记为D)进行计算，具体步骤如下[2]：。

1将全体考生总分从高到低顺序排列；。

2按试卷一定比例(一般采用25%、27%两种比例)划分高分组和低分组，即从最高分开始向下取25%(或27%)为高分组(H)，从最低分开始向上取25%(或27%)为低分组(L)；。

3分别求出高分组和低分组中该题得分率，即P?xH和P?xL(其中xH为高分组的该题平均分，xL为低分组的该题平均LH??分，?为该题的满分值)；
4算出该题的区分度，即式(2)。

D?PH?PL?例如，根据式(2)求得表1中的区分度为： xH?xL?
(2) D?0.886?0.623?88.63?62.25?0.263 100表 1 考试成绩分组表序号高分组成绩序号中间组成绩1 序号中间组成绩2 序号低分组成绩ω 1 93 9 74.5 17 68.5 23 65 2 3 4 5 89 13 73 21 66 27 63 6 7 8 85 16 92.5 90.5 89.5 10 74.5 18 68 24 65 11 74 19 67 25 65 12 74 20 66 26 64 100 88.63 0.886 62.25 0.623 85 85 14 15 71 70 69.5 22 66 30 55 28 29 62 60 xH PH xL PL 利用式(2)进行区分度计算，运算过程相对较为简单，但是需要我们对每道试题得分进行手动分组，再逐个计算，这显然造成时间上的严重浪费。

因此，若能有一个更加简便的方法来实现对区分度的快速计算，那就可以大大提高试卷质量分析的效率。

2 利用Excel数组公式简化区分度计算 Excel中有这样一种公式，它可以比普通计算公式更快速地得到用户所需要的各种数据结果，这种公式就是数组公式。

其最大的优点在于[3]：当用户需要对一组或多组数据进行重复计算时，只需要进行简单的设置就可以得到所需要的计算结果。

下面本文将详细介绍利用数组公式来实现区分度快速计算的方法。

2.1 准备数据在Excel中建立两张工作表，表名设为“分析表”和“成绩”，在“分析表”中建立如图1所示的试卷情况分析表，在“成绩”表第一行建立字段名“学号”、“各题满分值”、“题
一得分”、“题二得分”,,,,，然后输入相应成绩数据。

图1 试卷情况分析表 2.2 统计成绩数目根据区分度计算的方法，计算“成绩”表中“题一得分”所在列25%的成绩数目(本文以“题一区分度”计算为例，并按25%比例对“题一得分”进行分组)，将计算结果存放于“分析表”C3单元格内，具体计算公式为“=ROUND(COUNT(成绩!C:C)* 25%,0)”。

2.3 统计高分组和低分组的平均分在“分析表”的D3单元格内输入“=AVERAGE(LARGE(成绩!C:C,ROW(INDIRECT("1:"&$C$3))))”，然后按下SHIFT+CTRL+ENTER键(这是生成数组公式的关键环节)，Excel将自动在公式两侧加上一对大括号，生成数组公式，即“{=AVERAGE(SMALL(成绩!C:C,ROW(INDIRECT("1:"&$C$3))))}”；同理，在“分析表”的E3单元格中生成公式
“{=AVERAGE(SMALL(成绩! C:C,ROW(INDIRECT("1:"&$C$3))))}”。

对于上述数组公式详细说明如下： (1)数组公式仍从“＝”开始。

(2)考虑到试卷分析时考生人数不尽相同，因此对“成绩”表“题一得分”的引用采用“C:C”的形式，这样公式将自动获取当前列拥有数据的区域。

(3)ROW (INDIRECT("1:"&$C$3)) 函数用于循环返回25%的成绩数目所在的行号。

(4)LARGE和SMALL函数用于统计“成绩”表“题一得分”列中第N个最大(小)值，其中N由ROW函数循环给出。

(5)利用数组公式对循环得到的多组数组参数进行平均分计算。

在单元格中数组公式输入完成后，不能按“Enter”键进行确认，必须按“SHIFT+CTRL+ENTER”键确认，Excel将在公式两边自动加上“{}”(注意：大括号不能直接用键盘输入)。

2.4 计算区分度统计出高分组和低分组的平均分后，我们就可以根据式(2)进行简单的区分度公式编辑。

为了“分析表”的美观，我们在显示区分度结果的单元格内设置了条件函数：当“成绩”表中没有题目成绩时，该单元格显示“0”，若有则显示区分度值，具体公式为“＝IF(成绩!C2=0,0,(D3-E3)/成绩!B2)”。

至此，我们利用上述公式模板统计出了任意成绩数目的“题一区分度”。

今后，用户只需在“成绩”表的各题得分列中输入相应的题目得分，Excel将自动在“分析表”中显示各题的区分度值。

2.5 保护工作表最后，为了维护数据的安全性，防止非法用户有意或无意更改公式，我们还可以对处理好的工作表进行公式隐藏和加密保护。

首先选择需要进行公式隐藏的单元格，单击“格式”菜单→“单元格…”→“保护”，选择“锁定”及“保护”；然后单击“工具”菜单→“保护”→“保护工作表?”，输入密码。

这样，公式不但被隐藏了，而且也只有在用户输入正确的密码的前提下才允许被改动。

3 结论利用数组公式模板实现对区分度的快速计算，使任课教师从繁琐的手工统计数据、手工填写试卷分析表的工作中解放出来，还可以用于试卷质量分析其他指标的计算，减轻了用户的统计工作，达到了简便、快速、准确的目的。

参考文献 [1] 吴珍珠.浅议区分度与试题质量的关系.生物学通报,2007,42(2):46-47. [2] 王孝玲. 教育测量[M ]. 上海: 华东师范大学出版社, 2002. [3] 日花弘子(日). Excel函数与公式辞典.北京:中国青年出版社,2007.
excel公式计算。