Moin
Das neue Jahr hat begonnen und bringt auch viel neues. Bei mir in der Firma unteranderem auch eine neue Exceltabelle für unseren Stundennachweis. Da haben die mir aber doch eine Tabelle ohne irgendwelche Formeln für meinen Komfort geschickt. Soll ich auch noch die Stunden selber zusammenrechnen :D
Somit hab ich mir mal die Tabelle geschnappt und überarbeitet und will euch hier mal ein paar der Tricks die ich genutzt habe erläutern.
Inhaltsverzeichniss:
Automatische Berechnung von Überstunden
Datum automatisch ausfüllen: nach Oben zurück
Erstmal eine allgemeine Ansicht der Tabelle:
In dieser Tabelle muß man im Feld A1 nur ein Datum ala "1.1.2021" eintragen und schon werden die Zeilen für Datum bis zum letzten Tag im Januar ausgefüllt. (Füllt auch bei Monate mit weniger Tage nur bis zum letzten Tag im Monat aus. restliche Zeilen bleiben leer.)
Zuerst hab ich das Zellenformat von A1 geändert damit mir bei ein Datum nur Monat und Jahr ausgegeben wird. Das macht man einfach mit rechtsklick auf die Zelle und "Zellen formatieren" auswählen. Im folgenden Dialog kann man unter einige Formate auswählen oder auch eigene erstellen. Ein passende Format hab ich dort nicht gefunden und somit unter benutzerdefiniert ein neues erstellt mit "MMMM JJJJ". Damit wird nun Monat und Jahreszahl ausgeschrieben.
Für die Zeilen A4 bis A34 hab ich ein fertiges Datumformat genommen. In der Zelle A4 übernehmen ich mit "=A1" einfach die Eingabe in A1. Bei den Zellen A5 bis A30 addierei ich einfach 1 zu der vorrigen Zeile "=A4+1" Bei den Zellen A31 bis A34 wird es etwas komplizierter. Ich will ja nicht das da Tage vom nächsten Monat erscheinen, also muß meine Formel das kontrollieren. Das erreicht man mit folgende Formel: "=WENN(A30="";"";WENN(MONAT(A30+1)=MONAT(A30);A30+1;""))"
In Worten heißt es dann ungefähr: "Wenn A30 leer ist bin Ich auch leer sonst, wenn der Monat von A30+1 gleich den Monat von A30 ist bin ich A30+1 sonst bin Ich leer"
Mit der Funktion =WENN(Bedinnung;Wahr;Falsch) kann man eine Zuweisung machen. Excel läßt da aber nur eine begrenzte Verschachtlung zu.
Die Funktion =Monat(Datum) gibt den Monat als Zahl zurück.
Mit dieser Formel hab ich dann ach keine Tage von anderen Monate mehr in der Liste.
Zusätzlich hab ich über "Bedingte Formatierung" noch eine Funktion hinzugefügt die, die Wochenenden markiert. Das erreicht man auch über eine Formel am einfachsten.
Für die Zellen "=$A$4:$L$34" gib ich die komplette Tabelle als Ziel der Formel. und mit der Formel: "=WOCHENTAG($A4;2)>5" bestimme ich nun ob es ein Wochenende ist oder nicht.
Die Funktion =WOCHENTAG(Datum;Format) gibts als Ergebnis eine Zahl für den Wochentag. Mit den Format 2 gibt die Funktion für Montag = 1 bis Sonntag =7 raus. Somit ist jeder Wert über 5 ein Wochenende. Durch das Dollarzeichen vor den A sorge ich dafür das die Formel nur die Spalte A betrifft und die Zeile halt am Bereich angepasst wird.
Automatische Berechnung der Überstunden zurück nach Oben
Nun zu die Komfortfunktionen. Nach den Eintrag für die Stunden für Fahrt, Arbeit usw. soll er zusätzlich zu den Gesamtstunden auch noch die Überstunden errechnen. Auf die Gesamtstunden geh ich mal nicht ein. Das sollte jeder können. Überstunden ist auch ur eine einfach =Wenn funktion aber will sie hier mal erwähnen. Mit der Formel: "=WENN(F4=0;"";WENN(F4>8;WENN(F4<10,1;F4-8;2);""))" .
Als erstes kontrolliere ich mit der Formel ob F4 (Gesamtstunden) null ist, wenn ja ist die Zelle leer, sonst gehts weiter.
In der nächsten Bedinnung kontrolliere ich nun ob F4 größer als 8 ist, wenn es Wahr ist wird noch kontrolliert ob F4 kleiner als 10,1 ist, wenn es wieder Wahr ist wird F4-8 gerechnet, wenn mehr als 10 sind wird nur 2 eingetragen. Der Rest geht nun in der nächsten Spalte. Da die Formel der hier ziemlich ähnlich ist erkläre ich sie hier nicht weiter.
Für die Spalte mit Sonntag nutze ich die Formel "=WENN(WOCHENTAG(A4;2)=7;WENN(F4>0;F4;"");"")" erreiche ich das die Sonntags selbstständig ausgefüllt wird.
Also wenn der Wochentag von A4 Sonntag ist und wenn F4 größer als 0 ist, ist der Wert gleich F4 sonst bleib die Zelle leer.
Dashboard zurück nach Oben
Ich habe bei Youtube immer mal wieder was gesehen zu Dashboards mit Exceltabellen und wollte es einfach mal selbst ausprobieren. Somit hab ich ein paar Pivottabellen erstellen müßen um dann die Charts für das Dashboard zu erstellen. Ich werd hier aber nicht weiter drauf eingehen und nur das Video verlinken mit den ich es erstellt hab.
Zum Schluß zurück nach Oben
Somit bin ich hier fertig. Zum Ende will ich noch die Exceldatei selbst zur Verfügung stellen.
Viel Erfolg und Spaß weiterhin
eurer Shadow