Verschachtelte Wenn Und Funktion für Feiertagsberechnung

[energygecko]

Hi liebe Community,

versuche gerade mit Excel eine Lohnabrechnung zu basteln, was soweit auch ganz gut funktioniert. Nur mit der Berechnung der Feiertage tue ich mir etwas schwer. Hier folgendes Problem.

Die Abrechnung ist wie folgt aufgebaut:

Datum

Urlaub

Krank

Tag

Beginn

Ende

TS

NS

Feiertag

Gesamt

NZ 23%

So 26%

So 3%

FT 100%

FT 77%

In die Spalte Tag wird durch Schaltfläche und Makro-Befehl jeweils Mo, Di, Mi, Do, Fr, Sa oder Sonntag eingefügt und der Rest der Spalte durch Auto-Fill vervollständigt.
In den Spalten TS und NS wird jeweils ein X gemacht für entweder Tagschicht oder Nachtschicht. Dementsprechend erscheint in den Spalten Beginn und Ende entweder 06:00-18:00 oder 18:00-06:00 Uhr.
In der Spalte Gesamt erscheinen jeweils immer 12 Stunden.

Die Spalten dahinter sind jeweils für die Zuschlagsberechnung.

Der NZ-Zuschlag wird durch Formal bestimmt. Ist in diesem Fall ja auch nicht besonders schwer.

Nun kommt der Knackpunkt.

Wenn in der Spalte Tag ein So für Sonntag steht und in der Spalte TS ein X (Tagschicht=X), dann sollen in der Spalte So 26% 12 Stunden erscheinen.
Wenn nun in der Spalte Tag ein So für Sonntag steht und in der Spalte NS ein X (Nachtschicht=X), dann sollen in der Spalte So 26% nur 2 Stunden stehen und in der Spalte SZ 3% 4 Stunden.

Kämpf da jetzt schon ne Weile mit rum, komm aber irgendwie nicht weiter. Würde mich über Hilfe freuen.

Lg

[DL]

Hi

Code: [Auswählen]

=(E5="So")*((H5="X")*12+(I5="X")*2)
=(E5="So")*(I5="X")*4

[energygecko]

Also ich ja jetzt kein Excel Crack, aber da in der Formel fehlt das Wenn oder?

Ich hab unten mal die Spalten- und Zeilennamen dazugeschrieben.

O24 : Tag
R24: Tag
S24: Nacht
Y24: So Zuschlag 26%
Z24: So Zuschlag 3%

[DL]

aber da in der Formel fehlt das Wenn oder?

Und deswegen darfst du die Formeln nicht verwenden, oder?

[energygecko]

Wenn ich die von Dir postete Formel poste erhalte ich WAHR, sollten aber entweder 2,00 Stunden oder 4,00 Stunden ausgegeben werden.

=(O24="So")*((R24="X")*12+(S24="X")*2)
=(O24="So")*(S24="X")*4

[steve1da]

Hola,

bei mir kommen Zahlen als Ergebnis.

Zeig doch bitte eine Beispieldatei wo WAHR erscheint.

Gruß,
steve1da

[energygecko]

Ich hab Euch unten mal die Exce-Datei verlinkt. Muss auch dazu sagen, dass ich jetzt die Zeilen/Spalten durch eigene Angaben ersetzt habe, ob das stimmt weiß ich nicht, weil ich noch nicht genau nachvollziehen kann, was die Formel eigentlich macht/machen sollte.

https://onedrive.live.com/redir?resid=E12B4F1A2356937A!354&authkey=!AB3tflCJn6gmPWY&ithint=file%2cxls

[DL]

Hi

Das sind zwei Formeln.

[energygecko]

Und was genau willst Du mir jetzt damit sagen? Das das 2 Formeln sind seh ich ja selber, hab ja auch beide in das Kästchen kopiert.

[steve1da]

Hola,

das soll heißen, dass entweder die eine oder die andere in die Zelle kommt. Nicht beide gleichzeitig!

Gruß,
steve1da

[energygecko]

Ok, das hab ich jetzt auch verstanden  . Erhalte aber in beiden Fällen immer 0,00.

[Melanie Breden]

Hallo,

du kannst die verschiedenen Bedingungen (Wochentag und Schicht) über Hilfsspalten je Zeile ermitteln und über einen Textvergleich die Ergebnisse aus einer definierten Tabelle auslesen.
Wenn sich die Werte ändern sollten, musst du nur die Werte-Tabelle anpassen.
Ein Beispiel ist in deiner Datei.

Gruß
Melanie

[DL]

Erhalte aber in beiden Fällen immer 0,00.

Das liegt daran dass in D24 nicht "So" steht.

Code: [Auswählen]

=(TEXT(D24;"TTT")="So")*((G24="X")*12+(H24="X")*2)
=(TEXT(D24;"TTT")="So")*(H24="X")*4

[energygecko]

Ok, super, das klappt schon mal. Wie müsste ich dann weiter vorgehen, wenn:

D24 = Samstag und H24 = X -> dann soll O24 (Sonntagszuschlag 3%)6,00 stehen.

=(TEXT(D24;"TTT")="So")*(H24="X")*4&" "&und(TEXT(D24;"TTT")="Sa")*(H24="X")*6) ??

Bin was so komplexe verschachtelte Formeln angeht noch der absolute Anfänger.

Desweiteren gibt es da noch das Problem mit dem Feiertag. In der ersten Spalte mit den Tagen habe ich mir mittels bedingter Formatierung den Feiertag farblich anzeigen lassen

=zählenwenn(Feiertage!$B$8:$F$22;Blanko_Abrechnung!A24)>0 und dann das jeweilige Format ausgewählt. Hab den Bereich um A24 noch bis nach unten erweitert.

Wenn also nun der Tag in der Spalte A24:A54 ein Feiertag ist, und G24 (Spalte für Tagschicht) ein X, dann soll in P24 eine 12 stehen, die Zellen N24:O24 leer bleiben. Wenn aber nun bei H24 (Spalte für Nachtschicht) ein X ist, dann soll in P24 2,00 stehen und in Q24 4, die Zellen N24:O24 bleiben in diesem Fall leer.

Nochmal als kleine Übersicht zum Verständnis, weil ich selber sonst nicht mehr durchblicke :-)

Sonntagszuschlag 6:00 - 20:00 >23%
Sonntagszuschlag 20:00 - 06:00 >3%

Feiertagszuschlag 6:00 - 20:00 >100%
Feiertagszuschlag 20:00 - 06:00 > 77%

Da verschachtelt sich wohl eine Funktion in die andere lol

[DL]

Hi

Code: [Auswählen]

=(TEXT(D24;"TTT")="So")*(H24="X")*4+(TEXT(D24;"TTT")="Sa")*(H24="X")*6

[energygecko]

Bleiben noch die zwei Spalten für den Feiertag

Wenn G24=x (Tagschicht) und I24=x (Feiertag), dann soll P24=12 (Feiertag 100%) sein.

Wenn H24=x (Nachtschicht) und I24=x (Feiertag), dann soll Q24=2 (Feiertag 77%) sein.

Langsam wirds echt kompliziert mit den ganzen Zahlen 

[DL]

Das Prinzip sollte doch so langsam klar sein.

Code: [Auswählen]

=(G24="X")*(I24="X")*12
=(H24="X")*(I24="X")*12

[energygecko]

Hab mal ein bißchen rumprobiert (Nachtschicht auf Arbeit hat man halt Zeit) und hab nun die folgenden Formeln in den Spalten:

Sonntag 26%:

=WENN(UND(TEXT(D24;"TTT")="So";G24="x");12;WENN(UND(TEXT(D24;"TTT")="So";H24="X");2;""))

Sonntag 3%:

=WENN(UND(TEXT(D24;"TTT")="So";G24="x");"";WENN(UND(TEXT(D24;"TTT")="So";H24="x");4;""))

Feiertag 100%:

=WENN(UND(I24="x";G24="x");"12";WENN(UND(I24="x";H24="x");2;""))

Feiertag 77%:

=WENN(UND(I24="x";H24="x");"4";"")

Jetzt will ich noch erreichen, dass wenn in den Spalten der Feiertage etwas steht, bei den Spalten für die Sonntage nichts stehen soll.

[energygecko]

Unten hab ich mal die derzeitige Version der Abrechnung gepostet.

Das Problem mit dem sverweis hab ich in der Zwischenzeit lösen können.

