☰

如何在 Excel 中创建“交叉连接”？

tag-icon • tag-icon microsoft-excel
如何在 Excel 中创建“交叉连接”？

我在 Excel 中有 2 个工作表，每个工作表有 10 行。Excel 中是否有某种“交叉连接”公式/宏，允许我创建所有行都交叉连接的第三张工作表（总共 100 行）。

答案1

如果使用 OLEDB 不是一个选项，则可以使用以下函数来生成交叉乘积

Option Base 1

Function Cross_Product_range(r1 As Range, r2 As Range) As Variant
    Cross_Product_range = Cross_Product_array(r1.Value, r2.Value)
End Function

Function Cross_Product_array(a1 As Variant, a2 As Variant) As Variant
    On Error GoTo ErrorHandler
    Dim TempArray(), k, m
    ReDim TempArray(UBound(a1) * UBound(a2), UBound(a1, 2) + UBound(a2, 2))
    k = 1
    For i = 1 To UBound(a1)
        For j = 1 To UBound(a2)
            m = 1
            For u = 1 To UBound(a1, 2)
                TempArray(k, m) = a1(i, u)
                m = m + 1
            Next
            For u = 1 To UBound(a2, 2)
                TempArray(k, m) = a2(j, u)
                m = m + 1
            Next
            k = k + 1
        Next
    Next
    Cross_Product_array = TempArray
    Exit Function
    ErrorHandler:
    Debug.Print Err
End Function

将上述函数添加到 vba 模块后，为了生成交叉乘积A1:B5并C1:D5选择目标单元格（例如 E1:H25）并输入公式并使用 ctrl + shift + enter

=Cross_Product_range(A1:B5,C1:D5)

答案2

您可以使用 Power Query 轻松完成此操作（如果您的 Excel 版本中有该功能）：

设置要合并的每个表的数据连接：在 Excel 中选择表，然后从功能区上的“数据”中选择“来自表/范围”。这将打开 Power Query 编辑器。

要返回 Excel，请单击“关闭并加载”按钮上的小箭头，选择“关闭并加载到”，然后选择“仅连接”。否则，您最终会在 Excel 中得到一份表格副本...

对第二张表执行相同操作。

现在窗口右侧应该会打开“查询和连接”窗格。如果没有，请单击功能区中的“数据 -> 查询和连接”。

这两个表应该作为查询列出。

下一步是将两者结合起来进行交叉连接查询并将结果返回到 Excel：

右键单击“查询和连接”窗格中的其中一个查询，然后选择“引用”。这将再次打开 Power Query 编辑器并创建指向其中一个表的第三个查询。

现在从 Power Query 功能区中选择“添加列 -> 自定义列”... 新列的公式将是第二个查询的名称 - 即要交叉连接的数据。 如果名称中没有空格，您可以直接输入，（例如 = Table2，否则需要 = #“Table 2”）。

现在您应该有一个新的列，标签为“自定义”，每个单元格中都有“表格”。单击列标题中的小符号可展开每个表格。

一切顺利，您大功告成！单击“关闭并加载”，交叉连接的表格应出现在新工作表中。

需要注意的是：当您更改源数据时，此交叉连接不会自动更新。您需要右键单击并选择“刷新”才能重新运行交叉连接查询。

这可能比我说的更简单！

相关内容