Zellen markieren bei mehrfachen Bedingungen

[Soey17]

Hellllooo,



ich hab da mal ein Anliegen.



Ich habe mir einen dynamischen Kalender angelegt

   

In der Spalte rechts daneben möchte ich je nach Tag ein x setzen wenn der Tag zutrifft. Ich möchte allerdings die x' hervorheben, die in einer Woche mindestens 2-mal vorkommen. Das habe ich soweit auch hinbekommen. Nun zu meinem Problem:

Der Kalender ist dynamisch. Man kann oben das Jahr eingeben und zack, ändert sich der ganze Kalender auf das eingegebene Jahr. Folglich verschieben sich die Wochen und meine Bedingung passt nicht mehr für den angegebenen Bereich.

Gibt es hier eventuell eine Verbindung, die die "Doppelbedingung" auf die dynamische Verschiebung des Kalenders anpassen lässt?

Vielen Dank schon mal   :)

[WillWissen]

Hallo,

bitte nicht an alte Threads anhängen. Ich habe deshalb deine Frage abgetrennt.

Um dir helfen zu können, solltest du anstelle des Bildchens deine Datei hochladen.

[schauan]

Hallöchen,

hier mal ein Ansatz. Der für das Datum definierte Bereich - bei mir A1:A21 - muss auch gefüllt sein. Ohne den 21.01. in A21 bekommst Du #ZAHL Ich hab das jetzt übrigens nicht bis zum Jahresende verfolgt, nicht das es da ungewollte Überschneidungen mit den KW gibt ...

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C	D
1	01. Jan	x	2	1
2	02. Jan		1	2
3	03. Jan	x	7	3
4	04. Jan
5	05. Jan
6	06. Jan
7	07. Jan
8	08. Jan
9	09. Jan
10	10. Jan
11	11. Jan
12	12. Jan	x
13	13. Jan	x
14	14. Jan	x
15	15. Jan	x
16	16. Jan	x
17	17. Jan	x
18	18. Jan	x
19	19. Jan	x
20	20. Jan
21	21. Jan

Zelle	Formel
C1	=SUMMENPRODUKT(N(KÜRZEN(($A$1:$A$21-DATUM(JAHR($A$1:$A$21+3-REST($A$1:$A$21-2;7));1;REST($A$1:$A$21-2;7)-9))/7)=D1)*($B$1:$B$21="x"))
D1	=KALENDERWOCHE(A1)
C2	=SUMMENPRODUKT(N(KÜRZEN(($A$1:$A$21-DATUM(JAHR($A$1:$A$21+3-REST($A$1:$A$21-2;7));1;REST($A$1:$A$21-2;7)-9))/7)=D2)*($B$1:$B$21="x"))
C3	=SUMMENPRODUKT(N(KÜRZEN(($A$1:$A$21-DATUM(JAHR($A$1:$A$21+3-REST($A$1:$A$21-2;7));1;REST($A$1:$A$21-2;7)-9))/7)=D3)*($B$1:$B$21="x"))

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

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

[Soey17]

Hallo Andrè,

ich bin mir gerade nicht sicher, ob ich dir nicht folgen kann oder du mir nicht.    Dennoch auf jeden Fall schon mal vielen Dank   :)

Also ich habe diese Exceltabelle. Sie hat nur 2 Spalten. A und B.

A ist der dynamische Kalender, der außer dass sich das Jahr ändert und er sich dadurch verschiebt, keine weitere Funktion hat.
B hingegen ist meine Wahlspalte. Hier setze ich immer an den Tagen ein Kreuz an denen es erforderlich ist.

Beispiel Frau K kauft am 13.05. Chips. Am 17.05. kauft Frau K Kartoffeln. Diese beiden Werte liegen für 2020 in einer Woche (nämlich von Mo 11.05.2020 bis So 17.05.2020).
Excel soll mir nun farblich darstellen, dass Frau K in der Woche 2x einkaufen war oder sogar öfter.

Mit der bedingten Formatierung "Doppelte Werte" kann ich nun für den Wochenabschnitt die Formel anwenden. Kein Problem. Ich kann einstellen, dass sobald 2 Werte in einem fest vorbelegtem Bereich grün markiert werden, sobald mind. 2 Kreuze vorliegen.

Jetzt zu meinem Problem:

Wenn ich den dynamischen Kalender auf 2017 stelle, dann verschiebt sich die Woche, und zwar auf Mo 15.05.2017 bis So 21.05.2017... Somit ist meine vorbelegte Formatierung nicht mehr zutreffend. Die Dopplung der Kreuze würde mir angezeigt, obwohl der 13.05. in der einen Woche und der 17.05. in der anderen Woche. Somit habe ich keine Dopplung mehr innerhalb einer Woche. Der Wert wäre also falsch....

Danke schonmal für die (nochmalige) Hilfe.

[Fred11]

Hallo Soey17,

hier ein Lösungsvorschlag mit einer Hilfsspalte (Spalte B), in der zum jeweiligen Datum die Kalenderwoche ermittelt wird.
Dann können mit ZÄHLENWENNS für jede Woche die Xe  gezählt werden...

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
1	2020	KW	Auswahl
2	01.01.2020	1	x
3	02.01.2020	1
4	03.01.2020	1	x
5	04.01.2020	1
6	05.01.2020	1	x
7	06.01.2020	2
8	07.01.2020	2
9	08.01.2020	2	x
10	09.01.2020	2
11	10.01.2020	2
12	11.01.2020	2
13	12.01.2020	2
14	13.01.2020	3
15	14.01.2020	3	x
16	15.01.2020	3	x
17	16.01.2020	3
18	17.01.2020	3
19	18.01.2020	3
20	19.01.2020	3
21	20.01.2020	4
22	21.01.2020	4
23	22.01.2020	4
24	23.01.2020	4	x
25	24.01.2020	4
26	25.01.2020	4
27	26.01.2020	4

Zelle	Formel
B2	=KALENDERWOCHE(A2;11)

Zelle	bedingte Formatierung...	Format
C2	1: UND(ZÄHLENWENNS($B$2:$B$370;B2;$C$2:$C$370;"X")>=2;C2="X")	abc
C3	1: UND(ZÄHLENWENNS($B$2:$B$370;B3;$C$2:$C$370;"X")>=2;C3="X")	abc
C4	1: UND(ZÄHLENWENNS($B$2:$B$370;B4;$C$2:$C$370;"X")>=2;C4="X")	abc
C5	1: UND(ZÄHLENWENNS($B$2:$B$370;B5;$C$2:$C$370;"X")>=2;C5="X")	abc

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

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

Siehe angehängte Beispieldatei

Gruß
Fred

[Soey17]

Oh Mein Gotttt! Wie geil ist das bitte?!

Du bist der Wahnsinn! Vielen vielen vielen vielen... D A N K!

Es funktioniert, ich bin soooo glücklich, dass ich heulen könnte...

Also wirklich vielen Dank   :)

[Soey17]

Hallo,

ich habe mich leider etwas zu früh gefreut... ^^'

Die Excel ist nun schon nahezu perfekt, aber es stören mich noch zwei Kleinigkeiten, bei denen ich nicht weiß, wie ich die Probleme beheben könnte:

1. Beim Spaltenwechsel erkennt der mir die Dopplung nicht an, obwohl es 1 Woche und sogar die gleiche KW ist...
Definition Spaltenwechsel: Ich bin unten am letzten Tag der Spalte angekommen und muss in der rechten Spalte oben fortsetzen. Der Grund warum ich die Spalte nicht fortlaufend nach unten habe, ist damit ich Papier beim Drucken spare und man es trotzdem - gerade noch so wenigstens - lesen kann  :)
2. Beim Jahreswechsel macht er innerhalb einer Woche eine neue KW. Kann man das verhindern?

Ich habe meinen Kalender mal beigefügt, damit derjenige der mir helfen möchte, weiß was ich meine ^^ und bei den ersten beiden Spalten die ausgeblendete KW-Spalte eingeblendet.

GLG und vielen Dank vorab 

[schauan]

Hallöchen,

kannst Du die Fragen mal anhand konkreter Beispiele beschreiben?

Wenn ich aus 2020 dann 2021 mache und mir die ersten Tage anschaue, ist alles ok. Eine KW geht nun mal bei uns in der Regel von Montag bis Sonntag und nicht von Freitag bis Donnerstag, nur weil das Jahr an einem Freitag beginnt. Die Einfärbung scheint auch in den ersten beiden "Spalten" ok zu sein.

[Fred11]

Hallo Soey17

zu 2:
Mit dieser Formel sollte die KW jetzt richtig berechnet werden:
=KALENDERWOCHE(B11;21)

zu 1:
Dass du das Jahr jetzt auf mehrere Spalten aufteilst, ist für eine solche Auswertung eher ungünstig und macht das jetzt halt komplexer / aufwendiger.
Ich habe die Formel in der bedingten Formatierung entsprechend ergänzt, so dass immer auch noch ein Spalte vor und nach der jeweiligen Spalte mit berücksichtigt wird. Vielleicht gibt's noch eine elegantere Lösung, aber so sollte es funktionieren.
(Achtung, die Formeln in der bed. Formatierung für die erste und die letzte Spalte sind jetzt unterschiedlich zu den Formeln in den "mittleren" Spalten)
z.B. Formel in Spalte H:
=UND((ZÄHLENWENNS($C$11:$C$101;G11;$D$11:$D$101;"X")+ZÄHLENWENNS($G$11:$G$101;G11;$H$11:$H$101;"X")+ZÄHLENWENNS($K$11:$K$101;G11;$L$11:$L$101;"X"))>=2;H11="X")

Siehe angehängte Beispieldatei

Gruß
Fred

[Soey17]

Ich raffe die Textschreibung hier nicht....

---

Jaaaaa genau sooo funktioniert es. Nochmal vielen Dank! :)

Ich bin total begeistert. Ich glaube jetzt ist es perfekt.

Und der Pokal gebührt FREEEEED :18:

Eine unabhängige Frage hätte ich noch. Anderes Thema. Man kann hier nach Schlagwörtern filtern, wenn man ganz viele Spalten und Zeilen mit Text hat:

[
Bild bitte so als Datei hochladen: Klick mich!
]

Kann man hier auch mit einer "oder"-Verknüpfung innerhalb dieser 1 Zeile arbeiten?

[
Bild bitte so als Datei hochladen: Klick mich!
]

Oder muss ich für jeden Begriff eine eigene Regel machen?

Beispielsweise ich möchte das mir alle AB's und CD's in hellroter Füllung angezeigt werden.

Aaaach keine Ahnung warum meine Screenshots nicht dargestellt werden -.-

Also unter Bedingter Formatierung -> Regeln zum Hervorheben von Zellen -> Textinhalt -> Alles was ich wissen möchte bezieht sich da auf diese eine Zeile