Creating combinations using Excel

问题:

I was wondering if there is a function, or combination of functions (maybe it requires VBA) in Excel that will help me solve the following problem:

There are 8 people in the group. I need to figure out and display all of the possible, non-repeating combinations created when 4 people are selected out of the 8. The order of the selected individuals isn’t important. I just need to find all of the unique combinations.

For example: The 8 people are Bob, Carol, Ted, Alice, Reed, Sue, Johnny, Ben (Cells A1 through A8 each contain one of the names).

One combination is Bob, Ted, Reed, Johnny. For my problem the order of the names isn’t important meaning Bob, Ted, Reed, Johnny is the same as Ted, Bob, Johnny, Reed. So any combination of those 4 people counts as one instance.

I’m not just trying to figure out how many combinations are possible. I need to actually see the possible combinations.

回答1:

I built a binary evaluator:

Public Sub DebugAllCombinations(lPickSize As Long, sPossibilities As String, Optional sDelimiter As String = ";")

    Dim i                   As Long
    Dim j                   As Long
    Dim sBIN                As String
    Dim aPossibilities()    As String
    Dim lSum                As Long
    Dim lHitCount           As Long

    aPossibilities = Split(sPossibilities, sDelimiter)

    For i = 1 To 2 ^ (UBound(aPossibilities) + 1) - 1
        lSum = 0
        sBIN = WorksheetFunction.Dec2Bin(i)
        For j = 1 To Len(sBIN)
            lSum = lSum + CLng(Mid(sBIN, j, 1))
        Next j
        If lSum = lPickSize Then
            For j = 1 To Len(sBIN)
                If Mid(sBIN, j, 1) = "1" Then Debug.Print aPossibilities(Len(sBIN) - j) & sDelimiter;
            Next j
            Debug.Print
            lHitCount = lHitCount + 1
        End If
    Next i

    Debug.Print lHitCount & " possibilities found"

End Sub

You can use it like this

DebugAllCombinations 4, "Person1;Person2;Person3;Person4;Person5;Person6;Person7;Person8"

It will debug in the immediate window

回答2:

Not tested, but if I understood your question right I think this should do it:

Sub combinationEnumeration(pool As String, elements As Integer, Optional delim As String)
Dim poolArray() As String, result As String

If delim = "" Then delim = ";"
poolArray = Split(pool, delim)

result = "Selection pool: " & pool & vbCr & "Number of selected elements: " & elements & vbCr & vbCr & "Result:" & vbCr

For i = 0 To UBound(poolArray) - 3
    For j = i + 1 To UBound(poolArray) - 2
        For k = j + 1 To UBound(poolArray)) - 1
            For l = k + 1 To UBound(poolArray)
                result = result & poolArray(i) & poolArray(j) & poolArray(k) & poolArray(l) & ";" & vbCr
            Next l
        Next k
    Next j
Next i

Debug.Print result
End Sub

Usage: Call combinationEnumeration("A;B;C;D;E;F;G;H", 4)

EDIT: Fixed small error. Code is correct now and outputs the expected number of results. You will get 70 lines, which you can double check by solving the binomial C(8,4): http://www.wolframalpha.com/input/?i=c%288%2C4%29.

回答3:

=COMBIN(number, number_chosen)