Excel2010数据处理与分析.ppt

Excel2010数据处理与分析 微软认证讲师 卢景德 课程内容 Office2010的用户界面结构以工作成果为导向的用户界面 功能区由描述特定场景和对象的标签组成自定义功能区Office2010快速访问工具栏自定义快速访问工具栏 全新的Backstage视图 文件 选项卡程序的左上角保护文档检查问题管理版本文档属性 全新文件格式介绍 新的文件后缀 x 和 m 新的格式对于普通用户好处 减小文件尺寸限制包含宏的文档模块化的 包 直接保存为PDF 发送Email docx 规范Excel表格 清晰的文件名 文件名 数据信息的直接描述添加适当的版本号不要以 新建Excel文件 命名注意文件的保存格式 标题冻结了吗 信息量较大的表格 发送之前冻结窗格方法 视图 冻结窗格 多余的信息隐藏了吗 文件发送之前 将辅助信息隐藏隐藏行 列隐藏工作表 打印设置正确吗 表格发送之前进行正确的打印设置数据是否打印在一页视图 分页预览设置打印比例重复打印标题行页面布局 打印标题 格式是否美观 尽量不要使用颜色较重的边框与底色使用 套用表格式 美化表格开始 套用表格式 是否该使用合并单元格 合并单元格给公式的拖拽 排序等操作带来意想不到的麻烦作为数据源的表格尽量无合并单元格或空格 数据之间无空行或空列如果是为了美观 可以使用合并单元格 改变 一日一表 的习惯 一个月31天 在一个文件内建立31张工作表如果做的是 一日一表 合并起来将非常麻烦可以将所有数据都写在一个表中 作为数据源 然后再用其他工具来提取所需数据 正确的录入数据 正确的数字格式设置数字货币格式日期型数字格式文本型数字格式 以单引号开头输入文本 自定义数字格式单元格格式快捷键Ctrl 1 Excel数据处理技巧 表格的基本操作 选择区域在拖动鼠标的同时按住Ctrl键选择不连续区域按快捷键Ctrl Shift 8 或Ctrl 可以选中当前单元格周围连续的矩形区域按住Shift键可以选中两点之间区域Ctrl 将光标移动到当前区域最右列 其他方向箭头依此类推 则Ctrl Shift 向下箭头 选择当前单元格至当前区域最后一行的内容 表格的编辑和调整 序列填充填充柄 选中区域右下角的黑点拖拽填充柄可实现序列填充 如 January February March等一切和数字有关的数据都有序列功能反向拖动填充柄相当于删除自定义填充序列 创建链接表格 创建链接表格 插入 超链接隐藏边框底纹 视图 网格线隐藏工作表标签Excel选项 高级 显示工作表标签 将规则文本导入EXCEL 数据 分列如果文本文件中的数据以制表符 冒号 分号或其他字符分隔 请选择 分隔符号 如果文本文件中的所有项的长度相同 请选择 固定宽度 可以手动调整分列的位置 也可以跳过某列而不导入 定义数据验证 数据 有效性定义有效数据范围 数值 日期 序列设置输入信息设置出错警告设置自定义类型的有效数据 创建下拉列表 数据 有效性 设置 允许 列表数据源 选择下拉菜单数据源如果数据源在另外一个sheet则需要先给数据源命名 丰富的条件格式 开始 条件格式什么是条件格式将满足特定条件的数据以指定格式显示使用条件格式显示单元格的数据例 将表格中大于500的数字以红色显示目的 突出显示满足某个条件数据可以设置颜色 图标等 管理条件格式规则 根据单元格值判断判断单元格唯一性与重复使用公式 活动单元格不能直接参与判断时使用公式调整规则顺序 运用函数处理数据 在Excel中编辑公式 点击等号 开始编辑公式在公式中可以使用加 减 乘 除四则混合运算 括号 乘方 2表示2次方 等公式复制和引用关系相对引用和绝对引用在公式中按F4键可以在相对引用和绝对引用之间进行切换 追踪单元格引用 公式 公式审核追踪引用的单元格追踪从属单元格追踪单元格引用错误 常用数学函数 SUM Average加权平均的计算MAX MINRound RoundUp RoundDown四舍五入函数 逻辑类函数 用于计算过程中的逻辑判断条件函数按管理的条件决定做什么 或不做什么IF 条件判断 成立则做A 否则做B 逻辑 与 用于判断多条件共存时的逻辑关系AND 条件1 条件2 条件3 条件30 统计类函数 用于对数据区域进行统计与分析 计数函数COUNT计算某数据区含有数字的单元格数量COUNT 数值1 数值2 条件计数计算某数据区内满足某条件的单元格数量COUNTIF 范围 条件 Countifs 范围1 条件1 范围2 条件2 数学类函数 条件求和函数SUMIF用途 按指定条件 对若干单元格进行求和计算 Sumif 条件区范围 条件 求和范围 Sumifs 求和范围 条件范围1 条件1 条件范围2 条件2 高级查询函数的使用 VLOOKUP 函数在表格的最左列查找某个数值 并返回该值右边所对应的第N列的数值Vlookup 要查找的值 范围 要显示的内容所在列数 查找类型 HLOOKUP 函数在表格的首行查找指定的数值 并由此返回该值下方所对应的第N行的数值 文本函数 字符运算使用 符号连接字符Len计算字符串长度Left Mid Right截取字符串函数例 根据身份证号计算生日 日期函数的使用 数据统计与分析 分类汇总 方法先排序后汇总数据 分类汇总分页打印分类汇总 每组数据分页隐藏行列的复制操作定位选择对象的一种方法编辑 定位 可见单元格 对表格数据的筛选分析 数据筛选自动筛选数据按数值筛选按颜色筛选数据 筛选 对数据进行合并计算 数据 合并计算将结构类似 位于不同位置 可以是不同的工作表或工作簿 的表格合并到一个区域并计算统计信息创建合并计算表格要求具有相同的行列标签创建合并计算前将光标放在要显示结果的位置 数据分析工具 数据透视 报表的 魔法 难题 创建透视表之前的准备工作 利用排序法删除空行在制作数据透视表之前要删除数据表中的所有空行 采用排序法删除直接排序保持原有数据顺序不变利用筛选法删除空行自动筛选 41 使用数据透视表 插入 数据透视表数据透视表字段包括 报表筛选列标签行标签数值区域按不同字段分类数据透视表只需要将要查看的字段拖拽到相应的字段即可按不同关键字查看数据 使用数据透视表 查看汇总结果明细信息双击汇总值即可查看明细信息 即来源 明细数据会显示在自动插入的新工作表中日期字段的处理可以按照日期进行筛选选中日期字段 选项 组合选择 在数据透视表中进行计算 改变字段汇总方式自定义数据透视表数据显示方式 差异 百分比 按某一字段汇总 使用计算字段和计算项字段之间的运算称为计算字段字段中的值之间的运算称为计算项 创建动态数据透视表 使用列表功能创建动态数据透视表Home formatasTableInsert PivotTable刷新数据透视表手动刷新数据透视表打开文件时自动刷新 数据透视图表 在现有数据透视表的基础上创建数据透视图直接为工作表创建数据透视图调整与修改数据透视图数据透视图的调整修改数据透视图类型 数据