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.
他们都不同，比如
E:
101%
105%
110%
120%
E:
101%
105%
110%
120%
J:
上 S
上行L
缺点B
高级 V
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.
这是一种生成使用 Excel 和 Microsoft Query 的两个或多个一维数据数组的笛卡尔积.
这些说明是用 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.仅勾选创建名称"对话框中的顶行，然后单击OK.
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.
数据|获取外部数据 |来自其他来源 |来自微软查询
Data | Get External Data | From Other Sources | From Microsoft Query
选择 Choose New Data Source 对话框中:
Choose <New Data Source>. In the Choose New Data Source dialog:
连接的友好名称
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.
接下来点击Cancel(真的！).此时可能会提示您继续在 Microsoft Query 中编辑?"(answer Yes)，或无法在图形编辑器中表示加入的投诉.忽略这一点并继续前进......
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.
您将返回到工作表.快完成了，我保证！勾选New worksheet 和OK.
You are returned to the worksheet. Almost done, I promise! Tick New worksheet and OK.
返回交叉连接的结果.
这篇关于Excel vba 创建范围的所有可能组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
