Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Mehrfachoperation

Beitrag: Lineare Optimierung mit Mehrfachoperation

Aufgabe
Beispiel, wie man mit dem Excel-Tool MEHRFACHOPERATION eine Optimierungsaufgabe lösen kann.

       A               B               C               D               E               F               G               H               I       
1     K1  K2  K3  K4  K5  K6  Fix  
2   S1  10  18  11  14  
3   S2  13  15  7  
4   S3  13  14  19  
5   S4  17  19  14  10  9  
6   S5  18  19  18  15  
7   S6  20  13  11  13  11 

K1 - K6 sind Kunden, die beliefert werden müssen. Jeder Kunde muß beliefert werden.

S1 - S6 sind potentielle Produktionsstandorte, die dafür errichtet werden können. Es können 1,2,3,4,5 oder alle 6 Standorte errichtet werden.

Die Zahlen sind Kosten.
Wenn z.B. Kunde1 von Standort1 beliefert wird, entstehen Kosten von 7

Für jeden Standort, der errichtet wird, entstehen Fixkosten, die in Spalte I (Fix) stehen.
Liefert ein Standort nichts, wird er gar nicht erst errichtet, dann fallen auch keine Fixkosten an.

Welche potentiellen Standorte müssen errichtet werden, um die niedrigsten Kosten zu erhalten (Jeder Kunde muß von einem Standort beliefert werden) und von welchem Standort wird dann jeder Kunde beliefert?




Lösung
Wie viele Möglichkeiten gibt es überhaupt?

Ein Standort wird entweder eingerichtet oder nicht.
Dies sind zwei Zustände WAHR - FALSCH oder 1 - 0.
Bei sechs Standorten gibt es sechs Zustände, z.B. wird bei 000001 nur der sechste Standort eingerichtet, bei 111111 werden alle Standorte eingerichtet.
Im Binärzahlensystem gibt es von 000001 (1) bis 111111 (63) genau 63 verschiedene Möglichkeiten, allgemein 2^n-1.

Schreibe in K1 '000001 (nicht wie im Bild 0101000)

Schreibe in A2
=TEIL($K$1;ZEILE()-1;1)*1
und kopiere das bis A7

Schreibe in C8
{=MIN(WENN($A$2:$A$7=1;C2:C7))}
und kopiere das bis H8

In I8=SUMMEWENN(A2:A7;1;I2:I7)

In L1
=SUMME(C8:I8) (dort steht nun die Lösung, wenn nur S6 errichtet wird, also 78)

dann schreibe in K2:K64 die Binärzahlen von 1 bis 63 (2^6-1), sechsstellig mit führenden Nullen.
Dazu nutze etwa die Funktion =TEXT(DEZINBIN(M2);"000000")

Dann selektiere K1:L64 und wähle
Daten>Mehrfachoperation (in xl2000 Daten>Tabelle)
Werte aus Spalte:K1.

In L2 bis L64 muß nun {=MEHRFACHOPERATION(;K1)} stehen, das sind die Ergebnisse aller Lösungsalternativen.
(MEHRFACHOPERATION nicht eingeben, sondern wie beschrieben einfügen.)

Das Minimum ist nun =Min(L:L) und die dazu gehörige Alternative ist
=INDEX(K:K;VERGLEICH(MIN(L:L);L:L;0))
Dieses schreibt man dann in K1 und erhält die gewünschten Antworten:

Es müssen lediglich die Standorte S2 und S4 errichtet werden, wobei S4 Kunde 1 und Kunde 4 beliefert und S2 den Rest.

Erläuterung
Die Funktion MEHRFACHOPERATION ist eine alte, wenig genutzte und schlecht dokumentierte und wenig verstandene Funktion.
Die hier von Jens vorgestellte Anwendungsmöglichkeit könnte ein Erwachen aus dem Dornröschenschlaf von MEHRFACHOPERATION bedeuten und hat den Rest von uns Dreien schlichtweg überrollt.

Der Clou ist, daß sich MEHRFACHOPERATION seine Eingabeparameter auf indirektem Wege selbst variiert.