在 Excel 中建立資料模型

Applies To
Excel for Microsoft 365 Excel 2024 Excel 2021

[資料模型] 可讓您整合多個資料表中的資料，有效地在 Excel 活頁簿內建立關聯式資料來源。 在 Excel 中，資料模型會透明地使用，提供樞紐分析表和樞紐分析圖中使用的表格式資料。 資料模型會以視覺化方式呈現為欄位清單中的資料表集合，而且大多數時候，您通常是透過樞紐分析表欄位清單來使用它，可能不會注意到它在那裡。 

您必須先取得一些資料，才能開始使用資料模型。 為此，我們將使用Power Query取得 & 轉換體驗，因此您可能需要退後一步並觀看影片，或遵循我們關於取得 & 轉換和 Power Pivot 的學習指南。您的資料應該在表格中，而不只是儲存格範圍 () 才能正確載入資料和關聯資料。

先決條件

Power Pivot 在哪裡？

  • 功能區中包含 Microsoft 365 Excel - Power Pivot。

Get & Transform (Power Query) 在哪裡？

  • Microsoft 365 Excel - 取得轉換 & (Power Query) [資料] 索引標籤上已與 Excel 整合。

開始使用

首先，您需要獲取一些數據。

  1. 建立新的活頁簿，或開啟不包含資料的活頁簿。

  2. 在 Microsoft 365 Excel 的功能區上，選取 [資料] 索引標籤。在 [取得 & 轉換資料] 區段中，選取 [取得資料] 以從任意數量的外部資料來源匯入資料，例如文字檔、Excel 活頁簿、網站、Microsoft Access、SQL Server 或包含多個相關資料表的其他關聯式資料庫。

  3. Excel 會提示您選取一個或多個資料表。 如果您想要從相同的資料來源取得多個資料表，請勾選 [選取多個項目 ] 方塊。

    1. 選取 [轉換]。 當您選取多個資料表時，Excel 會自動為您建立資料模型。 如需詳細資訊，請參閱：在 Excel 中建立、載入或編輯查詢 (Power Query) 。

      注意

      在這些範例中，我們使用包含班級和成績等虛構學生詳細資料的 Excel 活頁簿。 您可以下載我們的 學生資料模型範例活頁簿 並繼續操作。 您也可以 下載具有完整資料模型的版本。

      取得 & 轉換 (Power Query) Navigator

  4. 現在您擁有包含所有匯入資料表的資料模型，且這些資料表會顯示在 [樞紐分析表 欄位清單] 中。

注意

  • 當您在 Excel 中同時匯入兩個以上的資料表時，會隱含建立模型。
  • 當您使用 Power Pivot 增益集匯入資料時，會明確建立模型。 在增益集中，模型會以類似於 Excel 的索引標籤式版面配置來表示，其中每個索引標籤都包含表格式資料。 請參閱使用 Power Pivot 增益集取得資料，以了解使用 SQL Server 資料庫匯入資料的基本概念。
  • 一個模型可以包含單一資料表。 若要只根據一個資料表建立模型，請選取該資料表，然後按一下 Power Pivot 中的 [新增至資料模型 ]。 如果您想要使用 Power Pivot 功能，例如篩選的資料集、計算結果欄、計算欄位、KPI 和階層，則可以這麼做。
  • 如果您匯入具有主索引鍵和外部索引鍵關聯性的相關資料表，則可以自動建立資料表關聯。 Excel 通常可以使用匯入的關聯資訊，做為資料模型中資料表關聯的基礎。
  • 如需有關如何縮減資料模型大小的秘訣，請參閱 使用 Excel 和 Power Pivot 建立有效使用記憶體的資料模型。
  • 若要進一步探索，請參閱 教學課程：將資料匯入 Excel，以及建立資料模型。

秘訣

如何判斷您的活頁簿是否有資料模型？ 移至 Power Pivot>管理。 如果您看到類似工作表的資料，則模型存在。 請參閱： 瞭解活頁簿資料模型中使用的資料來源 以深入瞭解。

建立資料表之間的關聯性

下一步是在資料表之間建立關聯，以便從其中任何資料表提取資料。 每個資料表必須有主索引鍵或唯一欄位識別碼，例如學生識別碼或班級編號。 最簡單的方法是拖放這些欄位，以在 Power Pivot 的 圖表檢視中連接這些欄位。

  1. 移至 Power Pivot>管理。

  2. 在 [常用 ] 索引標籤上，選取 [圖表檢視]。

  3. 系統會顯示所有匯入的資料表，而且您可能需要花一些時間根據每個資料表的欄位數來調整其大小。

  4. 接著，將主索引鍵欄位從一個資料表拖曳到下一個資料表。 下列範例是我們學生資料表的圖表檢視：
    Power Query 資料模型關聯圖表檢視
    我們已建立下列連結：

    • tbl_Students |學生證 > tbl_Grades |學生證
      換句話說，將 [學生] 資料表中的 [學生識別碼] 欄位拖曳至 [成績] 資料表中的 [學生識別碼] 欄位。
    • tbl_Semesters |學期識別碼 > tbl_Grades |學期
    • tbl_Classes |班級編號 > tbl_Grades |班級編號

    注意

    • 若要建立關聯，欄位名稱不需要相同，但必須是相同的資料類型。
    • 圖表 檢視 中的連接器一側是 “1”，另一側是 “*”。 這表示資料表之間具有一對多關聯性，而這決定了資料在樞紐分析表中的使用方式。 請參閱： 資料模型中資料表之間的關聯性以 深入了解。
    • 連接器僅表示資料表之間存在關聯。 它們實際上不會顯示哪些欄位彼此連結。 若要查看連結，請移至 Power Pivot>管理>設計>關聯>管理關聯性。 在 Excel 中，您可以移至 [資料>關聯]。

使用資料模型建立樞紐分析表或樞紐分析圖

一個 Excel 活頁簿只能包含一個資料模型，但該模型可以包含多個可以在整個活頁簿中重複使用的資料表。 您可以隨時將更多資料表新增至現有的資料模型。

  1. 在 Power Pivot 中，移至 [ 管理]。
  2. 在 [常用 ] 索引標籤上，選取 [樞紐分析表]。
  3. 選取您要放置樞紐分析表的位置：新工作表或目前位置。
  4. 按一下 [確定]，Excel 就會新增一個空白樞紐分析表，並在右側顯示 [欄位清單] 窗格。
    Power Pivot 樞紐分析表欄位清單

接著， 建立樞紐分析表或 建立樞紐分析圖。 如果您已建立資料表之間的關聯性，您可以在樞紐分析表中使用其任何欄位。 我們已經在學生資料模型範例活頁簿中建立關聯。

將現有的不相關資料新增至資料模型

假設您已匯入或複製大量想在模型中使用的資料，但尚未將其新增至資料模型。 將新資料推入模型比您想像的要容易。

  1. 首先選取資料中想要新增至模型的任何儲存格。 它可以是任何資料範圍，但格式化為 Excel 表格 的資料最好。
  2. 使用下列其中一種方法來新增您的資料：
  3. 按一下 Power Pivot>新增至資料模型。
  4. 按一下 [ 插入>樞紐分析表]，然後在 [建立樞紐分析表] 對話方塊中，勾選 [ 將此資料新增至資料模型 ]。

範圍或資料表現在會以連結資料表的形式新增至模型。 若要深入了解如何在模型中使用連結資料表，請參閱 在 Power Pivot 中使用 Excel 連結資料表來新增資料。

新增資料至 Power Pivot 表格

在 Power Pivot 中，您無法像在 Excel 工作表中那樣，直接輸入新列，以將列新增至表格。 但您可以 複製和貼上，或更新來源資料和 重新整理 Power Pivot 模型來新增列。

需要更多協助嗎？

您隨時都可以詢問 Excel 技術社群 中的專家，或在 社群中取得支援。

另請參閱

取得 & 轉換和 Power Pivot 學習指南

在 Excel (Power Query) 中建立、載入或編輯查詢

使用 Excel 與 Power Pivot 建立有效使用記憶體的資料模型

教學課程：將資料匯入 Excel，然後建立資料模型

找出活頁簿資料模型中已使用哪些資料來源

資料模型中資料表之間的關聯