微軟Excel 2013：用PowerPivot 建立數據模型

Microsoft Excel是進行數據分析的世界標準，其易用性和強大功能使得Excel電子表格成為人人使用的工具，無論分析何種信息。

基本介紹

• 中文名：微軟Excel 2013：用PowerPivot 建立數據模型
• 作者：Alberto Ferrari Marco Russo
• 譯者：劉凱 魏新橋
• 出版社：清華大學出版社
• 出版時間：2015年01月01日
• 定價：49.5 元
• ISBN：9787302381549

內容簡介

MicrosoftExcel是進行數據分析的世界標準，其易用性和強大功能使得Excel電子表格成為人人使用的工具，無論分析何種信息。

這本書面向Excel用戶、項目經理和決策者，本書能滿足他們學習PowerPivotforExcel2013基礎知識、掌握用於PowerPivot的新的DAX語言以及學習PowerPivot高級數據建模和編程技術的願望。

這本書，不是一步一步指導PowerPivotforExcel2013的書。如果你正在尋找PowerPivot傻瓜書，那么只能說這不是你的菜。但是如果你需要一本書伴隨你在這漫長而令人愉悅的旅行中，從第一個簡單的Excel工作簿開始，不久將很快創建複雜的模擬，那么這本書就是你的終極資源。

在寫這本書時，我們決定把重點放在概念和實例上，從零開始帶你掌握DAX語言。

本書不涵蓋每個功能，也不用“單擊A，然後B”的方式解釋每個操作。相反，本書承載了大量信息，這樣一旦學完本書，你在Excel新的建模選項中將有足夠的背景知識。

用最後一句話來強調這本書的主要特點:它是用來學習的書，而不只是閱讀。做好長途旅行的準備——但我們向你保證這將是非常值得的。

前言

MicrosoftExcel是進行數據分析的世界標準，其易用性和強大功能使得Excel電子表格成為人人使用的工具，無論分析何種信息。

可以使用Excel存儲個人開支數據、現有賬號信息、客戶信息或複雜的商業計畫，甚至在難以堅持的節食期間存儲你的瘦身進展。使用Excel的可能性是無限的——我們就不再列舉可以用Excel分析的所有各類信息了。事實是，如果有一些待整理和分析的數據，那么Excel是可供使用的完美工具。即使只有相對有限的軟體知識，你也可以輕鬆地以表格式的形式組織數據，更新數據，生成圖表、數據透視表和基於數據的計算，並且作出預測。隨著雲計算的到來，現在你可以在移動設備上(諸如平板電腦和智慧型手機)使用Excel，也可利用網際網路持續訪問信息。此外，在Excel的早期版本中，每個工作表有65536的行數的限制，而事實上許多用戶都要求微軟增加行數(微軟確實這么做了，在Excel2007中將行數限制提高到100萬行)，這清晰地表明用戶希望在Excel中存儲和分析大量數據。

除了Excel用戶以外，還有一類人在其職業生涯中致力於數據分析:商業智慧型(BI)專業人士。BI是從大量信息中獲得的洞察力的科學，而且，近年來BI專業人士已經學習並創建了許多新的技術和工具，以管理可以處理高達數百萬甚至數億行交易記錄的系統。BI系統需要很多專業人士的努力和昂貴的硬體來運行。BI系統十分強大，但也存在嚴重缺點:構建起來十分昂貴和耗時。

2010年以前，在少量數據分析和大量數據分析之間存在清晰的界限:一方為Excel，另一方為複雜的BI系統。現在Excel邁出了融合這兩個世界的第一步，因為數據透視表工具已經能夠查詢BI系統。通過這么做，數據分析師可以查詢大型BI系統，實現魚和熊掌兼得，因為這樣一個查詢結果可以導入Excel數據透視表，因此可用於進一步的分析。

2010年，微軟公司發起的一項顛覆之舉打破了BI專業人員和Excel用戶之間的藩籬:通過引入一個名為xVelocity的強大引擎，直接在Excel內部驅動大型BI解決方案。彼時，MicrosoftSQLServer2008R2PowerPivotforExcel發布，成為Excel2010的免費外掛程式。目標是使得創建BI解決方案變得如此容易，Excel將不僅僅能作為BI客戶端啟動，而且還能作為BI伺服器啟動，能夠在筆記本電腦上託管複雜的BI解決方案。他們將其稱為自助式BI。

微軟PowerPivot對其可存儲的行數沒有限制:如果你需要處理100萬行，你大可放心地這么做，且其分析速度驚人。PowerPivot還引入了DAX語言，一個旨在創建BI解決方案的強大程式語言，而不僅僅通過Excel公式。最後，PowerPivot能夠高倍壓縮數據，使得大量信息可存儲於相對較小的工作簿中。但是，這還只是第一步。

第二個決定性的一步，是將用戶級BI的力量引入到Excel2013當中。PowerPivot不再是一個分離的Excel外掛程式，現在成為Excel技術的一個內在組成部分，為每個Excel用戶帶來了xVelocity引擎的力量。自助式BI時代於2010年開啟，且在2013年升級。

由於你已在閱讀這篇介紹，你可能有興趣加入到自助式BI的浪潮中，並且想要學習如何掌握PowerPivotforExcel。你將需要學習PowerPivot工具的基本知識，但是這僅僅是第一步。然後，需要學習如何組織數據以便有效地執行分析，即數據建模。最後，你將需要學習DAX語言並掌握其所有概念，以便充分利用其效力。如果這些是你想要的，那么這本書即是為你而作。

我們是BI專業人士，從經驗中我們知道構建BI解決方案並不容易。

我們不想誤導你:BI是激情科技，也是工程科技。本書的目的是幫助你採取必要的步驟，將你從Excel用戶轉變為自助式BI建模師。這將是一段漫長之旅，是需要時間和奉獻精神的旅行，你會發現自己需要做出調整以學習新技術。然而，最終修成的正果是無價的。

這本書，不是一步一步指導PowerPivotforExcel2013的書。如果你正在尋找PowerPivot傻瓜書，那么只能說這不是你的菜。但是如果你需要一本書伴隨你在這漫長而令人愉悅的旅行中，從第一個簡單的Excel工作簿開始，不久將很快創建複雜的模擬，那么這本書就是你的終極資源。

在寫這本書時，我們決定把重點放在概念和實例上，從零開始帶你掌握DAX語言。

本書不涵蓋每個功能，也不用“單擊A，然後B”的方式解釋每個操作。相反，本書承載了大量信息，這樣一旦學完本書，你在Excel新的建模選項中將有足夠的背景知識。

用最後一句話來強調這本書的主要特點:它是用來學習的書，而不只是閱讀。做好長途旅行的準備——但我們向你保證這將是非常值得的。

注意:PowerPivot和PowerView軟體功能只包含在特定的Office2013配置中。適用於所有Excel2010版本的PowerPivot功能，僅適用於Office2013的專業增強版、SharePoint2013企業版、SharePointOnline2013Plan2、Office365的E3或E4版本。在Excel2013中新增的PowerView功能，包含在同一版本的PowerPivot中。幸運的是，在Excel2013所有配置中均支持Excel數據模型。然而，請注意，各類可用配置是可以改變的。

本書為誰而作

這本書面向Excel用戶、項目經理和決策者，本書能滿足他們學習PowerPivotforExcel2013基礎知識、掌握用於PowerPivot的新的DAX語言以及學習PowerPivot高級數據建模和編程技術的願望。

對讀者的假定

這本書假定你對Excel2010或Excel2013有一個基本了解。你不需要成為一個Excel高手,只是一個普通用戶就好。將介紹從Excel過渡到PowerPivot需要什麼，但不以任何方式涵蓋Excel基礎知識，例如輸入公式、寫VLOOKUP函式或其他基本功能。不需要PowerPivot的預備知識。如果你已經嘗試過自己建立一個數據模型，那更好；但我們假設在閱讀這本書之前，你從來沒有打開過PowerPivot。

這本書如何組織

從頭到尾讀這本書。任何試圖直接跳轉到某個特定問題的解決方案，跳過一些內容，可能會是錯誤的選擇，在每個章節都會介紹需要在後續章節進一步理解的概念和功能。

此外，你將需要不止一次地閱讀本書的一些章節，因為其中的理論背景是很難在第一次閱讀時掌握的。

全書共分為16章。

第1章“PowerPivot簡介”介紹了PowerPivotforExcel2013中的基本功能。按照一步一步的指導，我們將展示使用PowerPivot滿足分析需求的主要好處。我們還展示了如何創建一份簡單的PowerView報告。

第2章“使用PowerPivot的獨特功能”展示了只有當你啟用PowerPivotforExcel後才可使用的功能。這包括計算列、計算欄位、層次結構和一些其他基本特性。本章是第1章的邏輯延續和結論。

在第3章“DAX簡介”中，我們開始涉獵DAX語言，包括DAX語法和最基本的函式。我們強調計算列和計算欄位之間的差異，而且在最後展示了使用DAX的第一個實例。

第4章“了解數據模型”是具有理論性的一章，涵蓋了數據建模基礎，並展示了PowerPivot資料庫中不同的建模選項。我們將介紹幾個明顯不屬於Excel用戶領域的概念，例如規範化和反規範化、SQL查詢語句的結構、關係的工作原理以及重要性、數據集市和數據倉庫的結構等。

第5章“發布到SharePoint”講解發布Excel工作簿到MicrosoftSharePoint的流程，以實現團隊級BI。此外我們將介紹PowerPivotforSharePoint作為一個伺服器端應用程式的概念，你可以使用Excel和PowerPivot來編程和擴展。

第6章“載入數據”致力於以多種方式將數據載入到PowerPivot內部。對於每個數據源，我們展示了其工作方式，並為具體來源提供了許多提示和最佳實踐。

第7章“理解計值上下文”和第8章“理解CALCULATE”是本書的理論核心。在這兩章中，我們介紹了計值上下文、關係和CALCULATE函式的概念。這些都是DAX語言的支柱，你在使用PowerPivot創建高級數據模型之前需要掌握這三大支柱。

第9章“使用層次結構”展示了如何創建和管理層次結構。本章涵蓋了基本的層次結構處理，如何計算層次結構的值，最後，本章展示了如何通過使用在第7章和第8章中所學到的概念來管理父/子層次結構。

第10章“使用PowerView”專門介紹Excel2013中的新的報告工具PowerView。本章展示了該報告工具的主要功能，如何創建簡單的PowerView報告，以及如何篩選數據並創建令人愉悅的報告供查看並提供從數據派生而來的有用見解。

第11章“構建報告”涵蓋了一些有關報告的高級主題，包括關鍵績效指標(KPI)，如何編制KPI，以及如何使用KPI來提高報告系統的質量，本章還涵蓋了PowerPivot中的PowerView元數據層、鑽通、Excel集或MDX集以及透視。

第12章“在DAX中執行日期計算”處理時間智慧型。YTD(年初至今)、QTD(季初至今)、MTD(月初至今)、工作日vs.非工作日、半累加度量、移動平均以及所有其他涉及時間的複雜計算都在本章之中。

第13章“使用高級DAX”組合了情景和解決方案，所有這一切都共享相同的背景:它們是使用Excel或任何其他任何工具難以解決的，而一旦你從本書前面章節中獲得必要的知識，在DAX之中它們就比較容易管理。所有這些例子都來自現實世界的情景，都是當我們作為顧問或在網路上看論壇時處於所看到的請求列表前列的情景。

第14章“使用DAX作為查詢語言”專門講述了使用DAX作為查詢語言。它涵蓋了用於查詢資料庫時的各種DAX函式。它也展示了高級函式，如反向連結和連結回表，這些能夠極大地提升PowerPivot構建複雜數據模型的能力。

第15章“使用VBA自動化操作”討論如何以編程的方式使用MicrosoftVisualBasicApplication(VBA)管理PowerPivot工作簿，以自動化一些常規任務；提供了一些代碼示例顯示如何解決一些常見情景，這些VBA可能十分有用。

第16章“比較Excel和SQLServer分析服務”比較了3種風格的PowerPivot技術:PowerPivotforExcel、PowerPivotforSharePoint和SQLServer分析服務(SSAS)。最後一章的目標是給你一個清晰的畫面:在PowerPivotforExcel中可以做什麼，何時需要進一步採用PowerPivotforSharePoint，其在SSAS中有哪些僅適用於SSAS的額外功能。

目錄

第1章PowerPivot簡介1

1.1在Excel表中使用數據透視表2

1.2在MicrosoftOffice2013環境中使用PowerPivot4

1.2.1將信息添加到Excel表格當中5

1.2.2創建一個包含多個表格的數據模型6

1.2.3了解關係9

1.3了解數據模型10

1.3.1查詢數據模型11

1.4PowerPivot載入項12

1.5使用OLAP工具並轉換為公式15

1.6理解PowerPivotforExcel201318

1.7創建一個PowerView報告20

第2章使用PowerPivot的獨特功能25

2.1載入外部數據源25

2.1.1創建PowerPivot數據透視表28

2.2使用DAX語言29

2.2.1創建計算列30

2.2.2創建計算欄位33

2.2.3計算複雜聚合35

2.3刷新PowerPivot數據模型37

第3章DAX簡介39

3.1理解DAX計算39

3.1.1DAX語法39

3.1.2DAX數據類型40

3.1.3DAX運算符41

3.1.4DAX值42

3.2了解計算列和計算欄位43

3.2.1計算列43

3.3計算欄位44

3.3.1計算列和度量之間的選擇46

3.4處理DAX表達式中的錯誤47

3.4.1轉換錯誤47

3.4.2算術運算錯誤48

3.4.3截獲錯誤50

3.5設定DAX代碼格式51

3.6常用的DAX函式53

3.6.1聚合函式53

3.6.2邏輯函式55

3.6.3信息函式56

3.6.4數學函式57

3.6.5文本函式58

3.6.6轉換函式59

3.6.7日期和時間函式59

3.6.8關係函式60

3.7使用基本的DAX函式61

第4章了解數據模型65

4.1了解數據建模基礎知識65

4.1.1在缺乏數據模型時生成一份報告66

4.1.2構建數據模型68

4.2關於關係的更多內容69

4.3了解規範化和反規範化71

4.4在SQL查詢中的反規範化74

4.4.1PowerPivot查詢設計器74

4.4.2何時對表格反規範化80

4.5了解過度反規範化81

4.6理解OLTP和數據集市83

4.6.1查詢OLTP資料庫83

4.6.2數據集市，事實和維度86

4.6.3星形模式87

4.6.4對於查詢而言，哪種資料庫最好88

4.7使用高級關係88

第5章發布到SharePoint92

5.1SharePoint2013和PowerPivot集成92

5.2授權和設定93

5.3將工作簿發布到SharePoint93

5.4使用PowerPivot圖庫97

5.5將Excel連線到SharePoint的Excel數據模型98

5.6創建一個PowerView報表100

5.7管理PowerPivot數據刷新102

第6章載入數據105

6.1理解數據源105

6.2從資料庫載入107

6.2.1從表格列表載入108

6.2.2載入關係110

6.2.3選擇相關表格110

6.2.4從SQL查詢載入112

6.2.5從視圖載入112

6.3打開現有連線113

6.4從Access載入114

6.5從SQLServer分析服務載入115

6.5.1使用MDX編輯器117

6.5.2在OLAP多維數據集中的鍵的處理118

6.5.3從表格式資料庫載入120

6.6從SharePoint載入121

6.7使用連結表124

6.8從Excel檔案載入126

6.9從文本檔案載入128

6.10從剪貼簿載入130

6.11從一份報告中載入131

6.12從數據饋送載入135

6.13從WindowsAzure市場載入137

6.13.1現有連線139

6.14刷新連線139

第7章理解計值上下文142

7.1計值上下文簡介142

7.1.1理解行上下文145

7.2測試你對計值上下文的理解146

7.2.1在計算列中使用SUM146

7.2.2在計算欄位中使用欄位147

7.3使用疊代器創建行上下文149

7.4理解FILTER、ALL和上下文互動152

7.5使用多個表格154

7.5.1行上下文和關係154

7.5.2篩選上下文和關係156

7.5.3VALUES介紹158

7.5.4ISFILTERED和ISCROSSFILTERED介紹158

7.6計值上下文要點161

7.7創建一個參數表162

第8章理解CALCULATE165

8.1為何需要CALCULATE165

8.2CALCULATE實例168

8.2.1篩選單列168

8.2.2用複雜條件進行篩選172

8.3行上下文中使用CALCULATE175

8.4理解循環依賴178

8.5CALCULATE規則181

8.6理解ALLSELECTED182

第9章使用層次結構185

9.1理解層次結構185

9.1.1何時建立層次結構186

9.1.2建立層次結構187

9.1.3對多個表格創建層次結構187

9.1.4使用層次結構執行計算188

9.2使用父/子層次結構196

第10章使用PowerView205

10.1什麼是PowerView205

10.2PowerView基礎知識205

10.2.1使用篩選器窗格208

10.2.2美化報告210

10.3理解表、矩陣和卡片210

10.3.1使用矩陣可視化類型211

10.3.2使用卡可視化類型212

10.3.3將表格作為切片器213

10.4使用圖表213

10.4.1使用折線圖214

10.4.2使用餅圖214

10.4.3使用散點圖215

10.5使用地圖217

10.6了解向下鑽取220

10.7使用圖塊221

10.8了解序列圖222

10.9有效地使用PowerView224

第11章構建報告225

11.1關鍵績效指標225

11.2為PowerView創建數據模型233

11.3了解PowerView元數據237

11.3.1使用匯總類型238

11.3.2使用默認欄位集239

11.3.3使用“表行為”對話框241

11.4定義集合242

11.5用MDX創建動態集合245

11.6使用透視249

11.7了解鑽通251

第12章在DAX中執行日期計算253

12.1建立一個日曆表253

12.1.1使用多個日曆表格255

12.1.2計算工作日258

12.1.3計算工作日內的差異262

12.2隨時間推移的聚合和比較266

12.2.1年初至今(YTD)、季度初至今(QTD)和月初至今(MTD)266

12.2.2帶有CALCULATE的時間智慧型函式268

12.2.3計算上一年的期間(PY)271

12.2.4計算移動年度合計273

12.2.5使用其他聚合函式274

12.2.6計算上一年的差額275

12.3隨時間推移的期末餘額276

12.3.1半累加度量277

12.3.2OPENINGBALANCE和CLOSINGBALANCE函式279

12.3.3通過使用事務(交易)更新餘額282

12.4計算移動平均284

第13章使用高級DAX289

13.1分區間289

13.2排名293

13.3使用多對多關係299

13.4計算新客戶和回頭客302

13.5了解KEEPFILTERS305

13.6實施購物籃分析313

13.7理解計算列的威力:ABC分析318

13.8處理貨幣兌換321

第14章使用DAX作為查詢語言326

14.1理解EVALUATE326

14.2用EVALUATE創建一個Excel表327

14.3使用查詢中的常用函式330

14.3.1使用FILTER330

14.3.2使用CALCULATETABLE331

14.4使用ADDCOLUMNS331

14.4.1使用帶有ADDCOLUMNS的VALUES333

14.5使用SUMMARIZE334

14.5.1使用ROLLUP選項336

14.6連結回DAX查詢337

14.7用一個連結回表計算ABC分析340

14.8使用CROSSJOIN343

14.9使用GENERATE344

14.10用DAXStudio查詢346

第15章使用VBA自動化操作348

15.1啟用功能區上的“開發工具”選項卡348

15.2通過VBA更新連結回DAX查詢353

15.3使用模型對象356

15.4使用VBA將數據導入到數據模型357

15.5了解數據連線360

第16章比較Excel和SQLServer分析服務363

16.1理解引擎的不同版本363

16.2特性矩陣364

16.3數據安全364

16.4可程式性和靈活性366

16.5翻譯367

16.6資料庫大小368

16.7資料庫數量369

16.8PowerPivot作為一個原型系統370