|
![](https://clubstatic.excelhome.net/image/common/ico_lz.png)
楼主 |
发表于 2023-12-30 11:31
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
交作业,递归是真不好用,还是不太会。 一维数组递归组合勉强会抄了,二维行列同时递归,感觉有点深度搜索或者动态规划,思路能想通,代码不会改。先按一维数组递归组合写一个
测试模块
- ' 有个疑问
- ' 如果不读取单元格32个数字,直接用默认1-32组成数组进行组合,速度 0.5~
- ' 如果读取单元格8行4列的数字,作为数据源数组进行组合,速度 1.1~
- ' 而实际时间的计算是从数据源数组生成后开始计时,也就是组合过程和数据源其实无关
- Sub Test()
- Dim ar, r As Long
- With New ClsCombin
- ' .Source = Range("c3:f10").Value ' 注释后会用默认数组,内容一致
- ar = .Result
- If .Cnt < 100 Then r = .Cnt Else r = 100
- End With
- Columns(1).ClearContents
- Range("a1").Resize(r).Value = ar
- End Sub
- Sub Test1()
- Dim ar, r As Long
- With New ClsCombin
- .Source = Range("m3").CurrentRegion.Value
- .n = Range("k4").Value
- .nRow = Range("k6").Value
- .nColumn = Range("k8").Value
- ar = .Result
- If .Cnt < 100 Then r = .Cnt Else r = 100
- Range("H10").Value = .n & " ; " & .nRow & " ; " & .nColumn & " ; " & .Cnt
- If .Cnt = 0 Then Exit Sub
- End With
- Columns(1).ClearContents
- Range("a1").Resize(r).Value = ar
- End Sub
复制代码 类模块
- ' 类名称:ClsCombin
- ' 组合递归代码
- ' 读入一个二维数组和抽取数量,行列约束数量,共4个参数,返回符合条件的组合数组
- Option Explicit
- Private m_ As Integer '-- 元素总数量
- Private n_ As Integer '-- 抽取数量
- Private nRow_ As Integer '-- 行约束条件数量
- Private nColumn_ As Integer '-- 列约束条件数量
- Private Source_ '-- 数据源转化为一列
- Private Result_ '-- 结果
- Private ubound1_ As Integer '-- 数组上限
- Private ubound2_ As Integer '-- 数组上限
- Private cntMax_ As Long '-- 组合上限
- Private cnt_ As Long '-- 组合结果数量
- Private arrNum_() As Integer '-- 一维数组(备用)
- Private arrRow_() As Integer '-- 记录行数量
- Private arrColumn_() As Integer '-- 记录列数量
- Public Property Let m(m0 As Integer)
- m_ = m0
- End Property
- Public Property Get m() As Integer
- m = m_
- End Property
- Public Property Let n(n0 As Integer)
- n_ = n0
- End Property
- Public Property Get n() As Integer
- n = n_
- End Property
- Public Property Let nRow(nRow0 As Integer)
- nRow_ = nRow0
- End Property
- Public Property Get nRow() As Integer
- nRow = nRow_
- End Property
- Public Property Let nColumn(nColumn0 As Integer)
- nColumn_ = nColumn0
- End Property
- Public Property Get nColumn() As Integer
- nColumn = nColumn_
- End Property
- Public Property Let Source(Source0 As Variant) '-- 二维数组为数据源
- ReadSourceArray Source0
- End Property
- Public Property Get Result() As Variant
- GetCombin
- Result = Result_
- End Property
- Public Property Get Cnt() As Variant
- Cnt = cnt_
- End Property
- Public Sub GetCombin()
- Dim tms
- tms = Timer
- cntMax_ = Application.WorksheetFunction.Combin(m_, n_)
- ReDim Result_(1 To cntMax_, 1 To 1)
- cnt_ = 0
- Call Recursion("", 0, 1)
-
- MsgBox cnt_ & vbCrLf & Format(Timer - tms, "0.000s")
- End Sub
- Private Sub Recursion(s As String, i As Long, t As Long)
- Dim j As Long, ss As String
- For j = i + 1 To m_
- If arrRow_(Source_(j, 2)) < nRow_ Then
- If arrColumn_(Source_(j, 3)) < nColumn_ Then
- If t = n_ Then
- cnt_ = cnt_ + 1
- Result_(cnt_, 1) = Mid(s & ";" & Source_(j, 1), 2)
- Else
- arrRow_(Source_(j, 2)) = arrRow_(Source_(j, 2)) + 1
- arrColumn_(Source_(j, 3)) = arrColumn_(Source_(j, 3)) + 1
- Call Recursion(s & ";" & Source_(j, 1), j, t + 1)
- arrRow_(Source_(j, 2)) = arrRow_(Source_(j, 2)) - 1
- arrColumn_(Source_(j, 3)) = arrColumn_(Source_(j, 3)) - 1
- End If
- End If
- End If
- Next
- End Sub
- Private Sub Class_Initialize()
- InitiDefault
- End Sub
- Private Sub Class_Terminate()
- End Sub
- Private Sub InitiDefault()
- Dim i As Long, j As Long, k As Long
-
- n_ = 6
- nRow_ = 2
- nColumn_ = 3
-
- ubound1_ = 8
- ubound2_ = 4
- m_ = ubound1_ * ubound2_
- ReDim Source_(1 To m_, 1 To 4)
- For i = 1 To ubound1_
- For j = 1 To ubound2_
- k = k + 1
- Source_(k, 1) = k
- Source_(k, 2) = i
- Source_(k, 3) = j
- Next
- Next
- ReDim arrRow_(1 To ubound1_)
- ReDim arrColumn_(1 To ubound2_)
- End Sub
- Private Sub ReadSourceArray(Source0)
- Dim i As Long, j As Long, k As Long
- ubound1_ = UBound(Source0, 1)
- ubound2_ = UBound(Source0, 2)
- m_ = ubound1_ * ubound2_
-
- ReDim Source_(1 To m_, 1 To 4)
- For i = 1 To ubound1_
- For j = 1 To ubound2_
- k = k + 1
- Source_(k, 1) = Source0(i, j)
- Source_(k, 2) = i
- Source_(k, 3) = j
- Next
- Next
-
- ReDim arrRow_(1 To ubound1_)
- ReDim arrColumn_(1 To ubound2_)
-
- End Sub
- ''''Private Sub Recursion(s As String, i As Long, t As Long)
- '''' Dim j As Long, ss As String
- '''' dgn = dgn + 1
- '''' For j = i + 1 To m_
- '''' If t = n_ Then
- '''' '--方案1
- '''' If CheckNum(s & ";" & Source_(j, 1)) Then
- '''' cnt_ = cnt_ + 1
- '''' Result_(cnt_, 1) = Mid(s & ";" & Source_(j, 1), 2)
- '''' End If
- '''' Else
- '''' If CheckNum(s & ";" & Source_(j, 1)) Then
- '''' Call Recursion(s & ";" & Source_(j, 1), j, t + 1)
- '''' Else
- '''' End If
- '''' End If
- '''' Next
- ''''End Sub
- ''''Private Function CheckNum(s0 As String) As Boolean
- '''' Dim ar, br1, br2
- '''' Dim i As Long, j As Long, r As Long, c As Long
- '''' ar = Split(s0, ";")
- '''' ReDim br1(1 To ubound1_)
- '''' ReDim br2(1 To ubound2_)
- '''' For i = 1 To UBound(ar)
- '''' j = ar(i)
- '''' r = Source_(j, 2)
- '''' c = Source_(j, 3)
- '''' br1(r) = 1 + br1(r)
- '''' br2(c) = 1 + br2(c)
- '''' If br1(r) > nRow_ Then Exit Function
- '''' If br2(c) > nColumn_ Then Exit Function
- '''' Next
- '''' CheckNum = True
- ''''End Function
复制代码
Combin.zip
(29.57 KB, 下载次数: 2)
|
|