Das Ermitteln aller möglichen Zahlenkombinationen in einer Liste, die sich zu einer bestimmten Summe addieren, stellt für viele Excel-Benutzer eine Herausforderung dar, sei es für Budgetierungs-, Planungs- oder Datenanalysezwecke.

In diesem Beispiel haben wir eine Liste mit Zahlen und das Ziel besteht darin, herauszufinden, welche Kombinationen aus dieser Liste zusammen 480 ergeben. 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 anderem. In diesem Artikel werden wir verschiedene Methoden untersuchen, um die spezifischen Zahlenkombinationen in einer Liste zu bestimmen, die einen bestimmten Wert in Excel ergeben.

Finden Sie mit der Solver-Funktion eine Zahlenkombination, die einer gegebenen Summe entspricht

Ermitteln Sie alle Zahlenkombinationen, die einer bestimmten Summe entsprechen

• Durch die Verwendung einer benutzerdefinierten Funktion
• Durch eine intelligente Funktion – Kutools für Excel

Rufen Sie mit VBA-Code alle Zahlenkombinationen ab, deren Summe in einem Bereich liegt

Finden Sie mit der Solver-Funktion eine Zellkombination, die einer bestimmten Summe entspricht

In Excel einzutauchen, um Zellkombinationen zu finden, die in der Summe eine bestimmte Zahl ergeben, mag entmutigend erscheinen, aber das Solver-Add-in macht es zum Kinderspiel. Wir führen Sie durch die einfachen Schritte zum Einrichten von Solver und zum Finden der richtigen Kombination von Zellen, sodass eine scheinbar komplexe Aufgabe einfach und machbar wird.

Schritt 1: Aktivieren Sie das Solver-Add-In

1. Bitte gehen Sie auf Reichen Sie das > OptionenIn der Excel-Optionen Dialogfeld, klicken Sie auf Add-Ins Klicken Sie dann im linken Bereich auf Go Taste. Siehe Screenshot:
   
2. Dann ist die Add-Ins Das Dialogfeld wird angezeigt. Überprüfen Sie das Solver-Add-In Option 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 Sie Solver und führen Sie es aus, um das Ergebnis zu erhalten

1. Klicken Sie auf Kostenlos erhalten und dann auf Installieren. Datum > Solver auf dem Sprung Solver-Parameter Dialogfeld Führen Sie im Dialogfeld die folgenden Vorgänge aus:
   • (1.) Klicken Sie auf Schaltfläche, um die Zelle auszuwählen B11 wo sich Ihre Formel befindet Ziel setzen Sektion;
   • (2.) Dann in der Zu Abschnitt auswählen Wert vonund geben Sie Ihren Zielwert ein 480 wie du es brauchst;
   • (3.) Unter dem Durch Ändern variabler Zellen Abschnitt, bitte klicken Klicken Sie auf die Schaltfläche, um den Zellbereich auszuwählen B2: B10 wo werden Ihre entsprechenden Nummern markiert.
   • (4.) Klicken Sie dann auf Speichern .
   • 
2. Dann ein Einschränkung hinzufügen Das Dialogfeld wird angezeigt. Klicken Sie auf Klicken Sie auf die Schaltfläche, um den Zellbereich auszuwählen B2: B10, und wählen Sie Kasten aus der Dropdown-Liste. Zum Schluss klicken Sie OK Taste. Siehe Screenshot:
   
3. Im Solver-Parameter Klicken Sie auf das Dialogfeld Lösen Knopf, einige Minuten später, a Solver-Ergebnisse Das Dialogfeld wird ausgeblendet und Sie können sehen, dass die Kombination von Zellen, die einer bestimmten Summe 480 entsprechen, in Spalte B als 1 markiert ist. In der Solver-Ergebnisse Dialog, bitte auswählen Behalten Sie die Solver-Lösung Option und klicken Sie auf OK um den Dialog zu verlassen. Siehe Screenshot:
   

Ermitteln Sie alle Zahlenkombinationen, die einer bestimmten Summe entsprechen

Wenn Sie die umfassenderen Funktionen von Excel erkunden, können Sie jede Zahlenkombination finden, die einer bestimmten Summe entspricht, und das ist einfacher, als Sie vielleicht denken. Dieser Abschnitt zeigt Ihnen zwei Methoden zum Finden aller Zahlenkombinationen, die einer bestimmten Summe entsprechen.

Ermitteln Sie mit der benutzerdefinierten Funktion alle Zahlenkombinationen, die einer bestimmten Summe entsprechen

Um alle möglichen Zahlenkombinationen aus einer bestimmten Menge aufzudecken, 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 ALT + F11 Tasten in Excel, und es öffnet sich die Microsoft Visual Basic für Applikationen Fenster.
2. Klicken Sie auf Kostenlos erhalten und dann auf Installieren. Insert > Modulund fügen Sie den folgenden Code in das Modulfenster ein.
   VBA-Code: Alle Zahlenkombinationen abrufen, 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

Schließen Sie nach dem Einfügen des Codes 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 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))

Erhalten Sie mit einer leistungsstarken Funktion alle Zahlenkombinationen, die einer bestimmten Summe entsprechen

Angesichts der Einschränkungen der oben genannten Funktion empfehlen wir eine schnelle und umfassende Lösung: die Funktion „Eine Zahl bilden“ von Kutools for Excel, die mit jeder Excel-Version kompatibel ist. Diese Alternative kann effektiv positive Zahlen, Dezimalzahlen und negative Zahlen verarbeiten. Mit dieser Funktion können Sie schnell alle Kombinationen erhalten, die einer bestimmten Summe entsprechen.

1. Klicken Sie auf Kostenlos erhalten und dann auf Installieren. Kutoolen > Inhalt > Erstelle eine Nummer, siehe Screenshot:
   
2. Dann, in der Erstelle eine Nummer Dialogfeld klicken Sie bitte Klicken Sie auf die Schaltfläche, um die Nummernliste auszuwählen, die Sie verwenden möchten Datenquelle, und geben Sie dann die Gesamtzahl in das Feld ein Sum Textfeld. Abschließend klicken OK Schaltfläche, siehe Screenshot:
   
3. Anschließend erscheint ein Eingabeaufforderungsfeld, das Sie daran erinnert, eine Zelle auszuwählen, um das Ergebnis zu finden, und dann zu klicken OK, siehe Screenshot:
   
4. Und jetzt wurden alle Kombinationen, die dieser angegebenen Zahl entsprechen, wie im folgenden Screenshot gezeigt angezeigt:
   

Rufen Sie mit VBA-Code alle Zahlenkombinationen ab, deren Summe in einem Bereich liegt

Manchmal befinden Sie sich möglicherweise in einer Situation, in der Sie alle möglichen Zahlenkombinationen identifizieren müssen, die zusammen eine Summe innerhalb eines bestimmten Bereichs ergeben. Beispielsweise könnten Sie versuchen, alle möglichen Zahlengruppen zu finden, deren Gesamtsumme zwischen 470 und 480 liegt.

Das Entdecken aller möglichen Zahlenkombinationen, die in der Summe einen Wert innerhalb eines bestimmten Bereichs ergeben, stellt in Excel eine faszinierende und äußerst praktische Herausforderung dar. In diesem Abschnitt wird ein VBA-Code zur Lösung dieser Aufgabe vorgestellt.

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

1. Halten Sie die ALT + F11 Tasten in Excel, und es öffnet sich die Microsoft Visual Basic für Applikationen Fenster.
2. Klicken Sie auf Kostenlos erhalten und dann auf Installieren. Insert > Modulund fügen Sie den folgenden Code in das Modulfenster ein.
   VBA-Code: Rufen Sie alle Zahlenkombinationen ab, die in der Summe einen bestimmten Bereich ergeben

   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. Drücken Sie nach dem Einfügen des Codes F5 Drücken Sie die Taste, um diesen Code auszuführen. Wählen Sie im ersten angezeigten Dialogfeld den Zahlenbereich aus, den Sie verwenden möchten, und klicken Sie auf OK. Siehe Screenshot:
   
2. Wählen Sie im zweiten Eingabeaufforderungsfeld die untere Grenzwertzahl aus oder geben Sie sie ein und klicken Sie auf OK. Siehe Screenshot:
   
3. Wählen Sie im dritten Eingabeaufforderungsfeld die Höchstzahl aus oder geben Sie sie ein und klicken Sie auf OK. Siehe Screenshot:
   
4. Wählen Sie im letzten Eingabeaufforderungsfeld eine Ausgabezelle aus, in der die Ausgabe der Ergebnisse beginnen soll. Dann klick OK. Siehe Screenshot:
   

Ergebnis

Jetzt wird jede qualifizierende Kombination in aufeinanderfolgenden Zeilen im Arbeitsblatt aufgeführt, beginnend mit der von Ihnen ausgewählten Ausgabezelle.

Excel bietet Ihnen mehrere Möglichkeiten, Zahlengruppen zu finden, die sich zu einer bestimmten Summe addieren. Jede Methode funktioniert anders, sodass Sie eine davon auswählen können, je nachdem, wie vertraut Sie mit Excel sind und was Sie für Ihr Projekt benötigen. Wenn Sie weitere Excel-Tipps und -Tricks erfahren möchten, finden Sie auf unserer Website Tausende von Tutorials Klicken Sie hier, um darauf zuzugreifen. Vielen Dank fürs Lesen und wir freuen uns darauf, Ihnen in Zukunft weitere hilfreiche Informationen zur Verfügung zu stellen!

Weitere Artikel: