在 Excel 中建立資料模型

在 Excel 中建立資料模型

資料模型可讓您整合來自多個資料表的資料,有效地在 Excel 活頁簿中建立關聯式資料來源。 在 Excel 中,資料模型是透明的,可提供樞紐分析表和樞紐分析圖中使用的表格式資料。 資料模型在欄位清單中會以表格集合的形式進行視覺化,在大部分情況下,您甚至不會知道它在那裡。

您必須先取得一些資料,才能開始使用資料模型。 我們將使用「取得 & 轉換(Power Query)」體驗,因此您可能會想要執行並觀賞影片,或遵循我們在取得 & 轉換與 Power Pivot 的學習指南。

Power Pivot 在哪裡?

在哪裡可以取得 & 轉換(Power Query)?

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

  • Excel 2013 -Power Query 是包含在 Excel 中的增益集,但需要啟用。 移至 [檔案] > [ >增益集]選項,然後在窗格底部的 [管理] 下拉式清單中,選取 [ COM 增益集] > [移至]。 核取 [ Microsoft Power Query For Excel],然後選取[確定]加以啟用。 [ Power Query ] 索引標籤會新增至功能區。

  • Excel 2010 -下載並安裝 Power Query 增益集。. 啟動後,[ Power Query ] 索引標籤即會新增至功能區。

開始使用

首先,您需要取得一些資料。

  1. 在 Excel 2016 和 Microsoft 365 Excel中,使用資料>取得 & 轉換資料>取得資料,以匯入來自任何數目的外部資料源的資料,例如文字檔、Excel 活頁簿、網站、Microsoft Access、SQL Server 或其他包含多個相關資料表的關聯式資料庫。

    在 Excel 2013 和2010中,移至Power Query >取得外部資料],然後選取您的資料來源。

  2. Excel 會提示您選取表格。 如果您想要從同一個資料來源取得多個資料表,請核取 [啟用選取多個表格] 選項。 當您選取多個表格時,Excel 會自動為您建立資料模型。

    附註: 在這些範例中,我們使用的 Excel 活頁簿包含班級與成績上的虛構學生詳細資料。 您可以下載我們的 [學生資料模型] 範例活頁簿,並追蹤。 您也可以下載含有已完成資料模型的版本。.

    取得 & 轉換(Power Query)瀏覽器
  3. 選取一或多個資料表,然後按一下 [載入]。

    如果您需要編輯來源資料,您可以選擇 [編輯] 選項。 如需詳細資訊,請參閱:查詢編輯器簡介(Power Query)

您現在已有一個資料模型,其中包含所有您已匯入的資料表,而它們將會顯示在 [樞紐分析表欄位清單] 中。

附註: 

  • 當您在 Excel 中同時匯入兩個或多個表格時,會隱式建立模型。

  • 當您使用 Power Pivot 增益集匯入資料時,會明確建立模型。 在增益集中,模型會以分頁版面配置來表示,就像 Excel 一樣,每個索引標籤都包含表格式資料。 請參閱使用 Power Pivot 增益集取得資料,以瞭解使用 SQL Server 資料庫的資料匯入的基本概念。

  • 模型可以包含單一資料表。 若要建立以單一資料表為基礎的模型,請選取資料表,然後按一下 Power Pivot中的 [新增至資料模型]。 如果您想要使用 Power Pivot 功能(例如經過篩選的資料集、計算結果欄、匯出欄位、Kpi 及階層),您可能會執行此動作。

  • 如果您匯入具有主鍵與外鍵關聯性的相關資料表,就可以自動建立資料表關聯。 Excel 通常可以使用匯入的關聯資訊做為資料模型中的資料表關聯基礎。

  • 如需如何縮減資料模型大小的秘訣,請參閱使用 Excel 和 Power Pivot 建立有效使用記憶體的資料模型

  • 如需進一步探索,請參閱教學課程:將資料匯入 Excel,以及建立資料模型

提示: 如何判斷您的活頁簿是否有資料模型? 移至Power Pivot >管理]。 如果您看到類似工作表的資料,則表示存在模型。 請參閱:找出在活頁簿資料模型中使用哪些資料來源來進一步瞭解。

建立表格之間的關聯

下一個步驟是建立資料表之間的關聯,因此您可以從其中任何一項提取資料。 每個資料表都需要有主鍵或唯一的欄位識別碼,就像是學生識別碼或班級編號等。 最簡單的方法是拖放那些欄位,在 Powerpivot 的 [圖表] 視圖中將它們連線。

  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 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

另請參閱

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

查詢編輯器簡介 (Power Query)

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

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

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

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

需要更多協助?

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×