excel中的数据处理.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,数据管理与数据透视表,学习目标,1,、掌握数字与汉字排序的,区别,2,、掌握,EXCEL,排序,的方法,3,、理解,条件区域,4,、掌握,数据筛选,的方法,5,、掌握工作表数据的,分类汇总,方法,6,、掌握,数据透视表,的功能及用法,7,、应用,数据链接,管理多表数据,4.1,数据,排序,1,、排序规则,数字排序规则,字母排序规则,逻辑值排序规则,汉字排序规则,汉语拼音序,按笔画排序,2,、排序方式,升序,降序,4.1.2,数值,排序,3,、排序案例,【,例,7.1】,某班某次期末考试成绩如图（,a,）所示，已经计算出了每位同学的平均分，现需得出一个名次表，即从高分到低分的成绩表，以便为前,3,名同学发奖学金。,4.1.2,数值,排序,按日期排序,4.1.3,汉字与字符排序,按汉字笔画排序,1,、选中要排序的数据区域后,单击,”,排序和筛选”,按钮,2,、单击,”,自定义排序”,按钮，弹出“,排序,”对话框,3,、单击,”,选项”,按钮，弹出“,选项,”对话框,3,、指定,”,笔画序”,4.1.4,自定义,排序次序,自定义排序,有时需要按人为指定的次序对某此资料排序。,【,例,7.3】,某大学有,7,个学院，每个学院的基本情况如图所示。,要求按以下次序排序此表：计算机学院、管理学院、通信学院、财务学院、外语学院、政法学院、中文学院。,4.1.4,自定义,排序次序,方法,单击,Office,按钮“,Excel,选项”“常用”“使用,Excel,时采用的首选项”“编辑自定义列表”，,Excel,会弹出“选项”对话框,按前面的方式显示出“,排序,”对话 框中，通过“,次序,”下面的“,自定义序列,”指定建立的序列为排序依据。,在此输入自定义的排序序列,4.1.5,账号、,零件号等排序,账号、零件号排序的困难,由于账号、零件号常采用不同的区段进行编码，为排序带来了困难。,账号、零件号排序的解决方法,一种方法是确保同类编号中的任何部分都有相同的长度。例如在上述例子中，将“,ZK-999-10”,输入为“,ZK-0999-10”,，这样在比较排序时就不会出错了。,另一种方法是把编号的不同部分输入在不同列的单元格中，如上面两个编号用图,7.6,所示的方式输入。在排序时，对编号所涉及到的三列同时进行排序，排序结果就不会出错，最后再用“,&”,运算符把排序后的各部分组合起来。,4.1.6,多关键字,排序,多关键字排序,就是对数据表中的数据按两个或两个以上的关键字进行排序。多关键字排序可使数据在第一关键字相同的情况下，按第二关键字排序，在第一、第二关键字都相同的情况下，数据按第三关键字有序，其余的以此类推。,在,Excel 2007,中，最多允许有,64,个排序关键字，但不管有多少关键字，排序之后的数据总是按第一关键字有序的。,4.1.6,多关键字,排序,【,例,7.4】2004,年某杂货店各雇员的销售数据如图,7.7,（,a,）的,A1:F7,所示，以第一季度为第一关键字、第二季度为第二关键字、第三季度为第三关键字的排序结果如图,7.7,（,a,）的,A9:F15,区域所示，排序的方式是递增。,1,、源数据表,2,、单击,“开始”“排序和筛选”“自定义排序”,命令，弹出“排序”对话框,3,、依次指定排序关键字,3,、排序结果,4.2,数据,筛选,1,、概念,数据筛选就是将数据表中所有不满足条件的记录行暂时隐藏起来，只显示那些满足条件的数据行。,2,、,Excel,的数据筛选方式,1,、自动筛选,自动筛选提供了快速查找工作表数据的功能，只需通过简单的操作，就能够筛选出需要的数据。,2,、高级筛选,高级筛选能够完成自动筛选所不能实现的复杂筛选。,4.2.1,数据,筛选,3,、自动筛选,案例,【,例,7.5】,某公司的人员档案是按招聘报到的先后次序建立的，如图,7.8,（,a,）所示。可以看出，图,7.8,的档案比较混乱，公司的管理人员可能需要这样的信息：已婚人员的名单，各部门的人员档案信息，工资高于,1 200,的人员有哪些，奖金高于,1000,的人员有哪些，各种学历的人员档案,（,1,）已婚人员的名单。,（,2,）各部门的人员档案信息。,（,3,）工资高于,1 200,的人员有哪些。,（,4,）奖金高于,1000,的人员有哪些。,（,5,）各种学历的人员档案,如此等等,4.2.1,数据,筛选,原数据表,具有自动筛标记的数据表,单击“数据”,|“,筛选”，,Excel,会在表格的标题边显示出自动筛选标志。可借此完成各种自动筛选！,4.2.1,数据,筛选,自动筛选的结果示例,筛选出运维部的工作人员。,7.2.1,数据,筛选,如果筛选关键字对应的列是数值型数据，可通过“数字筛选”命令指定自定义筛选条件，实现灵活的数据查询。,【,例,7.6】,在图,7.8(a),所示的职工档案表中，筛选年龄最大的,3,名职工。,1,、单击,“数据”！“筛选”,为数据表显示出自动筛选标志,2,、单击,“年龄”,右边的自动筛选下拉箭头,3,、单击,“,10,个最大的值”，,弹出对话框,4,、将,“,10,”改为,3,，,即可筛选出年龄最大的,3,位员工！,【,例,7.7】,查看图,7.10,中职工档案表中工资高于,2800,元的人员,1,、指定工作表的“,筛选,”标志,2,、单击“工资,筛选,”中的“,大于,”命令,3,、在弹出的对话框中输入,工资数据,4.2,数据,筛选,4,、高级筛选,高级筛选的条件区域 的类型,（,1,）“或”条件,（,2,）“与”条件,（,3,）多列的“或”条件,（,4,）用“或”连接的“与”条件,4.2.2,条件区域,条件区域的构建方法,4.2.3,高级,筛选,1,、条件“与筛选”,【,例,7.8】,查看例,7.5,（图,7.8,）中“运维部”工资和奖金都高于,500,元的人员。,1,、单击“,高级”,按钮，弹出高级筛选设置对话框,2,、指定筛选结果的保存方式。,2,、指定筛选条件和数据区域。,4.2.3,高级,筛选,2,条件“,或,”的高级筛选,【,例,7.9】,对于图,7.8,，现要找出其中奖金小于,500,或已经结婚的工作人员。,4.2.3,高级,筛选,3,与条件同或条件的组合应用,【,例,7.10】,对于前面讨论的职工档案数据表，现要查找运维部和信息资源部中工资高于,1000,且奖金高于,500,的人员档案,4.2.3,高级,筛选,4,、使用计算条件的高级筛选,什么是计算条件,计算条件的构造方法,计算条件中的标题可以是任何文本或都是空白，但不能与数据清单中的任一列标相同，这一点正好与前面讨论的条件区域相反。,必须以绝对引用的方式引用数据清单或数据库中之外的单元格。,必须以相对引用的方式引用数据清单或数据库中之内的单元格。,4.2.3,高级,筛选,基于计算条件的高级筛选案例,【,例,7.11】,某商场,2003,年,2,月,1,号到,8,号的销售记录如图,7.18,的区域,A2:F10,所示，找出其中销售额高于平均值的销售记录。,4.3,数据,的分类与汇总,1,、概述,分类汇总就是对工作表中指定的行或列中的数据进行,汇总统计,，它通过,折叠,或展开原工作表中的行、列数据及汇总结果，从汇总和明细两种角度显示数据，可以快捷地创建各种汇总报告。分类汇总的数据折叠层次可达,8,层,。,2,、分类汇总能够完成以下事性,在数据表中显示一组或多组数据的分类汇总及总和。,在分组数据上完成不同的计算，如求和、统计个数、求平均数、求最大（最小值）等。,4.3,数据,的分类与汇总,案例,右图是某公司的职工工资表。,完成以下事情：,每部门有多少人；,每部门平均工资；,哪个部门的总评奖金最高；,应分发给各部门的总金额各是多少,通过函数能够完成这些事件，但分类汇总简单多了。,4.3,数据,的分类与汇总,3,、分类汇总的准备工作,分类汇总表的每个数据列都有列标题；,必须对要进行分类汇总的列排序。,排序的列标题称为分类,汇总,关键字，在进行分类汇总时，只能,指定,排序后的列标题为汇总关键字。,按部门排序后的工作表,4.3,数据,的分类与汇总,4,、建立分类汇总,单击“数据”选项卡中的,“,分类汇总”命令,显示“分类汇总”对话框。,1,、指定分类字段,2,、指定汇总方式,3,、指定汇总字段,4,、指定汇总结果的保存方式,4.3,数据,的分类与汇总,5,、汇总结果的保存方式,替换当前分类汇总,。选择这种方式时，最后一次的汇总会取代以前的分类汇总。,每组数据分页,。选择这种方式时，各种不同的分类数据将被分页保存。,汇总结果显示在数据下方,。选择这种方式时，原数据的下方会显示汇总计算的结果。,上述,3,种方式可同时选中。,Excel,的默认选择是第,1,和第,3,项。,4.3,数据,的分类与汇总,6,、分类汇总结果示例,4.3.3,高级,分类与汇总,7,、多次分类汇总,Excel,可以对同一分类进行多重汇总，若要在同一汇总表中显示两个以上的汇总数据，只需对同一数据清单进行两次不同的汇总运算。第二次分类汇总在第一次的汇总结果上进行。,在前面的汇总结果基础上，统计每部门的人数。,4.3.4,嵌套,分类汇总,1,、嵌套分类汇总,在一个已经建立了分类汇总的汇总表中再进行另一种分类汇总，两次的分类汇总关键字不同，这种分类汇总方式称为嵌套分类汇总。,2,、嵌套分类汇总的排序方式,建立嵌套分类汇总的前提仍然是要对每个分类汇总关键字排序。第一级汇总关键字应该是排序的第一关键字，第二级汇总关键字应该是第二排序关键字，其余以此类推。,有几层嵌套汇总就需要进行几次分类汇总操作，第二次汇总在第一次的结果集上操作，第三次在第二次的结果是操作，其余以此类推。在一个已经建立了分类汇总的汇总表中再进行另一种分类汇总，两次的分类汇总关键字不同，这种分类汇总方式称为嵌套分类汇总。,4.3.4,嵌套,分类汇总,以部门为第一汇总关键字、婚否为第二汇总关键字进行的嵌套分类汇总。,4.3.5,删除分类汇总,8,、删除分类汇总,对于刚建立的分类汇总，如果要将其恢复为原始数据表，可以选择“编辑”中的“撤消”菜单项取消分类汇总，或单击撤消按钮。,但若在建立分类汇总之后，又进行了其他操作，上述方法就无效了，可按如下方法删除分类汇总。,（,1,）单击分类汇总表中的任一单元格。,（,2,）选择“数据”选项卡中的,“,分类汇总”命令,（,3,）单击“分类汇总”对话框中的“全部删除”按钮。,4.4,数据,透视表,数据透视表的功能,对数值数据进行分类汇总和聚合；展开或折叠要关注结果的数据级别，查看感兴趣区域摘要数据的明细；将行移动到列或将列移动到行（或“透视”），以查看源数据的不同汇总；对最有用和最关注的数据子集进行筛选、排序、分组和有条件地设置格式，突出显示重要信息；提供简明、带有批注的联机报表或打印报表,4.4,数据,透视表,2,、源数据表与数据透视表的对应关系,4.4.2,建立数据,透视表,3,、案例（,注：数据透视表的全部案例都在,Ch7-15.Xlsx,工作薄的各个工作表中）,【,例,7.15】,假设某电视销售商场有,6,位职工，分别是劳得诺、令狐冲、陆大安、任我行、韦小宝、向问天，销售的电视产品有,TCL,、长虹、康佳、创维、熊猫几种品牌。每天的销售数据都记录在,Excel,的一个工作表中，如图所示，该数据表有,300,多行数据。,4.4.2,数据,透视表,完成以下统计分析工作,统计各种品牌的电视的总销售额,；,统计每位职工的总销售额；,统计每位职工销售的各种类型的电视机的销售额；,统计各种运输方式的总数。,数据透视表能够很方便地完成这些工作。,4.4.2,数据,透视表,4,、建立数据透视表的过程,1,、建立源数据表后，单击“,插入,”选项卡中的“,数据透视表,”弹出“创建数据透视表”对话框,1,、“,创建数据透视表,”对话框,1,、指定数据透视表的数据区域后，单击“,确定,”按钮，将弹出数据透视表的布局设置对话框,2,、根据第,1,步设置创建的数据透视表,1,、用鼠标将数据字段拖放到行、列标签和数值汇总对应的文本框中,4.4.2,数据,透视表,其中数据透视表布局中的内容,行标签,拖放到行中的数据字段，该字段中的第一个数据项将占据透视表的一行。,列标签,与行相对应，放置在列中的字段，该字段中的每个项将占一列。,报表筛选,行和列相当于,X,轴和,Y,轴，由它们确定一个二维表格，页则相当于,Z,轴。拖放在页中的字段，,Excel,将按该字段的数据项对透视表进行分页。,数值,进行计数或汇总的字段名称。,4.4.2,数据,透视表,统计各销售员销售数据的透视表布局,4.4.3,查看透视表中,数据的具体来源,统计各销售员销售数据的透视表,双击任一单元格，会得到这些数据的来源明细数据,4.4.3,查看透视表中,数据的具体来源,双击上表,E5,单元格得到的明细数据表,4.4.4,利用报表筛选,创建分页显示,利用报表筛选（即页字段）创建分页显示,页字段,用鼠标单击它，从下拉列表中选择某个日期，可以查看此日期的销售数据,4.4,数据,透视表,5,修改数据透视表,增加或减少行、列中的数据项,重新组织数据透视表,行、列置换,在字段内移动个别项,4.4.5,建立多字段的,数据透视表,6,、使用多重数据字段建立透视表,【,例,7.16】,在前面所讨论的电视销售数据表中，要查看每位职工销售了多少种产品，每种产品的数量各是多少，而且还要查看其销售的产品中，每种运输方式各运输了多少数量。,在行标签中放置了”,销售员姓名,“和”,运输方式“,两字段,4.4.6,修改数据透视,表的汇总函数,7,、修改数据透视表的汇总函数,数据透视表中使用的,透视函数称为,汇总函数，在默认情况下，数据透视表采用,SUM,函数对其中的数值项进行汇总，用,COUNT,函数对表中的文本类型字段项进行计数。,Excel,提供了许多汇总函数，如下图所示。,单击”,求和项,“的下拉箭头，从弹出的菜单中选择”,值字段设置“,，弹出值字段设置对话框,在此选择需要的”,汇总函数,“,4.4.7,修改透视表,数据的显示方式,8,、修改透视表数据的显示方式,以百分比显示数据,单击”,求和项,“的下拉箭头，从弹出的菜单中选择”,值字段设置“,，弹出值字段设置对话框,单击”,此,“下拉箭头，从弹出的菜单中选择”,占总和的百分比“,4.4.8,显示数据项,的明细数据,【,例,7.19】,查看图,7.32,（,a,）中创为电视各种运输方式明细数据的百分比，如图,7.33(a),所示。,用鼠标右击某一产品名称，从弹出的菜单中选择”,展开,/,折叠,“，显示出”,显示明细数据,“对话框,从中选择要显示明细数据的数据项名称,4.4.9,对日期,进行分组透视,在日常工作中，常常需要制作月报表、季度报表或年度报表之类与日期密切相关的报表，这类报表的制作往往需要对源数据表中的日期和时间进行分组计算，才能制作出来。,例如，对于前面介绍的电视销售记录，要制作出每种产品在每个月的员工销报表，其制作方法是把源数据中的产品名称字段放在数据透视表的“报表筛选”中，把销售员姓名字段放在“行标签”中，把日期字段放在“列标签”中，得到的数据透视表如图,7.34,（,a,）所示。很显然，图,7.34(a),不是我们需要的数据透视表，需要的是图,7.34(b),所示的透视报表。,1,、以”,销售员姓名,“为行标签，“,日期,”为列标签制作数据透视表,2,、单击列标签下面的任何一个日期，如“,1997,年,2,月,27,日”，然后单击“,数据透视表工具,”,“,选项,”“,将所选内容分组,”，,Excel,会弹出“,分组,”设置对话框，,3,、选择一种分组的时间方式，如月,对日期进行分组透视的过程,4.4.10,制作数据,透视图表,9,、制作数据透视图表,4.5,数据,链接、嵌入,1,、链接概述,链接就是指两个不同工作表或不同文档（可以是不同应用程序建立的文档，如,WORD,和,EXCEL,之间）存在的,数据连接,。,链接让一个工作簿可以共享另一个工作薄中的数据，可以链接单元格、单元格区域、命名公式、常量或工作表。,总的说来，链接具有以下优点：,在不同的工作簿和工作表之间，可以进行数据共享。,小工作簿比大工作簿的运行效率更高。,分布在不同地域中的数据管理可以在不同的工作簿中完成，通过超级链接可以进行远程数据采集、更新和汇总。,可以在不同的工作薄中修改、更新数据，大家可以同时工作。如果所有的数据都存于一个工作表中，就增加了多人合作办公的难度。,4.5.1,建立,链接,2,、建立,EXCEL,工作薄之间的链接,【,例,7.20】,某茶厂生产的茶叶销售于西南地区，每个季度的销售统计分别在不同的工作簿中，下图就是第一、二季度的茶叶销售统计工作薄示例。,4.5.1,建立,链接,建立一新工作薄，将这前面两不同工作薄中的工作表连接在该工作薄。,本工作薄中的两个工作表是从其它工作薄链接过来的。修改原工作表中的数据时，本工作薄中的数据会立即更新！,链接建立的方法,(1),复制,源工作表数据区域,(2),单击,”,开始,”选项卡,”,剪粘板”组中的,”,粘贴,”的下拉箭头,(3),从下拉菜单中选择,”,粘贴链接,”,4.5.2,将,Excel,的工作表或,图表链接到其他应用程序,案例,某茶叶厂第一季度的销售数据表及销售对比柱形图如图所示。,现要将此图表作为在,Word,工作报表中的一部份，以使报告更有说服力,4.5.2,将,Excel,的工作表或,图表链接到其他应用程序,链接到,Word,报告中的,Excel,工作表,4.5.3,修改与,更新链接,更新链接的原因,作为数据源的工作簿如果改名了，或者移到了另外的磁盘目录中，在打开链接工作薄时,Excel,就无法找到链接的数据源，链接了其他数据源的工作表就无法工作（或无法正确工作）了，,Excel,会报告一个链接错误信息。,单击,“数据,”选项卡“,链接,”组中“,编辑链接,”命令，,Excel,会弹出图示的“,编辑链接”,对话框。,4.6,合并,计算,1,、概念,所谓合并计算，就是对来源于同一工作簿的不同工作表，或不同工作簿中的数据表进行合并计算。,Excel,支持将不超过,255,个工作表中的信息收集到一个主工作表中，支持的工作表可在同一个工作簿中，也可来源于不同的工作簿。,4.6,合并,计算,案例,【,例,7.22】,某电视机厂生产,21,英寸、,25,英寸、,29,英寸及,34,英寸几种规格的电视产品，主要销售于西南地区。该厂每个季度进行一次销售统计，每次统计分析保存在一个独立的工作簿中,4.6,合并,计算,建立合并计算,单击“,数据,”选项卡“,数据工具,”组中的“,合并计算,”按钮，弹出,“合并计算,”对话框。,4.6,合并,计算,合并计算的结果,本表是对前面四个工作薄数据的合并结果,4.6,合并,计算,保存明细数据的合并计算结果,The End,谢谢大家！,