在 Excel 中從 2 個欄位建立交叉聯結（所有組合）— 完整指南

作者曉陽修改日期2025-11-12

當您在 Excel 中處理兩個清單（例如產品名稱與尺寸、地區與業務代表，或學生與課程）時，可能需要產生這兩個清單的所有可能組合。這種操作稱為「交叉聯集」（Crossjoin，亦稱笛卡爾積）。本教學將逐步說明操作方法、優缺點及實際範例，協助您選擇最適合自身工作流程的方式。

什麼是交叉聯結？

交叉聯結（又稱笛卡爾積）是一種用於生成兩個清單間所有可能組合的操作。在 Excel 中，這代表將清單 A 的每一項與清單 B 的每一項進行配對，從而產生完整的組合矩陣。

交叉聯結在許多實際資料情境中極具幫助，例如：

產品變體
結合顏色 × 尺寸 × 款式，輕鬆生成完整產品目錄！

銷售分析
建立地區 × 業務代表 × 季度的組合。

排程與規劃
自動產生員工×班別或學生×課程的所有可能組合，輕鬆掌握排程全貌！

測試與模擬
為建模、預測或驗證，快速產生多元情境組合。

範例：

如果您有：
資料來源

交叉聯集結果將為：
交叉連接結果

在 Excel 中執行交叉聯結

Excel 提供多種建立交叉聯結的方式，最佳選擇取決於您所用的 Excel 版本、對公式或工具的熟悉度，以及資料集的規模。以下列出四種實用又高效的方法——從簡易公式到進階擴充工具（如適用於 Excel 的 Microsoft Power Query 與 VBA）。每種方法各具優勢，您可依自身工作流程、資料量大小及自動化需求，挑選最適合的方案。

方法 1：使用公式執行交叉聯結（Excel 365）

1. 準備您的資料：將第一個清單放在一欄中（例如 A2:A5 為產品），第二個清單放在另一欄中（例如 C2:C5 為顏色）。
準備資料

2. 在要輸出結果的空白儲存格中輸入下列公式，然後按下 ENTER 鍵。公式將自動溢出，一次返回所有產生的組合。請參閱截圖：

=TEXTSPLIT(TEXTJOIN(",", TRUE, A2:A5 & "|" & TRANSPOSE(C2:C5)), "|", ",")

公式說明：

