EXCEL在统计学中应用.ppt

统计学与统计工具 学生第二课堂自制课程 统计学与统计软件 市场经济呼唤统计学统计学是研究客观事物数量特征和数量关系的方法论学科 能够告诉人们如何通过打开几扇窗口去探索一个未知的世界 教会人们怎样用一种新的方式来思考问题 是一门很实用的学科 大至国家的宏观决策 小至企事业单位的微观管理 都离不开统计的应用 现代市场经济对统计信息的需求急剧增加 对统计理论与方法提出了更高的要求 社会发展问题 经济可持续发展问题 国际竞争力问题 金融风险管理问题 保险精算问题 人口与社会保障问题 环境保护问题 科学研究探索问题等等 这些都迫切地等待着我们去深入地研究 统计学的分类 统计学与统计软件的关系 统计分析软件是数据分析的主要工具完整的数据分析过程包括 数据的收集数据的整理数据的分析结果的解释和推论统计学为数据分析过程提供一套完整的科学的方法论 统计软件为数据分析提供了实现手段 常用统计软件介绍 SAS Ver9 1 真正的巨无霸 被誉为国际上的标准统计软件和最权威的组合式优秀统计软件 常用统计软件简介SPSS Ver13 统计软件中的贵族最易上手的统计软件 NCSS国产统计软件的佼佼者 DPSExcel它严格说来并不是统计软件 但作为数据表格软件 必然有一定统计计算功能 统计分析软件的一般特点 功能全面 系统地集成了多种成熟的统计分析方法 有完善的数据定义 操作和管理功能 方便地生成各种统计图形和统计表格 使用方式简单 有完备的联机帮助功能 软件开放性好 能方便地和其他软件进行数据交换 Excel在统计分析中的应用 主要内容 一 Excel基本介绍二 数据管理功能三 统计图的绘制四 统计分析功能 一 Excel基本介绍 MicrosoftExcel是当前功能强大 技术先进和使用方便灵活的电子表格系统 Excel的历史渊源1987年10月 美国Microsoft公司推出视窗版Excel 超越 被公认达到软件技术的最佳专业水平 1993年 微软又把Word和Excel集成在Office办公套装软件内 使其能相互共享数据 MicrosoftOffice办公室自动化集成软件的重要组成部分 它是目前应用最为广泛的表格处理软件之一 自Excel诞生以来 主要历经了Excel3 0 Excel4 0 Excel5 0 Excel95 Excel97和Excel2000 Excel2003等不同版本 随着版本的不断提高 Excel强大的数据处理功能和操作的简易性逐渐走入了一个新的境界 整个系统的智能化程度也不断提高 它甚至可以在某些方面判断用户的下一步操作 使用户操作大为简化 这些特性 已使Excel成为现代办公软件重要的组成部分 Excel的应用领域及应用特点 Excel的应用领域在财务 金融 统计 办公自动化等领域广泛应用Excel的应用特点操作简单易学易用用户界面友好 Excel的基本功能 表格处理 可制作各类表格 并具有强大的处理功能图形处理 系统有100种以上的图表供选用 还可自行绘制数据运算 有400多个函数 可完成复杂的表内和表间计算 并可进行适当的统计分析数据库管理 将有组织的数据清单当作数据库处理 Excel2003的界面 标题栏 菜单栏 工具栏 编辑栏 列标 单元格 工作区 行号 滚动按钮 表格签 滚动条 任务窗格 下拉按钮 Office助手 2007版本 Excel的基本概念 工作簿在Excel中创建的文件叫工作簿 由1个或多个工作表组成 最多可以包括255个工作表 是Excel管理数据的文件单位 相当于人们日常生活中的 文件夹 它们以独立文件的形式存储在磁盘上 工作表工作表就是人们平常所说的电子表格 由一些横向和纵向的网格组成 横向称为行 纵向称为列 在网格中可以填写不同的数据 一个工作表最多可有65536行 256列 行号工作表由65536行组成 每行用一个数字进行编号列标每列用字母来标识 称为列标 列标用字母A到IV表示 共256列 Excel的基本概念 Excel的基本概念 单元格Excel的工作表实际上是一个二维表格 单元格就是这个表格中的一个 格子 单元格是输入数据 处理数据及显示数据的基本单位 单元格中的内容可以是数字 文本或计算公式等 最多可包含32000个字符 二 数据管理功能 数据列表数据排序数据筛选数据的分类与汇总 数据列表 数据排序 按单变量排序根据某一列的数据按单一关键字对行数据进行排序 单击要排序的变量列中的任一单元格 再单击常用工具栏的 升序 或 降序 按钮即可 数据排序 按多变量排序根据某一列的数据排序时 会遇到有相同数据的情况 此时可采用按多个列排序的方法 单击要排序的数据列表中的任一单元格 再单击 数据 排序 排序 对话框 数据筛选 有时需要从工作表的数据行中找出满足一定条件的几行或几列数据 这就要用到Excel的数据筛选功能 数据筛选将工作表中所有不满足条件的数据行暂时隐藏起来 只显示那些满足条件的数据行 数据并没有丢失 Excel提供了两种不同的筛选方式 自动筛选高级筛选 数据的分类与汇总 分类汇总是对数据列表按某一字段值进行分类 将同类别数据放在一起 并分别为各类数据进行统计汇总 求总和 平均值 最大值 最小值 统计个数等 操作过程 先对数据列表依据分类字段排序单击 数据 菜单 分类汇总 分类汇总 对话框 三 统计图的绘制 条图圆图线图散点图 条图 单式条图 条图 单式条图 条图 复式条图 圆图 普通线图 半对数线图 散点图 四 统计分析功能 描述性统计推断统计分析 在使用Excel进行数据分析时 要经常使用到Excel中一些函数和数据分析工具 其中 函数是Excel预定义的内置公式 它可以接受被称为参数的特定数值 按函数的内置语法结构进行特定计算 最后返回一定的函数运算结果 例如 SUM函数可对单元格或单元格区域执行相加运算 函数的语法以函数名称开始 后面分别是左圆括号 以逗号隔开的参数和右圆括号 参数可以是数字 文本 形如TRUE或FALSE的逻辑值 数组 形如 N A的错误值 或单元格地址 给定的参数必须能产生有效的值 参数也可以是常量 公式或其它函数 Excel还提供了一组数据分析工具 称为 分析工具库 在建立复杂的统计分析时 使用现成的数据分析工具 可以节省很多时间 只需为每一个分析工具提供必要的数据和参数 该工具就会使用适宜的统计或数学函数 在输出表格中显示相应的结果 其中的一些工具在生成输出表格时还能同时产生图表 要浏览已有的分析工具 可以单击 工具 菜单中的 数据分析 命令 如果 数据分析 命令没有出现在 工具 菜单上 则必须运行 安装 程序来加载 分析工具库 安装完毕之后 再通过 工具 菜单中的 加载宏 命令 在 加载宏 对话框中选择并启动它 分析工具库的安装 在安装Microsoftoffice办公软件时必须选择 完全安装 分析工具库才会被安装到电脑上 操作过程 单击 工具 菜单 加载宏 加载宏 对话框 工具 菜单 描述性统计 描述性统计中常用的统计量包括 均数 中位数 几何均数等平均数指标极差 方差 标准差 四分位数间距 变异系数等变异指标最小值 最大值Excel提供了两种不同的计算方式 利用函数进行描述统计利用分析数据库进行描述统计 利用分析数据库进行描述统计 操作过程 单击 工具 菜单 数据分析 数据分析 对话框 描述统计 描述统计 对话框 参看视频 利用函数进行描述统计 计算算术均数的函数计算几何均数的函数计算中位数的函数 AVERAGE B5 B14 GEOMEAN B5 B14 MEDIAN B5 B14 MAX B5 B14 MIN B5 B14 STDEV B5 B14 QUARTILE B5 B14 3 QUARTILE B5 B14 1 B19 B15 100 推断统计的应用 二项分布工具其他几种主要分布函数随机抽样工具样本推断总体假设检验单因素方差分析线性回归分析 二项分布工具 一 简介 在Excel中想要计算二项分布的概率值 累积概率 需要利用Excel的工作表函数BINOMDIST 函数BINOMDIST适用于固定次数的独立实验 实验的结果只包含成功或失败二种情况 且每次实验成功的概率固定不变 例如 已知次品概率的情况下 函数BINOMDIST可以计算抽查10个样品中发现2个次品的概率 以下例子说明如何在Excel中计算二项分布的概率值 以及如何进一步建立二项分布图表 二 操作步骤 例子如下所示 一个推销员打了六个电话 推销成功的概率是0 3 那么可以按以下步骤建立推销成功次数的概率分布图表 1 如图附 1所示 先在Excel之下建立好概率分布表格的框架 图附 1 2 如图附 2所示 先在B7至F7单元格分别输入概率计算公式 图附 2 3 公式的拷贝 选取B7至F7单元格 然后移动鼠标至F7单元格的右下角 使其成为黑色实心十字星状 一般称之为 填充柄 拖动 填充柄 至F13单元格即可完成公式的拷贝操作 结果图附 3所示 图附 3 4 下面开始创建二项分布图表 选取B7至B13单元格 选取 插入 菜单的 图表 子菜单 5 选择 柱状图 然后单击 下一步 6 单击 系列 标签 单击 分类 X 轴标志 框 并用鼠标选取A7至A13单元格为图表X轴的轴标 然后单击 下一步 7 分别键入图表名称 二项分布图 X轴名称 成功次数 Y轴名称 成功概率 单击 完成 按扭即可生成二项分布图表 三 结果说明 如图附 3所示 利用Excel的BINOMDIST的函数可以计算出二项分布的概率以及累积概率 BINOMDIST函数可以带四个参数 各参数的含义分别是 实验成功的次数 实验的总次数 每次实验中成功的概率 是否计算累积概率 第四个参数是一个逻辑值 如果为TRUE 函数BINOMDIST计算累积分布函数概率值 如果为FALSE 计算概率密度函数概率值 其它几种主要分布的函数 一 函数CRITBINOM 1 说明 函数CRITBINOM可称为BINOMDIST的逆向函数 它计算出使累积二项式分布概率P X x 大于等于临界概率值的最小值 2 语法 CRITBINOM trials probability s alpha Trials 贝努利实验次数 Probability s 一次试验中成功的概率 Alpha 临界概率 3 举例 CRITBINOM 6 0 5 0 75 等于4 表明如果每次试验成功的概率为0 5 那么6次试验中成功的次数小于等于4的概率恰好超过或等于0 75 二 函数HYPGEOMDIST 1 说明 函数HYPGEOMDIST计算超几何分布 给定样本容量 总体容量和样本总体中成功的次数 函数HYPGEOMDIST计算出样本取得给定成功次数的概率 使用该函数可以解决有限总体的问题 其中每个观察值只有两种取值 或者为成功或者为失败 且给定样本区间的所有子集有相等的发生概率 2 语法 HYPGEOMDIST sample s number sample population s number population Sample s 样本中成功的次数 Number sample 样本容量 Population s 样本总体中成功的次数 Number population 样本总体的容量 3 举例 容器里有20块巧克力 8块是焦糖的 其余12块是果仁的 如果从中随机选出4块 下面函数计算式计算出只有一块是焦糖巧克力的概率 HYPGEOMDIST 1 4 8 20 0 363261 随机抽样工具 一 简介 Excel中的RAND 函数可以产生大于等于0小于1的均匀分布随机数 RAND 不带任何参数运行 每次计算时时都将产生一个新的随机数 如果将RAND 函数从一个单元格复制或移动到另外一个单元格 RAND 函数也将重新计算一个新的数值 RAND 函数可以被用来作为不重复抽样调查的工具 二 操作步骤 如图附 4所示 10个象征性的样本数据 欲从中随机抽取5个数据可按如下步骤操作 图附 4 1 选择B2单元格 输入公式 RAND 并回车 2 拖动B2单元格右下角的填充柄至B11单元格 并在B1单元格输入列标志名称 random 3 选取单元格B2至B11 右击选中的区域选择 复制 再次右击选中的区域 选择 选择性粘贴 单击选项 数值 后 点击 确定 按扭 此时B2 B11单元格是10个稳定的随机数 4 选取单元格A2至B11单元格 选择 数据 菜单项下的排序子菜单 5 选取 RANDOM 为主要关键字 然后点击 确定 按扭 排序结果如图附 5所示 可以用A2至A6单元格的样本作为随机抽取的5个样本 图附 5 三 结果说明 1 以上进行的是不重复随机抽样 可以用类似的方法 利用Excel的RANDBETWEEN TOP BOTTOM 函数实现总体的重复随机抽样 RANDBETWEEN TOP BOTTOM 函数可随机产生介于TOP与BOTTOM之间的随机整数 抽取此整数对应编号的样本可作为总体的重复随机抽样的结果 2 RAND 函数产生的是0与1之间均匀的随机数 利用数据分析工具中的随机数发生器 可以生成用户指定类型分布的随机数 例如0 1正态分布的随机数 指定 参数的迫松分布的随机数等 3 Excel易于产生各类型随机数 可以用类似的方法方便的进行进行随机数字模拟试验与随机游走模拟试验 样本推断总体 一 简介 利用Excel的几个函数 如求平均函数AVERAGE 标准差函数STDEV T分布函数TINV等的组合使用可以构造出一个专门用于实现样本推断总体的Excel工作表 以下例子先计算样本的平均数和标准差 然后在一定置信水平上估计总体均值的区间范围 二 操作步骤 1 构造工作表 如图附 6所示 首先在各个单元格输入以下的内容 其中左边是变量名 右边是相应的计算公式 2 将A列的名称定义成为B列各个公式计算结果的变量名 选定A4 B6 A8 B8和A10 B15单元格 先用鼠标选择第一部分 再按住CTRL键选取另外两个部分 选择 插入 菜单的 名称 子菜单的 指定 选项 用鼠标点击 最左列 选项 然后点击 确定 按扭即可 3 输入样本数据 和用户指定的置信水平0 95 如图附 13所示 4 为样本数据命名 选定D1 D11单元格 选择 插入 菜单的 名称 子菜单的 指定 选项 用鼠标点击 首行 选项 然后点击 确定 按扭 最后得到图附 14所示的计算结果 图附 6 三 结果说明 以上例子说明如何交叉组合使用Excel的公式和函数 以构造出一个能实现样本推断总体有关计算的Excel工作表 实际上 在用Excel进行数据统计处理之时 许多统计功能可以使用和上例类似的方法 通过组合使用Excel的各类统计函数和公式加以实现 图附 7 假设检验 一 简介 假设检验是统计推断中的重要内容 以下例子利用Excel的正态分布函数NORMSDIST 判断函数IF等 构造一张能够实现在总体方差已知情况下进行总体均值假设检验的Excel工作表 二 操作步骤 1 构造工作表 如图附 8所示 首先在各个单元格输入以下的内容 其中左边是变量名 右边是相应的计算公式 图附 8 2 将A列的名称定义成为B列各个公式计算结果的变量名 选定A3 B4 A6 B8 A10 A11 A13 A15和A17 B19单元格 选择 插入 菜单的 名称 子菜单的 指定 选项 用鼠标点击 最左列 选项 然后点击 确定 按扭即可 3 输入样本数据 以及总体标准差 总体均值假设 置信水平数据 如图附 9所示 图附 9 4 为样本数据指定名称 选定C1 C11单元格 选择 插入 菜单的 名称 子菜单的 指定 选项 用鼠标点击 首行 选项 然后点击 确定 按扭 最后得到如图附 16中所示的计算结果 三 结果说明 如图附 9所示 该例子的检验结果不论是单侧还是双侧均为拒绝Ho假设 所以 根据样本的计算结果 在5 的显著水平之下 拒绝总体均值为35的假设 同时由单侧显著水平的计算结果还可以看出 在总体均值是35的假设之下 样本均值小于等于31 4的概率仅为0 020303562 0 05 小概率事件居然发生 所以 同样得出在5 的显著水平下 拒绝总体均值为35的假设的结论 单因素方差分析 一 简介 单因素方差分析可用于检验两个或两个以上的总体均值相等的假设是否成立 此方法是对双均值检验 如t 检验 的扩充 该检验假定总体是服从正太分布的 总体方差是相等的 并且随机样本是独立的 这种工具适用于完全随机化试验的结果分析 例子如图附 10表中所示 一产品制造商雇佣销售人员向销售商打电话 制造商想比较四种不同电话频率计划的效率 他从销售人员中随机选出32名 将他们随机分配到4种计划中 在一段时期内记录他们的销售情况已经在表中列出 试问其中是否有一种计划会带来较高的销售水平 图附 10 单因素方差分析 一 简介 单因素方差分析可用于检验两个或两个以上的总体均值相等的假设是否成立 此方法是对双均值检验 如t 检验 的扩充 该检验假定总体是服从正太分布的 总体方差是相等的 并且随机样本是独立的 这种工具适用于完全随机化试验的结果分析 例子如图附 17表中所示 一产品制造商雇佣销售人员向销售商打电话 制造商想比较四种不同电话频率计划的效率 他从销售人员中随机选出32名 将他们随机分配到4种计划中 在一段时期内记录他们的销售情况已经在表中列出 试问其中是否有一种计划会带来较高的销售水平 图附 10 单因素方差分析 一 简介 单因素方差分析可用于检验两个或两个以上的总体均值相等的假设是否成立 此方法是对双均值检验 如t 检验 的扩充 该检验假定总体是服从正太分布的 总体方差是相等的 并且随机样本是独立的 这种工具适用于完全随机化试验的结果分析 例子如图附 17表中所示 一产品制造商雇佣销售人员向销售商打电话 制造商想比较四种不同电话频率计划的效率 他从销售人员中随机选出32名 将他们随机分配到4种计划中 在一段时期内记录他们的销售情况已经在表中列出 试问其中是否有一种计划会带来较高的销售水平 图附 10 二 操作步骤1 选择 工具 菜单的 数据分析 子菜单 双击 方差分析 单因素方差分析 选项 弹出单因素方差分析对话框 2 按图附 11所示方式填写对话框 然后单击 确定 按扭即可 图附 11 三 结果分析 按照如上的操作步骤即可得到图附 12的计算结果 其中表格的第二部分则是方差分析的结果 SS列分别给出了四个分组的组间方差 组内方差以及总方差 DF列分别给出了对应方差的自由度 MS列是平均值方差 由SS除于DF得到 它是总体方差的两个估计值 F列是F统计量的计算结果 如果四个总体均值相等的假设成立的化 它应该服从F分布 即近似为1 它是最终的计算结果 通过将它与一定置信水平下的F临界值Fcrit比较 可以判断均值相等的假设是否成立 在本例中 1 67761 2 94668 所以不能拒绝四个总体均值相等的假设 P value列 是单尾概率值 表明如果四个总体均值相等的假设成立的化 得到如上样本结果的概率是19 442 即得到以上样本并不是小概率事件 同样也得到不能拒绝四个总体均值相等的假设的结论 按相似方法可进行无重复双因素方差分析 有重复双因素方差分析 图附 12 线性回归分析 一 简介 线性回归分析通过使用 最小二乘法 对样本数据进行直线拟合 用于分析单个因变量是如何受一个或几个自变量影响的 例子如图附 13所示 表中是我国1987年至1997年的布匹人均产量和人均纱产量 试用线性回归分析的方法分析两组数据之间的关系 图附 13 二 操作步骤1 选择 工具 菜单的 数据分析 子菜单 双击 回归 选项 弹出回归分析对话框 对话框主要选项的含义如下 Y值输入区域 在此输入因变量数据区域 该区域必须由单列数据组成 X值输入区域 在此输入对自变量数据区域 自变量的个数最多为16 置信度 如果需要在汇总输出表中包含附加的置信度信息 则选中此复选框 然后在右侧的编辑框中 输入所要使用的置信度 95 为默认值 常数为零 如果要强制回归线通过原点 则选中此复选框 输出区域 在此输入输出表左上角单元格的地址 用于控制计算结果的输出位置 汇总输出表至少需要有七列的宽度 包含的内容有anova表 系数 y估计值的标准误差 r2值 观察值个数 以及系数的