使用数据透视表使外部表数据可用于 Excel Services

重要:  本文是由机器翻译的,请参阅免责声明。请在 此处 中查找本文的英文版本以便参考。

如果要处理基于 Excel Services 中的外部数据连接的表数据,则不能使用外部数据区域(也称为查询表)。必须创建一个数据透视表,以将多维层次结构数据平展到一个表或两个维中。操作方法如下。

比较多维数据和二维数据

1. 将多维数据透视表转换…

2. …为二维数据透视表…

3. …以便可以在 Excel Services 中显示平展的数据表。

您要做什么?

了解如何 Excel Services 支持连接到外部数据源

了解有关连接到外部数据的数据透视表报表与外部数据区域之间的差异

使二维表格式数据透视表报表

创建数据透视表报表,并将其连接到外部数据源

添加、 布局和排列行标签区域中的字段

更改数据透视表选项和字段设置

为二维表格数据透视表报表的布局

自定义数据透视表报表的设计

将工作簿发布到 Excel Services

了解 Excel Services 如何支持外部数据源连接

数据透视表用于将大量数值数据聚合成总计和合计,并处理被组织成层次结构的多维数据。另一方面,外部数据区域是一个显示非聚合源数据记录的行列结构的二维表。

当您使用的数据连接向导或 Microsoft Query 连接到外部数据时,您通常会创建外部数据区域。此规则的唯一例外是当您创建一个已连接到外部数据的数据透视表报表。数据透视表报表不会创建外部数据区域。这种差异中连接的创建方式,请务必了解当您需要将工作簿发布到 Excel Services,因为 Excel Services 仅支持在数据透视表报表基于的外部数据连接,并且不支持外部数据区域。

返回页首

了解连接到外部数据的数据透视表与外部数据区域之间的差异

何时使用已连接到数据透视表报表 外部数据,有数据透视表的行为与外部数据区域,您应注意的包括以下之间的差异︰

数据透视表按从左到右的顺序将数据分为组和子组

通常,分组的数据基于按某一列或多个列对数据进行的排序。例如,如果要查看按员工部门分组的所有员工,则可以先按部门对数据进行主要排序,然后再按员工对数据进行次要排序。还可以嵌套组,例如“产品系列、类别和产品”或“地域、国家/地区、省/州和城市”。默认情况下,将按照从左到右的顺序将数据透视表自动分成组和子组,以便于显示相关数据,显示标题和汇总信息,以及展开或折叠相关项目。

产品线,分组并按类别subgrouped 在每个产品行内的数据

产品系列

类别

产品

引擎

飞机

机翼装置

机尾装置

助推器

汽车

269 马力

454 马力

排放装置

客车

特热

标准

SUV

小型

通过将具有唯一值的列放在“行标签”区域的左边,可以自动平展数据。

通过添加ID Num列平展相同产品线数据

ID 编号

产品系列

类别

产品

WM-345

引擎

飞机

机翼装置

TM-231

引擎

飞机

机尾装置

BSTR-567

引擎

飞机

助推器

6C-734

引擎

汽车

269 马力

8C-121

引擎

汽车

454 马力

MF-202

排放装置

客车

特热

MF-321

排放装置

客车

标准

MF-211

排放装置

SUV

小型

如果在将列添加到数据透视表后不想显示该列,则可以隐藏该列。(选择该列,然后在“开始”选项卡上的“单元格”组中,单击“格式”旁边的箭头,指向“隐藏和取消隐藏”,然后单击“隐藏列”。)

重要: 如果数据透视表检测到数据源中有两行或更多行数据发生重复,则数据透视表将只显示一行。如果要在数据透视表中显示所有行(甚至是重复行),则必须将包含唯一值的列添加到从数据源导入的表、查询或视图中。如果数据源不包含唯一列,则可以在数据源中添加一个唯一列。例如,可以将具有“自动编号”数据类型的列添加到 Microsoft Office Access 表中,将具有“标识”数据类型的列添加到 Microsoft SQL Server 表中,或将显示每个记录的唯一编号的计算列添加到数据库查询或视图中。

编辑单元格数据和添加计算列

在将工作簿发布到 Excel Services 之前,可以在外部数据区域中编辑数据,但是数据透视表数据始终是只读的。

如果在外部数据区域中双击某个单元格,则会进入编辑模式,您可以更改值,或者甚至可以输入公式。但是,请注意您并未更改数据源中的原始值,下一次刷新数据时,新值可能会被覆盖,是否覆盖新值取决于外部数据区域属性“刷新数据时行数是否发生变化”的设置。

如果双击数据透视表中的“行标签”单元格,则单元格将展开或折叠该“行标签”中的项目。在将数据透视表平展为二维表时,平展操作对使单元格左边组中的所有行值消失或再次出现有影响。

可以在基于外部数据区域的 Microsoft Office Excel 表中的任何位置添加计算列,或在外部数据区域中插入列和向下填充公式。无法在数据透视表内插入计算列(虽然可以插入计算字段)。但是,可以紧挨着数据透视表的左边或右边添加一个向下填充数据或公式的列,并且在向下填充的公式内,可以使用数据透视表单元格引用。

创建图表

如果您想要创建基于数据透视表报表中的数据的图表,您可以创建数据透视图报表,它类似于标准图表,但也有一些 差异,包括以下内容︰

交互     使用标准图表,可以为要显示的每个数据视图创建一个图表,但是这些视图不具有交互性。使用数据透视图报表,通过更改报表布局或所显示的明细,可以创建一个图表并以不同方式交互查看数据。在 Excel Services 中,用户通过与工作簿交互,可以更新标准图表和数据透视图报表,但是这两种图表都显示为静态图像。

图表类型     标准图表的默认图表类型为簇状柱形图,该图可跨类别比较值。数据透视图报表的默认图表类型为堆积柱形图,该图可跨类别比较每个数值占总数值的大小。可以将数据透视图报表更改为除 XY 散点图、股价图或汽泡图之外的任何图表类型。

图表元素    数据透视图报表包含相同的元素与标准图表,但它们也包含字段和项,可以添加到、 旋转或删除,以显示您的数据的不同视图。类别、 系列和标准图表中的数据将分类字段、 系列字段和数据透视图报表中的数值字段。数据透视图报表还可包含报表筛选器。每个字段包含的项,标准图表中显示为图例中的分类标签或系列名称。

格式    大多数格式,包括元素、 布局和样式时,将保留您刷新数据透视图报表。但是,不保留趋势线、 数据标签、 误差线,并向数据系列的其他更改。标准图表不会丢失一次应用此格式。

移动或调整项目     在数据透视图报表中,虽然可以为图例选择若干个预设置的位置之一,并且可以更改标题的字体大小,但却无法移动或调整绘图区、图例、图表标题或坐标轴标题。在标准图表中,可以移动和调整所有这些元素。

如果您愿意创建数据透视图报表中后,您可以将其转换为标准图表。有关详细信息,请参阅文章创建数据透视表对 anaylze 工作表数据创建数据透视图

比较外部数据区域属性和数据透视表选项

外部数据区域和数据透视表都具有可用于控制数据格式和布局以及数据刷新方式的属性。

外部数据区域和数据透视表都允许您调整列宽,如果刷新外部数据,还可以保留列排序、筛选、单元格格式和数据布局。

在外部数据区域中,也可以选择的数据的第一列中包括行号。在数据透视表报表中,没有类似的选项。请参阅部分,数据透视表报表的顺序从左到右的组和子组数据、 有关在数据源中添加唯一列的讨论。

在刷新外部数据区域时,如果数据区域中的行数发生变化,则通过设置以下选项之一,可以控制如何处理现有数据:

  • 为新数据插入单元格,删除没有使用的单元格(默认)

  • 为新数据插入整行,清除没有使用的单元格

  • 用新数据覆盖现有单元格,清除没有使用的单元格

刷新数据透视表时,它只需将中 新的数据。但是,可能会提示您,要求决定是否要覆盖现有当前在报表下方的单元格。

筛选数据

在外部数据区域和在数据透视表中筛选数据非常相似,但是存在一些差别,包括以下方面:

  • 外部数据区域使用“文本筛选器”“数字筛选器”“日期筛选器”命令,而数据透视表使用“标签筛选器”“值筛选器”“日期筛选器”命令。

  • 外部数据区域的筛选器菜单始终中删除不再有效的项目和筛选菜单新项目永远不会包含与应用的筛选器刷新操作之后,但数据透视表报表将具有选项来控制此行为。有关详细信息,请参阅更改数据透视表选项和字段设置部分。

  • 数据透视表具有报表筛选功能(外部数据区域没有),通过此功能,用户可以在数据透视表中处理数据子集。

对数据进行排序

在外部数据区域中,最多可以按 64 列执行多列排序,可以选择按任何列进行主要排序,按任何其他列进行次要排序。但是在数据透视表中对数据排序的过程与对外部数据区域排序的过程有所区别。与外部数据区域不同的是,在数据透视表中,只能按最左边的列执行主要排序,所有其他列将按照其左边紧邻的列执行次要排序。简言之,只可以按照从左到右的顺序执行主要排序和次要排序。

此外,在将具有唯一值的列作为第一列放在左边时,只可以按该列进行排序,因为实际上,您为每行创建了一个主要组。根据用户的需求,可能需要在 Excel 中对数据进行预排序,在将工作簿发布到 Excel Services 后,通过在 Microsoft Office Excel Web Access Web 部件工具窗格中清除“排序”复选框来关闭排序。

使用超链接

与外部数据区域不同的是,在数据透视表单元格中不支持活动的超链接。超链接会被视为文本,但是无法在 Excel 或 Excel Services 中使用超链接访问网页或文档。

返回页首

创建二维表格式数据透视表

若要将多维数据平展为二维表格式数据透视表,需要执行多个步骤。为了获得最佳效果,请按顺序执行以下步骤。

返回页首

创建数据透视表并将其连接到外部数据源

  1. 选择空单元格。

  2. “插入”选项卡上的“表”组中,单击“数据透视表”,然后单击“数据透视表”

    Excel 功能区图像

    将显示“创建数据透视表”对话框。

  3. 单击“使用外部数据源”

  4. 单击“选择连接”

    " 将显示现有连接对话框。

  5. 在对话框顶部的“显示”列表中,为要选择的连接选择连接类别,或者选择“所有现有连接”(默认选项)。

  6. “选择连接”列表中选择连接,然后单击“打开”

  7. 输入位置。请执行下列操作之一:

    • 若要将数据透视表放在新工作表中并从单元格 A1 开始,请单击“新建工作表”

    • 若要将数据透视表放在现有工作表中,请选择“现有工作表”,然后键入要在其中放置数据透视表的单元格区域的第一个单元格。

      或者,单击折叠对话框 按钮图像 以临时隐藏对话框中,选择在工作表上的开始单元格,然后单击展开对话框 按钮图像

  8. 单击“确定”

显示有数据透视表字段列表的空数据透视表将被添加到所输入的位置。

返回页首

在“行标签”区域中添加、布置和排列字段

使用数据透视表字段列表添加、布置和排列字段,并确保将所有字段都添加到“行标签”区域中。

  1. 单击数据透视表。

  2. 请执行下列一项或多项操作:

    • 若要将字段添加到报表中,请执行下列一项或多项操作:

      • 在字段列表的字段部分中,选中每个字段名称旁边的复选框。每个字段将放置在布局部分的默认区域中,但是可以重新排列这些字段。

        默认情况下,非数值字段将添加到“行标签”区域中,数值字段将添加到“值”区域中,时间/日期字段将添加到“列标签”区域中。

      • 若要将字段移动到“行标签”区域中,请右击字段名称,然后在快捷菜单上选择“添加到行标签”

        提示: 还可以单击并按住字段名称,然后将字段从字段部分拖动到布局部分的区域中。

    • 若要重新排列字段,请单击其中一个区域中的字段名称,然后选择下列命令之一:

上移   

在区域中将字段向上移动一个位置。

向下移动   

在区域中将字段向下移动一个位置。

移到开头   

将字段移至区域的首端。

移至末尾   

将字段移至区域的尾端。

移动到行标签   

将字段移至“行标签”区域。

  • 若要删除 字段中,单击字段名称中选择一种在布局区域中,然后单击删除字段,或清除每个要删除的信息,请在域部分中的字段名称旁边的复选框。

    提示: 也可以单击并按住布局部分中的字段名称,然后将该字段名称拖至数据透视表字段列表外。

    注意: 如果清除字段部分中的某个复选框,则将从报表中删除字段的所有实例。

返回页首

更改数据透视表选项和字段设置

若要确保数据透视表与外部区域的行为相似,请执行下列操作:

更改数据透视表选项   

  1. 单击数据透视表。

  2. “选项”选项卡上的“数据透视表”组中,单击“选项”

    将显示“数据透视表选项”对话框。

  3. 为了使列宽在刷新操作结束后自动进行调整,请单击“布局和格式”选项卡,然后在“显示”部分下,选中“更新时自动调整列宽”复选框。

  4. 为了在每次刷新数据时保留格式,请单击“布局和格式”选项卡,然后在“显示”部分下,选中“更新时保留单元格格式”复选框。

  5. 为了确保在刷新操作结束后“筛选”菜单删除不再有效的项目,请单击“数据”选项卡,然后在“每个字段保留的项数”框中,选择“无”

更改域设置   

  1. 单击数据透视表。

  2. 若要在数据透视表中包括应用了筛选器的新项目,对于每个字段,请右击单元格,单击快捷菜单上的“字段设置”,单击“分类汇总和筛选”选项卡,然后在“筛选”部分下,选中“在手动筛选中包含新项目”复选框。

返回页首

将数据透视表布局设置为二维表样式

若要将数据透视表布局设置为二维表样式,请执行下列操作:

  1. 单击数据透视表。

  2. “设计”选项卡上的“布局”组中,单击“分类汇总”,然后单击“不显示分类汇总”

  3. “设计”选项卡上的“布局”组中,单击“总计”,然后单击“对行和列禁用”

  4. “设计”选项卡上的“布局”组中,单击“报表布局”,然后单击“以表格形式显示”

  5. “选项”选项卡上的“显示/隐藏”组中,单击“字段标题”

返回页首

自定义数据透视表的设计

多维数据,而不是二维表设计的数据透视表的内置样式和样式选项。平面报表化到二维表中,这些样式 不显示单元格边框和镶边行镶边列数据透视表样式选项组中的设计选项卡中的复选框不会影响单元格的格式。但是,以便每个单元格有单元格边框和数据透视表报表使用条件格式显示镶边的行或列可以自定义数据透视表样式。

自定义数据透视表样式   

  1. 单击数据透视表。

  2. “设计”选项卡上的“数据透视表样式”组中,选择所需的样式,然后执行下列步骤:

    1. 右击可见的样式,滚动浏览库,或者单击滚动条底部的“更多”以显示所有可用样式。

    2. 单击“复制”

      将显示“修改数据透视表快速样式”对话框。

    3. “名称”框中输入新名称(可选)。

    4. “表元素”框中,选择“整张表格”,然后单击“格式”

      将显示“设置单元格格式”对话框。

    5. 单击“边框”选项卡,然后创建一个单元格边框。

    6. 单击“填充”“字体”选项卡并进行其他更改(可选)。

    7. 单击“确定”两次。

    8. 单击滚动条底部的“更多”按钮,然后在“自定义”类别下,单击刚刚创建的数据透视表样式。

使用条件格式显示镶边的行或镶边的列   

  1. 选择数据透视表中的所有列。

  2. “开始”选项卡上的“样式”组中,单击“条件格式”旁边的箭头,然后单击“管理规则”

    Excel 功能区图像

    将显示“条件格式规则管理器”对话框。

  3. 若要添加条件格式,请单击“新建规则”

    将显示“新建格式规则”对话框。

  4. “选择规则类型”下,单击“使用公式确定要设置格式的单元格”

    1. “编辑规则说明”下的“为符合此公式的值设置格式”框中,输入公式。

    2. 请执行下列操作之一:

      • 若要对行进行镶边,请输入下列公式:

=AND(MOD(ROW(),2)=1,ROW()<=COUNTA(A:A))

  • 若要对列进行镶边,请输入下列公式:

=AND(MOD(COLUMN(),2)=1,COLUMN()<=COUNTA(1:1))

  • 若要对行和列进行镶边,请输入下列公式:

=AND(MOD(ROW(),2)=1,ROW()<=COUNTA(A:A),MOD(COLUMN(),2)=1,COLUMN()<=COUNTA(1:1))

  1. 单击“格式”以显示“设置单元格格式”对话框。

  2. 选择当单元格值符合条件时要应用的数字、字体、边框或填充格式,然后单击“确定”

    可以选择多个格式。选择的格式将在“预览”框中显示出来。

返回页首

将工作簿发布到 Excel Services

  1. 单击Microsoft Office 按钮 Office 按钮图像 ,单击发布,旁边的箭头,然后单击分配给其他人的文档下的Excel Services

  2. “文件名”框中,输入服务器的路径,并接受建议的工作簿名称或键入新名称(如果需要)。

    在运行 Windows Vista 的计算机上   

    • 地址栏中,选择一个目标用户可以访问的网络位置,然后单击保存

      在运行的 Microsoft Windows XP 的计算机上   

    • “保存位置”框中,选择目标用户能够访问的网络位置,然后单击“保存”

      例如,键入http://服务器/网站/文件名

      注意: Excel 只能将工作簿以基于 Microsoft Office Excel 2007 XML 的文件格式 (.xlsx) 或 Office Excel 2007 二进制文件格式 (.xlsb) 发布到服务器中。

  3. 单击“确定”

  4. 若要验证工作簿的可查看区域是否正确地显示在浏览器中,请选中“保存后在我的浏览器中打开此工作簿”复选框。

  5. 单击“保存”

有关详细信息,请参阅文章发布到 Excel Services 的工作簿

返回页首

注意: 机器翻译免责声明:本文是由无人工介入的计算机系统翻译的。Microsoft 提供机器翻译是为了帮助非英语国家/地区用户方便阅读有关 Microsoft 产品、服务和技术的内容。由于机器翻译的原因,本文可能包含词汇、语法或文法方面的错误。

扩展你的技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×