|
楼主 |
发表于 2016-1-10 20:12
|
显示全部楼层
班门弄斧,胡乱堆放,请香川侠女雅正,谢谢!
- Option Explicit
- Dim sj, jg(), ism, h1&, h2&, m&, n&, k&
- Sub test() 'by kagawa 2106/1/5-1/7
- Dim a&(9), sj1, sj2, sj01, i&, j&, L&, cl&, r&, t&, tms#
- Dim imn, imx
- tms = Timer
- m = [a1].End(4).Row - 1 '尾数行
- For i = 1 To m
- a(Cells(i + 1, 1)) = 1 '提取尾数
- Next
- cl = [a1].End(2).Column '数值列
- n = WorksheetFunction.Sum([b1].Resize(, cl - 1)) '抽取个数和
- ' If n <> 5 Then MsgBox "n <> 5 Err !": Exit Sub
- sj1 = [a1].CurrentRegion
- r = UBound(sj1) - 1
- ReDim sj2(1 To r, 1 To n) '声明要存放排列组合数值的数组
- n = 0: r = 1
- For j = 2 To cl
- For L = 1 To sj1(1, j)
- n = n + 1: i = 0
- For k = 2 To UBound(sj1) '遍历数值
- If sj1(k, j) <> "" Then
- If sj1(k, j) = 0 Then sj01 = sj01 + 1 '判断0序列
- t = sj1(k, j)
- If a(t Mod 10) Then i = i + 1: sj2(i, n) = t '提取符合尾数条件的数值
- End If
- Next
- r = r * i '组合总数
- Next
- Next
- [b20].CurrentRegion = ""
- [b20].Resize(UBound(sj2), n) = sj2 '将符合尾数条件的数值写入单元格
- sj = [b20].CurrentRegion '将符合尾数条件的数值赋值给数组
- ReDim jg(r, n + 1) '声明存放组合结果的数组
- If r > 100000 Then ReDim ism(1 To r \ 10) Else ReDim ism(1 To r \ 2) '声明并缩小存放组合和值数组,避开空值,避免类型不匹配现象
- h1 = Range("l3") '设置最小和值
- h2 = Range("m3") '设置最大和值
- If sj01 > 0 Then '判断组合数值是0序列还是1序列(0序列:0~9,每列都是0~9;1序列:1~100,从1开始由小到大不重复输入)
- k = 0: Call dgMN0(0, 1)
- Else
- k = 0: Call dgMN1(0, 1)
- End If
- imn = Application.Min(ism) '组合最小值
- imx = Application.Max(ism) '组合最大值
- Range("l7:m7") = ""
- Range("l7:m7") = Array(imn, imx)
- If imx < h1 Then MsgBox "和值筛选范围错误!"
- [o1].CurrentRegion = ""
- For L = 1 To n
- Cells(1, L + 15) = L '结果表头
- Next
- [o1] = "NO:" & k
- [o1].Offset(, n + 1) = "和值"
- If k Then [o2].Resize(k, 2 + n) = jg
- MsgBox Format(Timer - tms, "0.000s ") & k
- End Sub
- Sub dgMN1(r&, j&) '1序列不重复组合
- Dim h&, i&, L&, s&, t&
- For i = 1 To UBound(sj)
- t = sj(i, j): If sj(i, j) = "" Then Exit For
- If t > jg(k, j - 1) Then
- jg(k, j) = t
- If j = n Then
- s = 0: ReDim b&(9)
- For L = 1 To n
- h = jg(k, L) Mod 10: If b(h) = 0 Then b(h) = 1: s = s + 1
- Next
- If s = m Then
- h = r + t
- ism(k + 1) = h
- If h >= h1 Then
- If h <= h2 Then
- jg(k, n + 1) = h: k = k + 1: jg(k - 1, 0) = k
- For L = 1 To n - 1
- jg(k, L) = jg(k - 1, L)
- Next
- End If
- End If
- End If
- Else
- Call dgMN1(r + t, j + 1)
- End If
- End If
- Next
- End Sub
- Sub dgMN0(r&, j&) '0序列允许重复组合
- Dim h&, i&, L&, s&, t&
- For i = 1 To UBound(sj)
- t = sj(i, j): If sj(i, j) = "" Then Exit For
- If t >= jg(k, j - 1) Then
- jg(k, j) = t
- If j = n Then
- s = 0: ReDim b&(9)
- For L = 1 To n
- h = jg(k, L) Mod 10: If b(h) = 0 Then b(h) = 1: s = s + 1
- Next
- If s = m Then
- h = r + t
- ism(k + 1) = h
- If h >= h1 Then
- If h <= h2 Then
- jg(k, n + 1) = h: k = k + 1: jg(k - 1, 0) = k
- For L = 1 To n - 1
- jg(k, L) = jg(k - 1, L)
- Next
- End If
- End If
- End If
- Else
- Call dgMN0(r + t, j + 1)
- End If
- End If
- Next
- End Sub
复制代码
香川多列组合尾数及和值.rar
(22.06 KB, 下载次数: 58)
|
|