excel根据给出的数字,列出所有6位数集合
假如我给出数字A1~A9 ,如何列出所有6位数的集合。比如(1,2,3,4,5,6) (1,2,3,4,5,7).....(5,6,7,8,9,10) 这样的6位数集合
这是个组合问题。一共有C(9,6)=9!/(6!(9-6)!)=84组。列出每组数据用VBA代码。
Sub 组合()
'9个对象集合中提取6个对象的组合数是84(用工作表函数COMBIN求得)
'列出这84个对象组合,用数组存储结果,然后输出于B:G列中
Dim cm(1 To 84, 1 To 6) '命名数组
ST = Timer '记住开始时间
For a1 = 1 To 9 '从此开始是9个嵌套的For……Next语句
For a2 = a1 + 1 To 9
For a3 = a2 + 1 To 9
For a4 = a3 + 1 To 9
For a5 = a4 + 1 To 9
For a6 = a5 + 1 To 9
n = n + 1
cm(n, 1) = Cells(a1, 1) '将循环变量的值赋值于数组
cm(n, 2) = Cells(a2, 1)
cm(n, 3) = Cells(a3, 1)
cm(n, 4) = Cells(a4, 1)
cm(n, 5) = Cells(a5, 1)
cm(n, 6) = Cells(a6, 1)
Next: Next: Next: Next: Next: Next
Range("B1:G84") = cm '将数组的值赋值于单元格区域
Cells(1, 9) = "组合数"
Cells(1, 10) = n
Cells(2, 9) = "运行时间(秒)"
Cells(2, 10) = Timer - ST '计算列举组合元素需要的时间
End Sub