教學課程：使用 Excel、Power Pivot 和 DAX 擴充資料模型關聯

此教學課程中的各節如下所示：

• 使用 Power Pivot 的 [圖表檢視] 來新增關聯

• 使用計算結果資料行來擴充資料模型

• 建立階層

• 在樞紐分析表中使用階層

• 重點複習和測驗

本教學課程結尾有一項測驗，可供您測驗學習成效。

本系列會使用說明奧運獎牌、主辦國家/地區及各種奧運運動賽事的資料。 本系列中的教學課程如下：

1. 將 Excel 資料匯入及建立資料模型

2. 使用 Excel 擴充資料模型關聯 Power Pivot ，和 DAX

3. 建立以地圖為基礎的 Power View 報表

4. 併入網際網路資料與設定 Power View 報表預設值

5. Power Pivot 說明

6. 建立令人讚嘆的 Power View 報表 - 第 2 部分

建議您依序瀏覽。

這些教學課程中使用Power Pivot啟用 Excel 2013。在 Excel 2013 的詳細資訊，請按一下這裡。如需啟用Power Pivot的指引，請按一下這裡。

使用 Power Pivot 的 [圖表檢視] 來新增關聯

在本節中，您會使用 Microsoft Office Power Pivot in Excel 2013 增益集來擴充模型。 使用 Microsoft SQL Server Power Pivot for Excel 的 [圖表檢視] 可讓您輕鬆建立關聯。 首先，您必須確定您已啟用 Power Pivot 增益集。

附註： Microsoft Excel 2013 中的 Power Pivot增益集是 Office 專業增強版。如需詳細資訊，請參閱啟動 Power Pivot in Microsoft Excel 2013 增益集。

啟用Power Pivot增益集，將Power Pivot新增至 Excel 功能區

啟用 Power Pivot 後，Excel 2013 中就會出現一個名為 POWER PIVOT 的功能區索引標籤。 若要啟用 Power Pivot，請依照下列步驟執行。

1. 前往 [檔案] > [選項] > [增益集]。

2. 在靠近底部的 [管理] 方塊中，按一下 [COM 增益集] > [執行]。

3. 核取 [Microsoft Office Microsoft Excel 2013 中的 Power Pivot] 方塊，然後按一下 [確定]。

Excel 功能區現在就會出現 [POWER PIVOT] 索引標籤。

新增關聯Power Pivot中使用圖表檢視

Excel 活頁簿中含有一個名為「主辦城市」的資料表。 我們透過複製並貼至 Excel 的方法來匯入「主辦城市」，然後將資料格式設為資料表。 若要新增 [主辦城市] 資料表至資料模型，我們必須建立關聯。 我們來利用 Power Pivot 以視覺效果呈現資料模型中的關聯，然後再建立關聯。

1. 在 Excel 中，按一下 [主辦城市] 索引標籤，使其成為使用中的工作表。

2. 在功能區上選取 [POWER PIVOT] > [資料表] > [新增至資料模型]。 這個步驟會將 [主辦城市] 資料表新增至資料模型中。 同時也會開啟 Power Pivot 增益集，以用於執行這項工作中的其餘步驟。

3. 請注意Power Pivot視窗會顯示在模型中，包括主機所有資料表。按一下 [透過幾個資料表。在Power Pivot您可以檢視所有的資料包含您的模型，即使對方未顯示在 Excel 中，例如分項、事件，以及獎牌資料，以及S_Teams，W_ 任何工作表中小組，和運動。
   
   

4. 在Power Pivot視窗中，在 [檢視] 區段中，按一下 [圖表檢視]。

5. 若要調整圖表大小，使您可以看到圖表中的所有物件使用滑桿。拖曳其標題列，以便您可見及旁另一個位置來重新排列資料表。請注意四個資料表不相關資料表的其餘部分：主機、事件、 W_Teams和S_Teams。
   
   

6. 請注意，[獎牌] 資料表和 [競賽細項] 資料表兩者皆有一個名為 [DisciplineEvent] 的欄位。 進一步檢查後，您判斷 [競賽細項] 資料表中的 [DisciplineEvent] 欄位所包含的是唯一且非重複的值。

1. 建立 [獎牌] 資料表和 [競賽細項] 資料表之間的關聯。 在 [圖表檢視] 時，將 [DisciplineEvent] 欄位從 [競賽細項] 資料表拖曳到 [獎牌] 中的 [DisciplineEvent] 欄位。 兩者之間便會出現一條線，表示已建立關聯。

2. 按一下 [事件] 和 [獎牌所連接的線條。下列畫面所示定義關聯，醒目提示的欄位。
   
   
   

3. 若要將 [主辦城市] 連接到資料模型，我們需要一個欄位，其中包含能唯一識別 [主辦城市] 資料表中各資料列的值。 然後我們可以搜尋資料模型，查看該筆相同資料是否也存在於其他資料表中。 若在 [圖表檢視] 中查看，我們無法執行此動作。 選取 [主辦城市] 後，請切換回 [資料檢視]。

4. 在檢查後的欄，我們發現主機沒有唯一值的資料行。我們必須建立並使用計算結果的欄及資料分析運算式 (DAX)。

如果資料模型中的資料有建立關聯必要的所有欄位及用於在 Power View 或樞紐分析表以視覺效果呈現的混合資料，就會十分方便。 不過，資料表不一定都能具備所有要件，因此下一節將為您說明如何使用 DAX 建立新資料行，以便用於建立資料表之間的關聯。

使用計算結果資料行來擴充資料模型

若要在 [主辦城市] 資料表與資料模型之間建立關聯，從而擴充資料模型以包含 [主辦城市] 資料表，則 [主辦城市] 必須有能唯一識別各資料列的欄位。 此外，該欄位必須對應於資料模型中的某個欄位。 那些對應欄位 (每個資料表中有一個) 就是能讓資料表資料建立關聯的關鍵所在。

[主辦城市] 資料表中沒有這類欄位，因為您需要建立。若要保留的資料模型完整性，您無法使用Power Pivot編輯或刪除現有的資料。您不過，可以使用現有的資料為基礎的導出的欄位，以建立新的資料行。

透過瀏覽 [主辦城市] 資料表，然後再查看其他資料模型資料表，我們找到可在 [主辦城市] 建立唯一欄位的理想選擇，然後與資料模型中的資料表建立關聯。 這兩個資料表都需要新的計算結果資料行，才能符合建立關聯的必要需求。

在 [主辦城市] 中，我們可以將 [年度] 欄位 (奧運賽事舉辦年份) 與 [季節] 欄位 (夏季或冬季) 結合，以建立唯一的計算結果資料行。 [獎牌] 資料表中也有 [年度] 欄位和 [季節] 欄位，因此，如果我們在上述各個資料表中建立了結合 [年度] 和 [季節] 欄位的計算結果資料行，就能在 [主辦城市] 與 [獎牌] 之間建立關聯。 以下畫面顯示 [主辦城市] 欄位，其中選取了 [年度] 和 [季節] 欄位

使用 DAX 建立計算結果資料行

我們從 [主辦城市] 資料表開始著手。 目標就是在 [主辦城市] 資料表中建立計算結果資料行，然後再於 [獎牌] 資料表中將計算結果資料行用於建立兩者之間的關聯。

您可以在 Power Pivot 中，使用資料分析運算式 (DAX) 來建立計算。 DAX 是 Power Pivot 和樞紐分析表的公式語言，是針對 Power Pivot 中可用的關聯式資料與關聯式分析而設計。 您可以在新的 Power Pivot 資料行，以及在 Power Pivot 的 [計算區域] 中建立 DAX 公式。

1. 在 Power Pivot 中，選取 [常用] > [檢視] > [資料檢視]，確定已選取 [資料檢視]，而非處於 [圖表檢視]。

2. 選取 [主辦城市] 資料表中Power Pivot。相鄰區域中的現有資料行是空白資料行標題為 [加入資料行。Power Pivot提供該資料行做為預留位置。有許多方法可以將新的資料行新增至Power Pivot，一個是，只要選取 [有標題的 [加入資料行的空白欄中的表格。
   
   
   

3. 在資料編輯列中，輸入下列 DAX 公式。CONCATENATE 函數合併兩個或多個欄位成一份。當您輸入時，自動完成幫助您輸入的完整名稱的欄和表格，，並列出所提供的功能。您可以使用] 索引標籤，選取 [自動完成建議]。您也可以按一下資料行時輸入您的公式和Power Pivot插入公式的欄名稱。
   
   =CONCATENATE([Edition],[Season])

4. 公式建立完畢後，按下 Enter 以接受公式。

5. 隨後就會在計算結果資料行中填入所有資料列的值。 如果向下捲動瀏覽資料表，就會發現每個資料列都是獨一無二的，因此我們已順利地在 [主辦城市] 資料表中建立能唯一識別各資料列的欄位。 這樣的欄位就稱為主索引鍵。

6. 讓我們將該計算結果資料行重新命名為 EditionID。 任何資料行都可以重新命名，只要在資料行上按兩下，或在資料行上按滑鼠右鍵後選擇 [重新命名資料行] 即可。 完成後，Power Pivot 中的 [主辦城市] 資料表看起來就會如同以下畫面。
   
   

[主辦城市] 資料表已備妥。 接下來，我們要在 [獎牌] 中建立計算結果資料行，其格式必須與我們在 [主辦城市] 中建立的 [EditionID] 資料行格式相符，這樣我們才能在兩者之間建立關聯。

1. 首先，在 [獎牌] 資料表中建立新資料行，如同對 [主辦城市] 執行的動作一樣。 在 Power Pivot 中，選取 [獎牌] 資料表，然後按一下 [設計] > [資料行] > [加入]。 請注意，[加入資料行] 已選取。 這與直接選取 [加入資料行] 的效果相同。

2. [獎牌] 中的 [年度] 資料行，其格式與 [主辦城市] 中的 [年度] 資料行格式不同。 將 [年度] 資料行與 [季節] 資料行結合或串連起來以建立 [EditionID] 資料行之前，我們必須先建立一個修正 [年度] 資料行格式的中繼欄位。 在資料表上方的公式列中，輸入以下 DAX 公式。

   = YEAR([Edition])
   

3. 公式建立完畢後，請按下 Enter。 系統就會根據您所輸入的公式在計算結果資料行中填入所有資料列的值。 如果您將此資料行與 [主辦城市] 中的 [年度] 資料行相比，就會發現這兩個資料行的格式相同。

4. 以滑鼠右鍵按一下 [CaculatedColumn1] 並選取 [重新命名資料行]，將該資料行重新命名。輸入「年份」，然後按下 Enter。

5. 建立新資料行後，Power Pivot 就會新增另一個名為 [加入資料行] 的預留位置資料行。 接下來，我們要建立 [EditionID] 計算結果資料行，所以請選取 [加入資料行]。 在公式列中輸入以下 DAX 公式，然後按下 Enter。
   
   =CONCATENATE([Year],[Season])

6. 按兩下 [CalculatedColumn1]，然後輸入 EditionID，以重新命名該資料行。

7. 以遞增順序欄排序。[獎牌] 表格中Power Pivot現在看起來類似下列畫面。
   
   
   

請注意，[獎牌] 資料表的 [EditionID] 欄位中會有許多值重複。 沒關係，這是預期中的情況，因為在每一年度的奧運 (現在以 EditionID 值表示) 都會頒發許多獎牌。 [獎牌] 資料表中的唯一值就是每一面頒贈的獎牌。 [獎牌] 資料表中各項記錄的唯一識別碼 (也就是其指定的主索引鍵) 就是 [MedalKey] 欄位。

下一步是建立 [主辦城市] 和 [獎牌] 之間的關聯。

使用計算結果資料行建立關聯

接下來，我們要使用我們所建立的計算結果資料行來建立 [主辦城市] 和 [獎牌] 之間的關聯。

1. 在 [ Power Pivot ] 視窗中，從功能區中選取常用 > 檢視 > 圖] 檢視。下列畫面所示，您也可以切換格線檢視與使用的按鈕，底部的 [PowerView] 視窗中的 [圖表] 檢視之間。
   
   

2. 展開 [主辦城市]，讓您可以檢視其所有欄位。我們建立作為主機資料表主索引鍵 （唯一且非重複欄位） 的 [EditionID] 欄，並啟用建立他們之間的關聯的 [獎牌] 表格中建立的 EditionID 資料行。我們需要找出兩個並建立關聯。Power Pivot提供在功能區]尋找功能，讓您可以搜尋您的資料模型的相對應的欄位。下列畫面顯示 [尋找中繼資料] 視窗中，使用 [尋找目標] 欄位中輸入 EditionID。
   

3. 調整 [主辦城市] 資料表的位置，使其位於 [獎牌] 資料表旁邊。

4. 拖曳至 [EditionID] 欄中主機獎牌[EditionID] 欄。Power Pivot建立根據 [EditionID] 欄中，表格之間的關聯，並繪製一條線之間的兩個資料行，表示關聯性。
   
   

在此區段中，您學到新的技巧，將新的資料行，建立使用 DAX 導出資料行，並使用該資料行以建立新資料表之間的關聯。[主辦城市] 資料表現在整合至資料模型，以及其資料可Sheet1中樞紐分析表。您也可以使用相關聯的資料來建立其他樞紐分析表，樞紐分析圖的 Power View 報表，以及執行其他功能。

建立階層

大部分的資料模型所含的資料本身就有階層結構。 常見的範例包括行事曆資料、地理資料和產品類別。 在 Power Pivot 中建立階層是很實用的，因為您可以將一個項目拖曳到報表 (階層)，而不需要不斷組合與排列相同的欄位。

奧運資料也具有階層結構。 就運動、分項與競賽細項方面，瞭解奧運階層會很有幫助。 每一項運動都會有一或多個相關聯的分項 (有時候數量會很多)。 而每一個分項也都會有一或多個競賽細項 (同樣地，有時候一個分項會有許多競賽細項)。 其階層如下圖所示。

在本節中，您會在本教學課程所使用的奧運資料中建立兩個階層。 然後，使用這些階層，看看階層如何簡化樞紐分析表及 Power View (於後續教學課程) 中的資料整理作業。

建立「運動」階層

1. 在 Power Pivot 中，切換至 [圖表檢視]。 展開 [競賽細項] 資料表，好讓您更輕鬆地檢視其所有欄位。

2. 按住 Ctrl，然後按一下 [運動]、[分項] 及 [競賽細項] 欄位。 選取這三個欄位後，以滑鼠右鍵按一下，然後選取 [建立階層]。 隨後會在資料表底部建立一個父階層節點 [階層 1]，而選取的資料行則會複製到該階層下作為子節點。 確認 [運動] 顯示在階層的開頭，然後是 [分項]，後面再接著 [競賽細項]。

3. 按兩下標題 [階層 1]，然後輸入 SDE 重新命名新的階層。 現在您已擁有包含 [運動]、[分項] 及 [競賽細項] 的階層。 您的 [競賽細項] 資料表現在看起來會如同以下畫面。
   
   

建立「地點」階層

1. 仍在Power Pivot中的 [圖表] 檢視，選取 [主辦城市] 資料表然後按一下 [建立階層圖] 按鈕，在表格標題中，下列畫面所示。
   
   
   表格的底部會出現空白階層的父節點。

2. 輸入「地點」作為新階層的名稱。

3. 新增資料行至階層有許多方法。 將 [季節]、[城市] 及 [國家奧委會國家地區] 欄位拖曳到階層名稱 (即本案例中的 [地點])，直到階層名稱呈醒目提示狀態為止，然後再放開以新增這些項目。

4. 以滑鼠右鍵按一下 [EditionID]，然後選取 [新增至階層]。 選擇 [地點]。

5. 請確定您的階層子節點的順序。從上到下，順序應合乎： 季節、 NOC、 縣/市 EditionID。如果您子節點的順序，只要將它們拖曳至階層中的適當的順序。您的表格看起來應該像下列畫面。
   

現在您的資料模型已經具備可在報表中善加運用的階層。 在下節中，您將學習如何利用這些階層以更快、更一致的方式建立報表。

在樞紐分析表中使用階層

現在，我們有 [運動] 階層和 [地點] 階層，我們可以將其新增至樞紐分析表或 Power View 中，然後迅速取得包含實用資料分組的結果。 建立階層之前，您必須將個別欄位新增至樞紐分析表，並依您想要的檢視方式排列這些欄位。

在本節中，您將使用在上一節所建立的階層，迅速調整您的樞紐分析表。 然後，您將使用階層中的個別欄位建立相同的樞紐分析表檢視，好讓您能夠比較使用階層和使用個別欄位的差異。

1. 回到 Excel。

2. 在 [工作表1] 中，移除 [樞紐分析表欄位] 的 [資料列] 區域中的欄位，然後移除 [資料行] 區域中的所有欄位。 請確定已選取樞紐分析表 (樞紐分析表現在相當小，所以您可以選擇儲存格 A1，以確定有選取樞紐分析表)。 樞紐分析表欄位中僅剩的欄位就是 [篩選] 區域中的 [獎牌] 及 [值] 區域中的 [獎牌數]。 幾近空白的樞紐分析表看起來應如同以下畫面。

   

3. 從 [樞紐分析表欄位] 區域中，將 SDE事件資料表中拖曳至 [列] 區域。將 [欄] 區域，然後從 [主辦城市] 資料表拖曳位置。只要拖曳這兩個階層，樞紐分析表會填入大量資料，這些都依排列您在上述步驟中定義的階層。畫面看起來應該像下列畫面。
   

4. 現在就讓我們來篩選資料元，而且只會看到的事件的第一次十個資料列。在 [樞紐分析表中，按一下 [列標籤中的箭號按一下 [（全選） 若要移除所有選取項目，然後按一下 [顯示前十項運動旁邊的方塊。樞紐分析表現在看起來類似下列畫面。
   

5. 您可以展開任何這些運動樞紐分析表，也就是 SDE 階層的最上層，並查看中向下階層 （訓練） 中的下一層的資訊。如果在階層中的較低層級有該分項，您可以展開 [分項，以查看其事件。您可以執行相同的位置階層，其中的最上層是季節，會顯示成夏天 」 和 「 冬天樞紐分析表。當我們展開 Aquatics 運動時，我們可以看到所有子項分項目和其資料。當我們展開底下 Aquatics 跳水分項時，我們看到其子事件太，如下列畫面所示。我們可以針對水 Polo 執行相同，並查看它有一個事件。
   

透過拖曳這兩個階層，您快速地建立了樞紐分析表，其中包含有趣的結構化資料，可供您切入、篩選與排列。

現在，我們來建立相同的樞紐分析表，但這次不利用階層的優點。

1. 在 [樞紐分析表欄位] 區域中，移除 [資料行] 區域中的 [地點]。 然後移除 [資料列] 區域中的 [SDE]。 您又回復到基本的樞紐分析表。

2. 從 [主辦城市] 資料表中，將 [季節]、[城市]、[國家奧委會國家地區] 及 [EditionID] 拖曳至 [資料行] 區域，然後由上往下按此順序排列。

3. 從 [競賽細項] 資料表中，將 [運動]、[分項] 及 [競賽細項] 拖曳至 [資料列] 區域，然後由上往下按此順序排列。

4. 在樞紐分析表中，將 [列標籤] 篩選為顯示前十項運動。

5. 摺疊所有列與欄，然後展開 Aquatics，然後跳水及水 Polo。您的活頁簿看起來類似下列畫面。
   

畫面看起來很類似，但有一點除外：您將七個個別欄位拖曳至 [樞紐分析表欄位] 區域，而非直接拖曳兩個階層。 如果您是唯一一個根據此資料建立樞紐分析表或 Power View 報表的人，那麼建立階層似乎只是比較方便而已。 不過，如果有多人要建立報表，且必須找出適當的欄位排列順序以獲得正確的檢視，階層馬上就成為提升生產力的利器，而且還能維持一致性。

在另一個教學課程中，您將學習如何在透過 Power View 建立的視覺化報表上使用階層及其他欄位。

重點複習和測驗

複習所學內容

您的 Excel 活頁簿現在的資料模型包含來自多個來源的資料，透過現有欄位與計算結果資料行建立關聯。 您還有能夠反映資料表內資料結構的階層，可讓您快速、一致、輕鬆地建立令人讚嘆的報表。

您學到了建立階層可讓您在資料內指定固有結構，然後在報表中快速使用階層式資料。

在本系列的下一個教學課程中，您將使用 Power View 建立視覺上令人注目的奧運獎牌相關報表。 您還可以執行更多計算作業、將資料最佳化以加快報表建立作業，並匯入額外資料，為這些報表增添更多趣味。 連結如下：

教學課程 3：建立以地圖為基礎的 Power View 報表

測驗

想看看您對於所學內容記住了多少？ 機會來了。 以下測驗強調了您在本教學課程中所學的功能或需求。 答案就在頁面底部。 祝您好運！

第 1 題：以下哪一種檢視可讓您建立兩個資料表之間的關聯？

A：您可以在 Power View 中建立資料表之間的關聯。

B：您可以在 Power Pivot 中透過 [設計檢視] 建立資料表之間的關聯。

C：您可以在以下工具中透過 [方格檢視] 建立資料表之間的關聯。 Power Pivot

D：以上皆是。

第 2 題：是非題：您可以根據透過 DAX 公式所建立的唯一識別碼建立資料表之間的關聯。

A：對

B：錯

第 3 題：您可以在以下哪一個位置建立 DAX 公式？

A：Power Pivot 的 [計算區域] 中。

B：Power Pivot 的新資料行中。

C：Excel 2013 的任一儲存格中。

D：A 和 B 皆是。

第 4 題：關於階層，以下哪一項是正確的？

A：建立階層後，內含的欄位就無法再個別使用。

B：建立階層後，只要將階層拖曳至 Power View 或樞紐分析表區域，就能在用戶端工具中使用內含的欄位 (包括其階層)。

C：建立階層後，資料模型中的基礎資料就會合併到一個欄位中。

D：您無法在 Power Pivot 中建立階層。

測驗答案

1. 正確答案：D

2. 正確答案：A

3. 正確答案：D

4. 正確答案：B