資料模型可讓您整合多個表格中的資料,有效地在 Excel 活頁簿內建立關聯式資料來源。 在 Excel 中,資料模型會以透明的方式使用,提供用於樞紐分析表和樞紐分析圖的表格式資料。 資料模型會以視覺化的方式呈現為 [欄位清單] 中的資料表集合,而在大多數情況下,您甚至永遠無法知道它存在。
開始使用資料模型之前,您必須先取得一些資料。 因此,我們會使用「取得&轉換 (Power Query) 體驗」,因此您可能會想要退後一步並觀看影片,或依照我們的取得&轉換與 Power Pivot 學習指南。
-
Excel 2016 & Microsoft 365 Excel - 功能區中包含 Power Pivot。
-
Excel 2013 - Power Pivot 是 excel 2013 Office 專業增強版版本的一部分,但預設不會啟用。 深入瞭解 如何啟動適用于 Excel 2013 的 Power Pivot 增益集。
-
Excel 2010 - 下載 Power Pivot 增益集,然後 安裝 Power Pivot 增益集。
-
Excel 2016 & Microsoft 365 Excel - 取得&轉換 (Power Query) 已與 [資料] 索引標籤上的 Excel 整合。
-
Excel 2013 - Power Query 是 Excel 隨附的增益集,但必須啟用。 移至 [ 檔案 > 選項 > 載入宏],然後在窗格底部的 [ 管理 ] 下拉式清單中,選取 [COM 載入 宏] > [移]。 請檢查Microsoft Power Query版 Excel,然後選取 [確定] 來啟用它。 Power Query索引標籤會新增至功能區。
-
Excel 2010 -下載並安裝Power Query增益集。. 啟用後,Power Query索引標籤會新增至功能區。
開始使用
首先,您需要取得一些資料。
-
在 Excel 2016 和 Microsoft 365 Excel中,使用資料>取得&轉換資料>取得資料以從任何數目的外部資料源匯入資料,例如文字檔、Excel 活頁簿、網站、Microsoft Access、SQL Server 或其他包含多個相關資料表的關係資料庫。
在 Excel 2013 和 2010 中,移至Power Query >取得外部資料],然後選取您的資料來源。
-
Excel 會提示您選取表格。 如果您想要從相同的資料來源取得多個資料表,請核取 [啟用選取多個資料表] 選項。 當您選取多個表格時,Excel 會自動為您建立資料模型。
附註: 在這些範例中,我們使用 Excel 活頁簿,其中包含關於班級和成績的虛構學生詳細資料。 您可以下載我們的 [學生資料模型] 範例活頁簿,並依照下列步驟進行。 您也可以 下載具有已完成資料模型的版本。.
-
選取一或多個資料表,然後按一下 [ 載入]。
如果您需要編輯來源資料,可以選擇 [ 編輯 ] 選項。 如需詳細資料,請參閱:查詢編輯器 (Power Query) 簡介。
您現在有一個資料模型,其中包含您匯入的所有資料表,它們將會顯示在樞紐分析表 欄位清單中。
附註:
-
當您在 Excel 中同時匯入兩個或多個資料表時,會隱含建立模型。
-
當您使用 Power Pivot 增益集匯入資料時,會明確建立模型。 在增益集中,模型會以類似 Excel 的索引標籤式版面配置來表示,其中的每一個索引標籤都包含表格式資料。 請參閱使用 Power Pivot 載入宏取得資料,以瞭解使用SQL Server資料庫匯入資料的基本概念。
-
模型可以包含單一資料表。 若只要根據一個資料表建立模型,請選取該資料表,然後按一下 [在 Power Pivot中 新增至資料模型 ]。 如果您想要使用篩選的資料集、計算結果欄、匯出欄位、KPI 和階層等 Power Pivot 功能,您可以這麼做。
-
如果您匯入具有主鍵和外鍵關聯的相關資料表,就可以自動建立資料表關聯。 Excel 通常可以使用匯入的關聯資訊做為資料模型中資料表關聯的基礎。
-
如需如何縮減資料模型大小的秘訣,請參閱 使用 Excel 和 Power Pivot 建立有效使用記憶體的資料模型。
-
如需進一步探索,請參閱 教學課程:將資料匯入 Excel 和建立資料模型。
提示: 如何分辨活頁簿是否有資料模型? 移至 Power Pivot > 管理]。 如果您看到類似工作表的資料,則表示模型存在。 請參閱: 瞭解活頁簿資料模型中使用哪些資料來源 以深入瞭解。
建立資料表之間的關聯
下一個步驟是建立資料表之間的關聯,以便從其中任何一個資料表屑取資料。 每個資料表都必須有主鍵或唯一欄位識別碼,例如 [學生識別碼] 或 [班級編號]。 最簡單的方式是在 Power Pivot 的 [圖表檢視] 中拖放這些欄位來連接它們。
-
移至[管理] > Power Pivot。
-
在 [常 用] 索引 標籤上,選 取 [圖表檢視]。
-
所有匯入的資料表都會顯示出來,而且您可能需要一些時間來根據每個資料表的欄位數來調整它們的大小。
-
接著,將主鍵欄位從一個資料表拖曳到下一個資料表。 下列範例是我們學生資料表的圖表檢視:
我們已建立下列連結:
-
tbl_Students |學生識別碼> tbl_Grades |學生識別碼
換句話說,將 [學生識別碼] 欄位從 [學生] 資料表拖曳到 [成績] 資料表中的 [學生識別碼] 欄位。
-
tbl_Semesters |學期 ID > tbl_Grades |學期
-
tbl_Classes |課程編號> tbl_Grades |班級編號
附註:
-
功能變數名稱不需要相同,才能建立關聯,但必須是相同的資料類型。
-
[ 圖表檢視 ] 中的連接器一端有一個 「1」,另一端有一個 「*」。 這表示資料表之間有一對多關聯性,這會決定樞紐分析表中資料的使用方式。 若要深入瞭解,請參閱: 資料模型中資料表之間的關聯 。
-
連接器只會指出資料表之間有關聯。 它們並不會實際顯示哪些欄位彼此連結。 若要查看連結,請移至Power Pivot >管理>設計>關聯>管理關聯性。 在 Excel 中,您可以移至 [資料 > 關聯]。
-
使用資料模型建立樞紐分析表或樞紐分析圖
Excel 活頁簿只能包含一個資料模型,但該模型可以包含多個表格,可在整個活頁簿中重複使用。 您可以隨時新增更多資料表至現有的資料模型。
-
在 Power Pivot中,移至 [管理]。
-
在 [常 用] 索引 標籤上,選取 [ 樞紐分析表]。
-
選取您要放置樞紐分析表的位置:新的工作表或目前的位置。
-
按一下 [確定],Excel 就會新增空白的樞紐分析表,右側會顯示 [欄位清單] 窗格。
接下來, 建立樞紐分析表,或 建立樞紐分析表。 如果您已經建立資料表之間的關聯,您可以在樞紐分析表中使用其任何欄位。 我們已經在學生資料模型範例活頁簿中建立關聯。
將現有不相關的資料新增至資料模型
假設您已匯入或複製許多要在模型中使用的資料,但尚未將這些資料新增至資料模型。 將新資料推入模型比您認為的更簡單。
-
首先,選取您要新增至模型之資料中的任何儲存格。 它可以是任何範圍的資料,但格式化為 Excel 表格 的資料是最好的。
-
使用下列其中一種方法來新增您的資料:
-
按一下 [Power Pivot > 新增至資料模型]。
-
按一下 [插入 > 樞紐分析表],然後核取 [建立樞紐分析表] 對話方塊中的 [將 此資料新增至資料模型 ]。
範圍或資料表現在會新增到模型中做為連結資料表。 若要深入瞭解如何在模型中使用連結表格,請參閱 在 Power Pivot 中使用 Excel 連結表格新增資料。
將資料新增至 Power Pivot 資料表
在 Power Pivot中,您無法像在 Excel 工作表中一樣直接在新的列中輸入,將列新增至表格。 但您可以 複製並貼上,或更新來源資料並 重新整理 Power Pivot 模型,藉此新增列。
需要更多協助嗎?
您可以隨時詢問 Excel 技術社群中的專家,或是在 Answers 社群取得支援。