数据透视表转自OFFICE网站.docx

1、数据透视表转自OFFICE网站数据透视表全部显示全部隐藏在数据透视表 （数据透视表:一种交互的、交叉制表的 Excel 报表，用于对多种来源（包括 Excel 的外部数据）的数据（如数据库记录）进行汇总和分析。）中，您可以使用值字段 （字段:在数据透视表或数据透视图中，来源于源数据中字段的一类数据。数据透视表具有行字段、列字段、页字段和数据字段。数据透视图具有系列字段、分类字段、页字段和数据字段。）中的汇总函数 （汇总函数:是一种计算类型，用于在数据透视表或合并计算表中合并源数据，或在列表或数据库中插入自动分类汇总。汇总函数的例子包括 Sum、Count 和 Average。）来合并基础源数据

2、 （源数据:用于创建数据透视表或数据透视图的数据清单或表。源数据可以来自 Excel 数据清单或区域、外部数据库或多维数据集，或者另一张数据透视表。）中的值。如果汇总函数和自定义计算没有提供所需的结果，则可在计算字段 （计算字段:数据透视表或数据透视图中的字段，该字段使用用户创建的公式。计算字段可使用数据透视表或数据透视图中其他字段中的内容执行计算。）和计算项 （计算项:数据透视表字段或数据透视图字段中的项，该项使用用户创建的公式。计算项使用数据透视表或数据透视图中相同字段的其他项的内容进行计算。）中创建您自己的公式 （公式:单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公

3、式总是以等号 (=) 开始。）。例如，可以为销售佣金添加一个带公式的计算项，这对于各个地区可能有所不同。然后，数据透视表将自动在分类汇总和总计中包含佣金。您要做什么？了解有关计算数据透视表中的值的更多信息可用的计算方法源数据的类型如何影响计算在数据透视表中使用公式在数据透视图中使用公式在数据透视表中创建公式添加计算字段向字段中添加计算项按单元格逐个为计算项输入不同的公式调整多个计算项或公式的计算顺序查看数据透视表中使用的所有公式编辑数据透视表公式确定公式是在计算字段中还是计算项中编辑计算字段公式编辑计算项的单个公式为计算项的特定单元格编辑独立的公式删除数据透视表公式了解有关计算数据透视表中的值

4、的更多信息数据透视表提供了计算数据的方式。了解可用的计算方法，了解源数据的类型如何影响计算，并了解如何在数据透视表和数据透视图中使用公式。可用的计算方法若要计算数据透视表中的值，您可以使用下列任一或所有计算方法类型：值字段中的汇总函数值区域中的数据是数据透视表中基础源数据的汇总。例如，下列源数据：生成下列数据透视表和数据透视图。如果基于数据透视表中的数据创建数据透视图，则该数据透视图中的值将影响其关联数据透视表 （相关联的数据透视表:为数据透视图提供源数据的数据透视表。在新建数据透视图时，将自动创建数据透视表。如果更改其中一个报表的布局，另外一个报表也随之更改。）中的计算。在数据透视表中，“月

5、份”列字段提供了“三月”和“四月”两项 （项:数据透视表和数据透视图中字段的子分类。例如，“月份”字段可能有“一月”、“二月”等项。）。“地区”行字段提供了“北部”、“南部”、“东部”和“西部”四项。“四月”所在列和“北部”所在行交汇处的值就是“月份”值为“四月”而“地区”值为“北部”的源数据中记录的总销售额。在数据透视图中，“地区”字段可能是分类字段，它将“北部”、“南部”、“东部”和“西部”显示为分类。而“月份”字段是系列字段，它将“三月”、“四月”和“五月”这几项显示为图例中表示的系列。称为“销售总和”的“值”字段包含代表每个地区每月总收入的数据标记 （数据标记:图表中的条形、面积、圆点

6、、扇面或其他符号，代表源于数据表单元格的单个数据点或值。图表中的相关数据标记构成了数据系列。）。例如，相应的数据标记将在垂直（值）轴 （坐标轴:界定图表绘图区的线条，用作度量的参照框架。y 轴通常为垂直坐标轴并包含数据。x 轴通常为水平轴并包含分类。）上标示出“北部”地区“四月”的总销售额。若要计算值字段，则下列汇总函数可用于除联机分析处理 (OLAP) （OLAP:为查询和报表（而不是处理事务）而进行了优化的数据库技术。OLAP 数据是按分级结构组织的，它存储在多维数据集而不是表中。） 源数据之外的所有源数据类型。函数功能合计求值的总和。这是数字数据的默认函数。计数求数据值的个数。Count

7、 汇总函数的作用与 COUNTA 函数相同。Count 是非数字数据的默认函数。平均值求数值平均值。Max求最大值。Min求最小值。产品求数值的乘积。Count Nums求数字数据值的个数。Count Nums 汇总函数的作用与 COUNT 函数相同。StDev估算总体的标准偏差，样本为总体的子集。StDevp计算总体的标准偏差。汇总的所有数据为总体。Var估计总体方差，样本为总体的子集。Varp计算总体的方差。汇总的所有数据为总体。自定义计算自定义计算 （自定义计算:用数据透视表的数据区域中的其他单元格值对数据区域中的值进行汇总的方法。使用数据字段的“数据透视表字段”对话中的“数据显示方式”

8、列表可创建自定义计算。）显示根据数据区域中的其他项或单元格得到的数值。例如，可将“销售总和”数据字段中的数值作为“三月”销售额的百分数显示，或者作为“月份”字段中的项的运行汇总显示。下面的函数可用于在值字段中进行自定义计算。函数结果无计算显示在该字段中输入的值。总计百分比显示的值为报表中所有值或所有数据点的总计的百分比。列汇总百分比显示每一列或每个系列的所有值为该列或该系列总计的百分比。行汇总百分比显示每一行或每个类别的值相对于该行或该类别总计的百分比。百分比显示的值为“基本字段”中“基本项”值的百分比。父行汇总百分比按下式计算值：（该项的值）/（行上父项的值）父列汇总百分比按下式计算值：（该

9、项的值）/（列上父项的值）父级汇总的百分比按下式计算值：（该项的值）/（所选“基本字段”中父项的值） 差异显示的值为与“基本字段”中“基本项”值的差。差异百分比显示的值为与“基本字段”中“基本项”值的百分比差值。按某一字段汇总将“基本字段”中连续项的值显示为累计总和。按某一字段汇总的百分比计算“基本字段”中连续项的值，该值以百分比显示累计总和。按升序排名显示某一特定字段中所选值的排位，其中将该字段中的最小项列为 1，而每个较大的值将具有较高的排位值。按降序排名显示某一特定字段中所选值的排位，其中将该字段中的最大项列为 1，而每个较小的值将具有较高的排位值。索引按下式计算值： (单元格中值) x

10、 (总计) / (行总计) x (列总计)公式如果汇总函数和自定义计算没有提供所需的结果，则可在计算字段和计算项中创建您自己的公式。例如，可以为销售佣金添加一个带公式的计算项，这对于各个地区可能有所不同。然后，该报表将自动在分类汇总和总计中包含佣金。返回页首源数据的类型如何影响计算报表中可用的计算和选项取决于源数据是来自 OLAP 数据库还是非 OLAP 数据源。基于 OLAP 源数据的计算对于通过 OLAP 多维数据集 （多维数据集:一种 OLAP 数据结构。多维数据集包含维度，如“国家/地区/省（或市/自治区）/市（或县）”，还包括数据字段，如“销售额”。维度将各种类型的数据组织到带有明细

11、数据级别的分层结构中，而数据字段度量数量。）创建的报表而言，OLAP 服务器会在 Excel 显示结果之前预先对汇总值进行计算。不能更改这些预先计算的值在数据透视表内部的计算方式。例如，您不能更改用来计算数据字段或分类汇总的汇总函数，也不能添加计算字段或计算项。此外，如果 OLAP 服务器可提供计算字段（称作计算成员），则将在“数据透视表字段列表”中看到这些字段。还将看到由宏（该宏是用 Visual Basic for Applications (VBA) （Visual Basic for Applications (VBA):Microsoft Visual Basic 的宏语言版本，用于

12、编写基于 Microsoft Windows 的应用程序，内置于多个 Microsoft 程序中。） 编写而成的，并存储在工作簿中）创建的任何计算字段和计算项，但是不能更改这些字段或项。如果需要其他计算类型，请与 OLAP 数据库管理员联系。对于 OLAP 源数据而言，当计算分类汇总和总计时，可以包含或排除隐藏项的值。基于非 OLAP 源数据的计算在基于其他类型的外部数据或工作表数据的数据透视表中，Excel 使用 Sum 汇总函数计算含有数字数据的值字段，使用 Count 汇总函数计算含有文本的数据字段。您可以选择其他的汇总函数来进一步分析和自定义数据，如 Average、Max 或 Min

13、。通过在字段中创建计算字段或计算项，还可以创建使用报表元素或其他工作表数据的您自己的公式。返回页首在数据透视表中使用公式只能在基于非 OLAP 源数据的报表中创建公式。您不能在基于 OLAP 数据库的报表中使用公式。当您在数据透视表中使用公式时，应了解以下公式语法规则和公式行为：数据透视表公式元素在为计算字段和计算项创建的公式中，您可以像在其他工作表公式中一样使用运算符 （运算符:一个标记或符号，指定表达式内执行的计算的类型。有数学、比较、逻辑和引用运算符等。）和表达式。您可以使用常量 （常量:不进行计算的值，因此也不会发生变化。例如，数字 210 以及文本“每季度收入”都是常量。表达式以及表

14、达式产生的值都不是常量。）并引用报表中的数据，但不能使用单元格引用或定义的名称 （名称:代表单元格、单元格区域、公式或常量值的单词或字符串。名称更易于理解，例如，“产品”可以引用难于理解的区域“Sales!C20:C30”。）。您不能使用需要将单元格引用或定义的名称作为参数的工作表函数，也不能使用数组函数。字段名称和项名称Excel 使用字段名称和项名称来识别公式中的报表元素。在下面的示例中，区域 C3:C9 中的数据使用的字段名称为“奶制品”。在“种类”字段中，可以使用公式（如 =奶制品 * 115%）根据“奶制品”的销售情况估计新产品的销售情况。注释在数据透视图中，字段名称显示在“数据透视

15、表字段列表”中，而项名称则显示在每个字段的下拉列表中。不要将这些名称与图表提示中的信息相混淆，图表提示中的信息只用于反映系列和数据点的名称。公式对求和汇总进行操作，而不是针对单个记录计算字段中公式的操作对象是公式中任意字段的基础数据的和。例如，计算字段公式 =销售额 * 1.2 是指将每类产品和地区的销售额的和乘以 1.2；并不是将每笔独立的销售额都乘以 1.2，然后再将所得的乘积结果相加。计算项中公式的操作对象则是单个记录。例如，计算项公式 =奶制品 * 115% 是指将“奶制品”的每笔销售额都乘以 115%，然后再将所得乘积在“值”区域中相加。名称中的空格、数字和符号在包含多个字段的名称中

16、，字段可按任意顺序排列。在上面的示例中，单元格 C6:D6 可以是“四月北部”或“北部四月”。使用单引号将包含多个词、数字或符号的名称括起来。汇总公式不能引用汇总（例如，本示例中的“三月份汇总”、“四月份汇总”和“总计”）。项引用中的字段名称可以在对项的引用中包含字段名称。项的名称必须用中括号括起来，例如“地区北部”。当报表中两个不同字段中的两个项有同样的名称时， 使用这种格式可以避免 #NAME? 错误。例如，如果报表中的“种类”字段中有一个叫“肉类”的项，“分类”字段中也有一个叫“肉类”的项， 用“种类肉类”和“分类肉类”的形式引用项可以防止 #NAME? 错误。按位置引用项在报表中，可以

17、根据当前排序和项显示的位置来引用项。“种类1”是“奶制品”，而“种类2”是“海产品”。当项的位置发生变化，或者显示或隐藏不同的项时，用这种方式引用的项可随之更改。隐藏项并不归入此索引。可以用相对位置来引用项。该位置是由与其相关的包含公式的计算项决定的。如果“南部”是当前地区，则“地区-1”就是“北部”；如果“北部”是当前地区，则“地区+1”就是“南部”。例如，计算项可使用公式 =地区-1 * 3%。如果提供的位置在字段的第一项之前或最后一项之后，则公式将产生 #REF! 错误。返回页首在数据透视图中使用公式若要在数据透视图中使用公式，您可在关联的数据透视表中创建公式（在该数据透视表中可查看构成

18、数据的单个值），然后以图形方式在数据透视图中查看结果。例如，下面的数据透视图显示了每个地区各个推销员的销售额：若要了解销售额增长百分之十后的情况，您可以在使用公式（如 =销售额 * 110%）的关联数据透视表中创建计算字段。结果将立即显示在数据透视图中，如下列图表所示：若要查看北部地区的销售额减去百分之八的运输费后所得到的独立数据标记，可在“地区”字段中使用公式（如 =北部 (北部 * 8%)）创建计算项。生成的图表如下所示：但是，在“销售人员”字段中创建的计算项将显示为图例中所代表的系列，同时在图表中显示为每个分类中的数据点。返回页首在数据透视表中创建公式重要提示不能在连接到联机分析处理 (

19、OLAP) （OLAP:为查询和报表（而不是处理事务）而进行了优化的数据库技术。OLAP 数据是按分级结构组织的，它存储在多维数据集而不是表中。） 数据源的数据透视表中创建公式。开始前，确定在字段中是需要计算字段还是需要计算项。当您想在公式中使用其他字段的数据时，请使用计算字段。当您想在公式中使用字段中的一个或多个特定项 （项:数据透视表和数据透视图中字段的子分类。例如，“月份”字段可能有“一月”、“二月”等项。）的数据时，请使用计算项。对于计算项，可以按单元格逐个输入不同的公式。例如，如果名称为 OrangeCounty 的计算项有一公式 =Oranges * .25 作用于所有月份，则可将

20、六月、七月和八月的公式更改为 =Oranges *.5。如果有多个计算项或公式，则可以调整计算顺序。添加计算字段单击数据透视表。这将显示“数据透视表工具”，上面添加了“选项”和“设计”选项卡。在“选项”选项卡上的“计算”组中，单击“字段、项和集”，然后单击“计算字段”。在“名称”框中，键入字段的名称。在“公式”框中，输入字段的公式。若要在公式中使用来自另一字段的数据，请在“字段”框中单击该字段，然后单击“插入字段”。例如，若要计算“销售额”字段中每个值 15% 的佣金，可输入“= 销售额 * 15%”。单击 “添加”。返回页首向字段中添加计算项单击数据透视表。这将显示“数据透视表工具”，上面添

21、加了“选项”和“设计”选项卡。如果字段中的项已经分组，请在“选项”选项卡上的“组合”组中，单击“取消组合”。单击要添加计算项的字段。在“选项”选项卡上的“计算”组中，单击“字段、项和集”，然后单击“计算项”。在“名称”框中，键入计算项的名称。在“公式”框中，输入项的公式。若要在公式中使用项的数据，请单击“项”下拉列表中的项，然后单击“插入项”（该项必须和计算项来自同一字段）。单击 “添加”。返回页首按单元格逐个为计算项输入不同的公式单击需更改公式的单元格。若要更改几个单元格中的公式，请按住 Ctrl 单击其他单元格。在“编辑栏 （编辑栏:位于 Excel 窗口顶部的条形区域，用于输入或编辑单元

