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

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

您的瀏覽器不支援視訊。

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

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

  2. 執行下列其中一項操作:將資料格式化為表格,或在新 工作表中將外部資料以表格的形式輸入。

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

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

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

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

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

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

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

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

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

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

  6. 按一下 [確定]

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

關聯性的相關附註

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

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

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

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

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

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

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

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

  2. 按一下[從資料服務> [從>取得 Microsoft Azure 資料。 資料 Microsoft Azure 市集首頁會在資料表導入精靈中開啟。

  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。

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

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

頁面頂端

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Office Insiders

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×