数据透视表II―轻松创建综合报告-英雄云拓展知识分享

PivotTables part 2: Creating summary reports made easy
数据透视表2：轻松创建综合报告

One of the goals we had for our PivotTable work in Excel 12 was to make creating PivotTables a much more approachable task. In this post, I would like to walk through an example of creating a PivotTable in order to highlight the changes we have made in Excel 12. In general, we tried to make the process simpler and more intuitive.
让用户很方便、很直观的创建数据透视表是我们的一个目标，我们已在Excel 12 中实现了这个目标。在这篇文章，为了重点说明我们在Excel 12 中对数据透视表作出的改变，我创建一个数据透视表作为例子。通常，我们试图使得操作进程简单而且直观。

As I said in the previous post, PivotTables are great for summarizing large amounts of data. For example, a user might have a table full of sales data (contained in a query table, that they copied from elsewhere, that they have been typing into the grid over time, etc.) that contains products, sales figures, product categories, etc., and might want to see a summary of sales grouped by product and product category. This is exactly the sort of thing that is easy to create with a PivotTable. To start, the user needs to tell Excel they want to create a PivotTable. There are two places they can do this. First, on the Insert tab, where the first button in the ribbon is an inset PivotTable button.
正如我在之前的文章中所说，数据透视表主要用于处理大量的数据。举例来看，某个用户可能有一个填满销售数据的列表，包括产品、销售指数、产品种类等等，并且该用户想经过产品及产品种类看到各个销售组的销售概要。经过创建数据透视表这是件很容易实现的事情。回到动身点，用户需要告知Excel，他们想要创建数据透视表。创建数据透视表有两类方式：第一、在“插入”标签上，在 ribbon 里的第一个按钮就是“插入数据透视表”按钮。

Second, on the Table tab (the tab that shows up when I am working with a table of data), we have added a command to “Summarize With Pivot”. This is essentially the same command, except when you use this command, we know that you want the table you are working with to be the data source for the PivotTable.
第2、在“列表”标签上（当我激活数据表时这个标签才会显示出来），我们已附加一个命令到“Summarize With Pivot”。从本质上讲，这是相同的命令，除非当你使用这个命令时，我们知道你想把你正在操作的列表作为数据透视表的源数据。

Once the user selects one of these commands, they are presented with a new dialog for creating PivotTables. This dialog replaces the existing multistep wizard with a simple dialog that only presents the user with the most necessary choices … our usability research showed that a lot of users never made it past the wizard due to the complexity of choices required. (Note, there are probably some out there wondering about whether they can still pivot against multiple consolidation ranges, etc. … the answer is yes. The existing wizard is still available for advanced users that want to take advantage of its functionality; it just isn’t the mainline UI):
一旦用户选择这两个命令的其中之一时，会出现一个创建数据透视表的新对话框。这个简单的对话框取代了现有的多级向导，只显示用户必须的选项……我们的可用性调查报告表示：由于繁琐的选择条件，许多用户历来不依照向导的步骤创建数据透视表。（注：可能会有人怀疑这样的数据透视表是不是依然可以随着多重合并区域的变动而变。回答是肯定的。现有的向导依然适用于想利用这个功能的高级用户，但它已不处在主界面上了。）

In our example, because we had a table selected when we created the PivotTable, all our user has to do is to click OK and the PivotTable is created – that’s a total of two clicks. If the user wanted to work with data external to Excel, they would simply select “Use an external data source” and select the external data connection they would like to use from the drop down. (Note that in the Table/Range refedit control, you can now type “structured references” to tables or parts of tables in addition to cell references. This is true for most of the refedit controls in Excel, and is another small but powerul addition to the product. See this post for more on structured referencing.)
在我们的例子里，由于当我们创建数据透视表时已选择了一个列表，所以用户所要做的就是点击“OK”，那末数据透视表就创建完成了——这总共只需要点击两次鼠标。如果用户想要援用外部数据到Excel，只需要简单的选择“使用外部数据源”并从下拉列表当选择他们想要用的外部数据连接。（注：在列表/范围refedit controns，除单元格援用以外，你还可以键入“structured references（成形的援用）”。在 Excel中，多数的refedit controns都是可以实现的，这也是另外一个小而高效率的附加功能。这篇文章（ this post）可以看到更多的关于成形援用的介绍。）

After clicking OK in the dialog, the user sees a new PivotTable in the Excel grid, and a field list (significantly changed from previous versions) with which to populate the PivotTable:
在对话框中点击Ok后，用户在Excel格子里可以看到一个新的数据透视表和一个用来构造数据透视表的字段列表（与之前的版本相比，此处的改变很值得关心）：

Now that the PivotTable has been created, the next step is to add the data the user wants summarized. This is another area where our customer research and usability studies demonstrated that many users had trouble, specifically in three areas.
现在，数据透视表已建立了，下一步是添加用户想要汇总的数据。另外一方面，我们所做的消费者调查和可用性研究表示许多用户还有3个方面的烦恼：

• Figuring out that they needed to get their “fields” onto the various areas of the PivotTable
• Deciding which area, or “drop zone” of the PivotTable they needed to add their data to build their report.
• Figuring out how to get the field to the drop zone (drag-drop not being an action that is all that common in Office applications)
• 肯定他们需要的字段该放到数据透视表的哪些区域。
• 判断数据透视表的哪一个区域或“拖放区域”需要增加他们的数据才能建立他们的报表。
• 学会将字段添加到拖放区域中（拖拽其实不是所有的Office利用软件共有的操作方式）。

In order to address these problems, we made a couple of changes. First, we added checkboxes to the field list, which, though a simple change, clearly advertises to the user what actions they need to take. Second, we changed the rules, so that users don’t need to move fields to drop zones to get going – when they check a checkbox for a field, that field is automatically added to the PivotTable, using a simple but generally effective algorithm. If the data type of the field is numeric, the field is added to the Values area, with a default aggregation of sum. If the data type is non-numeric, the field is added to the Row Labels area. As you click more non-numeric fields, Excel places them on the inside of fields already on the PivotTable, building a hierarchy. Un-checking a checkbox will remove the field from the PivotTable.
为了解决这些困难，我们做了两个改动。第一，我们在字段列表框里增加了复选框，虽然这是一个很简单的改变，但是能清楚的告知用户，他们需要如何来操作。第2，我们改变原本的惯例，所以使用者不需要移动字段到拖放区域——当他们勾选某个字段的复选框时，这个字段就会自动增加到数据透视表中，利用了一个简单但通用行之有效的的运算法则。如果这个字段的数据类型为数值，那末这个字段就会增加到值区域，构成一个求和的默许汇聚。如果该数据类型不是数值型，那末这个字段就会增加到行标签区域。当你点击多个非数值型字段时，Excel 会自动把这些字段安排在数据透视表的字段区域当中，并建立层次结构。不勾选复选框的话，这些字段就会从数据透视表中移除。

So with the new field list, a user can get a quick summary by simply checking a couple of checkboxes, and that’s it. Let’s walk through what this looks like. As I said, our user wants to build a sales summary for products by their product category. The first field for the user to add is Product Category, so they click in the checkbox for that field …
由于有了新的字段列表，用户只需要简单的勾选几个复选框就能够很快地得到他们想要的汇总报告。让我们来看看这个字段列表究竟是个甚么模样。正如我所举的例子，我们的用户想要经过他们的产品种类建立一个产品销售报告。用户第一个要增加字段的就是产品种类，所以他们勾选该字段的复选框就能够了……

… and the items of the Product Category field are immediately added to the PivotTable.
产品种类字段的项目就会立即添加到数据透视表中。

Next, to show the individual products of each product category, the user adds the Product Name field:
下一步，为了显示每个产品种类的单个产品，用户就要添加产品名称字段：

Now we have the products nicely listed under their product category in the PivotTable, complete with some UI that hints that you can expand and collapse levels … more on that in a later post.
现在，在数据透视表中，各个产品都罗列在他们各自的产品种类之下，包括一些提示你进行展开和折叠操作的用户界面……在后面的文章中你会看到更多的介绍。

