2003Ecel技巧之数据透视表(精华)

《2003Ecel技巧之数据透视表(精华)》由会员分享，可在线阅读，更多相关《2003Ecel技巧之数据透视表(精华)（12页珍藏版）》请在装配图网上搜索。

1、数据透视表”报表 101（精华） 打印全部隐藏适用于Microsoft Office Excel 2003Microsoft Excel 2002假设您已编辑了一个大的数据 列表（例如公司每种产品的销售额）。您现在可以从这些数据中提取一些有意义的信息并找到下列问题的答案：按区域划分，每种产品的总销售额是多少？ 过去的时间内哪些产品销量最好？业绩最好的销售人员是谁？要回答这些问题以及其他问题，可以创建PivotTable报表（自动提取、组织和汇总 数据的交互表）。然后可以使用此报表分析数据、进行比较、检测模式和关系以及分析 趋势。例如，您可以使用下图中的数据.910 iT1213A 月榆 一月

2、一月一月一月一月一月二月二月二月二月年度肆销售人员销售鎳里区域3002300230023002300230023002300230023002品品品品品 i澤縫霜i軀DodsworthDavolio Buchanan686257285 -7690862562二月 二月一205uyama 5uyama Davolio Buchanan Dodsworth Buchanan Davolio 5uyama65342669-573857635943294666214506365O -U 378988524r- r-o r- r- r- r-o r- 却制制制制BfBf制却制Bf 西北南东北西南南西南西

3、231西部_. 创建“数据透视表”报表，以显示当前每种产品在各销售区域的销售状况（如下图所 示）。ABCDEF1销售颔总计类型2医域饮料奶制品肉类农产品汇总3东部353339,44433,4062875136,758北部51,83263,41817,06226,799159,111530 tm27,88958,0253% 074155,160640j392444108,43536j466129,4037汇总157垃917861116,92813114580,432列各部分提供了概述并给出了有效使用“数据透视表”报表的顺序步骤。“数据透视表”报表使用概述通过汇总和分析数据，“数据透视表”报表可以

4、帮助您抓住关键信息。可以控制 Excel 汇 总数据的方式（例如，求总和、平均值或计数），而无需输入任何公式。“数据透视表” 报表中，可以迅速：添加或删除数据 “数据透视表”报表中的信息不太充分吗？可能您还希望报表 中包括各销售人员的销售额等数据。在“数据透视表”报表中可以轻松添加或删除数据类 别。例如，可以添加销售人员 字段，如下图所示。重新排列布局 对“数据透视表”报表的布局不满意吗？例如，您可能更愿意按 行而不是按列显示销售人员信息。利用“数据透视表”报表的交互特性，可以翻转表格： 您可以轻松地移动（或旋转）行和列以查看不同的汇总数据。例如，您可以移动“销售人员”字段，使该项向下按行显示

5、而不是跨列显示。查看数据的子集 要 筛选 数据以便您在某一时候能按需查看某部分数据 吗？要这样做，可用 页面字段 将“数据透视表”报表分成若干独立的“页面”。每个页面 都包含一个在“数据透视表”报表中显示为汇总数据的数据子集。例如，可添加一个“年”类别的页面字段。这样可以查看某一年度（例如 2003 年 或综合所有年度的销售数据。ABCDE1年度200323销售颔总计类型 -I4区域销售人员饮料奶制品肉类5东部Eiu 匚 hanan1,13216,1916Davolio8,3341147413417Dodsworth1,89805uyama7,53813562659东部总计18,9021883

6、017,89710北部Buchanan3,522156211Davolio8,725% 291如果“数据透视表”报表包含很多字段，可用页面字段来保持报表的紧凑性和易读 性。在检索某些大型外部数据库中的数据时，也可以添加并设置页面字段以限制 检索数据量。这样可以避免长时间的等待和内存问题。显示您需要的细节 是否关注“数据透视表”报表中的特定详细信息？您可以显 示或隐藏行字段或列字段中的项目，例如特定产品或销售人员。您也可以显示或隐藏行 字段或列字段中项目的详细信息。例如，在“类型”字段中，可以隐藏除饮料之外的所有产品项目。ABCD1销售颔总计类型2区域销售人员饮料汇总3东部Buchanan1,1

7、321,1324Davolio13,78113,7815Dodsworth4,0974,09765uyama162316,2237东部总计35,23335,233您可能会急于开始编制第一个数据透视表。但在开始之前，最好确保您的数据组织良好 并且已准备就绪，如下面部分所述。第1步：准备源数据 阅读此部分，了解在数据透视表中可以使用哪些数据及如何准备这些数据。准备好数据后，您可以使用“数据透视表和数据透视图”向导创建数据透视表。向导的开始部分是选择源数据。可以选择已在Excel中输入的数据或数据库中的数据 （称为外部数据源）。如果选择了外部数据源，向导将启动名为Microsoft Query的单

8、独程序，可以使用该程序连接到数据源并选择要显示的数据。单击下列任一源数据类型，了解如何使用这些数据类型：Excel列表或数据库使用创建列表命令，可以使用包含相关数据的多个连续行，也可使用指定用作数据表的多个连续行。要轻松准备 Excel 数据，应考虑执行下列任一或全部操作：确保列表组织良好 务必整洁！例如，确保列表的第一行包含列标签，因为 Excel要在报表中将此数据用作字段名。还要确保每一列都包含类似项目，例如，一列 中包含文本而另一列中包含数字值。删除任何自动的分类汇总 不要担心，数据透视表将为您计算分类汇总和总 计。命名区域 如果计划稍后添加其他数据，请确保为源数据中输入的单元格 区域指

9、定名称。这样，无论在何时向数据区域添加数据，都可以更新数据透视表，将新 数据包括在内，而不必指定新区域引用。使用“高级筛选”命令 要使用经过筛选的数据，指向数据菜单中的筛选，单击 高级筛选，然后单击复制到其他位置。这样可将筛选后的数据提取至其他工作表位置 从而可以在您的数据透视表中使用这些数据。注释不要就地筛选数据或使用自动筛选命令。此操作会隐藏列表中的一些数 据，但数据透视表仍将包括全部数据。外部数据源可以使用数据库文件、文本文件或In ternet数据源。要准备外部数据，可能需要执行下列操作：安装必要的工具和驱动程序 您可能已完全准备就绪，只需确保安装了相应的 开放式数据库连接(ODBC)

10、驱动程序或所需的数据源驱动程序。初次使用时，将自动 安装Microsoft Query包括帮助和驱动程序)。然后，当您使用数据透视表和数据透 视图向导呛建数据透视表时，将使用Microsoft Query实际地检索外部数据。如果需要，在启动向导之前检索数据 如果下表包含了您使用的数据检索方 法，您将无法在数据透视表和数据透视图向导中用Query来检索数据。相反，在启 动向导来选择包含外部数据的工作表范围之前，应按照以下说明将数据插入Excel工 作簿。 Office数据连接建议使用Office数据连接(.ode)文件为您的报表检索外部数据。您可以在仅从单个数据库表中或者从OLAP数据库中检索数

