Optimierung eines Dienstplanes mit Stundenberechnung

[scholdes]

Klappt vorzüglich...
Leider frisst die Tabelle z.B. in B14 das LFW+ bzw. LNW+ nicht, da wird immer das + verschluckt und dementsprechend in B15 zuwenig angezeigt.
Habs abgeändert auf +LFW und +LNW und schon gehts .

Und die bedingte Formatierung bringt mich auch noch um.
Ich muss jeden Monat für sich machen, kopieren geht net...

[balu]

Hallo Flo,

Und die bedingte Formatierung bringt mich auch noch um.
Ich muss jeden Monat für sich machen, kopieren geht net...

Das ist ja auch kein Wunder, bei den Zellbezügen in der Bedingten Formatierung.

Code: Alles auswählen

WENN(B$10="Sa")
WENN(B$10="So")

Mach mal folgendes.
In der Spalte B änderst Du jede einzelne Zelle (B9 - B16) der Reihe nach die Bedingungen fürs Wochenende, und zwar so:

Code: Alles auswählen

B10="Sa"
B10="So"

Zusätzlich änderst Du diese Bedingte Formatierung in B14.

Code: Alles auswählen

WENN(ODER(B14="LF";B14="LN";B14="LFW";B14="LNW";B14="LFW+";B14="LNW+"))

Mit folgenden Trick kannst Du dir das Leben dabei erleichtern.
Du hast ja irgendwo die Dienstzeiten als Hilfstabelle stehen, in der auch die Dienste LF bis +LNW aufgelistet sind. Und wenn sie zusammenhängend schön untereinander sind, dann markierst Du jetzt diesen Spaltenbereich, beispielsweise AO7-AO12.
Nun Drückst du Strg + F3, alternativ
-> Menü
-> Einfügen
-> Namen --> Festlegen
Bei Name gibst Du z.B. DienstkürzelBF ein. Das BF soll für Bedingte Formatierung stehen. Du siehst unten bei Zugeordnet zu den eben markierten Zellbereich. Klick auf Hinzufügen, und OK.

Jetzt rufst Du in B14 die eben zitierte Bedingte Formatierung auf, und gibst dort folgendes ein.

Code: Alles auswählen

SUMMENPRODUKT(DienstkürzelBF=B14)

Für B14 kannst Du dir in einer anderen hinsicht auch noch das Leben erleichtern. Und zwar geht es um das Auswahlfeld. Du hast ja bei
-> Menü
-> Daten
-> Gültigkeit ->Zulassen ->Liste ausgewählt und eingestellt. Und spätestens jetzt, wo Du das mit dem + bei den Diensten festgestellt hast, wartet ne menge Arbeit auf dich. Dies kannst Du dir natürlich erleichtern.
Eben hast Du einen Namen wegen der Bedingten Formatierung erstellt, und das machst Du jetzt auch für das Auswahlfeld. Das geht fast genau so, nur das Du den Zellbereich etwas größer wählen musst, da ja noch ein paar Dienste hinzukommen. Und als Namen kannst Du ja ganz einfach Dienstkürzel nehmen.

Wenn Du das gemacht hast, dann klick auf die Zelle B14, und nun
-> Menü
-> Daten
-> Gültigkeit ->Zulassen ->Zellbereich
und bei ->Quelle gibst Du jetzt den eben vergebenen Namen: Dienstkürzel ein, und fertig.

Jetzt hast Du die Bedingten Formatierungen, und das Auswahlfeld in dem Bereich B9-B16 geändert, und nun kannst Du auch alles schön kopieren.
Das beschriebene Verfahren mit Name festlegen, hat den imens großen Vorteil, dass man bei Änderungen der Zellbereiche nur die Zelladressierung im Namen ändern muss, und die Formeln mit den Namen braucht man nicht zu ändern. Dat is einfach klasse .

Bei Fragen und Problemen bitte melden .


Gruß
balu

[scholdes]

Hallo

Klasse Anleitung, macht das alles um einiges einfacher, funktioniert aber trotzdem nicht...
Wenn ich B9-B16 so formatiert habe und dann mit dem Pinselchen Format übertragen mache, sollten eigentlich alle Samstage gelb, alle Sonntage orange etc. werden.
Leider macht mir das einen Strich durch die Rechnung und färbt nur die Samstag/Sonntag-Zellen in Zeile 14 und 16 ein, der Rest wird weiß
Ausserdem frisst es das + nach LFW/LNW immer noch nicht sondern liest nur das LFW bzw. LNW, so dass eine falsche Zeit ausgegeben wird...
Muss ich damit leben und den Dienst umbenennen oder gibts da noch was, damit es klappt?

Ich häng mal die aktuelle Version (die nicht so ganz will wie ich) dran

Gruß
Flo

[scholdes]

Vielleicht schreib ich noch dazu, dass ich dass AddOn für die bedingte Formatierung und die OO-Version 3.2 benutze.

[balu]

Hay Flo,

Vielleicht schreib ich noch dazu, dass ich dass AddOn für die bedingte Formatierung und die OO-Version 3.2 benutze.

Das Du mottco benutzt weiß ich doch schon längst. Aber dennoch danke für beide Infos .


Wenn Du dir die Bedingte Formatierung der Zellen B9-B16 einzeln nacheinander anschaust, dann wirst Du sehen das einmal auf B10, dann auf B5, B6 usw. Bezug genommen wird. Und das ist ja nicht korrekt. Ich habe das jetzt mal für dich im Januar erledigt. Schau dir trotzdem noch mal die Bedingten Formatierungen der Zellen B9-B16 in Ruhe an. Die B11 habe ich nämlich auch noch geändert.

Das mit dem + dürfte ich jetzt auch geregelt haben.
Es musste etwas in den Optionen in Calc (siehe Screenshot) geändert werden, damit Calc richtig arbeitet. Diese Einstellung in den Optionen wird in der Datei gespeichert.

Optionen.jpg (72.38 KiB) 1963 mal betrachtet

Mir ist da jetzt etwas aufgefallen, was so nicht in Ordnung sein kann.

1.Prüfung B11 leer? Wenn ja dann B13 auch leer. Wenn nein, dann 2.Prüfung.
2.Prüfung B14 K oder U dann B13 00:00. Wenn weder K noch U dann 3. Prüfung.
3.Prüfung B14 T,N, S etc. dann die jeweilige Zeit.

Die 2. Prüfung ist nicht Korrekt, da weder K, noch U ein Soll-Dienst ist.
Die 3. Prüfung ist ein Fehler von dir, den Du mir mit "etc." vorgegeben hast.
Denn die Formel in B13 ist NUR für die Soll-Stunden zuständig, also darf sie auch nur diese Stunden ausgeben die zu den Soll-Dienst dazu gehören. Und es gibt nur 3 Soll-Dienste: T, N und S. Alles andere hat hier nichts verloren.

Demzufolge muss die Formel in B13 so aussehen.

Code: Alles auswählen

=WENN(B11<>"";SVERWEIS(B11;$AO$3:$AP$5;2;0);"")

Und mehr nicht.

B15 ist Korrekt, und kann so bleiben.

Nun stellen wir die Formel in B16 um.

Code: Alles auswählen

=WENN(ODER(B15=0;B15=B13);"";B15-B13)

Ich gehe mal davon aus, dass U gleich Urlaub und K gleich Krank heißt. Und demzufolge werden dafür auch keine Stunden verrechnet.
Dadurch ergibt sich jetzt insgesammt folgendes Szenario.
Wenn jemand Soll-Dienst T hätte, dann werden bei Soll-Stunden 08:34 angezeigt. Da er aber jetzt z.B. K ist, wird bei Ist-Stunden 00:00 angezeigt. Und bei Differenz steht nichts. Ist doch irgendwie logisch, oder!?

Nur frag ich mich wofür das A steht, dass mit 00:00 angegeben ist?


Gruß
balu

[scholdes]

Guten Morgen

Das mit dem + dürfte ich jetzt auch geregelt haben.

Alles klar, das klappt, danke.

Mir ist da jetzt etwas aufgefallen, was so nicht in Ordnung sein kann.
[...]
Die 2. Prüfung ist nicht Korrekt, da weder K, noch U ein Soll-Dienst ist.
Die 3. Prüfung ist ein Fehler von dir, den Du mir mit "etc." vorgegeben hast.
Denn die Formel in B13 ist NUR für die Soll-Stunden zuständig, also darf sie auch nur diese Stunden ausgeben die zu den Soll-Dienst dazu gehören. Und es gibt nur 3 Soll-Dienste: T, N und S. Alles andere hat hier nichts verloren.

Das ist so nicht ganz richtig. Sobald der Ist-Dienst aus K(rank) oder U(rlaub) besteht, werden die Soll-Stunden "genullt". Dein Lösungsansatz ist zwar ok, aber irgendwie "unschön", ausserdem steht dann am Monatsende eine zu hohe Sollstundenzahl. Die erste Formel lass ich so.

Nur frag ich mich wofür das A steht, dass mit 00:00 angegeben ist?

Das A steht in B14 für Ausgleich und bewirkt eine Ist-Stunden-Zahl von 00:00.

Und wenn das Forum hier mal aufsteht und wach wird, kann ich vielleicht Deine Tabelle mal runterladen und anschauen , heute Morgen ist hier anscheinend noch Winterschlaf oder so.

Gruß
Flo

[balu]

Mahlzeit Flo,

ja, das mit dem Forum is heut auch son Ding .

Okay, Du hast mich überredet. Wenn für dich mein Vorschlag "unschön" ist, dann machen wir ihn halt etwas schöner .
Als erstes nimmst Du die Beispieldatei {wenn Du sie mittlerweile downloaden konntest}, und vergibst dort den Zellbereich AQ9 bis AQ11 den Namen: UKA. Hab ja beschrieben wie man das macht. Und nun gibst Du in B13 folgende Formel ein.

Code: Alles auswählen

=WENN(B11<>"";WENN(SUMMENPRODUKT(UKA=B14);0;SVERWEIS(B11;$AO$3:$AP$5;2;0));"")

Dadurch wird jetzt in B12 00:00 Stunden angezeigt, wenn man A, U oder K ist. Ich hoffe das gefällt dir jetzt besser.

Das A steht in B14 für Ausgleich und bewirkt eine Ist-Stunden-Zahl von 00:00.

Alles klar, hat ich mir doch schon so gedacht.


Gruß
balu

[scholdes]

Dadurch wird jetzt in B12 00:00 Stunden angezeigt, wenn man A, U oder K ist. Ich hoffe das gefällt dir jetzt besser.

Nee, auch nicht

Spass beiseite, das A ist noch zuviel, hab ich wieder reausgenommen. Wenn man Ausgleich hat, hat man ja Sollstunden, und wenn man da zuhause bleibt, bekommt man Stunden abgezogen.
Aber ansonsten passt es.

Gruß
Flo

[scholdes]

So, dann jetzt hier nochmal

Guten Morgen

Irgendwie hats meinen Rechner zerlegt, zumindest habe ich mir meine Tabelle zerschossen .

Bis auf die ominöse Stundenberechnung bis =HEUTE() hab ich alles soweit, aber wenn ich hier aus den Beispieldateien die wir mal gemacht hatten die Formel nehme, kommt entweder gar nix raus oder 00:00 .

Wo liegt denn hier schon wieder mein Fehler?

Was mich vor allem interessieren würde: auf was bezieht sich bei der Formel in P4

Code: Alles auswählen

 =INDIREKT("AH"&7+(MONAT(H4)*9))

das "&7" und wieso wird der Monat mit 9 multipliziert?

Gruß
Flo

[balu]

Hallo Flo,

warum machst Du dir eigentlich noch die Mühe und erstellst den Dienstplan komplett neu? Hier ist doch schon eine "fertige" Lösung die Du doch nur noch erweitern brauchst.

Egal, ich werd dir trotzdem weiterhelfen.

Thema: Formel in P4
Wie sieht eine Zelladresse richtig aus?
Natürlich so: AH7
Aber man kann natürlich auch die Zelladresse mit =INDIREKT() zusammenstückeln.

Code: Alles auswählen

=INDIREKT("AH"&7)

Das ist eine Textverkettung. Da wird der Text AH mit der Zahl 7 miteinander verkettet, und das ergibt dann nichts anderes als AH7.
Diese zusammengesetzte Zelladresse dient uns als Zellbasis, von wo wir uns weiter nach unten in der Spalte AH bewegen.

Code: Alles auswählen

=INDIREKT("AH"&7+MONAT(H4))

Hier wird der Monat in H4 als Zahlenwert ausgewertet. Und das wäre z.B. bei Januar die 1, und bei Mai die 5. Und wenn es beispielsweise der Mai wäre, dann könnte genau so gut die Formel wie folgt aussehen.

Code: Alles auswählen

=INDIREKT("AH"&7+5)

Die Zelladresse würde dann folglich AH12 lauten. Denn nach Adam Riese ergibt 7+5 gleich 12.

Code: Alles auswählen

=INDIREKT("AH"&7+(MONAT(H4)*9))

Und jetzt kommen wr zu dem Zeilenmultiplikator 9.
Der Zeilenabstand von einem Monat zu dem nächsten ist genau 9 Zeilen groß. Und nun nehmen wir als Beispielmonat den Februar, er hat den ausgewerteten Zahlenwert 2. Und nun kommt etwas Mathematik zum tragen.

Als erstes wird die Klammer mit dem Monat ausgerechnet.
Monat = 2
Zeilenmultiplikator = 9
Ergibt: 2 x 9 = 18
Zu der 7, die unsere Starbasis ist, addieren wir die 18, und das macht dann 25.
Also lautet die Zelladresse die wir mit INDIREKT ausgearbeitet haben; AH25. Und folglich wird nun der Zellwert von AH25 ausgegeben.

In den hier vorangegangenen Beispieldateien standen die Monatssummen in der Spalte AH in den Zeilen 16, 25, 34... Jedoch hast Du nun in der neuen Datei wohl die gleiche Spalte genommen, aber alles um 3 Zeilen nach oben verschoben. Dadurch bekommst Du jetzt natürlich immer 0 ausgegeben. Und das bedeutet jetzt für dich, das Du die Zellbasis in der INDIREKT-Formel dementsprechend korrigieren musst. Bekommst Du das jetzt alleine hin .



Gruß
balu

[scholdes]

warum machst Du dir eigentlich noch die Mühe und erstellst den Dienstplan komplett neu? Hier ist doch schon eine "fertige" Lösung die Du doch nur noch erweitern brauchst.

Ich hatte Langeweile, und da sich mittlerweile so einige kleine Fehlerchen in meine Datei eingeschlichen haben (aufgrund eigener Unfähigkeit oder auch wasweißichwarum), hab ich mir gedacht, um wieder durch den ganzen Kram durchzusteigen bau ich es mir nochmal neu auf.

Danke für die Hilfe, werd ich dann mal genau durcharbeiten wenn ich wieder zu Hause am eigenen Rechenknecht sitze.

Was anderes...kann es sein dass bei OO 3.4.1 das Mottco net mehr geht? Nach dem Update war es plötzlich weg und lässt sich auch nicht mehr installieren.

Gruß
Flo

[Karolus]

Hallo

Du brauchst MottCo nicht mehr, übers normale Menü gehen jetzt auch mehr als 3 BF.

Karolus

[balu]

Hallo

@Karolus

kann es sein dass bei OO 3.4.1 das Mottco net mehr geht?

Du brauchst MottCo nicht mehr, übers normale Menü gehen jetzt auch mehr als 3 BF.

Verzeih, aber kann es sein das Du deine Brille noch nicht geputzt hast, denn seit wann gibt es in AOO 3.4.1 mehr als 3 BF? Ich weiß jetzt nicht ganz genau ab wann sich das in LO geändert hat, aber in LO 3.4.0 gibt es auch nur die 3.
Ja, ich weiß das es in LO 3.5.5.3 keine beschräkungen mehr bei der BF gibt. Ich weiß nur nicht seit welcher Versioinsnummer das so ist.



@Flo

Was anderes...

Dein Problem mit mottco gehört in einen seperaten Thread. Und dort gibst Du dann bitte an was Du alles gemacht hast, also mehr infos bitte schön. Okay!?



Gruß
balu

[Karolus]

Hallo
@Balu
Du hast Recht, in Aoo gibts nur 3 BF per Menü.

Karolus

[scholdes]

So, geschnackelt

Code: Alles auswählen

=INDIREKT("AH"&4+(MONAT(H4)*9))

passt.

Letztes Problem (hoffentlich):

In meiner alten Ursprungsdatei habe ich noch eine Funktion eingebaut, die mir die Anzahl der Soll- und Ist-Schichten pro Monat zählt.

Jetzt funktioniert diese Formel für die Soll-Schichten

Code: Alles auswählen

{=SUMME(ZÄHLENWENN(B9:AF9;AP5:AP7))}

in der neuen aber nicht .

Genausowenig wie für die Ist-Schichten

Code: Alles auswählen

{=SUMME(ZÄHLENWENN(B11:AF11;AR5:AR715)}

.

Ich hab mich schon halbtot gegoogelt, nix passt. Entweder ich bekomme eine Fehlermeldung, Null oder 31 (bzw 28/29/30, je nach Monat) als Ergebnis.

Gruß
Flo