Optimierung eines Dienstplanes mit Stundenberechnung

[balu]

Hallo Flo,

nö, nö! Das Forum hat kein eigenleben. Zu der Zeit als ich am editieren war, hattest Du auch geschrieben. Dadurch kam eine kleine Überschneidung unserer Beiträge zustande. Also nicht deine Schuld .
Aber sorry, ich bin heute auch etwas durchn Wind .

Nimm also jetzt die von mir zitierte SVERWEIS-Formel, und setze diese in B14 ein.

müsste es doch $J$7>7 heißen, oder? Es gibt ja die Ausgleichsgruppen 1-7, sonst wird mir ja immer ne leere Zelle angezeigt ab Gruppe 4...

Nein! Denn die Formel kümmert sich ja nur um die Dienstgruppen. Und die Ausgleichsgruppen sind doch wie die Dienstgruppen in 3 Spalten aufgeteilt. Okay, is bischen blöde formuliert. Aber wenn Du dir die beiden Hilfstabellen anschaust, dann siehst Du doch das Du 3 Spalten für beide hast.
Die einzelnen Ausgleichsgruppen (1-7) werden ja NICHT von der Formel ausgewertet, sondern nur von der Bedingten Formatierung.

In der Zeile 13 hast Du ja eine Bedingte Formatierung, und in dieser steht als 1. Bedingung eine lange Formel von dir drin. Und genau diese lange schmeißt Du jetzt einfach raus, und setzt diese ein.

Code: Alles auswählen

B14=$J$9

Die Zeilen 15 + 16 brauchst Du nicht mehr. Sie sind überflüssig.

Hab jetzt keine Zeit mehr. Meld dich bei weiteren ungereimtheiten und Probleme, ich schaue später wieder rein.


Gruß
balu

[scholdes]

Schwupps, schon fertig
Jetzt hängt aber die Stundenberechnung, die zeigt immer 00:00 an
[EDIT]Hab den Fehler gefunden, war in der Formel eine Änderung, muss in U7 jetzt

Code: Alles auswählen

=INDIREKT("AH"&9+(MONAT(U9)*9))+SUMME(E140:AA140)

heissen, da ich ja immer 2 Zeilen je Monat gelöscht habe.[/]

[scholdes]

Aaaargh

Fast Fertig und dann so ne Schei...

Wieso bekomme ich denn ohne Schaltjahr jetzt in AF21 und AF22 was angezeigt
Und warum machts mir AF20 farbig
Ich habs schon mit WENN(ISTSCHALTJAHR... probiert, aber das will nicht

[balu]

Hallo Flo,

Hab den Fehler gefunden

Freut mich echt zu hören, dass Du es selber herausgefunden hast.

Fast Fertig

Aber auch nur fast. Da gibts noch ein paar Punkte .

Wieso bekomme ich denn ohne Schaltjahr jetzt in AF21 und AF22 was angezeigt

Schau dir doch mal die Formel in AF20 an. Fällt dir da was im vergleich zu AF21 auf?

Richtig!

In AF21 fehlt die Schaltjahrabfrage. Genau so in AF22, AF24 und AF25.

Zur genaueren Info.
Auch wenn angeblich nix in AF18 drin steht, so interpretiert Calc das "nichts" als den 30.12.1899, also das Startdatum von OOo. Und dieses Datum (Tag und Monat) verrechnet Calc mit REST() und findet auch einen Eintrag und gibt diesen in AF21 aus.
Jetzt kann man zwischen 2 Möglichkeiten wählen, um das zu vermeiden.

1.
Eine Abfrage ob in AF21 eine Zahl drin steht, ISTZAHL(AF21). Was aber für uns überflüssig ist, da wir die 2. Methode anwenden.

2.
Die Abfrage mit ISTSCHALTJAHR() ist für unsere Zwecke vollkommen ausreichend. Also für AF21

Code: Alles auswählen

=WENN(ISTSCHALTJAHR($AE18);WENN($J$7>3;"";SVERWEIS(REST(AF18;147);$AJ$29:$AM$175;$J$5+1;0));"")

Alles Klar .


Gruß
balu

[scholdes]

Moin Balu

Erstmal ein dickes Danke und ein großes Lob für die vielen oftmals nicht einfachen Hilfestellungen.
Ne einfache Frage zu beantworten ist das eine, sich in eine solch komplexe Tabelle reinzuarbeiten, diese zu verstehen und dann noch verbessern zu können ist aber...wie soll ich sagen...1a mit Sternchen.

2.
Die Abfrage mit ISTSCHALTJAHR() ist für unsere Zwecke vollkommen ausreichend. Also für AF21

Code: Alles auswählen

=WENN(ISTSCHALTJAHR($AE18);WENN($J$7>3;"";SVERWEIS(REST(AF18;147);$AJ$29:$AM$175;$J$5+1;0));"")

Oh Mann. Die letzten 4 Zeichen waren es *grmpftl*

Alles Klar .

Jetzt schon, danke.

Gruß
Flo

[balu]

Ja Servus Flo,

sich in eine solch komplexe Tabelle reinzuarbeiten,

Die Komplexibilität dieser Tabelle ist bei mir Standard .

diese zu verstehen und dann noch verbessern zu können

Is doch nur "einfache" Logik da drin. Hat man erst mal das Grundprinzipo der dortigen Logik verstanden, ist der Rest ein Kinderspiel. Und das hast Du doch auch schon ganz gut hinbekommen.
Aber dennoch Danke ich dir für deine lieben Worte .

Versuch dich doch schon mal daran das mit den Zeiten zu verbessern, denn das ist das nächste Thema.


Gruß
balu

[scholdes]

Moin

Versuch dich doch schon mal daran das mit den Zeiten zu verbessern, denn das ist das nächste Thema.

Ähm, meinst Du die Formeln die dann die Soll- bzw. Ist-Stunden in die Zellen schreiben? Uff...mal probieren

[Gert Seler]

Hallo Balu,
ich wünsch Euch allen einen schönen "Vadderdaach" !
Kannst Du mir erklären, was die "Jahreszahlen-Suche" in Zeile_01 Spalten (F bis H) bezweckt ?

Ich fahr jetzt in Kurzurlaub und bin am Sonntag wieder da.

Wünsche Allen ein schönes verlängertes Wochenende.

mfg
Gert

[scholdes]

Kannst Du mir erklären, was die "Jahreszahlen-Suche" in Zeile_01 Spalten (F bis H) bezweckt ?

In der aktuellen Version gibts diese Felder nicht mehr.
Diese Suche sollte das Eintragen der Ausgleichsgruppen bezwecken. Das hat sich ja jetzt durch die Matrix in der Formel

Code: Alles auswählen

=WENN($J$5>3;"";SVERWEIS(REST(B9;147);[color=#FF0000]$AJ$29:$AM$175[/color];$J$5+1;0))

erledigt, da sich auch die Folge der Ausgleichsgruppen irgendwann zwangsläufig wiederholt .
Bei Interesse kann ich die komplette Tabelle auch per eMail versenden (ist zum hier hochladen zu groß).
@Balu
irgendwie komm ich mit den Stunden net weiter. alles was ich mach zerschießt mir die Formel, es kommt dann nur Käse raus. Zwar immer verschiedene Sorten, aber halt Käse.
Schönen Vatertag noch

Gruß
Flo

[balu]

Hallo Flo,

irgendwie komm ich mit den Stunden net weiter. alles was ich mach zerschießt mir die Formel, es kommt dann nur Käse raus.

Ja was machst Du denn, dass Käse dabei rauskommt?
Wenn Du mal ne Beispielformel postest, dann kann ich dir sagen ob Du aufm richtigen Weg bist, oder aber aufm Holzweg.

Auch noch nen schönen Vadderdach .


Gruß
balu

[scholdes]

Tja, nagut

Also, in B13 steht

Code: Alles auswählen

=WENN(B14="K";ZEIT(0;0;0);WENN(B14="U";ZEIT(0;0;0);WENN(B11="T";ZEIT(8;34;0);WENN(B11="N";ZEIT(12;0;0);WENN(B11="S";ZEIT(20;35;0);WENN(B11="";""))))))

B13 ist formatiert als Zeit [HH]:MM.

Ich hab schon versucht, das ZEIT(HH;MM;SS) durch die entsprechende Minutenzahl zu ersetzten und dann ans Ende /24/60 zu setzen, also quasi

Code: Alles auswählen

=WENN(B14="K";0;WENN(B14="U";0;WENN(B11="T";514;WENN(B11="N";720;WENN(B11="S";1235;WENN(B11="";""))))))/24/60

, das funktioniert auch, leider zeigt mir das dann keine leere Zelle mehr an, wenn kein Dienst in B11 steht, sondern 00:00 , schöner wäre halt in dem Fall ne leere Zelle.

Mein Problem ist also, dass ich nicht weiß, wie ich die Zeitangaben in der Formel anders darstellen kann als mit ZEIT(HH;MM;SS) bzw. was ich sonst noch machen müsste, wenn ich da normale Zahlen reinbaue.

Dass ich alles was den gleichen Zeitwert hat, noch mit ODER zusammenfasse, das bin ich grade am fertigstellen.

verzweifelnde Grüße
Flo

[balu]

Hallo Flo,

Du gehst einen nicht eleganten Weg, der seeehr unflexibel ist. Deshalb vergess dein weiteres vorgehen!

Du hast ja schon SVERWEIS() kennengelernt, und auch angewendet. Ich denk mir mal das Du im groben jetzt auch SVERWEIS verstehst, und wenn nicht, so kommt das noch .
Auch für diesen Fall ist SVERWEIS die bessere Lösung.

Um dir eine kleine Starthilfe zu geben, habe ich eine kleine Datei angehängt, mit der Du experimentieren kannst, oder aber in deine Datei einfügst.
In dieser Datei gibt es 3 Spalten, wobei Spalte B und C für SVERWEIS von Bedeutung ist. Du musst also die SVERWEIS auf diese beiden Spalten "programmieren". Im Prinzip funktioniert sie so ähnlich wie die andere für die Solldienst-Kürzel (T, N, S). Nur das Du dich jetzt nicht mit ihr auf das Datum beziehst, sondern auf die Solldienst-Kürzel. Und RERST() entfällt jetzt.

Versuch mal dein Glück .


Gruß
balu

[scholdes]

Oooohhh
Einzeln klappts
Aber sobald ich was wursteln will mit Bezüge auf zwei verscheidene Zellen, gehts net.
B13

Code: Alles auswählen

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

B15

Code: Alles auswählen

=WENN(B14="";"";SVERWEIS(B14;$AQ$3:$AR$17;2;0))

B15 kann so stehenbleiben, dass passt. Nur mit B13 kämpfe ich noch. Da bräuchte ich eher was in der Richtung

Code: Alles auswählen

=WENN(B11="";"";SVERWEIS(B14;$AQ$15:$AR$16;2;0);SVERWEIS(B11;$AO$3:$AP$5;2;0))

oder sowas in der Art...

Also quasi
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.

Ich glaub da läuft was noch net so wies soll (bei mir )
Gruß
Flo (Der jetzt mal was bruzzeln geht...Putenkeule...hmmmm)

[balu]

Hallo Flo,

ich hoffe das Bruzeln hat Spaß gemacht, und das Ergebnis hat geschmeckt .

Sodale, dann wolln ma mal weiter.

Wenn ich deine Formeln richtig lese, dann hast Du meine Zeittabelle verstanden, und so weit in den Griff bekommen. Prima.
Nur mit der letzten Formel, ja ich weiß das sie nur laut gedacht ist, da habe ich mit den Zellbereichen so meine Verständigungsprobleme, weil ich mir nicht vorstellen kann was in den einzelnen Bereichen drin steht. Aber ich versuchs trotzdem mal.

Mein Lösungsansatz wäre folgender.

Code: Alles auswählen

=WENN(ZÄHLENWENN($AQ$15:$AQ$16;B14);SVERWEIS(B14;$AQ$15:$AR$16;2;0);WENN(ZÄHLENWENN($AO$3:$AO$5;B11);SVERWEIS(B11;$AO$3:$AP$5;2;0);""))

Die Zellbezüge musst Du wohl noch anpassen.

Also quasi
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.

Wie Du siehst arbeitet meine Formel nicht nach deinen theoretischen Logikablauf, sondern fast andersrum. Natürlich könnte ich in meiner Formel als erstes auf B11="" prüfen, aber das würde eine zusätzliche WENN-Verschachtelung bedeuten. Wenn Du willst, dann kannst Du ja diese Prüfung noch zusätzlich mit einbauen.
Ich persönlich Meine aber, da es sich bei dieser Datei um einen "Kalender" handelt, der nicht jede Sekunde neu berechnet werden muss und dadurch wegen dieser Formel ausgebremst würde, kann man das so lassen. Aber das ist auch Geschmackssache.

Versuchs mal mit der Formel, und meld dich bei Verständigungsproblemen.


Gruß
balu

[scholdes]

Nur mit der letzten Formel, ja ich weiß das sie nur laut gedacht ist, da habe ich mit den Zellbereichen so meine Verständigungsprobleme, weil ich mir nicht vorstellen kann was in den einzelnen Bereichen drin steht.[...]
Wie Du siehst arbeitet meine Formel nicht nach deinen theoretischen Logikablauf, sondern fast andersrum.

Ich werde es mal probieren.
Die Reihenfolge der Prüfungen hatte ich extra so gewählt. Ist mir mal bei einer bedingten Formatierung zur Einfärbung des Tages an dem Ausgleich ist aufgefallen.
Da hatte ich "wenn Tag=Samstag dann gelb, wenn Tag=Sonntag dann orange und wenn errechneter Ausgleich=im Kopf eingetragener Ausgleich dann lila".
Hier muss ich aber die Formel für den Ausgleich als erste setzen, sonst macht er mir den Tag z.B. an einem Samstag nicht lila sondern lässt ihn gelb...
Deswegen meine Reihenfolge so...mal sehen obs funktioniert

Gruß
Flo