Hoe alle combinaties te vinden die gelijk zijn aan een bepaalde som in Excel?

Het ontdekken van alle mogelijke combinaties van getallen binnen een lijst die optellen tot een specifieke som, is een uitdaging waarmee veel Excel-gebruikers mogelijk te maken krijgen, of het nu voor budgettering, planning of data-analyse is.

In dit voorbeeld hebben we een lijst met getallen, en het doel is om te identificeren welke combinaties uit deze lijst samen optellen tot 480. De bijgevoegde schermafbeelding demonstreert dat er vijf mogelijke groepen combinaties zijn die deze som bereiken, inclusief combinaties zoals 300+120+60, 250+120+60+50, enzovoort. In dit artikel zullen we verschillende methoden verkennen om de specifieke combinaties van getallen binnen een lijst te identificeren die een aangewezen waarde in Excel totaliseren.

Zoek een combinatie van getallen die gelijk is aan een gegeven som met de Solver-functie

Krijg alle combinaties van getallen die gelijk zijn aan een gegeven som

• Door gebruik te maken van een door de gebruiker gedefinieerde functie
• Door een slimme functie – Kutools voor Excel

Krijg alle combinaties van getallen die een som hebben in een bereik met VBA-code

Zoek celcombinaties die gelijk zijn aan een gegeven som met de Solver-functie

Het duiken in Excel om celcombinaties te vinden die optellen tot een specifiek getal kan ontmoedigend lijken, maar de Solver-invoegtoepassing maakt het een makkie. We leiden je door de eenvoudige stappen om Solver in te stellen en de juiste combinatie van cellen te vinden, waardoor wat een complexe taak leek, eenvoudig en haalbaar wordt.

Stap 1: Schakel de Solver-invoegtoepassing in

1. Ga naar Bestand > Opties, in het Excel-opties dialoogvenster, klik op Invoegtoepassingen vanuit het linkerpaneel, en klik vervolgens op Ga knop. Zie screenshot:
   
2. Vervolgens verschijnt het Invoegtoepassingen dialoogvenster, vink de Solver-invoegtoepassing optie aan, en klik op OK om deze invoegtoepassing succesvol te installeren.
   

Stap 2: Voer de formule in

Nadat je de Solver-invoegtoepassing hebt geactiveerd, moet je deze formule invoeren in cel B11:

=SUMPRODUCT(B2:B10,A2:A10)

Stap 3: Configureer en voer Solver uit om het resultaat te krijgen

1. Klik op Gegevens > Solver om naar het Solver-parameter dialoogvenster te gaan, in het dialoogvenster voer je de volgende handelingen uit:
   • (1.) Klik op knop om de cel te selecteren B11 waar je formule zich bevindt vanuit de Doel instellen sectie;
   • (2.) Selecteer vervolgens in de sectie Naar de optie Waarde Van, en voer je doelwaarde 480 in zoals je nodig hebt;
   • (3.) Onder de Door veranderlijke cellen te wijzigen sectie, klik op knop om het celbereik te selecteren B2:B10 waar je overeenkomstige getallen markeert.
   • (4.) Klik vervolgens op de knop Toevoegen.
   • 
2. Vervolgens verschijnt een Beperking toevoegen dialoogvenster, klik op knop om het celbereik te selecteren B2:B10, en selecteer bin uit de vervolgkeuzelijst. Klik ten slotte op de OK knop. Zie screenshot:
   
3. In het Solver-parameter dialoogvenster, klik op de Oplossen knop, enkele minuten later verschijnt een Solver-resultaten dialoogvenster, en je kunt zien dat de combinatie van cellen die gelijk zijn aan een gegeven som van 480 gemarkeerd zijn als 1 in kolom B. In het Solver-resultaten dialoogvenster, selecteer Behoud Solver-oplossing optie, en klik op OK om het dialoogvenster te sluiten. Zie screenshot:
   

Krijg alle combinaties van getallen die gelijk zijn aan een gegeven som

Het verkennen van de diepere mogelijkheden van Excel laat je elke getallencombinatie vinden die overeenkomt met een specifieke som, en het is makkelijker dan je misschien denkt. In deze sectie laten we je twee methoden zien om alle combinaties van getallen te vinden die gelijk zijn aan een gegeven som.

Krijg alle combinaties van getallen die gelijk zijn aan een gegeven som met een door de gebruiker gedefinieerde functie

Om elke mogelijke combinatie van getallen uit een specifieke set te ontdekken die gezamenlijk een bepaalde waarde bereiken, dient de onderstaande aangepaste functie als een effectief hulpmiddel.

Stap 1: Open de VBA-module-editor en kopieer de code

