建立 Excel 中資料表之間的關聯性

您是否曾使用 VLOOKUP 將某一資料表中的欄送至另一個資料表?現在 Excel 有內建的資料模型,VLOOKUP 已過時了。您可以根據每個資料表中相對應的資料,建立兩個資料表間的關聯。接著,您可以建立 Power View 工作表,並使用每個資料表的欄位建立樞紐分析表與其他報表,即使資料表是來自不同來源也可以。例如,您有客戶的銷售資料,可能會想要匯入銷售資料並建立時間智慧資料的關聯,以便依年度和月份分析銷售模式。

樞紐分析表和 Power View 欄位清單會列出活頁簿中的所有資料表。

您的瀏覽器不支援視訊。 安裝 Microsoft Silverlight、Adobe Flash Player 或 Internet Explorer 9。

如果您要從關聯式資料庫匯入相關的資料表,Excel 往往可以在幕後建置的資料模型中建立關聯。 否則,您就需要手動建立關聯。

  1. 請確認活頁簿包含至少兩個資料表,而且每個資料表都有資料欄對應到另一個資料表中的資料欄。

  2. 將資料格式化為資料表,或

    在新工作表中,將外部資料匯入為資料表

  3. 為每個資料表取一個有意義的名稱:在 [資料表工具] 中,按一下 [設計][表格名稱]>輸入名稱。

  4. 驗證其中一個資料表內的欄具備唯一資料值,沒有重複。 Excel 只能在欄包含唯一值的情形下建立關聯。

    例如,若要建立客戶銷售資料與時間智慧之間的關聯,這兩個資料表必須包含相同格式的日期 (例如1/1/2012),而且至少有一個資料表 (時間智慧) 中列出的日期只在資料欄中出現一次。

  5. 按一下 [資料][關聯圖]

如果 [關聯圖] 呈現灰色而無法使用,這是因為活頁簿中只有一個資料表。

  1. [管理關聯] 對話方塊中,按一下 [新增]

  2. [建立關聯] 對話方塊中,按一下 [表格] 的箭號,並從清單中選取資料表。若為一對多關聯,這個資料表應該位於多端。以我們的客戶和時間智慧為例,您應該要先選擇客戶銷售資料表,因為大多數的銷售可能會發生在任何一天。

  3. 在選取 [欄 (外部)] 時,選取含有 [相關欄 (主要)] 相關資料的欄。例如,如果兩個資料表中都有某個日期欄,您現在就可以選擇該欄。

  4. 選取 [關聯資料表] 時,請選取至少有一個資料欄與您剛才在 [資料表] 中選取之資料表相關聯的資料表。

  5. 選取 [相關欄 (主要)] 時,請選取具有唯一值的欄,這些值應與您為 [欄] 選取之欄中的值相符。

  6. 按一下 [確定]

深入了解 Excel 中資料表之間的關聯性

關聯性的相關附註

範例:建立時間智慧資料與航班資料的關聯

「可能需要表格之間的關聯」

步驟1:決定讓哪些資料表建立關聯

步驟 2:找出可以用來建立資料表間路徑的欄

關聯性的相關附註

  • 當您將不同資料表中的欄位拖曳到樞紐分析表欄位清單時,您就會知道關聯是否存在。 如果您沒有收到建立關聯的提示,則代表 Excel 已有建立資料關聯所需的關聯資訊。

  • 建立關聯的方式類似於使用 VLOOKUP:資料欄必須包含相符的資料,如此 Excel 才能交互參照某個資料表中的資料列與另一個資料表。在時間智慧的範例中,客戶資料表必須具備同時存在於時間智慧資料表的日期值。

  • 在資料模型中,資料表關聯可以是一對一 (每位乘客都有一份登機證) 或一對多 (每趟航班都有許多乘客) 的關係,但不會是多對多。多對多關聯會產生循環相依性錯誤訊息,例如「偵測出循環相依性」。如果您直接連結兩個具有多對多或間接關聯 (在各自關聯中為一對多形式,但在端對端檢視時為多對多形式的資料表關聯鏈) 的資料表,就會產生此錯誤訊息。詳細資訊請參閱資料模型中資料表之間的關聯

  • 兩欄中的資料類型必須相容。詳情請參閱 Excel 資料模型中的資料類型

  • 您可以用其他更直覺的方式建立關聯,特別是如果不確定要使用哪些欄的話。 請參閱在 Power Pivot 圖表檢視中建立關聯

範例:建立時間智慧資料與航班資料的關聯

您可以用 Microsoft Azure Marketplace 上的免費資料來了解資料表關聯和時間智慧。其中的一些資料集相當大,需要快速的網際網路連線才能在合理的時間內完成資料下載。

  1. 啟動 Microsoft Excel 中的 Power Pivot 增益集並開啟 Power Pivot 視窗

  2. 按一下 [取得外部資料] > [從資料服務] > [從 Microsoft Azure Marketplace]。Microsoft Azure Marketplace 首頁會在 [資料表匯入精靈] 中開啟。

  3. [價格] 底下,按一下 [免費]

  4. [類別] 底下,按一下 [科學及統計資料]

  5. 尋找 [DateStream],然後按一下 [訂閱]。 時間智慧資料摘要的詳細資訊。

  6. 輸入您的 Microsoft 帳戶,然後按一下 [登入]。預覽資料應該會出現在視窗中。

  7. 捲動到底端,按一下 [選取查詢]

  8. [下一步]

  9. 選擇 [BasicCalendarUS],然後按一下 [完成] 匯入資料。透過快速的網際網路連線來進行,匯入約需一分鐘的時間。完成之後,您應該會看到已傳輸 73,414 列的狀態報表。按一下 [關閉]

  10. 按一下 [取得外部資料] > [從資料服務] > [從 Microsoft Azure Marketplace] 以匯入第二個資料集。

  11. [類型] 底下,按一下 [資料]

  12. [價格] 底下,按一下 [免費]

  13. 尋找 [US Air Carrier Flight Delays],然後按一下 [選取]

  14. 捲動到底端,按一下 [選取查詢]

  15. [下一步]

  16. 按一下 [完成] 匯出資料。透過快速網際網路連線,匯入可能要 15分鐘。完成之後,您應該會看到已傳輸 2,427,284 列的狀態報表。按一下 [關閉]。現在,您的資料模型中應該已經有兩個資料表。要在它們之間建立關聯,我們需要各個資料表中有相容的欄。

  17. 請注意,[BasicCalendarUS] 中的 [DateKey] 格式為 1/1/2012 12:00:00 AM。On_Time_Performance 資料表也有日期時間欄 [FlightDate],其數值所指定格式同樣是:1/1/2012 12:00:00 AM。這兩個資料欄包含相符的資料、相同資料類型,以及至少有一者的欄 (DateKey) 只包含唯一值。在接下來的幾個步驟中,您將會使用這些欄來建立資料表關聯。

  18. 在 Power Pivot 視窗中,按一下 [樞紐分析表],在新的或現有工作表建立樞紐分析表。

  19. 在 [欄位清單] 中,展開 [On_Time_Performance] 並按一下 [ArrDelayMinutes] 將它新增至 [值] 區域。在樞紐分析表中,您應該會看到班機延遲的時間總計,以分鐘為單位。

  20. 展開 [BasicCalendarUS],然後按一下 [MonthInCalendar],將它加入至 [列] 區域。

  21. 請注意,現在樞紐分析表會列出月份,但每個月的時間總計 (以分鐘為單位) 是相同的。重複而相同的值表示需要關聯。

  22. 在 [欄位清單] 的「資料表之間可能需要關聯」,按一下 [建立]

  23. 在 [關聯資料表] 中選取 [On_Time_Performance],並在 [相關欄 (主要)] 中選取 [FlightDate]

  24. 在 [表格] 中選取 [BasicCalendarUS],在 [欄 (外部)] 中選取 [DateKey]。按一下 [確定] 建立關聯。

  25. 請注意,延遲的分鐘數總和現在會隨著每個月而變化。

  26. [BasicCalendarUS] 中,將 [YearKey] 拖曳到 [MonthInCalendar] 上方的 [列] 區域。

您現在可以按照年度和月份分割航班延遲狀況,或是行事曆中的其他值。

提示: 根據預設,月份會依照字母順序列出。您可以使用 Power Pivot 增益集變更排序方式,使月份依時間順序顯示。

  1. 確定 [BasicCalendarUS] 資料表已在 Power Pivot 視窗中開啟。

  2. 在 [常用] 資料表,按一下 [循欄排序]

  3. 在 [排序],選擇 [MonthInCalendar]

  4. 在 [條件],選擇 [MonthOfYear]

樞紐分析表現在會依照一年當中的月份數字 (10、11) 排序每個月份-年度的組合 (2011 年 10 月、2011 年 11 月)。變更排序順序很簡單,因為 [DateStream] 摘要提供所有必要的欄,讓這個案例能順利運作。如果您使用不同的時間智慧資料表,步驟也會有所不同。

「可能需要資料表之間有關聯性」

當您新增欄位至樞紐分析表時,系統會告知您是否需要建立資料表關聯,才能讓您在樞紐分析表中選取的欄位具有意義。

在需要關聯時顯示的 [建立] 按鈕

雖然 Excel 可以在需要關聯時提示您,但卻無法判斷要使用哪些資料表和欄,或甚至是否有可能建立資料表關聯。嘗試執行下列步驟,以取得所需的答案。

步驟1:決定讓哪些資料表建立關聯

如果模型只包含幾個資料表,您可能一眼就能看出哪些是需要使用的。但在較大的模型中,您或許會需要一些協助。有一個方法是使用 Power Pivot 增益集中的 [圖表檢視]。[圖表檢視] 能以視覺化的方式呈現資料模型中的所有資料表。您可以使用 [圖表檢視],快速判斷哪些資料表與模型的其餘部分是分開的。

以圖表檢視顯示已中斷連線的資料表

附註: 您有可能建立了不明確的關聯,因而無法在樞紐分析表或 Power View 報表中使用。假設您的所有資料表都與模型中的其他資料表存有若干關聯,但是當您嘗試合併不同資料表中的欄位時,卻出現「資料表之間可能需要關聯」的訊息。最可能的原因是,您建立了多對多關聯。針對您要使用的資料表,如果您追蹤資料表關聯的連鎖關係,則可能會發現您有兩個或多個一對多的資料表關聯。並沒有輕鬆的因應措施能適用於每一種狀況,但您也許可以嘗試建立計算結果欄,將您想要使用的欄合併成一份資料表。

步驟 2:找出可以用來建立路徑並往來於資料表之間的欄。

當您識別出哪個資料表與模型的其餘部分不相連時,檢閱其欄來判斷是否模型中有其他地方的欄包含相符的值。

例如,假設您有一個模型包含依區域劃分的產品銷售資料,而您隨後匯入人口統計資料,查詢每個區域中的銷售狀況和人口統計趨勢之間是否有相互關聯。因為人口統計資料來自不同的資料來源,其資料表一開始與模型的其餘部分是隔離的。若要將人口統計資料與模型的其餘部分整合,您必須先在人口統計資料表中找到欄位,使它能夠對應您已在使用的資料表。舉例來說,如果人口統計資料是依地區劃分,而您的銷售資料也依照地區來記載銷售狀況,您就可以尋找兩者之間共同的欄,例如州、郵遞區號或地區,在兩個資料集之間建立關聯以便提供查閱。

除了相符的值,建立關聯還有幾個額外的需求:

  • 在查閱欄中的資料值必須是唯一的。換句話說,資料欄不能包含重複的值。在資料模型中,Null 和空白字串相當於空白,這是獨特的資料值。這表示,您無法在查閱欄中有多個 Null。

  • 來源欄和查閱欄的資料類型必須相容。如需資料類型的詳細資訊,請參閱資料模型中的資料類型

若要深入了解表格關聯,請參閱資料模型中表格之間的關聯

頁面頂端

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×