Excel VBA中的组合算法 [英] Combination Algorithm in Excel VBA

问题描述

我需要一个算法，生成一个设定数字的所有可能的组合并输出到Excel电子表格。

I need an algorithm which generates all possible combination of a set number and output all of them onto Excel spreadsheet.

例如，在n = 5（1， 2,3,4,5）和r = 2（为此创建了一个小gui），它将生成所有可能的组合，并将它们输出到excel电子表格这样...

For example, with n = 5(1,2,3,4,5) and r = 2(created a small gui for this), it will generate all possible combinations and output them into excel spreadsheet like this...

1,2
1,3
1,4
...

打印的顺序并不重要。它可以先打印（5,1），然后（1,2）。
任何人都可以告诉我如何做到这一点？

The order in which it prints doesn't matter. It can first print (5,1), then (1,2). Can anyone show me how to do this?

非常感谢。

推荐答案

这段代码如何...

Option Explicit

Private c As Integer

Sub test_print_nCr()
  print_nCr 5, 3, Range("A1")
End Sub

Function print_nCr(n As Integer, r As Integer, p As Range)
  c = 1
  internal_print_nCr n, r, p, 1, 1
End Function


Private Function internal_print_nCr(n As Integer, r As Integer, ByVal p As Range, Optional i As Integer, Optional l As Integer) As Integer

  ' n is the number of items we are choosing from
  ' r is the number of items to choose
  ' p is the upper corner of the output range
  ' i is the minimum item we are allowed to pick
  ' l is how many levels we are in to the choosing
  ' c is the complete set we are working on

  If n < 1 Or r > n Or r < 0 Then Err.Raise 1
  If i < 1 Then i = 1
  If l < 1 Then l = 1
  If c < 1 Then c = 1
  If r = 0 then 
    p = 1
    Exit Function
  End If

  Dim x As Integer
  Dim y As Integer

  For x = i To n - r + 1
    If r = 1 Then
      If c > 1 Then
        For y = 0 To l - 2
          If p.Offset(c - 1, y) = "" Then p.Offset(c - 1, y) = p.Offset(c - 2, y)
        Next
      End If
      p.Offset(c - 1, l - 1) = x
      c = c + 1
    Else
      p.Offset(c - 1, l - 1) = x
      internal_print_nCr n, r - 1, p, x + 1, l + 1
    End If
  Next

End Function

这篇关于Excel VBA中的组合算法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！