Excel中VBA创建范围的每一个可能的组合Excel中VBA创建范围的每一个可能的组合(Excel

我有我一直没能找到在网络上任何地方的问题（也可能是有，但我不能找到它，嘿嘿）。

我有数据的13列的电子表格。 每列包含需要进入一个总体测试情况的​​参数的变化。

所有的人都不同，像

E：
101％
105％
110％
120％

记者：
上小号
上行大号
下行乙
高级V

我见过几个解决方案，它使用嵌套循环相结合的问题。 我想避开的13个嵌套循环（但是这是目前我最好的选择）。 我有点就如何在每列生成每个独特组合的损失。

我不知道这对你们赚了足够的意识。 我希望有人能至少指向我朝着正确的方向与递归算法。 我想使它足够的动态采取不同的行和列的数字。

感谢您的任何帮助你们可以给我。

Answer 1:

由于我提供了一个ODBC的方法，我想我应该详细说明它，因为它是不会立即明显如何做到这一点。 而且，诚实，我需要重新学习的过程，并记录它自己。

这是为了产生一个方式笛卡尔乘积使用Excel和Microsoft查询两个或更多个一维数据阵列。

这些指令是在任何版本编写与XL2007但应与未成年人工作（如果有的话）的修改。

步骤1

组织阵列列。

重要提示：每个列应该有两个“头”的名字下面以粗体显示。 最上面的名称稍后将被解释为“表名”。 第二个名字将被解释为“列名”。 这几个步骤后变得明显。

选择每个数据范围反过来，包括“头”，然后按下Ctrl+Shift+F3 。 勾选只有Top row在“创建名称”对话框，然后单击OK 。

一旦所有的命名范围建立，保存文件。

第2步

数据| 获取外部数据| 从其他来源| 从微软查询

选择<New Data Source> 。 在Choose New Data Source对话框：

1. 您的连接的友好名称

2. 选择适当的Microsoft Excel驱动程序

...然后Connect

第3步

Select Workbook...然后浏览你的文件。

第4步

从你的“表”添加“列”。 现在您可以看到为什么在步骤1中的“两个头”的布局是非常重要的 - 它的技巧司机到正确理解数据。

接下来，单击Cancel （真的！）。 你可能会在这一点上会提示“继续在Microsoft Query编辑？” （答案Yes ），或加入投诉不能在图形编辑器来表示。 忽略此锐意上...

第5步

Microsoft Query中打开，默认情况下您添加的表将进行交叉连接。 这将产生一个笛卡尔乘积，这就是我们想要的。

现在干脆关闭MSQUERY。

第6步

您将返回到工作表。 几乎做到了，我答应！ 勾选New worksheet和OK 。

第7步

交叉连接返回结果。

Answer 2:

不知道为什么你不愿意循环。 看到这个例子。 它花了不到一秒钟。

Option Explicit

Sub Sample()
    Dim i As Long, j As Long, k As Long, l As Long
    Dim CountComb As Long, lastrow As Long

    Range("G2").Value = Now

    Application.ScreenUpdating = False

    CountComb = 0: lastrow = 6

    For i = 1 To 4: For j = 1 To 4
    For k = 1 To 8: For l = 1 To 12
        Range("G" & lastrow).Value = Range("A" & i).Value & "/" & _
                                     Range("B" & j).Value & "/" & _
                                     Range("C" & k).Value & "/" & _
                                     Range("D" & l).Value
        lastrow = lastrow + 1
        CountComb = CountComb + 1
    Next: Next
    Next: Next

    Range("G1").Value = CountComb
    Range("G3").Value = Now

    Application.ScreenUpdating = True
End Sub

快照

注意 ：上面的是一个小例子。 我做了一个测试的4列与每200行。 在这种情况下可能的总组合是1600000000 ，花了16秒。

在这种情况下它穿过了Excel行限制。 我能想到的是写输出到一个文本文件在这种情况下一个选择。 如果你的数据很小，那么你可以逃脱不使用数组和直接写入细胞。 :)但在大数据的情况下，我会建议使用数组。

Answer 3:

我需要这个自己好几次，终于建成了它。

相信代码扩展的列的任何总数和列内的任何数量的不同值（例如每列可以包含任意数量的值）

它假定每个列的所有值是唯一的（如果这是不正确的，你会得到重复的行）

它假定你想，不管以何种细胞当前已选定交叉联接输出（请确保您全部选中）

它假定你要输出到当前的选择后开始一列。

它是如何工作（简述）：首先对于每一列和每一行：它可以计算支持所有连击在N列所需总的行数（列1 *项目的项目2栏... *列N项）

第二为每列：基于总连击，并计算两个回路先前列的总连击上。

ValueCycles（你多少次通过当前列的所有值必须循环）ValueRepeats（多少次列重复每个值连续）

Sub sub_CrossJoin()

Dim rg_Selection As Range
Dim rg_Col As Range
Dim rg_Row As Range
Dim rg_Cell As Range
Dim rg_DestinationCol As Range
Dim rg_DestinationCell As Range
Dim int_PriorCombos As Long
Dim int_TotalCombos As Long
Dim int_ValueRowCount As Long
Dim int_ValueRepeats As Long
Dim int_ValueRepeater As Long
Dim int_ValueCycles As Long
Dim int_ValueCycler As Long

int_TotalCombos = 1
int_PriorCombos = 1
int_ValueRowCount = 0
int_ValueCycler = 0
int_ValueRepeater = 0

Set rg_Selection = Selection
Set rg_DestinationCol = rg_Selection.Cells(1, 1)
Set rg_DestinationCol = rg_DestinationCol.Offset(0, rg_Selection.Columns.Count)

'get total combos
For Each rg_Col In rg_Selection.Columns
    int_ValueRowCount = 0
    For Each rg_Row In rg_Col.Cells
        If rg_Row.Value = "" Then
            Exit For
        End If
        int_ValueRowCount = int_ValueRowCount + 1
    Next rg_Row
    int_TotalCombos = int_TotalCombos * int_ValueRowCount
Next rg_Col

int_ValueRowCount = 0

'for each column, calculate the repeats needed for each row value and then populate the destination
For Each rg_Col In rg_Selection.Columns
    int_ValueRowCount = 0
    For Each rg_Row In rg_Col.Cells
        If rg_Row.Value = "" Then
            Exit For
        End If
        int_ValueRowCount = int_ValueRowCount + 1
    Next rg_Row
    int_PriorCombos = int_PriorCombos * int_ValueRowCount
    int_ValueRepeats = int_TotalCombos / int_PriorCombos


    int_ValueCycles = (int_TotalCombos / int_ValueRepeats) / int_ValueRowCount
    int_ValueCycler = 0

    int_ValueRepeater = 0

    Set rg_DestinationCell = rg_DestinationCol

    For int_ValueCycler = 1 To int_ValueCycles
        For Each rg_Row In rg_Col.Cells
            If rg_Row.Value = "" Then
                Exit For
            End If

                For int_ValueRepeater = 1 To int_ValueRepeats
                    rg_DestinationCell.Value = rg_Row.Value
                    Set rg_DestinationCell = rg_DestinationCell.Offset(1, 0)
                Next int_ValueRepeater

        Next rg_Row
    Next int_ValueCycler

    Set rg_DestinationCol = rg_DestinationCol.Offset(0, 1)
Next rg_Col
End Sub

Answer 4:

解决方案基于我的第二个评论。 这个例子假设你有三列数据，但可以适用于处理更多。

我开始与您的样本数据。 我加了顶行为了方便计数。 我还添加了组合的总数（计数的产品）。 这是Sheet1 ：

在Sheet2 ：

公式：

A2:C2 （橙色细胞）被硬编码=0

A3=IF(SUM(B3:C3)=0,MOD(A2+1,Sheet1!$E$1),A2)

B3=IF(C3=0,MOD(B2+1,Sheet1!$G$1),B2)

C3=MOD(C2+1,Sheet1!$J$1)

D2=INDEX(Sheet1!$E$2:$E$5,Sheet2!A2+1)

E2=INDEX(Sheet1!$G$2:$G$6,Sheet2!B2+1)

F2=INDEX(Sheet1!$J$2:$J$5,Sheet2!C2+1)

从3行向下填充尽可能多的行， Total显示在Sheet1

Answer 5:

调用该方法并投入目前的水平，这将在该方法中被递减（抱歉ENG）

样品：

    sub MyAdd(i as integer)
      if i > 1 then
        MyAdd = i + MyAdd(i-1)
      else
        MyAdd = 1
      end if
    end sub

文章来源: Excel vba to create every possible combination of a Range