Clever-Excel-Forum

Clever-Excel-Forum > Office-Anwendungen > Excel > Zeile nach KW formatieren
Normale Version: Zeile nach KW formatieren
Du siehst gerade eine vereinfachte Darstellung unserer Inhalte. Normale Ansicht mit richtiger Formatierung.
Seiten: 1 2

ipod86

21.01.2016, 19:27
Hallo,

ich habe einen klassischen Excelkalender der sich automatisch aktualisiert wenn man in einer Zelle das Jahr ändert.
[attachment=3928]
Für das Jahr 2015 habe ich manuell alle KWs eingefärbt. Die Farben zeigen wann wer das Haus Putzen muss. 

KW 1 Partei A
KW2 Partei B
KW3 Partei C
KW 4 Partei A
KW5 Partei B
KW6 Partei C
usw...

Wenn ich den Kalender jetzt von 2015 auf 2016 stelle passen natürlich meine Farben nicht mehr.
Nun suche ich einen Weg, wie jede KW nach und nach nacheinander mit den 3 Farben der Parteien im Haus eingefärbt wird.

schauan

21.01.2016, 22:20
Hallöchen,

Du könntest über den Rest der Division der KW durch 3 eine Formatierung durchführen.

ipod86

22.01.2016, 06:47
Wie meinst du das?
Habe den Kalender an sich aus dem Internet und nur an meine Bedürfnisse angepasst.
Hab zwar ein paar Excelkenntnisse, aber die gehen nicht sooooo weit.

Rabe

22.01.2016, 12:06
Hi,

schau mal hier die bedingten Formatierungen:

Familienkalender 2013
BCDEFGH
4Januar
5MDMDFSS
6   1234
7567891011
812131415161718
919202122232425
10262728293031 
Schriftart wird in dieser Tabelle nicht dargestellt

 verbundene Zellen
B4:H4

