利用Excel进行主成分分析.docx

利用Excel进行主成分分析.docx

《利用Excel进行主成分分析.docx》由会员分享，可在线阅读，更多相关《利用Excel进行主成分分析.docx（25页珍藏版）》请在冰点文库上搜索。

利用Excel进行主成分分析

1利用Excel2000进行主成分分析

第一步，录入数据，并对进行标准化。

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

长度和宽度。

A

B

C

D

E

1

样本编号

长度壯

宽度出

州标准化/肥标准化好

2

1

3

2

-1.786045

-1.806077

3

2

4

10

-1.559389

-0.141490

4

3

6

5

T.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

6027199

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

23

力差

19,46560

23.09760

1]1]

29

标准差

4.41198

4.80600

1

1

图1原始数据和标准化数据及其均值、方差

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

计算的详细过程如下：

⑴将原始数据绘成散点图（图2）。

主持分分析原则上要求数据具有线性相关趋势

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

从图2可见，原始数据具有线性相关趋势，且测定系

数R2=0.4979,相应地，相关系数R=0.7056。

⑵对数据进行标准化。

标准化的数学公式为

*Xij-Xj

这里假定按列标准化，式中

ij

cj

n

—1百

XijXij，

ni二

-Var（Xj）

分别为第j列数据的均值和标准差，

Xij为第i行（即第

i个样本）、第j列（即第j个变

度宽

原始数据的散点图y=0.7686X+2.3174

2

R=0.4979

图2原始数据的散点图

量）的数据，x*为相应于Xj的标准化数据，n二25为样本数目

对数据标准化的具体步骤如下：

①求出各列数据的均值，命令为average，语法为:

average（起始单元格：

终止单元格）。

如图1所示，在单元格B27中输入

“=AVERAGE（B1:

B26）”，确定或回车，即得第一列数据的均值x,=10.88;然后抓住单

元格B27的右下角（光标的十字变细）右拖至C27，便可自动生成第二列数据的均值

X2二10.68。

2求各列数据的方差。

命令为varp，语法同均值。

如图1所示，在单元格B28中输入

“=VARP（B2:

B26）”，确定或回车，可得第一列数据的方差Var（x1）=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

IL

.相关系数

协力差

列1

列2

列1

列2

列1

1

列1

1

列2

0.705603

1

列2

0.705603

1

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

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

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

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

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

⑷计算特征根。

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

而二阶单位矩阵为

0【

1

于是得到：

=7；，取匚=1，则有，因此得基础解系为

一1]_0.7071]

2二，单位化为e2=

卜1|L_0.7071

这里e>e2便是标准正交向量。

⑹求对角阵。

首先建立标准正交矩阵P，即有

"0.70710.7071]

P=[e1e2]

『0.7071-0.7071

该矩阵的一个特殊性质便是PT=PA，即矩阵的转置等于矩阵的逆。

根据D=PTCP,

可知

0.70710.707110.70560.70710.70711.70560

0.7071-0.70710.70561'0.7071-0.7071''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）。

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

SW、

7二=miDult（mmult（Al:

62,Cl:

D2）aEl:

F2）

A

IIB

C

■D

E

■F

1

6707107

707107

L000000

0.705603

0.707107

CL707107；

2

0.707107

-0.707107

6705603

1.000000

0.707107

-0.707107：

3

4

=[1.70560343031230,0;

返回两数组矩阵的乘积■苴中虹T的行数与虹®的列数相等.

谿蠶蟹的两停组矩阵'第“数組細」数应谨与第二偉鉅

计算结果=1TC550348确定职消|

图10矩阵连乘的命令与语法

显然Zt与z2之间正交。

I（fcffi

g•厂…匾w齐鮎聚Bl0?

j宋儒

C3、

J=匸冊IULT（MHULT（Al:

B2*Cl:

D2）,El:

F2）}

A

B

IIc

D

1E

F

1

0.707107

0.707107

1.000000

0.705603

0,707107

6707107

2

0.707107

-0.707107

0.705603

L000000

0.707107

-0.707107

3

1.705603

0

4

厂0

0.294397

图11乘法结果：

对角矩阵

（7）

根据特征根计算累计方差贡献率。

现已求得第一特征根为「=1.7056，第二特征根为

=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）=

迹（trace，即对角

也就是说:

■i冷2:

2，（一冷2）/m=2/2=100%

这表明，如果仅取第一个主成分，可以反映原来数据85.28%的信息一一换言之，舍弃第

二个主成分，原来数据的信息仅仅损失14.72%，但分析变量的自由度却减少一个，整个

分析将会显得更加简明。

⑻计算主成分载荷。

根据公式:

[=.■jej，容易算出

°.7071_0

0.70710.9235

二1.7056

0.7071|（0.9235

⑼计算公因子方差和方差贡献。

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

再考虑全部的两个主成分的时候，对应于\和’2的公因子方差分别为

22°2

V,二iij=0.92350.3837=1

j

222

V='Aj0.9235（-0.3837）=1

j

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

22

CVi;?

.j=0.92350.9235=1.7056

i

—22

CV2Pij=0.3837（_0.3837）0.2944

i

可以看出（图12）:

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

CVj=■j

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

V1“2

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

7、='、'CVj=m=2

.j

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

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

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

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

O

A

C

D

F

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

1.705603

6

特征根入

1.705603

0.294397

特征根L

1.705603

图12公因子方差、方差贡献的计算结果及其与特征根的贡献

⑽计算主成分得分。

根据主成分与原始变量的关系，应有

T

Z=PX

或者

X=PZ

对于本例而言，式中

0.7071

-0.7071

|X1I戶丨匚]e11e12I0.7071

*一>2J>21e22一]0.7071

这里©=e„e’2J,e2=e21e22「为前面计算的标准化特征向量。

于是有

化为代数形式便是

z^0.7071X’0.7071x2

Z2=0.7071x-0.7071X2

式中的x均为标准化数据。

对Z=PTX进行转置，可得

图13计算特征向量的公式及语法

A

■B

C

D

E

F

G

H

1

样本编号

业标准化斷“

牝标准化X,

特征向量习特征向量口

引得分

在得分

2

1

-1.7860447

-1.8060771

长度

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

-1.17642

-0.38781

6

5

-1.1060784

-0,1414899

-0.88216

-0.68207

7

6

-0.879423

-1.S060771

-L89894

0.655243

8

7

-0.S79423

64827303

-0.2805

-0.96319

g

8

-0.6527676

-63495633

-0.70375

-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.187155

-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.48050948

-69737835

-0・3488

1.02834

18

17

0.48050948

0.6908037

0.32S243

-0.1487

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.050938

25

24

1.61373657

L73117072

2.365242

-CL033

26

24

2.0670974

L93924412

2.832911

0.090406

27

力差

1

1

方差

1・7056

0.2944

图14计算主成分得分

根据这个式子，利用Excel计算主成分得分的步骤如下:

1将特征向量复制到标准化数据的附近；

2选中一个与标准化数据占据范围一样大小的数值区域（如G2:

H2⑤；

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

-0.387807

-0.882593

-0.700301

&

5

-0.882164

上1682067

-0.661829

-L231676

7

6

-1.898935

0.655243

-1.424645

1.1332376

8

_7

-0.230504

-0.963188

-0.210444

-1.739323

9

8

-0.708755

-0.214398

-CL531732

-0.38715^

10

g

-1.137006

0.534392

-0.85302

0.9650047

11

10

-0.695616

0.093002

-0.521874

0.1679427

12

u

0.187165

-0.789779

0.1404176

-1.426181

13

12

-0.682476

0.400402

-0.512017

0.7230446

14

13

0.213444

-0.174979

0.160133

-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

13

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

-0.031058

1.8807028

22

21

L001653

0.318969

0.7514735

0.5759934

23

一22

1.322192

0.639508

0.9919528

1.1548225

24

23

L910712

0,050988

1.4334807

0.0920732

25

24

2.365242

-0,083003

1.7744843

-0.149887

26

25

2.832911

0,090406

2.1253456

0.1632549

27

样本方差

1.776670

0.306663

1

1]

图15主成分得分的标准化结果

样本方差的计算公式为

n

十）

1寸

Varg）（Xij

nTi-

相应地，标准差为

（xj

\n-1i二

标准化公式同前面给出的一样。

结果见表15。

注意，这里之所以按样本方差进行标准化,

主要目的是为了与SPSS的计算结果进行比较。

分别以Z1、Z2为坐标轴，将主成分得分（包括标准化的得分）点列标绘于坐标图中,可以发现，点列分布没有任何趋势：

回归结果表明，回归系数和相关系数均为零，即有

a0，b=0，R=0（图16，图17）。

这从几何图形上显示：

主成分之间是正父的，即有cos二=0（试将图16、图17与图2、图3对比）。

主成分得分的空间分布

•-1.000000

第一主成分得分

图16主成分得分的相关系数为零

2.5

*

4

y=

-2E-16X

-4E-17

1.5

-

R2=

3E-32

■

*1

■