MS Excel, Nutzungszeiten mehrere Nutzer im Diagramm "stapeln"

[stonefred]

Ich habe eine Logdeite eines Floating Lizenzservers aufbereitet, d.h. ich habe folgende Infos zeilenweise:
Lizenzfeature, Nutzername, PC-Name, checkout, checkoutdatumzeit, checkindatumzeit, nutzungsdauer

Pro Lizenzfeature gibt es eine Anzahl verfügbarer Lizenzen, d.h. z.B. es gibt 5 Lizenzen Lizenzfeature1, die bis zu 5 Nutzer gleichzeitig nutzen können. Ich will in einem Zeitdiagramm die Peaks sichtbar machen, z.B. wenn an einem Tag 5 Nutzer gleichzeitig Lizenzfeature1 genutzt haben, dann soll die Kurve den maximalen Ausschlag haben und entsprechend heruntergehen, wenn 5 Nutzer abwechselnd Lizenzfeature1 genutzt haben. (Ein Nutzer kann die Lizenz am Tag so oft auschecken, wie er möchte, auch nur für ein paar Minuten.

Die Kurve wäre kein Problem, wenn sie nur einen Nutzer anzeigen würde, aber ich will alle gleichzeitigen Nutzer (z.B. aus 100) darstellen, um herauszufinden, ob genügend Floatinglizenzen da sind oder Lizenzen hinzugekauft werden müssen.

Technisch könnte man es auch so sehen, dass man pro Nutzer einer Lizenz die Nutzungszeiten auf einer Nullinie aufträgt, so dass bei gleichzeitiger Nutzung die Kurve ansteigt. Das wäre bei entsprechender Genauigkeit möglich, z.B. bei 100 Usern im Logfile 100 Durchläufe. Ich würde für das Diagramm eine Hilfstabelle mit den Werten bauen mit 5 Zeilen für 5 vorhandene Lizenzen von Lizenzfeature1 und entsprechend der Genauigkeit die Spalten pro Tag, also bei einer Genauigkeit pro Minute 24h x 60min = 1.440 Werten von 0-5 pro Tag (!) in der Hilfstabelle. Ich würde das per Powershell in eine CSV-Datei bringen und in die Hilfstabelle in Excel importieren. Das aufbereitete Logfile des Lizenzservers geht über mehrere Monate, das kann doch nicht meine Lösung sein. Es muss "eleganter" möglich sein.

Wie nennt man diese Art Kurve und wie erstellt man sie mit Excel ohne diese aufwendige Hilfstabelle?

 

[Haskelltier]

Es geht besser, ob das allerdings mit Excel (einfach) umzusetzen ist, weiß ich nicht. Vermutlich nicht, aber ich nutze auch kein Excel, von daher, keine Ahnung.

Das Vorgehen sieht ungefähr wie folgt aus:

Zuerst einmal erstellst du eine Liste für jedes Lizenzfeature, in der du nur die Einträge mit dem entsprechenden Lizenzfeature einträgst. einfach nur filtern.

Das folgende wiederholst du für jede dieser Teillisten:

Du nimmst die Zeitpunkte aus der Spalte „checkindatumzeit“ und „checkoutdatumzeit“ und trägst sie in eine gemeinsame Spalte „zeitpunkte“ ein. Du ergänzt diese Spalte um eine weitere Spalte, in der du eine 1 einträgst, wenn der Zeitpunkt aus „checkindatumzeit“, und eine -1 falls er aus „checkoutdatumzeit“ stamm.
Der springende Punkt ist nun, dass sich die Nutzerzahl nur an genau diesen Zeitpunkten ändert (und zwar immer um +1 oder -1, je nach dem ob der Punkt aus checkin oder checkout stammt). Du kannst diese Tabelle nun nach den Zeitpunkten ordnen. Sei t_i der i-te und t_(i+1) der (i+1)-te Zeitpunkt in der geordneten Tabelle. Dann ist die Nutzerzahl im Intervall [t_i, t_(i+1)] einfach bestimmbar, indem du die Spalte mit den 1-en und -1-en der geordneten Tabelle vom 1-ten bis i-ten Eintrag aufsummierst. Das machst du nun für alle Intervalle und notierst in einer weiteren Tabelle hübsch Anfangs- und Endzeitpunkt der Intervalle und diese Nutzerzahl. Die Spalte mit den Anfangszeiten ist schlicht der Eintrag t_1 bis t_(n-1) aus der sortierten Liste, die Spalte mit den Endzeiten schlicht die Einträge t_2 bis t_n, wobei n die Zahl der Einträge ist.

Um nun zu bestimmen wie viele Nutzer an einem bestimmten Tag maximal gemeinsam ein bestimmtes Lizenzfeature genutzt haben, filterst du aus der Intervallliste alle Zeilen, deren Anfangszeit vor 00:00 des Folgetags und deren Endzeit nach 00:00 dieses Tags liegen (trifft beides zu, liegt ein Teil des Intervalls in dem Tag). Das Maximum der Nutzerzahlen dieser gefilterten Liste ist dann der gesuchte Wert. Das machst du für alle Tage und kannst die Werte gegen die Tage plotten.
Das sollte die Zahl der Zwischenwerte erheblich reduzieren.

 

[stonefred]

Du nimmst die Zeitpunkte aus der Spalte „checkindatumzeit“ und „checkoutdatumzeit“ und trägst sie in eine gemeinsame Spalte „zeitpunkte“ ein.

Weisst Du eigentlich, was ich veranstaltet habe, um diese gemeinsame Spalte Zeitpunkte in Checkout und Checkin zu trennen und die Nutzungsdauer pro User herauszufinden und in jeweils eine Zeile zu bringen? Die Liste war ursprünglich genau so, wie Du vorschlägst.

Aber Deine Idee ist so einfach wie genial, ich werde das gleich mal ausprobieren.

 

[stonefred]

Das ist so geil. Ich habe die Nutzung zwar nicht per Excel Formel zählen können, da die Formeln auch in die gefilterten Zeilen referenzieren, aber ich hatte das Powershell eh schon fertig und das zählen lassen. Anschliessend habe ich eine Pivottable gemacht und darauf ein 3D-Balkendiagramm, so dass ich jetzt jeden Tag für jedes Feature einen Balken habe. Danke! Die einfachsten Dinge, auf die man nicht kommt.