数据管理与分析.ppt
《数据管理与分析.ppt》由会员分享,可在线阅读,更多相关《数据管理与分析.ppt(75页珍藏版)》请在三一文库上搜索。
1、第五讲 数据管理与分析,Overview 概述 数据列表 数据排序 数据筛选 分类汇总 使用数据透视表 数据导入与导出,Excel具有强大的数据管理与分析能力,能够对工作表中的数据进行排序、筛选、分类汇总等,还能够使用数据透视表对工作表的数据进行重组,对特定的数据行或数据列进行各种概要分析,并且可以生成数据透视图,直观的表示分析结果。,概述,由于排序与筛选数据记录的操作需要通过“数据列表”来进行,因此在操作前应先创建好“数据列表”。 “数据列表”是工作表中包含相关数据的一系列数据行,如前面所建立的销售清单和销售统计表,就包含有这样的数据行,它可以像数据库一样接受浏览与编辑等操作。,1.数据列表
2、,数据列表的创建方法如下:选定要创建列表的数据区域,然后选择Excel的“数据”“列表”“创建列表”菜单项。例如,要建立如图5-1所示的销售记录数据列表,应选中A1:O36区域,然后选择“数据”“列表”“创建列表”菜单项建立数据列表。 实际上,如果一个工作表只有一个连续数据区域,并且这个数据区域的每个列都有列标题,那么系统会自动将这个连续数据区域识别为数据列表。如图5-2。,创建数据列表,图5-1 建立数据列表,图5-2 自动识别为数据列表,将类型相同的数据项置于同一列中 使数据列表独立于其它数据 将关键数据置于列表的顶部或底部 注意显示行和列 注意数据列表格式 使用单元格边框突出显示数据列表
3、 避免空行和空列,数据列表-数据输入准则,当数据表或列表中的数据记录太多时,要查看、修改或编辑其中的某条记录很困难,为了解决这个问题,Excel提供了记录单功能。 只有每列数据都有标题的工作表才能够使用记录单功能。单击销售记录数据列表中的任一单元格,从“数据”下拉菜单中选择“记录单”命令,进入图5-3所示的数据记录单对话框就能完成这些操作。,使用记录单,记录单具有条件查询功能,允许使用通配符查找,即用“*”代替不可知的任意长度的任何符号,,图5-3 记录单,数据排序的功能是按一定的规则对数据进行整理和排列,为进一步处理数据做好准备。Excel 提供了多种对数据列表进行排序的方法,既可以按升序或
4、降序进行排序,也可以按用户自定义的方式进行排序。 例如,每月商品的销量排名情况就需要对商品销售数量进行排序,从中可以得出卖得最好的商品或卖得最差的商品。,2 数据排序,使用“数据”下拉菜单的“排序”命令,打开“排序”对话框。,排序对话框,主要关键字 选择排序字段,按升序或降序排序。 次要关键字 “主要关键字”列中出现了重复项,就按次要关键字来排序重复的部分。 第三关键字 “主要关键字”与“次要关键字”列中都出现了重复项,就按第三关键字来排序重复的部分。 有标题行:排序时包含列表第一行。 无标题行:排序时不包含列表第一行。,图5-4 排序对话框,现在我们使用普通排序来完成任务1,对图5-2中的销
5、售数量进行降序排序,得到商品销量排名,排在第一的即是月度销售冠军;对利润金额进行降序排序,则得到商品利润排名,排在第一的即是利润最高的商品。,a.普通排序,步骤1:单击源工作表中的任一单元格,或选中要排序的整个单元格区域。本例中,可单击A3:G30中的任一单元格,也可以选择整个A3:G30区域。 步骤2:选择“数据”“排序”菜单项,系统会显示图5-4所示的对话框。 步骤3:从对话框中“主要关键字”下拉列表中选择排序关键字,选择“销售数量”。 步骤4:指定排序方式,由于需要找出月度销售冠军,因此选择“降序”作为排序的方式。 步骤5:将“有标题行”单选钮选中,然后单击“确定”按钮,Excel就会对
6、源工作表中的数据按销售数量从高到低进行重新排列,其结果如图5-5所示。,操作步骤,如何找出利润最高的商品?,图5-5 对销售数量进行降序排序后的工作表,有时,我们需要按照一种指定的次序进行排序,而不是按照数值或者文本的顺序排序。例如在任务1中,店主需要知道本月各个类别的商品销售排名情况,商品类别要按照“手机、相机、MP4、MP3、储存卡”这个自定义顺序排列,而不是字母或笔画顺序。 要完成这样的排序,需要先建立一个自定义序列。,b. 自定义排序,步骤1:选择“工具”“选项”菜单项,系统弹出如图5-6所示的对话框。 步骤2:选择“选项”对话框中的“自定义序列”标签,然后在该对话框中的“输入序列”编
7、辑框中输入自定义序列,每输入一个类别后按一下回车键。 步骤3:输入完成后,单击“确定”按钮,就将这个用户自定义序列添加到了系统中。,自定义序列,图5-6 自定义序列,步骤1:单击图5-2中A3:G30中的任一单元格,也可以选择整个A3:G30区域。然后选择“数据”“排序”菜单项。 步骤2:在弹出的“排序”对话框中,单击“选项”按钮,系统将弹出“排序选项”对话框,如图5-7所示。 步骤3:在 “自定义排序次序”下拉列表中选择前面建立的自定义序列,按“确定”按钮回到“排序”对话框。 步骤4:在“排序”对话框中选择“类别”作为主关键字,排序方式为“升序”,选择“销售数量”作为次要关键字,排序方式为“
8、降序”,按“确定”按钮皆可得到如图5-8所示的结果。,对销售统计表自定义排序,图5-8 自定义排序后的结果,图5-7 排序选项,数字:根据其值的大小从小到大排序。 文本和包含数字的文本:按字母顺序对文本项进行排序。 逻辑值:False排在True之前。 错误值:所有的错误值都是相等的。 空白(不是空格):空白单元格总是排在最后。 汉字:汉字有两种排序方式:汉语拼音和笔画,排序规则,数据筛选是一种用于查找数据的快速方法,筛选将数据列表中所有不满足条件的记录暂时隐藏起来,只显示满足条件的数据行,以供用户浏览和分析。Excel提供了自动和高级两种筛选数据的方式。,3.数据筛选,自动筛选为用户提供了在
9、具有大量记录的数据列表中快速查找符合某些条件的记录的功能。筛选后只显示出包含符合条件的数据行,而隐藏其它行。 在任务2中,为了及时跟踪各个类别与各个品牌的商品销售情况,需要从图5-1中的销售清单中查询相关信息,可以通过自动筛选获取上述信息。,a.自动筛选,以“类别”字段的筛选作为例子,具体操作步骤如下。 步骤1:单击图5-1中销售清单中的任一单元格。 步骤2:选择“数据”“筛选”“自动筛选”菜单项。数据列表中第一行的各列中将分别显示出一个下拉按钮,如图5-1所示,自动筛选就将通过它们进行。 步骤3:单击需要进行筛选的列标的下拉列表,Excel会显示出该列中所有不同的数据值,这些值可用于筛选条件
10、,如单击“类别”旁边的下拉列表,会显示出“类别”列中所有的值,如图5-9所示。 步骤4:如要查看“手机”的销售情况,只需在下拉列表中选择“手机”,系统就会显示如图5-10所示的结果。,对销售清单自动筛选,图5-9自动筛选,图5-10自动筛选后的结果,对库存清单自动筛选,在任务2中,我们还需对库存清单进行筛选,找出库存最大的前5种商品,给店主张某提供进货的参考,库存清单如图5-11所示。,图5-11 库存清单,点击“自动筛选”命令后,系统添加下拉列表标志,我们要筛选出5种库存最大的商品,应单击“期末库存”列标的下拉列表,然后选择列表中的“(前10个)”,Excel会弹出显示个数设置的对话框,如图
11、5-12所示。在 “显示”的下拉列表中选择“最大”,然后在编辑框中输入5。筛选结果如图5-13所示。,图5-12 自动筛选前10个,图5-13 自动筛选前10个后的结果,自定义自动筛选,如果要找出库存大于0并且小于等于3的手机的库存情况,需要分别对“期末库存”和“类别”进行两步筛选。第一步自定义自动筛选方式如图5-14所示; 第二步类别筛选同前面例子。,图5-14 自定义自动筛选,自定义筛选只能完成条件简单的数据筛选,如果筛选的条件比较复杂,自定义筛选就会显得比较麻烦。对于筛选条件较多的情况,可以使用高级筛选功能来处理。 使用高级筛选功能,必须先建立一个条件区域,用来指定筛选条件。条件区域的第
12、一行是所有作为筛选条件的字段名,这些字段名与数据列表中的字段名必须一致,条件区域的其它行则输入筛选条件。需要注意的是,条件区域和数据列表不能连接,必须用以空行或空列将其隔开。 条件区域的构造规则是:同一列中的条件是“或”,同一行中的条件是“与”。,b.高级筛选,步骤1:在库存清单中创建一个条件区域,输入筛选条件,这里在I1、J1、K1单元格中分别输入“类别”、“期末库存”、“期末库存”,在I2、J2、K2中分别输入“手机”、“0”、“=3”。 步骤2:选定库存清单数据列表中的任一单元格,然后选择“数据”“筛选”“高级筛选”菜单项,打开如图5-15所示的“高级筛选”对话框。 步骤3:指定数据列表
13、区域和条件区域。 步骤4:指定保存结果的区域。 步骤5:最后单击“确定”按钮。,对库存清单高级筛选,图5-15 高级筛选,现在让我们来完成任务2中的最后一个要求,分析销售统计表,找出销售金额高于平均销售金额的商品。 那么是否可以在高级筛选条件中包含一个平均值计算公式呢?答案是肯定的,Excel的高级筛选允许建立计算条件。建立计算条件须满足下列3条原则。 计算条件中的标题可以是任何文本或空白,不能与数据列表中的任一列标相同,这一点与前面指定的条件区域刚好相反。 必须以绝对引用的方式引用数据列表外的单元格。 必须以相对引用的方式引用数据列表内的单元格。,包含计算条件的高级筛选,步骤1:在单元格I9
14、(或任一空白单元格)中输入平均值计算公式“=median(E4:E30)”,该公式的计算结果为1440。 步骤2:在I1中输入计算条件的列标,其值须满足上述的第1条原则,如输入“高于平均销售金额”。 步骤3:在I2中输入计算条件公式“=E4$I$9”,输入该公式须满足上述的第2、3条规则,E4是数据列表中的单元格,因此只能使用相对引用的方式。I9包含平均值公式,是数据列表之外的单元格,只能采用绝对引用的方式。 计算条件建立好之后,如图5-16所示,按照前面介绍的步骤进行高级筛选,筛选的结果如图5-17所示。,建立计算条件,图5-16 建立计算条件,图5-17 使用计算条件筛选后的结果,分类汇总
15、是对数据列表指定的行或列中的数据进行汇总统计,统计的内容可以由用户指定,通过折叠或展开行、列数据和汇总结果,从汇总和明细两种角度显示数据,可以快捷的创建各种汇总报告。在这一节中,我们将使用分类汇总来完成任务3。,4.分类汇总,Excel可自动计算数据列表中的分类汇总和总计值。当插入自动分类汇总时,Excel将分级显示数据列表,以便为每个分类汇总显示或隐藏明细数据行。Excel分类汇总的数据折叠层次最多可达8层。分类汇总可以完成以下工作。 显示一组数据的分类汇总及总和。 显示多组数据的分类汇总及总和。 在分组数据上完成不同的计算,如求和、统计个数、求平均值(或最大值、最小值)、求总体方差等。,分
16、类汇总概述,在创建分类汇总之前,首先要保证要进行分类汇总的数据区域必须是一个连续的数据区域,而且每个数据列都有列标题;然后必须对要进行分类汇总的列进行排序。这个排序的列标题称为分类汇总关键字,分类汇总时只能指定排序后的列标题为汇总关键字。 例如,对于图5-1所示的销售清单,如果要统计各个类别的商品销售数量,应该先以“类别”字段为主要关键字进行自定义排序,并以“品牌”字段为次要关键字按升序排序,排序后的结果如图5-18所示。,a.创建分类汇总,图5-18 按类别自定义排序后的销售清单,步骤1:单击数据区域中的任一单元格,然后选择“数据”“分类汇总”菜单项,打开如图5-19所示的“分类汇总”对话框
17、。 步骤2:从“分类字段”下拉列表中选择要进行分类的字段,分类字段必须是已经排好序,在本例中,我们选择“类别”作为分类字段。 步骤3:“汇总方式”下拉列表中列出了所有汇总方式(统计个数、计算平均值、求最大值或最小值及计算总和等)。在本例中,我们选择“求和”作为汇总方式。 步骤4:“选定汇总项”的列表中列出了所有列标题,从中选择需要汇总的列,列的数据类型必须和汇总方式相符合。在本例中我们选择“数量”作为汇总项。 步骤5:选择汇总数据的保存方式,有3种方式可以选择,可同时选中,默认选择是第1和第3项。点击“确定”按钮,结果如图5-20所示。,统计各个类别的商品销售数量,图5-19 分类汇总对话框,
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据管理 分析
链接地址:https://www.31doc.com/p-3168500.html