Institut für angewandte
Optimierung

IfaO GmbH

Pré-Rond 8
CH-2022 Bevaix
Tel:  +41 32 846 17 83
Fax: +41 32 846 18 59

 

Optimieren statt Probieren...

Richtig entscheiden ist einfach ... ?

Lange Zeit konnte man die Definition von Unternehmenszielen und die Durchführung der zu ihrer Erreichung notwendigen Massnahmen mit dem Schiessen auf ein stehendes Ziel vergleichen. Die eingesetzten Mittel Kapital, Material, Personal, Know How und Kundentreue standen stellvertretend für die ruhige Hand des Schützen, die verwendete Waffe, die Munition und einen ruhigen Schiesswart. Das war, wenn auch nicht immer, noch relativ einfach.

Read More

 

Besten Dank für Ihr Interesse an einer intelligenten Lösung für Stauraumoptimierung, Laderaumoptimierung oder Zuschnittoptimierung.

Wir bereiten neue Lösungen für das Jahr 2019 vor und informieren Sie gerne. Bitte kontaktieren Sie uns.


per Telefon: +41 32 846 17 83

oder

Kontaktformular

 

Wir rufen Sie schnellstens zurück.
Bitte zögern Sie nicht! Sie werden sehen, es lohnt sich!

 

Ihr
Institut für angewandte Optimierung

Die Suche zweier 'Unbekannter', die sich gleich sind

Wir suchen eine Zahl. Wenn man von dieser Zahl 5 abzieht, muss das gleiche Ergebnis herauskommen, als ob man diese Zahl durch 5 geteilt hätte. Die Ergebnis muss eine positive Zahl sein.

Der Solver ist ein hervorragendes Instrument zur Lösung einer solchen Aufgabe. Wir geben zuerst das Problem in Excel ein:

Eingabe des Problems in Excel

Die Zellen A1 und A2 sollen die beiden Zahlen enthalten, die wir suchen. Da wir sie noch nicht kennen, geben wir '0' ein. B1 enthält den Wert 5, durch den wir den Wert in A1 teilen wollen. Die entsprechende Formel befindet sich in C1.

B2 enthält ebenfalls den Wert 5, der von dem Wert in A2 abgezogen werden soll. Die entsprechende Formel befindet sich in C2.

Wenn die Lösung richtig ist, müssen A1 und A2 gleich sein. Um dies prüfen zu können, geben wir in A3 die Formel A1 geteilt durch A2 ein. Sind beide Zahlen gleich, muss das Ergebnis 1 sein. Entsprechend gilt das auch für C3. Der Wert in C3 muss ebenfalls 1 sein, wenn C1 und C2 gleich sind, oder anders ausgedrückt, wenn A1 geteilt duch B1 gleich A2 minus B2 ist.

Jetzt aber aufgepasst! Hier ist eine Falle eingebaut, die verhindert, dass der Solver überhaupt anfängt zu rechnen:

 

Die Formel, die durch Null teilen will

Wenn in einem Problem, das mit dem Solver gelöst werden soll, eine Division durch 0 enthalten ist, wie es bei unserem Modell in der Zelle A3 der Fall ist, gibt der Solver eine Fehlermeldung aus und startet nicht. Hinzu kommt, dass das Ergebnis eine positive Zahl sein muss. Würden wir z. B. in A1 und A2. jeweils eine 1 eingeben, hätten wir zwar das Problem #DIV/0 gelöst, der Wert in C2 wäre jedoch negativ.

 Wollen wir, dass die Ergebnisse positiv sind, müssen die Werte in A1 und A2 also grösser als 5 sein.

Dieses Problem können wir durch die Eingabe von Nebenbedingungen im Solver lösen:

 

Die Bedingungen im Solver

Zuserst müssen wir jedoch die Zielzelle definieren. Unsere Zielzelle, also die Zelle, die das durch den Solver zu erechnende Ergebnis enthalten soll, ist die Zelle C3. Sie soll den Wert 1 enthalten. Damit ist sichergestellt, dass die Ergebnisse in C1 und C2 gleich sind. Damit wir ein positives Ergebnis erhalten, müssen die Werte in A1 und A2 gr�sser 5 sein. Das geben wir als Nebenbedingung ein. Würden wir nur 5 eingeben, wäre das Ergebnis der Berechnung 5 minus 5 in der Zelle C2 gleich 0 und in C3 würden wir auch eine Fehlermeldung #DIV/0 enthalten.

Als zweite Nebenbedingung, die der Solver bei der Berechnung beachten muss, geben wir ein, dass der Wert der Zelle A3 gleich 1 sein muss. In den Zellen A1 und A2 soll jeweils die gesuchte Zahl stehen. Hier darf der Solver Veränderungen vornehmen. Durch Anklicken von 'Lösen' berechnen wir das Ergebnis und der Solver präsentiert:

Die optimale Lösung

Und das Ergebnis stimmt: 6.25 geteilt durch 5 ist 1.25, genauso wie 6.25 minus 5 gleich 1.25 ist.

Wenn Sie Fragen zur praktischen Anwendung des Solvers oder der Zielwertsuche haben, helfen wir Ihnen gerne KOSTENLOS weiter. Nehmen Sie Kontakt mit uns auf und klicken Sie dazu bitte hier.

Die optimierten Kabelkosten

Die Kopfnuss für Verlegekosten

Ein Städtchen liegt an einem Fluss, der einen Kilometer breit ist. Der Gemeinderat beschliesst, auf dem zwei Kilometer flussabwärts auf der anderen Fluss-Seite gelegenen Sportgelände eine Flutlichtanlage zu installieren. Die Umspannstation, von der aus das notwendige Starkstrom-Kabel durch den Fluss und bis zum Sportgelände gezogen werden soll, liegt in der Stadt direkt am Fluss.

Die Kabelverlegung auf dem Lande kostet 1'100 € pro Meter, diejenige unter Wasser 1'300 € pro Meter. Der Stadtrat beauftragt seinen Kämmerer, eine kostenminimale Verlegungsvariante zu erarbeiten.

Der Kämmerer ist pfiffig und sucht verständlicherweise die kürzeste Strecke, indem er den alten Pythagoras zu Rate zieht:
a2 + b2 = c2 und daraus die Quadratwurzel ergibt die kürzeste Strecke. Stolz legt er dem Stadtrat bei der nächsten Sitzung folgende Rechnung vor:

"1 km x 1 km + 2 km x 2 km = 5 km und daraus die Qadratwurzel ergibt 2,2361 km. Das Kabel wird also nur im Wasser verlegt und kostet somit 2'906'930 €."

Ein Mitglied des Stadtrates behauptet jedoch, es gäbe eine kostengünstigere Lösung, die er mit dem Solver in Excel gefunden habe.

Hat er recht und wenn ja, wie sieht diese Lösung aus?

Das Mitglied des Stadtrates hat recht. Er hat eine Skizze gemacht, damit auch jeder sich von seiner Genialität überzeugen kann.

Die Zeichnung

Mit x hat er die Strecke bezeichnet, bei der auf Land kein Kabel verlegt wird. Dann ist die Strecke S1, auf der auf Land das Kabel verlegt wird 2000 - x. Nun lässt sich mit Hilfe von Pythagoras die Strecke S2 berechnen, die unter Wasser liegt, und zwar wie folgt:

S2 = Quadratwurzel(x2 + 10002) (zur Erinnerung: 1000 ist die Breite des Flusses in m)

Die insgesamt zu verlegende Kabelstrecke S beträgt also S1 + S2
S = (2000 - x) + Quadratwurzel(x2 + 10002)

Die Strecken S1 und S2 sind mit den jeweiligen Verlegekosten zu multiplizieren, um die Gesamtkosten K zu erhalten:

K = (2000 - x)*1100 + ( Quadratwurzel(x2 + 10002))*1300.

Die Gesamtkosten K sind zu minimieren, und zwar mit dem in Excel enthaltenen Optimierungstool Solver.

Dies sieht in Excel so aus:

 

A

B

1

Kosten Wasserverlegung pro Meter

1300

2

Kosten Landverlegung pro Meter

1100

3

Gesuchte Strecke x Land ohne Kabel

0

4

Zielzelle Gesamtkosten K

=B1*(WURZEL(1000^2+B3^2))+B2*(2000-B3)

5

2000-x (Landstrecke S1)

=2000-B3

6

Wasserstrecke S2

=WURZEL(1000^2+B3^2)

7

Kosten Land

=B5*B2

8

Kosten Wasser

=B6*B1

 Ausgefüllt mit Zahlen sieht das Werk jetzt so aus:

 

A

B

1

Kosten Wasserverlegung pro Meter

1300.00

2

Kosten Landverlegung pro Meter

1100.00

3

Gesuchte Strecke x Land ohne Kabel

0.0000000

4

Zielzelle Gesamtkosten K

3500000.00

5

2000-x (Landstrecke S1)

2000.0000

6

Wasserstrecke S2

1000.0000

7

Kosten Land

2200000.00

8

Kosten Wasser

1300000.00

Damit der Solver rechnen kann, benötigt er folgende Angaben:

Die Zielzelle, in der er das Ergebnis berechnet. In unserem Fall ist das die Zelle B4, deren Wert zu minimieren ist, denn der Stadtrat möchte eine kostenminimale Lösung haben. Der Wert in dieser Zelle ergibt sich aus der dahinterliegenden Formel und den bereits eingegebenen Basisdaten.
Auserdem muss der Solver wissen, welche Zelle er verändern darf, um auf das gesuchte Ergebnis zu kommen. Das ist in unserem Falle die Zelle B3.
Zusätzlich muss die Nebenbedingung 'Gesuchte Strecke >= 0' eingegeben werden, da der Solver sonst mit negativen Werten arbeiten könnte.

In den Solver eingegeben sieht das so aus:
solver

Die Zelle B3 wurde in Excel mit einem Namen versehen (Ges_Strecke = gesuchte Strecke).

Jetzt braucht nur noch der Knopf Lösen angeklickt zu werden und in wenigen Sekunden präsentiert der Solver das gesuchte Ergebnis:

Kosten Wasserverlegung pro Meter

1'300.00

Kosten Landverlegung pro Meter

1'100.00

Gesuchte Strecke x Land ohne Kabel

1'587.71

Zielzelle Gesamtkosten K

2'892'820.32

2000-x (Landstrecke S1)

412.29

Wasserstrecke S2

1'876.39

Kosten Land

453'515.44

Kosten Wasser

2'439'304.88

Kosten-/ Nutzensbetrachtung

Auf 'hochnotpeinliches' Befragen durch den Stadtdirektor gibt der clevere Stadratrat zu, dass er sich bei einem guten Bekannten am Institut für angewandte Optimierung Rat geholt hat, und dass das ganze Modell für 500 € zu haben war. Der Nutzen seiner Variante (immerhin 14'106,68 €) überstieg seine Ausgaben um das stolze Achtundzwanzigfache. Der Stadtdirektor gratuliert im Namen des gesamten Stadtrates.

Wenn Sie sich die Arbeit des Eingebens sparen wollen, können Sie sich die Datei Fluss.exe (selbstentpackend, 24 k) herunterladen.

Wenn Sie Fragen zur praktischen Anwendung des Solvers haben, helfen wir Ihnen gerne KOSTENLOS weiter. Nehmen Sie Kontakt mit uns auf und klicken Sie dazu bitte hier.