Thema: Variable Summenspalte

Hallo an die helfenden Mitleser.

Nach langer Forumssuche habe ich es leider noch nicht geschafft auf eine Lösung meines Problems zu kommen.

Ich suche eine Möglichkeit,
in der hochgeladenen Datei (Monatsabrechnung) die Summenspalte variabel zu gestalten.
https://www.herber.de/bbs/user/175416.xlsx

In dieser Liste werden unsere Mitarbeiter (12 Stück) eingepflegt.
Ich brauche zur Kontrolle der Wochenleistung (max. 40h) eine Summenspalte,
da die Kalenderwochen, je Monat natürlich unterschiedlich sind,
sollte diese variabel gestaltet werden, also sie soll sich automatisch für jedes Monat bei der Erstellung eines neuen Monats verschieben.
Verwirklicht soll dies am Ende über ein Makro werden, wenn das Monat fertig ist und ein neues Monat beginnt,
wird ein "Button" gedrückt und es beginnt wieder alles von vorne.

Ich hoffe ich konnte es so einigermaßen verständlich erklären und würde mich über helfende Tipps freuen.

Hallo

Nur als Frage:
Warum dieser Tabellenaufbau?
Warum die viel Zahl an bedingten Formatierungen?
Falls anderer Aufbau zulässig ist Ist Power Query auch zu gelassen als Lösungsfindung?

Mit dieser Liste hatten meine Kolleginnen gearbeitet,
als ich in den Betrieb gekommen bin.

Die bedingten Formatierungen waren nur der Anfang um Summen und/oder Stunden <40 aufzuzeigen.
da wir nur jeweils eine Woche für die Mitarbeiter planen können, aber die Summe pro Woche als auch die Stunden pro Monat nicht überschritten werden dürfen.
Wenn ich es ganz genau nehme, sollte ich auch noch einen Durchschnitt von etwa 15 Wochen berücksichtigen, aber das sprengt erstmal den Rahmen.

Für Vorschläge bin ich gerne offen.

Die Datei die veröffentlicht wurde, wäre ja nur für einen Mitarbeiter.
In dieser Liste sind normal 12 Mitarbeiter untereinander aufgeführt und jeden Monat wird ein Tabellenblatt erweitert,
sodass man am Ende des Jahres dann sozusagen 12 Tabellenblätter hat und diese Datei wird dann für jedes Jahr neu angelegt und das alte Jahr abgelegt und aufbewahrt.

Und was genau ist FG ?

Hallo

Anbei mein erster Probeentwurf wie ich den Plan machen würde.
Bisher erst nur für 2 Mitarbeiter. Die Überschriften wie sie bisher sind gefallen mir noch nicht das sei schon mal vor bemerkt.
Die Summenbildung soll später im Tabellenblatt "Auswertung" erfolgen.
https://www.herber.de/bbs/user/175422.xlsx

Fg ist die Abkürzung für Fahrgast.

Haben Sie eine Möglichkeit gefunden?
Auch die Möglichkeit mit VBA zu arbeiten würde ich in Betracht ziehen, falls dies der Lösungsfindung dient. :-)

