Excel在主成份分析法中的应用.pdf

第 32 卷第 5 期四 川 林 业 科 技Vo1 32 No 5 2011 年10 月 Journal of Sichuan Forestry Science and TechnologyOct 2011 收稿日期 2011 03 28 基金项目 湖南省教育厅科学研究项目 07D036 作者简介 龚静 1972 女 湖南岳阳人 副教授 主要从事数据挖掘研究及教学工作 E mail 2427064 qq com 通讯作者 陈俊华 男 副研究员 E mail chenjh 2005 yahoo com cn Excel 在主成份分析法中的应用 龚静1陈俊华2 1 湖南环境生物职业技术学院 湖南 衡阳421005 2 四川省林业科学研究院 四川 成都610081 摘要 主成份分析法在多元统计分析中属协方差逼近技术 将高维系统表示为低维系统 在此过程中可揭示研究 对象的诸多性质和特征 其计算结果可用于回归分析 聚类分析以及神经网格分析等 在林业系统中也得到广泛 应用 本文首先简介了在 Excel 中如何建立与命名矩阵 然后举例详细简介了在 Excel 中如何求特征根 特征向量 方差贡献率 因子负荷矩阵等的计算模板 关键词 Excel 主成份分析法 函数 矩阵 应用 中图分类号 S711文献标识码 A 文章编号 1003 5508 2011 05 0095 04 On Application of Excel in Principal Component Analysis GONG Jing1 CHEN Jun hua2 1 Hunan Environmental Biology College of Vocational and Technical Hengyang421005 2 Sichuan Academy of Forestry Chengdu610081 Abstract The principal component analysis method in multivariate statistical analysis belongs to covari ance approximation technique and the high dimensional system is expressed as the low dimensional sys tem In this process many properties and characteristics of object of study can be revealed The calculated results can be used for regression analysis clustering analysis and neural grid analysis etc which are al so widely used in the forestry system In this paper a brief description is given of how using Excel to build and name matrix and then how to seek characteristic root feature vector variance contribution and factor calculation of load matrix etc Key words Excel Principal component analysis Function Matrix Application 主 成 份 分 析 principal components analysis PCA 是将多个观测指标因子变化为少数几个新指 标的一种多元统计方法 1 其分析结果可用于回归 分析 聚类分析 神经网格分析等等 我国林业专家 应用主成份分析法在多个领域 2 6 作了研究 主成 份分析的算法中 最麻烦的是矩阵特征根的计算 目 前在一些统计软件 7 中有集成模块 当然也可以通 过编程来计算 但数据录入 修改不方便 Microsoft Excel 能很好地解决这些问题 Microsoft Excel 是美国微软公司研制开发的用 于个人财务分析与规划 公司营运管理与目标设定 和薪水管理等的一个出色的电子表格软件 它不仅 具有友好的界面 强大的数据计算 并且还具有统计 分析功能 Excel 进行数据计算与统计分析的一个 显著特点是 它可以进行公式编辑和插人函数 而且 具有连环链锁计算能力 8 有关矩阵的特征根和 特征向量的 Excel 算法 相关文献中也有报道 9 11 但对整个主成份分析的过程却不见系统报道 下 面 笔者详细介绍如何在 Excel 2003 中建立数据进 行主成份分析 以与搞林业工作的同仁们分享 1原始数据的录入及相关系数的计算 本文以参考文献 7 第 11 章 因子分析法 的 数据为例 建立矩阵来进行主成份分析 以便与专业 软件 SPSS 进行数据结果精度的比较 在 Excel2003 中录入数据进行相关系数的计 算 非常简单 步骤如下 1 打开 Microsoft Excel 2003 选择区域 A1 F9 输入原始数据 图 1 图 1原始数据的录入 2 相关系数矩阵的计算 单击 工具 T 下拉菜单中的 数据分析 D 在弹出的 数据分析 窗口中 选择 相关系 数 输入要计算数据的区域等选项 单击 确定 后 结果如图 2 所示 图 2相关系数计算结果 2Excel 中矩阵的建立及命名 在 Excel 中 可以在一个单元格区域内通过逐 个输入矩阵的各个元素来建立矩阵 还可以使用数 组公式和数组常量更加方便地建立矩阵 12 1 新建一个工作表 选择单元格区域 A1 F6 2 输入公式 1 0 557 0 443 0 535 0 614 0 107 即 1 中计算出来的相关系 数矩阵 在 Excel 的数组公式中 将矩阵元素用大 括号 括起来称为数组常量 其中不同列的元素用 逗号隔开 不同行的元素用分号隔开 12 3 同时按下 Ctrl Alt Enter 键 完成数组的 输入 并在左上角框中将其命名为数组 A 如图 3 所 示 3特征根的计算 1 选择单元格区域 G1 L9 输入 6 阶单位矩 图 3矩阵的建立及命名 阵并将其命名为 I 2 在单元格 C7 中输入 MDETERM A 8 来计 算矩阵 A 的行列式值 其结果为 0 037248 3 预测矩阵 A 的特征值所在区间 Gersgorin 圆盘定理 13 认为 设对称方阵 A aij n n 则 A 的每一个特征值必属于下述某个圆盘 之中 aii ri n j 1 j i aij i 1 2 n 理论上 A 的特征值包含在区间 0 6 内 在区 间 0 6 取若干个点作特征多项式 f det A I 的 平滑散点图 步骤为 1 在工作表 Sheet1 的单元格 A8 中输入 0 按 住 Ctrl 键 拖动鼠标直到单元格 A68 在单元格 B8 中 输入工式 A8 10 同样拖动鼠标直到 B68 即输 入 0 0 1 0 2 每隔 0 1 取一个值 在单元格 C7 中输入公式 MDETERM A B8 I 按 Ctrl Shift Enter 键 在 C8 中形成数组公式 则 C8 中的 值 0 037248 为特征多项式 f 在 0 的值 图 4特征多项式 f 的平滑线散点图 2 选定单元格 C8 拖动鼠标直到 C68 计算各 特征根的值 可见有几次符号改变 而每一次符号改 变就说明此附近有一特征根 当到 2 3 以后符号不 再改变 说明 2 3 附近为 A 的最大特征根 3 选定单元格 B8 C31 即 2 3 作 XY 散点 图 的 平滑线散点图 类型曲线 见图 4 从图 4 可 以看出 A 的特征多项式与 x 轴有 6 个交点 还可以 看出这 6 个点分别落在区间 0 0 1 0 1 0 25 69四川林业科技32 卷 0 6 0 7 0 9 1 1 7 1 8 2 2 2 3 4 求 A 的全部特征根 1 选择工作表 Sheet2 里面的单元格 A1 A6 即 将这 6 个单元格作为求特征根的可变单元格 2 在 B1 中输入公式 MDETERM A A1 I 按 Ctrl Shift Enter 键 形成数组公式 MDETERM A A1 I 3 拖动 B1 到 B6 4 单击 工具 菜单中的 规划求解 命令 打开 规划求解参数 对话框 若没有这项 可单击 工 具 菜单中的 加载宏 I 在弹出的窗口中将 规 划求解 勾选上 5 在 设置目标单元格 E 中输入 B1 单击 等于 栏中的 值为 输入 0 在 可变单元格 B 中 输入或选择单元格区域 A1 A6 6 单击 添加 A 按钮 在 添加约束 对话框 中添加条件 A1 0 A1 0 1 7 单击 求解 按钮完成操作 此时单元格 A1 中的值 0 067662 就是实对称矩阵 A 的第一个特征 值 8 重复步骤 5 7 注意条件 这样就求出 了全部特征根 如图 5 所示 这与文献 7 中用统计 软件 SPSS 求出来的值非常接近 SPSS 求出的 6 个 值 分 别 为 2 285 1 793 0 954 0 700 0 201 0 068 7 图 5实对称矩阵 A 的全部特征值 4求累计方差贡献率 方差贡献率的计算公式较为简单 例如 对于 1 2 285 则方差贡献率为 2 285 6 38 09 以此类推 计算出来的方差贡献率及累计方差贡献 率见图 5 5每个特征值对应的特征向量及因子负荷 矩阵的求解 1 特征向量的解法 对应于 1的特征向量可由下面齐次方程组求 出 1 r11 x1 r12x2 r16x6 0 6 r61 x1 r62x2 r66x6 0 将矩阵 A 及 的值代人方程组 令 x6 1 用前 面的 5 个方程联立求解 就可以求得相对于 的特 征向量 这样求特征向量问题就是求解多元一次线 性方程组的问题 Excel 解线性方程组的方法主要 有逆矩阵法 迭代计算法以及规划求解法等 本文 以逆矩阵法为例来说明如何求特征向量 1 选定 Sheet3 中的区域 A1 F6 输入公式 P A P 为特征根形成的对角线矩阵 按下 Ctrl Shift Enter 键 计算矩阵 A 2 选择区域 A8 E12 输入公式 Minverse A1 E5 按下 Ctrl Shift Enter 键 计算前面 5 个未知 数形成的矩阵的逆矩阵 3 在 F8 F12 中输入 F1 F5 作为常数项 因为令 x6 1 4 矩阵相乘 选取区域 H8 H12 输入 MMULT A8 E12 F8 F12 按下 Ctrl Shift Enter 键 并在 H13 中输 入 1 求得方程的一组解 5 对解向量进行标准化 在单元格 I8 中 输入公式 H8 SQRT SUMSQ MYMHMYM8 MYMHMYM13 SUMSQ 函数为 求平方和 拖动到 I13 得到特征向量的值 将另外的 代入方程组 求得其他特征根所对 应的特征向量 2 因子负荷矩阵的求法 例如 对于 1 2 285 在 J8 中输入 SQRT 2 285 I8 拖动至 J13 可求得 1所对应的主成 份因子负荷向量 同样求得其他特征根所对应的因 子负荷向量 全部向量构成因子负荷矩阵 6结论 主成份分析法在林业中应用非常广泛 2 6 目 795 期龚静 等 Excel 在主成份分析法中的应用 前有些专业统计软件 如 SPSS SAS 等可以进行计 算 但这些软件属于 傻瓜 型软件 其操作过程仿 佛是在一个 暗箱 内进行的 对于使用者而言 可 能对计算结果无法进行合理解释 本文利用 Excel 进行主成份的计算 使读者理解其计算过程 如果再 结合 SPSS 进行计算结果的分析 就会感到理解透彻 些 此外 作者写这篇文章的目的还为了提供一些 Excel 的基本操作 尤其是有关矩阵 数组 的建立 及操作等 Excel 计算精度控制方便 操作简单 且 在各类计算机上随处可见 使用者众多 14 16 这也 是写这篇文章的目的所在 参考文献 1 陈华豪 洪伟等 林业应用数理统计 M 辽宁 大连海运学院 出版社 1988 8 第一版 243 251 2 陈松河 郑清芳 黄甜竹笋用林丰产培育技术模式的研究 J 竹子研究汇刊 2001 20 1 61 67 3 高永 孟海生 高国雄 等 吉兰泰盐湖引种樟子松的试验研究 J 内蒙古林学院学报 自然科学版 1999 21 2 12 18 4 朱春云 赵越 刘霞 等 锦鸡儿等旱生树种抗旱生理的研究 J 干早区研究 1996 13 1 59 63 5 谢会成 姜志林 叶镜中 麻栎光合作用的特性及其对 CO2倍 增的响应 J 南京林业大学学报 自然科学版 2002 26 4 67 70 6 陈存及 梁一池 邱尔发 等 毛竹种源多性状综合选择的研究 J 林业科学 2001 37 1 Sp 18 23 7 刘大海 李宁 晁阳编著 SPSS15 0 统计分析送入门到精通 M 清华大学出版社 北京 2008 298 317 8 桂红义 樊荣 张建森 等 EXCE1 97 中文版入门与提高 M 北京 清华大学出版社 1997 9 杨明波 卢建立 Excel 中矩阵的命名与特征值特征向量的计 算 J 电脑知识与技术 2007 3 1295 1296 10 曹永卿 吴宏斌 相关矩阵的特征值及特征向量的算法 J 湖南城建高等专科学校学报 2001 10 4 64 65 11 邵荣 用 Excel VBA 进行矩阵特征值的数值计算 J 江南大 学学报 自然科学版 2009 8 4 418 422 12 卢秋根 中文版 Office 应用基础教程 M 上海 上海科学普 及出版社 2005 年 9 月第 1 版 151 152 13 李庆杨 王能超 易大义 数值分析 M 4 版 北京 清华大学 出版社 2001 295 296 14 陈彦光编著 基于 Excel 的地理数据分析 M 科学出版社 北京 2008 15 陈俊华 慕长龙 朱志芳 Excel 在物元模型及层次分析法 AHP 中的应用 J 四川林业科技 2009 30 5 58 62 16 陈俊华 文吉富 王国良 向成华 等 Excel 在计算群落生物 多样性指数中的应用 J 四川林业科技 2009 30 3 88 90 櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏櫏 60 上接第 103 页 随时输出样图 校对修改后即可出图 错误率 返工 率均显著下降 尤其是分幅出图的实现 使得森林 分类区划成果的应用变得极其便利 3 3基于 ArcGIS 的森林分类区划