數據集函數Part1 概述（一）

數據集函數Part1 概述（一）

譯者：apolloh? 來源：http://blogs.msdn.com/excel 發表于：2006年7月7日

polThe Excel 12 Blog Rides Again, or “CUBE Functions Part 1” 繼續Excel 12 博客游戲——數據集函數 Part 1

OK, I am freshly back from a fascinating couple weeks of visiting a number of customers (thanks to all you that spent time with us over the last few weeks), so it is time to get back to talking about Excel 12 features. 好了，前幾周我做了有幾個有趣的客戶拜訪（感謝客戶們抽了幾周來陪我們），剛才回來。現在接著談EXCEL 12的特性。

We’ve already talked about using Pivot Tables to bring OLAP data from SQL Server Analysis Services into Excel. In cases where you want to explore the data and look for interesting facts within the data by “slicing and dicing” it, PivotTables are the perfect tool. You can change the fields that are being displayed, drill into the data, change the filtering and sorting that has been applied, and in the case of OLAP data from SQL Server Analysis Services, you can use all the other features I have covered to look at the data your way. 我們曾討論過如何用數據透視表從SQL Server Analysis Services取數到Excel里。如果您想對數據進行深入挖掘和剖析，找出其中有價值的信息，那么數據透視表是一個非常合適的工具。您可以更改數據透視表的字段顯示，重排數據，改變篩選和排序的應用。如果您使用SQL Server Analysis Services的OLAP數據源，還可用前面我介紹過的其它功能，以自己的方式來查看數據。

There are other scenarios that involve bringing OLAP data from SQL Server Analysis Services into Excel, though, that are not as well suited to using a PivotTable. While PivotTables are an ideal tool for quickly exploring your data and finding a wide variety of answers in an ad hoc manner, they are not as well suited for building fixed reports with irregular or custom layouts. Here are a few quick examples:　 還有幾種需要從 SQL Server Analysis Services 的OLAP 數據源取數的情況，但并不適合用數據透視表。雖然數據透視表是個理想的工具，它可以通過特定的方式，快速地挖掘數據，尋求多種答案，但是它不適合用來創建固定版式的不規則或自定義的報表。舉幾個例子：

·“Asymmetric reports,” wherein the rows or columns don’t follow a fixed pattern in terms of the data they contain (more on this below) ·自由式報告，報告的行列不遵循約定的固定模式。（詳見后敘）

·Reports that mix data from multiple data sources ·混合使用多數據源的報告

·Reports that require a lot of custom spacing and/or precise grouping and ordering, often in a different way than the data is stored in the data source ·帶有很多間隔行和/或細化分組排序的報告，常常不同于數據源的數據存儲方式。

To address those needs (and others), we wanted to provide a way to place OLAP data from SQL Server Analysis Services directly into specific spreadsheet cells without being constrained in any way by what might be in the adjacent cells. This would provide users with the power of a multi-dimensional data store in conjunction with the power of the Excel formula language and calculation engine. Let’s take a look. 為了滿足這些需求（或者更多），我們考慮提供有一種方法，將SQL Server Analysis Services的OLAP數據直接放入特定的工作表單元格中，而不受到周圍單元格的任何限制。它可同時具備多維數據存儲和Excel公式化語言及計算引擎的能力。讓我們看個例子。

Here is an example of an asymmetric report that was built using the Excel 12 beta build and the Adventure Works sample database that ships with SQL Server 2005 Analysis Services. 下例使用存放在SQL Server 2005 分析服務器上的Adventure Works樣本數據，在Excel 12 測試版上，創建了一張自由式報表。

Note that the fields across the top axis (on columns) do not come from the same dimension. The sales reasons (‘On Promotion’ and ‘Price’) and the countries (‘Canada’ and ‘United States’) are members from two completely distinct dimensions. Also note that while all the values are based on the items in Column A, this report contains blank “spacer” columns in columns B and E. Finally, you can see the formula for cell C2 which fetches the ‘On Promotion’ member from the ‘Sales Reason’ cube dimension. This formula uses the CUBEMEMBER function (which is one of seven new CUBE functions) to place OLAP data from SQL Server Analysis Services into Excel spreadsheet cells. 請注意標題列的字段取自不同的數據維度。銷售成因（“促銷”和“價格”）和國家（“加拿大”和“美國”）分別取自兩個維度。另外，可以見到所有數值的計算都基于A欄中的項目，且報表的B和E兩欄是空列。最后，還可看到單元格C2中的公式從“銷售成因”的維度提取了一個成員——“促銷”。該公式使用CUBEMEMBER函數（7個新CUBE函數的一個），將SQL Server Analysis Services的OLAP數據，取到Excel電子表格的單元格中。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。