利用Excel进行主成分分析

11 利用 Excel2000 进行主成分分析第一步，录入数据，并对进行标准化例】一组古生物腕足动物贝壳标本的两个变量：长度和宽度图 1 原始数据和标准化数据及其均值、方差（取自张超、杨秉庚《计量地理学基础》）计算的详细过程如下：⑴ 将原始数据绘成散点图（图 2）主持分分析原则上要求数据具有线性相关趋势——如果数据之间不相关（即正交），则没有必要进行主成分分析，因为主成分分析的目的就是用正交的变量代替原来非正交的变量；如果原始数据之间为非线性关系，则有必要对数据进行线性转换，否则效果不佳从图 2 可见，原始数据具有线性相关趋势，且测定系数 R2=0.4979，相应地，相关系数 R=0.7056⑵ 对数据进行标准化标准化的数学公式为2jiijx*这里假定按列标准化，式中，nijijx1 )(Var)(12ijnijijij xx分别为第 j 列数据的均值和标准差， 为第 i 行（即第 i 个样本）、第 j 列（即第 j 个变量）ij的数据， 为相应于 的标准化数据， 为样本数目ijxij 5原 始 数 据 的 散 点 图 y = 0.7686x + 2.3174R2 = 0.497905101520250 5 10 15 20 25长 度宽度图 2 原始数据的散点图标 准 化 数 据 的 散 点 图 y = 0.7056x + 2E-16R2 = 0.4979-2.000000-1.0000000.0000001.0000002.0000003.000000-2.000000 -1.000000 0.000000 1.000000 2.000000 3.000000长 度宽度图 3 标准化数据的散点图对数据标准化的具体步骤如下：① 求出各列数据的均值，命令为 average，语法为：3average(起始单元格 :终止单元格)。

如图 1 所示，在单元格 B27 中输入“=AVERAGE(B1:B26)”，确定或回车，即得第一列数据的均值 ；然后抓住单8.10x元格 B27 的右下角（ 光标的十字变细）右拖至 C27，便可自动生成第二列数据的均值68.102x②求各列数据的方差命令为 varp，语法同均值如图 1 所示，在单元格 B28 中输入“=VARP(B2:B26)”，确定或回车，可得第一列数据的方差 ，右拖至465.9)(VarxC28 生成第二列数据的方差 0976.23)(Varx③ 求各列数据的标准差将方差开方便得标准差也可利用命令 stdevp 直接生成标准差，语法和操作方法同均值、方差，不赘述④ 标准化计算如图 1 所示，在单元格 D2 中输入“=(B2-$B$27)/$B$29”，回车可得第一列第一个数据“3”的标准化数值-1.786045，然后按住单元格 D2 的右下角下拖至D26，便会生成第一列数据的全部标准化数值；按照单元格 D2 的右下角右拖至 E2，就能生成第二列第一个数据“2”的标准化数据-1.806077，抓住单元格 E2 的右下角下拖至E26 便会生成第二列数据的全部标准化数值。

⑤ 作标准化数据的散点图（图 3）可以看出，点列的总体趋势没有变换，两种数据的相关系数与标准化以前完全相同但回归模型的截距近似为 0，即有 ，斜率等于0a相关系数，即有 Rb⑶ 求标准化数据的相关系数矩阵或协方差矩阵求相关系数矩阵的方法是：沿着“工具（T）”→“数据分析（D）”的路径打开“分析工具（A）”选项框（图 4），确定，弹出“相关系数”对话框（图 5），在“输入区域”的空白栏中输入标准化数据范围，并以单元格 G1 为输出区域，具体操作方法类似于回归分析确定，即会在输出区域给出相关图 4 分析工具选项框图 5 相关系数对话框4系数矩阵的下三角即对角线部分，由于系对称矩阵，上三角的数值与下三角相等，故未给出（图 6），可以通过“拷贝——转置——粘帖”的方式补充空白部分图 6 标准化数据的相关系数和协方差求协方差的方法是在“分析工具”选项框中选择“协方差”（图 7），弹出“协方差”选项框（图 8），具体设置与“相关系数”类似，不赘述结果见图 6，可以看出，对于标准化数据而言，协方差矩阵与相关系数矩阵完全一样因此，二者任取其一即可图 7 在分析工具选项框中选择“协方差”图 8 协方差选项框⑷ 计算特征根。

我们已经得到相关系数矩阵为，17056..C而二阶单位矩阵为，I5于是根据公式 ，我们有0)det(CI 017056..1756..1 按照行列式化为代数式的规则可得 2.0.)(22根据一元二次方程的求根公式，当 时，我们有4acb据此解得 ， （对于本例，显然 ， ）这便是7056.129. R12相关系数矩阵的两个特征根⑸ 求标准正交向量将 代入矩阵方程 ，得到1 0)(CI756.0. 21在系数矩阵 中，用第一行加第二行，化为CI 0.. 21由此得 ，令 ，则有 ，于是得基础解系2112，单位化为71.1e单位化的公式为 （ ）21iie,i完全类似，将 代入矩阵方程 ，得到20)(CI756.0. 21用系数矩阵的第二行减去第一行，化为 0.. 21于是得到 ，取 ，则有 ，因此得基础解系为2112，单位化为271.2e这里 、 便是标准正交向量。

1e2⑹ 求对角阵首先建立标准正交矩阵 P，即有 701..0][21e6该矩阵的一个特殊性质便是 ，即矩阵的转置等于矩阵的逆根据 ，可1PT CPDT知  701..07056...701.D 2934.056.下面说明一下利用 Excel 进行矩阵乘法运算的方法矩阵乘法的命令为 mmult，语法是mmult (矩阵 1 的单元格范围，矩阵 2 的单元格范围)例如，用矩阵 与矩阵 C 相乘，TP首先选择一个输出区域如 G1:H2，然后输入“=mmult(A1:B2,C1:D2)”，然后按下“Ctrl+Shift+Enter”键（图 9），即可给出1.206044 1.2060440.20817 -0.20817再用乘得的结果与 P 阵相乘，便得对角矩阵1.705603 00 0.294397如果希望一步到位也不难，选定输出区域如 C3:D4，然后输入“=mmult(mmult(A1:B2,C1:D2),E1:F2)” （图 10），同时按下“Ctrl+Shift+Enter”键，立即得到结果（图 11）显然，对角矩阵对角线的数值恰是相关系数矩阵的特征值。

图 9 矩阵乘法示例图 10 矩阵连乘的命令与语法至此，标准化的原始变量 x 与主成分之间 z 之间可以表作 2121221 94.0756.17056.. zx7显然 与 之间正交1z2图 11 乘法结果：对角矩阵⑺ 根据特征根计算累计方差贡献率现已求得第一特征根为 ，第二特征根为7056.1，二者之和刚好就是矩阵的维数，即有 ，这里 m=2 为变量数294.0 221目（注意前面的 n=25 为样本数目）比较 图 6 或图 10 中给出的相关系数矩阵 C 与图 11中给出的对角矩阵 D 可以看出，Tr.(C)=1+1=2，Tr.(D)=1.7056+0.2944=2，即有 Tr.(C)= Tr.(D)，可见将相关系数亦即协方差矩阵转换为对角矩阵以后，矩阵的迹（trace，即对角线元素之和）没有改变，这意味着将原始变量化为主成分以后，系统的信息量没有减少现在问题是，如果我们只取一个主成分代表原来的两个变量，能反映原始变量的多少信息？这个问题可以借助相关系数矩阵的特征根来判断利用 Excel 容易算出，第一特征根占特征根总和即矩阵维数的 85.28%（见下表），即有特征根 累计值 百分比 累计百分比1.705603 1.705603 85.28% 85.28%0.294397 2 14.72% 100.00%也就是说：：1.7056，1%28.5/706.1/m：0.2944，2 7492：2，)(这表明，如果仅取第一个主成分，可以反映原来数据 85.28%的信息——换言之，舍弃第二个主成分，原来数据的信息仅仅损失 14.72%，但分析变量的自由度却减少一个，整个分析将会显得更加简明。

⑻ 计算主成分载荷根据公式 ，容易算出jje9235.071.056.187..294.⑼ 计算公因子方差和方差贡献根据上述计算结果可以比较公因子方差和方差贡献再考虑全部的两个主成分的时候，对应于 和 的公因子方差分别为12138.095.221 jiV81)387.0(925.22 jiV对应于第一主成分 z1 和第二主成分 z2 的方差贡献分别为56...02ijC940)387(22ij可以看出（图 12）： 第一，方差贡献等于对应主成分的特征根，即有 jjCV第二，公因子方差相等或彼此接近，即有 21第一，公因子方差之和等于方差贡献之和，即有 mjji第一个规律是我们决定提取主成分数目的判据与之一，第二个规律是我们判断提取主成分数目是否合适的判据之一，第三个规律是我们判断提取主成分后是否损失信息的判据之一去掉次要的主成分以后，上述规律理当仍然满足这时如果第二个规律不满足，就意味着主成分的提取是不合适的此外，上述规律也是我们检验计算结果是否正确的判据之一图 12 公因子方差、方差贡献的计算结果及其与特征根的贡献⑽ 计算主成分得分。

根据主成分与原始变量的关系，应有 XPZT或者对于本例而言，式中， ，21xX21zZ2121ee 701..0这里 ， 为前面计算的标准化特征向量于是有Te11 T1 2127.0.xz化为代数形式便是 11..x22z式中的 x 均为标准化数据对 进行转置，可得XPZTT9图 13 计算特征向量的公式及语法图 14 计算主成分得分根据这个式子，利用 Excel 计算主成分得分的步骤如下：① 将特征向量复制到标准化数据的附近；10② 选中一个与标准化数据占据范围一样大小的数值区域（如 G2:H26）；③ 输入如下计算公式“=mmult(标准化数据的范围，特征向量的范围)”，在本例中就是“=MMULT(B2:C26,E2:F3)”（图 13）；④ 同时按下“Ctrl+Shift+Enter”键⑤ 计算主成分得分的均值和方差，可以发现，均值为 0（由于误差之故，约等于 0），方差等于特征根⑥ 最后，可以对主成分得分进行标准化已知主成分得分的均值为 0,我们不按总体方差进行标准化，而按样本方差进行标准化。

图 15 主成分得分的标准化结果样本方差的计算公式为 nijijj xx12)()(Var相应地，标准差为11nijijjj xx12)()(Var标准化公式同前面给出的一样结果见表 15注意，这里之所以按样本方差进行标准化，主要目的是为了与 SPSS 的计算结果进行比较分别以 z1、z 2 为坐标轴，将主成分得分（包括标准化的得分）点列标绘于坐标图中，可以发现，点列分布没有任何趋势：回归结果表明，回归系数和相关系数均为零，即有， ， （图 16，图 17）这从几何图形上显示：主成分之间是正交的，0ab0R即有 （试将图 16、图 17 与图 2、图 3 对比）cos。