|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
真的很有用,顶!- Dim sj, jg(), m&, n&, k&, r&, c&, w$, cnt&
- Sub Combin_dg() 'by kagawa
- tms = Timer
- m = [a1].End(4).Row: sj = [a1].Resize(m): [a:a].EntireColumn.AutoFit
- n = [b2]: k = WorksheetFunction.Combin(m, n)
- w = [b4]
- r = [b5]: If r = 0 Then r = 50000: [b5] = r
- c = Val([b6]): If c Then cnt = ((k - 1) \ r + 1) * IIf(c = 1, 1, n + 1) + 6: If cnt > Columns.Count Then MsgBox cnt & " > " & Columns.Count & " Err !": Exit Sub
- [e1].Resize(, Columns.Count - 5) = 1: [e1].CurrentRegion = "": [e1] = "Output": [b9:b12] = "":
-
- k = 0: cnt = 0: tm1 = Timer
- If c = 0 Then
- Open ActiveWorkbook.Path & "\CombinResult.txt" For Output As #1
- Call dgZH0("", 0, 1)
- Close #1
- ElseIf c = 1 Then
- ReDim jg(1 To r, 1 To 1)
- Call dgZH1("", 0, 1)
- If [b3] > r Then [iv1].End(1).Offset(, 1).Resize(k) = jg
- [f1].Resize(, [b7]).EntireColumn.AutoFit
- Else '
- c = n: [b6] = n
- [f1].Resize(, 1 + [b7]).ColumnWidth = [a1].ColumnWidth
- ReDim a&(1 To n): ReDim jg(1 To r, 1 To n)
- Call dgZH2(a, 0, 1)
- If [b3] > r Then [iv1].End(1).Offset(, 2).Resize(k, n) = jg
- End If
-
- [d1] = "": [b9] = cnt
- If c And [b3] < r Then
- [b10] = Format(Timer - tm1, "0.000"): tm2 = Timer
- [f1].Resize(k, c) = jg: [f1].Resize(, c).EntireColumn.AutoFit: [b11] = Format(Timer - tm2, "0.000")
- End If
- [b12] = Format(Timer - tms, "0.000")
- MsgBox Format(Timer - tms, "0.000s ") & Format([b3], "#,##0") & "/" & Format(cnt, "#,##0")
- End Sub
- Sub dgZH0(s$, i&, t&) 'by kagawa
- Dim j&
- cnt = cnt + 1
- For j = i + 1 To m - n + t
- If t = n Then k = k + 1: Print #1, Mid(s & w & sj(j, 1), Len(w) + 1) Else Call dgZH0(s & w & sj(j, 1), j, t + 1)
- Next
- End Sub
- Sub dgZH1(s$, i&, t&) 'by kagawa
- Dim j&
- cnt = cnt + 1
- For j = i + 1 To m - n + t
- If t = n Then
- k = k + 1: jg(k, 1) = Mid(s & w & sj(j, 1), Len(w) + 1)
- If k = r Then [iv1].End(1).Offset(, 1).Resize(r) = jg: k = 0
- Else
- Call dgZH1(s & w & sj(j, 1), j, t + 1)
- End If
- Next
- End Sub
- Sub dgZH2(a&(), i&, t&) 'by kagawa
- Dim j&, l&
- cnt = cnt + 1
-
- For j = i + 1 To m - n + t
- a(t) = j
- If t = n Then
- k = k + 1
- For l = 1 To n
- jg(k, l) = sj(a(l), 1)
- Next
- If k = r Then [iv1].End(1).Offset(, 2).Resize(r, n) = jg: k = 0
- Else
- Call dgZH2(a, j, t + 1)
- End If
- Next
- End Sub
复制代码 |
|