数据管理与Excel数据透视表.pptx
- 文档编号:18669517
- 上传时间:2023-08-26
- 格式:PPTX
- 页数:54
- 大小:911.08KB
数据管理与Excel数据透视表.pptx
《数据管理与Excel数据透视表.pptx》由会员分享,可在线阅读,更多相关《数据管理与Excel数据透视表.pptx(54页珍藏版)》请在冰点文库上搜索。
第5章数据管理与数据透视表Excel与数据处理与数据处理本章学习目标本章学习目标1、掌握数字与汉字排序的区别2、掌握EXCEL排序的方法3、理解条件区域4、掌握数据筛选的方法5、掌握工作表数据的分类汇总方法6、掌握数据透视表的功能及用法7、应用数据链接管理多表数据5.1数据排序数据排序1、排序规则、排序规则n数字排序规则数字排序规则n字母排序规则字母排序规则n逻辑值排序规则逻辑值排序规则n汉字排序规则汉字排序规则n汉语拼音序汉语拼音序n按笔画排序按笔画排序2、排序方式、排序方式n升序升序n降序降序5.1数据排序数据排序3、排序案例、排序案例n【例【例5.1】某班某次期末考试成绩如图(】某班某次期末考试成绩如图(a)所示,已经)所示,已经计算出了每位同学的平均分,现需得出一个名次表,即从计算出了每位同学的平均分,现需得出一个名次表,即从高分到低分的成绩表,以便为前高分到低分的成绩表,以便为前3名同学发奖学金。
名同学发奖学金。
5.1数据排序数据排序n按日期排序按日期排序5.1数据排序数据排序n按汉字笔画排序按汉字笔画排序5.1数据排序数据排序n自定义排序自定义排序n有时需要按人为指定的次序对某此资料排序。
有时需要按人为指定的次序对某此资料排序。
n【例】某大学有【例】某大学有7个学院,每个学院的基本情个学院,每个学院的基本情况如图所示。
况如图所示。
要求按以下次序排要求按以下次序排序此表:
计算机学序此表:
计算机学院、管理学院、通院、管理学院、通信学院、财务学院信学院、财务学院、外语学院、政法、外语学院、政法学院、中文学院。
学院、中文学院。
5.1数据排序数据排序n方法方法n建立自定义序列建立自定义序列n在排序对话框在排序对话框中,通过“选中,通过“选项”指定建立的项”指定建立的序列为排序依序列为排序依据。
据。
5.2数据筛选数据筛选1、概念、概念n数据筛选就是将数据表中所有不满足条件的记数据筛选就是将数据表中所有不满足条件的记录行暂时隐藏起来,只显示那些满足条件的数录行暂时隐藏起来,只显示那些满足条件的数据行。
据行。
2、Excel的数据筛选方式的数据筛选方式n1、自动筛选、自动筛选n自动筛选提供了快速查找工作表数据的功能,只需通过简自动筛选提供了快速查找工作表数据的功能,只需通过简单的操作,就能够筛选出需要的数据。
单的操作,就能够筛选出需要的数据。
n2、高级筛选、高级筛选n高级筛选能够完成自动筛选所不能实现的复杂筛选。
高级筛选能够完成自动筛选所不能实现的复杂筛选。
5.2数据筛选数据筛选3、自动筛选、自动筛选n案例案例某公司的人员档案是按招聘报到的先后次序建立某公司的人员档案是按招聘报到的先后次序建立的,到目前为止,该公司的人员档案如下图的,到目前为止,该公司的人员档案如下图9所所示。
公司的管理人员可能需要这样的信息:
示。
公司的管理人员可能需要这样的信息:
(1)已婚人员的名单。
)已婚人员的名单。
(2)各部门的人员档案信息。
)各部门的人员档案信息。
(3)工资高于)工资高于1200的人员有哪些。
的人员有哪些。
(4)奖金高于)奖金高于1000的人员有哪些。
的人员有哪些。
(5)各种学历的人员档案)各种学历的人员档案n如此等等如此等等5.2数据筛选数据筛选选择“数据”选择“数据”|“筛选”筛选”|“自动筛选”,自动筛选”,Excel会显示会显示出自动筛选标志。
可借此完成各种自动筛选!
出自动筛选标志。
可借此完成各种自动筛选!
原数据原数据表表具有自具有自动筛标动筛标记的数记的数据表据表5.2数据筛选数据筛选n自动筛选的结果示例筛选出运维部的自动筛选的结果示例筛选出运维部的工作人员。
工作人员。
5.2数据筛选数据筛选4、高级筛选、高级筛选n高级筛选的条件区域的类型高级筛选的条件区域的类型n
(1)“或”条件n
(2)“与”条件n(3)多列的“或”条件n(4)用“或”连接的“与”条件5.2数据筛选数据筛选条件区域的构建方法条件区域的构建方法5.2数据筛选数据筛选n高级筛选案例高级筛选案例n查看下表中“运维部”和“信息资源部”人员中,查看下表中“运维部”和“信息资源部”人员中,工资高于工资高于1000且奖金高于且奖金高于500元的有哪些。
元的有哪些。
5.2数据筛选数据筛选n使用计算条件的高级筛选使用计算条件的高级筛选n什么是计算条件什么是计算条件n计算条件的构造方法计算条件的构造方法n计算条件中的标题可以是任何文本或都是空白,但计算条件中的标题可以是任何文本或都是空白,但不能与数据清单中的任一列标相同,这一点正好与不能与数据清单中的任一列标相同,这一点正好与前面讨论的条件区域相反。
前面讨论的条件区域相反。
n必须以绝对引用的方式引用数据清单或数据库中之必须以绝对引用的方式引用数据清单或数据库中之外的单元格。
外的单元格。
n必须以相对引用的方式引用数据清单或数据库中之必须以相对引用的方式引用数据清单或数据库中之内的单元格。
内的单元格。
5.2数据筛选数据筛选n基于计算条件的高级筛选案例基于计算条件的高级筛选案例n某商场有某商场有2月月1号到号到8号的销售记录如图的号的销售记录如图的A2:
F10单元格区域所示,现在要找出销售额高于平均值的销单元格区域所示,现在要找出销售额高于平均值的销售记录。
售记录。
5.3数据的分类与汇总数据的分类与汇总1、概述、概述n分类汇总就是对工作表中指定的行或列中的数据进行分类汇总就是对工作表中指定的行或列中的数据进行汇总统计,它通过折叠或展开原工作表中的行、列数汇总统计,它通过折叠或展开原工作表中的行、列数据及汇总结果,从汇总和明细两种角度显示数据,可据及汇总结果,从汇总和明细两种角度显示数据,可以快捷地创建各种汇总报告。
分类汇总的数据折叠层以快捷地创建各种汇总报告。
分类汇总的数据折叠层次可达次可达8层。
层。
2、分类汇总能够完成以下事性、分类汇总能够完成以下事性n在数据表中显示一组或多组数据的分类汇总及总和。
在数据表中显示一组或多组数据的分类汇总及总和。
n在分组数据上完成不同的计算,如求和、统计个数、在分组数据上完成不同的计算,如求和、统计个数、求平均数、求最大(最小值)等。
求平均数、求最大(最小值)等。
5.3数据的分类与汇总数据的分类与汇总n案例案例n右图是某公司的职工右图是某公司的职工工资表。
工资表。
n完成以下事情:
完成以下事情:
n每部门有多少人;每部门有多少人;n每部门平均工资;每部门平均工资;n哪个部门的总评奖哪个部门的总评奖金最高,金最高,n应分发给各部门的应分发给各部门的总金额各是多少总金额各是多少通过函数能够完成通过函数能够完成这些事件,但分类这些事件,但分类汇总简单多了。
汇总简单多了。
5.3数据的分类与汇总数据的分类与汇总3、分类汇总的准备工作、分类汇总的准备工作n分类汇总表的每个数据列都有列标题;分类汇总表的每个数据列都有列标题;n必须对要进行分类汇总的列排序。
必须对要进行分类汇总的列排序。
n排序的列标题称为分类汇总关键字,在进行分类汇排序的列标题称为分类汇总关键字,在进行分类汇总时,只能指定排序后的列标题为汇总关键字。
总时,只能指定排序后的列标题为汇总关键字。
按部门按部门排序后排序后的工作的工作表表5.3数据的分类与汇总数据的分类与汇总4、建立分类汇总、建立分类汇总n选择“数据”选择“数据”|“分类汇总”菜单。
分类汇总”菜单。
n1、指定分类字段、指定分类字段2、指定汇总方式、指定汇总方式3、指定汇总字段、指定汇总字段4、指定汇总结果、指定汇总结果的保存方式的保存方式5.3数据的分类与汇总数据的分类与汇总5、汇总结果的保存方式1.1.替换当前分类汇总。
选择这种方式时,最后替换当前分类汇总。
选择这种方式时,最后一次的汇总会取代以前的分类汇总。
一次的汇总会取代以前的分类汇总。
2.2.每组数据分页。
选择这种方式时,各种不同每组数据分页。
选择这种方式时,各种不同的分类数据将被分页保存。
的分类数据将被分页保存。
3.3.汇总结果显示在数据下方。
选择这种方式汇总结果显示在数据下方。
选择这种方式时,原数据的下方会显示汇总计算的结果。
时,原数据的下方会显示汇总计算的结果。
n上述3种方式可同时选中。
Excel的默认选择是第1和第3项。
5.3数据的分类与汇总数据的分类与汇总6、分类汇总结果示例、分类汇总结果示例5.3数据的分类与汇总数据的分类与汇总7、高级分类汇总、高级分类汇总nExcel可以对同一分类进行多重汇总,若要在同一汇总表中显示可以对同一分类进行多重汇总,若要在同一汇总表中显示两个以上的汇总数据,只需对同一数据清单进行两次不同的汇总两个以上的汇总数据,只需对同一数据清单进行两次不同的汇总运算。
第二次分类汇总在第一次的汇总结果上进行。
运算。
第二次分类汇总在第一次的汇总结果上进行。
n在前面的汇总结果基础上,统计每部门的人数。
在前面的汇总结果基础上,统计每部门的人数。
5.3数据的分类与汇总数据的分类与汇总n8.嵌套分类汇总嵌套分类汇总n也是多次分类汇总,但两次的分类汇总关键字也是多次分类汇总,但两次的分类汇总关键字不同不同n如,先统计各部门的平均工资和平均奖金,然如,先统计各部门的平均工资和平均奖金,然后再在每个部门再按照已婚和未婚统计平均工后再在每个部门再按照已婚和未婚统计平均工资和奖金资和奖金5.3数据的分类与汇总数据的分类与汇总9、删除分类汇总、删除分类汇总n对于刚建立的分类汇总,如果要将其恢复为原始数对于刚建立的分类汇总,如果要将其恢复为原始数据表,可以选择“编辑”中的“撤消”菜单项取消据表,可以选择“编辑”中的“撤消”菜单项取消分类汇总,或单击撤消按钮。
分类汇总,或单击撤消按钮。
n但若在建立分类汇总之后,又进行了其他操作,上但若在建立分类汇总之后,又进行了其他操作,上述方法就无效了,可按如下方法删除分类汇总。
述方法就无效了,可按如下方法删除分类汇总。
n
(1)单击分类汇总表中的任一单元格。
)单击分类汇总表中的任一单元格。
n
(2)选择“数据”)选择“数据”|“分类汇总”菜单项分类汇总”菜单项n(3)单击“分类汇总”对话框中的“全部删)单击“分类汇总”对话框中的“全部删除”按钮。
除”按钮。
5.4数据透视表数据透视表1.1.数据透视表的功能数据透视表的功能n数据透视表不仅能够改变数据表的行、列布局,而数据透视表不仅能够改变数据表的行、列布局,而且能够快速汇总大量数据。
也能够基于原数据表创且能够快速汇总大量数据。
也能够基于原数据表创建数据分组,对建立的分组进行汇总统计。
建数据分组,对建立的分组进行汇总统计。
n5.4数据透视表数据透视表2、源数据表与数据透视表的对应关系、源数据表与数据透视表的对应关系5.4数据透视表数据透视表3、案例、案例n假设某电视销售商场有假设某电视销售商场有6位职工,分别是劳得诺、令狐位职工,分别是劳得诺、令狐冲、陆大安、任我行、韦小宝、向问天,销售的电视产品冲、陆大安、任我行、韦小宝、向问天,销售的电视产品有有TCL、长虹、康佳、创维、熊猫几种品牌。
每天的销、长虹、康佳、创维、熊猫几种品牌。
每天的销售数据都记录在售数据都记录在Excel的一个工作表中,如图所示,该数的一个工作表中,如图所示,该数据表有据表有300多行数据。
多行数据。
5.4数据透视表数据透视表n完成以下统计分析工作完成以下统计分析工作n统计各种品牌的电视的总销售额;统计各种品牌的电视的总销售额;n统计每位职工的总销售额;统计每位职工的总销售额;n统计每位职工销售的各种类型的电视机的销售额;统计每位职工销售的各种类型的电视机的销售额;n统计各种运输方式的总数。
统计各种运输方式的总数。
数据透视表能够很方便地完成这些工作。
数据透视表能够很方便地完成这些工作。
5.4数据透视表数据透视表4、建立数据透视表的过程、建立数据透视表的过程n选择“数据”选择“数据”|“数据透视表和数据透视图”数据透视表和数据透视图”n按向导提示做完第一、二步。
在最后的第三步向导按向导提示做完第一、二步。
在最后的第三步向导对话框中,选择“布局”按钮。
会见到如图所示的对话框中,选择“布局”按钮。
会见到如图所示的对话框。
对话框。
5.4数据透视表数据透视表n其中n行n拖放到行中的数据字段,该字段中的第一个数据项拖放到行中的数据字段,该字段中的第一个数据项将占据透视表的一行。
将占据透视表的一行。
n列n与行相对应,放置在列中的字段,该字段中的每个与行相对应,放置在列中的字段,该字段中的每个项将占一列。
项将占一列。
n页n行和列相当于行和列相当于X轴和轴和Y轴,由它们确定一个二维表轴,由它们确定一个二维表格,页则相当于格,页则相当于Z轴。
拖放在页中的字段,轴。
拖放在页中的字段,Excel将按该字段的数据项对透视表进行分页。
将按该字段的数据项对透视表进行分页。
n数据n进行计数或汇总的字段名称。
进行计数或汇总的字段名称。
5.4数据透视表数据透视表n统计各销售员销售数据的透视表布局统计各销售员销售数据的透视表布局5.4数据透视表数据透视表n统计各销售员销售数据的透视表统计各销售员销售数据的透视表双击任一单元双击任一单元格,会得到这格,会得到这些数据的来源些数据的来源明细数据明细数据5.4数据透视表数据透视表n双击上表双击上表E5单元格得到的明细数据表单元格得到的明细数据表5.4数据透视表数据透视表n利用页字段创建分页显示利用页字段创建分页显示页字段页字段5.4数据透视表数据透视表n5修改数据透视表n增加或减少行、列中的数据项n重新组织数据透视表重新组织数据透视表n行、列置换n在字段内移动个别项5.4数据透视表数据透视表n6、使用多重数据字段建立透视表n5.4数据透视表数据透视表7、修改数据透视表的概要函数、修改数据透视表的概要函数n数据透视表中使用的透视函数称为概要函数,在默认情况下,数据透数据透视表中使用的透视函数称为概要函数,在默认情况下,数据透视表采用视表采用SUM函数对其中的数值项进行汇总,用函数对其中的数值项进行汇总,用COUNT函数对表中函数对表中的文本类型字段项进行计数。
的文本类型字段项进行计数。
nExcel提供了许多概要函数,如下图所示。
提供了许多概要函数,如下图所示。
5.4数据透视表数据透视表8、修改透视表数据的显示方式、修改透视表数据的显示方式以百分比显示数据5.4数据透视表数据透视表9、制作数据透视图表5.5数据链接、嵌入数据链接、嵌入n1、概述、概述n链接就是指两个不同工作表或不同文档(可以是不同应用程序建链接就是指两个不同工作表或不同文档(可以是不同应用程序建立的文档,如立的文档,如WORD和和EXCEL之间)存在的数据连接。
之间)存在的数据连接。
n链接让一个工作簿可以共享另一个工作薄中的数据,可以链接单链接让一个工作簿可以共享另一个工作薄中的数据,可以链接单元格、单元格区域、命名公式、常量或工作表。
元格、单元格区域、命名公式、常量或工作表。
n总的说来,链接具有以下优点:
总的说来,链接具有以下优点:
n在不同的工作簿和工作表之间,可以进行数据共享。
在不同的工作簿和工作表之间,可以进行数据共享。
n小工作簿比大工作簿的运行效率更高。
小工作簿比大工作簿的运行效率更高。
n分布在不同地域中的数据管理可以在不同的工作簿中完成,分布在不同地域中的数据管理可以在不同的工作簿中完成,通过超级链接可以进行远程数据采集、更新和汇总。
通过超级链接可以进行远程数据采集、更新和汇总。
n可以在不同的工作薄中修改、更新数据,大家可以同时工可以在不同的工作薄中修改、更新数据,大家可以同时工作。
如果所有的数据都存于一个工作表中,就增加了多人合作。
如果所有的数据都存于一个工作表中,就增加了多人合作办公的难度。
作办公的难度。
5.5数据链接、嵌入数据链接、嵌入n2、建立、建立EXCEL工作薄之间的链接工作薄之间的链接n【例【例5】某茶厂生产的茶叶销售于西南地区,每个季度的销售】某茶厂生产的茶叶销售于西南地区,每个季度的销售统计分别在不同的工作簿中,下图就是第一、二季度的茶叶销售统计分别在不同的工作簿中,下图就是第一、二季度的茶叶销售统计工作薄示例。
统计工作薄示例。
5.5数据链接、嵌入数据链接、嵌入n建立一新工作薄,将这前面两不同工作薄中的工作表连接在该工建立一新工作薄,将这前面两不同工作薄中的工作表连接在该工作薄。
作薄。
本工作薄中的两个工作表是从其它工作本工作薄中的两个工作表是从其它工作薄链接过来的。
修改原工作表中的数据薄链接过来的。
修改原工作表中的数据时,本工作薄中的数据会立即更新!
时,本工作薄中的数据会立即更新!
5.5数据链接、嵌入数据链接、嵌入n3、将、将Excel的工作表或图表链接到其他应用程序的工作表或图表链接到其他应用程序n案例案例n某茶叶厂第一季度的销售数据表及销售对比柱形图如图所示。
某茶叶厂第一季度的销售数据表及销售对比柱形图如图所示。
n现要将此图表作为在现要将此图表作为在Word工作报表中的一部份,以使报告更有说服力工作报表中的一部份,以使报告更有说服力5.5数据链接、嵌入数据链接、嵌入n链接到链接到Word报告中的报告中的Excel工作表工作表5.5数据链接、嵌入数据链接、嵌入n4、将、将Excel的工作表或图表嵌入到其他应用程序的工作表或图表嵌入到其他应用程序n案例案例n某茶叶厂第一季度的销售数据表及销售对比柱形图如图所示。
某茶叶厂第一季度的销售数据表及销售对比柱形图如图所示。
5.5数据链接、嵌入数据链接、嵌入n嵌入到嵌入到Word报告中的报告中的Excel工作表工作表5.6合并计算合并计算1、概念、概念n所谓合并计算,就是对所谓合并计算,就是对来源于同一工作簿的不来源于同一工作簿的不同工作表,或不同工作同工作表,或不同工作簿中的数据表进行合并簿中的数据表进行合并计算。
计算。
Excel支持将不支持将不超过超过255个工作表中个工作表中的信息收集到一个主工的信息收集到一个主工作表中,支持的工作表作表中,支持的工作表可在同一个工作簿中,可在同一个工作簿中,也可来源于不同的工作也可来源于不同的工作簿。
簿。
5.6合并计算合并计算n案例案例n某电视机厂生产某电视机厂生产21英寸、英寸、25英寸、英寸、29英寸及英寸及34英寸几种规格的电视产品,主要销售于西南地区。
该英寸几种规格的电视产品,主要销售于西南地区。
该厂每个季度进行一次销售统计,每次统计分析保存在厂每个季度进行一次销售统计,每次统计分析保存在一个独立的工作簿中一个独立的工作簿中5.6合并计算合并计算n建立合并计算建立合并计算5.6合并计算合并计算n合并计算的结果合并计算的结果本表是对本表是对前面四个前面四个工作薄数工作薄数据的合并据的合并结果结果5.6合并计算合并计算n保存明细数据的合并计算结果保存明细数据的合并计算结果TheEnd谢谢大家!
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据管理 Excel 数据 透视