如何通过Excel 365公式获取两个集合的Cartesian product（交叉连接）

通用公式 #

针对任意列数、任意内容的两个集合，直接用以下公式即可生成笛卡尔积，只需替换公式中的range1和range2为你的两个集合的单元格范围：

=HSTACK(INDEX(range1,ROUNDUP(SEQUENCE(ROWS(range1)*ROWS(range2))/ROWS(range2),0),SEQUENCE(,COLUMNS(range1))),INDEX(range2,MOD(SEQUENCE(ROWS(range1)*ROWS(range2))-1,ROWS(range2))+1,SEQUENCE(,COLUMNS(range2))))

公式原理拆解 #

• 总行数计算：ROWS(range1)*ROWS(range2)得到笛卡尔积的总行数，也就是两个集合行数的乘积。
• 行索引定位：
  • ROUNDUP(SEQUENCE(...)/ROWS(range2),0)：让第一个集合的每一行重复ROWS(range2)次，比如第一个集合有3行、第二个有3行，第一个集合的第1行会对应结果的前3行。
  • MOD(SEQUENCE(...)-1,ROWS(range2))+1：让第二个集合的行循环遍历，比如第二个集合有3行，会按1→2→3→1→2→3...的顺序重复ROWS(range1)次。
• 提取整行内容：INDEX函数配合SEQUENCE(,COLUMNS(rangeX))，可以一次性取出集合中对应行的所有列，不用单独指定每一列。
• 横向拼接：HSTACK把两个集合的结果横向合并，得到完整的笛卡尔积结构。

针对你的例子的实际用法 #

假设你的第一个集合是A1:B3（左侧两列），第二个集合是D1:E3（右侧两列），代入公式后：

=HSTACK(INDEX(A1:B3,ROUNDUP(SEQUENCE(ROWS(A1:B3)*ROWS(D1:E3))/ROWS(D1:E3),0),SEQUENCE(,COLUMNS(A1:B3))),INDEX(D1:E3,MOD(SEQUENCE(ROWS(A1:B3)*ROWS(D1:E3))-1,ROWS(D1:E3))+1,SEQUENCE(,COLUMNS(D1:E3))))

输入后Excel会自动溢出9行结果，每行对应两个集合的一组组合，完全符合笛卡尔积的要求。

内容的提问来源于stack exchange，提问作者Sandra Rossi