Het ontdekken van alle mogelijke combinaties van getallen binnen een lijst die samen een bepaald bedrag opleveren, is een uitdaging waar veel Excel-gebruikers mee te maken kunnen krijgen, of het nu gaat om budgettering, planning of gegevensanalyse.

In dit voorbeeld hebben we een lijst met getallen en het doel is om te identificeren welke combinaties uit deze lijst samen 480 opleveren. De weergegeven schermafbeelding laat zien dat er vijf mogelijke groepen combinaties zijn die deze som behalen, inclusief combinaties zoals 300+120 +60, 250+120+60+50, onder andere. In dit artikel zullen we verschillende methoden onderzoeken om de specifieke combinaties van getallen in een lijst te lokaliseren die een bepaalde waarde in Excel optellen.

Vind een combinatie van getallen die gelijk zijn aan een gegeven som met de Oplosser-functie

Verkrijg alle combinaties van getallen die gelijk zijn aan een bepaalde som

• Door de door de gebruiker gedefinieerde functie te gebruiken
• Door een slimme functie – Kutools voor Excel

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

Zoek cellencombinaties die gelijk zijn aan een bepaalde som met de Oplosser-functie

Het lijkt misschien lastig om in Excel te duiken om celcombinaties te vinden die optellen tot een specifiek getal, maar de Solver Add-in maakt het een fluitje van een cent. We leiden u door de eenvoudige stappen om Oplosser in te stellen en de juiste combinatie van cellen te vinden, waardoor wat een complexe taak leek, eenvoudig en uitvoerbaar wordt.

Stap 1: Schakel de invoegtoepassing Oplosser in

1. Ga dan naar Dien in > Opties, in de Excel-opties dialoogvenster, klik Add-Ins in het linkerdeelvenster en klik vervolgens op Go knop. Zie screenshot:
   
2. Dan de Add-Ins dialoogvenster verschijnt, controleer dan de Oplosser-invoegtoepassing optie en klik op OK om deze invoegtoepassing met succes te installeren.
   

Stap 2: Voer de formule in

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

=SUMPRODUCT(B2:B10,A2:A10)

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

1. Klik Data > Oplosser naar de Oplosserparameter dialoogvenster, voer in het dialoogvenster de volgende bewerkingen uit:
   • (1.) Klik knop om de cel te selecteren B11 waar uw formule zich bevindt vanaf de Stel een doelstelling in sectie;
   • (2.) Vervolgens in de Naar sectie, selecteer Waarde vanen voer uw streefwaarde in 480 zoals je nodig hebt;
   • (3.) Onder de Door variabele cellen te veranderen sectie, klik dan om het celbereik te selecteren B2: B10 waar zal uw overeenkomstige nummers markeren.
   • (4.) Klik vervolgens op Toevoegen knop.
   • 
2. Vervolgens een Beperking toevoegen dialoogvenster wordt weergegeven, klik op om het celbereik te selecteren B2: B10En Select bak uit de vervolgkeuzelijst. Klik ten slotte OK knop. Zie screenshot:
   
3. In het Oplosserparameter dialoogvenster, klik op de Oplossen knop, enkele minuten later, een Oplosser resultaten Er verschijnt een dialoogvenster en u kunt zien dat de combinatie van cellen die gelijk zijn aan een bepaalde som 480 is gemarkeerd als 1 in kolom B. Oplosser resultaten dialoogvenster, selecteer alstublieft Houd Oplosser-oplossing optie en klik op OK om het dialoogvenster te verlaten. Zie screenshot:
   

Verkrijg alle combinaties van getallen die gelijk zijn aan een bepaalde som

Door de diepere mogelijkheden van Excel te verkennen, kunt u elke getalcombinatie vinden die overeenkomt met een specifieke som, en dat is eenvoudiger dan u misschien denkt. In dit gedeelte ziet u twee methoden om alle combinaties van getallen te vinden die gelijk zijn aan een bepaalde som.

Verkrijg alle combinaties van getallen die gelijk zijn aan een bepaalde som met de 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 hieronder beschreven aangepaste functie als een effectief hulpmiddel.

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

1. Houd de toets ingedrukt ALT + F11 toetsen in Excel, en het opent het Microsoft Visual Basic voor toepassingen venster.
2. Klik Invoegen > Moduleen plak de volgende code in het modulevenster.
   VBA-code: verkrijg alle combinaties van getallen die gelijk zijn aan een bepaalde 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 u de code hebt geplakt, sluit u het codevenster om terug te gaan naar het werkblad. Voer de volgende formule in een lege cel in om het resultaat uit te voeren en druk vervolgens op Enter toets om alle combinaties te krijgen. Zie schermafbeelding:

=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))

Verkrijg alle combinaties van getallen die gelijk zijn aan een bepaalde som met een krachtige functie

Gezien de beperkingen van de bovengenoemde functie, raden we een snelle en alomvattende oplossing aan: Kutools voor Excel's Verzin een nummer-functie, die compatibel is met elke versie van Excel. Dit alternatief kan effectief omgaan met positieve getallen, decimalen en negatieve getallen. Met deze functie kun je snel alle combinaties krijgen die gelijk zijn aan een bepaalde som.

1. Klik Kutools > Content > Verzin een nummer, zie screenshot:
   
2. Vervolgens in de Verzin een nummer dialoogvenster, klik dan om de nummerlijst die u wilt gebruiken te selecteren uit de Databronen voer vervolgens het totale aantal in het Som tekstveld. Klik ten slotte op OK knop, zie screenshot:
   
3. En dan verschijnt er een promptvenster om u eraan te herinneren een cel te selecteren om het resultaat te lokaliseren en vervolgens te klikken OK, zie screenshot:
   
4. En nu zijn alle combinaties die gelijk zijn aan dat gegeven getal weergegeven zoals hieronder afgebeeld:
   

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

Soms bevindt u zich misschien in een situatie waarin u alle mogelijke combinaties van getallen moet identificeren die samen een som vormen binnen een bepaald bereik. U probeert bijvoorbeeld elke mogelijke groep getallen te vinden waarbij het totaal tussen 470 en 480 ligt.

Het ontdekken van alle mogelijke combinaties van getallen die binnen een bepaald bereik een waarde vormen, is een fascinerende en zeer praktische uitdaging in Excel. In deze sectie wordt een VBA-code geïntroduceerd voor het oplossen van deze taak.

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

1. Houd de toets ingedrukt ALT + F11 toetsen in Excel, en het opent het Microsoft Visual Basic voor toepassingen venster.
2. Klik Invoegen > Moduleen plak de volgende code in het modulevenster.
   VBA-code: ontvang alle combinaties van getallen die samen een specifiek bereik vormen

   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. Druk na het plakken van de code op F5 Om deze code uit te voeren, selecteert u in het eerste pop-upvenster het bereik van getallen dat u wilt gebruiken en klikt u op OK. Zie screenshot:
   
2. In het tweede promptvenster selecteert of typt u het ondergrensnummer en klikt u op OK. Zie screenshot:
   
3. In het derde promptvenster selecteert of typt u het bovenlimietnummer en klikt u op OK. Zie screenshot:
   
4. Selecteer in het laatste promptvenster een uitvoercel, waar de resultaten zullen worden uitgevoerd. Dan klikken OK. Zie screenshot:
   

Resultaat

Nu wordt elke kwalificerende combinatie in opeenvolgende rijen in het werkblad weergegeven, te beginnen bij de uitvoercel die u hebt gekozen.

Excel biedt u verschillende manieren om groepen getallen te vinden die samen een bepaald totaal vormen. Elke methode werkt anders, dus u kunt er een kiezen op basis van hoe bekend u bent met Excel en wat u nodig heeft voor uw project. Als u geïnteresseerd bent in meer Excel-tips en -trucs, biedt onze website duizenden tutorials aan klik hier om ze te openen. Bedankt voor het lezen en we kijken ernaar uit u in de toekomst van meer nuttige informatie te voorzien!

Gerelateerde artikelen: