Office: WENN UND mit Bezug auf mehrere Spalten

Hallo ,

ich scheiter gerade an meiner Formel

=WENN(C15="";"";WENN(UND(A15=Zuschläge!$E$36:$E$36;WOCHENTAG(A15)>1);WENN(UND(WOCHENTAG(A15)>1;F15<G15>1;(I15+J15)-G15;G15)))*24)


Problem bereitet mir der Fett makierte Bereich, dort soll eigentlich die Abfrage der Bezug von E36 bis E49 gehen. Die Formel funktioniert aber nur wenn ich nur einen Bereich (wie o.g.) einbinde.

Habe schon ein wenig mit Sverweis, Vergleich und Identisch umher probiert , mir ist aber keine Lösung gelungen evtl hat ja einer von Euch eine Idee.

Gruß
Korni

 

Hallo!

Deine Formel hat's leider zerschossen...
Wenn du Formeln mit Größer- und Kleiner-Zeichen posten möchtest, dann setze bitte ein Häkchen unter dem Antwortfenster bei "HTML in diesem Beitrag deaktivieren"!
Sonst denkt die Forum-Software, dass es sich um HTML-Tags handeln soll.

 

Hallo schatzi,

danke für den Hinweis, hier der nächste Versuch

=WENN(C15="";"";WENN(UND(A15=$Zuschläge!$E$36:$E36;WOCHENTAG(A15)>1);WENN(UND(WOCHENTAG(A15)>1;F15<G15);I15+J15;WENN(WOCHENTAG(A15)>1;(I15+J15)-G15;G15)))*24)

 

das gehört natürlich nicht zur Formel, sondern war ein versuch den bereich fett darzustellen, vermute aber mal durch das deaktivierte html das er es nicht genommen hat

 

Hallo!

Wenn ich richtig verstanden habe, dann geht es um den Teil
A15=Zuschläge!$E$36:$E$36
den du gerne durch
A15=Zuschläge!$E$36:$E$49
ersetzen würdest?

So geht das leider nicht, aber vielleicht passt es, wenn du diesen Teil durch dies ersetzt:
ZÄHLENWENN(Zuschläge!$E$36:$E$49;A15)>0

Falls nicht, dann müsstest du mal etwas ausführlicher beschreiben, was du da genau abfragen möchtest.

 

Hallo Schatzi,
das Funktioniert leider nicht.
Die Formel soll mir in einem Stundenzettel, die Stunden berechnen die ich an einem Feiertag arbeite.

Im Tabellenblatt "Zuschläge" stehen in den Spalten E35-49 die Feiertage

Der Grund warum ich nicht mit einer anderen Formel zur Berechnung der Feiertagsstunden arbeite ist das ich zwei Unterschiedliche Berechnungen haben muss einmal für die Tage von Mo-Sa und einmal wo der Feiertag noch auf einen Sonntag fehlt (sprich in P sollen mir die Feiertagsstunden von Mo-Sa ausgerechnet werden - anhand der genannten formel) und für Sonntag in Q habe ich in der Formel bei Wochentag anstatt größer als 1 mit gleich 1 gearbeitet (soweit so gut und funktioniert)
Außer der genannten Formel bin ich mit keiner bisher zu einem brauchbaren ergebniss gekommen wenn ich eine Aufteilung haben möchte. Wenn es aber noch andere Lösungen gibt bin ich für alles offen.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

7

Datum

Wochentag

Dienst

Beginn lt. Plan

Ende lt. Plan

Ist Beginn

Ist Ende

Pause

Dienststunden

Überstunden

Fortbildungsstunden

sonstige Std. unter Bemerkung

Nachtstunden

Samstagszuschlag

Sonntagszuschlag

Feiertag Mo.-Sa.

Feiertag am So.

8

01

Sa

Zelle	Format	Wert
A8	TT	01.01.2011
B8	TTT	01.01.2011
D8	hh:mm
E8	hh:mm
F8	hh:mm
G8	hh:mm
H8	h:mm;;	0
I8	[h]:mm;;
J8	[h]:mm;;	0
K8	[h]
M8	0,00;;
N8	0,00;;
O8	0,00;;

Zelle	Formel
A8	=E3
B8	=$A8
D8	=WENN(C8="";"";SVERWEIS(C8;Dienstzeiten!B:P;WOCHENTAG(A8;2)*2;0))
E8	=WENN(C8="";"";SVERWEIS(C8;Dienstzeiten!B:P;WOCHENTAG(A8;2)*2+1;0))
F8	=D8
G8	=E8
H8	=WENN(ODER(C8={"ID";"Fobi"});((REST(G8-F8;1)>=0,25)*0,5/24+(REST(G8-F8;1)>=3/8)*0,25/24);0)
I8	=WENN(C8="";"";REST(G8-F8;1)-H8-J8)
J8	=WENN(ODER(C8="";D8="Eingabe";E8="Eingabe");0;SUMME(D8-F8)+REST(G8-E8;1))
K8	=WENN(C8="Fobi";(F8>G8)+G8-F8;"")
M8	=WENN(C8="";"";MAX(;1-MAX(F8;Zuschläge!$A$6)+MAX(;Zuschläge!$C$6-F8)+MIN(G8;Zuschläge!$C$6)+(G8>Zuschläge!$A$6)*(G8-Zuschläge!$A$6)-(F8<G8)*(1-(Zuschläge!$A$6-Zuschläge!$C$6))-(Zuschläge!$A$6<Zuschläge!$C$6)*(G8+(F8>G8)-F8))*(F8<>G8)*(Zuschläge!$A$6<>Zuschläge!$C$6)*24)
N8	=WENN(C8="";"";WENN(UND(WOCHENTAG(A8)=7);MAX(;1-MAX(F8;Zuschläge!$A$10)+MAX(;Zuschläge!$C$10-F8)+MIN(G8;Zuschläge!$C$10)+(G8>Zuschläge!$A$10)*(G8-Zuschläge!$A$10)-(F8<G8)*(1-(Zuschläge!$A$10-Zuschläge!$C$10))-(Zuschläge!$A$10<Zuschläge!$C$10)*(G8+(F8>G8)-F8))*(F8<>G8)*(Zuschläge!$A$10<>Zuschläge!$C$10))*24)
O8	=WENN(C8="";"";WENN(UND(WOCHENTAG(A8)=7;G8>F8);0;WENN(UND(WOCHENTAG(A8)>1;WOCHENTAG(A8)<7);0;WENN(UND(WOCHENTAG(A8)=1;F8<G8);I8+J8;WENN(WOCHENTAG(A8)=1;(I8+J8)-G8;G8))))*24)

habe versucht mal einen Tabellenauszug einzufügen , hoffe das er korrekt dargestellt wird und das es dadruch verständlicher wird was ich erreichen möchte. Wenn Du andere Ideen/Lösungen hast kann die Formel auch komplett anders gestallt werden.


Korni

 

Hallo!

Verstehe ich nicht...
Die Abfrage mit ZÄHLENWENN passt doch!

	A	B	C	D	E	F	G	H
1	Datum	Feiertag Mo-Sa	Feiertag So					Feiertage
2	Samstag, 1. Januar 2011	WAHR	FALSCH					01.01.2011
3	Sonntag, 2. Januar 2011	FALSCH	WAHR					02.01.2011
4	Montag, 3. Januar 2011	FALSCH	FALSCH					07.01.2011
5	Dienstag, 4. Januar 2011	FALSCH	FALSCH					08.01.2011
6	Mittwoch, 5. Januar 2011	FALSCH	FALSCH					09.01.2011
7	Donnerstag, 6. Januar 2011	FALSCH	FALSCH
8	Freitag, 7. Januar 2011	WAHR	FALSCH
9	Samstag, 8. Januar 2011	WAHR	FALSCH
10	Sonntag, 9. Januar 2011	FALSCH	WAHR
11	Montag, 10. Januar 2011	FALSCH	FALSCH
12	Dienstag, 11. Januar 2011	FALSCH	FALSCH
13	Mittwoch, 12. Januar 2011	FALSCH	FALSCH
14	Donnerstag, 13. Januar 2011	FALSCH	FALSCH

Zelle	Formel
B2	=UND(ZÄHLENWENN(H$2:H$6;A2)>0;WOCHENTAG(A2)>1)
C2	=UND(ZÄHLENWENN(H$2:H$6;A2)>0;WOCHENTAG(A2)=1)

(Die Feiertage sind natürlich fiktiv...)

 

Hallo,

Vielen Dank werde es morgen nochmal versuchen. Da hatte ich wohl irgendwo einen Fehler eingebaut.
Wenn es nicht klappen sollte würd ich mich nochmal melden. Ansonsten nochmals vielen Dank für Deine Hilfe. Feedback kommt aufjedenfall!

 

Hallo Schatzi,

habe es eben nochmal mit Deiner Formel probiert. Die Abfrage ob Wahr oder Falsch funktioniert auch soweit.

wenn ich nun aber hinter deiner formel noch den Rest von meiner Einsetze um die Stundenberechnung durchzuführen, erhalte ich die Fehlermeldung das ich entweder zuviele oder zu wenig Argumente der Funktion hinzugefügt habe.

so siehts derzeit bei mir aus.

=WENN(C15="";"";UND(ZÄHLENWENN(Zuschläge!E35:E49;A15)>0;
der Teil Funktioniert

WOCHENTAG(A15)>1);WENN(UND(WOCHENTAG(A15)>1;F15<G15);I15+J15;WENN(WOCHENTAG(A15)>1;(I15+J15)-G15;G15)))*24)
wenn ich diesen Teil hinzusetze erhalte ich die Fehlermeldung

Muss ich für den zweiten Teil auch mit einer anderen Formel arbeiten ?

 

Hallo!

Da fehlt ein WENN.

=WENN(C15="";"";WENN(UND(ZÄHLENWENN(Zuschläge!E35:E49;A15)>0;WOCHENTAG(A15)>1);WENN(UND(WOCHENTAG(A15)>1;F15<G15>1;(I15+J15)-G15;G15)))*24)

 

Hallo Schatzi,

vielen Dank für die Hilfe hin und wieder sieht man den Wald vor lauter Bäumen nicht.

mit dem eingefügten Wenn funktionierte die Formel so gut wie. Jedoch wurden sowohl in der Spalte von Mo-Sa und in der Sonntagsspalte jeweils die Stunden berechnet.

Ich habe sie daher noch etwas angepasst und nun scheint es zu funktionieren, soweit ich es getestet habe funktioniert nun der Bezug auf den Wochentag einwandfrei

Hier meine Lösung:

=WENN(C15="";"";WENN(UND(ZÄHLENWENN(Zuschläge!$E$35:$E$49;A15)*WOCHENTAG(A15)>1);WENN(UND(WOCHENTAG(A15)>1;F15<G15);I15+J15;WENN(WOCHENTAG(A15)>1;(I15+J15)-G15;G15)))*24)

geändert habe ich folgenden Abschnitt

WENN(UND(ZÄHLENWENN(Zuschläge!E35:E49;A15)>0 - das war der alte

WENN(UND(ZÄHLENWENN(Zuschläge!$E$35:$E$49;A15)*WOCHENTAG(A15)>1 - das ist der neue

So weit ich das jetzt geprüft habe s



Evtl kannst Du mir aber bei einem weiteren Problem mit der Formel helfen das ich bisher gar nicht berücksichtigt hatte und eben erst beim umher probieren mir aufgefallen ist. Es kann vorkommen das ich Nachtschichten habe und dem entsprechend z.B. von einem Nichtfeiertag in einen Feiertag hineinarbeite oder halt wie z.B. man von Ostersonntag auf Ostermontag in der Nacht arbeitet.
In diesem Falle sollte natürlich wenn der nächste Tag ein Feiertag ist die entsprechenden Stunden auch berücksichtigt werden.

Ich war beim überlegen ob man einen Bezug auf die vorrige Zeile schaffen kann, jedoch sollte berücksichtigt weren das die Monate in einzelen Tabellenblättern (jeder Monat ein eigenes Blatt) das der Bezug auf die Vorzeile bei Monatswechselprobleme verursachen würde und es mir daher sinnvoller erscheint wenn man einen bezug auf den Vortag schaffen könnte.

Aber leider sind meine Excel Kenntnisse mit diesem Fall total überfordert

Gruß
Korni

 

Hallo!

Hier findest du jeweils etwas über Nachtschiechten und deren Abrechnung:
http://www.excelformeln.de/formeln.html?welcher=9
http://www.excelformeln.de/tips.html?welcher=67

 

Hallo Schatzi,

Danke für die Links

mit der Berechnung von Nachtstunden habe ich kein Problem , die Formel ist in meiner Tabelle bereits enthalten, Spalte M

Ich weiß nur nicht wie ich die Formel Sinnvoll und Plausibel mit der Berechnung der Feiertage verknüpfen kann. Da wenn ich die Formel um die Min/Max erweiter oder anstatt der einfachen Stundenberechnung mit der Min/Max Formel arbeite ich Fehler erhalte oder die Stunden für nicht mehr richtig berechnet werden.

Ich hatte es z.B. über diesen Weg probiert

=WENN(C17="";"";WENN(UND(ZÄHLENWENN(Zuschläge!$E$35:$E$49;A17)*WOCHENTAG(A17)>1);WENN(UND(WOCHENTAG(A17)>1;F17<G17);MAX(;1-MAX(F17;Zuschläge!$A$6)+MAX(;Zuschläge!$C$6-F17)+MIN(G17;Zuschläge!$C$6)+(G17>Zuschläge!$A$6)*(G17-Zuschläge!$A$6)-(F17<G17)*(1-(Zuschläge!$A$6-Zuschläge!$C$6))-(Zuschläge!$A$6<Zuschläge!$C$6)*(G17+(F17>G17)-F17))*(F17<>G17)*(Zuschläge!$A$6<>Zuschläge!$C$6)*24);WENN(WOCHENTAG(A17)>1;(I17+J17)-G17;G17)))*24

in Zuschläge A6 und Zuschläge C 6 habe ich als Zeit jeweils 00:00 eingetragen

 

Hallo!

Mir fehlt momentan die Zeit, mich mit solchen "Monstren" auseinander zu setzen.
Versuch doch mal mit der Formelauswertung (Menü Extras > Formelüberwachung) dem Übeltäter Schritt für Schritt auf die Schliche zu kommen!
Mir hat das schon bei vielen langen Formeln sehr geholfen.

 

Hallo Schatzi,

kann ich gut nachvollziehen. Ich danke Dir sehr für Deine Hilfe Du hast mich schon ein sehr gutes Stück weiter gebracht. Ich werde noch ein wenig umher probieren. Die Funktion Formelüberwachung kannte ich noch nicht und werde es mal versuchen, danke für den Tip.

Sobald ich eine entsprechende Lösung gefunden habe, womit ich dieses Problem gelöst bekommen habe, werde ich das hier nochmal Posten.

Ich wünsche noch einen schönen Abend.