Power Pivot for Excel 教程：熱門用例和示例

執行摘要

在此處下載數據集以跟隨教程。

皇帝的新裝：Power Pivot 教程

在跨越金融、金融分析、金融市場和金融投資的各個領域和子領域中，Microsoft Excel 是王者。 然而，隨著大數據的到來和指數級增長，在數十年的數據聚合和積累、廉價雲存儲的出現和物聯網（即電子商務、社交媒體和設備的互連）的興起的推動下，Excel 的遺留功能和能力已被推到了極限。

更具體地說，老一代 Excel 的基礎架構和處理限制，例如 1,048,576 行的行數限制，或者在涉及大型數據集、數據表和互連電子表格時不可避免的處理速度減慢，降低了其作為有效大數據工具的可用性。 然而，在 2010 年，微軟為 Excel 添加了一個新維度，稱為 Power Pivot。 Power Pivot 為 Excel 提供了下一代商業智能和商業分析功能，能夠在不影響處理速度的情況下提取、組合和分析幾乎無限的數據集。 然而，儘管它在八年前發布，但大多數金融分析師仍然不知道如何使用 Power Pivot，許多人甚至不知道它的存在。

在本文中，我將向您展示如何使用 Power Pivot 來克服常見的 Excel 問題，並通過一些示例了解該軟件的其他主要優勢。 本 Power Pivot 教程旨在作為您可以使用此工具實現的目標的指南，最後，它將探索一些 Power Pivot 通常證明非常寶貴的示例用例。

什麼是 Power Pivot，為什麼有用？

Power Pivot 是 Microsoft Excel 的一項功能，作為 Excel 2010 和 2013 的加載項引入，現在是 Excel 2016 和 365 的本機功能。正如 Microsoft 解釋的那樣，Power Pivot for Excel“使您能夠導入數百萬行將來自多個數據源的數據整合到一個 Excel 工作簿中，創建異構數據之間的關係，使用公式創建計算列和度量，構建數據透視表和數據透視圖，然後進一步分析數據，以便您可以在不需要 IT 幫助的情況下及時做出業務決策。 ”

Microsoft 在 Power Pivot 中使用的主要表達式語言是 DAX（數據分析表達式），儘管在特定情況下可以使用其他語言。 同樣，正如微軟解釋的那樣，“DAX 是函數、運算符和常量的集合，可用於公式或表達式中，以計算和返回一個或多個值。 更簡單地說，DAX 可幫助您從模型中已有的數據中創建新信息。” 幸運的是，對於那些已經熟悉 Excel 的人來說，DAX 公式看起來很熟悉，因為許多公式具有相似的語法（例如SUM 、 AVERAGE 、 TRUNC ）。

為清楚起見，使用 Power Pivot 與基本 Excel 的主要優勢可總結如下：

• 它允許您導入和操作數億行數據，其中 Excel 的硬約束只有超過一百萬行。
• 它允許您將來自多個源的數據導入到一個單一的源工作簿中，而無需創建多個受版本控制和可轉移性問題困擾的源工作表。
• 它可以讓您處理導入的數據、分析它並得出結論，而不會讓您的計算機慢到蝸牛的速度。
• 它使您可以使用 PivotCharts 和 Power BI 可視化數據。

在以下部分中，我將逐一介紹上述每個內容，並向您展示 Power Pivot for Excel 如何提供幫助。

如何使用 Power Pivot

1) 導入大型數據集

如前所述，Excel 的主要限制之一與處理超大數據集有關。 對我們來說幸運的是，Excel 現在可以將超過一百萬行的限制直接加載到 Power Pivot 中。

為了證明這一點，我生成了一個包含九個不同產品類別和四個地區的體育用品零售商兩年銷售額的樣本數據集。 生成的數據集是 200 萬行。

使用功能區上的數據選項卡，我從 CSV 文件創建了一個新查詢（請參閱下面的創建新查詢）。 此功能以前稱為 PowerQuery，但在 Excel 2016 和 365 中，更緊密地集成到 Excel 的“數據”選項卡中。

創建新查詢

從 Excel 中的空白工作簿到將所有 200 萬行全部加載到 Power Pivot 中，大約需要一分鐘！ 請注意，我可以通過將第一行提升為列名來執行一些輕量級的數據格式化。 在過去幾年中，Power Query 功能已從 Excel 加載項大幅改進為工具欄上“數據”選項卡的緊密集成部分。 Power Query 可以通過其選項套件和自己的語言 M.

2) 從多個來源導入數據

Power Pivot for Excel 的其他主要優勢之一是能夠輕鬆地從多個來源導入數據。 以前，我們中的許多人為我們的各種數據源創建了多個工作表。 通常，此過程涉及編寫 VBA 代碼並從這些不同的源複製/粘貼。 不過，對我們來說幸運的是，Power Pivot 允許您將來自不同數據源的數據直接導入 Excel，而不必遇到上述問題。

使用圖表 1 中的查詢功能，我們可以從以下任何來源中提取：

• 微軟天青
• SQL 服務器
• 太極數據
• Facebook
• 銷售隊伍
• JSON 文件
• Excel 工作簿
• …還有很多

此外，可以在查詢功能或 Power Pivot 窗口中組合多個數據源以集成數據。 例如，您可以通過 Query 將 Excel 工作簿中的生產成本數據和 SQL Server 中的實際銷售結果提取到 Power Pivot 中。 從那裡，您可以通過匹配生產批次數來組合這兩個數據集以產生每單位毛利率。

3) 使用大型數據集

Power Pivot for Excel 的另一個關鍵優勢是能夠操縱和使用大型數據集以得出相關結論和分析。 我將通過下面的一些常見示例讓您了解該工具的強大功能。

措施

Excel 愛好者無疑會同意，數據透視表既是最有用的一種，同時也是我們執行的最令人沮喪的任務之一。 在處理更大的數據集時尤其令人沮喪。 幸運的是，Power Pivot for Excel 允許我們在處理更大的數據集時輕鬆快速地創建數據透視表。

在下圖中，標題為“創建度量” ，請注意 Power Pivot 窗口是如何分成兩個窗格的。 頂部窗格包含數據，底部窗格包含度量。 度量是在整個數據集上執行的計算。 我通過在突出顯示的單元格中輸入了一個度量值。

 Total Sales:=SUM('Accounting Data'[Amount])

這將創建一個新的度量值，該度量值在 Amount 列中求和。 同樣，我可以在下面的單元格中鍵入另一個度量

Average Sales:=AVERAGE('Accounting Data'[Amount])

創建度量

從那裡，觀察在大型數據集上創建熟悉的數據透視表有多快。

創建數據透視表

維度表

作為使用 Excel 的金融分析師，我們擅長使用複雜的公式來使技術符合我們的意願。 我們掌握了VLOOKUP 、 SUMIF ，甚至是可怕的INDEX(MATCH()) 。 但是，通過使用 Power Pivot，我們可以將其中的大部分內容扔到窗外。

將用戶創建的表添加到 Power Pivot 模型

為了演示此功能，我創建了一個小型參考表，其中我將每個類別分配給一個類型。 通過選擇“添加到數據模型”，此表將加載到 Power Pivot（請參閱上面的將用戶創建的表添加到 Power Pivot 模型）。

我還創建了一個日期表來與我們的數據集一起使用（請參閱下面的創建日期表）。 Power Pivot for Excel 可以輕鬆快速地創建日期表，以便按月、季度和星期幾進行合併。 用戶還可以創建更自定義的日期表，以按週、會計年度或任何特定於組織的分組進行分析。

創建日期表

計算列

除了度量之外，還有另一種類型的計算：計算列。 Excel 用戶會很樂意編寫這些公式，因為它們與在數據表中編寫公式非常相似。 我在下面創建了一個新的計算列（請參閱下面的創建計算列），它按金額對會計數據表進行排序。 低於 50 美元的銷售額被標記為“小”，而所有其他銷售額被標記為“大”。 公式感覺不直觀嗎？

創建計算列

關係

然後，我們可以使用圖表視圖在會計數據表的類別字段和類別表的類別字段之間創建關係。 此外，我們可以定義會計數據表的銷售日期字段和日曆表的日期字段之間的關係。

定義關係

現在，不需要任何SUMIF或VLOOKUP函數，我們可以創建一個按年份計算總銷售額的數據透視表，然後鍵入事務大小的切片器。

使用關係的數據透視表

或者，我們可以使用新的日曆表為一周中的每一天創建一個平均銷售額圖表。

使用關係的數據透視圖

雖然這個圖表看起來很簡單，但令人印象深刻的是，它只用了不到 10 秒的時間就創建了超過 200 萬行數據的合併，而無需向銷售數據添加新列。

在能夠執行所有這些合併報告方案的同時，我們始終可以深入了解各個行項目。 我們保留高度精細的數據。

高級功能

到目前為止，我展示的大部分分析都是相對簡單的計算。 現在，我想展示這個平台的一些更高級的功能。

時間智能

通常，當我們檢查財務結果時，我們希望將其與上一年的可比時間框架進行比較。 Power Pivot 具有一些內置的時間智能功能。

 Same Period Last Year Sales:=CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[Date])) YOY Sales Growth:=if(not(ISBLANK([Same Period Last Year Sales])),([Total Sales]/[Same Period Last Year Sales])-1,BLANK())

例如，將上面的兩個度量添加到 Power Pivot 中的記帳數據表中，我只需單擊幾下即可生成以下數據透視表。

時間智能數據透視表

粒度不匹配

作為一名金融分析師，我經常需要解決的一個問題是粒度不匹配。 在我們的示例中，實際銷售數據顯示在類別級別，但讓我們準備一個僅在季節性級別上的預算。 為了進一步加劇這種不匹配，我們將編制季度預算，即使銷售數據是每天的。

不匹配的粒度 - 預算表

使用 Power Pivot for Excel，這種不一致很容易解決。 通過在數據庫命名法中創建兩個額外的參考表或維度表，我們現在可以創建適當的關係來分析我們的實際銷售額與預算金額。

不匹配的粒度 - 關係

在 Excel 中，以下數據透視表可以快速組合在一起。

不匹配的粒度——預算與實際結果

此外，我們可以定義新的度量來計算實際銷售額和預算銷售額之間的差異，如下所示：

 Actual-to-Budget Variance:=DIVIDE([Total Sales],[Total Budgeted Sales])-1

使用此度量，我們可以在數據透視表上顯示方差。

不匹配的粒度 - 方差結果

佔總數的百分比

最後，讓我們檢查特定類別的銷售額佔所有銷售額的百分比（例如，類別對整體銷售額的貢獻），以及特定類別的銷售額占同一類型的所有銷售額的百分比（例如，類別對季節性類型的貢獻）銷售量）。 我創建了以下兩個度量：

 Total Sales as Percent of All Sales:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data')) Total Sales as Percent of Type:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'[Category]))

這些度量現在可以部署在新的數據透視表中：

佔總數的百分比

請注意如何在類別和季節性類型級別執行計算。 我喜歡在如此龐大的數據集上快速輕鬆地執行這些計算。 這些只是 Power Pivot 優雅和強大計算能力的幾個例子。

壓縮

另一個好處是文件大小縮小。 原始文件大小為 91MB，現在小於 4MB。 那是原始文件的 96% 的壓縮。

文件大小

這是怎麼發生的？ Power Pivot 使用 xVelocity 引擎壓縮數據。 簡單來說，數據存儲在列中而不是行中。 這種存儲方法允許計算機壓縮重複值。 在我們的示例數據集中，只有四個區域在所有 200 萬行中重複。 Power Pivot for Excel 可以更有效地存儲這些數據。 結果是對於具有許多重複值的數據，存儲這些數據的成本要低得多。

需要注意的一點是，我在此示例數據集中使用了整美元的金額。 如果我包含兩個小數點來反映美分，壓縮效果將減少到原始文件大小的 80%，仍然令人印象深刻。

SSAS 表格

Power Pivot 模型還可以擴展到整個企業。 假設您構建了一個 Power Pivot 模型，該模型開始在組織中獲得許多用戶，或者數據增長到一千萬行，或者兩者兼而有之。 此時，您可能不希望 30 個不同的用戶刷新模型或進行更改。 該模型可以無縫轉換為 SSAS 表格。 所有的表格和關係都被保留了，但現在您可以控制刷新頻率，為不同的用戶分配角色（例如，只讀、讀取和處理），並且只部署一個鏈接到表格模型的小型 Excel 前端。 結果是您的用戶隨後可以使用小型工作簿訪問已部署的表格模型，但無法訪問公式和度量。

4) 數據可視化與分析

立方體公式

我的客戶不斷提出的要求之一是我創建符合嚴格定義的佈局的報告。 我有客戶要求特定的列寬、RGB 顏色代碼以及預定義的字體名稱和大小。 考慮以下儀表板：

CUBE 公式嵌入

如果我們所有的銷售都使用 Power Pivot for Excel，我們如何在不生成數據透視表的情況下填充銷售數字？ 使用 CUBE 公式！ 我們可以在任何 Excel 單元格中編寫 CUBE 公式，它將使用我們已經構建的 Power Pivot 模型執行計算。

例如，在“2016 年總銷售額”下的單元格中鍵入以下公式：

=立方值（“ ThisWorkbook 數據模型"," [措施].[總銷售額] "," [日曆].[年份].[2016] ")

公式的第一部分以黃色突出顯示，指的是 Power Pivot 模型的名稱。 通常，對於較新版本的 Power Pivot for Excel，它通常是 ThisWorkbookDataModel。 綠色部分定義我們要使用總銷售額度量。 藍色部分指示 Excel 僅篩選銷售日期為 2016 年的行。

在幕後，Power Pivot 構建了一個包含數據、計算列和度量的在線分析處理 (OLAP) 多維數據集。 這種設計允許 Excel 用戶通過直接使用 CUBE 函數獲取數據來訪問數據。 使用 CUBE 公式，我已經能夠構建符合預定義佈局的完整財務報表。 此功能是使用 Power Pivot for Excel 進行財務分析的亮點之一。

電源 BI

Power Pivot for Excel 的另一個優勢是您可以快速獲取您構建的任何 Power Pivot 工作簿並將其快速轉換為 Power BI 模型。 通過將 Excel 工作簿直接導入 Power BI Desktop 應用或 Power BI Online，您可以分析、可視化並與組織中的任何人共享您的數據。 從本質上講，Power BI 是 Power Pivot、PowerQuery 和 SharePoint 合而為一。 下面，我通過將以前的 Power Pivot for Excel 工作簿導入 Power BI 桌面應用程序來創建儀表板。 注意界面的交互性：

電源 BI

Power BI 的一大優點是自然語言問答。 為了演示，我將 Power BI 模型上傳到我的在線 Power BI 帳戶。 在網站上，我可以提出問題，Power BI 在我鍵入時構建適當的分析：

自然語言問答

這種類型的查詢功能使用戶能夠以比 Excel 更簡單的方式詢問數據模型問題並與數據交互。

Power BI 的另一個好處是 Microsoft 的開發人員會不斷發布更新。 每月推出許多用戶要求的新功能。 最重要的是，它是從 Power Pivot for Excel 的無縫過渡。 因此，您投入學習 DAX 公式的時間可以部署在 Power BI 中！ 對於需要在不同設備上與許多用戶分享其分析的分析師來說，Power BI 可能值得探索。

最佳實踐

一旦開始，您應該遵循一些最佳實踐。

首先是要深思熟慮地首先決定要導入什麼。 你會使用銷售人員的家庭住址嗎？ 在本工作簿的上下文中，我是否需要知道我的客戶的電子郵件地址？ 如果目標是將數據聚合到儀表板中，則某些可用數據對於這些計算將不是必需的。 花時間整理傳入的數據將在以後擴展數據集時大大緩解問題和內存使用。

另一個最佳做法是記住 Power Pivot 不是 Excel。 在 Excel 中，我們習慣於通過不斷向右擴展工作表來創建計算。 如果我們限制這種對明顯命運的渴望，Power Pivot for Excel 可以最有效地處理數據。 學習在底部窗格中編寫度量，而不是不斷地在數據右側創建計算列。 這種習慣將確保更小的文件大小和更快的計算。

最後，我建議使用簡單的英文名稱進行度量。 這個我花了很長時間才採用。 最初幾年我花了一些時間來編造SumExpPctTotal類的名稱，但是一旦其他人開始使用相同的工作簿，我就有很多解釋工作要做。 現在，當我開始一個新的工作簿時，我使用諸如Expense Line Item as Percent of Total Expenses類的度量名稱。 雖然名稱較長，但對其他人來說更容易使用。

實際用例

在本文中，我僅介紹了 Power Pivot for Excel 允許您在普通 Excel 之外邁出重要一步的幾種方法。 我認為強調一些我發現 Power Pivot for Excel 非常有用的實際用例會很有用。

這裡有一些：

• 分析大型資產組合在不同時間範圍內的表現：由於 Excel 的 Power Pivot 允許我們定義將某個時間段與前一個時間段進行比較的度量，我們可以快速獲得季度環比、同比、只需編寫幾個度量值，所有這些都在滾動的基礎上進行。
• 使用自定義聚合級別匯總會計數據：通過按名稱、類別和財務報表識別每個總帳行項目，可以快速創建包含適當行項目的報告。
• 連鎖店可以識別同店銷售額：使用一張映射商店何時上線的表格，可以在同店的基礎上比較財務結果。
• 查明銷售表現出色和表現不佳的企業：可以創建數據透視表，按銷售額、毛利率、生產時間框架等突出顯示前五個 SKU 和後五個 SKU。
• 零售商可以定義使用 4-4-5 配置的日曆表：使用自定義日期表，零售商可以輕鬆地將每一天分配到特定的 4-4-5 月份，然後可以將每日銷售結果滾動到相應的月份。

從笨重的電子表格到現代工作簿

作為金融分析師，我們需要對不斷擴展的數據集執行複雜的計算。 由於 Excel 已經是默認的分析工具，Power Pivot 的學習曲線很容易，並且許多功能都反映了 Excel 的本機功能。

通過使用 CUBE 函數，Power Pivot for Excel 可以無縫融入您現有的 Excel 工作簿。 計算效率增益不容忽視。 假設處理速度提高 20%（這是保守的），金融分析師每天在 Excel 中花費 6 小時可以節省 300 小時！

此外，我們現在可以分析比以前使用傳統 Excel 大得多的數據集。 借助高效設計的模型，我們可以輕鬆獲得 10 倍於傳統 Excel 之前允許的數據量，同時保持快速的分析敏捷性。 借助將模型從 Power Pivot 轉換為 SSAS 表格的能力，可以處理的數據量是我們在 Excel 中可以實現的 100-1,000 倍。

Power Pivot for Excel 能夠對大量數據執行閃電般的快速計算，並且仍然保留深入研究細節的能力，可以將財務分析從笨重的電子表格轉變為現代工作簿。

如果您有興趣嘗試 Power Pivot for Excel，以下是一些有用的材料，可幫助您入門。

有用的參考和指南

Collie, R. 和 Singh, A. (2016)。 Power Pivot 和 Power BI：Excel 2010-2016 中的 DAX、Power Query、Power BI 和 Power Pivot 用戶指南。 美國：神聖宏！ 圖書。

Ferrari, A. 和 Russo, M. (2015)。 DAX 權威指南：Microsoft Excel、SQL Server Analysis Services 和 Power BI 的商業智能。 美國：微軟出版社，美國。