Wie findet man in Excel alle Kombinationen, die einer vorgegebenen Summe entsprechen?

Alle möglichen Kombinationen von Zahlen in einer Liste zu finden, die sich zu einer vorgegebenen Summe addieren, ist eine Herausforderung, mit der viele Excel-Nutzer konfrontiert sind – sei es für Budgetierung, Planung oder Datenanalyse.

In diesem Beispiel liegt eine Liste von Zahlen vor, und das Ziel ist es, alle Kombinationen zu finden, deren Summe genau 480 ergibt. Wie im bereitgestellten Screenshot zu sehen ist, gibt es fünf mögliche Kombinationsgruppen – darunter beispielsweise 300 + 120 + 60 oder 250 + 120 + 60 + 50. In diesem Artikel stellen wir verschiedene Methoden vor, mit denen Sie in Excel gezielt Zahlkombinationen identifizieren können, die einen vorgegebenen Gesamtwert ergeben.

Eine Kombination von Zahlen finden, deren Summe einem vorgegebenen Wert entspricht, mithilfe der Solver-Funktion

Alle Kombinationen von Zahlen erhalten, deren Summe einem vorgegebenen Wert entspricht

• Mithilfe einer benutzerdefinierten Funktion
• Mithilfe einer intelligenten Funktion – Kutools für Excel

Alle Kombinationen von Zahlen ermitteln, deren Summe innerhalb eines Bereichs liegt, mithilfe von VBA-Code

Zellkombinationen finden, deren Summe einem vorgegebenen Wert entspricht, mithilfe der Solver-Funktion

In Excel nach Zellkombinationen zu suchen, die sich zu einer bestimmten Summe addieren, mag zunächst entmutigend wirken – doch mit dem Solver-Add-In wird es kinderleicht. Wir führen Sie Schritt für Schritt durch die einfache Einrichtung des Solvers und zeigen Ihnen, wie Sie mühelos die passende Zellkombination finden. Was zunächst komplex erscheint, wird so spielend einfach und problemlos umsetzbar.

Schritt 1: Solver-Add-In aktivieren

1. Gehen Sie zu Datei > Optionen. Klicken Sie im Dialogfeld Excel-Optionen im linken Bereich auf Add-Ins und anschließend auf die Schaltfläche Weiter. Siehe Screenshot:
   
2. Anschließend wird der Add-Ins-Dialog angezeigt. Aktivieren Sie die Option Solver-Add-In und klicken Sie auf OK, um dieses Add-In erfolgreich zu installieren.
   

Schritt 2: Formel eingeben

Nach der Aktivierung des Solver-Add-Ins müssen Sie folgende Formel in die Zelle B11 eingeben:

=SUMPRODUCT(B2:B10,A2:A10)

Schritt 3: Solver konfigurieren und ausführen, um das Ergebnis zu erhalten

1. Klicken Sie auf Daten > Solver, um zum Solver-Parameter-Dialogfeld zu gelangen. Führen Sie im Dialogfeld die folgenden Schritte aus:
   • (1.) Klicken Sie auf die -Schaltfläche, um die Zelle B11auszuwählen, in der sich Ihre Formel befindet, im Abschnitt Ziel festlegen;
   • (2.) Wählen Sie dann im Abschnitt AufFesten Wertaus und geben Sie Ihren Zielwert 480wie gewünscht ein;
   • (3.) Klicken Sie im Abschnitt Durch Ändern der variablen Zellen auf die -Schaltfläche, um den Zellbereich B2:B10 auszuwählen, in dem Ihre entsprechenden Zahlen markiert sind.
   • (4.) Klicken Sie anschließend auf die Schaltfläche Hinzufügen.
   • 
2. Anschließend wird das Nebenbedingung hinzufügen-Dialogfeld angezeigt. Klicken Sie auf die Schaltfläche, um den Zellbereich B2:B10 auszuwählen, und wählen Sie bin aus der Dropdown-Liste aus. Klicken Sie abschließend auf die Schaltfläche OK . Siehe Screenshot:
   
3. Klicken Sie im Solver-Parameter-Dialogfeld auf die Schaltfläche Lösen. Nach einigen Minuten öffnet sich das Solver-Ergebnisse-Dialogfeld, und Sie sehen, dass die Kombination der Zellen, deren Summe dem vorgegebenen Wert 480 entspricht, in Spalte B mit einer „1“ markiert ist. Wählen Sie im Solver-Ergebnisse-Dialogfeld die Option Solver-Lösung verwenden aus, und klicken Sie auf OK, um das Dialogfeld zu schließen. Siehe Screenshot:
   

Alle Kombinationen von Zahlen erhalten, deren Summe einem vorgegebenen Wert entspricht

Mit den erweiterten Funktionen von Excel finden Sie jede Zahlkombination, die einer bestimmten Summe entspricht – einfacher, als Sie denken! Dieser Abschnitt stellt zwei Methoden vor, um alle Kombinationen von Zahlen zu ermitteln, deren Summe einem vorgegebenen Wert entspricht.

Alle Kombinationen von Zahlen ermitteln, deren Summe einem vorgegebenen Wert entspricht, mithilfe einer benutzerdefinierten Funktion

Um jede mögliche Kombination von Zahlen aus einer bestimmten Menge zu finden, die gemeinsam einen vorgegebenen Wert ergibt, ist die nachfolgend beschriebene benutzerdefinierte Funktion ein effektives Werkzeug.

Schritt 1: VBA-Modul-Editor öffnen und Code kopieren

1. Halten Sie in Excel die Tasten ALT + F11 gedrückt – dadurch öffnet sich das Fenster Microsoft Visual Basic for Applications.
2. Klicken Sie auf Einfügen>Modul, und fügen Sie den folgenden Code im Modulfenster ein.
   VBA-Code: Alle Kombinationen von Zahlen ermitteln, deren Summe einem vorgegebenen Wert entspricht

   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
   

Schritt 2: Benutzerdefinierte Formel eingeben, um das Ergebnis zu erhalten

Schließen Sie nach dem Einfügen des Codes das Code-Fenster, um zum Arbeitsblatt zurückzukehren. Geben Sie die folgende Formel in eine leere Zelle ein, um das Ergebnis anzuzeigen, und drücken Sie anschließend die Enter-Taste, um alle Kombinationen zu erhalten. Siehe Screenshot:

=MakeupANumber(A2:A10,B2)

Tipp: Wenn Sie die Kombinationsergebnisse vertikal in einer Spalte auflisten möchten, wenden Sie bitte die folgende Formel an:

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

Alle Kombinationen von Zahlen ermitteln, deren Summe einem vorgegebenen Wert entspricht, mithilfe einer leistungsstarken Funktion

Angesichts der Einschränkungen der oben genannten Funktion empfehlen wir eine schnelle und umfassende Lösung: die Funktion „Zahlen erstellen“ von Kutools für Excel, die mit jeder Excel-Version kompatibel ist. Diese Alternative verarbeitet positive Zahlen, Dezimalzahlen und negative Zahlen zuverlässig und liefert Ihnen im Handumdrehen alle Kombinationen, deren Summe dem vorgegebenen Wert entspricht.

1. Klicken Sie auf Kutools>Inhalt>Zahlen erstellen, siehe Screenshot:
   
2. Im Zahlen erstellen-Dialogfeld klicken Sie auf die Schaltfläche, um den gewünschten Zahlenbereich aus dem Quellbereich auszuwählen, geben anschließend die Gesamtsumme in das TextfeldSumme ein und klicken abschließend auf die SchaltflächeOK . Siehe Screenshot:
   
3. Anschließend erscheint eine Meldungsbox, die Sie auffordert, eine Zelle für die Ausgabe des Ergebnisses auszuwählen. Klicken Sie dann auf OK – siehe Screenshot:
   
4. Nun werden alle Kombinationen, deren Summe dem vorgegebenen Wert entspricht, wie im folgenden Screenshot dargestellt angezeigt:
   

Alle Kombinationen von Zahlen ermitteln, deren Summe innerhalb eines Bereichs liegt, mithilfe von VBA-Code

Gelegentlich kann es vorkommen, dass Sie alle möglichen Kombinationen von Zahlen ermitteln müssen, deren Gesamtsumme innerhalb eines bestimmten Bereichs liegt. Beispielsweise könnten Sie nach allen möglichen Gruppierungen von Zahlen suchen, deren Gesamtsumme zwischen 470 und 480 liegt.

Alle möglichen Kombinationen von Zahlen zu finden, deren Summe innerhalb eines bestimmten Bereichs liegt, ist eine faszinierende und äußerst praktische Herausforderung in Excel – und genau dafür bietet dieser Abschnitt eine elegante Lösung per VBA-Code.

Schritt 1: VBA-Modul-Editor öffnen und Code kopieren

1. Halten Sie in Excel die Tasten ALT + F11 gedrückt – und schon öffnet sich das Fenster Microsoft Visual Basic for Applications.
2. Klicken Sie auf Einfügen>Modul, und fügen Sie den folgenden Code im Modulfenster ein.
   VBA-Code: Alle Kombinationen von Zahlen ermitteln, deren Summe innerhalb eines bestimmten Bereichs liegt

   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
   
   
   

Schritt 2: Code ausführen

1. Drücken Sie nach dem Einfügen des Codes die Taste F5, um den Code auszuführen. Wählen Sie im ersten erscheinenden Dialogfeld den gewünschten Zahlenbereich aus und klicken Sie auf OK. Siehe Screenshot:
   
2. Geben Sie im zweiten Dialogfeld die Untergrenze ein oder wählen Sie sie aus, und klicken Sie auf OK. Siehe Screenshot:
   
3. Geben Sie im dritten Dialogfeld die Obergrenze ein oder wählen Sie sie aus, und klicken Sie auf OK. Siehe Screenshot:
   
4. Wählen Sie im letzten Dialogfeld eine Zelle aus, ab der die Ergebnisse eingefügt werden sollen, und klicken Sie anschließend auf OK. Siehe Screenshot:
   

Ergebnis

Nun wird jede qualifizierende Kombination in aufeinanderfolgenden Zeilen des Arbeitsblatts aufgelistet, beginnend mit der Ausgabezelle, die Sie ausgewählt haben.

Excel bietet gleich mehrere Möglichkeiten, Zahlengruppen zu finden, die eine bestimmte Summe ergeben. Da jede Methode unterschiedlich funktioniert, können Sie die passende je nach Ihrer Vertrautheit mit Excel und den Anforderungen Ihres Projekts auswählen. Wenn Sie noch mehr Excel-Tipps und -Tricks entdecken möchten,finden Sie auf unserer Website Tausende von Anleitungen. Vielen Dank fürs Lesen – wir freuen uns darauf, Ihnen auch in Zukunft hilfreiche Informationen bereitzustellen!

Verwandte Artikel: