PQ: Anwesenheits-Tage multipel berechnen (1)

Tage Pro Monat berechnen

Servus,
ich habe schon wild gegooglet aber bin leider nicht fündig geworden.

Ich muss eine Übersicht führen wie viele Tage Mitarbeiter im Monat vor Ort sind.
Aktuell gebe ich das Beginn und Enddatum ein und trage pro monat die Tage ein.
Gibt es eine Formel die mir die Arbeit beim Eintragen der einzelnen Tage pro Monat abnimmt ?
Hier geht es nur um die Tage gesamt, egal ob Wochenende oder Feiertag.
Da der Beginn und das Ende nicht immer am 01 bzw 30/31 eines monats sind, können sich hier evtl fehler einschleichen.

Als Beispiel habe ich eine vereinfachte Version der Liste im Anhang beigefügt.

Die im Foren-Beitrag ange­hängte Datei habe ich etwas ergänzt und Ihnen hier zur Ver­fü­gung gestellt. Die gelb-braun hin­ter­legten Bere­iche entsprechen dem Orig­i­nal-File. Lei­der wird es schein­bar ein Zeichen der Zeit, dass sich Fragesteller in Foren extrem wenig Mühe geben, eine wirk­lich gute Vor­lage für die Helfer zu „liefern”. Schade… 🙁 – Und noch ein Hin­weis von mein­er Seite: Bitte gehen Sie exakt nach meinen Anweisun­gen vor. Ich weiß, dass dieses oder jenes in Ihren Augen vielle­icht nicht richtig ist; ich habe ver­sucht, „Fak­tor Men­sch” (also typ­is­che Fehler) einzubauen und diese dann im End­ef­fekt zu korigieren. Das bedeutet aber keines­falls, dass die hier gezeigte Vorge­hensweise (ohne Berück­sich­ti­gung der Fehler) das „non plus ultra” ist. PQ-Profis wür­den manch­es anders lösen, aber ich spreche in erster Lin­ie typ­is­che Anwen­der mit einem gewis­sen Leis­tungs­stand an.

Die erste Antwort im Forum (von steve1da) zeigt einen Weg auf, der imho mit ein­er rel­a­tiv ein­fachen Formel auf der Basis Plain Excel weg­weisend ist. Die weit­er aufge­führten Möglichkeit­en sind nicht immer für typ­is­che User auf den ersten oder auch zweit­en Blick nachvol­lziehbar. 😉 Ich habe einen Weg gesucht und gefun­den, das Prob­lem mit Pow­er Query zu lösen. Im ersten Teil des Beitrags habe ich mich exakt an die Vor­gaben des Fragestellers gehal­ten. Im zweit­en Teil habe ich das Fein­tun­ing dieser Lösung vorgenom­men und die Auf­gabe etwas erweit­ert, indem ich Woch­enen­den und Feiertage „aus­geklam­mert” habe. Das dürfte in vie­len Fällen der Real­ität eher entsprechen. Die Vor­gabe des Hil­fe Suchen­den kön­nte sich auf Per­son­alein­sätze in weit ent­fer­n­ten Gebi­eten beziehen, wo eine Heim­fahrt an Woch­enen­den bzw. auch an Feierta­gen nicht sin­nvoll ist.

Spätestens jet­zt soll­ten Sie meine Muster-Datei herun­ter­laden und erst ein­mal analysieren. Selb­st wenn Sie der Mei­n­ung sind, dass ein ander­er Auf­bau der Lösung sin­nvoll sein kön­nte (was auch auf mich zutrifft), der Fragesteller möchte das Ergeb­nis genau dieser Form haben und dem soll Genüge getan wer­den. Und bei der Gele­gen­heit ein weit­er­er Hin­weis: Mitunter scheint sich ein Fehler oder Schreibfehler eingeschlichen zu haben. Belassen Sie es ein­fach dabei; erst wenn Sie an Ende des 2. Teils noch dieses oder jenes verbesserungswürdi­ges ent­deck­en, bitte eine Nachricht an mich. Dieses oder jenes habe ich als typ­is­che Fehler einge­baut und Ihnen dann auch den Weg aufgezeigt, wie das Ganze wieder zu bere­ini­gen ist. 😉

Markieren Sie zu Beginn die Dat­en aus dem Bere­ich A1:D5 und importieren Sie diese Werte anschließend in den Pow­er Query-Edi­tor. Achtung: Da die Zeile 2 leer ist, wird Ihnen beim Erstellen der Tabelle vorgeschla­gen, dass die Tabelle keine Über­schriften hat; hier soll­ten Sie das Häkchen bei Tabelle hat Über­schriften unbe­d­ingt set­zen. Im näch­sten Schritt wer­den Sie auf beliebigem Wege alle leeren Zeilen ent­fer­nen (auch wenn es nur die Zeile 1 ist), den Daten­typ der Spal­ten Beginn und Ende auf (nur) Datum anpassen und anschließend Datei | Schließen & laden in… | Nur Verbindung erstellen. Der erste Schritt ist getan.

Da ja für jeden Monat des Jahres eine eigene Spalte erstellt wer­den soll, muss diese irgend­wie gener­iert wer­den; nicht für jeden Monat wur­den Dat­en erfasst. In Plain Excel ist das naturgemäß sehr „ele­gant” lös­bar: Jan­u­ar in eine Zelle eingeben und dann 11 weit­ere Zellen nach rechts ziehen, schon existiert die Auf­stel­lung. 😉 Möcht­en Sie das in Pow­er Query abar­beit­en, hil­ft Ihnen gewiss dieser Beitrag unseres Blogs. Und ja, das sind 12 Zeilen, aber das ist gar nicht ein­mal so schlecht, wie es vielle­icht zu Anfang scheint. Es ist sog­ar genau das, was zielführend ist.

Sie haben jet­zt also eine weit­ere Abfrage Monat­sna­men, wo alle Monate von Jan­u­ar bis Dezem­ber als Text in der kor­rek­ten Rei­hen­folge aufge­führt sind. Auch diese Abfrage über Schließen & laden in… | Nur Verbindung erstellen sich­ern. Hin­weis: Ohne die Tabelle mit jedem einzel­nen Monate des Jahres würde Pow­er Query für die Aus­gabe der Dat­en nur jene Monate ver­wen­den, die auch Dat­en enthal­ten, also min­destens ein Mitar­beit­er tätig war.

Öff­nen Sie nun wieder die erste Abfrage (Tabelle1). Es soll für jeden Mitarbeiter/jeden Daten­satz eine Liste jen­er Tage erstellt wer­den, wo eine Anwe­sen­heit gegeben war. Dazu gehen Sie so vor:

• Spalte hinzufü­gen | Benutzerdefinierte Spalte und tra­gen Sie bei Neuer Spal­tenname beispiel­sweise Ein­satz­tage ein.
• Im Feld Benutzerdefinierte Spal­tenformel geben Sie nach dem = diese Formel unter Beach­tung der exak­ten Groß- Klein­schrei­bung ein:
  List.Dates([Beginn], Duration.Days([Ende]-[Beginn])+1, #duration(1,0,0,0))

So sollte Ihr Dia­log vor dem Klick auf OK ausse­hen

• Erweit­ern Sie die Spalte Ein­satz­tage durch einen Klick auf den Dop­pelpfeil  und wählen Sie im Dia­log Auf neue Zeilen ausweit­en.

Sie haben jet­zt für jeden Mitar­beit­er für jeden Tag der Anwe­sen­heit eine einzelne Zeile, also einen eige­nen Daten­satz. Gedanklich soll­ten Sie nun einen Schnitt machen, denn ab hier unter­schei­det sich die Vorge­hensweise etwas, wenn sie statt aller Tage des Bere­ichs Beginn bis Ende nur die typ­is­chen Arbeit­stage, also ohne Woch­enende und ohne Feiertage berech­nen wollen. Mein Vorschlag: Merken Sie sich die Posi­tion des Ein­trags im recht­en Seit­en­fen­ster, da kön­nen Sie später die eine oder andere Funk­tion­al­ität noch ein­fü­gen.

Hier geht es erst mal mit der vom Fragesteller definierten Auf­gabe weit­er, also die Anwe­sen­heit­stage für jede Per­son pro Monat und entsprechend auch dem Gesamt-Zeitraum zu berech­nen.

• Wenn Sie eher der „Kon­troll­f­reak” sind, dann wer­den Sie erst ein­mal die Spalte Ein­satz­tage und dann über Spalte hinzufü­gen | Datum | Monat | Name des Monats gehen, um den entsprechen­den Monat­sna­men als Text zu erstellen. Sie kön­nen Pow­er Query aber auch ver­trauen und die Spalte Ein­satz­tage direkt über Trans­formieren | Datum | Monat | Namen des Monats in den Text des Monats umwan­deln.
• Um die Per­for­mance bei umfan­gre­icheren Dateien zu erhöhen, löschen Sie nun alle Spal­ten außer Name, Vor­name und Ein­satz­tage; nur diese wer­den noch gebraucht.
• Markieren Sie nun (in dieser Rei­hen­folge) die Spal­ten Name, Vor­name, Ein­satz­tage und wählen Sie per Recht­sklick in eine der Über­schriften den Kon­textmenü-Punkt Grup­pieren nach…

Die Rei­hen­folge im oberen Bere­ich sollte stim­men! Vor­gabe übernehmen und OK

Das Textfeld Neuer Spal­tenname noch anpassen (siehe Abbil­dung) und dann OK. Das Ergeb­nis sollte prinzip­iell überzeu­gen:

Prinzip­iell ein Ergeb­nis, wie es auch aus­sagekräftig ist

Spätestens jet­zt soll­ten Sie den Namen der Spalte Ein­satz­tage in Ein­satz­monate ändern, was ja eher den Gegeben­heit­en entspricht. – Auf den allerersten Blick scheint es vielle­icht so zu sein, dass die Monat­sna­men etwas durcheinan­der sind; aber nach ein­er kurzen Analyse wer­den Sie fest­stellen, dass inner­halb jedes Mitar­beit­ers die Rei­hen­folge in kor­rek­ter kalen­darischen Abfolge gegeben ist.

Jet­zt geht es „nur” noch darum, diese Dat­en in die gewün­schte Form der Kreuzta­belle zu über­tra­gen. Meine erste Idee, die Abfrage Monat­sna­men über Trans­formieren | Ver­tauschen in die hor­i­zon­tale Form zu „kip­pen”, habe ich rasch ver­wor­fen. Der Aufwand des kor­rek­ten Zusam­men­führens wäre zu groß. Darum zeige ich Ihnen einen anderen Weg auf.

Wech­seln Sie zu Abfrage Monat­sna­men. Spalte hinzufü­gen | Indexs­palte | Von 1 um einen den Monat­sna­men entsprechen­den numerischen Wert zu erzeu­gen. Ein „nor­maler” Index würde ja mit 0 begin­nen, was für Pow­er Query natür­lich kein Prob­lem darstellen würde, uns Anwen­der aber jedes Mal zum Umdenken ani­miert; wir sind eher die „echt­en” numerischen Beze­ich­nun­gen der Monate gewohnt. 😉

Im näch­sten Schritt gehen Sie über Start | Kom­binieren | Abfra­gen zusam­men­führen und wählen Sie im unteren Bere­ich des Fen­sters die Abfrage Tabelle1; verknüpfen Sie die bei­den Abfra­gen dergestalt, dass die bei­den Spal­ten mit den Monat­sna­men markiert sind und ver­wen­den Sie die Vor­gabe bei Join-Art.

Auch wenn die Über­schriften nicht iden­tisch sind, klappt das, wenn der Daten­typ gle­ich ist

Erweit­ern Sie nach dem OK die Spalte Tabelle1. Anschließend sortieren Sie die Spalte Index, löschen die Spalte Column1 und Sie haben erst ein­mal ein Ergeb­nis. Jet­zt aber kommt die 100.000 €-Frage: Obwohl Pow­er Query alles kor­rekt berech­net hat, ist das Ergeb­nis nicht dem eigentlich gewün­scht­en Ziel entsprechend; warum nicht? 😕

Diese Frage werde ich im näch­sten Teil dieser Auf­gaben­stel­lung beant­worten und natür­lich auch aufzeigen, wie Sie den „gordis­chen Knoten” durch­schla­gen (sprich: diese oder jene Lösung erar­beit­en) kön­nen und wie Sie die Dat­en das gewün­schte Ziel-For­mat der Kreuzta­belle brin­gen wer­den.

Ver­suchen Sie aber gerne schon ein­mal an dieser Stelle für das gegebene For­mat die Gesamt-Tage der einzel­nen Monate zu berech­nen. Gerne mit oder ohne Berück­sich­ti­gung des logis­chen Fehlers in der Abfrage. In jedem Falle sollte das über Grup­pieren nach… gehen, eine andere Lösung ist auch gut mach­bar. Ach ja, mein Ergeb­nis (mit dem noch nicht ganz korrekten/erwünschten Ergeb­nis) kön­nen Sie hier herun­ter­laden.

▲ nach oben …

Der 2. Teil dieses Beitrags wird/wurde 14 Tagen nach der Veröf­fentlichung online gestellt und hat diesen Link.