Datenvisualisierung im Fabriklayout

Unternehmen erheben heute unzählige Daten. Die größte Herausforderung ist es aus den Daten, Erkenntnisse für Entscheidungen zu ziehen. Dabei hilft Datenvisualisierung. Sie soll Daten so darstellen, dass Sie als Entscheider Abweichungen sehen, Muster erkennen oder Trends ausmachen und dadurch bessere Entscheidungen treffen.

In Unternehmen ist Datenvisualisierung oft identisch mit Business-Intelligence (BI) Auswertungen. Ein BI-Tool erstellt regelmäßig oft immer dieselben Daten – jede Menge Diagramme: Balkendiagramme, Liniendiagramme, Kreisdiagramme, Flächendiagramme oder Punktdiagramme. Aber auch Netzdiagramme, Treemaps, Sunbursts, Histogramme oder alle Kombinationen daraus, wenn Ihre BI schon moderner ist.

Bei vielen Anwendungsfällen hilft das auch genau in dieser Form enorm weiter.

Wo bleibt die dritte Dimension?

Wird eine Fabrik geplant oder verändert, betreiben Unternehmen einen großen Aufwand, um Arbeitsplätze an der richtigen Stelle zu platzieren (vgl. Dialog-geführtes Dreiecksverfahren). Mit einer materialflussoptimierten Anordnung versuchen sie, Kostenvorteile zu erreichen. Jede kürzere Transportstrecke spart Kosten. Abgestimmte Puffer zwischen Arbeitsplätzen sorgen für bessere Durchlaufzeiten.

Damit ist dieser hohe Aufwand sinnvoll investierte Zeit, um die hohen Investitionskosten einer Fabrik zu amortisieren.

Was passiert aber nach Abschluss der Planung, wenn die Produktion läuft? Das aufwändig ermittelte Fabriklayout wird nicht in der Leistungsmessung der Produktion eingesetzt. Manchmal werden später Prozessflüsse visualisiert (vgl. Materialflüsse können einfach sein). Das ist ein guter Anfang.

Aber schauen Sie sich einfach Ihre eigenen KPI’s an. Welche davon stehen im Zusammenhang mit Ihrem Fabriklayout?

Deswegen ist es verschwenderisch, die Dimension Fabriklayout nicht in Ihrer Datenvisualisierung auftauchen zu lassen und Sie Ihr aufwändig geplantes Layout zur besseren Entscheidungsfindung nutzen.

Fabriklayouts

Eine Ursache dafür ist, dass Fabriklayouts meistens nur einem kleinen Kreis zur Verfügung stehen. Öffnen können sie oft nur CAD Anwender mit teurer Software wie Autocad oder Microstation. Wenn es doch klappt, sieht man überfrachtete Layout mit vielen Details, Bemaßungen und Beschriftungen.

Die Datenvisualisierung entwickelt sich aber extrem schnell weiter. Wäre eine Fabrik ein Globus, wäre das heute einfach lösbar. Heute gibt es out-of-the-box webbasierte Lösungen, die Daten in Welt-, Länder- oder Stadtkarten visualisieren (siehe observablehq.com) und so Diagrammen eine weitere Dimension hinzufügen.

Beispiel: Bevölkerungsbalken pro Kontinent (siehe gapminder.org)

Sind die Bevölkerungsdaten klassisch, wie in einem BI-Report, in einem Balkendiagramm visualisiert, ist auch klar, wo die meisten Menschen wohnen. Aber in der layoutorientierten Karte sehen Sie mehr: Wie stehen die Nachbarstaaten da? Wie stehen die Kontinente zueinander?

Der gleiche Zusammenhang gilt auch für die Fabrik. Können Sie layoutrelevante Daten in einer layoutbasierten Visualisierung präsentieren, erzeugen Sie eine zusätzliche Dimension, um Muster oder Trends klarer darzustellen. So eine neue Betrachtungsweise hilft Ihnen vielleicht dabei, neue Zusammenhänge zu erkennen.

Beispiel Ausschussanteil

In einer typischen Produktion werden oft Ausschussanteile jeder Maschine erfasst und visualisiert. Für einen Produktionsbereich könnte das in einer dieser Formen aussehen.

Balkendiagramm: Ausschussanteil
Treemap: Ausschussanteil
Kreisdiagramm: Ausschussanteil

Beispiel: Ausschussanteil Visualisierung im Fabriklayout

