实例说明利用Excel进行主成分分析分解.docx
《实例说明利用Excel进行主成分分析分解.docx》由会员分享，可在线阅读，更多相关《实例说明利用Excel进行主成分分析分解.docx（22页珍藏版）》请在冰豆网上搜索。
实例说明利用Excel进行主成分分析分解
方法:
1利用Excel2000进行主成分分析
第一步，录入数据，并对进行标准化。
【例】一组古生物腕足动物贝壳标本的两个变量：
长度和宽度。
A
B
C
D
E
1
样本编号
长度勒
宽度&
利标准化kJ吧标准化/
2
1
3
2
-1.786045
-1.806077
3
2
4
10
-1.559389
-0.141490
4
3
6
5
-1.106078
-L181857
5
4
6
8
-k106078
-0.557637
6
5
6
10
T.106078
-0.141490
7
6
7
2
-0.879423
-1.806077
8
7
7
13
-0.879423
0.482730
9
8
8
9
-0.652768
-0.349563
10
9
9
5
-0.426112
-1.181857
11
10
9
8
-0.426112
-0.557637
12
11
9
14
-0.426112
0.690804
13
12
10
7
-0.199457
-0.765710
14
13
11
12
0.027199
0.274657
15
14
12
10
0.253854
-0.141490
16
15
12
11
0.253854
0.066583
17
16
13
6
0.480509
-0.973784
18
17
13
14
0.480509
0.690804
19
18
13
15
0.480509
0.898877
20
19
13
17
0.480509
1.315024
21
20
14
7
0.707165
-0.765710
22
21
15
13
0.933820
0.482730
23
22
17
13
L387131
0.482730
24
23
17
17
L387131
1.315024
25
24
18
19
L613787
1.731171
26
25
20
20
2.067097
1.939244
27
均值
10.88
10.68
0.0000000.000000
28
19,46560
23.09760
1111
29
标准差
4.41198
4.80600
1
1]
图1原始数据和标准化数据及其均值、方差
（取自张超、杨秉庚《计量地理学基础》）
计算的详细过程如下：
⑴将原始数据绘成散点图（图2）。
主持分分析原则上要求数据具有线性相关趋势
—如果数据之间不相关（即正交），则没有必要进行主成分分析，因为主成分分析的目的就是用正交的变量代替原来非正交的变量；如果原始数据之间为非线性关系，则有必要对数据进行线性转换，否则效果不佳。
从图2可见，原始数据具有线性相关趋势，且测定系
数R2=0.4979，相应地，相关系数R=0.7056。
⑵对数据进行标准化。
标准化的数学公式为
*Xjj-Xj
为—
j
这里假定按列标准化，式中
Xj
分别为第j列数据的均值和标准差,
n
；「ij=、：
（xij-Xj）=Var（Xj）
Xij为第i行（即第i个样本）、第j列（即第j个变
标准化数据的散点图y=0.7056X+2E-16
图3标准化数据的散点图
对数据标准化的具体步骤如下：
①求出各列数据的均值，命令为average,语法为：
average（起始单元格：
终止单元格）。
如图1所示，在单元格B27中输入
“=AVERAGE（B1:
B26）”，确定或回车，即得第一列数据的均值X^10.88；然后抓住单
元格B27的右下角（光标的十字变细）右拖至C27，便可自动生成第二列数据的均值X2二10.68。
2求各列数据的方差。
命令为varp，语法同均值。
如图1所示，在单元格B28中输入
“=VARP（B2:
B26）”，确定或回车，可得第一列数据的方差Var（xJ=19.4656，右拖至
C28生成第二列数据的方差Var（x2）=23.0976。
3求各列数据的标准差。
将方差开方便得标准差。
也可利用命令stdevp直接生成标准
差，语法和操作方法同均值、方差，不赘述。
4标准化计算。
如图1所示，在单元格D2中输入“=（B2-$B$27）/$B$29”，回车可得
第一列第一个数据“3”的标准化数值-1.786045，然后按住单元格D2的右下角下拖至
D26，便会生成第一列数据的全部标准化数值；按照单元格D2的右下角右拖至E2,就能
生成第二列第一个数据“2”的标准化数据-1.806077，抓住单元格E2的右下角下拖至
E26便会生成第二列数据的全部标准化数值。
5作标准化数据的散点图（图3）。
可以看出，点列的总体趋势没有变换，两种数据
的相关系数与标准化以前完全相同。
但回归模型的截距近似为0,即有a>0，斜率等于
相关系数，即有b=R。
⑶求标准化数据的相关系数矩阵或协方差矩阵。
求相关系数矩阵的方法是：
沿着“工具
（T）”-“数据分析（D）”的路径打开“分析工具（A）”选项框（图4）,确定，弹出“相关系数”对话框（图5），在“输入区域”的空白栏中输入标准化数据范围，并以单元格G1为输出区域，具体操作方法类似于回归分析。
确定，即会在输出区域给出相关
图4分析工具选项框
图5相关系数对话框
系数矩阵的下三角即对角线部分，由于系对称矩阵，上三角的数值与下三角相等，故未给出（图6）,可以通过“拷贝一一转置一一粘帖”的方式补充空白部分。
G
H
I
J
K
L
相关系数
协方差
列1
列2
列1
列2
列1
1
列1
1
列2
0.705603
1
列2
0.705603
1
图6标准化数据的相关系数和协方差
求协方差的方法是在“分析工具”选项框中选择“协方差”（图7），弹出“协方
差”选项框（图8），具体设置与“相关系数”类似，不赘述。
结果见图6,可以看出，对于标准化数据而言，协方差矩阵与相关系数矩阵完全一样。
因此，二者任取其一即可。
图7在分析工具选项框中选择“协方差”
图8协方差选项框
0.7056
1
01
1
⑷计算特征根。
我们已经得到相关系数矩阵为
-1C二
1（0.7056
而二阶单位矩阵为
1I
〔0
于是根据公式det（'l-C）=0，我们有
1
0
1
0.7056
丸-1
-0.7056
0
1
0.7056
1
-0.7056
丸-1
按照行列式化为代数式的规则可得
222
（--1）-0.7056=■-2'0.502仁0
根据一元二次方程的求根公式，当b2-4ac_0时，我们有
2
、-b±>/b-4ac
A=
2a
•2=1一R）。
这便是
据此解得r=1.7056，t=0.2944（对于本例，显然R，
相关系数矩阵的两个特征根。
⑸求标准正交向量
将「代入矩阵方程
（■I-ck-0，得到
0.7056-0.7056
-0.70560.7056
在系数矩阵I-C中，用第一行加第二行，化为
1IJ
oo_
0.7056-0.7056\_0
[00jkr（0j
由此得='-；2，令=1，则有’J=1，于是得基础解系
单位化为eF7071】||1|[0.7071
屮.
单位化的公式为i（i=1,2）。
i
完全类似，将匕代入矩阵方程（’I-C）?
=0，得到
1-0.7056
|t-0.7056
用系数矩阵的第二行减去第一行，化为
1-0.7056
_0
1，则有
--1-
这里8、e2便是标准正交向量。
-0.70560
-0.7056「2.||0
于是得到=，取二
-0.7056'■10
0「2〔0
--1，因此得基础解系为
0.7071
，单位化为e2
[-0.7071」
⑹求对角阵。
首先建立标准正交矩阵P,即有
厂rn0.70710.7071I
P二[ee2]
[0.7071-0.7071一该矩阵的一个特殊性质便是PT二P‘，即矩阵的转置等于矩阵的逆
可知
0.70710.7071
D=I
0.7071-0.70710.70561」〔0.7071-0.7071[0
10.70560.70710.7071
根据D二PTCP,
1.70560
00.2934
下面说明一下利用Excel进行矩阵乘法运算的方法。
矩阵乘法的命令为mmult，语法是
mmult（矩阵1的单元格范围，矩阵2的单元格范围）。
例如，用矩阵PT与矩阵C相乘，首先选择一个输出区域如G1:
H2,然后输入“=mmult（A1:
B2,C1:
D2）”，然后按下
“Ctrl+Shift+Enter”键（图9）,即可给出
1.2060441.206044
0.20817-0.20817
再用乘得的结果与P阵相乘，便得对角矩阵
1.7056030
00.294397
如果希望一步到位也不难，选定输出区域如C3:
D4，然后输入
“=mmult（mmult（A1:
B2,C1:
D2）,E1:
F2）”（图10），同时按下“Ctrl+Shift+Enter”
键，立即得到结果（图11）。
显然，对角矩阵对角线的数值恰是相关系数矩阵的特征值。
SUM▼
A
5|C
DEF
G1H
1
0.707107
0.707107；L000000
6.705603：
0,7071070.707107
2
0.707107
-0.707107：
0.705603
1.000000；0.70710T-0.707107
图9矩阵乘法示例
□rasa（a|
5・C+逼jER
宋休
C3二
=[=OULT（MULT（Al:
B2,Cl:
D2）,El：
F2）}
A
B
Ic
D
E
F
1
0.707107
0.707107
k000000
0.705603
0,707107
0.707107
2
0.707107
-0.707107
0,705603
L000000
0.707107
-0.707107
3
1.705603
0
4
1o
0.294397
图11乘法结果：
对角矩阵
⑺根据特征根计算累计方差贡献率。
现已求得第一特征根为「=1.7056，第二特征根为
、2=0.2944，二者之和刚好就是矩阵的维数，即有•2=m=2，这里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%
100.00%
息？
这个问题可以借助相关系数矩阵的特征根来判断。
利用占特征根总和即矩阵维数的85.28%（见下表），即有特征根累计值百分比
1.7056031.70560385.28%
0.294397214.72%
也就是说:
」:
1.7056，」/m=1.7056/2=85.28%■2:
0.2944，匕/m=0.2944/m=14.72%
■「■2:
2，（■,2）/m=2/2=100%
这表明，如果仅取第一个主成分，可以反映原来数据85.28%的信息一一换言之，舍弃第
二个主成分，原来数据的信息仅仅损失14.72%，但分析变量的自由度却减少一个，整个
分析将会显得更加简明。
⑻计算主成分载荷。
根据公式S二■jej，容易算出
「1.7056a707—°9235
1[0.70711（0.9235
P20.7071L[°38371
[-0.7071一[-0.3837一
⑼计算公因子方差和方差贡献。
根据上述计算结果可以比较公因子方差和方差贡献。
再考虑全部的两个主成分的时候，对应于’和•2的公因子方差分别为
222
V1「％=0.92350.3837=1
j
V2八=0.92352（-0.3837）2=1
对应于第一主成分Z1和第二主成分Z2的方差贡献分别为
22
CVj讣=0.92350.9235=1.7056
i
22
CV2讣=0.38372（-0.3837）2=0.2944
i
可以看出（图12）:
第一，方差贡献等于对应主成分的特征根，即有
CVj=扎j
第二，公因子方差相等或彼此接近，即有
y=v2
第一，公因子方差之和等于方差贡献之和，即有
二7、='CVj=m=2
ij
第一个规律是我们决定提取主成分数目的判据与之一，第二个规律是我们判断提取主成分数目是否合适的判据之一，第三个规律是我们判断提取主成分后是否损失信息的判据之一。
去掉次要的主成分以后，上述规律理当仍然满足。
这时如果第二个规律不满足，就意味着主成分的提取是不合适的。
此外，上述规律也是我们检验计算结果是否正确的判据之
O
A
B
C
D
G
1
记入全部（两个）主成分
只考虑笫一主成分
2
笫一主成分第二主成分公因子方差
第一主成分公因子方差
3
长度羔1
0.92347265
0.38366425
1
长度兀】
0.92347265
0.852802
4
宽度E2
0.92347265
-0.3836643
1
宽度卞2
0.92347265
0.852802
5
方差贡献
1.705603
0.294397
2
方差贡献
1.705603
1.705603
6
特征根几
1.705603
0.294397
特征根也
1.705603
图12公因子方差、方差贡献的计算结果及其与特征根的贡献
⑽计算主成分得分。
根据主成分与原始变量的关系，应有z=pTx或者
X=PZ
对于本例而言，式中
xF
z=FIp=ej异11肃
"0.70710.70711
=
]X2一
见」621622一
[0.7071-0.7071一
这里©-'e]1e12T,e2二b21e22卩为前面计算的标准化特征向量。
于是有
化为代数形式便是
乙二0.7071x10.7071x2z2=0.7071为—0.7071x2
式中的x均为标准化数据。
对Z二PTX进行转置，可得
ZT=XTP
图13计算特征向量的公式及语法
A
■B
C
D
E
F
G
IH
1
样本编号
百标准化斷水
怒标准化讨
特征向量口特征向量习
引得分
跨得分
2
1
-1.7860447
-1.8050771
长度
0.7071068
0,7071068
-2.54001
0.014165
3
2
-1.5593893
-0.1414899
宽度
0.7071068
-0.70711
-1.2027
-1.00261
4
3
-1.1060784
-1.1818569
-1.61781
0.053583
5
4
-1.1060784
-0.5576367
-L17642
-0.38781
6
5
-1.1060784
-0.1414899
-0.88216
-0.68207
7
6
-0.879423
-1.S060771
-L89894
0.655243
S
T
-0.879423
64327303
-0.2805
-0.96319
g
_3_
-0.6527676
-0.3495633
-0.70875
-0.2144
10
9
-0.4261122
-1.1318569
-L13701
0.534392
n
10
-0.4261122
-0.5576367
-0.69562
0.093002
12
11
-0.4261122
0.6908037
0.187165
-0.78978
13
12
-0.1994568
-0.7657101
-0.68248
0.400402
14
13
0.02719865
0.27465689
0.213444
-0.17498
15
14
0.25385407
-0.1414899
0.079453
0.27955
16
15
0.25385407
0.06658349
0.226584
0.13242
17
16
0.4805094S
-0.9737835
-0.3433
1.02834
18
17
0.48050948
0,6908037
0.328243
-0.148?
19
18
0,48050948
0.8988771
0.975374
-0.29583
20
19
0.48050948
1.31502391
1.269634
-0.59009
21
20
0.7071649
-0.7657101
-0,0414
1.04148
22
21
0.93382032
0.4827303
1.001653
0.318969
23
22
1.38713115
0.4827303
1.322192
0.639508
24
23
1.38713115
L31502391
1.910712
0.050988
25
24
1.61378657
L73117072
2.365242
-Cl033
26
24
2.0670974
L93924412
2.832911
a090406
27
力差
1
1
XJbJl
方差
1.7Q56
0.2944
图14计算主成分得分
根据这个式子，利用Excel计算主成分得分的步骤如下：
1将特征向量复制到标准化数据的附近；
2选中一个与标准化数据占据范围一样大小的数值区域（如G2:
H26）;
3输入如下计算公式“=mmult（标准化数据的范围，特征向量的范围）”，在本例中就是
“=MMULT（B2:
C26,E2:
F3）”（图13）;
4同时按下“Ctrl+Shift+Enter”键。
5计算主成分得分的均值和方差，可以发现，均值为0（由于误差之故，约等于0），方差等于特征根。
6最后，可以对主成分得分进行标准化。
已知主成分得分的均值为0,我们不按总体方差
进行标准化，而按样本方差进行标准化。
1A
■B
1C
1D
1E
1
样本序号
可得分
女得分
标准化可
标准化可
2
1
-2.540014
0,014165
-1.905604
0.0255793
3
2
-1.202703
-1.002606
-0.902308
-L810505
4
3
-1.617815
0.053583
-L213739
0.096761
5
4
-1.176424
上3.387807
-0.882593
-0.700301
6
5
-0.882164
-0,682067
-0.661829
-L231676
7
6
-1.898935
0.655243
-1.424645
1.1832376
8
7
-0.280504
-0.963188
-0.210444
-1.739323
9
8
-0.708755
-0.214398
-0.531732
-0.38715^
10
g
-L137006
0.534392
-CL85302
0.9650047
11
10
-0.695616
0.093002
-0.521874
61679427
12
11
0.187165
-0.789779
0.1404176
-1.426181
13
12
-0.682476
0.400402
-0.512017
0.7230446
14
13
0.213444
-0.174979
6160133
-0.315978
15
14
0.079453
0.279550
0.0596087
0.5048117
16
15
0.226584
0.132420
0.1699906
0.2391244
17
1G
-0.348797
1.028340
-0.26168
1.8569756
12
17
0.828243
-0.148700
0.6213762
-0.268523
19
18
0.975374
-0.295831
0.7317582
-0.53421
20
19
L269634
-0.590091
0.9525221
-1.065585
21
20
-0.041398
1.041480
升级会员 