Excel2013高级教程.ppt
《Excel2013高级教程.ppt》由会员分享,可在线阅读,更多相关《Excel2013高级教程.ppt(191页珍藏版)》请在装配图网上搜索。
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学会利用排序、筛选、分类汇总功能进行数据分析,4,1、数据处理与分析基础,1.1Excel的功能与界面1.2数据的输入、编辑与运算(案例:销售产品基本信息表、销售记录汇总表)1.3利用数据条件格式进行数据处理1.4利用排序、筛选、分类汇总功能进行数据分析,1.1Excel的功能与界面,1.1.1Excel新增的主要功能(1)取消了菜单方式,采用了面向结果的用户界面,易于找到。(2)更强大的数据管理能力和安全性。(如更多的行和列104857616384,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自动筛选知识点:欲选择某列中一个项止,先取消本列中的“全选”复选框,然后再单选;注意状态栏提示(筛选出的个数)、列标右侧的图标显示;筛选是累加的,后一次筛选在前一次基础上进行;解除一列筛选:按“全选”;全部解除:按“筛选”按钮。,22,1.4利用排序、筛选、分类汇总功能进行数据分析,1.4.3高级筛选概述:可实现多字段间的“或”条件筛选,并能将结果复制到其他区域。任务6:查看一段时间内(如2007年8月上旬)特定用户(如“好又多”)的销售情况此任务可用高级筛选或自动筛选完成。,23,1.4利用排序、筛选、分类汇总功能进行数据分析,1.4.3高级筛选知识点:1、条件的写法(日期条件中用的是日期的数值)2、取消高级筛选单击“清除”思考:要筛选出销售数量不低于100,或者毛利不小于2000元的数据怎么做。,24,数量毛利=100=2000,发货日期发货日期客户名称=3929550,sum(B2:B5),0)=if(a2=10,“科技处”,if(a2=20,“财务处”,“人事处”),31,条件,取真时,取假时,2利用函数进行数据分析,2.2sumif函数的应用任务1:新建一个工作表,命名为“函数应用“,统计各客户购买数量、购买额等数据之和。知识点:sumif函数(1)输入要分析的数据栏目(从销售记录汇总表挑选字段粘贴而来)(2)输入客户名称的技巧(将分类汇总结果复制到别处)(3)使用sumif函数计算各个字段。(4)使用排名函数RANK按毛利对客户排名。,32,相关知识:Sumif函数,SUMIF(条件判断区域,条件,求和区域)根据指定条件对若干单元格求和。条件可以是数字、表达式或文本,如:“北京”、“=50”等,要使用引号。也可以是单元格引用。案例中C5处(数量)的公式:=SUMIF(销售记录汇总!$A$2:$A$107,$B5,销售记录汇总!$G$2:$G$107)复制到右侧单元格,并修改最后一项中的列号,依次为IKL列。(sumif其他例子见“综合实例”工作薄),33,求和区域,条件区域,条件值,相关知识:RANK函数的使用,格式:RANK(要排位的数值,排位区域,选项)功能:返回一个数值在一组数值中的排位。选项:非0:升序排位0或省略:降序排位,34,2利用函数进行数据分析,2.3sumifs函数的应用任务2:用sumifs函数对工作表“函数应用”,统计各客户购买数量、购买额等数据之和。知识点:sumifs函数(1)“数量”的公式为=SUMIFS(销售记录汇总!$G$2:$G$107,销售记录汇总!$A$2:$A$107,B$19),35,求和区域,条件区域,条件值,相关知识:sumifs函数的使用,格式:sumifs(求和区域,条件范围1,条件1,条件范围2)功能:对某一区域内满足多重条件的单元格求和,最多127个条件。条件可以是“=39295)*(销售记录汇总!$C$3:$C$410=39304)*销售记录汇总!$N$3:$N$410)中旬与下旬类似,只是数值不同。思考:什么情况下要使用SUMPRODUCT函数?,41,3利用图表进行数据分析,学习目标1了解图表的类型及其组成部分2掌握图表的建立、编辑与美化方法3理解复杂图表的建立方法4了解动态图表的建立方法,42,3利用图表进行数据分析,3.1图表概述3.2图表的建立、编辑与美化3.3复杂图表的建立3.4动态图表的建立,43,3.1图表概述,图表是EXCEL中一个强有力的工具,利用图表可以使数据更加直观地显示出来。EXCEL有丰富的图表类型,其功能不逊于某些专业图表软件。了解图表有关术语和组成部分是正确使用图表的前提。1、有关术语(1)数据点:即数据标记,本质上是一个单元格中的数值图形表示,不同类型的图表数据点形状不同,柱形图中表现为一个柱形,折线图中为一个点,面积图中为一扇形区域。(2)数据系列:图表中一组相关的数据点,它来自于数据表的某行或某列,每个数据系列有相同的颜色和图案,并且通过图例标识。一张图表有多个数据系列,但饼图只有一个。(3)数据标签:是指为数据系列或数据点添加的标识,默认情况下图表没有数据标签,用户可以添加数据标签、并修改它的位置和大小。,44,3.1图表概述,有关术语:(4)网格线:用于查看和评估数据,有水平和垂直两种。(5)轴:作为绘图区一侧边界的直线,有X轴也称分类轴,Y轴也称数值轴两种。(6)刻度线与刻度标志:类似于尺子上的刻度。(7)图例:用于说明每个数据系列中的数据点所采用的图形外表。(8)标题:有图表标题、分类轴标题和数值轴标题。识别图表区域的技巧:单击图表上不同区域,观看:图表工具布局当前所选内容。,45,3.1图表概述,2、最常见的图形介绍(1)柱形图:用于显示某段时间内数据的变化,或比较各数据项之间的差异。分类一般为日期或时间,数值在垂直方向组织,以便于强调相对于时间的变化。(2)条形图:也用于显示各数据之间的比较。与柱形图不同的是,其分类在垂直方向(一般为日期或时间),而数值在水平方向,使观察者的注意力集中在数据值的比较上,而不在时间上。(3)折线图:主要用于显示各数据随时间而变化的趋势情况。横坐标几乎总是表现为时间。(3)饼图:用于显示组成数据系列的各数据项与数据项总和的比例。当只有一个数据系列,并且用于强调整体中的各个组成部分占整体的比例时十分有效。,46,3.2图表的建立,任务1:对前面一章任务5中工作表中的结果,制作各系列产品销售额百分比分离型三维饼图。知识点:(1)建立“总计”行,写入公式:SUM(范围)(3)选择上下数据两行插入饼图分离型三维饼图;(4)输入图表标题“各系列产品销售额占比分析”;(5)添加百分比数据标签;图表工具-布局-其他数据标签选项选择:类别名称、百分比;选择:数字百分比2位小数。(6)删除图例,47,3.2图表的建立,任务2:仍对前面的工作表中,制作分离型三维饼图,分析从各客户获取利润的百分比。要求:(1)图表标题为“从各客户获取利润占比分析”。(2)图例在下方。思考:如何选择数据源?,48,3.2图表的建立,归纳:建立图表的步骤(1)首先选择数据源。要选定图表数据所在的单元格,如希望数据的行、列标志也显示在图表中,则选定区还应包括行/列标题。(2)应用预定义的图表布局和样式设置图表。方法:图表工具布局/样式。(3)创建图表工作表。选中图表,设计位置移动图表。(可将嵌入式图表变成图表工作表,并可移回),49,3.3复杂图表的建立,1、概述:标准图表在数据分析中有时仍不能满足实际需要,用户可能需要根据实际情况制作复杂一些的图表,例如在图表中添加涨跌柱线、垂直线、系列线和高低点连线,给图表增加趋势线等,也可以运用一些技巧制作复杂图表,如:组合图表、双轴图表、复合饼图、甘特图、图片图表、趋势图等。,50,3.4复杂图表的建立,2、在图形上增加趋势线趋势线简介:趋势线以图形方式显示数据的发展趋势,常用于预测分析(也称回归分析,在图表中扩展趋势线,根据实际数据预测未来数据),条形图、折线图、柱形图、股价图、气泡图、XY散点图、非堆积型二维面积图都支持趋势线。任务3:根据下表的数据作出图,并根据折线图作出趋势线(线性、指数、对数等不同类型的趋势线),并利用趋势函数预测五月份数据。,51,3.3复杂图表的建立,在折线图上增加趋势线效果图,52,折线图增加趋势线制作步骤,知识点:操作步骤(1)先作折线图。(2)右击数据系列添加趋势线,在趋势线选项中选择一种类型,并“显示公式”。(3)根据趋势线函数预测5月份数据,X取第5个数据点:Y=103.5*5+6218=6735.5这是5月份数据的预测值。其他类型的趋势线制作方法与此类似。,53,3.4复杂图表的建立,3、组合图表:在一个图表中表示两个甚至多个数据系列,而不同数据系列用不同的图表类型表示。任务4:组合图表(柱形-折线)的制作,基础数据如下表。,54,3.3复杂图表的建立,组合图表(柱形-折线)图的制作效果图,55,组合图表制作步骤,知识点:操作步骤(1选中数据区域。(2)插入图表柱形图(3)将数值小的系列右击变为折线图,56,3.4复杂图表的建立,4、双轴图表即双数据坐标轴图表,是指在一个图表中在主、次坐标轴上分别绘制一个或多个数据系列,它们采用两种不同的坐标轴值来度量数据。任务5:双轴图表的制作,基础数据如下表。,57,3.3复杂图表的建立,双轴图表的制作效果图,58,双轴图表制作步骤,知识点:操作步骤(1)选中数据区域。(2)插入图表柱形图(3)将数值小的系列右击变为折线图(4)在折线图上右击,设置数据系列格式系列选项次坐标轴。(5)添加图表标题,两个坐标轴标题,设置字体。,59,3.3复杂图表的建立,5、复合饼图:饼图可以用来表达单个数据与整体结果之间的比例关系,而复合饼图还可以对数据进行深层次分析。任务6:复合条饼图的制作,基础数据如下表。,60,3.3复杂图表的建立,复合条饼图的制作效果图,61,复合饼图制作步骤,知识点:操作步骤(1)选择数据区,插入“图表“饼图”“复合条饼图”,设置数据标签(百分比、显示引导线)(2)右击主饼图设置数据系列格式系列选项将”第二绘图区“2改为3。(3)优化图表,选中图例删除,双击标题、百分比标签,并修改其内容、设置字体。,62,3.4动态图表的建立,概述所谓动态图表,是指图表的数据源可以根据需要动态变化,从而使数据图表也随之变化,一般有三种方式:1、利用有关函数设置动态区域;2、通过定义数据区域名称,并引入辅助数据区域;3、利用动态控件链接图表中的引用数据,以实现用户的自由选择。,63,3.4动态图表的建立,任务7:利用CHOOSE函数和组合框建立动态图表,64,3.4动态图表的建立,利用CHOOSE函数和组合框建立动态图表效果图,65,3.4动态图表的建立,知识点:操作步骤(1)在原数据区外建立动态数据区域,作为数据源复制地区列到I3,在J3输入公式:=CHOOSE($M$3,C4,D4,E4,F4,G4),要根据M3内容显示相应内容,向下复制公式。(2)转置复制“余额”等列标题到L3(3)开发工具控件插入表单控件组合框,右击设置控件格式控制数据源区域/单元格链接,下拉项数,将组合框拖入图表区。(4)以动态数据区域建立柱形图并格式化。(5)从组合框选择项目,观看柱形图变化。,66,3.4动态图表的建立,知识点:相关知识公式解释Choose函数的功能是从值的列表中返回一个值。如:Choose(2,”a1”,”a2”,”a3”,”a4”)则返回a2,其中第一参数2为序号。,67,3.4动态图表的建立,任务8:利用INDIRECT等函数建立动态图表,68,3.4动态图表的建立,利用INDIRECT等函数建立动态图表效果图,69,3.4动态图表的建立,知识点:操作步骤(1)在原数据区外建立动态数据区域,作为数据源(2)复制标题行到动态数据区域(3)在“地区”下B15输入公式:=INDIRECT(ADDRESS(CELL(row),COLUMN(B4)向右方各列复制(4)以动态数据区域建立饼图,单击任一地区名称,按F9键,即显示指定地区的饼图。,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。,71,4利用数据透视表(图)进行数据分析,学习目标1掌握数据透视表、图的概念和术语。2学会建立数据透视表和数据透视图3了解EXCEL的数据链接和访问外部数据源的一般方法。,72,4利用数据透视表(图)进行数据分析,4.1数据透视表简介4.2数据透视表的建立4.3数据透视表的美化4.4数据透视图4.5关于数据链接,73,4.1数据透视表简介,1、数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格,是一个功能强大的数据分析工具。2、数据透视表的显著特点是对数据进行动态分析,只需要改变字段的位置,即可得到多种分析结果。因此字段的设置是关键。,74,4.1数据透视表简介,3、数据透视表的主要功能(1)对数值数据进行分类汇总和聚合,按分类和子分类对数据进行汇总;(2)展开或折叠要关注结果的数据级别,查看感兴趣区域摘要数据的明细;(3)将行移动到列或将列移动到行(或透视),以查看源数据不同组合与汇总的结果;(4)对最有用和最关注的数据子集进行筛选、排序、分组和有条件地设置格式,突出显示重要信息;(5)提供简明、带有批注的联机报表或打印报表。,75,4.1数据透视表简介,4、何时使用数据透视表?如果要对工作表的数据进行行、列变换,或者要在数据量较大的工作表中进行数据的多种对比分析,就应该使用数据透视表。数据透视表也能完成排序、分类汇总和计数统计等方面的工作。,76,4.1数据透视表简介,5、数据透视表字段列表的运用(1)行标签。该字段的一个数据项占一行,相当于X轴。(2)列标签。该字段的一个数据项占一列,相当于Y轴。(3)报表筛选。由该字段确定一个二维表,相当于Z轴。按该字段对透视表分页。(4)数值。该字段中的数据完成指定的计算,如计数、求和、求平均等。,77,4.2数据透视表的建立,任务1:对“销售记录汇总”表分析各客户各系列产品的销售情况知识点:方法1:(1)光标放在数据区,插入数据透视表数据区确定(在一个新的工作表中建立了透视表)(2)设置行字段区:客户名称、系列。,78,4.2数据透视表的建立,方法2:(1)在一个新的工作表中,插入数据透视表使用外部数据源选择连接浏览更多我的电脑选磁盘、文件夹、工作薄、工作表放置位置。(2)设置行字段区:客户名称、系列。,79,4.2数据透视表的建立,知识点:(3)设置数值区:数量、销售额、总成本、毛利等(4)设报表筛选区:发货日期(5)重设布局。让行标签水平显示(默认情况下在行标签下的字段都逐一显示在首行标签下。下拉-以大纲形式显示项目标签/以表格形式显示)。(6)美化。数据透视表工具设计-数据透视表样式(7)从筛选处下拉。选择单个日期或“选择多项”。(8)改名工作表“透视表应用”,80,4.2数据透视表的建立,任务2分析各系列产品的销售情况知识点:调整行标签,让“系列”在“客户名称“上面即可。,81,4.3数据透视表的美化,(1)对选中的区域:开始字体边框/填充颜色,以添加边框效果或填充颜色;(2)设计数据透视表样式其他(3)设置报表筛选字段,可以根据筛选条件来显示数据,可添加多个筛选字段。(4)可以行/列/数值标签中删除或增加字段。标签中字段次序可调整。(5)数值字段的计算方式可改变。,82,4.3数据透视表的美化,(6)可控制显示/不显示分类汇总和总计(分类汇总是每小组的小计,而总计是一行或一列的总计)。(7)可对选中的字段排序显示。(8)可对行标签“选择字段”或选择“值筛选”。(9)创建好透视表后,可更改数据源以创建不同效果的透视表。选项数据更改数据源。(10)清除全部透视表数据。选项操作-清除。(11)复制透视表至别处。选项操作选择整个数据透视表复制/粘贴。,83,4.4数据透视图,1、概述数据透视图是根据透视表的结果转化而来的,透视表与透视图相关联,它们彼此有对应的字段。透视表中的字段改变了,透视图也会变化。数据透视图与常规图表的意义和操作方法基本相同,其主要不同是透视图是一种动态图表,一张透视图实际上是一系列图表,图表内容可根据数据项的变化而变化。,84,4.4数据透视图,2、数据透视表图的生成与编辑任务3:修改前面的透视表并转化为透视图,字段安排如下:(1)报表筛选:发货日期,行标签:客户名称,数值:毛利求和,分离型三维饼形图;(2)报表筛选:日期,行标签:客户名称,数值:销售额求和、总成本求和、簇状柱形图;(3)行标签:发货日期,数值:毛利求和,分离型三维饼形图;(4)报表筛选:日期,行标签:客户名称,列标签:系列,数值:销售额求和,簇状柱形图。,85,4.4数据透视图,2、数据透视表图的生成与编辑将透视表转化为透视图后,会自动在工作表中生成“数据透视图筛选窗格”,通过设置该窗格中的条件,可以生成不同的透视图。数据透视图的主要编辑有:编辑图表标题,坐标轴标题,图例,数据标签,坐标轴刻度,数据系列的样式,更改数据源、更改图表类型数据透视图的美化设置、图标设置、纵坐标轴设置、图例设置等,与普通图表的设置类似。,86,4.4数据透视图,3、图表布局与图表样式图表布局是指图表中各对象的摆放位置。图表样式是指设置图表效果的一种样式方案。EXCEL提供了多种布局和样式方案可供选择。方法是:主菜单:数据透视工具设置图表布局;主菜单:数据透视工具设置图表样式。新增练习:对“综合实例”工作薄,透视表图工作表操作:(1)各省各民族人数。(2)各班各民族人数(3)各班生源分布(4)各省生源政治面目情况。,87,4.5关于数据链接,1、外部引用在一个工作薄中引用另一工作薄中的数据称外部引用,也叫链接。前者称目标工作薄,后者称源工作薄。链接使一个工作薄可以共享另一工作薄中的数据。打开目标时,源可打开也可关闭,如果先打开源后打开目标,数据会自动更新。用户可以自己适时刷新数据。当修改源数据时,EXCEL会通过链接自动修改链接工作薄中的数据。举例:利用外部数据源(如数据源在另外一个工作表)生成透视表。(各客户销售额统计),88,4.5关于数据链接,2、EXCEL与外部数据库企业数据往往以数据库或特殊文件(如XML、TXT)形式存储在服务器、网站或某些办公室的计算机中,如果将这些数据导入到EXCEL,就能利用EXCEL进行数据处理和分析,同时EXCEL也可以作为数据库系统的数据采集工具。,第89页,4.5关于数据链接,3、EXCEL可访问的外部数据库包括SQLServer,Oracel,Access,Foxpro等。4、数据源数据源就是数据的来源,是其他软件如EXCEL访问数据库的一组信息,包括数据库服务器的名称和位置,用于连接数据库的驱动程序名称,以及登录到数据库时需要的各种信息。EXCEL可通过数据源访问各种关系型数据库。5、数据源的类型主要有ODBC数据源OLEDB数据源,第90页,4.5关于数据链接,另外,EXCEL可以直接访问文本文件和Access数据库。数据源的数据更新后,可通过在EXCEL中右击数据列表后的菜单选择“刷新”以获得数据库最新的数据。6、ODBC数据源的访问前提:计算机中需要有数据库相应的ODBC驱动程序,如果没有(如MYSQL),可以到该数据库的官方网站进行下载并安装。,第91页,4.5关于数据链接,访问步骤:1、创建ODBC数据源:控制面板-管理工具-数据源(ODBC)-文件DSN-选择ODBC驱动程序如OracleinOraHome92,MicrosoftODBCforOracle-输入数据源名称及保存位置-直到完成2、在Excel中“数据”“自其他来源”“来自MicrosoftQuery”选择刚建立的数据源,第92页,4.5关于数据链接,7、MicrosoftQueryMicrosoftQuery是一个功能强大而且灵活的应用程序,通过它可以检索外部数据库中的数据,在EXCEL中可以调用它,将外部数据库中的数据(甚至是数据库中的符合条件的数据)导入EXCEL。8、访问外部数据库的条件具有访问外部数据库的权限、安装MicrosoftQuery、建立ODBC或其他数据源驱动程序。通过WEB查询不需安装MicrosoftQuery,但须有连接网络的各种设备和身份,还须有可运行的WEB查询文件(.iqy)。,第93页,4.5关于数据链接,9、OLEDB数据源的访问所有的ODBC数据源都可以以OLEDB方式导入提供了OLEDB驱动程序的数据库可以以OLEDB方式导入访问步骤:在Excel中“数据”“来自数据连接向导”其他/高级选择OLEDB驱动程序-输入必要信息。,第94页,4.5关于数据链接,10、EXECL可直接访问的数据源可以在EXCEL和文本文件(如记事本)或Access数据库的表记录间复制粘贴。但这种数据是“死”的数据。如在EXCEL中链接到Access数据库,Access数据库中的数据更新后,在EXCEL中可“刷新”得到最新数据。方法:数据自文本或数据自Access。,第95页,新增的内容,5构造动态数据分析报表,学习目标1理解EXCEL表的的概念,学会建立EXCEL表的方法。2学会使用结构化引用建立动态报表。3学会使用D函数和结构化引用建立动态报表。,96,5构造动态数据分析报表,5.1表与动态报表5.2D函数与动态报表,97,5.1表与动态报表,5.1.1表的基本概念及其操作1、问题的提出所谓动态报表就是指数据区域的大小可能会随时间而变化的工作表。在数据的统计与分析中,经常需要构造动态报表。以“函数应用”工作表为例,随着时间的推移,当“销售记录汇总”表增加或减少数据行时,原来的公式就不能自动适应,从而就不能得到正确的分析结果。对于前面介绍的透视表和透视图,同样有类似问题。只有更新透视表的数据源方到最新分析结果。Excel中,可以通过Excel专用表(简称表或表格)快捷地构造出动态报表,并能方便地对动态报表进行各种统计分析。,98,5.1表与动态报表,2、什么是表?如何建立表表也称表格,是Excel的特殊对象(即03版本的列表、数据清单)它是一个完整的结构,包含表区域、表数据区域、汇总行、标题行、列标题、调整大小控制点等内容。表具有数据筛选、排序、汇总和计算等功能,并能自动扩展数据区域,查通过表来构造动态报表。表(表格)工作表(区域),它有许多特性,有“表工具”选项卡。建立表的方法:选中工作表中的数据区域(也可以是空白区域),“插入”“表”创建表对话框。注意列必须要有标题。一个工作表中可以插入多个表。也通过自动套用表样式也能将区域转化为表。,99,5.1表与动态报表,3、关于表样式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.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.CopyActiveCell.Offset(-2,4).Range(A1).Select,6.1宏的概念、录制与运行,ActiveSheet.PasteActiveCell.Offset(3,-4).Range(A1).SelectApplication.CutCopyMode=FalseSelection.CopyActiveCell.Offset(-3,5).Range(A1).SelectActiveSheet.PasteActiveCell.Offset(4,-5).Range(A1).SelectApplication.CutCopyMode=FalseSelection.CopyActiveCell.Offset(-4,6).Range(A1).SelectActiveSheet.PasteActiveCell.Offset(5,-6).Range(A1).SelectApplication.CutCopyMode=FalseSelection.CopyActiveCell.Offset(-5,7).Range(A1).SelectActiveSheet.PasteActiveCell.Offset(7,-7).Range(A1).SelectNextEndSub,6.1宏的概念、录制与运行,录制和运行宏的注意事项:使用相对引用还是绝对引用?(录制宏时的光标开始位置和执行宏时的光标开始位置相同吗)录制宏前光标放在何处?是定位后再录还是录制开始后再定位?执行宏前光标放在何处?绝对引用的宏执行时不必定位,但绝对位置上的内容会被覆盖。录制宏时会自动插入一个模块。一个模块内可以有多个宏,6.1宏的概念、录制与运行,宏的基本结构,SubsumSelection()对选择的区域求和2010年3月编制ForEachxinSelections=s+xNextmegBoxsEndsub,宏名,注释,宏程序,宏结束,6.1宏的概念、录制与运行,录制宏要注意几点:根据情况选择“绝对引用”还是“相对引用”。在同一工作表多次使用宏一般应选“相对引用”,录制前、录制后要注意光标位置,执行前也要注意光标位置。录制开始第一步,光标定位否很重要。录制结束时,光标要为下次执行宏定好位。尝试任务1,开始录制时光标定不定位的区别。,6.1宏的概念、录制与运行,6.1.5Excel2007中宏的安全设置Office2007采用了比早期版本更加安全的宏运行方式。具有宏代码和没有宏代码的程序被分别保存在不同类型的文件中,用户通过文件扩展名就可判断文档中是否具有宏代码。在Excel2007中,没有宏代码的工作薄被保存在.xlsx文件中,具有宏代码的工作薄只能被保存在.xlsm类型或与早期版本兼容的.xls文件类型中。,6.1宏的概念、录制与运行,Excel2007信任中心Excel在默认情况下不再允许任何VBA程序的执行。要使宏程序运行,用户必须采取一些必要的措施,在“信任中心”中对宏的执行方式进行安全性配置.,(1)单击Office按钮,然后单击“Excel选项”。(2)在弹出的“Excel选项”对话框中单击“信任中心”标签,然后单击其中的“信任中心设置”,Excel会弹出“信任中心”设置对话框,单击其中的“宏设置”,6.1宏的概念、录制与运行,设置受信任位置,单击“受信任位置”,单击“添加新位置”,在此输入受信任的磁盘目录,凡存入此目录中的Excel工作薄,其中的宏都会被认为是安全的。Excel就不给出警告信息就执行它,6.2宏与VBA的关系,为何使用VBA?宏记录了许多不需要的步骤宏记录了许多不需要的资料宏无法使用较深入的功能宏无法完成循环过程宏无法完成需要条件判断和循环性的工作,宏,VBA,6.2宏与VBA的关系,什么是VBA?它与VB有何差异?,即VisualBasicforApplications,它是VisualBasic的一个派生体,它有针对性地对VisualBasic进行了优化和设置。两者的主要区别在于:VisualBasic开发的应用程序可以独立在Windows系统中运行,而用VBA开发的程序只能在提供它的应用程序中运行。如在EXCEL中编写的VBA程序只能在EXCEL中运行。(1)VB具有自己的开发环境,而VBA是集成在Office应用程序中的;(2)VB是以界面为中心的开发方式,重点在使用者如何进行操作,VBA以文件为中心,重点在使用者最后看到什么;(3)VB开发出的应用程序经过编译才能生成可执行文件,VBA开发的程序依赖于其所绑定的办公软件。,6.2宏与VBA的关系,认识VBA,VBA是一个通用程序语言,具备VB所有的特点;基于对象的开发方式:对象模型、属性、方法、事件;内置于Office中;用VBA语言可以控制应用程序的各项操作;用VBA可以创建用户窗体、模块、对象事件过程;利用语句控制循环操作,使宏完成一系列复杂操作;VBA提供了许多内部函数,还可以自定义函数;VBA和VB有相似的语句结构。,6.3VBA编辑器,VB编辑器(VBE)是编写VBA程序的环境。用来编辑宏程序或制作更高级的整合性应用程序,它内置于Office中,能使用较少的系统资源,达到编辑应用程序的目的。打开VBE的方法:按ALT+F11;或开发工具VBAVBE由工程资源管理器窗口、属性窗口、代码窗口等组成,可通过“视图”菜单调出。,138,6.3VBA编辑器,工程资源管理器窗口:是管理工程资源的窗口,当前打开的每一个工作薄和加载宏都是一个工程。代码窗口:工程资源管理器窗口中的每一个对象,包括工作薄、工作表、模块、类模块、用户窗体等都会有一个代码窗口,在代码窗口可以查看、编辑模块或对象中的代码,并可复制或粘贴代码。,6.4VBA的组成结构,VBA程序可分为模块、注释、过程、函数、函数与过程的调用五部分。在了解这五部分之前,首先要了解VBA的对象、属性、方法和集合等基本概念。对象:在EXCEL- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel2013 高级 教程
装配图网所有资源均是用户自行上传分享,仅供网友学习交流,未经上传用户书面授权,请勿作他用。
关于本文