《Excel2013高级教程-数据处理及分析上课讲义》由会员分享，可在线阅读，更多相关《Excel2013高级教程-数据处理及分析上课讲义（191页珍藏版）》请在金锄头文库上搜索。
1、1 Excel2013高级教程数据统计与处理分析 Excel是Office软件中的核心成员 是最优秀的电子表格软件之一 具有强大的数据处理和数据分析能力 是个人及办公事务中进行表格处理和数据分析的理想工具之一 如何利用Excel的函数 图表 高级分析工具 VBA程序等功能进行数据分析是本次学习的重点 1 数据处理与分析基础 学习目标1认识Excel的功能与界面2学会利用数据条件格式进行数据处理3学会利用排序 筛选 分类汇总功能进行数据分析 Excel在数据分析中的应用 4 1 数据处理与分析基础 1 1Excel的功能与界面1 2数据的输入 编辑与运算 案例 销售产品基本信息表 销售记录汇总表
2、 1 3利用数据条件格式进行数据处理1 4利用排序 筛选 分类汇总功能进行数据分析 1 1Excel的功能与界面 1 1 1Excel新增的主要功能 1 取消了菜单方式 采用了面向结果的用户界面 易于找到 2 更强大的数据管理能力和安全性 如更多的行和列1048576 16384 1600万种颜色 3 更强大的表功能 提供了全新的数据引用方式 称为结构化引用 可以方便地构造动态数据报表 4 其他方面 提供了大量预定义主题和样式丰富的条件格式自动调整编辑栏函数记忆式输入 1 1Excel的功能与界面 1 1 1Excel新增的主要功能改进的筛选和排序功能 可按日期和颜色排序 图表外观更美观 更专
3、业 布局和样式更多易于使用的数据透视表 数据透视图快速连接外部数据源 1 1Excel的功能与界面 1 1 2Excel的用户界面整个界面由功能区和工作表区组成 功能区有 Office按钮 选项卡 组 快速访问工具栏 标题栏 状态栏 Office按钮 相当于早期的 文件 菜单 选项卡 面向任务 包括功能控件 开始 有日常操作功能 页面布局 与打印有关 有书也称主菜单 组 每个组都与特定任务相关 有书也称工具栏 快速访问工具栏 独立显示 默认有 保存 撤销 和 恢复 三按钮 可以自定义 标题栏 显示工作薄名称 状态栏 宏录制按钮 查看方式 缩放工具 1 2数据的输入 编辑与运算 目标 建立某企业
4、产品销售数据统计报表 两个工作表 1 2 1建立产品基本信息表任务1 在 产品营销数据处理与分析实例 工作薄建立形如下面的工作表 名称为 产品基本信息 企业所有商品基本信息列表知识点 表格内容了解 新建工作薄 新建或改名工作表 格式化工作表 包括对齐方式 设置单元格格式 下划线 列宽 边框 填充颜色 1 2数据的输入 编辑与运算 1 2 2建立 销售记录汇总 表任务2 建立表框架 见实例 区分数据来源 原始的 引用的 计算的 输入公式 从信息表引用的 本表计算的 数据复制 客户名称 发货日期 订单号 数量 思考 什么是公式 其价值何在 公式是由 号或 号开头 由常数 函数 单元格引用以及运算符
5、组成的式子 其价值不仅是计算 重要的是构建计算模型 1 2数据的输入 编辑与运算 1 2 2建立 销售记录汇总 表思考 公式可分为几大类 可分三大类 普通公式 如 A1 B1 sum a1 b5 数组公式 如 average B1 B10 A1 A10 使用数组公式可减少存储空间 提高工作效率 详见 综合实例 工作薄中相关内容 命名公式 如 Data名称表示a1 a10 sum data 1 2数据的输入 编辑与运算 1 2 2建立 销售记录汇总 表知识点 跨表引用 根据 产品编号 自动返回产品基本信息表 系列 等字段内容 vlookup函数 如E3中公式 VLOOKUP D2 产品基本信息表
6、 B 3 F 38 2 FALSE 公式计算字段 系列 产品名称 销售单价 成本单价 要找的值 查找区域 第2列 精确比较 相关知识 Vlookup函数的使用 格式 Vlookup 查找的值 查找区域 返回的列号 选项 功能 在表格或数组的首列查找值 并返回表格或数组中其它列的值 选项 FALSE 精确匹配 若找不到返回 N ATRUE或省略 近似匹配 若找不到返回一个小于要找参数的最大值 Excel中的函数帮助可能有误 请在编辑栏输入公式时查看选项含义 VLOOKUP的稍高级应用见综合实例中相关练习 1 2数据的输入 编辑与运算 本任务中相关公式说明 销售额 数量 销售单价总成本 数量 成本
7、单价毛利 销售额 总成本销售数据可从 销售原始 工作表复制而得 1 3利用数据条件格式进行数据处理 概述 条件格式功能 指的是当单元格中的数据满足某种条件时就设置某种格式 否则不予设置 利用此功能 可将单元格中的满足条件的数据进行特殊标记 以便直观地查看 方法 选中数据区 开始 样式 条件格式任务3 对 销售记录汇总 表进行如下操作 1 把 销售单价 以图标集形式显示 2 把 销售额 以数据条形式显示 3 把 毛利 低于平均值的数据设置为黄色 1 3利用数据条件格式进行数据处理 知识点 条件格式有以下几种 1 突出显示单元格规则 对选定区域内满足条件的单元格突出显示 默认的规则是用某种色彩填充
8、单元格背景 条件包括 大于 小于 等于 文本包含等 2 项目选取规则 对选定区域内小于或大于某个阈值的单元格实施条件格式 条件包括 值最大的10项 高于平均值等 3 数据条 以色彩条形图直观地表示单元格数据 数据条长度代表数值的大小 1 3利用数据条件格式进行数据处理 知识点 4 色阶 用颜色的深浅表示数据的分布和变化 包括双色阶和三色阶 双色阶使用两种颜色的深浅程度比较某个区域的单元格 颜色的深浅表示值的高低 如在绿色和红色的双色刻度中 可指定越高越绿 越低越红 三色阶用三种颜色的深浅表示值的高 中 低 5 图标集 按阈值把数据分成3 5个类别 每个图标代表一个值的范围 根据数据的大小比例设
9、置图标的形状或颜色 1 4利用排序 筛选 分类汇总功能进行数据分析 1 4 1排序概述 排序是对数据进行重新组织安排的一种方式 排序有助于直观地显示 组织和查找所需数据 任务4 对销售记录汇总表操作 1 为查看各 系列 产品的销售情况 对 系列 排序 升序或降序 2 查看不同 发货日期 各 系列 产品的销售情况 按 发货日期 和 系列 两个字段排序 1 4利用排序 筛选 分类汇总功能进行数据分析 知识点 按一列排序 光标放于某列中 单击 升序 降序 按钮 可升序 降序排序 按多字段排序 光标放于表中任意单元格 单击 排序 按钮 可按多字段排序 注意 排序内容是否包含标题栏的选定 选择某列排序时
10、的 排序提醒 当有复杂表头时的选择区域再排序 自定义序列排序 如按职务高低排序 见 综合实例 工作薄 1 4利用排序 筛选 分类汇总功能进行数据分析 知识点 注意 Excel对排序字段不再局限于3个 不论升降序 空行总在排在最后 Excel可以按单元格颜色排序 思考 排序之后如何回到排序前的状态 必要时引入辅助字段 1 4利用排序 筛选 分类汇总功能进行数据分析 1 4 2自动筛选概述 筛选就是只把满足条件的数据行显示出来 而把不关注的数据隐藏 筛选有自动筛选和高级筛选两种 自动筛选易于使用 高级筛选条件可以更复杂 任务5 对销售记录汇总表操作 1 查看特定系列产品 如 观音酥 的销售情况 自
11、动 筛选 2 查看特定用户 如 好又多 特定系列产品 如 旅游产品 的销售情况 组合自动筛选 1 4利用排序 筛选 分类汇总功能进行数据分析 1 4 2自动筛选知识点 欲选择某列中一个项止 先取消本列中的 全选 复选框 然后再单选 注意状态栏提示 筛选出的个数 列标右侧的图标显示 筛选是累加的 后一次筛选在前一次基础上进行 解除一列筛选 按 全选 全部解除 按 筛选 按钮 Excel在数据分析中的应用 22 1 4利用排序 筛选 分类汇总功能进行数据分析 1 4 3高级筛选概述 可实现多字段间的 或 条件筛选 并能将结果复制到其他区域 任务6 查看一段时间内 如2007年8月上旬 特定用户 如
12、 好又多 的销售情况此任务可用高级筛选或自动筛选完成 Excel在数据分析中的应用 23 1 4利用排序 筛选 分类汇总功能进行数据分析 1 4 3高级筛选知识点 1 条件的写法 日期条件中用的是日期的数值 2 取消高级筛选单击 清除 思考 要筛选出销售数量不低于100 或者毛利不小于2000元的数据怎么做 Excel在数据分析中的应用 24 数量毛利 100 2000 发货日期发货日期客户名称 39295 39304好又多 1 4利用排序 筛选 分类汇总功能进行数据分析 1 4 4分类汇总概述 分类汇总能够对工作表数据按不同的类别进行汇总 并通过分级显示方式展现数据 任务7 对销售记录汇总表
13、操作按 系列 统计销售额 总成本 毛利等数据分类之和知识点 1 先按分类字段 系列 排序2 数据 分级显示 分类汇总 分类字段 汇总方式 汇总项 3 删除分类汇总 分类汇总 全部删除4 分级显示的使用与取消 Excel在数据分析中的应用 25 1 4利用排序 筛选 分类汇总功能进行数据分析 任务8统计本期哪位客户购货数量最多知识点 1 先按 客户名称 字段排序2 数据 分级显示 分类汇总3 对分类汇总后的结果进行排序 按数量排序 Excel在数据分析中的应用 26 1 4利用排序 筛选 分类汇总功能进行数据分析 1 4 5高级分类汇总概述 高级分类汇总就是按同一类别进行不同字段或不同汇总方式的
14、汇总 任务9 对销售记录汇总表操作按 客户名称 统计汇总销售额总和 总成本总和 毛利平均值知识点 1 先按分类字段 客户名称 排序2 数据 分级显示 分类汇总 选汇总项目和方式3 数据 分级显示 分类汇总 选另外的汇总项目和方式注意 不选择 替换当前分类汇总 Excel在数据分析中的应用 27 1 4利用排序 筛选 分类汇总功能进行数据分析 1 4 6嵌套分类汇总概述 嵌套分类汇总就是在一种分类汇总基础上再进行不同类别的分类汇总 任务10 对销售记录汇总表操作按 客户名称 统计汇总销售额总和 总成本总和 毛利总和 以及某客户不同 系列 的销售数量之和 知识点 1 先按多个分类字段排序 客户名称
15、 系列 2 数据 分级显示 分类汇总 选分类字段 客户名称 汇总方式求和 汇总项为销售额 总成本 毛利 3 数据 分级显示 分类汇总 选分类字段 系列 选汇总方式求和 汇总项目为数量 注意 不选择 替换当前分类汇总 Excel在数据分析中的应用 28 2利用函数进行数据分析 学习目标1掌握函数的输入技巧和获取帮助的手段2掌握sumif sumifs sumproduct等函数的应用 Excel在数据分析中的应用 29 2利用函数进行数据分析 2 1函数概述2 2sumif函数的应用2 3sumifs函数的应用2 4使用sumproduct函数分析数据 Excel在数据分析中的应用 30 2利用
16、函数进行数据分析 2 1函数概述函数就是预定义的公式 它使用一些参数进行运算并产生返回结果 EXCEL中有350多个内置函数 分数学与三角函数 财务函数 统计函数 工程函数等11大类 用户还可使用VBA自定义函数 函数可分为有参数函数和无参数函数两大类 在公式和自定义宏中可以调用函数 函数可以嵌套调用 如 if average A2 A5 50 sum B2 B5 0 if a2 10 科技处 if a2 20 财务处 人事处 Excel在数据分析中的应用 31 条件 取真时 取假时 2利用函数进行数据分析 2 2sumif函数的应用任务1 新建一个工作表 命名为 函数应用 统计各客户购买数量 购买额等数据之和 知识点 sumif函数 1 输入要分析的数据栏目 从销售记录汇总表挑选字段粘贴而来 2 输入客户名称的技巧 将分类汇总结果复制到别处 3 使用sumif函数计算各个字段 4 使用排名函数RANK按毛利对客户排名 Excel在数据分析中的应用 32 相关知识 Sumif函数 SUMIF 条件判断区域 条件 求和区域 根据指定条件对若干单元格求和 条件可以是数字 表达式或文本 如
