Excel2013高级教程-数据处理及分析

Excel2013高级教程
数据统计与处理分析
Байду номын сангаас
1


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