教學課程：使用 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 部分

建議您依序瀏覽。

這些教學課程採用 Excel 2013 並啟用 Power Pivot 功能。 如需 Excel 2013 的詳細資訊，請按一下這裡。 如需啟用 Power Pivot 的相關指示，請按一下這裡。

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

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

注意： Microsoft Excel 2013 中的 Power Pivot 載入巨集是 Office 專業增強版 的一部分。 如需詳細資訊，請參閱在載入巨集中啟動 Microsoft Excel 2013 Power Pivot。

啟用 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 載入巨集，讓您用來執行這項工作的其餘步驟。

3. 請注意， Power Pivot 視窗會顯示模型中的所有數據表，包括 [主辦者]。 您可以按幾個表格看看。 在 Power Pivot 中，您可以檢視模型包含的所有數據，即使這些數據未顯示在 Excel 中的任何工作表中，例如下面的 [分項]、[事件] 和 [獎牌] 數據，以及 [S_Teams]、[W_Teams]和 [ 運動]。
   
   

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

5. 使用滑桿列調整圖表大小，好讓您看到圖表中的所有物件。 拖曳表格的標題列以重新排列表格，讓它們顯示並位於彼此旁邊。 請注意，四個數據表與其餘數據表無關： [主辦者]、[ 事件]、[ W_Teams] 和 [S_Teams。
   
   

6. 您會注意到 [ 獎牌 ] 表格和 [ 事件 ] 數據表都有名為 DisciplineEvent 的字段。 進一步檢查時，您會判斷 [事件 ] 資料表中的 DisciplineEvent 欄位包含唯一的非重複值。

1. 在 [獎牌 ] 表格和 [ 活動 ] 表格之間建立關聯。 在 [圖表檢視] 中，將 [DisciplineEvent] 字段從 [事件 ] 數據表拖曳到 [ 獎牌] 中的 [DisciplineEvent] 字段。 它們之間會出現一條線，表示已建立關聯。

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

3. 若要將 [主辦者 ] 連線至數據模型，我們需要一個具有值的欄位，以唯一識別 [主辦者 ] 數據表中的每一列。 然後，我們可以搜尋數據模型，以查看相同的數據是否存在於另一個數據表中。 在 [圖表檢視] 中查看無法讓我們執行此動作。 選取 [主辦者] 后，切換回 [數據檢視]。

4. 檢查欄之後，我們發現 [主辦者 ] 沒有唯一值的欄。 我們必須使用計算結果列來建立，而數據分析表達式 (DAX) 。

當數據模型中的數據具有建立關聯所需的所有欄位，並將數據對應到 Power View 或數據透視表中可視化時，這會很好。 但數據表並非總是如此合作，因此下一節說明如何使用DAX建立新數據行，以用於建立數據表之間的關聯。

使用計算結果列延伸數據模型

若要在 [主辦者 ] 數據表與 [數據模型] 之間建立關聯，並藉此延伸數據模型以包含 [主辦者 ] 數據表， [主辦者 ] 必須具備可唯一識別每一列的欄位。 此外，該欄位必須對應到數據模型中的欄位。 這些對應欄位是每個數據表中一個可讓數據表數據關聯的原因。

由於 [ 主辦者 ] 數據表沒有這樣的字段，您必須建立它。 若要保留資料模型的完整性，您無法使用 Power Pivot 來編輯或刪除現有數據。 不過，您可以使用根據現有數據的導出欄位來建立新欄。

透過查看 [主辦者 ] 數據表，然後查看其他數據模型數據表，我們便可在 [ 主辦者] 中建立一個唯一字段，然後與數據模型中的數據表建立關聯，以尋找適合的候選字段。 這兩個數據表都需要新的計算結果列，才能符合建立關聯所需的需求。

在 [主辦者] 中，我們可以將 Edition 欄位結合 (奧運賽事年份) 和夏季或冬季 () 的 [季節] 字段，來建立唯一的計算結果列。 [ 獎牌] 數據表中也有 [版本] 欄位和 [季節] 欄位，因此如果我們在合併 [版本] 和 [季節] 字段的每個數據表中建立計算結果列，我們可以在 [主辦者 ] 和 [ 獎牌] 之間建立關聯。 下列畫面顯示 [ 主辦者] 數據表，其中已選取 [版本] 和 [季節] 字段

使用 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 數據行之前，我們需要建立一個中間欄位，將 Edition 設定為正確的格式。 在表格上方的數據編輯列中，輸入下列DAX公式。

   = YEAR([Edition])
   

3. 當您完成建立公式時，請按 Enter。 會根據您輸入的公式，填入計算結果列中所有列的值。 如果您將此欄與 [ 主辦者] 中的 [版本] 欄進行比較，您會看到這些欄的格式相同。

4. 以滑鼠右鍵按一下 [CaculatedColumn1] 並選取 [重新命名欄]，將該欄重新命名。 輸入 Year，然後按 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 功能區上提供 [尋找 ] 功能，因此您可以搜尋數據模型中的對應字段。 下列畫面顯示 [ 尋找元數據 ] 視窗，其中 [ Find What ] 字段中輸入了 EditionID。
   

3. 將 [主辦者] 表格放在 [ 獎牌] 旁邊。

4. 將 [獎牌 ] 中的 EditionID 欄拖曳到 [主辦者] 中的 [EditionID] 欄。 Power Pivot 根據 EditionID 數據行建立數據表之間的關聯，並在兩個數據行之間繪製一條線，指出關聯。
   
   

在本節中，您學到新增數據行、使用 DAX 建立計算結果列，以及使用該數據行在數據表之間建立新關聯的新技術。 [ 主辦者 ] 數據表現在已整合至數據模型，其數據可供工作 表1 的數據透視表使用。 您也可以使用相關聯的數據來建立其他數據透視表、樞紐分析圖、Power View 報表等等。

建立階層

大部分的數據模型都包含固有的階層式數據。 常見的範例包括行事曆資料、地理資料和產品類別。 在 Power Pivot 中建立階層是很實用的，因為您可以將一個專案拖曳到報表，也就是階層，而不需要一次一次組合和排序相同的字段。

奧運數據也是階層式的。 了解奧運階層對於運動、分項和賽事很有説明。 針對每項運動，都有一或多個相關聯的分項 (有時候有許多) 。 針對每個分項， (一或多個事件，有時候每個分項) 有許多事件。 下圖說明階層。

在本節中，您會在此教學課程中使用的奧運數據中建立兩個階層。 接著，您可以使用這些階層來查看階層如何讓數據透視表輕鬆整理數據，以及在後續教學課程中使用Power View。

建立 [運動] 階層

1. 在 Power Pivot中，切換到 [圖表檢視]。 展開 [ 事件] 數據表，讓您可以更輕鬆地查看其所有欄位。

2. 長按 Ctrl，然後按擊 [運動]、[分項] 和 [賽事] 字段。 選取這三個字段后，以滑鼠右鍵按下並選 取 [建立階層]。 父階層節點 階層 1 會在表格底部建立，且選取的欄會複製到階層底下做為子節點。 確認 [運動] 先出現在階層中，接著 [分項]，再 [賽事]。

3. 按兩下標題、 階層1，然後輸入 SDE 以重新命名您的新階層。 您現在有一個包含 [運動]、[分項] 和 [賽事] 的階層。 您的 [事件] 表格現在看起來像下列畫面。
   
   

建立位置階層

1. 在 Power Pivot中的 [圖表檢視] 中，選取 [主辦者 ] 表格，然後按兩下表格標題中的 [建立階層] 按鈕，如下列畫面所示。
   
   
   空白階層父節點會顯示在表格底部。

2. 輸入 位置 做為新階層的名稱。

3. 有許多方法可以將欄新增至階層。 將 [季節]、[城市] 和 [NOC_CountryRegion] 字段拖曳到階層名稱 (，在此情況下[ 位置 ]) ，直到階層名稱醒目提示為止，然後放開以新增它們。

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

5. 確定階層子節點的順序。 從上到下，順序應該是：季節、NOC、城市、EditionID。 如果您的子女節點順序不符，只要將節點拖曳到階層中的適當順序即可。 您的表格看起來應該會像下列畫面一樣。
   

您的數據模型現在有階層，可在報表中善用。 在下一節中，您將了解這些階層如何讓報表建立更快速且更一致。

在數據透視表中使用階層

現在我們有 [運動] 階層和 [位置] 階層，我們可以將它們新增至數據透視表或 Power View，並快速取得包含實用數據群組的結果。 建立階層之前，您必須先將個別欄位新增至數據透視表，並依您想要的方式排列這些欄位。

在此區段中，您可以使用上一節中建立的階層來快速精簡數據透視表。 然後，您可以使用階層中的個別欄位建立相同的數據透視表檢視，好讓您比較使用階層與使用個別欄位的比較。

1. 回到 Excel。

2. 在 工作表1 中，從數據 透視表欄位的 [列] 區域移除欄位，然後從 COLUMNS 區域移除所有欄位。 請確定已選取數據透視表 (現在變得相當小，因此您可以選擇儲存格 A1 以確保已選取資料透視表) 。 數據透視表欄位中唯一的剩餘欄位是 [FILTERS] 區域中的 [獎牌]，以及 [值] 區域中的 [獎牌數]。 幾乎空白的數據透視表看起來應該會像下列畫面一樣。

   

3. 從 [數據透視表字段] 區域，將 [SDE] 從 [ 事件 ] 數據表拖曳至 [列] 區域。 然後將 [ 主辦者] 數據表中的 [位置] 拖曳到 [COLUMNS] 區域。 只要拖曳這兩個階層，數據透視表就會填入大量數據，而這些數據都是以您在上述步驟中定義的階層排列而成。 您的畫面應該會類似下列畫面。
   

4. 讓我們篩選一下該數據，只查看前十列的事件。 在數據透視表中，按兩下 [ 列卷標 ] 中的箭號，按兩下 [ (選取所有) 以移除所有選取專案，然後按兩下前十個 [運動] 旁的方塊。 您的數據透視表現在看起來像下列畫面。
   

5. 您可以在數據透視表中展開其中任何一個運動，也就是SDE 階層的頂層，並在階層中查看下一層的資訊， (分項) 。 如果該分項在階層中有較低階層，您可以展開分項以查看其事件。 您可以對 [位置] 階層執行相同的動作，最上層的 [季節] 會在數據透視表中顯示為 [夏季] 和 [冬季]。 當我們展開水族運動時，我們會看到其所有子項元素及其數據。 當我們展開 [水族] 下的 [游泳] 下的 [游泳] 分項時，也會看到其子項賽事，如下列畫面所示。 我們可以對「水的賈便」執行同樣的動作，並看到它只有一個活動。
   

拖曳這兩個階層，即可快速建立含有有趣且結構化數據的數據透視表，以便您切入、篩選及排列數據。

現在讓我們建立相同的數據透視表，而不需要階層。

1. 在 [數據透視表字段] 區域中，移除 [欄] 區域中的位置。 然後從 [列] 區域移除 SDE。 您已返回基本數據透視表。

2. 從 [ 主辦城市] 數據表，將 [季節]、[城市]、[NOC_CountryRegion] 和 [EditionID] 拖曳到 [COLUMNS] 區域，然後依順序由上到下排列。

3. 從 [賽事] 表格中，將 [運動]、[分項] 和 [事件] 拖曳到 [列] 區域，然後依順序從上到下排列。

4. 在數據透視表中，篩選 [列卷標] 到前十名的 [運動]。

5. 折迭所有列和欄，然後展開 [水族]，然後展開 [游泳] 和 [水族生物]。 該活頁簿看起來會類似下列畫面。
   

畫面看起來很類似，只是您將七個個別欄位拖曳到 [ 數據透視表 字段] 區域，而不只是拖曳兩個階層。 如果您是唯一根據此數據建立數據透視表或 Power View 報表的人，建立階層可能只是很方便。 但是當許多人正在建立報表，而且必須找出字段的正確順序才能正確檢視時，階層會快速變成生產力提升，並啟用一致性。

在另一個教學課程中，您將瞭解如何在使用Power View建立的視覺吸引人報表中使用階層和其他字段。

重點複習和測驗

複習所學內容

您的 Excel 活頁簿現在有一個數據模型，其中包含來自多個來源的數據，與使用現有欄位和計算結果列相關。 您也有反映數據表內數據結構的階層，可讓您快速、一致且輕鬆地建立引人注目的報表。

您已瞭解建立階層可讓您指定數據內的固有結構，並快速地在報表中使用階層式數據。

在本系列的下一個教學課程中，您可以使用 Power View 建立關於奧運獎牌的引人注目的報告。 您也可以執行更多計算、優化數據以快速建立報表，以及匯入其他數據，讓這些報表變得更有趣。 以下是連結：

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

測驗

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

問題 1： 下列哪一種檢視可讓您在兩個數據表之間建立關聯？

答：您可以在 Power View 中建立數據表之間的關聯。

B：在 Power Pivot中使用 [設計視圖] 建立數據表之間的關聯。

C：在 Power Pivot中使用 [網格線檢視] 建立數據表之間的關聯

D：以上皆是。

問題 2： TRUE 或 FALSE：您可以根據使用 DAX 公式建立的唯一標識碼，建立數據表之間的關聯。

答：TRUE

B：FALSE

問題 3： 您可以在下列哪一項中建立DAX公式？

答：在 Power Pivot的計算區域中。

B：在 Power Pivotf 中的新欄中。

C：在 Excel 2013 中的任何單元格中。

D：A 和 B。

問題 4： 階層的下列哪一個是正確的？

答：當您建立階層時，已不再個別提供包含的欄位。

B：當您建立階層時，只要將階層拖曳到 Power View 或數據透視表區域，就可以在用戶端工具中使用包括其階層在內的包含字段。

C：當您建立階層時，數據模型中的基礎數據會合併成一個字段。

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

測驗答案

1. 正確答案：D

2. 正確答案：A

3. 正確答案：D

4. 正確答案：B