Wie verwende ich den Excel-Solver, um dies zu lösen?

Question

ABC Corporation ist ein weltweiter Distributor von elektrischen Teilen und Komponenten. Das Unternehmen hat Verträge zum Kauf von Komponenten und Teilen aus Europa abgeschlossen und lässt diese an Lager in drei europäischen Häfen liefern: E1, E2 und E3. Die verschiedenen Komponenten und Teile werden je nach Nachfrage der US-Kunden in Container geladen. Jeder Hafen hat jeden Monat eine begrenzte Anzahl an Containern zur Verfügung. Die Container werden dann per Containerschiff zu den Häfen P1, P2, P3 und P4 verschifft. Von diesen Seehäfen aus werden die Container normalerweise auf Lastwagen aufgebockt und zu den Binnenhäfen I1, I2 und I3 transportiert. In jedem Hafen steht jeden Monat eine feste Anzahl an Frachttransportern zur Verfügung. Diese Binnenhäfen werden manchmal „Frachtdörfer“ oder intermodale Knotenpunkte genannt, wo die Container gesammelt und von einem Transportmittel auf ein anderes umgeladen werden (d. h. vom Lastwagen auf die Schiene und umgekehrt). Von den Binnenhäfen aus werden die Container zu den ABC-Verteilungszentren in D1, D2, D3, D4 und D5 transportiert. Nachfolgend sind die Bearbeitungs- und Versandkosten ($/Container) zwischen den einzelnen Einschiffungs- und Zielpunkten entlang dieser Übersee-Lieferkette sowie die in jedem Hafen verfügbaren Container aufgeführt:

Beispiel für eine Excel-Tabelle

Eine rote Zelle bedeutet, dass die jeweilige Route keine praktikable Option ist und daher nicht in Betracht gezogen werden kann. Bestimmen Sie die optimalen Sendungen von jedem Einschiffungspunkt zu jedem Ziel entlang dieser Übersee-Lieferkette, die zu minimalen Gesamtversandkosten und den Gesamtversandkosten führen, die in jeder Phase anfallen.

Answer 1

Hier ist ein Beispiel für die Einrichtung Ihres Problems für Solver. Sie können es möglicherweise kompakter gestalten, aber mit meiner Einrichtung können Sie Probleme leicht diagnostizieren.

1. Verwenden Sie Ihre Originaldaten in einer Registerkarte „Daten“.
2. Erstellen Sie eine neue Registerkarte, ich habe sie „Gleichung“ genannt, um das Solver-Problem einzurichten.
3. Die Spalten A bis D werden zum Einrichten aller möglichen Routenkombinationen verwendet. Zeile 1 enthält z. B. Überschriften, Zeile 2 E1, P1, I1, D1, Zeile 2 E1, P1, I1, D2 usw.
4. Die Spalten E bis G enthalten die Kosten für Europa zum US-Hafen, den US-Hafen zum Binnenhafen und den Binnenhafen zum Vertriebszentrum. Diese werden mithilfe von SVERWEIS und VERGLEICH ausgefüllt. Beispielsweise ist in E2 =VLOOKUP(A2,Data!$A$3:$E$5,MATCH(B2,Data!$A$2:$E$2,0),FALSE); in F2 ist =VLOOKUP(B2,Data!$A$11:$D$14,MATCH(C2,Data!$A$10:$D$10,0),FALSE); in G2 ist =VLOOKUP(C2,Data!$A$20:$F$22,MATCH(D2,Data!$A$19:$F$19,0),FALSE). Diese werden alle ausgefüllt.
5. Spalte H enthält die gesamten Stückkosten. Um nicht realisierbare Routen deutlich zu machen, habe ich die Gesamtkosten für diese auf 1.000.000 festgelegt. Beispiel: H2 ist =IF(OR(E2=0,F2=0,G2=0),1000000,SUM(E2:G2)). Dies wird ausgefüllt.
6. Spalte I ist die Anzahl der Einheiten (Container), die diese Route zurücklegen. Diese Spalte wird vom Solver bearbeitet. Ich habe jede Zeile mit 1 initialisiert.
7. Spalte J sind die Gesamtkosten für die Route. Beispiel: J2 ist =H2*I2und wird ausgefüllt.
8. Auf derselben Registerkarte wird eine weitere Tabelle erstellt, die die Zielfunktion und die Einschränkungen enthält. Sie befindet sich in L1 bis O19. Einige Beispiele für die Berechnungen hier sind: nUnits sind Summen aus der vorherigen Tabelle, also beträgt die Anzahl der Container aus E1 =SUMIFS($I$2:$I$181,$A$2:$A$181,L2), die Anzahl der Container aus P1 =SUMIFS($I$2:$I$181,$B$2:$B$181,L5), usw.; maxUnits werden aus der Registerkarte Daten extrahiert, also beträgt die maximale Anzahl an Containern aus E1 =VLOOKUP(L2,Data!$A$3:$F$5,6,FALSE), die maximale Anzahl an Containern aus I1 =HLOOKUP(L9,Data!$B$10:$D$15,6,FALSE), usw.; ebenso werden Bedarfseinheiten aus der Registerkarte Daten extrahiert.
9. Ich habe eine zusätzliche Einschränkung hinzugefügt, um sicherzustellen, dass keine nicht durchführbaren Routen ausgewählt werden.
10. Das Ziel sind die Gesamtkosten, also die Summe der Spalte J.

Hier ist ein Screenshot der Registerkarte „Gleichung“ vor dem Ausführen von Solver. Mehrere Zeilen sind ausgeblendet.

Der Solver ist wie folgt eingerichtet:

1. Ziel setzen: ist$M$19
2. An: ist Min
3. Durch Ändern variabler Zellen: ist$I$2:$I$181
4. „Uneingeschränkte Variablen als nicht negativ festlegen“ ist deaktiviert (dies wird mit Einschränkungen behandelt)
5. Wählen Sie eine Lösungsmethode: ist evolutionär. Evolutionär ist beim Umgang mit ganzzahligen Einschränkungen viel schneller als die anderen Methoden.
6. Vorbehaltlich der Einschränkungen: ist vieles...
   • $I$2:$I$181 = integer- muss eine ganzzahlige Anzahl von Containern haben
   • $I$2:$I$181 >= 0 - es dürfen keine negativen Kosten entstehen
   • $M$2 <= $N$2für jede Zeile dupliziert, um $M$11 <=$N$11die maximale Anzahl an Containern nicht zu überschreiten
   • $M$12 = $O$12für jede Zeile dupliziert, um $M$16 = $O$16die Nachfrage zu befriedigen
   • $M$17 = $N$17- keine nicht realisierbaren Optionen nutzen

Unten sehen Sie einen Screenshot des Dialogfelds „Solver-Parameter“ ...

Mit dieser Konfiguration erreiche ich Gesamtkosten von 1.661.119,00 $, wobei die gesamte Nachfrage erfüllt ist und keine Einschränkungen verletzt werden. Unten sehen Sie einen Screenshot der Einschränkungstabelle ...

Unten sehen Sie einen Screenshot der Kostentabelle, aus dem alle Nullrouten herausgefiltert wurden ...

Answer 2

Wenn Sie eine einmalige Antwort wünschen, E2 > P4 > I2 > D2kostet der günstigste Weg 1.452 $.

Die Methode, die ich verwendet habe, war nicht elegant. Ich habe eine Tabelle mit allen möglichen Optionen erstellt, die Kosten für jede Etappe der Reise mit INDEXund MATCHnachgeschlagen und dann aufsteigend nach Gesamtkosten sortiert. So sieht mein Blatt aus:

Die Daten auf der linken Seite habe ich aus Ihrer Tabelle kopiert. Die Tabelle auf der rechten Seite habe ich erstellt. Für jedes der Felder E, P, I und D habe ich sie einfach manuell in Gruppen eingegeben. D1, D2, D3, D4, D5. Kopieren Sie diese fünf und fügen Sie sie häufig ein. I1, nach unten kopieren für eine Reihe von Ds, I2, nach unten kopieren für eine Reihe von Ds usw., bis die Tabelle ausgefüllt war. Für das Feld $EP habe ich INDEXund kombiniert MATCH, um die Reisekosten von E (was auch immer) nach P (was auch immer) nachzuschlagen.

=1/(1/INDEX($B$3:$E$5,MATCH([@E],$A$3:$A$5,0),MATCH([@P],$B$2:$E$2,0)))

INDEXnimmt ein Array auf und gibt eine Zelle von irgendwo innerhalb dieses Arrays zurück. Um die gewünschte Zeile zu finden, suchte ich MATCHnach dem E-Wert im Bereich A3:A5. Um die Spalte zu finden, suchte ich nach dem P-Wert in B2:B4. Das gab mir die Kosten für den Weg von E nach P. Ich fügte 1/(1/...)dem Ganzen einen Wrapper hinzu, sodass ein Fehler zurückgegeben würde, wenn die Teilstrecke Null wäre, was bedeutet, dass sie nicht verfügbar war.

Ich habe diese Formel dann in die Felder $PI und $ID kopiert und sie jeweils so angepasst, dass sie auf die richtigen Bereiche verweisen. Schließlich habe ich im Feld „Gesamtkosten“ einfach diese drei Strecken zusammengerechnet. Ich habe aufsteigend sortiert und die günstigste Route gefunden. Alle Fehler – diejenigen, die nicht verfügbare Routen verwendeten – wurden nach unten verschoben.

---

Wenn Sie ein fortlaufendes System wünschen, mit dem Sie verfolgen können, wie viele Transportmittel derzeit für jede Etappe der Reise unterwegs sind, damit Sie basierend auf den aktuell verfügbaren Transportmöglichkeiten die günstigste Route auswählen können, sollten Sie sich eine Datenbanklösung ansehen. Alternativ können Sie die sortierte Liste verwenden und einfach oben beginnen und sich nach unten vorarbeiten, bis Sie eine Route finden, auf der jede Etappe derzeit verfügbar ist.