|
楼主 |
发表于 2018-12-20 10:19
|
显示全部楼层
将A列数据,列出所有组合
- Dim arr, r, d
- Sub test()
- Set d = CreateObject("scripting.dictionary")
- Application.ScreenUpdating = False
- Sheets(1).UsedRange.Offset(1, 1).ClearContents
- arr = Sheets(1).UsedRange
- For j = 2 To UBound(arr)
- If Len(arr(j, 1)) > 0 Then d(arr(j, 1)) = d.Count
- Next j
- r = 2
- arr = d.keys
- For j = 0 To UBound(arr)
- d.RemoveAll
- d(arr(j)) = d.Count
- dg arr, j + 1
- Next j
- Application.ScreenUpdating = True
- End Sub
- Sub dg(arr, j)
- For i = j To UBound(arr)
- d(arr(i)) = ""
- Cells(r, 2) = Join(d.keys, ",")
- r = r + 1
- dg arr, i + 1
- d.Remove (arr(i))
- Next i
- End Sub
复制代码
下面是修改nm,列出指定数量的组合,nm>=2
- Dim arr, r, d, nm
- Sub test1()
- nm = 4
- Set d = CreateObject("scripting.dictionary")
- Application.ScreenUpdating = False
- Sheets(1).UsedRange.Offset(1, 1).ClearContents
- arr = Sheets(1).UsedRange
- For j = 2 To UBound(arr)
- If Len(arr(j, 1)) > 0 Then d(arr(j, 1)) = d.Count
- Next j
- r = 2
- arr = d.keys
- For j = 0 To UBound(arr)
- d.RemoveAll
- d(arr(j)) = ""
- dg arr, j + 1
- Next j
- Application.ScreenUpdating = True
- End Sub
- Sub dg(arr, j)
- For i = j To UBound(arr)
- d(arr(i)) = ""
- If d.Count = nm Then
- Cells(r, 4) = Join(d.keys, ",")
- r = r + 1
- Else
- If d.Count < nm Then
- dg arr, i + 1
- End If
- End If
- d.Remove (arr(i))
- Next i
- End Sub
复制代码 |
|