Excel2007在数据处理与分析中的应用课件.ppt

1,excel2007在数据处理与分析中的应用,excel在数据分析中的应用,excel2007是office软件中的核心成员，是最优秀的电子表格软件之一，具有强大的数据处理和数据分析能力，是个人及办公事务中进行表格处理和数据分析的理想工具之一。 如何利用excel2007的函数、图表、高级分析工具、vba程序等功能进行数据分析是本次学习的重点。,内容提要,2 利用函数进行数据分析 p29,1 数据处理与分析基础 p4,4 利用透视表（图）进行数据分析 p72,3 利用图表进行数据分析 p42,5 构建动态数据分析报表 p96,6 宏与vba在数据分析中的应用 p114,7 excel 的数据分析工具简介 p146,1、数据处理与分析基础,学习目标 1 认识excel2007的功能与界面 2 学会利用数据条件格式进行数据处理 3 学会利用排序、筛选、分类汇总功能进行数据分析,4,excel在数据分析中的应用,1、数据处理与分析基础,1.1 excel2007的功能与界面 1.2 数据的输入、编辑与运算 （案例：销售产品基本信息表、销售记录汇总表） 1.3 利用数据条件格式进行数据处理 1.4 利用排序、筛选、分类汇总功能进行数据分析,1.1 excel2007的功能与界面,1.1.1 excel2007新增的主要功能 （1）取消了菜单方式，采用了面向结果的用户界面，易于找到。 （2）更强大的数据管理能力和安全性。（如更多的行和列104857616384，1600万种颜色） （3）更强大的表功能，提供了全新的数据引用方式，称为结构化引用，可以方便地构造动态数据报表。 （4）其他方面： 提供了大量预定义主题和样式 丰富的条件格式 自动调整编辑栏 函数记忆式输入,1.1 excel2007的功能与界面,1.1.1 excel2007新增的主要功能 改进的筛选和排序功能（可按日期和颜色排序） 图表外观更美观、更专业、布局和样式更多 易于使用的数据透视表、数据透视图 快速连接外部数据源,1.1 excel2007的功能与界面,1.1.2 excel2007的用户界面 整个界面由功能区和工作表区组成； 功能区有：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/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 利用排序、筛选、 分类汇总功能进行数据分析,知识点： 注意： excel2007对排序字段 不再局限于3个； 不论升降序，空行总在排 在最后； excel2007可以按单元格颜色排序。 思考：排序之后如何回到排序前的状态。（必要时引入辅助字段）,1.4 利用排序、筛选、 分类汇总功能进行数据分析,1.4.2 自动筛选 概述： 筛选就是只把满足条件的数据行显示出来，而把不关注的数据隐藏。 筛选有自动筛选和高级筛选两种。自动筛选易于使用，高级筛选条件可以更复杂。 任务5：对销售记录汇总表操作 （1） 查看特定系列产品（如“观音酥”）的销售情况（自动）筛选 （2）查看特定用户（如“好又多”）、特定系列产品（如“旅游产品 ” ）的销售情况组合自动筛选,1.4 利用排序、筛选、 分类汇总功能进行数据分析,1.4.2 自动筛选 知识点： 欲选择某列中一个项止，先取消本列中的“全选”复选框，然后再单选 ； 注意状态栏提示（筛选出的个数）、列标右侧的图标显示； 筛选是累加的，后一次筛选在前一次基础上进行； 解除一列筛选：按“全选”；全部解除：按“筛选”按钮。,22,excel在数据分析中的应用,1.4 利用排序、筛选、 分类汇总功能进行数据分析,1.4.3 高级筛选 概述：可实现多字段间的“或”条件筛选，并能将结果复制到其他区域。 任务6： 查看一段时间内（如2007年 8月上旬）特定用户（如“好又多”）的销售情况 此任务可用高级筛选或自动筛选完成。,23,excel在数据分析中的应用,1.4 利用排序、筛选、 分类汇总功能进行数据分析,1.4.3 高级筛选 知识点： 1、条件的写法（日期条件中用的是日期的数值） 2、取消高级筛选单击“清除” 思考： 要筛选出销售数量不低于100，或者毛利不小于2000元的数据怎么做。,24,数量 毛利 =100 =2000,发货日期 发货日期 客户名称 =39295 =39304 好又多,excel在数据分析中的应用,1.4 利用排序、筛选、 分类汇总功能进行数据分析,1.4.4 分类汇总 概述：分类汇总能够对工作表数据按不同的类别进行汇总，并通过分级显示方式展现数据。 任务7：对销售记录汇总表操作 按“系列”统计销售额、总成本、毛利等数据分类之和 知识点： 1、先按分类字段“系列”排序 2、数据|分级显示|分类汇总(分类字段、汇总方式、汇总项) 3、删除分类汇总：分类汇总|全部删除 4、分级显示的使用与取消,25,excel在数据分析中的应用,1.4 利用排序、筛选、 分类汇总功能进行数据分析,任务8 统计本期哪位客户购货数量最多 知识点： 1、先按“客户名称”字段排序 2、数据|分级显示|分类汇总 3、对分类汇总后的结果进行排序（按数量排序）,26,excel在数据分析中的应用,1.4 利用排序、筛选、 分类汇总功能进行数据分析,1.4.5 高级分类汇总 概述： 高级分类汇总就是按同一类别进行不同字段或不同汇总方式的汇总。 任务9：对销售记录汇总表操作 按“客户名称”统计汇总销售额总和、总成本总和、毛利平均值 知识点： 1、先按分类字段“客户名称”排序 2、数据|分级显示|分类汇总|选汇总项目和方式 3、数据|分级显示|分类汇总|选另外的汇总项目和方式 注意：不选择“替换当前分类汇总”,27,excel在数据分析中的应用,1.4 利用排序、筛选、 分类汇总功能进行数据分析,1.4.6 嵌套分类汇总 概述：嵌套分类汇总就是在一种分类汇总基础上再进行不同类别的分类汇总。 任务10：对销售记录汇总表操作 按“客户名称”统计汇总销售额总和、总成本总和、毛利总和，以及某客户不同“系列”的销售数量之和。 知识点： 1、先按多个分类字段排序（客户名称+系列） 2、数据|分级显示|分类汇总|选分类字段“客户名称”，汇总方式求和，汇总项为销售额、总成本、毛利。 3、数据|分级显示|分类汇总|选分类字段“系列”，选汇总方式求和，汇总项目为数量。 注意：不选择“替换当前分类汇总”,28,excel在数据分析中的应用,2 利用函数进行数据分析,学习目标 1 掌握函数的输入技巧和获取帮助的手段 2 掌握sumif、sumifs、sumproduct等函数的应用,29,excel在数据分析中的应用,2 利用函数进行数据分析,2.1 函数概述 2.2 sumif函数的应用 2.3 sumifs函数的应用 2.4 使用sumproduct函数分析数据,30,excel在数据分析中的应用,2 利用函数进行数据分析,2.1函数概述 函数就是预定义的公式。它使用一些参数进行运算并产生返回结果。 excel中有350多个内置函数，分数学与三角函数、财务函数、统计函数、工程函数等11大类，用户还可使用vba自定义函数。 函数可分为有参数函数和无参数函数两大类。 在公式和自定义宏中可以调用函数。 函数可以嵌套调用。 如： =if( average(a2:a5)50, sum(b2:b5), 0 ) =if(a2=10,“科技处”,if(a2=20,“财务处”,“人事处”),31,条件,取真时,取假时,excel在数据分析中的应用,2 利用函数进行数据分析,2.2 sumif函数的应用 任务1：新建一个工作表，命名为“函数应用“，统计各客户购买数量、购买额等数据之和。 知识点：sumif函数 （1）输入 要分析的数据栏目 （从销售记录汇总表挑选字段粘贴而来） （2）输入 客户名称 的技巧（将分类汇总结果复制到别处） （3）使用sumif函数计算各个字段。 （4）使用排名函数rank按毛利对客户排名。,32,excel在数据分析中的应用,相关知识：sumif函数,sumif（条件判断区域，条件，求和区域） 根据指定条件对若干单元格求和。条件可以是数字、表达式或文本，如：“北京”、“=50”等，要使用引号。也可以是单元格引用。 案例中c5处（数量）的公式： =sumif(销售记录汇总!$a$2:$a$107,$b5,销售记录汇总!$g$2:$g$107) 复制到右侧单元格，并修改最后一项中的列号，依次为ikl列。 （sumif其他例子见“综合实例”工作薄）,33,求和区域,条件区域,条件值,excel在数据分析中的应用,相关知识：rank函数的使用,格式： rank（要排位的数值，排位区域，选项） 功能： 返回一个数值在一组数值中的排位。 选项： 非0： 升序排位 0或省略：降序排位,34,excel在数据分析中的应用,2 利用函数进行数据分析,2.3 sumifs函数的应用 任务2：用sumifs函数对工作表“函数应用”，统计各客户购买数量、购买额等数据之和。 知识点：sumifs函数 （1）“数量”的公式为 =sumifs(销售记录汇总!$g$2:$g$107,销售记录汇总!$a$2:$a$107,b$19),35,求和区域,条件区域,条件值,excel在数据分析中的应用,相关知识：sumifs函数的使用,格式： sumifs（求和区域，条件范围1，条件 1，条件范围2 ） 功能： 对某一区域内满足多重条件的单元格求和，最多127个条件。条件可以是“18”,”学生”，b4等形式。 sumifs详细使用见“综合实例”工作薄中的“sumifs用法举例”。 类似的： excel2007也提供了averageif, averageifs,countif等函数。,36,excel在数据分析中的应用,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),37,excel在数据分析中的应用,2 利用函数进行数据分析,任务4： 在“函数应用”工作表中，统计各客户本期购买各系列产品的销售额。使用sumproduct函数。 知识点： （1）？处公式为： = sumproduct(销售记录汇总!$a$2:$a$107=$b41)*(销售记录汇总!$e$2:$e$107=c$40),销售记录汇总!$i$2:$i$107) （2）sumproduct详细使用见“综合实例”工作薄中的“sumproduct用法举例”。,38,excel在数据分析中的应用,相关知识：sumproduct函数的使用,格式： sumproduct（数组1，数组2，数组3 ） 功能： 可对几个数组间的对应元素相乘，并返回 乘积之和。 其基本应用是两个数组间对应元素相乘再求和。,39,c3中公式 =sumproduct(a1:a3, b1:b3)结果为14,excel在数据分析中的应用,2 利用函数进行数据分析,任务5： 任务同上。但要求使用sumifs函数实现。 知识点： （1）？处公式为： sumifs(销售记录汇总!$i$2:$i$107,销售记录汇总!$a$2:$a$107,b$57,销售记录汇总!$e$2:$e$107,c$56) (2) sumifs用法。,40,求和区域,条件区域1,条件值1,条件值2,条件区域2,excel在数据分析中的应用,2 利用函数进行数据分析,任务6：在“函数应用”工作表中，统计某时段的销售额 使用sumproduct 函数。 知识点： 上旬公式为： sumproduct(销售记录汇总!$c$3:$c$410=39295)*(销售记录汇总!$c$3:$c$410=39304)*销售记录汇总!$n$3:$n$410) 中旬与下旬类似，只是数值不同。 思考： 什么情况下要使用sumproduct函数？,41,excel在数据分析中的应用,3 利用图表进行数据分析,学习目标 1 了解图表的类型及其组成部分 2 掌握图表的建立、编辑与美化方法 3 理解复杂图表的建立方法 4 了解动态图表的建立方法,42,excel在数据分析中的应用,3 利用图表进行数据分析,3.1 图表概述 3.2 图表的建立、编辑与美化 3.3 复杂图表的建立 3.4 动态图表的建立,43,excel在数据分析中的应用,3.1 图表概述,图表是excel中一个强有力的工具，利用图表可以使数据更加直观地显示出来。excel有丰富的图表类型，其功能不逊于某些专业图表软件。 了解图表有关术语和组成部分是正确使用图表的前提。 1、有关术语 （1）数据点：即数据标记，本质上是一个单元格中的数值图形表示，不同类型的图表数据点形状不同，柱形图中表现为一个柱形，折线图中为一个点，面积图中为一扇形区域。 （2）数据系列：图表中一组相关的数据点，它来自于数据表的某行或某列，每个数据系列有相同的颜色和图案，并且通过图例标识。一张图表有多个数据系列，但饼图只有一个。 （3）数据标签：是指为数据系列或数据点添加的标识，默认情况下图表没有数据标签，用户可以添加数据标签、并修改它的位置和大小。,44,excel在数据分析中的应用,3.1 图表概述,有关术语： （4）网格线：用于查看和评估数据，有水平和垂直两种。 （5）轴：作为绘图区一侧边界的直线，有x轴也称分类轴，y轴也称数值轴两种。 （6）刻度线与刻度标志：类似于尺子上的刻度。 （7）图例：用于说明每个数据系列中的数据点所采用的图形外表。 （8）标题：有图表标题、分类轴标题和数值轴标题。识别图表区域的技巧： 单击图表上不同区域，观看：图表工具布局当前所选内容。,45,excel在数据分析中的应用,3.1 图表概述,2、最常见的图形介绍 （1）柱形图：用于显示某段时间内数据的变化，或比较各数据项之间的差异。分类一般为日期或时间，数值在垂直方向组织，以便于强调相对于时间的变化。 （2）条形图：也用于显示各数据之间的比较。与柱形图不同的是，其分类在垂直方向（一般为日期或时间），而数值在水平方向，使观察者的注意力集中在数据值的比较上，而不在时间上。 （3）折线图：主要用于显示各数据随时间而变化的趋势情况。横坐标几乎总是表现为时间。 （3）饼 图：用于显示组成数据系列的各数据项与数据项总和的比例。当只有一个数据系列，并且用于强调整体中的各个组成部分占整体的比例时十分有效。,46,excel在数据分析中的应用,3.2 图表的建立,任务1： 对前面一章任务5中工作表中 的结果 ，制作 各系列产品销售额百分比分离型三维饼图。 知识点： （1）建立“总计”行，写入公式：sum（范围） （3）选择上下数据两行插入饼图分离型三维饼图； （4）输入图表标题“各系列产品销售额占比分析”； （5）添加百分比数据标签； 图表工具-布局-其他数据标签选项选择：类别名称、百分比；选择：数字百分比2位小数。 （6）删除图例,47,excel在数据分析中的应用,3.2 图表的建立,任务2 ： 仍对前面的工作表中，制作分离型三维饼图，分析从各客户获取利润的百分比。 要求： （1）图表标题为“从各客户获取利润占比分析”。 （2）图例在下方。 思考：如何选择数据源？,48,excel在数据分析中的应用,3.2 图表的建立,归纳 ：建立图表的步骤 （1）首先选择数据源。要选定图表数据所在的单元格，如希望数据的行、列标志也显示在图表中，则选定区还应包括行/列标题。 （2）应用预定义的图表布局和样式设置图表。方法：图表工具布局/样式。 （3）创建图表工作表。选中图表，设计位置移动图表。（可将嵌入式图表变成图表工作表，并可移回 ）,49,excel在数据分析中的应用,3.3 复杂图表的建立,1、概述： 标准图表在数据分析中有时仍不能满足实际需要，用户可能需要根据实际情况制作复杂一些的图表，例如在图表中添加涨跌柱线、垂直线、系列线和高低点连线，给 图表增加趋势线等，也可以运用一些技巧制作复杂图表，如：组合图表、双轴图表、复合饼图、甘特图、图片图表、趋势图等。,50,excel在数据分析中的应用,3.4 复杂图表的建立,2、在图形上增加趋势线 趋势线简介：趋势线以图形方式显示数据的发展趋势，常用于预测分析（也称回归分析，在图表中扩展趋势线，根据实际数据预测未来数据），条形图、折线图、柱形图、股价图、气泡图、xy散点图、非堆积型二维面积图都 支持趋势线。 任务3 ：根据下表的数据作出图，并根据折线图作出趋势线（线性、指数、对数等不同类型的趋势线），并利用趋势函数预测五月份数据。,51,excel在数据分析中的应用,3.3 复杂图表的建立,在折线图上增加趋势线效果图,52,excel在数据分析中的应用,折线图增加趋势线制作步骤,知识点：操作步骤 （1）先作折线图。 （2）右击数据系列添加趋势线，在趋势线选项中选择一种类型，并“显示公式”。 （3）根据趋势线函数预测5月 份数据，x取第5个数据点： y=103.5*5+6218=6735.5 这是5月 份数据的预测值。 其他类型的趋势线制作方法与此类似。,53,excel在数据分析中的应用,3.4 复杂图表的建立,3、组合图表：在一个图表中表示两个甚至多个数据系列，而不同数据系列用不同的图表类型表示。 任务4 ：组合图表（柱形-折线）的制作，基础数据如下表。,54,excel在数据分析中的应用,3.3 复杂图表的建立,组合图表（柱形-折线）图的制作效果图,55,excel在数据分析中的应用,组合图表制作步骤,知识点：操作步骤 （1 选中数据区域。 （2）插入图表柱形图 （3）将数值小的系列右击变为折线图,56,excel在数据分析中的应用,3.4 复杂图表的建立,4、双轴图表 即双数据坐标轴图表，是指在一个图表中在主、次坐标轴上分别绘制一个或多个数据系列，它们采用两种不同的坐标轴值来度量数据。 任务5 ：双轴图表的制作，基础数据如下表。,57,excel在数据分析中的应用,3.3 复杂图表的建立,双轴图表的制作效果图,58,excel在数据分析中的应用,双轴图表制作步骤,知识点：操作步骤 （1）选中数据区域。 （2）插入图表柱形图 （3）将数值小的系列右击变为折线图 （4）在折线图上右击，设置数据系列格式系列选项次坐标轴。 （5）添加图表标题，两个坐标轴标题 ，设置字体。,59,excel在数据分析中的应用,3.3 复杂图表的建立,5、复合饼图：饼图可以用来表达单个数据与整体结果之间的比例关系，而复合饼图还可以对数据进行深层次分析。 任务6：复合条饼 图的制作，基础数据如下表。,60,excel在数据分析中的应用,3.3 复杂图表的建立,复合条饼 图的制作效果图,61,excel在数据分析中的应用,复合饼图制作步骤,知识点：操作步骤 （1）选择数据区，插入“图表“饼 图”“复合条饼图”，设置数据标签（百分比、显示引导线） （2）右击主饼 图设置数据系列格式系列选项将”第二绘图区“2改为3。 （3）优化图表，选中图例删除 ，双击标题、百分比标签，并修改其内容、设置字体。,62,excel在数据分析中的应用,3.4 动态图表的建立,概述 所谓动态图表，是指图表的数据源可以根据需要动态变化，从而使数据图表也随之变化，一般有三种方式： 1、利用有关函数设置动态区域； 2、通过定义数据区域名称，并引入辅助数据区域； 3、利用动态控件链接图表中的引用数据，以实现用户的自由选择。,63,excel在数据分析中的应用,3.4 动态图表的建立,任务7 ：利用choose函数和组合框建立动态图表,64,excel在数据分析中的应用,3.4 动态图表的建立,利用choose函数和组合框建立动态图表效果图,65,excel在数据分析中的应用,3.4 动态图表的建立,知识点：操作步骤 （1）在原数据区外建立动态数据区域，作为数据源 复制地区列到i3，在j3输入公式： =choose($m$3,c4,d4,e4,f4,g4)，要根据m3内容显示相应内容，向下复制公式。 （2）转置复制“余额”等列标题到l3 （3）开发工具控件插入表单控件组合框，右击设置控件格式控制数据源区域/单元格链接，下拉项数，将组合框拖入图表区。 （4）以动态数据区域建立柱形 图并格式化。 （5）从组合框选择项目，观看柱形图变化。,66,excel在数据分析中的应用,3.4 动态图表的建立,知识点：相关知识公式解释 choose函数的功能是从值的列表中返回一个值 。 如： choose（2，”a1”,”a2”,”a3”,”a4”）则返回a2，其中第一参数2为序号。,67,excel在数据分析中的应用,3.4 动态图表的建立,任务8 ：利用indirect等函数建立动态图表,68,excel在数据分析中的应用,3.4 动态图表的建立,利用indirect等函数建立动态图表效果图,69,excel在数据分析中的应用,3.4 动态图表的建立,知识点：操作步骤 （1）在原数据区外建立动态数据区域，作为数据源 （2）复制标题行到动态数据区域 （3）在“地区”下b15输入 公式： =indirect(address(cell(“row“),column(b4) 向右方各列复制 （4）以动态数据区域建立饼 图，单击任一地区名称，按f9键，即显示指定地区的饼 图。,70,excel在数据分析中的应用,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。,71,excel在数据分析中的应用,4 利用数据透视表（图） 进行数据分析,学习目标 1 掌握数据透视表、图的概念和术语。 2 学会建立数据透视表和数据透视图 3 了解excel的数据链接和访问外部数据源的一般方法。,72,excel在数据分析中的应用,4 利用数据透视表（图） 进行数据分析,4.1 数据透视表简介 4.2 数据透视表的建立 4.3 数据透视表的美化 4.4 数据透视图 4.5 关于数据链接,73,excel在数据分析中的应用,4.1 数据透视表简介,1、数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格，是一个功能强大的数据分析工具。 2、数据透视表的显著特点是对数据进行动态分析，只需要改变字段的位置，即可得到多种分析结果。因此字段的设置是关键。,74,excel在数据分析中的应用,4.1 数据透视表简介,3、数据透视表的主要功能 （1）对数值数据进行分类汇总和聚合，按分类和子分类对数据进行汇总； （2）展开或折叠要关注结果的数据级别，查看感兴趣区域摘要数据的明细； （3）将行移动到列或将列移动到行（或透视），以查看源数据不同组合与汇总的结果； （4）对最有用和最关注的数据子集进行筛选、排序、分组和有条件地设置格式，突出显示重要信息； （5）提供简明、带有批注的联机报表 或打印报表。,75,excel在数据分析中的应用,4.1 数据透视表简介,4、何时使用数据透视表？ 如果要对工作表的数据进行行、列变换，或者要在数据量较大的工作表中进行数据的多种对比分析，就应该使用数据透视表。 数据透视表也能完成排序、分类汇总和计数统计等方面的工作。,76,excel在数据分析中的应用,4.1 数据透视表简介,5、数据透视表字段列表的运用 （1）行标签。该字段 的一个数据项占一行，相当于x轴。 （2）列标签。该字段 的一个数据项占一列，相当于y轴。 （3）报表筛选。由该字段 确定一个二维表，相当于z轴。 按该字段对透视表分页。 （4）数值 。该字段 中的数据完成指定的计算，如计数、求和、求平均等。,77,excel在数据分析中的应用,4.2 数据透视表的建立,任务1： 对“销售记录汇总”表分析各客户各系列产品的销售情况 知识点： 方法1： （1）光标放在数据区，插 入数据透视表数据区确定（在一个新的工作表中建立了透视表） （2）设置行字段区：客户名称、系列。,78,excel在数据分析中的应用,4.2 数据透视表的建立,方法2： （1）在一个新的工作表中，插入数据透视表使用外部数据源选择连接浏览更多我的电脑选磁盘、文件夹、工作薄、工作表放置位置。 （2）设置行字段区：客户名称、系列。,79,excel在数据分析中的应用,4.2 数据透视表的建立,知识点： （3）设置数值区：数量、销售额、总成本、毛利等 （4）设报表筛选区：发货日期 （5）重设布局。让行标签水平显示（默认情况下在行标签下的字段都逐一显示在首行标签下。下拉-以大纲形式显示项目标签/以表格形式显示）。 （6）美化。数据透视表工具设计-数据透视表样式 （7）从筛选处下拉。选择单个日期或“选择多项”。 （8）改名工作表“透视表应用”,80,excel在数据分析中的应用,4.2 数据透视表的建立,任务2分析各系列产品的销售情况 知识点： 调整行标签，让“系列”在“客户名称“上面即可。,81,excel在数据分析中的应用,4.3 数据透视表的美化,（1）对选中的区域：开始字体边框/填充颜色，以添加边框效果或填充颜色； （2）设计数据透视表样式其他 （3）设置报表筛选字段，可以根据筛选条件来显示数据，可添加多个筛选字段。 （4）可以行/列/数值标签中删除或增加字段。标签中字段次序可调整。 （5）数值字段的计算方式可改变。,82,excel在数据分析中的应用,4.3 数据透视表的美化,（6）可控制显示/不显示分类汇总和总计（分类汇总是每小组的小计，而总计是一行或一列的总计）。 （7）可对选中的字段排序显示。 （8）可对行标签“选择字段”或选择“值筛选”。 （9）创建好透视表后，可更改数据源以创建不同效果的透视 表。选项数据更改数据源。 （10）清除全部透视 表数据。选项操作-清除。 （11）复制透视表至别处。选项操作选择整个数据透视表复制/粘贴 。,83,excel在数据分析中的应用,4.4 数据透视图,1、概述 数据透视图是根据透视表的结果转化而来的，透视表与透视图相关联，它们彼此有对应的字段。透视表中的字段改变了，透视 图也会变化。 数据透视图与常规图表的意义和操作方法基本相同，其主要不同是透视图是一种动态图表，一张透视图实际上是一系列图表，图表内容可根据数据项的变化而变化。,84,excel在数据分析中的应用,4.4 数据透视图,2、数据透视表图的生成与编辑 任务3： 修改前面的透视表并转化为透视图，字段安排如下： （1）报表筛选：发货日期，行标签：客户名称，数值：毛利求和，分离型三维饼形图； （2）报表筛选：日期，行标签：客户名称，数值：销售额求和、总成本求和、簇状柱形图； （3）行标签：发货日期，数值：毛利求和，分离型三维饼形图； （4）报表筛选：日期，行标签：客户名称，列标签：系列，数值：销售额求和，簇状柱形图。,85,excel在数据分析中的应用,4.4 数据透视图,2、数据透视表图的生成与编辑 将透视表转化为透视图后，会自动在工作表中生成“数据透视图筛选窗格”，通过设置该窗格中的条件，可以生成不同的透视 图。 数据透视图的主要编辑有：编辑图表标题，坐标轴标题，图例，数据标签，坐标轴刻度，数据系列的样式，更改数据源、更改图表类型 数据透视图的美化设置、图标设置、纵坐标轴设置、图例设置等，与普通图表的设置类似。,86,excel在数据分析中的应用,4.4 数据透视图,3、图表布局与图表样式 图表布局是指图表中各对象的摆放位置。 图表样式是指设置图表效果的一种样式方案。 excel提供了多种布局和样式方案可供选择。 方法是： 主菜单：数据透视工具设置图表布局； 主菜单：数据透视工具设置图表样式。 新增练习：对“综合实例”工作薄，透视 表图工作表操作： （1）各省各民族 人数。（2）各班各民族 人数 （3）各班生源分布（4）各省生源政治面目情况。,87,excel在数据分析中的应用,4.5 关于数据链接,1、外部引用 在一个工作薄中引用另一工作薄中的数据称外部引用，也叫链接。前者称目标工作薄，后者称源工作薄。 链接使一个工作薄可以共享另一工作薄中的数据。打开目标时，源可打开也可关闭，如果先打开源后打开目标，数据会自动更新。用户可以自己适时刷新数据。 当修改源数据时，excel会通过链接自动修改链接工作薄中的数据。 举例： 利用外部数据源（如数据源在另外一个工作表）生成透视表。（各客户销售额统计）,88,excel在数据分析中的应用,4.5 关于数据链接,2、excel与外部数据库 企业数据往往以数据库或特殊文件（如xml、txt）形式存储在服务器、网站 或某些办公室的计算机中，如果将这些数据导入到excel，就能利用excel进行数据处理和分析 ，同时 excel也可以作为数据库系统的数据采集工具。,第89页,excel在数据分析中的应用,4.5 关于数据链接,3、excel可访问的外部数据库 包括sql server,oracel, access,foxpro等。 4、数据源 数据源就是数据的来源，是其他软件如excel访问数据库的一组信息，包括数据库服务器的名称和位置，用于连接数据库的驱动程序名称，以及登录到数据库时需 要的各种信息。excel可通过数据源访问各种关系型 数据库。 5、数据源的类型主要有 odbc 数据源 ole db数据源,第90页,excel在数据分析中的应用,4.5 关于数据链接,另外，excel可以直接访问文本文件和 access数据库。 数据源的数据更新后，可通过在excel中右击数据列表后的菜单选择“刷新” 以获得数据库最新的数据。 6、odbc 数据源的访问 前提： 计算机中需要有数据库相应的odbc驱动程序，如果没有(如mysql)，可以到该数据库的官方网站进行下载并安装。,第91页,excel在数据分析中的应用,4.5 关于数据链接,访问步骤： 1、 创建odbc数据源:控制面板-管理工具-数据源（odbc）-文件dsn-选择odbc驱动程序如oracle in orahome92 ， microsoft odbc for oracle-输入数据源名称及保存位置-直到完成 2、 在excel中 “数据”“自其他来源”“来自microsoft query ”选择刚建立的数据源,第92页,excel在数据分析中的应用,4.5 关于数据链接,7、microsoft query microsoft query是一个功能强大而且灵活的应用程序，通过它可以检索外部数据库中的数据，在excel中可以调用它，将外部数据库中的数据（甚至是数据库中的符合条件的数据）导入excel。 8、访问外部数据库的条件 具有访问外部数据库的权限、安装 microsoft query、建立odbc或其他数据源驱动程序 。通过web查询不需安装microsoft query，但须有连接网络的各种设备和身份，还须有可运行的web查询文件（.iqy） 。,第93页,excel在数据分析中的应用,4.5 关于数据链接,9、ole db数据源的访问 所有的odbc数据源都可以以ole db方式导入 提供了ole db驱动程序的数据库可以以ole db方式导入 访问步骤： 在excel中 “数据”“来自数据连接向导”其他/高级选择ole db驱动程序-输入必要信息。,第94页,excel在数据分析中的应用,4.5 关于数据链接,10、execl可直接访问的数据源 可以在excel和文本文件（如记事本）或access数据库的表记录间复制粘贴。但这种数据是“死”的数据。 如在excel中链接到access数据库，access数据库中的数据更新后，在excel中可“刷新”得到最新数据。 方法：数据自文本 或数据自access。,第95页,excel在数据分析中的应用,新增的内容,5 构造动态数据分析报表,学习目标 1 理解excel表的的概念，学会建立excel表的方法。 2 学会使用结构化引用建立动态报表。 3 学会使用d函数和结构化引用建立动态报表。,96,excel在数据分析中的应用,5 构造动态数据分析报表,5.1 表与动态报表 5.2 d函数与动态报表,97,excel在数据分析中的应用,5.1 表与动态报表,5.1.1 表的基本概念及其 操作 1、问题的提出 所谓动态报表就是指数据区域的大小可能会随时间而变化的工作表。在数据的统计与分析中，经常需要构造动态报表。 以“函数应用”工作表为例，随着时间的推移，当“销售记录汇总”表增加或减少数据行时，原来的公式就不能自动适应，从而就不能得到正确的分析结果。对于前面介绍的透视表和透视图，同样有类似问题。只有更新透视表的数据源方到最新分析结果。 excel2007中，可以通过excel专用表（简称表或表格）快捷地构造出动态报表，并能方便地对动态报表进行各种统计分析。,98,excel在数据分析中的应用,5.1 表与动态报表,2、什么是表？如何建立表 表也称表格，是excel的特殊对象（即03版本的列表、数据清单 ）它是一个完整的结构，包含表区域、表数据区域、汇总行、标题 行、列标题 、调整大小控制点等内容。 表具有数据筛选、排序、汇总和计算等功能，并能自动扩展数据区域，查通过表来构造动态报表。 表（表格）工作表（区域），它有许多特性，有“表工具”选项卡。 建立表的方法： 选中工作表中的数据区域（也可以是空白区域），“插入”“表”创建表对话框 。注意列必须要有标题 。 一个工作表中可以插入多个表。 也通过自动套用表样式也能将区域转化为表。,99,excel在数据分析中的应用,5.1 表与动态报表,3、关于表样式 excel为表预定义了许多格式，称为表样式，它包括颜色、边框线、底纹等诸多格式化样式，格式化工作表最直接的方式就是套用表样式。 在套用表样式时，excel会将区域转化为表。 为工作表自动套用表样式的方法： 单击任一单元区域，开始样式套用表格样式选择一种样式确定；在“表工具”“设计”中，提供了许多选项。 表转换为区域： 可通过“表工具”“设计”“工具”“转换为区域 ”区域把表转换为区域。,100,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.2 d函数与动态报表,1、d函数 excel把每个列都有标题的数据表称为数据库，并提供了12个专用函数来简化对这种表的统计和查找工作，这些函数均以d打头，故称d函数。如dsum、daverage 、dcount、dmax,dmin、dproduct等。d函数的调用格式、形式参数表都 相同。语法格式如下： dname(单元格区域，要计算的列字段，条件区) 其中： 单元格区域，要求每列都有列标题。 列字段，是某列的列标题。 条件区的含义和构造方法同高级筛选。,5.2 d函数与动