无需Power Query，Excel中如何高效实现产品与合同全量配对？

针对1000个产品与33份合同的全量配对需求，以下是比你之前Power Query逆透视更高效的实现方式：

1. 动态数组公式（Excel 365/2021 首选） #

利用Excel动态数组的溢出特性，无需手动下拉，直接生成完整配对：

假设产品数据在A2:A1001（A1为表头“产品”），合同数据在C2:C34（C1为表头“合同”）：

• 生成产品序列列：

  =INDEX(A:A,INT(SEQUENCE(1000*33,1,2,1/33)))
  

• 生成合同序列列：

  =INDEX(C:C,MOD(SEQUENCE(1000*33,1,0),33)+2)
  

如果产品/合同数量不确定，可改用动态计数适配：

• 产品列（自动统计产品数量）：

  =INDEX(A:A,INT(SEQUENCE((COUNTA(A:A)-1)*(COUNTA(C:C)-1),1,2,1/(COUNTA(C:C)-1))))
  

• 合同列（自动统计合同数量）：

  =INDEX(C:C,MOD(SEQUENCE((COUNTA(A:A)-1)*(COUNTA(C:C)-1),1,0),COUNTA(C:C)-1)+2)
  

输入公式后按回车，Excel会自动溢出生成全部33000行配对数据。

2. 简化版Power Query交叉连接 #

无需构造标识列，直接通过查询交叉连接实现：

1. 将产品表和合同表分别导入Power Query（菜单栏「数据」→「自表格/区域」）
2. 右键合同表的查询→「关闭并上载至」→选择「仅创建连接」
3. 打开产品表的查询编辑器，点击「添加列」→「自定义列」，输入公式：=合同表（替换为你的合同查询名）
4. 点击自定义列右侧的「展开」按钮，选择「展开到新行」
5. 删除冗余列后，点击「关闭并上载」即可得到全量配对数据

这个方法本质是直接实现两张表的笛卡尔积，操作步骤比逆透视简洁很多，处理1000+33的规模毫无压力。

3. VBA宏（适合重复批量操作） #

如果需要频繁生成这类配对，写个宏一键生成：

1. 按Alt+F11打开VBA编辑器，插入新模块
2. 粘贴以下代码（根据实际表名、列范围调整）：

Sub GenerateFullPairing()
    Dim prodRng As Range, contRng As Range
    Dim prodArr As Variant, contArr As Variant
    Dim resultArr() As Variant
    Dim i As Long, j As Long, k As Long
    
    ' 定义产品和合同数据范围
    Set prodRng = ThisWorkbook.Sheets("产品表").Range("A2:A" & Sheets("产品表").Cells(Rows.Count, "A").End(xlUp).Row)
    Set contRng = ThisWorkbook.Sheets("合同表").Range("C2:C" & Sheets("合同表").Cells(Rows.Count, "C").End(xlUp).Row)
    
    prodArr = prodRng.Value
    contArr = contRng.Value
    ReDim resultArr(1 To UBound(prodArr) * UBound(contArr), 1 To 2)
    
    k = 1
    For i = 1 To UBound(prodArr)
        For j = 1 To UBound(contArr)
            resultArr(k, 1) = prodArr(i, 1)
            resultArr(k, 2) = contArr(j, 1)
            k = k + 1
        Next j
    Next i
    
    ' 输出结果到新工作表
    Sheets.Add.Name = "配对结果"
    Sheets("配对结果").Range("A1:B1") = Array("产品", "合同")
    Sheets("配对结果").Range("A2").Resize(UBound(resultArr), 2) = resultArr
End Sub

3. 回到Excel界面，按Alt+F8运行宏，即可自动生成全量配对数据。

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