[Gelöst] Excel - Inhalte von Zellen zählen, wenn Formatierungsbedingung erfüllt ist

[Sinush]

Hallo zusammen,

ich habe eine Frage an die Excel-Cracks hier.

Ich bin gemeinsam mit meinem Teamleiter für die Dienstplanung zuständig und ich habe zur Erleichterung dessen eine Excel-Vorlage geschrieben, in der wir nur noch Monat, Anzahl Werktage und Dienste (in Form von Kürzeln) eintragen müssen.
Die Tabellen prüfen anhand einer weiteren kleinen Tabelle (KollegInnen und Stundenumfang), wieviele Stunden jeweils in dem Monat gearbeitet werden sollen und wieviele Stunden jeweilige Dienste (24h-Dienste, Tagdienste, Teambesprechungen, etc.) bringen. Die Übersicht färbt anhand einer bedingten Formatierung die Wochenend-Tage (Samstag und Sonntag) automatisch ein, die Wochentage werden automatisch anhand der Angabe des Monates ermittelt (z.B. 03.24 für März 2024, der 01.03. ist dann automatisch ein Freitag).

Ich möchte nun eine weitere Funktion einfügen:
Excel soll mir ausgeben, welche KollegIn in dem Monat wieviele Wochenend-Dienste übernimmt.

Mein erster Gedanke wäre also unter der Monatsübersicht eine Abfrage, wieviele Wochenend-Felder [Wochentag($A3)=7 bzw. =1] nicht leer sind (also entweder ein "NB" für Nachtbereitschaft oder ein * für Folgetag der Nachtbereitschaft beinhalten) und dies dann numerisch zurückgeben.

Nur wie?

Hier mal ein Screenshot meiner Tabellen mit erklärenden Kommentaren. Solltet Ihr Fragen zu den verwendeten Funktionen haben, fragt. Gerne auch Hinweise zur weiteren Vereinfachung der Tabelle.

 

[awehring]

Bin jetzt nicht in die Details deiner Frage eingestiegen.

Zählewenn(...) kennst du?

 

[Sinush]

Ja, kenne ich...nur wie sage ich der Funktion, dass sie nur die Zellen mit Inhalt der Wochenendtage zählen soll? Das müsste irgendwie verknüpft werden mit der Angabe Wochentag($A3=7, bzw. =1), und dies bekomme ich noch nicht hin.

 

[awehring]

Ein gängiger Trick ist, eine Hilfsspalte (oder -zeile) anzulegen. In der steht dann eine 0 oder 1, was mit beliebig komplexen Bedingungen errechnet wird. Zählewenn() oder Summewenn() wird dann auf diese Hilfsspalte angewendet.
Die Hilfsspalte kann man ausblenden, damit sie den Benutzer nicht verwirrt.

 

[Sinush]

Das heißt, dass ich in der Hilfsspalte mit verschachteltem WENN arbeiten müsste:

1. Prüfung, ob die abgefragte Zelle ein Samstag ist
2. Wenn ja, dann zählen, wenn die entsprechende Zelle "NB" oder "*" enthält und Anzahl zurückgeben
3. Ansonsten Prüfung, ob die abgefragte Zelle ein Sonntag ist
4. Wenn ja, dann zählen, wenn die entsprechende Zelle "NB" oder "*" enthält und Anzahl zurückgeben
5. Ansonsten Zelle leer lassen

=WENN(abgefragter Bereich="Sa.");ZÄHLENWENN(UND(abgefragte Zelle="NB";abgefragte Zelle="*");WENN(abgefragter Bereich="So.";ZÄHLENWENN(UND(abgefragte Zelle="NB";abgefragte Zelle="*");""))

So?

Edit:

Ne, klappt nicht. So müsste ich ja für jeden Tag eine einzelne Abfrage machen. Und das nochmal für jede KollegIn.

Könnte vielleicht =SVERWEIS helfen?

In B3:B33 schauen, ob Sa. oder So. steht und dann den Wert aus der entsprechenden Zelle in C3:C33 wiedergeben. Dann in einem weiteren Schritt einfach nur die Wiedergaben aus dem SVERWEIS mit ZÄHLENWENN zählen...

Stay tuned!

 

[Zyxx]

=ZÄHLENWENN(A1:A2;"<>") zählt wenn irgendwas drin ist. Das <> ist hier ungleich. Damit sollte auch eine Abfrage auf andere Werte bastelbar sein.

 

[awehring]

Ja, das wäre der Weg.
Wenn die Aufgabenstellung kompliziert ist (Arbeitspläne mit Wochenenden, Schichten, etc.) landet man bei komplizierten Wenn-Konstrukten.
Irgendwo da sind dann die sinnvollen Grenzen von Excel erreicht. Spätestens wenn man nach ein, zwei Jahren noch mal etwas anpassen will oder das Arbeitsblatt an einen Nachfolger übergeben, wird es schwierig.

Die nächste Stufe, um der Komplexität besser Herr zu werden, wäre eine Datenbank wie MS Access.

 

[Sinush]

Ich hab vorhin Feierabend gemacht. In meinem nächsten Dienst werde ich mal frickeln und schauen, ob mich das so weiterbringt. Habt alle Beide auf jeden Fall schon einmal 1000 Dank für den Input, @awehring @Zyxx

 

[Sinush]

Soooo.

Ich stoße mit =ZÄHLENWENN auf das Problem, dass die Funktion ein "Sa." oder "So." in B3:B33 nicht erkennt (s. mein letzter Ausschnitt in #5). Liegt das daran, dass ich die Wochentage automatisch ermitteln lasse mit =$F$1 in B3 und dann "B3+1" in B4, "B4+1" in B5 usw. und den Wert dann über die Textformatierung in Wochentage (bzw. deren Abkürzung) umformatieren lasse?

 

[Zyxx]

Das kann ich dir ohne es auszuprobieren leider nicht sagen.
Ich hatte damit ein Sheet gebaut das verschiedene Eingaben pro Tage filtert und graphisch aufbereitet, damit es bunt wird.
Im Endeffekt wurde dann mit Zählenwenn auf ein Bereich gezählt ob da *_1 oder *_2 oder *_3 drin steht oder eben gar nichts.
Das * hat den Reiz das es bei zählenwenn auf das Ende ankommt, davor kann ein ganzer Roman stehen solange dieser nur mit _1 _2 oder weiteren abgeschlossen wird.
Links dann die Monatstage von 1 bis 30, die mit bedingter Formatierung fett markiert werden.
Neben jedem Monatstag dann die verschiedenen Vorgänge.
Gezählt und ausgewertet wird dann in einem anderen Blatt und / oder im selben Blatt in Zellen mit der kleinstmöglichen Breite. Andernfalls funktionierten Graphiken in dem alten Excel nicht wenn Zellen ausgeblendet wurden.

 

[awehring]

Du musst die Funktion Wochentag(...) verwenden.

WOCHENTAG-Funktion - Microsoft-Support

Wandelt eine fortlaufende Zahl in einen Wochentag um. Der Tag wird standardmäßig als ganze Zahl ausgegeben, die einen Wert von 1 (Sonntag) bis 7 (Samstag) annehmen kann.

support.microsoft.com

 

[Sinush]

So, da bin ich nochmal.

Eure Lösungsansätze bringen leider nicht den gewünschten Erfolg.

Ich vermute, es liegt an den ursprünglichen Werten der Zellen, die ich abfragen will. Ich hab noch einmal gebastelt und kommentiert, wie ich die Grundlage der Abfrage setze:

Spoiler: Screenshot

Dreh- und Angelpunkt ist A2 mit der Benennung von Monat und Jahr. Davon leitet sich Spalte A (Datumszahl) und B (Wochentag) ab. Will ich nun abfragen, welche Felder Sonntag oder Samstag ergeben, erhalte ich immer den Wert 0 zurück: =ZÄHLENWENN(B4:B33;WOCHENTAG($A4)=7) bzw. =ZÄHLENWENN(B4:B33;WOCHENTAG($A4)=1)
Ich vermute, dass es daran liegt, weil in den Zellen de facto nicht "Sonntag" oder "Samstag" (oder deren Abkürzung) steht, sondern für jeden Tag eine andere 5stellige Ziffer, die lediglich intern in einen Wochentag umgerechnet und in der Ausgabe umformatiert wird. Oder verwende ich einfach nicht die richtige Formel in B36 für eine Abfrage, wieviele Samstage und Sonntage im Bereich B4:B33 vorkommen?

Ich weiß, dies passt nicht zu meiner ursprünglichen Frage ganz oben, aber sobald ich eine funktionierende Formel für die Wochenendtage-Abfrage habe, kann ich mir - glaube hoffe ich - den Rest auch erschließen. Wie gesagt, momentan hakt es einfach bereits an der einfachen Abfrage, wieviele Samstage und Sonntage in dem Bereich vorkommen.

Beitrag automatisch zusammengeführt: 12.03.2024

Zusatz:

Ich benutze "Wochentag($A4)", weil es auch bei der bedingten Formattierung für den Hintergrund dazu führt, dass die jeweiligen Tage erkannt und dann die entsprechenden Zellen eingefärbt werden:

Spoiler: Screenshot

Beitrag automatisch zusammengeführt: 12.03.2024

Erneutes Update:

Ich bin einen Schritt näher. Ich habe einfach die Abfrage geändert in: =WENN(WOCHENTAG($B4)=7;"YES!";WENN(WOCHENTAG($B4)=1;"YES!";"nopes"))

Also: Abfrage, ob der Wochentag in der jeweiligen Zelle ein Samstag ist; wenn Ja, dann "Freude"; wenn Nein, dann Abfrage, ob der Wochentag in der jeweiligen Zelle ein Sonntag ist; wenn Ja, dann "Freude"; wenn Nein, dann "nopes".

Darunter dann =ZÄHLENWENN(C4:C33;"YES!"), und schon habe ich die Anzahl an Wochenendtagen:

Spoiler: Screenshot

Daraus mache ich nun eine Abfrage mit =ZÄHLENWENNS mit mehreren Parametern (z.B. ob zusätzlich noch das Kürzel für Nachtbereitschaft da steht).

Stay tuned!

 

[Sinush]

So, ich kann Erfolge vermelden:

Ich bin das Problem noch einmal völlig anders angegangen und dementsprechend meine Abfragen gemacht:

Mittels verschachtelter Wenn-Funktion überprüfe ich lediglich, ob der Tag ein Samstag ist, ob dafür kein Dienst geplant ist (Zelle="") und ob in der darauf folgenden Zelle (zwingend ein Sonntag) kein Dienst geplant ist -> Dann in eine ausgeblendete Spalte eine 1 setzen:

=WENN(WOCHENTAG(B3)=7;WENN(C3="";WENN(C4="";1;););)

Unten dann die Einsen mittels Summenfunktion zusammenzählen: Et voilá, ich hab die Anzahl der freien Wochenenden.

Danke erneut an @awehring und @Zyxx für den hilfreichen Input auf meinem Weg zum Ziel!!! 💖

 

[awehring]

Schön, dass Du es hinbekommen hast!

 

[Sinush]

So, ich habe die Formel mithilfe von UND( ; ) verschlanken können und mit NICHT(UND( ; ) ; ) außerdem noch die Bedingung eingefügt, dass Wochenenden inmitten von Urlaub nicht frei sind:

=WENN(UND(WOCHENTAG($B3)=7;C3="";C4="";NICHT(UND(C2="U";C5="U")));1;)

Kennt Ihr das? Dieses äußerst befriedigende Gefühl, eine Excel-Problemstellung erfolgreich wegformeln zu können?🥰🥰🥰