作者:乔山办公网日期:
返回目录:excel表格制作
使用公式:
=A1&B1&C1&D1&E1&F1
下拉。
如果你的数据中都包含有非数字文本,可以用公式:
=PHONETIC(A1:F1)
下拉。
代码及注释如下:
Sub a()
Dim i%, j%, k%'定义变量为整理
k = 1'从1开始显知示所有组合
For i = 1 To [a65536].End(3).Row'i从1到道A列从底往上第一专个不为空的行号为止循环
For j = 1 To [d65536].End(3).Row'j从1到D列从底往上第一个不为空的行号为止循环
Range("A" & i & ":C" & i).Copy Range("H" & k)'i行A列到C列区域,复制到H行从第1行开始属的区域
Range("D" & j & ":F" & j).Copy Range("K" & k)'j行D列到F列区域,复制到K行从第一样开始的区域
k = k + 1'每完成上述操作,行数增加1
Next j
Next i
End Sub
A列输入数据,点击按钮,E列后出结果
如果12个数里面选择出4个数进行排列组合,其个数应为:C=12*11*10*9=11880,其VBA代码如下:
Sub Qiong()
Dim i, j, k, l, m As Long
Dim a, b, c, d As String
m = 0
Set mysheet1 = ThisWorkbook.Worksheets("Sheet1")
For i = 1 To 12
For j = 1 To 12
For k = 1 To 12
For l = 1 To 12
a = Choose(i, "A1", "A2", "A3", "B1", "B2", "B3", "C1", "C2", "C3", "D1", "D2", "D3")
If j <> i Then
b = Choose(j, "A1", "A2", "A3", "B1", "B2", "B3", "C1", "C2", "C3", "D1", "D2", "D3")
If k <> i And k <> j Then
c = Choose(k, "A1", "A2", "A3", "B1", "B2", "B3", "C1", "C2", "C3", "D1", "D2", "D3")
If l <> i And l <> j And l <> k Then
d = Choose(l, "A1", "A2", "A3", "B1", "B2", "B3", "C1", "C2", "C3", "D1", "D2", "D3")
m = m + 1
mysheet1.Cells(m, 5) = a & b & c & d
End If
End If
End If
Next
Next
Next
Next
End Sub
类似的例子参见百度经验e799bee5baa6e59b9ee7ad94337链接:《Excel表格上面使用VBA进行数组组合 》