EXCEL分析工具库教程.docVIP

EXCEL分析工具库教程 第一节：分析工具库概述 “分析工具库”实际上是一个外部宏（程序）模块，它专门为用户提供一些高级统计函数和实用的数据分析工具。利用数据分析工具库可以构造反映数据分布的直方图；可以从数据集合中随机抽样，获得样本的统计测度；可以进行时间数列分析和回归分析；可以对数据进行傅立叶变换和其他变换等。本讲义均在Excel2007环境下进行操作。 1.1. 分析工具库的加载与调用 打开一张Excel表单，选择“数据”选项卡，看最右边的“分析”选项中是否有“数据分析”，若没有，单击左上角的图标，单击最下面的“Excel选项”，弹出“Excel选项”对话框，在左侧列表中选择“加载项”，在下方有“管理：Excel加载项转到”，单击“转到”，勾选“分析工具库”（加载数据分析工具）和“分析工具库-VBA”（加载分析工具库所需要的VBA函数）（图 11），单击确定，则“数据分析”出现在“数据｜分析”中。 图 11 加载分析工具库 1.2. 分析工具库的功能分类 分析工具库内置了19个模块，可以分为以下几大类： 表 11 随机发生器功能列表 分类 工具模块 抽样设计 随机数发生器 抽样 数据整理 直方图 参数估计 描述统计 排位与百分比排位 假设检验 z-检验：双样本均值差检验 t-检验：平均值的成对二样本分析 t-检验：双样本等方差假设 t-检验：双样本异方差假设 F检验：双样本方差检验 方差分析 方差分析：单因素方差分析 方差分析：无重复双因素方差分析 方差分析：可重复双因素方差分析 相关与回归分析 相关系数 协方差 回归 时间序列预测 移动平均 指数平滑 傅利叶分析 第二节．随机数发生器 重庆三峡学院 关文忠 1. 随机数发生器主要功能 “随机数发生器”分析工具可用几个分布之一产生的独立随机数来填充某个区域。可以通过概率分布来表示总体中的主体特征。例如，可以使用正态分布来表示人体身高的总体特征，或者使用双值输出的伯努利分布来表示掷币实验结果的总体特征。 2. 随机数发生器对话框简介 执行如下命令：“数据｜分析｜数据分析｜随机数发生器”，弹出随机数发生器对话框（图 21）。 图 21随机数发生器对话框 该对话框中的参数随分布的选择而有所不同，其余均相同。 变量个数：在此输入输出表中数值列的个数。 随机数个数：在此输入要查看的数据点个数。每一个数据点出现在输出表的一行中。 分布：在此单击用于创建随机数的分布方法。包括以下几种：均匀分布、正态分布、伯努利分布、二项式、泊松、模式、离散。具体应用将在第3部分举例介绍。 随机数基数：在此输入用来产生随机数的可选数值。可在以后重新使用该数值来生成相同的随机数。 输出区域：在此输入对输出表左上角单元格的引用。如果输出表将替换现有数据，Excel 会自动确定输出区域的大小并显示一条消息。 新工作表：单击此选项可在当前工作簿中插入新工作表，并从新工作表的 A1 单元格开始粘贴计算结果。若要为新工作表命名，请在框中键入名称。 新工作簿：单击此选项可创建新工作簿并将结果添加到其中的新工作表中。 3. 随机数发生器应用举例 3.1. 均匀随机数的产生 均匀：以下限和上限来表征。其变量是通过对区域中的所有数值进行等概率抽取而得到的。普通的应用使用范围 0 到 1 之间的均匀分布。相当于工作表函数：“= a+RAND()*(b-a)”，与RANDBETWEEN (a,b)”的区别是，RANDBETWEEN产生的是离散型随机数，而随机数发生器产生的是连续型随机数。 离散型函数产生可重复随机数，若想产生无重复随机数，应使用连续型，再从中利用RANK函数产生整型。通常在进行抽样设计时要产生无重复的整型均匀随机数。 例：在编号为1至20之间随机抽取10个无重复的均匀随机数。 数据｜分析｜数据分析｜随机数发生器｜“分布”选择均匀，产生对话框（图 22）： 图 22 均匀随机数对话框 单击“确定”生成连续型随机数（如图 23 A列）。 随机数 公式显示模式 图 23 产生随机数 由图可见，所产生的是连续型随机数，若四舍五入取整，在B1单元格输入公式“=ROUND(A1,0)”，并复制到B1:B10，得到整型随机数（图 23 B列）。由图可见，数字7出现了两次，为可重复随机数。在统计调查时，不能对同一调查对象调查两次，应产生无重复随机数。处理的办法如下： 在A列对总体进行编号；在B2输入如图所示公式，生产0至1之间的均匀随机数，并复制到B3:B21；C列显示样本序号；选择D2:D11单元格区域，输入D2单元格所示公式，按住Ctrl+Shift不放再按回车键，生成随机数。该随机数是无重复的。当然也可由VLOOKUP函数实现，所处从略。  图 24 无重复随机数的产生（普通模式与公式显著模式