EXCEL分析工具库教程

EXCEL分析工具库教程第一节：分析工具库概述“分析工具库”实际上是一个外部宏（程序）模块，它专门为用户提供一些高级统计函数和实用的数据分析工具。

利用数据分析工具库可以构造反映数据分布的直方图；可以从数据集合中随机抽样，获得样本的统计测度；可以进行时间数列分析和回归分析；可以对数据进行傅立叶变换和其他变换等。

本讲义均在Excel2007环境下进行操作。

1.1. 分析工具库的加载与调用打开一张Excel表单，选择“数据”选项卡，看最右边的“分析”选项中是否有“数据分析”，若没有，单击左上角的图标，单击最下面的“E xcel选项”，弹出“Excel选项”对话框，在左侧列表中选择“加载项”，在下方有“管理：Excel加载项转到”，单击“转到”，勾选“分析工具库”（加载数据分析工具）和“分析工具库-VBA”（加载分析工具库所需要的VBA函数）（图 1-1），单击确定，则“数据分析”出现在“数据｜分析”中。

图 1-1 加载分析工具库1.2. 分析工具库的功能分类分析工具库内置了19个模块，可以分为以下几大类：表 1-1 随机发生器功能列表第二节．随机数发生器重庆三峡学院关文忠1.随机数发生器主要功能“随机数发生器”分析工具可用几个分布之一产生的独立随机数来填充某个区域。

可以通过概率分布来表示总体中的主体特征。

例如，可以使用正态分布来表示人体身高的总体特征，或者使用双值输出的伯努利分布来表示掷币实验结果的总体特征。

2.随机数发生器对话框简介执行如下命令：“数据｜分析｜数据分析｜随机数发生器”，弹出随机数发生器对话框（图2-1）。

图2-1随机数发生器对话框该对话框中的参数随分布的选择而有所不同，其余均相同。

变量个数：在此输入输出表中数值列的个数。

随机数个数：在此输入要查看的数据点个数。

每一个数据点出现在输出表的一行中。

分布：在此单击用于创建随机数的分布方法。

包括以下几种：均匀分布、正态分布、伯努利分布、二项式、泊松、模式、离散。

具体应用将在第3部分举例介绍。

随机数基数：在此输入用来产生随机数的可选数值。

可在以后重新使用该数值来生成相同的随机数。

输出区域：在此输入对输出表左上角单元格的引用。

如果输出表将替换现有数据，Excel 会自动确定输出区域的大小并显示一条消息。

新工作表：单击此选项可在当前工作簿中插入新工作表，并从新工作表的A1单元格开始粘贴计算结果。

若要为新工作表命名，请在框中键入名称。

新工作簿：单击此选项可创建新工作簿并将结果添加到其中的新工作表中。

3.随机数发生器应用举例3.1.均匀随机数的产生均匀：以下限和上限来表征。

其变量是通过对区域中的所有数值进行等概率抽取而得到的。

普通的应用使用范围0到1之间的均匀分布。

相当于工作表函数：“=a+RAND()*(b-a)”，与RANDBETWEEN (a,b)”的区别是，RANDBETWEEN产生的是离散型随机数，而随机数发生器产生的是连续型随机数。

离散型函数产生可重复随机数，若想产生无重复随机数，应使用连续型，再从中利用RANK函数产生整型。

通常在进行抽样设计时要产生无重复的整型均匀随机数。

例：在编号为1至20之间随机抽取10个无重复的均匀随机数。

数据｜分析｜数据分析｜随机数发生器｜“分布”选择均匀，产生对话框（图 2-2）：图2-2均匀随机数对话框单击“确定”生成连续型随机数（如图 2-3 A列）。

随机数公式显示模式图2-3产生随机数由图可见，所产生的是连续型随机数，若四舍五入取整，在B1单元格输入公式“=ROUND(A1,0)”，并复制到B1:B10，得到整型随机数（图 2-3 B列）。

由图可见，数字7出现了两次，为可重复随机数。

在统计调查时，不能对同一调查对象调查两次，应产生无重复随机数。

处理的办法如下：在A列对总体进行编号；在B2输入如图所示公式，生产0至1之间的均匀随机数，并复制到B3:B21；C列显示样本序号；选择D2:D11单元格区域，输入D2单元格所示公式，按住Ctrl+Shift不放再按回车键，生成随机数。

该随机数是无重复的。

当然也可由VLOOKUP 函数实现，所处从略。

图2-4无重复随机数的产生（普通模式与公式显著模式）3.2.正态随机数的产生正态分布描述：图2-5正态分布描述图2-6正态分布曲线正态：以平均值和标准偏差来表征，相当于工作表函数“=NORMINV(rand(),mu,sigma)”例：产生10行8列来自均值为100、标准差为10的总体随机数。

“数据｜分析｜数据分析｜随机数发生器”，选择“分布”为“正态”，设置对话框如下：图2-7随机数发生器对话框的正态分布设置单击“确定”生成随机数如下：图2-8产生的正态分布随机数3.3.产生0-1分布随机数伯努利：以给定的试验中成功的概率（p值）来表征。

伯努利随机变量的值为0或1。

等价于函数：“=IF(RAND()”.例：产生5列10行的成功概率为0.5的0-1随机数。

验证概率的频率法定义。

数据｜分析｜数据分析｜随机数发生器｜“分布”选择柏努利，设置对话框如下：图2-90-1随机数对话框单击“确定”生成随机数（图 2-9 A至E列）。

在G列输入累积的试验度数；H2输入公式，统计正态朝上的次数（1的个数）；I2求得频率；鼗H2:I2复制到H3:I21单元格区域（图 2-10、图 2-11）。

以H列为横坐标，I列为纵坐标，绘制不带标志点的折线型散点图（图 2-12）。

由图可见，随机试验次数的增加，频率逐步趋于0.5.图2-10产生的0-1分布随机数（公式显示模式）图2-11产生的0-1分布随机数图2-12频率法概率定义的验证3.4.产生二项分布随机数二项式：以一系列试验中成功的概率（p值）来表征。

例如，可以按照试验次数生成一系列伯努利随机变量，这些变量之和为一个二项式随机变量。

二项分布描述：图2-13二项分布描述图2-14二项分布曲线例：某射手中靶的概率为0.8，每次射击10发子弹，射击10次，模拟每次中靶的次数。

“数据｜分析｜数据分析｜随机数发生器”，选择“分布”为“二项”，设置对话框如下：图2-15随机数发生器对话框的二项分布设置单击“确定”生成随机数如下：图2-16产生的二项分布随机数3.5.产生泊松分布随机数泊松：以值λ来表征，λ等于平均值的倒数。

泊松分布经常用于表示单位时间内事件发生的次数，例如，汽车到达收费停车场的平均速率。

其描述如下：图2-17泊松分布描述图2-18泊松分布曲线例：某加油站，平均每小时前来加油的车辆为10辆，试进行100次模拟，并求其分布情况。

“数据｜分析｜数据分析｜随机数发生器”，选择“分布”为“泊松”，设置对话框如下：图2-19随机数发生器对话框的泊松分布设置单击“确定”生成随机数如下：图2-20产生的泊松分布随机数求得最大值，最小值，确定组限，利用frequency函数统计频数，并求频率如下图。

图2-21频数统计（公式显示模式）图2-22频数统计3.6.产生重复序列模式：以下界和上界、步幅、数值的重复率和序列的重复率来表征。

在生物遗传学中常用到重复序列。

EXCEL的“模式”所产生的重复序列是按相同步长产生的重复序列。

如：下列对话框设置：图2-23重复序列对话框可产生的重复序列为：1122331122331122333.7.产生离散随机数离散：以数值及相应的概率区域来表征。

该区域必须包含两列，左边一列包含数值，右边一列为与该行中的数值相对应的发生概率。

所有概率的和必须为1。

例如：某商品销售情况根据某干时期统计如下（经验分布）：试进行80次模拟。

（1）在A列和B列输入参数（经验分布）图2-24离散（经验分布）随机数的产生（2）数据｜分析｜数据分析｜随机数发生器｜离散，设置如下：图2-25离散分布对话框（3）单击确定，在C1:M8产生80个随机数。

（4）对产生的随机数利用frequency函数统计频数，并求频率（见O:Q列）。

第三节．抽样重庆三峡学院关文忠“抽样”分析工具以数据源区域为总体，从而为其创建一个样本。

当总体太大而不能进行处理或绘制时，可以选用具有代表性的样本。

如果确认数据源区域中的数据是周期性的，还可以仅对一个周期中特定时间段中的数值进行采样。

例如，如果数据源区域包含季度销售量数据，则以四为周期进行采样，将在输出区域中生成与数据源区域中相同季度的数值。

1.1.随机抽样（1）打开一张工作表，输入总体编号或总体标志值（本例A2:J11单元格区域）。

图3-1随机抽样（2）数据｜分析｜数据分析｜抽样，弹出抽样对话框：图3-2随机抽样对话框设置单击“确定”生成随机样本（图 3-1L列）。

注意，该样本是可重复抽样，重复率与总体单位数成反比，与样本量成正比。

1.2.周期抽样例：从1至10编号按固定周期间隔分别为2、3、4、5抽样。

图3-3周期抽样对话框设置单击“确定”抽得样本（D列），取间隔依次取3、4、5,输出区域依次改为E2、F2、G2,得随机数如图图 3-4E、F、G列。

图3-4周期抽取的样本该种抽样类似等距抽样，但不同的是统计学中的等距抽样是在第1组进行简单随机抽样，以后的样本等于首样本位置依次加组距的k倍。

第四节．直方图重庆三峡学院关文忠1.直方图的功能“直方图”分析工具可计算数据单元格区域和数据接收区间的单个和累积频率。

此工具可用于统计数据集中某个数值出现的次数，其功能基本上相当于函数FREQUENCY。

所不同的是可以添加累积百分比、百分比排序及插入图表等。

需要注意的是，该工具只能对数值型标志进行统计，且各组频数是包含组上限的。

如统计学生成绩，若组限确定为“60以下、60-70、70-80、80-90、90-100”则统计结果将60分划分为不及格组之中。

因此可根据最小分值差确定上限，如“0-59.5,…”,更强大的数据整理工具可使用“数据透视表”工具。

2.直方图工具的使用例：对图中的数据按组数7进行等距分组，利用直方图工具统计频数。

图4-1统计分组观测值数据操作步骤：（1）先确定组上限图4-2组上限的确定（公式显著模式）利用工作表函数在H1和H2单元格求得最大和最小值；H3求得全距R，H4为确定的组数，H5计算组距。

J2为第1组上限＝最小值+组距；其他各组上限均等于前组上限+组距。

图4-3组上限（2）调用直方图工具在EXCEL表格中进行如下操作：“数据｜分析｜数据分析｜直方图”，弹出直方图工具对话框。

图4-4直方图对话框设置输入区域：观测值所在的单元格区域。

接收区域：组上限所有的单元格区域。

标志：如果数据源区域的第一行或第一列中包含标志项，请选中此复选框。

输出区域：在此输入对输出表左上角单元格的引用，可在当前工作表中输入结果。

新工作表：在当前工作簿中插入新工作表，并从新工作表的A1单元格开始粘贴计算结果。