☰

Excel：识别和计数集合

tag-icon • tag-icon microsoft-excel
Excel：识别和计数集合

摘要：尝试识别和计算 Excel 中可用的“集”（项目）。

主要逻辑及要求

• 假设有 6 个插槽或选项：A、B、C、D、E、F
• 要成为有效集合，它必须包含来自 3 个不同集合的 1 个项目，因此在测试 1 中例如：
  └✅ [A、B、C] 或 [A、C、F] 是有效集合
  └❌但是 [A、A、B] 不是有效集合，因为它只使用了 A 和 B
• 下表给出了两个数据集或场景需要考虑
• 数字代表您拥有的每个项目/插槽的数量（将它们视为物理对象）
• 一旦您使用了集合中的某个项目，数量（“可用数量”）就会减少 1，因此您需要用剩余的物品制作进一步的集合。

示例数据和示例

设想 A 乙 C 德 埃 F
测试 1 2 3 4 2 2 2
测试 2 - 1 2 2 1 -

在测试 1有可能5 组...
[A,B,C]; [B,C,D]; [C,E,F]; [A,B,C]; [D,E,F]

在测试 2有可能2 套...
[B,C,D]; [C,D,E]

备注（测试 1）：可以制作其他集合组合，例如：[A,B,C] [A,B,C] [D,E,F] [D,E,F]和[B,C,D] [B,C,D] [A,E,F] [A,E,F]，但是由于上述 5 个集合是最佳的（即比这两个组合中的任何一个多 1 个集合），所以这将是 Excel 中的目标/理想输出。

另外，需要澄清的是，如果它们是等价的，我对集合的组成没有任何特殊要求...例如，给定 A、B、C、D、E、F 各 1 个 - 选项 1：[A,B,C] [D,E,F]和选项 2：（[A,C,E] [B,D,F]以及其他类似的选项）对我来说都是完全有效和可以接受的。

附言：如果有帮助，可以把它想象成“烤蛋糕”。A、B、C 是不同的配料（例如糖、黄油、面粉等），每列中的数字表示你有多少配料。任何蛋糕配方只要使用 3 种不同配料中的 1 种，都是有效的。一旦使用一种配料，数量就会减少 1。目标是使用给定的配料堆烤出尽可能多的蛋糕。

请有人帮我找出解决这个问题的公式。

提前致谢！

编辑：问题在首次发布后更新，感谢@Engineer-Toast和@Ron Rosenfeld感谢您的评论和意见。

答案1

我想感谢大家在这里耐心等待并详细解释一切。为了不浪费大家的时间，我尝试用 VBA 来解决这个问题。我不知道如何将其变成纯公式。

我添加了一些注释，希望能够让大家理解这个过程。我不确定它是否能按预期工作，但据我所知，它为问题中的 test1 和 test2 输出了有效的解决方案。

Sub combinations()
    Dim test() As Variant, backup() As Variant, output As Collection
    test = Array(2, 3, 4, 2, 2, 2) ' the test counts are specified here
    Set output = New Collection
    
    Dim hci As Integer
    Do While canMakeSet(test)
        For i = 0 To 2
            hci = highestCountIndex(test) 'get the slot with highest count
            output.Add hci 'output that slot
            test(hci) = test(hci) - 1 'decrement slot by 1
            test(hci) = test(hci) * -1 ' "disable" slot so the next item is guaranteed to come from a different one
        Next i
        
        test = reset(test) 're-enable all slots
    Loop
    Debug.Print outputString(output)
End Sub

Function combinationsFc(ByVal rng As Range) As String
    Dim test() As Variant, backup() As Variant, output As Collection
    If rng.Rows.Count <> 1 Or rng.Columns.Count <> 6 Then
        combinationsFc = "Wrong input range size"
        Exit Function
    End If
    test = Array(rng.Cells(1, 1), rng.Cells(1, 2), rng.Cells(1, 3), rng.Cells(1, 4), rng.Cells(1, 5), rng.Cells(1, 6))
    Set output = New Collection
    
    Dim hci As Integer
    Do While canMakeSet(test)
        For i = 0 To 2
            hci = highestCountIndex(test) 'get the slot with highest count
            output.Add hci 'output that slot
            test(hci) = test(hci) - 1 'decrement slot by 1
            test(hci) = test(hci) * -1 ' "disable" slot so the next item is guaranteed to come from a different one
        Next i
        
        test = reset(test) 're-enable all slots
    Loop
    combinationsFc = outputString(output)
End Function

Function outputString(ByVal col As Collection) As String
    'this will just print out the output collection in a readable manner
    Dim val As String, letters As Variant
    letters = Array("A", "B", "C", "D", "E", "F")
    outputString = "["
    For i = 1 To col.Count
        val = letters(col(i))
        If i Mod 3 = 0 Then
            outputString = outputString & val & "]; ["
        Else
            outputString = outputString & val & ", "
        End If
    Next i
    outputString = Left(outputString, Len(outputString) - 3)
End Function

Function canMakeSet(ByVal arr As Variant) As Boolean
    'determines whether creating a set is possible by counting how many "slots" with at least one element are left
    'If there is at least 3, you can make a set
    canMakeSet = False
    Dim val As Integer, counter As Integer
    counter = 0
    For i = LBound(arr) To UBound(arr)
        If arr(i) > 0 Then counter = counter + 1
    Next i
    If counter >= 3 Then canMakeSet = True
End Function

Function highestCountIndex(ByVal arr As Variant) As Integer
    'determines the slot with highest count, element from this slot will be used next in a set
    highestCountIndex = LBound(arr)
    Dim val As Integer
    For i = LBound(arr) To UBound(arr)
        val = arr(i)
        If val > arr(highestCountIndex) Then highestCountIndex = i
    Next i
End Function

Function reset(ByVal arr As Variant) As Variant
    'I "disable" slots that are already in a set by turning them negative
    'this function resets all negative slots back to positive
    For i = LBound(arr) To UBound(arr)
        arr(i) = Math.Abs(arr(i))
    Next i
    reset = arr
End Function

您可以运行“combinations”子程序以在即时窗口中获取输出，或者在工作表中使用“combinationsFc”函数，如下所示：

在此处输入图片描述

答案2

假设您可以通过一次检查一个项目列表来“打包”您的套装，这里有一个公式（我认为）当任何项目的数量都不超过时有效int(sum(items)/3)：

=CHOOSECOLS(WRAPCOLS(TEXTSPLIT(CONCAT(REPT(B1:G1&",",B2:G2)),,",",TRUE),INT(SUM(B2:G2)/3)),1,2,3)

从内部开始：

  • REPT：重复文本项目数并添加逗号（创建项目列表）
  • CONCAT：加入名单
  • TEXTSPLIT：用逗号将列表拆分为单元格
  • WRAPCOLS：将一维数组转换为二维数组，包装由INT(SUM())
  • INT(SUM()CHOOSECOLS：获取前 3 列（当不能被 3 整除时出现第四列。

注意事项：

  • 我没有完全测试
  • 如上所述，这仅在没有特定项目超过项目计数总和的 1/3 时才有效（我猜当你的项目计数超过 1/3 时你需要一个 lamda 函数来执行）
  • 它只给出了一种可能的集合。我确信通过改变物品的包装方式，您可以得到其他集合。
  • 我确信还有其他公式组合比我创建的更简单。

答案3

我发布这个 wiki 是因为它只是一个中间步骤，可能会或可能不会帮助那些试图制定公式解决方案的人。

=TRANSPOSE(IFERROR(TEXTSPLIT(TEXTJOIN("|",FALSE,TRIM(REPT(B$1:G$1&" ",B2:G2)))," ","|",FALSE),""))

此公式可以创建一个表格，其中包含每个字母的正确数字，并以列分隔。如果您有一些基于合并列的公式，这可能会有所帮助。您可以使用类似的东西=LET(table,TRANSPOSE(~)...将其存储为变量，然后可以对其执行其他操作。

截屏

相关内容