Dienstplan - Zählewenn mit mehreren Bedingungen

[Matze200982]

Hallo,

Ich möchte eine Dienstplantabelle schreiben die mir das Schreiben vom Dienstplan erleichtert. Die Tabelle hat bis jetzt die Funktion (mit ZÄHLEWENN), mir in der Spalte bzw. der Zelle für die betreffend Spalte, jew. im Bereich von Bspw. F3:F22, G3:G22, H3:H22... etc... (20 Mitarbeiter) für die fortlaufenden Tage mit unterschiedlichen Dienstplansymbolen wie z.B.: "F" oder "S", die eingetragenen Dienste als Anzahl in einer jeweiligen Zelle auszuspucken.

Das hat den Sinn, dass ich beim Dienstplan schreiben dann die mindeste Besetzungszahl pro Tag (nämlich 2 für Frühdienst und 2 für Spätdienst) für den jew. Diensttag angezeigt bekomme.

Nun gibt es aber noch den Nachtdienst was mit dem Symbol "N" bei ZÄHLEWENN (und der mindesten Besetzung von 2 für den Typ Dienst und den Dienstag) kein Problem macht. Das Problem hierbei ist nur, dass es auch für den Tag an dem man in den Nachtdienst geht ein Extrasymbol "/N" gibt und für den Tag an dem man aus dem Nachtdienst kommt auch noch "N/". Das hat zum Verstädnis den Sinn das man beim Schreiben besser Überblicken kann ob an dem Tag an dem zwei Mitarbeiter aus dem Nachtdienst gehen auch wieder zwei in den Nachtdienst gehen - soweit so gut.

Nun würde es an dem betreffenden Tag in der Spalte je zwei Symbole mit "/N" und "N/" geben was bei der Formel

Code: Alles auswählen

=ZÄHLEWENN(A1:A10;"/N")+ZÄHLEWENN(A1:A10;"N/")+ZÄHLEWENN(A1:A10;"N")

= 4 ergeben würde. Nun soll aber, weil es ja logisch ist dass es an dem Tag zwei sind die in die Nacht gehen bzw. kommen, der Wert 2 stehen. - Hier aber in orange markiert, 4.

Da es aber mit o.g. Formel bei "/2" (am Ende) mit zwei Nachtdiensten, nämlich im fortlaufenden Dienst mit "N", der Wert "0" herauskommt und das dann einen falschen Personalwert an diesem Tag darstellt, müsste ich dann dem Ergebnis zur Folge noch zwei Nachtdienste einsetzen (was dann aber insgesamt 4 wären, was aber nicht sein soll).

Hier die Formel

Code: Alles auswählen

=ZÄHLENWENN(V3:V22;"N")+ZÄHLENWENN(V3:V22;"/N")+ZÄHLENWENN(V3:V22;"N/")

Und damit man es sich besser vorstellen kann, hier die Tabelle.



Zur Sicherheit lade ich die Datei hoch, dann könnt ihr euch bei Bedarf die Formeln um die Ohren schlagen - bin echt hilflos!!! Habe aber dafür mit Hilfe dieses Forums einige Probleme vorab lösen können (Bspw. "bedingte Formatierung")


Ich hoffe ihr habt mich verstanden was ich mit der Tabelle und besagter Funktion machen möchte. Könnt ihr mir helfen?

Gruß, Matthias

[Matze200982]

Bin eben gerade beim putzen auf eine kleine Lösung gekommen. Mit folgender Formel komme ich der Lösung relativ nahe...

Code: Alles auswählen

=ZÄHLENWENN(F3:F22;"N")+ZÄHLENWENN(F3:F22;"/N")*0,5+ZÄHLENWENN(F3:F22;"N/")*0,5

So zeigt er mir bei

Code: Alles auswählen

2x "N/" = 1

und bei

Code: Alles auswählen

2x "N/" + 2x "/N" = 2

an.

Wobei jeder Wert größer als 2, z.B.:

Code: Alles auswählen

2x "N/" = 1 + 1x "N" = 3

"falsch" ist.

Was mich der eigentlichen Lösung nur ein kleines Stück weiter bringt. Dann wäre es laut Formel nicht falsch, wenn

Code: Alles auswählen

2x 0,5 + 1 = 2

wäre. Es aber dann laut Dienstplan zwei Leute geben würde die die Nacht beginnen während einer weiterhin Nacht hat, was de facto einer zuviel wäre (es aber laut Formel nicht ist).
siehe Bild


IDEE
Könnte man die Formel evtl. nochmal irgendwie verfeinern? Oder vielleicht sogar mit der Formel ausdrücken, das entweder nur

Code: Alles auswählen

2x "N"

oder

Code: Alles auswählen

2x "/N" + 2x "N"/

Richtig ist

und alles andere was vorkommt falsch ist?

[clag]

Hallo Matze200982,

versuch es mal mit folgender Formel in Zelle V25

=ZÄHLENWENN(V3:V22;"N")+WENN(ZÄHLENWENN(V3:V22;"N/")=ZÄHLENWENN(V3:V22;"/N");ZÄHLENWENN(V3:V22;"N/");MIN(ZÄHLENWENN(V3:V22;"N/");ZÄHLENWENN(V3:V22;"/N")))

hilft dir das weiter?

[Hiker]

Hallo,

Oder vielleicht sogar mit der Formel ausdrücken, das entweder nur 2x "N" oder 2x "/N" + 2x "N"/ richtig ist

Es gibt tatsächlich eine Entweder/Oder-Funktion: engl. XOR deutsch XODER mit der Du Deine ursprünglichen Zählaufrufe kombinieren kannst:

Code: Alles auswählen

 =XODER( ZÄHLENWENN(V3:V22;"N") = 2  ;
           UND( (ZÄHLENWENN(V3:V22;"/N") = 2)  ; (ZÄHLENWENN(V3:V22;"N/") = 2) ))

MfG, Jörn

[Matze200982]

@Claq
Vielen Dank für die schnelle Antwort *thumbsup*

versuch es mal mit folgender Formel in Zelle V25
=ZÄHLENWENN(V3:V22;"N")+WENN(ZÄHLENWENN(V3:V22;"N/")=ZÄHLENWENN(V3:V22;"/N");ZÄHLENWENN(V3:V22;"N/");MIN(ZÄHLENWENN(V3:V22;"N/");ZÄHLENWENN(V3:V22;"/N")))

hilft dir das weiter?

Es kommt dem was ich brauche sehr sehr nah. Aber so zählt er nicht die "/N" Symbole alleine mit. Eben nur wenn sie im gleichen Verhältnis mit "N/" stehen gibt er sie als Wert raus. Aber alles andere in Kombination zueinander funktioniert soweit;-)

siehe Bild


Kombiniert er zwar "/N" mit "N/" aber nur jew. wenn sie im gleichen Verähltnis zueinander stehen. - Wenn jetzt z.B.:

Code: Alles auswählen

2x "/N" mit 3x "N/"

eingegeben werden würde (was ja falsch wäre weil dann zwei Mitarbeiter in den Nachtdienst gehen und drei raus), dann zeigt er trotzdem

Code: Alles auswählen

"2"

an.

Hierbei sollte er mir aber einen Wert ausspucken der dann größer als 2 ist und dank Bedingter Formatierung angezeigt wird.

@Jörn

Es gibt tatsächlich eine Entweder/Oder-Funktion: engl. XOR deutsch XODER mit der Du Deine ursprünglichen Zählaufrufe kombinieren kannst:

Code: Alles auswählen

=XODER(ZÄHLENWENN(V3:V22;"N" =2;UND((ZÄHLENWENN(V3:V22;"/N")=2);(ZÄHLENWENN(V3:V22;"N/")=2)))

MfG, Jörn

Funktioniert leider gänzlich überhaupt nicht. Aus welche mGrund auch immer... *ratlos* - obwohl ich den Ansatz wirklich gut verstehe!!!

siehe Bild



Ich finde es aber toll das Ihr versteht was ich bezwecken will!

[mikeleb]

Hallo,

Hierbei sollte er mir aber einen Wert ausspucken der dann größer als 2 ist

Welchen Wert würdest du dir in diesem Falle wünschen?
Vielleicht ist anstelle Min() die Max()-Funktion das Richtige:

Code: Alles auswählen

=ZÄHLENWENN(V3:V22;"N")+WENN(ZÄHLENWENN(V3:V22;"N/")=ZÄHLENWENN(V3:V22;"/N");ZÄHLENWENN(V3:V22;"N/");MAX(ZÄHLENWENN(V3:V22;"N/");ZÄHLENWENN(V3:V22;"/N")))

[Hiker]

@Jörn

Es gibt tatsächlich eine Entweder/Oder-Funktion: engl. XOR deutsch XODER mit der Du Deine ursprünglichen Zählaufrufe kombinieren kannst:

Code: Alles auswählen

=XODER(ZÄHLENWENN(V3:V22;"N" =2;UND((ZÄHLENWENN(V3:V22;"/N")=2);(ZÄHLENWENN(V3:V22;"N/")=2)))

MfG, Jörn

Funktioniert leider gänzlich überhaupt nicht. Aus welche mGrund auch immer... *ratlos* - obwohl ich den Ansatz wirklich gut verstehe!!!

Ich geb ja zu, ich hab es geschrieben, ohne es auszuprobieren, aber ich schätze mal Du hast irgendwo einen Tippfehler.
Bei mir geht es problemlos (s. Anhang) mit LO 4.1.3.2 auf diesem Rechner.
Anmerken sollte ich noch, daß die Formel einen Wahrheitswert liefert (ich hatte mehr an die Bedingte Formatierung gedacht, die
anzeigt, ob der Wert richtig ist). In Dein normales Feld passt "WAHR" aber nicht rein, so daß ### angezeigt wird - aber keine Fehlermeldung.
Ich musste hinterher dann noch die Zelle wieder als Zahl formatieren.
Wenn DU die Formel für Die Besetzungsberechnung mißbrauchen willst (es wird eben nicht wirklich berechnet):

Code: Alles auswählen

=WENN( XODER( ZÄHLENWENN(V3:V22;"N") = 2; UND(ZÄHLENWENN(V3:V22;"/N") =2 ; ZÄHLENWENN(V3:V22;"N/") =2)) ; 2; 0)

MfG, Jörn

[Gert Seler]

Hallo Jörn und Matthias,
da die erste Tabelle von "Matthias" im " *.xls format ", Excel_bis_2003,
erstellt ist, dort aber keine Funktion XODER() zur Verfügung steht, sollte das
evtl berücksichtigt werden.

mfg
Gert

[Hiker]

Hallo,

In einem openoffice-Forum gibt es erstmal Antworten ohne Berücksichtigung älterer Excel-Versionen.
Neue Excel-Versionen kennen ausserdem xor - evtl mit anderer Syntax.

Mfg, Jõrn

[Matze200982]

Es gibt tatsächlich eine Entweder/Oder-Funktion: engl. XOR deutsch XODER mit der Du Deine ursprünglichen Zählaufrufe kombinieren kannst:

Code: Alles auswählen

=XODER(ZÄHLENWENN(V3:V22;"N" =2;UND((ZÄHLENWENN(V3:V22;"/N")=2);(ZÄHLENWENN(V3:V22;"N/")=2)))

MfG, Jörn

Ich geb ja zu, ich hab es geschrieben, ohne es auszuprobieren, aber ich schätze mal Du hast irgendwo einen Tippfehler.

War auch so... Irgendwie bringt er die Formatierung der Leerzeichen durcheinander. Es lag aber vermutlich eher an

Code: Alles auswählen

 XOR

statt's

Code: Alles auswählen

XODER

Hier die Formel

Code: Alles auswählen

=WENN(XOR(ZÄHLENWENN(P3:P22;"N")=2;UND(ZÄHLENWENN(P3:P22;"/N")=2;ZÄHLENWENN(P3:P22;"N/")=2));2;0)

Klappt soweit sehr gut! Ich bin sehr beeindruckt. Er zeigt mir nur noch ein paar Fehler als Richtig an.

Siehe Bild


Eingebaute Fehler in Spalte

• "P" = 2x"N" + 4x"N/"
  "Q" = 2x"N" + 1x"N"/ + 2x"/N"

werden als Richtig erkannt, was er ja nicht soll. Alle anderen "Fehler" funktionieren so wie sie sollen.

Gruß Matthias

[Matze200982]

Hallo HeritzP,

Leider nein, dann würde er mir wie weiter oben schnon beschrieben bei einer Übeschneidung von Zwei Kollegen die aus der Nacht kommen mit dem Symbol "N/" und zweien die in die Nacht reingehen mit dem Symbol "/N" vier ausgeben.

[Hiker]

Hallo Matthias,

wenn Du mal Deine Datei hochladen würdest könnte man ja mal nach dem Fehler suchen.
Der Screenshot zeigt ja nicht einmal eine der beiden Formeln an, die eventuell falsch sind.

Mfg, Jörn