Fehlermeldungen im halbstündigen Rhythmus auswerten

In dieser Beispiel-Datei sind in 3 Tabel­len­blät­tern auf einen bes­timmten Zeitraum begren­zte Auszüge von Fehler­pro­tokollen je ein­er Mas­chine aufge­lis­tet. Das Ziel ein­er Excel-Auswer­tung (PQ) soll sein, die Anzahl der Fehler je halbe Stunde (begin­nend mit dem Start ein­er Stunde, also ##:00 Uhr) zu berech­nen. Es sollen ein­er­seits die Werte je Mas­chine als ander­er­seits auch aller Maschi­nen gemein­sam errech­net wer­den. Für die Lösung gibt es ver­schiedene Möglichkeit­en; ange­fan­gen bei Plain Excel über VBA bis zu Pow­er Query. Ich stelle Ihnen hier zwei von mehreren denkbaren PQ-Lösun­gen vor, bei­de sind aus mein­er Sicht gut nachvol­lziehbar. Und bedenken Sie bitte, dass solche Lis­ten meist sehr viel umfan­gre­ich­er sind und darum VBA oder PQ aus Grün­den der Per­for­mance eher in Betra­cht gezo­gen wer­den soll­ten.

Zum Ein­stieg habe ich aber eine etwas bess­er hän­del­bare „Tabelle” erstellt, es ist das erste Tabel­len­blatt mit dem Namen Zusam­menge­fügt. Und „wie im richti­gen Leben” sind auch leere Spal­ten zwis­chen den einzel­nen Lis­ten (was ja im Sinne ein­er ordentlichen Daten­hal­tung prinzip­iell nicht vorkom­men sollte). Und ja, ein typ­is­ch­er Anwen­der wird die über alle Spal­ten gehende Über­schrift als auch alle drei Daten­blöcke gemein­sam markieren (von A1:E43), und genau das wer­den Sie für die erste Übung auch tun.

Importieren Sie nun bei beste­hen­der Markierung A1:E43 die Dat­en in den Pow­er Query-Edi­tor. Da Excel bei diesem Vor­gang den markierten Bere­ich in eine „Intel­li­gente” Tabelle for­matiert wird in einem Dia­log gefragt, ob die Tabelle Über­schriften hat oder nicht. Aus­nahm­sweise ist das in diesem Fall egal, denn die eigentlichen, kün­fti­gen und dann rel­e­van­ten Über­schriften ste­hen ja sowieso in Zeile 2. – Per Default wird angegeben, dass hier keine Überschrift(en) existieren und dabei belassen Sie es auch.

In der Excel-Tabelle als auch im Pow­er Query Edi­tor sind automa­tis­che Spal­ten-Über­schriften Spalte1 bis Spalte5 vergeben wor­den. (Ab hier beziehen sich meine Anmerkun­gen stets auf den PQ-Edi­tor.) Die erste Daten­zeile enthält auss­chließlich in Spalte1 Dat­en, näm­lich die ursprüngliche, zen­triere Über­schrift und in der zweit­en Zeile ste­hen die eigentlichen Über­schriften. Bevor irgendwelche Kor­rek­turen an den Dat­en vorgenom­men wer­den, gebe ich der Abfrage erst ein­mal einen „sprechen­den” Namen. Typ­is­cher­weise ist dieses Roh­Dat­en oder (meis­tens wie auch hier) die englis­che Beze­ich­nung Raw­Da­ta.

Aus prinzip­iellen Erwä­gun­gen ver­suche ich, die Per­for­mance so opti­mal zu gestal­ten wie möglich. Darum wäre im Nor­mal­fall der näch­ste Schritt, die bei­den leeren Spal­ten (Spalte2 und Spalte4) zu löschen. Aus zwei Grün­den tue ich das hier nicht: Erstens ist das bei deut­lich mehr Spal­ten ziem­lich aufwändig und zweit­ens ist das beibehal­ten der leeren Spal­ten pri­ma für die Demon­stra­tion ein­er Automatik bei der weit­eren Ver­ar­beitung geeignet. Im fol­gen­den Schritt gehen Sie im Menüband über Zeilen ver­ringern | Zeilen ent­fer­nen und löschen nur die erste Zeile. Jet­zt ste­hen in der ersten Dat­en-Zeile die eigentlichen Über­schriften. Im Menüband ein Klick auf Erste Zeile als Über­schriften ver­wen­den und ein wichtiges Zwis­chen­ziel ist erre­icht.

Im fol­gen­den Schritt kön­nte es darum gehen, den Daten­typ anzu­passen. Aber muss das unbe­d­ingt schon zu diesem Zeit­punkt sein? Nein, denn es ist im Prinzip „nur” eine Frage des Ausse­hens; es sollen ja mit den als Dez­i­malzahl dargestell­ten Zeit­en keine speziellen Time-Oper­a­tio­nen der Sprache M vogenom­men wer­den. 💡

Okay, im näch­sten Schritt sollen die ganzen numerischen Werte untere­inan­der geschrieben und der jew­eili­gen Mas­chine zuge­ord­net wer­den. Also in der ersten Spalte ste­ht dann der Name der Mas­chine und in der Folges­palte der numerische Wert, welch­er ja die Uhrzeit spiegelt. Das Stich­wort dafür heißt „Ent­piv­otieren”. Zwei mögliche, bei­des rel­a­tiv ein­fache Wege führen zum prinzip­iell iden­tis­chen Ziel:

1. Sie markieren alle Spal­ten, entwed­er Trans­formieren | Spal­ten ent­piv­otieren oder Recht­sklick in eine der Über­schriften und im Kon­textmenü Spal­ten ent­piv­otieren wählen.
2. Alter­na­tiv gehen Sie über Spalte hinzufü­gen | Indexs­palte, Recht­sklick in die Spalte Index und Andere Spal­ten ent­piv­otieren. Das ist übri­gens meine Lieblings-Vorge­hensweise, da ich bei Abfra­gen mit vie­len Spal­ten diese Hin- und Her­schieberei der Ansicht nicht so gerne mag. 😐 Im Anschluss lösche ich die Spalte Index gle­ich wieder. Aunahme: Ich lösche sie nicht, wenn ich später im Ver­lauf wieder die Orig­i­nal-Rei­hen­folge der Dat­en her­stellen möchte.

Und siehe da, es sind über diesen Weg auss­chließlich die Zeilen mit Werten erfasst wor­den, Zellen der leeren Spal­ten, die ja auss­chließlich den Inhalt null haben, fehlen vol­lkom­men. Und genau das meinte ich weit­er oben mit dem Hin­weis auf die Automatik. 😉 Und nun brauche ich auch nur in 1 Spalte (Wert) den Daten­typ von Beliebig auf Zeit zu ändern und nicht in 3 (oder mehr) Spal­ten! Zugegeben, das sieht auch bess­er aus! 😎

Damit habe ich eine solide Basis für die bei­den kom­menden Auswer­tun­gen geschaf­fen. Und bis hier­hin war das alles auch noch Basiswis­sen in Sachen PQ. Nun kommt die Über­legung zum tra­gen, dass jed­er Uhrzeit ein Halb­stun­den­bere­ich zuge­ord­net wer­den muss. Da ich ja die Zeit­en vergeben habe weiß ich auch, dass der früh­este Ein­trag um 12:00 Uhr ist. Der erste Zeit­bere­ich wären also die 30 Minuten von 12:00 bis 12:29. Hin­weis: Wenn Sie die Spalte Wert erweit­ern, wer­den die darin enthal­te­nen Zeit­en automa­tisch auf­steigend angezeigt; der ober­ste Wert ist also immer die este Zei­tangabe für die pro­tokol­lierten Fehler. Weit­er geht es dann mit 12:30 bis 12:59, und so weit­er. Auch wenn der erste Ein­trag nicht zur vollen Stunde sein sollte, der zu ver­wen­dende Zählbere­ich begin­nt stets um ##:00 oder ##:30. Und Sie behal­ten bitte im Hin­terkopf, dass 2 Auswer­tun­gen erstellt wer­den sollen. Ein­er­seits die Gesamtzahl der Fehler aller Maschi­nen im jew­eili­gen Halb­stun­den­bere­ich und ander­er­seits die Anzahl der Fehler jed­er einzel­nen Mas­chine im entsprechen­den Zeitraum.

Viele Wege führen nach Rom. Ich zeige Ihnen hier für die Zuord­nung der einzel­nen Zeit­en einen Weg auf, der gewiss nicht alle Möglichkeit­en der Sprache M nutzt; dafür aber sind die einzel­nen Schritte für Sie gewiss bess­er nachvol­lziehbar, weil sie an die Vorge­hensweise in Plain Excel angelehnt sind. Fol­gende Über­legung liegt meinen Schrit­ten zugrunde:

• Die Angabe der Stunde bleibt für jede Fehler­mel­dung in jedem Fall erhal­ten, sie verän­dert sich nicht.
• Für die jew­eilige Minute gilt, dass sie auf :00 herun­terge­brochen wird, wenn sie klein­er ist als 30, anson­sten wird die Anzeige der Minute auf :30 abgerun­det.

Markieren Sie dazu als Erstes die Spalte Wert, wo ja die Uhrzeit­en der Fehler einge­tra­gen sind. Spalte hinzufü­gen, im Menüband ein Klick auf Zeit | Stunde | Stunde. Noch ein­mal Wert markieren, Zeit | Minute. Nun haben Sie je eine neue Spalte, wo die dez­i­malen numerischen Werte der Stunde und der jew­eili­gen Minute einge­tra­gen sind. Markieren Sie nun die Spalte Minute. Spalte hinzufü­gen | Bed­ingte Spalte und füllen Sie im Dia­log jew­eils diese Werte ein bzw. wählen Sie aus:

Eine visuelle Kon­trolle wird Ihnen rasch zeigen, dass die Ergeb­nisse stim­men. Die Spal­ten Wert und Minute kön­nen Sie jet­zt löschen. Markieren Sie nun Stunde und Start­Minute (beacht­en Sie die Rei­hen­folge), Recht­sklick in eine der bei­den Über­schriften und Spal­ten zusam­men­führen. Als Trennze­ichen wählen Sie: Dop­pelpunkt und bei Neuer Spal­tenname (option­al) schreiben Sie Zeit­bere­ich ab hinein. Auf beliebigem Wege ändern Sie nun den Daten­typ der Spalte Zeit­bere­ich ab zum Typ Zeit. Damit ist die erste Episode der Auswer­tung fast abgeschlossen. Um die bei­den Berech­nun­gen „sauber” durchzuführen, erstellen Sie 2 neue Abfra­gen. Sie kön­nten diese Abfrage zwar Duplizieren (Klick im linken Seit­en­fen­ster oder über Start | Ver­wal­ten), ich ziehe es vor, einen Ver­weis auf Raw­Da­ta zu erstellen; dadurch habe ich in dieser Query wesentlich weniger Schritte im recht­en Seit­en­fen­ster sicht­bar (und das hat in anderen Sit­u­a­tio­nen weit­ere Vorteile). Bei der Gele­gen­heit erstelle ich gle­ich noch einen zweit­en Ver­weis, es sollen ja zwei unter­schiedliche Berech­nun­gen erstellt wer­den.

Hin­weis für Fortgeschrittene/Spezis/Cracks, … Ja, es geht deut­lich „schlanker”, manch ein­er wird auch „ele­gan­ter” sagen. Das Wun­schergeb­nis ste­ht mit ein­er einzi­gen if – Formel der Sprache M in der drit­ten Spalte. Das ist naturgemäß meine Vorge­hensweise. 😎 Mailen Sie mich an und ich schreibe Ihnen, wie Sie gegen eine Spende von 1,00 € den Code-Schnipsel, welchen Sie in eine Benutzerdefinierte Spalte ein­fü­gen kön­nen, von mir erhal­ten. Dieser Euro wird dann auss­chließlich für die Kosten dieses Blogs ver­wendt. Ver­sprochen!

▲ nach oben …

Anzahl Fehlermeldungen aller Maschinen je Zeitraum

Wech­seln Sie zu Abfrage Raw­Da­ta (2) und benen­nen Sie diese ide­al­er­weise gle­ich um. Ich schlage Ihnen alle Maschi­nen vor. Das hat zwar keinen Ein­fluss auf die Arbeitsweise von Pow­er Query sorgt aber für deut­lich mehr Trans­parenz. Löschen Sie erst ein­mal ide­al­er­weise die Spalte Attrib­ut, sie wird für diese Berech­nung nicht benötigt. Markieren Sie Zeit­bere­ich ab, Recht­sklick in die Über­schrift und wählen Sie Grup­pieren nach… Eine kurze Analyse wird Ihnen zeigen, dass die Vor­gaben genau dem entsprechen, was sie als Ergeb­nis erwarten. Darum ein Klick auf OK und es ist gut. 😉 Bleibt nur noch Schließen & laden oder Schließen & laden in… und im Excel-Work­book ist das Ergeb­nis als for­matierte Tabelle einge­tra­gen wor­den.

▲ nach oben …

Anzahl Fehlermeldungen jeder Maschine je Zeitraum

Im ersten Schritt wer­den Sie der Abfrage Raw­Da­ta (3) auch einen anderen Namen geben, beispiel­sweise einzelne Maschi­nen. Der Form hal­ber kön­nten bzw. soll­ten Sie die Spalte Attrib­ut zu Mas­chine umbe­nen­nen. Markieren Sie nun nacheinan­der Mas­chine und Zeit­bere­ich ab, Recht­sklick in eine der bei­den Über­schriften und Grup­pieren nach… Auch hier ist das durch Pow­er Query berech­nete Ergeb­nis erst ein­mal kor­rekt.

„Erst ein­mal”, weil die Anord­nung gewiss nicht opti­mal ist. Die Dat­en soll­ten noch sortiert wer­den. Und nun stellt sich die Frage, welche Form von Überblick Sie gerne hät­ten. Die eine Möglichkeit stellt sich so dar, dass erst alle Zeit­en der Mas­chine 1 jew­eils nach Uhrzeit­en zusam­menge­fasst dargestellt wer­den und darunter die gle­iche Logik für die Mas­chine 2 und fol­gende. – Die zweite von mir angedachte Darstel­lungsweise wäre so, dass die Sortierung erst nach Zeit­blöck­en auf­steigend erfol­gt und inner­halb der Zeit­bere­iche die Maschi­nen in auf­steigen­der Rei­hen­folge. Bei­de Sichtweisen lassen sich durch die Rei­hen­folge der Sortierung der Ergeb­nis-Abfrage real­isieren. Schauen Sie gerne in der Mus­ter­datei im Blat­treg­is­ter Auswer­tun­gen nach, dort sind auch diese bei­den per PQ erstell­ten Ergeb­nisse gezeigt. Natür­lich hätte auch eine der bei­den Sortierungn als Ergeb­nis gere­icht und User wür­den dann in Excel die Sortierung ändern kön­nen. 😎 

Möcht­en Sie bei dieser Form der Berech­nung auch noch zusät­zlich die Gesamtzahl aller (100) Fehler berech­net haben, kön­nte dieses zwar auch in Pow­er Query (typ­is­cher­weise ein­er getren­nten Abfrage) geschehen; ich schlage Ihnen vor, die Ergeb­nis-Tabelle in Excel durch eine automa­tis­che Berech­nung der Summe unter­halb der let­zten Daten­zeile in ein­er Ergeb­niszeile durchzuführen.

Es bleibt ihrer Exper­i­men­tier­freude unbenom­men, die Anzeige des Zeit­bere­ichs genauer zu definieren; das wäre dann beispiel­sweise der Text 14:00 – 14:29. Und natür­lich kön­nen Sie als fort­geschrit­ten­er Anwen­der in Sachen Pow­er Query die Berech­nung des Zeit­in­ter­valls mit unter­schiedlichen M-Funk­tio­nen durch­führen. Das bietet sich gewiss immer dann an, wenn sie beispiel­sweise einen 5‑Minuten-Rhyth­mus gener­ieren wollen oder Sie lieber mit reinem Code als mit diversen Mausklicks arbeit­en.

▲ nach oben …

3 Maschinen, 3 *.csv

In vie­len Fällen wird von der Maschi­nen-Steuerungssoft­ware jed­er einzelne Mas­chine auch ein getren­nter Fehler­bericht erstellt. Typ­is­cher­weise wird dann je Mas­chine für 1 Kalen­der-Tag (bis zu 24 Stun­den Ein­satzzeit) eine einzelne csv-Datei erstellt. Ich habe für Sie die gle­ichen Dat­en wie zuvor in jew­eils eine csv abge­spe­ichert und Ihnen hier als gepack­te zip-Datei zur Ver­fü­gung gestellt. Und in diesen (ent­pack­ten) Files sind auch Spal­ten-Über­schriften. Ide­al­er­weise wer­den Sie die drei Files in 1 getren­nten Ord­ner ent­pack­en, wo vorzugsweise auch nur diese Dat­en enthal­ten sind. Das erle­ichtert den Ein­stieg … 😛 

Apro­pos „Ein­stieg”: Dieser gestal­tet sich naturgemäß deut­lich anders als in der vorheri­gen Übung. Erstens sind es ja keine Excel- son­dern es sind reine Text-Dateien und zweit­ens ist es nicht nur ein einziges File son­dern es sind mehrere Dateien, die zu ein­er einzi­gen Abfrage zusam­menge­fasst wer­den sollen und müssen.

Ich gehe davon aus, dass die ent­pack­ten Dateien bere­its in dem ange­sproch­enen getren­nten Fold­er liegen. In Excel gehen Sie über Dat­en | Dat­en abrufen | Aus Datei | Aus Ord­ner und wählen Sie dann im Dia­log den entsprechen­den Ord­ner aus. Nach einem Klick auf Öff­nen erweit­ern Sie die Schalt­fläche Laden und wählen Laden in… Ide­al­er­weise Klick­en Sie auf Nur Verbindung erstellen und schließen dann auch diesen Dia­log. Öff­nen Sie nun die (noch) einzige Abfrage, welche typ­is­cher­weise den Namen des Verze­ich­niss­es hat.

Sie erken­nen eine Query, wo in der zweit­en Spalte (Name) der Name neb­st Erweiterung aller Dateien des entsprechen­den Verze­ich­niss­es angezeigt wird. Wenn auss­chließlich die drei Files mit den Fehler­mel­dun­gen enthal­ten sind, brauchen Sie an dieser Stelle nichts weit­er zu unternehmen. Andern­falls nutzen Sie die Möglichkeit, die gewün­scht­en Dateien nach den erforder­lichen Kri­te­rien zu fil­tern.

Erweit­ern Sie nun die erste Spalte Con­tent durch einen Klick auf die zwei nach unten zeigen­den Pfeile und über­prüfen Sie im Dia­log Dat­en kom­binieren, ob die Vorschau prinzip­iell dem entspricht, was Sie für die Auswer­tung als Basis­dat­en brauchen. Dass PQ ohne Ihr Zutun erken­nt, dass es sich um Semi­ko­la als Trennze­ichen han­delt und auch die Codierung (Dateiur­sprung) kor­rekt zuord­net ist eine feine Sache… 😉 

Nach dem Schließen des Dialogs per OK  wer­den sie erken­nen, dass im recht­en Seit­en­fen­ster diverse Schritte einge­fügt wur­den und bei den Abfra­gen im linken Seit­en­fen­ster auch ver­schiedene Abfra­gen automa­tisch erstellt wor­den sind. – In Stich­worten hier die weit­ere Vorge­hensweise, wie ich sie Ihnen empfehle:

• Löschen Sie die erste Zeile der Abfrage.
• Wählen Sie Start | Erste Zeile als Über­schrift ver­wen­den.
• Ändern Sie die Über­schrift der ersten Spalte auf (nur) Mas­chine.
• Ändern Sie den Daten­typ der Spalte Date auf Datum.
• Der gewün­schte Erfolg ist, dass jene Zellen dieser Spalte, die kein Datum enthal­ten, nun als Fehler (Error) deklar­i­ert sind.
• Die Spalte Date ist markiert; Zeilen ver­ringern | Zeilen ent­fer­nen | Fehler ent­fer­nen.
• In der Spalte Mas­chine die Endung .csv löschen und eventuell den _ durch ein Leerze­ichen erset­zen lassen. Bei­des geht beispiel­sweise über das Menü Trans­formieren.
• Die Spalte Time entwed­er in den Daten­typ Zeit kon­vertieren und dann nach dem Muster wie oben beschrieben weit­er­ma­chen oder die Spalte nach dem Zeichen : teilen (Beim äußer­sten linken Trennze­ichen) und die bei­den Spal­ten in Stunde und Minute umbe­nen­nen; der Daten­typ muss dann natür­lich noch auf Ganze Zahl angepasst wer­den.

Im Grunde genom­men haben Sie jet­zt eine Daten­lage, die Sie in prak­tisch gle­ich­er Form in der ersten Übung wiederfind­en kön­nen. Die restlichen Schritte sind dann auch so gut wie iden­tisch. Was da anders ist: Das durch die Mas­chine erfasste Datum ist hier noch in der Query enthal­ten und kann bei Bedarf mit gespe­ichert und aus­gew­ertet wer­den.

Ins­beson­dere für Monats-Auswer­tun­gen, wo dann ja die csv-Dateien viel­er Tage im Verze­ich­nis erfasst sind, kann das eine Über­sicht recht kom­fort­a­bel ermöglichen. – Bei der Gele­gen­heit meine Anmerkung: Sie kön­nen natür­lich bei Schließen & laden in… auch gle­ich die Möglichkeit wählen, die let­z­tendliche Auswer­tung über eine Piv­ot­Table mit all den schö­nen Möglichkeit­en zu machen.

▲ nach oben …

Rück­mel­dun­gen / Feed­back gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 2,50 € freuen … (← Klick mich!)