A2:A5 & “|“ & TRANSPOSE(C2:C5）： 將 A 欄中的每個值與 C 欄中的各個值進行配對。C。
TEXTJOIN(",", TRUE, ……）：將所有配對結果以逗號分隔，合併為單一長字串。
TEXTSPLIT(……， “|“， “，“）：將字串分割為兩欄結構化表格。

簡言之，此公式會建立 A|C 配對，將其合併為單一字串後，再分割成結構化的兩欄表格，有效產生所有可能的組合。

提示：

除了上述公式外，您也可使用以下公式獲得相同結果。

=LET(a,A2:A5,b,C2:C5,
MAKEARRAY(ROWS(a)*ROWS(b),2,
 LAMBDA(r,c,
  IF(c=1, INDEX(a, 1+INT((r-1)/ROWS(b))), INDEX(b, 1+MOD(r-1, ROWS(b))))
 )
))

優點

完全動態
無需輔助欄位
當來源清單變更時自動更新

缺點

需要 Excel 365
公式對初學者而言較為複雜

✨ 列出所有組合 — 一鍵生成所有可能的組合！

厭倦了撰寫複雜公式來取得所有可能組合嗎？透過 Kutools for Excel，您只需幾次點擊，就能立即列出多個欄位或數值的所有組合——無需公式、無需適用於 Excel 的 Microsoft Power Query！

✅幾秒內即可結合顏色、尺寸或產品選項

✅支援多個欄位與彈性輸出格式

✅完美適用於產品清單、情境規劃與測試

✅簡單、快速且 100% 無需公式

使用 Kutools 列出所有組合

立即試用 Kutools for Excel

方法 2：使用適用於 Excel 的 Microsoft Power Query 執行交叉聯結

適用於 Excel 的 Microsoft Power Query 提供簡潔直覺、無需編碼的解決方案，全面支援所有現代版本的 Excel。只需透過幾個快速點選步驟，即可輕鬆建立交叉聯結，完全無需撰寫任何公式。請依照下列逐步說明進行操作。

步驟 1：為每個欄位資料建立表格

1. 選取第一筆資料清單，按一下插入> 表格，在建立表格對話方塊中按一下確定，即可立即獲得您的第一個表格！
為第一欄資料建立表格

2. 在表格設計選項卡中，將表格重新命名為有意義的名稱，方便日後快速引用。
為表格命名

3. 重複相同步驟，將另一欄資料轉換為表格並指定名稱。
為第二欄資料建立表格並重新命名

步驟 2：匯入表格並載入為連線

1. 選取第一個表格，然後點擊資料> 從表格/範圍，詳情請參閱下方截圖：
按一下「資料」>「從表格/範圍」

2. 在開啟的 適用於 Excel 的 Microsoft Power Query 編輯器視窗中，點選位於首頁選項卡中的關閉並載入> 關閉並載入。
按一下「關閉並載入」指令

3. 將出現匯入資料對話方塊，選取僅建立連線選項，然後按一下確定。
選取「僅建立連線」選項

4. 右側將出現查詢與連線窗格，顯示已新增一個僅用於連線的查詢。
「查詢與連線」窗格將顯示第一個連線

5. 重複相同步驟，以僅建立連線的方式載入第二個表格，您將看到它與第一個查詢一同出現在查詢與連線窗格中。
將第二個表格新增至連線

步驟 3：建立參考查詢與自訂欄

1. 在交叉聯集中用作第一個表格的查詢上按一下右鍵，然後選取參考。
選取「參考」

2. 在 適用於 Excel 的 Microsoft Power Query 編輯器視窗中，切換至新增欄選項卡，然後點選自訂欄。請參閱截圖：
按一下「自訂欄位」

3. 在自訂欄對話方塊中，於自訂欄公式方塊內輸入要用於交叉聯集的另一個表格名稱，然後點選確定按鈕。
輸入公式

注意：
若您的查詢名稱包含空格（例如 Product Color），在自訂欄位公式方塊中輸入時，必須使用語法 #“Query Name“ 將其括住。例如，Product Color 應輸入為 #“Product Color“。

4. 將出現新的自訂欄，按一下展開按鈕即可顯示其內容。
按一下「展開」按鈕以顯示其內容

5. 在展開窗格中，確認要聯集的欄位名稱已勾選，然後按一下確定。
確認您要進行連接的欄位名稱已勾選

6. 現在，您將看到由兩個表格所產生的所有組合。
由兩個表格產生的所有組合

步驟 4：將資料載入至工作表

前往首頁索引標籤，按一下關閉並載入＞ 關閉並載入，包含所有組合的表格將載入至新工作表。
→

優點

處理大量資料：輕鬆流暢地應付數千列資料。
可重複使用且能自動更新：只要在來源區域新增更多資料，重新整理查詢後，結果就會自動同步更新。

缺點

步驟稍多
需要基本的適用於 Excel 的 Microsoft Power Query 知識

方法 3：使用資料透視表執行交叉聯結

此方法雖屬間接，卻在無需撰寫公式的情況下，出奇有效地生成所有組合，尤其適合偏好視覺化點選操作的使用者，或使用不支援動態陣列公式的 Excel 版本者。

1. 使用與方法 2 步驟 1 相同的步驟，為資料清單建立兩個獨立表格並命名。步驟 1 的方法 2.

2. 選取要用作第一欄的表格，切換至插入選項卡，並點擊樞紐分析表，請參閱下方截圖：
按一下以插入樞紐分析表

3. 在從表格或範圍建立樞紐分析表對話方塊中，選取現有工作表，指定樞紐分析表的位置，勾選將此資料新增至資料模型，然後按一下確定。
選擇樞紐分析表的位置

4. 當右側出現樞紐分析表欄位窗格時，勾選該表格的欄位名稱，系統將自動將其加入列區域。
勾選欄位名稱以將其加入列區域

5. 接著，在全部標籤頁中，於樞紐分析表欄位窗格內選取要用作交叉聯集第二欄的表格，勾選其欄位名稱並加入列區域，即可獲得如下圖所示的資料透視表：
選取另一個表格以新增它

6. 按一下資料透視表中的任一儲存格，切換至設計選項卡，選取報表版面配置> 以表格形式顯示，即可輕鬆獲得表格形式的資料透視表！請參閱截圖：
選取「以表格格式顯示」選項

7. 繼續點擊報表版面配置＞ 重複顯示所有項目標籤，即可在每一列中完整呈現所有項目。
選取「重複顯示所有項目標籤」以在每一列顯示所有項目

8. 最後，按一下總計＞ 關閉列與欄的總計。
將列與欄設定為「關閉」

現在，樞紐分析表已顯示完整的組合清單，不含任何摘要列或欄。
樞紐分析表顯示出所有組合的清晰清單

優點

無需公式或適用於 Excel 的 Microsoft Power Query
操作直覺且視覺化
適合快速分析

缺點

非動態
需手動操作
輸出結果與原始資料無關聯

方法 4：使用使用者自訂函數執行交叉聯結（Excel 365／Excel 2021 及更新版本）

若您經常需要在兩個清單之間生成所有可能的組合，使用者自訂函數（UDF）能提供乾淨、可重複使用且高度靈活的解決方案。搭配 Excel 365 或 Excel 2021 的溢出陣列輸出功能，更能讓結果完全動態化，以強大又便利的方式自動化交叉聯結作業。

1. 按下 Alt + F11，立即開啟 VBA 編輯器！

2. 接著，按一下插入＞模組，並將下列程式碼複製貼上至空白模組中。

Function CrossJoin(list1 As Range, list2 As Range)
    'Updateby Extendoffice
    Dim arr1, arr2, result()
    Dim i As Long, j As Long, r As Long
    arr1 = list1.Value
    arr2 = list2.Value
    ReDim result(1 To UBound(arr1, 1) * UBound(arr2, 1), 1 To 2)
    r = 1
    For i = 1 To UBound(arr1, 1)
        For j = 1 To UBound(arr2, 1)
            result(r, 1) = arr1(i, 1)
            result(r, 2) = arr2(j, 1)
            r = r + 1
        Next j
    Next i
    CrossJoin = result
End Function

3. 返回 Excel 工作表，在下方輸入公式並按下 Enter 後，Excel 將自動溢出所有組合結果。

=CrossJoin(A2:A5, C2:C5)

使用 VBA 程式碼取得交叉連接

結論

總結來說，在 Excel 中執行交叉聯結有多種靈活且高效的方法，您可以根據自身特定需求與工作環境選擇最適合的方案：

公式善用 Excel 365 的動態陣列公式，無需撰寫程式碼即可快速產出結果，非常適合偏好使用原生公式的輕量級使用者！
適用於 Excel 的 Microsoft Power Query提供清晰且可重複使用的流程，能高效處理大型資料集，是資料清洗與自動化報表的理想選擇！
「資料透視表」方法雖較不直接，但對熟悉此操作的使用者來說，卻直覺又高效！
「VBA 使用者自訂函數」提供極致的自訂彈性，特別適合需整合至複雜巨集程式碼的應用場景。

最佳 Office 生產力工具

🤖	KUTOOLS AI 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多