1.1 列联表

列联表是观测数据按两个或更多属性（定性变量）进行交叉分类时所列出的频数表。列联表分析常用来判断同一个调查对象的两个特性之间是否存在明显相关性。例如，房地产商常常设计相关列联表问卷，调查顾客的职业类型和顾客所选房子的户型是否有明显的相关性。同样，列联表分析也可以在Excel 2007中实现。

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

χ2统计量的计算公式为

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

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

函数语法：CHITEST(actual_range, expected_range)

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

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

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

函数语法：CHIINV(probability, degrees_freedom)

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

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

● degrees_freedom为自由度的数值。

例1.1 顾客所在地区和所选房子地板类型之间的相关性分析

下面用一个具体例子说明列联表相关性分析。表1.1是某装修公司的调查报告数据表，用列联表分析方法分析顾客所在地区与所选房子地板类型之间是否存在明显的相关性。

新建工作表“例1.1 装修公司的调查报告数据.xlsx”，输入表1.1中的调查报告数据，如图1.1所示。

下面使用Excel 2007进行相关性分析，具体操作步骤如下。

Step 01：打开“例1.1 装修公司的调查报告数据.xlsx”，如图1.2所示，先在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单元格，求出各列总数，从而建立期望频数表，如图1.3所示。

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

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

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

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

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

【结论】

以上的操作步骤即完成对整个列联表的分析，从图1.8所示中可以看出，B14单元格的卡方概率值与B15单元格的卡方统计量是表格的两个重要计算结果。其中卡方概率值等于1.30821E-07，表明如果总体的两类属性，即所在地区和所选地板类型，是不相关的，那么得到以上观察的样本的概率是0.000000130821，这个概率几乎等于0，所以可以认为总体的这两个属性是显著相关的。