EXCEL技巧数据管理与透视分析.ppt

《EXCEL技巧数据管理与透视分析.ppt》由会员分享，可在线阅读，更多相关《EXCEL技巧数据管理与透视分析.ppt（48页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、本章本章学习目标学习目标1 1、了解、了解Excel2003Excel2003的列表功能的列表功能2 2、掌握记录单的应用方法、掌握记录单的应用方法3 3、掌握数据排序的方法、掌握数据排序的方法4 4、掌握数据筛选的方法、掌握数据筛选的方法5 5、掌握工作表数据的分类汇总方法、掌握工作表数据的分类汇总方法6 6、掌握数据透视表的功能及用法、掌握数据透视表的功能及用法7.1 用列表管理数据1、关于列表n列表是Excel2003新加的功能。它能够自动扩展或缩小其数据区域。可方便地实现可变区域的数据处理与计算问题。n同普通工作表相比，列表具有许多优点。它能够对数据进行排序和筛选，可按升序或降序对列表

2、进行排序，或创建自定义排序次序。还可筛选列表以仅显示符合指定条件的数据。n在公式或函数中引用列表区域时，如果列表区域发生了变化，公式或函数中的列表区域会自动更新 7.1 用列表管理数据2、认识列表标题行边界框插入行汇总行聚合函数 7.1 用列表管理数据3、建立列表的方法（1）选择要建立列表的工作表区域；（2）选择“数据”|“列表”菜单中的“创建列表”菜单项。7.2 记录单1、案例n某超市销售的商品多达数百种，该超市的产品库存及订货数据表如下图所示。n查看、修改、更新各种某种产品的库存量、订购量、查看、修改、更新各种某种产品的库存量、订购量、再订购量及产品单价等数据。如数据行较多，要找到再订购量

3、及产品单价等数据。如数据行较多，要找到某数据行就有难度了。某数据行就有难度了。但记录单提供了这类操作的方但记录单提供了这类操作的方便！便！“上一行上一行”、“下一行下一行”可可进行逐行数据查看。进行逐行数据查看。7.2 记录单1、使用记录单修改、删除或增加记录 选择选择“数据数据”|“记录单记录单”菜单命令菜单命令，Excel就会显就会显示工作表的记录单！示工作表的记录单！“新建新建”可在数据表最后一可在数据表最后一行的下面添加一行行的下面添加一行“删除删除”将删除工作中与记将删除工作中与记录单内容相对应的数据行录单内容相对应的数据行7.2 记录单2、用数据单进行条件查询 n单击记录单中的“条

4、件”就能进行条件查询！条件！条件！7.3数据排序1、排序规则、排序规则n数字排序规则数字排序规则n字母排序规则字母排序规则n逻辑值排序规则逻辑值排序规则n汉字排序规则汉字排序规则n汉语拼音序汉语拼音序n按笔画排序按笔画排序2、排序方式、排序方式n升序升序n降序降序7.3数据排序3、排序案例n某公司2004年1到3月份职工的奖金表如图（a）所示。图（b）是按奖金进行降序排序后的工作表。7.3数据排序n按日期排序7.3数据排序n按汉字笔画排序7.4数据筛选1、概念n数据筛选就是将数据表中所有不满足条件的记录行暂时隐藏起来，只显示那些满足条件的数据行。2、Excel的数据筛选方式n1、自动筛选n2、

5、高级筛选7.4数据筛选3、自动筛选n案例某超市销售的产品多达几百种，该超市在Excel数据表中管理各种销售商品的订购数据。n（1）查询库存量已小于3，需要及时订购的产品。n（2）查询单价最高的前10种产品。n（3）查询已经中止订购关系的供应商。n（4）查询所销售饮料有哪些品种。7.4数据筛选选择选择“数据数据”|“筛选筛选”|“自动筛选自动筛选”，Excel会显示出会显示出自动筛选标志。可借此完成各种自动筛选！自动筛选标志。可借此完成各种自动筛选！原数据原数据表表具有自具有自动筛标动筛标记的数记的数据表据表7.4数据筛选n n自动筛选的结果示例筛选出所有的海鲜7.4数据筛选4、高级筛选n高级筛

6、选的条件区域 的类型n（1）“或或”条件条件n（2）“与与”条件条件n（3）多列的）多列的“或或”条件条件n（4）用）用“或或”连接的连接的“与与”条件条件7.4数据筛选条件区域的构建方法条件区域的构建方法7.4数据筛选n n高级筛选案例n某商场的商品订购与库存情况如图第6行以后的表格所示。筛选出库存量不超过20，再订购量高于20的饮料，以及库存量不少于于60，再订购量不高于30的调味品。7.4数据筛选n n使用计算条件的高级筛选n什么是计算条件n计算条件的构造方法n计算条件中的标题可以是任何文本或都是空白，但不能与数据清单中的任一列标相同，这一点正好与前面讨论的条件区域相反。n必须以绝对引用

7、的方式引用数据清单或数据库中之外的单元格。n必须以相对引用的方式引用数据清单或数据库中之内的单元格。7.4数据筛选n n基于计算条件的高级筛选案例n某商场有12月1号到8号的销售记录如图的A7:E15单元格区域所示，现在要找出销售额高于平均值的销售记录。7.5 数据的分类与汇总1、概述n分类汇总实际上就是对工作表中指定的行或列中的数据进行汇总统计，它通过折叠或展开原工作表中的行、列数据及汇总结果，从汇总和明细两种角度显示数据，可以快捷地创建各种汇总报告。分类汇总的数据折叠层次可达8层。2、分类汇总能够完成以下事性n在数据表中显示一组或多组数据的分类汇总及总和。n在分组数据上完成不同的计算，如求

8、和、统计个数、求平均数、求最大（最小值）等。7.5 数据的分类与汇总n n案例n右图是某公司的职工工资表。n完成以下事情：n每部门有多少人；n每部门平均工资；n哪个部门的总评奖金最高，n应分发给各部门的总金额各是多少 通过函数能够完成通过函数能够完成这些事件，但分类这些事件，但分类汇总简单多了。汇总简单多了。7.5 数据的分类与汇总3、分类汇总的准备工作 n分类汇总表的每个数据列都有列标题；n必须对要进行分类汇总的列排序。n这这个个排排序序的的列列标标题题称称为为分分类类汇汇总总关关键键字字，在在进进行行分分类类汇汇总时，只能指定排序后的列标题为汇总关键字。总时，只能指定排序后的列标题为汇总关

9、键字。按部门按部门排序后排序后的工作的工作表表7.5 数据的分类与汇总4、建立分类汇总n选择“数据”|“分类汇总”菜单。n 1、指定分类字段、指定分类字段2、指定汇总方式、指定汇总方式3、指定汇总字段、指定汇总字段4、指定汇总结果、指定汇总结果的保存方式的保存方式7.5 数据的分类与汇总5、汇总结果的保存方式、汇总结果的保存方式1.替替换换当当前前分分类类汇汇总总。选选择择这这种种方方式式时时，最最后后一一次次的汇总会取代以前的分类汇总。的汇总会取代以前的分类汇总。2.每每组组数数据据分分页页。选选择择这这种种方方式式时时，各各种种不不同同的的分分类数据将被分页保存。类数据将被分页保存。3.汇

10、汇总总结结果果显显示示在在数数据据下下方方。选选择择这这种种方方式式时时，原原数据的下方会显示汇总计算的结果。数据的下方会显示汇总计算的结果。n上上述述3种种方方式式可可同同时时选选中中。Excel的的默默认认选选择择是是第第1和和第第3项。项。7.5 数据的分类与汇总6、分类汇总结果示例7.5 数据的分类与汇总7、多次分类汇总 nExcel可以对同一分类进行多重汇总，若要在同一汇总表中显示两个以上的汇总数据，只需对同一数据清单进行两次不同的汇总运算。第二次分类汇总在第一次的汇总结果上进行。7.5 数据的分类与汇总8、删除分类汇总 n对于刚建立的分类汇总，如果要将其恢复为原始数据表，可以选择“

