如何以数据库全外连接形式合并Excel多个动态数组

你需要将两个动态数组生成笛卡尔积（即每个员工与所有科目组合，对应SQL的CROSS JOIN，而非FULL JOIN），以下是两种可行的Excel动态数组公式方案：

方案1：INDEX+SEQUENCE+TOCOL+HSTACK组合 #

假设你的Staff数组引用为A2:B4，Subjects数组引用为D2:D5，直接使用以下公式即可生成目标数组：

=HSTACK(
    INDEX(A2:B4, SEQUENCE(ROWS(A2:B4)*ROWS(D2:D5),,1,1/ROWS(D2:D5)), COLUMN(A2:B4)),
    TOCOL(INDEX(D2:D5, SEQUENCE(ROWS(D2:D5)), SEQUENCE(,ROWS(A2:B4))), 2)
)

公式解析： #

• Staff重复列：SEQUENCE(ROWS(A2:B4)*ROWS(D2:D5),,1,1/ROWS(D2:D5))生成步长为1/科目数的序列，让INDEX循环取出每个员工行，重复对应科目次数（比如3个员工×4个科目，序列为1,1,1,1,2,2,2,2,3,3,3,3）。
• Subjects重复列：INDEX(D2:D5, SEQUENCE(ROWS(D2:D5)), SEQUENCE(,ROWS(A2:B4)))将科目数组横向重复员工数次（4行→4行3列），再用TOCOL(...,2)转成单列，得到按顺序循环的科目列表。
• 合并数组：HSTACK将两部分结果横向合并，得到最终的笛卡尔积数组。

方案2：MAKEARRAY（Excel 365最新版本支持） #

如果你的Excel支持MAKEARRAY函数，可以用更直观的Lambda逻辑实现：

=MAKEARRAY(
    ROWS(A2:B4)*ROWS(D2:D5),
    COLUMNS(A2:B4)+COLUMNS(D2:D5),
    LAMBDA(r,c,
        IF(c<=COLUMNS(A2:B4),
            INDEX(A2:B4, CEILING(r/ROWS(D2:D5),1), c),
            INDEX(D2:D5, MOD(r-1,ROWS(D2:D5))+1, c-COLUMNS(A2:B4))
        )
    )
)

公式解析： #

• MAKEARRAY指定生成数组的总行数（员工数×科目数）和总列数（员工列数+科目列数）。
• 通过LAMBDA(r,c)遍历每个单元格：
  • 当列数≤员工列数时，用CEILING(r/科目数,1)计算当前行对应的员工行号，取出员工数据。
  • 当列数>员工列数时，用MOD(r-1,科目数)+1计算当前行对应的科目行号，取出科目数据。

注意事项： #

如果Staff或Subjects是动态溢出数组（比如用FILTER、UNIQUE生成的动态结果），直接把公式中的A2:B4、D2:D5替换为对应的动态数组引用即可，公式会自动适配行数变化。

内容的提问来源于stack exchange，提问作者David H