1. Houd de toetsen ALT + F11 ingedrukt in Excel, en het venster Microsoft Visual Basic for Applications wordt geopend.
2. Klik op Invoegen > Module, en plak de volgende code in het Module-venster.
   VBA-code: Krijg alle combinaties van getallen die gelijk zijn aan een gegeven som

   Public Function MakeupANumber(xNumbers As Range, xCount As Long)
   'updateby Extendoffice
       Dim arrNumbers() As Long
       Dim arrRes() As String
       Dim ArrTemp() As Long
       Dim xIndex As Long
       Dim rg As Range
   
       MakeupANumber = ""
       
       If xNumbers.CountLarge = 0 Then Exit Function
       ReDim arrNumbers(xNumbers.CountLarge - 1)
       
       xIndex = 0
       For Each rg In xNumbers
           If IsNumeric(rg.Value) Then
               arrNumbers(xIndex) = CLng(rg.Value)
               xIndex = xIndex + 1
           End If
       Next rg
       If xIndex = 0 Then Exit Function
       
       ReDim Preserve arrNumbers(0 To xIndex - 1)
       ReDim arrRes(0)
       
       Call Combinations(arrNumbers, xCount, ArrTemp(), arrRes())
       ReDim Preserve arrRes(0 To UBound(arrRes) - 1)
       MakeupANumber = arrRes
   End Function
   
   Private Sub Combinations(Numbers() As Long, Count As Long, ArrTemp() As Long, ByRef arrRes() As String)
   
       Dim currentSum As Long, i As Long, j As Long, k As Long, num As Long, indRes As Long
       Dim remainingNumbers() As Long, newCombination() As Long
       
       currentSum = 0
       If (Not Not ArrTemp) <> 0 Then
           For i = LBound(ArrTemp) To UBound(ArrTemp)
               currentSum = currentSum + ArrTemp(i)
           Next i
       End If
    
       If currentSum = Count Then
           indRes = UBound(arrRes)
           ReDim Preserve arrRes(0 To indRes + 1)
           
           arrRes(indRes) = ArrTemp(0)
           For i = LBound(ArrTemp) + 1 To UBound(ArrTemp)
               arrRes(indRes) = arrRes(indRes) & "," & ArrTemp(i)
           Next i
       End If
       
       If currentSum > Count Then Exit Sub
       If (Not Not Numbers) = 0 Then Exit Sub
       
       For i = 0 To UBound(Numbers)
           Erase remainingNumbers()
           num = Numbers(i)
           For j = i + 1 To UBound(Numbers)
               If (Not Not remainingNumbers) <> 0 Then
                   ReDim Preserve remainingNumbers(0 To UBound(remainingNumbers) + 1)
               Else
                   ReDim Preserve remainingNumbers(0 To 0)
               End If
               remainingNumbers(UBound(remainingNumbers)) = Numbers(j)
               
           Next j
           Erase newCombination()
   
           If (Not Not ArrTemp) <> 0 Then
               For k = 0 To UBound(ArrTemp)
                   If (Not Not newCombination) <> 0 Then
                       ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
                   Else
                       ReDim Preserve newCombination(0 To 0)
                   End If
                   newCombination(UBound(newCombination)) = ArrTemp(k)
   
               Next k
           End If
           
           If (Not Not newCombination) <> 0 Then
               ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
           Else
               ReDim Preserve newCombination(0 To 0)
           End If
           
           newCombination(UBound(newCombination)) = num
   
           Combinations remainingNumbers, Count, newCombination, arrRes
       Next i
   
   End Sub
   

Stap 2: Voer de aangepaste formule in om het resultaat te krijgen

Nadat je de code hebt geplakt, sluit je het codevenster om terug te keren naar het werkblad. Voer de volgende formule in een lege cel in om het resultaat uit te voeren, en druk vervolgens op de Enter-toets om alle combinaties te krijgen. Zie screenshot:

=MakeupANumber(A2:A10,B2)

Tip: Als je de combinatieresultaten verticaal in een kolom wilt weergeven, pas dan de volgende formule toe:

=TRANSPOSE(MakeupANumber(A2:A10,B2))

Krijg alle combinaties van getallen die gelijk zijn aan een gegeven som met een krachtige functie

Gezien de beperkingen van de voorgaande functie, raden we een snelle en uitgebreide oplossing aan: de functie Getallen aanvullen van Kutools voor Excel, die compatibel is met elke versie van Excel. Dit alternatief kan positieve getallen, decimalen en negatieve getallen effectief verwerken. Met deze functie kun je snel alle combinaties krijgen die gelijk zijn aan een gegeven som.

1. Klik op Kutools > Tekst > Getallen aanvullen, zie screenshot:
   
2. Vervolgens, in het Getallen aanvullen dialoogvenster, klik op knop om de nummerlijst te selecteren die je wilt gebruiken vanuit de Bronbereik, en voer vervolgens het totaal aantal in in de Som tekstvak. Klik ten slotte op de OK knop, zie screenshot:
   
3. Vervolgens verschijnt er een pop-upvenster om je eraan te herinneren een cel te selecteren om het resultaat te plaatsen, klik dan op OK, zie screenshot:
   
4. En nu worden alle combinaties die gelijk zijn aan dat gegeven getal weergegeven zoals in de onderstaande screenshot te zien is:
   

Krijg alle combinaties van getallen die een som hebben in een bereik met VBA-code

Soms kom je in een situatie terecht waarin je alle mogelijke combinaties van getallen moet identificeren die samen optellen tot een som binnen een specifiek bereik. Bijvoorbeeld, je zoekt misschien naar elke mogelijke groepering van getallen waarbij het totaal tussen 470 en 480 ligt.

Het ontdekken van alle mogelijke combinaties van getallen die optellen tot een waarde binnen een specifiek bereik vertegenwoordigt een fascinerende en zeer praktische uitdaging in Excel. In deze sectie introduceren we een VBA-code om deze taak op te lossen.

Stap 1: Open de VBA-module-editor en kopieer de code

1. Houd de toetsen ALT + F11 ingedrukt in Excel, en het venster Microsoft Visual Basic for Applications wordt geopend.
2. Klik op Invoegen > Module, en plak de volgende code in het Module-venster.
   VBA-code: Krijg alle combinaties van getallen die optellen tot een specifiek bereik

   Sub Getall_combinations()
   'Updateby Extendoffice
       Dim xNumbers As Variant
       Dim Output As Collection
       Dim rngSelection As Range
       Dim OutputCell As Range
       Dim LowLimit As Long, HiLimit As Long
       Dim i As Long, j As Long
       Dim TotalCombinations As Long
       Dim CombTotal As Double
       Set Output = New Collection
       On Error Resume Next
       Set rngSelection = Application.InputBox("Select the range of numbers:", "Kutools for Excel", Type:=8)
       If rngSelection Is Nothing Then
           MsgBox "No range selected. Exiting macro.", vbInformation, "Kutools for Excel"
           Exit Sub
       End If
       On Error GoTo 0
       xNumbers = rngSelection.Value
       LowLimit = Application.InputBox("Select or enter the low limit number:", "Kutools for Excel", Type:=1)
       HiLimit = Application.InputBox("Select or enter the high limit number:", "Kutools for Excel", Type:=1)
       On Error Resume Next
       Set OutputCell = Application.InputBox("Select the first cell for output:", "Kutools for Excel", Type:=8)
       If OutputCell Is Nothing Then
           MsgBox "No output cell selected. Exiting macro.", vbInformation, "Kutools for Excel"
           Exit Sub
       End If
       On Error GoTo 0
       TotalCombinations = 2 ^ (UBound(xNumbers, 1) * UBound(xNumbers, 2))
       For i = 1 To TotalCombinations - 1
           Dim tempArr() As Double
           ReDim tempArr(1 To UBound(xNumbers, 1) * UBound(xNumbers, 2))
           CombTotal = 0
           Dim k As Long: k = 0
           
           For j = 1 To UBound(xNumbers, 1)
               If i And (2 ^ (j - 1)) Then
                   k = k + 1
                   tempArr(k) = xNumbers(j, 1)
                   CombTotal = CombTotal + xNumbers(j, 1)
               End If
           Next j
           If CombTotal >= LowLimit And CombTotal <= HiLimit Then
               ReDim Preserve tempArr(1 To k)
               Output.Add tempArr
           End If
       Next i
       Dim rowOffset As Long
       rowOffset = 0
       Dim item As Variant
       For Each item In Output
           For j = 1 To UBound(item)
               OutputCell.Offset(rowOffset, j - 1).Value = item(j)
           Next j
           rowOffset = rowOffset + 1
       Next item
   End Sub
   
   
   

Stap 2: Voer de code uit

1. Nadat je de code hebt geplakt, druk op de F5 toets om deze code uit te voeren, in het eerste pop-updialoogvenster selecteer je het bereik van getallen dat je wilt gebruiken, en klik je op OK. Zie screenshot:
   
2. In het tweede promptvenster, selecteer of typ het laagste limietgetal, en klik op OK. Zie screenshot:
   
3. In het derde promptvenster, selecteer of typ het hoogste limietgetal, en klik op OK. Zie screenshot:
   
4. In het laatste promptvenster, selecteer een uitvoercel, waar de resultaten beginnen te worden uitgevoerd. Klik vervolgens op OK. Zie screenshot:
   

Resultaat

Nu wordt elke kwalificerende combinatie weergegeven in opeenvolgende rijen in het werkblad, startend vanaf de uitvoercel die je hebt gekozen.

Excel biedt je verschillende manieren om groepen getallen te vinden die optellen tot een bepaald totaal, elke methode werkt anders, dus je kunt er een kiezen op basis van hoe bekend je bent met Excel en wat je nodig hebt voor je project. Als je geïnteresseerd bent in het verkennen van meer Excel-tips en -trucs, onze website biedt duizenden tutorials. Bedankt voor het lezen, en we kijken ernaar uit om je in de toekomst meer nuttige informatie te bieden!

Gerelateerde artikelen: