Office: (Office 2019) Ermittlung der täglichen Nutzungsdauer einer Anlage

Hallo zusammen,

ich beiße mir derzeit die Zähne an folgender Problemstellung aus:
Mein Ziel ist eine Ermittlung der wertschöpfenden Nutzungsdauer einer Anlage pro Tag in Excel.

Situation:
Es wird rund um die Uhr an jedem Tag des Jahres produziert. Für eine bestimmte Periode (01.01.2021 - 30.04.2021 = 120 Tage) habe ich mir die Start- und Endzeitpunkte der Arbeitsschritte für die Aufträge aus dem System gezogen, die innerhalb dieser Zeitperiode bearbeitet wurden, die Daten soweit Anonymisiert und in Form von Datums- und Zeitstempel gelistet.
Für diese Zeitperiode (120 Tage x 24 Stunden/Tag = 2880 Stunden) möchte ich wissen, wie lange jede Anlage mit einer rein wertschöpfenden Tätigkeit beschäftigt war, sprich: wie lange eine Anlage einen Auftrag bearbeitet hat. Im Endeffekt reicht mir eine prozentuale Aussage, bspw. "Für Anlage 403 entfielen für den festgelegten Betrachtungszeitraum nur 40 % der verfügbaren Zeit in wertschöpfenden Tätigkeiten".

Bei allen Anlagen handelt es sich grundsätzlich um zwei verschiedene Anlagentypen:

Anlagentyp A (403 - 408) können die Aufträge jeweils nur sequentiell bearbeiten. Ein Auftrag wird als Stapel gepackt und der komplette Stapel gleichzeitig von Anfang bis Ende bearbeitet. Die Ermittlung der Nutzungsdauer gestaltet sich hier einfach.

Anlagentyp B (410 und 411) bearbeiten Aufträge allerdings in kontinuierlicher durchlaufweise. Ein Auftrag besteht aus mehreren Produkten, die allesamt nacheinander bearbeitet werden. Die Anlage besteht aus diversen Belegungsplätzen und jedes Produkt muss jeden Belegungsplatz nacheinander durchlaufen. Ein Auftrag gilt dann als abgeschlossen, wenn jedes Produkt des Auftrages alle Bearbeitungsplätze durchlaufen hat. Wenn das letzte Produkt des aktuellen Auftrages einen bestimmten Belegungsplatz erreicht hat, startet bereits der nächste Auftrag. Zeitgleich werden also mehrere Aufträge gleichzeitig bearbeitet.

Beispiel Anlagentyp B:
- Auftrag 1: Start (20.02.2021 01:33), Ende (20.02.2021 11:14), Bearbeitungsdauer (09:41 oder dezimal 9,68 Stunden)
- Auftrag 2: Start (20.02.2021 04:44), Ende (20.02.2021 14:23), Bearbeitungsdauer (09:38 oder dezimal 9,64 Stunden)
- Auftrag 3: Start (20.02.2021 07:46), Ende (20.02.2021 17:19), Bearbeitungsdauer (09:32 oder dezimal 9,55 Stunden)
- Auftrag 4: Start (20.02.2021 11:06), Ende (20.02.2021 20:26), Bearbeitungsdauer (09:19 oder dezimal 9,33 Stunden)

Hinzu kommen jeweils vorher und nachher weitere Aufträge (der Einfachheit halber habe ich sie in diesem Beispiel weggelassen). Summiert man alle Bearbeitungsdauern auf, ergibt sich als Ergebnis: 38,2 Stunden wertschöpfende Tätigkeit. Das Ergebnis ist zwar nicht falsch, aber eben nur die halbe Wahrheit. Denn die Summierung geht von der Annahme aus, dass alle Tätigkeiten hintereinander und nicht überlappend ausgeführt wurden. Unter der Annahme, dass die Aufträge überlappend bearbeitet werden, müsste eigentlich eine Bearbeitungszeit von 18:53 Stunden rauskommen. Somit liegt der Fehler bei meiner Herangehensweise an beschriebene Problematik.

Um meine - zugegeben, lange - Ausführung zu verdeutlichen, habe ich die betroffene Excel-Datei angehängt.
Hat jemand von euch eine Idee, wie man diese Thematik lösen bzw. angehen kann?

Danke im Voraus für jeglichen Hilfeversuch.

Gruß
Darkwing

 

Ich denke, dass die 410-411 Herausforderung mit einem kleinen Makro loesbar ist

Detailfragen:

• Um einen Auftrag zeitlich (einer 120-Tage Periode) zuzuordnen, gilt da das Start oder das End-Datum?
• Ausser den von dir in der Beschreibung angegebenen Anlagetypen 403-408, 410-411 gibt es in deinem Excel noch andere zB 501-503. Wie ist mit denen zu verfahren?

 

Hallo WuwuMan,

erstmal besten Dank für deine Antwort :)
Habe schon befürchtet, dass ich die Thematik zu umständlich formuliert hatte.

Zu deinen Detailfragen:

1. Tja letztlich ist es eine Auslegungssache. Den Sinn der Frage verstehe ich, jedoch ist es meines Erachtens nach für die Wertschöpfungsfragestellung nebensächlich, ob man sich auf das Start- oder Enddatum konzentriert. Legen wir der Einfachheit halber das Start-Datum fest.
2. Lassen wir diese erst einmal außen vor. Die an den Anlagen 403 - 408 bearbeiteten Chargen müssen nach der Bearbeitung eigentlich in einer der drei Anlagen 501 - 503 (welche gerade frei ist) weiterverarbeitet werden. Allerdings stimmen die Daten dieser Anlagen offensichtlich nicht wirklich. Im Reiter "Pivot-Auswertung" wird dies deutlich: Bei einer gesamt zur Verfügung stehenden Zeitspanne von 2880 Stunden im Zeitintervall produzieren diese Anlagen (gemäß den Ergebnissen der Pivot-Auswertung) mit teilweise über 100 % Leistung (bspw. Anlage 503: 3876 Stunden). Was bei den Anlagen 410 und 411 zulässig ist, kann bei den 50Xer-Anlagen definitiv nicht stimmen. Da sind die zugrundliegenden Daten entweder viel zu ungenau oder vom jeweils gesetzten Zeitstempel falsch. Rein physikalisch benötigen diese Anlagen halb solange die die Anlagen 403 - 408 für die Bearbeitung einer Charge. Dahingehend werde ich mir die Datenquelle nochmal anschauen müssen.

 

So!, also hier ist mein Loesungsvorschlag.
Wenn ich die Herausforderung richtig verstanden hab, dann ist das Ergebnis auf Grund der aktuellen Eingabedaten nicht sehr erstaunlich - naemlich, dass die Maschinen bis auf 2 Tage (01/01 und 30/03) durchlaufen.

Im xls hab ich 3 Blaetter hinzugefuegt. Sie sind historisch gewachsen, drum siehts noch ein wenig durcheinander aus (das koennt ich noch bereinigen) aber funktionell tuts das, was ich glaube, es tun soll.

Auf Blatt "Parameter" gibts eine kleine Text-Box mit Bedienungs-Anweisungen.

Hab per Makro das Screen-Update ausgeschaltet, da die Verarbeitung dadurch schneller geht. Zur "Ueberwachung" zeig ich aber links unten per Durchlauf das soeben verarbeitete Datum an.

Von einem Schoenheitsfehler weiss ich schon - den kann ich wenn gewuenscht auch noch beheben: Wenn die Anlage ueber eine Tagesgrenze laeuft, dann zeig ich um eine Sekunde mehr Laufzeit (die tatsaechliche Ende-Zeit +1s)

Bitte um Testung und Verbesserungsvorschlaege.

 

Ich komme leider erst jetzt dazu, eine Antwort zu verfassen; dahingehend bitte ich schonmal um Nachsicht für die Verzögerung.

Bevor ich mir die Tabelle im Detail anschaute, obsiegte die reine Neugierde: ich drückte den Start-Button. Es öffnete sich die angehängte Fehlermeldung, die mich am Weitermachen hindert.



Ich interpretiere die Anweisungsbox (Tabelle "Parameter") so, dass die Eingabe der Auswertedaten in Tabellenblatt "Auswertung AG 40&45" vornimmt und nach Betätigung des Start-Buttons die Tabelle "Tab1" eigentlich für jeden Tag einzeln die Bearbeitungszeiten von Chargen anzeigen soll. Bei mir steht dort in jeder Zelle nur "#NAME?".

Wie kann ich die Fehlermeldung lösen/beheben?

 

Tut mir leid! Der Grund dafuer liegt hoechstwahrscheinlich in unterschiedlichen Versionen von Excel. Ich bin (unueberlegterweise) davon ausgegangen, dass du auf der aktuellsten 365er bist, das ist aber vermutlich nicht der Fall - ich schliesse das aus dem "#NAME?", das ich rund um die Fehlermeldung sehe. Stimmt das? (Du koenntest auch mal auf Zelle "Parameter!H19" gehen und mit F2 die Formelhilfe aufrufen, um zu sehen, ob die SORT Funktion verfuegbar ist.)
Haettest du die Moeglichkeit, das Ding in so einer 365er-Umgebung laufen zu lassen?
Wenn nicht, dann laesst sich das Ding natuerlich modifizieren - ist aber ein ziemlicher Aufwand und wird auch die Laufzeiten der Berechnung erheblich verlaengern.

 

In der Tat nutze ich privat Office 2019 und beruflich Office 2016. Eine Nutzung der 365er-Version kommt (leider) erst einmal nicht in Frage.

Deinen beschriebenen Vorschlag konnte ich nicht umsetzen (nach Druck der Taste F2 auf besagter Zelle öffnete sich die Web-Hilfe).

 

Hier kommt die modifizierte Version, die auf Version2016 / 2019 laufen sollte. (Hoffe ich hab nicht uebersehen)

In der anghaengten Version zeigt das Blatt "Ergebnis" die aktuelle Situation (01-04/2021) - es gibt keine Loecher. Die Durchrechnung hat +/- 23 Minuten gedauert (siehe Parameter!L4)

Falls du das Ding mit neuen Daten / neuer Selektion laufen laesst, beachte: Am Schirm tut sich waehrend der Berechnung nichts, ausser dass links unten ein Fortschritts- Hinweis aktualisiert wird.
Neue Daten bitte ueber den bestehenden Bereich auf Blatt "Auswertung AG 40&45" kopieren.