|
数据透视表和数据透视图 （数据透视图：提供交互式数据分析的图表，与数据透视表类似。可以更改数据的视图，查看不同级别的明细数据，或通过拖动字段和显示或隐藏字段中的项来重新组织图表的布局。）提供了多种计算方式。数据字段 （数据字段：源数据清单、表或数据库中的字段，其中包含在数据透视表或数据透视图中汇总的数据。数据字段通常包含数字型数据，如统计或销售数量。）用汇总函数 （汇总函数：是一种计算类型，用于在数据透视表或合并计算表中合并源数据，或在列表或数据库中插入自动分类汇总。汇总函数的例子包括 Sum、Count 和 Average。）来合并基本源数据 （源数据：用于创建数据透视表或数据透视图的数据清单或表。源数据可以来自 Excel 数据清单或区域、外部数据库或多维数据集，或者另一张数据透视表。）中的数值。还可用自定义计算 （自定义计算：用数据透视表的数据区域中的其他单元格值对数据区域中的值进行汇总的方法。使用数据字段的“数据透视表字段”对话中的“数据显示方式”列表可创建自定义计算。）比较数据值或添加使用报表元素或其他工作表数据的公式。
数据透视表和数据透视图如何汇总数据 | 地区 | 月份 | 销售额 | | 南部 | 五月 |
8,677.00 | | 南部 | 四月 |
450.00 | | 北部 | 四月 |
1,500.00 | | 南部 | 五月 |
3,802.00 | | 东部 | 五月 |
2,741.00 | | 北部 | 四月 |
9,291.00 | | 西部 | 五月 |
5,477.00 | | 东部 | 五月 |
5,416.00 | | 东部 | 四月 |
9,136.00 |
源数据
数据区域 （数据区域：数据透视表中包含汇总数据的部分。数据区域中每个单元格的值代表源记录或行的汇总数据。）中的数值可对报表中的基本源数据进行汇总。
| 求和项:销售额 | 月份 | | | | 地区 |
四月
|
五月
|
总计
| | 北部 |
10,791.00 | |
10,791.00 | | 东部 |
9,136.00 |
8,157.00 |
17,293.00 | | 南部 |
450.00 |
12,479.00 |
12,929.00 | | 西部 | |
5,477.00 |
5,477.00 | | 总计 |
20,377.00 |
26,113.00 |
46,490.00 |
根据上面的源数据创建的数据透视表
“月份”列字段 （列字段：数据透视表中按列显示的字段。与列字段相关的项显示为列标志。）提供了“三月”和“四月”两个项 （项：数据透视表和数据透视图中字段的子分类。例如，“月份”字段可能有“一月”、“二月”等项。）。“地区”行字段 （行字段：数据透视表中按行显示的字段。与行字段相关的项显示为行标志。）提供了“东部”、“南部”、“西部”和“北部”四个项。“四月”所在列和“北部”所在行交汇处的数值就是“月份”值为“四月”而“地区”值为“北部”的源数据中记录的总销售额。
根据上面的数据透视表示例创建的数据透视图
在数据透视图报表中，“地区”字段是分类字段 （数据透视图分类字段：在数据透视图中，指定为分类方向的字段。在图表中，分类通常出现在图表的 x 轴或水平轴上。），它将“北部”、“南部”、“东部”和“西部”项目显示为分类。而“月份”字段是系列字段 （数据透视图系列字段：在数据透视图中，指定为系列方向的字段。在图表中，系列由图例表示。），它将“三月”、“四月”和“五月”显示为图例中的系列。“求和项：销售额”字段包含代表每个地区每月总收入的数据标志 （数据标记：图表中的条形、面积、圆点、扇面或其他符号，代表源于数据表单元格的单个数据点或值。图表中的相关数据标记构成了数据系列。）。例如，相应的数据标志将在数值轴 （坐标轴：界定图表绘图区的线条，用作度量的参照框架。y 轴通常为垂直坐标轴并包含数据。x 轴通常为水平轴并包含分类。）上标示出北部地区四月份的总销售额。
数据透视图中的值和计算将影响其相关联的数据透视表 （相关联的数据透视表：为数据透视图提供源数据的数据透视表。在新建数据透视图时，将自动创建数据透视表。如果更改其中一个报表的布局，另外一个报表也随之更改。）中的值和计算，反之亦然。
自定义计算 自定义计算显示根据数据区域的其他项或单元格得到的数值。例如，可将“求和项：销售额”数据字段中的数值作为“三月”销售额的百分数显示，或者作为“月份”字段中的项的运行汇总显示。
公式 如果汇总函数和自定义计算没有提供所需的结果，则可在计算字段 （计算字段：数据透视表或数据透视图中的字段，该字段使用用户创建的公式。计算字段可使用数据透视表或数据透视图中其他字段中的内容执行计算。）和计算项 （计算项：数据透视表字段或数据透视图字段中的项，该项使用用户创建的公式。计算项使用数据透视表或数据透视图中相同字段的其他项的内容进行计算。）中创建所需公式。例如，可以对销售年金添加一个带公式的计算项。然后该报表将自动在分类汇总和总计中包含年金值。
源数据类型对计算的影响方式
报表中可用的计算和选项依赖于源数据是来自 OLAP （OLAP：为查询和报表（而不是处理事务）而进行了优化的数据库技术。OLAP 数据是按分级结构组织的，它存储在多维数据集而不是表中。） 数据库还是其他类型的数据库。
OLAP 源数据 对于通过 OLAP 多维数据集 （多维数据集：一种 OLAP 数据结构。多维数据集包含维度，如“国家/地区）/省（或市/自治区）/市（或县）”，还包括数据字段，如“销售额”。维度将各种类型的数据组织到带有明细数据级别的分层结构中，而数据字段度量数量。）创建的报表而言，OLAP 服务器会在 Microsoft Excel 显示结果之前预先对汇总数值进行计算。因此，不能更改这些值在报表内部的计算方式。也不能更改用来计算数据字段或分类汇总的汇总函数，或添加计算字段 （计算字段：数据透视表或数据透视图中的字段，该字段使用用户创建的公式。计算字段可使用数据透视表或数据透视图中其他字段中的内容执行计算。）或计算项 （计算项：数据透视表字段或数据透视图字段中的项，该项使用用户创建的公式。计算项使用数据透视表或数据透视图中相同字段的其他项的内容进行计算。）。 如果 OLAP 服务器可提供计算字段（称作计算成员），则可在数据透视表的字段列表中查看这些字段。还可查看任何一个由宏 （宏：可用于自动执行任务的一项或一组操作。可用 Visual Basic for Applications 编程语言录制宏。）创建的计算字段和计算项（该宏在 Visual Basic for Applications （Visual Basic for Applications (VBA)：Microsoft Visual Basic 的宏语言版本，用于编写基于 Microsoft Windows 的应用程序，内置于多个 Microsoft 程序中。） 中编辑，并存储在工作簿中），但是不能更改这些字段或项。如果需要其他计算类型，请与 OLAP 数据库管理员联系。
源数据的其他类型 在根据其他类型的外部数据或工作表数据创建的报表中，Microsoft Excel 使用 Sum 汇总函数计算含有数字数据的数据字段，使用 Count 汇总函数计算含有文本的数据字段。您可以选择其他的汇总函数来进一步分析和自定义数据，如 Average、Max 或 Min 等。通过在字段中创建计算字段或计算项，还可以创建使用报表或其他工作表数据的自定义公式。
汇总中的隐藏项 对 OLAP 源数据而言，当计算分类汇总和总计时，可包含或排除隐藏项的数值。对于其他类型的源数据，在默认情况下，已排除了隐藏项的数值，但是可选择是否包括页字段 （页字段：在数据透视表或数据透视图中指定为页方向的字段。在页字段中，既可以显示所有项的汇总，也可以一次显示一个项，而筛选掉所有其他项的数据。）的隐藏项。
公式语法
只能在不是基于 OLAP （OLAP：为查询和报表（而不是处理事务）而进行了优化的数据库技术。OLAP 数据是按分级结构组织的，它存储在多维数据集而不是表中。） 源数据的报表中创建公式。
数据透视图可以使用公式，且其语法与数据透视表中相同。为了在数据透视图中获得最佳效果，请在与其相关联的数据透视表 （相关联的数据透视表：为数据透视图提供源数据的数据透视表。在新建数据透视图时，将自动创建数据透视表。如果更改其中一个报表的布局，另外一个报表也随之更改。）中创建并编辑公式（在该数据透视表中可查看构成数据的单个数值），然后在数据透视图中查看结果。
公式元素 像其他工作表公式一样，在为计算字段 （计算字段：数据透视表或数据透视图中的字段，该字段使用用户创建的公式。计算字段可使用数据透视表或数据透视图中其他字段中的内容执行计算。）和计算项 （计算项：数据透视表字段或数据透视图字段中的项，该项使用用户创建的公式。计算项使用数据透视表或数据透视图中相同字段的其他项的内容进行计算。）创建的公式中，也可以使用运算符 （运算符：一个标记或符号，指定表达式内执行的计算的类型。有数学、比较、逻辑和引用运算符等。）和表达式。还可以使用常量 （常量：不进行计算的值，因此也不会发生变化。例如，数字 210 以及文本“每季度收入”都是常量。表达式以及表达式产生的值都不是常量。）并引用报表中的数据，但不能使用单元格引用和定义的名称 （名称：代表单元格、单元格区域、公式或常量值的单词或字符串。名称更易于理解，例如，“产品”可以引用难于理解的区域“Sales!C20:C30”。），也不能使用以单元格引用或定义的名称为参数的工作表函数 （函数：函数是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。函数可以简化和缩短工作表中的公式，尤其在用公式执行很长或复杂的计算时。），还不能使用数组 （数组：用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。）函数。
报表中的名称 Microsoft Excel 可提供名称来识别公式中的报表元素。名称由字段 （字段：在数据透视表或数据透视图中，来源于源数据中字段的一类数据。数据透视表具有行字段、列字段、页字段和数据字段。数据透视图具有系列字段、分类字段、页字段和数据字段。）和项 （项：数据透视表和数据透视图中字段的子分类。例如，“月份”字段可能有“一月”、“二月”等项。）的名称组成。在下面的例子中，区域 C3:C9 中的数据被命名为“奶制品”。
| 求和项:销售额 | | 种类 | | | | 月份 | 地区 | 奶制品 | 肉类 | 总计 | | 三月 | 西部 | |
7,954.00 |
7,954.00 | | 三月 汇总 | |
7,954.00 |
7,954.00 | | 四月 | 北部 |
1,500.00 |
9,291.00 |
10,791.00 | | | 东部 |
9,136.00 | |
9,136.00 | | | 南部 | |
450.00 |
450.00 | | 四月 汇总 |
10,636.00 |
9,741.00 |
20,377.00 | | 五月 | 东部 |
8,157.00 | |
8,157.00 | | | 南部 |
8,677.00 |
3,802.00 |
12,479.00 | | | 西部 | |
7,954.00 |
7,954.00 | | 五月 汇总 |
16,834.00 |
11,756.00 |
28,590.00 | | 总计 | |
27,470.00 |
29,451.00 |
56,921.00 |
在数据透视图中，字段名称显示在字段按钮中，而项目名称则在每一字段的下拉列表中。不要将这些名称与图表提示中的信息相混淆，它们只用于反映系列和数据点的名称。
示例 名为“预测”的计算字段可用如下公式预测未来的订货情况：
=销售额 * 1.2
在“种类”字段中，可以使用如下公式根据“奶制品”的销售情况估计新产品的销售情况：
=奶制品 * 115%
公式对求和汇总进行操作，而不是针对单个记录 计算字段中公式的操作对象是公式中任意字段的源数据的和。例如，公式 =销售额 * 1.2 是指将每类产品和区域的销售额的和乘以 1.2，并不是将每笔独立的销售额都乘以 1.2，然后再将所得的乘积结果相加。然而，计算项中公式的操作对象则是独立的记录，例如，计算项公式 =奶制品 * 115% 将“奶制品”的每笔销售额都乘以 115%，然后再将所得乘积在数据区域 （数据区域：数据透视表中包含汇总数据的部分。数据区域中每个单元格的值代表源记录或行的汇总数据。）中相加。
名称中的空格、数字和符号 在包含多个字段的名称中，字段可按任何顺序排列。在上面的例子中，单元格区域 C6 6 可以是“北部四月”或“四月北部”。在包含多个词、数字或符号的名称中可使用单引号。
汇总 公式中不能引用汇总（如本例中的“三月份汇总”、“四月份汇总”和“总计”）。
数据项引用中的字段名称 可以在对数据项的引用中包含字段名称。数据项的名称必须用中括号括起来，例如“地区[北部]”。当报表中两个不同字段中的两个数据项有同样的名称时，使用这种格式可以避免 #NAME 错误。例如，如果报表中的“种类”字段中有一个叫“肉类”的数据项，“分类”字段中也有一个叫“肉类”的数据项，用“类别[肉类]”和“分类[肉类]”的形式引用数据项可以防止 #NAME 错误。
按位置引用数据项 在报表中，可以根据当前排序和数据项显示的位置来引用数据项。“种类[1]”是“奶制品”，而“种类[2]”是“海产品”。当数据项的位置发生变化，或者显示或隐藏不同的数据项时，用这种方式引用的数据项可随之更改。
可用相对位置来引用数据项。该位置是由与其相关的包含公式的计算项决定的。如果“南部”是当前地区，则“地区[-1]”就是“北部”；如果“北部”是当前地区，则“地区[+1]”就是“南部”。例如，计算项可使用公式 =地区[-1] * 3%。如果所给的位置在字段的第一项之前或最后一项之后，则公式将产生 #REF! 错误。
在计算数据项公式中，如果根据绝对或相对位置引用数据项，那么“数据透视表‘排序并列出前 10 个’”或“数据透视表字段高级选项”对话框中的“自动显示前 10 项”和“自动排序选项”下的任何选项都将被重置成“关闭”或“手动”，从而这些选项都将不可用。
使用数据透视图中的公式
在数据透视图中创建公式的方法和规则与在数据透视表中创建公式的方法相同。当您在数据透视图中创建计算字段 （计算字段：数据透视表或数据透视图中的字段，该字段使用用户创建的公式。计算字段可使用数据透视表或数据透视图中其他字段中的内容执行计算。）或计算项 （计算项：数据透视表字段或数据透视图字段中的项，该项使用用户创建的公式。计算项使用数据透视表或数据透视图中相同字段的其他项的内容进行计算。）时，这些计算会反映在相关联的数据透视表 （相关联的数据透视表：为数据透视图提供源数据的数据透视表。在新建数据透视图时，将自动创建数据透视表。如果更改其中一个报表的布局，另外一个报表也随之更改。）上，反之亦然。为了获得最佳效果，请在相关联的数据透视表（在其中可查看构成数据的单个数值）中为数据透视图创建公式，然后在数据透视图中查看图形化的结果。
例如，下面的数据透视图显示了每个地区各个推销员的销售额：
若要了解销售额增长百分之十后的情况，您可以创建使用下面公式的计算字段：
=销售额 * 110%
计算结果将反映在图表中：
若要查看北部地区的销售额减去百分之八的运输费后所得到的独立数据标志 （数据标记：图表中的条形、面积、圆点、扇面或其他符号，代表源于数据表单元格的单个数据点或值。图表中的相关数据标记构成了数据系列。），可在“地区”字段中使用以下公式创建一个计算项：
=北部 – (北部 * 8%)
结果如下所示：
但是，在“销售人员”字段中所创建的计算项将显示为图例中所代表的数据系列，同时在图表中显示为每个分类中的数据点。
[此贴子已经被作者于2007-12-19 16:46:18编辑过] |
提升卡
喧嚣卡
千斤顶
显身卡
