如何用EXCEL判断调查对象特性之间的相关性—列联表

列联表是观测数据按两个或更多属性（定性变量）进行交叉分类时所列出的频数表。列联表分析常用来判断同一个调查对象的两个特性之间是否存在明显相关性。

一个实际频数fij的期望频数eij，是总频数的个数n乘以该实际频数fij落入第i行和第j列的概率，即

χ2统计量的计算公式为

其自由度为（r-1）（c-1）。χ2独立性检验可以检验列联表中行变量与列变量之间的相关性。根据显著性水平α和自由度（r-1）（c-1）查出临界值χa2，若χ2>=χa2则行变量与列变量之间是相关的；若χ2<=χa2，则行变量与列变量之间独立。

Excel提供函数CHITEST计算χ2统计量的概率，提供函数CHIINV计算临界值χα2。

函数语法：CHITEST(actual_range, expected_range)

CHITEST函数语法具有以下参数。

● actual_range为包含观察值的数据区域，将对期望值作检验。

● expected_range为包含行列汇总的乘积与总计值之比率的数据区域。

函数语法：CHIINV(probability, degrees_freedom)

CHIINV函数语法具有以下参数。

● probability为与χ2分布相关的概率。

● degrees_freedom为自由度的数值。

Step 01：先在A8:F12单元格范围建立期望频数表的框架。

Step 02：单击B9单元格，在编辑栏中输入公式“=B$6*$F3/$F$6”，然后按回车键结束；再单击B9单元格，将鼠标指针移动至B9单元格右下角，当鼠标指针变为小黑色十字形状时按下鼠标左键拖曳至E11单元格，求出B9:E11各单元格值。

Step 03：利用Excel的求和函数SUM计算行总数。单击F9单元格，在编辑栏中输入“=SUM(B9:E9)”，按回车键；再单击F9单元格，将鼠标指针移动至F9单元格右下角，当鼠标指针变为黑色十字形状时，按下鼠标左键拖曳至F12单元格，利用自动填充功能求出各行总数。

Step 04：计算列总数。单击B12，在编辑栏中输入“=SUM(B9:B12)”，按回车键；然后单击B12单元格，将鼠标指针移动至B12单元格右下角，当鼠标指针变为黑色十字形状时，按下鼠标左键并拖曳至F12单元格，求出各列总数，从而建立期望频数表，如图所示。

Step 05：在A14单元格输入标志项“卡方概率值”，先点击B14单元格，单击菜单栏【公式】/【插入函数】命令，弹出【插入函数】对话框，在【或选择类别】一项选择【统计】；在【选择函数】中选择【CHITEST】函数。

Step 06：单击【插入函数】对话框【确定】按扭，弹出【函数参数】对话框；【Actual_range】后选择B3:E5单元格区域；【Expected_range】后选择B9:E11单元格区域；最后单击【确定】按扭，即可得到卡方概率值1.30821E-07，如图所示。

Step 07：求χ2统计量。在A15单元格输入标志项“卡方统计量”，单击B15单元格，在编辑栏中输“=SUM((B3:E5-B9:E11)^2/B9:E11)”，完成后按Ctrl+Shift+Enter组合键结束，结果如图所示。

Step 08：进行假设检验。在A17单元格输入标志项“置信水平”，在B17单元格输入0.01；在A18单元格输入标志项“临界值”，单击B18单元格，在编辑栏中输入公式“=CHIINV(B17,6)”，按回车键；在A19单元格输入标志项“检验结果”，单击B19单元格，在编辑栏中输入公式“=IF(B15＞B18, “拒绝两种属性不相关的假设”, “接受两种属性不相关的假设”)”，按回车键。结果如图所示。

CHIINV函数的自由度=(第一类属性的分类数-1)×(第二类属性的分类数-1)，即(r-1)(c-1)=(3-1)×(4-1)=6。