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

摘要： 這是系列的第二個教學課程。 在第一個教學課程將 資料匯入並建立資料模型中，Excel 活頁簿是使用從多個來源匯入的資料來建立。

在本教學課程中，您將使用 Power Pivot 來擴充資料模型、建立階層，以及從現有的資料建立導出欄位，以在資料表之間建立新的關聯。

本教學課程的各個章節如下：

• 使用 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 圖表檢視新增關聯

在本節中，您會使用 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 的功能區索引標籤。 若要啟用 PowerPivot，請依照下列步驟執行。

1. 移至 [檔案 > 選項 > ] 增益集。
2. 在底部附近的 [管理] 方塊中，按一下 [COM 增益集> ] [前往]。
3. 核取 [Microsoft Excel 2013 中的 Microsoft Office Power Pivot] 方塊，然後按一下 [確定]。

Excel 功能區現在會有 POWER PIVOT 索引標籤。

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

Excel 活頁簿包含名為 [主機] 的資料表。 我們透過複製並將 Hosts 複製並貼上到 Excel 中來匯入主機，然後將資料格式化為表格。 若要將 Hosts 資料表新增至資料模型，我們需要建立關聯。 讓我們使用 Power Pivot 以視覺化方式呈現資料模型中的關聯，然後建立關聯。

1. 在 Excel 中，按一下 [主機 ] 索引標籤，將其設為使用中的工作表。

2. 在功能區上，選取 [POWER PIVOT > 資料表新增至 > 資料模型]。 此步驟會將 Hosts 資料表新增至資料模型。 它也會開啟 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. 檢查資料欄位之後，我們發現 Hosts 沒有唯一值的資料行。 我們必須使用計算結果欄和 Data Analysis Expressions (DAX) 來建立它。

如果資料模型中的資料具有建立關聯性所需的所有欄位，以及混合資料以在 Power View 或樞紐分析表中視覺化，那就太好了。 但資料表不一定能如此合作，因此下一節將說明如何使用 DAX 建立新資料行，以建立資料表之間的關聯。

使用計算結果欄擴展資料模型

若要在 Hosts 資料表與 [資料模型] 之間建立關聯，進而擴充我們的 [資料模型] 以包含 [Hosts] 資料表，[ 主機] 必須具有唯一識別每個資料列的欄位。 此外，該欄位必須對應至 [資料模型] 中的欄位。 每個資料表中一個對應的欄位可讓您關聯資料表的資料。

由於 [主機] 資料表沒有這類欄位，您需要建立它。 為了保留資料模型的完整性，您無法使用 Power Pivot 來編輯或刪除現有資料。 不過，您可以使用根據現有資料的計算欄位來建立新資料行。

透過查看 [主機] 資料表，再查看其他 [資料模型] 資料表，我們可以找到可在 [主機] 中建立的唯一欄位的良好候選項目，然後與 [資料模型] 中的資料表建立關聯。 這兩個資料表都需要新的計算資料行，以符合建立關聯所需的需求。

在 [主機] 中，我們可以結合 [版本] 欄位 (奧運會賽事年份) ，以及 [季節] 欄位 ([夏季] 或 [冬季) ] 來建立唯一的計算資料行。 在 [勳章] 資料表中也有一個 [版本] 欄位和一個 [賽季] 欄位，因此如果我們在每個資料表中建立一個計算結果欄來結合 [版本] 和 [賽季] 欄位，我們可以建立 東道主 和 勳章之間的關係。 下列畫面顯示 [主機] 資料表，其中已選取 [版本] 和 [季節] 欄位

        

使用 DAX 建立計算結果欄

讓我們從 [主機] 資料表開始。 目的是在 [Hosts] 資料表，然後在 [Medals ] 資料表中建立計算結果欄，以建立它們之間的關聯。

在 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 函數可將兩個或多個欄位結合成一個。 當您輸入時，自動完成可協助您輸入資料行和資料表的完整名稱，並列出可用的函數。 使用 Tab 選取 [自動完成] 建議。 您也可以在輸入公式時按一下欄，Power Pivot 便會將欄名稱插入您的公式中。

   =CONCATENATE([Edition],[Season])

4. 當您完成建立公式時，請按 Enter 接受公式。

5. 隨後就會在計算結果欄中輸入所有列的值。 如果您向下捲動表格，您會看到每一列都是獨一無二的，因此我們成功建立了一個欄位，可以唯一識別 [ 主機] 資料表中的每一列。 這類欄位稱為主索引鍵。

6. 將計算結果欄重新命名為 EditionID。 您可以按兩下任何欄，或以滑鼠右鍵按一下欄並選擇 [ 重新命名欄] 來重新命名欄，以重新命名欄。 完成後，Power Pivot 中的 [主機] 資料表將如下列畫面所示。

   

[ 主機] 資料表已準備就緒。 接下來，讓我們在 Medals 中建立一個與我們在 Hosts 中建立的 EditionID 資料行格式相符的計算結果欄，以便建立它們之間的關聯。

1. 首先在 [獎牌] 資料表中建立新資料行，就像我們對 [主持人] 所做的那樣。 在 Power Pivot 中，選取 [獎牌] 資料表，然後按一下 [設計 > 欄] > [新增]。 請注意，已選取 [新增欄位 ]。 這與僅選取 [新增欄位] 具有相同效果。

2. [ 獎牌] 中的 [版本] 欄的格式與 [主機] 中的 [版本] 欄不同。 在將 Edition 欄與 Season 欄合併或串連以建立 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 值表示) 頒發了許多獎牌。 獎牌表中的獨特之處在於每枚授予的獎牌。 Medals 資料表中每筆記錄及其指定的主索引鍵的唯一識別碼是 MedalKey 欄位。

下一步是在 主持人 和 勳章之間建立關係。

使用計算資料行建立關聯

接下來，讓我們使用我們建立的計算結果欄來建立 東道主 和 勳章之間的關係。

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

   

2. 展開 [ 主機] ，以便檢視其所有欄位。 我們建立了 EditionID 資料行來做為 Hosts 資料表主索引鍵 (唯一、不重複的欄位) ，並在 Medals 資料表中建立了 EditionID 資料行，以便在它們之間建立關聯。 我們需要找到他們倆，並建立一種關係。 Power Pivot 在功能區上提供 [尋找] 功能，讓您可以搜尋 [資料模型] 以尋找對應的欄位。 下列畫面顯示 [尋找中繼資料 ] 視窗，其中在 [尋找目標 ] 欄位中輸入了 EditionID。
   

3. 將 [ 主持者] 資料表放置在 [獎牌] 旁邊。

4. 將 [獎牌] 中的 EditionID 資料行拖曳至 [主機] 中的 [EditionID] 資料行。 Power Pivot 會根據 EditionID 資料行建立資料表之間的關聯，並在兩欄之間畫一條線來表示關聯。

   

在本節中，您學習了新增資料行的新技術、使用 DAX 建立計算資料行，並使用該資料行在資料表之間建立新關聯。 Hosts 資料表現已整合至 [資料模型]，其資料可供 Sheet1 中的樞紐分析表使用。 您也可以使用相關資料來建立其他樞紐分析表、樞紐分析圖、Power View 報表等等。

建立階層

大部分資料模型都包含固有的階層式資料。 常見的範例包括行事曆資料、地理資料和產品類別。 在 Power Pivot 內建立階層很有用，因為您可以將一個項目拖曳到報表（階層），而不必一遍又一遍地組合和排序相同的欄位。

奧運會數據也是分層的。 了解奧運會在運動、學科和項目方面的等級制度很有幫助。 對於每項運動，都有一個或多個相關學科 (有時也有很多) 。 對於每個學科，都有一個或多個事件 (，有時每個學科) 中都有很多事件。 下圖說明階層。

在本節中，您將在本教學課程中使用的奧運資料中建立兩個階層。 接著，您可以使用這些階層，來了解階層如何讓您在樞紐分析表中以及後續的教學課程中，在 Power View 中輕鬆組織資料。

建立運動階層

1. 在 Power Pivot 中，切換到 [圖表檢視]。 展開 [事件] 資料表，以便更輕鬆地看到其所有欄位。

2. 按住 Ctrl，然後按一下 [運動]、[學科] 和 [項目] 欄位。 選取這三個欄位後，請以滑鼠右鍵按一下，然後選取 [建立階層]。 父階層節點階層 1 會在表格底部建立，而選取的欄會複製到階層下作為子節點。 確認 [運動] 首先出現在階層中，然後是 [紀律]，然後是 [項目]。

3. 按兩下標題 Hierarchy1，然後輸入 SDE 以重新命名新的階層。 您現在擁有包括運動、紀律和項目在內的階層。 [事件] 資料表現在看起來像下列畫面。

   

建立位置階層

1. 仍在 Power Pivot 的圖表檢視中，選取 [主機] 資料表，然後按一下資料表標題中的 [建立階層] 按鈕，如下圖所示。
   

   隨後表格底端就會出現一個空白的階層父節點。

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

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

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

5. 請確定您的階層子節點井然有序。 從上到下，順序應為：Season、NOC、City、EditionID。 如果您的子節點不按順序排列，只需將它們拖曳到階層中的適當順序即可。 您的表格會像下列畫面所示。
   

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

在樞紐分析表中使用階層

現在我們有了運動階層和位置階層，我們可以將它們新增至樞紐分析表或 Power View，並快速取得包含有用資料分組的結果。 建立階層之前，您必須將個別欄位新增至樞紐分析表，並依您想要的方式排列欄位。

在本節中，您可以使用上一節中建立的階層來快速精簡樞紐分析表。 然後，您可以使用階層中的個別欄位建立相同的樞紐分析表檢視，以便比較使用階層與使用個別欄位。

1. 回到 Excel。
2. 在 Sheet1 中，移除 [樞紐分析表欄位] 中 [列] 區域的欄位，然後移除 [欄] 區域中的所有欄位。 請確定已選取 [樞紐分析表]， (現在的樞紐分析表已經很小了，所以您可以選擇儲存格 A1 來確定已選取 [樞紐分析表]) 。 樞紐分析表欄位中唯一剩餘的欄位是 [篩選] 區域中的 [獎牌] 和 [值] 區域中的 [獎牌計數]。 幾乎空白的樞紐分析表應該會像下列畫面。
   
3. 從 [樞紐分析表欄位] 區域，將 SDE 從 [ 事件 ] 資料表拖曳至 [列] 區域。 接著，將 [位置] 從 [主機 ] 資料表拖曳至 [欄位] 區域。 只要拖曳這兩個階層，您的樞紐分析表就會填入大量資料，而所有資料都會按照您在先前步驟中定義的階層來排列。 您的畫面會像下列畫面。
   
4. 讓我們稍微篩選這些資料，只看看事件的前十列。 在樞紐分析表中，按一下 [ 列標籤 ] 中的箭號，按一下 [全選 () 移除所有選取項目，然後按一下前十個運動項目旁邊的方塊。 您的樞紐分析表現在看起來像下列畫面。
   
5. 您可以在樞紐分析表中展開任何這些運動，這是 SDE 階層的最上層，並在階層 (學科) 的下一層中查看資訊。 如果該領域存在階層中的較低層級，您可以展開該領域以查看其事件。 您也可以對 [位置] 階層執行相同的操作，其頂層為 [季節]，它在樞紐分析表中顯示為 [夏季] 和 [冬季]。 當我們擴展水上運動時，我們會看到它所有的兒童紀律元素及其數據。 當我們在水上運動下展開跳水項目時，我們也會看到其子項目，如下圖所示。 我們可以對水球做同樣的事情，看看它只有一個項目。
   

藉由拖曳這兩個階層，您快速建立了包含有趣且結構化資料的樞紐分析表，您可以切入、篩選及排列資料。

現在，讓我們建立相同的樞紐分析表，而不需要階層。

1. 在 [樞紐分析表欄位] 區域中，從 [欄] 區域移除 [位置]。 然後從 [列] 區域移除 SDE。 您回到基本的樞紐分析表。
2. 從 [Hosts] 資料表中，將 [Season]、[City]、[NOC_CountryRegion] 和 [EditionID] 拖曳到 [欄] 區域，並依照由上至下的順序排列。
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