将数据透视表单元格转换为工作表公式

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

数据透视表具有多个提供了预定义的报表中,结构的版式,但无法自定义这些布局。如果您需要更灵活地设计数据透视表的布局,您可以转换为工作表公式的单元格,然后更改通过充分利用可用的所有功能在工作表中的这些单元格的布局。或者,您可以将单元格转换为使用多维数据集函数,或使用 GETPIVOTDATA 函数的公式。大大转换为公式的单元格简化的创建,过程更新,并维护这些自定义数据透视表。

将单元格转换为公式时,这些公式访问与数据透视表相同的数据,并可以刷新查看最新的结果。但是,与报表筛选器可能异常,您不再有权访问的数据透视表,例如筛选、 排序或展开和折叠级别的交互式功能。

注意: 转换联机分析处理 (OLAP) 数据透视表时,您可以继续刷新的数据,以获取最新的度量值,但不能更新报表中显示的实际成员。

了解有关将数据透视表转换为工作表公式的常见情况

以下典型示例说明将数据透视表单元格转换为工作表公式后,可以执行哪些操作来自定义转换后单元格的布局。

重排和删除单元格   

假设您需要每月为员工创建一个定期报表。您只需要一部分报表信息,并且喜欢用自定义方式放置这些数据。您只需执行以下操作即可:用所需的设计布局移动和排列单元格,删除员工月报表中不必要的单元格,然后将单元格和工作表设置为自己喜欢的格式。

插入行和列   

假设您希望显示按区域和产品组中前, 两年的销售信息,并且想要插入扩展其他行中的批注。只需插入行,然后输入文本。此外,您想要添加列显示按区域和产品不是原始数据透视表中的组的销售额。插入一列,只需添加公式以获得所需的结果,然后向下填充列来获得的结果为每个行。

使用多个数据源   

假设您要对生产数据库和测试数据库之间的结果进行比较,以确保测试数据库产生预期的结果。您只需轻松地执行以下操作:复制单元格公式,然后将连接参数更改为指向测试数据库,以比较这两个数据库之间的结果。

使用单元格引用改变用户输入   

假设您要基于用户输入更改整个报表。您可以将多维数据集公式的参数更改为工作表上的单元格引用,然后在这些单元格中输入不同的值,从而得出不同的结果。

创建不一致的行或列布局(也称为非对称报表)   

假设您需要创建一个包含 2008年列称为实际销售额,2009年列称为计划的销售报表,但您不希望任何其他列。您可以创建包含仅在这些列与数据透视表,其中需要对称报表不同的报告。

创建您自己的多维数据集公式和 MDX 表达式   

假设您要创建一个报表,在其中显示三个特定销售人员在七月份针对某一特定产品的销售额。如果您熟悉 MDX 表达式和 OLAP 查询,可以自行输入多维数据集公式。虽然这些公式可能变得非常复杂,但您可以使用“公式记忆式键入”简化公式的创建并提高这些公式的准确性。有关详细信息,请参阅使用公式记忆式键入

注意: 您只能使用此过程转换联机分析处理 (OLAP) 数据透视表。

  1. 若要保存以供将来使用数据透视表,我们建议您进行工作簿的副本,才能通过单击文件转换数据透视表 >另存为。有关详细信息,请参阅保存文件

  2. 准备数据透视表,以便您可以通过执行下列转换后的单元格重排最小化:

    • 更改为与所需布局最类似的布局。

    • 使用报表的交互功能(如筛选、排序和重新设计报表)获得所需的结果。

  3. 单击数据透视表。

  4. “选项”选项卡的“工具”组中,单击“OLAP 工具”,然后单击“转换为公式”

    如果没有报表筛选,则转换操作将完成。如果有一个或多个报表筛选,将显示“转换为公式”对话框。

  5. 决定要转换的数据透视表的方式:

    转换整个数据透视表   

    • 选中“转换报表筛选”复选框。

      这将转换为工作表公式的所有单元格并删除整个数据透视表。

      只转换数据透视表行标签、列标签和值区域,而保留报表筛选   

    • 确保未选中“转换报表筛选”复选框(这是默认设置)。

      这将转换所有行标签、 列标签和值区域的单元格于工作表公式,并保留原始数据透视表,但使用的报表筛选器,以便您可以继续使用报表筛选器筛选。

      注意: 如果数据透视表格式版本 2000年-2003年或更早版本,则只能转换整个数据透视表。

  6. 单击“转换”

    转换操作首先刷新数据透视表,以确保使用最新的数据。

    在执行转换操作时,会在状态栏上显示一条消息。如果该操作需要很长时间,并且您希望在其他时间转换,则可以按 Esc 取消该操作。

    注意: 

    • 不能转换其筛选应用于隐藏级别的单元格。

    • 不能转换其字段包含自定义计算的单元格,这些计算通过“值字段设置”对话框(在“选项”选项卡的“活动字段”组中,单击“活动字段”,然后单击“值字段设置”,即可显示该对话框)的“值显示方式”选项卡创建。

    • 对于转换后的单元格,会保留其单元格格式,但会删除其数据透视表样式,因为这些样式只能应用于数据透视表。

您可以在公式中使用 GETPIVOTDATA 函数时要处理非 OLAP 数据源,当您不希望立即,升级到新的数据透视表版本 2007年格式,或者当您想要避免将数据透视表单元格转换为工作表公式使用多维数据集函数的复杂程度越高。

  1. 确保打开“选项”选项卡上“数据透视表”组中的“生成 GETPIVOTDATA”命令。

    注意: “生成 GETPIVOTDATA”命令设置或清除“Excel 选项”对话框中“使用公式”部分的“公式”分类中的“使用 GETPIVOTTABLE 函数获取数据透视表引用”选项。

  2. 在数据透视表中,确保您想要在每个公式中使用的单元格可见。

  3. 在数据透视表外部表单元格中,键入您要在其中包含从报表的数据点向上所需的公式。

  4. 单击要在数据透视表中的公式中使用数据透视表中的单元格。GETPIVOTDATA 工作表函数将添加到您从数据透视表中检索数据的公式。此函数继续检索正确的数据,如果更改报表布局或刷新数据。

  5. 键入完公式,然后按 Enter。

注意: 如果从报表中删除 GETPIVOTDATA 公式中引用的任何单元格,则该公式会返回 #REF!。

另请参阅

问题:无法将数据透视表单元格转换为工作表公式

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

此信息是否有帮助?

谢谢您的反馈!

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

×