Finden Sie alle Kombinationen von Zahlen, die einer gegebenen Summe in Excel entsprechen

3 Möglichkeiten, alle Kombinationen aus einer Reihe von Zahlen zu finden, die zu einer bestimmten Gesamtsumme summieren.

Das Finden von Wertenkombinationen, die zu einer bestimmten Summe führen, ist ein häufiges Problem in der Datenanalyse. Zum Beispiel möchten Sie möglicherweise alle möglichen Kombinationen von Artikeln kennen, die mit einem bestimmten Budget gekauft werden können, oder alle möglichen Möglichkeiten, Ressourcen zur Erfüllung bestimmter Anforderungen zuzuweisen. In diesem Artikel werden wir untersuchen, wie Excel Solver und VBA verwendet werden, um die Aufgabe zu erfüllen. Wir werden auch einige mögliche Fallstricke und Einschränkungen jedes Ansatzes diskutieren, die bei der Behandlung großer Datensätze oder komplexer Einschränkungen auftreten können.

• Finden Sie eine Kombination von Zahlen, die einer gegebenen Summe entsprechen - Excel Solver
• Holen Sie sich alle Kombinationen, die zu einer bestimmten Gesamtzahl der benutzerdefinierten Funktion summieren
• Geben Sie alle Kombinationen zurück, die zu einem bestimmten Wert addieren - Makro

Finden Sie eine Kombination von Zahlen, die einer gegebenen Summe mit Excel Solver entsprechen

Bedauerlicherweise kann Ihnen keine eingebaute Excel -Funktionen helfen, die Zahlen zu identifizieren, die zu einer bestimmten Gesamtsumme führen. Glücklicherweise bietet Excel ein spezielles Add-In zur Lösung linearer Programmierprobleme. Das Solver-Add-In ist in allen Versionen von Excel enthalten, ist jedoch standardmäßig nicht aktiviert. Wenn Sie mit diesem Tool nicht vertraut sind, finden Sie hier einen großartigen Artikel zum Hinzufügen und Verwenden von Solver in Excel.

Fahren Sie mit dem in Ihrem Excel aktivierten Solver-Add-In die folgenden Schritte aus:

1. Erstellen Sie das Modell.

   Geben Sie dazu Ihre Zahlenmenge in eine Spalte ein (A3: A12 in unserem Fall) und fügen Sie rechts von Ihren Zahlen eine leere Spalte für die Ergebnisse hinzu (B3: B12). Geben Sie in einer separaten Zelle (B13) eine ähnliche Sumproduktformel ein, die dieser ähnelt:

   =SUMPRODUCT(A3:A12, B3:B12)

   

2. Führen Sie das Solver-Add-In aus.

   Klicken Sie auf der Registerkarte Daten in der Analysegruppe auf die Schaltfläche Löser .

   

3. Definieren Sie das Problem für Löser.

   Konfigurieren Sie im Dialogfeld Solver -Parameter die objektiven und variablen Zellen:

   • Geben Sie im Feld Zielziel die Adresse der Formelzelle (B13) ein.
   • Wählen Sie im Abschnitt den Wert des Wertes des Wertes und geben Sie den gewünschten Summenwert (50 in diesem Beispiel) ein.
   • Wählen Sie im Feld Variable Zellen den Bereich aus, der mit den Ergebnissen gefüllt ist (B3: B12).

   

4. Fügen Sie die Einschränkungen hinzu.

   Um die Einschränkungen anzugeben, klicken Sie auf die Schaltfläche Hinzufügen . Wählen Sie im Dialogfeld "Einschränkungen hinzufügen" den resultierenden Bereich (B3: B12) und wählen Sie Bin aus der Dropdown-Liste. Die Einschränkung wird automatisch auf binär eingestellt. Wenn Sie fertig sind, klicken Sie auf OK.

   

5. Das Problem lösen.

   Wenn Sie zum Dialogfenster des Löserparameters zurückgeführt werden, überprüfen Sie Ihre Einstellungen und klicken Sie auf die Schaltfläche Lösen .

   

   Einige Sekunden (oder Minuten) später wird das Dialogfeld Solver -Ergebnisse angezeigt. Wenn Sie erfolgreich sind, wählen Sie die Option LOLVER -Lösung aufbewahren und klicken Sie auf OK, um den Dialogfeld zu beenden.

   

