Thema: Umarbeitung Wochenstundenzettel | Herbers Excel-Forum

Hallo Fachleute,
ich möchte einen Wochenstundenzettel verbessern, weiß aber nicht wie ich das am Besten angehen kann.
Im Tabellenblatt 1 (Stundenzettel) sind eben die 7 Tage der Woche in einer Spalte angegeben….Das erfolgt ja nach Datum welches in einer gesonderten Zelle angegeben wird, Mo mit Datum bis Sonntag mit Datum angegeben.
Rechtes neben dem Datum sollen in der Zeile anhand von verschiedenen Buchstabenkürzeln (die Dienste) jeweils die Anfangszeit Endzeit Pause automatisch angezeigt werden.
Diese Zeiten und die Abkürzungen stehen auf Tabellenblatt 2 welches ich „Dienste“ genannt habe. Um das Ganze auszulösen kann man in der Zelle J5 anhand eines Rollups die Dienste auswählen. Diese sollten automatisch aus der Spalte A des Tabellenblattes 2 (Dienste) übernommen werden.
Bisher habe ich dazu mit Funktionen gearbeitet. Für die Anfangszeit z.B: =WENN($J$5="F";Dienste!$B$4)+……….
Nun kommen aber ca. 50 Dienste zusätzlich dazu. Das heißt in einer Zelle stünden dann ca. 60 mal die Funktion eingetragen.
Hinzu kommt, wenn man die Zeit manuell ändert ist die Funktion in dieser Zelle natürlich gelöscht.
Mein Wunsch wäre nun, daß man die Dienste z.B. F weiterhin in einer Tabelle variabel (Tabellenblatt Dienste) eingetragen werden kann, und die Daten dann automatisch auf das Tabellenblatt 1 (Stundenzettel) ausgegeben werden.
Im Moment ist das so eingetragen…..
A B C D E
1 Dienste Beginn Ende Pause anrechenbare Stunden
2 F 06:00 13:15 00:00 07:15
Dienst F in A2
Anfangszeit in B2
Endzeit in C2
Pause in Minuten in D2
Differenz in E2
Habt ihr dazu eine Idee?
Ich bin damit überfordert und würde also 60 mal die Funktion =WENN in jede einzelne Zelle eintragen. Das ginge ja noch aber bei jedem neuen Dienst müsste man ja die Funktion erweitern. Vielleicht wisst ihr etwas Eleganteres?
Es wäre toll, wenn jeder neue Dienst den man im Tabellenblatt Dienste eintragen würde, automatisch auch auf Tabellenblatt Stundenzettel zur Verfügung stehen könnte.
Danke euch für eure Hilfe
Rolf

Habe mal ein Muster hochgeladen.
Ihr findet es hier:
https://www.herber.de/bbs/user/66271.xls

Hallo Rolf,
ich würde das mit VBA angehen, denn die 50 Abfragen dürften dann woll techn. nicht mehr machbar sein und an die Übersichtlichkeit möchte ich garnicht denken.
Falls Du Interesse hast melde Dich wir können das dann auch mit einer direkten Verbindung angehen.
Ich beschäftige mich seit 20 Jahren mit der Zeitwirtschaft, DP etc. und natürlich VBA.
Gruß Armin

Hallo Armin,
danke für dein Angebot. Ganu wegen der unübersichtlichkeit dachte ich mir auch, daß eswohl besser dies in VBA zu machen. Aber diesbezüglich bin ich eine Niete.
Wenn Du mir da helfen könntest, wäre ich dir sehr dankbar!
Wie könnten wir direkt zusammen kommen?
Gruß Rolf

Hallo Armin,
wenn du möchtest gebe ich dir mal meine Mail durch?
rolfwagner@t-online.de
Danke
Gruß Rolf

Hallo Rolf,
nur so auf die Schnelle
Verwende statt viele WENNs die Funktion SVERWEIS für die Übernahme der Daten aus dem Blatt Dienste.
Dabei muss du als Suchkriterium dann die Eingaben in Spalte D und J per z.B, D5 &" " & J5 so zusammenfügen, dass sie den Eingaben in Spalte A der Tabelle mit den Vorgabe-Zeiten entsprechen.
Der Einfachheit halber solltest du für die Daten im Blatt Dienste einen Bereichsnamen vergeben. Dann muss du beim Ergänzen von Diensten "nur" darauf achten, dass der Name alle Daten erfasst. Die Namen kannst du sowohl als Bereich in den Rolldowns als auch in den Formeln verwenden.
Manuelles Überschreiben kannst du kaum verhindern, dazu müsstest du für manuelle Korrekturen separate Spalten vorhalten und dann in den Auswerteformeln den manuellen Wert nehmen, wenn einer eingetragen ist.
Gruß
Franz

Hallo Franz,
auch eine Idee.
Aber reicht dann bei SVERWEIS eine einmalige Angabe z.B: der Bereichname, oder muss man das nicht auch für alle eventuelle Dienste wiederholen wie bei der WENN Funktion?
Gruß Rolf

Hallo Rolf
mal ein Versuch von mir. Auswahl ueber Dropdown.
https://www.herber.de/bbs/user/66275.xls
gruss hary

Hallo Hary,
danke für deinen Versuch.
Du hast das in den Dropdown in Spalte D gemacht?
Sorry habe da wohl einiges vergessen anzugeben.
Ich wähle die Dienste normal in Zelle J5 aus.
Die Dropdowns in der Spalte D soll nur benutzt werden wenn man in J5 Sonderdienst angegeben hat.
Das ist für Springer gedacht die jeden Tag anders arbeiten müssen.
Bei deiner Lösung werden die Angaben in den Zellen nicht mehr gelöscht wenn man in der Spalte D einen Dienst löscht. Als ich nun die noch weiterhin angezeigten Uhrzeiten manuell lösche, werden die Felder nicht mehr gefüllt wenn ich einen anderen Dienst im Dropdown in Spalte D eintrage.
Gleichfalls sind leider nicht alle Dienste ann allen 7 Wochentagen gleich. Das konnte ich mit WENN einfach lösen indem ich an den betreffenden Tagen eben diesen Eintrag entfernt habe.
Sorry wegen der vergessenen Info
Gruß Rolf

Habe noch einiges vergessen zu erwähnen.
Man wählt die Dienste normal in Zelle J5 über Dropdown aus.
Die Dropdowns in der Spalte D sollen nur benutzt werden wenn man in J5 Sonderdienst angegeben hat.
Das ist für Springer gedacht die jeden Tag anders arbeiten müssen.
Gleichfalls sind leider nicht alle Dienste ann allen 7 Wochentagen gleich. So kann es Dienste geben die von Mo-Do gelich sind am Fr aber eine Stunde verkürzt sind.
Das konnte ich mit der Funktion =WENN..... einfach lösen indem ich an den betreffenden Tagen eben diesen Eintrag entfernt habe.
Sorry wegen der vergessenen Info
Gruß Rolf

Hallo Rolf,
ich hab jetzt mal die SVERWEIS-Lösung eingebaut.
https://www.herber.de/bbs/user/66281.xls
Wird in Spalte D etwas eingetragen, dann geht der Weg über die Sondertätigkeiten. Auch freie Tage müssen in Spalte D eingetragen werden, ansonsten werden aus der Auswahl in J5 die Basisdaten ermittelt. Der Eintrag in Spalte D hat also immer Vorrang vor dem Eintrag in J5.
So kannst du jetzt die Listen fast beliebig verlängern.
Gruß
Franz

Hallo Franz,
danke für deine Mühen.
Obgleich in dieses SVERWEIS noch nicht verstehe. Ich sehe in deiner Lösung keinen Bezug. Oder anders wie kann ich den Bezug vergrößern, weil da ja noch sehr viel mehr Dienste eingetragen werden müssen?
Was mir jetzt gut gefällt ist daß die Dropdowns in der Spalte D die Einträge aus J5 überschreiben. Das ist sehr gut!
Allerdings glaube ich so langsam daß ich mit SVERWEIS die einzelnen Wochentage nicht berücksichtigen kann. Oder ginge das auch?
Ich habe mittlerweile die meisten Dienste erfasst und bis jetzt bin ich auf ca. 45 gekommen.
Aber wie schon gesagt laufen nicht alle Dienste von Mo bis So.
Habe gerade auch versucht deine Funktion mit SVERWEIS in mein Tabellenblatt zu übertragen.
Auf dem Server funktioniert sie gut aber bei mir zu Hause nicht.
Ich werde mal gleich noch meine mittlerweile mit mehr Daten erfasste Datei hochzuladen.
Hier findet man sie: https://www.herber.de/bbs/user/66286.xls
Gruß Rolf

Hallo Rolf,
hier die Datei mit SVERWEIS-Update,
https://www.herber.de/bbs/user/66287.xls
der Verweis in den Formeln wird über den benamten Bereich "Dienste.Data" hergestellt.
In Spalte O hab ich Hilfsformeln eingefügt.
Hier wird geprüft, ob in Spalte D etwas steht. Falls Ja, dann wird per SVERWEIS geprüft, ob der Wert in Spalte D im Blatt Dienste vorhanden ist. Falls ja, dann wird der Wert aus Spalte D übernommen, sonst 0 als Ergebnis ausgegeben. Wenn in D kein Wert, dann wird für den Wert in J5 per SVERWEIS geprüft, ob für den Wochentag ein "x" im Blatt Dienste eingetragen ist. Wenn "x" vorhanden, dann wird Wert J5 genommen, sonnst 0 eingetragen.
Mit dem in O ermittelten Wert wird dann in den Spalten E bis H der Wert aus dem Blatt Dienste ausgelesen.
Für die Springer erfolgt in Spalte O noch keine Prüfung auf "x" beim Wochentag. Falls das doch sein soll, dann muss die Formel in Spalte O entsprechend erweitert werden - analog zur Prüfung für den Wert aus J5. Dann muss bei den Springerdiensten im Blatt "Dienste" für alle Wochentage an denen dieser Dienst wählbar sein soll ein "x" stehen.
Gruß
Franz

Hallo Franz,
du bist ja ein Genie!
Das ist schon fast perfekt.
Noch etwas was du nicht wissen konntest. Zum Beispiel der NEF N1 Dienst.
Du hast das so gut gelöst daß man die Dienste einfach durch die Kreuze für die Tage verändern kann.
Das ist Top.
Aber könnte man auch Dienste kombinieren?
Beispiel: Der Nachtdienst geht eigentlich von Mi bis am darauffolgenden Di.
deswegen habe ich die Idee mit dem N1 udn N2 und N WE gehabt. Vielleicht war das auch doof?
Die Zeiten am Fr und Sa sind die Dienste N WE udn sollten in der gleichen Woche wie N1 ausgegeben werden. Das gleiche gilt auch für RTW N und RTW N WE
Unn zusätzlich im oberen Bereich unter J5 wo steht Wochenenddienst.
Kann man das machen?
Die Idee mit der Spalte O ist pfiffig.
Gruß Rolf

Hallo Rolf,
da muss man das Hilfsspalten-Konzept etwas erweitern. Die Hilfsspalten kann man in der Endversion ja ausblenden.
Im Blatt "Dienste" wird eine weitere Spalte für die Bezeichnung des Wochenddienstes eingefügt.
Im Blatt "Stundenzettel" wird in Zelle P12, wenn in J8 "WE-Dienst" gewählt ist, der zum Wert in J5 gehörige WE-Dienst ermittelt und in Zeilen 13 bis 19 analog zur Spalte O die Prüfung auf die X-Einträge bei den Wochentagen durchgeführt. In Spalte Q wird dann die Kombination der Eingaben in Spalte D und der Ergebnisse in Spalten O und P ausgewerte. Der Wert in Q wird dann in den SVERWEIS-Formeln verwendet.
Damit dies optimal funktioniert hab ich im Blatt "Dienste" die Zeilen in Gruppen unterteilt, so dass die Auswahllisten für Dienste nach Woche, Wochenende und Springer optimiert sind. Die Namen und Bereiche für die Auswahllisten hab ich entsprechend angepasst.
Gruß
Franz
https://www.herber.de/bbs/user/66307.xls

Hallo Franz,
puh, das würde ich so nie hinbekommen......Danke dir für deine Mühen.
Allerdings dürfen die Kollegen in Zelle J8 keine manuellen Einträge vornehmen. Dieses Feld war eigentlich ursprünglich nur als Anzeige gedacht.
Da es nun Dienste gibt bei denen nur Mo-Fr und am Samstag gearbeitet wird und Dienste and denen
von Mo-Fr und Sa und So gearbeitet wird, habe ich das richtig verstanden daß ich das jetzt auch alleine im Blatt "Dienste" in Zeile 38-40 eingeben könnte? Alsoso daß alles automatisch erfolgt?
Also bei RTW T geht dies ja schon super. Beispiel:
RTW T arbeitet Mo-Sa gleich
RTW F arbeitet Mo-Fr 06:00-13:15 und am Sa und So 08:00-20:00 Uhr
RTW N1 arbeitet Mi Do und So 20:00-06:15 Fr und Sa 20:00-08:00 Uhr
dieser Dienst wäre identisch mit NEF N1
Könnte ich diesen Dienst dann einfach auf Blatt Dienste oben einkopieren?
Also z.B.ab Zeile 35 einfach weitere Zeilen einfügen, oder würde das alles durcheinander bringen?
Lediglich die Dienste NEF T und NEF N haben eine Springerfunktion und werden sehr unterschiedlich eingesetzt
Was bedeutet dein Einttrag in G37 "Leerzeile für Rolldown Auswahl" ?
Und ginge es, daß man generell die SOLL Stunden in der Spalte H13-H19 löscht wenn ein x in L13...L19 bei Überstundenfrei steht?
Entschuldige die vielen Fragen.
Gruß Rolf

Hallo Rolf,
ich hab mal deine Frageliste in die Datei eingearbeitet und dabei auch ein paar Formelvereinfachungen im Bereich O12:Q19 vorgenommen - der ISTFEHLER-Teil ist jetzt weg. Diese Vereinfachungen sind möglich, da durch die Datenprüfung in den Rolldown-Auswahleingaben Datenkonsistenz hergestellt wird. Ich hab die Daten-Prüfung "etwas verschärft" und auf STOP statt INFORMATION umgestellt; dadurch wird die Eingabe eines gültigen Werts erzwungen.

puh, das würde ich so nie hinbekommen......Danke dir für deine Mühen.

Mit Übung und guter Kenntnis der Funktionen in Excel kann man schon so einiges an Daten über mehrere Ecken verbinden. Deshalb hier auch die Verwendungvon Bereichsnamen in den Formeln. Das erleichtert die Übersicht und Formelpflege.

Allerdings dürfen die Kollegen in Zelle J8 keine manuellen Einträge vornehmen. Dieses Feld war eigentlich ursprünglich nur als Anzeige gedacht.

Zelle J8 enthält keine Formel mehr. Arbeitsfreie Tage müssen jedoch in Spalte D gekennzeichnet werden.
In Zelle P12 ermittelt die Formel jetzt ohne Prüfung des Werts in J8 aus der Eingabe in J5 den zugehörigen Wert aus Blatt Dienste in Spalte Q.
Die Formeln in Zellen J9 und J10 benutzen jetzt mit geänderter Prüfung den Wert in P12 zur Anzeige der abweichenden Arbeitszeiten am Wochenende.

Da es nun Dienste gibt bei denen nur Mo-Fr und am Samstag gearbeitet wird und Dienste and denen
von Mo-Fr und Sa und So gearbeitet wird, habe ich das richtig verstanden daß ich das jetzt auch alleine im Blatt "Dienste" in Zeile 38-40 eingeben könnte? Alsoso daß alles automatisch erfolgt?

Ja das hast du richtig verstanden.
Alle Wochendienste, die einen 2. Satz Tage mit einer anderen Arbeitszeit haben trägst du hier ein. Wenn die Zeiten und Tage identisch sind, muss du die Zeile nur einmal anlegen und kannst Sie mehrfach in Spalte Q verwenden. In der Beispieldatei hab ich das mal so angelegt. Wochendienst "NEF F" und "RTW F"verwendet z.B. die Wochenend-Daten von "WE"; "NEF N1" und "RTW N1" die Daten von "N WE". Das kannst du flexibel Handhaben. Einzige Bedingung: In Spalte A im Blatt Dienste darf keine Bezeichnung doppelt vorkommen.

RTW N1 arbeitet Mi Do und So 20:00-06:15 Fr und Sa 20:00-08:00 Uhr
dieser Dienst wäre identisch mit NEF N1
Könnte ich diesen Dienst dann einfach auf Blatt Dienste oben einkopieren?
Also z.B.ab Zeile 35 einfach weitere Zeilen einfügen, oder würde das alles durcheinander bringen?

Ja mit Zeilen-Kopieren bzw. Zeilen-Ausschneiden und einfügen kannst du Datenzeilen ergänzen/verschieben. Wichig ist immer die gesamten Zeilen zu markieren. Die Datenbereiche passen sich dann automatisch an. Ich hab in der Datei die Bereich im Blatt "Dienste" nochmal farblich hervorgehoben und in Spalte A ein paar Zellen mit Kommentaren versehen.

Lediglich die Dienste NEF T und NEF N haben eine Springerfunktion und werden sehr unterschiedlich eingesetzt

Das kannst du nach Belieben festlegen. Den Dienst "Frei" muss du hier aber auch zur Verfügen stellen, damit in Spalte D des "Stundenzettels" die arbeitsfreien Tage gekennzeichnet werden können.

Was bedeutet dein Einttrag in G37 "Leerzeile für Rolldown Auswahl" ?

Das ist eine kleine Erleichterung für die Auswahl durch den Anwender. Wenn in der Zelle mit dem Rolldown nichts steht, dann wird so die Liste mit dem 1. Wert als Auswahl angeboten. Andernfalls irgeneine andere Zelle ohne Eintrag, was meistens lästig ist, weil man ggf. unnötig scrollen muss.

Und ginge es, daß man generell die SOLL Stunden in der Spalte H13-H19 löscht wenn ein x in L13...L19 bei Überstundenfrei steht?

Ich hab die Formeln in H13:H19 um einen entsprechenden WENN-Teil ergänzt. Ich hoffe das passt so.

Entschuldige die vielen Fragen.

Kein Problem.
Hier die aktualisierte Datei.
https://www.herber.de/bbs/user/66314.xls
Gruß
Franz

Guten Morgen Franz,
ich bin sprachlos. Du hast auf den ersten Blick anscheinend alles top umgesetzt was uns sehr sehr weiterhilft. Bis hierher vielen lieben Dank an dich!
Eine Dummheit ist mir aber schonmal passiert .
Bei der Bitte....."Und ginge es, daß man generell die SOLL Stunden in der Spalte H13-H19 löscht wenn ein x in L13...L19 bei Überstundenfrei steht?", habe ich dir die falsche Spalte angegeben. Es muss natürlich die rechts daneben sein, die ausgeblendet werden soll. Bitte sei mir nicht allzu böse.
Also I13:I19 da ja die geleisteten Stunden bei Überstundenfrei zu einer -Anzeige führen müssen Entschudlige.
Nun habe ich zwar versucht diese WENN Option selber dort einzubauen was mir aber nicht gelungen ist.
Auch habe ich dabei gleich versucht daß auch die beiden anderen Spalteneinträge "Urlaub" und "krank" in die Spalte J13:J19 einschreiben zu lassen wenn das x entsprechend gesetzt ist. Aber ich bin doch zu doof dazu. Habe folgendes versucht
=WENN(L13="x";$L$5;0)+WENN(M13="x";$M$5;0)+WENN(N13="x";$N$5;0) aber das führt immer zu #WERT!.
Die Einträge x bei Urlaub und krank dürfen dabei aber nicht zur Löschung der Einträge in Spalte J13:J19 führen. Sie sollen nur als Text eingetragen werden.
Könntest du da mal nach schauen bitte?
Ich bedanke mich ganz herzlich im Voraus bei dir
Die anderen Verbesserungen von dir sind wirklich gut gelungen. Bis jetzt ist alles genauso wie wir das brauchen. Alles ist flexibel und leicht verständlich. Auch die farbige Hervorhebung ist genial!
Danke dir vielmals für die gute Arbeit!
Gruß Rolf

Hallo Rolf,
hier die Formelanpassungen

Zelle H13: =WENN($Q13="";0;SVERWEIS($Q13;Dienst.Data;5;0))
Zelle I13: =WENN($Q13="";0;WENN(L13="x";0;REST(F13-E13-G13;1)))
oder
Zelle I13: =WENN(ODER($Q13="";L13="x");0;REST(F13-E13-G13;1))
Zelle J13: =WENN(L13="x";$L$5;"")&WENN(M13="x";$M$5;"")&WENN(N13="x";$N$5;"")
oder
Zelle J13 =WENN(ISTFEHLER(VERGLEICH("x";L13:N13;0));"";
INDEX($L$5:$N$12;1;VERGLEICH("x";L13:N13;0)))
oder
Zelle J13 =VERKETTEN(WENN(L13="x";$L$5;"");WENN(M13="x";$M$5;"");WENN(N13="x";$N$5;""))

Diese Formel-Zeilen kannst du dann bis Zeile19 herunterkopieren.

Aber ich bin doch zu doof dazu. Habe folgendes versucht
=WENN(L13="x";$L$5;0)+WENN(M13="x";$M$5;0)+WENN(N13="x";$N$5;0) aber das führt immer zu #WERT!.

Der Versuch Text mit dem mathematisch Operator "+" zu addieren funktioniert in Formeln nicht. "+" erfordert zwingend nummerische Werte oder leere Zellen als Parameter. Für Text-Verkettungen gibt es den Operator "&".
Gruß
Franz

Hallo Franz,
danke für deine Erklärung. Wieder was gelernt.
Deine Formeln funktionieren einwandfrei.
Gut dann werde dich die Datei mal an ein paar Kollegen zum testen geben.
Mal sehen ob es noch eine ungelöste Kombination gibt.
Und wenn ich darf melde ich mich dann nochmals bei dir?
Du hast mir/uns aber auf jeden Fall schon sehr sehr viel weiter geholfen.
Ohne dich wäre das nichts geworden.
Hab recht herzlichen Dank dafür.
Gruß Rolf

Hallo Franz,
damit du nicht denkst daß ich dich vergessen hätte, melde ich mich mal zwischendrin ;-)
Ich habe deine Arbeit bis jetzt mal durchprobiert. Funktioniert alles soweit sehr gut!
Werde die Tabelle jetzt mal an Kollegen geben. Vielleicht kommen die ja auf eine Konstellation die ich nie so machen würde. Aber ich glaube du hast mein Problem sehr sehr gut gelöst.
Hab nochmal recht herzlichen Dank dafür.
Du haßt mir wirklich sehr gut weitergeholfen
Gruß Rolf

Hallo Franz,
also es kamen nur positive Rückmeldungen.
Keine Fehler. Alles funktioniert hervorragend.
Du hast die Sache großartig gelößt und uns sehr geholfen.
Wir können uns nur vielmals bei dir bedanken!
Also, recht herzlichen Dank an dich
Viele Grüße Rolf
Die Sache ist gelößt