22、格或图表中的值或公式。编辑栏中显示了存储于活动单元格中的常量值或公式。）”中，键入对公式的更改。返回页首调整多个计算项或公式的计算顺序单击数据透视表。这将显示“数据透视表工具”，上面添加了“选项”和“设计”选项卡。在“选项”选项卡上的“计算”组中，单击“字段、项和集”，然后单击“求解顺序”。单击某个公式，然后单击“上移”或者“下移”。继续上一步的操作，直到达到所需的顺序。返回页首查看数据透视表中使用的所有公式您可以显示在当前数据透视表中使用的所有公式的列表。单击数据透视表。这将显示“数据透视表工具”，上面添加了“选项”和“设计”选项卡。在“选项”选项卡上的“计算”组中，单击“字段、项和集”，然

23、后单击“列出公式”。返回页首编辑数据透视表公式在编辑公式之前，确定该公式是在计算字段中还是计算项中。如果公式是在计算项中，也请确定它是否是计算项唯一使用的公式。对于计算项，您可以为计算项的特定单元格编辑独立的公式。例如，如果名称为 OrangeCalc 的计算项有一公式 =Oranges * .25 作用于所有月份，则可将六月、七月和八月的公式更改为 =Oranges *.5。确定公式是在计算字段中还是计算项中单击数据透视表。这将显示“数据透视表工具”，上面添加了“选项”和“设计”选项卡。在“选项”选项卡上的“计算”组中，单击“字段、项和集”，然后单击“列出公式”。在公式列表中，在“计算字段”

24、或“计算项”下查找希望更改的公式。如果一个计算项有多个公式，在该项 （项:数据透视表和数据透视图中字段的子分类。例如，“月份”字段可能有“一月”、“二月”等项。）创建时输入的默认公式在 B 列拥有计算项名称。对于计算项的附加公式，B 列包含计算项名称和相交项的名称。例如，名为“我的项”的计算项有默认的公式以及另一个标识为“我的项的一月份销售额”的公式。在数据透视表中，这一公式在“我的项”行和“一月”列的“销售额”单元格中。使用下列编辑方法之一继续操作。编辑计算字段公式单击数据透视表。这将显示“数据透视表工具”，上面添加了“选项”和“设计”选项卡。在“选项”选项卡上的“计算”组中，单击“字段、项

25、和集”，然后单击“计算字段”。在“名称”框中，选择要更改公式的计算字段。在“公式”框中，编辑公式。单击“修改”。编辑计算项的单个公式单击含有计算项的字段。在“选项”选项卡上的“计算”组中，单击“字段、项和集”，然后单击“计算项”。在“名称”框中，选择计算项。在“公式”框中，编辑公式。单击“修改”。为计算项的特定单元格编辑独立的公式单击需更改公式的单元格。若要更改几个单元格中的公式，请按住 Ctrl 单击其他单元格。在“编辑栏 （编辑栏:位于 Excel 窗口顶部的条形区域，用于输入或编辑单元格或图表中的值或公式。编辑栏中显示了存储于活动单元格中的常量值或公式。）”中，键入对公式的更改。提示如果

26、有多个计算项或公式，则可以调整计算顺序。有关详细信息，请参阅调整多个计算项或公式的计算顺序。返回页首删除数据透视表公式注释删除数据透视表公式会将其永久删除。如果您不想永久地删除公式，则可以改为隐藏字段或项，方法为将该公式拖出数据透视表。确定公式是在计算字段还是计算项中。计算字段显示在“数据透视表字段列表”中。而计算项则在其他字段中以项 （项:数据透视表和数据透视图中字段的子分类。例如，“月份”字段可能有“一月”、“二月”等项。）的形式出现。执行下列操作之一：若要删除某个计算字段，请单击数据透视表中的任意位置。若要删除某个计算项，请在数据透视表中，单击包含要删除的项的字段。这将显示“数据透视表工具”，上面添加了“选项”和“设计”选项卡。在“选项”选项卡上的“计算”组中，单击“字段、项和集”，然后单击“计算字段”或“计算项”。在“名称”框中，选择要删除的字段或项。单击“删除”。