Massen - Lösung mit Solver | HERBERS Excel Forum

Hallo, ich habe mir für Flugplanungszwecke eine Excel Tabelle gemacht. Aus bekannten Daten kann sie errechnen: a) Welcher Wind in der gewünschten Flughöhe weht // b) Welchen Sprit - Verbrauch die Maschine (in Abhängigkeit von Aussentemperatur, Druckhöhe und Leistungseinstellung) stündlich hat // c.) Wie lange man braucht um von einem Wegpunkt (X) zum nächsten (Y) zu kommen.
Ist a-b-c erstmal bekannt, kann man aus den vielen möglichen fliegbaren Höhen diejenige auswählen, in der der Flieger am wenigsten Sprit zwischen X & Y verbraucht: Hat man extrem starken Rückenwind in niedrigen Höhen, sollte man tief fliegen, obwohl eigentlich Flugzeuge in Bodennähe viel Benzin "saufen", der Rückenwind - Effekt überwiegt die Nachteile der geringen Flughöhe. Andersherum sollte man hoch fliegen, wenn man zwar in der Höhe leichten Gegenwind hat (am Boden herrsche leichter Rückenwind), aber der Spritverbrauch in Bodennähe soviel höher wäre, dass es der leichte Rückenwind "noch nicht bringt".
Manuell ist Herumprobieren mühsam. Daher kann man dafür (incl. Nebenbedingungen, e.g. "fliege nicht tiefer als die in Spalte "MSA" angegebene Mindestsicherheitshöhe") den Solver bemühen, der das Optimum in Spalte "Spritverbrauch" berechnet. (Das ist jetzt die Version für den Umweltfreund bis hierher)
ein typischer Flugplan besteht aus etwa 10 - 30 Wegpunkten. Wollte man nun alle Streckenabschnitte einzeln optimieren, säße man 10 - 30 Minuten untätig vorm PC: Mein Rechner ist mit den 11.5 MB der betr. Excel - Tabelle derart überfordert, dass er trotz Centrino 1.4 GHz Chip und 768 MB DDRRAM pro Optimierung (sonst keine Programme offen) 60 sek. reine Rechenzeit verbraucht (!!!!)
Ich habe gerade versucht, dem Solver zu sagen, er soll in Spalte "FLUGHOEHE" gleich alles auf einmal optimieren so dass "LEGFUEL" (Streckenabschnitts - Verbräuche) JEWEILS minimal wird. Ging aber net. Der Solver nimmt immer nur eine Optimier - Zelle pro Mal.
Bediene ich da was falsch oder gibt es einen Trick, wie ich ihm sage "optimiere FLUGHOEHE in Abh. von LEGFUEL"? Also gleich die ganze Spalte mit den Einzelhöhen auf einmal? Dann könnte ich während der Rechenzeit nämlich schon was anderes sinnvolles machen und müßte nicht alle Minute zum Rechner zurückkehren um den Solver mühsam für die nächste Zeile klarzumachen.
Im Endeffekt soll nämlich nicht nur (o. erwähntes Vorhaben ist nur eine "Vorübung" für kleine Excelfans) der jeweilige (Achtung: variable Kosten!) Streckenabschnittsverbrauch optimiert werden sondern nach einer Erweiterung um die Info "Charterkosten des Flugzeugs brutto / naß / Flugstunde" auch der Gesamtkostenbetrag in Abhängigkeit von Flugtempo UND Flughöhe bei bekanntem Wind in allen Höhen.
In der Fliegerei ist es nämlich so: Wenn ich als Pilot einen Flieger bei Flugzeugverleihfirma "XYZ" miete, zahle ich lt. Vertrag einen Fix(!!)betrag incl. Steuer, Sprit, Öl, nämlich den Preis "brutto - naß / Flugstunde".
Wenn ich extrem viel Geld für die Miete zahle ist es besser, wenn ich mal richtig Gas gebe, dafür ordentlich Sprit mehr durchhaue, aber 5 min eher da bin, um 1/12 Stunde weniger Chartergebühr zu zahlen, da ich eh wieder am Startflugplatz an der Tankstelle des Vermieters nachtanke, und das ist ja "all incl." (Das ist jetzt die Version für den Nicht - Umweltfreund). Ganz besonders komplex wird die Sache wenn man eine lange Flugtour macht und außerhalb nachtanken muss. Dann zählt eine Mischung aus Fixkostenoptimierung (Schnellfliegen um wenig Miete zu zahlen) und Spritoptimierung (nicht - so - schnell - Fliegen), denn wenn ich außerhalb tanke, bekomme ich für die vorverauslagten Liter zwar einen vereinbarten auswärts - Tank - Betrag vom Verleih zurück aber der ist meist viel niedriger als das was man unterwegs am Zapfhahn blecht, also ist weder das eine noch das andere ideal, der goldene Mittelweg ist zu finden!!
auch für letzteres fehlt mir ein Lösungsansatz mittels solvers. oder ist das schon so komplex, dass es excel net mehr kann? müßte ich da richtig was programmieren?
Und um die Gesamtkosten zu optimieren, muss ja Excel erstmal JEDE Flughöhe und AUCH Fluggeschwindigkeit in JEDEM Abschnitt optimieren und dann noch schauen, ob dann nicht die Flugzeit unverhältnismäßig zu lang wird. Das muss in einem Schritt geschehen - sprich: Ich kann dann gar nicht mehr einzeln die LEGFUEL - Zeilenwerte optimieren, weil ich als Mensch diese vielen Mehrfachabhängigkeiten nicht mehr überblicken kann ob eine Veränderung in diese oder jene Richtung nicht einen negativeren Effekt auf das Gesamtbild hat ...
danke für eure hilfe im voraus. Gruß Holger Rogol

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

'Mit dem Makrorekorder unter Excel97 aufgezeichnetes Makro, das den Solver für eine Zelle _ aufruft, zurücksetzt und mit den Daten und Nebenbedingungen füllt und die Lösung berechnet. _ Die mehrfach bei Umschaltungen im Solver-Dialog aufgezeichneten identischen Zeilen hab ich gelöscht. _ Dieses Makro ist aber nicht lauffähig, es kommen Fehlermeldungen. Sub Makro2() ' Makro2 Makro ' Makro am 12.12.06 von fcs aufgezeichnet SolverReset SolverOk SetCell:="$D$2", MaxMinVal:=3, ValueOf:="0", ByChange:="$A$2" SolverAdd CellRef:="$B$2", Relation:=3, FormulaText:="$C$2" SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _ :=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _ IntTolerance:=5, Scaling:=False, Convergence:=0.001, AssumeNonNeg:=False SolverSolve End Sub ' Angepaßte Version unter Excel97, so dass der Solver die Daten übernimmt, _ die Lösung berechnet und die gefundenen Lösung automatisch übernimmt. _ Ich habe keine Ahnung, warum es mit den Variablennamen Probleme gibt. _ Ich tippe mal, dass es hier noch Übersetzungsprobleme Deutsch/Englisch gibt. _ Ob dieses Problem unter der 2003er Version auch noch besteht weiss ich nicht. Sub SolverFuettern() ' SolverFuettern Makro Application.Run "Solver.XLA!SolverReset" 'Setzt den Solver zurück 'Eingaben im Hauptfenster des Solverdialogs Application.Run "Solver.XLA!SolverOk", "$D$2", 3, "0", "$A$2" 'Eingaben für Nebenbedingungen im separaten Solver-Unterdialog Application.Run "Solver.XLA!SolverAdd", "$B$2", 3, "$C$2" 'Eingaben für Optionen im separaten Solver-Unterdialog Application.Run "Solver.XLA!SolverOptions", 100, 100, 0.000001, False, False, 1, 1, 1, 5, False, 0.001, False Application.SendKeys ("{Enter}") 'Sendet Taste Enter an den folgenden Dialog zur Übernahme der Lösung Application.Run "Solver.XLA!SolverSolve" End Sub Sub SolverFuetternSchleife() ' Solver füttern für Zellen mit ähnlichen Formeln im Zellbereich D2 bis D20, _ wobei der variable Wert jeweils in Spalte A _ und die Werte der Nebenbedingung in den Spalten B und C sind. ' Die Zellen in den Spalten A, B und C sind dabei relativ zur Zelle in Spalte D angesprochen. Dim Zelle As Range For Zeile = 2 To 20 Set Zelle = Cells(Zeile, "D") Application.Run "Solver.XLA!SolverReset" 'Setzt den Solver zurück Application.Run "Solver.XLA!SolverOk", Zelle.Address, 3, "0", Zelle.Offset(0, -3).Address Application.Run "Solver.XLA!SolverAdd", Zelle.Offset(0, -2).Address, 3, Zelle.Offset(0, -1).Address Application.Run "Solver.XLA!SolverOptions", 100, 100, 0.000001, False, False, 1, 1, 1, 5, False, 0.001, False Application.SendKeys ("{Enter}") 'Sendet Taste Enter an den folgenden Dialog zur Übernahme der Lösung Application.Run "Solver.XLA!SolverSolve" Next Zeile End Sub

Hallo Holger,
dann jetzt einmal der VBA-Crash-Curs
Schritt 1:
Öffne eine Kopie der Exceldatei mit dem Flugplan-Rechner (sicher ist sicher)
Schritt 2:
Makro-Aufzeichnen starten via Menü Exteas--Makros, dabei Makro in dieser Arbeitsmappe speichern
Schritt 3:
1. Zelle selektieren in der Solverberechnung durchgeführt werden soll.
Solver zurücksetzen (nur so ist sichergestellt, dass alle erforderlichen Eingaben aufgezeichnet werden)
Alle erfoderlichen Eingaben und Selektionen machen für den Solver inklusive Nebenbedingungen un dggf. Optionen.
Solver Lösung berechnen lassen.
Lösung übernehmen
Schritt 4:
Makroaufzeichnung beenden.
Jetzt hast du den 1. Schritt neiner Beschreibung geschafft.
Öffne mit Tastenkombination Alt+F11 oder via Menu Extras--Makro den VBA-Editor.
Doppelklicke Links auf Module und dann auf Modul1. Der aufgezeichnete Code wird jetzt angezeigt.
Markiere den gesamten aufgezeichnenten Code von Sub ... bis End Sub, kopiere ihn und füge die Kopie unterhalb von End Sub ein.
Ändere in der Kopie den Namen der Prozedur hinter Sub ... von Makro1 in z.B. SolverBerechnung.
Jetzt machst du den 2. Schritt meiner Beschreibung
D.h.
1. vorhanden doppelte code-Zeilen löschen
2. Application.Run "Solver.XLA!
am Anfang der Zeilen einfügen, die mit Solver... beginnen, hinter dem Namen ein Anführungszeichen einfügen und wenn weitere Parameter folgen ein Komma
3. Die parameter-Namen einschließlich Doppelpunkt und Gleichheitszeichen löschen.
Die Zeilen sollten danach z.B. etwa so ausschauen:
Application.Run "Solver.XLA!SolverOk", "$D$2", 3, "0", "$A$2"
4. vor der Zeile
Application.Run "Solver.XLA!SolverSolve"
die Zeile
Application.SendKeys ("{Enter}")
einfügen
Jetzt kannst du testen ob es funktioniert.
Wechsle zur Execeltabell mit dem Flugplanberechner
Speichere die DAtei
Starte via Menü Extras-Makros das soeben erstellt Makro
Wenn alles richtig gemacht wurde, dann sollte die Sanduhr angezeigt werden und nach einem Pling, Piep oder Ding-Dong die Lösung in der/den variablen Zellen eingetragen werden.
Prüfe jetzt, ob der Solver korrekt ausgefüllt wurde. Hierzu Berechnungszelle auswählen und Solver anzeigen.
Wenn alles ok ist, dann kannst du im VBA-Editor an den Aufbau der Schleife gehen, die den Solver die Berechnung der einzelnen Flugabschnitte durchführen läßt. Dabei müssen die Zelladressen (z.B. $B$2) der Daten, die sich in jeder Zeile ändern durch entsprechend variable VBA-Befehle ersetzt werden.
In meinem Beispiel habe ich als Bezugsspalte die Spalte D gewählt und die Schleife läuft von Zeile 2 bis Zeile 20. Spalte D war die Spalte mit der Berechnungsformel.


Sub SolverFuetternSchleife()
Dim Zelle As Range
For Zeile = 2 To 20
Set Zelle = Cells(Zeile, "D")

in der Dim-Zeille werden die benötigten Variablen deklariert (hier nur Zelle als Tabellenbereich.
Mit Set = wird dieser Variablen dann in de r For-Next-Schleife immer jeweils die Zelle zugewiesen in der für den Solver die Berechnungsformel steht.
Mit der Address-Methode wird dann immer variabel die zutreffende(n) Zelladressen für die Solverberechnung zugewiesen.
Mit Zelle.Offset(0, -3) werden Zellen relativ zu "Zelle" festgelegt, die Solver verwenden soll. Die 1. Zahl gibt den Zeilen-Offset an, die 2. Zahl den Spalten-Offset. Hier im Beispiel also die Zelle in der gleichen Zeile, 3 Spalten nach links relativ zur definierten Zelle.
Evtl. macht es Sinn noch eine If-Bedingung einzubauen, wenn der Solver nicht für alle Zeilen ein Lösung berechnen soll, wenn zum Beispiel nur Daten für 10 Teilstrecken eingeben sind und weitere 10 Zeilen für Teilstrecken in der Tabelle leerbleiben.
Wenn diese Anpassungen erledigt sind, dann steht wieder ein Test an.
Wenn alles reibungslos klappt, dann kannst du das makro in deine Originaldatei einbauen.
Ich hoffe das du mit diesen Infos weiterkommst.
Gruss
Franz

Beliebteste Forumthreads (12 Monate)

Massen - Lösung mit Solver

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread