数据透视表转自OFFICE网站.docx
《数据透视表转自OFFICE网站.docx》由会员分享，可在线阅读，更多相关《数据透视表转自OFFICE网站.docx（13页珍藏版）》请在冰豆网上搜索。
数据透视表转自OFFICE网站
数据透视表
全部显示
全部隐藏
在数据透视表（数据透视表:
一种交互的、交叉制表的Excel报表，用于对多种来源（包括Excel的外部数据）的数据（如数据库记录）进行汇总和分析。
）中，您可以使用值字段（字段:
在数据透视表或数据透视图中，来源于源数据中字段的一类数据。
数据透视表具有行字段、列字段、页字段和数据字段。
数据透视图具有系列字段、分类字段、页字段和数据字段。
）中的汇总函数（汇总函数:
是一种计算类型，用于在数据透视表或合并计算表中合并源数据，或在列表或数据库中插入自动分类汇总。
汇总函数的例子包括Sum、Count和Average。
）来合并基础源数据（源数据:
用于创建数据透视表或数据透视图的数据清单或表。
源数据可以来自Excel数据清单或区域、外部数据库或多维数据集，或者另一张数据透视表。
）中的值。
如果汇总函数和自定义计算没有提供所需的结果，则可在计算字段（计算字段:
数据透视表或数据透视图中的字段，该字段使用用户创建的公式。
计算字段可使用数据透视表或数据透视图中其他字段中的内容执行计算。
）和计算项（计算项:
数据透视表字段或数据透视图字段中的项，该项使用用户创建的公式。
计算项使用数据透视表或数据透视图中相同字段的其他项的内容进行计算。
）中创建您自己的公式（公式:
单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。
公式总是以等号（=）开始。
）。
例如，可以为销售佣金添加一个带公式的计算项，这对于各个地区可能有所不同。
然后，数据透视表将自动在分类汇总和总计中包含佣金。
您要做什么？
了解有关计算数据透视表中的值的更多信息
可用的计算方法
源数据的类型如何影响计算
在数据透视表中使用公式
在数据透视图中使用公式
在数据透视表中创建公式
添加计算字段
向字段中添加计算项
按单元格逐个为计算项输入不同的公式
调整多个计算项或公式的计算顺序
查看数据透视表中使用的所有公式
编辑数据透视表公式
确定公式是在计算字段中还是计算项中
编辑计算字段公式
编辑计算项的单个公式
为计算项的特定单元格编辑独立的公式
删除数据透视表公式
了解有关计算数据透视表中的值的更多信息
数据透视表提供了计算数据的方式。
了解可用的计算方法，了解源数据的类型如何影响计算，并了解如何在数据透视表和数据透视图中使用公式。
可用的计算方法
若要计算数据透视表中的值，您可以使用下列任一或所有计算方法类型：
值字段中的汇总函数 值区域中的数据是数据透视表中基础源数据的汇总。
例如，下列源数据：
生成下列数据透视表和数据透视图。
如果基于数据透视表中的数据创建数据透视图，则该数据透视图中的值将影响其关联数据透视表（相关联的数据透视表:
为数据透视图提供源数据的数据透视表。
在新建数据透视图时，将自动创建数据透视表。
如果更改其中一个报表的布局，另外一个报表也随之更改。
）中的计算。
在数据透视表中，“月份”列字段提供了“三月”和“四月”两项（项:
数据透视表和数据透视图中字段的子分类。
例如，“月份”字段可能有“一月”、“二月”等项。
）。
“地区”行字段提供了“北部”、“南部”、“东部”和“西部”四项。
“四月”所在列和“北部”所在行交汇处的值就是“月份”值为“四月”而“地区”值为“北部”的源数据中记录的总销售额。
在数据透视图中，“地区”字段可能是分类字段，它将“北部”、“南部”、“东部”和“西部”显示为分类。
而“月份”字段是系列字段，它将“三月”、“四月”和“五月”这几项显示为图例中表示的系列。
称为“销售总和”的“值”字段包含代表每个地区每月总收入的数据标记（数据标记:
图表中的条形、面积、圆点、扇面或其他符号，代表源于数据表单元格的单个数据点或值。
图表中的相关数据标记构成了数据系列。
）。
例如，相应的数据标记将在垂直（值）轴（坐标轴:
界定图表绘图区的线条，用作度量的参照框架。
y轴通常为垂直坐标轴并包含数据。
x轴通常为水平轴并包含分类。
）上标示出“北部”地区“四月”的总销售额。
若要计算值字段，则下列汇总函数可用于除联机分析处理（OLAP）（OLAP:
为查询和报表（而不是处理事务）而进行了优化的数据库技术。
OLAP数据是按分级结构组织的，它存储在多维数据集而不是表中。
）源数据之外的所有源数据类型。
函数
功能
合计
求值的总和。
这是数字数据的默认函数。
计数
求数据值的个数。
Count汇总函数的作用与COUNTA函数相同。
Count是非数字数据的默认函数。
平均值
求数值平均值。
Max
求最大值。
Min
求最小值。
产品
求数值的乘积。
CountNums
求数字数据值的个数。
CountNums汇总函数的作用与COUNT函数相同。
StDev
估算总体的标准偏差，样本为总体的子集。
StDevp
计算总体的标准偏差。
汇总的所有数据为总体。
Var
估计总体方差，样本为总体的子集。
Varp
计算总体的方差。
汇总的所有数据为总体。
自定义计算 自定义计算（自定义计算:
用数据透视表的数据区域中的其他单元格值对数据区域中的值进行汇总的方法。
使用数据字段的“数据透视表字段”对话中的“数据显示方式”列表可创建自定义计算。
）显示根据数据区域中的其他项或单元格得到的数值。
例如，可将“销售总和”数据字段中的数值作为“三月”销售额的百分数显示，或者作为“月份”字段中的项的运行汇总显示。
下面的函数可用于在值字段中进行自定义计算。
函数
结果
无计算
显示在该字段中输入的值。
总计百分比
显示的值为报表中所有值或所有数据点的总计的百分比。
列汇总百分比
显示每一列或每个系列的所有值为该列或该系列总计的百分比。
行汇总百分比
显示每一行或每个类别的值相对于该行或该类别总计的百分比。
百分比
显示的值为“基本字段”中“基本项”值的百分比。
父行汇总百分比
按下式计算值：
（该项的值）/（行上父项的值）
父列汇总百分比
按下式计算值：
（该项的值）/（列上父项的值）
父级汇总的百分比
按下式计算值：
（该项的值）/（所选“基本字段”中父项的值）
差异
显示的值为与“基本字段”中“基本项”值的差。
差异百分比
显示的值为与“基本字段”中“基本项”值的百分比差值。
按某一字段汇总
将“基本字段”中连续项的值显示为累计总和。
按某一字段汇总的百分比
计算“基本字段”中连续项的值，该值以百分比显示累计总和。
按升序排名
显示某一特定字段中所选值的排位，其中将该字段中的最小项列为1，而每个较大的值将具有较高的排位值。
按降序排名
显示某一特定字段中所选值的排位，其中将该字段中的最大项列为1，而每个较小的值将具有较高的排位值。
索引
按下式计算值：
（（单元格中值）x（总计））/（（行总计）x（列总计））
公式 如果汇总函数和自定义计算没有提供所需的结果，则可在计算字段和计算项中创建您自己的公式。
例如，可以为销售佣金添加一个带公式的计算项，这对于各个地区可能有所不同。
然后，该报表将自动在分类汇总和总计中包含佣金。
返回页首
源数据的类型如何影响计算
报表中可用的计算和选项取决于源数据是来自OLAP数据库还是非OLAP数据源。
基于OLAP源数据的计算 对于通过OLAP多维数据集（多维数据集:
一种OLAP数据结构。
多维数据集包含维度，如“国家/地区/省（或市/自治区）/市（或县）”，还包括数据字段，如“销售额”。
维度将各种类型的数据组织到带有明细数据级别的分层结构中，而数据字段度量数量。
）创建的报表而言，OLAP服务器会在Excel显示结果之前预先对汇总值进行计算。
不能更改这些预先计算的值在数据透视表内部的计算方式。
例如，您不能更改用来计算数据字段或分类汇总的汇总函数，也不能添加计算字段或计算项。
此外，如果OLAP服务器可提供计算字段（称作计算成员），则将在“数据透视表字段列表”中看到这些字段。
还将看到由宏（该宏是用VisualBasicforApplications（VBA）（VisualBasicforApplications（VBA）:
MicrosoftVisualBasic的宏语言版本，用于编写基于MicrosoftWindows的应用程序，内置于多个Microsoft程序中。
）编写而成的，并存储在工作簿中）创建的任何计算字段和计算项，但是不能更改这些字段或项。
如果需要其他计算类型，请与OLAP数据库管理员联系。
对于OLAP源数据而言，当计算分类汇总和总计时，可以包含或排除隐藏项的值。
基于非OLAP源数据的计算 在基于其他类型的外部数据或工作表数据的数据透视表中，Excel使用Sum汇总函数计算含有数字数据的值字段，使用Count汇总函数计算含有文本的数据字段。
您可以选择其他的汇总函数来进一步分析和自定义数据，如Average、Max或Min。
通过在字段中创建计算字段或计算项，还可以创建使用报表元素或其他工作表数据的您自己的公式。
返回页首
在数据透视表中使用公式
只能在基于非OLAP源数据的报表中创建公式。
您不能在基于OLAP数据库的报表中使用公式。
当您在数据透视表中使用公式时，应了解以下公式语法规则和公式行为：
数据透视表公式元素 在为计算字段和计算项创建的公式中，您可以像在其他工作表公式中一样使用运算符（运算符:
一个标记或符号，指定表达式内执行的计算的类型。
有数学、比较、逻辑和引用运算符等。
）和表达式。
您可以使用常量（常量:
不进行计算的值，因此也不会发生变化。
例如，数字210以及文本“每季度收入”都是常量。
表达式以及表达式产生的值都不是常量。
）并引用报表中的数据，但不能使用单元格引用或定义的名称（名称:
代表单元格、单元格区域、公式或常量值的单词或字符串。
名称更易于理解，例如，“产品”可以引用难于理解的区域“Sales!
C20:
C30”。
）。
您不能使用需要将单元格引用或定义的名称作为参数的工作表函数，也不能使用数组函数。
字段名称和项名称 Excel使用字段名称和项名称来识别公式中的报表元素。
在下面的示例中，区域C3:
C9中的数据使用的字段名称为“奶制品”。
在“种类”字段中，可以使用公式（如=奶制品*115%）根据“奶制品”的销售情况估计新产品的销售情况。
注释 在数据透视图中，字段名称显示在“数据透视表字段列表”中，而项名称则显示在每个字段的下拉列表中。
不要将这些名称与图表提示中的信息相混淆，图表提示中的信息只用于反映系列和数据点的名称。
公式对求和汇总进行操作，而不是针对单个记录 计算字段中公式的操作对象是公式中任意字段的基础数据的和。
例如，计算字段公式=销售额*1.2是指将每类产品和地区的销售额的和乘以1.2；并不是将每笔独立的销售额都乘以1.2，然后再将所得的乘积结果相加。
计算项中公式的操作对象则是单个记录。
例如，计算项公式=奶制品*115%是指将“奶制品”的每笔销售额都乘以115%，然后再将所得乘积在“值”区域中相加。
名称中的空格、数字和符号 在包含多个字段的名称中，字段可按任意顺序排列。
在上面的示例中，单元格C6:
D6可以是“四月北部”或“北部四月”。
使用单引号将包含多个词、数字或符号的名称括起来。
汇总 公式不能引用汇总（例如，本示例中的“三月份汇总”、“四月份汇总”和“总计”）。
项引用中的字段名称 可以在对项的引用中包含字段名称。
项的名称必须用中括号括起来， 例如“地区[北部]”。
当报表中两个不同字段中的两个项有同样的名称时，使用这种格式可以避免#NAME?
错误。
例如，如果报表中的“种类”字段中有一个叫“肉类”的项，“分类”字段中也有一个叫“肉类”的项，用“种类[肉类]”和“分类[肉类]”的形式引用项可以防止#NAME?
错误。
按位置引用项 在报表中，可以根据当前排序和项显示的位置来引用项。
“种类[1]”是“奶制品”，而“种类[2]”是“海产品”。
当项的位置发生变化，或者显示或隐藏不同的项时，用这种方式引用的项可随之更改。
隐藏项并不归入此索引。
可以用相对位置来引用项。
该位置是由与其相关的包含公式的计算项决定的。
如果“南部”是当前地区，则“地区[-1]”就是“北部”；如果“北部”是当前地区，则“地区[+1]”就是“南部”。
例如，计算项可使用公式=地区[-1]*3%。
如果提供的位置在字段的第一项之前或最后一项之后，则公式将产生#REF!
错误。
返回页首
在数据透视图中使用公式
若要在数据透视图中使用公式，您可在关联的数据透视表中创建公式（在该数据透视表中可查看构成数据的单个值），然后以图形方式在数据透视图中查看结果。
例如，下面的数据透视图显示了每个地区各个推销员的销售额：
若要了解销售额增长百分之十后的情况，您可以在使用公式（如=销售额*110%）的关联数据透视表中创建计算字段。
结果将立即显示在数据透视图中，如下列图表所示：
若要查看北部地区的销售额减去百分之八的运输费后所得到的独立数据标记，可在“地区”字段中使用公式（如=北部–（北部*8%））创建计算项。
生成的图表如下所示：
但是，在“销售人员”字段中创建的计算项将显示为图例中所代表的系列，同时在图表中显示为每个分类中的数据点。
返回页首
在数据透视表中创建公式
重要提示 不能在连接到联机分析处理（OLAP）（OLAP:
为查询和报表（而不是处理事务）而进行了优化的数据库技术。
OLAP数据是按分级结构组织的，它存储在多维数据集而不是表中。
）数据源的数据透视表中创建公式。
开始前，确定在字段中是需要计算字段还是需要计算项。
当您想在公式中使用其他字段的数据时，请使用计算字段。
当您想在公式中使用字段中的一个或多个特定项（项:
数据透视表和数据透视图中字段的子分类。
例如，“月份”字段可能有“一月”、“二月”等项。
）的数据时，请使用计算项。
对于计算项，可以按单元格逐个输入不同的公式。
例如，如果名称为OrangeCounty的计算项有一公式=Oranges*.25作用于所有月份，则可将六月、七月和八月的公式更改为=Oranges*.5。
如果有多个计算项或公式，则可以调整计算顺序。
添加计算字段
单击数据透视表。
这将显示“数据透视表工具”，上面添加了“选项”和“设计”选项卡。
在“选项”选项卡上的“计算”组中，单击“字段、项和集”，然后单击“计算字段”。
在“名称”框中，键入字段的名称。
在“公式”框中，输入字段的公式。
若要在公式中使用来自另一字段的数据，请在“字段”框中单击该字段，然后单击“插入字段”。
例如，若要计算“销售额”字段中每个值15%的佣金，可输入“=销售额*15%”。
单击“添加”。
返回页首
向字段中添加计算项
单击数据透视表。
这将显示“数据透视表工具”，上面添加了“选项”和“设计”选项卡。
如果字段中的项已经分组，请在“选项”选项卡上的“组合”组中，单击“取消组合”。
单击要添加计算项的字段。
在“选项”选项卡上的“计算”组中，单击“字段、项和集”，然后单击“计算项”。
在“名称”框中，键入计算项的名称。
在“公式”框中，输入项的公式。
若要在公式中使用项的数据，请单击“项”下拉列表中的项，然后单击“插入项”（该项必须和计算项来自同一字段）。
单击“添加”。
返回页首
按单元格逐个为计算项输入不同的公式
单击需更改公式的单元格。
若要更改几个单元格中的公式，请按住Ctrl单击其他单元格。
在“编辑栏（编辑栏:
位于Excel窗口顶部的条形区域，用于输入或编辑单元格或图表中的值或公式。
编辑栏中显示了存储于活动单元格中的常量值或公式。
）”中，键入对公式的更改。
返回页首
调整多个计算项或公式的计算顺序
单击数据透视表。
这将显示“数据透视表工具”，上面添加了“选项”和“设计”选项卡。
在“选项”选项卡上的“计算”组中，单击“字段、项和集”，然后单击“求解顺序”。
单击某个公式，然后单击“上移”或者“下移”。
继续上一步的操作，直到达到所需的顺序。
返回页首
查看数据透视表中使用的所有公式
您可以显示在当前数据透视表中使用的所有公式的列表。
单击数据透视表。
这将显示“数据透视表工具”，上面添加了“选项”和“设计”选项卡。
在“选项”选项卡上的“计算”组中，单击“字段、项和集”，然后单击“列出公式”。
返回页首
编辑数据透视表公式
在编辑公式之前，确定该公式是在计算字段中还是计算项中。
如果公式是在计算项中，也请确定它是否是计算项唯一使用的公式。
对于计算项，您可以为计算项的特定单元格编辑独立的公式。
例如，如果名称为OrangeCalc的计算项有一公式=Oranges*.25作用于所有月份，则可将六月、七月和八月的公式更改为=Oranges*.5。
确定公式是在计算字段中还是计算项中
单击数据透视表。
这将显示“数据透视表工具”，上面添加了“选项”和“设计”选项卡。
在“选项”选项卡上的“计算”组中，单击“字段、项和集”，然后单击“列出公式”。
在公式列表中，在“计算字段”或“计算项”下查找希望更改的公式。
如果一个计算项有多个公式，在该项（项:
数据透视表和数据透视图中字段的子分类。
例如，“月份”字段可能有“一月”、“二月”等项。
）创建时输入的默认公式在B列拥有计算项名称。
对于计算项的附加公式，B列包含计算项名称和相交项的名称。
例如，名为“我的项”的计算项有默认的公式以及另一个标识为“我的项的一月份销售额”的公式。
在数据透视表中，这一公式在“我的项”行和“一月”列的“销售额”单元格中。
使用下列编辑方法之一继续操作。
编辑计算字段公式
单击数据透视表。
这将显示“数据透视表工具”，上面添加了“选项”和“设计”选项卡。
在“选项”选项卡上的“计算”组中，单击“字段、项和集”，然后单击“计算字段”。
在“名称”框中，选择要更改公式的计算字段。
在“公式”框中，编辑公式。
单击“修改”。
编辑计算项的单个公式
单击含有计算项的字段。
在“选项”选项卡上的“计算”组中，单击“字段、项和集”，然后单击“计算项”。
在“名称”框中，选择计算项。
在“公式”框中，编辑公式。
单击“修改”。
为计算项的特定单元格编辑独立的公式
单击需更改公式的单元格。
若要更改几个单元格中的公式，请按住Ctrl单击其他单元格。
在“编辑栏（编辑栏:
位于Excel窗口顶部的条形区域，用于输入或编辑单元格或图表中的值或公式。
编辑栏中显示了存储于活动单元格中的常量值或公式。
）”中，键入对公式的更改。
提示 如果有多个计算项或公式，则可以调整计算顺序。
有关详细信息，请参阅调整多个计算项或公式的计算顺序。
返回页首
删除数据透视表公式
注释 删除数据透视表公式会将其永久删除。
如果您不想永久地删除公式，则可以改为隐藏字段或项，方法为将该公式拖出数据透视表。
确定公式是在计算字段还是计算项中。
计算字段显示在“数据透视表字段列表”中。
而计算项则在其他字段中以项（项:
数据透视表和数据透视图中字段的子分类。
例如，“月份”字段可能有“一月”、“二月”等项。
）的形式出现。
执行下列操作之一：
若要删除某个计算字段，请单击数据透视表中的任意位置。
若要删除某个计算项，请在数据透视表中，单击包含要删除的项的字段。
这将显示“数据透视表工具”，上面添加了“选项”和“设计”选项卡。
在“选项”选项卡上的“计算”组中，单击“字段、项和集”，然后单击“计算字段”或“计算项”。
在“名称”框中，选择要删除的字段或项。
单击“删除”。
升级会员 