Um da was Praktikables zu erreichen, muss man fast die GANZE Tabelle umbauen. SO arbeitet man nicht mit Excel, so macht man sich alles nur viel schwerer als nötig.
Excel ist schliesslich eine Tabellenkalkulation und keine Textverarbeitung.
Du brauchst z.B. die Datumszeile als Formel, die, nach Auswahl des Monats und des Jahres automatisch das Datum errechnet (und fortführt) - und zwar im Datumsformat.
Dann kann z.B. auch Wochentag ruckzuck errechnet werden (und alles andere wird auch leichter. Du könntest sogar die Wochentag-Zeile weglassen, wenn du das Datum mit Wochentag formatierst.
Da du jeweils 2 Zeitpaare eingeben willst, würde ich unter den beiden Zeilen noch eine Summenzeile einbauen, wo die Tagessumme errechnet wird, dadurch werden auch alle anderen Berechnungen wesentlich einfacher. Und pro Tag würde ich auch eine Spalte für die Stundenberechnung des Zeitpaares einbauen (wenn du sie unbedingt brauchst).
Wenn du das alles änderst, helfe ich dir gerne mit dem Rest.

Leider versteh ich nicht ganz was sie meinen.

Ich hab die Abrechnung nochmal hochgeladen,
nun wären 2 Mitarbeiter angelegt, so hat sie bis jetzt ausgesehen, nur das 12 Mitarbeiter untereinander standen.

Alles was ganz rechts neben den Summenspalten am Ende steht, ist normal nicht sichtbar und die "Hilfstabelle" auch nicht.

https://www.herber.de/bbs/user/175419.xlsx

Wenn Sie ein Beispiel für mich haben,
um meinem Verständnis auf die Sprünge zu helfen, wäre die super.

P.S.: Die Datums- bzw. Tageszeile wurde angepasst.
Konnte sonst nicht mehr weiterrechnen. :-)

Übrigens - hier wird sich prinzipiell nur GEDUZT.
Teste mal:
https://www.herber.de/bbs/user/175420.xlsx

Deine bedingten Formatierungen sind Schrott - erkläre, was du wann wie Färben willst und ich reduziere sie gewaltig.

Okay super,
danke erstmal für die ersten Inputs.
Mit dieser Form kann ich auch arbeiten.
Ist mir noch gar nicht in den Sinn gekommen. :-)

Warum sind meine "bedingten Formatierungen" Schrott?
Ich habe fälschlicher Weise einfach nur den Mitarbeiter kopiert und dadurch dürfte die Tabelle des 2ten Mitarbeiters nicht 100% stimmen,
beim 1sten Mitarbeiter sollte sie eigentlich passen, oder?
Ist es leichter diverse Formatierungen via VBA umzusetzen?

Aber im Prinzip sollen die Wochenstunden, wenn sie über 40h geplant sind, oder tatsächlich gefahren sind "ROT" markiert werden und auch die monatlichen Gesamtstunden.
Wenn ein Wert in "Urlaub", oder "Rufbereitschaft", oder "Sonstiges" eingetragen wird, soll die Farbe der jeweiligen Summenspalte in "dunkel" erscheinen, sonst bleibt sie leer.

Sollte eine "kurzfristig verständigte Schicht" eingetragen werden, wird der Wert sichtbar und in "Rot" markiert und am Ende des Monats, in der Summenspalte sichtbar.
"Sonn- und Feiertage" sollen ebenfalls über die Spalten der betroffenen Tage hervorgehoben werden, hab ich aber noch nicht eingearbeitet.

Die Daten kommen dann später über einen Import in die Tabelle, sollen aber auch bearbeitbar bleiben.

"Warum sind meine "bedingten Formatierungen" Schrott? " ?
Weil man eigentlich, wenn man es vernünftig macht, nur EINE bedForm pro Farbe braucht und nicht Eine pro Zelle und Farbe. :)

"Warum sind meine "bedingten Formatierungen" Schrott? " ?
Weil man eigentlich, wenn man es vernünftig macht, nur EINE bedForm pro Farbe und Regel braucht und nicht Eine pro Zelle und Farbe. :)
Wo stehen den die reel gearbeiteten Monatsstunden? Ich sehe nur die max. Monatsstunden dort eingetragen.

Meinst du die gesamten Monatsstunden?

Die sind in meiner Tabelle in den Spalten "BX".
Die "Arbeitszeit TAZ" sind die tatsächlich gearbeiteten Stunden.
Die "Arbeitszeit BAZ" sind die bezahlten Arbeitsstunden.
Und unter "Dienstplan" sehen wir, ob wir in der Wochenplanung und auch in der Monatsplanung die maximal Stunden nicht überschritten haben.

Der Bezug für die "Max.-W-Std." ist noch auf "CA3" und
der Bezug für die "Max.-M-Std." ist derzeit noch auf "CA4".

Ich hoffe du meinstest dies. :-)

"Die "Arbeitszeit TAZ" sind die tatsächlich gearbeiteten Stunden.
Die "Arbeitszeit BAZ" sind die bezahlten Arbeitsstunden.
Und unter "Dienstplan" sehen wir, ob wir in der Wochenplanung und auch in der Monatsplanung die maximal Stunden nicht überschritten haben. "

Warum steht dann da keine Summenformel, sondern immer nur 173 (Max-Stunden)?

"Warum sind meine "bedingten Formatierungen" Schrott? " ?
Weil man eigentlich, wenn man es vernünftig macht, nur EINE bedForm pro Farbe und Regel braucht und nicht Eine pro Zelle und Farbe. :)
Wo stehen den die reel gearbeiteten Monatsstunden? Ich sehe nur die max. Monatsstunden dort eingetragen.
Was sind denn die gearbeiteten Stunden ? Dienstplan oder Dienstzeit?

Ich habe nun die Formatierungen, die Datums- und Wochentagszeilen etwas angepasst.

Also das Thema mit der "variablen Summenspalte" ist somit erfolgreich beendet.
Soll ich nun ein neues Thema öffnen, oder kann ich hier weitere Fragen zu "Berechnungen",
"Rufbereitschaft" und ein quasi "Auto einsetzen der neuen Tabelle", wenn neuer Mitarbeiter angelegt wird,
ohne dass die Vormonate davon berührt oder geändert werden.

https://www.herber.de/bbs/user/175490.xlsx

Das würde bedeuten, dass das Tabellenblatt "Mitarbeiter" sich immer nur auf das neue Kalendermonat,
auf einem neuen Tabellenblatt bezieht.
Ist soetwas möglich bzw. machbar?

Vielen Dank für eure Unterstützung und Hilfe.

Bin doch schon dabei, alles zu verbessern....
Da ist sowieso noch ein Problem gewesen, denn ein Monat kann sogar über SECHS Wochen laufen, wenn z.B. der 1.3. auf Sa oder So fällt.
Gib mir etwas Zeit...
Soll denn pro Blatt etwa nur ein Mitarbeiter aufgeführt sein? Wieviel Mitarbeiter maximal habt Ihr?
Kleine Erläuterung (in Worten - ohne Verweise auf Zellen) wäre auch noch gut, damit ich alle Ergebnis-Formeln (Monat und Woche) richtig umstellen kann.

Uff, das mit der 6sten Woche hab ich gar nicht bedacht.
Danke für den Hinweis. :-)

Nein ein Tabellenblatt ist für alle Mitarbeiter für den aktuellen Monat.
Zur Zeit sind es 12 Mitarbeiter, werden aber ständig mehr, daher muß ich die Tabellen auch erweitern können.
Ich habe schon an eine Input-Box gedacht, aber zuerst muss ich einmal das Grundgerüst fertig bekommen. :-D

Zu den Erläuterungen brauch ich leider nochmal etwas Denkanstoß, welche Infos du brauchen könntest. :-)

Dankend für deine Hilfe.

"Zu den Erläuterungen brauch ich leider nochmal etwas Denkanstoß, welche Infos du brauchen könntest. " - Ich meine die Auswertungen (alle Summenformeln).
Du könntest mal alle (Summen-) Formeln einer Woche durch fortlaufende Nummern ersetzen und nebenan jede einzelne Nummer erklären.
z.B. Nr 1: Summe aller tatsächlich gearbeiteten Stunden in der Woche. Das Selbe mit den Monatsauswertungen. Nur damit meine Formeln das Selbe berechnen wie Deine.
Sorry, aber deine Formeln zu analysieren verursacht bei mir fast schon Kopfschmerzen, weil sie so ellenlang sind.

