1、Excel 在统计分析与金融计量中的应用 解决那些传说中的统计功能！金融博士 自己用的工具 Excel 简介 Microsoft Excel 是美国微软公司开发的 Windows 环境下的电子表格系统，它是目 前应用为广泛的办公室表格处理软件之一。Excel 具有强有力的数据库管理功能、丰 富的宏命令和函数、强有力的决策支持工具，它具有以下主要特点。（1）分析能力（2）操作简便（3）图表能力（4）数据库管理能力（5）宏语言功能（6）样式功能（7）对象连接和嵌入功能（8）连接和合并功能 Excel 基本操作 鼠标操作法数据的手动输入：数值型，字符型，日期 型，和逻辑型公式生成数据：如SUM（求
2、和）、CORREL（求相关系数）、STDEV（求标准差）复制生成数据：公式单元格的复制，一种是值复制，一种是公式复制。=A2+B2，=$A$1+$B$1 ，=A$1+B$1（水平变化），与其它软件交换数据的方法 Excel在描述统计中的应用在使用Excel进行数据分析时，要经常使用到Excel中一些函数和数据分析工具。其中，函数是Excel预定义的内置公式。它可以接受被称为参数的特定数值，按函数 的内置语法结构进行特定计算，后返回一定的函数运算结果。例如，SUM函数对单 元格或单元格区域执行相加运算，PMT函数在给定的利率、贷款期限和本金数额基础 上计算偿还额。函数的语法以函数名称开始，后面是
3、左圆括号、以逗号隔开的参数和 右圆括号。参数可以是数字、文本、形如TRUE或FALSE的逻辑值、数组、形如#N/A的 错误值，或单元格引用。给定的参数必须能产生有效的值。参数也可以是常量、公式 或其它函数。Excel还提供了一组数据分析工具，称为“分析工具库”，在建立复杂的统计分析 时，使用现成的数据分析工具，可以节省很多时间。只需为每一个分析工具提供必要的 数据和参数，该工具就会使用适宜的统计或数学函数，在输出表格中显示相应的结果。其中的一些工具在生成输出表格时还能同时产生图表。如果要浏览已有的分析工具，可 以单击“工具”菜单中的“数据分析”命令。如果“数据分析”命令没有出现在“工 具”菜单
4、上，则必须通过“工具”菜单中的“加载宏”命令，在“加载宏”对话框中选 择并启动它。单变量分析，提供数据趋中性和易变性等 有关信息。1用鼠标点击工作表中待分析数据的任一单元格。2选择“工具”菜单的“数据分析”子菜单。3用鼠标双击数据分析工具中的“描述统计”选项。4出现“描述统计”对话框，对话框内各选项的含义如下：输入区域：在此输入 待分析数据区域的单元格范围。一般情况下Excel会自动根据当前单元格确定待分析数 据区域。分组方式：如果需要指出输入区域中的数据是按行还是按列排列，则单击“行”或“列”。标志位于第一行/列：如果输入区域的第一行中包含标志项（变量名），则选中“标 志位于第一行”复选框；
5、如果输入区域的第一列中包含标志项，则选中“标志位于第一 列”复选框；如果输入区域没有标志项，则不选任何复选框，Excel将在输出表中生成 适宜的数据标志。均值置信度：若需要输出由样本均值推断总体均值的置信区间，则选中此复选框，然后在右侧的编辑框中，输入所要使用的置信度。第 K个大/小值：如果需要在输出表的某一行中包含每个区域的数据的第 K 个 大/小值，则选中此复选框。然后在右侧的编辑框中，输入 K 的数值。输出区域：在此框中可填写输出结果表左上角单元格地址,用于控制输出结果的存 放位置。整个输出结果分为两列,左边一列包含统计标志项，右边一列包含统计值。根 据所选择的“分组方式”选项的不同，E
6、xcel 将为输入表中的每一行或每一列生成一个 两列的统计表。新工作表：单击此选项，可在当前工作簿中插入新工作表，并由新工作表的A1单元 格开始存放计算结果。如果需要给新工作表命名，则在右侧编辑框中键入名称。新工作簿：单击此选项，可创建一新工作簿，并在新工作簿的新工作表中存放计算 结果。汇总统计：指定输出表中生成下列统计结果，则选中此复选框。这些统计结果有：平均值、标准误差、中值、众数、标准偏差、方差、峰值、偏度、极差（全距）小值、大值、总和、样本个数。填写完“描述统计”对话框之后，按“确定”按扭即可。直方图工具 1用鼠标点击表中待分析数据的任一单元格。2选择“工具”菜单的“数据分析”子菜单。
7、3用鼠标双击数据分析工具中的“直方图”选项。4出现“直方图”对话框，对话框内主要选项的含义如下：输入区域：在此输入待分析数据区域的单元格范围。接收区域（可选）：在此输入接收区域的单元格范围，该区域应包含一组可选的用 来计算频数的边界值。这些值应当按升序排列。只要存在的话，Excel将统计在各个相 邻边界值之间的数据出现的次数。如果省略此处的接收区域，Excel将在数据组的小 值和大值之间创建一组平滑分布的接收区间。标志：如果输入区域的第一行或第一列中包含标志项，则选中此复选框；如果输 入区域没有标志项，则清除该复选框，Excel将在输出表中生成适宜的数据标志。输出区域：在此输入结果输出表的左上
8、角单元格的地址。如果输出表将覆盖已有 的数据，Excel会自动确定输出区域的大小并显示信息。柏拉图：选中此复选框，可以在输出表中同时显示按降序排列频率数据。如果此复 选框被清除，Excel将只按升序来排列数据。累积百分比：选中此复选框，可以在输出结果中添加一列累积百分比数值，并同 时在直方图表中添加累积百分比折线。如果清除此选项，则会省略以上结果。图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。5按需要填写完“直方图”对话框之后，按“确定”按扭即可。结果说明：完整的结果通常包括三列和一个频率分布图，第一列是数值的 区间范围，第二列是数值分布的频数，第三列是频数分布的累积百分比
9、。利用Excel绘制散点图 数据透视表工具 1选中图3中表格中有数据的任一单元格，然后选择“数据”菜单的“数据透视 表”子菜单，进入数据透视表向导。2选择“Microsoft Excel数据清单或数据库”为数据源。单击“下一步”。3选择待分析的数据的区域，一般情况下Excel会自动根据当前单元格确定待分析 数据区域，因此你只要直接单击“下一步”按扭即可。4确定数据透视表的结构，在此例中，要建立的是一个交叉频数表，分别按语文 和数学的成绩对学生的人数进行交叉频数分析，因此可按图4将三个项目“学号”、“语 文”、“数学”分别拖放到表格的指定部位，并且双击“求和项：学号”，将其改为记数项，然后单击“
10、下一步”按扭。5选择数据透视表的显示位置之后，单击“完成按扭”，可出现如图5所示的数据 透视表。排位与百分比工具 用鼠标点击表中待分析数据的任一单元格。2选择“工具”菜单的“数据分析”子菜单。3用鼠标双击数据分析工具中的“排位与百分比”选项。4填写完“排位与百分比”对话框，单击“确定”按扭即可。Excel在推断统计中的应用 二项分布工具（1）简介在Excel中想要计算二项分布的概率分布、累积概率，需要利用Excel的工作表函数 BINOMDIST。函数BINOMDIST适用于固定次数的独立实验，实验的结果只包含成功或失败 二种情况，且每次实验成功的概率固定不变。例如，已知次品概率的情况下，函数
11、 BINOMDIST可以计算10个产品中发现2个次品的概率。以下例子说明如何在Excel中计算 二项分布的概率，以及如何建立二项分布图表。（2）操作步骤例子如下所示，一个推销员打了六个电话，推销成功的概率是0.3，那么可以按以 下步骤建立推销成功次数的概率分布图表。1如图6所示，先在Excel之下建立好概率分布表格的框架。图6 二项分布框架表 2.先在B7至F7单元格分别输入概率计算公式:“=BINOMDIST(A7,6,0.3,0)”，“=BINOMDIST(A7,6,0.3,1)”“=C7-B7”，“=1-C7”，“=1-D7”3公式的拷贝。选取B7至F7单元格，拖动“填充柄”至F13单元
12、格即可完成公式的 拷贝操作。4下面开始创建二项分布图表。选取B7至B13单元格，选取“插入”菜单的“图表”子菜单。5选择“柱状图”，然后单击“下一步”。6单击“系列”标签，单击“分类(X)轴标志”框，并用鼠标选取A7至A13单元格 为图表X轴的轴标，然后单击“下一步”。7分别键入图表名称“二项分布图”，X轴名称“成功次数”，Y轴名称“成功概 率”，单击“完成”按扭即可生成二项分布图表。BINOMDIST(number_s,trials,probability_s,cumulative)其中 number_s 为试验成功的次数；trials 为独立试验的次数；probability_s 为每次试
13、验 中成功的概率；cumulative为一逻辑值，用于确定函数的形式。如果cumulative为 TRUE，函数 BINOMDIST 返回累积分布函数，即至多 number_s 次成功的概率；如果为 FALSE，返回概率密度函数，即 number_s 次成功的概率。函数CRITBINOM可称为BINOMDIST的逆向函数，它返回使累积二项式分布 概率P(Xx)。如果tails=2，函数TDIST返回双尾 分布，TDIST的计算公式为TDIST=P(|X|x)=P(Xx or X-x)。3举例：TDIST(1.96,60,2)=0.054645 随机抽样的工具 1.利 用 Excel 的 RAN
14、DBETWEEN(TOP,BOTTOM)函数实现总体的重复随机抽样。RANDBETWEEN(TOP,BOTTOM)函数可随机返回介于TOP与BOTTOM之间的整数，抽取此整数对编号的样本可作为总体的 重复随机抽样的结果。2.RAND()函数返回的是0与1之间均匀的随机数，利用Excel数据分析工具中的随机 数发生器，可以生成用户指定类型分布的随机数。例如0-1正态分布的随机数，指定参 数的泊松分布的随机数等。3.利用Excel易于产生各类型随机数的特性，可以用类似的方法方便的进行随机数 字模拟试验与随机游走模拟试验。由样本推断总体 利用Excel的几个函数，如求平均函数AVERAGE、标准差函
15、数STDEV、t分布函数TINV 等的组合使用可以构造出一个专门用于实现样本推断总体的Excel工作表。以下例子先 计算样本的平均数和标准差，然后在一定置信水平上估计总体均值的区间范围。1构造工作表。如图9（a）所示，首先在各个单元格输入以下的内容，其中左边是变量名，右边是相应的计算公式。要显示公式，用鼠标选择“工具”菜单的“公式审 核”子菜单的“公式审核模式”选项即可。2.为表格右边的公式计算结果定义左边的变量名。选定A4:B6,A8:B8和A10:B15单 元格(先选择第一部分，再按住CTRL键选取另外两个部分)，选择“插入”菜单的“名 称”子菜单的“指定”选项，用鼠标点击“左列”选项,然
16、后点击“确定”按扭即可。3输入样本数据，和用户指定的置信水平0.95,如图9（b）所示。4为样本数据命名。选定D1:D11单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“首行”选项,然后点击“确定”按扭，得到图9（b）所示的计算结果。以上例子说明如何交叉组合使用Excel的公式和函数，以构造出一 个能实现样本推断总体有关计算的Excel工作表。实际上，在用Excel进行数据统计处理 之时，许多统计功能可以使用和上例类似的方法，通过组合使用Excel的各类统计函数 和公式加以实现的。假设检验 以下例子利用Excel的正态分布函数NORMSDIST、判断函数IF 等，构造一张能
17、够实现在总体方差已知情况下进行总体均值 假设检验的Excel工作表。1构造工作表2.为表格右边的公式计算结果定义左边的变量名。选定A3:B4,A6:B8，A10:B11,A13:B15和A17:B19 单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“左列”选项,然后点击“确定”按扭即可。3输入样本数据，以及总体标准差、总体均值假设、置信水平数据。4为样本数据命名。选定C1:C11 单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“首行”选项,然后点击“确定”按扭，得到如图11中所示 的计算结果。结果说明：该例子的检验结果不论是单侧还是双侧均为拒绝Ho 假设
18、。所以，根据样本的计算结果，在5%的显著水平之下,拒绝总体均值为35的假设。同时由单侧显著水平的计算结果还可以看出，在总体均值是35的假设之下，样本均值小 于等于31.4的概率仅为0.02030363。假设检验 双样本等均值假设检验 双样本等均值检验是在一定置信水平之下,在两个总体方差相等的假设之下，检验 两个总体均值的差值等于指定平均差的假设是否成立的检验。我们可以直接使用在 Excel数据分析中提供双样本等均值假设检验工具进行假设检验。以下通过一例说明双 样本等均值假设检验的操作步骤。例子如下，某工厂为了比较两种装配方法的效率，分 别组织了两组员工，每组9人，一组采用新的装配方法，另外一组
19、采用旧的装配方法。18个员工的设备装配时间图12中表格所示。根据以下数据，是否有理由认为新的装配方 法更节约时间？以上例子可按如下步骤进行假设检验。正态性的 X2检验 2检验可以用来判断所观测的样本是否来自某一特定分布的总体，这种检验亦称 为一致性检验。以下例子，已知某样本的相关统计量和分组频数分布如图14所示，试用 X2 检验判断该样本是否来自一正态总体。按同样的方法可以作总体泊松分布、总体超几何分布等其它分布的检验。此类统计 应用也是由Excel各类公式和函数综合使用而实现的,为了以后使用方便，和上面的一些 例子一样，一般需要将整个表格的计算框架和标志项罗列好，再保存成文件，以后只要 对数
20、据项稍作修改即可很快得到计算结果。如果对Excel宏语言较为熟悉，还可以将它 编成一个宏语言程序，加入Excel的工具栏，这样以后使用起来更为方便。列联表分析 列联表分析经常用来判断同一个调查的对象的两个特性之间是否存在明显相关关 系。例如，房地产商常常设计列联表问卷，调查顾客的职业和顾客所选房子的类型是否 有明显的相关关系。列联表分析同样也可以由Excel加以实现，下面用一个例子给予说 明。如图19所示，表中是某装修公司的调查报告，试用列联表分析方法分析在顾客的所 在地区和所选房子的地板类型之间是否存在明显的相关关系。操作步骤 1建立期望频数表。如图19所示，先建立期望频数表的框架，然后在B
21、10单元格输入公式“=B$7*$F4/$F$7”，再利用“填充柄”将公式复制到表格的其它单元格，后利用 Excel的求和函数sum计算行和与列和。2计算 X2 概率值。在A15单元格输入标志项“卡方概率值”，先点击B15单元格，从“插入”菜单中“函数”子菜单，选择“统计函数”中的“CHITEST”函数，单击“确定按扭，然后在弹出的对话框中分别添入实际频数范围“B4:E6”和预期频数范围“B10:E12”。后 单击“确定”按扭即可得到计算结果1E-07,如图20所示。（3）结果分析：以上的操作步骤完成整个列联表的分析。其中，B15单元格的卡方 概率值与B24单元格的卡方统计量是表格的两个重要计算
22、结果。其中卡方概率值等于 1E-07，表明如果总体的两类属性是不相关的，那么得到以上观察的样本的概率是 0.0000001。这个概率几乎接近于0，所以可以认为总体的这两个属性是显著相关的。单因素方差分析 单因素方差分析可用于检验两个或两个以上的总体均值相等的假设是否成立。此方 法是对双均值检验（如t-检验）的扩充。检验假定总体是服从正态分布的，总体方差是 相等的，并且随机样本是独立的。这种工具使用于完全随机化试验的结果分析。例子如 图22中所示，一产品制造商雇佣销售人员向销售商打电话。制造商想比较四种不同电话 频率计划的效率，他从销售人员中随机选出32名，将他们随机分配到4种计划中，在一段时期
23、内记录他们的销售情况已经在表中列出，试问其中是否有一种计划会带来较高的 销售水平。按照如上的操作步骤即可得到图24的计算结果。其中表格的第二部分则是方差分析 的结果。SS列分别给出了四个分组的组间方差、组内方差以及总方差，DF列分别给出了 对应方差的自由度，MS列是平均值方差，由SS除以DF得到，它是总体方差的两个估计值。F列是F统计量的计算结果，如果四个总体均值相等的假设成立的化，它应该服从F分布，即近似为1，它是终的计算结果，通过将它与一定置信水平下的F临界值F crit比较，可以判断均值相等的假设是否成立，在本例中1.677612.94668，所以不能拒绝四个总 体均值相等的假设。P-v
24、alue列，是单尾概率值，表明如果四个总体均值相等的假设成立的话，得到如上样本结果的概率是19.442%,即得到以上样本并不是小概率事件，同 样也得到不能拒绝四个总体均值相等的假设的结论。按相似方法可进行无重复双因素方差分析，有重复双因素方差分析。线性回归分析）结果分析 按照如上的操作步骤即可得到图26的计算结果。结果可以分为四个部分，第一部分 是回归统计的结果包括多元相关系数、判定系数 2 R、调整之后的相关系数、回归标准 差以及样本个数。第二部分是方差分析的结果包括可解释的离差、残差、总离差和它们 的自由度以及由此计算出的F统计量和相应的显著水平。第三部分是回归方程的截距和 斜率的估计值以及它们的估计标准误差、t统计量大小双边拖尾概率值、以及估计值的 上下界。根据这部分的结果可知回归方程为Y=0.08309*X+2.77457。第四部分是样本散 点图，其中蓝色的点是样本的真实散点图，红色的点是根据回归方程进行样本历史模拟的散点。如果觉得散点图不够清晰可以用鼠标拖动图形的边界达到控制图形大小的目 的。用相同的方法可以进行多元线性方程的参数估计，还可以在自变量中引入虚拟变量 以增加方程的拟合程度。对于非线性的方程的参数估计，可以在进行样本数据的线性化 处理之后，再按以上步骤进行参数估计。未完待续制作中太长了，下一篇吧。