11、编辑”中的“撤消”菜单项取消分类汇总，或单击撤消按钮。n但若在建立分类汇总之后，又进行了其他操作，上述方法就无效了，可按如下方法删除分类汇总。n（1）单击分类汇总表中的任一单元格。n（2）选择“数据”|“分类汇总”菜单项n（3）单击“分类汇总”对话框中的“全部删除”按钮。7.6 数据透视表1.1.数据透视表的功能n数据透视表不仅能够改变数据表的行、列布局，而且能够快速汇总大量数据。也能够基于原数据表创建数据分组，对建立的分组进行汇总统计。n 7.6 数据透视表2、源数据表与数据透视表的对应关系数据源分页：相当于Z轴行：相当于X轴字段：数据源中的列标题项：某列的单元格中的内容 7.6 数据透视表

12、3、案例n某某电电视视销销售售商商场场有有6位位职职工工，分分别别是是张张维维芳芳、黄黄梅梅翠翠、李李光光病病、江江月月、王王明明明明、黄黄海海鑫鑫。该该商商场场销销售售的的电电视视产产品品有有TCL、长长虹虹、康康佳佳、创创维维、熊熊猫猫几几种种品品牌牌。每每天天的的销销售售数数据记录在据记录在Excel的一个工作表中，如下图所示。的一个工作表中，如下图所示。7.6 数据透视表n n完成以下统计分析工作完成以下统计分析工作完成以下统计分析工作完成以下统计分析工作n统计各种品牌的电视的总销售额统计各种品牌的电视的总销售额；n统计每位职工的总销售额；统计每位职工的总销售额；n统计每位职工销售的各

13、种类型的电视机的销售额；统计每位职工销售的各种类型的电视机的销售额；n统计各种运输方式的总数。统计各种运输方式的总数。数据透视表能够很方便地完成这些工作。数据透视表能够很方便地完成这些工作。7.6 数据透视表4、建立数据透视表的过程n选择“数据”|“数据透视表和数据透视图”n按向导提示做完第一、二步。在最后的第三步向导对话框中，选择“布局”按钮。会见到如图所示的对话框。7.6 数据透视表n n其中其中n行行n拖放到行中的数据字段，该字段中的第一个数据项将占据透视表的一行。n列列n与行相对应，放置在列中的字段，该字段中的每个项将占一列。n页页n行和列相当于X轴和Y轴，由它们确定一个二维表格，页则

14、相当于Z轴。拖放在页中的字段，Excel将按该字段的数据项对透视表进行分页。n数据数据n进行计数或汇总的字段名称。7.6 数据透视表n n统计各销售员销售数据的透视表布局7.6 数据透视表n n统计各销售员销售数据的透视表双击任一单元双击任一单元格，会得到这格，会得到这些数据的来源些数据的来源明细数据明细数据7.6 数据透视表n n双击上表E5单元格得到的明细数据表7.6 数据透视表n n5 修改数据透视表修改数据透视表n增加或减少行、列中的数据项增加或减少行、列中的数据项 n重新组织数据透视表 n行、列置换行、列置换 n在字段内移动个别项在字段内移动个别项 7.6 数据透视表n n6、使用多

15、重数据字段建立透视表、使用多重数据字段建立透视表 n 7.6 数据透视表7、修改数据透视表的概要函数 n数数据据透透视视表表中中使使用用的的透透视视函函数数称称为为概概要要函函数数，在在默默认认情情况况下下，数数据据透透视视表表采采用用SUM函函数数对对其其中中的的数数值值项项进进行行汇汇总总，用用COUNT函函数数对对表表中中的的文文本类型字段项进行计数。本类型字段项进行计数。nExcel提供了许多概要函数，如下图所示。提供了许多概要函数，如下图所示。7.6 数据透视表8、修改透视表数据的显示方式 以百分比显示数据7.6 数据透视表9、制作数据透视图表、制作数据透视图表7.7 合并计算n n概念概念n所谓合并计算，就是对来源于同一工作簿的不同工作表，或不同工作簿中的数据表进行合并计算。Excel支持将不超过255个工作表中的信息收集到一个主工作表中，支持的工作表可在同一个工作簿中，也可来源于不同的工作簿。7.7 合并计算n n案例n某电视机厂生产21英寸、25英寸、29英寸及34英寸几种规格的电视产品，主要销售于西南地区。该厂每个季度进行一次销售统计，每次统计分析保存在一个独立的工作簿中 7.7 合并计算n n合并计算的结果本表是对本表是对前面四个前面四个工作薄数工作薄数据的合并据的合并结果结果7.7 合并计算n n保存明细数据的合并计算结果The End