- ·上一篇:excel表格饼图怎么做的好看
- ·下一篇:excel表格怎么每行插3条空白行
excel表格怎么把4个数排列组合
1.如何用excel实现4组数的全部排列
亲,可以用公式,不过最好用VBA。
因为数据比较多的时候,数组公式运行起来会很慢。 演示效果和代码如下: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Sub pl() Dim MyArr(1 To 4), ss As String Dim i, j, n1, n2, n3, n4 As Long ss = "ABCD" Range("A:A").ClearContents For n1 = 1 To 4 For n2 = 1 To 4 If n1 <> n2 Then For n3 = 1 To 4 If n3 <> n1 And n3 <> n2 Then For n4 = 1 To 4 If n4 <> n1 And n4 <> n2 And n4 <> n3 Then If Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Find(Mid(ss, n1, 1) & Mid(ss, n2, 1) & Mid(ss, n3, 1) & Mid(ss, n4, 1)) Is Nothing Then If Range("A1") = "" Then Range("A1") = Mid(ss, n1, 1) & Mid(ss, n2, 1) & Mid(ss, n3, 1) & Mid(ss, n4, 1) Else Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Mid(ss, n1, 1) & Mid(ss, n2, 1) & Mid(ss, n3, 1) & Mid(ss, n4, 1) End If End If End If Next End If Next n3 End If Next n2 Next n1 End Sub 。
2.请问怎么请EXCEL 0 1 2 3 4 5 6 7 8 9 四位数全部排列组合啊?
用下面的VBA代码运行一下就会在当前工作表的 A列填充出全部组合
VBA代码用法:
'按alt+f11进入VBE编辑窗口,然后选择插入----模块----会打开一个模
'块窗口,把下面的代码复制进去--保存,再次按alt+f11返回excel窗口
'按alt+f8打开运行宏窗口,会看到test宏,运行它就可以了
Option Explicit
Sub test()
Dim i As Integer, j As Integer, l As Integer, n As Integer, k As Long
For i = 0 To 9
For j = 0 To 9
For l = 0 To 9
For n = 0 To 9
k = k + 1
ActiveSheet.Range("a" & k) = "'" & i & j & l & n
Next
Next
Next
Next
End Sub
3.excel 4个字符排列组合
excel本身没有这个功能或者函数,用excel做一个自定义函数才能实现。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
SubTEST()
DimMYAR AsVariant, i AsInteger, j AsInteger, k AsDouble, p AsInteger, q AsInteger
DimNumFree() AsBoolean, TotalNum AsDouble, MyMtrx() AsInteger
DimMyFetch() AsInteger
TotalNum = Application.WorksheetFunction.Permut(4, 4)
ReDimNumFree(1 To4) '输入数字返回该数是否自由(未取)
ReDimMyMtrx(1 ToTotalNum, 1 To4)
ReDimMyFetch(1 To4) '输入列号,返回该列应取数字
Fori = 1 To4
MyFetch(i) = i '初始化
Nexti
Fork = 1 ToTotalNum
Fori = 1 To4 '列号
MyMtrx(k, i) = MyFetch(i)
Nexti
NumFree(MyFetch(4)) = True'释放最末列的数字
Forj = 4 - 1 To1 Step-1 '从倒数第2列开始往前
NumFree(MyFetch(j)) = True'释放当前列
Fori = MyFetch(j) + 1 To4 '向下试探
IfNumFree(i) Then
MyFetch(j) = i
NumFree(i) = False
Forp = j + 1 To4 '从当前列往后逐列从上往下找数
Forq = 1 To4 '数字从上往下找
IfNumFree(q) Then
MyFetch(p) = q
NumFree(q) = False
ExitFor
EndIf
Nextq
Nextp
ExitFor
EndIf
Nexti
IfNotNumFree(MyFetch(4)) Then'最末列都取到数了,跳出
ExitFor
EndIf
Nextj
Nextk
4.excel如何列出所有排列组合
'你这个用excel函数比较麻烦,但用VBA代码就比较简单。
'如果你的四个环节名分别放在A1:D1,第一环节的四种情况分别在A2:A4,……,则在excel VBA里运行下面的代码就可以在A6:P21区域中得出你要的256中组合Sub 四环节组合()Dim i%, j%, k%, l%, m%, n%For i = 2 To 5 Step 1 For j = 2 To 5 Step 1 m = m + 1 n = 0 For k = 2 To 5 Step 1 For l = 2 To 5 Step 1 n = n + 1 Cells(m + 5, n) = Cells(i, 1) & Cells(j, 2) & Cells(k, 3) & Cells(l, 4) Next Next NextNextEnd Sub。