And finally, our user adds the Sales Amount field to finish their summary report.
最后，我们的用户添加销售额字段就能够完成他们的综合报告了。

They probably want to format it as a currency too, which is one click on the ribbon. And, with that, our user now has their summary report – Excel has grouped all the original data by Category and Product, calculated the Sales Amount, and slapped on a Grand Total … all in a total of 5 clicks.
他们大概还想要把这个综合报告格式化成为通用的格式，这只需要在ribbon上点击一下就能够了。这样，我们的用户就完成了他们的综合报告——Excel已汇聚了所有产品种类和产品名称的原始数据，计算出销售额并进行汇总……所有这些操作只需要点击5下鼠标。

I can hear some of your saying “but I liked dragging things around”. I can also hear some other folks asking “how do I get things to the Filter area or the Column area to build a crosstab?” (And probably a lot of other things, but I will stop guessing, address those two, and hear the rest in your comments.) Well, you probably noticed in the screenshots above that as I added fields to the PivotTable, they appeared in the lower section of the field list. This section holds the drop zones, which were designed around two key points – making it easy to determine the current placement of fields in the PivotTable, as well as making it easy to rearrange the fields in a PivotTable. There are four areas in a PivotTable, each of which is represented by a drop zone in the field list.
有些人会这样说“我喜欢从周围拖拽东西”。也有些人会这样问“我怎样把东西放到挑选区域或列区域上建立交叉表？”（可能还有许多其他的问题，但是我会禁止这些猜想，对刚才两个问题进行解答，并听取其他的留言。）你可能也注意到在上面的截图里，当我添加字段到数据透视表时，它们会出现在字段列表的下面。这部份保存了托拽区域，这围绕两个关键点进行设计——使得用户很容易肯定数据透视表字段确当前位置，也能够很容易的重新排列数据透视表的字段。在数据透视表中有4个区域，在字段列表中每个区域代表一个拖拽区域。

1. Report Filter. This area holds the fields that the whole PivotTable is filtered by.
2. Row Labels. This area holds fields that act as labels for the values, and the labels appear to the left of the values.
3. Column Labels. This area is just like row area, but the labels appear above the values, breaking them out by column instead of row.
4. Values. This area holds the fields that are summarized (for example sales amount). Fields in this area are typically numeric, but can also be non-numeric (in which case they are counted).
1、 报告挑选。这个区域保存全部数据透视表未被使用的字段。
2、 行标签。这个区域保存担当值标签的字段，并且这个标签出现在值的左边。
3、 列标签。这个区域跟行区域相像，但是这个标签出现在值的上方，用“列”把它们隔开而不是“行”。
4、 值。这个区域保存被汇总的字段（比如销售额）。在这个区域的字段通常代表数值，但是也能够是非数值的（在这类情况下只能进行计数）。

If you prefer to drag fields from the field list to the drop zones, you can … this is also the answer to how you add fields to the Filter or Column drop zones. To change the report layout, you can drag and drop fields between the different drop zones or you can click a field in a drop zone and select which area to move it to in the menu that pops up.
如果你喜欢从字段列表中拖拽字段到拖拽区域，你可以这么做……这也是怎样添加字段到挑选或列拖拽区域的答案。为了改变报告的版面，你可以在不同的拖拽区域之间拖拽字段，也能够在某个拖拽区域里点击一个字段，然后在弹出的菜单当选择你要移到的区域。

And finally, for those of you that really want the drop zones in the grid, we have put in a toggle to bring them back, but there are some additional considerations to that one, so I will cover it further in a later post.
最后，为了满足那些确切想要在格子里拖拽区域的用户，我们已引进了开关来允许旧式的操作，因是为这其中有一些其它的斟酌，所以我会在以后的文章中做出更深层次的介绍。

So, to sum up, we have worked to make PivotTables easier and faster to create, and our usability testing with both beginning users as well as PivotTable experts (see post on usability studies for more on that subject) show that both user groups benefit from the new design.
总而言之，我们所做的工作就是使得创建数据透视表更容易更快速，并且我们在低级用户和数据透视表专家二者所做的可用性测试表示这两个用户群都能受益于新的设计。

Next week, much more on PivotTables.
下周，更多的有关数据透视表的介绍。

Published Friday, December 09, 2005 12:25 PM by David Gainer

非常感谢Kevin的帮助和指点!

选择英雄云的云表单=选择更智能的Excel

在现代企业管理中，数据的高效管理和处理至关重要。随着信息技术的不断发展，英雄云的云表单已经成为了提高数据录入、管理和分析效率的不可或缺的工具。让我们来深入探讨英雄云-云表单的几大优势。

基础字段：多样性满足业务需求

英雄云的云表单中包括了各种基础字段，如单行文本、多行文本、数字输入框、单选框、复选框、下拉框、下拉复选框、日期时间、分割线等。这些字段的多样性使用户可以根据具体的业务需求，轻松进行文本、数据和时间信息的录入或修改。例如，您可以使用单行文本字段录入员工姓名、产品型号等，或者使用下拉框进行多选，根据不同情况选择更加方便的字段类型。

高级字段：提升工作效率

英雄云的云表单还提供了高级字段，如地址、图片、附件、手写签名、手机、子表、关联数据、关联查询以及流水号。这些高级字段在基础字段的基础上升级，可帮助用户完成一些琐碎的工作。例如，使用地址字段可以避免逐字打字，而流水号字段可以自动生成规律性的编号，非常适用于合同编号生成等场景。

部门成员字段：精确管理与通讯录的关联

英雄云的部门成员字段允许企业对各个部门的成员进行精确管理。用户可以通过部门成员字段获取通讯录中的部门成员信息，应用于记录报销人、报销部门等场景。这些成员字段还细分为成员单选和成员多选，可根据具体需求在通讯录中选择一个或多个成员。

聚合表：数据处理更智能

英雄云的聚合表功能用于对已存在的表单数据进行聚合计算，从而得到一张聚合表，后续其他表单可调用聚合表进行数据联动、关联查询和关联数据等操作完成数据处理。这一功能可应用于多种场景，如进销存管理、财务管理和门店零售管理等，帮助企业完成数据处理，提高工作效率。

表单权限设置：灵活管理数据访问

英雄云的表单权限设置允许用户根据企业的具体需求管理表单的访问和操作权限。用户可以根据系统权限或自定义权限对不同成员或团队进行权限设置，以确保数据的安全和合规性。这一功能使企业能够根据变化的业务需求和团队结构，实时调整权限设置。

自定义打印模板：文档输出更便捷

英雄云的云表单支持自定义打印模板，可将表单数据转换为可打印的Word文档。用户可以根据自己的需求进行排版和编辑，将产品规格说明书等文档轻松生成。这一功能提供了一种标准化的文档输出方式，简化了信息整理的过程。

综合来看，选择英雄云的云表单意味着选择更智能、更灵活、更高效的数据管理工具。无论是提高工作效率，精确管理数据，还是实现数据处理，英雄云的云表单都能满足您的多样化需求，助力您的业务发展。

如果您正在寻找一款强大的云表单工具，不妨考虑英雄云，它将为您带来更多的便捷和智能，助您事半功倍。

免责声明：

本网址（www.yingxiongyun.com）发布的材料主要源于独立创作和网友匿名投稿。此处提供的所有信息仅供参考之用。我们致力于提供准确且可信的信息，但不对材料的完整性或真实性作出任何保证。用户应自行验证相关信息的正确性，并对其决策承担全部责任。对于由于信息的错误、不准确或遗漏所造成的任何损失，本网址不承担任何法律责任。本网站所展示的所有内容，如文字、图像、标志、音频、视频、软件和程序等的版权均属于原创作者。如果任何组织或个人认为网站内容可能侵犯其知识产权，或包含不准确之处，请即刻联系我们进行相应处理。