Sie sehen sofort, die Maschine ZX-1 hat den höchsten Ausschussanteil. Und dass eine mit ähnlichen Namen ZX-2 auch einen hohen Wert hat. Kennen Sie die Produktion, wissen Sie vielleicht, dass es sich bei den Maschinen um eine Schleifmaschine handelt und können sich so fragen warum beim Schleifen mehr Teile ausfallen. Bekommen Sie aber eine zusätzliche Dimension, können Sie sich weitere Fragen stellen.

Warum liegen die Maschinen mit den höchsten Ausschussanteilen gerade nahe räumlich zusammen? Gibt es ein Temperaturproblem durch Zugluft aus dem Logistikbereich? Treten Erschütterungen beim Be- und Entladen im Warenein-/ausgang auf?

Beispiel Druckluftverbrauch

Druckluft ist eines der teuersten Medien in der Fabrik. Deswegen gibt es viele Unternehmen, die den Druckluftverbrauch an Ihren Maschinen messen.

Eine Auswertung könnte aussehen, wie im Balkendiagramm dargestellt. Sie können jetzt schauen, warum an der DMU50V der höchste Verbrauch ist. Visualisieren Sie die Daten mit der Dimension Fabriklayout, sehen Sie vielleicht mehr und stellen sich bessere Fragen. Warum haben alle Maschinen in diesem Bereich einen höheren Verbrauch? Gibt es ein Problem mit der Druckluftzuführung zu diesem Bereich?

Beispiel: Druckluftverbrauch Visualisierung im Fabriklayout

Mit der Datenvisualisierung im Fabriklayout bekommen Sie zwei wertvolle Vorteile. Sie können sich auf der einen Seite sofort tiefer in die Problemerkennung bewegen. Auf der anderen Seite kennen Sie nach einer gewissen Zeit Ihre Produktion in jedem Bereich viel genauer und können Verbesserungsthemen erkennen, die sie zuvor nicht damit in Verbindung gebracht hätten.

DIY

Leider gibt es dabei auch einen Haken. Um solche Diagramme im Layout zu erstellen, hilft Ihnen Standard-Software nicht weiter. Dafür brauchen spezielle Tools. Das lohnt sich vielleicht noch für Auswertungen, welche Sie immer wieder aktualisieren und zeigen. Für genau diese bringt aber ein layoutbasiertes Diagramm immer weniger. Irgendwann haben alle Nutzer die notwendigen Layoutkenntnisse.

Erfolgsträchtiger sind eher neue Darstellungen. Diejenigen, die Sie nur einmal brauchen und sich erst herausstellen soll, ob Sie für die Unterstützung einer Entscheidung taugen.

Sich dafür im normalen Büroalltag in eine Spezial-Software oder in Programmiersprache wie Javascript, Python oder R einarbeiten? Das ist kaum realistisch.

Ich würde Ihnen natürlich schon empfehlen, genau das in Ihrer Freizeit zu beginnen. Sie finden zahlreiche Anregungen hier in meinem Blog.

In der Regel brauchen Sie dafür einen Spezialisten. Mit diesem langen Weg erledigt sich eine impulsive Idee, von der Sie noch nicht wissen, ob sie funktioniert, von selbst.

Bleibt der Weg es mit den Mitteln anzupacken, mit denen man sich im Büroalltag sowieso auseinandersetzt: Standard-Office Anwendungen, wie Microsoft Excel.

Fabriklayout als Basis

Voraussetzung ist ein Fabriklayout. Das müssen Sie sich irgendwie besorgen. Für die meisten Anwendungsfälle braucht das Layout auch nur wenige Details. Deshalb muss es kein CAD Format sein, dass Sie vorher aufwändig konvertieren müssen.

Da die Auswertung in Excel erfolgt, eignen sich Bild-Formate (PNG, JPG) sowieso viel besser. Mögliche Formate sind alles was Excel importieren kann.

3D-Layout sind etwas besser geeignet, dort können Sie die räumliche Höhe für die Visualisierung besser nutzen. Sie müssen aber nicht sein. Ein 2D-Layout lässt sich mit Excel sehr einfach zu einer 3D-Sicht umwandeln.

Über die Bildformate formt Excel das flache Bild zur Isometrie um.

Umwandlung zur 3D-Sicht über Excel-Bildform Isometrie

Umwandlung zur fertigen Isometrie ist abgeschlossen

Visualisieren mit TAG-Marken

Kennzeichnung eines Arbeitsplatzes mit einer Tag-Marke

Excel muss irgendwie wissen, wo sich Ihre Datenpunkte im Layout befinden. Diese Punkte müssen einmalig über ein Etikett (engl. tag) gekennzeichnet werden. Möglich wird das durch ein beliebiges Form-Element. Ich verwende hier rote Kreise. Wichtig ist, dass Sie diesem Element einen eindeutigen Namen geben und ihn so zu einer TAG-Marke machen.

TAGs können Sie im Layout für alle möglichen Layoutpunkte anbringen. Alles was Sie später auswerten möchten eignet sich dafür.

Die Größe und Form der Tag Marke spielt keine wichtige Rollen, wenn Sie nicht weiter notwendig ist, lässt sie sich mit einem Klick im erzeugten Layout ausblenden.

Datenbasis vorbereiten

Eine Visualisierung braucht die passende Datenbasis. Der eindeutige Primärschlüssel dafür ist der TAG. Dieser kommt in die erste Spalte. Alle weiteren Spalten können Sie beliebig mit Ihren Daten füllen oder auch Verknüpfungen zu bereits vorhandenen Auswertungen herstellen.

Grundlegende Einstellungen ermöglichen

Damit sich die Visualisierung komfortabler anpassen lässt, müssen grundlegende Einstellungen auch ohne Visual Basic möglich sein. Dafür gibt es ein eigenes Tabellenblatt Setup.

Sie definieren dort in welcher Tabelle Ihre Daten liegen und auf welchem Tabellenblatt die Visualisierung erzeugt werden soll.

Wichtigstes Kriterium ist das Feld, das visualisiert werden soll. Es wird über die Spaltennummer gewählt. Damit Berechnungen möglich werden, muss es sich um ein Zahlenfeld handeln.

Fast genauso wichtig ist die Form der Visualisierung. Erzeugt wird sie auch über ein Form-Element. Für ein 2D-Layout eignen sich flache Elemente. Ich verwende in diesem Beispiel einen Kreis. Damit der VBA-Code das Form-Element findet, braucht es einen eindeutigen Namen SETUP_SIZEINF_SHAPE. Dieses Form-Element wird größentechnisch auf Ihr ausgewähltes Datenfeld angepasst. Dafür ist es entscheidend, welche Formatierung Sie für das Element verwenden. Überlagern sich flache Elemente bleiben sonst Informationen verdeckt. Das lässt sich verhindern, indem Sie die Transparenz auf z. B. 50% erhöhen.

Visualisierungseinstellungen werden über ein eigenes Tabellenblatt ermöglicht

Damit die maximale Größe zu Ihrem Gesamtlayout passt, lässt sich diese festlegen. Das ist oft nicht einfach, da Excel intern mit Punkten als Einheit rechnet. Einfacher ist es die maximale Größe direkt im Form Element einzustellen. Dann ist es direkt für Sie sichtbar. Die Umrechnung in Punkte klappt komfortabel mit Klick auf Ermitteln aus Shape.

Möchten Sie Balken verwenden ist es sinnvoller, wenn nicht das ganze Element skaliert wird. Die Breite oder die Höhe sollten dann für alle Elemente im Layout gleichbleiben. Dafür gibt es auch eine Lösung. Hinter der Größenangabe können Sie festlegen, ob diese skaliert werden soll oder für alle Werte bei dem eingetragenen Wert bleiben soll.

Bleibt noch die Frage, wie das Form Element zu der Tag Marke positioniert werden soll. Das kann je nach Anwendungsfall unterschiedlich sein, deswegen macht es Sinn, das auch einstellbar zu machen. Umgesetzt habe ich die Positionen Links-Mitte-Rechts für die X-Koordinate und Oben-Mitte-Unten für die Y-Koordinate.

VBA Schritt für Schritt

Diese Grundeinstellungen reichen aus, um loszulegen. Der Ablaufprozess beginnt, wenn Sie auf den Button Visualisieren klicken.

Vorbereitungen

Zuerst muss der Makro wissen, wo die Quelldaten stehen und auf welchem Tabellenblatt sich das Fabriklayout mit den Tag-Marken befindet. Beide Werte liegen als Feldinformationen im Tabellenblatt Setup vor. Die Sub SetSheetNames liest beide in globale Variablen ein, damit stehen sie allem VBA Code zur Verfügung.

Private Sub SetSheetNames()
  sheetData = Sheets(SHEET_SETUP).Range("SHEET_DATA")
  sheetLayout = Sheets(SHEET_SETUP).Range("SHEET_LAYOUT")
End Sub

Falls bereits eine Visualisierung im Layout Tabellenblatt vorliegt, löscht die Subroutine DeleteShapes diese.

  Call SetSheetNames

  Call DeleteShapes(sheetLayout, "SIZEINF_")

  maxValue = WorksheetFunction.Max(Sheets(sheetData).Columns(Sheets(SHEET_SETUP).Range("SETUP_SIZEINF_COLUMN")))
  minValue = WorksheetFunction.Min(Sheets(sheetData).Columns(Sheets(SHEET_SETUP).Range("SETUP_SIZEINF_COLUMN")))
  maxWidth = Sheets(SHEET_SETUP).Range("SETUP_SIZEINF_LARGE_WIDTH")
  maxHeight = Sheets(SHEET_SETUP).Range("SETUP_SIZEINF_LARGE_HEIGHT") 

  i = 1

Um später die Größe des Visualisierungs-Form-Elements zu berechnen, ermittelt die Excel Funktion Max den größten Wert der festgelegten Datenspalte über Sheets(SHEET_SETUP).Range(„SETUP_SIZEINF_COLUMN“). Der Minimum Wert kann genauso ermittelt werden, wird aber momentan noch nicht verwendet.

In die beiden Variablen maxWidth und maxHeight speichert der Vorbereitungsteil die eingetragenen Daten aus dem Setup Tabellenblatt.

Datenzeilen einlesen

Jetzt durchläuft eine do-until-Schleife alle Zeilen der Daten-Tabelle. Die Zählvariable ist i.

    i = i + 1
    tagName = Sheets(sheetData).Cells(i, 1)
    value = Sheets(sheetData).Cells(i, Sheets(SHEET_SETUP).Range("SETUP_SIZEINF_COLUMN"))

In der Datentabelle stehen in der ersten Zeile die Spaltenüberschriften, deswegen beginnt die Schleife mit der Erhöhung von i. Die erste Spalte enthält den eindeutigen Namen der Tag-Marke. Der Datenwert muss eine Zahl sein. Ist das nicht so, tritt während der Schleife ein Laufzeitfehler auf. Um diesen zu vermeiden, steht am Anfang der Routine ein On Error Resume Next.

Form Element kopieren und einfügen

Jetzt prüft die Routine, ob sich im Tabellenblatt des Layouts überhaupt eine Tag-Marke mit dem Namen aus der eingelesenen Zeile befindet. Da ein Wert mit 0 nicht gezeichnet werden kann, wird dieser auch in die Bedingungen aufgenommen.

    If ShapeExists(sheetLayout, tagName) = True And value <> 0 Then
      Sheets(SHEET_SETUP).Shapes("SETUP_SIZEINF_SHAPE").Copy
      Sheets(sheetLayout).Paste
      Set shp = Sheets(sheetLayout).Shapes("SETUP_SIZEINF_SHAPE")
      shp.name = "SIZEINF_" & tagName

Über den Copy Befehl des Shapes auf dem Setup Tabellenblatt kopiert Excel das Form Element in die Zwischenablage. Auf dem Layout Tabellenblatt wird es wieder eingefügt mittels Paste. Damit später die Elemente wieder erkannt werden können erhalten Sie einen Prefix „SIZEINF_“ und den Namen der Tag-Marke. Mit diesem lassen sie sich auf einfach wieder Löschen. Die Routine DeleteShapes nutzt das aus.

Größe des Visualisierungs-Form-Elements berechnen

Das neue Form Element befindet sich jetzt schon sichtbar im Layout. Was noch fehlt ist die richtige Größe und Position. Als erstes wird die Größe berechnet.

Die Abfrage unterscheidet noch, ob in dem Eingabefeld von Breite eine Fixierung eingetragen ist („J“). Falls ja wird die Breite shpWidth auf die maximale Breite maxWidth gesetzt. Wenn nicht wird skaliert mit dem Datenwert in der Variablen value. Bei der Höhe wird genauso verfahren.

      If Left$(UCase(Sheets(SHEET_SETUP).Range("SETUP_SIZEINF_LARGE_WIDTH_FIXED")), 1) = "J" Then
        shpWidth = maxWidth
      Else
        shpWidth = maxWidth * value / maxValue
      End If
      
      If Left$(UCase(Sheets(SHEET_SETUP).Range("SETUP_SIZEINF_LARGE_HEIGHT_FIXED")), 1) = "J" Then
        shpHeight = maxHeight
      Else
        shpHeight = maxHeight * value / maxValue
      End If

Die X-Koordinate entspricht in Excel der Eigenschaft Left des Form Elements. Bei der Positionsberechnung unterscheidet auch eine Abfrage nach der eingestellten Positionierung: Links-Mitte-Rechts. Grundlage ist jeweils die Mitte des Form Elements der Tag-Marke im Layout Tabellenblatt. Diese ist Left+Width/2. Je nach Einstellung wird entsprechend des gesamten Wertes von shpWidth, shpWidth/2 oder nichts abgezogen, um die X-Position zu erhalten. Gleiches gilt analog für die Y-Koordinate. Die zugehörige Excel Eigenschaft ist hier Top.

      s = Left$(UCase(Sheets(SHEET_SETUP).Range("SETUP_SIZEINF_PASTE_X")), 1)
      If s = "L" Then
        shp.Left = Sheets(sheetLayout).Shapes(tagName).Left + Sheets(sheetLayout).Shapes(tagName).Width / 2
      ElseIf s = "M" Then
        shp.Left = Sheets(sheetLayout).Shapes(tagName).Left + Sheets(sheetLayout).Shapes(tagName).Width / 2 - shpWidth / 2
      Else
        shp.Left = Sheets(sheetLayout).Shapes(tagName).Left + Sheets(sheetLayout).Shapes(tagName).Width / 2 - shpWidth
      End If
      
      s = Left$(UCase(Sheets(SHEET_SETUP).Range("SETUP_SIZEINF_PASTE_Y")), 1)
      If s = "O" Then
        shp.Top = Sheets(sheetLayout).Shapes(tagName).Top + Sheets(sheetLayout).Shapes(tagName).Height / 2
      ElseIf s = "M" Then
        shp.Top = Sheets(sheetLayout).Shapes(tagName).Top + Sheets(sheetLayout).Shapes(tagName).Height / 2 - shpHeight / 2
      Else
        shp.Top = Sheets(sheetLayout).Shapes(tagName).Top + Sheets(sheetLayout).Shapes(tagName).Height / 2 - shpHeight
      End If
      
      shp.Width = shpWidth
      shp.Height = shpHeight

Die berechneten Werte werden am Ende dem neu eingefügten Shape zugewiesen.

Text einfügen

Falls im Setup Tabellenblatt die Einstellung Wert anzeigen den Wert Ja hat, muss noch ein Text in das Form Element eingefügt werden. Da nur Text eingefügt wird, muss die Formatierung bereits im Form Element im Setup Tabellenblatt eingestellt sein. Der Aufruft ist in eine eigene Subroutine ChangeShapeText gekapselt.

      Call ChangeShapeText(shp, Sheets(sheetData).Cells(i, Sheets(SHEET_SETUP).Range("SETUP_SIZEINF_COLUMN")).Text)

Das ist nicht unbedingt notwendig, macht es aber einfacher auf Fehler zu reagieren. Falls das eingefügte Shape einen Text darstellen kann, wie z. B. ein 3D-Element oder ein Bild. Reicht auch ein On Error Resume Next, um die Sub einfach wieder ohne großen Aufwand zu verlassen.

Sub ChangeShapeText(shp As Shape, txt As String)
On Error Resume Next
  If UCase(Sheets(SHEET_SETUP).Range("SETUP_SHOW_VALUE")) = "J" Then
    shp.TextFrame2.TextRange.Characters.Text = txt
  End If
End Sub

Schleife abschließen

Am Schluss der Routine wird über den Tag Namen geprüft, ob noch Daten vorliegen. Hat dieser keinen Inhalt, beendet die Routine die Schleife.

  Loop Until tagName = ""
  Sheets(sheetLayout).Select

Das mit Sie als Benutzer merken, dass die Visualisierung fertiggestellt ist, wechselt der Befehlt Select direkt ins Fabriklayout uns zeigt das Tabellenblatt an.

Geschafft, das war auch das Ende der Routine.

Beispiele

Leistung pro Maschine (Form Element)
Leistung pro Maschine in Isometrie (Form Element)
Mitarbeiteranzahl in der Produktion (Piktogramme)
Kritische Kapazitäten (Form Element)

Jetzt wird es Zeit für Ihren ersten Visualisierungsversuch in der nächsten Dimension. Schreiben Sie mir gerne, wie Ihre Visualisierung geworden ist. Ich bin gespannt.

Download

Hier können Sie das Makro und eine Beispieltabellen downloaden und Visualisierungen ausprobieren.

Links

Diagramme bei gapminder.org
Visualisierungen mit D3.js: observablehq.com
Artikel Dialog-geführtes Dreiecksverfahren
Artikel Materialflüsse können einfach sein

Technik und Coding