计算机辅助英语教学与研究
（操作篇讲义）
浙江师范大学外语学院 夏建新
2006年10月
9. 用Excel计算等级相关系数
9.1 等级相关的概念
等级相关是指以等级次序排列或以等级次序表示的变量之间的相关。主要包括斯皮尔曼(Spearman)二列等级相关及肯德尔和谐系数(the Kandall Coefficient of Concordance)多列等级相关。
9.2 适用条件与计算公式
z 当测量到的数据不是等距或等比数据，而是具有等级顺序的测量数据；
z （或）得到的数据是等距或等比的测量数据，但其所来自的总体分布不是正态的; z （或）样本容量不一定大于50（或30）
都可以在无法满足积差相关系数的适用条件时，只要满足上述三个条件中的任何一个，
计算其等级相关系数。由于该系数并不要求总体是否呈正态分布，也不要求N>50（或N>30），所以应用范围较广。
斯皮尔曼等级相关系数rR的计算公式为：
6ΣD2rR=1−2n(n−1)
在该式中，D = (Rx – Ry)，它表示对偶等级之差。
9.3 操作练习
计算下表的相关系数。
学号
学习潜能 自学能力 71 7 68 7 84 2 9 76 5 69 8 90 3 71 8 66 10 71 6 199901 199902 199903 199904 199905 199906 199907 199908 199909 199910
(注：自学能力是按能力高低从小往大的数字打的，即数值越小，说明自学能力越强)
步骤一：先用Excel中的“排序”工具对“学习潜能”进行等级赋值，操作步骤如下所示： 数据 → 排序 → 主要关键字 → 学习潜能 → 递减 → 有标题行→ 确定
结果如下：
学号 学习潜能自学能力19990790 3 19990384 2 19990576 5 19990171 7 19990871 8 19991071 6 19990669 8 19990268 7 19990966 10 199904 9
然后对“学习潜能”进行赋值，结果如下：
序号 学号 学习潜能1 19990790 2 19990384 3 19990576 5 19990171 4 19990871 6 19991071 7 19990669 8 19990268
等级1
1 2 3 5 5 5 7 8 自学能力
3 2 5 7 8 6 8 7
9 19990910 19990466 9 10 10 9
说明：因4、5、6号三位学生的“学习潜能”分相等，其赋值取三者的平均等级5（计算方法为名次的总和除以同名次人数，即(4+5+6)/3=5）。
步骤二：按步骤一中所述方法对“自学能力”进行排序和赋值（考虑到“自学能力”的数值越小，等级越高，排序时应该选“递增”）。结果如下：
序号 学号 学习潜能等级1自学能力等级22 199903 84 2 2 1 1 199907 90 1 3 2 3 199905 76 3 5 3 6 199910 71 5 6 4 5 199901 71 5 7 5.5 8 199902 68 8 7 5.5 4 199908 71 5 8 7.5 7 199906 69 7 8 7.5 10 199904 10 9 9 9 199909 66 9 10 10
步骤三：算出等级差数与差数平方。在Excel表中的G2单元格中输入：=D2-F2，得数为-1。用填充柄工具完成等级差数的计算；在H2单元格中输入：=G2*G2，得数为1，用填充柄工具完成差数平方的计算。结果如下：
等级差数 差数平方
-1 1 1 1 0 0 -2.5 6.25 -0.5 0.25 1 1 -0.5 0.25 2.5 6.25
-1 1
1 1
步骤四：算出差数平方之和。用Σ工具或在某单元格输入=sum(H2:H11)，得数为18。
步骤五：把差数平方和代入公式中进行计算。在Excel中可输入=1-(6*18)/(10*(10*10-1))，得数为0.0909。
步骤六：进行显著性检验。由于样本数n=10，用t检验。在Excel中输入：=0.0909*SQRT(10-2)/SQRT(1-0.0909*0.0909)，得数为5.548155。
注意：为简便起见，上述有些数值可以直接用单元格代号代入计算公式。
步骤七：统计决断。查t值表，得t（8）0.01=3.355。我们算出的数值大于该t值，可以判定学生的学习潜能与自学能力的相关系数有显著差异，即这两者有高度的正相关。
9.4 课堂练习
以下是10个学生的数学和物理分数，请计算数学与物理的相关系数。
学号 200201200202200203200204200205200206200207200208200209200210
数学成绩物理成绩
86 70 68 65 72 82 90 92 68 61 60 94 93 86 92 66 76 70 76 9.5 积差相关与等级相关比较
例：两位英语教师(x, y)分别给20个学生的英语作文打分，用相关系数原理说明两者打分的一致性程度。
作文编号教师X 教师Y
1 17 17 2 13 14 3 8 12 4 15 17 5 15 16 6 7 7 7 10 14 8 12 10 9 11 15 10 14 13 11 15 15 12 12 15 13 10 7 14 18 16 15 15 12 16 16 16 17 10 16 18 10 10 19 19 15 20 9 8
用积差相关系数原理计算，r=0.67
注：除了“操作篇 08”所示的显著性检验方法，还可查积差相关系数检验表来进行检验。这里所查的α=0.05双尾临界值为0.444，单尾临界为0.378（自由度不用减）。结论：相关显著。
用等级相关系数原理计算，r=0.69
查等级相关系数表，α=0.05双尾临界值为0.447，单尾临界为0.380（自由度不用减）。相关显著。
之所以等级相关系数高于积差相关系数，是因为等级赋值时会出现一些并列等级，这样计算出来的相关系数会略高于积差相关系数。所以，如果能用积差相关原理计算的，尽量用积差相关公式，这样可以精确些。
9.6 肯德尔和谐系数的计算
适用情况：用于描述或检验几个评估者对一组被评者的等级评定的一致性程度，或同一评估者对同一组被评者前后几次评定结果的一致性程度。
当多个（两个以上）变量以等级次序排列或以等级次序表示，描述这几个变量之间的一致性程度（即相关）的量，称为肯德尔和谐系数。它常用来表示几个评定者对同一组学生学习成绩等级评定的一致性程度，或同一个评定者对同一组学生的学习成绩用等级先后评定多次之间的一致性程度。
公式如下：
rw=
∑R
2
123
K(n−n)12
−(∑R)2/n
其中R为某一学生得到的4个等级分之和；K为评定者的人数。
例：4位教师对6个学生作文竞赛的名次排列次序如下表。问评定的一致性程度如何(α=0.01)？
R R2 学生 评定者
(4) (3) n=6 (2)
(1) 1 2 3 4 3 4 2 1 10 100 1
4 3 1 3 11 121 2
2 1 3 4 10 100 3
6 5 6 5 22 484 4
1 2 4 2 9 81 5
5 6 5 6 22 484 6
84 1370 总和
在Excel中，输入
=(G10-POWER(F10,2)/6)/(1/12*POWER(4,2)*(POWER(6,3)-6))
得数：0.692857
检验方式为卡方检验，公式：
χ=n(m−1)rw
则：x2 = 4×(6-1) ×0.692857=13.85714
查卡方分布表，df=6-1=5的卡方临界值是15.09，结论是没有显著相关。
2
（这类检验用SPSS做更方便）
9.7 Task 9
a. 两位教师以评定等级的形式对某班级15名学生的英语口语能力进行打分，结果如下：
学生序号教师A教师B
1 11 15 2 2 1 3 5 7 4 15 12 5 9 6 6 10 10 7 1 3 8 4 5 9 13 13 10 3 2 11 8 9 12 7 8 13 6 4 14 12 14 15 14 11
请计算两组等级之间的等级相关系数，并对之进行假设检验（α=0.01，双尾）
b. 自建一学生成绩或某两个项目（如德育与英语、学习动机与英语成绩、家庭条件与学习成绩、父母关心程度与学习动机，等等）的评比等级数据库，对其进行等级相关系数的计算和显著性检验，并简要说明之。
（a、b任选其一）
