Excel vba创建一个范围的每一个可能的组合 [英] Excel vba to create every possible combination of a Range
问题描述
我有一个问题，我无法在网路上找到任何地方（可能在那里，但我找不到，呃）。
I have a problem that I haven't been able to find anywhere on the web (it may be there, but I can't find it, heh).
我有一个包含13列数据的电子表格。每个列都包含需要进入整体测试用例的参数的变体。
I have a spreadsheet with 13 columns of data. Each of the column contains variations of a parameter that needs to go into an overall test case.
所有这些都有所不同，如
All of them differ, like
E：
101％
105％
110％
120％
E:
101%
105%
110%
120%
J：
上S
上升L
下行B
Premium V < br>
J:
Upper S
Upside L
Downside B
Premium V
我已经看到使用嵌套循环的组合问题的几种解决方案。我想引导13个嵌套循环（但是这是我最好的打算）。我对于每一列如何产生每一个独特的组合都感到失落。
I have seen several solutions to the combination issue which uses nested loops. I'd like to steer clear of 13 nested loops (but this is my best bet at the moment). I'm kind of at a loss on how to generate every unique combination in in each column.
我不知道这对你们来说足够有意义。我希望有人能用递归算法至少指出我正确的方向。我想让它足够动态，可以使用不同数量的列和行。
I'm not sure if that makes enough sense for you guys. I was hoping someone could at least point me in the right direction with a recursive algorithm. I'd like to make it dynamic enough to take varying numbers of columns and rows.
感谢您能给我的任何帮助。
Thanks for any help you guys can give me.
推荐答案
由于我提供了一个ODBC方法，我以为我应该详细说明一下，因为现在不清楚如何做到这一点。而且，诚实地说，我需要重新学习这个过程并自己记录。
Since I offered an ODBC approach I thought I should elaborate on it, as it is not immediately obvious how to do this. And, in honesty, I needed to relearn the process and document it for myself.
这是一种生成笛卡尔乘积。
这些说明是用XL2007编写的，但是应该在任何版本中进行小修改（如果有）修改。
These instructions were written with XL2007 but should work with minor (if any) modifications in any version.
按列组织数组。
重要提示：每列应该有两个标题名称，如下面的粗体所示。最后的名称将被解释为表名称。第二个名称将被解释为列名称。
Important: Each column should have two "header" names as shown in bold below. The topmost name will later be interpreted as a "table name". The second name will be interpreted as a "column name". This will become apparent a few steps later.
依次选择每个数据范围，包括标题，然后点击 Ctrl + Shift + F3 。在创建名称对话框中单击顶行，然后单击确定。
Select each data range in turn, including both "headers", and hit Ctrl+Shift+F3. Tick only Top row in the 'Create Names" dialog and click OK.
一旦所有命名范围建立，请保存文件。
Once all named ranges are established, save the file.
数据|获取外部数据|来自其他来源|从Microsoft查询
Data | Get External Data | From Other Sources | From Microsoft Query
选择<新数据源> 在选择新数据源对话框
-
您的连接的友好名称
A friendly name for your connection
选择适当的Microsoft Excel驱动程序
choose the appropriate Microsoft Excel driver
...然后连接
选择工作簿。 .. 然后浏览您的文件。
从表添加列。您现在可以看到为什么步骤1中的两个标题布局很重要 - 它会使驱动程序正确理解数据。
Add the "columns" from your "tables". You can see now why the "two header" layout in step 1 is important--it tricks the driver into understanding the data correctly.
下一步点击取消（真的！）。此时可能会提示您继续在Microsoft Query中进行编辑？ （答案是），或者连接的投诉无法在图形编辑器中表示。忽略这个并伪造...
Next click Cancel (really!). You might be prompted at this point to "continue editing in Microsoft Query?" (answer Yes), or a complaint that joins cannot be represented in the graphical editor. Ignore this and forge on...
Microsoft Query打开，默认情况下，加入。这将产生一个笛卡尔乘积，这就是我们想要的。
Microsoft Query opens, and by default the tables you added will be cross-joined. This will generate a Cartesian product, which is what we want.
现在关闭MSQuery。
Now close MSQuery altogether.
您将返回到工作表。几乎完成了，我保证！勾选新工作表和 OK 。
You are returned to the worksheet. Almost done, I promise! Tick New worksheet and OK.
返回交叉结果。
这篇关于Excel vba创建一个范围的每一个可能的组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
