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

摘要： 這是系列教學的第二個。 在第一個教學中，「 匯入資料並建立資料模型」是用從多個來源匯入的資料建立的 Excel 工作簿。

在這個教學中，你將使用 Power Pivot 擴展資料模型、建立階層結構，並從現有資料建立計算欄位，以建立資料表間的新關係。

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

• 在 Power Pivot 中使用 Diagram View 新增關係
• 利用計算欄位擴充資料模型
• 建立階層
• 在樞紐分析表中使用階層結構
• 重點複習和測驗

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

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

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 中使用 Diagram View 新增關係

在本節中，你將使用 Microsoft Office Power Pivot in Excel 2013 外掛來擴充模型。 在 Microsoft SQL Server Power Pivot for Excel 中使用圖表檢視功能，讓建立關係變得簡單。 首先，你需要確保你啟用了 Power Pivot 外掛。

注意：Microsoft Excel 2013 中的 Power Pivot 外掛是 Office 專業增強版的一部分。 更多資訊請參閱 Microsoft Excel 2013 外掛中的 Start Power Pivot。

透過啟用 Power Pivot 外掛，將 Power Pivot 加入 Excel 功能區

啟用 Power Pivot 時，你會在 Excel 2013 看到一個叫 做 POWER PIVOT 的區塊分頁。 要啟用 Power Pivot，請遵循以下步驟。

1. 前往>檔案選項>的附加元件。
2. 在底部的 管理 框中，點選 COM Add-ins> Go。
3. 勾選 Microsoft Office Power Pivot in Microsoft Excel 2013 的選項，然後點擊確定。

Excel 功能區現在有一個 POWER PIVOT 分頁。

在 Power Pivot 中使用 Diagram View 新增關係

Excel 工作簿中包含一個名為 Hosts 的表格。 我們 是透過 複製並貼上到 Excel 匯入主機，然後將資料格式化成表格。 要將 Hosts 資料表加入資料模型，我們需要建立關聯。 我們先用 Power Pivot 在資料模型中視覺化表示關係，然後建立關聯。

1. 在 Excel 裡，點選「 Hosts」 標籤，讓它成為活動工作表。

2. 在功能區上，選擇「 POWER PIVOT > Tables > 新增到資料模型」。 此步驟將 主機 資料表加入資料模型。 同時也會開啟 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 欄位從 Events 表格拖曳到 Medals 的 DisciplineEvent 欄位。 他們之間出現一條線，表示已經建立了關係。

2. 點擊連接 活動 與 獎牌的線。 高亮欄位定義了這種關係，如下畫面所示。

   

3. 要將 主機 連接到資料模型，我們需要一個欄位，該欄位能唯一識別 主機資料表 中的每一列。 接著我們可以搜尋資料模型，看看是否存在其他資料表中。 在圖示檢視中查看無法達成此目的。 選擇 主機 後，切回資料檢視。

4. 檢視欄位後，我們發現 Hosts 沒有唯一值欄位。 我們必須用計算出的欄位，以及 DAX) (Data Analysis Expressions 來建立它。

當你的資料模型中有所有建立關係所需的欄位，並且能將資料混合起來，在 Power View 或樞紐分析表中視覺化時，這會非常方便。 但資料表並不總是那麼合作，因此下一節將說明如何使用 DAX 建立一個新欄位，來建立資料表間的關係。

利用計算欄位擴充資料模型

為了建立 Hosts 資料表與資料模型之間的關係，進而擴展我們的資料模型以包含 Hosts 資料 表，主機 必須有一個欄位能唯一識別每一列。 此外，該欄位必須對應資料模型中的欄位。 這些對應欄位，分別位於每個表格中，是讓資料表能夠關聯的關鍵。

因為 Hosts 表格沒有這樣的欄位，你需要自己建立。 為了維護資料模型的完整性，你不能使用 Power Pivot 來編輯或刪除現有資料。 不過，你可以根據現有資料使用計算欄位來建立新的欄位。

透過瀏覽 Hosts 表格，再查看其他資料模型資料表，我們會找到一個適合在 Hosts 中建立的獨特欄位，然後與資料模型中的表格關聯。 這兩個表格都需要一個新的計算欄位，以符合建立關係所需的條件。

在 主辦單位中，我們可以將奧運項目) 年份 (的版本欄位與夏季或冬季) 賽季欄位 (結合，建立一個獨特的計算欄位。 獎 牌 表中還有一個版本欄位和一個賽季欄位，因此如果我們在每個表格中建立一個計算欄位，結合賽事欄位與賽季欄位，就能建立主辦 人 與 獎牌之間的關係。 以下畫面顯示 主辦人 表格，並選取了版次和賽季欄位

        

使用 DAX 建立計算欄位

我們先從 主持 人桌開始。 目標是在 主持 人表建立計算欄位，然後在 獎牌 表中建立，用來建立它們之間的關係。

在 Power Pivot 中，你可以使用 DAX) (Data Analysis Expressions 來建立計算。 DAX 是一種用於 Power Pivot 與樞紐分析表的公式語言，專為 Power Pivot 中可用的關聯資料與情境分析而設計。 你可以在新的 Power Pivot 欄位或 Power Pivot 的計算區建立 DAX 公式。

1. 在 Power Pivot 中，選擇 HOME > View > Data View ，確保選擇 Data View，而不是在 Diagram View 中。

2. 在 Power Pivot 中選擇 主機 表格。 現有欄位旁邊有一個空欄位，標題為 「新增欄位」。 Power Pivot 提供該欄位作為佔位符。 在 Power Pivot 中新增欄位的方法有很多，其中一種是直接選取標題為 「新增欄位」的空欄位。

   

3. 在資料編輯列中輸入以下 DAX 公式。 CONCATENATE 函數將兩個或多個欄位合併為一個。 當你輸入時，自動補全會幫助你輸入完整合格的欄位和表格名稱，並列出可用的函式。 用分頁選出自動補全建議。 你也可以在輸入公式時點擊欄位，Power Pivot 會將欄位名稱插入公式中。

   =CONCATENATE([Edition],[Season])

4. 當你完成公式建構後，按下 Enter 鍵接受它。

5. 隨後就會在計算結果欄中輸入所有列的值。 如果你往下捲動表格，你會發現每一列都是唯一的——所以我們成功建立了一個欄位，能唯一識別 Hosts 表格中的每一列。 此類欄位稱為主鍵。

6. 我們將計算出來的欄位重新命名為 EditionID。 你可以雙擊任何欄位，或右鍵點擊欄位並選擇 重新命名欄位來重新命名欄位。 完成後，Power Pivot 中的 主機 表格會呈現如下畫面。

   

主持人桌準備好了。 接著我們在 Medals 中建立一個計算欄位，格式與 我們在 Hosts 建立的 EditionID 欄位相符，這樣才能建立它們之間的關聯。

1. 先在 獎牌 表中建立一個新欄位，就像我們對 主持人所做的那樣。 在 Power Pivot 中選擇 獎章 表，點選 「設計 > 欄位 > 新增」。 請注意，已選擇 新增欄位 。 這和直接選擇 新增欄位的效果相同。

2. 獎章欄的版面欄格式與《Hosts》版塊不同。 在我們將 Edition 欄位與 Season 欄位合併或串接以建立 EditionID 欄位之前，我們需要建立一個中介欄位，讓 Edition 格式正確。 在表格上方的公式欄中，輸入以下 DAX 公式。

   = YEAR([Edition])
   
   

3. 當你完成公式建立後，按下 Enter 鍵。 計算欄中所有列的數值都會根據你輸入的公式填入。 如果你把這欄和 Hosts裡的Edition欄比較，你會發現這兩欄的格式是一樣的。

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 欄位作為 Hosts 表格的主鍵， (唯一且不重複的欄位) ，並在 Medals 表格中建立了 EditionID 欄位，以便建立它們之間的關係。 我們需要找到他們兩個，建立關係。 Power Pivot 在色帶上提供 「尋找 」功能，讓你能搜尋資料模型中的對應欄位。 以下畫面顯示尋找 元資料 視窗，EditionID 輸入於 「尋找什麼 」欄位。
   

3. 將 主持 人桌擺放在獎 牌旁邊。

4. 將 Medals 中的 EditionID 欄位拖曳到 Hosts 的 EditionID 欄位。 Power Pivot 會根據 EditionID 欄位建立資料表之間的關係，並在兩欄位之間畫出一條線，以顯示該關係。

   

在這部分，你學到了新增欄位的新技巧，利用DAX建立計算欄位，並用該欄位建立表格間的新關係。 Hosts 表格現已整合進資料模型，其資料可在 Sheet1 的樞紐分析表中取得。 你也可以利用相關資料建立額外的樞紐分析表、樞紐分析圖、Power View 報告等多種功能。

建立階層

大多數資料模型包含本質上具有階層式的資料。 常見的範例包括行事曆資料、地理資料和產品類別。 在 Power Pivot 中建立階層結構很有用，因為你可以拖動一個項目到報告——也就是階層結構——而不必一再組合和排序相同的欄位。

奧運數據也是階層式的。 了解奧運的階層結構，無論是運動項目、項目還是項目，都很有幫助。 每項運動都有一個或多個相關的項目 (有時) 。 每個項目都有一個或多個項目 (，有時每個項目) 會有很多項目。 下圖說明了階層結構。

在本節中，你會在你在這個教學中使用的奧運數據中建立兩個階層結構。 接著你可以利用這些階層結構，看看如何在樞紐分析表中讓資料組織變得簡單，並在後續教學中也能在 Power View 中進行。

建立運動階層

1. 在 Power Pivot 中，切換到 圖表檢視。 展開 事件 表，讓你能更輕鬆地看到所有欄位。

2. 按住 Ctrl，點選運動、項目和項目欄位。 選中這三個欄位後，右鍵點擊並選擇 建立階層。 在表格底部建立一個父階層節點 Hierarchy 1，所選欄位會以子節點的形式複製到階層結構下。 確認運動在階層中先出現，然後是紀律，最後是項目。

3. 雙擊標題 Hierarchy1，輸入 SDE 即可重新命名你的新階層。 你現在有一個包含運動、紀律和項目的階層。 你的事件表格現在看起來像以下畫面。

   

建立地點階層

1. 仍在 Power Pivot 的圖表檢視中，選擇 Hosts 表格，並點擊表格標題中的「建立階層」按鈕，如下畫面所示。
   

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

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

3. 有許多方法可以將欄位加入階層結構。 將季節、城市和NOC_CountryRegion欄位拖曳到階層名稱上， (在此 情況下，地點) 直到階層名稱被高亮，然後放開它們即可新增。

4. 右鍵點選 EditionID，並選擇 加入階層結構。 選擇 地點。

5. 確保你的階層子節點順序正確。 從上到下，順序應該是：季節、NOC、城市、EditionID。 如果你的子節點順序不對，只要把它們拖到適當的階層順序。 你的表格應該會像以下畫面一樣。
   

你的資料模型現在有階層結構，可以在報告中發揮良好作用。 在下一節，你將學習這些階層如何讓你的報告產生更快、更一致。

在樞紐分析表中使用階層結構

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

在本節中，你可以利用前一節建立的階層來快速精煉你的樞紐分析表。 接著，你用階層中的各個欄位建立相同的樞紐分析表視圖，這樣你就能比較使用階層結構和使用個別欄位的差異。

1. 回到 Excel。
2. 在 Sheet1 中，先移除 PivotTable Fields 的 ROWS 欄位，再從 COLUMNS 區域移除所有欄位。 請確定選取了樞紐分析表， (現在已經很小了，所以你可以選擇 A1 格，確保你的樞紐分析表) 被選中。 樞紐分析表欄位中僅剩 FILTERS 區的 Medal 和 VALUES 區的 Count of Medal。 你幾乎空的樞紐分析表應該會像以下畫面一樣。
   
3. 從樞紐分析表欄位區，將 SDE 從 事件 表拖曳到 ROWS 區。 然後將 Locations 從 Hosts 表格拖到 COLUMNS 區域。 只要拖動這兩個階層，你的樞紐分析表就會被填入大量資料，這些資料都依照你在前幾個步驟定義的階層中排列。 你的螢幕應該會像以下畫面一樣。
   
4. 我們稍微篩選一下這些資料，只看前十行的事件。 在樞紐分析表中，點擊列標籤中的箭頭 ，點 選 (選取所有) 以移除所有選項，然後點選前十項運動旁的方框。 你的樞紐分析表現在看起來如下畫面。
   
5. 你可以在樞紐分析表中展開這些運動項目，這是 SDE 階層的頂層，並且在階級 (學科) 的下一層查看資訊。 如果該學科的階層較低，你可以擴展該學科以查看其事件。 你也可以對位置階層做同樣的操作，其頂層是季節，在樞紐分析表中顯示為夏季和冬季。 當我們擴展水上運動時，可以看到所有兒童項目及其數據。 當我們將跳水項目擴展到水上活動時，也會看到其子項目，如下畫面所示。 我們也可以對水球做同樣的事，因為它只有一個項目。
   

透過拖動這兩個階層，你很快就建立了一個包含有趣且結構化資料的樞紐分析表，可以深入分析、篩選和整理。

現在讓我們建立相同的樞紐分析表，但沒有階層結構。

1. 在樞紐分析表欄位區，從欄位區移除位置。 然後從 ROWS 區域移除 SDE。 你又回到基本的樞紐分析表。
2. 從 主辦者 表格中，將季節、城市、NOC_CountryRegion 和 EditionID 拖入欄位區，並依序從上到下排列。
3. 從 活動 表中，將運動、紀律和賽事拖曳到 ROWS 區域，並依序從上到下排列。
4. 在樞紐分析表中，將列標籤篩選為前十項運動項目。
5. 把所有列和欄都摺疊，然後展開水上活動，再展開潛水和水球。 該活頁簿看起來會類似下列畫面。
   

畫面看起來類似，只是你把七個欄位拖到 樞紐分析表欄位 ，而不是單純拖兩個階層。 如果你是唯一根據這些資料製作樞紐分析表或 Power View 報告的人，建立層級可能看起來只是方便。 但當許多人在製作報告，並必須正確排序欄位以正確呈現時，階層結構迅速成為提升生產力並促進一致性的手段。

在另一個教學中，你會學習如何在使用 Power View 製作的視覺化報告中使用階層結構和其他欄位。

重點複習和測驗

複習所學內容

您的 Excel 工作簿現在有一個資料模型，包含多個來源的資料，這些資料是利用現有欄位和計算欄位來關聯。 你還有反映資料表結構的階層結構，使得製作引人注目的報告變得快速、一致且輕鬆。

你學到建立階層結構可以指定資料內的固有結構，並快速在報告中使用階層式資料。

在本系列的下一個教學中，你將使用 Power View 製作視覺上引人注目的奧運獎牌報告。 你還要做更多計算，優化資料以快速產生報表，並匯入額外資料，讓報表更有趣。 這裡有個連結：

教學三：建立基於地圖的 Power View 報告

測驗

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

問題一： 以下哪一種檢視能讓你在兩個資料表之間建立關係？

答：你在 Power View 中建立資料表之間的關係。

B：你可以在 Power Pivot 中使用 Design View 建立表格間的關係。

C：你可以在 Power Pivot 中使用 Grid View 建立表格間的關係

D：以上皆是。

問題二： 真或假：你可以根據使用 DAX 公式建立的唯一識別碼建立資料表間的關係。

答：沒錯

B：錯誤

問題三： 以下哪一種可以建立 DAX 公式？

答：在Power Pivot的計算領域。

B：在 Power Pivotf 新專欄中。

C：在 Excel 2013 的任何儲存格中。

D：A和B都一樣。

問題四： 以下哪一項關於階層制度是正確的？

答：當你建立階層結構時，包含的欄位不再單獨可用。

B：當你建立階層時，包含的欄位及其階層結構可以在客戶端工具中使用，只需將階層拖曳到 Power View 或樞紐分析表區域即可。

問：當你建立階層結構時，資料模型中的底層資料會合併成一個欄位。

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

測驗答案

1. 正確答案：D
2. 正確答案：A
3. 正確答案：D
4. 正確答案：B