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

1 Excel2013高级教程数据统计与处理分析 Excel是Office软件中的核心成员 是最优秀的电子表格软件之一 具有强大的数据处理和数据分析能力 是个人及办公事务中进行表格处理和数据分析的理想工具之一 如何利用Excel的函数 图表 高级分析工具 VBA程序等功能进行数据分析是本次学习的重点 内容提要 2利用函数进行数据分析P29 1数据处理与分析基础P4 4利用透视表 图 进行数据分析P72 3利用图表进行数据分析P42 5构建动态数据分析报表P96 6宏与VBA在数据分析中的应用P114 7Excel的数据分析工具简介P146 1 数据处理与分析基础 学习目标1认识Excel的功能与界面2学会利用数据条件格式进行数据处理3学会利用排序 筛选 分类汇总功能进行数据分析 1 数据处理与分析基础 1 1Excel的功能与界面1 2数据的输入 编辑与运算 案例 销售产品基本信息表 销售记录汇总表 1 3利用数据条件格式进行数据处理1 4利用排序 筛选 分类汇总功能进行数据分析 1 1Excel的功能与界面 1 1 1Excel新增的主要功能 1 取消了菜单方式 采用了面向结果的用户界面 易于找到 2 更强大的数据管理能力和安全性 如更多的行和列1048576 16384 1600万种颜色 3 更强大的表功能 提供了全新的数据引用方式 称为结构化引用 可以方便地构造动态数据报表 4 其他方面 提供了大量预定义主题和样式丰富的条件格式自动调整编辑栏函数记忆式输入 1 1Excel的功能与界面 1 1 1Excel新增的主要功能改进的筛选和排序功能 可按日期和颜色排序 图表外观更美观 更专业 布局和样式更多易于使用的数据透视表 数据透视图快速连接外部数据源 1 1Excel的功能与界面 1 1 2Excel的用户界面整个界面由功能区和工作表区组成 功能区有 Office按钮 选项卡 组 快速访问工具栏 标题栏 状态栏 Office按钮 相当于早期的 文件 菜单 选项卡 面向任务 包括功能控件 开始 有日常操作功能 页面布局 与打印有关 有书也称主菜单 组 每个组都与特定任务相关 有书也称工具栏 快速访问工具栏 独立显示 默认有 保存 撤销 和 恢复 三按钮 可以自定义 标题栏 显示工作薄名称 状态栏 宏录制按钮 查看方式 缩放工具 1 2数据的输入 编辑与运算 目标 建立某企业产品销售数据统计报表 两个工作表 1 2 1建立产品基本信息表任务1 在 产品营销数据处理与分析实例 工作薄建立形如下面的工作表 名称为 产品基本信息 企业所有商品基本信息列表知识点 表格内容了解 新建工作薄 新建或改名工作表 格式化工作表 包括对齐方式 设置单元格格式 下划线 列宽 边框 填充颜色 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 2数据的输入 编辑与运算 1 2 2建立 销售记录汇总 表知识点 跨表引用 根据 产品编号 自动返回产品基本信息表 系列 等字段内容 vlookup函数 如E3中公式 VLOOKUP D2 产品基本信息表 B 3 F 38 2 FALSE 公式计算字段 系列 产品名称 销售单价 成本单价 要找的值 查找区域 第2列 精确比较 相关知识 Vlookup函数的使用 格式 Vlookup 查找的值 查找区域 返回的列号 选项 功能 在表格或数组的首列查找值 并返回表格或数组中其它列的值 选项 FALSE 精确匹配 若找不到返回 N ATRUE或省略 近似匹配 若找不到返回一个小于要找参数的最大值 Excel中的函数帮助可能有误 请在编辑栏输入公式时查看选项含义 VLOOKUP的稍高级应用见综合实例中相关练习 1 2数据的输入 编辑与运算 本任务中相关公式说明 销售额 数量 销售单价总成本 数量 成本单价毛利 销售额 总成本销售数据可从 销售原始 工作表复制而得 1 3利用数据条件格式进行数据处理 概述 条件格式功能 指的是当单元格中的数据满足某种条件时就设置某种格式 否则不予设置 利用此功能 可将单元格中的满足条件的数据进行特殊标记 以便直观地查看 方法 选中数据区 开始 样式 条件格式任务3 对 销售记录汇总 表进行如下操作 1 把 销售单价 以图标集形式显示 2 把 销售额 以数据条形式显示 3 把 毛利 低于平均值的数据设置为黄色 1 3利用数据条件格式进行数据处理 知识点 条件格式有以下几种 1 突出显示单元格规则 对选定区域内满足条件的单元格突出显示 默认的规则是用某种色彩填充单元格背景 条件包括 大于 小于 等于 文本包含等 2 项目选取规则 对选定区域内小于或大于某个阈值的单元格实施条件格式 条件包括 值最大的10项 高于平均值等 3 数据条 以色彩条形图直观地表示单元格数据 数据条长度代表数值的大小 1 3利用数据条件格式进行数据处理 知识点 4 色阶 用颜色的深浅表示数据的分布和变化 包括双色阶和三色阶 双色阶使用两种颜色的深浅程度比较某个区域的单元格 颜色的深浅表示值的高低 如在绿色和红色的双色刻度中 可指定越高越绿 越低越红 三色阶用三种颜色的深浅表示值的高 中 低 5 图标集 按阈值把数据分成3 5个类别 每个图标代表一个值的范围 根据数据的大小比例设置图标的形状或颜色 1 4利用排序 筛选 分类汇总功能进行数据分析 1 4 1排序概述 排序是对数据进行重新组织安排的一种方式 排序有助于直观地显示 组织和查找所需数据 任务4 对销售记录汇总表操作 1 为查看各 系列 产品的销售情况 对 系列 排序 升序或降序 2 查看不同 发货日期 各 系列 产品的销售情况 按 发货日期 和 系列 两个字段排序 1 4利用排序 筛选 分类汇总功能进行数据分析 知识点 按一列排序 光标放于某列中 单击 升序 降序 按钮 可升序 降序排序 按多字段排序 光标放于表中任意单元格 单击 排序 按钮 可按多字段排序 注意 排序内容是否包含标题栏的选定 选择某列排序时的 排序提醒 当有复杂表头时的选择区域再排序 自定义序列排序 如按职务高低排序 见 综合实例 工作薄 1 4利用排序 筛选 分类汇总功能进行数据分析 知识点 注意 Excel对排序字段不再局限于3个 不论升降序 空行总在排在最后 Excel可以按单元格颜色排序 思考 排序之后如何回到排序前的状态 必要时引入辅助字段 1 4利用排序 筛选 分类汇总功能进行数据分析 1 4 2自动筛选概述 筛选就是只把满足条件的数据行显示出来 而把不关注的数据隐藏 筛选有自动筛选和高级筛选两种 自动筛选易于使用 高级筛选条件可以更复杂 任务5 对销售记录汇总表操作 1 查看特定系列产品 如 观音酥 的销售情况 自动 筛选 2 查看特定用户 如 好又多 特定系列产品 如 旅游产品 的销售情况 组合自动筛选 1 4利用排序 筛选 分类汇总功能进行数据分析 1 4 2自动筛选知识点 欲选择某列中一个项止 先取消本列中的 全选 复选框 然后再单选 注意状态栏提示 筛选出的个数 列标右侧的图标显示 筛选是累加的 后一次筛选在前一次基础上进行 解除一列筛选 按 全选 全部解除 按 筛选 按钮 1 4利用排序 筛选 分类汇总功能进行数据分析 1 4 3高级筛选概述 可实现多字段间的 或 条件筛选 并能将结果复制到其他区域 任务6 查看一段时间内 如2007年8月上旬 特定用户 如 好又多 的销售情况此任务可用高级筛选或自动筛选完成 1 4利用排序 筛选 分类汇总功能进行数据分析 1 4 3高级筛选知识点 1 条件的写法 日期条件中用的是日期的数值 2 取消高级筛选单击 清除 思考 要筛选出销售数量不低于100 或者毛利不小于2000元的数据怎么做 数量毛利 100 2000 发货日期发货日期客户名称 39295 39304好又多 1 4利用排序 筛选 分类汇总功能进行数据分析 1 4 4分类汇总概述 分类汇总能够对工作表数据按不同的类别进行汇总 并通过分级显示方式展现数据 任务7 对销售记录汇总表操作按 系列 统计销售额 总成本 毛利等数据分类之和知识点 1 先按分类字段 系列 排序2 数据 分级显示 分类汇总 分类字段 汇总方式 汇总项 3 删除分类汇总 分类汇总 全部删除4 分级显示的使用与取消 1 4利用排序 筛选 分类汇总功能进行数据分析 任务8统计本期哪位客户购货数量最多知识点 1 先按 客户名称 字段排序2 数据 分级显示 分类汇总3 对分类汇总后的结果进行排序 按数量排序 1 4利用排序 筛选 分类汇总功能进行数据分析 1 4 5高级分类汇总概述 高级分类汇总就是按同一类别进行不同字段或不同汇总方式的汇总 任务9 对销售记录汇总表操作按 客户名称 统计汇总销售额总和 总成本总和 毛利平均值知识点 1 先按分类字段 客户名称 排序2 数据 分级显示 分类汇总 选汇总项目和方式3 数据 分级显示 分类汇总 选另外的汇总项目和方式注意 不选择 替换当前分类汇总 1 4利用排序 筛选 分类汇总功能进行数据分析 1 4 6嵌套分类汇总概述 嵌套分类汇总就是在一种分类汇总基础上再进行不同类别的分类汇总 任务10 对销售记录汇总表操作按 客户名称 统计汇总销售额总和 总成本总和 毛利总和 以及某客户不同 系列 的销售数量之和 知识点 1 先按多个分类字段排序 客户名称 系列 2 数据 分级显示 分类汇总 选分类字段 客户名称 汇总方式求和 汇总项为销售额 总成本 毛利 3 数据 分级显示 分类汇总 选分类字段 系列 选汇总方式求和 汇总项目为数量 注意 不选择 替换当前分类汇总 2利用函数进行数据分析 学习目标1掌握函数的输入技巧和获取帮助的手段2掌握sumif sumifs sumproduct等函数的应用 2利用函数进行数据分析 2 1函数概述2 2sumif函数的应用2 3sumifs函数的应用2 4使用sumproduct函数分析数据 2利用函数进行数据分析 2 1函数概述函数就是预定义的公式 它使用一些参数进行运算并产生返回结果 EXCEL中有350多个内置函数 分数学与三角函数 财务函数 统计函数 工程函数等11大类 用户还可使用VBA自定义函数 函数可分为有参数函数和无参数函数两大类 在公式和自定义宏中可以调用函数 函数可以嵌套调用 如 if average A2 A5 50 sum B2 B5 0 if a2 10 科技处 if a2 20 财务处 人事处 条件 取真时 取假时 2利用函数进行数据分析 2 2sumif函数的应用任务1 新建一个工作表 命名为 函数应用 统计各客户购买数量 购买额等数据之和 知识点 sumif函数 1 输入要分析的数据栏目 从销售记录汇总表挑选字段粘贴而来 2 输入客户名称的技巧 将分类汇总结果复制到别处 3 使用sumif函数计算各个字段 4 使用排名函数RANK按毛利对客户排名 相关知识 Sumif函数 SUMIF 条件判断区域 条件 求和区域 根据指定条件对若干单元格求和 条件可以是数字 表达式或文本 如 北京 50 等 要使用引号 也可以是单元格引用 案例中C5处 数量 的公式 SUMIF 销售记录汇总 A 2 A 107 B5 销售记录汇总 G 2 G 107 复制到右侧单元格 并修改最后一项中的列号 依次为IKL列 sumif其他例子见 综合实例 工作薄 求和区域 条件区域 条件值 相关知识 RANK函数的使用 格式 RANK 要排位的数值 排位区域 选项 功能 返回一个数值在一组数值中的排位 选项 非0 升序排位0或省略 降序排位 2利用函数进行数据分析 2 3sumifs函数的应用任务2 用sumifs函数对工作表 函数应用 统计各客户购买数量 购买额等数据之和 知识点 sumifs函数 1 数量 的公式为 SUMIFS 销售记录汇总 G 2 G 107 销售记录汇总 A 2 A 107 B 19 求和区域 条件区域 条件值 相关知识 sumifs函数的使用 格式 sumifs 求和区域 条件范围1 条件1 条件范围2 功能 对某一区域内满足多重条件的单元格求和 最多127个条件 条件可以是 18 学生 B4等形式 SUMIFS详细使用见 综合实例 工作薄中的 sumifs用法举例 类似的 Excel也提供了Averageif Averageifs countif等函数 2利用函数进行数据分析 2 4使用sumproduct函数分析数据任务3 在 函数应用 工作表中 统计 观音饼 和 观音酥 两种产品的销售量和利润额 知识点 1 销售量计算公式 SUMPRODUCT 销售记录汇总 E 3 E 107 观音饼 销售记录汇总 E 3 E 107 观音酥 销售记录汇总 G 3 G 107 2 销售利润 毛利 SUMPRODUCT 销售记录汇总 E 3 E 107 观音饼 销售记录汇总 E 3 E 107 观音酥 销售记录汇总 L 3 L 107 2利用函数进行数据分析 任务4 在 函数应用 工作表中 统计各客户本期购买各系列产品的销售额 使用SUMPRODUCT函数 知识点 1 处公式为 SUMPRODUCT 销售记录汇总 A 2 A 107 B41 销售记录汇总 E 2 E 107 C 40 销售记录汇总 I 2 I 107 2 SUMPRODUCT详细使用见 综合实例 工作薄中的 sumproduct用法举例 相关知识 sumproduct函数的使用 格式 sumproduct 数组1 数组2 数组3 功能 可对几个数组间的对应元素相乘 并返回乘积之和 其基本应用是两个数组间对应元素相乘再求和 C3中公式 sumproduct a1 a3 b1 b3 结果为14 2利用函数进行数据分析 任务5 任务同上 但要求使用SUMIFS函数实现 知识点 1 处公式为 SUMIFS 销售记录汇总 I 2 I 107 销售记录汇总 A 2 A 107 B 57 销售记录汇总 E 2 E 107 C 56 2 SUMIFS用法 求和区域 条件区域1 条件值1 条件值2 条件区域2 2利用函数进行数据分析 任务6 在 函数应用 工作表中 统计某时段的销售额使用SUMPRODUCT函数 知识点 上旬公式为 SUMPRODUCT 销售记录汇总 C 3 C 410 39295 销售记录汇总 C 3 C 410 39304 销售记录汇总 N 3 N 410 中旬与下旬类似 只是数值不同 思考 什么情况下要使用SUMPRODUCT函数 3利用图表进行数据分析 学习目标1了解图表的类型及其组成部分2掌握图表的建立 编辑与美化方法3理解复杂图表的建立方法4了解动态图表的建立方法 3利用图表进行数据分析 3 1图表概述3 2图表的建立 编辑与美化3 3复杂图表的建立3 4动态图表的建立 3 1图表概述 图表是EXCEL中一个强有力的工具 利用图表可以使数据更加直观地显示出来 EXCEL有丰富的图表类型 其功能不逊于某些专业图表软件 了解图表有关术语和组成部分是正确使用图表的前提 1 有关术语 1 数据点 即数据标记 本质上是一个单元格中的数值图形表示 不同类型的图表数据点形状不同 柱形图中表现为一个柱形 折线图中为一个点 面积图中为一扇形区域 2 数据系列 图表中一组相关的数据点 它来自于数据表的某行或某列 每个数据系列有相同的颜色和图案 并且通过图例标识 一张图表有多个数据系列 但饼图只有一个 3 数据标签 是指为数据系列或数据点添加的标识 默认情况下图表没有数据标签 用户可以添加数据标签 并修改它的位置和大小 3 1图表概述 有关术语 4 网格线 用于查看和评估数据 有水平和垂直两种 5 轴 作为绘图区一侧边界的直线 有X轴也称分类轴 Y轴也称数值轴两种 6 刻度线与刻度标志 类似于尺子上的刻度 7 图例 用于说明每个数据系列中的数据点所采用的图形外表 8 标题 有图表标题 分类轴标题和数值轴标题 识别图表区域的技巧 单击图表上不同区域 观看 图表工具 布局 当前所选内容 3 1图表概述 2 最常见的图形介绍 1 柱形图 用于显示某段时间内数据的变化 或比较各数据项之间的差异 分类一般为日期或时间 数值在垂直方向组织 以便于强调相对于时间的变化 2 条形图 也用于显示各数据之间的比较 与柱形图不同的是 其分类在垂直方向 一般为日期或时间 而数值在水平方向 使观察者的注意力集中在数据值的比较上 而不在时间上 3 折线图 主要用于显示各数据随时间而变化的趋势情况 横坐标几乎总是表现为时间 3 饼图 用于显示组成数据系列的各数据项与数据项总和的比例 当只有一个数据系列 并且用于强调整体中的各个组成部分占整体的比例时十分有效 3 2图表的建立 任务1 对前面一章任务5中工作表中的结果 制作各系列产品销售额百分比分离型三维饼图 知识点 1 建立 总计 行 写入公式 SUM 范围 3 选择上下数据两行 插入 饼图 分离型三维饼图 4 输入图表标题 各系列产品销售额占比分析 5 添加百分比数据标签 图表工具 布局 其他数据标签选项 选择 类别名称 百分比 选择 数字 百分比2位小数 6 删除图例 3 2图表的建立 任务2 仍对前面的工作表中 制作分离型三维饼图 分析从各客户获取利润的百分比 要求 1 图表标题为 从各客户获取利润占比分析 2 图例在下方 思考 如何选择数据源 3 2图表的建立 归纳 建立图表的步骤 1 首先选择数据源 要选定图表数据所在的单元格 如希望数据的行 列标志也显示在图表中 则选定区还应包括行 列标题 2 应用预定义的图表布局和样式设置图表 方法 图表工具 布局 样式 3 创建图表工作表 选中图表 设计 位置 移动图表 可将嵌入式图表变成图表工作表 并可移回 3 3复杂图表的建立 1 概述 标准图表在数据分析中有时仍不能满足实际需要 用户可能需要根据实际情况制作复杂一些的图表 例如在图表中添加涨跌柱线 垂直线 系列线和高低点连线 给图表增加趋势线等 也可以运用一些技巧制作复杂图表 如 组合图表 双轴图表 复合饼图 甘特图 图片图表 趋势图等 3 4复杂图表的建立 2 在图形上增加趋势线趋势线简介 趋势线以图形方式显示数据的发展趋势 常用于预测分析 也称回归分析 在图表中扩展趋势线 根据实际数据预测未来数据 条形图 折线图 柱形图 股价图 气泡图 XY散点图 非堆积型二维面积图都支持趋势线 任务3 根据下表的数据作出图 并根据折线图作出趋势线 线性 指数 对数等不同类型的趋势线 并利用趋势函数预测五月份数据 3 3复杂图表的建立 在折线图上增加趋势线 效果图 折线图增加趋势线制作步骤 知识点 操作步骤 1 先作折线图 2 右击数据系列 添加趋势线 在趋势线选项中 选择一种类型 并 显示公式 3 根据趋势线函数预测5月份数据 X取第5个数据点 Y 103 5 5 6218 6735 5这是5月份数据的预测值 其他类型的趋势线制作方法与此类似 3 4复杂图表的建立 3 组合图表 在一个图表中表示两个甚至多个数据系列 而不同数据系列用不同的图表类型表示 任务4 组合图表 柱形 折线 的制作 基础数据如下表 3 3复杂图表的建立 组合图表 柱形 折线 图的制作 效果图 组合图表制作步骤 知识点 操作步骤 1选中数据区域 2 插入 图表 柱形图 3 将数值小的系列右击变为折线图 3 4复杂图表的建立 4 双轴图表即双数据坐标轴图表 是指在一个图表中在主 次坐标轴上分别绘制一个或多个数据系列 它们采用两种不同的坐标轴值来度量数据 任务5 双轴图表的制作 基础数据如下表 3 3复杂图表的建立 双轴图表的制作 效果图 双轴图表制作步骤 知识点 操作步骤 1 选中数据区域 2 插入 图表 柱形图 3 将数值小的系列右击变为折线图 4 在折线图上右击 设置数据系列格式 系列选项 次坐标轴 5 添加图表标题 两个坐标轴标题 设置字体 3 3复杂图表的建立 5 复合饼图 饼图可以用来表达单个数据与整体结果之间的比例关系 而复合饼图还可以对数据进行深层次分析 任务6 复合条饼图的制作 基础数据如下表 3 3复杂图表的建立 复合条饼图的制作 效果图 复合饼图制作步骤 知识点 操作步骤 1 选择数据区 插入 图表 饼图 复合条饼图 设置数据标签 百分比 显示引导线 2 右击主饼图 设置数据系列格式 系列选项 将 第二绘图区 2改为3 3 优化图表 选中图例删除 双击标题 百分比标签 并修改其内容 设置字体 3 4动态图表的建立 概述所谓动态图表 是指图表的数据源可以根据需要动态变化 从而使数据图表也随之变化 一般有三种方式 1 利用有关函数设置动态区域 2 通过定义数据区域名称 并引入辅助数据区域 3 利用动态控件链接图表中的引用数据 以实现用户的自由选择 3 4动态图表的建立 任务7 利用CHOOSE函数和组合框建立动态图表 3 4动态图表的建立 利用CHOOSE函数和组合框建立动态图表 效果图 3 4动态图表的建立 知识点 操作步骤 1 在原数据区外建立动态数据区域 作为数据源复制地区列到I3 在J3输入公式 CHOOSE M 3 C4 D4 E4 F4 G4 要根据M3内容显示相应内容 向下复制公式 2 转置复制 余额 等列标题到L3 3 开发工具 控件 插入表单控件组合框 右击设置控件格式 控制 数据源区域 单元格链接 下拉项数 将组合框拖入图表区 4 以动态数据区域建立柱形图并格式化 5 从组合框选择项目 观看柱形图变化 3 4动态图表的建立 知识点 相关知识 公式解释Choose函数的功能是从值的列表中返回一个值 如 Choose 2 a1 a2 a3 a4 则返回a2 其中第一参数2为序号 3 4动态图表的建立 任务8 利用INDIRECT等函数建立动态图表 3 4动态图表的建立 利用INDIRECT等函数建立动态图表 效果图 3 4动态图表的建立 知识点 操作步骤 1 在原数据区外建立动态数据区域 作为数据源 2 复制标题行到动态数据区域 3 在 地区 下B15输入公式 INDIRECT ADDRESS CELL row COLUMN B4 向右方各列复制 4 以动态数据区域建立饼图 单击任一地区名称 按F9键 即显示指定地区的饼图 3 4动态图表的建立 知识点 相关知识 公式解释 1 CELL row 返回活动单元格的行号 如光标在B4 则返回4 2 column B4 返回B4单元格的列号 即B 3 address 3 4 则返回单元格地址 D 3 ADDRESS CELL row COLUMN B4 返回光标所在单元格的地址 4 INDIRECT返回由文本字符串指定的引用 在例题中 INDIRECT ADDRESS 3 4 将返回 二季度 又如 A1单元格内容为 A2 A2的内容是100 A3的公式是 INDIRECT A1 则A3为100 4利用数据透视表 图 进行数据分析 学习目标1掌握数据透视表 图的概念和术语 2学会建立数据透视表和数据透视图3了解EXCEL的数据链接和访问外部数据源的一般方法 4利用数据透视表 图 进行数据分析 4 1数据透视表简介4 2数据透视表的建立4 3数据透视表的美化4 4数据透视图4 5关于数据链接 4 1数据透视表简介 1 数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格 是一个功能强大的数据分析工具 2 数据透视表的显著特点是对数据进行动态分析 只需要改变字段的位置 即可得到多种分析结果 因此字段的设置是关键 4 1数据透视表简介 3 数据透视表的主要功能 1 对数值数据进行分类汇总和聚合 按分类和子分类对数据进行汇总 2 展开或折叠要关注结果的数据级别 查看感兴趣区域摘要数据的明细 3 将行移动到列或将列移动到行 或透视 以查看源数据不同组合与汇总的结果 4 对最有用和最关注的数据子集进行筛选 排序 分组和有条件地设置格式 突出显示重要信息 5 提供简明 带有批注的联机报表或打印报表 4 1数据透视表简介 4 何时使用数据透视表 如果要对工作表的数据进行行 列变换 或者要在数据量较大的工作表中进行数据的多种对比分析 就应该使用数据透视表 数据透视表也能完成排序 分类汇总和计数统计等方面的工作 4 1数据透视表简介 5 数据透视表字段列表的运用 1 行标签 该字段的一个数据项占一行 相当于X轴 2 列标签 该字段的一个数据项占一列 相当于Y轴 3 报表筛选 由该字段确定一个二维表 相当于Z轴 按该字段对透视表分页 4 数值 该字段中的数据完成指定的计算 如计数 求和 求平均等 4 2数据透视表的建立 任务1 对 销售记录汇总 表分析各客户各系列产品的销售情况知识点 方法1 1 光标放在数据区 插入 数据透视表 数据区 确定 在一个新的工作表中建立了透视表 2 设置行字段区 客户名称 系列 4 2数据透视表的建立 方法2 1 在一个新的工作表中 插入 数据透视表 使用外部数据源 选择连接 浏览更多 我的电脑 选磁盘 文件夹 工作薄 工作表 放置位置 2 设置行字段区 客户名称 系列 4 2数据透视表的建立 知识点 3 设置数值区 数量 销售额 总成本 毛利等 4 设报表筛选区 发货日期 5 重设布局 让行标签水平显示 默认情况下在行标签下的字段都逐一显示在首行标签下 下拉 以大纲形式显示项目标签 以表格形式显示 6 美化 数据透视表工具 设计 数据透视表样式 7 从筛选处下拉 选择单个日期或 选择多项 8 改名工作表 透视表应用 4 2数据透视表的建立 任务2 分析各系列产品的销售情况知识点 调整行标签 让 系列 在 客户名称 上面即可 4 3数据透视表的美化 1 对选中的区域 开始 字体 边框 填充颜色 以添加边框效果或填充颜色 2 设计 数据透视表样式 其他 3 设置报表筛选字段 可以根据筛选条件来显示数据 可添加多个筛选字段 4 可以行 列 数值标签中删除或增加字段 标签中字段次序可调整 5 数值字段的计算方式可改变 4 3数据透视表的美化 6 可控制显示 不显示分类汇总和总计 分类汇总是每小组的小计 而总计是一行或一列的总计 7 可对选中的字段排序显示 8 可对行标签 选择字段 或选择 值筛选 9 创建好透视表后 可更改数据源以创建不同效果的透视表 选项 数据 更改数据源 10 清除全部透视表数据 选项 操作 清除 11 复制透视表至别处 选项 操作 选择 整个数据透视表 复制 粘贴 4 4数据透视图 1 概述数据透视图是根据透视表的结果转化而来的 透视表与透视图相关联 它们彼此有对应的字段 透视表中的字段改变了 透视图也会变化 数据透视图与常规图表的意义和操作方法基本相同 其主要不同是透视图是一种动态图表 一张透视图实际上是一系列图表 图表内容可根据数据项的变化而变化 4 4数据透视图 2 数据透视表图的生成与编辑任务3 修改前面的透视表并转化为透视图 字段安排如下 1 报表筛选 发货日期 行标签 客户名称 数值 毛利求和 分离型三维饼形图 2 报表筛选 日期 行标签 客户名称 数值 销售额求和 总成本求和 簇状柱形图 3 行标签 发货日期 数值 毛利求和 分离型三维饼形图 4 报表筛选 日期 行标签 客户名称 列标签 系列 数值 销售额求和 簇状柱形图 4 4数据透视图 2 数据透视表图的生成与编辑将透视表转化为透视图后 会自动在工作表中生成 数据透视图筛选窗格 通过设置该窗格中的条件 可以生成不同的透视图 数据透视图的主要编辑有 编辑图表标题 坐标轴标题 图例 数据标签 坐标轴刻度 数据系列的样式 更改数据源 更改图表类型数据透视图的美化设置 图标设置 纵坐标轴设置 图例设置等 与普通图表的设置类似 4 4数据透视图 3 图表布局与图表样式图表布局是指图表中各对象的摆放位置 图表样式是指设置图表效果的一种样式方案 EXCEL提供了多种布局和样式方案可供选择 方法是 主菜单 数据透视工具 设置 图表布局 主菜单 数据透视工具 设置 图表样式 新增练习 对 综合实例 工作薄 透视表图工作表操作 1 各省各民族人数 2 各班各民族人数 3 各班生源分布 4 各省生源政治面目情况 4 5关于数据链接 1 外部引用在一个工作薄中引用另一工作薄中的数据称外部引用 也叫链接 前者称目标工作薄 后者称源工作薄 链接使一个工作薄可以共享另一工作薄中的数据 打开目标时 源可打开也可关闭 如果先打开源后打开目标 数据会自动更新 用户可以自己适时刷新数据 当修改源数据时 EXCEL会通过链接自动修改链接工作薄中的数据 举例 利用外部数据源 如数据源在另外一个工作表 生成透视表 各客户销售额统计 4 5关于数据链接 2 EXCEL与外部数据库企业数据往往以数据库或特殊文件 如XML TXT 形式存储在服务器 网站或某些办公室的计算机中 如果将这些数据导入到EXCEL 就能利用EXCEL进行数据处理和分析 同时EXCEL也可以作为数据库系统的数据采集工具 4 5关于数据链接 3 EXCEL可访问的外部数据库包括SQLServer Oracel Access Foxpro等 4 数据源数据源就是数据的来源 是其他软件如EXCEL访问数据库的一组信息 包括数据库服务器的名称和位置 用于连接数据库的驱动程序名称 以及登录到数据库时需要的各种信息 EXCEL可通过数据源访问各种关系型数据库 5 数据源的类型主要有ODBC数据源OLEDB数据源 4 5关于数据链接 另外 EXCEL可以直接访问文本文件和Access数据库 数据源的数据更新后 可通过在EXCEL中右击数据列表后的菜单选择 刷新 以获得数据库最新的数据 6 ODBC数据源的访问前提 计算机中需要有数据库相应的ODBC驱动程序 如果没有 如MYSQL 可以到该数据库的官方网站进行下载并安装 4 5关于数据链接 访问步骤 1 创建ODBC数据源 控制面板 管理工具 数据源 ODBC 文件DSN 选择ODBC驱动程序如OracleinOraHome92 MicrosoftODBCforOracle 输入数据源名称及保存位置 直到完成2 在Excel中 数据 自其他来源 来自MicrosoftQuery 选择刚建立的数据源 4 5关于数据链接 7 MicrosoftQueryMicrosoftQuery是一个功能强大而且灵活的应用程序 通过它可以检索外部数据库中的数据 在EXCEL中可以调用它 将外部数据库中的数据 甚至是数据库中的符合条件的数据 导入EXCEL 8 访问外部数据库的条件具有访问外部数据库的权限 安装MicrosoftQuery 建立ODBC或其他数据源驱动程序 通过WEB查询不需安装MicrosoftQuery 但须有连接网络的各种设备和身份 还须有可运行的WEB查询文件 iqy 4 5关于数据链接 9 OLEDB数据源的访问所有的ODBC数据源都可以以OLEDB方式导入提供了OLEDB驱动程序的数据库可以以OLEDB方式导入访问步骤 在Excel中 数据 来自数据连接向导 其他 高级 选择OLEDB驱动程序 输入必要信息 4 5关于数据链接 10 EXECL可直接访问的数据源可以在EXCEL和文本文件 如记事本 或Access数据库的表记录间复制粘贴 但这种数据是 死 的数据 如在EXCEL中链接到Access数据库 Access数据库中的数据更新后 在EXCEL中可 刷新 得到最新数据 方法 数据 自文本或数据 自Access 5构造动态数据分析报表 学习目标1理解EXCEL表的的概念 学会建立EXCEL表的方法 2学会使用结构化引用建立动态报表 3学会使用D函数和结构化引用建立动态报表 5构造动态数据分析报表 5 1表与动态报表5 2D函数与动态报表 5 1表与动态报表 5 1 1表的基本概念及其操作1 问题的提出所谓动态报表就是指数据区域的大小可能会随时间而变化的工作表 在数据的统计与分析中 经常需要构造动态报表 以 函数应用 工作表为例 随着时间的推移 当 销售记录汇总 表增加或减少数据行时 原来的公式就不能自动适应 从而就不能得到正确的分析结果 对于前面介绍的透视表和透视图 同样有类似问题 只有更新透视表的数据源方到最新分析结果 Excel中 可以通过Excel专用表 简称表或表格 快捷地构造出动态报表 并能方便地对动态报表进行各种统计分析 5 1表与动态报表 2 什么是表 如何建立表表也称表格 是Excel的特殊对象 即03版本的列表 数据清单 它是一个完整的结构 包含表区域 表数据区域 汇总行 标题行 列标题 调整大小控制点等内容 表具有数据筛选 排序 汇总和计算等功能 并能自动扩展数据区域 查通过表来构造动态报表 表 表格 工作表 区域 它有许多特性 有 表工具 选项卡 建立表的方法 选中工作表中的数据区域 也可以是空白区域 插入 表 创建表对话框 注意列必须要有标题 一个工作表中可以插入多个表 也通过自动套用表样式也能将区域转化为表 5 1表与动态报表 3 关于表样式Excel为表预定义了许多格式 称为表样式 它包括颜色 边框线 底纹等诸多格式化样式 格式化工作表最直接的方式就是套用表样式 在套用表样式时 Excel会将区域转化为表 为工作表自动套用表样式的方法 单击任一单元区域 开始 样式 套用表格样式 选择一种样式 确定 在 表工具 设计 中 提供了许多选项 表转换为区域 可通过 表工具 设计 工具 转换为区域 区域把表转换为区域 5 1表与动态报表 任务1 1 把 销售记录汇总 表复制为 销售记录汇总表 工作表 2 将普通工作表转换为表 修改表名为 销售表 3 在 动态报表 工作表中完成 统计各客户本期购买各系列产品的金额 5 1表与动态报表 知识点 表的基本操作 1 增加计算列 写入公式 如 2或 销售表 毛利 2 不一定在最上方单元格内输入 并回车 公式可扩展至其他行 即整列 2 可修改列名称 3 可以控制是否显示 汇总 行 4 修改表名称 5 扩展表格 6 表工具 设计 其他按钮 5 1表与动态报表 通过表能够方便地构造日常工作中的动态报表 在各种公式中通过对表的结构化引用 不仅使公式含义清楚 而且能够扩展公式的计算能力 实现对动态报表的各类计算 5 1表与动态报表 5 1 2结构化引用1 什么是结构化引用在对表进行计算的公式中 可以引用表中的单元格 也可以引用表的行 列 数据区域 汇总行或标题等表结构 后者称为结构化引用 其最大特点是对动态报表的自动识别 无论表的数据区域怎样变化 结构化引用的单元格区域都能自动调整 极大地方便了在表中增加 删除行或列时重写公式的需要 简单而言 公式中引用了表结构 就是结构化引用 5 1表与动态报表 2 结构化引用的语法规则 1 表名称 相当于表数据区 不包括标题行和汇总行 名称可修改 2 列说明符 由括号括起 并引用列数据 不包括标题行和汇总行 3 特殊项目说明符 说明表中特殊部分 如 数据 全部 此行 标题 汇总 等 4 表说明符 跟在表名称后 括起 5 结构化引用 以表名称开始 以表说明符结尾 5 1表与动态报表 3 结构化引用示例在表中输入一个公式如 sum L2 L107 在表最下方增加数据 毛利 看公式能否调整 这是单元格引用 输入一个公式如 sum 销售表 毛利 在表最下方增加数据 毛利 看公式能否调整 这是结构化引用 公式中引用了表的结构 5 2D函数与动态报表 1 D函数Excel把每个列都有标题的数据表称为数据库 并提供了12个专用函数来简化对这种表的统计和查找工作 这些函数均以D打头 故称D函数 如Dsum Daverage Dcount Dmax Dmin Dproduct等 D函数的调用格式 形式参数表都相同 语法格式如下 Dname 单元格区域 要计算的列字段 条件区 其中 单元格区域 要求每列都有列标题 列字段 是某列的列标题 条件区的含义和构造方法同高级筛选 5 2D函数与动态报表 2 D函数与表结合构造动态数据分析表可以在D函数中对表进行访问 通过在D函数中对表的结构化引用 能够轻松地计算出动态报表中的各项统计分析数据 5 2D函数与动态报表 2 D函数与表结合构造动态数据分析表任务2的操作 在 动态报表 中完成 统计各客户本期购买各系列产品的金额 D4公式为 SUMIFS 销售表 销售额 销售表 客户名称 动态报表 C4 销售表 系列 动态报表 D 3 公式也可以是 SUM IF 销售表 客户名称 动态报表 C4 IF 销售表 系列 动态报表 D 3 销售表 销售额 按Ctrl Shift Enter键输入公式 然后复制到其他单元格 5 2D函数与动态报表 任务3 在 动态报表 中完成 各系列产品的销售额之和 D19的公式为 DSUM 销售表 全部 销售表 标题 销售额 动态报表 D17 D18 按Ctrl Shift Enter键 然后复制到其他单元格 注意 第2个参数必须是列标题 5 2D函数与动态报表 3 D函数本身具有对动态数据表进行自动计算的能力 它与Excel数据库结合也能实现动态计算首先 工作表每列都要有标题 是Excel数据库 其次 D函数的第1个参数引用的普通Excel数据库区域要足够大 包括空白单元格 这样 当增加数据行时 D函数可以扩展计算 示例 动态报表 中求本表中 观音饼 交易金额之和 注意 D函数对表的结构化引用 可以实现完全自动的动态数据分析 而D函数对普通Excel数据库 区域 单元格的引用 只有当数据不超过第1个参数指出的范围时 才能动态计算 不如D函数结构化引用来得方便 5 2D函数与动态报表 概念整理 1 工作表 区域 2 Execel数据库 3 表 表格 4 动态报表 5 结构化引用 6 D函数对于Excel数据库和数据库专用表 均能动态计算 前者受限 5 2D函数与动态报表 对动态报表进行动态分析的途径 工作表 区域 表 非D函数结构化引用 动态分析 D函数结构化引用 Excel数据库 D函数非结构化引用 第1参数足够大 转化 有列标题 综合实例中有相关练习 6宏与VBA在数据分析中的应用 学习目标1 了解Excel宏的基本知识2 掌握宏的录制 编写和运行方法3 了解VBA程序设计的基本知识 6宏与VBA在数据分析中的应用 6 1宏的概念 录制与运行6 2VBA与宏的关系6 3VBA编辑器6 4VBA的组成和结构6 5VBA程序的编写与运行6 6VBA实例 6 1宏的概念 录制与运行 6 1 1宏的概念什么是宏 字面是宏伟 宽广 宏是一段定义好的操作 它可以是用VBA程序设计语言编写或录制的程序 其中保存有一系列Excel的命令 宏可以作为单个命令执行来自动完成某项任务 而且可以被多次重复使用 宏是存储在工作薄中的代码段 它可以自动处理重复复杂的任务 快速地组织数据 使用宏可以减少完成任务所需的步骤 减少失误 当需要处理重复性的 繁杂的工作时可以使用宏 如何创建宏 在Excel中 可以用Excel提供的宏录制工具录制宏程序 也可以使用它提供的 VisualBasic编辑器 直接编写宏 6 1宏的概念 录制与运行 使用宏的四大步骤开始录制宏按步骤操作 执行需要经常重复性执行的任务 把这些步骤录制到宏里 完成宏录制 执行宏 宏的创建方式有录制宏 加载宏 自定义宏三种 6 1宏的概念 录制与运行 6 1 2宏的录制方法宏录制器是Excel提供的一种软件工具 它能够将用户的操作过程记录下来 并自动将所记录的操作转换成为VBA程序代码 对于经常重复进行的操作过程 可以通过宏录制器将它记录下来 当需要再次进行这些操作时 只需要运行录制的宏 Excel就能自动完成这些重复的操作 说明 当录制宏的工作开始后 所有的操作步骤都将被记录在宏中 所以录制前要作好规划 最好将操作的步骤列出来 录制中应尽量减少不必要的或错误的操作 如果在录制宏时出现失误 更正失误的操作也会记录在宏中 宏录制不同于录像 不是按时间进度录制 只记录对工作薄有影响的操作 6 1宏的概念 录制与运行 录制宏的方法有3种 任务1 对 宏与VBA 工作薄中产品销售数据的整理 方法1 通过 视图 建立宏 单击 视图 宏 的下箭头 选择 录制宏 命令 弹出 录制新宏 对话框方法2 通过 开发工具 代码 录制宏 如果开发工具选项卡不显示 Office按钮 Excel选项 常用 在功能区显示开发 方法3 通过状态栏中的按钮单击 视图 宏 的下箭头 选择 停止录制 命令 或击Excel最下边状态栏中的停止录制按钮 6 1宏的概念 录制与运行 6 1 3宏的运行通过快捷键运行宏通过对话框运行宏通过图形控件或窗体命令按钮运行宏通过自定义工具按钮运行宏 6 1宏的概念 录制与运行 1 通过自定义工具按钮运行宏开发工具 控件 插入表单控件 在工作表中画个按钮 指定宏 如macro3 右击 编辑文字单击工作表中的按钮即执行宏 6 1宏的概念 录制与运行 2 通过图形控件在工作表插入图片 右击指定宏 单击图片执行宏 6 1宏的概念 录制与运行 6 1 4宏的保存宏的保存位置 可以是当前工作薄 个人宏工作薄 新工作薄 如果要在每次使用EXCEL时都能够使用宏 应选择 个人宏工作薄 保存带有宏的工作薄时要注意 保存类型 6 1宏的概念 录制与运行 保存在个人宏工作簿 Personal xls 中 保存在专门保存宏的 新的工作簿 中 保存在建立宏的当前工作薄中 6 1宏的概念 录制与运行 6 1 4编辑查看录制宏选择 视图 宏 停止录制 选择 视图 宏 查看宏 菜单项 会显示 宏 对话框 选中其中的 产品信息整理 单击 编辑 6 1宏的概念 录制与运行 在Excel编程环境中看见的程序代码 6 1宏的概念 录制与运行 宏代码Sub产品信息整理 产品信息整理Macro 快捷键 Ctrl Shift ZDimiAsLongFori 1To40Application CutCopyMode FalseSelection CopyActiveCell Offset 0 2 Range A1 SelectActiveSheet PasteActiveCell Offset 1 2 Range A1 SelectApplication CutCopyMode FalseSelection CopyActiveCell Offset 1 3 Range A1 SelectActiveSheet PasteActiveCell Offset 2 3 Range A1 SelectApplication CutCopyMode FalseSelection C