KutoolsforOffice — 一套方案，五大工具。事半功倍。

在 Excel 中从两列创建 Crossjoin（所有组合）——完整指南

作者肖阳修改日期
交叉连接示例

在 Excel 中处理两列列表时，例如产品名称与尺寸、区域与销售代表或学生与课程，您可能需要生成两个列表的所有可能组合。这种操作称为交叉连接（也称笛卡尔积）。本教程提供逐步指导、优缺点分析及实际案例，帮助您选择最适合自己工作流程的方法。

什么是 Crossjoin？

Crossjoin（也称笛卡尔积）是一种操作，用于生成两个列表之间的所有可能组合。在 Excel 中，这意味着将列表 A 的每个项目与列表 B 的每个项目配对，形成完整的组合矩阵。

Crossjoin 在许多实际数据场景中非常有用，例如：

产品变体
通过颜色×尺寸×款式的组合，轻松生成完整产品目录，助您高效管理多样化商品。

销售分析
轻松创建地区、销售代表与季度的多维组合，助您全面洞察业绩表现。

调度与计划
轻松生成员工与班次或学生与课程的所有可能组合，助您高效管理排班与课程安排。

测试与模拟
轻松生成建模、预测或验证所需的多样场景组合，助您精准掌控每一个细节。

示例：

如果您有：
数据源

交叉连接结果将是：
交叉连接结果

在 Excel 中执行 Crossjoin

Excel 提供多种创建 Crossjoin 的方法，最佳选择取决于您使用的 Excel 版本、对公式或工具的熟悉程度以及数据集的大小。以下是四种实用且高效的方法，涵盖从简单公式到更高级的 Power Query 和 VBA。每种方法各有优势，您可以根据工作流程、数据规模和自动化需求进行选择。

方法 1：使用公式进行 Crossjoin（Excel 365）

1.准备数据。将第一个列表放在一列中（例如A 2:A 5 的产品），第二个列表放在另一列中（例如C 2:C 5 的颜色）。
准备数据

2.在目标空白单元格中输入以下公式，按下 ENTER 键后，公式将自动溢出，一次性返回所有生成的组合。详见截图：

=TEXTSPLIT(TEXTJOIN(",", TRUE, A2:A5 & "|" & TRANSPOSE(C2:C5)), "|", ",")
使用公式获取交叉连接

此公式说明：

  • A2:A5 & "|" & TRANSPOSE(C2:C5):将 A 列中的每个值与 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 助您轻松列出多列或多个值的所有组合——无需公式，无需 Power Query，几次点击即可完成！

✅秒级组合颜色、尺寸或产品选项
✅支持多列及灵活输出格式
✅完美适用于产品列表、场景规划和测试
✅简单、快速且 100% 无公式

通过 kutools 列出所有组合

方法 2：使用 Power Query 进行 Crossjoin

Power Query 为所有现代 Excel 版本提供了一种简洁的无代码方法，允许您通过快速点击操作生成 Crossjoin，无需编写任何公式。请按照以下分步指南进行操作。

步骤 1：为每列数据创建表

1.选择第一个数据列表，点击 插入>表格，在 创建表格对话框中，点击 确定，即可生成第一个表格。
为第一列数据创建表格

2.在 表格设计选项卡中，为表格重命名，轻松实现后续高效引用。
为表格命名

3.重复相同步骤，将另一列数据转换为表格并命名。
为第二列数据创建表格并重命名

步骤 2：导入表并加载为连接

1.选择第一个表格，点击 数据>自表格/范围加载，参见截图：
点击 数据 > 从表/范围

2.在打开的 Power Query 编辑器 窗口中，点击 关闭并加载 > 关闭并加载，即可返回 主页选项卡。
点击关闭并加载命令

3.将弹出 导入数据对话框，选择 仅创建连接选项后，点击 确定。
选择仅创建连接选项

4.右侧将显示 查询和连接窗格，展示已有的仅连接查询。
查询和连接面板显示第一个连接

5.重复相同步骤，将第二个表格加载为仅连接查询，您将在 查询和连接窗格中看到它与第一个表格并列显示。
将第二个表添加到连接

步骤 3：创建引用查询和自定义列

1.右键点击您想作为交叉连接中第一个表格的查询，选择 引用。
选择引用

2.在 Power Query 编辑器 窗口中，切换到 添加列选项卡，点击 自定义列，参见截图：
点击自定义列

3.在 自定义列对话框中，自定义列公式框内输入您要用于交叉连接的另一表名，然后点击 确定按钮。
输入公式

注意：
如果您的查询名称中包含空格（例如 Product Color），在自定义列公式框中输入时，必须使用#"查询名称"的格式括起。例如，Product Color 应写为#“Product Color“。

4.将出现新的自定义列，点击 展开按钮即可查看其内容。
点击展开按钮以显示内容

5.在展开的面板中，确保勾选您要连接的列名，然后点击 确定，轻松完成设置。
确保选中你想要连接的列名

6.您现在将看到两个表格生成的所有组合结果。
从两个表生成的所有组合

步骤 4：将数据加载到工作表

转到 “开始”选项卡，点击 关闭并加载，然后选择 关闭并加载。所有组合的表格将被加载到新工作表中，轻松管理数据。
包含所有组合的表将加载到新工作表中→包含所有组合的表将加载到新工作表中

优点

  • 处理大数据：数千行数据表现优异
  • 可重用且可刷新：如果向来源区域中添加更多数据，只需刷新查询，结果将自动更新

缺点

  • 步骤稍多
  • 需要基本的 Power Query 知识

方法 3：使用数据透视表进行 Crossjoin

该方法虽然间接，但在需要生成所有组合且不想编写公式时，效果出乎意料地好。特别适合喜欢通过可视化点击操作的用户，或在不支持动态数组公式的 Excel 版本中使用。

1.为数据列表创建两个独立表格，并按照 方法 2 第 1 步进行命名。

2.选择您打算用作第一列的表格，然后转到 插入选项卡，点击 数据透视表，参见截图：
点击插入数据透视表

3.在 从表格或范围创建数据透视表对话框中，选择 现有工作表并指定数据透视表的位置，勾选 将此数据添加到数据模型，然后点击 确定，轻松完成设置。
选择数据透视表的位置

4.当 数据透视表字段窗格出现在右侧时，勾选表中的列名，即可自动添加到 行区域。
勾选列名以添加到行区域

5.然后切换到 所有选项卡，在 数据透视表字段窗格中，选择您想作为交叉连接第二列的表格，勾选其列名以添加到 行区域。您将获得下图所示的数据透视表：
选择另一个表添加

6.点击数据透视表中的任一单元格，转到 设计选项卡，选择 报表布局>以表格形式显示，即可获得表格形式的数据透视表，见截图：
选择以表格式显示选项

7.继续点击 报表布局>重复显示所有项标签，即可在每一行显示所有项目。
选择重复显示所有项目标签以在每行显示所有项目

8.最后，点击 总计，选择 关闭行和列。
为行和列选择关闭

现在，数据透视表清晰显示列出所有组合，没有汇总行或汇总列。
数据透视表显示整洁的所有组合列表

优点

  • 无需公式或 Power Query
  • 非常简单且直观
  • 适合快速分析

缺点

  • 不具动态性
  • 需要手动操作
  • 输出结果未链接到原始数据

方法 4：使用用户定义函数进行 Crossjoin（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 中的 Crossjoin 提供多种灵活高效的解决方案，允许您根据具体需求和工作环境选择最合适的方法：

  • 公式采用Excel36 5 中的动态数组公式，极速生成结果，无需编程，完美满足偏好原生公式的轻量用户需求。
  • Power Query 提供清晰且可复用的流程，轻松处理大规模数据集，是数据清洗和自动化报告的理想利器，助您高效提升工作效率。
  • 该 数据透视表方法虽然不算直接，但对熟练用户来说极为高效且直观。
  • 该 VBA 用户定义函数 具备极致可定制性，专为需要集成复杂宏代码的场景量身打造，助您轻松实现高效自动化！

最佳办公效率工具

🤖KUTOOLS AI 助手：基于以下内容革新数据分析 智能执行   |  生成代码|  创建公式  |  数据分析及生成图表|  调用 Kutools Functions……
热门功能：查找、突出显示或标记重复项   |  删除空白行   |  合并列或不丢失数据的单元格   |  不使用公式的四舍五入……
高级 LOOKUP：多条件 VLookup  |  多值 VLookup  |   跨多张表 VLookup   |   模糊查找……
高级下拉列表：快速创建下拉列表   |  联动下拉列表   |  多选下拉列表……
列管理器：添加指定数量的列|移动列|切换隐藏列的可见状态|比较范围和列……
特色功能：网格聚焦   |  设计视图   |增强编辑栏   | 工作簿和表管理器   |  资源库（自动文本）|  日期提取   |  汇总工作表  |  加密单元格   | 发送邮件按列表   |  超级筛选   |   特殊筛选（筛选斜体单元格/删除线……） ……
前 15 大工具集：12 文本工具（添加文本，删除特定字符，……）|   50+图表类型（甘特图，……）|   40+ 实用 公式（基于生日计算年龄，……）|   19 插入工具（插入二维码，插入图片来自路径，……）|   12 转换工具（小写金额转大写，汇率转换，……）|   7 合并和拆分工具（高级合并行，分割单元格，……）|…… 以及更多
在您的首选语言中使用 Kutools——支持英语、西班牙语、德语、法语、中文及 40 多种语言！

通过 Kutools for Excel，轻松提升您的 Excel 技能，体验前所未有的高效工作方式。Kutools for Excel 汇集 300 多项强大功能，助您大幅提升生产力，节省宝贵时间。立即点击，获取您最需要的实用功能……

Office Tab 为 Office 带来选项卡界面，让您的工作更加轻松

  • 支持在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中开启选项卡式编辑与阅读，提升办公效率，轻松管理多文档！
  • 在同一窗口的新选项卡中打开和创建多个文档，而非在新 windows 中打开。
  • 提升您的生产力 50%，每天帮您减少数百次鼠标点击操作！

所有 Kutools 加载项。一键安装

Kutools for Office 套件集成了 Excel、Word、Outlook 和 PowerPoint 加载项，以及 Office Tab Pro，完美满足跨 Office 应用团队的高效协作需求。

ExcelWordOutlook标签PowerPoint
  • 一体化套件— Excel、Word、Outlook 和 PowerPoint 加载项 + Office Tab Pro
  • 一键安装，一张许可— 几分钟即可设置完成（支持 MSI）
  • 协同工作更高效— 跨 Office 应用 s 实现精简生产力
  • 30 天全功能试用— 无需注册，无需信用卡
  • 最超值— 较购买单个加载项更优惠