Kreisverfahren mit Excel-Unterstützung

Im Artikel Layoutoptimierung mit dem Kreisverfahren nach Schwerdtfeger habe ich die einfache und wirkungsvolle Methode vorgestellt. Erkennbar wurde auch die Problematik des Verfahrens: die Vorbereitungen sind ziemlich zeitaufwändig. Wie man das lösen kann, erfahren Sie in diesem Blog.

Normalerweise findet man für so eine Aufgabe nicht so schnell einen Freiwilligen, um den Kreis mit allen Beziehungen aufzubauen. Zumal das meistens mit spezieller Software, wie CAD-Systemen oder Microsoft Visio, passiert.

Das war mir irgendwie zu kompliziert. Die besten Ergebnisse sind für mich die, die man selber komplett verstanden hat. Und das geht am besten, wenn man es selber macht.

Die Basisdaten müssen immer aus dem ERP-System bereitgestellt werden – das ist klar – aber der Anordnungsvorgang mit der Linienbildung bleibt als aufwendiger Teil immer übrig.

Damit war für mich klar, wie man dieses Problem verbessern kann.

Ein Tool muss her…

Für mich ist eine Lösung über ein allgemeines Tool wichtig. Ich möchte mich nicht mit der Bedienung einer Spezialsoftware auseinander setzen. Bei der Anordnungsoptimierung möchte ich meine Energie in die Optimierung stecken und nicht in die Bedienung einer Software.

Meine Wahl fällt damit auf Microsoft Excel wichtig. Es steht für die meisten Benutzer zur Verfügung, die Grundbedienung ist klar und es kann sehr einfach erweitert werden.

Ein weiterer Punkt für Excel ist die Möglichkeit, sehr einfach Daten bereitzustellen, um diese für Berechnungen zu verwenden.

Dieser Punkt ist für das Ausgangskonstrukt der Anordnungsoptimierung sehr wichtig – der Transportmatrix.

Die Datengrundlage ist ein zwei Tabellen aufgeteilt: Abteilungen und Transportinformationen (Fluss).

Daten_Abteilungen
Hier stehen die Abteilungen, die entlang des Kreisumfangs angeordnet werden. Der Name muss eindeutig sein – jeder darf nur einmal vorkommen.

Datenauszug für Abteilungen mit Start- und Ende-Relevanz

Daten_Fluss
In dieser Tabelle stehen nur die einzelnen Transportbeziehungen. Jede Verbindung zwischen Abteilungen wird mit der gewünschten Transportbeziehung eingetragen. Im Beispiel ist das der Jahresbedarf. Auch hier muss die Kombination „Von“ – „Nach“ eindeutig sein.

Datenauszug für VON-NACH-Beziehungen von Abteilungen mit Jahresbedarfen

Da die Aufbereitung jetzt automatisch erfolgt, wollte ich das Verfahren mit weiteren visuellen Informationen ausbauen.

Die Visualisierung

Für Abteilungen habe ich zusätzlich zwei weitere Visualisierungen mit eingebaut: Prozess-Start und -Ende Informationen.

Je nachdem wie groß die Anzahl von Abteilungen ist, ist nicht immer sofort ersichtlich, wo Prozesse beginnen und wo sie enden. Das ist aber für den Planer entscheidend. Er muss die nächsten anknüpfenden Prozesse, wie beispielsweise Versand oder einen Weitertransport, mit berücksichtigen.

Um diese Lücke zu schließen, blendet das Tool diese Information gleich mit ein. Die Information selbst ist mit in der Tabelle Daten_Abteilungen hinterlegt. In meinem Fall ist das ein Prozentwert. Das klingt auf den ersten Blick vielleicht nicht logisch, macht aber durchaus Sinn.

Nicht jede Abteilung, bei der ein Prozess endet, hat nur genau diese Aufgabe. Sie kann nicht nur eine „Prozess-Ende-Abteilung“ sein. Es gibt immer die Möglichkeit, dass eine Abteilung bei anderen Prozessschritten, normal mitten im Ablauf, integriert ist. An ihr enden also nur bestimmte Prozesse und andere nicht. Damit macht eine Prozentangabe durchaus Sinn. Sie sagt aus, wieviele Elemente eines Prozesses bei der entsprechenden Abteilung enden. Bei einfachen Abläufen kann das z. B. immer 100% sein.

Das gleiche gilt für den Start eines Prozesses. Die Information ist für den Planer genauso interessant. Auch hier müssen die Vorgänger-Prozesse bei einer Optimierung berücksichtigt werden.

Um das für den Planer anzuzeigen, verwende ich im Tool die Eigenschaften des Abteilungs-Rechtecks.

Start-Abteilung: Füllbereich
Je dunkelgrau, desto „mehr“ Start-Abteilung
Ende-Abteilung: Rahmen
Je dicker, desto „mehr“ Ende-Abteilung

Bei den Transportbeziehungen entscheidet die Häufigkeit in der Spalte Jahresbedarf über das Aussehen der Verbindungspfeile zwischen den Abteilungen. Die Pfeildicke wird an den Jahresbedarf oder eine andere Transportbeziehung angepasst.

Je dicker, desto mehr Transportbeziehung
zusätzlich eine Farbinformation, die über Anteile im Tool einstellbar ist
- rot : Hauptmaterialflüsse (z. B. 40%)
- gelb: normale Materialflüsse (z. B. 40%)
- grau: Nebenmaterialflüsse (z. B. 20%)

Wie funktioniert das Ganze?

Excel bietet die Möglichkeit Formen mit Pfeilen zu verbinden, das sogenannte Docking. Genau das macht sich der entwickelte Excel Makro zu eigen.

Im Makro passiert das über die ConnectorFormat-Eigenschaft des Rechteckshapes der Abteilung:

 If ShapeVorhanden("R_" & Von) = True And ShapeVorhanden("R_" & Nach) = True Then
 ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 0, 0, 100, 100).Select
 Selection.name = "P_" & Von & "_" & Nach
 Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle
 Selection.ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes("R_" & Von), 3
 Selection.ShapeRange.ConnectorFormat.EndConnect ActiveSheet.Shapes("R_" & Nach), 1

Schauen Sie sich am besten das VBA-Modul des Makros an. Den Link zum Download finden Sie am Ende des Blogs.

Um die Funktionen zu testen, rufen Sie das Dialogfenster des Makros über die Schaltfläche Kreisverfahren… in der Tabelle Arbeitsblatt auf.

Einstellmöglichkeiten im Dialogfeld des Microsoft Excel Makros

Im Dialogfenster können Sie verschiedene Einstellungen zur Größe der Shapes der Abteilungen einstellen und den Durchmesser des Kreises wählen.

Die Pfeile lassen sich, wie oben beschrieben, je nach Intensität der Transportbeziehung einfärben.

Eine Möglichkeit ist es hier, das Pareto-Prinzip – die 80/20-Regel anzuwenden. Im Beispiel sind die ersten 40% des Jahresbedarfs Rot, die nächsten 40% Gelb, die restlichen 20% Schwarz.

Mit einem Druck auf Starten, beginnt der Makro mit seiner Arbeit und fügt alle Grafikelemente in der Tabelle Arbeitsblatt ein.

Das Dialogfenster kann durch erneuten Klick auf Kreisverfahren… wieder aufgerufen werden.

Mit der Schaltfläche Elemente löschen lassen sich alle Elemente wieder entfernen und beispielsweise bei geänderten Daten neu zeichnen.

Der Trick mit dem Blatt-Schutz

Wenn Sie jetzt beginnen möchten und Abteilungen auf dem Kreis verschieben, müssen Sie ziemlich aufpassen, nicht immer wieder auch Pfeile auszuwählen und diese zu verschieben. Ist das passiert, ist die Konnektivität zum Shape der Abteilung verloren gegangen und der Pfeil „hängt in der Luft“.

Um das zu verhindern, gibt es einen einfachen Trick. Die Shapes der Abteilungen werden über die Eigenschaften in Visual Basic als „nicht gesperrt“ (locked=false) eingestellt. Vor dem Verschieben müssen Sie jetzt nur noch den Blattschutz von Excel einschalten.

Von nun an können nur noch Abteilungen angepackt und verschoben werden.

Sind die fertig können Sie den Blattschutz auch wieder ausschalten. Am einfachsten erfolgt das direkt über den Dialog.

Big Data

Das obige Beispiel bereitet für einen Planer mit oder ohne Tool keinen großen Aufwand, um eine brauchbare Lösung zu finden. Die Praxis sieht aber meistens ganz anders aus. Hier zeigt das Tool seine Stärken.

Durch die Automatik spielt die Anzahl der Abteilungen und Beziehungen (fast) keine Rolle.

Als Beispiel finden Sie hier den Ausgangszustand eines Optimierungsprojekts einer Fertigung.