(Excel 2008) Überstunden berechnen

[Mr. Snoot]

Die Zellbezüge müssen halt stimmen, da ist evtl. etwas Handarbeit notwendig.

Hab mal ein Beispiel für November und Dezember gemacht. Die Zusatztabelle ist im Tabellenblatt Zulagen: Anhang anzeigen excel.zip


In der Zulagentabelle greifst du dann bspw. in der Zelle B3 auf die erste Zeile der Novembertabelle zu (November!C15 oder November!D15).

Und in Zelle B35 greifst du auf die Dezembertabelle zu: Dezember!C15 oder Dezember!D15.


Und in der Novembertabelle greifst du dann für die Zelle G15 über =Zulagen!G3 auf die Nachtzulagen zu und für die Sonntagszulagen in Zelle H15 mit =Zulagen!I3.

Entsprechend in der Dezembertabelle, Zelle G15 mit =Zulagen!G35 bzw. für Zelle H15 mit =Zulagen!I35



Wenn du dann für Januar eine neue Tabelle brauchst, musst du leider etwas aufpassen. Ich würde dann die Zusatztabelle von Dezember kopieren und darunter einfügen, so dass in A68 1.01.09 steht. Wie du aber feststellen wirst, steht in B68 überall C48 bzw. D48 und weiterhin Dezember. Dezember müsstest du in Januar ändern und aus 48 jeweils 15 machen (eben so in den Spalten daneben). Danach kannst du die Formeln wie üblich nach unten ziehen.


Wenn du dann ein neues Blatt für Januar machst, würdest du in Zelle G15 mit =Zulagen!G68 bzw, in H15 mit =Zulagen!I68 zugreifen usw. usf. Sofern du nicht für jedes Jahr eine eigene Datei anlegst, müsstest du die Tabellenblätter auch eindeutig benennen. also Bspw. November08, Dezember08, Januar09, Februar09, ..., November09, Dezember09 usw., sonst weiß Excel ja nicht, auf welches Novemberblatt du zugreifen willst (dementsprechend greifst du dann halt mit =November08!ZelleX oder =Februar09!ZelleY drauf zu).




Wenn du die Zusatztabelle jeweils ins selbe Blatt machst, dann müsstest du nichts anpassen, da die Zusatztabelle dann in jedem Blatt an der gleichen Stelle steht und sich dadurch nichts verschiebt.




---------------------------------------------------------------------------

edit:

wenn dich aber die Zusatztabelle so sehr stört, dann könnte man zumindest bis auf Spalte S und T alles löschen und in je eine Formel für Nachtzuschläge und Sonntagszuschläge packen, die dann halt direkt in G15 und H15 stehen. Wären zwar sehr unhandlich, wenn man etwas ändern möchte, aber möglich wärs. Solange du nicht an den einzelnen Arbeitsstunden in der Nacht und Sonntags interessiert bist, braucht man ja einige Spalten der jetzigen Zusatztabelle auch nicht. Ich hatte es halt mal so gemacht, damit es übersichtlich ist.


Die kürzeste Lösung wäre also Spalte S und T behalten und je eine etwas längere Formel für Nacht- und Sonntagszuschläge benutzen (wobei hauptsächlich die Formel in Spalte G schlimm aussieht): Anhang anzeigen excel.zip

 

[Sannel]

Hi...das ist ja super mit der Zusatztabelle, kann man aber alles wieder etwas größer machen?
leider besteht jetzt immer noch das Problem mit Nachtzulage und Sonntagszulage. Also ich meine WEnn Sonntag ist darf es keine Nachtzulage geben.

 

[Mr. Snoot]

Hm, ich dachte zuerst die Zusatztabelle stört dich. Nun ist sie zu klein - oder ist was anderes zu klein?

Das mit "wenn Sonntag dann kein Nachtzuschlag" ist einfach eine Wenn-Anweisung in der G-Spalte.

Da steht ja (Zelle G15) im Moment

=X15​

Da fragt man nun einfach, ob die Sonntagsspalte, H, 0:00 oder leer ist (zur Sicherheit einfach beides), also

=WENN(ODER(H15=0;H15="");X15;0)​

(oder statt 0 ganz am Ende halt "" wenn die Zelle nicht 0 sondern dann leer bleiben soll)

Wenn also Sonntags nicht gearbeitet wird (0:00), dann soll die Nachtzulage eingetragen werden (falls vorhanden). Wenn Sonntags gearbeitet wird, dann ist die Nachtzulage 0 (oder halt leer).

 

[Sannel]

nein ich meinte ob man den Stundennachweiszettel wieder etwas vergrößern kann

 

[Mr. Snoot]

Ich hab an der Größe nichts verändert, vielleicht ist nur der Zoom im Moment nicht auf 100%?

Eigentlich sollte es dazu in den Menüleisten eine Auswahlbox geben, oder im Menü Ansicht.

 

[Sannel]

jetzt ist die komplette zusatztabelle wieder da...und habe nichts gemacht...mh egal.ich hau mich jetzt erst mal aufs ohr muss früh raus.dir noch ne angenehme nacht bis denne. Aber die formeln scheinen alle zu funktionieren.respekt...ich würdce wahrscheinlich jahre daran sitzen :-))

 

[Sannel]

hey sagmal wenn ich dfen Feiertagszuschlag berechnen will muss ich dann auch ne zahl nehmen, alsop so wie für die Wochentage oder was steht für einen Feiertag?

 

[Mr. Snoot]

Hü,

Feiertage kann Excel leider nicht selbst ermitteln. Es gibt zwar Möglichkeiten, dazu muss man aber entweder zusätzliche Addons benutzen oder sich mit Visual Basic auseinandersetzen. Außerdem müsste man wohl nochmal etwas rumprogrammieren, damit nur die relevanten Feiertage auftauchen (es gibt ja nicht jeden Feiertag in jedem Bundesland).


Es gibt zwei andere Möglichkeiten. Grundsätzlich bräuchte man noch eine Extraspalte (z.B. mal in Spalte Z) in der bspw. 1 steht, wenn der Tag ein Feiertag ist (und 0 oder nichts, wenn eben kein Feiertag ist). Dann könnte man einfach prüfen:

=WENN(Z15=1;berechne_Feiertagszuschlag;sonst_nicht)​

Muss noch geklärt werden, wo diese 1en für die Feiertage herkommen!?

1. man macht sich kurz die Arbeit und trägt es selbst ein (sind ca. 30 Stück pro Jahr)
2. man benutzt eine weitere Liste, die alle relevanten Feiertage enthält und prüft, ob das aktuelle Datum in dieser Liste vorkommt. Dann wäre es ein Feiertag.

Letzteres wäre also zumindest halbautomatisch, bräuchte aber mehr Platz für die Feiertagsliste (die man aber pro Jahr nur ein einziges mal in einem gesonderten Tabellenblatt einfügen müsste) - allerdings muss ich mich da gerade selbst schlau machen. Weil die Berechnung, die ich dafür vorgenommen hätte komische Werte liefert.

 

[Sannel]

Ha ... gibt es doch noch dinghe die dich an deine grenzen bringen

ich finds schon cool, was man alles so mit excel machen kann, vorausgesetzt man hat ahnung
hast du beruflich mit dem programm viel zu tun oder machst das alles privat weil es dich interessiert?

 

[Mr. Snoot]

Ich mach das privat, weil es dich interessiert

Ansonsten bruach ich Excel hauptsächlich auf Arbeit, aber da brauchts meist auch keine komplizierten Formeln.

Wie gesagt, das sind bisher ja im Grunde keine komplizierten Dinge. Lediglich Wenn-/Und-/Oder-Funktionen, man darf halt den Überblick nicht verlieren.

Wenn du selbst nicht mit Excel zu tun hast, und dann quasi von 0 auf 100 so eine Stundenabrechnung machen willst, ist es klar, dass das kompliziert erscheint.

Aber wenn man mit kleinen Dingen anfängt - einzelne Funktionen, nichts Verschachteltes - dann kommt das alles von alleine. Wenn man das Grundprinzip einmal verinnerlicht hat ist der Rest nicht mehr schwer.

 

[Sannel]

hast du schon mal solche langen formeln zusammen gestellt außer für mich
habe mich vorher noch nie wirklich mit excel beschäftigt und mit den Formel halt noch gar nicht.
mich ärgert es halt etwas, dass ich zur zeit nicht wirklich viel zeit habe um mich damit auseinander zusetzen, es braucht ja doch etwas zeit, bis das alles so drin ist.

aufjedenfall hast mir übelst geholfen

erstmal

 

[Mr. Snoot]

Jo gab schon öfter Fälle, wo das ähnlich lang wurde. Aber das ist ja nicht das Schlimme daran. Ob eine oder sieben verschachtelte Wenn-Funktionen - ist im Grunde ja das gleiche

aufjedenfall hast mir übelst geholfen

Kein Problem, dafür ist das Forum ja da.

gibt es doch noch dinghe die dich an deine grenzen bringen

Gibt sicher genug. Ich hab mit den meisten Excel-Funktionen ja auch noch nichts gemacht. Was ich meistens brauchte sind insgesamt allerhöchstens 10 Funktionen wie Wenn/Summe/Sverweis/Und/Oder/Stabwn/Zählenwenn - also wohl noch die einfacheren Dinge.

Aber zur Not gibts ja noch Google- und da findet man in einschlägigen Office-Foren zu eigentlich jedem Problem mehrere Lösungsansätze

 

[Sannel]

sag mal die zusatztabelle, kann ich die einfach kopieren und in die anderen einfügen ( S 13) oder gibts da noch was, was individuell geändert werden muss?

 

[Mr. Snoot]

S13?

Die Tabelle kannst du einfach kopieren. Sollte aber in jeder Tabelle an der gleichen Stelle (gleiche Zeilen/Spalten) sitzen, damit es keine Probleme mit den Zellbezügen gibt.

 

[Sannel]

mh er lässt es mich aber nicht kopieren, also wieder einfügen dann. S und 13 ist doch die spalte und zelle oder nicht

 

[Mr. Snoot]

Achso, ja Zelle S13

Eigentlich sollte ganz normales Kopieren + Einfügen klappen. Hab mal ein Video angehängt.

 

[Sannel]

geht. aber wenn ich jetzt z.B deine G15 formel in meine G15 eintrage, zeigt er mir wieder überall 0 an...muss da noch was geändert werden?

 

[Mr. Snoot]

Eigentlich nicht. Sollte denn derzeit etwas anderes als 0 rauskommen?

In G15 steht jetzt diese Formel:

=WENN(ODER(H15=0;H15="");X15;0)​

Korrekt?

Dann gibts ja zwei Mögichkeiten:

1. Es ist kein Sonntag, also ist H15 gleich 0 oder leer. Dann wird in G15 der Wert von X15 eingetragen (steht in X15 eine Uhrzeit?).
2. Es ist Sonntag, und damit steht in H15 irgendeine Arbeitszeit. Dann wird in G15 0 eingetragen.

 

[Sannel]

habe sie jetzt nochmal rein kopiert...er schreibt immer null obwohl er mir doch nachtzuschläge (min.) anzeigen müsste

 

[Sannel]

die Sonntagsformel H15 usw. klappt