Calc:函数GETPIVOTDATA

出自OOo 中文指南手册翻译

目录 1 难点 Difficulty 2 语法 Syntax 2.1 第一种语法形式 First syntax variation 2.2 第二种语法形式 Second syntax variation 3 进度表 4 相关链接

GETPIVOTDATA函数可以用在Calc的公式里。如果想在电子表格里的某处重复使用数据助理的结果会用到该函数。 The function GETPIVOTDATA can be used with formulas in Calc. Use this function if you want to reuse the results from the DataPilot somewhere else in your spreadsheet.

[编辑] 难点 Difficulty

通常通过输入包含一个值的单元格地址来创建对该值的引用。比如，公式=C6*2创建对C6单元格的引用并返回双倍值。 Normally you create a reference to a value by entering the address of the cell that contains the value. For example, the formula =C6*2 creates a reference to cell C6 and returns the doubled value.

如果该单元格位于数据助理生成的结果区域，那么他包含通过引用行和列字段的特定分类计算得到的结果。比如，C6单元格包含销售员汉斯销售书籍的销售额总和。C12单元格里的公式使用该数值。 If this cell is located in the results area of the DataPilot, it contains the result that was calculated by referencing specific categories of the row and column fields. For example, the cell C6 contains the sum of the sales values of the employee Hans in the category Sailing. The formula in the cell C12 uses this value.

如果基础数据或数据助理的版式变了，那么必须将出现在不同单元格里汉斯的销售额也考虑在内。你的公式仍然引用C6单元格的话就使用了一个错误的数值。真正想要引用的数值可以在不同的位置找到。 If the underlying data or the layout of the DataPilot changes, then you must take into account that the sales value for Hans might appear in a different cell. Your formula still references the cell C6 and therefore uses a wrong value. The value that you really want to use can be found in a different location.

GETPIVOTDATA函数允许在数据助理内通过为该值使用特定识别分类来引用该值。 The function GETPIVOTDATA allows you to have a reference to a value inside the DataPilot by using the specific identifying categories for this value.

[编辑] 语法 Syntax

语法有两种形式： The syntax has two variations:

GETPIVOTDATA(target field, DataPilot; [ Field name / Element; ... ])

GETPIVOTDATA(DataPilot; specification)

[编辑] 第一种语法形式 First syntax variation

必须给出target field指定数据助理的哪个数据字段在函数中被使用。如果数据助理仅有一个数据字段，该项被忽略，但无论如何也必须输入他。 Giving the target field is necessary to specify which data field of the DataPilot is used within the function. If your DataPilot has only one data field, this entry is ignored, but you have to enter it anyway.

如果数据助理有超过一个的数据字段，那么必须输入基础数据源里的字段名（例如销售额）或数据字段自己的字段名（例如求和 - 销售额）。 If your DataPilot has more than one data field, then you have to enter the field name from the underlying data source (for example “sales value”) or the field name of the data field itself (for example “sum – sales value”).

DataPilot参数指定要使用的数据助理。文档中包含多于一个的数据助理是可能的。在这里输入数据助理结果区域中的某一个单元格引用。总是使用数据助理左上角的单元格是个好主意，这样能确保即使数据助理的版式改变了该单元格也在其内。 The argument DataPilot specifies the DataPilot that you want to use. It is possible that your document contains more than one DataPilot. Enter here a cell reference that is inside the results area of your DataPilot. It might be a good idea to always use the upper left corner cell of your DataPilot, so you can be sure that the cell will always be within your DataPilot even if the layout changes.

例: GETPIVOTDATA("销售额";A1)

Example: GETPIVOTDATA("sales value";A1)

如果仅输入头两个参数，那么函数将返回数据助理的整个结果。 If you enter only the first two arguments, then the function returns the total result of the DataPilot.

可以添加更多成对出现的参数field name和element重新得到特定的部分和。在表65的例中，我们想得到汉斯销售书籍的部分和，C12单元格里的公式将如下所示： You can add more arguments as pairs with field name and element to retrieve specific partial sums. In the example in Figure 65, where we want to get the partial sum of Hans for sailing, the formula in cell C12 would look like this:

=GETPIVOTDATA("销售额";A1;"销售员";"汉斯";"分类";"书籍")

=GETPIVOTDATA("sales value";A1;"employee";"Hans";"category";"sailing")

[编辑] 第二种语法形式 Second syntax variation

DataPilot参数以另一种语法形式中的同样方式给出。 The argument DataPilot has to be given in the same way as for the other syntax variation.

对于specifications，输入想从数据助理得到的值——以空格分隔的列表。如果有多于一个的数据字段，列表就必须包含数据字段的名字，否则不需要。要选择特定的部分结果，以 Field name[element]的形式添加更多的项目。 For the specifications, enter a list separated by spaces to specify the value you want from the DataPilot. This list must contain the name of the data field, if there is more than one data field, otherwise it is not required. To select a specific partial result, add more entries in the form of Field name[element].

在图65的例中，我们想得到汉斯销售书籍的部分和，C12单元格里的公式将如下所示： In the example in Figure 65, where we want to get the partial sum of Hans for Sailing, the formula in cell C12 would look like this:

=GETPIVOTDATA(A1;"销售额 销售员[汉斯] 分类[书籍]")

=GETPIVOTDATA(A1;"sales value employee[Hans] category[sailing]")

[编辑] 进度表

Calc:函数GETPIVOTDATA 中文化进度

翻译

0

校对

0

插图

/2

上次编辑

20100707061941

编者

Yookee

[编辑] 相关链接

本文原文