Im Tabellenblatt Feiertag werden die Feiertage für die nächsten paar Jahre aufgelistet. Wenn der Abrechnungsmonat nun einen Feiertag aufweist, möchte ich, dass dies in der Spalte D24:D54 farblich markiert wird. Hatte dies mit einer bedingten Formatierung bereits gelöst gehabt, dachte ich zumindestens, nur scheint die bei mir mal zu funktionieren und mal nicht:

In der Bedingten Formatierung:
=ZÄHLENWENN(Feiertage!$B$8:$F$22;D$24>0)

Leider wird mir der Feiertag in der Spalte D nicht farblich markiert.

Desweiteren sollte auch in der Spalte Feiertage bei dem jeweiligen Tag ein X stehen, damit ich weiter rechts in der Tabelle die Spalten für 100 und 77 % jeweils mit Formeln füllen kann.

100%
=WENN(UND(L24="X";J24="X");"12,00";WENN(UND(L24="X";K24="X");"2,00";""))

77%
=WENN(UND(L24="X";K24="X");"4,00";"")

[steve1da]

Hola,

zum Einen ist das Suchkriterium der Formel falsch, zum anderen hast du alles auf Zeile 24 fixiert, nicht auf Spalte D!

Code: [Auswählen]

=ZÄHLENWENN(Feiertage!$B$8:$F$22;$D24)
Und für das X:

Code: [Auswählen]

=WENN(ZÄHLENWENN(Feiertage!$B$8:$F$22;$D24);"x";"")
Aber dir ist doch hoffentlich bewusst, dass viele deiner Formeln TEXT-Werte ausgeben, die nur wie Zahlen aussehen und deshalb deine Summen am Blattende nicht funktionieren, oder? (FT100%, FT77%, WSF8%)

Gruß,
steve1da

[energygecko]

Erstmal danke für die Hilfe Steve. Das mit dem Feiertag klappt wunderbar.

Das am Spaltenende die Summen nicht stimmen ist mir auch schon aufgefallen. Wenn ich die einzelnen Zellen jedoch markiere, dann steht bei Formateigenschaft Zahl. Weiß nicht, warum er mir das am Ende nicht zusammenrechnet.

Bei der Berechnung der Sonntagszuschläge verwende ich ja folgende Formel:

=WENN(UND(TEXT(D24;"TTT")="So";G24="x");12;WENN(UND(TEXT(D24;"TTT")="So";H24="X");2;""))

Wie könnte ich die noch ergänzen, dass er mir in diese Zelle nichts reinschreibt, wenn bereits in der Spalte P24 ein Wert drin steht (gibt leider bei uns keinen Sonntags und Feiertagszuschlag zusammen)?

[steve1da]

Hola,

du schreibst die "Uhrzeiten" fest in die Zelle rein, allerdings mit "" darum. Das ist TEXT für Excel. Ersetz die Werte durch 9/24 für z.B. 9 Stunden, oder 12/24 für 12 Stunden. Dann weiß Excel dass das Uhrzeiten sein sollen.

Wenn du noch P24 dazu abfragen willst kommt das einfach als weitere Bedingung bei UND dazu:

Code: [Auswählen]

=.....wenn(und(P24<>"";Text(D24;"TTT".......
Gruß
steve1da

[energygecko]

Hab die Anführungszeichen bei den Zahlen ganz übersehen, danke für den Hinweis. Jetzt werden die Spalten richtig gerechnet.

Bei der Formelergänzung bin ich mir grad aber nicht so sicher:

=WENN(UND(TEXT(D24;"TTT")="So";G24="x");12;WENN(UND(TEXT(D24;"TTT")="So";H24="X");2;WENN(UND(P24<>"";TEXT(D24;"TTT")="So");"";"")))

Hab da irgendwo nen Fehler drin, da mir immernoch beide Zuschläge angezeigt werden. Und wenn bei B24 bzw C24 (Urlaub und Krank) ein X ist, gibt es ja leider keine Zuschläge und somit darf bei Sonntag oder beim Feiertag auch nix stehen.

[energygecko]

Ok habs selber rausgefunden :-)

=WENN(UND(B24="X";TEXT(D31;"TTT")="So");"";WENN(UND(P24<>"";TEXT(D24;"TTT")="So");"";
WENN(UND(TEXT(D24;"TTT")="So";G24="x");12;WENN(UND(TEXT(D24;"TTT")="So";H24="X");2;""))))

{maninweb: Umbruch eingefügt, weil's sonst das Forums-Layout irritiert hat}