数据管理与分析ppt课件

1、Overview概述数据列表 数据排序数据挑选分类汇总运用数据透视表数据导入与导出Excel具有强大的数据管理与分析才干，可以对任务表中的数据进展排序、挑选、分类汇总等，还可以运用数据透视表对任务表的数据进展重组，对特定的数据行或数据列进展各种概要分析，并且可以生成数据透视图，直观的表示分析结果。由于排序与挑选数据记录的操作需求经过“数据列表来进展，因此在操作前应先创建好“数据列表。“数据列表是任务表中包含相关数据的一系列数据行，如前面所建立的销售清单和销售统计表，就包含有这样的数据行，它可以像数据库一样接受阅读与编辑等操作。数据列表的创建方法如下：选定要创建列表的数据区域，然后选择Excel

2、的“数据“列表“创建列表菜单项。例如，要建立如图5-1所示的销售记录数据列表，应选中A1:O36区域，然后选择“数据“列表“创建列表菜单项建立数据列表。实践上，假设一个任务表只需一个延续数据区域，并且这个数据区域的每个列都有列标题，那么系统会自动将这个延续数据区域辨以为数据列表。如图5-2。图图5-1 建立数据列表建立数据列表图图5-2 自动辨以为数据列表自动辨以为数据列表 将类型一样的数据项置于同一列中 使数据列表独立于其它数据 将关键数据置于列表的顶部或底部 留意显示行和列 留意数据列表格式 运用单元格边框突出显示数据列表 防止空行和空列 当数据表或列表中的数据记录太多时，要查看、修正或编

3、辑其中的某条记录很困难，为理处置这个问题，Excel提供了记录单功能。 只需每列数据都有标题的任务表才可以运用记录单功能。单击销售记录数据列表中的任一单元格，从“数据下拉菜单中选择“记录单命令，进入图5-3所示的数据记录单对话框就能完成这些操作。记录单具有条件查询功能，记录单具有条件查询功能，允许运用通配符查找，即允许运用通配符查找，即用用“*替代不可知的恣意替代不可知的恣意长度的任何符号，长度的任何符号， 图图5-3 记录单记录单 数据排序的功能是按一定的规那么对数据进展整理和陈列，为进一步处置数据做好预备。Excel 提供了多种对数据列表进展排序的方法，既可以按升序或降序进展排序，也可以按

4、用户自定义的方式进展排序。 例如，每月商品的销量排名情况就需求对商品销售数量进展排序，从中可以得出卖得最好的商品或卖得最差的商品。运用“数据下拉菜单的“排序命令，翻开“排序对话框。主要关键字主要关键字选择排序字段，按升序或降序排序。选择排序字段，按升序或降序排序。次要关键字次要关键字“主要关键字列中呈现了反复项，就主要关键字列中呈现了反复项，就按次要关键字来排序反复的部分。按次要关键字来排序反复的部分。第三关键字第三关键字“主要关键字与主要关键字与“次要关键字列中次要关键字列中都呈现了反复项，就按第三关键字都呈现了反复项，就按第三关键字来排序反复的部分。来排序反复的部分。有标题行：排序时包含列

5、表第一行。有标题行：排序时包含列表第一行。无标题行：排序时不包含列表第一行。无标题行：排序时不包含列表第一行。图图5-4 排序对话框排序对话框 如今我们运用普通排序来完成义务1，对图5-2中的销售数量进展降序排序，得到商品销量排名，排在第一的即是月度销售冠军；对利润金额进展降序排序，那么得到商品利润排名，排在第一的即是利润最高的商品。 步骤1：单击源任务表中的任一单元格，或选中要排序的整个单元格区域。本例中，可单击A3:G30中的任一单元格，也可以选择整个A3:G30区域。步骤2：选择“数据“排序菜单项，系统会显示图5-4所示的对话框。步骤3：从对话框中“主要关键字下拉列表中选择排序关键字，选

6、择“销售数量。步骤4：指定排序方式，由于需求找出月度销售冠军，因此选择“降序作为排序的方式。步骤5：将“有标题行单项选择钮选中，然后单击“确定按钮，Excel就会对源任务表中的数据按销售数量从高到低进展重新陈列，其结果如图5-5所示。 如何找出利润最高的商品？如何找出利润最高的商品？图图5-5 对销售数量进展降序排序后的任务表对销售数量进展降序排序后的任务表 有时，我们需求按照一种指定的次序进展排序，而不是按照数值或者文本的顺序排序。例如在义务1中，店主需求知道本月各个类别的商品销售排名情况，商品类别要按照“手机、相机、MP4、MP3、储存卡这个自定义顺序陈列，而不是字母或笔画顺序。 要完成这

7、样的排序，需求先建立一个自定义序列。 步骤1：选择“工具“选项菜单项，系统弹出如图5-6所示的对话框。 步骤2：选择“选项对话框中的“自定义序列标签，然后在该对话框中的“输入序列编辑框中输入自定义序列，每输入一个类别后按一下回车键。 步骤3：输入完成后，单击“确定按钮，就将这个用户自定义序列添加到了系统中。图图5-6 自定义序列自定义序列步骤1：单击图5-2中A3:G30中的任一单元格，也可以选择整个A3:G30区域。然后选择“数据“排序菜单项。步骤2：在弹出的“排序对话框中，单击“选项按钮，系统将弹出“排序选项对话框，如图5-7所示。步骤3：在 “自定义排序次序下拉列表中选择前面建立的自定义

8、序列，按“确定按钮回到“排序对话框。步骤4：在“排序对话框中选择“类别作为主关键字，排序方式为“升序，选择“销售数量作为次要关键字，排序方式为“降序，按“确定按钮皆可得到如图5-8所示的结果。图图5-8 自定义排序后的结果自定义排序后的结果图图5-7 排序选项排序选项 数字：根据其值的大小从小到大排序。 文本和包含数字的文本：按字母顺序对文本项进展排序。 逻辑值：False排在True之前。 错误值：一切的错误值都是相等的。 空白不是空格：空白单元格总是排在最后。 汉字：汉字有两种排序方式：汉语拼音和笔画数据挑选是一种用于查找数据的快速方法，挑选将数据列表中一切不满足条件的记录暂时隐藏起来，只

9、显示满足条件的数据行，以供用户阅读和分析。Excel提供了自动和高级两种挑选数据的方式。 自动挑选为用户提供了在具有大量记录的数据列表中快速查找符合某些条件的记录的功能。挑选后只显示出包含符合条件的数据行，而隐藏其它行。 在义务2中，为了及时跟踪各个类别与各个品牌的商品销售情况，需求从图5-1中的销售清单中查询相关信息，可以经过自动挑选获取上述信息。以“类别字段的挑选作为例子，详细操作步骤如下。步骤1：单击图5-1中销售清单中的任一单元格。步骤2：选择“数据“挑选“自动挑选菜单项。数据列表中第一行的各列中将分别显示出一个下拉按钮，如图5-1所示，自动挑选就将经过它们进展。步骤3：单击需求进展挑

10、选的列标的下拉列表，Excel会显示出该列中一切不同的数据值，这些值可用于挑选条件，如单击“类别旁边的下拉列表，会显示出“类别列中一切的值，如图5-9所示。 步骤4：如要查看“手机的销售情况，只需在下拉列表中选择“手机，系统就会显示如图5-10所示的结果。 图图5-9自动挑选自动挑选图图5-10自动挑选后的结果自动挑选后的结果在义务2中，我们还需对库存清单进展挑选，找出库存最大的前5种商品，给店主张某提供进货的参考，库存清单如图5-11所示。图图5-11 库存清单库存清单点击“自动挑选命令后，系统添加下拉列表标志，我们要挑选出5种库存最大的商品，应单击“期末库存列标的下拉列表，然后选择列表中的

11、“前10个，Excel会弹出显示个数设置的对话框，如图5-12所示。在 “显示的下拉列表中选择“最大，然后在编辑框中输入5。挑选结果如图5-13所示。图图5-12 自动挑自动挑选前选前10个个图图5-13 自动挑选前自动挑选前10个后的结果个后的结果假设要找出库存大于0并且小于等于3的手机的库存情况，需求分别对“期末库存和“类别进展两步挑选。第一步自定义自动挑选方式如图5-14所示； 第二步类别挑选同前面例子。图图5-14 自定义自动挑选自定义自动挑选自定义挑选只能完成条件简单的数据挑选，假设挑选的条件比较复杂，自定义挑选就会显得比较费事。对于挑选条件较多的情况，可以运用高级挑选功能来处置。运

12、用高级挑选功能，必需先建立一个条件区域，用来指定挑选条件。条件区域的第一行是一切作为挑选条件的字段名，这些字段名与数据列表中的字段名必需一致，条件区域的其它行那么输入挑选条件。需求留意的是，条件区域和数据列表不能衔接，必需用以空行或空列将其隔开。条件区域的构造规那么是：同一列中的条件是“或，同一行中的条件是“与。步骤1：在库存清单中创建一个条件区域，输入挑选条件，这里在I1、J1、K1单元格中分别输入“类别、“期末库存、“期末库存，在I2、J2、K2中分别输入“手机、“0、“$I$9，输入该公式须满足上述的第2、3条规那么，E4是数据列表中的单元格，因此只能运用相对援用的方式。I9包含平均值公

13、式，是数据列表之外的单元格，只能采用绝对援用的方式。计算条件建立好之后，如图5-16所示，按照前面引见的步骤进展高级挑选，挑选的结果如图5-17所示。图图5-16 建立计算条件建立计算条件图图5-17 运用计算条件挑选后的结果运用计算条件挑选后的结果分类汇总是对数据列表指定的行或列中的数据进展汇总统计，统计的内容可以由用户指定，经过折叠或展开行、列数据和汇总结果，从汇总和明细两种角度显示数据，可以快捷的创建各种汇总报告。在这一节中，我们将运用分类汇总来完成义务3。Excel可自动计算数据列表中的分类汇总和总计值。当插入自动分类汇总时，Excel将分级显示数据列表，以便为每个分类汇总显示或隐藏明

14、细数据行。Excel分类汇总的数据折叠层次最多可达8层。分类汇总可以完成以下任务。 显示一组数据的分类汇总及总和。显示多组数据的分类汇总及总和。在分组数据上完成不同的计算，如求和、统计个数、求平均值或最大值、最小值、求总体方差等。在创建分类汇总之前，首先要保证要进展分类汇总的数据区域必需是一个延续的数据区域，而且每个数据列都有列标题；然后必需对要进展分类汇总的列进展排序。这个排序的列标题称为分类汇总关键字，分类汇总时只能指定排序后的列标题为汇总关键字。例如，对于图5-1所示的销售清单，假设要统计各个类别的商品销售数量，应该先以“类别字段为主要关键字进展自定义排序，并以“品牌字段为次要关键字按升

15、序排序，排序后的结果如图5-18所示。图图5-18 按类别自定义排序后的销售清单按类别自定义排序后的销售清单步骤1：单击数据区域中的任一单元格，然后选择“数据“分类汇总菜单项，翻开如图5-19所示的“分类汇总对话框。步骤2：从“分类字段下拉列表中选择要进展分类的字段，分类字段必需是曾经排好序，在本例中，我们选择“类别作为分类字段。步骤3：“汇总方式下拉列表中列出了一切汇总方式统计个数、计算平均值、求最大值或最小值及计算总和等。在本例中，我们选择“求和作为汇总方式。步骤4：“选定汇总项的列表中列出了一切列标题，从中选择需求汇总的列，列的数据类型必需和汇总方式相符合。在本例中我们选择“数量作为汇总

16、项。步骤5：选择汇总数据的保管方式，有3种方式可以选择，可同时选中，默许选择是第1和第3项。点击“确定按钮，结果如图5-20所示。图图5-19 分类汇分类汇总对话框总对话框图图5-20分类汇总分类汇总后的结果后的结果在Excel中我们也可以对多项目的进展汇总，并且可以进展嵌套分类汇总 。如今让我们来完成义务3，我们需求对销售统计表中的销售金额和利润金额两项目的进展汇总，并且需求对各个类别与各个品牌的商品进展分类汇总，由于每个类别都有多个品牌，因此我们可以先对类别进展分类汇总，然后在此根底上再对品牌进展分类汇总。 步骤1：单击销售统计表中的任一单元格，然后选择“数据“分类汇总菜单项，翻开 “分类

17、汇总对话框。步骤2：分类字段选择“类别，汇总方式选择“求和，在“选定汇总项下拉列表框中选择“销售金额和“利润金额两个字段，按“确定按钮即可得到如图5-21所示的结果。步骤3：再次选择“数据“分类汇总菜单项。分类字段选择“品牌，汇总方式和汇总项与第2步一样，去除“交换当前分类汇总复选框。按下“确定按钮，我们就可以得到如图5-22所示的结果。分类汇总前先将销售统计表按类别和品牌关键字排序。图图5-21 一级分类汇总结果一级分类汇总结果图图5-22 两级分类汇总结果两级分类汇总结果以以下图中以以下图中“金士顿金士顿 汇总错误的显汇总错误的显示在示在“储存卡储存卡 汇总的下面，这是汇总的下面，这是Ex

