计算机辅助英语教学与研究（操作篇）浙江师范大学外语学院夏建新第9讲用Excel计算等级相关系数目次9.1 等级相关的概念 (1)9.2 适用条件与计算公式 (1)9.3 操作练习 (1)9.4 课堂练习 (3)9.5 积差相关与等级相关比较 (4)9.6 肯德尔和谐系数的计算 (5)9.7 Task 9 (6)9.1 等级相关的概念等级相关是指以等级次序排列或以等级次序表示的变量之间的相关。

主要包括斯皮尔曼(Spearman)二列等级相关及肯德尔和谐系数(the Kandall Coefficient of Concordance)多列等级相关。

9.2 适用条件与计算公式z当测量到的数据不是等距或等比数据，而是具有等级顺序的测量数据；z（或）得到的数据是等距或等比的测量数据，但其所来自的总体分布不是正态的;z（或）样本容量不一定大于50（或30）在无法满足积差相关系数的适用条件时，只要满足上述三个条件中的任何一个，都可以计算其等级相关系数。

由于该系数并不要求总体是否呈正态分布，也不要求N>50（或N>30），所以应用范围较广。

斯皮尔曼等级相关系数r R的计算公式为：在该式中，D = (Rx – Ry)，它表示对偶等级之差。

9.3 操作练习计算下表的相关系数。

学号学习潜能自学能力199901 71 7199902 68 7199903 84 2199904 64 9199905 76 5199906 69 8199907 90 3199908 71 8199909 66 10199910 71 6(注：自学能力是按能力高低从小往大的数字打的，即数值越小，说明自学能力越强)步骤一：先用Excel中的“排序”工具对“学习潜能”进行等级赋值，操作步骤如下所示：数据→ 排序 → 主要关键字 → 学习潜能 → 递减 → 有标题行→ 确定结果如下：学号 学习潜能自学能力19990790 319990384 219990576 519990171 719990871 819991071 619990669 819990268 719990966 1019990464 9然后对“学习潜能”进行赋值，结果如下：序号学号学习潜能等级1 自学能力1 19990790 1 32 19990384 2 23 19990576 3 55 19990171 5 74 199908715 86 19991071 5 67 19990669 7 88 19990268 8 79 19990966 9 1010 19990464 10 9说明：因4、5、6号三位学生的“学习潜能”分相等，其赋值取三者的平均等级5（计算方法为名次的总和除以同名次人数，即(4+5+6)/3=5）。

步骤二：按步骤一中所述方法对“自学能力”进行排序和赋值（考虑到“自学能力”的数值越小，等级越高，排序时应该选“递增”）。

结果如下：序号学号学习潜能等级1自学能力等级22 199903 84 2 2 11 199907 90 1 3 23 199905 76 3 5 36 199910 71 5 6 45 199901 71 5 7 5.58 199902 68 8 7 5.54 199908 715 8 7.57 199906 69 7 8 7.510 199904 64 10 9 9199909 66 9 10 109步骤三：算出等级差数与差数平方。

在Excel表中的G2单元格中输入：=D2-F2，得数为-1。

用填充柄工具完成等级差数的计算；在H2单元格中输入：=G2*G2，得数为1，用填充柄工具完成差数平方的计算。

结果如下：等级差数 差数平方-1 11 10 0-2.5 6.25-0.5 0.251 1-0.5 0.252.5 6.25-1 11 1步骤四：算出差数平方之和。

用Σ工具或在某单元格输入=sum(H2:H11)，得数为18。

步骤五：把差数平方和代入公式中进行计算。

在Excel中可输入=1-(6*18)/(10*(10*10-1))，得数为0.890909。

步骤六：进行显著性检验。

由于样本数n=10，用t检验。

在Excel中输入：=0.890909*SQRT(10-2)/SQRT(1-0.890909*0.890909)，得数为5.548155。

注意：为简便起见，上述有些数值可以直接用单元格代号代入计算公式。

步骤七：统计决断。

查t值表，得t（8）0.01=3.355。

我们算出的数值大于该t值，可以判定学生的学习潜能与自学能力的相关系数有显著差异，即这两者有高度的正相关。

9.4 课堂练习以下是10个学生的数学和物理分数，请计算数学与物理的相关系数。

学号数学成绩物理成绩20020186 7020020268 6520020372 8220020490 9220020564 6820020661 6020020794 9320020886 9220020966 7620021070 769.5 积差相关与等级相关比较例：两位英语教师(x, y)分别给20个学生的英语作文打分，用相关系数原理说明两者打分的一致性程度。

作文编号教师X 教师Y1 17 172 13 143 8 124 15 175 15 166 7 77 10 148 12 109 11 1510 14 1311 15 1512 12 1513 10 714 18 1615 15 1216 16 1617 10 1618 10 1019 19 1520 9 8用积差相关系数原理计算，r=0.67注：除了“操作篇 08”所示的显著性检验方法，还可查积差相关系数检验表来进行检验。

这里所查的α=0.05双尾临界值为0.444，单尾临界为0.378（自由度不用减）。

结论：相关显著。

用等级相关系数原理计算，r=0.69查等级相关系数表，α=0.05双尾临界值为0.447，单尾临界为0.380（自由度不用减）。

相关显著。

之所以等级相关系数高于积差相关系数，是因为等级赋值时会出现一些并列等级，这样计算出来的相关系数会略高于积差相关系数。

所以，如果能用积差相关原理计算的，尽量用积差相关公式，这样可以精确些。

9.6 肯德尔和谐系数的计算适用情况：用于描述或检验几个评估者对一组被评者的等级评定的一致性程度，或同一评估者对同一组被评者前后几次评定结果的一致性程度。

当多个（两个以上）变量以等级次序排列或以等级次序表示，描述这几个变量之间的一致性程度（即相关）的量，称为肯德尔和谐系数。

它常用来表示几个评定者对同一组学生学习成绩等级评定的一致性程度，或同一个评定者对同一组学生的学习成绩用等级先后评定多次之间的一致性程度。

公式如下：)(121/)(3222n n K nR R w r −−∑∑=其中R 为某一学生得到的4个等级分之和；K 为评定者的人数。

例：4位教师对6个学生作文竞赛的名次排列次序如下表。

问评定的一致性程度如何(α=0.01)？评定者(2)R (3) R 2 (4) 学生 n=6 (1) 1 2 3 413 4 2 1 10 100 24 3 1 3 11 121 32 134 10 100 46 5 6 5 22 484 51 2 4 2 9 81 65 6 5 6 22 484 总和84 1370在Excel 中，输入=(G10-POWER(F10,2)/6)/(1/12*POWER(4,2)*(POWER(6,3)-6))得数：0.692857检验方式为卡方检验，公式：w r m n )1(2−=χ则：x 2 = 4×(6-1) ×0.692857=13.85714查卡方分布表，df=6-1=5的卡方临界值是15.09，结论是没有显著相关。

（这类检验用SPSS做更方便）9.7 Task 9a. 两位教师以评定等级的形式对某班级15名学生的英语口语能力进行独立打分，结果如下：学生序号教师A教师B1 11 152 2 13 5 74 15 125 9 66 10 107 1 38 4 59 13 1310 3 211 8 912 7 813 6 414 12 1415 14 11请计算两组等级之间的等级相关系数，并对之进行假设检验（α=0.01，双尾）b. 自建一学生成绩或某两个项目（如德育与英语、学习动机与英语成绩、家庭条件与学习成绩、父母关心程度与学习动机，等等）的评比等级数据库，对其进行等级相关系数的计算和显著性检验，并简要说明之。

（a、b任选其一）。