HERBERS Excel-Forum - das Archiv

Thema: Zahlenreihe maximal an Endzahl annähern

Zahlenreihe maximal an Endzahl annähern
Heinerlei
Hallo Miteinander,
ich habe eine Reihe von unregelmäßigen Zahlen, die an eine Endzahl bis auf´s Minimum an Rest zusammengezogen werden sollen und um eine maximale Zusammensetzung dieser Zahlen zu erreichen.
Oder einfacher gesagt:
Excel soll sich aus der Zahlenliste die Zahlen aussuchen um im Gesamtergebnis möglichst nahe an die 1000 zu kommen.
Z.B folgende Auflistung: 158, 358, 297, 421, 97, 233, 178, 311, 181.
Endzahl: 1000.
Mir fällt gerade nicht ein wie ich es besser beschreiben soll.
Hat jemand eine Idee?
Danke und Grüße.
AW: Zahlenreihe maximal an Endzahl annähern
{Boris}
Hi,

so was kann der Solver ganz gut.

Userbild

VG, Boris
AW: Ein Vorschlag
Sulprobil
Hallo,

Probier' mal dies aus:
https://www.sulprobil.com/accounts_receivable_problem_en/

Viele Grüße,
Bernd
AW: Ein Vorschlag
Sulprobil
Wenn mit der gegebenen Toleranz keine Lösung möglich ist, gibt das Program jetzt einen entsprechenden Hinweis aus.
Hier noch eine iterative Lösung
{Boris}
Hi,

anbei eine Lösung mit Trial & Error und Protokollierung der besten Zwischenergebnisse.

Wichtig: In den Exceloptionen - Formeln - die Iteration aktivieren mit max. Iterationszahl 1.

Die gelbe Zelle ist die Steuerzelle. Wenn Du dort ne Null einträgst, geht alles auf Null. Trage eine 1 ein, um loszulesen. Einfach die Taste F9 gedrückt halten.

https://www.herber.de/bbs/user/169162.xlsx

VG, Boris
AW: Zahlenreihe maximal an Endzahl annähern
daniel
Hi
mal als Brute Force Lösung für maximal 20 Zahlen in Excel

1. Trage in Zelle A1:I9 die neun Ziffern ein.
2. Trage in die Zelle A2 diese Formel ein: =WENN(REST(QUOTIENT(ZEILE(A1);2^(SPALTE(A$1)-1));2);A$1;"---")
3. Ziehe die Formel aus A2 nach rechts bis Spalte I und nach unten soweit wie benötigt (2^Anzahl Werte, hier 512)
4. Trage in J2 die Formel ein: =Summe(A2:I2) und ziehe diese nach unten soweit wie Daten vorhanden sind
5. jetzt kannst du in der Spalte J mit dem Autofilter nach dem Wert suchen, der am nächsten an 1000 ist und nach diesem Filtern um zu sehen, welche Einzelwerte verwendet werden.

Gruß Daniel
Hier noch mit Lösung
{Boris}
Userbild

VG, Boris
AW: Zahlenreihe maximal an Endzahl annähern
Heinerlei
Bei dieser Lösung mit Solver verstehe ich noch nicht, wie ich die Tabelle aufbereiten muß, um die "Nebenbedingung" "$C$3:$C$11=Ganzzahlig" eingetragen zu bekommen. Schreibe ich "Ganzzahlig" in das Feld "Nebenbedingung" so erhalte ich die Rückmeldung "Die Nebenbedingung muss eine Zahl, ein Zellbezug oder eine Formel sein." Was muss ich da eintragen? Danke.
AW: Zahlenreihe maximal an Endzahl annähern
peter
Hallo

Nebendingung hinzufügen
Zellenbezug C3:C11 auswählen
mittleres Feld "int" auswählen
hinzufügen

Peter
AW: Zahlenreihe maximal an Endzahl annähern
Heinerlei
Ah ja, "int", logisch!
Jetzt funktioniert das mit dem Solver.
Morgen schaue ich mir die anderen Ideen an.
Danke allen.
Hier noch die Solver-Datei
{Boris}
https://www.herber.de/bbs/user/169167.xlsx

VG, Boris
AW: Hier noch die Solver-Datei
Heinerlei
Guten Morgen,
die Solver-Lösung hat den Nachteil, dass bei mir die Lösung nur mit den Beispielzahlen funktioniert.
D.H., wenn ich eine der Zahlen (z.B. die Zahl 233 in 232) ändere, dann wird leider kein Ergebnis gefunden. Hiernach soll Excel ja die Zahlenkombination finden, die der 1000 am nächsten kommt (Z.B. 999, 998 ...). Geht es mit Solver?
AW: Hier noch die Solver-Datei
{Boris}
Hi,

Du kannst noch eine zusätzliche Nebenbedingung einfügen (C12<=B1) und statt "Bis: Wert" auf "Bis: Max" ändern.
Das erste Solverergebnis wird dabei nicht immer das Beste sein - dann einfach nochmal lösen lassen (und ggfls. nochmal).

Ansonsten sieh Dir doch mal meine Datei mit der iterativen Lösung an.

Userbild

VG, Boris
AW: Hier noch die Solver-Datei
HeinerleiH
Danke, die Lösung funktioniert soweit.
Die "iterativen Lösung" habe ich mir nun auch angeschaut - siehe unten.
AW: Hier noch die Solver-Datei
HeinerleiH
Fehler von mir die "interiterativen Lösung" habe ich noch nicht, sondern die von Sulprobil.
Da ich aber keine Ahnung von Visual Basic habe, komme ich da nicht weiter.
Ändere ich z.B. den Zielwert (Amount) auf 1000, so erhalte ich die Fehlermeldung:

Visual Basic
Laufzeitfehler "13":
Typen unverträglich

Ansonsten würde diese Lösung mir auch gefallen, da Sie meiner Grundidee am nächsten ist.
AW: Hier noch die Solver-Datei
{Boris}
Hi,

da Sie meiner Grundidee am nächsten ist

...sagst Du, bevor Du die Iteration angesehen hast?

Nun denn. In beigefügter Datei hab ich die Berechnungen nochmal mit VBA automatisiert. In die hellblaue Zelle gibt man die Anzahl der Berechnungsdurchläufe ein (hab das mit 1000 vorbelegt. Wenn Du nicht sicher bist, ob es ein eindeutiges Ergebnis gibt, dann die Zahl nicht zu hoch schrauben - lieber das Makro erneut starten!). Das Makro steigt auch sofort aus, wenn der Zielwert zu 100% erreicht wurde.

https://www.herber.de/bbs/user/169219.xlsm

VG, Boris
AW: Das VBA Programm macht es richtig
Sulprobil
Bitte sei Dir bewusst, dass bei einer Änderung von 233 zu 232 in dem ersten Beispiel nur noch 999 und nicht 1000 genau erreicht werden können. Das VBA Programm kommt dann mit einer Toleranz von 1 auf dieselbe Lösung, aber mit einer Toleranz von 0 gibt es keine.