Infolgedessen haben Sie 1 neben den Zahlen eingefügt, die zur angegebenen Summe addieren. Keine benutzerfreundliche Lösung, aber es ist das Beste, was Excel aus der Box heraus kann.

Für Visualisierungszwecke habe ich die Zellen hervorgehoben, die die gewünschte Summe in hellgrüner Summe verleihen:

Begrenzung : Excel Solver kann höchstens eine Kombination von Zahlen finden, die einer bestimmten Summe entsprechen.

Finden Sie alle Kombinationen, die einer bestimmten Summe mit benutzerdefinierter Funktion entsprechen

Um alle möglichen Kombinationen aus einer bestimmten Reihe von Zahlen zu erhalten, die zu einem bestimmten Wert addieren, können Sie die benutzerdefinierte Funktion unten verwenden. Wenn Sie bei UDFS neu sind, finden Sie in diesem Handbuch viele nützliche Informationen: So erstellen Sie benutzerdefinierte Funktionen in Excel.

Benutzerdefinierte Funktion, um alle Kombinationen zu finden, die einer bestimmten Summe entsprechen

Option Explizite öffentliche Funktionsfindsumcombinationen (RNGNumbers als Bereich, ltargetsum so lang ) dimen arnumbers () so lang , Teil () als lange dim () als String dim indi als lange dimem Zellcrem als Reichweite reduzieren (0) IF RNGNUMBERS.COUNT> 1 , dann reduzieren RNGNUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERUMBERS . arNumbers(indI) = CLng(cellCurr.Value) indI = indI 1 Next cellCurr Call SumUpRecursiveCombinations(arNumbers, lTargetSum, part(), arRes()) End If ReDim Preserve arRes(0 To UBound (arRes) - 1) FindSumCombinations = arRes End Function Private Sub SumUpRecursiveCombinations(Numbers() As Long , target As Long , part() As Long , ByRef arRes() As String ) Dim s As Long , i As Long , j As Long , num As Long , indRes As Long Dim remaining() As Long , partRec() As Long s = SumArray(part) If s = target Then indRes = UBound (arRes) ReDim Preserve arRes(0 To indRes 1) arRes(indRes) = ArrayToString(part) End If If s > target Then Exit Sub if if ( nicht nummern ) 0 , dann für i = 0 bis ubound (number) restaling () num = number (i ) für j = i 1 zu Ubound (number) addtoarray verbleibend, nummern (j) nächste j löre parrec ( ) copyArray parrec, Teil addtoarray parrec, num sumusive subcurschombing, arres -arres -arres -arres -art -subcursrivierungen ArrayToString (x () so lang ) als String dim n so lang , Ergebnis als String -Ergebnis = x (n ) für n = lBound ( x) 1 zu Ubound ( x ) Ergebnis = Ergebnis & " , " & x ( n ) nächst (x) sumarray = sumarray x (n) nächstes n ende Wenn Endfunktion privates sub addtoarray (arr ( ) so lang , x so lang ) Wenn ( nicht arr ) > 0 , erhalten Sie arr (0 bis ubound ( arr ) 1 ) sonst Redim Preserve arr ( 0 bis 0 ) . 0 dann für n = 0 bis ubound (Quelle) AddtoArray -Ziel, Quelle (n) Nächstes Ende , wenn Ende Sub beendet ist

Wie diese Funktion funktioniert

Die Hauptfunktion, Findsumcombinations , ruft einige Nebenfunktionen auf, die kleinere Unteraufgaben implementieren. Die Funktion namens Sumuprecursivecombinations führt den Kernalgorithmus aus, der alle möglichen Summen im angegebenen Bereich findet und diejenigen filtert, die das Ziel erreichen. Die ArrayToString -Funktion steuert die Form der Ausgangszeichenfolgen. Drei weitere Funktionen ( Sumarray , AddtoArray und CopyArray ) sind für die Verarbeitung von Zwischenarrays verantwortlich: Jedes Mal, wenn wir ein temporäres Array erstellen, fügen Sie ein Element aus dem Quellarray hinzu und überprüfen Sie, ob die Zielsumme erreicht ist. Der Kernalgorithmus stammt aus diesem Stackoverflow -Thread. Vielen Dank, dass Sie es geteilt haben!

Syntax

Aus Sicht des Benutzers ist die Syntax unserer benutzerdefinierten Funktion so einfach wie folgt:

Findsumcombinations (Bereich, Summe)

Wo:

• Reichweite ist der zu testende Zahlenbereich.
• Summe ist die Zielsumme.

Notiz! Die benutzerdefinierte Funktion funktioniert nur in Dynamic Array Excel 365 und 2021.

So verwenden Sie die Funktion für Findsumcombinations:

1. Fügen Sie den oben genannten Code in ein Codemodul Ihrer Arbeitsmappe ein und speichern Sie ihn als Makro-fähige Arbeitsmappe (.xlsm). Die Schritt-für-Schritt-Anweisungen finden Sie hier.
2. Geben Sie in jeder leeren Zelle eine Findsumcombinations -Formel ein und drücken Sie die Eingabetaste . Stellen Sie sicher, dass es genügend leere Zellen am Recht gibt, alle Kombinationen auszugeben, andernfalls gibt die Formel einen #Spill -Fehler zurück.

Um beispielsweise alle möglichen Kombinationen von Zahlen im Bereich A6: A15 zu finden, die der Summe in A3 entsprechen, lautet die Formel:

=FindSumCombinations(A6:A15, A3)

Wie bei jeder anderen dynamischen Array -Funktion ist die Formel nur eine Zelle (C6 im Bild unten) und die Ergebnisse populiert nach Bedarf in so viele Zellen. Standardmäßig sind die von Kommas getrennten Zeichenfolgen in einer Reihe ausgeben:

Um die Ergebnisse in einer Spalte zurückzugeben, wickeln Sie die benutzerdefinierte Funktion wie folgt in eine Transponierung ein:

=TRANSPOSE(FindSumCombinations(A6:A15, A3))

Um die Zeichenfolgen in Form eines in lockigen Klammern eingeschlossenen Arrays auszugeben, ändern Sie die ArrayToString -Funktion wie folgt:

Private function arrayToString (x () so lang ) als String dim n so lang , Ergebnis als String -Ergebnis = "{" & x (n) für n = lBound (x) 1 bis ubound (x) result = result & " ," & x ( n ) nächst

Die Ergebnisse sehen ähnlich aus:

Einschränkung : Diese benutzerdefinierte Funktion funktioniert nur in Dynamic Array Excel 365 und Excel 2021.

Holen Sie sich alle Kombinationen, die einer bestimmten Summe mit VBA -Makro entsprechen

Die oben beschriebene benutzerdefinierte Funktion gibt die Kombinationen von Zahlen als Zeichenfolgen zurück. Wenn Sie lieber jede Zahl in einer separaten Zelle haben möchten, ist dieses Makro hilfreich. Der Code wurde von einem anderen Excel -Experten Alexander Trifuntov verfasst, der den Benutzern aktiv geholfen hat, verschiedene Excel -Probleme in diesem Blog zu lösen.

Fügen Sie zunächst den folgenden Code zu Ihrer Arbeitsmappe hinzu. Die detaillierten Schritte finden Sie unter dem Einfügen von VBA -Code in Excel.

Makro, um alle Kombinationen zu finden, die zu einem bestimmten Wert addieren

Public refArray1 als String öffentliche ds als variante öffentliches targetsum so lange öffentliches targetcol wie ganzzahl public targetrow als ganzzahlige sub combination () userform1.how end sub function Graycode (Elemente als Variante ) als String -Dim -Codevection () als Ganzzahl dim i, kk, rr, col1, Row 1, n1, als Sting -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Dim -Wert ( -stingeger jedoch nicht integer. Boolean Dim OddSstep als boolean dim ss dim targetArray () als String kk = targetCol rr = targetrow col1 = targetCol 3 Row1 = targetRow OddSstep = wahrer niedrigerer = lBund (Elemente) obere = Ubound (Elemente) Zellen (RR - 1, KK) = "Ergebnis " Zellen (rr - 1, kk 1) = " kk) .font.bold = echte Zellen (rr - 1, kk 1) .font.bold = True Redim codevector ( unter obere obere) 'Es startet alle 0 bis bis zugegebener Newsub = "" Für i = unter obere obere if codevector (i) = 1 , wenn Newsub = " dann Newsub = ", & isSs SSS = SSS = SSS ISSSS (i) . SSS -Elemente (i) Ende , wenn Sie als nächstes in Newsub = "" dann Newsub = "{}" 'leerer Set Sublist = Sublist & VBCRLF & Newsub if sss = targetsum dann Zellen (RR, KK) .NumberFormat = "@" Cells (RR, KK) = "SPLACT (MIDUB, 2) & MIDUB, 2) &"@"@" TAGARRAY () = "{" & mid (newsub, 2) & "," | "," ) n1 = Ubound (targetArray) für E = 0 bis N1 -Zellen (Row1, Col1) = TargetArray (e) Row1 = Row1 1 Nächstes Col1 = col1 1 row1 = targetRow rr = rr 1 Ende , wenn SSS = 0 'Jetzt Code -Vektor aktualisieren, wenn OddStep , dann erster Bit -Codevektor . Codevector ( i) > 1 i = i 1 loop ' erledigt if i = ober

Erstellen Sie als Nächstes eine Benutzerform ( einfügen > userform ) mit den folgenden Designs und den Eigenschaften:

Fügen Sie den Code für das Formular hinzu, nachdem Sie das Design des Formulars abgeschlossen haben. Klicken Sie hierfür mit der rechten Maustaste auf das Formular im Project Explorer und wählen Sie den Code anzeigen :

Code für die Benutzerform

Private sub commandbutton1_click () dim b dim c as Integer dim d AS Ganzzahl dim a () als variante dim i als Ganzzahl dim E als Ganzzahl ds = Bereich (refedit1) targetsum = textbox1.Value -Bereich (refedit2). Wählen Sie targetCol = selection.column targetrow = selektion.row c = lbound (ds) d = Ubound (ds) Redim b (d - 1 ) für i = 1 bis d e = i - 1 b (e ) = ds (i, 1 ) Als nächstes nenn

Tipp. Anstatt das Formular von Grund auf neu zu erstellen, können Sie unser Beispiel-Arbeitsbuch am Ende dieses Beitrags herunterladen und das Codemodul und die Benutzerform1 von dort aus kopieren (siehe, wie ein Makro in ein anderes Arbeitsbuch kopiert). Kopieren Sie es in das Personal -Makro -Arbeitsbuch, um das Makro in allen Arbeitsmappen zugänglich zu machen.

Drücken Sie mit dem Code und dem Formular mit dem Code und dem Formular Alt F8 und führen Sie das Makro findAllcombinations aus:

Definieren Sie auf dem Formular, das auftaucht, Folgendes:

• Der Bereich mit den Quellzahlen (A4: A13)
• Die Zielsumme (50)
• Die obere linke Zelle des Zielbereichs (C4).

Stellen Sie bei der Angabe der oberen linken Zelle des Ausgangsbereichs sicher, dass mindestens eine leere Reihe über (für Header) und genügend leere Zellen nach unten und rechts vorhanden ist. Wenn es nicht genügend leere Zellen gibt, werden Ihre vorhandenen Daten überschrieben.

Wenn Sie auf OK klicken, werden das Ergebnis erzeugt, wie im folgenden Screenshot gezeigt:

• In C4: C6 haben Sie die Kombination von Zahlen als von Kommas getrennte Werte.
• Die Spalten F, G und H enthalten die gleichen Zahlenkombinationen, wobei jede Zahl in einer separaten Zelle liegt.
• In D4 haben Sie die Zielsumme.

Diese Form der Ausgabe erleichtert die Überprüfung des Ergebnisses. Geben Sie einfach die Summenformel in Zelle F13 ein, ziehen Sie es nach rechts über zwei weitere Zellen und Sie werden feststellen, dass jede Zahlenkombination den angegebenen Wert addiert (50).

Einschränkung: Bei einer großen Anzahl von Zahlen kann das Makro einige Zeit in Anspruch nehmen, um alle möglichen Kombinationen zu generieren.

Vorteile : Arbeiten in allen Versionen von Excel 2010 - 365; Bietet zwei Ausgangsformen - Strings von Kommas getrennten Werten und Zahlen in getrennten Zellen.

Zusammenfassend ist es ein leistungsstarkes Instrument zur Lösung eines weiten Bereichs von Datenanalyseproblemen, um alle Wertekombinationen zu finden, die einem bestimmten Wert gleichwertig sind. Hoffentlich hat dieser Artikel Ihnen einen nützlichen Ausgangspunkt für die Erforschung dieses Themas und die Behandlung ähnlicher Probleme in Ihrer eigenen Arbeit geliefert. Danke fürs Lesen!

Arbeitsbuch üben

Finden Sie Kombinationen, die einer bestimmten Summe entsprechen - Beispiele (.xlsm -Datei).

Das obige ist der detaillierte Inhalt vonFinden Sie alle Kombinationen von Zahlen, die einer gegebenen Summe in Excel entsprechen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!