Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1976to1980
1976to1980
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Zahlenreihe maximal an Endzahl annähern

Forumthread: Zahlenreihe maximal an Endzahl annähern

Zahlenreihe maximal an Endzahl annähern
30.04.2024 12:27:48
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.
Anzeige

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zahlenreihe maximal an Endzahl annähern
30.04.2024 12:48:29
{Boris}
Hi,

so was kann der Solver ganz gut.

Userbild

VG, Boris
AW: Ein Vorschlag
02.05.2024 21:39:52
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
30.04.2024 13:39:54
{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
Anzeige
AW: Zahlenreihe maximal an Endzahl annähern
30.04.2024 13:47:02
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
Anzeige
Hier noch mit Lösung
30.04.2024 12:50:46
{Boris}
Userbild

VG, Boris
AW: Zahlenreihe maximal an Endzahl annähern
30.04.2024 14:44:37
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.
Anzeige
AW: Zahlenreihe maximal an Endzahl annähern
30.04.2024 14:52:30
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
30.04.2024 15:37:27
Heinerlei
Ah ja, "int", logisch!
Jetzt funktioniert das mit dem Solver.
Morgen schaue ich mir die anderen Ideen an.
Danke allen.
AW: Hier noch die Solver-Datei
01.05.2024 10:59:18
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?
Anzeige
AW: Hier noch die Solver-Datei
01.05.2024 12:49:35
{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
Anzeige
AW: Hier noch die Solver-Datei
02.05.2024 12:43:54
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
02.05.2024 12:49:30
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.
Anzeige
AW: Hier noch die Solver-Datei
02.05.2024 13:53:59
{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
Anzeige
AW: Das VBA Programm macht es richtig
03.05.2024 01:07:26
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.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige