Excel 中的自助式 Power BI 實作：Power Pivot 篇

在上篇文章中我們介紹了 Power Query，而今天讓我們承襲上篇文章的範例，繼續進到續集，來介紹第二個主題：Power Pivot。

有了 Power Pivot，您可以從各種資料來源建立自己的資料模型，並依照本身需要確切設定模型與結構，同時還可以經常視情況從原始來源重新整理資料。現在將資料載入 Excel 優異的自助資料模型功能：Power Pivot。 Power Pivot 可讓您在 Excel 中直接建立及管理資料表和關聯性的集合。

Chapter 1：資料載入

我們將附加的 S&P 500 NYSE 和 NASDAQ 每日資料載入到資料模型，但若同時也希望載入基本 S&P 500 資料表。要做到這一點非常容易，只要選取載入了 S&P 500 清單的 Excel 工作表索引標籤，然後從工具列上的 [Power Pivot] 索引標籤選取 [新增至資料模型] 。

當新增資料表到資料模型時，Power Pivot 會在另一個視窗中開啟，並顯示新增至模型的資料表。我們也可以從 [Power Pivot] 工具列中選取 [管理] 圖示，來開啟 Power Pivot 視窗。

在 Power Pivot 中，資料模型中的資料表會以索引標籤形式顯示，和資料表在 Excel 中的顯示方式非常類似。我們先將剛才新增的資料表重新命名為 SP 500 ( 按兩下該索引標籤，並輸入新名稱)。

Chapter 2：精簡您的資料

當逐一查看模型中的資料時，我們發現遺漏了重要的觀點：年度績效。這時可以抽出第一天的資料和最後一天的資料 (例如價格)，然後直接比較差異。 如此非常簡單且快速。

當我們已製作了合併及附加所有每日資料的查詢，也就是命名為 NYSE – NASDAQ – SP500 附加 的查訊。若我們需要相同查詢的精簡版資料，精簡程度為僅包含第一天和最後一天的資料。這時先要回到 Excel 中，找出查詢 (在 Power Pivot 和 Excel 之間切換非常簡單，只需要選取 Excel 視窗即可。 不需要關閉 Power Pivot 視窗)。

回到 Excel 後，我們先重啟 [活頁簿查詢] 窗格 (選取 [Power Query] > [管理查詢] > [活頁簿] ，重新開啟窗格)。會發現再次選取 [活頁簿] 功能區按鈕會關閉 [活頁簿查詢] 窗格，但我們希望窗格保持開啟，因此再按一下 [活頁簿] 按鈕。

在 [活頁簿查詢] 窗格中找到查詢，接著瀏覽到 [資料表工具] 中的 [查詢] 索引標籤，然後選取 [複製] 。如此可使用先前建立的查詢開始作業，首先附加合併的 NYSE 和 NASDAQ 資料，然後篩選到僅包含一年當中第一個與最後一個交易日。

複製的查詢會顯示在 [活頁簿查詢] 窗格底部，名稱為 NYSE – NASDAQ – SP500 附加 (2) 。 當游標停留在該查詢上時，會出現包含資料預覽的飛出式視窗。我們從飛出式視窗的底部選取了 [編輯查詢] 。

[查詢視窗] 隨即出現，可以在視窗中設定，限制 [NewColumn.date] 欄位只能包含第一個交易日。 當瀏覽到 [NewColumn.date] 欄位，發現該欄位的資料類型並非使用 [日期] 格式 。

要修正這一點很容易，在 [查詢編輯器] 中即可輕鬆完成。選取 [NewColumn.date] 資料行，然後從功能區 [轉換] 區段中的 [資料類型] 下拉式清單中選取 [日期] 。

資料類型設為 [日期] 後，便可以從該資料行篩選日期，讓資料僅包含一年當中的第一個交易日。

接著只需要建立查詢、取得第一天的資料、複製查詢以取得最後一天的資料，然後將兩個查詢中的資料合併到單一資料表中即可進行所要的計算 (第一個和最後一個交易日的資料需要置於模型中的 單一 筆記錄或列中)。現在，讓我們釐清這些步驟，這樣一來，您就可以使用自己的 Excel 活頁簿順利地跟著操作。

1. 從 [NewColumn.date] 欄中只選取第一個交易日 (1/2/2009)，然後按一下 [確定] 。

2. 在 [查詢編輯器] 中，從每個欄移除 NewColumn. 字首。 如此可以增進欄位的可讀性。 以滑鼠右鍵按一下資料行，然後從出現的功能表中選擇 [重新命名...] 。

3. 移除下列資料行 (範例中我們不需要這些資料行)： [SEC 存檔] 、 [初次新增日期] 、 [stock_symbol] (重複資料行)、 [stock_gain_loss_dollar] 、 [stock_gain_loss_percent] 、 [dollar_volume] 和 [stock_volume] 。

4. 重新命名查詢為 [第一個交易日] ，並清除 [載入至工作表] 核取方塊。

5. 從功能區選取 [套用並關閉] 。 查詢會出現在 [活頁簿查詢] 窗格中。

6. 以滑鼠右鍵按一下 [活頁簿查詢] 窗格中的 [第一個交易日] 查詢，然後選取 [複製] 。 複製的查詢會新增至 [活頁簿查詢] 窗格，名稱為 [第一個交易日 (2)] 。 您可能需要在 [活頁簿查詢] 窗格中向下捲動，以查看該查詢。

7. 以滑鼠右鍵按一下 [第一個交易日 (2)] ，然後從出現的功能表中選取 [編輯查詢] 。

接下來的幾個步驟比較難處理，但可以充分展現 Power Query 的靈活性和強大的功能。 這些步驟也能說明 Power Query 一項優異的功能，以及它是如何處理查詢的重塑與篩選步驟。

當選取 [第一個交易日 (2)] 的 [編輯查詢] 時，會出現下列畫面。 請注意 [日期] 欄位，以及 [查詢設定] 窗格中的 [套用步驟] 區段，其中最後項目 ( [RemovedColumns] ) 已選取。

當選取上述其中一個步驟時，查詢中的資料會自動回復為查詢重塑過程中 該 時間點的狀態 (且未套用篩選)。 例如，當選取 [來源] ( [套用步驟] 中第一個套用的步驟) 時，資料會回復為將該步驟套用到查詢時的狀態。

您會發現資料行名稱回復了，現在名稱中出現了 NewColumn. 前置詞。 您也會留意到日期欄位回復為一般資料類型，且 (雖然不太容易看出來) 日期欄位沒有套用任何篩選，因此所有交易日都出現在資料集中。

當選取 [套用步驟] 中的第四個步驟 ( [RenamedColumns] 步驟) 時，資料會顯示為資料重塑過程中進行該步驟時的狀態。 在此時，只剩下移除未使用資料行的變更。

若希望為這個查詢進行變更，讓它篩選出一年當中的最後一個交易日，而非一年當中的第一個交易日。 僅須採取下列步驟，從我們先前結束的地方開始：

8. 在 [查詢設定] 窗格內 [套用步驟] 區段中的 [FilteredRows] 項目右側，按一下齒輪圖示。 下列視窗隨即出現。

9. 在顯示 1/2/2009 的欄位中選取下拉式箭號，然後選取最後一個項目，亦即 12/31/2009。 選取 [確定] 。

10. 選取 [套用步驟] 中的最後一個項目 ( [RemovedColumns] 步驟)。

11. 將查詢重新命名為 [最後一個交易日] 。

12. 選取 [套用並關閉] 。

接下來，合併這兩個查詢。 以下是我們會採取的步驟：

1. 從功能區上的 [Power Query] 索引標籤中選取 [合併] 。

2. 會顯示 [合併] 視窗。 將 [第一個交易日] 和 [最後一個交易日] 查詢指定為要合併的資料表，然後選取 [行情指示器代號] 為相符資料行。 選取 [確定] 。

3. 和過去一樣，來自合併查詢中的資料行會顯示為資料表，位於查詢結尾名稱為 [NewColumn] 的資料行下方。 按一下雙箭號圖示，將該資料表展開成個別資料行。

4. 將查詢重新命名為 [SP500 年度資料] 。

5. 選取 [載入至資料模型] 旁的核取方塊，然後選取 [套用並關閉] 。

完成後，我們的資料模型中現在有了包含第一和最後一天收盤價資料的新資料表。 資料模型中的資料表裡包含了一些不需要的資料行，以及一些希望重新命名的資料行。不成問題，因為這些作業都可以在 Power Pivot 中完成。

我們會從 Power Pivot 中進行下列變更：

1. 將 [日期] 資料行重新命名為 [第一天的日期] ，並將 [NewColumn.date] 資料行重新命名為 [最後一天的日期] 。

2. 將 [stock_price_close] 重新命名為 [第一天收盤價] ，並將 [NewColumn.stock_price_close] 重新命名為 [最後一天收盤價] 。

3. 從 [GICS 類股] 和 [GICS 子產業] 的資料行名稱中移除 GICS。

4. 刪除下列重複資料行 (也可以在 Power Query 中進行此作業)： [關鍵] 、 [NewColumn.行情指示器代號] 、 [NewColumn.公司] 、 [NewColumn.GICS 類股] 、 [NewColumn.GICS 子產業] 、 [NewColumn.總部地址] 、 [NewColumn.關鍵] 以及 [NewColumn.交易] 。

重新命名這些資料行會在稍後當我們從資料建立報表時有所助益。

Chapter 3：建立運算

現在，我們已在資料模型中備妥年度資料，可以 (使用金額及百分比為單位) 計算各股票的整體漲幅。 在 Power Pivot 中，您可以在任何資料表中建立新資料行，並在這些資料行中使用可執行多種不同功能的公式。 這些計算通常稱為計算欄位。

在 Power Pivot 中，計算欄位會使用 資料分析運算式 (DAX) 公式。 DAX 公式非常類似 Excel 中的公式，能同時在 Power Pivot 和樞紐分析表中運作。 如果您熟知如何在 Excel 中建立公式，使用 DAX 對您不會有任何難度。

我們先建立的第一個公式，會以金額為單位計算年度漲幅或跌幅。首先，在 [新增資料行] 中選取儲存格，接著在 DAX 資料編輯列中輸入公式。當輸入時，Power Pivot 會根據資料模型中可用的資料表和欄位做出建議。

公式完成時，會針對資料表中的每一列計算結果。我們新增了一些其他資料行，包括一年當中的漲幅百分比，並為各資料行套用適當的資料類型和格式 。以下是建立的計算 (公式列於括弧中)：

• 上漲或下跌 - 金額 ( =[Last Day Close]-[First Day Close] )
• 上漲或下跌 - 百分比 ( =([Last Day Close]-[First Day Close])/[First Day Close] )
• 類股 ( =RANKX('SP500 Annual Data',[Gain or Loss - dollars],) )

除了新資料行，若想要建立可提供彙總合計等功能的計算欄位，可透過選取 Power Pivot 計算區域中的儲存格來達到這個目的，計算區域是介於資料表資料和索引標籤之間的儲存格集合。 計算欄位可以使用計算區域置於任何儲存格。

我們建立了下列計算欄位。如果您在自己的活頁簿中照著做，且使用相同的資料行名稱，您可以直接複製以下各項內容並貼到計算區域中的儲存格：

• 上漲或下跌總和 - 金額：=SUM([Gain or Loss - dollars])
• 平均漲幅 - 金額：=AVERAGE([Gain or Loss - dollars])
• 平均漲幅 - 百分比：=AVERAGE([Gain or Loss - percent])
• 表現最佳股票 - 金額：=MAX([Gain or Loss - dollars])
• 表現最差股票 - 金額：=MIN([Gain or Loss - dollars])
• 表現最佳股票 - 百分比：=MAX([Gain or Loss - percent])
• 表現最差股票 - 百分比：=MIN([Gain or Loss - percent])

Chapter 4：建立關聯

在 Power Pivot 中，您也可以定義資料表之間的關係。 關聯會在資料表之間建立連線，是以各資料表中包含類似或相同資料的資料行為基礎。 關聯可讓您建立包含來自相關資料表資料的報表。

以行情指示器代號為基礎，在 SP 500 資料表和資料模型中的另外兩個資料表之間建立關聯。可以在 Power Pivot 的 [圖表檢視] 中使用簡單的拖放動作完成這項作業。在 Power Pivot 中，是從 [常用] 功能區的 [檢視] 區段選取 [圖表檢視] 。

為建立關聯，會將主要資料表的欄位拖曳到應建立關聯之資料表中的相應欄位。兩個資料表間會顯示線條，代表兩者間的關聯。可以選取線條，為相關聯的欄位加上醒目提示。

將 SP500 年度資料 資料表中的 [行情指示器代號] 欄位，拖曳到 SP500 資料表中的 [行情指示器代號] 欄位，以建立關聯。 我們看了一下 [圖表檢視] 中另一個資料表的名稱，該名稱相當長：NYSE___NASDAQ___SP500_Append，所以決定將它重新命名，這項作業可以直接在 [圖表檢視] 內進行 (按兩下資料表的名稱，然後輸入新的名稱：SP500 每日資料 )。

將 SP500 每日資料 資料表中的 [行情指示器代號] 欄位拖曳到 SP 500 資料表中的 [行情指示器代號] 欄位，建立了另一個關聯。 選取資料表間的關聯線條，會為關聯加上醒目提示。我們的 Power Pivot [圖表檢視] 現在的外觀正如以下畫面所示。

Chapter 5：建立階層

在 Power Pivot 中，所謂的階層是指共用邏輯上下關聯的資料元素群組。 例如，地理階層可能是「省/市」、「縣/市」和「鄉/鎮/市/區」；「省/市」是高於「縣/市」的階層 (且包含多個「縣/市」)，而「縣/市」高於「鄉/鎮/市/區」(且包含多個「鄉/鎮/市/區」)。

在 Power Pivot 中使用階層，可以讓您建立可向下切入資料的報表。假設我們認為類股和子產業階層很值得觀察，尤其是如果能建立可供向下切入特定部門資料的報表，會更有意義。 在 Power Pivot 中建立階層的方式很多；我們決定在 [圖表檢視] 中建立階層。

為在 SP500 年度資料 資料表中建立階層，在 [圖表檢視] 中以滑鼠右鍵按一下資料表，此時會出現可供建立階層的功能表。

突然，我們留意到 SP 500 資料表的 [類股] 和 [子產業] 欄位前面仍有 GICS 字詞。 雖然我們並不是要在 SP 500 資料表中建立階層 (而是要在 SP500 年度資料 資料表中建立)，但還是希望能修正這個問題。 進一步檢查後，發現 SP500 每日資料 資料表的欄位也有這個希望去掉的字首。此時可以直接從 [圖表檢視] 中修正這個問題 (按兩下 SP 500 資料表中的 [GICS 類股] 欄位，然後去除 GICS 字首)。我們也為其他三個欄位進行同樣的作業。

現在，可以開始建立階層。選取 [SP500 年度資料] 並將階層命名為 [類股和子產業] ，同時也納入了 SP500 年度資料 資料表中的 [類股] 欄位和 [子產業] 欄位。這些欄位排列的順序構成了階層。

在完成關聯與階層的建立後，現在可以開始建立報表。我們希望製作動態且吸引人的報表，加上許多酷炫的視覺效果，也希望報表是互動式的，這樣一來，當發佈報表時，同事就能夠以有意義或有趣的方式，檢閱並分析資料。 Power View 就是能做到這一點 (更有其他更多優點) 的自助式 BI 功能。

Power Pivot 摘要

在 Power Pivot 中，您可以進行自訂、運用計算和階層擴充功能，以及管理強大的 Excel 資料模型。 Power Pivot 能順暢且自動與 Power Query 搭配運作，也能和其他 Excel 功能配合使用，讓您可以在熟悉的 Excel 環境中 (運用計算和階層) 管理與擴充您的自訂資料庫 (資料模型)。 Power Pivot 包含任何您從 Power Query 納入的資料，以及任何其他您新增至模型的資料。 此外，Power Pivot 中的整個資料模型 (包括資料表、資料行、計算、階層和任何其他自訂內容) 都會在 Power View 中公開為報表可用的元素。

如需瞭解 Power Pivot 和 DAX 的詳細資訊，請參考下列連結：

• PowerPivot：Excel 中的強大資料分析與資料模型
• 教學課程：將資料匯入 Excel 2013 並建立資料模型 (六回教學課程系列中的第一回)
• Power Pivot 中的計算
• Data Analysis Expressions (DAX) 概觀

Comments

• Anonymous
  August 31, 2014
  這是 Power BI 實作系列的第三篇文章囉，前兩篇分別是 Power Query 實作 和 Power Pivot 實作 ，今天我們要來談 Power View。
  在 Power View 中
• Anonymous
  August 31, 2014
  這是 Power BI 實作系列的第三篇文章囉，前兩篇分別是 Power Query 實作 和 Power Pivot 實作 ，今天我們要來談 Power View。
  在 Power View 中
• Anonymous
  September 01, 2014
  這將是 Excel 中的自助式 Power BI 實作系列的最後一篇介紹，我們要來談的是 Power Map (Excel 的最新附加功能之一)，它是以地球為中心的時序地圖繪製功能，可將地理位置資料導入威力強大且引人入勝的
• Anonymous
  September 19, 2014
  資料量每天都在不斷增加，因此，對於能將資料轉為深入見解以利決策進行的自助式商業智慧，需求也日漸增加。然而，在大多數情況下，擷取這些深入見解所需的工具，和資料本身一樣難以處理。 Microsoft Power
• Anonymous
  August 27, 2015
  貼圖看不到
• Anonymous
  November 02, 2015
  微軟技術文章的特色，一堆文字，剩下自己腦補>.0