Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Tabelle Auswertung in "rollierender" Art mit einer Funktion oder SQL?
#1
Hallo zusammen,

Ziel meiner Frage ist es eine Lösung für mein Problem zu finden...

Gegeben ist eine große Tabelle (50.000 Reihen), die die Arbeitszeitenbuchungen der MA beeinhaltet von Januar-September. (Siehe Screenshot als Beispiel, der MA "Ben" zeigt auch schon gewisse Besonderheiten im Datensatz auf  Confused ).

So leider haben manche MA die Arbeitszeitregeln nicht eingehalten und meine Aufgabe ist herauszufinden, wer wann wo und wie.

Mithilfe von Excelfunktionen habe ich schon viele Regelungen abgedeckt, allerdings beiße ich mir an dieser Regelung die Zähne aus:

1.) Mehrarbeit: Als Mehrarbeit gelten die Stunden, die über 48h / Woche bzw. 8h pro Werktag (Mo-Sa) hinausgehen.

2.) Im Durchschnitt darf keine Mehrarbeit innerhalb eines halben Jahres geleistet werden. D.h. etwaige Mehrarbeit ist innerhalb von 24 Wochen bzw. eines halben Jahres (rollierend) auszugleichen.




Hat jemand einen Ratschlag wie ich das ermitteln kann?

Bisher habe ich entweder die Tabelle um eine Hilfsfunktion erweitert (z.B. hat der MA an einem Sonntag gearbeitet? --> Datumsüberprüfung für Sonntage) und diese Hilfsspalte dann mithilfe der Microsoft Query eine Abfrage direkt in der Excel gebaut und diese mir dann als Tabelle ausgegeben lassen. Ich habe es auch schon mit Pivottabellen probiert, bin aber auch gescheitert.

Wenn das Jahr bis jetzt 36 Wochen hatten, müsste ich ja für die Mehrarbeit alle Kombinationen für Woche 1-24; 2-25; 3-26; 4-27 etc. usw. für die rollierenden 24 Wochen durchprobieren und da die Durchschnittsstunden ermitteln? Leider scheitere ich bis jetzt schon an der Aggregierung der Stunden pro Woche für jeden MA...

Also wäre für jede Hilfstipp dankbar! :)

Beste Grüße
an Alle :D

(ich hoffe Ihr seht meinen Screenshot?)

[
Bild bitte so als Datei hochladen: Klick mich!
]


Angehängte Dateien Thumbnail(s)
   
Antworten Top
#2
Hallöchen

1) Du kannst mit der Funktion Kalenderwoche selbige ermitteln und per SUMMENPRODUKT oder SUMMEWENNS die Wochenstunden zusammenzählen
2) würde z.B. mit einer Hilfstabelle gehen wo Du die Monatssummen der Mitarbeiter listest. Die Monate gibst Du dort z.B. in Form des Datums ein und kannst z.B. per Zellformat in 01/2020 usw. die Anzeige ändern - hab ich hier nicht.

Arbeitsblatt mit dem Namen 'Tabelle1'
ABCDEFGH
1Willi01. Jan12501. JanWilli100
2Bald05. Jan12501. JanBald125
3Willi09. Jan22501. FebWilli50
4Bald13. Jan32501. FebBald102
5Willi17. Jan325
6Bald21. Jan425
7Willi25. Jan425
8Bald29. Jan550
9Willi02. Feb525
10Bald06. Feb677
11Willi10. Feb725
12Bald14. Feb725

ZelleFormel
C1=KALENDERWOCHE(B1;11)
H1=SUMMENPRODUKT((DATUM(JAHR(B:B);MONAT(B:B);1)=F1)*(D:D)*(A:A=G1))
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Antworten Top
#3
Hallo Hallo,

der Summewenns Tipp war schon mal Gold wert, das hat jetzt geklappt. Danke dafür!

Hättest du auch eine Idee, wie ich die 24 Wochen Zeiträume betrachten kann? :100: 

Schöne Grüße :19:
Antworten Top
#4
Hallöchen,

wenn Du das so aufbaust wie in meinem Beispiel kannst Du ab Februar auch mit SUMMEWENN oder SUMMENPRODUKT arbeiten. Als Bereich gibst Du die  Zellen von der ersten Zeile, z.B. F$1 - wichtig ist hier der  $ vor der 1 - bis zur Zeile ein.

Arbeitsblatt mit dem Namen 'Tabelle1'
FGHI
101. JanWilli100100
201. JanBald125125
301. FebWilli50150
401. FebBald102227
501. OktWilli555555

ZelleFormel
I1{=SUMMENPRODUKT(($G$1:G1=G1)*(MONAT($F$1:F1)>(MONAT(F1)-7))*($H$1:H1))}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg

Problematisch wird es mit dem Ansatz nur, wenn die Daten über den Jahreswechsel gehen. Dann würde der Monat nicht ausreichen, man müsste das komplette  Datum vom entsprechenden Monat benutzen. Ich hab hier übrigens 6 Monate genommen und nicht 24 Wochen. Unser Jahr hier in Thüringen hat um die 52, also 26 im Halbjahr Smile
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste