Hallo,
mikeleb hat geschrieben:
das Monatsende ist bereits erfasst
Ich glaube Du hattest meine Worte wohl nicht so richtig verstanden. Macht aber gar nix, da dies jetzt der folgende Beitrag das etwas genauer erklärt.
So, da ich nicht nur immer hier "nutzlos" herumquatsche, habe ich jetzt mal etwas an der Datei rumgebastelt.
Es ist doch immer das gleiche, da will man ja eigentlich nur ne Kleinigkeit ändern, und eh man es sich versieht sind schwupp-di-wupp die Stunden ins Land verstrichen. Aber egal, hauptsache ich habe ein Teilziel erreicht.
Also denn, hinein ins *vergnügen*.
Da ja der Plan besteht, pro Monat ein Monatsblatt zu haben, habe ich die Hilfsspalten ein wenig aufgeräumt.
Von ehemals 19 Spalten, AC und AE bis AV, sind jetzt nur noch 15 Hilfsspalten übrig geblieben. Durch das Um- und Aufräumen musste ich folgende Zellbereiche etwas ändern.
AE34:AE36
AF34:AF36
AI34:AI36
AJ34:AJ36
Das Ergebniss könnt ihr Euch ja selber anschauen.
Bis hier war das ja nicht so sehr problematisch.
Ach ja!
Das rumgeräume hatte unter anderem folgenden Grund.
4 doch recht lange Spalten weniger bei nur einem Blatt ist nicht wild. Aber bei 12 Blättern macht das dann doch immerhin 48 Spalten weniger. Und das würde dann in ungefähr ein zusätzliches Blatt ausmachen, was sich ja vermeiden lässt.
Als nächstes, und das hat bedeutend mehr Zeit verbraucht auch wenn es nicht so sehr den Anschein macht für das Ergebnis, habe ich mir gedanken über den "Stundenübertrag für den Folgemonat" gemacht. Hierbei hatte ich micht jetzt erstmal auf folgende Situation konzentriert.
Wie bekomme ich die Sonntagszeiten herausgefiltert, wenn der letzte Tag im Monat ein Samstag ist?
Hört sich eigentlich banal an. Um aber das auch gleich fleißig gegenzutesten, musste ich schon so einiges anstellen. Aber das Prinzip ist folgendes.
Als erstes musste ich irgendwie herausfinden welches die letzte benutze Zeile für "Nachtarbeit 22h - 5h +25%" in Spalte L, und welche die letzte benutzte Zeile für "Sonntag 50%" in Spalte M ist.
In der Makro Programmierung wäre das kein Problem, kenne ich zu genüge. Aber hier wird kein Makro verwendet, also musste ich auf eine einfache Formellösung zurückgreifen, die ich im Nähkästchen irgendwo herumfliegen habe.
Und hier ist die Formellösung.
Für die Spalre L.
Die Geschwiften Klammern {} werden nicht direkt von Hand eingegeben, da sie eine Matrixformel ist. Entweder im Funkstions-Assitenten (oder wie er auch immer heißt) das Kästchen "Matrixformel" aktivieren, oder die Eingabe mit "Strg" + "Umschalt" + "Enter" abschließen.
Wenn jetzt in genau dieser Spalte in der Zeile 33 ein Wert vorhanden ist, wird die dementsprechende Zeilennummer ausgegeben.
Okay, jetzt noch das gleiche für die Spalte M.
Auch hier wieder Matrixformel.
Und wenn hier z.B in der letzten Zeile 31 ein Wert steht, dann wird die dementsprechende Zeilennummer ausgegeben.
Dadurch sind die Formeln für die ersten beiden Bedingungen erstellt. Jedoch fehlt da noch etwas entscheidendes. Und zwar das hier.
Hiermit wird überprüft ob der letzte Tag eines Monats ein Samstag ist.
Nun müssen alle Bedingungen nur noch in eine Formel gepackt werden. Und dazu nehmen wir jetzt mal an, das die Matrixformeln wohl die dementsprechenden Spalten abfragen, aber die für Spalte L steht in AL38, und die für Spalte M steht in Spalte AM38. Und daraus ergibt sich dann folgende Formel.
Und warum steht dort das hier?
Nun ich hatte ja eben als Beispiel gesagt, das die erste Matrixformel die Zeilennummer 33 und die zweite Matrixformel 31 ausgibt. Und 33 ist nicht das gleiche wie 31. Und in folgedessen gibt diese Klammer als Wahrheitswert FALSCH zurück. Selbst wenn die erste Klammer für den Wochentag WAHR zurück gibt, so ergibt dann WAHR * FALSCH gleich FALSCH, und das heißt dann, es ist wohl Samstag es hat aber keine Fahrt in den Sonntag hinein gegeben.
Erst wenn alle beide Klammern WAHR zurück geben, ist es Samstag und es hat eine fahrt in den Sonntag hinein gegeben. Und dadurch wissen wir das wir etwas für den Folgemonat übertragen können.
Jedoch bringt uns das Ergebnis der Matrixformeln so nicht weiter. Aus der Zeilennummer müssen wir noch eine Adresse basteln um auf die dementsprechen Zelle zuzugreifen können.
Die Matrixformeln stehen in AL38 und in AM38.
Und deshalb setzen wir die folgende Formel in AL39
und die nächste in AM39.
Und nun geben uns diese beiden INDIREKT-formeln den Wert aus der dementsprechenden Zelle zurück. Dies hat aber den Nachteil, das sie immer einen Wert zurück geben auch wenn er vielleicht Null ist, egal ob Samstag oder nicht.
Dies ist ja jetzt auch nur als ein zwischenschritt anzusehen.
Da ich ja noch nicht wusste wo hin mit den Stunden für den Übertrag, habe ich das jetzt erstmal wie folgt vorübergehend gelöst.
Die eben erklärten Formeln befinden sich wohl alle in allen Beispielblättern, nur auf das Ergebnis greift der Folgemonat, also nicht der aktuelle Monat, drauf zu.
Wir befinden uns jetzt im Monatsblatt März. Und dort habe ich in V1 diese Formel drin.
Und in der Zelle W1 diese Formel.
Jetzt ergibt auch die vorige Formel
einen Sinn, warum ich da eine 1 eingetragen habe. Denn nur dann, wenn dort die 1 steht, weiss die Formel im Folgemonat das sie die Stunden als Übertrag nehmen darf, ansonsten gibts keinen Übertrag.
Wie vielleicht schon aufgefallen ist, habe ich die Datei um weitere Monatsblätter erweitert, es sind jetzt 4 Monate vorhanden, Januar bis April.
Im Blatt *Grundlagen* habe ich vorübergehend die Zelle G2 dazu genutzt, um das Startjahr einzugeben. Dies wird 4-Stellig als Zahl gemacht. Also kein Datum eingeben, sondern z.B. 2018, mehr nicht.
Die vier Monatsblätter erstellen jetzt das Datum von allein. Dazu greifen sie auf diese eben genannte Zelle zu.
Um mal selber zu testen wie die Datei sich bei 12 Monatsblättern verhält, einfach das Blatt *April* nach rechts kopieren und das Blatt dementsprechend umbennen. Und das so oft bis der Dezember erreicht ist. Und wie schon gesagt, das Datum ändert sich automatisch.
Ich habe extra kein aktuelles Jahr voreingestellt, wie z.B. 2018, sondern das Jahr 2015 ausgewählt, weil da nämlich der 28.02.2015 ein Samstag war. Das erschien mir Ideal um zu testen ob mein Übertragssystem funktioniert. Das gehörte nämlich auch dazu ein Datum zu finden, welches meine gestellten Bedingungen erfüllt.
So, jetzt habe ich wohl auch wieder viel gequatscht
, aber dafür ist im Anhang jetzt die überarbeitete Datei die meine Worte in der Tat vorführt.
Und immer daran denken: ich habe damit lediglich ein Teilziel erreicht. Der Übertrag von Stunden eines normalen Werktages ist noch nicht eingebaut.
Viel Spaß beim studieren und testen der Datei.
Gruß
balu