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

Das Entdecken aller möglichen Kombinationen von Zahlen innerhalb einer Liste, die sich zu einer bestimmten Summe addieren, ist eine Herausforderung, mit der viele Excel-Benutzer konfrontiert sein könnten, sei es für Budgetierung, Planung oder Datenanalysezwecke.

In diesem Beispiel haben wir eine Liste von Zahlen, und das Ziel ist es, herauszufinden, welche Kombinationen aus dieser Liste sich zu 480 summieren. Der bereitgestellte Screenshot zeigt, dass es fünf mögliche Gruppen von Kombinationen gibt, die diese Summe erreichen, einschließlich Kombinationen wie 300+120+60, 250+120+60+50, unter anderen. In diesem Artikel werden wir verschiedene Methoden untersuchen, um die spezifischen Kombinationen von Zahlen innerhalb einer Liste zu identifizieren, die einen bestimmten Wert in Excel ergeben.

Finde eine Kombination von Zahlen, die einer bestimmten Summe mit der Solver-Funktion entspricht

Erhalte alle Kombinationen von Zahlen, die einer bestimmten Summe entsprechen

• Durch die Verwendung einer benutzerdefinierten Funktion
• Durch ein intelligentes Feature – Kutools für Excel

Erhalte alle Kombinationen von Zahlen, deren Summe in einem Bereich mit VBA-Code liegt

Finde Zellenkombinationen, die einer bestimmten Summe mit der Solver-Funktion entsprechen

Das Eintauchen in Excel, um Zellenkombinationen zu finden, die sich zu einer bestimmten Zahl addieren, mag zunächst einschüchternd erscheinen, aber das Solver-Add-In macht es zum Kinderspiel. Wir führen Sie durch die einfachen Schritte, um Solver einzurichten und die richtige Kombination von Zellen zu finden, wodurch eine scheinbar komplexe Aufgabe einfach und machbar wird.

Schritt 1: Aktivieren Sie das Solver-Add-In

1. Bitte gehen Sie zu Datei > Optionen, im Excel-Optionen Dialogfeld, klicken Sie auf Add-Ins im linken Bereich, und klicken Sie dann auf Gehe zu Knopf. Siehe Screenshot:
   
2. Dann erscheint das Add-Ins Dialogfeld, aktivieren Sie die Option Solver Add-in und klicken Sie auf OK um dieses Add-In erfolgreich zu installieren.
   

Schritt 2: Geben Sie die Formel ein

Nachdem Sie das Solver-Add-In aktiviert haben, müssen Sie diese Formel in die Zelle B11 eingeben:

=SUMPRODUCT(B2:B10,A2:A10)

Schritt 3: Konfigurieren und Ausführen von Solver, um das Ergebnis zu erhalten

1. Klicken Sie auf Daten > Solver um zum Solver-Parameter Dialogfeld zu gelangen, im Dialogfeld führen Sie bitte die folgenden Operationen aus:
   • (1.) Klicken Sie auf Knopf, um die Zelle B11 auszuwählen, wo sich Ihre Formel befindet, im Abschnitt Ziel festlegen ;
   • (2.) Wählen Sie dann im Abschnitt 'To' die Option 'Value Of' aus und geben Sie Ihren Zielwert 480 ein, wie Sie es benötigen;
   • (3.) Im Abschnitt Durch Ändern variabler Zellen klicken Sie bitte auf Knopf, um den Zellbereich B2:B10 auszuwählen, der Ihre entsprechenden Zahlen markieren wird.
   • (4.) Klicken Sie dann auf die Schaltfläche Hinzufügen.
   • 
2. Dann wird ein Einschränkung hinzufügen Dialogfeld angezeigt, klicken Sie auf Knopf, um den Zellbereich B2:B10auszuwählen, und wählen Sie bin aus der Dropdown-Liste. Klicken Sie abschließend auf OK Knopf. Siehe Screenshot:
   
3. Im Solver-Parameter Dialogfeld, klicken Sie auf die Lösen Schaltfläche, einige Minuten später erscheint ein Solver-Ergebnisse Dialogfeld, und Sie können sehen, dass die Kombinationen von Zellen, die einer bestimmten Summe von 480 entsprechen, in Spalte B als 1 markiert sind. Im Solver-Ergebnisse Dialogfeld, wählen Sie bitte die Option Solver-Lösung beibehalten und klicken Sie auf OK um das Dialogfeld zu schließen. Siehe Screenshot:
   

Erhalten Sie alle Kombinationen von Zahlen, die einer bestimmten Summe entsprechen

Die tieferen Fähigkeiten von Excel zu erkunden ermöglicht es Ihnen, jede Zahlenkombination zu finden, die einer bestimmten Summe entspricht, und es ist einfacher, als Sie vielleicht denken. Dieser Abschnitt zeigt Ihnen zwei Methoden, um alle Kombinationen von Zahlen zu finden, die einer bestimmten Summe entsprechen.

Erhalten Sie alle Kombinationen von Zahlen, die einer bestimmten Summe mit einer benutzerdefinierten Funktion entsprechen

Um jede mögliche Kombination von Zahlen aus einer bestimmten Menge zu entdecken, die zusammen einen bestimmten Wert erreichen, dient die unten beschriebene benutzerdefinierte Funktion als effektives Werkzeug.

Schritt 1: Öffnen Sie den VBA-Modul-Editor und kopieren Sie den Code

1. Halten Sie die Tasten ALT + F11 in Excel gedrückt, und es ö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: Erhalten Sie alle Kombinationen von Zahlen, die einer bestimmten Summe entsprechen

   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: Geben Sie die benutzerdefinierte Formel ein, um das Ergebnis zu erhalten

Nachdem Sie den Code eingefügt haben, schließen Sie das Codefenster, um zum Arbeitsblatt zurückzukehren. Geben Sie die folgende Formel in eine leere Zelle ein, um das Ergebnis auszugeben, und drücken Sie dann die Eingabetaste, um alle Kombinationen zu erhalten. Siehe Screenshot:

=MakeupANumber(A2:A10,B2)

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

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

Erhalten Sie alle Kombinationen von Zahlen, die einer bestimmten Summe mit einem leistungsstarken Feature entsprechen

Angesichts der Einschränkungen der oben genannten Funktion empfehlen wir eine schnelle und umfassende Lösung: Kutools für Excel's Zahlen erstellen-Feature, das mit jeder Version von Excel kompatibel ist. Diese Alternative kann positive Zahlen, Dezimalstellen und negative Zahlen effektiv verarbeiten. Mit diesem Feature können Sie schnell alle Kombinationen erhalten, die einer bestimmten Summe entsprechen.

1. Klicken Sie auf Kutools > Text > Zahlen erstellen, siehe Screenshot:
   
2. Dann, im Zahlen erstellen Dialogfeld, klicken Sie bitte auf Knopf, um die Zahlenliste auszuwählen, die Sie verwenden möchten, aus der Quellbereich, und geben Sie dann die Gesamtzahl in das Textfeld Summe ein. Klicken Sie abschließend auf OK Knopf, siehe Screenshot:
   
3. Danach wird ein Hinweisfeld angezeigt, das Sie daran erinnert, eine Zelle zur Platzierung des Ergebnisses auszuwählen, dann klicken Sie auf OK, siehe Screenshot:
   
4. Und jetzt werden alle Kombinationen, die der gegebenen Zahl entsprechen, wie im folgenden Screenshot dargestellt, angezeigt:
   

Erhalten Sie alle Kombinationen von Zahlen, deren Summe in einem Bereich mit VBA-Code liegt

Manchmal finden Sie sich in einer Situation wieder, in der Sie alle möglichen Kombinationen von Zahlen identifizieren müssen, die sich zu einer Summe innerhalb eines bestimmten Bereichs addieren. Zum Beispiel suchen Sie möglicherweise nach jeder möglichen Gruppierung von Zahlen, bei denen die Summe zwischen 470 und 480 liegt.

Das Entdecken aller möglichen Kombinationen von Zahlen, die sich zu einem Wert innerhalb eines bestimmten Bereichs summieren, stellt eine faszinierende und äußerst praktische Herausforderung in Excel dar. Dieser Abschnitt führt einen VBA-Code zur Lösung dieser Aufgabe ein.

Schritt 1: Öffnen Sie den VBA-Modul-Editor und kopieren Sie den Code

1. Halten Sie die Tasten ALT + F11 in Excel gedrückt, und es ö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: Erhalten Sie alle Kombinationen von Zahlen, die sich auf einen bestimmten Bereich summieren

   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: Führen Sie den Code aus

1. Nachdem Sie den Code eingefügt haben, drücken Sie die F5 Taste, um diesen Code auszuführen, im ersten erscheinenden Dialogfeld wählen Sie den Zahlenbereich aus, den Sie verwenden möchten, und klicken Sie auf OK. Siehe Screenshot:
   
2. Im zweiten Eingabefeld wählen oder tippen Sie die untere Grenzzahl aus und klicken Sie auf OK. Siehe Screenshot:
   
3. Im dritten Eingabefeld wählen oder tippen Sie die obere Grenzzahl aus und klicken Sie auf OK. Siehe Screenshot:
   
4. Im letzten Eingabefeld wählen Sie eine Ausgabezelle aus, ab der die Ergebnisse ausgegeben werden sollen. Dann klicken Sie auf OK. Siehe Screenshot:
   

Ergebnis

Jetzt wird jede qualifizierende Kombination in aufeinanderfolgenden Zeilen im Arbeitsblatt aufgelistet, beginnend von der von Ihnen gewählten Ausgabezelle.

Excel bietet Ihnen mehrere Möglichkeiten, Gruppen von Zahlen zu finden, die sich zu einem bestimmten Gesamtwert addieren. Jede Methode funktioniert unterschiedlich, sodass Sie eine auswählen können, basierend darauf, wie vertraut Sie mit Excel sind und was Sie für Ihr Projekt benötigen. Wenn Sie daran interessiert sind, weitere Excel-Tipps und -Tricks zu erkunden, bietet unsere Website Tausende von Tutorials. Vielen Dank fürs Lesen, und wir freuen uns darauf, Ihnen in Zukunft weiterhin hilfreiche Informationen bereitzustellen!

Verwandte Artikel: