Excel表格2013高级教程(数据处理及分析处理).pptx

1 Excel2013高级教程 数据统计与处理分析 Excel是Office软件中的核心成员，是最优秀的电 子表格软件之一，具有强大的数据处理和数据分 析能力，是个人及办公事务中进行表格处理和数 据分析的理想工具之一。 如何利用Excel的函数、图表、高级分析工具、 VBA程序等功能进行数据分析是本次学习的重 点。 内容提要 2 2 利用函数进行数据分析利用函数进行数据分析 P29P29 1 1 数据处理与分析基础数据处理与分析基础 P4P4 4 4 利用透视表（图）进行数据分析利用透视表（图）进行数据分析 P72P72 3 3 利用图表进行数据分析利用图表进行数据分析 P42P42 5 5 构建动态数据分析报表构建动态数据分析报表 P96P96 6 6 宏与宏与VBAVBA在数据分析中的应用在数据分析中的应用 P114P114 7 Excel 7 Excel 的数据分析工具简介的数据分析工具简介 P146P146 1、数据处理与分析基础 学习目标 1 认识EXCEL的功能与界面 2 学会利用数据条件格式进行数据处理 3 学会利用排序、筛选、分类汇总功能进行数据 分析 EXCEL在数据分析中的应用4 1、数据处理与分析基础 1.1 EXCEL的功能与界面 1.2 数据的输入、编辑与运算 （案例：销售产品基本信息表、销售记录汇总表） 1.3 利用数据条件格式进行数据处理 1.4 利用排序、筛选、分类汇总功能进行数据分 析 1.1 Excel的功能与界面 1.1.1 EXCEL新增的主要功能 （1）取消了菜单方式，采用了面向结果的用户界面， 易于找到。 （2）更强大的数据管理能力和安全性。（如更多的行 和列104857616384，1600万种颜色） （3）更强大的表功能，提供了全新的数据引用方式， 称为结构化引用，可以方便地构造动态数据报表。 （4）其他方面： 提供了大量预定义主题和样式 丰富的条件格式 自动调整编辑栏 函数记忆式输入 1.1 Excel的功能与界面 1.1.1 EXCEL新增的主要功能 改进的筛选和排序功能（可按日期和颜色排序 ） 图表外观更美观、更专业、布局和样式更多 易于使用的数据透视表、数据透视图 快速连接外部数据源 1.1 Excel的功能与界面 1.1.2 EXCEL的用户界面 整个界面由功能区和工作表区组成； 功能区有：OFFICE按钮、选项卡、组、快速访问工具栏、标题栏、状态栏。 OFFICE按钮：相当于早期的“文件”菜单； 选项卡：面向 任务，包括功能控件。“开始”有日常操作功能，“页面布局”与 打印有关（有书也称主菜单） 组：每个组都与特定任务相关；（有书也称工具栏） 快速访问工具栏：独立显示，默认有“保存”、“撤销”和“恢复“三按钮，可以 自定义； 标题栏：显示工作薄名称。 状态栏：宏录制按钮、查看方式、缩放工具。 1.2 数据的输入、编辑与运算 目标：建立某企业产品销售数据统计报表（两个工作表） 1.2.1建立产品基本信息表 任务1：在“产品营销数据处理与分析实例”工作薄建立形如下面的工作 表，名称为“产品基本信息” 企业所有商品基本信息列表 知识点： 表格内容了解、新建工作薄、新建或改名工作表； 格式化工作表。包括对齐方式、设置单元格格式（下划线、列 宽、边框、填充颜色） 产品编号 系列产品名称 进货单价 销售单价 AP11001 观音饼观音饼（花生） 6.5 12.8 AP11002观音饼观音饼（桂花） 6.5 12.8 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/A TRUE或省略：近似匹配，若找不到返回一个小于要 找参数的最大值。 （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可以按单元格颜色排序。 思考：排序之后如何回到排序前的状态。（必要时引入 辅助字段） 姓名性别年龄工资辅助 张三男2534561 李四男2332152 王五女22 8903 1.4 利用排序、筛选、 分类汇总功能进行数据分析 1.4.2 自动筛选 概述： 筛选就是只把满足条件的数据行显示出来，而把 不关注的数据隐藏。 筛选有自动筛选和高级筛选两种。自动筛选易于 使用，高级筛选条件可以更复杂。 任务5：对销售记录汇总表操作 （1） 查看特定系列产品（如“观音酥”）的销售 情况（自动）筛选 （2）查看特定用户（如“好又多”）、特定系列产 品（如“旅游产品 ” ）的销售情况组合自动筛选 1.4 利用排序、筛选、 分类汇总功能进行数据分析 1.4.2 自动筛选 知识点： 欲选择某列中一个项止，先取消本列中的“ 全选”复选框，然后再单选 ； 注意状态栏提示（筛选出的个数）、列标 右侧的图标显示； 筛选是累加的，后一次筛选在前一次基础上 进行； 解除一列筛选：按“全选”；全部解除：按“ 筛选”按钮。 EXCEL在数据分析中的应用22 1.4 利用排序、筛选、 分类汇总功能进行数据分析 1.4.3 高级筛选 概述：可实现多字段间的“或”条件筛选， 并能将结果复制到其他区域。 任务6： 查看一段时间内（如2007年 8月上 旬）特定用户（如“好又多”）的销售情况 此任务可用高级筛选或自动筛选完 成。 EXCEL在数据分析中的应用23 1.4 利用排序、筛选、 分类汇总功能进行数据分析 1.4.3 高级筛选 知识点： 1、条件的写法（日期条件中用的是日期的数值） 2、取消高级筛选单击“清除” 思考： 要筛选出销售数量不低于100，或者毛利不小于2000 元的数据怎么做。 EXCEL在数据分析中的应用24 数量 毛利 =100 =2000 发货日期 发货日期 客 户名称 =39295 50, SUM(B2:B5), 0 ) =IF(A2=10,“科技处”,IF(A2=20,“财务处”,“人事 处”) EXCEL在数据分析中的应用31 条件取真时取假时 2 利用函数进行数据分析 2.2 SUMIF函数的应用 任务1：新建一个工作表，命名为“函数应用“， 统计各客户购买数量、购买额等数据之和。 知识点：SUMIF函数 （1）输入 要分析的数据栏目 （从销售记录汇总表挑选字段粘贴而来） （2）输入 客户名称 的技巧（将分类汇总结果 复制到别处） （3）使用SUMIF函数计算各个字段。 （4）使用排名函数RANK按毛利对客户排名。 EXCEL在数据分析中的应用32 客户名称 数量销售额 总成本 毛利 毛利排名 相关知识：Sumif函数 SUMIF（条件判断区域，条件，求和区域） 根据指定条件对若干单元格求和。条件可以是数 字、表达式或文本，如：“北京”、“=50”等，要使用 引号。也可以是单元格引用。 案例中C5处（数量）的公式： =SUMIF(销售记录汇总!$A$2:$A$107,$B5,销售记录 汇总!$G$2:$G$107) 复制到右侧单元格，并修改最后一项中的列号，依次 为IKL列。 （SUMIF其他例子见“综合实例”工作薄） EXCEL在数据分析中的应用33 求和区域 条件区域条件值 相关知识：RANK函数的使用 格式： RANK（要排位的数值，排位区域，选项） 功能： 返回一个数值在一组数值中的排位。 选项： 非0： 升序排位 0或省略：降序排位 EXCEL在数据分析中的应用34 2 利用函数进行数据分析 2.3 SUMIFS函数的应用 任务2：用SUMIFS函数对工作表“函数应用 ”，统计各客户购买数量、购买额等数据 之和。 知识点：SUMIFS函数 （1）“数量”的公式为 =SUMIFS(销售记录汇总!$G$2:$G$107,销售记录汇总 !$A$2:$A$107,B$19) EXCEL在数据分析中的应用35 客户名称 数量销售额 总成本 毛利 毛利排名 求和区域 条件区域条件值 相关知识：sumifs函数的使用 格式： SUMIFS（求和区域，条件范围1，条件 1，条件范围 2 ） 功能： 对某一区域内满足多重条件的单元格求和，最多127 个条件。条件可以是“=39295)*( 销售记录汇总!$C$3:$C$410=39304)*销售记录汇总 !$N$3:$N$410) 中旬与下旬类似，只是数值不同。 思考： 什么情况下要使用SUMPRODUCT函数？ EXCEL在数据分析中的应用41 3 利用图表进行数据分析 学习目标 1 了解图表的类型及其组成部分 2 掌握图表的建立、编辑与美化方法 3 理解复杂图表的建立方法 4 了解动态图表的建立方法 EXCEL在数据分析中的应用42 3 利用图表进行数据分析 3.1 图表概述 3.2 图表的建立、编辑与美化 3.3 复杂图表的建立 3.4 动态图表的建立 EXCEL在数据分析中的应用43 3.1 图表概述 图表是EXCEL中一个强有力的工具，利用图表可以 使数据更加直观地显示出来。EXCEL有丰富的图表类型 ，其功能不逊于某些专业图表软件。 了解图表有关术语和组成部分是正确使用图表的前 提。 1、有关术语 （1）数据点：即数据标记，本质上是一个单元格中 的数值图形表示，不同类型的图表数据点形状不同，柱 形图中表现为一个柱形，折线图中为一个点，面积图中 为一扇形区域。 （2）数据系列：图表中一组相关的数据点，它来自 于数据表的某行或某列，每个数据系列有相同的颜色和 图案，并且通过图例标识。一张图表有多个数据系列， 但饼图只有一个。 （3）数据标签：是指为数据系列或数据点添加的标 识，默认情况下图表没有数据标签，用户可以添加数据 标签、并修改它的位置和大小。 EXCEL在数据分析中的应用44 3.1 图表概述 有关术语： （4）网格线：用于查看和评估数据，有水平和垂 直两种。 （5）轴：作为绘图区一侧边界的直线，有X轴也 称分类轴，Y轴也称数值轴两种。 （6）刻度线与刻度标志：类似于尺子上的刻度。 （7）图例：用于说明每个数据系列中的数据点所 采用的图形外表。 （8）标题：有图表标题、分类轴标题和数值轴标 题。识别图表区域的技巧： 单击图表上不同区域，观看：图表工具布局 当前所选内容。 EXCEL在数据分析中的应用45 3.1 图表概述 2、最常见的图形介绍 （1）柱形图：用于显示某段时间内数据的变化，或 比较各数据项之间的差异。分类一般为日期或时间，数 值在垂直方向组织，以便于强调相对于时间的变化。 （2）条形图：也用于显示各数据之间的比较。与柱 形图不同的是，其分类在垂直方向（一般为日期或时间 ），而数值在水平方向，使观察者的注意力集中在数据 值的比较上，而不在时间上。 （3）折线图：主要用于显示各数据随时间而变化的 趋势情况。横坐标几乎总是表现为时间。 （3）饼 图：用于显示组成数据系列的各数据项与数 据项总和的比例。当只有一个数据系列，并且用于强调 整体中的各个组成部分占整体的比例时十分有效。 EXCEL在数据分析中的应用46 3.2 图表的建立 任务1： 对前面一章任务5中工作表中 的结果 ，制作 各系列产 品销售额百分比分离型三维饼图。 知识点： （1）建立“总计”行，写入公式：SUM（范围） （3）选择上下数据两行插入饼图分离型三维饼图 ； （4）输入图表标题“各系列产品销售额占比分析”； （5）添加百分比数据标签； 图表工具-布局-其他数据标签选项选择：类别名称、百 分比；选择：数字百分比2位小数。 （6）删除图例 EXCEL在数据分析中的应用47 3.2 图表的建立 任务2 ： 仍对前面的工作表中，制作分离型三维饼图，分析 从各客户获取利润的百分比。 要求： （1）图表标题为“从各客户获取利润占比分析”。 （2）图例在下方。 思考：如何选择数据源？ EXCEL在数据分析中的应用48 3.2 图表的建立 归纳 ：建立图表的步骤 （1）首先选择数据源。要选定图表数据所在的单元格 ，如希望数据的行、列标志也显示在图表中，则选定 区还应包括行/列标题。 （2）应用预定义的图表布局和样式设置图表。方法： 图表工具布局/样式。 （3）创建图表工作表。选中图表，设计位置移动 图表。（可将嵌入式图表变成图表工作表，并可移回 ） EXCEL在数据分析中的应用49 3.3 复杂图表的建立 1、概述： 标准图表在数据分析中有时仍不能满足 实际需要，用户可能需要根据实际情况制作复杂 一些的图表，例如在图表中添加涨跌柱线、垂直 线、系列线和高低点连线，给 图表增加趋势线 等，也可以运用一些技巧制作复杂图表，如：组 合图表、双轴图表、复合饼图、甘特图、图片图 表、趋势图等。 EXCEL在数据分析中的应用50 3.4 复杂图表的建立 2、在图形上增加趋势线 趋势线简介：趋势线以图形方式显示数据的发展趋势， 常用于预测分析（也称回归分析，在图表中扩展趋势线 ，根据实际数据预测未来数据），条形图、折线图、柱 形图、股价图、气泡图、XY散点图、非堆积型二维面积 图都 支持趋势线。 任务3 ：根据下表的数据作出图，并根据折线图作出趋势线 （线性、指数、对数等不同类型的趋势线），并利用趋 势函数预测五月份数据。 EXCEL在数据分析中的应用51 某市1-4月份城乡居民存款额（万元） 一月二月三月四月 6296 6448 6560 6604 3.3 复杂图表的建立 在折线图上增加趋势线效果图 EXCEL在数据分析中的应用52 折线图增加趋势线制作步骤 知识点：操作步骤 （1）先作折线图。 （2）右击数据系列添加趋势线，在趋势线选 项中选择一种类型，并“显示公式”。 （3）根据趋势线函数预测5月 份数据，X取第5 个数据点： Y=103.5*5+6218=6735.5 这是5月 份数据的预测值。 其他类型的趋势线制作方法与此类似。 EXCEL在数据分析中的应用53 3.4 复杂图表的建立 3、组合图表：在一个图表中表示两个甚至多个数据系 列，而不同数据系列用不同的图表类型表示。 任务4 ：组合图表（柱形-折线）的制作，基础数据如 下表。 EXCEL在数据分析中的应用54 物流信息系统项目进度表 规划 咨询 需求 调研 模块 设计 软件 编码 系统 集成 后期 测试 计划工作日 252015302510 实际工作日 193025201525 3.3 复杂图表的建立 组合图表（柱形-折线）图的制作效果图 EXCEL在数据分析中的应用55 组合图表制作步骤 知识点：操作步骤 （1 选中数据区域。 （2）插入图表柱形图 （3）将数值小的系列右击变为折线图 EXCEL在数据分析中的应用56 3.4 复杂图表的建立 4、双轴图表 即双数据坐标轴图表，是指在一个图表中在主、次 坐标轴上分别绘制一个或多个数据系列，它们采用两 种不同的坐标轴值来度量数据。 任务5 ：双轴图表的制作，基础数据如下表。 EXCEL在数据分析中的应用57 A市2009年3月上旬新建商品房销售情况表 1号2号3号4号5号6号7号8号9号10号 总销售面 积（m2） 131200 92500 100020 203050 220000 80000 67000 75000 80000 56000 销售均价 （元/m2） 8500 1000093008900910094008700850083008400 3.3 复杂图表的建立 双轴图表的制作效果图 EXCEL在数据分析中的应用58 双轴图表制作步骤 知识点：操作步骤 （1）选中数据区域。 （2）插入图表柱形图 （3）将数值小的系列右击变为折线图 （4）在折线图上右击，设置数据系列格式系 列选项次坐标轴。 （5）添加图表标题，两个坐标轴标题 ，设置字 体。 EXCEL在数据分析中的应用59 3.3 复杂图表的建立 5、复合饼图：饼图可以用来表达单个数据与整体 结果之间的比例关系，而复合饼图还可以对数据进行 深层次分析。 任务6：复合条饼 图的制作，基础数据如下表。 EXCEL在数据分析中的应用60 HP小型机 售后一年客户维修情况反馈 项目数量 已经更换过 62 没有修理过 234 修理过1次 56 修理过2次 35 修理过3次以上 17 3.3 复杂图表的建立 复合条饼 图的制作效果图 EXCEL在数据分析中的应用61 复合饼图制作步骤 知识点：操作步骤 （1）选择数据区，插入“图表“饼 图”“复 合条饼图”，设置数据标签（百分比、显示引 导线） （2）右击主饼 图设置数据系列格式系列选 项将”第二绘图区“2改为3。 （3）优化图表，选中图例删除 ，双击标题、百 分比标签，并修改其内容、设置字体。 EXCEL在数据分析中的应用62 3.4 动态图表的建立 概述 所谓动态图表，是指图表的数据源可以根据需要动 态变化，从而使数据图表也随之变化，一般有三种方 式： 1、利用有关函数设置动态区域； 2、通过定义数据区域名称，并引入辅助数据区域； 3、利用动态控件链接图表中的引用数据，以实现用户 的自由选择。 EXCEL在数据分析中的应用63 3.4 动态图表的建立 任务7 ：利用CHOOSE函数和组合框建立动态图表 EXCEL在数据分析中的应用64 某月各地区个人储蓄业务发展分析（定期存款）单位：万元 地区余额本月净增额 累计净增额本月增长率累计增长率 北京 1283648616.8191768.5-0.1312.292 天津 579627.36302.1717780.779.891 河北 271006.41734.638310.30.44811.522 山西 365395.8490.527660.5-0.2595.733 内蒙 233582.3501.532508.70.02811.319 辽宁 969297884.8153092.80.06313.132 3.4 动态图表的建立 利用CHOOSE函数和组合框建立动态图表效 果图 EXCEL在数据分析中的应用65 3.4 动态图表的建立 知识点：操作步骤 （1）在原数据区外建立动态数据区域，作为数据源 复制地区列到I3，在J3输入公式： =CHOOSE($M$3,C4,D4,E4,F4,G4)，要根据M3内 容显示相应内容，向下复制公式。 （2）转置复制“余额”等列标题到L3 （3）开发工具控件插入表单控件组合框，右击 设置控件格式控制数据源区域/单元格链接， 下拉项数，将组合框拖入图表区。 （4）以动态数据区域建立柱形 图并格式化。 （5）从组合框选择项目，观看柱形图变化。 EXCEL在数据分析中的应用66 3.4 动态图表的建立 知识点：相关知识公式解释 CHOOSE函数的功能是从值的列表中返回一 个值 。 如： CHOOSE（2，”A1”,”A2”,”A3”,”A4”）则返 回A2，其中第一参数2为序号。 EXCEL在数据分析中的应用67 3.4 动态图表的建立 任务8 ：利用INDIRECT等函数建立动态图表 EXCEL在数据分析中的应用68 一季度城乡居民存款情况 地区一季度二季度三季度四季度 北 京 19304 19990 20919 21856 天 津 6733 6855 7152 7463 河 北 19691 20353 20714 21222 山 西 10819 11378 11938 12191 内 蒙 5260 5478 5715 5945 辽 宁 19284 19850 20417 20767 吉 林 7889 8220 8432 8450 黑龙江 11660 11872 12046 11989 3.4 动态图表的建立 利用INDIRECT等函数建立动态图表效果图 EXCEL在数据分析中的应用69 3.4 动态图表的建立 知识点：操作步骤 （1）在原数据区外建立动态数据区域，作为数 据源 （2）复制标题行到动态数据区域 （3）在“地区”下B15输入 公式： =INDIRECT(ADDRESS(CELL(“ROW“),COLUMN(B4) ) 向右方各列复制 （4）以动态数据区域建立饼 图，单击任一地区 名称，按F9键，即显示指定地区的饼 图。 EXCEL在数据分析中的应用70 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。 EXCEL在数据分析中的应用71 A 1A2 2100 3 4 利用数据透视表（图） 进行数据分析 学习目标 1 掌握数据透视表、图的概念和术语。 2 学会建立数据透视表和数据透视图 3 了解EXCEL的数据链接和访问外部数据源的 一般方法。 EXCEL在数据分析中的应用72 4 利用数据透视表（图） 进行数据分析 4.1 数据透视表简介 4.2 数据透视表的建立 4.3 数据透视表的美化 4.4 数据透视图 4.5 关于数据链接 EXCEL在数据分析中的应用73 4.1 数据透视表简介 1、数据透视表是一种对大量数据快速汇 总和建立交叉列表的交互式表格，是一 个功能强大的数据分析工具。 2、数据透视表的显著特点是对数据进行 动态分析，只需要改变字段的位置，即 可得到多种分析结果。因此字段的设置 是关键。 EXCEL在数据分析中的应用74 4.1 数据透视表简介 3、数据透视表的主要功能 （1）对数值数据进行分类汇总和聚合，按分类和 子分类对数据进行汇总； （2）展开或折叠要关注结果的数据级别，查看感 兴趣区域摘要数据的明细； （3）将行移动到列或将列移动到行（或透视）， 以查看源数据不同组合与汇总的结果； （4）对最有用和最关注的数据子集进行筛选、排 序、分组和有条件地设置格式，突出显示重要 信息； （5）提供简明、带有批注的联机报表 或打印报 表。 EXCEL在数据分析中的应用75 4.1 数据透视表简介 4、何时使用数据透视表？ 如果要对工作表的数据进行行、列变换 ，或者要在数据量较大的工作表中进行数 据的多种对比分析，就应该使用数据透视 表。 数据透视表也能完成排序、分类汇总和 计数统计等方面的工作。 EXCEL在数据分析中的应用76 4.1 数据透视表简介 5、数据透视表字段列表的运用 （1）行标签。该字段 的一个数据项占一行，相当于 X轴。 （2）列标签。该字段 的一个数据项占一列，相当于 Y轴。 （3）报表筛选。由该字段 确定一个二维表，相当于 Z轴。 按该字段对透视表分页。 （4）数值 。该字段 中的数据完成指定的计算，如 计数、求和、求平均等。 EXCEL在数据分析中的应用77 4.2 数据透视表的建立 任务1： 对“销售记录汇总”表分析各客户各系列产 品的销售情况 知识点： 方法1： （1）光标放在数据区，插 入数据透视表数 据区确定（在一个新的工作表中建立了透视 表） （2）设置行字段区：客户名称、系列。 EXCEL在数据分析中的应用78 4.2 数据透视表的建立 方法2： （1）在一个新的工作表中，插入数据透视表 使用外部数据源选择连接浏览更多我 的电脑选磁盘、文件夹、工作薄、工作表 放置位置。 （2）设置行字段区：客户名称、系列。 EXCEL在数据分析中的应用79 4.2 数据透视表的建立 知识点： （3）设置数值区：数量、销售额、总成本、毛利等 （4）设报表筛选区：发货日期 （5）重设布局。让行标签水平显示（默认情况下在 行标签下的字段都逐一显示在首行标签下。下拉- 以大纲形式显示项目标签/以表格形式显示）。 （6）美化。数据透视表工具设计-数据透视表样 式 （7）从筛选处下拉。选择单个日期或“选择多项”。 （8）改名工作表“透视表应用” EXCEL在数据分析中的应用80 4.2 数据透视表的建立 任务2分析各系列产品的销售情况 知识点： 调整行标签，让“系列”在“客户名称“上面即可。 EXCEL在数据分析中的应用81 4.3 数据透视表的美化 （1）对选中的区域：开始字体边框/填充颜 色，以添加边框效果或填充颜色； （2）设计数据透视表样式其他 （3）设置报表筛选字段，可以根据筛选条件来 显示数据，可添加多个筛选字段。 （4）可以行/列/数值标签中删除或增加字段。 标签中字段次序可调整。 （5）数值字段的计算方式可改变。 EXCEL在数据分析中的应用82 4.3 数据透视表的美化 （6）可控制显示/不显示分类汇总和总计（分类 汇总是每小组的小计，而总计是一行或一列的 总计）。 （7）可对选中的字段排序显示。 （8）可对行标签“选择字段”或选择“值筛选”。 （9）创建好透视表后，可更改数据源以创建不 同效果的透视 表。选项数据更改数据 源。 （10）清除全部透视 表数据。选项操作-清 除。 （11）复制透视表至别处。选项操作选择 整个数据透视表复制/粘贴 。 EXCEL在数据分析中的应用83 4.4 数据透视图 1、概述 数据透视图是根据透视表的结果转化而来的，透 视表与透视图相关联，它们彼此有对应的字 段。透视表中的字段改变了，透视 图也会变 化。 数据透视图与常规图表的意义和操作方法基本相 同，其主要不同是透视图是一种动态图表，一 张透视图实际上是一系列图表，图表内容可根 据数据项的变化而变化。 EXCEL在数据分析中的应用84 4.4 数据透视图 2、数据透视表图的生成与编辑 任务3： 修改前面的透视表并转化为透视图，字段安排如下： （1）报表筛选：发货日期，行标签：客户名称，数值： 毛利求和，分离型三维饼形图； （2）报表筛选：日期，行标签：客户名称，数值：销售 额求和、总成本求和、簇状柱形图； （3）行标签：发货日期，数值：毛利求和，分离型三维 饼形图； （4）报表筛选：日期，行标签：客户名称，列标签：系 列，数值：销售额求和，簇状柱形图。 EXCEL在数据分析中的应用85 4.4 数据透视图 2、数据透视表图的生成与编辑 将透视表转化为透视图后，会自动在工作表中生成“数 据透视图筛选窗格”，通过设置该窗格中的条件，可 以生成不同的透视 图。 数据透视图的主要编辑有：编辑图表标题，坐标轴标题 ，图例，数据标签，坐标轴刻度，数据系列的样式， 更改数据源、更改图表类型 数据透视图的美化设置、图标设置、纵坐标轴设置、图 例设置等，与普通图表的设置类似。 EXCEL在数据分析中的应用86 4.4 数据透视图 3、图表布局与图表样式 图表布局是指图表中各对象的摆放位置。 图表样式是指设置图表效果的一种样式方案。 EXCEL提供了多种布局和样式方案可供选择。 方法是： 主菜单：数据透视工具设置图表布局； 主菜单：数据透视工具设置图表样式。 新增练习：对“综合实例”工作薄，透视 表图工作表操作： （1）各省各民族 人数。（2）各班各民族 人数 （3）各班生源分布（4）各省生源政治面目情况。 EXCEL在数据分析中的应用87 4.5 关于数据链接 1、外部引用 在一个工作薄中引用另一工作薄中的数据称外部引用 ，也叫链接。前者称目标工作薄，后者称源工作薄。 链接使一个工作薄可以共享另一工作薄中的数据。打 开目标时，源可打开也可关闭，如果先打开源后打开目 标，数据会自动更新。用户可以自己适时刷新数据。 当修改源数据时，EXCEL会通过链接自动修改链接工 作薄中的数据。 举例： 利用外部数据源（如数据源在另外一个工作表）生成 透视表。（各客户销售额统计） EXCEL在数据分析中的应用88 4.5 关于数据链接 2、EXCEL与外部数据库 企业数据往往以数据库或特殊文件（如XML 、TXT）形式存储在服务器、网站 或某些办公 室的计算机中，如果将这些数据导入到EXCEL ，就能利用EXCEL进行数据处理和分析 ，同时 EXCEL也可以作为数据库系统的数据采集工 具。 EXCEL在数据分析中的应用第89页 4.5 关于数据链接 3、EXCEL可访问的外部数据库 包括SQL SERVER,ORACEL, ACCESS,FOXPRO 等。 4、数据源 数据源就是数据的来源，是其他软件如EXCEL访 问数据库的一组信息，包括数据库服务器的名称 和位置，用于连接数据库的驱动程序名称，以及 登录到数据库时需 要的各种信息。EXCEL可通过 数据源访问各种关系型 数据库。 5、数据源的类型主要有 l ODBC 数据源 l OLE DB数据源 EXCEL在数据分析中的应用第90页 4.5 关于数据链接 l另外，EXCEL可以直接访问文本文件和 ACCESS数据库。 数据源的数据更新后，可通过在EXCEL 中右击数据列表后的菜单选择“刷新” 以 获得数据库最新的数据。 6、ODBC 数据源的访问 前提： 计算机中需要有数据库相应的ODBC驱动 程序，如果没有(如MYSQL)，可以到该数据 库的官方网站进行下载并安装。 EXCEL在数据分析中的应用第91页 4.5 关于数据链接 访问步骤： 1、 创建ODBC数据源:控制面板-管理工具-数据源（ ODBC）-文件DSN-选择ODBC驱动程序如ORACLE IN ORAHOME92 ， MICROSOFT ODBC FOR ORACLE-输 入数据源名称及保存位置-直到完成 2、 在EXCEL中 “数据”“自其他来源”“来自 MICROSOFT QUERY ”选择刚建立的数据源 EXCEL在数据分析中的应用第92页 4.5 关于数据链接 7、MICROSOFT QUERY MICROSOFT QUERY是一个功能强大而且灵 活的应用程序，通过它可以检索外部数据库中的 数据，在EXCEL中可以调用它，将外部数据库中 的数据（甚至是数据库中的符合条件的数据）导 入EXCEL。 8、访问外部数据库的条件 具有访问外部数据库的权限、安装 MICROSOFT QUERY、建立ODBC或其他数据源驱 动程序 。通过WEB查询不需安装MICROSOFT QUERY，但须有连接网络的各种设备和身份，还 须有可运行的WEB查询文件（.IQY） 。 EXCEL在数据分析中的应用第93页 4.5 关于数据链接 9、OLE DB数据源的访问 所有的ODBC数据源都可以以OLE DB方式导入 提供了OLE DB驱动程序的数据库可以以OLE DB方式 导入 访问步骤： 在EXCEL中 “数据”“来自数据连接向导”其他/高级 选择OLE DB驱动程序-输入必要信息。 EXCEL在数据分析中的应用第94页 4.5 关于数据链接 10、EXECL可直接访问的数据源 可以在EXCEL和文本文件（如记事本）或 ACCESS数据库的表记录间复制粘贴。但这种数 据是“死”的数据。 如在EXCEL中链接到ACCESS数据库，ACCESS 数据库中的数据更新后，在EXCEL中可“刷新”得 到最新数据。 方法：数据自文本 或数据自ACCESS。 EXCEL在数据分析中的应用第95页 新增的内容 5 构造动态数据分析报表 学 习目标 1 理解EXCEL表的的概念，学会建立 EXCEL表的方法。 2 学会使用结构化引用建立动态报表。 3 学会使用D函数和结构化引用建立 动态报表。 EXCEL在数据分析中的应用96 5 构造动态数据分析报表 5.1 表与动态报表 5.2 D函数与动态报表 EXCEL在数据分析中的应用97 5.1 表与动态报表 5.1.1 表的基本概念及其 操作 1、问题的提出 所谓动态报表就是指数据区域的大小可能会随时间而 变化的工作表。在数据的统计与分析中，经常需要构造 动态报表。 以“函数应用”工作表为例，随着时间的推移，当“销售 记录汇总”表增加或减少数据行时，原来的公式就不能自 动适应，从而就不能得到正确的分析结果。对于前面介 绍的透视表和透视图，同样有类似问题。只有更新透视 表的数据源方到最新分析结果。 EXCEL中，可以通过EXCEL专用表（简称表或表格）快 捷地构造出动态报表，并能方便地对动态报表进行各种 统计分析。 EXCEL在数据分析中的应用98 5.1 表与动态报表 2、什么是表？如何建立表 表也称表格，是EXCEL的特殊对象（即03版本的列表、数 据清单 ）它是一个完整的结构，包含表区域、表数据区 域、汇总行、标题 行、列标题 、调整大小控制点等内容。 表具有数据筛选、排序、汇总和计算等功能，并能自动 扩展数据区域，查通过表来构造动态报表。 表（表格）工作表（区域），它有许多特性，有“表工 具”选项卡。 建立表的方法： 选中工作表中的数据区域（也可以是空白区域），“插入” “表”创建表对话框 。注意列必须要有标题 。 一个工作表中可以插入多个表。 也通过自动套用表样式也能将区域转化为表。 EXCEL在数据分析中的应用99 5.1 表与动态报表 3、关于表样式 EXCEL为表预定义了许多格式，称为表样式，它包 括颜色、边框线、底纹等诸多格式化样式，格 式化工作表最直接的方式就是套用表样式。 在套用表样式时，EXCEL会将区域转化为表。 为工作表自动套用表样式的方法： 单击任一单元区域，开始样式套用表格 样式选择一种样式确定；在“表工具”“设 计”中，提供了许多选项。 表转换为区域： 可通过“表工具”“设计”“工具”“转换为区域 ”区域把表转换为区域。 EXCEL在数据分析中的应用100 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.2 D函数与动态报表 1、D函数 EXCEL把每个列都有标题的数据表称为数据库，并提 供了12个专用函数来简化对这种表的统计和查找工 作，这些函数均以D打头，故称D函数。如DSUM、 DAVERAGE 、DCOUNT、DMAX,DMIN、DPRODUCT 等。D函数的调用格式、形式参数表都 相同。语法 格式如下： DNAME(单元格区域，要计算的列字段，条件区) 其中： 单元格区域，要求每列都有列标题。 列字段，是某列的列标题。 条件区的含义和构造方法同高级筛选。 5.2 D函数与动态报表 2、D函数与表结合构造动态数据分析表 可以在D函数中对表进行访问，通过在D函数中 对表的结构化引用，能够轻松地计算出动态报表 中的各项统计分析数据。 5.2 D函数与动态报表 2、D函数与表结合构造动态数据分析表 任务2的操作 ： 在“动态报表”中完成“统计各客户本期购买各 系列产品的金额”。 D4公式为： =SUMIFS(销售表销售额,销售表客户名称,动态 报表!$C4,销售表系列,动态报表!D$3) 公式也可 以是： =SUM(IF(销售表客户名称=动态报表!$C4,IF(销 售表系列=动态报表!D$3,销售表销售额) 按CTRL+SHIFT+ENTER键输入公式，然后复 制到其他单元 格。 5.2 D函数与动态报表 任务3： 在“动态报表”中完成“各系列产品的销售 额之和”。 D19的公式为： = DSUM(销售表#全部,销售表#标题 ,销售额,动态报表!D17:D18) 按CTRL+SHIFT+ENTER键，然后复制 到其他单元格。 注意：第2个参数必须是列标题。 5.2 D函数与动态报表 3、D函数本身具有对动态数据表进行自动计算的能 力，它与EXCEL数据库结合也能实现 动态计算 首先，工作表每列都要有标题，是EXCEL数据库 ； 其次，D函数的第1 个参数引用的普通EXCEL数据 库区域要足够大（包括空白单元格），这样，当增 加数据行时，D函数可以扩展计算。 示例：“动态报表”中求本表中“观音饼”交易金额之 和。 注意： D函数对表的结构化引用 ，可以实现完全自动的 动态数据分析， 而D函数对普通EXCEL数据库（区域）单元格的引用 ，只有当数据不超过第1 个参数指出的范围时，才能动 态计算，不如D函数结构化引用来得方便。 5.2 D函数与动态报表 概念整理 （1）工作表、区域 （2）EXECEL数据库 （3）表（表格） （4）动态报表 （5）结构化引用 （6）D函数对于EXCEL数据库和数据库专用 表，均能动态计算（前者受限）。 5.2 D函数与动态报表 对动态报表进行动态分析的途径 工作表 （