Hoe vindt u in Excel alle combinaties die samen de opgegeven som opleveren?

Het vinden van alle mogelijke combinaties van getallen in een lijst die optellen tot een specifieke som is een uitdaging die veel Excel-gebruikers tegenkomen, of het nu gaat om budgettering, planning of data-analyse.

In dit voorbeeld hebben we een lijst met getallen, en het doel is om te achterhalen welke combinaties uit die lijst optellen tot 480. De bijgevoegde screenshot laat zien dat er vijf mogelijke groepen combinaties zijn die deze som opleveren, zoals 300 + 120 + 60, 250 + 120 + 60 + 50 en andere. In dit artikel verkennen we verschillende methoden om in Excel de specifieke combinaties van getallen binnen een lijst te vinden die samen een bepaalde waarde vormen.

Een combinatie van getallen vinden die gelijk is aan een opgegeven som met de Solver-functie

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

• Met een door gebruiker gedefinieerde functie
• Met een slimme functie – Kutools voor Excel

Alle combinaties van getallen ophalen die een som vormen binnen een bereik met VBA-code

Celcombinatie vinden die gelijk is aan een opgegeven som met de Solver-functie

Dieper duiken in Excel om celcombinaties te vinden die optellen tot een specifiek getal lijkt misschien ontmoedigend, maar met de Solver-invoegtoepassing is het kinderlijk eenvoudig. We begeleiden u stap voor stap door de intuïtieve instellingen van Solver om precies de juiste celcombinatie te vinden – waardoor een ogenschijnlijk complexe taak plotseling eenvoudig en haalbaar wordt.

Stap 1: Solver-invoegtoepassing inschakelen

1. Ga naar Bestand > Opties. In het dialoogvenster Excel-opties klikt u op Invoegtoepassingen in het linkerdeelvenster en vervolgens op Ga. Zie screenshot:
   
2. Vervolgens verschijnt het Invoegtoepassingen-dialoogvenster. Schakel de optie Solver-invoegtoepassing in en klik op OK om deze invoegtoepassing succesvol te installeren.
   

Stap 2: Formule invoeren

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

=SUMPRODUCT(B2:B10,A2:A10)

Stap 3: Solver configureren en uitvoeren om het resultaat te krijgen

1. Klik op Gegevens > Solver om naar het Solver-parameters dialoogvenster te gaan. Voer in dit dialoogvenster de volgende handelingen uit:
   • (1.) Klik op de knop om cel B11waar uw formule zich bevindt te selecteren in het gedeelte Doel instellen;
   • (2.) Selecteer vervolgens in het gedeelte NaarWaarde van, en voer uw doelwaarde in 480zoals u dat nodig heeft;
   • (3.) Klik onder het gedeelte Door variabele cellen te wijzigen op de knop om het celbereik B2:B10 te selecteren waarin uw bijbehorende getallen worden gemarkeerd.
   • (4.) Klik daarna op de knop Toevoegen.
   • 
2. Vervolgens wordt het dialoogvenster Beperking toevoegen weergegeven. Klik op de knop om het celbereik B2:B10 te selecteren en kies bin in de vervolgkeuzelijst. Klik ten slotte op de knop OK. Zie screenshot:
   
3. Klik in het dialoogvenster Solver-parameters op de knop Oplossen. Enkele minuten later verschijnt het dialoogvenster Solver-resultaten. U ziet dat de combinatie van cellen die samen de opgegeven som van 480 oplevert, wordt gemarkeerd met een 1 in kolom B. In het dialoogvenster Solver-resultaten, selecteert u Houd Solver-oplossing en klikt u op OK om het dialoogvenster te sluiten. Zie screenshot:
   

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

Door de geavanceerde mogelijkheden van Excel te verkennen, ontdekt u hoe u elke getalcombinatie kunt vinden die overeenkomt met een specifieke som – en het is eenvoudiger dan u denkt. In deze sectie tonen we u twee methoden om alle combinaties van getallen te vinden die gelijk zijn aan een opgegeven som.

Alle combinaties van getallen ophalen die gelijk zijn aan een opgegeven som met een door de gebruiker gedefinieerde functie

Om elke mogelijke combinatie van getallen uit een specifieke set te vinden die samen een bepaalde doelwaarde oplevert, is de hieronder beschreven aangepaste functie een krachtig hulpmiddel.

Stap 1: VBA-module-editor openen en de code kopiëren

1. Houd de toetsen ALT + F11 ingedrukt in Excel om het venster Microsoft Visual Basic for Applications te openen.
2. Klik op Invoegen>Moduleen plak de volgende code in het modulevenster.
   VBA-code: Alle combinaties van getallen ophalen die gelijk zijn aan een opgegeven 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: Aangepaste formule invoeren om het resultaat te krijgen

Nadat u de code hebt geplakt, sluit u het codevenster om terug te keren naar het werkblad. Voer in een lege cel de volgende formule in om het resultaat weer te geven en druk daarna op de toets Enter om alle combinaties op te halen. Zie screenshot:

=MakeupANumber(A2:A10,B2)

Tip: Als u de combinatieresultaten verticaal in een kolom wilt weergeven, past u de volgende formule toe:

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

Alle combinaties van getallen ophalen die gelijk zijn aan een opgegeven som met een krachtige functie

Gezien de beperkingen van de eerder genoemde functie, raden we een snelle en uitgebreide oplossing aan: de functie **Getallen aanvullen** van Kutools voor Excel, compatibel met elke Excel-versie. Deze krachtige alternatief handelt positieve getallen, decimalen én negatieve getallen vlot af — en levert in een handomdraai alle combinaties die exact overeenkomen met uw doelsom.

1. Klik op Kutools>Inhoud>Getallen aanvullen, zie screenshot:
   
2. Vervolgens klikt u in het dialoogvenster Getallen aanvullen op de knop om de lijst met getallen te selecteren die u wilt gebruiken uit Bronbereik, en voert u het totaalgetal in het tekstvak Som in. Klik ten slotte op de knop OK. Zie screenshot:
   
3. Er verschijnt vervolgens een meldingsvenster waarin u wordt gevraagd een cel te selecteren om het resultaat in te plaatsen. Klik daarna op OK. Zie screenshot:
   
4. Nu worden alle combinaties die gelijk zijn aan het opgegeven getal weergegeven, zoals in onderstaande screenshot:
   

Alle combinaties van getallen ophalen die een som vormen binnen een bereik met VBA-code

Soms komt u in een situatie terecht waarin u alle mogelijke combinaties van getallen moet vinden die samen een som opleveren binnen een bepaald bereik — bijvoorbeeld elke mogelijke groepering van getallen waarvan het totaal tussen 470 en 480 ligt.

Het vinden van alle mogelijke combinaties van getallen die optellen tot een waarde binnen een specifiek bereik is een fascinerende én uiterst praktische uitdaging in Excel. In deze sectie presenteren we een VBA-code om deze taak eenvoudig en efficiënt op te lossen.

Stap 1: VBA-module-editor openen en de code kopiëren

1. Houd de toetsen ALT + F11 ingedrukt in Excel om het venster Microsoft Visual Basic for Applications te openen.
2. Klik op Invoegen>Moduleen plak de volgende code in het modulevenster.
   VBA-code: Alle combinaties van getallen ophalen 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: Code uitvoeren

1. Nadat u de code hebt geplakt, drukt u op de toets F5 om deze uit te voeren. Selecteer in het eerste weergegeven dialoogvenster het bereik met getallen dat u wilt gebruiken en klik op OK. Zie screenshot:
   
2. Selecteer of typ in het tweede meldingsvenster het ondergrensgetal en klik op OK. Zie de screenshot:
   
3. Selecteer of typ in het derde meldingsvenster het bovengrensgetal en klik op OK. Zie de screenshot:
   
4. Selecteer in het laatste meldingsvenster een uitvoercel waar de resultaten worden weergegeven en klik vervolgens op OK. Zie screenshot:
   

Resultaat

Elke in aanmerking komende combinatie verschijnt nu in opeenvolgende rijen op het werkblad, te beginnen bij de door u gekozen uitvoercel.

Excel biedt verschillende manieren om groepen getallen te vinden die optellen tot een bepaald totaal. Elke methode werkt anders, zodat u de beste keuze kunt maken op basis van uw vertrouwdheid met Excel en de eisen van uw project. Als u nog meer Excel-tips en -trucs wilt ontdekken,biedt onze website duizenden tutorials. Bedankt voor het lezen — we kijken ernaar uit u in de toekomst nog meer waardevolle informatie te bieden!

Gerelateerde artikelen: