Excel 2003 / graph. Auswertung Mitarbeiteranwesenheiten

Question

Hallo Fachleute,

da hier so fitte Leute hart "arbeiten" und das auch noch ehrenamtlich ;-) wage ich es nochmal eine für mich viel zu komplizierte Auswertung zu erfragen:

Tabelle (Konglomerat aus vielen Arbeitszeiterfassungsbögen) mit folgendem Aufbau:

Spalte A "Tagesdatum" (Formatierung Standard) = 20101101 (hier bspw. der 01.11.2010)

Spalte B "Beginn1" (Formatierung Benutzerdefiniert h:mm) = 7:30
Spalte C "Ende1" (Formatierung Benutzerdefiniert h:mm) = 10:00
Spalte D "Beginn2" (Formatierung Benutzerdefiniert h:mm) = 10:15
Spalte E "Ende2" (Formatierung Benutzerdefiniert h:mm) = 13:00
Spalte F "Beginn3" (Formatierung Benutzerdefiniert h:mm) = 13:30
Spalte G "Ende3" (Formatierung Benutzerdefiniert h:mm) = 16:15

Spalte I (Formatierung Zahl) = 300001 (soll die Personalnummer sein)

Die Spalten B bis G sind jeweils Anfangs- und Endzeiten eines Mitarbeiters am entsprechenden Tag. In diesem Beispiel hat der Mitarbeiter zwischen 10:00 und 10:15 und zwischen 13:00 und 13:30 die Arbeit unterbrochen.

Jetzt möchte ich eine graphische Auswertung (am besten über PivotTabelle und PivotChart) in der ich an einem beliebigen Wochentag eine Tageszeit anklicken kann und mir angezeigt wird, wie viele Mitarbeiter zu diesem Zeitpunkt arbeitend anwesend waren, d.h. sich nicht in Pause befunden haben.

Könnte man (ich kann´s bestimmt nicht :-) ) das mit Bordmitteln von Excel überhaupt umsetzen oder braucht man eine professionelle Personal-Software?

Vielen Dank im Voraus

LG

Lucie

Answer 1

Hi Lucie,

ich habe zwar eine kleine aber aufwendige Idee im Kopf, bin mir aber selbst nicht sicher, ob ich die überhaupt umgesetzt bekomme.

Mit Pivot kenne ich mich eigentlich ganz gut aus, aber ich denke damit wird es nur unter gewissen Vorgaben funktionieren kann.

Ein Problem könnte nämlich werden... wenn da Minutengenau gerechnet wird und nicht z.B. in Viertelstd.-takten. Für diese Info wäre ich Dir schon mal sehr verbunden, da das meine Ausarbeitung evtl. dann möglich machen würde. Bei minutengenauer Berechnung müsste ich dann streiken... :-(

Problem bei Pivot... wenn Du z.B. 13.00 Uhr eingibst, dann kann er nur die filtern, die auch 13 Uhr als Info da stehen haben. Und wenn der Zeitrahmen von 11.30 Uhr bis 15.00 Uhr geht, dann steht da nirgends 15 Uhr. Von daher würde ich mir eine Tabelle bauen, in der die Viertelstd.-takte enthalten sind und die, die innerhalb einer Zeitspanne liegen, markieren lassen.

Danach könnte man danach suchen lassen. Aber sicherlich eher dann als Makro und weniger als Pivot. Evtl. haben aber die Makrospezialisten bereits eine Idee im Kopf und können Dir auf eine einfachere Art helfen.

In diesem Sinne... have a nice Day... Gruß Helmut

Answer 2

Hallo Lucie,

hab mal was probiert:

In Zelle I1 trägst Du das gesuchte Datum ein (wie in Spalte A, in Zelle J1 die gesuchte Zeit.

In J2 trägst Du die Formel ein und ziehst Sie dann runter, solange deine Tabelle ist.

=SUMMENPRODUKT(($J$1>=B2)*($J$1<=C2)*($I$1=A2))+SUMMENPRODUKT(($J$1>=D2)*($J$1<=E2)*($I$1=A2))+SUMMENPRODUKT(($J$1>=F2)*($J$1<=G2)*($I$1=A2))

Sobald Du oben den Datums- und Zeitwert (Zellen I1, J1) deiner Suche einträgst, wird in der Spalte J hinter dem entsprechenden Mitarbeiter eine 1 eingetragen. Jetzt kannst Du filtern, oder ein Diagramm basteln.

Ich weiß, ist nicht die eleganteste Lösung, müsste aber funzen.

Gruß Maik

Answer 3

Hallo Maik,

Die Formel ist super!

Beim testen ist mir aufgefallen, wenn z.B. bei Ende 1 und Beginn 2 10:00 h steht wertet er 2 aus, so als wäre der Mitarbeiter um 10:00 h 2 mal dagewesen.
Ich würde vorschlagen, bei der Eingabe von "Beginn 2" 10:00:01 einzugeben, damit wäre das Problem gelöst, wenn in J1 10:00 eingegeben wird.
Das gleiche natürlich bei "Beginn 3"

Gratuliere nochmals zu dieser gelungenen Formel!


schöne Grüße an Alle

Paul1

Answer 4

@ Paul1

natürlich wäre es korrekter die Differenz im Sekundentakt zu vergleichen, ich bin nur davon ausgegangen, dass die Pausen in Minuten gezählt werden.

Gruß Maik

Answer 5

Hallo Maik,

Die Formel ist und bleibt super, denn wer rechnet schon in Sekunden.
Der Hinweis bezüglich der Zeiteingabe wäre eigentlich für Lucie gedacht, außerdem sieht man die 1 Sekunde gar nicht.

schöne Grüße an Alle

Paul1