实例说明利用Excel进行主成分分析分解Word格式.docx

实例说明利用Excel进行主成分分析分解Word格式.docx

《实例说明利用Excel进行主成分分析分解Word格式.docx》由会员分享，可在线阅读，更多相关《实例说明利用Excel进行主成分分析分解Word格式.docx（22页珍藏版）》请在冰豆网上搜索。

20

1.315024

21

0.707165

22

0.933820

23

L387131

24

25

L613787

1.731171

26

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原始数据和标准化数据及其均值、方差

（取自张超、杨秉庚《计量地理学基础》）

计算的详细过程如下：

⑴将原始数据绘成散点图（图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

0.705603

图6标准化数据的相关系数和协方差

求协方差的方法是在“分析工具”选项框中选择“协方差”（图7），弹出“协方

差”选项框（图8），具体设置与“相关系数”类似，不赘述。

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

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

图7在分析工具选项框中选择“协方差”

图8协方差选项框

0.7056

01

⑷计算特征根。

我们已经得到相关系数矩阵为

-1C二

1（0.7056

而二阶单位矩阵为

1I

〔0

于是根据公式det（'

l-C）=0，我们有

丸-1

-0.7056

按照行列式化为代数式的规则可得

222

（--1）-0.7056=■-2'

0.502仁0

根据一元二次方程的求根公式，当b2-4ac_0时，我们有

、-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

用系数矩阵的第二行减去第一行，化为

_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:

D2）,E1:

F2）”（图10），同时按下“Ctrl+Shift+Enter”

键，立即得到结果（图11）。

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

SUM▼

5|C

DEF

G1H

0.707107

0.707107；

L000000

6.705603：

0,7071070.707107

-0.707107：

0.705603

1.000000；

0.70710T-0.707107

图9矩阵乘法示例

□rasa（a|

5・C+<

sA1!

逼jER

宋休

C3二

=[=OULT（MULT（Al:

B2,Cl:

D2）,El：

F2）}

Ic

F

0.707107

k000000

0,707107

-0.707107

0,705603

L000000

-0.707107

1.705603

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的公因子方差分别为

V1「％=0.92350.3837=1

V2八=0.92352（-0.3837）2=1

对应于第一主成分Z1和第二主成分Z2的方差贡献分别为

22

CVj讣=0.92350.9235=1.7056

CV2讣=0.38372（-0.3837）2=0.2944

可以看出（图12）:

第一，方差贡献等于对应主成分的特征根，即有

CVj=扎j

第二，公因子方差相等或彼此接近，即有

y=v2

第一，公因子方差之和等于方差贡献之和，即有

二7、='

CVj=m=2

ij

第一个规律是我们决定提取主成分数目的判据与之一，第二个规律是我们判断提取主成分数目是否合适的判据之一，第三个规律是我们判断提取主成分后是否损失信息的判据之一。

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

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

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

O

记入全部（两个）主成分

只考虑笫一主成分

笫一主成分第二主成分公因子方差

第一主成分公因子方差

长度羔1

0.92347265

0.38366425

长度兀】

0.852802

宽度E2

-0.3836643

宽度卞2

0.852802

方差贡献

1.705603

0.294397

特征根几

特征根也

图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计算特征向量的公式及语法

■B

IH

百标准化斷水

怒标准化讨

特征向量口特征向量习

引得分

跨得分

-1.7860447

-1.8050771

长度

0.7071068

0,7071068

-2.54001

0.014165

-1.5593893

-0.1414899

宽度

-0.70711

-1.2027

-1.00261

-1.1060784

-1.1818569

-1.61781

0.053583

-0.5576367

-L17642

-0.38781

-0.88216

-0.68207

-0.879423

-1.S060771

-L89894

0.655243

S

T

64327303

-0.2805

-0.96319

g

_3_

-0.6527676

-0.3495633

-0.70875

-0.2144

-0.4261122

-1.1318569

-L13701

0.534392

-0.5576367

-0.69562

0.093002

0.6908037

0.187165

-0.78978

-0.1994568

-0.7657101

-0.68248

0.400402

0.02719865

0.27465689

0.213444

-0.17498

0.25385407

0.079453

0.27955

0.25385407

0.06658349

0.226584

0.13242

0.4805094S

-0.9737835

-0.3433

1.02834

0.48050948

0,6908037

0.328243

-0.148?

0,48050948

0.8988771

0.975374

-0.29583

1.31502391

1.269634

-0.59009

0.7071649

-0.7657101

-0,0414

1.04148

0.93382032

0.4827303

1.001653

0.318969

1.38713115

1.322192

0.639508

L31502391

1.910712

0.050988

1.61378657

L73117072

2.365242

-Cl033

2.0670974

L93924412

2.832911

a090406

力差

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

1C

1D

1E

样本序号

可得分

女得分

标准化可

-2.540014

0,014165

-1.905604

0.0255793

-1.202703

-1.002606

-0.902308

-L810505

-1.617815

-L213739

0.096761

-1.176424

上3.387807

-0.882593

-0.700301

-0.882164

-0,682067

-0.661829

-L231676

-1.898935

-1.424645

1.1832376

-0.280504

-0.963188

-0.210444

-1.739323

-0.708755

-0.214398

-0.531732

-0.38715^

-L137006

-CL85302

0.9650047

-0.695616

-0.521874

61679427

0.187165

-0.789779

0.1404176

-1.426181

-0.682476

-0.512017

0.7230446

-0.174979

6160133

-0.315978

0.279550

0.0596087

0.5048117

0.226584

0.132420

0.1699906

0.2391244

1G

-0.348797

1.028340

-0.26168

1.8569756

0.828243

-0.148700

0.6213762

-0.268523

0.975374

-0.295831

0.7317582

-0.53421

L269634

-0.590091

0.9525221

-1.065585

-0.041398

1.041480