Excel在数模中的运用.ppt

主讲人：吴慧萍 2008年10月24日,Excel在数模中的运用,Excel的主要功能,函数运用与计算 实用数据导入、排序与筛选 强大的作图制表功能 数据拟合与预测 求解矩阵的相关问题 BP神经网络插件,一、函数运用与计算,数列1-n （+Ctrl键） 函数 数学和三角函数（60条）例：ABS、FACT等 统计函数（80条） 例：AVERAGE、COUNT等 （参考网站：/zgq666/blog/item/04bf469062b8c18ba877a4e5.html ）,EXP 用途：返回e的n次幂 语法：EXP(number) FACT 用途：返回一个数的阶乘，即1*2*3*. 语法：FACT(number) GCD 用途：返回两个或多个整数的最大公约数 语法：GCD(number1，number2，.) FLOOR 用途：将参数Number沿绝对值减小的方向去尾舍入，使其等于最接近的significance的倍数 语法：FLOOR(number，significance) DEGREES 用途：将弧度转换为度 语法：DEGREES(angle),数学和三角函数,统计函数,AVERAGEA SUM COUNT MIN MAX SQRT(平方根) AVEDEV (与均值的绝对偏差的平均值) COVAR（协方差 ） STDEVA（样本标准差） VAR(方差),EXCEL的数据处理除了提供了很多的函数外，但这个工具必须加载相应的宏后才能使用，操作步骤为：点击菜单“工具-加载宏”，会出现一个对话框，从中选择“分析工具库”，点击确定后，在工具菜单栏内出现了这个分析工具。如果你的电脑中没有出现分析工具库，则需要使用OFFICE的安装光盘，运行安装程序。在自定义中点开EXCEL，找到分析工具库，选择“在本机运行”，安装添加即可。 在数据分析工具库中提供了3种基本类型的方差分析：单因素方差分析、双因素无重复试验和可重复试验的方差分析在这里仅介绍单因素方差分析（检验由单一因素影响的一个(或几个相互独立的)因变量由因素各水平分组的均值之间的差异是否具有统计意义 ）的运用。,方差分析,单因素方差分析,在进行单因素方差分析之前，须先将试验所得的数据按一定的格式输入到工作表中，其中每种水平的试验数据可以放在一行或一列内，表中每个水平的试验数据结果放在同一行内。 数据输入完成以后，操作“工具-数据分析”，选择数据分析工具对话框内的“单因素方差分析”，出现一个对话框，对话框的内容如下： 1输入区域：选择分析数据所在区域，可以选择水平标志，针对表中数据进行分析时选取（绿色）和*区域。 2分组方式：提供列与行的选择，当同一水平的数据位于同一行时选择行，位于同一列时选择列，本例选择行。 3如果在选取数据时包含了水平标志，则选择标志位于第一行，本例选取。 4：显著性水平，一般输入0.05，即95%的置信度。 5输出选项：按需求选择适当的分析结果存储位置。,二、实用数据导入、排序与筛选,在【数据】-【导入外部数据】-【导入数据】中选择要导入的数据源 【数据】-【排序】 【数据】-【筛选】-【自动筛选】 或者【高级筛选】,三、制表与数据拟合,制表：1.选定数据区域 2.【插入】-【图表】 或 “图表向导” 数据拟合与预测 通过添加趋势线，得函数拟合数据，再做预测,先输入原始矩阵 例如A1:B2 1 2 3 4 然后选择一个2X2的区域（例如 A4:B5），直接输入 =minverse(A1:B2) 接着按CTRL+SHIFT别松手，再按回车键。 OK，逆矩阵已经出来了,五、BP神经网络,神经网络简介 神经网络就是通过计算机程序对人类大脑的认知活动的一种模拟运算，神经网络具有人脑的学习记忆功能，主要应用到数据建模、预测和模式识别和函数优化等领域 神经网络的基本类形，主要有线形神经网络（主要应用在信号处理和简单的数据分类领域），前向传递神经网络（主要包括BP神经网络、径向基神经网络），其中应用最广泛的就是三层前向BP神经网络（市面上有很多工具箱都是基于BP神经网络的原理建立，如2nsoft等），还有反馈形神经网络（例如Elman、Hopfielddeng），他们一般用于信号处理和函数(条件)优化（主要是应用它们的预测和联想记忆功能）,还有一种就是自组织神神经网络（主要包括学习矢量量化神经网络和自组织特征映射神经网络）。,在神经网络领域应用最广的一类神经网络：BP神经网络（主要用于模型预测和模式识别还有数据分类）。,BP神经网络的理论基础基于一个定理：就是从理论上已经证明，一个三层前向传递BP神经网络在理论上可以实现对任意有有限个间断点的任意函数实现从n维空间到m维空间的映射。,从中我们可以看出BP神经网络的最基本应用就是函数拟和（可以实现对任意函数的映射），我们从数学角度可以理解这一点，同时，这样我们也可以理解BP神经网络可以实现预测的原因（这也是我们所最关注的），BP能实现模式识别（例如银行对不同用户的签名的识别）的主要原理就是先找出和每个字母相对应的对应关系，然后利用BP神经网络的学习功能就可以实现识别，实现数据分类的原理和模式识别很相似,预测的整个流程是：,（1），原始数据的划分：一般划分维建模数据（输入数据，一般占数据总量的2/3-3/4）和验证数据（也叫输出数据，一般占数据总量的1/4-1/3） （2），数据的预处理（指的就是数据的归一化处理），一般有三种方法。 归一化化定义：我是这样认为的，归一化化就是要把你需要处理的数据经过处理后（通过某种算法）限制在你需要的一定范围内。首先归一化是为了后面数据处理的方便，其次是保正程序运行时收敛加快。 （3），BP神经网络模型的建立，一般建立的都是三层前向传递神经网络模型。,网络的基本结构,网络结构包括好多参数：传递函数，学习函数，隐层神经元个数的设定等，一般来说输入层到输出层用tansig（正切S型函数），隐层到输出层一般用purelin（线形传递函数）函数，分类是一般用（logsig函数，对数S型函数），隐层神经元个数一般在5到30之间（实际中需要根据预测精度的要求进行调整，个数并不是越多越好），最复杂就是网络的学习函数，最常用和最有效（个人观点）就是三种，分别为traingdm、trainlm和trainbr。还有其他一些网络参数，例如网络迭代次数的设定（一般不会超过10000，不过有时一百万此都可能），还有网络目标误差的设定（一般在0.010.00001之间）。,利用excel插件作bp神经网络分析,输入量2,神经网络,输出量,输入量1,Excel插件nnx介绍,本软件是针对使用神经网络中最烦琐的数据和图形表示而开发，特别方便用户处理数据和编辑图形 注意事项： 1)基本步骤是按菜单中的“新建网络”-“训练样本”-和“预测”进行，可以保存和打开以前训练好的网络模型并了解权值。 2)训练数据前需要打开或输入样本数据的Excel工作表,利用神经网络预测基本步骤,建立神经网络 输入样本数据 训练网络，让网络进行学习直至误差降低到可接受的范围 输入需要预测的数据 Bp模拟仿真计算，得出预测结果,网络目标误差设定与训练,为什么要设置网络目标误差：BP（backprogatation）的源意就是反向传播算法，它每运算一次，网络都会有一个输出网络误差，它会和你设置的网络目标误差相比较，一次来运行网络，知道它的输出误差，在规定的迭代次数内小于你预先设定的目标误差才停止原算，BP神经网络就是这么来的。 下面将对建立好的网络模型进行训练，训练就是指让网络产生学习记忆的功能，为了后来所数据预测做好准备。 最后就是对网络模型可靠型的验证：具体说就是用预留出来的验证数据对网络的预测精度进行检验，如果达到你所接受的范围，那此时的网络结构以后就可以用于你的预测应用了，如果达不到要求，就要修改网络参数，让