第5章-数据管理与分析ppt课件

.,第五章数据管理与分析,.,Overview5.0概述5.1数据列表5.2数据排序5.3数据筛选5.4分类汇总5.5使用数据透视表5.6数据导入与导出,Chapter5数据管理与分析,.,Excel具有强大的数据管理与分析能力，能够对工作表中的数据进行排序、筛选、分类汇总等，还能够使用数据透视表对工作表的数据进行重组，对特定的数据行或数据列进行各种概要分析，并且可以生成数据透视图，直观的表示分析结果。,5.0概述,.,在这一章中，我们将使用Excel的数据管理与分析功能，对第4章实例中的商品销售清单、销售统计表和库存清单等工作表进行分析。找出月度销售冠军和利润最高的商品；跟踪各个类别与各个品牌的商品销售情况，监测商品的库存情况；对各个类别与各个品牌的商品销售进行分类汇总和数据透视，找出最畅销的类别和品牌，从而帮助店主确定今后的经营方向。,本章目标,.,我们将上面提出的目标进行细分，分为4个任务。任务1：分别按销售数量和利润金额对销售统计表进行排序，找出月度销售冠军和利润最高的商品；按商品的类别自定义排序，得到各个类别的商品销售排名情况。任务2：使用自动筛选功能分析销售清单，跟踪各个类别与各个品牌的商品销售情况；使用自动筛选分析库存清单，得出库存最大的5种商品与库存为0的商品，作为店主进货的依据；使用高级筛选功能分析销售统计表，找出销售金额高于平均销售金额的商品。任务3：使用分类汇总对销售统计表进行汇总，计算各个类别与各个品牌的销售总金额与利润总金额。任务4：使用数据透视表和数据透视图分析销售清单，统计各个类别与各个品牌商品的销售总数量，找出最畅销的类别和品牌。,本章任务,.,由于排序与筛选数据记录的操作需要通过“数据列表”来进行，因此在操作前应先创建好“数据列表”。“数据列表”是工作表中包含相关数据的一系列数据行，如前面所建立的销售清单和销售统计表，就包含有这样的数据行，它可以像数据库一样接受浏览与编辑等操作。,5.1数据列表,.,数据列表的创建方法如下：选定要创建列表的数据区域，然后选择Excel的“数据”“列表”“创建列表”菜单项。例如，要建立如图5-1所示的销售记录数据列表，应选中A1:O36区域，然后选择“数据”“列表”“创建列表”菜单项建立数据列表。实际上，如果一个工作表只有一个连续数据区域，并且这个数据区域的每个列都有列标题，那么系统会自动将这个连续数据区域识别为数据列表。如图5-2。,5.1.1创建数据列表,.,图5-1建立数据列表,图5-2自动识别为数据列表,.,将类型相同的数据项置于同一列中使数据列表独立于其它数据将关键数据置于列表的顶部或底部注意显示行和列注意数据列表格式使用单元格边框突出显示数据列表避免空行和空列,数据列表-数据输入准则,.,当数据表或列表中的数据记录太多时，要查看、修改或编辑其中的某条记录很困难，为了解决这个问题，Excel提供了记录单功能。只有每列数据都有标题的工作表才能够使用记录单功能。单击销售记录数据列表中的任一单元格，从“数据”下拉菜单中选择“记录单”命令，进入图5-3所示的数据记录单对话框就能完成这些操作。,5.1.2使用记录单,.,记录单具有条件查询功能，允许使用通配符查找，即用“*”代替不可知的任意长度的任何符号，,图5-3记录单,.,数据排序的功能是按一定的规则对数据进行整理和排列，为进一步处理数据做好准备。Excel提供了多种对数据列表进行排序的方法，既可以按升序或降序进行排序，也可以按用户自定义的方式进行排序。例如，每月商品的销量排名情况就需要对商品销售数量进行排序，从中可以得出卖得最好的商品或卖得最差的商品。,5.2数据排序,.,使用“数据”下拉菜单的“排序”命令，打开“排序”对话框。,排序对话框,主要关键字选择排序字段，按升序或降序排序。次要关键字“主要关键字”列中出现了重复项，就按次要关键字来排序重复的部分。第三关键字“主要关键字”与“次要关键字”列中都出现了重复项，就按第三关键字来排序重复的部分。有标题行：排序时包含列表第一行。无标题行：排序时不包含列表第一行。,图5-4排序对话框,.,现在我们使用普通排序来完成任务1，对图5-2中的销售数量进行降序排序，得到商品销量排名，排在第一的即是月度销售冠军；对利润金额进行降序排序，则得到商品利润排名，排在第一的即是利润最高的商品。,5.2.1普通排序,.,步骤1：单击源工作表中的任一单元格，或选中要排序的整个单元格区域。本例中，可单击A3:G30中的任一单元格，也可以选择整个A3:G30区域。步骤2：选择“数据”“排序”菜单项，系统会显示图5-4所示的对话框。步骤3：从对话框中“主要关键字”下拉列表中选择排序关键字，选择“销售数量”。步骤4：指定排序方式，由于需要找出月度销售冠军，因此选择“降序”作为排序的方式。步骤5：将“有标题行”单选钮选中，然后单击“确定”按钮，Excel就会对源工作表中的数据按销售数量从高到低进行重新排列，其结果如图5-5所示。,操作步骤,.,如何找出利润最高的商品？,图5-5对销售数量进行降序排序后的工作表,.,有时，我们需要按照一种指定的次序进行排序，而不是按照数值或者文本的顺序排序。例如在任务1中，店主需要知道本月各个类别的商品销售排名情况，商品类别要按照“手机、相机、MP4、MP3、储存卡”这个自定义顺序排列，而不是字母或笔画顺序。要完成这样的排序，需要先建立一个自定义序列。,5.2.2自定义排序,.,步骤1：选择“工具”“选项”菜单项，系统弹出如图5-6所示的对话框。步骤2：选择“选项”对话框中的“自定义序列”标签，然后在该对话框中的“输入序列”编辑框中输入自定义序列，每输入一个类别后按一下回车键。步骤3：输入完成后，单击“确定”按钮，就将这个用户自定义序列添加到了系统中。,自定义序列,.,图5-6自定义序列,.,步骤1：单击图5-2中A3:G30中的任一单元格，也可以选择整个A3:G30区域。然后选择“数据”“排序”菜单项。步骤2：在弹出的“排序”对话框中，单击“选项”按钮，系统将弹出“排序选项”对话框，如图5-7所示。步骤3：在“自定义排序次序”下拉列表中选择前面建立的自定义序列，按“确定”按钮回到“排序”对话框。步骤4：在“排序”对话框中选择“类别”作为主关键字，排序方式为“升序”，选择“销售数量”作为次要关键字，排序方式为“降序”，按“确定”按钮皆可得到如图5-8所示的结果。,对销售统计表自定义排序,.,图5-8自定义排序后的结果,图5-7排序选项,.,数字：根据其值的大小从小到大排序。文本和包含数字的文本：按字母顺序对文本项进行排序。逻辑值：False排在True之前。错误值：所有的错误值都是相等的。空白（不是空格）：空白单元格总是排在最后。汉字：汉字有两种排序方式：汉语拼音和笔画,5.2.3排序规则,.,数据筛选是一种用于查找数据的快速方法，筛选将数据列表中所有不满足条件的记录暂时隐藏起来，只显示满足条件的数据行，以供用户浏览和分析。Excel提供了自动和高级两种筛选数据的方式。,5.2数据筛选,.,自动筛选为用户提供了在具有大量记录的数据列表中快速查找符合某些条件的记录的功能。筛选后只显示出包含符合条件的数据行，而隐藏其它行。在任务2中，为了及时跟踪各个类别与各个品牌的商品销售情况，需要从图5-1中的销售清单中查询相关信息，可以通过自动筛选获取上述信息。,5.3.1自动筛选,.,以“类别”字段的筛选作为例子，具体操作步骤如下。步骤1：单击图5-1中销售清单中的任一单元格。步骤2：选择“数据”“筛选”“自动筛选”菜单项。数据列表中第一行的各列中将分别显示出一个下拉按钮，如图5-1所示，自动筛选就将通过它们进行。步骤3：单击需要进行筛选的列标的下拉列表，Excel会显示出该列中所有不同的数据值，这些值可用于筛选条件，如单击“类别”旁边的下拉列表，会显示出“类别”列中所有的值，如图5-9所示。步骤4：如要查看“手机”的销售情况，只需在下拉列表中选择“手机”，系统就会显示如图5-10所示的结果。,对销售清单自动筛选,.,图5-9自动筛选,图5-10自动筛选后的结果,.,对库存清单自动筛选,在任务2中，我们还需对库存清单进行筛选，找出库存最大的前5种商品，给店主张某提供进货的参考，库存清单如图5-11所示。,图5-11库存清单,.,点击“自动筛选”命令后，系统添加下拉列表标志，我们要筛选出5种库存最大的商品，应单击“期末库存”列标的下拉列表，然后选择列表中的“(前10个)”，Excel会弹出显示个数设置的对话框，如图5-12所示。在“显示”的下拉列表中选择“最大”，然后在编辑框中输入5。筛选结果如图5-13所示。,图5-12自动筛选前10个,图5-13自动筛选前10个后的结果,.,自定义自动筛选,如果要找出库存大于0并且小于等于3的手机的库存情况，需要分别对“期末库存”和“类别”进行两步筛选。第一步自定义自动筛选方式如图5-14所示；第二步类别筛选同前面例子。,图5-14自定义自动筛选,.,自定义筛选只能完成条件简单的数据筛选，如果筛选的条件比较复杂，自定义筛选就会显得比较麻烦。对于筛选条件较多的情况，可以使用高级筛选功能来处理。使用高级筛选功能，必须先建立一个条件区域，用来指定筛选条件。条件区域的第一行是所有作为筛选条件的字段名，这些字段名与数据列表中的字段名必须一致，条件区域的其它行则输入筛选条件。需要注意的是，条件区域和数据列表不能连接，必须用以空行或空列将其隔开。条件区域的构造规则是：同一列中的条件是“或”，同一行中的条件是“与”。,5.3.2高级筛选,.,步骤1：在库存清单中创建一个条件区域，输入筛选条件，这里在I1、J1、K1单元格中分别输入“类别”、“期末库存”、“期末库存”，在I2、J2、K2中分别输入“手机”、“0”、“$I$9”，输入该公式须满足上述的第2、3条规则，E4是数据列表中的单元格，因此只能使用相对引用的方式。I9包含平均值公式，是数据列表之外的单元格，只能采用绝对引用的方式。计算条件建立好之后，如图5-16所示，按照前面介绍的步骤进行高级筛选，筛选的结果如图5-17所示。,建立计算条件,.,图5-16建立计算条件,.,图5-17使用计算条件筛选后的结果,.,分类汇总是对数据列表指定的行或列中的数据进行汇总统计，统计的内容可以由用户指定，通过折叠或展开行、列数据和汇总结果，从汇总和明细两种角度显示数据，可以快捷的创建各种汇总报告。在这一节中，我们将使用分类汇总来完成任务3。,5.4分类汇总,.,Excel可自动计算数据列表中的分类汇总和总计值。当插入自动分类汇总时，Excel将分级显示数据列表，以便为每个分类汇总显示或隐藏明细数据行。Excel分类汇总的数据折叠层次最多可达8层。分类汇总可以完成以下工作。显示一组数据的分类汇总及总和。显示多组数据的分类汇总及总和。在分组数据上完成不同的计算，如求和、统计个数、求平均值（或最大值、最小值）、求总体方差等。,5.4.1分类汇总概述,.,在创建分类汇总之前，首先要保证要进行分类汇总的数据区域必须是一个连续的数据区域，而且每个数据列都有列标题；然后必须对要进行分类汇总的列进行排序。这个排序的列标题称为分类汇总关键字，分类汇总时只能指定排序后的列标题为汇总关键字。例如，对于图5-1所示的销售清单，如果要统计各个类别的商品销售数量，应该先以“类别”字段为主要关键字进行自定义排序，并以“品牌”字段为次要关键字按升序排序，排序后的结果如图5-18所示。,5.4.2创建分类汇总,.,图5-18按类别自定义排序后的销售清单,.,步骤1：单击数据区域中的任一单元格，然后选择“数据”“分类汇总”菜单项，打开如图5-19所示的“分类汇总”对话框。步骤2：从“分类字段”下拉列表中选择要进行分类的字段，分类字段必须是已经排好序，在本例中，我们选择“类别”作为分类字段。步骤3：“汇总方式”下拉列表中列出了所有汇总方式（统计个数、计算平均值、求最大值或最小值及计算总和等）。在本例中，我们选择“求和”作为汇总方式。步骤4：“选定汇总项”的列表中列出了所有列标题，从中选择需要汇总的列，列的数据类型必须和汇总方式相符合。在本例中我们选择“数量”作为汇总项。步骤5：选择汇总数据的保存方式，有3种方式可以选择，可同时选中，默认选择是第1和第3项。点击“确定”按钮，结果如图5-20所示。,统计各个类别的商品销售数量,.,图5-19分类汇总对话框,图5-20分类汇总后的结果,.,在Excel中我们也可以对多项指标进行汇总，并且可以进行嵌套分类汇总。现在让我们来完成任务3，我们需要对销售统计表中的销售金额和利润金额两项指标进行汇总，并且需要对各个类别与各个品牌的商品进行分类汇总，由于每个类别都有多个品牌，因此我们可以先对类别进行分类汇总，然后在此基础上再对品牌进行分类汇总。,嵌套分类汇总,.,步骤1：单击销售统计表中的任一单元格，然后选择“数据”“分类汇总”菜单项，打开“分类汇总”对话框。步骤2：分类字段选择“类别”，汇总方式选择“求和”，在“选定汇总项”下拉列表框中选择“销售金额”和“利润金额”两个字段，按“确定”按钮即可得到如图5-21所示的结果。步骤3：再次选择“数据”“分类汇总”菜单项。分类字段选择“品牌”，汇总方式和汇总项与第2步相同，清除“替换当前分类汇总”复选框。按下“确定”按钮，我们就可以得到如图5-22所示的结果。分类汇总前先将销售统计表按类别和品牌关键字排序。,嵌套分类汇总（续）,.,图5-21一级分类汇总结果,图5-22两级分类汇总结果,下图中“金士顿汇总”错误的显示在“储存卡汇总”的下面，这是Excel2003的一个Bug，出现以下情况时，可能会导致结果的显示顺序不正确。汇总显示在明细数据的下方。一个或多个分类汇总片段仅包含一个项目。,由于销售统计表中储存卡仅包含金士顿一个品牌，而汇总显示在明细数据的下方，因此导致了图5-22中的错误显示。,.,如果由于某种原因，需要取消分类汇总的显示结果，恢复到数据列表的初始状态。其操作步骤如下。步骤1：单击分类汇总数据列表中任一单元格。步骤2：选择“数据”“分类汇总”命令，打开“分类汇总”对话框。步骤3：单击对话框中的“全部删除”按钮即可。经过以上步骤之后，数据列表中的分类汇总就被删除了，恢复成汇总前的数据。第3步中的“全部删除”只会删除分类汇总，不会删除原始数据。,5.4.3删除分类汇总,.,数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格，不仅能够改变行和列以查看源数据的不同汇总结果，也可以显示不同页面以筛选数据，还可以根据需要显示区域中的明细数据。数据透视图则是一个动态的图表，它可以将创建的数据透视表以图表的形式显示出来。在这一节中我们将使用数据透视表和数据透视图来完成任务4。,5.5使用数据透视表,.,5.5.1数据透视表概述,.,数据透视表示例,图5-23数据透视表示例,.,数据透视表的创建可以通过“数据透视表和数据透视图向导”进行。利用向导创建数据透视表需要3个步骤。第1步，选择所创建的数据透视表的数据源类型；第2步，选择数据源的区域；第3步，设计将要生成的数据透视表的版式和选项。下面让我们来完成任务4，从销售清单中创建数据透视表，统计各个类别与各个品牌的销售数量。,5.5.2创建数据透视表,.,步骤1选择数据源与报表类型,图5-24向导步骤1,.,步骤2选定区域步骤3选定显示位置,图5-25向导步骤2,图5-26向导步骤3,.,步骤4设置布局,图5-27向导步骤4,.,生成的数据透视表,图5-28生成的数据透视表,.,创建好数据透视表之后，根据需要有可能要对它的布局、数据项、数据汇总方式与显示方式、格式等进行修改。修改数据透视表的布局修改数据透视表的数据项隐藏或显示行、列中的数据项调整数据项显示的位置修改数据透视表的数据汇总方式和显示方式修改数据透视表的格式,5.5.3修改数据透视表,.,修改数据透视表的布局,图5-29修改数据透视表的布局,.,隐藏或显示行、列中的数据项例如在图5-28中所示的数据透视表中，不想显示第5行，即储存卡的销售数量，可以单击“类别”字段旁边的下拉列表条，系统会显示如图5-30所示的对话框，将对话框中“储存卡”前面的复选标志清除，然后单击“确定”按钮，数据透视表中就没有“储存卡”这一行的数据了。调整数据项显示的位置拖动数据项的名称到合适的位置释放即可。,修改数据透视表的数据项,图5-30行字段列表,.,在默认情况下，数据透视表采用SUM函数对其中的数值项进行汇总，用COUNT函数对文本类型字段项进行计数。但有时SUM和COUNT函数并不能满足透视需要，如平均值、百分比、最大值之类的计算。实际上，Excel提供了很多汇总方式，在数据透视表中可以使用这些函数。操作方法是：双击数据透视表中的数据字段，系统会弹出“数据透视表字段”对话框，如图5-31所示，在对话框中从“汇总方式”中选择需要的函数。,修改数据透视表的数据汇总方式和显示方式,.,默认情况下，数据透视表中的数据是以数值方式显示，不过我们可以根据需要将它修改为其他形式的数据显示形式，如显示为小数、百分数或其他需要的形式。例如把数据显示方式设为“占总和的百分比”。,图5-31数据透视表字段,.,数据透视表的格式也可以修改，可以象格式化工作表一样，将数据透视表的格式设置成各种需要的样式。用户可以使用“自动套用格式”命令将Excel内置的数据透视表格式应用于选中的数据透视表。自动套用数据透视表格式时，首先选定数据透视表，然后单击“数据透视表”工具栏中的“设置报告格式”按钮，打开如图5-32所示的“自动套用格式”对话框。在对话框中选择要应用的格式即可。,修改数据透视表的格式,.,图5-32自动套用格式,.,数据透视图表是利用数据透视的结果制作的图表，数据透视图总是与数据透视表相关联的。如果更改了数据透视表中某个字段的位置，则透视图中与之相对应的字段位置也会改变。数据透视表中的行字段对应于数据透视图中的分类字段，而列字段则对应于数据透视图中的系列字段。数据透视表中的页字段和数据字段分别对应于数据透视图中的页字段和数据字段。,5.5.4制作数据透视图,.,在“数据透视表和数据透视图向导”的第1步中将所需创建的报表类型选为“数据透视图（及数据透视表）”，这样就会同时创建数据透视表和数据透视图。如果已经单独创建了数据透视表，那么只要单击常用工具栏中的“图表向导”或“数据透视表”工具栏中的“图表向导”按钮，系统会自动插入一个新的数据透视图。,数据透视图的创建方法,.,图5-33与图28中数据透视表对应的数据透视图,.,修改数据透视图的图表类型和选项,上一节中我们使用“自动套用格式”去修改数据透视表的格式，而数据透视图则可以修改它的图表类型和图表选项。单击“图表”菜单的“图表类型”或“图表选项”菜单项即可打开相应对话框,图5-34修改图表类型,图5-35修改图表选项,.,在进行数据管理与分析的过程中，有时需要将数据导入到Excel工作表中，有时则要将Excel工作表导出为其他类型的数据。本节介绍Excel常用的导入导出功能。,5.6数据的导入与导出,.,在进行数据管理与分析的过程中，有时需要将网页中的数据导入到Excel工作表中。例如店主张某可能需要获取最新的手机报价，然后据此调整店铺的手机卖价。Excel提供了导入Web页数据的功能。,5.6.1导入Web数据,.,步骤1：首先要连上Internet网络，保证可以访问Internet上的Web网站。步骤2：选择“数据”“导入外部数据”“新建Web查询”菜单项，打开“新建Web查询”对话框，如图5-36所示。步骤3：在图5-36中“地址”文本框中输入要导入数据的Web页的网络地址，然后单击“转到”按钮，系统会显示完整的Web页。本例中显示的是太平洋电脑网的手机频道。步骤4：单击Web页数据区域中要导入数据前的按钮，它会变成。步骤5：单击“导入”按钮，系统会弹出设置数据放置位置的对话框，如图5-37所示。步骤6：设置了数据的放置位置后，单击“确定”按钮，系统就会将选定的数据导入到工作表中，结果如图5-38所示。,操作步骤,.,图5-36新建web查询,图5-38导入数据后的工作表,图5-37导入数据对话框,.,与Excel交换数据的文本文件通常是带分隔符的文本文件(.txt)，一般用制表符分隔文本的每个字段，也可以使用逗号分隔文本的每个字段。导入文本文件有两种方法，可以通过打开文本文件的方式来导入，也可以通过导入外部数据的方式来导入。我们以导入一个存放有客户资料的文本文件为例，如图5-39所示，介绍导入文本文件的操作过程。Excel工作表中的数据可以直接保存为文本文件。方法是：使用“文件”“另存为”菜单项将Excel工作表转换为文本文件。,5.6.2文本文件的导入与导出,.,图5-39客户资料.txt,图5-40打开文件对话框,.,步骤1：启动Excel，选择“文件”“打开”菜单项，系统弹出“打开”对话框，如图5-40所示。步骤2：在“打开”对话框中，从“文件类型”下拉列表中选择“文本文件”，在文件名中输入“客户资料.txt”，然后单击“打开”按钮。Excel将启动“文本导入向导”，打开向导的第1步对话框。步骤3：向导的第1步主要是设置文本文件中各列数据之间的间隔符，这与导入的文件相关。在本例中，我们选择“分隔符号”，并在导入起始行中输入1，即要把标题行导入到Excel工作表中，如不要标题行，可将导入起始行改为2。然后单击“下一步”按钮，进入向导的第2步对话框。步骤4：向导的第2步主要是设置用作分隔文本数据列的具体符号。本例中我们选择“Tab”键，然后单击“下一步”按钮，打开向导的第3步对话框。步骤5：向导的第3步主要是设置每列的数据类型。本例中，我们将“固定电话”、“手机”、“邮编”这3列全部设置成文本。最后单击“完成”按钮，系统就会将该文本文件导入到Excel中。,通过打开文本文件来导入,.,步骤1：单击要用来放置文本文件数据的单元格。步骤2：选择“数据”“导入外部数据”“导入数据”菜单项，系统会弹出“选取数据源”对话框。步骤3：在“选取数据源”对话框中，从“文件类型”下拉列表中选择“文本文件”，在文件名中输入“客户资料.txt”，然后单击“打开”按钮。Excel将启动“文本导入向导”“文本导入向导”的操作步骤与第一种方法基本相同，不再赘述。步骤4：完成“文本导入向导”的第3步