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

注意: 我们希望能够尽快以你的语言为你提供最新的帮助内容。本页面是自动翻译的,可能包含语法错误或不准确之处。我们的目的是使此内容能对你有所帮助。可以在本页面底部告诉我们此信息是否对你有帮助吗?请在此处查看本文的英文版本以便参考。

注意: 本文已完成其工作,随后将不再使用。为防止出现“找不到页面”问题,我们将删除已知无效的链接。如果创建了指向此页面的链接,请删除这些链接,以便与我们一起为用户提供通畅的 Web 连接。

如果您想要使用基于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

小型

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

重要: 如果数据透视表中检测到的数据源的两个或多个重复行,数据透视表中显示只能有一行。如果您希望所有行,即使重复的数据透视表中显示的行,您必须添加一列中包含唯一值到表、 查询或从数据源导入的视图。如果数据源不具有唯一列,则可以添加一个数据源。例如,您可以添加一列具有 AutoNum 数据类型到 Microsoft Office Access 表中,具有到 Microsoft SQL Server 表中,标识数据类型的列或计算的列,其中显示每条记录的唯一编号数据库查询或 view。

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

在将工作簿发布到 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. 单击“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. 单击“保存”。

返回页首

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

此信息是否有帮助?

谢谢您的反馈!

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

×