Ga naar hoofdinhoud

Hoe vind ik alle combinaties die gelijk zijn aan een bepaalde som in Excel?

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

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)
Note: In deze formule: B2: B10 is een kolom met lege cellen naast uw nummerlijst, en A2: A10 is de nummerlijst die u gebruikt.

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:
Note: Deze methode heeft echter een beperking: deze kan slechts één combinatie van cellen identificeren die optellen tot de opgegeven som, zelfs als er meerdere geldige combinaties bestaan.

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)
Note: In deze formule: A2: A10 is de nummerlijst, en B2 is het totale bedrag dat u wilt ontvangen.

Tip: Als u de combinatieresultaten verticaal in een kolom wilt weergeven, past u de volgende formule toe:
=TRANSPOSE(MakeupANumber(A2:A10,B2))
De beperkingen van deze methode:
  • Deze aangepaste functie werkt alleen in Excel 365 en 2021.
  • Deze methode is uitsluitend effectief voor positieve getallen; decimale waarden worden automatisch afgerond op het dichtstbijzijnde gehele getal, en negatieve getallen resulteren in fouten.

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.

Tips: Om dit toe te passen Verzin een nummer functie, ten eerste moet u downloaden Kutools for Excelen pas de functie vervolgens snel en gemakkelijk toe.
  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:
Note: Als u deze functie wilt toepassen, alstublieft download en installeer Kutools voor Excel kopen.

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:

  • Lijst of genereer alle mogelijke combinaties
  • Laten we zeggen dat ik de volgende twee kolommen met gegevens heb, en nu wil ik een lijst met alle mogelijke combinaties genereren op basis van de twee lijsten met waarden zoals het linker screenshot wordt getoond. Misschien kunt u alle combinaties een voor een opsommen als er weinig waarden zijn, maar als er meerdere kolommen zijn met meerdere waarden die nodig zijn om de mogelijke combinaties te vermelden, zijn hier enkele snelle trucs die u kunnen helpen om dit probleem in Excel op te lossen .
  • Genereer alle combinaties van 3 of meerdere kolommen
  • Stel dat ik 3 kolommen met gegevens heb, nu wil ik alle combinaties van de gegevens in deze 3 kolommen genereren of weergeven, zoals onderstaand screenshot. Beschikt u over goede methoden om deze taak in Excel op te lossen?
  • Genereer een lijst met alle mogelijke combinaties van 4 cijfers
  • In sommige gevallen moeten we mogelijk een lijst genereren van alle mogelijke combinaties van 4 cijfers van de cijfers 0 tot 9, wat betekent dat we een lijst moeten genereren van 0000, 0001, 0002… 9999. Om de lijsttaak snel in Excel op te lossen, introduceer ik enkele trucs voor je.