Wie verwende ich den Excel-Löser, um das Problem zu lösen?

1489
Darklord Achilles

Die ABC Corporation ist ein weltweiter Distributor von elektrischen Teilen und Komponenten. Das Unternehmen beauftragt den Kauf von Komponenten und Teilen aus Europa und lässt sie in drei europäischen Häfen (E1, E2, E3) an Lagerhäuser liefern. Die verschiedenen Komponenten und Teile werden je nach Bedarf von US-Kunden in Container verladen. Für jeden Hafen steht eine begrenzte Anzahl festgelegter Container pro Monat zur Verfügung. Die Container werden dann von Containerschiffen nach Übersee zu den Häfen P1, P2, P3 und P4 transportiert. Von diesen Seehäfen aus werden die Container normalerweise mit Lastwagen gekoppelt und zu Binnenhäfen in I1, I2 und I3 befördert. In jedem Hafen gibt es jeden Monat eine feste Anzahl von Spediteuren. Diese Binnenhäfen werden manchmal als „Güterdörfer“ oder intermodale Kreuzungen bezeichnet, an denen die Container gesammelt und von einem Transportmittel in ein anderes transferiert werden (z vom LKW auf die Schiene und umgekehrt). Von den Binnenhäfen aus werden die Container zu den Distributionszentren von ABC in D1, D2, D3, D4 und D5 transportiert. Im Folgenden sind die Abwicklungs- und Versandkosten ($ / Container) zwischen den Ein- und Zielpunkten entlang dieser Übersee-Lieferkette und den verfügbaren Containern in jedem Hafen aufgeführt:

Beispiel Excel-Blatt

Ein rotes Feld bedeutet, dass die bestimmte Route keine praktikable Option ist und daher nicht berücksichtigt werden kann. Bestimmen Sie die optimalen Sendungen von jedem Punkt der Einschiffung zu jedem Bestimmungsort entlang dieser Lieferkette in Übersee, was zu minimalen Gesamtkosten für den Versand und den Gesamtkosten für den Versand in jeder Phase führt.

-1
Wenn also etwas von P1 an E1 an I1 an D1 verschickt wurde, wären die Gesamtkosten 2.769 $? Sie möchten also die kostengünstigste Route für alle diese Optionen finden? Ist es eine einmalige Sache oder möchten Sie etwas komplizierter machen, z. B. wie viel Lastwagen / Züge / Boote verwendet werden, um die jeweils günstigste Option zu einem bestimmten Zeitpunkt zu finden? Engineer Toast vor 7 Jahren 0
@EngineerToast Ich möchte die Container und die Kosten im Auge behalten. Darklord Achilles vor 7 Jahren 0
Nach dem Lesen und basierend auf Ihren Kommentaren verstehe ich Folgendes: Sie möchten explizit das Solver-Add-In verwenden, um die Route zu ermitteln, die jeder Container jeden Monat benötigt, um von Europa zu einem von mehreren Zielen zu gelangen. Jede Destination möchte jeden Monat eine bestimmte Anzahl von Containern erhalten und Sie möchten die Gesamtkosten aller Sendungen minimieren, oder? Engineer Toast vor 7 Jahren 0

2 Antworten auf die Frage

0
Engineer Toast

Wenn Sie eine einmalige Antwort wünschen, E2 > P4 > I2 > D2kostet die billigste Route 1.452 $.

Die Methode, die ich verwendete, war nicht elegant. Ich machte aus jeder möglichen Option eine Tabelle, benutzte INDEXund MATCH, um die Kosten jeder Etappe der Reise nachzuschlagen und dann nach aufsteigenden Gesamtkosten zu sortieren. So sieht mein Blatt aus:

Spreadhseet

Die Daten auf der linken Seite sind die, die ich aus Ihrer Tabelle kopiert habe. Die Tabelle rechts ist das, was ich geschaffen habe. 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, kopiere für einen Satz von D, I2, kopiere für einen Satz von D usw., bis die Tabelle gefüllt ist. Für das Feld $ EP habe ich die Kosten für die Reise von E (was auch immer) nach P (was auch immer) kombiniert INDEXund MATCHnachgeschlagen.

=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 innerhalb dieses Arrays zurück. Um die Zeile zu finden, die ich wollte, habe ich herausgefunden MATCH, wo der E-Wert im Bereich liegt A3:A5. Um die Spalte zu finden, habe ich den P-Wert in gesucht B2:B4. Das gab mir die Kosten für den Umstieg von E nach P. Ich fügte den 1/(1/...)gesamten Wrapper so hinzu, dass ein Fehler zurückgegeben würde, wenn die Strecke null wäre, was darauf hinweist, dass er nicht verfügbar war.

Ich habe diese Formel dann in die Felder $ PI und $ ID kopiert und jeweils angepasst, um auf die korrekten Bereiche zu verweisen. Schließlich fügte das Feld Gesamtkosten diese drei Abschnitte einfach hinzu. Ich habe aufsteigend sortiert und die günstigste Route gefunden. Alle Fehler - diejenigen, die nicht verfügbare Routen verwenden - wurden nach unten gesendet.

Wenn Sie ein laufendes System haben möchten, in dem Sie verfolgen möchten, wie viele Transporte für jede Etappe der Route unterwegs sind, und Sie die billigste Route basierend auf der aktuell verfügbaren Route auswählen können, möchten Sie möglicherweise eine Datenbanklösung suchen. Alternativ können Sie die sortierte Liste verwenden und einfach oben beginnen und so lange nach unten arbeiten, bis Sie eine Route finden, auf der jede Etappe aktuell verfügbar ist.

haben sie die Anzahl der Behälter berücksichtigt? Darklord Achilles vor 7 Jahren 0
und eine Idee, Excel-Löser in diesem zu verwenden? Darklord Achilles vor 7 Jahren 0
@DarklordAchilles Wenn Sie möchten, dass es sich um eine laufende Operation handelt, bei der Sie die derzeit günstigste Route basierend auf der Verfügbarkeit von Containern angeben können, möchten Sie möglicherweise eine tatsächliche Datenbank anzeigen, um alle Ihre Sendungen auf dem Weg nachverfolgen zu können . Engineer Toast vor 7 Jahren 0
Sogar ich mag eine Datenbanklösung. Aber diese Aufgabe besteht darin, Solver zu lernen, und ich kann nicht finden, wie ich diese vielen Ebenen mit diesem lösen kann. Darklord Achilles vor 7 Jahren 0
0
OldUgly

Hier ist ein Beispiel für die Einrichtung Ihres Problems für Solver. Sie können es zwar kompakter machen, aber die Art und Weise, wie ich es einrichte, ist leicht, Probleme zu diagnostizieren.

  1. Verwenden Sie Ihre Originaldaten in einer Registerkarte "Daten".
  2. Erstellen Sie eine neue Registerkarte namens "Gleichung", um das Solver-Problem einzurichten.
  3. Die Spalten A bis D dienen zum Einrichten aller möglichen Kombinationen von Routen. zB Zeile 1 ist Header; Reihe 2 ist E1, P1, I1, D1; Reihe 2 ist E1, P1, I1, D2; usw.
  4. Spalte E bis G sind die Kosten, die mit Europa für den US-Hafen, den US-Hafen für den Binnenhafen und den Binnenhafen für das Vertriebszentrum verbunden sind. Dies wird mit VLOOKUP und MATCH gefüllt. zB in E2 ist =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 sind alle ausgefüllt.
  5. Spalte H ist die Gesamtkosten pro Einheit. Um nicht realisierbare Routen offensichtlich zu machen, setze ich die Gesamtkosten für diese auf 1.000.000. zB ist H2 =IF(OR(E2=0,F2=0,G2=0),1000000,SUM(E2:G2)). Das ist ausgefüllt.
  6. Spalte I ist die Anzahl der Einheiten (Container), die diese Route zurücklegen. Diese Spalte wird von Solver manipuliert. Ich habe jede Zeile mit 1 initialisiert.
  7. Spalte J gibt die Gesamtkosten für die Route an. zB J2 ist =H2*I2und ist ausgefüllt.
  8. Auf derselben Registerkarte wird eine weitere Tabelle erstellt, die die Zielfunktion und die Einschränkungen enthält. Es ist in L1 bis O19. Einige Beispiele für die Berechnungen hier sind: nUnits sind Summen aus der vorherigen Tabelle, also 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, dh die maximale Anzahl von Containern von E1 ist =VLOOKUP(L2,Data!$A$3:$F$5,6,FALSE)die maximale Anzahl von Containern von I1 =HLOOKUP(L9,Data!$B$10:$D$15,6,FALSE)usw. In ähnlicher Weise werden Bedarfseinheiten von der Registerkarte "Daten" extrahiert.
  9. Ich fügte eine zusätzliche Einschränkung hinzu, um sicherzustellen, dass keine nicht geeigneten Routen ausgewählt werden.
  10. Das Ziel sind die Gesamtkosten, die sich aus der Summe der Spalte J ergeben.

Hier ist eine Bildschirmaufnahme des Gleichungsregisters, bevor Solver ausgeführt wird. Mehrere Zeilen sind ausgeblendet.

Der Solver ist wie folgt eingerichtet:

  1. Set Objective: ist $M$19
  2. An: ist min
  3. Durch Ändern variabler Zellen: ist $I$2:$I$181
  4. Nicht festgelegte Variablen als nicht negativ definieren ist nicht ausgewählt (dies wird mit Einschränkungen behandelt)
  5. Wählen Sie eine Lösungsmethode: ist evolutionär. Evolutionary ist viel schneller als andere Methoden, wenn es um Ganzzahl-Constraints geht.
  6. Abhängig von den Einschränkungen: gibt es viele ...
    • $I$2:$I$181 = integer - muss eine ganzzahlige Anzahl von Containern haben
    • $I$2:$I$181 >= 0 - können keine negativen Kosten verursachen
    • $M$2 <= $N$2für jede Zeile dupliziert, um $M$11 <=$N$11die maximale Anzahl von Containern nicht zu verletzen
    • $M$12 = $O$12für jede Zeile dupliziert, um $M$16 = $O$16die Nachfrage zu befriedigen
    • $M$17 = $N$17 - Verwenden Sie keine nicht realisierbaren Optionen

Unten sehen Sie einen Screenshot des Dialogfelds Solver Parameters ...

Mit diesem Setup bekomme ich Gesamtkosten von 1.661.119,00 US-Dollar, wenn alle Anforderungen erfüllt sind und keine Auflagen verletzt wurden. Unten ist ein Screenshot der Einschränkungstabelle ...

Unten sehen Sie einen Screenshot der Kostentabelle, wobei alle Null-Routen herausgefiltert werden ...

konnte diese Lösung nicht überprüfen. werde diese Woche versuchen. Vielen Dank Darklord Achilles vor 7 Jahren 0

Verwandte Probleme