Excel在统计学中的应用

Excel在统计中的应用 学习目标掌握Excel中数据的输入和编辑掌握Excel的数据管理功能掌握Excel中图表的创建和设置掌握Excel的数据分析功能 第一章数据的输入和编辑 1 工作簿Excel工作簿的文件扩展名为 xls 工作簿默认情况下有三张工作表Sheet1 Sheet2和Sheet3 可根据需要增减工作表 但最多不超过255张 工具 选项 常规 改变工作簿中默认工作表的数目 第一节Excel的基础知识 2 工作表工作表是一个由行和列组成的二维表 其中行号用数字标识 1 65536 列号用字母标识 A IV Z列之后是AA列 AZ列之后是BA列 如此一直到IV列 因此 一个工作表最多可包含65536行和256列 3 单元格单元格是Excel工作表的最小组成单位 所有对工作表的操作都是建立在对单元格操作的基础上 每一个行和列的交叉处即为一个单元格 单元格的名字由它所在行和列的位置决定 例如 C3表示第3行第C列交叉处的单元格 编辑确认区 状态栏 工作簿窗口组成 Excel的启动和退出 1 启动 开始 程序 MicrosoftExcel双击桌面上Excel的快捷方式图标双击Excel文档2 退出 文件 关闭 文件 退出 ALT F4 快捷键 工作簿的操作 建立工作簿M新建空白工作簿方法1 单击 常用 工具栏上的 新建 按钮 可以直接新建一个空白工作簿 工作簿默认的取名为Book1 Book2 Book3等 方法2 选择 文件 菜单中的 新建 命令 在 新建工作簿 任务窗格中单击 空白工作簿 M根据模板新建 文件 新建 新建工作簿 通用模板 改变所选模板的显示形式 选择 电子方案表格 选项卡 2 打开已有的工作簿 M 文件 菜单 打开 命令 3 保存建立好的工作簿 M 文件 菜单 保存 命令 4 关闭工作簿 M 文件 菜单中的 关闭 命令 M标题栏右端的 按钮 单个单元格的选择 鼠标单击选定连续单元格区域 选中区域左上角的单元格 按下鼠标拖动至区域右下角的单元格 先选中左上角的单元格 按住Shift键单击区域右下角的单元格 当然用Shift键加方向键同样可完成区域的选定 区域的表示方法为 左上角单元格名 右下角单元格名 例如A2 D5 注意 选中区域后 活动单元格为此区域的左上角单元格 工作表的操作 选定多个不连续单元格 多个区域 先选定第一个单元格 或区域 按住Ctrl键 再选择其他单元格 或区域 选定多个不连续区域 选定行和列要选中整行 列 单击行 列 号即可 要选定几行 列 若这几行 列 是连续的 沿行 列 号拖动鼠标 或者先选定区域中的第一行或列 然后按住Shift键再选定区域中最后一行或列 若是不连续的行 列 那么先选定第一行 列 然后按住Ctrl键再选定其他的行 列 选定整个工作表单击工作表行号和列号交叉处的全选框即可 取消原选定只要单击任一单元格 就可取消工作表内原先选定的单元格或区域 1 单个单元格数据输入直接输入 选定单元格直接数据数据确认输入 Enter键 tab键 或单击编辑栏上的 按钮 第二节EXCEL中数据的输入 Excel中常用的数据类型 1 文本输入文本左对齐 输入纯数字形式的文本 身份证号码 电话号码 邮政编码等 在第一个数字前加一个单引号 2 数值输入数值右对齐输入分数时 应先输入 0 和一个空格 然后再输入分数 3 日期和时间输入例如 f98 5 81998 5 8f5 85月8日f14 2014 20fctrl 系统日期fctrl shift 系统时间 4 公式输入先输入 然后输入公式内容 2 系列数据自动填充输入 1 相同数据的输入方法一 选定输入相同数据的区域 输入数据 再按CTRL ENTER键 方法二 在第一单元格内输入数据 拖动其右下角的填充柄到最后一个单元格 2 系列数据输入输入字符数据 在第一单元格内输入数据 向下或向右拖动其右下角的填充柄 输入数值序列数据 应先在第一单元格内输入数据 按住CTRL键再拖动其右下角的填充柄 选择填充类型 如果初始值是文字和数字的混合体 填充时文字不变 数字递增 自动填充柄 自定义序列数据方法1 工具 选项 自定义序列 选项卡 新序列 在此处输入新序列 自定义序列 方法2 导入区域中的数据序列 通过此按钮选择导入数据的区域 3 使用鼠标建立序列输入前两个单元格中的数据 选中这两个单元格 再拖动选定区域右下角的填充柄进行填充 4 使用 序列 对话框建立序列输入第一个单元格中的初始值 单击 编辑 填充 序列 命令 在行方向上填充从当前日期开始到5月1日结束的所有工作日 1 公式中的运算符 算术运算符 文字运算符 比较运算符 单元格引用运算符 空格 第三节EXCEL中数据的计算 算术运算符 比较运算符 文本运算符 用于将一个或多个文本连接为一个更长的文本 例如 Excel用起来 很方便 得到 Excel用起来很方便 另外 除了以上介绍的运算符外 还有一些符号也可以当作运算符 例如 用于区域的表示中 用于函数中多个参数之间进行分隔 和 用于强制性改变运算的优先次序 当一个公式中出现不止一种运算符时 其优先次序为 算术运算符 文本运算符 比较运算符 2 编制公式输入公式的方法是 选定要输入公式的单元格后 先输入 然后输入根据具体情况编写的公式 最后按回车键或鼠标单击编辑栏中的按钮确定 注意 如果选中公式所在的单元格 单元格中显示的是公式的计算结果 公式本身在编辑框中显示 修改公式的方法是 只需选中此单元格 然后在编辑框中进行修改 修改结束后 按回车键或鼠标单击编辑栏中的按钮即可 3 单元格引用 1 相对引用 当编制的公式被复制到其他单元格中时 能够根据移动的位置自动调节引用的单元格 操作 直接输入单元格的名称 2 绝对引用 复制公式时 绝对引用单元格将不随公式位置的移动而改变单元格的引用 不论公式复制到哪里 公式中引用的单元格都不会改变 操作 在行号和列标前面均加上一个 3 混合引用 是指引用单元格名称时 在行号前加 符或在列标前加 符号的引用方法 4 非当前工作表中单元格的引用引用方法 工作表标签 单元格引用 常用函数及其格式 求和函数 sum 参数1 参数2 求平均函数 average 参数1 参数2 统计个数函数 count 参数1 参数2 最大值函数 max 参数1 参数2 最小值函数 min 参数1 参数2 4 函数引用函数引用格式 函数名 参数1 参数2 函数的输入插入点定位到引用函数的位置 插入 函数 或单击编辑栏确定区中的按钮函数类别和函数名设置函数参数 函数参数折叠框 相邻区域的数据求和 选中单元格后单击自动计算按钮 5 自动求和 自动求和按钮 也可以直接双击此单元格 常见错误信息 单元格中公式所产生的结果太长 该单元格容纳不下 可以通过调整单元格的宽度来消除该错误 日期或时间格式的单元格中出现负值 DIV 0 如果单元格中出现 DIV 0 错误信息 很可能该单元格的公式中出现零除问题 即输入的公式中包含除数0 也可能在公式中的除数引用了零值单元格或空白单元格 而空白单元格的值将解释为零值 常见错误信息 N A在函数或公式中没有可用数值时 会产生这种错误信息 NAME 在公式中使用了Excel所不能识别的文本时将产生错误信息 NAME NUM 这是在公式或函数中某个数值有问题时产生的错误信息 常见错误信息 NULL 在单元格中出现此错误信息的原因可能是试图为两个并不相交的区域指定交叉点 例如 使用了不正确的区域运算符或不正确的单元格引用等 REF 单元格中出现这样的错误信息是因为该单元格引用无效的结果 VALUE 当公式中使用不正确的参数或运算符时 将产生错误信息 VALUE 第四节EXCEL中的数据编辑 1 修改数据修改部分内容选中单元格后在编辑框中修改双击单元格进行修改替换单元格内容选中单元格后直接输入新内容 2 清除数据选定单元格区域 按住delete键只能清除数据内容 不能清除格式 编辑 菜单下的 清除 命令 可选择 格式 内容 批注 全部 将单元格的格式恢复为 常规 相当于选中单元格后按Del键 保留格式和批注 包括以下三项 只删除批注 其他项 3 复制和移动数据鼠标拖动法按住CTRL的同时拖动鼠标实现的是复制操作 直接拖到鼠标实现的是移动操作 使用 剪贴板 选中源区域后 选择 编辑 复制 然后选择 编辑 粘贴 将上述 复制 改为 剪切 即为 移动 数据 也可直接使用 常用 工具栏上的相关按钮 5 1单元格 行 列的插入和删除 1 工作表的行 列 单元格的插入 插入 菜单下的 单元格 行 列 命令2 工作表的行 列 单元格的删除步骤 a 选定要删除的对象b 编辑 菜单下的 删除 命令 第五节工作表的格式化 1 调整行高与列宽 1 鼠标拖动 拖动行 列号分隔线2 菜单命令 选定行或列 格式 菜单 行或列 行高 或 列宽 命令 退出 上页 下页 5 2工作表格式化编辑 2 对Excel工作表可进行下列格式化操作 数字格式日期时间格式字符对齐方式字体格式单元格边框底纹和图案保护格式 退出 上页 下页 格式化工作表的主要操作界面 格式 菜单 单元格 单元格格式 对话框 目录 退出 上页 下页 数字格式的设定 目录 退出 上页 下页 从未输入过数据的单元格中输入数字 默认 常规 格式设置数字格式的方法 方法1 选定单元格 或区域 单元格格式 对话框 数字 标签方法2 使用格式工具栏货币样式 百分比样式 千位分隔样式 增加 减少小数位数等 日期 时间格式的设定 单元格格式 对话框 数字 标签 选择日期 时间中的某个 类型 目录 退出 上页 下页 标题与数据对齐方式的设定 1 标题 居中 2 数据对齐 目录 退出 上页 下页 标题 居中 方法1 格式 工具栏 合并及居中 按钮方法2 单元格格式 对话框 对齐 标签 水平及垂直居中 并选定 合并单元格 复选框 目录 退出 上页 下页 数据对齐 1 对齐 方法1 格式 工具栏 水平对齐方式按钮方法2 单元格格式 对话框 对齐 标签 水平或垂直对齐方式2 旋转 单元格格式 对话框 对齐 标签 拖动方向中的红色标志 目录 退出 上页 下页 字符格式设定 方法1 使用 格式 工具栏方法2 单元格格式 对话框 字体 标签 目录 退出 上页 下页 网格线与边框 1 网格线 工具 菜单 选项 选项 对话框 视图 标签 网格线 复选项2 边框 1 格式 工具栏 边框按钮2 单元格格式 对话框 边框 标签 设置边框颜色 线型 样式等注意 应先设置线条 再单击相应框线按钮 目录 退出 上页 下页 单元格底纹与颜色 单元格格式 对话框 图案 标签 目录 退出 上页 下页 条件格式 选定区域 格式 菜单 条件格式 条件格式 对话框 设置相关条件 可同时设置三个条件 退出 上页 下页 自动套用格式 选定单元格区域 格式 菜单 自动套用格式 自动套用格式 对话框 退出 上页 下页 复制格式 使用格式刷进行格式复制使用格式刷能复制的格式有 字体格式 数字格式 对齐方式 框线 底纹 是否合并单元格等 退出 上页 下页 删除格式 选定要删除格式的单元格 区域 编辑 菜单 清除 格式 退出 上页 退出 上页 下页 第六节EXCEL工作表的编辑 工作表选择1 单个工作表的选择方法一 单击工作表标签方法二 右键单击标签滚动按钮处 选择标签名 2 多个工作表的选择 多个连续工作表 单击第一个工作表标签 按住SHIFT同时单击最后一个工作表标签 多个不连续工作表 CTRL 单击工作表标签选择全部工作表 右键单击任一工作表标签 在快捷菜单中选 选择全部工作表 取消工作组选定 单击任一未选定工作表标签 或右键单击在快捷菜单中选 取消成组工作表 工作表的插入 删除和重命名1 工作表插入操作方法 单击 插入 菜单下的 工作表 命令2 工作表删除方法一 选定工作表 单击 编辑 菜单下的 删除 命令方法二 选定工作表 右键单击选择快捷菜单中的 删除 命令3 工作表重命名操作步骤 选定工作表 右键单击选择快捷菜单中的 重命名 命令 在反白显示的工作表标签处删除原名 输入新名 回车确认 工作表的复制和移动移动工作表 鼠标拖动复制工作表 按住ctrl键同时 鼠标拖动方法二 选定工作表 右键单击标签 在快捷菜单中选择 移动或复制工作表 命令 在出现的对话框中 设定目标位置 复制选定 建立副本 项 方法一 工作表窗口的拆分和冻结 1 工作表窗口拆分拆分方法 拖动 水平 或 垂直 拆分按钮撤销拆分方法 双击拆分线或 窗口 菜单下的 撤销拆分窗口 2 工作表窗口冻结是指将工作表窗口的上部或左部固定住 使其不随滚动条的滚动而移动 冻结方法 选定冻结行或列的下一行或右一列 单击 窗口 菜单的 冻结拆分窗口 命令 取消冻结方法 单击 窗口 菜单中的 撤销冻结窗口 命令 第二章数据的管理和分析 Excel除了具有数据计算功能 还提供了简单的数据库管理功能 使用它可以对表中数据进行例如列表 排序 筛选等工作 数据列表是将工作表某一个区域中的数据作为数据库处理 按照行 列方式组织数据 每一行称为记录 每一列称为字段 数据列表的第一行称为标题行 由字段名组成 第一节数据列表 数据清单 数据清单 正确创建规则 列名唯一纯数据 杜绝空行 非法数据 同列数据的数据类型和格式应完全相同避免在一张工作表上放置多份数据清单 使用 数据 记录单 菜单命令 打开某数据清单的记录单 利用记录单可以进行下列操作 输入数据修改和删除数据查找数据清单中的记录 使用记录单编辑数据清单 排序分类 单列数据的排序 快速排序 复杂数据的排序 轻重缓急 数据类型 升序 降序 文本A B ZZ X A数字1 2 10 9 日期Jan1 Dec31 数据排序 操作步骤 第一步 选择排序的数据区域 第二步 打开 数据 菜单 单击 排序 弹出 排序 对话框 第三步 选择排序的主要关键字及按递增或递减 第四步 单击 确定 复杂数据的排序 复杂数据的排序 用以设置两个以上的排序约束条件 筛选数据 大表 明晰的小表 有条件的筛选 自动筛选的操作 第一步 选择筛选的数据区域 第二步 单击 数据 指向 筛选 单击 自动筛选 在数据清单第一行的各字段名旁出现一个下拉箭头 第三步 在要筛选字段名的下拉箭头上单击 并在列表中选择或输入筛选条件 第四步 单击 确定 取消某个字段的筛选条件 单击该字段的筛选箭头 选择 全部 即可 取消所有的筛选条件 选择 数据 菜单的 筛选 选项中再次选择 自动筛选 命令项即可 分类汇总 先分类 排序 后按类汇总分析 1 简单汇总 在分类汇总前必须按分类关键字段排序 选择所需的汇总单元格区域 单击 数据 中 分类汇总 选择分类字段 与排序的分类关键字段一致 选择 汇总方式 选定 汇总项 选择 替换当前分类汇总 汇总结果显示在数据下方 单击 确定 按钮 完成分类汇总 分类汇总数据分级显示数据默认分为3级 分级显示数据 3 嵌套分类汇总 嵌套分类汇总的前提是数据已按两个以上关键字排序分类 分类汇总 数据透视表 数据透视表也是一种汇总表 但它提供了一种自动的功能使得创建和汇总数据变得方便简单 用户可以随时按照不同的需要 依不同的关系提取和组织数据 1 创建数据透视表 单击数据清单中的任一单元格 调用菜单命令 数据 数据透视表和图表报告 指定待分析数据的数据源类型为 MicrosoftExcel数据清单或数据库 所需的报表类型为 数据透视表 单击 下一步 选定要建立数据透视表的数据源区域 单击 下一步 选择 数据透视表显示位置 设定数据透视表的版式 单击 确定 单击 完成 按钮 完成要求的数据透视表 2 更新数据透视表中的数据方法一 先选定数据透视表中的任一单元格 单击 数据 菜单下的 更新数据 命令 方法二 先选定数据透视表中的任一单元格 单击 数据透视表 工具栏上的 更新数据 按钮 第三章图表的绘制 图表是工作表数据的图形表示 用来生动 形象地表示数据 1 选中用于制表的数据区域 2 选择 插入 菜单的 图表 命令 或单击 常用 工具栏上的 图表向导 按钮 选择图表类型 3 在 图表数据源 对话框中 选择图表的数据系列 4 在 图表选项 对话框中 设置标题 坐标轴 网格线 图例 数据标志和数据表 5 在 图表位置 对话框中 选择是建立嵌入式图表还是工作表图表 第一节图表的创建 任务一图表的建立1 建立数据源 1 数据系列 在图表中表现的数据组 Y轴代表垂直方向 表示数值的大小 称数值轴 2 类 坐标的X轴代表水平方向 常用来表示时间或种类称分类轴 而时间或种类被统称作类 是数据系列的标题 2 实际建立一个图表1 使用图表向导建立图表 工具栏快捷按钮 插入 图表 快捷按钮 菜单方式 进入图表向导 我得返回再复习一次 Yeah 大功告成了 第二节图表的编辑 编辑图表 图表 菜单 图表 工具栏 1 图表对象图表对象是一个图表的各个组成部分 2 移动 复制 缩放和删除图表 移动 直接拖动图表复制 按住ctrl键拖动图表缩放 拖动周围的控点删除 选择后按delete键 3 改变图表类型 图表 菜单下的 图表类型 命令4 编辑图表中的数据 1 删除数据 2 向图表中添加数据序列步骤 在工作表中选择要添加的数据 单击工具栏上的 复制 按钮 单击图表区 再单击工具栏上的 粘贴 按钮 5 修改图表区表格设置图表区格式 选定图表 单击 格式 菜单下的 图表区 命令 6 图表中各对象的格式化双击或右键单击对象 第四章Excel在统计中的应用 Excel还提供了一组数据分析工具 称为 分析工具库 在建立复杂的统计分析时 使用现成的数据分析工具 可以节省很多时间 只需为每一个分析工具提供必要的数据和参数 该工具就会使用适宜的统计或数学函数 在输出表格中显示相应的结果 如果已经安装分析工具库 可以单击 工具 菜单中的 数据分析 命令 如果 数据分析 命令没有出现在 工具 菜单上 则必须加载 分析工具库 通过 工具 菜单中的 加载宏 命令 在 加载宏 对话框中选择 分析工具库 此分析工具用于生成对输入区域中的单变量数据的量化特征分析 提供数据趋中性和易变性等有关信息 即可以直接得到数据的求和 平均数 众数 中位数 标准差 最大值 最小值等统计指标 第一节描述统计工具 操作步骤 1 用鼠标点击工作表中待分析数据的任一单元格 2 选择 工具 菜单的 数据分析 子菜单 3 用鼠标双击数据分析工具中的 描述统计 选项 第一节描述统计工具 第一节描述统计工具 4 出现 描述统计 对话框 对话框内各选项的含义如下 输入区域 在此输入待分析数据区域的单元格范围 标志位于第一行 列 如果输入区域的第一行 列中包含标志项 变量名 则选中 标志位于第一列 行 复选框 如果输入区域没有标志项 则不用选择 第K个最大 小值 如果需要在输出表的某一行中包含每个区域的数据的第k个最大 小值 则需要在此输入 第二节直方图工具 用于在给定工作表中数据单元格区域和接收区间的情况下 计算数据的个别和累积频率 可以统计一组数据中某个数值元素的出现次数 即可以直接将原始的未分组数据按照需要编成一个组距数列 操作步骤 1 用鼠标点击表中待分析数据的任一单元格 2 选择 工具 菜单的 数据分析 子菜单 3 用鼠标双击数据分析工具中的 直方图 选项 第二节直方图工具 第二节直方图工具 4 出现 直方图 对话框 对话框内各选项的含义如下 输入区域 在此输入待分析数据区域的单元格范围 接收区域 即各组的组限 应当按升序排列 柏拉图 选中 可以在输出频数表中同时显示按频数降序排列数据 累积百分比 选中 可以在输出结果中添加一列累积百分比数值 并同时在直方图表中添加累积百分比折线 图表输出 选中 可以在输出表中同时生成一个直方图 注 此命令中本组上限计入本组 第三节排位与百分比工具 此分析工具可以产生一个数据列表 在其中罗列给定数据集中各个数值的大小次序排位和相应的百分比排位 主要用来分析一组数据中各数值间的相互位置关系 操作步骤 1 用鼠标点击表中待分析数据的任一单元格 2 选择 工具 菜单的 数据分析 子菜单 3 用鼠标双击数据分析工具中的 排位与百分比 选项 第三节排位与百分比工具 4 出现 排位与百分比 对话框 输出结果说明如下 输出的结果可分为四列 第一列 点 是数值原来的存放位置第二列是相应的数值第三列是数值的排序号第四列是数值的百分比排位 它的计算方法是 小于该数值的数值个数 数值总个数 1 第三节排位与百分比工具 第四节抽样工具 此分析工具可以由计算机系统自动从抽样框中抽取样本 抽样分析工具以数据源区域为总体 从而为其创建一个样本 当总体太大而不能进行处理或绘制时 可以选用具有代表性的样本 如果确认数据源区域中的数据是周期性的 还可以对一个周期中特定时间段中的数值进行采样 操作步骤 1 用鼠标点击表中待分析数据的任一单元格 2 选择 工具 菜单的 数据分析 子菜单 3 用鼠标双击数据分析工具中的 抽样 选项 第四节抽样工具 4 出现 抽样 对话框