Du brauchst aber nur ein einziges Blatt mit allen Mitarbeitern - die nicht ausgefüllten Blöcke kann man ebenso herausfiltern lassen wie die Spalten.

SO geht es vielleicht einfacher für dich.
Im ersten Block und der ersten Woche habe ich mal MEINE Summen-Formeln eingesetzt - überprüfe sie bitte mal anhand irgend welcher Testeintragungen.
Hier die aktuelle "Baustelle":
https://www.herber.de/bbs/user/175492.xlsx

Hallo nochmal,
irgendwie muss ich immer suchen wo die Antworten zum selben Beitrag sind. HiHi :-)

Ich habe zuletzt eine angepasste Version gesendet,
kann ich hier deine Formeln der Summen einfügen um sie zu testen?

Könntest du so nett sein und mir dein Anliegen, mit den Summen-Formeln kurz anhand eines Screenshots erläutern?
Wie meinst du "die Formeln" durch "fortlaufende Nummern" ersetzen?
Also alle Formeln löschen und durch Nummern ersetzen, für die Wochenberechnung und die Monatsberechnung?

Ich muss mich da etwas durcharbeiten, daher habe ich in der aktuell veröffentlichten Version,
https://www.herber.de/bbs/user/175490.xlsx
alles auf vorerst einen Mitarbeiter reduziert, auch die "bedingten Formatierungen",
die Datums-Auswahl habe ich angepasst, damit deine Formeln für die "Tagesanzeige" und den "Wochentag" funktionieren.

Alles was in den "gelben" Zellen steht, also oberhalb von "Urlaub", sind die "tatsächlich gefahrene Zeiten",
diese werden am Ende der Woche zusammengerechnet, inkl. "Urlaub & Krank" und "Schulungen".
die Rufbereitschaften sollten nur dann angerechnet werden, wenn:
1. die Rufbereitschaft eingetragen, aber nichts gefahren wird. dann werden 8h verrechnet (aber nur in der oberen Summenspalte)
2. die Rufbereitschaft eingetragen, eine Schicht gefahren wird, die >= 8h ist, dann wird die Arbeitszeit berechnet, aber nicht die Rufbereitschaft selbst.
3. die Rufbereitschaft eingetragen, eine Schicht gefahren wird, aber die Schicht kürzer als 8h ist, dann wird eine Mindestschichtdauer von 8h dazugerechnet.
Die Ausfälle, da werden nur die Schichten gezählt, ohne Fahrgastfahrten (diese Schichten werden dann oberhalb entfernt und kommen dann in diese Zeile),
werden dann am Ende der Woche dazugezählt.


Alles was in den "blauen" Zellen steht, also unterhalb von "Urlaub" und so weiter, ist die Zeit, die wir in der Planung geplant haben.
Auch diese werden am Ende der Woche summiert, hier werden alle Dienst- und Fg-Zeiten zusammengezählt und
noch zusätzlich die "Urlaube", die "Schulungen" und die "Rufbereitschaften", jedoch hier in voller Länge.

Am Ende der Tabelle werden dann alle Wochensummen zusammengezählt und es sollten die Monatsstunden ergeben.
Unter "TAZ" ist dann die tatsächliche Arbeitszeit zusammengezählt, inkl. Urlaube, Schulungen und Rufbereitschaften nur, wenn tatsächlich etwa gefahren wurde
(also entweder die Schichtdauer, oder die Mindestschichtlänge von 8h), excl. den Ausfällen.

Unter "BAZ" werden alle Wochenstunden summiert, die bezahlt werden müssen,
Eigentlich alle Stunden, inkl. Urlaube, Schulungen, Rufbereitschaften (wenn etwas gefahren wurde, dann die Schichtdauer, oder mindestens 8h) und die Ausfälle.

