Calc:DataPilot对话框
出自OOo 中文指南手册翻译
- 使用DataPilot
- 例：销售量概览
- 例：时间记录
- 例：频率分布
- DataPilot函数
- 数据源
- DataPilot对话框
- 布局和结果分组
- 排序、深究、过滤、刷新
- 多个数据字段
- 函数GETPIVOTDATA
目录 |
数据助理功能主要在两个地方操作：第一个是数据助理对话框，第二个是对电子表格中的结果进行操作。这部分将详细描述对话框。
The function of the DataPilot is mainly managed in two places: first in the dialog of the DataPilot and second through manipulations of the result in the spreadsheet. This section describes the dialog in detail.
[编辑] 基本布局
数据助理对话框有四个大的空白区域，用图表的形式来表示结果的布局。这些布局区域旁边是按钮状的字段，字段名称就是数据源里的名称。你可以使用鼠标拖动这些字段到空白区域进行布局。 In the DataPilot are four big white areas, that indicate schematically the layout of the result. Beside these fields are button-like fields with the names of the fields in your data source. You can choose your layout by moving these fields with the mouse into the white areas.
中间的 数据字段 区域必须至少包含一个字段，通常也就有一个字段，高级用户可以设置多个字段。数据字段使用了汇总功能，例如，如果拖动 销售sales 字段到这，那么显示的是 求和 – 销售sales。 The area Data Fields in the middle must contain at least one field. Normally it has exactly one field. Advanced users could use more than one field here. For the data field an aggregate function is used. For example, if you move the field sales into the area Data fields, it appears there as Sum – sales.
行字段 和 列字段 表示分析结果在行和列的方向如何进行分类。如果行或列字段里没有条目，那么相应的行或列里将不会分类汇总。行或列的分类汇总经常会有多个字段，这时字段的顺序决定了从总体到局部的分类汇总顺序。 Row Fields and Column Fields indicate in which groups the result will be sorted in the columns and rows. If there are no entries in the row or column fields, then there won’t be partial sums for the corresponding rows or columns. There are often more than one field at the same time used to get partial sums for rows or columns. The order of the fields gives you then the order of the sums from overall to specific.
例如，把 region 和 employee 都拖到行字段里，结果就是这样：在行的方向，汇总先按 region 分类，每个 region 再按 employee 分类。 If you drag for example region and employee into the area Row Fields, the result will be the following: in the vertical direction (row wise), the sum will be divided into the branches. Within the branches will be the listing for the different regions.
放在 页字段 区域的字段在结果中作为下拉列表显示在顶部。结果只依据你在下拉列表中选择的项进行分析。例如，如果你使用 employee 作为页字段，你就可以针对每个雇员employee 进行数据分析。 Fields that are placed into the area Page Fields appear in the result above as a drop down list. The summary in your result takes only that part of your base data into account that you’ve selected. For example, if you use the field Employee as a page field, you can filter the shown results for each employee.
要从空白布局区域删除字段，使用鼠标拖动区域边界外，这时光标变成叉形。另外，可以使用 删除 按钮。 To remove a field from the white layout area just move it with the mouse to the border and let it drop there. The cursor will change to a crossed symbol. As alternative you can use the button Remove.
[编辑] 更多选项
要显示更多选项，点击 更多。 To expand the DataPilot and show more options, click More.
将结果输出到
将结果输出到 定义结果输出的地方。如果不输入，数据助理将把结果放到数据列表之下。如果在右边的文本框中输入单元格引用地址，数据助理将在这个位置显示结果。实际上，使用 -新建工作表- 设置而不是 -未定义的- 更有用，Calc 会新建一个工作表显示结果。这更实用，因为它更清晰，并能确保没有数据区域会被覆盖。 <ref name="ftn1">这里的 -未定义的- 的翻译是错误的并会误导。即便 未定义，结果的输出位置也是确定的。其实缺省设置应该是 -新建工作表-，因为实际上大多数是这样使用的。</ref>
Results to defines where your result will be shown. If you do not enter anything, the DataPilot will put your result below the list, that contains your data. If you leave – undefined – and you enter a cell reference, then the DataPilot will show the results at this place. It's useful instead of using the setting – undefined – to use the setting - new sheet –. Calc adds in this case a new sheet. This is useful, because it is much cleaner and makes sure that no areas with data are overwritten.<ref name="ftn1">The word - undefined – used in this place is wrong and misleading. The output position is also exactly defined. It is somewhat annoying, that the default setting is not -new sheet -, which is in almost all cases the needed setting.</ref>
忽略空行
使用这个设置，源数据区域内的完全的空行将被数据助理忽略。之前的前提条件对数据列表要求没有空行，因此，这个设置很少用到。 By using this setting, completely empty rows in the source area will be ignored by the DataPilot. As mentioned in the requirements for the database, empty rows are against the rules for processing list-like data within a spreadsheet. Therefore, this setting is used on the rare occasions when the source data is not in the expected (recommended) form.
识别分类
如果基本数据不满足所建议的数据结构，这个选项也有效果。当列表中有缺失的条目，它就会起作用，如这个例子所示： This option also has an effect if the basic data does not meet the recommended data structure. It takes effect when there are missing entries in a list, as shown in this example:
这个例子中，识别分类 选项将确保第 3、4 行添加到 Apples 产品中，第 6 行添加到 Pears 中。没有识别分类，数据助理就显示成一个空白的分类。 The option Identify categories would make sure that in this case the lines 3 and 4 are added to the product Apples and the row 6 is added to Pears. Without category recognition, the DataPilot shows an empty category.
从纯逻辑角度考虑，没有识别分类的行为更正确。含有缺失条目的列表也更缺少用途，因为你不能使用其他功能（如排序或筛选）。 From a purely logical point of view, the behavior without category recognition is more correct. A list with the shown missing entries is also less useful, because you cannot use other functions (like sorting or filtering).
总列数 / 总行数
这个选项控制数据助理是否在额外一行中汇总每一列或者在最右边的一列中汇总每一行。某些情况下，这个汇总没有用处，例如条目是累积值或者比较值。 With this option you decide if the DataPilot will show an extra row with the sums of each column or if it adds on the very right a column with the sums of each row. In some cases, an added total sum is useless, for example if your entries are accumulated or in comparisons.
添加筛选器
这个选项控制数据助理是否隐藏结果上方的标记 筛选器 的单元格，该单元格是一个按钮，方便于添加筛选设置。 This option allows the blending or hiding of the cell labelled Filter above the DataPilot results. This cell is a convenient button for additional filtering options within the DataPilot.
显示明细数据
在数据助理分析结果的区域中，鼠标双击有特殊效果。一般单元格内的双击会进入编辑模式，但在数据助理中双击会显示相关的明细数据，通常会新建一个工作表去显示。禁止这个双击功能将恢复通常操作方式。 In the table area of the DataPilot result, a double-click has a special effect. Normally a double-click on any cell switches to the edit mode, but within the DataPilot a listing of the detailed data that give you the shown result will appear. By disabling this function the double-click will keep its usual function within a spreadsheet.
[编辑] 字段的更多设置
前面讨论的选项是数据助理常用设置，另外，可以针对已添加到布局区域内的字段进行设置。在数据助理对话框里点击布局区域内的字段，然后点击 选项 按钮，或者直接双击字段。 The discussed options are valid for the DataPilot in general. Additionally, you can change settings for every field, that you have added to the DataPilot layout. You can reach the dialog for the field settings either by clicking on the button Options in the DataPilot dialog box or by a double-click on the appropriate field.
数据字段、行或列字段、以及页字段有不同的设置。 There are differences between data fields, row or column fields, and page fields of the DataPilot.
[编辑] 数据字段
在数据字段对话框中，可以选择 求和 函数对源数据进行汇总。多数情况使用求和函数，但也有其他函数（如标准分布或计数函数）。例如，计数函数对于非数字数据就很有用。 In the preferences dialog of a data field you can select the Sum function for accumulating the values from your data source. In many cases you will need the sum function, but other functions (like standard distribution or a counting function) are also available. For example, the counting function can be useful for non-numerical data fields.
数据字段对话框 中点击 更多 查看更多选项。 On the Data Field dialog, click More to see more options.
在 显示值 部分，使用不同合计函数来选择更多的分析方法。依据 类型 中的设置，再选择 基本字段 和 基本项。 In the Displayed value section, you can choose more analyzing possibilities by using the aggregate function. Depending on the setting for Type, you have to choose a definition for Base field and Base element.
| 类型 | 基本字段 | 基本项 | 解释 |
|---|---|---|---|
| 普通 | — | — | 合计函数的简单用法（如，求和） |
| 差异 | 从数据助理的数据源字段中选择（如，雇员 employee） Selection of a field from the data source of the DataPilot (for example, employee) | 从已选的基本字段内元素选择（如，雇员 Brigitte） Selection of an element from the selected base field (for example, Brigitte) | 与基本项元素比较的差异（如，其他雇员的销售额Sales与 Brigitte的差异） Result as difference to the result of the base element (for example, Sales volume of the employees as difference of the sales volume of Brigitte) |
| 百分比 | 从数据助理的数据源字段中选择（如，雇员 employee） | 从已选的基本字段内元素选择（如，雇员 Brigitte） | 与基本项元素结果的比率（如，雇员的销售额Sales与 Brigitte的百分比） Result as a ratio based on the result of the base element (for example, Sales result of the employee relative to the sales result of Brigitte) |
| 差异百分比 % difference from | 从数据助理的数据源字段中选择（如，雇员 employee） | 从已选的基本字段内元素选择（如，雇员 Brigitte） | 与基本项元素结果的相对差异百分比（如，雇员的销售额Sales与 Brigitte的相对差异百分比） Result as relative difference to the result of the base element (for example, Sales volume of the employees as relative difference of the sales volume of Brigitte) |
| 按某一字段汇总 Running total in | 从数据助理的数据源字段中选择（如，日期date） Selection of a field from the data source of the DataPilot (for example, date) | — | 累积求和的结果（如，按月或日的销售额的累积求和） Result as a continuing sum (for example, Continuing sum of the sales volume for days or months) |
| 占同行数据总和的百分比 % of row | — | — | 与同行数据总和的百分比结果 Result as relative part of the result in the whole row (for example the row sum) |
| 占同列数据总和的百分比 % of column | — | — | 与同列数据总和的百分比结果 Result as relative part of the total column (for example, the column sum) |
| 占总和的百分比 % of total | — | — | 与全部数据总和的百分比结果 Result as relative part of the overall result (for example the total sum) |
| 指数 Index | — | — | 按以下公式的计算结果：
条目值 X 全部数据总和 / (同行数据总和 X 同列数据总和) Default result x total result / (row result x column result) |
[编辑] 行和列字段
在行和列字段的选项对话框中可以选择是否对每个分类进行分类汇总。这缺省是禁用的。只有在行或列字段能对另一子字段进行部分汇总时才有用处。 In the preferences dialog for the row or column fields, you can choose if there are partial sums for each category. This is deactivated by default. It is only useful if the values in one row or column field can be divided into partial sums for another (sub)field.
下面三张图片解释说明了些示例。 Some examples are shown in the next three figures.
在 分类汇总 中选择 自动 使用合计函数进行分类汇总（如上图）。要为分类汇总设置其他合计函数，选择 自定义。 Choose the option Automatically to use the aggregate function for the partial results that can also be used for the data fields (see above). To set up the aggregate function for the partial results independently from the overall settings of the DataPilot, choose User-defined.
通常，数据助理不显示分类中没有条目的行或列。使用 显示不带数据的项 选项可以强制显示。 Normally, the DataPilot does not show a row or column for categories that have no entries in the underlying database. With the help of the Show items with no data option, you can force this.
为解释说明，如下图 雇员 Brigitte 没有 golfing 分类销售数据： For illustration purposes, the data was manipulated in such a way, that the employee Brigitte has no sales values for the category golfing.
[编辑] 页字段
页字段 选项对话框与行和列字段的一样，通过灵活使用数据助理对话框设置，可以在他们之间切换不同的数据字段，这里不再详述。 The preferences dialog for page fields is the same as for row and column fields, even though it appears to be useless to do the same settings as described for the row and column fields. With the flexible use of the DataPilot you can switch often the use of the different fields for pages, columns or rows. The fields keep the settings that you made for them. For this, the page field has the same properties as a row or column field. These settings only take effect when you use the field not as page field but as row or column field.
[编辑] 进度表
| Calc:DataPilot对话框 中文化进度 | 翻译 | 1 | 校对 | 0 | 插图 | / | 上次编辑 | 20091216092715 | 编者 | PingjuOOo |
