Clever-Excel-Forum

Hallo,

nach längerer Zeit der Abwesenheit melde ich mich wieder mit einer kniffligen Aufgabe.

Es geht (wieder) um Arbeitsstunden, Urlaubsstunden, freie Stunden usw.

Ich habe eine Beispieldatei erstellt mit einer Datenbank deren Inhalt monatlich per Makro aus monatlichen Blättern zusammenkopiert wird. Die Eingaben der AN werden kopiert.
Diesen ersten Schritt lasse ich erst mal außen vor, das ist der leichteste Teil der Aufgabe.

Jedes Zeichen entspricht einer Stunde:
X = 1 Arbeitsstunde
C = 1 Urlaubsstunde
RS = 1 Stunde Versammlung
T = 1 Stunde Tutorat
und es gibt noch andere.

Meine eigentliche Aufgabe ist eine doppelte und sie ist entstanden durch Aenderungen im Kollektivvertrag.
Für diese doppelte Aufgabe interessieren eigentlich nur die leeren Zellen, weil es um Freizeit geht: jede leere Zelle = 1 freie Stunde.

1. Erste Regel:
Jeder Arbeitnehmer (AN) hat Recht auf eine ununterbrochene Freizeit von 44 Stunden pro 7 Tage.
Nach einer Auszeit von 44 St. muß die nächste binnen der nächsten 7 Tage erfolgen.
Diejenigen AN die während 8 7-Tage-Perioden, egal ob sukkzessiv oder nicht, nicht diese Auszeit hatten, erhalten als Kompensation einen zusätzlicchen Urlaubstag.

Aufgabe die sich daraus ergibt: die 7-Tage-Perioden zählen, wo es keine 44 St. frei gab

2. Zweite Regel:
Die Auszeit von 44 Stunden muß wenigstens 20 mal pro Jahr mit dem Wochenende übereinstimmen, wobei die Periode die als WE angesehen wird sich von Samstags 6:00 bis Dienstags 6:00 erstreckt.

Aufgabe: die WE zählen während denen der AN weniger als eine ununterbrochene Auszeit von 44 St. hatte und sie über das Jahr addieren.

Im angehängten Blatt sind die WE Perioden gelb eingefärbt.

Ich weiß nicht, ob diesen Aufgaben per reiner Formellösung beizukommen ist, oder ob besser VBA bemüht wird.

Mit freundlichen Grüßen
Alooha

Bitte diese angehängte Datei ignorieren; es gibt Fehler drin, die ich korrigiert habe.
Hier die neue Datei

Hallöchen,

ich denke, da solltest Du VBA verwenden. Per Formel bekommst Du eventuell nicht die verschiedenen Bedingungen geregelt. Zumindest zählen könnte man die "Blöcke" nach Länge, dann hört es aber langsam auf

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
1	Willi	1	0
2		2	1
3		3	0
4		4	2
5
6	Egon
7	Erna
8
9
10	Hugo
11
12
13
14
15	Otto
16	Kar

Zelle	Formel
C1	{=SUMME(((KGRÖSSTE((A1:A100<>"")*ZEILE(1:100);ZEILE(1:99))-KGRÖSSTE((A1:A100<>"")*ZEILE(1:100);ZEILE(2:100))-1)=B1)*1)}
C2	{=SUMME(((KGRÖSSTE((A1:A100<>"")*ZEILE(1:100);ZEILE(1:99))-KGRÖSSTE((A1:A100<>"")*ZEILE(1:100);ZEILE(2:100))-1)=B2)*1)}
C3	{=SUMME(((KGRÖSSTE((A1:A100<>"")*ZEILE(1:100);ZEILE(1:99))-KGRÖSSTE((A1:A100<>"")*ZEILE(1:100);ZEILE(2:100))-1)=B3)*1)}
C4	{=SUMME(((KGRÖSSTE((A1:A100<>"")*ZEILE(1:100);ZEILE(1:99))-KGRÖSSTE((A1:A100<>"")*ZEILE(1:100);ZEILE(2:100))-1)=B4)*1)}

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

Hallo,
vielen Dank!

Nur, leider verstehe ich die Formeln nicht und weiß nicht, wie sie auf meine Situation anwenden, die sich etwas anders darstellt als dein Beispiel. Da gibt es keine Zahlen (außerin Kombination mit einem Buchstaben), sondsern nur Buchstaben.

Hallöchen,

In Spalte A, wo hier die Leerzeilen gezählt werden, gibt's keine Zahlen..

Vielen Dank für deine Mühe, aber ich sehe nicht, wie ich deinen Vorschlag auf meine Situation anwenden soll, da die von dir dargestellte Situation eine ganz andere ist.
Mir geht es darum, zu ermitteln, wie oft jede Person am WE (2. Regel) 44 Stunden am Stück frei war, und dazu muß ich ermitteln, welche konsekutiven Zellen zwischen Sa. 6 Uhr und Di. 6 Uhr leer sind, sowie, ob es 8 7-Tage Perioden gibt, wo sie keine 44 Stunden pro 7 Tage frei war 1. Regel).
Und das muß ich per Formel oder VBA in den Spalten B:AB recherchieren.

Hallo Alooha,

anbei meine Lösung, teils mit VBA, sofern ich das richtig verstanden habe. (s. Mappe)

Sigi

Hallo Sigi,

vielen Dank für deine Lösung.
Leider kann ich im Augenblick nicht überprüfen, ob und wie sie funktioniert, da ich keine gültige Lizenz von Excel mehr habe.
Da ich in letzter Zeit Excel so gut wie nicht gebraucht habe, habe ich sie bisher nicht verlängert, und mich mit LibreOffice begnügt, das zu einem hohen Grade kompatibel ist, aber eine andere Programmiersprache benutzt als VBA.

Ich werde die Lizenz baldigst (heute) verlängern und dann sehen, wie deine Lösung funktioniert.

MfG
Alooha

Hallo,

das hat dann doch gedauert.

Deine Lösung geht in die richtige Richtung, aber was fehlt ist das Einbeziehen des Namens in Spalte C. Wenn ich an einem WEtag einen anderen Namen eingebe, ändert sich nichts am Resultat. In der Praxis kommt das zwar nicht so vor, aber beim Wechsel von einer Person zu der nächsten kann und wird es vorkommen. Und die Werte die deine Formeln ergeben müssen ja auch der richtigen Person zugeordnet werden.

Außerdem sind die Formeln in  AE, jeweils am Dienstag, manuell eingegeben worden. Das müsste natürlich automatisiert geschehen.
Mir erschließt sich nicht die Rolle der Formeln in AC, da sie nicht in die Zahlen in AE einfließen.

Du orientierst dich nach der Farbe der Zellen, aber in der Wirklichkeit gibt es die nicht; ich habe sie nur zur Illustration gefärbt, d.h. das Suchkriterium soll nicht die Farbe sein sondern der Bereich der eingefärbt ist, also Sa. 6:00 bis Di. 6:00. Theoretisch könnte ich sie natürlich per Bedingter Formatierung einfärben, aber das macht ja keinen Sinn.

Und die ganze Datenbank ist zusammenhängend, ohne Leerzeilen.

MfG
Alooha

Hallöchen,

eventuell könntest und solltest Du bei Personalwechsel eine neue Zeile für den neuen MA verwenden und die Zeile des alten erst mal nicht weiter nehmen.