11、据 时使用这些文件，这样，在您创建报表之前，您无需合并外部数据库中多个表 的数据，或是筛选数据以选择特定记录。查询文件和报表模板 要使用查询文件(.dqy、.oqy或.rqy检索数 据，请在Excel中打开查询文件。要使用没有包括数据透视表的报表模板 (xl,t)请打开该模板。 参数查询要使用参数查询检索数据，必须首先在Microsoft Query中 创建参数查询，以便它把数据返回Excel。请注意，您不能创建参数查询来从 OLAP数据库中检索源数据。有关创建参数查询的详细信息,请参阅Microsoft Query中的帮助。 Web查询要使用Web查询来检索Internet上的数据，您必须首

12、先 创建Web查询并将数据检索到Excel中。要创建Web查询，请指向数据菜 单上的导入外部数据，然后单击新建 Web 查询。多重合并计算数据区域您可以合并与汇总几个不同 Excel 列表的数据。要准备来自多个 Excel 列表的数据，可能需要执行下列操作：确保列表组织良好务必整洁！例如，确保列表有与您要汇总的项目相匹配的 行和列名称。删除任何自动的分类汇总 不要担心，数据透视表将为您计算分类汇总和总计。命名区域 如果计划稍后添加其他数据，请确保为 源数据 中输入的单元格 区域指定名称。这样，无论何时向数据区域添加其他数据，都可以更新数据透视表，将 新数据包括在内，而不必指定新区域引用。另一个

13、数据透视表如果计划使用同样的数据创建多个数据透视表，可以重新使用现有的数据透视表，来创 建新的数据透视表，从而节省内存和磁盘空间。此操作将把原始的和新的数据透视表链 接起来。（如果您刷新了原数据透视表的数据，新报表的数据也会刷新，反之亦然。）要准备来自其他数据透视表的数据，可能需要执行下列操作：确保两个数据透视表在同一工作簿中 如果原数据透视表位于不同的工作簿，请将原报表复制到您要显示新报表的工作簿中。检查页面字段设置 您可能已在原数据透视表中更改了页面字段设置，以便分别检索每个页面的外部数据。在这种情况下，您需要重新设置页面字段，以便一次为全 部项目检索外部数据。要检查设置，双击每一个页面字

14、段，然后单击高级。注释要了解有关页面字段的其他信息，参见本文稍后部分的数据透视表解析”。第2步：创建数据透视表要创建数据透视表，请首先使用“数据透视表和数据透视图向导”。该向导可帮助您指定 要使用的数据及创建报表框架。然后您可使用数据透视表工具栏在该框架内排列数据。例如，您可以选择要包括的数据类别（衬以蓝色底纹）和要汇总的相关数据值（衬以黄 色底纹）。ABCDEFG1月份年度类型销售人员销售靈2686单位2一月2003期制品Dodsworth5611西部3一月2003饮料Davolio87256890北部4一月2003农产品Buchanan67152652南部5一月2003奶制品5uyama4

15、3565636乐部6一月2003肉类5uyama96623400北部7一月2003农产品Davolio13282861西部0二月2003饮料Buchanan35715178南部9二月2003奶制品Dodsworth67587817南部10二月2003饮料Buchanan49535889西部11二月2003肉类Davolio斗3124南部12二月2003肉类5uyama2666744西部13二月2003农产品Buchanan1450234西部然后，告诉Excel如何在数据透视表的行和列中排列数据类别。Excel创建完报表后, 它将自动汇总和总计数据值。ABCDEF1销售霰总计类型2区域饮料奶制品

16、肉类农产品汇总3东部35333%44433,4062875136,7584北部513263,41817,0622 799159,1115南部30,17227,88958,0253% 074155,1606西部4039244,1108,43536j466129,4037汇总15729174,861116,92813014580,432要创建数据透视表，执行下列步骤：启动数据透视表和数据透视图向导1. 打开您要创建数据透视表的工作簿。如果您的报表要基于Excel列表或数据库， 请单击列表或数据库中的一个单元格。2. 在数据菜单上，单击数据透视表和数据透视图。3. 在“数据透视表和数据透视图向导”的

17、第 1 步中，请选择将要使用的数据类型， 然后单击所需创建的报表类型下的数据透视表。4. 在向导的第 2 步中，按照定位数据的说明进行操作。如果要为数据透视表检索 外部数据，可以从Excel帮助或Query帮助中获得有关使用Microsoft Query或 Query 向导的详细信息。5. 在向导的第 3 步中单击完成。定义数据透视表的布局1. 将字段按钮从数据透视表字段列表窗口拖到数据透视表图表的标签区域。2. 要重新排列各字段，请在区域间拖动这些字段。要删除字段，请将其拖至数据 透视表之外。注释有些情况下，您可能不希望通过在工作表上直接拖动字段来手动定义数据透视 表布局。例如，如果源数据来

18、自大型外部数据库，手动定义布局则可能过于费时。在这 种情况下，可以使用向导定义布局。在“数据透视表和数据透视图向导”的第 3 步中单击 布局来设置数据透视表布局。数据透视表解析 您可能已经注意到，Microsoft ExceI使用了特定的术语来标识数据透视表的各部分。 如果您尚不熟悉这些术语，请阅读此部分，迅速获得初步知识。ABcDE1年度200323销售颔总计类型-14销售人员饮料奶制品肉类5东部、Buchanan1,13216,1916Davolio8,33414,47413417Dodsworth1,89805uyama7,53813562659东部总计18,9021830179710北

19、部Buchanan3,522156211Davolio8,725% 291:页面字段 此字段来自源数据，用于数据透视表中的页面（或筛选）。例如，“年” 是一个页面字段，可以使用“年”字段仅显示 2003 年、2004 年等的汇总数据。:数据字段 此字段来自源数据，其中包含要汇总的值。例如，“销售总额”是一个数据 字段。对于多数源数据类型，可以选择汇总数据的方式（例如，求总和、平均值或计数）。数 据字段一般用于汇总数字，但也可汇总用于文本。例如，可以对某字段中特定文本项（例 如“是”或“否”）出现的次数进行计数。s列字段 此字段来自源数据，用于数据透视表中的列。例如，“类型”是一个列字段。项目

20、行、列或页面字段的子类别。例如，“类型”字段包含了下列项目：饮料、奶制 品和肉制品。销售人员字段包含了这些项目：Buchanan、Davolio、Dodsworth和 Suyama。行字段 此字段来自源数据，用于数据透视表中的行。例如“地区”和“销售人员”都是 行字段。数据区域 数据透视表中包含汇总数据的单元格。例如单元格C5中的值汇总了 2003 年 Buchanan 在东部地区的饮料销售额。也可以说，该单元格是源数据中包含 Buchanan、饮料、东部和2003等项目的各行的销售额汇总。第3步：自定义数据透视表 您现在已经创建了数据透视表，但这还只是开始。是否还记得有关数据的那些问题？数

21、据透视表不仅是静态的汇总，还可以使用该表以交互的方式探索数据的含义。可以查看 详细信息，也可进行大局观察。可以按照地区或时间段查看销售额。自定义类型由您自 己确定。要自定义数据透视表，请执行下列一项或多项操作：添加或删除数据是否希望在数据透视表中查看多一些或少一些的数据类别？可以方便地添加或删除字 段（包括行、列或页面字段）要添加字段，请从数据透视表字段列表窗口中将字段拖至数据透视表报表中的相应区 域。要删除字段，请将字段拖至数据透视表之外。注释不得在数据透视表中直接编辑数据。而是要编辑源数据，然后刷新数据透视表。快速重新排列布局是否需要重新组织数据透视表以获得不同的数据汇总结果？通过移动字段

22、或重新安排 字段内各项的顺序，可以快速“旋转”数据透视表。要移动字段，请将字段拖至数据透视表中相应区域。要重新安排字段内各项的顺序，将 项目拖至字段内的新位置。使用页面字段查看数据子集数据透视表中是否包括了页面字段？如果是，该数据透视表即是由多个“页面”组成的三 维报表。其中每页包含了在报表中已经汇总的数据子集。要查看其他页面，请单击页面字段项旁边的箭头，然后单击列表中的项目。显示所需的详细信息在数据透视表中，可以查看详细信息，也可隐藏信息以获得全局认识。可以快速地：显示或隐藏行或列中的项目 单击字段中的箭头，然后根据自己的需要选择或 清除复选框。例如，在“类型”字段中，可以隐藏除“饮料”外的

23、全部产品项目：隹部显示）渝制品肉类农产品确定取消1,13213814,097东部总计汇总1,13213,7814,0971翻23显示或隐藏项目的特定详细信息 单击字段中的项目，然后单击数据透视表工 具栏上的显示详细信息或隐藏详细信息按钮。如果出现提示，单击希望显示其详细信息 的字段。例如，对于“类型”字段中的“饮料”项，通过显示每年销售额可以增加其详细信息的 内容。显示特定数据单元格的基础源数据 只需双击单元格即可查看其详细信息。例如，双击东部地区的饮料分类汇总，可以查看用于计算该单元格的源数据记录。年度23456709销售额总计医域东部销售人员 Buchanan Davolio Dodswo

24、rth Buyam 日类型 Ed 饮料 奶制品1,132% 13613,781 22,9534,097东部总计10北部123456789A月份七月 三月 七月 六月 四月 十月 十月 一月B | C年度类型D销售人员F单位G区域2003饮料2003饮料2004饮料2004饮料2003饮料2004饮料2003饮料2004饮料Bu匚hananDavolioDavolioDodsworth Dodsworth 5uyama 5uyama 5uyama113283345447219918985122753835633326293390698496817329928984183东部 东部 东部 东部 东

25、部 东部 东部 东部第4步：打印数据透视表是否要打印数据透视表？如果该报表是一个多页报表，可以设置打印选项以控制报表的 打印外观，如下所示：为方便浏览多页的数据透视表，可以在各页顶端自动重新打印标签。例如，在打印页面上，可以重复行 1 和行 2 中的标签，也可重复外行字段项目 “四月”。销售额汇总-第2页、销售额总计类型月份区域销售人员饮料奶制品肉类农产品汇总四月北部Davolio5uyama2J689,2911,50010,7912,768北部总计27689j291IjSOO1359南部Davolio Dodsworth278450450278L訓丿南部总计二一2,4784501,8612,

26、9281,861为确保在每页中能打印数据透视表的逻辑区块，可以指定Excel插入分页符的 位置。可以选择在特定位置插入分页符，例如，在“月”字段中每个月开始之前插入分页 符，以避免将一个月的项目分隔开来。例如，在下图中，自动分页符插入到了行 41 和行 42 之间，将四月的项目分隔开来。要使四月的项目打印在一起，可以 指定将分页符放置在行 38 “三月总计”的正下方。ABCDE1销售颔类型2月份区域销售人员饮料奶制品3一月东部5uyama3,5634,3564东部总计3,5634,3565北部Buchanan5,5946Davolio8,7257Dodsworth4,23138三月总计25,4

27、752,24239四月东部Buchanan9,13640Dodsworth1,898419,13642北部Davolio9,291435uyama2,76844北部总计2,7689,29145南部Davolio46Dodsworth2,47847南部总计2,478打印之前，执行下列步骤以指定报表的打印区域：1. 在数据透视表内单击。2.在数据透视表工具栏中，单击数据透视表，指向选择，然后单击整个表格。3.在文件菜单中，指向打印区域，然后单击设置打印区域。要设置数据透视表的打印选项，请执行下列任一或所有步骤：-在每个打印页面上重复行标签1. 在文件菜单中，单击页面设置，然后单击工作表选项卡。确保

28、顶端标题行框和 左端标题列框是空白框，然后单击确定。2. 在数据透视表内单击。3. 在数据透视表工具栏中单击数据透视表，然后单击表格选项。4. 在格式选项下选择设置打印标题复选框。在每个打印页面上重复外行字段项目1. 在数据透视表内单击。2. 在数据透视表工具栏中单击数据透视表，然后单击表格选项。3. 在格式选项下选择在每一打印页上重复项目复选框。在单页上打印数据透视表的各部分1. 双击要在单页上打印其项目的外行字段的标题。例如，要在每类产品之间插入 自动分页符，双击“类型”行的标题。2. 在数据透视表字段对话框中单击布局。注释如果外行字段中仅有一项，布局将不可用。3.在打印选项下选择每项后面插入分页符复选框，然后单击两次确定。4.在视图菜单中，单击分页预览，然后根据需要调整自动分页。可以通过拖动来移动分页符，或按 DELETE 键来删除分页符。提示要使所打印的数据透视表易于浏览，在数据透视表工具栏中单击设置报 表格式按钮，从而快速切换为缩进格式。使用这种格式，各行字段的数据都会缩 进（类似于文字大纲），所有数据字段的汇总数据显示在一列中。打印选项设置完成之后，可以单击文件菜单上的打印预览，以预览打印的报表。准备好 打印报表之后，单击打印。