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

1利用Excel2000进行主成分分析举例如下：第一步，录入数据，并对进行标准化。【例】一组古生物腕足动物贝壳标本的两个变量：长度和宽度。ABCDE1样本编号长度比宽度&用标准化忌标准化/2132-1.786045-1.80607732410-1.559389-0.1414904365T.106078-L1818575468-106078-0.55763765610T.106078-0.1414907672-0.879423-1.80607787713-0.8794230.4827309889-0.652768-0.34956310995-0.426112-1.181857111098-0.426112-0.5576371211914-0.4261120.6908041312107-0.199457-0.7657101413111260271990.274657151412100.253854-0.141490161512110.2538540.06658317161360.480509-0.973784181713140.4805090.690804191813150.4805090.898877201913170.4805091.31502421201470.707165-0.765710222115130.9338200.48273023221713L3871310.482730242317171.3871311.31502425241819L6137871.731171262520202.0670971.93924427均值10.8810.680.0000000.00000023力差19,4656023.097601]1129标巻差4.411984.8060011图1原始数据和标准化数据及其均值、方差（取自张超、杨秉庚《计量地理学基础》）计算的详细过程如下：⑴将原始数据绘成散点图（图2）。主持分分析原则上要求数据具有线性相关趋势—如果数据之间不相关（即正交），则没有必要进行主成分分析，因为主成分分析的目的就是用正交的变量代替原来非正交的变量；如果原始数据之间为非线性关系，则有必要对数据进行线性转换，否则效果不佳。从图2可见，原始数据具有线性相关趋势，且测定系2数R=0.4979，相应地，相关系数R=0.7056。⑵对数据进行标准化。标准化的数学公式为*Xij-XjXijJ这里假定按列标准化，式中-j二，'、'（Xj_Xj）2.iaXij为第i行（即第i个样本）、第j列（即第j个变n二25为样本数目。FVar(Xj)-丄aXijXij，ni吕分别为第j列数据的均值和标准差，量）的数据，X*为相应于Xij的标准化数据,原始数据的散点图y=0.7686X+2.3174图2原始数据的散点图标准化数据的散点图y=0.7056X+2E-16图3标准化数据的散点图对数据标准化的具体步骤如下：①求出各列数据的均值，命令为average,语法为：average（起始单元格：终止单元格）。如图1所示，在单元格B27中输入“=AVERAGE（B1:B26）”，确定或回车，即得第一列数据的均值X^10.88;然后抓住单元格B27的右下角（光标的十字变细）右拖至C27，便可自动生成第二列数据的均值x2=10.68。求各列数据的方差。命令为varp，语法同均值。如图1所示，在单元格B28中输入“=VARP（B2:B26）”，确定或回车，可得第一列数据的方差Var（xJ=19.4656，右拖至C28生成第二列数据的方差Var（x2）=23.0976。求各列数据的标准差。将方差开方便得标准差。也可利用命令stdevp直接生成标准差，语法和操作方法同均值、方差，不赘述。标准化计算。如图1所示，在单元格D2中输入“=（B2-$B$27）/$B$29”，回车可得第一列第一个数据“3”的标准化数值-1.786045，然后按住单元格D2的右下角下拖至D26，便会生成第一列数据的全部标准化数值；按照单元格D2的右下角右拖至E2,就能生成第二列第一个数据“2”的标准化数据-1.806077，抓住单元格E2的右下角下拖至E26便会生成第二列数据的全部标准化数值。作标准化数据的散点图（图3）。可以看出，点列的总体趋势没有变换，两种数据的相关系数与标准化以前完全相同。但回归模型的截距近似为0,即有a>0，斜率等于相关系数，即有b=R。⑶求标准化数据的相关系数矩阵或协方差矩阵。求相关系数矩阵的方法是：沿着“工具（T）”-“数据分析（D）”的路径打开“分析工具（A）”选项框（图4）,确定，弹出“相关系数”对话框（图5），在“输入区域”的空白栏中输入标准化数据范围，并以单元格G1为输出区域，具体操作方法类似于回归分析。确定，即会在输出区域给出相关图4分析工具选项框图5相关系数对话框系数矩阵的下三角即对角线部分，由于系对称矩阵，上三角的数值与下三角相等，故未给出（图6），可以通过“拷贝一一转置一一粘帖”的方式补充空白部分。GHIJKL相关系数协方差列1列2列1列2列11列11列20.7056031列20.7056031图6标准化数据的相关系数和协方差求协方差的方法是在“分析工具”选项框中选择“协方差”（图7），弹出“协方差”选项框（图8），具体设置与“相关系数”类似，不赘述。结果见图6,可以看出，对于标准化数据而言，协方差矩阵与相关系数矩阵完全一样。因此，二者任取其一即可。图7在分析工具选项框中选择“协方差”图8协方差选项框⑷计算特征根。我们已经得到相关系数矩阵为-1110.70560.70561而二阶单位矩阵为叫0]于是根据公式det('l-C)=0，我们有10.70560.70561■-1-0.7056-0.7056丸一1按照行列式化为代数式的规则可得('-俨-0.7056?二*_2'0.5021二0根据一兀二次方程的求根公式，当2b-4ac_0时，我们有-b二、b2-4ac扎=2a据此解得-1.7056,-0.2944(对于本例，显然-VR,，2=1-R)。这便是相关系数矩阵的两个特征根。⑸求标准正交向量。将■1代入矩阵方程「I-C)J-0，得到0.7056-0.7056'i0-0.70560.70562|(0在系数矩阵I-C中，用第一行加第二行，化为0.7056_0-0.70560由此得='-；2，令=1，则有=1，于是得基础解系单位化为©£707；i2亠212完全类似，将'2代入矩阵方程单位化的公式为ei(i=1,2)。(I-C圧-0，得到I「0.7056|[-0.7056用系数矩阵的第二行减去第一行[化为1-0.705610于是得到，取匚=1，则有＜--1--0.7056'1_0-0.7056「2c一0.705600，t；20--1，因此得基础解系为_0.7071]，单位化为62二'-0.7071这里e1、e2便是标准正交向量。⑹求对角阵。首先建立标准正交矩阵P,即有0.70710.70711P二[巴62]=]0.7071-0.7071一该矩阵的一个特殊性质便是卩丁，即矩阵的转置等于矩阵的逆。根据D=PTCP，可知d_0.70710.7071T10.7056厂0.70710.70711_「1.70560]-^0.7071-0.707^^0.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.2060440.20817-0.20817再用乘得的结果与P阵相乘，便得对角矩阵1.705603000.294397如果希望一步到位也不难，选定输出区域如C3:D4,然后输入“=mmult(mmult(A1:B2,C1:D2),E1:F2)”(图10)，同时按下“Ctrl+Shift+Enter”键，立即得到结果(图11)。显然，对角矩阵对角线的数值恰是相关系数矩阵的特征值。567B9101丄121314NMULTArraylt31刊乙口T：D刃虫二{120604376694426Array2|E1：F2V]={0.7071057611B6S^=[1.70560343031230,0;返回两数组矩阵的乘积，苴中Array!的行数与"龙的列数相等。"切黠蠶蔘的两偉组粥'第”蝴帧数应沪第二偉组叵]计算结果二1TC95Q340确定取消|SUM,[X八=nwult(Al;B2,Cl:D2)ABC」DEFG1H10-7071QT0.707107：L0000QQ0-705603；0.7071070.707107=mmult(Al;B2hCl:D2)20.707107-0.707107：0.7056031.OOOOOfl!0.707107-0.707107图9矩阵乘法示例T*』二二mmu"(mmu"(Al:B2,C]L：D2),El:F2)ABc■ZDEF1a707107CL707107L0000000.7056030.7071070.707107；26707107-0.70710767056031.0000000.707107-0.707107：34图10矩阵连乘的命令与语法至此，标准化的原始变量[10.7056显然z1与z2之间正交。X1X21X与主成分之间Z之间可以表作1.7056000.2944z20.7056x11X2=Z1Z21]□3日持庖直|电E花肌計|P呵专衲C3二J={=NMl£|(MMULT(Al：E2pI：E2)』l：F2)}ABLCD—LEF10.7071070.707107k00000067056030,7071070.70710720.707107-0.7071070,705603L0000000.707107-0.70710731.7056030400.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，，2/m=0.2944/m=14.72%■「■2:2，(■■,2)/m=2/2=100%这表明，如果仅取第一个主成分，可以反映原来数据85.28%的信息一一换言之，舍弃第二个主成分，原来数据的信息仅仅损失14.72%，但分析变量的自由度却减少一个，整个分析将会显得更加简明。⑻计算主成分载荷。根据公式.■jej，容易算出0.70710.92353二1.70561啓［0.7071°383Tf-0.7071」卜0.38371||0.7071|(0.9235?2二⑼计算公因子方差和方差贡献。根据上述计算结果可以比较公因子方差和方差贡献。再考虑全部的两个主成分的时候，对应于和•2的公因子方差分别为y「52=0.923520.38372=1jV2八订2=0.92352(-0.3837)2=1j对应于第一主成分z1和第二主成分z2的方差贡献分别为CV1ij=0.923520.92352=1.7056iCV2二'订=0.38372(-0.3837)2=0.2944i可以看出(图12)：第一，方差贡献等于对应主成分的特征根，即有CVj—'j第二，公因子方差相等或彼此接近，即有V1“2第一，公因子方差之和等于方差贡献之和，即有二ViCVj=m=2ij第一个规律是我们决定提取主成分数目的判据与之一，第二个规律是我们判断提取主成分数目是否合适的判据之一，第三个规律是我们判断提取主成分后是否损失信息的判据之一。去掉次要的主成分以后，上述规律理当仍然满足。这时如果第二个规律不满足，就意味着主成分的提取是不合适的。此外，上述规律也是我们检验计算结果是否正确的判据之0Zi_0.7071z2||_0.70710.7071xi-0.7071」x2一A1B丄CFG1记入全部（两个）主成分只考虑第一主成分2第一主成分第二主咸分公因子方差第一主成分公因子方差3长度x10.923472650.383664251长度□0923472650.8528024宽度兀20.92347265-0.38366431宽度七0.923472650.8528025方差贡献1.7056030.2943972方差贡献1_7056031.7056036辅征根人1.7056030.294397特征根h1.705&03图12公因子方差、方差贡献的计算结果及其与特征根的贡献⑽计算主成分得分。根据主成分与原始变量的关系，应有或者z=pTx对于本例而言，式中X=PZ■xjX=|1X2一,z=[Zdp=e曳]异11曲-Z2-弓21e?2_"0.7071[0.70710.70711-0.7071一这里q=e12T,e2二fe21e22『为前面计算的标准化特征向量。于是有化为代数形式便是乙二0.7071x10.7071x2式中的x均为标准化数据。对z2=0.7071论一0.7071x2z二PTX进行转置，可得zT=xTp图13计算特征向量的公式及语法A■BCDEFGIH1样本编号百标准化斷水怒标准化讨特征向量口特征向量习引得分跨得分21-1.7860447-1.8050771长度0.70710680,7071068-2.540010.01416532-1.5593893-0.1414899宽度0.7071068-0.70711-1.2027-1.0026143-1.1060784-1.1818569-1.617810.05358354-1.1060784-0.5576367-L17642-0.3878165-1.1060784-0.1414899-0.88216-0.6820776-0.879423-1.S060771-L898940.655243ST-0.87942364327303-0.2805-0.96319g_3_-0.6527676-0.3495633-0.70875-0.2144109-0.4261122-1.1318569-L137010.534392n10-0.4261122-0.5576367-0.695620.0930021211-0.42611220.69080370.187165-0.789781312-0.1994568-0.7657101-0.682480.40040214130.027198650.274656890.213444-0.1749815140.25385407-0.14148990.0794530.2795516150.253854070.066583490.2265840.1324217160.4805094S-0.9737835-0.34331.0283418170.480509480,69080370.328243-0.148?19180,480509480.89887710.975374-0.2958320190.480509481.315023911.269634-0.5900921200.7071649-0.7657101-0,04141.0414822210.933820320.48273031.0016530.31896923221.387131150.48273031.3221920.63950824231.38713115L315023911.9107120.05098825241.61378657L731170722.365242-Cl03326242.0670974L939244122.832911a09040627力差11XJbJl方差1.7Q560.2944图14计算主成分得分根据这个式子，利用Excel计算主成分得分的步骤如下：将特征向量复制到标准化数据的附近；选中一个与标准化数据占据范围一样大小的数值区域（如G2:H26）;输入如下计算公式“=mmult（标准化数据的范围，特征向量的范围）”，在本例中就是“=MMULT（B2:C26,E2:F3）”（图13）;同时按下“Ctrl+Shift+Enter”键。计算主成分得分的均值和方差，可以发现，均值为0（由于误差之故，约等于0），方差等于特征根。最后，可以对主成分得分进行标准化。已知主成分得分的均值为0,我们不按总体方差进行标准化，而按样本方差进行标准化。1A■B1C1D1E1样本序号可得分女得分标准化可标准化可21-2.5400140,014165-1.9056040.025579332-1.202703-1.002606-0.902308-L81050543-1.6178150.053583-L2137390.09676154-1.176424上3.387807-0.882593-0.70030165-0.882164-0,682067-0.661829-L23167676-1.8989350.655243-1.4246451.183237687-0.280504-0.963188-0.210444-1.73932398-0.708755-0.214398-0.531732-0.38715^10g-L1370060.534392-CL853020.96500471110-0.6956160.093002-0.5218746167942712110.187165-0.7897790.1404176-1.4261811312-0.6824760.400402-0.5120170.723044614130.213444-0.1749796160133-0.31597815140.0794530.2795500.05960870.504811716150.2265840.1324200.16999060.2391244171G-0.3487971.028340-0.261681.856975612170.828243-0.1487000.6213762-0.26852319180.975374-0.2958310.7317582-0.534212019L269634-0.5900910.9525221-1.0655852120-0.0413981.041480-0.0310581.880702822211.0016530.3189690.75147350.575993423221.3221920.6395080.99195281.15482252423L9107120,0509881.43348070.092073225_242.365242-0,0830031.7744843-0.14988726252.8329110,0904062.12534560.163254927样本方差1.7766700.30666311]图15主成分得分的标准化结果样本方差的计算公式为相应地，标准差为11''-2畑(Xj“需/j-Xj)=JVar(Xj)=]£瓦(Xij—Xj)2\n—1i=i标准化公式同前面给出的一样。结果见表15。注意，这里之所以按样本方差进行标准化,主要目的是为了与SPSS的计算结果进行比较。分别以Zi、Z2为坐标轴，将主成分得分（包括标准化的得分）点列标绘于坐标图中，可以发现，点列分布没有任何趋势：回归结果表明，回归系数和相关系数均为零，即有a=0，b=0，R=0（图16，图17）。这从几何图形上显示：主成分之间是正交的，即有COS，-0（试将图16、图17与图2、图3对比）。主成分得分的空间分布1.5000001.000000彳分得分成主-3.00C+0.500000——«'_*0.000000000-2.000000-1.0000000.00(*-0.500000y=-7E-17x-2E-16R2=2E-320001.0000002.0000003.000000430000+-1.0000001.500000第一主成分得分图16主成分得分的相关系数为零1.5*•1*0.5y=-2E-16x-4E-17R2=3E-32♦♦■,•3-2-1*0.5(♦-1♦-1.5♦♦o宀冷123主成分得分的空间分布（标准化）2.5第一主成分得分分得分成主一一第图17主成分得分的相关系数为零（标准化）最后可以验证因子载荷即为（标准化）原始数据与主成分得分之间的相关系数，容易算出'（X1,乙）=Correl（X1,乙）=0.9235，「（x2,乙）=Correl（x2,zj=0.9235，「(Xi,Z2)=Correl(Xi,Z2)=0.3837,「(x2,z2)=Correl(x2,z2)--0.3837图表标题x2-ziz1=1.206x2-3E-16R2=0.8528+z1得分—线性（zi得分）图19X2与zi的关系及其回归方程图表标题x1-z21.5「z2=0.2082X1=1E-16R=0.14721♦*z2得分—线性（z2得分）■*——=1=1.5图20X!与Z2的关系及其回归方程图表标题x2=z2回归方程为■1■■T0.5**F''0*.♦<■0=1广（-r\r*1^23=0.5♦A.♦■=1♦*z2得分—线性（z2得分）z2==0.2082x2-2E-16R=0.1472图21X2与Z2的关系及其回归方程z1二1.206x1乙=1.206x2Z2=0.2082x1Z2二-0.2082x2方程的系数恰是以下矩阵的元素G=PC0.70710.70710.7071「10.7056〕「1.20604—1.206041-0.7071」］o.7O561一10.20817—0.20817一