EXCEL与数据分析ppt课件.ppt

授课教师 马银戌 EXCEL与数据分析 2 课程教学内容 34课时 第一章EXCEL数据处理入门 2课时 第二章定性数据的基本特征分析 6课时 第三章定量数据的基本特征分析 4课时 第四章数据间的相关性分析 6课时 第五章数据间的差异性分析 4课时 第六章数据的变动趋势分析 6课时 第七章EXCEL与数据综合分析 6课时 3 第一节EXCEL概述 第二节数据的录入与编辑 第三节数据的类型与预处理 第一章EXCEL数据处理入门 4 第一节EXCEL概述 一 EXCEL的基本功能二 EXCEL的基本概念三 EXCEL数据分析工具的加载 5 一 EXCEL的基本功能 MicrosoftExcel是由微软公司开发的一种电子表格程序 是微软公司的办公套装软件Microsoftoffice的重要组成部分 Excel功能强大 使用方便 为日常生活中处理各式各样的表格提供了良好的工具 它可完成表格数据的输入 加工整理 分类汇总 简单计算等多项工作 可生成精美直观的数据清单 表格 图表 Excel中大量的公式函数可以应用选择 使用Excel可以执行繁重而复杂的计算 可以分析信息并管理电子表格或网页中的数据信息列表 带给使用者方便 Excel的数据分析工具 还可以进行各种数据的高级处理 统计分析和辅助决策操作 因而可以广泛地应用于统计 管理 财经 金融等众多领域 6 二 EXCEL的基本概念 Excel中的基本概念有 工作簿 工作表 单元格及单元格地址 工作簿 工作簿名 一张工作表及其名称 单元格 行号 列号 7 工作簿是工作表 图表及宏表的集合 它以文件的形式存放在计算机的外存贮器中 新创建的工作簿 Excel将自动给其命名 如Book1 Book2 用户可以重新赋予工作簿有意义的名字 如 员工档案资料 工作表是用于编辑 显示和分析一组数据的表格 它由排成行和列的单元格组成 每张工作表由65536行和256列单元格组成 工作表是包含在工作簿中的 一个工作簿最多可包含255张工作表 在新建工作簿时 Excel默认3张工作表 并自动给工作表命名为sheet1 sheet2 sheet3 用户可根据需要添加工作表 可对每张工作表重新命名 如 员工工资 等 8 单元格是构成工作表的基本元素 用于输入 显示和计算数据 一个单元格内只能存放一个数据 单元格地址用来标识一个单元格的坐标 它由列号行号组合表示 其中 行号由1 2 3 表示 列号由A B C 表示 Excel对单元格地址的引用有四种方式 相对地址 形式为 行号列号 如 A5 绝对地址 形式为 行号 列号 如 A 5 混合地址 是相对地址和绝对地址的混合 如 A 5 三维地址 是在单元格地址前面加上工作表的名称和感叹号 如 Sheet3 A5 表示Sheet3工作表中的A5单元格 9 三 EXCEL数据分析工具的加载 Excel中提供了一组数据分析工具 称为 分析工具库 使用这些工具在建立复杂统计或工程分析时可节省操作步骤和时间 应用时 只需为每一个分析工具提供必要的数据和参数 该工具就会使用适当的统计或工程宏函数 在输出表格中显示相应的结果 其中有些工具在生成输出表格的同时还能生成图表 用户通过这些分析工具可以很轻松地分析数据 但需要注意的是 分析工具库 在安装MicrosoftOfficeExcel后 需要通过在Excel中加载宏之后才能使用 加载 分析工具库 有两种方法 10 方法一 1 打开Excel 单击 工具 在下拉菜单中单击 加载宏 打开如下的对话框 2 勾选 可用加载宏 列表框中的 分析工具库 然后单击 确定 系统将会引导用户进行安装 3 安装完毕后 数据分析 会自动出现在Excel的 工具 菜单中 11 方法二 1 在Windows中单击 开始 搜索 在弹出的对话框中单击 所有文件和文件夹 然后在 全部或部分文件名 下面的框内输入 ANALYS32 XLL 单击 搜索 搜索程序就会在硬盘中搜索该文件 2 双击搜索到的 ANALYS32 XLL 文件 在打开的对话框中单击 启用宏 系统就会将该文件加载宏安装到Excel中 3 安装完毕后 数据分析 会自动出现在Excel的 工具 菜单中 12 第二节数据的录入与编辑 一 工作簿和工作表的基本操作二 录入数据三 编辑数据 13 一 工作簿和工作表的基本操作 新建工作簿有三种途径 启动Excel自动创建 工作簿自动命名为Book1 或Book2 Book3 用户可根据需要对工作簿重命名 每个工作簿默认包含3张工作表 单击工具栏中新建按钮 自动出现一新工作簿 其命名方法同上 根据模板新建工作簿 单击 文件 菜单 选择 新建 窗口右侧会出现一个对话框 允许用户选择 模板 然后Excel再根据模板来创建新工作簿 14 保存工作簿新建的工作簿呈现打开的可编辑状态 如果不保存起来则所做的工作就无效 要养成随时保存的良好习惯 保存工作簿可以分为保存新建工作簿 另存工作簿和自动保存工作簿3种情况 保存新建工作簿 新建的工作簿在第一次保存时 应该为其指定保存位置 并输入文件名 另存工作簿 对打开的工作簿进行编辑后 可以直接保存 也可以将其另存 一是将工作簿以其他名字保存在相同路径下 二是以相同名字保存在其他路径下 三是以其他名字保存在其他路径下 自动保存工作簿 单击 工具 菜单 点击 选项 在选项对话框中选中 保存自动恢复信息 复选框 在右侧的数值框中输入进行自动保存的时间间隔 如10分钟 单击 确定 即可 也可在工作过程中随时点击保存按钮手动保存文件 15 工作表的基本操作每个工作簿默认包含3张工作表 用户可根据需要添加或删除工作表 可移动工作表的位置 可重新命名工作表 添加工作表 单击 插入 菜单 点击 工作表 即可插入一张新工作表 一个工作簿最多可容纳255张工作表 删除工作表 打开要删除的工作表 单击 编辑 菜单 点击 删除工作表 即可 移动工作表 单击要移动的工作表 拖到相应的位置即可 重新命名工作表 右击要重命名的工作表名称 在复选框中点击 重命名 然后输入新的名称即可 16 二 录入数据 Excel中广义的数据包括数字 文本 符号 字母等各种形式 录入各种形式数据的步骤相同 单击要输入数据的单元格 使其成为当前单元格 在此单元格中输入数据 按回车键结束 注意 如果要将数字作为文本型数据保存 必须在前面加单撇号 输入日期和时间型数据 一般用斜线 和连字符 用作日期分隔符 冒号 用作时间分隔符 17 快速填充数据 Excel提供了数据填充功能 用以自动生成有规律的数据 如相同数据 等比 等差数列等 可提高输入数据的效率 在一行或一列中产生相同数据 向某一单元格输入第一个数据 然后单击该单元格右下角 此时鼠标变为 号 按住鼠标左键向下 或左右 拖动即可 在一行或一列中产生等差或等比数列 向某一单元格输入第一个数据 单击 编辑 菜单 单击 填充 子菜单 选择 序列 菜单项 出现如下对话框 18 在对话框中 根据需要确定如下内容 数列在工作表上是以行还是列方式生成 数列的类型 日期型数据的时间单位 等差或对比数列的步长值及终止值 单击 确定 按钮 即可生成相应数列 对于步长值为1的等差数列 也可按住Ctrl键 同时单击第一个单元格右下角 向下 或左右 拖动即可 19 三 编辑数据 数据移动 复制和粘贴对于普通数据的移动 复制和粘贴操作 可通过三种方法实现 1 选中要操作的单元格 单击 编辑 菜单 点击 复制 或 剪切 单击目标单元格 单击 编辑 菜单 点击 粘贴 2 选中要操作的单元格 点击鼠标右键 点击 复制 或 剪切 右击目标单元格 点击 粘贴 3 使用快捷键Ctrl X 剪切 Ctrl C 复制 和Ctrl V 粘贴 完成 20 使用 选择性粘贴 完成数据复制和粘贴选中要操作的单元格 单击 编辑 菜单 或单击右键 点击 复制 注意不能使用 剪切 单击目标单元格 单击 编辑 菜单 或右击目标单元格 点击 选择性粘贴 出现如下复选框 可根据需要在复选框中 粘贴 子菜单下选择有关项目 21 选择性粘贴 具有特殊功能 常用的有 以 全部 方式粘贴 将要复制的文件 按照原样粘贴到相应的位置 以 公式 方式粘贴 将要复制的文件 按照公式方式粘贴到相应的位置 以 数值 方式粘贴 将要复制的文件 从中去除格式 公式 仅提取数据内容 粘贴到相应的位置 以 格式 方式粘贴 将要复制的文件 从中去除数据 公式 仅提取格式内容 粘贴到相应的位置 22 使用 选择性粘贴 完成数据计算 选择性粘贴 不仅可以进行数据的复制 还可进行数据计算 如果要对表格中的特定数据进行加减乘除计算 可在复选框中 运算 子菜单下选择有关项目 23 案例 一次性为所有员工增加600元节日奖金 1 在任意单元格中输入600 并按Ctrl C快捷键 2 选中所有员工奖金单元格 3 选中 选择性粘贴 选项 4 在 运算 子菜单下选中 加 24 使用 选择性粘贴 完成行列转置利用 选择性粘贴 还可以进行数据的行列转置 所谓行列转置 指的是将选择复制的行或列 在粘贴复制时转置为列或行 只需在 选择性粘贴 复选框中选中 转置 项即可实现 25 数据查找和替换 一般数据的查找和替换 单击 编辑 菜单 点击 查找 输入要查找的文本或数字 单击 查找下一个 单击 替换 下拉子菜单 在 替换为 后面的空白框中输入要替换的内容 按照特定的格式条件查找指定数据如果事先对某些数据设置了特定格式 要查找这些数据 单击 编辑 菜单 点击 查找 单击 选项 单击 格式 单击 格式 然后从各种格式的复选框中寻找要查找的格式 单击 确定 单击 查找下一个 案例 在员工初始档案中 有几个人的学历掌握不准 因此录入时用了红色字体 经核对后 需进行调整 26 第三节数据的类型和预处理 一 数据的类型划分二 数据的预处理 27 一 数据的类型划分 数据 定性数据定量数据 定性数据 不能用数字表示 可用文字 符号等表示 又可分为分类数据和顺序数据 分类数据是只能划分类别的非数值型数据 顺序数据是可以划分有序类别的非数值型数据 定量数据 数值型数据 用数字表示 不同类型的数据加工整理和分析的方法不同 28 二 数据的预处理 数据的预处理是进行数据分析的前提步骤 包括数据的审核 筛选 排序 分类 分组 简单汇总以及所需新指标数据的计算等 一 数据的审核和筛选 数据的审核主要从完整性和准确性两个方面进行 完整性审核主要是检查数据是否有遗漏 各种相关项目如行列标题 编码 序号 计量单位等是否齐全 准确性审核主要是检查数据是否有错误 计算是否正确等 具体又包括逻辑检查和计算检查 逻辑检查主要是针对定性数据进行 计算检查主要是针对定量数据进行 29 逻辑检查主要是从定性角度审核数据是否符合逻辑 内容是否合理 各项目或数字之间有无相互矛盾的现象等 如工资总额等于基本工资加奖金 而某人的奖金高于其工资总额 显然属于逻辑性错误 应纠正 计算检查是检查各项数据在录入 汇总 计算结果和计算方法等方面是否有错 数据的筛选数据筛选包括两方面内容 一是将某些不符合要求的数据或有明显错误的数据予以剔除 二是将符合某种特定条件的数据筛选出来 对不符合条件的数据予以剔除 利用Excel进行筛选 包括 自动筛选 和 高级筛选 两种形式 30 自动筛选的使用 自动筛选 功能只能对单一条件进行筛选 是Excel最基本的操作 操作步骤为 打开Excel文件 选中文件数据区域任一单元格 单击 数据 下拉菜单 点击 筛选 自动筛选 然后在每列第一行的字段名下显出一个小三角 单击小三角 在其列表框中选中所要筛选的条件内容即可 若有多个条件需要筛选 则需多次从相应字段名小三角下的列表框中进行筛选 案例 从企业员工基本资料中筛选出性别为 男 学历为 硕士 的员工 31 高级筛选的使用 高级筛选 功能可以一次性对多个条件进行筛选 在数据较大较复杂时使用此功能较为快捷 操作步骤为 打开Excel文件 在空白位置输入筛选条件 单击 数据 下拉菜单 点击 筛选 打开 高级筛选 对话框 设置高级筛选条件以及筛选结果显示的位置 即可显示出满足筛选条件的数据 案例 从企业员工基本资料中筛选出性别为 男 年龄 40 学历为 硕士 的员工 32 二 数据的排序和分类汇总 一般来说 录入数据清单的数据是无序的 不能反映现象是本质与规律性 为了方便分析 要将其进行排序 分组 以使数据按要求排列 同时把性质相同的数据归为一组 让不同组数据之间的差异性显示出来 数据的排序 基本步骤如下 打开Excel文件 选中文件数据区域任一单元格 单击 数据 下拉菜单 点击 排序 然后在 主要关键字 下的列表框中选择要排序数据的字段名 在右侧选择 升序 或 降序 如果要把相同的记录再排序 则可在 次要关键字 下的列表框中选择要排序数据的字段名 在右侧选择 升序 或 降序 33 案例 对所有员工按学历和工资总额排序 1 打开Excel文件 单击数据区域任意单元格 2 单击 数据 下拉菜单 点击 排序 出现如下对话框 3 在 主要关键字 下的列表框中选择 学历 在右侧选择 升序 4 在 次要关键字 下的列表框中选择 工资总额 在右侧选择 降序 5 单击 确定 完成排序 34 数据的分类汇总 基本步骤如下 1 在数据排序的基础上 单击文件数据区域任一单元格 2 单击 数据 下拉菜单 点击 分类汇总 出现分类汇总的对话框 3 在 分类字段 下的列表框中选择要分类的字段名 4 在 汇总方式 下的列表框中选择 求和 5 在 选定汇总项 下的列表框中选择要汇总数值的字段名 6 单击 确定 完成分类汇总 35 案例 对所有员工按学历进行工资总额的分类汇总 1 打开Excel文件 单击数据区域任意单元格 2 单击 数据 下拉菜单 点击 分类汇总 出现如下对话框 3 在 分类字段 下