Clever-Excel-Forum

Hallo Zusammen,
ich benötige bitte mal Eure Hilfe bei einer Berechnung.
Ich habe mir einen Dienstplan erstellt, der soweit auch funktioniert.
Allerdings rechnet er im Februar nur bis zum 28. 02 :22: 
Wie muss ich meine Formel verändern, damit der automatisch auch den 29.02
in die Berechnungen einbezieht?

Februar

	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1				9. KW
2	Fr	Sa	So	Mo			Arbeits- Stunden	Sonntags stunden	Feiertags- stunden
3	26	27	28	29
4	2		2				27	8
5							24	4
6	2	2	2	2			26	6
7							6
8							20	2
9	2	2	2	2			26	4

Formeln der Tabelle

Zelle Formel AC1 =WENN(ISTFEHLER(FINDEN(AC3;Feiertagsliste));WENN(WOCHENTAG(AC3;2)=1;KÜRZEN((AC3-WOCHENTAG(AC3;2)-DATUM(JAHR(AC3+4-WOCHENTAG(AC3;2));1;-10))/7); "" );WVERWEIS(AC3;Berechnen_der_Feiertage;2;WAHR)) AD1 =WENN(ISTFEHLER(FINDEN(AD3;Feiertagsliste));WENN(WOCHENTAG(AD3;2)=1;KÜRZEN((AD3-WOCHENTAG(AD3;2)-DATUM(JAHR(AD3+4-WOCHENTAG(AD3;2));1;-10))/7); "" );WVERWEIS(AD3;Berechnen_der_Feiertage;2;WAHR)) AE1 =WENN(ISTFEHLER(FINDEN(AE3;Feiertagsliste));WENN(WOCHENTAG(AE3;2)=1;KÜRZEN((AE3-WOCHENTAG(AE3;2)-DATUM(JAHR(AE3+4-WOCHENTAG(AE3;2));1;-10))/7); "" );WVERWEIS(AE3;Berechnen_der_Feiertage;2;WAHR)) AF1 =WENN(AF3="";"";WENN(ISTFEHLER(FINDEN(AF3;Feiertagsliste));WENN(WOCHENTAG(AF3;2)=1;KÜRZEN((AF3-WOCHENTAG(AF3;2)-DATUM(JAHR(AF3+4-WOCHENTAG(AF3;2));1;-10))/7); "" );WVERWEIS(AF3;Berechnen_der_Feiertage;2;WAHR))) AG1 =WENN(AG3="";"";WENN(ISTFEHLER(FINDEN(AG3;Feiertagsliste));WENN(WOCHENTAG(AG3;2)=1;KÜRZEN((AG3-WOCHENTAG(AG3;2)-DATUM(JAHR(AG3+4-WOCHENTAG(AG3;2));1;-10))/7); "" );WVERWEIS(AG3;Berechnen_der_Feiertage;2;WAHR))) AH1 =WENN(AH3="";"";WENN(ISTFEHLER(FINDEN(AH3;Feiertagsliste));WENN(WOCHENTAG(AH3;2)=1;KÜRZEN((AH3-WOCHENTAG(AH3;2)-DATUM(JAHR(AH3+4-WOCHENTAG(AH3;2));1;-10))/7); "" );WVERWEIS(AH3;Berechnen_der_Feiertage;2;WAHR))) AC2 =AC3 AD2 =AD3 AE2 =AE3 AF2 =AF3 AG2 =AG3 AH2 =AH3 AC3 =AB3+1 AD3 =AC3+1 AE3 =AD3+1 AF3 =WENN(AE3>=DATUM(JAHR($B$3);MONAT($B$3)+1;TAG($B$3))-1;"";WENN(AE3=0;0;AE3+1)) AG3 =WENN(AF3>=DATUM(JAHR($B$3);MONAT($B$3)+1;TAG($B$3))-1;"";WENN(AF3=0;0;AF3+1)) AH3 =WENN(AG3>=DATUM(JAHR($B$3);MONAT($B$3)+1;TAG($B$3))-1;"";WENN(AG3=0;0;AG3+1)) AI4 =SUMME(D4:AE4)-AJ4 AJ4 =SUMMENPRODUKT((REST($D$2:$AE$2;7)=1)*1;D4:AE4) AK4 =SUMMENPRODUKT(($D$47:$AH$47=1)*1;$D4:$AH4) AI5 =SUMME(D5:AE5)-AJ5 AJ5 =SUMMENPRODUKT((REST($D$2:$AE$2;7)=1)*1;D5:AE5) AK5 =SUMMENPRODUKT(($D$47:$AH$47=1)*1;$D5:$AH5) AI6 =SUMME(D6:AE6)-AJ6 AJ6 =SUMMENPRODUKT((REST($D$2:$AE$2;7)=1)*1;D6:AE6) AK6 =SUMMENPRODUKT(($D$47:$AH$47=1)*1;$D6:$AH6) AI7 =SUMME(D7:AE7)-AJ7 AJ7 =SUMMENPRODUKT((REST($D$2:$AE$2;7)=1)*1;D7:AE7) AK7 =SUMMENPRODUKT(($D$47:$AH$47=1)*1;$D7:$AH7) AI8 =SUMME(D8:AE8)-AJ8 AJ8 =SUMMENPRODUKT((REST($D$2:$AE$2;7)=1)*1;D8:AE8) AK8 =SUMMENPRODUKT(($D$47:$AH$47=1)*1;$D8:$AH8) AI9 =SUMME(D9:AE9)-AJ9 AJ9 =SUMMENPRODUKT((REST($D$2:$AE$2;7)=1)*1;D9:AE9) AK9 =SUMMENPRODUKT(($D$47:$AH$47=1)*1;$D9:$AH9)

Bedingte Formatierungen der Tabelle

Zelle Nr.: / Bedingung Format AC2 1. / Formel ist = WOCHENTAG(D2;2) > 5 Abc AD2 1. / Formel ist = WOCHENTAG(D2;2) > 5 Abc AE2 1. / Formel ist = WOCHENTAG(D2;2) > 5 Abc AF2 1. / Formel ist = WOCHENTAG(D2;2) > 5 Abc AG2 1. / Formel ist = WOCHENTAG(D2;2) > 5 Abc AH2 1. / Formel ist = WOCHENTAG(D2;2) > 5 Abc AC3 1. / Formel ist = WOCHENTAG(D2;2) > 5 Abc AD3 1. / Formel ist = WOCHENTAG(D2;2) > 5 Abc AE3 1. / Formel ist = WOCHENTAG(D2;2) > 5 Abc AF3 1. / Formel ist = WOCHENTAG(D2;2) > 5 Abc AG3 1. / Formel ist = WOCHENTAG(D2;2) > 5 Abc AH3 1. / Formel ist = WOCHENTAG(D2;2) > 5 Abc AC4 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AD4 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AE4 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AF4 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AG4 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AH4 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AC5 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AD5 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AE5 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AF5 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AG5 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AH5 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AC6 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AD6 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AE6 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AF6 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AG6 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AH6 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AC7 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AD7 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AE7 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AF7 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AG7 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AH7 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AC8 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AD8 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AE8 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AF8 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AG8 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AH8 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AC9 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AD9 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AE9 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AF9 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AG9 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc AH9 1. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6) Abc

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8



Schon jetzt vielen Dank für Eure Hilfe.

Hallo,

Bei Excel 2013 gibt es einige Funktionen, die Dir das Leben erleichtern: z.B. Kalenderwoche oder Wennfehler.
Wochentag kann man ersetzen durch Rest, wobei das Oder in der bed. Form. Unnötig ist, man kann mit >5 beide Werte erschlagen.

Wo soll denn der 29.2. mitberechnet werden????

(20.04.2015, 10:39)BoskoBiati schrieb: [ -> ]Hallo,

Bei Excel 2013 gibt es einige Funktionen, die Dir das Leben erleichtern: z.B. Kalenderwoche oder Wennfehler.
Wochentag kann man ersetzen durch Rest, wobei das Oder in der bed. Form. Unnötig ist, man kann mit >5 beide Werte erschlagen.

Wo soll denn der 29.2. mitberechnet werden????

Hallo Edgar,
danke für deine rasche Antwort. Bin nicht so der Profi. Habe mir den Dienstplan "zusammengebastelt"
In den Spalten AI und AJ muss der 29 mitgerechnet werden.

Hallo,

Arbeitsblatt mit dem Namen 'Tabelle2'
	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	8. KW							9. KW
2	Mo	Di	Mi	Do	Fr	Sa	So	Mo
3	22.02.2016	23.02.2016	24.02.2016	25.02.2016	26.02.2016	27.02.2016	28.02.2016	29.02.2016

Name	Bezug
FT	=Tabelle2!$AA$21:$AA$31

Zelle	Formel
Y1	=WENN((ZÄHLENWENN(FT;Y3)=0)*(REST(Y3;7)=2);KALENDERWOCHE(Y3;21);"")
Y2	=WENN(Y3="";"";Y3)
Y3	=WENN($D$3+SPALTE(U1)>MONATSENDE($D3;0);"";$D$3+SPALTE(U1))

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

in AI 4:



In AJ4:

(20.04.2015, 10:56)BoskoBiati schrieb: [ -> ]Hallo,

Arbeitsblatt mit dem Namen 'Tabelle2'
	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	8. KW							9. KW
2	Mo	Di	Mi	Do	Fr	Sa	So	Mo
3	22.02.2016	23.02.2016	24.02.2016	25.02.2016	26.02.2016	27.02.2016	28.02.2016	29.02.2016

Name	Bezug
FT	=Tabelle2!$AA$21:$AA$31

Zelle	Formel
Y1	=WENN((ZÄHLENWENN(FT;Y3)=0)*(REST(Y3;7)=2);KALENDERWOCHE(Y3;21);"")
Y2	=WENN(Y3="";"";Y3)
Y3	=WENN($D$3+SPALTE(U1)>MONATSENDE($D3;0);"";$D$3+SPALTE(U1))

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

in AI 4:

Code:

=SUMME(D4:AH4)-AJ4

In AJ4:

Code:

=SUMMENPRODUKT((REST($D$2:$AH$2;7)=1)*1;D4:AH4)

Hallo Edgar, Danke für deine Hilfe.
Der einfachheit habe ich die Datei mal hochgeladen.:
Zu den geteilten Dateien "Freigegebene D"
Ist bei der Telekom im Mediencenter.

Bestimmt ist es so einfacher mein Problem zu verstehen.
Die Spalten AI und AJ sollten halt drei Jahre lang bis AE und im 4. Jahr (Schaltjahr) bis AF
rechnen.
Hoffentlich war das so verständlicher.
Danke

Hallo R@iner, (sei nicht Böse..., aber) nutze doch bitte die Möglichkeit Dateien hier direkt im Forum hochzuladen...

Hallo, diese Anfrage kommt mir doch seltsam bekannt vor... ... das hatten wir doch schon, oder?

(20.04.2015, 12:03)freiminute schrieb: [ -> ]Bestimmt ist es so einfacher mein Problem zu verstehen.
Die Spalten AI und AJ sollten halt drei Jahre lang bis AE und im 4. Jahr (Schaltjahr) bis AF
rechnen.
Hoffentlich war das so verständlicher.
Danke

Hallo, ich denke das ginge so..:

=SUMMENPRODUKT((REST($D$2:INDEX(A$2:$AH2;VERGLEICH(0;A3:AH3;-1));7)=1)*1;$D$4:INDEX(A$4:$AH4;VERGLEICH(0;A3:AH3;-1)))

Hallo,

ist doch so einfach zu lösen:

Arbeitsblatt mit dem Namen 'Februar'
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1			5.KW							6.KW							7.KW							8.KW							9.KW
2	2016		Mo	Di	Mi	Do	Fr	Sa	So	Mo	Di	Mi	Do	Fr	Sa	So	Mo	Di	Mi	Do	Fr	Sa	So	Mo	Di	Mi	Do	Fr	Sa	So	Mo			Arbeits- Stunden	Sonntags stunden
3	Februar		01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29
4				2	2	2	2	2	2	2					1	2		2	2	2	2	2	2	2				2		2				27	8

Name	Bezug
Feiertage	=Berechnung!$A$7:$R$7

Zelle	Formel
B3	=--("1."&AP1&"."&B2)
D1	=WENN(D3<>"";WENN((ZÄHLENWENN(Feiertage;D3)=0)*(REST(D3;7)=2);KALENDERWOCHE(D3;21)&".KW";WENNFEHLER("   "&WVERWEIS(D3;Berechnung!$7:$8;2;0);""));"")
D2	=D3
D3	=WENN($B$3+SPALTE(A1)-1>MONATSENDE($B3;0);0;$B$3+SPALTE(A1)-1)
AI4	=SUMME(D4:AH4)-AJ4
AJ4	=SUMMENPRODUKT((REST($D$2:$AH$2;7)=1)*1;D4:AH4)

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Hi Edgar, sicher, wenn man die dritte Zeile so abändert...  :21:

Dafür geht B3 einfacher... =--(AP1&-B2)