利用Excel进行主成分分析的具体操作

1 利用Excel2000进行主成分分析
举例如下：
第一步，录入数据，并对进行标准化。

【例】一组古生物腕足动物贝壳标本的两个变量：长度和宽度。

图1 原始数据和标准化数据及其均值、方差
（取自张超、杨秉庚《计量地理学基础》）
计算的详细过程如下：
⑴将原始数据绘成散点图（图2）。

主持分分析原则上要求数据具有线性相关趋势——如果数据之间不相关（即正交），则没有必要进行主成分分析，因为主成分分析的目的就是用正交的变量代替原来非正交的变量；如果原始数据之间为非线性关系，则有必要对数据进行线性转换，否则效果不佳。

从图2可见，原始数据具有线性相关趋势，且测定系数R2=0.4979，相应地，相关系数R=0.7056。

⑵对数据进行标准化。

标准化的数学公式为
j j
ij ij x x x σ-=*
这里假定按列标准化，式中
∑==n
i ij ij x n x 11，)(Var )(1
2ij n i j ij ij x x x =-=∑=σ 分别为第j 列数据的均值和标准差，ij x 为第i 行（即第i 个样本）、第j 列（即第j 个变量）的数据，*
ij x 为相应于ij x 的标准化数据，25=n 为样本数目。

图2 原始数据的散点图
图3 标准化数据的散点图
对数据标准化的具体步骤如下：① 求出各列数据的均值，命令为average ，语法为：
average(起始单元格:终止单元格)。

如图1所示，在单元格B27中输入“=AVERAGE(B1:B26)”，确定或回车，即得第一列数据的均值88.101=x ；然后抓住单元格B27的右下角（光标的十字变细）右拖至C27，便可自动生成第二列数据的均值68.102=x 。

②求各列数据的方差。

命令为varp ，语法同均值。

如图1所示，在单元格B28中输入“=VARP(B2:B26)”，确定或回车，可得第一列数据的方差4656.19)(V ar 1=x ，右拖至C28生成第二列数据的方差0976.23)(V ar 2=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 ，斜率等于相关系数，即有R b =。

⑶ 求标准化数据的相关系数矩阵或协方差矩阵。

求相关系数矩阵的方法是：沿着“工具（T ）”→“数据分析（D ）”的路径打开“分析工具（A ）”选项框（图4），确定，弹出“相关系数”对话框（图5），在“输入区域”的空白栏中输入标准化数据范围，并以单元格G1为输出区域，具体操作方法类似于回归分析。

确定，即会在输出区域给出相关
图4 分析工具选项框
图5 相关系数对话框
系数矩阵的下三角即对角线部分，由于系对称矩阵，上三角的数值与下三角相等，故未给出（图6），可以通过“拷贝——转置——粘帖”的方式补充空白部分。

图6 标准化数据的相关系数和协方差
求协方差的方法是在“分析工具”选项框中选择“协方差”（图7），弹出“协方差” 选项框（图8），具体设置与“相关系数”类似，不赘述。

结果见图6，可以看出，对于标准化数据而言，协方差矩阵与相关系数矩阵完全一样。

因此，二者任取其一即可。

图7 在分析工具选项框中选择“协方差”
图8 协方差选项框
⑷ 计算特征根。

我们已经得到相关系数矩阵为
⎥⎦
⎤⎢⎣⎡=17056.07056.01C ， 而二阶单位矩阵为
⎥⎦
⎤⎢⎣⎡=1001I ，
于是根据公式0)det(=-C I λ，我们有
017056.07056.01
17056.07056.01
100
1=----=-λλλ
按照行列式化为代数式的规则可得
05021.027056.0)1(222=+-=--λλλ
根据一元二次方程的求根公式，当042≥-ac b 时，我们有
a
ac b b 242-±-=λ 据此解得7056.11=λ，2944.02=λ（对于本例，显然R +=11λ，R -=12λ）。

这便是相关系数矩阵的两个特征根。

⑸ 求标准正交向量。

将1λ代入矩阵方程0)(=ψ-C I λ，得到
⎥⎦
⎤⎢⎣⎡=⎥⎦⎤⎢⎣⎡⎥⎦⎤⎢⎣⎡--007056.07056.07056.07056.021ψψ 在系数矩阵C I -λ中，用第一行加第二行，化为
⎥⎦⎤⎢⎣⎡=⎥⎦⎤⎢⎣⎡⎥⎦⎤⎢⎣
⎡-00007056.07056.021ψψ 由此得21ψψ=，令11=ψ，则有12=ψ，于是得基础解系
⎥⎦⎤⎢⎣⎡=111ξ，单位化为⎥⎦
⎤⎢⎣⎡=7071.07071.01e 单位化的公式为2221ψψψ+=i
i e （2,1=i ）。

完全类似，将2λ代入矩阵方程0)(=ψ-C I λ，得到
⎥⎦
⎤⎢⎣⎡=⎥⎦⎤⎢⎣⎡⎥⎦⎤⎢⎣⎡----007056.07056.07056.07056.021ψψ 用系数矩阵的第二行减去第一行，化为
⎥⎦
⎤⎢⎣⎡=⎥⎦⎤⎢⎣⎡⎥⎦⎤⎢⎣⎡--00007056.07056.021ψψ 于是得到21ψψ-=，取11=ψ，则有12-=ψ，因此得基础解系为
⎥⎦⎤⎢⎣⎡-=112ξ，单位化为⎥⎦
⎤⎢⎣⎡-=7071.07071.02e 这里1e 、2e 便是标准正交向量。

⑹ 求对角阵。

首先建立标准正交矩阵P ，即有
⎥⎦
⎤⎢⎣⎡-==7071.07071.07071.07071.0][21e e P
该矩阵的一个特殊性质便是1-=P P T ，即矩阵的转置等于矩阵的逆。

根据CP P D T =，可知
⎥⎦⎤⎢⎣⎡-⎥⎦⎤⎢⎣⎡⎥⎦⎤⎢⎣⎡-=7071.07071.07071.07071.017056.07056.017071.07071.07071.07071.0D ⎥⎦
⎤⎢⎣⎡=2934.0007056.1 下面说明一下利用Excel 进行矩阵乘法运算的方法。

矩阵乘法的命令为mmult ，语法是mmult (矩阵1的单元格范围，矩阵2的单元格范围)。

例如，用矩阵T P 与矩阵C 相乘，首先选择一个输出区域如G1:H2，然后输入“=mmult(A1:B2,C1:D2)”，然后按下“Ctrl+Shift+Enter ”键（图9），即可给出
1.206044 1.206044
0.20817 -0.20817
再用乘得的结果与P 阵相乘，便得对角矩阵
1.705603 0
0 0.294397
如果希望一步到位也不难，选定输出区域如C3:D4，然后输入“=mmult(mmult(A1:B2,C1:D2),E1:F2)” （图10），同时按下“Ctrl+Shift+Enter ”键，立即得到结果（图11）。

显然，对角矩阵对角线的数值恰是相关系数矩阵的特征值。

图9 矩阵乘法示例
图10 矩阵连乘的命令与语法
至此，标准化的原始变量x 与主成分之间z 之间可以表作
[][]⎥⎦⎤⎢⎣⎡⎥⎦⎤⎢⎣⎡=⎥⎦⎤⎢⎣⎡⎥⎦⎤⎢⎣⎡212121212944.00
07056.117056.07056.01z z z z x x x x
显然1z 与2z 之间正交。

图11 乘法结果：对角矩阵
⑺ 根据特征根计算累计方差贡献率。

现已求得第一特征根为7056.11=λ，第二特征根为2944.02=λ，二者之和刚好就是矩阵的维数，即有221==+m λλ，这里m =2为变量数目（注意前面的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λ：1.7056，%28.852/7056.1/1==m λ
2λ：0.2944，%72.14/2944.0/2==m m λ
21λλ+：2，%1002/2/)(21==+m λλ
这表明，如果仅取第一个主成分，可以反映原来数据85.28%的信息——换言之，舍弃第二个主成分，原来数据的信息仅仅损失14.72%，但分析变量的自由度却减少一个，整个分析将会显得更加简明。

⑻ 计算主成分载荷。

根据公式j j j e λρ=，容易算出 ⎥⎦
⎤⎢⎣⎡=⎥⎦⎤⎢⎣⎡=P 9235.09235.07071.07071.07056.11 ⎥⎦
⎤⎢⎣⎡-=⎥⎦⎤⎢⎣⎡-=P 3837.03837.07071.07071.02944.02
⑼ 计算公因子方差和方差贡献。

根据上述计算结果可以比较公因子方差和方差贡献。

再考虑全部的两个主成分的时候，对应于1λ和2λ的公因子方差分别为
13837.09235.02221=+==∑j ij V ρ
1)3837.0(9235.0222
2=-+==∑j
ij V ρ 对应于第一主成分z 1和第二主成分z 2的方差贡献分别为
7056.19235.09235.0221=+==∑i
ij CV ρ 2944.0)3837.0(3837.0222=-+==∑i ij CV ρ
可以看出（图12）： 第一，方差贡献等于对应主成分的特征根，即有
j j CV λ=
第二，公因子方差相等或彼此接近，即有
21V V =
第一，公因子方差之和等于方差贡献之和，即有
2===∑∑m CV V j j i i
第一个规律是我们决定提取主成分数目的判据与之一，第二个规律是我们判断提取主成分数目是否合适的判据之一，第三个规律是我们判断提取主成分后是否损失信息的判据之
一。

去掉次要的主成分以后，上述规律理当仍然满足。

这时如果第二个规律不满足，就意味着主成分的提取是不合适的。

此外，上述规律也是我们检验计算结果是否正确的判据之一。

图12 公因子方差、方差贡献的计算结果及其与特征根的贡献
⑽ 计算主成分得分。

根据主成分与原始变量的关系，应有
X P Z T =
或者
PZ X =
对于本例而言，式中
⎥⎦⎤⎢⎣⎡=21x x X ，⎥⎦⎤⎢⎣⎡=21z z Z ，[]⎥⎦⎤⎢⎣⎡==2221121121e e e e e e P ⎥⎦
⎤⎢⎣⎡-=7071.07071.07071.07071.0 这里[]T e e e 12111=，[]T e e e 22212=为前面计算的标准化特征向量。

于是有
⎥⎦⎤⎢⎣⎡⎥⎦⎤⎢⎣
⎡-=⎥⎦⎤⎢⎣⎡21217071.07071.07071.07071.0x x z z
化为代数形式便是
2117071.07071.0x x z +=
2127071.07071.0x x z -=
式中的x 均为标准化数据。

对X P Z T =进行转置，可得
T
Z T
X
P
图13 计算特征向量的公式及语法
图14 计算主成分得分
根据这个式子，利用Excel计算主成分得分的步骤如下：
① 将特征向量复制到标准化数据的附近；
② 选中一个与标准化数据占据范围一样大小的数值区域（如G2:H26）；
③ 输入如下计算公式“=mmult(标准化数据的范围，特征向量的范围)”，在本例中就是“=MMULT(B2:C26,E2:F3)”（图13）；
④ 同时按下“Ctrl+Shift+Enter ”键。

⑤ 计算主成分得分的均值和方差，可以发现，均值为0（由于误差之故，约等于0），方差等于特征根。

⑥ 最后，可以对主成分得分进行标准化。

已知主成分得分的均值为0,我们不按总体方差进行标准化，而按样本方差进行标准化。

图15 主成分得分的标准化结果
样本方差的计算公式为
∑=--=n
i j ij j x x n x 1
2)(11)(V ar 相应地，标准差为
∑=--=
=n
i j ij j j x x n x 1
2)(11)(Var σ 标准化公式同前面给出的一样。

结果见表15。

注意，这里之所以按样本方差进行标准化，
主要目的是为了与SPSS 的计算结果进行比较。

分别以z 1、z 2为坐标轴，将主成分得分（包括标准化的得分）点列标绘于坐标图中，可以发现，点列分布没有任何趋势：回归结果表明，回归系数和相关系数均为零，即有0=a ，0=b ，0=R （图16，图17）。

这从几何图形上显示：主成分之间是正交的，即有0cos =θ（试将图16、图17与图2、图3对比）。

图16 主成分得分的相关系数为零
图17 主成分得分的相关系数为零（标准化）
最后可以验证因子载荷即为（标准化）原始数据与主成分得分之间的相关系数，容易算出
9235.0),(Correl ),(1111==z x z x ρ， 9235.0),(Correl ),(1212==z x z x ρ， 3837.0),(Correl ),(2121==z x z x ρ， 3837.0),(Correl ),(2222-==z x z x ρ
图18 1x 与1z 的关系及其回归方程
图19 2x 与1z 的关系及其回归方程
图20 1x 与2z 的关系及其回归方程
图21 2x 与2z 的关系及其回归方程
回归方程为
11206.1x z = 21206.1x z = 122082.0x z = 222082.0x z -=
方程的系数恰是以下矩阵的元素
⎥⎦
⎤⎢⎣⎡-=⎥⎦⎤⎢⎣⎡⎥⎦⎤⎢⎣⎡-==20817.020817.020604.120604.117056.07056.01
7071.07071.07071.07071.0PC G。