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

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

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

将单元格转换为公式后,这些公式所访问的数据与数据透视表相同,因此您可以对这些公式进行刷新以查看最新结果。但是,您已不能使用数据透视表的交互功能(如筛选、排序或分层展开和折叠),报表筛选有可能除外。

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

你要进行什么操作?

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

将单元格转换为使用多维数据集函数的公式

使用 GETPIVOTDATA 函数转换单元格

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

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

重排和删除单元格   

假设您有定期发生,您需要为您的员工每月创建报表。您只需要报表信息的子集,并且您希望数据布局自定义方式。您只需可以移动和排列所需的设计布局中的单元格删除不是必需的员工月报表的单元格,然后设置格式的单元格和工作表以满足您的首选项。

插入行和列   

假设您希望在显示前两年的销售信息时按地区和产品组划分,并希望增加一些行以插入补充注释,则只需插入行并输入相应文本即可。此外,如果要添加一列,在其中按原始数据透视表中不包括的区域和产品组显示销售量,则只需执行以下操作即可:插入一列,添加一个用于获得所需结果的公式,然后向下填写该列,以获取每一行的结果。

使用多个数据源   

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

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

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

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

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

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

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

返回页首

将单元格转换为使用多维数据集函数的公式

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

  1. 若要保存以供将来使用数据透视表报表,我们建议您进行工作簿的副本,才能通过使用Microsoft Office 按钮 Office 按钮图像 上的另存为命令转换数据透视表。有关详细信息,请参阅保存文件

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

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

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

  3. 单击数据透视表。

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

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

  5. 确定要如何转换数据透视表:

    转换整个数据透视表   

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

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

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

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

      这会将所有行标签、列标签和值区域单元格都转换为工作表公式,而且保留原始数据透视表,但只保留报表筛选以便可以继续使用报表筛选进行筛选。

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

  6. 单击“转换”

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

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

    注释: 

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

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

    • 将被转换的单元格,单元格格式将保留,而删除数据透视表样式,因为这些样式可仅应用于数据透视表。

返回页首

使用 GETPIVOTDATA 函数转换单元格

如果您希望使用非 OLAP 数据源,不希望立即升级到 2007 版新数据透视表格式,或者希望避免使用多维数据集函数的麻烦,则可以在公式中使用 GETPIVOTDATA 函数将数据透视表单元格转换为工作表公式。

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

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

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

  3. 在数据透视表外部的工作表单元格中,请键入所需的公式,一直到要包含数据透视表数据的单元格位置。

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

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

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

返回页首

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

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

此信息是否有帮助?

谢谢您的反馈!

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

×