none
Zuschläge in Excel berechnen RRS feed

  • Frage

  • Verehrte Excel Spezialisten,

    ich habe derzeit die Aufgabe in Excel 2003 eine Tabelle zu entwickeln, welche unter anderem Zuschläge während der Arbeitszeit erfasst. Es ist aber derart kompliziert, dass ich nun absolut an den Rand meiner Fähigkeiten stoße und auf hilfe hoffe.

    Es gestaltet sich wie folgt:

    Es gibt, wenn es sie dann gibt, 3 Arten von Zuschlägen: Nacht, Sonntag, Feiertag

    Nacht und Feiertag wird jedem gewährt
    Sonntag nur bestimmten Mitarbeitern

    Nachtzuschlag wird gewährt von 23 - 6 Uhr
    Sonntag und Feiertag stets von 0-24 Uhr

    Zu berücksichtigen ist, dass bei den Zuschlägen auch die Pausenzeiten abgezogen werden.

    Die Problematik an der ganzen Sache ist, dass sich die Zuschläge unter umständen überschneiden und dann nur der jeweils prozentual höchste Zuschlag gewährt wird. Also wenn Sonntag der 3.Oktober ein Mitarbeiter von 0 - 8 Uhr arbeitet fällt von 0-6 Uhr Nachtzuschlag an, von 0-8 Sonntagszuschlag und von 0-8 Feiertagszuschlag. Da Feiertagszuschlag mit 100% am höchsten liegt, soll nur dieser berechnet werden.

    Arbeitet jemand am Samstag von 21 Uhr bis Sonntag 6 Uhr, dann ist von 23-0 Uhr am Samstag Nachtzuschlag und von 0-6 Uhr am Sonntag Sonntagszuschlag zu gewähren, da dieser höher ist als Nachtzuschlag. Sonntags von 21- Montag 6 Uhr würde dann von 21-0 Uhr Sonntagszuschlag gelten und von 0-6 Uhr Nachtzuschlag.

    Immer zu berücksichtigen ist dabei, das in dem Zuschlagszeitraum die Pause abgezogen wird, wenn der Anteil der Arbeitszeit mit zuschlägen höher ist, als der Zeitraum ohne Zuschlägen.

    Das Thema an sich ist schon schwer zu beschreiben, dennoch hoffe ich auf hilfreiche Antworten - oder jemand kennt die Patentlösung für diesen Fall.

    Freundliche Grüße
    S. Luehr
    Freitag, 25. September 2009 14:36

Antworten

  • Hallo Sascha,

    du kannst dies natürlich später in eine komplexe Formel packen, für den Anfang ist es aber leichter und ersichtlicher zunächst mit zwei weiteren Spalten zu arbeiten, in denen die Zeit auseinander genommen wird. Die erste Spalte berücksichtigt die Anzahl der Stunden vor 0 Uhr und die zweite Spalte die Stunden nach 0 Uhr.

    Also wenn die Endzeit kleiner der Anfangszeit ist, wurde in deiner Tabelle ja logischerweise über 0 Uhr hinaus gearbeitet. Dann trifft der Fall ein, dass die Zeit mehrgeteilt betrachtet werden muss. Die Anzahl der Stunden vor und nach 0 Uhr kannst du ja leicht ausrechnen und dann unterschiedlich berücksichtigen.

    Es ist zwar unwahrscheinlich, aber theoretisch ja nicht auszuschließen, dass jemand länger als 24 Stunden arbeitet. Falls dieser Fall bei dir möglich ist, funktioniert die Schlußfolgerung natürlich nicht, dass nur bei einer kleineren Endzeit gegenüber der Anfangszeit über 0 Uhr hinaus gearbeitet wurde. Denn eine Zeit von 10 Uhr bis 11 Uhr können dann sowohl 1 Stunde als auch 25 Stunden sein. In diesem Fall bleibt dir nur die Möglichkeit, das Datum (oder den Wochentag oder sonst eine Kennzeichnung) zusätzlich zu den Zeiten zu erfassen und diese mit auszuwerten.

    Viele Grüße
    Jan
    Montag, 5. Oktober 2009 21:29
  • Hallo Sascha,

    ich habe versucht, etwas zu finden, aber einige Formeln sind auch ehrlich gesagt zu lang geworden, als dass man als Außenstehender da schnell durchblicken kann... Es ist allerdings klar zu erkennen, dass der Fehler am Datum liegt bzw. in den Berechnungen, die das Datum verwenden. Wenn man sich die Formel einmal mit der Formelauswertung im Einzelschritt anschaut, sieht man, dass die Datumswerte intern in Zahlen umgerechnet werden. Das gleiche Ergebnis würde man auch direkt mit der Funktion DATWERT erreichen.
    Wenn ich deine Formel richtig verstehe, überprüfst du von dieser Zahl den Rest einer Division durch 7. Einfacher ist es, wenn du die Funktion WOCHENTAG verwendest. Diese gibt dir direkt einen Wert zwischen 1 und 7 zurück. Dabei musst du allerdings darauf achten, dass die Woche in den USA mit dem Sonntag beginnt. Der Sonntag hat also die 1 und der Samstag die 7. Das gleiche Verhalten trifft natürlich auch auf deine Formel zu, auch wenn du ohne die Funktion WOCHENTAG arbeitest. Ich vermute einmal, dass hier irgendwo der Fehler liegt, da die amerikanische Zählweise der Wochentage nicht jedem bekannt ist und daher oft zu Fehlern führt.

    Viele Grüße
    Jan
    Mittwoch, 7. Oktober 2009 09:33

Alle Antworten

  • Hallo S. Luehr,

    eine komplette Lösung ist hier schwer zu liefern, dafür bräuchte ich schon eine Excel-Datei als Basis. Ich habe aber bereits eine ähnliche Berechnung in Excel zur Berechnung des Verpflegungsmehraufwands erstellt. Demnach können bei einer Gesamtreisedauer von

    mehr als 8 Stunden: 6 Euro
    mehr als 14 Stunden: 12 Euro
    ganztätig, also 24 Stunden: 24 Euro

    steuerlich geltend gemacht werden. Zum Ziel kommt man bei einer solchen Aufgabe stets mit verschachtelten WENN-Funktionen, die dann unter dem Strich alle Fälle abdecken. In meinem Beispiel gestalten sich die Spalten wie folgt
    A: Datum
    B - D: Beschreibung
    E: Zeit von
    F: Zeit bis
    G: Dauer //Berechnung: =WENN((F9-E9) < 0; 24-F9-E9; F9-E9)
    H: Betrag //Berechnung: =WENN(STUNDE(G9) = 0;24;WENN(STUNDE(G9) >= 14;12;WENN(STUNDE(G9) >= 8; 6; 0)))

    In G berücksichtige ich, falls die Wiederankunft nach 0:00 Uhr liegt, da ich nur das Abreisedatum erfasse. So schließe ich eine negative Dauer aus. In H berücksichtige ich neben der Aufschlüsselung in Euro den Fall, dass Abreise- und Ankunftszeit jeweils 0:00 Uhr ist, ich also den ganzen Tag unterwegs war. Es gibt sicherlich elegantere Lösungen, doch für mich selbst reicht es aus und der grunsätzliche Weg über verschachtelte WENN-Funktionen ist immer gleich.

    In deinem Fall ist es eventuell noch komplizierter, denn bei der Umstellung von Sommer- auf Winterzeit bzw. umgekehrt, kann eine Zeit von 00:00 bis 08:00 Uhr auch einmal im Jahr nur 7 bzw. sogar 9 Stunden betragen. Bei der Vergütung wird das ja sicherlich berücksichtigt...

    Ich hoffe, du kannst aus meinen Erläuterungen etwas für deine Aufgabe mitnehmen!

    Viele Grüße
    Jan
    Freitag, 25. September 2009 15:35
  • Vielen Dank Jan,

    in der Tat wirft die Entwicklung einer Formel, bzw. diverser Formeln Fragen über Fragen auf, die alle zu berücksichtigen sind.
    Die Gesamtstrucktur der Datei beinhaltet noch zahlreiche andere Funktionen die alle der grundlegenden Datenvorbereitung zur Stundenerfassung und Abrechnung beitragen. Aber soweit ich dass umreißen kann habe ich das einmal Dargestellt.

    Leider kann ich hier keine Datei uploaden, würde diese aber gerne auch per Mail etc. zur verfügung stellen.

    Gruß

    Sascha
    Freitag, 25. September 2009 16:19
  • Hallo Sascha,

    meine E-Mail-Adresse möchte ich hier nicht mitteilen, dann ist mein Posteingang in wenigen Tagen voll mit Spam. Außerdem sollen andere ja auch etwas von dem Thread haben ;-) Das ist nicht böse oder abweisend gemeint. Wer mich kontaktieren möchte, findet außerdem schnell einen Weg... Für alle günstiger wäre es, wenn du eine Excel-Datei mit Beispieldaten irgendwo hochladen und den Link zur Datei hier veröffentlichen könntest.

    Vielleicht kommst du aber auch selbst zu der Lösung. Wichtig bei verschachtelten WENN-Funktionen ist, dass du diese Schritt für Schritt aufbaust. Also erst einmal zwei Fälle betrachtest (mit einer einzigen WENN-Funktion) und dann pro weiteren Fall eine WENN-Funktion hinzufügst. Wenn man nicht Schritt für Schritt vorgeht, verliert man auch als erfahrener Excel-Anwender schnell den Überblick bei verschachtelten Funktionen...

    Gruß
    Jan
    Samstag, 26. September 2009 00:22
  • Hallo Sascha,

    bist du mithilfe der Tipps bei deinen Funktionen weitergekommen?

    Viele Grüße
    Jan
    Freitag, 2. Oktober 2009 08:49
  • Hallo Jan,
    die Tipps haben mir schon eine Menge weitergeholfen,
    ich bin nun soweit, dass ich die einzelnen Zuschläge, sofern Sie dann Zutreffen berechnen lasse.
    Jedoch habe ich immer noch die Problematik, dass wenn sich zwei Zuschläge überschneiden - z.B. Samstag Nachts, dass dann beide herangezogen werden zur Berechnung.

    Also dem Ziel zwar schon näher, aber noch keine Konkrete Lösung.
    Montag, 5. Oktober 2009 11:00
  • Vielleicht mag ja mal jemand schauen,

    die Datei habe ich hier abgelegt:

    http://217.89.40.118/Excel/Stundenberechnung.xls


    Vielen Dank...
    Montag, 5. Oktober 2009 12:47
  • Hallo S.Luehr,

    ich habe mir die Datei einmal angesehen und sie ist etwas zu komplex, als dass man sich da schnell einarbeiten kann.

    Mir ist allerdings aufgefallen, dass es für die einzelnen Zuschläge auch jeweils eigene Spalten gibt. Treffen derzeit mehrere Zuschläge zu, werden in alle Spalten die Werte geschrieben. Du müsstest die Funktionen weiter ineinander verschachteln, um auch die Fälle zu berücksichtigen, in denen ein Zuschlag nicht zutrifft (z.B. indem überprüft wird, ob die Berechnung für einen höher priorisierten Zuschlag bereits einen Wert ergeben hat, heißt also ob die Zelle leer ist oder nicht). Für diesen Fall kannst du als Wert dann einfach eine leere Zeichenfolge mit "" (zwei Anführungszeichen) übergeben. Dann wird in die Zelle praktisch ein leerer Text geschrieben, womit die Zelle leer bleibt.

    Viele Grüße
    Jan
    Montag, 5. Oktober 2009 15:17
  • Hallo Jan,

    danke für das Feedback, vom Prinzip her habe ich das auch verstanden, was mir eigentlich fehlt - so als Gedankenanstoss ist wie ich die Tage so zerlege, das ich es auf einzelne Zuschläge prüfen kann.

    Also wenn ich Samstag von 17-2 Uhr arbeite muss ich berechnen das von 23-0 Uhr Nachtzuschlag ist
    und von 0-2 Uhr dann Sonntagszuschlag

    oder Sonntags 21-6 Uhr halt von 23-0 Uhr Sonntagszuschlag und von 0-6 Uhr Nachtzuschlag

    Mir fehlt nur die richtige Idee - in meinem Kopf ist es praktisch schon fertg.

    Wenn Sonntagszuschlag größer ist als 0 und der beginntag ist ein Samstag, dann prüfe ob samstag zwischen 23 und 0 Uhr gearbeitet wird, wenn ja, dann 1 sonst nix, und prüfe wenn Sonntagszuschlag gilt, wieviel stunden am sonntag gearbeitet werden.

    Ich denke wenn ich ersteinmal zwei zuschläge verschachtelt habe, dann bekomme ich den Feiertag da auch noch mit eingebaut. Ich muss es nur ersteinmal vom Prinzip her verstanden haben.

    Für einen Denkanstoss wäre ich echt dankbar...

    gruß Sascha
    Montag, 5. Oktober 2009 17:37
  • Hallo Sascha,

    du kannst dies natürlich später in eine komplexe Formel packen, für den Anfang ist es aber leichter und ersichtlicher zunächst mit zwei weiteren Spalten zu arbeiten, in denen die Zeit auseinander genommen wird. Die erste Spalte berücksichtigt die Anzahl der Stunden vor 0 Uhr und die zweite Spalte die Stunden nach 0 Uhr.

    Also wenn die Endzeit kleiner der Anfangszeit ist, wurde in deiner Tabelle ja logischerweise über 0 Uhr hinaus gearbeitet. Dann trifft der Fall ein, dass die Zeit mehrgeteilt betrachtet werden muss. Die Anzahl der Stunden vor und nach 0 Uhr kannst du ja leicht ausrechnen und dann unterschiedlich berücksichtigen.

    Es ist zwar unwahrscheinlich, aber theoretisch ja nicht auszuschließen, dass jemand länger als 24 Stunden arbeitet. Falls dieser Fall bei dir möglich ist, funktioniert die Schlußfolgerung natürlich nicht, dass nur bei einer kleineren Endzeit gegenüber der Anfangszeit über 0 Uhr hinaus gearbeitet wurde. Denn eine Zeit von 10 Uhr bis 11 Uhr können dann sowohl 1 Stunde als auch 25 Stunden sein. In diesem Fall bleibt dir nur die Möglichkeit, das Datum (oder den Wochentag oder sonst eine Kennzeichnung) zusätzlich zu den Zeiten zu erfassen und diese mit auszuwerten.

    Viele Grüße
    Jan
    Montag, 5. Oktober 2009 21:29
  • Hallo Jan,

    ich habe den gestrigen Abend mal genutzt und ein wenig weiter geforscht, getestet und gebastelt. Es funktioniert nun alles soweit - es sei denn es ist ein Samstag - frag mich nicht warum, aber am Samstag habe ich grundsätzlich die falschen ergebnisse.

    Ich habe die Datei nochmal auf dem Server abgelegt zum anschauen und für eventuelle verbesserungsvorschläge.


    Gruß Sascha

    http://217.89.40.118/Excel/Stundenberechnung.xls




    Dienstag, 6. Oktober 2009 07:18
  • Hallo Sascha,

    ich habe versucht, etwas zu finden, aber einige Formeln sind auch ehrlich gesagt zu lang geworden, als dass man als Außenstehender da schnell durchblicken kann... Es ist allerdings klar zu erkennen, dass der Fehler am Datum liegt bzw. in den Berechnungen, die das Datum verwenden. Wenn man sich die Formel einmal mit der Formelauswertung im Einzelschritt anschaut, sieht man, dass die Datumswerte intern in Zahlen umgerechnet werden. Das gleiche Ergebnis würde man auch direkt mit der Funktion DATWERT erreichen.
    Wenn ich deine Formel richtig verstehe, überprüfst du von dieser Zahl den Rest einer Division durch 7. Einfacher ist es, wenn du die Funktion WOCHENTAG verwendest. Diese gibt dir direkt einen Wert zwischen 1 und 7 zurück. Dabei musst du allerdings darauf achten, dass die Woche in den USA mit dem Sonntag beginnt. Der Sonntag hat also die 1 und der Samstag die 7. Das gleiche Verhalten trifft natürlich auch auf deine Formel zu, auch wenn du ohne die Funktion WOCHENTAG arbeitest. Ich vermute einmal, dass hier irgendwo der Fehler liegt, da die amerikanische Zählweise der Wochentage nicht jedem bekannt ist und daher oft zu Fehlern führt.

    Viele Grüße
    Jan
    Mittwoch, 7. Oktober 2009 09:33
  • Hallo Sascha,

    bist du mit den Berechnungen weitergekommen?

    Viele Grüße
    Jan
    Freitag, 9. Oktober 2009 17:00
  • Hallo Sascha,

    Ich gehe davon aus, dass die Antwort Dir weitergeholfen hat.
    Solltest Du noch "Rückfragen" dazu haben, so gib uns bitte Bescheid.

    Grüße,
    Robert

    Dienstag, 13. Oktober 2009 18:37
    Moderator
  • Hallo Zusammen,

    ich habe jetzt mehrfach probiert und mit der open geposteten Datei getestet - es gelingt mir in keinster Weise alle meine Anforderungen unter einen Hut zu bringen, auch intensive recherchen brachten mich nicht zum Erfolg.
    Komisch, dass ich nicht finde, dass jemand dieses Thema schon einmal so intensiv behandelt hat, obwohl die Problematik an sich doch eigentlich gang und gebe ist.

    Vielen Dank in diesem Zusammenhang übrigens nocheinmal an Jan, deine Tipps bezüglich der Funktion DATWERT und WOCHENTAG haben mir bei den einzelnen Berechnungen bereits deutlich weitergeholfen. Es funktioniert auch soweit, dass ich die Zuschläge an sich, sauber den gegebenheiten nach berechnen kann, jedoch wenn mehrere Zuschläge zutreffen, dann bricht das komplette Konstrukt immer wieder Zusammen und ich erhalte alles mögliche an Berechnungen nur nicht das Ergebnis.

    Hat nicht irgendjemand das schon einmal so, oder in ähnlicher Form behandelt ? Das man zu einer Arbeitszeit einen Zuschlag dazurechnet wenn gewisse Faktoren zutreffen ???

    Ich bin immer noch für hinweise und Lösungsvorschläge offen und nehme diese dankend entgegen.

    Gruß Sascha
    Dienstag, 13. Oktober 2009 18:50
  • Hallo Sascha,

    ich habe auch nochmal im Internet gesucht und zwar ähnliche Beispiele (mit einem Zuschlag) aber kein Beispiel gefunden, welches mehrere Zuschläge berücksichtigt. Wenn du die einzelnen Zuschläge korrekt berechnen kannst, dann versuche dochmal, nicht alles in eine einzige Formel zu packen, sondern mit einer - oder mehreren - weiteren Spalte zu arbeiten, welche das korrekte Ergebnis der anderen Spalte aufgreift. Dann sollte es ja funktionieren.

    Da ich als Trainer auch schon viele Access- und Excel-Schulungen unterrichtet habe, kenne ich die Schwierigkeit mit ineinander verschachtelten Funktionen nur zu gut. Ab einem gewissen Punkt der Verschachtelung sind die Formeln nur noch schwer bis gar nicht nachzuvollziehen - das geht mir selbst auch so. Ich gebe dann immer den Tipp, am besten nochmal ganz von vorne anzufangen und für jede Zwischenberechnung eine eigene Spalte einzufügen, auf dessen Ergebnis dann in einer weiteren Spalte zurückgegriffen wird. Wenn man die Aufgabe Schritt für Schritt angeht, kommt man viel leichter zum Ergebnis und kann die einzelnen Berechnungen auch wesentlich leichter nachvollziehen. Am Ende hat man dann eine zunächst unschöne Tabelle, dessen Berechnungen aber funktionieren. Erst wenn die Berechnung sauber funktioniert, sollte man die einzelnen Zwischenberechnungen in eine komplexe Formel zusammenpacken. Dies ist dann auch recht einfach, denn wenn ich in einer Zelle C3 beispielsweise die Berechnung "A1 + B2" habe und in einer anderen Zelle D5 erstmal mit einem Verweis auf C3 rechne, kann ich in dieser Teilformel dann später leicht "C3" durch "(A1 + B2)" ersetzen und die Spalte C anschließend löschen. So gehe ich dann Schritt für Schritt mit allen Spalten bzw. Teilformeln vor und überprüfe zwischendurch stets, ob die Berechnung noch korrekt funktioniert. Dann ergibt es sich auch von alleine, wie die einzelnen Funktionen korrekt verschachtelt werden müssen.
    Die meisten versuchen leider direkt mit einer Tabelle anzufangen, die bereits wie das Endprodukt aussieht, und verlieren dann den Überblick. In der Tat wehren sich die meisten Teilnehmer in Seminaren dagegen, mit ihrer Tabelle von vorne anzufangen und das bisherige über den Haufen zu werfen, da man hofft, die Lösung dann doch irgendwann als Geistesblitz zu erlangen. Das ist aber so gut wie nie der Fall und die Zeit, die für das Suchen des Fehlers draufgeht, übersteigt die Zeit, die für eine neue schrittweise Lösung draufgeht, in der Regel um ein Vielfaches.

    Ich kann dir daher nur empfehlen, das Ganze nochmal schrittweise neu anzupacken. Dann ist es übrigens auch für Dritte viel einfacher, die Tabelle zu verstehen und Tipps zu geben, wenn du an einer Stelle mal nicht weiterkommst.

    Viele Grüße
    Jan
    Mittwoch, 14. Oktober 2009 09:45