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