excel数据透视

下载 下载 第13章 Excel的数据透视表 问题 • 创建数据透视表 数据透视表重要性 理解交叉报表 理解控制变值报表 使用数据透视表向导 定义数据透视表字段 字段计算 查找数据透视表页面 将数据透视表的页面加至单一的工作簿页面 在数据透视表的各区域中使用多重字段 • 控制数据透视表的显示方式 更改数据透视表字段的排序 更改数据透视表字段的位置 浏览隐藏的报表详细信息 删除报表详细信息 删除数据透视表 结构化选取 更改列标 更改表格尺寸以适应新的字段名称 更改数据透视表单元格的内容 • 应用数据透视表 更新数据透视表 按时间间隔分类汇总 字段排序 显示分类汇总 使用数据透视表图表 本章摘要 •概述。E x c e l进行数据分析的功能卓然不群——它能从大量纷繁芜杂的数据中 总结 初级经济法重点总结下载党员个人总结TXt高中句型全总结.doc高中句型全总结.doc理论力学知识点总结pdf 并识别出 其趋势、类型，为决策者提供有力支持。数据透视表以内部列表或外部数据库为基本要素， 并立即产生一个交叉报表（结果生成在行与列的交叉单元格中），或产生将数据分类精简为 第13章 Excel的数据透视表使用133 下载 用户指定类别的控制变值报表（结果生成在分类汇总的各组中）。在浏览或对数据透视 表生成的报表进行格式编辑时，您拥有多种选择，这一特性使您得以充分地利用它。 • 创建数据透视表。用 E x c e l可以创建数据透视表帮助进行数据分析。快速生成数据透视 表并在屏幕上轻松地处理数据，将字段定义在所需的区字段，对字段中的数据进行计算， 并控制数据透视表中页的显示方式。 • 控制数据透视表的显示方式。不要误以为创建的数据透视表只能以最初的显示方式显示 出来。您可以改变字段的次序和定义位置，并浏览报表中的隐藏部分。用完数据透视表 时还可以删除它，或更改数据透视表中的列标题，改变表格的大小，甚至可以更改数据 透视表的单元格内容。 • 应用数据透视表。一旦创建完成，数据透视表可以不断更新。您可以将部分求和引入数 据透视表，将数据透视表中的各类信息分类，并以数据透视表中的数据为基础生成数据 透视图。此后该图将随着表中的数据而更新。 13.1 创建数据透视表 人们总劝我以数据为基础创建数据透视表。但什么是数据透视表，为什么要用它呢？ 数据透视表是 E x c e l的简明报表，用户可对它进行轻松的交互式操作，从而可以改变数 据汇总的类型。数据透视表可生成交叉报表或控制变值报表（这些术语与数据库管理系 统通用）。 什么是交叉报表？ 交叉报表的一个示例就是地图上的里程表。如果您在边栏和顶栏中各指定一个城市，那 么，两个城市的交叉单元格中就是这两个城市之间的距离。但数据透视表具有更强的功 能，它可以对交叉单元格求和、求平均值或进行其他计算。 用更专业的语言来说，数据透视表是一个表格，其元素同时与 X轴及Y轴对应。表格中 的交叉单元格代表 y处的x值。例如：您可以创建一个按月份显示某校区内每所学校学生出勤 率的表格。所有学校的名称放在第一列，当年的 1 2个月份放在自左向右的 1 2列。在学校与月 份的交叉单元格中，可以看到该校学生在某月份内的平均出勤率。如果 7月份的出勤率比其 他学校放假期间的出勤率要高，那也没什么奇怪的。但是，这样一张图表却可以显示出有些 学校能在某学年的某些特定月份将更多的学生留在学校，从而保持更高的出勤率。而这类信 息可以帮助学校管理机构考虑这些保持较高出勤率的学校是否拥有值得其他学校效仿的有用 经验。 如果您要人工创建这张表格并输入每个单元格对应的计算公式，那将是一个极其冗长而 乏味的过程。但如果这项工作是以一张包含了学校名称、日期以及该日期内学生出勤率的表 格为基础，那么，数据透视表的相关命令将帮您在不到 1分钟的时间内创建该表格。 什么是控制变值报表？ 用数据透视表也可以创建控制变值报表。所谓控制变值报表是指可以多层次生成分类汇 总的报表，这些分类汇总称为变值。控制变值报表的一个经典示例就是一张不同顾客类 1 2 3 型、不同地区内产品销售量的报表。在这样的报表中，您可能希望看到一张以地区为主变值 数据区的清单，然后列出该地区内所有类型的顾客。您将看到每种顾客对应的某项产品销售 总额。分类汇总就是每种顾客对应的某项产品销售额和每个地区对应的某项产品销售额。数 据透视表还可以求某种产品的销售总额。此时行与列显示的都是总计与分类汇总。图 1 3 - 1显 示了一份控制变值类型的数据透视表的一部分。 使用控制变值报表的主要优点之一是可以看到某地区内某种产品的销售总额，而且，只 需轻轻单击几下，就可以将该表格变为按地区显示产品销售额的报表，如图 1 3 - 2所示。 数据透视表作为生成图表的基础也是十分有用的。当您将一张图表链接到数据透视表 （这是创建数据透视表时的初始选项）后，该图表将随着数据透视表中数据的变动而自动改变。 您也可以通过将一张数据透视表链接到另一张数据透视表，来快速地建立简明数据表。 如何创建数据透视表？ 用“数据透视表和数据透视图”向导创建数据透视表。该向导在安装E x c e l时已同时安装。 1) 首先应确定要用到的表格或数据区域。该表格可以在 E x c e l工作表中，或位于外部数 据库中。请参看下面第3步的有关选项。 2) 如果该表格在E x c e l工作表中，选定表格中的任意单元格。要注意的是，为了使所有数 据信息都包括在数据透视表中，整张表格必须位于连续的单元格区域中。 134使用中文Excel 2000疑难解析 下载 图13-1 在数据透视表中生成多层次的分类 汇总 图13-2 将数据透视表改为显示各地区内某 产品销售额 4 3) 选择“数据|数据透视表和图表 报告 软件系统测试报告下载sgs报告如何下载关于路面塌陷情况报告535n,sgs报告怎么下载竣工报告下载 ”菜单项。将看到步骤之1的对话框，如图1 3 - 3所示， 它将询问用作数据源的类型。该数据源可以是Microsoft Excel数据清单或数据库、外部数据源， 多重合并计算数据区域，或另一份数据透视表或数据透视图。选定相应选项后单击“下一步” 按钮。 4) 选择要创建的是单独的数据透视表还是带数据透视图的数据透视表。 5) 第2步的对话框将询问数据源的位置。如果已经在数据源内选定了一个单元格，那么整 张数据表应该被选取框包围。整个数据范围也将在数据源字段中显示出来。确认选取的数据 范围无误后，单击“下一步”按钮。如果准备用外部数据库作数据源，单击“浏览”按钮并 找到包含数据的对应文件名。选定文件名后单击“确定”按钮，然后单击“下一步”按钮， 进行数据透视表向导的下一步操作。 6) 单击“版式”按钮， 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 数据透视报表，如图 1 3 - 4所示。（如果现在不单击“版式”按 钮，可在向导对话框关闭后，直接在工作表上设计数据透视表）。所有字段将以按钮形式在对 话框右侧显示出来。在报表表单上，共有“页”“行”“列”“数据”等4个区域。可将任一字 段按钮拖至上述任一区域。然后单击“确定”。 7) “数据透视表和数据透视图”向导的最后一个对话框会询问将数据透视表放置在什么 地方。可以在已有工作表上选择一个新的区域或创建一个新的工作表。最安全的做法是将数 据透视表放置在新的工作表中。单击“完成”按钮，数据透视表结构被确定。 可以将字段放置在数据透视表的什么位置？ 可以把字段从数据表中拖至数据透视表结构画面的四个区域之一。如果把字段拖至“页” 区，数据透视表将以该区域内的数据为基础创建。例如，如果您把角域拖至页面区域， 那么在每个角域中只能看到一个报表页面。表格不会立即分割为多个角域。要么每次看到某 第13章 Excel的数据透视表使用135 下载 图13-3 为数据透视表选择数据源类型 图13-4 通过拖动字段到想要的位置来 设计报表版式 5 个角域的汇总，要么看到所有角域的总计。 注意 如果在数据透视表版式中选定页区域，Excel将按页面分割打印数据透视表。但 它不会为数据透视表的每个部分分别创建工作簿。 如果将某字段拖至行区域，就指定了报表的每一行要汇总的数据。例如：将“地区”字 段拖至“行”区域，那么报表的每一行将显示一个不同的区域。如果有 7个地区，最终报表将 显示7行。 如您所预想的，“列”区域用来设定报表中自上向下的列标。如果将“产品”字段拖至 “列”区域，那么报表从左至右各列将显示每种产品的总计。如果已经将行区域定义为地区， 那么您将在报表的每一列中看到每种产品的销售情况，同时也看到每个地区的销售情况。 如果要对某字段进行数学处理（或计数），可将该字段拖至“数据”区域。如果将某字段 拖至“数据”区域，系统默认给出该字段的汇总。如果将一个非数字字段拖至该区域，系统 默认给出其个数。例如：如果将“单价”字段拖至“数据”区域，那么您将看到每种产品和 每个地区的销售总和。位于“东北地区”栏和“小件”栏交叉单元格将显示东北地区小件的 销售情况。 您可将经销商的姓名放置在数据区域，这样可得到每种产品的订单总数。由于每个记录 只包含有一个姓名字段而且是非数字字符，系统将默认统计字段个数，从而给出订单总数。 我并不需要数据总计或字段数目功能。那么我可以利用数据透视表中的其他功能吗？ 将某字段拖至数据透视表布局对话框的数据区域，对于数字字段，系统默认给出汇总； 对非数字字段，系统默认给出字段数目。把区域按钮放置到位后，可以双击该区域按钮， 选择不同的计算功能。“数据透视表字段”对话框会打开并提供许多其他功能供选择，包括 “平均值”、“最小值”、“最大值”、“ 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 偏差”等。 可将某字段多次拖至数据区域 ,这样既可以看到销售总额，也可以看到销售额的平均值。 由于计算类型不同，数据透视表将它们作为两种不同的字段对待。 如果将已有字段拖至“数据”区域，那么您只能使用数据透视表字段对话框中提供的函 数功能。Excel 97首次提供的一个新的功能，使用户可以同时定义被计算的字段和被计算的项 目。该功能可以使用户在数据透视表中定义新的字段用于公式计算，或定义新的数据类别 （如“产品”表格中没有数据的新项目）。这样，用户可以根据已有数据的计算结果对新数据 进行预测。 我在数据透视表向导中将一个字段拖至“页”区域，得到一张包括整张表格的总计在内 的总报表。但看起来数据透视表只给我的工作簿添加了一张页面，我怎样才能找到其他 页面呢？ “页”在数据透视表向导中几乎是个误用的术语，因为选择该选项并不会给用户工作簿 增加页面。在工作表的数据透视表顶部的“页”区域可以看到，该处有个下拉式列表， 其中很可能保留着该字段内所有数值的当前值。单击该下拉式列表，它将弹出所有“页”字 段的数值。选定某一数值，将立即计算数据透视表的该值所对应的汇总，使用户得以滤除其 他信息，只显示当前页的数值。该功能与数据表格中用于筛选信息的“自动筛选”功能相类 136使用中文Excel 2000疑难解析 下载 6 7 第13章 Excel的数据透视表使用137 下载 似。它只显示符合筛选条件的数据，其他数据仍然保存完好，选择“数据｜筛选｜全部显示” 菜单项，即可显示所有数据。 如果要将数据透视报表的每一页变为工作表中的单一页面，怎样才能强行将数据透视表 页变为单一的工作表页面呢？ 用户可能需要在工作表的一页中看到数据透视报表的实际页面。假设您要打印数据透视表， 您可能不愿手动选择每一页然后打印它。要往工作簿中添加工作表页面，只需双击工作表 顶部的“页”区域。如果数据透视表包含过多“页面”字段的数据项，就要小心操作。因为 E x c e l将整张数据透视表保留在内存中，如果您的计算机没有足够的内存，就可能会出问题。 我的数据库软件包提供给了一个控制变值报表，其中列出了地区，对每个地区还列出了 经销商名称。对每个经销商，还给出了每种产品的销售额汇总。我可以创建一个能提供 同样数据浏览功能的数据透视表吗？ 在数据透视表向导的版式对话框中，可将多个字段拖至四个区域中任一区域。如果用户 要得到传统版式的控制变值报表，可将多个字段拖至“行”区域。例如，仍以销售报表 作例子，如果将“地区”、“经销商”和“产品”拖至“行”区域，将“单价”拖至“数据” 区域，就可以得到如图 1 3 - 5所示的数据透视表。该表按“地区”、“经销商”和“产品”分层 次列表。 13.2 控制数据透视表的显示方式 如果我改变主意想让数据透视表改变次序，该怎么做？ 数据透视表最诱人的功能之一，就是可以很方便的改变其版式。方法有两种，一种是重 新打开数据透视表向导，另一种是将字段直接拖至数据透视表中。 数据透视表单元格与一般的单元格不同。由于它们与隐藏的数据之间存在关联，所以单 元格不能直接从键盘输入新值或进行替换。但是，控制数据版式的标题字段却可以轻松修改。 如果重新进入数据透视表向导，用户可以很方便地将字段拖至目标区域。可在数据透视 表内单击右键，然后从快捷菜单中选择“向导”重新打开向导。 8 9 图13-5 控制变值数据透视表层次示例 1 138使用中文Excel 2000疑难解析 下载 如果您更喜欢在包含数据透视表的工作表上处理数据，可以将鼠标指向目标字段并将它 拖到目标区域。一旦决定这样做，也就是说，您不愿按地区、经销商和产品来浏览销售额， 而希望看到各地区内某种产品由各经销商完成的销售额，您可以将光标放置在“姓名”标题 栏上，并将它拖至左侧“地区”和“产品”之间。 E x c e l将立即改变数据透视表的版式。 我怎样才能改变数据透视表中数据的显示方式，使数据字段左右相邻，而不是上下 相邻？ 数据透视表中，如果一个区域内有多个字段，默认的版式是自上至下逐个放置。例如： 如果将“数量”字段和“单价”字段都拖至“数据”区域，报表默认在总销售额上方显 示总销售量。但大多数用户并不喜欢以这种方式显示数据。 如果希望看到上述两项并排布置，而不是上下布置，可将数据字段拖至右侧（见图 1 3 - 6）。 报表将自动重新布局，宽度加大而长度变短（列数变多而行数变少）。 我偶然在某个单元格上双击了一下，工作簿上出现了一个新的页面。这是怎么回事？ 数据透视表为用户提供浏览任一数据汇总中隐藏的详细信息的功能，可能是这一功能把 您弄糊涂了。双击某单元格，可以调出隐藏的详细信息，该信息显示并存储在当前工作 簿的一张新工作表的新建表格中。 如果您要浏览组成数据透视表中各项数据的详细信息，可以利用此功能，双击某单元格 即可。 我双击一个单元格并浏览了生成表中数据的详细信息。在浏览完毕后，我怎样删除它们？ 没有用于撤消上述操作的专门命令（比如“编辑｜撤消”），但需注意详细信息将显示在 一张新的工作表上。用户可以通过选择“编辑｜删除工作表”菜单项来删除包含该详细 信息的工作表。在弹出的窗口上按下“确定”按钮，该工作表将永久删除。如果想再次浏览 详细信息，可再次双击数据透视表单元格，将会出现一张新的包含详细信息的表格。 我用数据透视表处理完数据后，可否删除整张数据透视表？ 您可以删掉包含数据透视表的工作表，也可以关闭创建数据透视表所用的文件，并选择 不保存更改内容。 2 图13-6 将数据字段拖至右侧使报表变宽 3 4 5 您也可以删除整张数据透视表，这时需要首先全选数据透视表（包括标题栏和“页”标 签）全选数据透视表的一个快捷方法是单击左上角单元格。 注意，您不能删除数据透视表中的某一部分。要么不删，要么全删。 当我单击数据透视表中的行号或列标单元格时，有一组单元格被选中，这是怎么回事？ 您遇到的是Excel 数据透视表的“结构化选定”功能。这是同时选中一组同类单元格的 快捷方法。它为突出显示一组同类单元格或选中特定单元格进行格式编排提供了方便。 您可以按下述步骤关闭或打开结构化选取功能。 1) 将鼠标指向数据透视表的特定类型数据，同时单击鼠标右键打开快捷菜单。 2) 从快捷菜单中选择“选定”项。 3) 选择“启用选定内容”项。 您也可以指定结构化选定功能是同时选取标志和数据，或只选取标志和只选取数据。如 有必要，也可以指定该功能选取整张表格（这种情形较少使用）。 如果“启用选定内容”打开，用户可以将鼠标指向数据标志上部或下部边缘，等待光标 变成小的后向箭头。出现后向箭头后，单击“数据标志”。则所有该类数据全部选中。例如， 单击“地区”字段，则选中所有地区名称。单击含有特定地区名称的单元格（比如 N E），则 所有包含“N E”字符的数据行（或数据列）全部被选中。如果已经定义了按产品及地区显示 销售额的数据透视表，单击带 N E标志的单元格将立即选中东北地区所有产品的销售额。 怎样给数据透视表列标重命名？ 有时数据透视表的列标显得不太合适。对一张要交给别人审阅的报表来说，“单价总计” 或“名称数目”就是不太理想的叫法。在数据透视报表中您可以很轻松地改变标题名称。 共有3种改变标题的方法。 • 改变标题最快捷的方法是双击标题名称。此时将弹出“数据透视表字段”对话框，显示 字段名，同时显示如何对字段进行计算的相关信息。您只需在该窗口的“字段名”栏键 入新的名称即可，然后单击确定按钮。新的名称将取代原来的名称。 • 数据透视表向导打开时（如果数据透视表向导已关闭，可在数据透视表中单击鼠标右键， 选择“向导”重新打开向导），用户可进入“版式”选项，双击字段名，然后在数据透 视表字段对话框的“字段名”栏输入新名称。 • 用户可在任何时候单击右键并从快捷菜单中选取“字段设置”项，打开“数据透视表字 段”对话框。 如果您在最初定义字段名时（在创建数据透视表之前）就已改好字段名，那将省去许多 格式编排的麻烦。 E x c e l将自动编排数据透视表的格式，使列宽与相应的列标志匹配。由于 “总计”和“数目”之类的名称过长，数据透视表将分配给它们超过实际需要的空间，而这将 使您更难于同时浏览整张数据透视表。如果缩短字段名称， E x c e l自动减短字段—但该功能 仅在还未创建数据透视表时起作用。 字段名可以任意选取。但也要遵守一条限制原则。已经用过的字段名不能重复使用。如 果工作表中已有字段命名“销售额”，您将它拖至数据透视报表布局图的数据区域，而该字段 的默认名称可能是“销售额汇总”。此时您不能将其命名为“销售额”，尽管这个名称是最合 第13章 Excel的数据透视表使用139 下载 6 7 140使用中文Excel 2000疑难解析 下载 理的，这是因为已经有“销售额”的字段名。您可在“销售额”前面或后面加上空格，但这 样做难于记住，或者您可以将其命名为“销售额￥”，这可能是最好的做法。 在改变了所有的数据标签，将其名称缩短后怎样才能让表格随着新名称变窄呢？ 可选取“格式 |自动套用格式”菜单项，使表格随字段名缩短而自动调整尺寸。数据透视 表默认使用“经典1”格式，所以重新选中它，E x c e l将自动计算表格列宽。 如果要改变某一组特定单元格的格式，可选定这些单元，然后单击鼠标右键，从快捷菜 单中选取“设置单元格格式”。图1 3 - 7显示了“单元格格式”对话框。 有时候最简单的方法最容易被遗忘。如果您的数据透视表列数不是非常大，改变其列宽 的最简单的方法可能是这样的，将鼠标在表格的列标行（ A，B，C等等）横向拖动，当光标 变为将相邻两列分开的短竖条时，双击鼠标。 E x c e l将显示每一列的最佳宽度。 如果我改变数据透视表某个单元格中的文本，而该文本在数据透视表的其他地方重复出 现，要改变它们有什么简便的方法吗？ 用户可以改变数据透视表中任何单元格的内容。单击单元格可编辑单元格内容，就像编 辑工作表中的单元格一样。如果选定了标签单元格，要交互的改变其名称，并不需要将 所有同类单元格全部选中。改变其中一个标签将改变所有单元格。 13.3 应用数据透视表 如果我更改数据表格中的数据，与之相关联的数据透视表会自动更新吗？ 数据透视表确实与相应数据相关联，但没有自动更新的功能。如果改变相关联数据，必 须选取“数据｜更新数据”菜单项来更新数据透视表。对外部数据源或内部数据源也是 如此。 按下数据透视表工具栏中的“更新数据”按钮可快速更新数据，该按钮上有类似感叹 号的标记。当您创建了一张新的数据透视表之后，“更新数据”按钮可用。如果数据 透视表工具栏不可见，在工作表的工具栏区域单击鼠标右键，从弹出的快捷菜单中选取 “数据透视表”项。 8 图13-7 从快捷菜单选取“设置单元格 格式”，显示“单元格格式”对 话框 9 1 我手头有一张包含每笔销售生意相关数据的表格。有什么办法能按季度或其他时间间隔 算出销售总额？ 您可将数据按时间段分组。例如，假设有一份包含您所有投资交易相关字段的表格：股 票、单价、数量、交易量、日期、交易类别（买进或卖出）、股票类别。您需要按交易 日画出股票买进情况，麻烦的是有太多细节信息。每个交易日在报表中将产生一个新的列。 您想将它们按季度分组。用下述步骤可实现您的想法。 1) 将光标放置在某个数据单元格上。 2) 单击鼠标右键显示快捷菜单。选取“组及分级显示｜组合”选项。 3) 在分组菜单中，有一个选项可按季度分组。也可以按月份分组。而且，可选定起始和 终止季度。选定起始和终止范围后，可将该范围对应的列插入数据透视表中—哪怕该范围 内没有对应的数据。 有没有改变数据透视表排列次序的方法？ 数据透视表对数据进行排序时默认采用字母表或数字升序。地区 1总排在地区2前面。芝 加哥排在印第安那泼里斯前面。而很多时候，用户需要变换数据排序规则。通常，需要 变换的次序可能是按销售量进行从大到小排序（最大的销售量排在最前面）。 按下述步骤改变数据透视表的排列次序： 1) 将光标放置在某个要改变的字段单元格中。 2) 单击右键弹出快捷菜单。选取“字段设置”项。在数据透视表对话框中单击“高级” 按钮，屏幕上将出现图1 3 - 8所示的对话框。 3) 在对话框的“自动排序选项”区域中选取“递减”单选框。 4) 在“使用字段”下拉框中，选取要排序的数据字段（本例中为“单价汇总”）。数据透 视表将变换显示方式，使所有地区按该地区内各类产品销售总额的次序显示。 如果报表包含有额外的控制变值—例如：您现在正在浏览的销售额报表先按顾客类别， 再按地区分类，则每种类型的顾客栏中的地区排序次序都不同，因为此时各地区的排序情况 反映了该地区内的销售金额大小。 有没有办法关闭数据透视表行末尾的总计功能？ 利用“数据透视表选项”对话框您可以更改某些数据透视表功能起作用的方式。单击右 键，显示快捷菜单，然后选取“表选项”选项，即可看到该对话框。 第13章 Excel的数据透视表使用141 下载 2 3 图13-8 数据透视表字段高级选项对话框 4 在对话框的各选项中，可以关闭在数据透视表的行与列中显示汇总的功能。 在创建数据透视表的时候，曾出现是否创建数据透视图的选项。但如果已经有数据透视 表，怎样才能创建数据透视图？ 要以数据透视表中的数据为基础创建数据透视图，首先全选数据透视表。用下述方法可 全选数据透视表。在数据透视表上单击右键，然后选择“选定｜整张表格”选项。此时 应出现数据透视表工具栏。单击工具栏上“图表向导”按钮，数据透视图将自动出现在另一 张工作表上。 提示 如果数据透视表工具栏不可见，可在选定数据透视表数据后，单击标准工具栏 上的“图表向导”，这样就可以创建数据透视图了。 完成上述操作后，您对数据透视表进行的任何更改都会同时影响数据透视图。 142使用中文Excel 2000疑难解析 下载 5 第13 章Excel 的数据透视表 13.1 创建数据透视表 13.2 控制数据透视表的显示方式 13.3 应用数据透视表