verwendete Formeln
Zelle Formel Bereich N/A
B4=DATUM(CalendarYear;1;1)
B6=WENN(TAG(JanSun1)=1;"";WENN(UND(JAHR(JanSun1+1)=CalendarYear;MONAT(JanSun1+1)=1);JanSun1+1;""))
C6=WENN(TAG(JanSun1)=1;"";WENN(UND(JAHR(JanSun1+2)=CalendarYear;MONAT(JanSun1+2)=1);JanSun1+2;""))
D6=WENN(TAG(JanSun1)=1;"";WENN(UND(JAHR(JanSun1+3)=CalendarYear;MONAT(JanSun1+3)=1);JanSun1+3;""))
E6=WENN(TAG(JanSun1)=1;"";WENN(UND(JAHR(JanSun1+4)=CalendarYear;MONAT(JanSun1+4)=1);JanSun1+4;""))
F6=WENN(TAG(JanSun1)=1;"";WENN(UND(JAHR(JanSun1+5)=CalendarYear;MONAT(JanSun1+5)=1);JanSun1+5;""))
G6=WENN(TAG(JanSun1)=1;"";WENN(UND(JAHR(JanSun1+6)=CalendarYear;MONAT(JanSun1+6)=1);JanSun1+6;""))
H6=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1)=CalendarYear;MONAT(JanSun1)=1);JanSun1;"");WENN(UND(JAHR(JanSun1+7)=CalendarYear;MONAT(JanSun1+7)=1);JanSun1+7;""))
B7=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+1)=CalendarYear;MONAT(JanSun1+1)=1);JanSun1+1;"");WENN(UND(JAHR(JanSun1+8)=CalendarYear;MONAT(JanSun1+8)=1);JanSun1+8;""))
C7=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+2)=CalendarYear;MONAT(JanSun1+2)=1);JanSun1+2;"");WENN(UND(JAHR(JanSun1+9)=CalendarYear;MONAT(JanSun1+9)=1);JanSun1+9;""))
D7=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+3)=CalendarYear;MONAT(JanSun1+3)=1);JanSun1+3;"");WENN(UND(JAHR(JanSun1+10)=CalendarYear;MONAT(JanSun1+10)=1);JanSun1+10;""))
E7=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+4)=CalendarYear;MONAT(JanSun1+4)=1);JanSun1+4;"");WENN(UND(JAHR(JanSun1+11)=CalendarYear;MONAT(JanSun1+11)=1);JanSun1+11;""))
F7=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+5)=CalendarYear;MONAT(JanSun1+5)=1);JanSun1+5;"");WENN(UND(JAHR(JanSun1+12)=CalendarYear;MONAT(JanSun1+12)=1);JanSun1+12;""))
G7=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+6)=CalendarYear;MONAT(JanSun1+6)=1);JanSun1+6;"");WENN(UND(JAHR(JanSun1+13)=CalendarYear;MONAT(JanSun1+13)=1);JanSun1+13;""))
H7=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+7)=CalendarYear;MONAT(JanSun1+7)=1);JanSun1+7;"");WENN(UND(JAHR(JanSun1+14)=CalendarYear;MONAT(JanSun1+14)=1);JanSun1+14;""))
B8=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+8)=CalendarYear;MONAT(JanSun1+8)=1);JanSun1+8;"");WENN(UND(JAHR(JanSun1+15)=CalendarYear;MONAT(JanSun1+15)=1);JanSun1+15;""))
C8=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+9)=CalendarYear;MONAT(JanSun1+9)=1);JanSun1+9;"");WENN(UND(JAHR(JanSun1+16)=CalendarYear;MONAT(JanSun1+16)=1);JanSun1+16;""))
D8=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+10)=CalendarYear;MONAT(JanSun1+10)=1);JanSun1+10;"");WENN(UND(JAHR(JanSun1+17)=CalendarYear;MONAT(JanSun1+17)=1);JanSun1+17;""))
E8=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+11)=CalendarYear;MONAT(JanSun1+11)=1);JanSun1+11;"");WENN(UND(JAHR(JanSun1+18)=CalendarYear;MONAT(JanSun1+18)=1);JanSun1+18;""))
F8=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+12)=CalendarYear;MONAT(JanSun1+12)=1);JanSun1+12;"");WENN(UND(JAHR(JanSun1+19)=CalendarYear;MONAT(JanSun1+19)=1);JanSun1+19;""))
G8=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+13)=CalendarYear;MONAT(JanSun1+13)=1);JanSun1+13;"");WENN(UND(JAHR(JanSun1+20)=CalendarYear;MONAT(JanSun1+20)=1);JanSun1+20;""))
H8=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+14)=CalendarYear;MONAT(JanSun1+14)=1);JanSun1+14;"");WENN(UND(JAHR(JanSun1+21)=CalendarYear;MONAT(JanSun1+21)=1);JanSun1+21;""))
B9=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+15)=CalendarYear;MONAT(JanSun1+15)=1);JanSun1+15;"");WENN(UND(JAHR(JanSun1+22)=CalendarYear;MONAT(JanSun1+22)=1);JanSun1+22;""))
C9=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+16)=CalendarYear;MONAT(JanSun1+16)=1);JanSun1+16;"");WENN(UND(JAHR(JanSun1+23)=CalendarYear;MONAT(JanSun1+23)=1);JanSun1+23;""))
D9=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+17)=CalendarYear;MONAT(JanSun1+17)=1);JanSun1+17;"");WENN(UND(JAHR(JanSun1+24)=CalendarYear;MONAT(JanSun1+24)=1);JanSun1+24;""))
E9=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+18)=CalendarYear;MONAT(JanSun1+18)=1);JanSun1+18;"");WENN(UND(JAHR(JanSun1+25)=CalendarYear;MONAT(JanSun1+25)=1);JanSun1+25;""))
F9=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+19)=CalendarYear;MONAT(JanSun1+19)=1);JanSun1+19;"");WENN(UND(JAHR(JanSun1+26)=CalendarYear;MONAT(JanSun1+26)=1);JanSun1+26;""))
G9=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+20)=CalendarYear;MONAT(JanSun1+20)=1);JanSun1+20;"");WENN(UND(JAHR(JanSun1+27)=CalendarYear;MONAT(JanSun1+27)=1);JanSun1+27;""))
H9=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+21)=CalendarYear;MONAT(JanSun1+21)=1);JanSun1+21;"");WENN(UND(JAHR(JanSun1+28)=CalendarYear;MONAT(JanSun1+28)=1);JanSun1+28;""))
B10=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+22)=CalendarYear;MONAT(JanSun1+22)=1);JanSun1+22;"");WENN(UND(JAHR(JanSun1+29)=CalendarYear;MONAT(JanSun1+29)=1);JanSun1+29;""))
C10=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+23)=CalendarYear;MONAT(JanSun1+23)=1);JanSun1+23;"");WENN(UND(JAHR(JanSun1+30)=CalendarYear;MONAT(JanSun1+30)=1);JanSun1+30;""))
D10=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+24)=CalendarYear;MONAT(JanSun1+24)=1);JanSun1+24;"");WENN(UND(JAHR(JanSun1+31)=CalendarYear;MONAT(JanSun1+31)=1);JanSun1+31;""))
E10=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+25)=CalendarYear;MONAT(JanSun1+25)=1);JanSun1+25;"");WENN(UND(JAHR(JanSun1+32)=CalendarYear;MONAT(JanSun1+32)=1);JanSun1+32;""))
F10=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+26)=CalendarYear;MONAT(JanSun1+26)=1);JanSun1+26;"");WENN(UND(JAHR(JanSun1+33)=CalendarYear;MONAT(JanSun1+33)=1);JanSun1+33;""))
G10=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+27)=CalendarYear;MONAT(JanSun1+27)=1);JanSun1+27;"");WENN(UND(JAHR(JanSun1+34)=CalendarYear;MONAT(JanSun1+34)=1);JanSun1+34;""))
H10=WENN(TAG(JanSun1)=1;WENN(UND(JAHR(JanSun1+28)=CalendarYear;MONAT(JanSun1+28)=1);JanSun1+28;"");WENN(UND(JAHR(JanSun1+35)=CalendarYear;MONAT(JanSun1+35)=1);JanSun1+35;""))

definierte Namen
Name Bezieht sich auf Tabelle Z1S1-Formel
CalendarYear='Familienkalender 2013'!$B$1='Familienkalender 2013'!R1C2
ImportantDates='Familienkalender 2013'!#BEZUG!='Familienkalender 2013'!#REF!
JanSun1=DATUM(CalendarYear;1;1)-WOCHENTAG(DATUM(CalendarYear;1;1))+1=DATE(CalendarYear,1,1)-WEEKDAY(DATE(CalendarYear,1,1))+1

Bedingte Formatierung Haupttabelle 1
Wird angewendet auf   Nr. Bed.  Regeltyp  Operator   Formel1   Formel2   Format Schrift Füllfarbe   Unterstrichen   Schrift- farbe Muster   Musterfarbe Typ Bereich
$B$6:$H$11,$K$6:$Q$11,$T$6:$Z$11,$B$15:$H$20,$K$15:$Q$20,$T$15:$Z$20,$B$24:$H$29,$K$24:$Q$29,$T$24:$Z$29,$B$33:$H$38,$K$33:$Q$38,$T$33:$Z$3801.Bed.: Formel ist=REST(KALENDERWOCHE(B6;21);3)=0192       2B6:H11,K6:Q11,T6:Z11,B15:H20,K15:Q20,T15:Z20,B24:H29,K24:Q29,T24:Z29,B33:H38,K33:Q38,T33:Z38
$B$6:$H$11,$K$6:$Q$11,$T$6:$Z$11,$B$15:$H$20,$K$15:$Q$20,$T$15:$Z$20,$B$24:$H$29,$K$24:$Q$29,$T$24:$Z$29,$B$33:$H$38,$K$33:$Q$38,$T$33:$Z$3802.Bed.: Formel ist=REST(KALENDERWOCHE(B6;21);3)=15296274       2B6:H11,K6:Q11,T6:Z11,B15:H20,K15:Q20,T15:Z20,B24:H29,K24:Q29,T24:Z29,B33:H38,K33:Q38,T33:Z38
$B$6:$H$11,$K$6:$Q$11,$T$6:$Z$11,$B$15:$H$20,$K$15:$Q$20,$T$15:$Z$20,$B$24:$H$29,$K$24:$Q$29,$T$24:$Z$29,$B$33:$H$38,$K$33:$Q$38,$T$33:$Z$3803.Bed.: Formel ist=REST(KALENDERWOCHE(B6;21);3)=265535       2B6:H11,K6:Q11,T6:Z11,B15:H20,K15:Q20,T15:Z20,B24:H29,K24:Q29,T24:Z29,B33:H38,K33:Q38,T33:Z38
$B$6:$H$11,$K$6:$Q$11,$T$6:$Z$11,$B$15:$H$20,$K$15:$Q$20,$T$15:$Z$20,$B$24:$H$29,$K$24:$Q$29,$T$24:$Z$29,$B$33:$H$38,$K$33:$Q$38,$T$33:$Z$3804.Bed.: Formel ist=SVERWEIS(B6;ImportantDates;1;FALSCH)=B67926015       2B6:H11,K6:Q11,T6:Z11,B15:H20,K15:Q20,T15:Z20,B24:H29,K24:Q29,T24:Z29,B33:H38,K33:Q38,T33:Z38
Bedingte Formatierung Haupttabelle 2 oberere/unterer Bereich  
Wird angewendet auf   Nr. Bed. Format Zelle Anhalten Auswahl   Anzeige   Anzahl   Durchschnitt   Typ   Bereich
$B$6:$H$11,$K$6:$Q$11,$T$6:$Z$11,$B$15:$H$20,$K$15:$Q$20,$T$15:$Z$20,$B$24:$H$29,$K$24:$Q$29,$T$24:$Z$29,$B$33:$H$38,$K$33:$Q$38,$T$33:$Z$3801.Bed.   Falsch          2B6:H11,K6:Q11,T6:Z11,B15:H20,K15:Q20,T15:Z20,B24:H29,K24:Q29,T24:Z29,B33:H38,K33:Q38,T33:Z38
$B$6:$H$11,$K$6:$Q$11,$T$6:$Z$11,$B$15:$H$20,$K$15:$Q$20,$T$15:$Z$20,$B$24:$H$29,$K$24:$Q$29,$T$24:$Z$29,$B$33:$H$38,$K$33:$Q$38,$T$33:$Z$3802.Bed.   Falsch          2B6:H11,K6:Q11,T6:Z11,B15:H20,K15:Q20,T15:Z20,B24:H29,K24:Q29,T24:Z29,B33:H38,K33:Q38,T33:Z38
$B$6:$H$11,$K$6:$Q$11,$T$6:$Z$11,$B$15:$H$20,$K$15:$Q$20,$T$15:$Z$20,$B$24:$H$29,$K$24:$Q$29,$T$24:$Z$29,$B$33:$H$38,$K$33:$Q$38,$T$33:$Z$3803.Bed.   Falsch          2B6:H11,K6:Q11,T6:Z11,B15:H20,K15:Q20,T15:Z20,B24:H29,K24:Q29,T24:Z29,B33:H38,K33:Q38,T33:Z38
$B$6:$H$11,$K$6:$Q$11,$T$6:$Z$11,$B$15:$H$20,$K$15:$Q$20,$T$15:$Z$20,$B$24:$H$29,$K$24:$Q$29,$T$24:$Z$29,$B$33:$H$38,$K$33:$Q$38,$T$33:$Z$3804.Bed.   Falsch          2B6:H11,K6:Q11,T6:Z11,B15:H20,K15:Q20,T15:Z20,B24:H29,K24:Q29,T24:Z29,B33:H38,K33:Q38,T33:Z38
Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.


Füllfarben und Muster
Zelle Rot Grün Blau Color Muster Farbe
B4 13616824816296072
B5:H5 16920724916371625
Zellen mit Füllfarbe automatisch werden nicht dargestellt

Schriftformate
Zelle Rot Grün Blau Color Stil Unterstreichung Effekte Durchgestrichen Schriftart
B4:H4, B5:B9, B10:H10, C5:C10, D5: D10, E5:E10, F5:F10, G5:G10 Fett  
Zellen mit Schriftformatierung automatisch werden nicht dargestellt
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 21.07 einschl. 64 Bit

ipod86

22.01.2016, 21:26
Danke für deine Mühe.

Habe jetzt mal versucht alles umzusetzen.
Doch leider sieht alles noch etwas komisch aus.

Wollte nicht wieder die xlsx hochladen, aber das Addin in deiner Signatur gibt mir immer eine Fehlermeldung, wenn ich den Code erzeugen will.

Habe also die Datei nochmal hochgeladen.

[attachment=3944]

atilla

22.01.2016, 22:23
Hallo,

die von Ralf verwendete Funktion Kalenderwoche funktioniert ab Excel 2010.

Wenn Du eine ältere Version nutzt, dann arbeitet diese Formel richtig für Kalenderwoche:

In Zelle F6: =KÜRZEN((F6-DATUM(JAHR(F6+3-REST(F6-2;7));1;REST(F6-2;7)-9))/7)

Siehe hier

ipod86

23.01.2016, 18:45
Nutze Excel 2016, sollte also nicht das Problem sein.

P.S.:
Mein versuch ist am weiten Tabellenblatt, am erste ist noch die "händische" Variante vom 2015.

GMG-CC

23.01.2016, 20:27
Moin,

vielleicht kannst du dir ja hier bei Excel-ist-sexy eine Anregung holen. Die zweite Tabelle hat die farblichen Hinterlegungen und das passt sich natürlich automatisch jedes Jahr an. Sogar den 29. Februar gibt es dieses Jahr Angel

schauan

23.01.2016, 21:00
Hallöchen,

ich sehe mit Ralf's Formel keine Probleme.
Arbeitsblatt mit dem Namen 'Familienkalender 2013'
 BCDEFGH
4Januar
5MDMDFSS
6   1234
7567891011
812131415161718
919202122232425
10262728293031 

NameBezug
CalendarYear='Familienkalender 2013'!$B$1
JanSun1=DATUM(CalendarYear;1;1)-WOCHENTAG(DATUM(CalendarYear;1;1))+1

ZelleFormel
B6=WENN(TAG(JanSun1)=1;"";WENN(UND(JAHR(JanSun1+1)=CalendarYear;MONAT(JanSun1+1)=1);JanSun1+1;""))

Zellebedingte Formatierung...Format
B61: =REST(KALENDERWOCHE(B6;21);3)=2abc
B62: =REST(KALENDERWOCHE(B6;21);3)=1abc
B63: =REST(KALENDERWOCHE(B6;21);3)=0abc
Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg

ipod86

24.01.2016, 12:16
Hab jetzt alles doppelt kontrolliert. 
Irgendwie hab ich wohl ein Verständnisproblem.

Hab die 3 Regeln in der Reihenfolge wie bei euch eingegeben. Sie beziehen sich auf alle Zellen mit Kalendertagen.

Hier mal ein Screen (bekomme die Tabellen nichts ins HTML)

[attachment=3958]
Seiten: 1 2
Clever-Excel-Forum > Office-Anwendungen > Excel > Zeile nach KW formatieren