Unter "Dienstplan" werden dann alle Fg-Zeiten, alle Dienstzeiten, alle Urlaube, alle Schulungen und Rufbereitschaften summiert, die geplant wurden,
damit wir sehen können, ob ein Mitarbeiter, entweder wöchentlich oder monatlich über das gesetzliche Limit geplant wurde.

Unter "KF" werden alle Kurzfrist-Schichten summiert, wenn es welche gegeben hat.

Ich hoffe ich konnte es etwas besser beschreiben.

Wo ich auch noch Probleme habe ist,
wenn ein Dienst an einem Tag beginnt und bis in den nächsten Tag überläuft, dies müsste ich auch noch abfangen.
Bei "Schulungen" und "Urlauben" ist dies jedoch nicht der Fall.

Du solltest nicht die einzelnen Zeilen, sondern nur die SUMMENSPALTEN erklären - z.B. so:
https://www.herber.de/bbs/user/175513.xlsx

Ah, okay.
Jetzt hab ich es auch verstanden.

Bin dran, gib mir bitte etwas Zeit.
Heute und morgen sind die stressigsten Tage.
Ich versuche es noch heute fertig zu bekommen.

Kein Problem...

Melde gehorsamst!
Datei fertiggestellt und bereit zur Übermittlung. :-P

Nein Scherz beiseite,
ich habe nun in der Tabelle die "erste" Summenspalte mit Nummern versehen, wie von dir beschrieben.
Dies zieht sich natürlich für alle "Wochensummen" hindurch bist zum Ende. :-)

Und auch in den Monatssummen habe ich die Formeln durch Nummern ersetzt und diese ebenfalls beschrieben.

https://www.herber.de/bbs/user/175557.xlsx

Ich hoffe es ist etwas übersichtlicher geworden.

Auf die "bedingten Formatierungen" hab ich noch nicht so geachtet,
aber es sind nun die Feier- und Sonntage bereits eingearbeitet.
Die Farbe für die nicht vorhandenen Tage, von "weiß" auf das ursprüngliche "grau" hab ich leider wieder vergessen umzustellen.

Vielen Dank für die Hilfe, die Unterstützung und die Geduld!

Was soll denn "ABER! Nummer 6 NUR" bedeuten ???

"7 … Ist in Nummer "8" nichts eingetragen, wird der Text "KF" für kurstfristig Verständigt ausgeblendet.
8 … Hier werden die "KF-Schichten" eingetragen, die der Mitarbeiter in der Woche erhalten hat, später wird dies aus einer Excel importiert."
Aber in deiner ersten Datei steht nix in 8 und trotzdem steht in 7 "KF" - Was stimmt denn nun ?

Aber das Problem mit den 6 Wochen ist eigentlich kein Problem. Man kann die nicht benötigten Spalten einfach automatisch ausblenden lassen....

Das hab ich schon einmal gelesen, würde eventuell auch die "bedingte Formatierung" zum ausblenden ersparen, oder?
Und schicker aussehen würde sie auch. :-)

Ja schon, aber ich würde das Ausgrauen so lassen, da man dann besser damit arbeiten (ausfüllen oder Ergebnisse überprüfen) kann. Ausserdem siehst du dann, ob du vergessen hast, einige alte Eintragungen zu löschen oder nicht.
Nur zum Drucken würde ICH sie ausblenden.

Auch ein guter und berechtigter Einwand. :-)

Ich warte immer noch auf eine Antwort.....

Ich habe das mal angefangen für den 1. des Monats, damit du siehst, was ich meine....

https://www.herber.de/bbs/user/175418.xlsx

Sorry hat sich jetzt überschnitten.
Hab ich das jetzt richtig verstanden, dass sie die Datumseingabe, bzw. die Weiterverarbeitung von "Tag" und "Wochentag" geändert haben?
Meinten sie dies mit "Textverarbeitung" alla Word? :-)