Excel Home

XII 筛选OLAP数据,以及一些“持续”改进(二)

译者:hxhgxy  来源:http://blogs.msdn.com/excel

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.

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.

连接到Analysis Services的Excel 12数据透视表允许你隐藏任何层次级,只要至少还有一级是可见的就可以。举例说,假如我想比较自行车,不管它们是什么型号(我不要查看Category或Subcategory信息)。要实现这个,我可以隐藏产品名称级别的父级别。确切地说,我只需要从数据透视表的右键菜单中选择显示、隐藏就可以,并且从那里,我可以切换任意级别的开或关。

(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)

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.
例如,这也允许我创建一个“前十位”销售最好的自行车,不管其品类。这是Excel 12数据透视表拥有强大分析能力的另一个例子。

(Click to enlarge)

Here is the final result.

(Click to enlarge)

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.

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.

最后,我们改进了OLAP数据透视表的用户应用格式的持续性。下面的截屏显示一个数据透视表,我通过在单元格里输入新名称,手动更改一自行车的名称为“Our Classic Mountain Bike”,并且将文本设置为粗斜体,然后设置单元格背景颜色为红色。

(Click to enlarge)

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授权转载。严禁任何人以任何形式转载,违者必究。

未经允许不得转载:ExcelHome » XII 筛选OLAP数据,以及一些“持续”改进(二)

本站特聘法律顾问:李志群律师   沪ICP备11019229号-2

沪公网安备 31011702000001号