使用Excel创建组合 [英] Creating combinations using Excel

问题描述

我想知道Excel中是否有一个功能或功能组合（也许要求VBA）可以帮助我解决以下问题：

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:

有小组中有8人。我需要弄清楚并显示从8人中选出4个人时创建的所有可能的，非重复的组合。所选个人的顺序并不重要。我只需要找到所有独特的组合即可。

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.

例如：
这8个人是Bob，Carol，Ted，Alice，Reed，Sue，Johnny ，Ben（单元格A1到A8每个都包含一个名称）。

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

其中一个组合是Bob，Ted，Reed和Johnny。对于我的问题，名称的顺序并不重要，这意味着Bob，Ted，Reed，Johnny与Ted，Bob，Johnny，Reed相同。因此，这4个人的任何组合都算作一个实例。

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.

推荐答案

我构建了一个二进制评估程序：

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

您可以像这样使用它

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

它将在立即窗口中调试

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