PivotTables 12: Filtering OLAP data, and some “persistence” improvements 数据透视表 12:筛选OLAP数据,以及一些“持续”改进
在前面的文章中,我讲过了Excel 12数据透视表的新排序和筛选功能。这些功能可为任何数据透视表所用,不管是什么数据源。连接到Analysis Services的数据透视表还有一些额外的筛选选项,因此今天我想要回顾一下这些。我也想要简短地看一下我们在Excel 12里所作的“格式持续性”的工作。 In a previous post I covered the new sorting and filtering capabilities of Excel 12 PivotTables. Those features are available for any PivotTable, regardless of the data source. There are a few additional filtering options available for PivotTables connected to Analysis Services, so I want to review those today. I also wanted to make a short point about some “formatting persistence” work we have done in Excel 12.[Point=2]
筛选成员属性 几天前,我写了关于成员属性的一些东西。当数据透视表从Analysis Services连接到数据时,你可以基于那些项目的属性值筛选项目。我们来看一个示例。在下面的截屏中,我有一个数据透视表,有Product Categories(产品品类), Products(产品)和Sales Amounts(销售数量)。我可能想要通过它们的某个属性在数据透视表里筛选Products。我可以通过申请一个Label Filter实现它……我只是简单地在某个产品上单击右键,并且从快捷菜单上选择Filter | Label Filter(译者,原文误为Filer)。 Filtering by member properties A few days ago I wrote about member properties. When a PivotTable is connected to data from Analysis Services, you can filter items in the PivotTable based on the value of that item’s member properties. Let’s look at an example. In the screenshot below, I have a PivotTable with Product Categories, Products, and Sales Amounts. I might want to filter the Products in the PivotTable by one of their properties. I can do this by applying a Label Filter … I simply need to right-click on one of the products and choose Filter|Label Filer from the context menu.
(Click to enlarge) 这会打开Label Filter对话框。因为我所选择的字段已经定义了很多成员属性,Label Filter对话框将它们列出供我从中选择。 This brings up the Label Filter dialog. Since there are member properties defined for the field I selected, the Label Filter dialog lists those for me to select from.
(Click to enlarge) 如果选择字段名称(本例中是“Product Name”),该筛选就会应用数据透视表中可见的项目。然而,如果我选择了某个列在字段名称下面下拉框里的成员属性,筛选器就会只关注该成员属性值。如果我只想看看银色的自行车的话,那么我可以使用Colour成员属性来实现。这里有个截屏,用颜色成员属性筛选的数据透视表,因此只有银色的自行车被显示了。 If I pick the field name (“Product Name” in the example), the filter will be applied to the visible items in the PivotTable. If I pick one of the member properties, however, which are listed under the field name in the drop down, the filter will look at the member-property values instead. If I only want to see the bikes where the color is silver, I can use the Colour member property to do that. Here is a screenshot of the PivotTable filtered by the color member property so only silver bikes are displayed.
(Click to enlarge)
筛选没有显示在数据透视表里的数值 连接到Analysis Server的数据透视表的另外一个筛选功能是筛选当前并没有显示在该数据透视表里的数值。例如,你可能想要在一个销售报表中,通过每个产品的利润来筛选产品,尽管利润没有显示在该数据透视表里。同样,我们来看一个例子。下面是个按产品和产品品类显示销售量的数据透视表。在本示例中,我仅想要查看利润超过40%的产品。为了做到这样,我将应用一个数值筛选到我的数据透视表。 Filtering by values not displayed in the PivotTable Another filter ability specific to PivotTables connected to Analysis Server is the ability to filter items by a value that is not currently displayed in the PivotTable. For example, you might want to filter products in a sales report by the profit margin of each product, even though profit is not showing in the PivotTable. Again, let’s walk through an example. Below is a PivotTable that shows Sales Amount by Product and Product Category. In this case, I only want to see products that have a profit margin which is greater than 40%. To do this I’ll apply a value filter to my PivotTable.
(Click to enlarge) 我在快捷菜单中选择Value Filter后,我看到Value Filter对话框。当我拉下第一个下拉列表时,列出了所有可用的不同数值字段,尽管该数据透视表仅包含Sales Amount(销售量)。我只是简单地选择了Gross Profit Margin,输入40,并按下OK。 After I select Value Filter from the context menu, I see the Value Filter dialog. When I drop the first drop-down, all the different value fields available are listed, even though the PivotTable only contains Sales Amount. I simply select Gross Profit Margin, type in 40, and press OK.
(Click to enlarge) 现在报表只显示利润大于40%的自行车了。 And now my report is showing only bikes with a profit margin greater than 40%.
(Click to enlarge) 我个人认为这个功能非常有趣。 I am personally a very big fan of this feature.
隐藏层次级别 连接到Analysis Services的Excel 12数据透视表允许你隐藏任何层次级,只要至少还有一级是可见的就可以。举例说,假如我想比较自行车,不管它们是什么型号(我不要查看Category或Subcategory信息)。要实现这个,我可以隐藏产品名称级别的父级别。确切地说,我只需要从数据透视表的右键菜单中选择显示、隐藏就可以,并且从那里,我可以切换任意级别的开或关。 Hiding levels of hierarchies Excel 12 PivotTables that are connected to Analysis Services allow you to hide any level of a hierarchy as long as at least one level is still visible. As an example, say I want to compare bikes independent of what type of bike they are (I don’t want to see Category or Subcategory information). To do this, I can hide the parent levels of the product name level. Specifically, I just need to select Show/Hide Levels from the PivotTable context menu, and from there I can toggle on or off any levels I like.
(Click to enlarge) 在隐藏两级后,我也按它们的销售量进行自行车排序,如你在下面截屏看到的,我现在可以在自行车组合里查看它们了。注意现在山地自行车和道路自行车等是混在一起的。 After hiding the two levels, I’ve also sorted the bikes by their individual total sales amounts and, as you can see in the screenshot below, I can now work with the bikes across their groups. Notice that mountain bikes are now mixed with road bikes etc.
(Click to enlarge) 例如,这也允许我创建一个“前十位”销售最好的自行车,不管其品类。这是Excel 12数据透视表拥有强大分析能力的另一个例子。 This also allows me to produce, for example, a “Top 10” list of the best-selling bikes regardless of category. This is another example of the very powerful analysis capabilities available in Excel 12 PivotTables.
(Click to enlarge) 这是最终结果。 Here is the final result.
(Click to enlarge) 如果我现在取消隐藏的Subcategory级的话,筛选会重新评估新的上下文,并且会获得每个子品类中的“前十位”的自行车清单。我认为那也是很灵巧的。 If I now unhide the Subcategory level, the filter will be reevaluated in the new context, and I will get a “Top 10” list of bikes for each subcategory. I think that’s pretty neat too.
用户应用格式的持续性更好了 最后,我们改进了OLAP数据透视表的用户应用格式的持续性。下面的截屏显示一个数据透视表,我通过在单元格里输入新名称,手动更改一自行车的名称为“Our Classic Mountain Bike”,并且将文本设置为粗斜体,然后设置单元格背景颜色为红色。 Better persistence of user applied formatting Finally, we have improved the persistence of user-applied formatting in OLAP PivotTables. The screenshot below shows a PivotTable where I’ve manually change the name of a bike to “Our Classic Mountain Bike” by typing the new name into the cell, and where I have also made the text bold + italics and then set the cell background color to red.
(Click to enlarge) 现在。如果我折叠数据透视表里的山地自行车(隐藏该具体的山地自行车),然后在展开山地自行车,我设置了格式的那个山地自行车将仍然会象我折叠山地自行车之前那样完成一样的格式。在现在的Excel版本中,在这样的方案中,所有的格式都会丢失。一个很小的项目,但是我肯定人们会喜欢看到它“固定了”。 Now, if I collapse Mountain Bikes in the PivotTable (which hides the individual mountain bikes), and then expand Mountain Bikes again, the mountain bike I formatted will still be formatted exactly like before I collapsed Mountain Bikes. In current versions of Excel, all the formatting is lost in this scenario. A small item, but one I am sure folks will be glad to see “fixed.”
Published Thursday, January 12, 2006 8:24 PM by David Gainer 注:本文翻译自http://blogs.msdn.com/excel,原文作者为David Gainer(a Microsoft employee),Excel home授权转载。严禁任何人以任何形式转载,违者必究。 [/Point]
[此贴子已经被Kevin于2006-5-4 15:43:44编辑过] |