Clever-Excel-Forum

Hi,

ich habe hier eine Liste mit Datums-Bereichen für Ferien und da möchte ich rechts davon eine Liste aller Ferientage untereinanderweg geschrieben haben, wie in der Spalte I manuell gezeigt. In I3 wird mit Drehfeld das Jahr ausgewählt. Ich möchte mit dieser Liste in einem Jahreskalender mit bedingter Formatierung die Ferientage bunt einfärben.
Wie kann ich die Tagesliste erstellen? Mit Formel? Mit VBA?

Ferien

	A	B	C	D	E	F	G	H	I
3		Winterferien	Osterferien	Pfingstferien	Sommerferien	Herbstferien	Weihnachtsferien		2016
4	2012		02.04. - 13.04.	29.05. - 09.06.	26.07. - 08.09.	29.10. - 02.11.	24.12. - 05.01.		29.03.2016
5	2013		25.03. - 05.04.	21.05. - 01.06.	25.07. - 07.09.	28.10. - 30.10.	23.12. - 04.01.		30.03.2016
6	2014		14.04. - 25.04.	10.06. - 21.06.	31.07. - 13.09.	27.10. - 30.10.	22.12. - 05.01.		31.03.2016
7	2015		30.03. - 10.04.	26.05. - 06.06.	30.07. - 12.09.	02.11. - 06.11.	23.12. - 09.01.		01.04.2016
8	2016		29.03. - 02.04.	17.05. - 28.05.	28.07. - 10.09.	02.11. - 04.11.	23.12. - 07.01.		02.04.2016
9	2017	'-	10.04. - 21.04.	06.06. - 16.06.	27.07. - 09.09.	30.10. - 03.11.	22.12. - 05.01.		17.05.2016
10	2018	'-	26.03. - 06.04.	22.05. - 02.06.	26.07. - 08.09.	29.10. - 02.11.	24.12. - 05.01.		18.05.2016
11	2019	'-	15.04. - 27.04.	11.06. - 21.06.	29.07. - 10.09.	28.10. - 30.10.	23.12. - 04.01.		19.05.2016
12	2020	'-	06.04. - 18.04.	02.06. - 13.06.	30.07. - 12.09.	'-	'-		20.05.2016

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.08 einschl. 64 Bit

Hallo,

Zitat:da möchte ich rechts davon eine Liste aller Ferientage untereinanderweg geschrieben haben

warum? Man könnte das auch abfragen, ohne es aufzudröseln.

Hi Edgar,

(20.11.2015, 15:36)BoskoBiati schrieb: [ -> ]warum? Man könnte das auch abfragen, ohne es aufzudröseln.

Ich möchte mit dieser Liste in einem Jahreskalender mit bedingter Formatierung die Ferientage bunt einfärben.
Aber wenn das auch anders geht, ...

Hallo,

Tabelle1

	B	C	D	E	F	G	H	I	J
3		Winterferien	Osterferien	Pfingstferien	Sommerferien	Herbstferien	Weihnachtsferien		2016
4	2012		02.04. - 13.04.	29.05. - 09.06.	26.07. - 08.09.	29.10. - 02.11.	24.12. - 05.01.		29.03.2016
5	2013		25.03. - 05.04.	21.05. - 01.06.	25.07. - 07.09.	28.10. - 30.10.	23.12. - 04.01.		30.03.2016
6	2014		14.04. - 25.04.	10.06. - 21.06.	31.07. - 13.09.	27.10. - 30.10.	22.12. - 05.01.		31.03.2016
7	2015		30.03. - 10.04.	26.05. - 06.06.	30.07. - 12.09.	02.11. - 06.11.	23.12. - 09.01.		01.04.2016
8	2016		29.03. - 02.04.	17.05. - 28.05.	28.07. - 10.09.	02.11. - 04.11.	23.12. - 07.01.		02.04.2016
9	2017	'-	10.04. - 21.04.	06.06. - 16.06.	27.07. - 09.09.	30.10. - 03.11.	22.12. - 05.01.		16.05.2015
10	2018	'-	26.03. - 06.04.	22.05. - 02.06.	26.07. - 08.09.	29.10. - 02.11.	24.12. - 05.01.		18.05.2016
11	2019	'-	15.04. - 27.04.	11.06. - 21.06.	29.07. - 10.09.	28.10. - 30.10.	23.12. - 04.01.		19.05.2016
12	2020	'-	06.04. - 18.04.	02.06. - 13.06.	30.07. - 12.09.	'-	'-		20.05.2016

Bedingte Formatierungen der Tabelle

Zelle Nr.: / Bedingung Format J4 1. / Formel ist =Ferientag Abc

Namen in Formeln Zelle Name Bezieht sich auf J4 Ferientag =SUMME((Tabelle1!$J4>=(LINKS(INDEX(Tabelle1!$D$4:$D$12;Tabelle1!$J$3-2011):INDEX(Tabelle1!$H$4:$H$12;Tabelle1!$J$3-2011);6)&Tabelle1!$J$3)*1)*(Tabelle1!$J4<=(RECHTS(INDEX(Tabelle1!$D$4:$D$12;Tabelle1!$J$3-2011):INDEX(Tabelle1!$H$4:$H$12;Tabelle1!$J$3-2011);6)&Tabelle1!$J$3)*1)) Namen verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Hallo Edgar,

ich habe es nun überprüft anhand einer Liste der gesamten Tage des Jahres.

Dabei fällt auf, daß die Weihnachtsferien nicht eingefärbt werden, vermutlich, weil sie in einem Jahr anfangen und im anderen enden. Da die Daten so aus dem Internet runtergeladen werden, möchte ich das auch nicht aufsplitten.


Ich wollte ja eigentlich die Tagesdatümer als Liste aus der Tabelle rausziehen und dann in dem tatsächlichen Kalenderblatt die Tage einfärben, wenn das Datum in der Zelle mit einem Datum in der Liste übereinstimmt, deswegen ist das tatsächliche Kalenderblatt anders aufgeteilt, hier mal das erste Quartal:

Jahreskalender

	A	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
1		2015																									kein Schaltjahr
2		Januar									Februar									März
3		KW	Mo	Di	Mi	Do	Fr	Sa	So		KW	Mo	Di	Mi	Do	Fr	Sa	So		KW	Mo	Di	Mi	Do	Fr	Sa	So
4		1				1	2	3	4		5							1		9							1
5		2	5	6	7	8	9	10	11		6	2	3	4	5	6	7	8		10	2	3	4	5	6	7	8
6		3	12	13	14	15	16	17	18		7	9	10	11	12	13	14	15		11	9	10	11	12	13	14	15
7		4	19	20	21	22	23	24	25		8	16	17	18	19	20	21	22		12	16	17	18	19	20	21	22
8		5	26	27	28	29	30	31			9	23	24	25	26	27	28			13	23	24	25	26	27	28	29
9																				14	30	31
10
11		April									Mai									Juni

verwendete Formeln
Zelle	Formel	Bereich	N/A
AA1	=WENN(TAG(--("1.2."&B1)+28)<>29;"kein ";"")&"Schaltjahr"
C4,L4,U4	=WENN(TEXT(DATUM($B$1;MONAT(B2&1);1);"TTT")=C3;DATUM($B$1;MONAT(B2&1);1);"")
D4,M4,V4	=WENN(C4<>"";C4+1;WENN(TEXT(DATUM($B$1;MONAT(B2&1);1);"TTT")=D3;DATUM($B$1;MONAT(B2&1);1);""))
E4,N4,W4	=WENN(D4<>"";D4+1;WENN(TEXT(DATUM($B$1;MONAT(B2&1);1);"TTT")=E3;DATUM($B$1;MONAT(B2&1);1);""))
F4,O4,X4	=WENN(E4<>"";E4+1;WENN(TEXT(DATUM($B$1;MONAT(B2&1);1);"TTT")=F3;DATUM($B$1;MONAT(B2&1);1);""))
G4,P4,Y4	=WENN(F4<>"";F4+1;WENN(TEXT(DATUM($B$1;MONAT(B2&1);1);"TTT")=G3;DATUM($B$1;MONAT(B2&1);1);""))
H4,Q4,Z4	=WENN(G4<>"";G4+1;WENN(TEXT(DATUM($B$1;MONAT(B2&1);1);"TTT")=H3;DATUM($B$1;MONAT(B2&1);1);""))
I4,R4,AA4	=WENN(H4<>"";H4+1;WENN(TEXT(DATUM($B$1;MONAT(B2&1);1);"TTT")=I3;DATUM($B$1;MONAT(B2&1);1);""))
C5:C7,L5:L7,U5:U7	=MAX(C4:I4)+SPALTE($B$1)-1
D5:I7,M5:R7,V5:AA7	=C5+1
B4:B9,K4:K9,T4:T9	=WENN(ANZAHL(C4:I4)=0;"";KÜRZEN((MIN(C4:I4)-DATUM(JAHR(MIN(C4:I4)+3-REST(MIN(C4:I4)-2;7));1;REST(MIN(C4:I4)-2;7)-9))/7))
C8:C9,L8:L9,U8:U9	=WENN(I7="";"";WENN(MONAT(I7+1)=MONAT(I7);I7+1;""))
D8:I9,M8:R9,V8:AA9	=WENN(C8="";"";WENN(MONAT(C8+1)=MONAT(C8);C8+1;""))
B2,K2,T2,B11,K11,T11	=TEXT((KÜRZEN((ZEILE()-1)/9;)*3+1+KÜRZEN((SPALTE()-1)/9;))*30;"MMMM")

definierte Namen
Name	Bezieht sich auf	Tabelle	Z1S1-Formel
Ferientag	=SUMME((Ferien!$I1>=(LINKS(INDEX(Ferien!$C$4:$C$12;Ferien!$I$3-2011):INDEX(Ferien!$G$4:$G$12;Ferien!$I$3-2011);6)&Ferien!$I$3)*1)*(Ferien!$I1<=(RECHTS(INDEX(Ferien!$C$4:$C$12;Ferien!$I$3-2011):INDEX(Ferien!$G$4:$G$12;Ferien!$I$3-2011);6)&Ferien!$I$3)*1))		=SUM((Ferien!RC9>=(LEFT(INDEX(Ferien!R4C3:R12C3,Ferien!R3C9-2011):INDEX(Ferien!R4C7:R12C7,Ferien!R3C9-2011),6)&Ferien!R3C9)*1)*(Ferien!RC9<=(RIGHT(INDEX(Ferien!R4C3:R12C3,Ferien!R3C9-2011):INDEX(Ferien!R4C7:R12C7,Ferien!R3C9-2011),6)&Ferien!R3C9)*1))
ft	=Jahreskalender!$AG$2:$AJ$26	Jahreskalender	=Jahreskalender!R2C33:R26C36

Bedingte Formatierung Haupttabelle 1
Wird angewendet auf	Nr. Bed.  Regeltyp	Operator	Formel1	Formel2	Format Schrift Füllfarbe	Unterstrichen	Schrift- farbe	Muster	Musterfarbe	Typ	Bereich
$C$3:$I$3,$L$30:$R$30,$L$3:$R$3,$U$3:$AA$3,$C$12:$I$12,$L$12:$R$12,$U$12:$AA$12,$C$21:$I$21,$L$21:$R$21,$U$21:$AA$21,$C$30:$I$30,$U$30:$AA$30	01.Bed.: Formel ist		=SVERWEIS($C1;ft;4;0)="X"		16764108					1	C3:I3,L30:R30,L3:R3,U3:AA3,C12:I12,L12:R12,U12:AA12,C21:I21,L21:R21,U21:AA21,C30:I30,U30:AA30
$C$3:$I$3,$L$30:$R$30,$L$3:$R$3,$U$3:$AA$3,$C$12:$I$12,$L$12:$R$12,$U$12:$AA$12,$C$21:$I$21,$L$21:$R$21,$U$21:$AA$21,$C$30:$I$30,$U$30:$AA$30	02.Bed.: Zellwert ist	gleich	=HEUTE()		16751001					1	C3:I3,L30:R30,L3:R3,U3:AA3,C12:I12,L12:R12,U12:AA12,C21:I21,L21:R21,U21:AA21,C30:I30,U30:AA30
$C$22:$G$27,$C$4:$G$9,$U$22:$Y$27,$U$9:$Y$9,$L$4:$P$9,$C$13:$G$18,$L$31:$P$36,$L$13:$P$18,$U$13:$Y$18,$L$22:$P$27,$C$31:$G$36,$U$31:$Y$36	01.Bed.: Formel ist		=SVERWEIS(C4;ft;4;0)="X"		13434828					1	C22:G27,C4:G9,U22:Y27,U9:Y9,L4: P9,C13:G18,L31: P36,L13: P18,U13:Y18,L22: P27,C31:G36,U31:Y36
$C$22:$G$27,$C$4:$G$9,$U$22:$Y$27,$U$9:$Y$9,$L$4:$P$9,$C$13:$G$18,$L$31:$P$36,$L$13:$P$18,$U$13:$Y$18,$L$22:$P$27,$C$31:$G$36,$U$31:$Y$36	02.Bed.: Zellwert ist	gleich	=HEUTE()		16751001					1	C22:G27,C4:G9,U22:Y27,U9:Y9,L4: P9,C13:G18,L31: P36,L13: P18,U13:Y18,L22: P27,C31:G36,U31:Y36
$H$4:$I$9,$Z$22:$AA$27,$H$22:$I$27,$H$13:$I$18,$Z$9:$AA$9,$Q$4:$R$9,$Z$13:$AA$18,$H$31:$I$36,$Q$13:$R$18,$Q$31:$R$36,$Q$22:$R$27,$Z$31:$AA$36	01.Bed.: Formel ist		=SVERWEIS(H4;ft;4;0)="X"		13434828					1	H4:I9,Z22:AA27,H22:I27,H13:I18,Z9:AA9,Q4:R9,Z13:AA18,H31:I36,Q13:R18,Q31:R36,Q22:R27,Z31:AA36
$H$4:$I$9,$Z$22:$AA$27,$H$22:$I$27,$H$13:$I$18,$Z$9:$AA$9,$Q$4:$R$9,$Z$13:$AA$18,$H$31:$I$36,$Q$13:$R$18,$Q$31:$R$36,$Q$22:$R$27,$Z$31:$AA$36	02.Bed.: Zellwert ist	gleich	=HEUTE()		16751001					1	H4:I9,Z22:AA27,H22:I27,H13:I18,Z9:AA9,Q4:R9,Z13:AA18,H31:I36,Q13:R18,Q31:R36,Q22:R27,Z31:AA36
$H$4:$I$9,$Z$22:$AA$27,$H$22:$I$27,$H$13:$I$18,$Z$9:$AA$9,$Q$4:$R$9,$Z$13:$AA$18,$H$31:$I$36,$Q$13:$R$18,$Q$31:$R$36,$Q$22:$R$27,$Z$31:$AA$36	03.Bed.: Zellwert ist	ungleich	=""		12632256					1	H4:I9,Z22:AA27,H22:I27,H13:I18,Z9:AA9,Q4:R9,Z13:AA18,H31:I36,Q13:R18,Q31:R36,Q22:R27,Z31:AA36
$U$4:$Y$8	01.Bed.: Formel ist		=SVERWEIS(U4;ft;4;0)="X"		13434828					1	U4:Y8
$U$4:$Y$8	02.Bed.: Zellwert ist	gleich	=HEUTE()		16751001					1	U4:Y8
$Z$4:$AA$8	01.Bed.: Formel ist		=SVERWEIS(Z4;ft;4;0)="X"		13434828					1	Z4:AA8
$Z$4:$AA$8	02.Bed.: Zellwert ist	gleich	=HEUTE()		16751001					1	Z4:AA8
$Z$4:$AA$8	03.Bed.: Zellwert ist	ungleich	=""		12632256					1	Z4:AA8
$C$10:$I$10,$L$10:$R$10	01.Bed.: Formel ist		=SVERWEIS(C10;ft;4;0)="X"		255					1	C10:I10,L10:R10
$C$10:$I$10,$L$10:$R$10	02.Bed.: Zellwert ist	gleich	=HEUTE()		16751001					1	C10:I10,L10:R10

Bedingte Formatierung Haupttabelle 2					oberere/unterer Bereich
Wird angewendet auf	Nr. Bed.	Format Zelle	Anhalten	Auswahl	Anzeige	Anzahl	Durchschnitt	Typ	Bereich
$C$3:$I$3,$L$30:$R$30,$L$3:$R$3,$U$3:$AA$3,$C$12:$I$12,$L$12:$R$12,$U$12:$AA$12,$C$21:$I$21,$L$21:$R$21,$U$21:$AA$21,$C$30:$I$30,$U$30:$AA$30	01.Bed.		Wahr					1	C3:I3,L30:R30,L3:R3,U3:AA3,C12:I12,L12:R12,U12:AA12,C21:I21,L21:R21,U21:AA21,C30:I30,U30:AA30
$C$3:$I$3,$L$30:$R$30,$L$3:$R$3,$U$3:$AA$3,$C$12:$I$12,$L$12:$R$12,$U$12:$AA$12,$C$21:$I$21,$L$21:$R$21,$U$21:$AA$21,$C$30:$I$30,$U$30:$AA$30	02.Bed.		Wahr					1	C3:I3,L30:R30,L3:R3,U3:AA3,C12:I12,L12:R12,U12:AA12,C21:I21,L21:R21,U21:AA21,C30:I30,U30:AA30
$C$22:$G$27,$C$4:$G$9,$U$22:$Y$27,$U$9:$Y$9,$L$4:$P$9,$C$13:$G$18,$L$31:$P$36,$L$13:$P$18,$U$13:$Y$18,$L$22:$P$27,$C$31:$G$36,$U$31:$Y$36	01.Bed.		Wahr					1	C22:G27,C4:G9,U22:Y27,U9:Y9,L4:P9,C13:G18,L31:P36,L13:P18,U13:Y18,L22:P27,C31:G36,U31:Y36
$C$22:$G$27,$C$4:$G$9,$U$22:$Y$27,$U$9:$Y$9,$L$4:$P$9,$C$13:$G$18,$L$31:$P$36,$L$13:$P$18,$U$13:$Y$18,$L$22:$P$27,$C$31:$G$36,$U$31:$Y$36	02.Bed.		Wahr					1	C22:G27,C4:G9,U22:Y27,U9:Y9,L4:P9,C13:G18,L31:P36,L13:P18,U13:Y18,L22:P27,C31:G36,U31:Y36
$H$4:$I$9,$Z$22:$AA$27,$H$22:$I$27,$H$13:$I$18,$Z$9:$AA$9,$Q$4:$R$9,$Z$13:$AA$18,$H$31:$I$36,$Q$13:$R$18,$Q$31:$R$36,$Q$22:$R$27,$Z$31:$AA$36	01.Bed.		Wahr					1	H4:I9,Z22:AA27,H22:I27,H13:I18,Z9:AA9,Q4:R9,Z13:AA18,H31:I36,Q13:R18,Q31:R36,Q22:R27,Z31:AA36
$H$4:$I$9,$Z$22:$AA$27,$H$22:$I$27,$H$13:$I$18,$Z$9:$AA$9,$Q$4:$R$9,$Z$13:$AA$18,$H$31:$I$36,$Q$13:$R$18,$Q$31:$R$36,$Q$22:$R$27,$Z$31:$AA$36	02.Bed.		Wahr					1	H4:I9,Z22:AA27,H22:I27,H13:I18,Z9:AA9,Q4:R9,Z13:AA18,H31:I36,Q13:R18,Q31:R36,Q22:R27,Z31:AA36
$H$4:$I$9,$Z$22:$AA$27,$H$22:$I$27,$H$13:$I$18,$Z$9:$AA$9,$Q$4:$R$9,$Z$13:$AA$18,$H$31:$I$36,$Q$13:$R$18,$Q$31:$R$36,$Q$22:$R$27,$Z$31:$AA$36	03.Bed.		Wahr					1	H4:I9,Z22:AA27,H22:I27,H13:I18,Z9:AA9,Q4:R9,Z13:AA18,H31:I36,Q13:R18,Q31:R36,Q22:R27,Z31:AA36
$U$4:$Y$8	01.Bed.		Wahr					1	U4:Y8
$U$4:$Y$8	02.Bed.		Wahr					1	U4:Y8
$Z$4:$AA$8	01.Bed.		Wahr					1	Z4:AA8
$Z$4:$AA$8	02.Bed.		Wahr					1	Z4:AA8
$Z$4:$AA$8	03.Bed.		Wahr					1	Z4:AA8
$C$10:$I$10,$L$10:$R$10	01.Bed.		Wahr					1	C10:I10,L10:R10
$C$10:$I$10,$L$10:$R$10	02.Bed.		Wahr					1	C10:I10,L10:R10

Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.08 einschl. 64 Bit

Die Namensformel ist mir zu komplex und somit bekomme ich die Formatierung im Original-Blatt nicht hin. Kannst Du Dir mal den Kalender anschauen?
[attachment=3295]

Hallo Ralf,

der Kalender war doch auch mal meine Baustelle, oder?

[attachment=3300]

Hi Edgar,

(23.11.2015, 15:52)BoskoBiati schrieb: [ -> ]der Kalender war doch auch mal meine Baustelle, oder?

das kann sein, aber das weiß ich nicht mehr.

Jetzt funktioniert es klasse mit den Ferien.

Ich hatte eine andere 'Lösung'

Hallo,

Zitat Ralf:

Zitat:Da die Daten so aus dem Internet runtergeladen werden, möchte ich das auch nicht aufsplitten.

Hi,

(23.11.2015, 18:45)snb schrieb: [ -> ]Ich hatte eine andere 'Lösung'

danke auch für Deine Lösung, aber wie schon Edgar schrieb, stehen die Ferientermine auf einer Internet-Seite und dort ist das genau so formatiert. Damit können die Feriendaten durch einfaches Kopieren ohne weitere Tätigkeiten in die Tabelle übertragen und dann die Tage eingefärbt werden.