1、1Excel2013高级教程数据统计与处理分析 Excel是 Office软件中的核心成员，是最优秀的 电子表格软件 之一，具有强大的 数据处理和数据分析 能力，是个人及办公事务中进行表格处理和数据分析的理想工具之一。 如何 利用 Excel的 函数、图表、高级分析工具、VBA程序 等功能进行数据分析是本次学习的重点。内容提要2 利用函数进行数据分析利用函数进行数据分析 P291 数据处理与分析基础数据处理与分析基础 P44 利用透视表（图）进行数据分析利用透视表（图）进行数据分析 P723 利用图表进行数据分析利用图表进行数据分析 P425 构建动态数据分析报表构建动态数据分析报表 P966
2、 宏与宏与 VBA在数据分析中的应用在数据分析中的应用 P1147 Excel 的数据分析工具简介的数据分析工具简介 P1461、 数据处理与分析基础学习目标1 认识 EXCEL的功能与界面2 学会利用 数据条件格式 进行数据处理3 学会利用 排序、筛选、分类汇总 功能进行数据分析EXCEL在数据分析中的应用 41、 数据处理与分析基础1.1 EXCEL的功能与界面1.2 数据的输入、编辑与运算（ 案例：销售产品基本信息表、销售记录汇总表）1.3 利用 数据条件格式 进行数据处理1.4 利用 排序、筛选、分类汇总 功能进行数据分析1.1 Excel的功能与界面1.1.1 EXCEL新增的主要功
3、能（ 1） 取消了菜单方式，采用了 面向结果 的用户界面，易于找到。（ 2）更强大的 数据管理能力 和安全性。（如更多的行和列 104857616384， 1600万种颜色）（ 3）更 强大的表功能 ，提供了全新的 数据引用 方式，称为 结构化引用 ，可以方便地构造动态数据报表。（ 4）其他方面： 提供了大量预定义主题和样式丰富的条件格式自动调整编辑栏函数记忆式输入1.1 Excel的功能与界面1.1.1 EXCEL新增的主要功能改进的筛选和排序功能（可按日期和颜色排序）图表外观更美观、更专业、布局和样式更多易于使用的数据透视表、数据透视图快速连接外部数据源1.1 Excel的功能与界面1.1
4、.2 EXCEL的用户界面整个界面由 功能区 和 工作表区 组成；功能区 有： OFFICE按钮 、 选项卡、组、快速访问工具栏、标题栏、状态栏。OFFICE按钮 ：相当于早期的 “文件 ”菜单；选项卡 ：面向 任务，包括功能控件。 “开始 ”有日常操作功能， “页面布局 ”与打印有关 （ 有书也称主菜单 ）组 ：每个组都与特定任务相关；（有书也称工具栏）快速访问工具栏 ：独立显示，默认有 “保存 ”、 “撤销 ”和 “恢复 “三按钮，可以自定义；标题栏 ：显示工作 薄 名称。状态栏 ：宏录制按钮、查看方式、缩放工具。1.2 数据的输入、编辑与运算目标 ：建立某企业产品销售数据统计报表（两个工
5、作表） 1.2.1建立 产品基本信息表任务 1：在 “产品营销数据处理与分析实例 ”工作薄建立形如下面的工作表，名称为 “产品基本信息 ”企业所有商品基本信息列表知识点 ：表格内容了解、新建工作薄、新建或改名工作表；格式化工作表。包括对齐方式、设置单元格格式（下划线、列宽、边框、填充颜色）产品编号 系列 产品名称 进货单价 销售单价 AP11001 观音饼 观音饼（花生） 6.5 12.8 AP11002 观音饼 观音饼（桂花） 6.5 12.8 1.2 数据的输入、编辑与运算1.2.2 建立 “销售记录汇总 ”表任务 2：建立表框架（见实例）；区分数据来源（原始的、引用的、计算的）；输入公式
6、（从信息表引用的、本表计算的）；数据复制（客户名称、发货日期、订单号、数量）。思考 ：什么是公式？其价值何在 ？公式是由 “=”号或 “+”号开 头 ，由常数、函数、 单 元格引用以及运算符 组 成的式子。其价 值 不 仅 是 计 算，重要的是构建 计 算模型。1.2 数据的输入、编辑与运算1.2.2 建立 “销售记录汇总 ”表思考 ：公式可分为几大类 ？可分三大类：普通公式： 如 =A1+B1 , =sum(a1:b5)数组公式：如 =average(B1:B10-A1:A10)（使用数组公式可减少存储空间，提高工作效率，详见 “综合实例 ”工作薄中相关内容）命名公式：如：Data名称表示
7、a1:a10=sum(data)1.2 数据的输入、编辑与运算1.2.2 建立 “销售记录汇总 ”表知识点 ： 跨表引用。根据 “产品编号 ”自动返回 产品基本信息表 “系列 ”等字段内容（ VLOOKUP函数）；如 E3中公式：=VLOOKUP($D2,产品基本信息表!$B$3:$F$38,2,FALSE)公式计算字段 “系列 ”、 “产品名称 ”、 “销售单价 ”、 “成本单价 ”要找的 值 查 找区域 第 2列 精确比较相关知识： Vlookup函数的使用格式：VLOOKUP（ 查找的值，查找区域，返回的列号，选项）功能 ：在表格或数组的首列查找值 ，并返回 表格或数组中其它列的值 。选
8、项 ：FALSE： 精确匹配，若找不到返回 #N/ATRUE或省略：近似匹配，若找不到返回一个小于要找参数的最大值。（ EXCEL中的函数帮助可能有误，请在编辑栏输入公式时查看选项含义） VLOOKUP的稍高 级应 用 见综 合 实例中相关 练习1.2 数据的输入、编辑与运算本任务中相关公式说明：销售额 =数量 * 销售单价总成本 =数量 * 成本单价毛利 = 销售额 -总成本销售数据可从 “销售原始 ”工作表复制而得。1.3 利用 数据条件格式 进行数据处理概述 ：条件格式功能，指的是当单元格中的数据满足某种条件时就设置某种格式，否则不予设置。利用此功能，可将单元格中的满足条件的数据进行特殊
9、标记，以便直观地查看。方法 ：选中数据区 开始 样式 条件格式任务 3：对 “销售记录汇总 ”表进行 如下操作：（ 1） 把 “销售单价 ”以 图标集 形式显示（ 2） 把 “销售额 ”以 数据条 形式显示（ 3）把 “毛利 ”低于平均值的数据设置为 黄色 。1.3 利用 数据条件格式 进行数据处理知识点：条件格式有以下几种：（ 1） 突出显示单元格规则 ：对选定区域内满足条件的单元格突出显示，默认的规则是用某种色彩填充单元格背景。条件包括：大于、小于、等于、文本包含等。（ 2） 项目选取规则 ：对选定区域内小于或大于某个阈值的单元格实施条件格式。条件包括：值最大的 10项、高于平均值等。（
10、3） 数据条 。以色彩条形图直观地表示单元格数据。 数据条长度代表数值的大小 。1.3 利用 数据条件格式 进行数据处理知识点：（ 4） 色阶 。用 颜色的深浅 表示数据的分布和变化，包括 双色阶 和 三色阶 。双色阶使用两种颜色的深浅程度比较某个区域的单元格，颜色的深浅表示值的高低。如在绿色和红色的双色刻度中，可指定越高越绿，越低越红。三色阶用 三种颜色的深浅表示值的高、中、低 。（ 5） 图标集 。按阈值把数据分成 3-5个类别 ，每个图标代表一个值的范围， 根据数据的大小比例设置图标的形状或颜色 。1.4 利用排序、筛选、分类汇总功能进行数据分析1.4.1 排序概述： 排序是对数据进行重
11、新组织安排的一种方式 ，排序有助于直观地显示、组织和查找所需数据。任务 4： 对销售记录汇总表操作（ 1） 为查看各 “系列 ”产品的销售情况，对 “系列 ” 排序（升序或降序）（ 2）查看不同 “发货日期 ”各 “系列 ”产品的销售情况按 “发货日期 ”和 “系列 ”两个字段排序1.4 利用排序、筛选、分类汇总功能进行数据分析知识点 ：按一列排序：光标放于某列中，单击 “升序 ”/“降序 ”按钮，可升序 /降序排序；按多字段排序：光标放于表中任意单元格，单击 “排序 ”按钮，可按多字段排序。注意 ：排序内容是否包含标题栏的选定；选择某列排序时的 “排序提醒 ”；当有复杂表头时的选择区域再排序
12、；自定义序列排序（如按职务高低排序，见 “综合实例 ”工作薄）；1.4 利用排序、筛选、分类汇总功能进行数据分析知识点 ：注意 ：EXCEL对排序字段 不再局限于 3个；不论升降序，空行总在排 在最后；EXCEL可以按单元格颜色排序。思考 ：排序之后如何回到排序前的状态。（必要时引入辅助字段） 姓名 性别 年龄 工资 辅助张三 男 25 3456 1李四 男 23 3215 2王五 女 22 890 31.4 利用排序、筛选、分类汇总功能进行数据分析1.4.2 自动筛选概述 ：筛选就是只把满足条件的数据行显示出来，而把不关注的数据隐藏。筛选有自动筛选和高级筛选两种。自动筛选易于使用，高级筛选条
13、件可以更复杂。任务 5： 对销售记录汇总表操作（ 1） 查看特定系列产品（如 “观音酥 ”）的销售情况 （ 自动）筛选（ 2） 查看特定用户（如 “好又多 ”）、特定系列产品（如 “旅游产品 ” ）的销售情况 组合自动筛选1.4 利用排序、筛选、分类汇总功能进行数据分析1.4.2 自动筛选知识点 ：欲选择某列中一个项止，先取消本列中的 “全选 ”复选框，然后再单选 ；注意状态栏提示（筛选出的个数）、列标右侧的图标显示；筛选是累加的，后一次筛选在前一次基础上进行；解除一列筛选：按 “全选 ”； 全部解除：按 “筛选 ”按钮。EXCEL在数据分析中的应用 221.4 利用排序、筛选、分类汇总功能进
14、行数据分析1.4.3 高级筛选概述 ：可实现多字段间的 “或 ”条件筛选，并能将结果复制到其他区域。任务 6：查看 一段时间 内 （如 2007年 8月上旬） 特定 用户（如 “好又多 ”） 的销售情况此任务可用高级筛选或自动筛选完成。EXCEL在数据分析中的应用 231.4 利用排序、筛选、分类汇总功能进行数据分析1.4.3 高级筛选知识点 ：1、 条件的写法（日期条件中用的是日期的数值）2、 取消高级筛选单击 “清除 ”思考 ：要筛选出销售数量不低于 100， 或者毛利不小于 2000元的数据怎么做。EXCEL在数据分析中的应用 24数量 毛利=100=2000发货日期 发货 日期 客户名
15、称 =39295 =39304 好又多1.4 利用排序、筛选、分类汇总功能进行数据分析1.4.4 分类汇总概述 ： 分类汇总能够对工作表数据按不同的类别进行汇总，并通过分级显示方式展现数据。任务 7： 对销售记录汇总表操作按 “系列 ”统计 销售额、总成本、毛利 等数据分类之和知识点 ：1、 先按分类字段 “系列 ”排序2、 数据 |分级显示 |分类汇总 (分类字段、汇总方式、汇总项 )3、 删除分类汇总：分类汇总 |全部删除4、 分级显示的使用与取消EXCEL在数据分析中的应用 251.4 利用排序、筛选、分类汇总功能进行数据分析任务 8统计本期 哪位客户 购货 数量 最多知识点 ：1、 先
16、按 “客户名称 ”字段排序2、 数据 |分级显示 |分类汇总3、 对分类汇总后的结果进行排序（按 数量 排序）EXCEL在数据分析中的应用 261.4 利用排序、筛选、分类汇总功能进行数据分析1.4.5 高级分类汇总概述 ：高级分类汇总就是按 同一类别 进行 不同字段 或不同汇总方式的汇总。任务 9： 对销售记录汇总表操作按 “客户名称 ”统计汇总 销售额总和、总成本总和、毛利平均值知识点 ：1、 先按 分类字段 “客户名称 ”排序2、 数据 |分级显示 |分类汇总 |选汇总项目和方式3、 数据 |分级显示 |分类汇总 |选 另外的 汇总项目和方式注意：不选择 “替换当前分类汇总 ”EXCEL
17、在数据分析中的应用 271.4 利用排序、筛选、分类汇总功能进行数据分析1.4.6 嵌套分类汇总概述 ： 嵌套分类汇总就是在一种分类汇总基础上再进行不同类别的分类汇总。任务 10： 对销售记录汇总表操作按 “客户名称 ”统计汇总 销售额总和、总成本总和、毛利总和，以及某客户不同 “系列 ”的销售数量之和。知识点 ：1、 先按 多个分类字段 排序（ 客户名称 +系列 ）2、 数据 |分级显示 |分类汇总 |选 分类字段 “客户名称 ”， 汇总方式求和，汇总项为 销售额、总成本、毛利。3、 数据 |分级显示 |分类汇总 |选 分类字段 “系列 ”，选汇总方式求和，汇总项目为数量。注意：不选择 “替换当前分类汇总 ”EXCEL在数据分析中的应用 282 利用 函数 进行数据分析学习目标1 掌握函数的输入技巧和获取帮助的手段2 掌握 SUMIF、 SUMIFS、 SUMPRODUCT等 函数的应用EXCEL在数据分析中的应用 292 利用 函数 进行数据分析2.1 函数概述2.2 SUMIF函数的应用2.3 SUMIFS函数的应用2.4 使用 SUMPRODUCT函数分析数据EXCEL在数据分析中的应用 30
