Excel技巧之数据透视表

1、r-8旦10辽2月月月月二二二03030303030303030303Suyama Suyama Davdio Buchanan Dodsworth Buchanan Davoiio6 6 O O3 3 O O6 6 4 45 5 3 31 86 78 8 1 12 51424241L1LB-B- 7J-7J- siBsiB制部制 东北西南南西南西13&75S159?1U155,160129,403汇总适用于Microsoft Office Excel 2003Microsoft Excel 2002假设您已编辑了一个大的数据列表（例如公司每种产品的销售额）。您现在可以从这些数据中提取一些有意

2、义的信息并找到下列问题的答案：按区域划分，每种产品的总销售额是多少？过去的时间内哪些产品销量最好？业绩最好的销售人员是谁？要回答这些问题以及其他问题，可以创建PivotTable? 报表（自动提取、组织和汇总数据的交互表）。然后可以使用此报表分析数据、进行比较、检测模式和关系以及分析趋势。例如，您可以使用下图中的数据二月0匹一农芒品Euchanari 1450 _ 234创建 数据透视表”报表，以显示当前每种产品在各销售区域的销售状况（如下图所示）销售额总计类型区域 -饮料 奶制品 肉类 农产品 汇总35,2333気留斗 33306287551,332 63,41617?05226?79930

3、,172 27,30558,025307440.392 44.1100,43536,4&6157,629 174,361 11628 13L014580,432F列各部分提供了概述并给出了有效使用数据透视表”报表的顺序步骤数据透视表”报表使用概述通过汇总和分析数据，数据透视表”报表可以帮助您抓住关键信息。可以控制Excel汇总数据的方式（例如，求总和、平均值或计数），而无需输入任何公式。数据透视表”报表中，可以迅速：数据透视表报表101 打印-全部隐臧份月月月 月二一月1010 2 21919 5 56 6 8 8 6 65 5 6 6 2 2销售人员Dodsworth Davolio Buc

4、hananSuyama6 6 281833628183365 5 6 6 2 2 7 7 55-2655-2636357996363579964913644249136442品品 类产料制料类类添加或删除数据数据透视表”报表中的信息不太充分吗？可能您还希望报表中包括各销售人员的销售额等数据。在 数据透视表”报表中可以轻松添加或删除数据类别。例如，可以添加销售人员 字段，如下图所示。B B111销售额总计 类型销售人贝II饮料1BuchananDavolioDodsworthSuyama ilf13213.7G1% 09716,223；北部9）垢14.1S66,48122,049；百南部3,57

5、16,71113,6506?040i7 |西部12,06616,9777,7823567|汇总250551,65532,210亿刃聖重新排列布局 对数据透视表”报表的布局不满意吗？例如，您可能更愿意按行而不是按列显示销售人 员信息。利用 数据透视表”报表的交互特性，可以翻转表格：您可以轻松地移动（或旋转）行和列以查看不同的 汇总数据。例如，您可以移动 销售人员”字段，使该项向下按行显示而不是跨列显示th1Q1U1亡1 1销售额总计类型1II区域销售人员P饮科朗制品肉类农产品Buchanan1,132136 1&J91II q4 4Dvolio13,78122.953 1*4+1忆 59915

6、5Dodsworth8319,996 16 6Suyama16,2237.355 7,65603017 7东部总计35,2333豹 4 3330620,6751查看数据的子集要筛选数据以便您在某一时候能按需查看某部分数据吗？要这样做，可用页面字段将数据透视表”报表分成若干独立的 页面”每个页面都包含一个在 数据透视表”报表中显示为汇总数据的数据子例如，可添加一个 年”类别的页面字段。这样可以查看某一年度（例如 2003年）或综合所有年度的销售 数据。F：cD DE E年度2Q03|111J梢售锁总计类型z11区域梢售人员饮料妬制品肉奏可东部Buchananlf13216jl916Davolii

7、：B.33414,47 斗13411fDodsiworth1,8981 q3Suyama7,5384,3562fi5i9东部总计18,90218,330“旳7；103EBuchanan3.523.522 215621562_111Davoio_I6725一骐里一1_ 如果数据透视表”报表包含很多字段，可用页面字段来保持报表的紧凑性和易读性。在检索某些大型外部 数据库中的数据时，也可以添加并设置页面字段以限制检索数据量。这样可以避免长时间的等待和内存问 题。显示您需要的细节 是否关注数据透视表”报表中的特定详细信息？您可以显示或隐藏行字段或列字段 中的项目，例如特定产品或销售人员。您也可以显示或

8、隐藏行字段或列字段中项目的详细信息。例如，在 类型”字段中，可以隐藏除饮料之外的所有产品项目AC1梢售歆总计类型2處感销售人员饮料汇总o东部Buchanan1?132Ijl32Davalio13,73113,7315Dodsworth4,0974,0978Suyama16品f东部总计矩23日35,233您可能会急于开始编制第一个数据透视表。但在开始之前，最好确保您的数据组织良好并且已准备就绪，如下面 部分所述。第1步：准备源数据阅读此部分，了解在数据透视表中可以使用哪些数据及如何准备这些数据。准备好数据后，您可以使用 数据透视表和数据透视图？”向导创建数据透视表。向导的开始部分是选择源数据。可

9、以选择已在Excel中输入的数据或数据库中的数据（称为外部数据源）。如果选择了外部数据源，向导将启动名为Microsoft Query 的单独程序，可以使用该程序连接到数据源并选择要显示的数据。单击下列任一源数据类型，了解如何使用这些数据类型：-Excel列表或数据库使用创建列表命令，可以使用包含相关数据的多个连续行，也可使用指定用作数据表的多个连续行。要轻松准备 Excel数据，应考虑执行下列任一或全部操作：确保列表组织良好务必整洁！例如，确保列表的第一行包含列标签，因为Excel要在报表中将此数据用作字段名。还要确保每一列都包含类似项目，例如，一列中包含文本而另一列中包含数字值。删除任何自

10、动的分类汇总不要担心，数据透视表将为您计算分类汇总和总计。命名区域如果计划稍后添加其他数据，请确保为 源数据 中输入的单元格 区域指定名称。这样，无论在何时向数据区域添加数据，都可以更新数据透视表，将新数据包括在内，而不必指定新区域引用。使用高级筛选”命令要使用经过筛选的数据，指向 数据菜单中的筛选，单击高级筛选，然后单击复制到其他位置。这样可将筛选后的数据提取至其他工作表位置，从而可以在您的数据透视表中使用这些数据。注释 不要就地筛选数据或使用 自动筛选 命令。此操作会隐藏列表中的一些数据，但数据透视表仍将包括全部数据。外部数据源可以使用数据库文件、文本文件或In ter net数据源。要准

11、备外部数据，可能需要执行下列操作：安装必要的工具和驱动程序您可能已完全准备就绪，只需确保安装了相应的开放式数据库连接（ODBC）驱动程序或所需的数据源驱动程序。初次使用时，将自动安装 Microsoft Query （包括帮助和驱动程序）。然后，当您使用 数据透视表和数据透视图向导 ”创建数据透视表时，将使用Microsoft Query 实际地检索外部数据。如果需要，在启动向导之前检索数据如果下表包含了您使用的数据检索方法，您将无法在 数据透视表和数据透视图向导”中用Query来检索数据。相反，在启动向导来选择包含外部数据的工作表范围之前，应按照 以下说明将数据插入 Excel工作簿。Off

12、ice数据连接建议使用Office数据连接（.ode ）文件为您的报表检索外部数据。您可以在仅从单个数据库表中或者从OLAP数据库中检索数据时使用这些文件，这样，在您创建报表之前，您无需合并外部数据库中多个表的数据，或是筛选数据以选择特定记录。查询文件和报表模板要使用查询文件（.dqy、.oqy或.rqy ）检索数据，请在 Excel中打开查询文件。要使用没有包括数据透视表的报表模板（.xlt），请打开该模板。参数查询要使用参数查询检索数据，必须首先在 Microsoft Query 中创建参数查询，以便它把数据返回Excel。请注意，您不能创建参数查询来从OLAP数据库中检索源数据。有关创建

13、参数查询的详细信息，请参阅 Microsoft Query 中的帮助。Web查询 要使用 Web查询来检索In ternet上的数据，您必须首先创建 Web查询并将数据 检索到Excel中。要创建 Web查询，请指向数据菜单上的导入外部数据，然后单击新建Web查询。多重合并计算数据区域您可以合并与汇总几个不同Excel列表的数据。要准备来自多个 Excel列表的数据，可能需要执行下列操作：确保列表组织良好务必整洁！例如，确保列表有与您要汇总的项目相匹配的行和列名称。删除任何自动的分类汇总不要担心，数据透视表将为您计算分类汇总和总计。命名区域如果计划稍后添加其他数据，请确保为 源数据 中输入的单

14、元格 区域指定名称。这样，无论何时向数据区域添加其他数据，都可以更新数据透视表，将新数据包括在内，而不必指定新区域引用。另一个数据透视表如果计划使用同样的数据创建多个数据透视表，可以重新使用现有的数据透视表，来创建新的数据透视表，从而 节省内存和磁盘空间。此操作将把原始的和新的数据透视表链接起来。（如果您刷新了原数据透视表的数据，新 报表的数据也会刷新，反之亦然。）要准备来自其他数据透视表的数据，可能需要执行下列操作：确保两个数据透视表在同一工作簿中如果原数据透视表位于不同的工作簿，请将原报表复制到您要显示新报表的工作簿中。检查页面字段设置您可能已在原数据透视表中更改了页面字段设置，以便分别检

15、索每个页面的外部数据。在这种情况下，您需要重新设置页面字段，以便一次为全部项目检索外部数据。要检查设置，双击每一个页 面字段，然后单击高级。注释要了解有关页面字段的其他信息，参见本文稍后部分的数据透视表解析”。第2步：创建数据透视表要创建数据透视表，请首先使用数据透视表和数据透视图向导”。该向导可帮助您指定要使用的数据及创建报表框架。然后您可使用 数据透视表工具栏在该框架内排列数据。例如，您可以选择要包括的数据类别（衬以蓝色底纹）和要汇总的相关数据值（衬以黄色底纹）yWTi豆133 -_UH_UH- - tntn* * 3J3J nrrnrr* * irTirT* * 月二一 一一一二二二二二

16、二2003奶制品2003肉类2003农产DR2003吹料2003则制品2003饮料2003肉类2003肉类2003农产品销售鶴单位区域3636 5611 西部6725 6690 H 匕部6715 265243S5 5S369662 34001323 23613571 5178675B 73174953 58S949231242666741450234TJTJ FJ-FJ- FJFJ rj-rj- -rj-rj- & ThTh1 1KrKr 部部部部部3J3J部剤 北西南南西南西西Excel创建完报表后，它将自动汇总和总计数据注释有些情况下，您可能不希望通过在工作表上直接拖动字段来手动定义数据透

17、视表布局。例如，如果源数据然后，告诉Excel如何在数据透视表的行和列中排列数据类别。 值。ABD销售额总计类型n区域饮料奶制品肉类农产品汇总东部35,23339,44433,40628j675耳北部51,3326331B17,06226755159,1115南部30,17227.3S9502539J074155,1606西部40,392411108335363661293037汇总157,629174,361g驱13L014无斗32要创建数据透视表，执行下列步骤：启动数据透视表和数据透视图向导1.打开您要创建数据透视表的工作簿。如果您的报表要基于Excel列表或数据库，请单击列表或数据库中的

18、一个单元格。2.在数据菜单上，单击 数据透视表和数据透视图。3.在数据透视表和数据透视图向导”的第1步中，请选择将要使用的数据类型，然后单击所需创建的报表类型下的数据透视表。4.在向导的第2步中，按照定位数据的说明进行操作。如果要为数据透视表检索外部数据，可以从Excel帮助或Query帮助中获得有关使用 Microsoft Query 或Query向导的详细信息。5.在向导的第3步中单击完成。定义数据透视表的布局1.将字段按钮从数据透视表字段列表窗口拖到数据透视表图表的标签区域。2.要重新排列各字段，请在区域间拖动这些字段。要删除字段，请将其拖至数据透视表之外。来自大型外部数据库，手动定义布

19、局则可能过于费时。在这种情况下，可以使用向导定义布局。在数据透视表和数据透视图向导”的第3步中单击布局来设置数据透视表布局。数据透视表解析您可能已经注意到，Microsoft Excel使用了特定的术语来标识数据透视表的各部分。如果您尚不熟悉这些术语, 请阅读此部分，迅速获得初步知识。年虞类型销售人员 DodsorthEiic hananSuyama SuyamaDavolb Buchanan Dodsworth Buchnari Davolio Suyma Buchanan乘制怎计北都BuchananDayaio _1 比 9023,5228,725%羽626513/30 1旳4,56299

20、1 _工页面字段此字段来自源数据，用于数据透视表中的页面（或筛选） 。例如，年”是一个页面字段，可以使用年”字段仅显示2003年、2004年等的汇总数据。曰数据字段此字段来自源数据，其中包含要汇总的值。例如，销售总额”是一个数据字段。对于多数源数据类型，可以选择汇总数据的方式（例如，求总和、平均值或计数）。数据字段一般用于汇总数字，但也可汇总用于文本。例如，可以对某字段中特定文本项（例如是”或 否”）出现的次数进行计数。列字段此字段来自源数据，用于数据透视表中的列。例如，类型”是一个列字段。项目行、列或页面字段的子类别。例如，类型”字段包含了下列项目：饮料、奶制品和肉制品。销售人员字段包含了这

21、些项目：Buchanan、Davolio、Dodsworth 和 Suyama。行字段此字段来自源数据，用于数据透视表中的行。例如地区”和销售人员”都是行字段。数据区域 数据透视表中包含汇总数据的单元格。例如单元格C5中的值汇总了 2003年Buchanan在东部地区的饮料销售额。也可以说，该单元格是源数据中包含Buchanan、饮料、东部和2003等项目的各行的销售额汇总。-第3步：自定义数据透视表您现在已经创建了数据透视表，但这还只是开始。是否还记得有关数据的那些问题？数据透视表不仅是静态的汇 总，还可以使用该表以交互的方式探索数据的含义。可以查看详细信息，也可进行大局观察。可以按照地区或

22、时 间段查看销售额。自定义类型由您自己确定。要自定义数据透视表，请执行下列一项或多项操作：添加或删除数据是否希望在数据透视表中查看多一些或少一些的数据类别？可以方便地添加或删除字段（包括行、列或页面字段）要添加字段，请从 数据透视表字段列表”窗口中将字段拖至数据透视表报表中的相应区域。要删除字段，请将字 段拖至数据透视表之外。注释|不得在数据透视表中直接编辑数据。而是要编辑源数据，然后刷新数据透视表。快速重新排列布局 是否需要重新组织数据透视表以获得不同的数据汇总结果？通过移动字段或重新安排字段内各项的顺序，可以快 速旋转”数据透视表。16J9114374lj441iBuchananDavoi

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

24、项目：显示或隐藏项目的特定详细信息单击字段中的项目，然后单击 数据透视表 工具栏上的显示详细信息 或隐藏详细信息 按钮。如果出现提示，单击希望显示其详细信息的字段。例如，对于 类型”字段中的 饮料项，通过显示每年销售额可以增加其详细信息的内容。显示特定数据单元格的基础源数据只需双击单元格即可查看其详细信息例如，双击东部地区的饮料分类汇总，可以查看用于计算该单元格的源数据记录B BI-I-K-1_ 1年度1 _|1II销售额总计类型IT匚区域销售人员饮料奶制品4 45 5Buchanan1,1329jl36|9jl36|Davolio13f7812253!6 67 7Dodsworth4,097

25、-* 11Suyama7,355|9 9东部总计0 35,2333934439344；WW3 3個D D| | E E | | F F | | G G月份年度类型销售人员销售濮单恆区黴七月2003饮糾Buchanan11323326部J三月2003饮糾DavolioB3342933东部42004饮糾Davolio54479069东部5六月2004饮糾Dodsworth21998496东部6四月2003饮幣Dodsworth1098ei7东部7十月2004饮料Suyama51223299东部口 I一月2003饮翘Suyamd7S39 2690东部9一月2004饮料Suyama35634183-第

26、4步：打印数据透视表是否要打印数据透视表？如果该报表是一个多页报表，可以设置打印选项以控制报表的打印外观，如下所示:为方便浏览多页的数据透视表，可以在各页顶端自动重新打印标签。例如，在打印页面上，可以重复行1和行2中的标签，也可重复外行字段项目四月”销售额汇总-第2页销售额总计类型月份区域销售人员诙料奶制品 肉类 农产品汇总北部DayoliaSuyarria2,7669|2911,500107312了鋁北部总计占極9.2911,50013559南部Davolio Dodsworth2严764S0450 乙慨四月南部总计23764502,928-一 一1PS611,851为确保在每页中能打印数据透视表的逻辑区块，可以指定Excel插入分页符的位置。可以选择在特定位置插入分页符，例如，在月”字段中每个月开始之前插入分页符，以避免将一个月的项目分隔开来。例如，在下图中，自动分页符插入到了行41和行42之间，将四月的项目分隔开来。要使四月的项目打印在一起，可以指定将分页符放置在行38三月总计”的正下方。AB11销售颤类型IIHl月份匡域销售人员饮料则制品一月东制3,5634J356I东部总计3,5631356；t北部Buchanan