18、cel 2003的一个的一个Bug，呈现以下情况，呈现以下情况时，能够会导致结果的显示顺序不正时，能够会导致结果的显示顺序不正确。确。 汇总显示在明细数据的下方。汇总显示在明细数据的下方。 一个或多个分类汇总片段仅包含一一个或多个分类汇总片段仅包含一个工程。个工程。由于销售统计表中储存卡仅包由于销售统计表中储存卡仅包含金士顿一个品牌，而汇总显含金士顿一个品牌，而汇总显示在明细数据的下方，因此导示在明细数据的下方，因此导致了图致了图5-22中的错误显示。中的错误显示。假设由于某种缘由，需求取消分类汇总的显示结果，恢复到数据列表的初始形状。其操作步骤如下。步骤1：单击分类汇总数据列表中任一单元格。

19、步骤2：选择“数据“分类汇总命令，翻开“分类汇总对话框。步骤3：单击对话框中的“全部删除按钮即可。经过以上步骤之后，数据列表中的分类汇总就被删除了，恢复成汇总前的数据。第3步中的“全部删除只会删除分类汇总，不会删除原始数据。数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格，不只可以改动行和列以查看源数据的不同汇总结果，也可以显示不同页面以挑选数据，还可以根据需求显示区域中的明细数据。数据透视图那么是一个动态的图表，它可以将创建的数据透视表以图表的方式显示出来。在这一节中我们将运用数据透视表和数据透视图来完成义务4。 坐标轴坐标轴数据透视表中的一维，例如行、列或页数据透视表中的一维，

20、例如行、列或页数据源数据源为数据透视表提供数据的数据列表或数为数据透视表提供数据的数据列表或数据库据库字段字段数据列表中的列标题数据列表中的列标题项项组成字段的成员，即某列中单元格中的组成字段的成员，即某列中单元格中的内容内容概要函数概要函数用来计算表格中数据的值的函数。默认用来计算表格中数据的值的函数。默认的概要函数是用于数字值的的概要函数是用于数字值的SUMSUM函数、函数、用于统计文本个数的用于统计文本个数的COUNTCOUNT函数函数透视透视通过重新确定一个或多个字段的位置来通过重新确定一个或多个字段的位置来重新布置数据透视表重新布置数据透视表图图5-23 数据透视表例如数据透视表例如

21、数据透视表的创建可以经过“数据透视表和数据透视图导游进展。 利用导游创建数据透视表需求3个步骤。第1步，选择所创建的数据透视表的数据源类型；第2步，选择数据源的区域；第3步，设计将要生成的数据透视表的版式和选项。下面让我们来完成义务4，从销售清单中创建数据透视表，统计各个类别与各个品牌的销售数量。 图图5-24 导游步骤导游步骤1图图5-25 导游步骤导游步骤2图图5-26 导游步骤导游步骤3图图5-27 导游步骤导游步骤4图图5-28 生成的数据透视表生成的数据透视表创建好数据透视表之后，根据需求有能够要对它的规划、数据项、数据汇总方式与显示方式、格式等进展修正。修正数据透视表的规划 修正数

22、据透视表的数据项 隐藏或显示行、列中的数据项调整数据项显示的位置修正数据透视表的数据汇总方式和显示方式 修正数据透视表的格式 图图5-29 修正数据透视表的规划修正数据透视表的规划 隐藏或显示行、列中的数据项 例如在图5-28中所示的数据透视表中，不想显示第5行，即储存卡的销售数量，可以单击 “类别字段旁边的下拉列表条，系统会显示如图5-30所示的对话框，将对话框中“储存卡前面的复选标志去除，然后单击“确定按钮，数据透视表中就没有“储存卡这一行的数据了。 调整数据项显示的位置 拖动数据项的称号到适宜的位置释放即可。图图5-30 行字段列表行字段列表在默许情况下，数据透视表采用SUM函数对其中的

23、数值项进展汇总，用COUNT函数对文本类型字段项进展计数。但有时SUM和COUNT函数并不能满足透视需求，如平均值、百分比、最大值之类的计算。实践上，Excel提供了很多汇总方式，在数据透视表中可以运用这些函数。操作方法是：双击数据透视表中的数据字段，系统会弹出“数据透视表字段对话框，如图5-31所示，在对话框中从“汇总方式中选择需求的函数。默许情况下，数据透视表中的数据是以数值方式显示，不过我们可以根据需求将它修正为其他方式的数据显示方式，如显示为小数、百分数或其他需求的方式。例如把数据显示方式设为“占总和的百分比。图图5-31 数据透视表字段数据透视表字段数据透视表的格式也可以修正，可以象

24、格式化任务表一样，将数据透视表的格式设置成各种需求的款式。用户可以运用“自动套用格式命令将Excel内置的数据透视表格式运用于选中的数据透视表。自动套用数据透视表格式时，首先选定数据透视表，然后单击“数据透视表工具栏中的“设置报告格式按钮，翻开如图5-32所示的“自动套用格式对话框。在对话框中选择要运用的格式即可。图图5-32 自动套用格式自动套用格式数据透视图表是利用数据透视的结果制造的图表，数据透视图总是与数据透视表相关联的。假设更改了数据透视表中某个字段的位置，那么透视图中与之相对应的字段位置也会改动。数据透视表中的行字段对应于数据透视图中的分类字段，而列字段那么对应于数据透视图中的系列

25、字段。数据透视表中的页字段和数据字段分别对应于数据透视图中的页字段和数据字段。 在“数据透视表和数据透视图导游的第1步中将所需创建的报表类型选为“数据透视图及数据透视表，这样就会同时创建数据透视表和数据透视图。 假设曾经单独创建了数据透视表，那么只需单击常用工具栏中的“图表导游或“数据透视表工具栏中的“图表导游按钮，系统会自动插入一个新的数据透视图。 图图5-33 与图与图28 中数据透视表对应的数据透视图中数据透视表对应的数据透视图上一节中我们运用上一节中我们运用“自动套用格式去修正数据透视表的格式，自动套用格式去修正数据透视表的格式，而数据透视图那么可以修正它的图表类型和图表选项。单击而数

26、据透视图那么可以修正它的图表类型和图表选项。单击“图图表菜单的表菜单的“图表类型图表类型 或或“图表选项菜单项即可翻开相应对图表选项菜单项即可翻开相应对话框话框图图5-34 修正图表类型修正图表类型图图5-35 修正图表选项修正图表选项在进展数据管理与分析的过程中，有时需求将数据导入到Excel任务表中，有时那么要将Excel任务表导出为其他类型的数据。本节引见Excel常用的导入导出功能。在进展数据管理与分析的过程中，有时需求将网页中的数据导入到Excel任务表中。例如店主张某能够需求获取最新的手机报价，然后据此调整店铺的手机卖价。Excel提供了导入Web页数据的功能。 步骤1：首先要连上

27、Internet网络，保证可以访问Internet上的Web网站。 步骤2：选择“数据“导入外部数据“新建Web查询菜单项，翻开“新建Web查询对话框，如图5-36所示。 步骤3：在图5-36中“地址文本框中输入要导入数据的Web页的网络地址，然后单击“转到按钮，系统会显示完好的Web页。本例中显示的是太平洋电脑网的手机频道。 步骤4：单击Web页数据区域中要导入数据前的按钮 ，它会变成 。 步骤5：单击“导入按钮，系统会弹出设置数据放置位置的对话框，如图5-37所示。 步骤6：设置了数据的放置位置后，单击“确定按钮，系统就会将选定的数据导入到任务表中，结果如图5-38所示。图图5-36 新建

28、新建web查询查询图图5-38 导入数据后的导入数据后的任务表任务表图图5-37导入数据对话框导入数据对话框与Excel交换数据的文本文件通常是带分隔符的文本文件 .txt，普通用制表符分隔文本的每个字段，也可以运用逗号分隔文本的每个字段。导入文本文件有两种方法，可以经过翻开文本文件的方式来导入，也可以经过导入外部数据的方式来导入。我们以导入一个存放有客户资料的文本文件为例，如图5-39所示，引见导入文本文件的操作过程。 Excel任务表中的数据可以直接保管为文本文件。方法是：运用“文件“另存为菜单项将 Excel 任务表转换为文本文件。图图5-39 客户客户资料资料.txt图图5-40 翻开

29、文件翻开文件对话框对话框步骤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 将启动“文本导入导游“文本导入导游的操作步骤与第一种方法