Office: Jahresdienstplan - Monatsplan - Einzelplan - Wverweis Problem

Hallo ihr Lieben,

ich versuche für meine Firma einen dynamischen Dienstplan zu erstellen, wo zum einen die einzelnen Schichtführer die Schichten ihrer Leute eintragen und zum anderen sich unter "Leistungsnachweis" einen Monatsplan ihrer Schicht aus dem Jahresplan "herausziehen" können.

Beim Leistungsnachweis hab ich das Problem, dass mir der Plan nur bei der A-Schicht die nötigen Informationen aus dem Tabellenblatt "Daten" herauszieht. Und da auch nur mit einer 0 am Ende, keine Ahnung wieso. Ich versteh auch nicht, warum mir Excel nach Betätigen der Drop-Down Liste die anderen Schichten nicht anzeigen möchte.

Ich bin mir auch nicht sicher bzw. weiß ich es überhaupt nicht ;-), wie ich mir den erforderlichen Monat der besagten Schichtgruppe im Reiter Leistungsnachweis anzeigen lassen kann.

Geplant ist auch noch einen Einzelnachweis zu erstellen, wo ich mir dann in einer senrechten Spalte nach Eingabe (Drop-Down Liste) der Personalnummer, einen Einzelplan für einen Mitarbeiter darstellen lassen kann. Diese Funktion sol gedacht sein für unseren Betriebsrat, damit er die Anfangs- und Endzeiten einzelner Mitarbeiter prüfen kann.

Würd mich über Hilfe freuen. Lern immer wieder gerne dazu, vor allem in Excel.

PS: Den Test-Dienstplan hab ich auch mal drangehängt.

Grüße
Heiko

 

Hallo Heiko,

schön wäre es zu wissen, welche Excel-Version du hast. Denn es ist ziemlich sinnlos, eine komplizierte Formel zu bauen, wenn es womöglich aufgrund einer neuen Excel-Version auch ganz einfach ginge. Umgekehrt ist es genauso sinnlos, wenn man eine einfache Formel baut, die von einer älteren Excel-Version nicht verstanden wird.
Die Versionsbezeichnung, um die es hier geht, ist zu finden im Menüband > Tab "Datei" > Button "Konto" : Unterhalb der Überschrift "Produktinformationen Microsoft"
Mögliche Versionen: Excel365, Excel2021, Excel2019, Excel2016, Excel2013, Excel2010, etc.

Soviel habe ich schon gesehen, dass du die WVERWEIS()-Funktion falsch verwendest. So kann sie nicht funktionieren.
Aber genau da wäre es schon wichtig zu wissen, welche Excel-Version du hast.

 

Hab auf die Office Professional Plus 2021 geupgraded. Also hier zu Hause auf dem Rechner. Die Version auf dem Firmenrechner ist mir leider nicht bekannt. Aber arbeiten werde ich an dem Plan hier von zu Hause aus.

Wie verhält sich das denn genau mit den unterschiedlichen Versionen. Mal angenommen ich bastel mir jetzt ne Excel Datei in Excel2021 und auf dem Firmenrechner läuft nur das 19er oder 16er. Kann ich die Datei dann dort auch bearbeiten und mit ihr arbeiten oder bloß öffnen und "anschauen"?

 

Hallo Heiko,

In der Regel können diese Excel-Dateien alle gegenseitig geöffnet werden.
Was die innere Logik und Funktionalität betrifft, galt früher in der Regel, je neuer die Version war, umso mehr Funktionalität hatte sie.

Mit den Versionen Excel2019, Excel2021, Excel365 wurde eine neue Logik bei der Array-Funktionalität eingeführt: die sogenannten dynamischen Arrays (im Gegensatz zu den früheren statischen Arrays).

Bei den Versionen Excel2021, Excel2019, Excel2016, etc. bekommt man eine fest vorgegebene Funktionalität, wie sie von Microsoft zum Zeitpunkt der Publikation dieser Version festgelegt wurde. Für diese gibt es dann hinterher nur mehr Fehlerbehebungen und diverse kleinere Verbesserungen. Man bezahlt dafür einmalig einen bestimmten Betrag.

Dagegen ist Excel365 die Abo-Version. Für dieses Abo bezahlt man entweder monatlich oder jährlich einen Abo-Betrag. Das hat andererseits den Vorteil, dass man von Zeit zu Zeit eine verbesserte Funktionalität bekommt mit neuen Funktionen. Man kann hier außerdem die Schnelligkeit von Updates festlegen. Allerdings sollte man für betriebliche Zwecke nicht zu experimentierfreudig sein mit zu schnellen Updates (Bei den schnellsten Updates sind Funktionsnamen / Funktionalität / Fehlerbehebung noch ein Thema). In dieser Abo-Version sind in der letzten Zeit Funktionen hinzugekommen, auf die man eigentlich schon lange gewartet hat und die sehr leistungsstark sind. Sie sind nur zum geringen Teil in Excel2019 bzw. Excel2021 enthalten.

Eines ist klar, eine Funktion, die in einer Version nicht enthalten ist, kann in dieser auch nicht angewandt werden - es sei denn, man baut sich diese Funktionalität in VBA nach. Man bekommt dann die Fehlermeldung #NAME?, weil dieser Name dann unbekannt ist.

 

Hallo Heiko,

in Ergänzung zu den allgemeinen Bemerkungen in Post#4 habe ich deine Excel-Datei modifiziert (Excel2021 konform):

1) Im Arbeitsblatt "Daten" habe ich die neue struktur.Tabelle "tbSchichten" eingefügt mit den gleichen Spalten wie die 5 Teiltabellen und um die Spalte "Schicht" erweitert. Diese eine Tabelle lässt sich mit der Funktion FILTER(..) leichter abfragen als die 5 Tabellen für je 1 Schicht.

2) Im Arbeitsblatt "Jahresplan" habe ich in die Spalten A und B der 1. Zeile jeder Schicht eine dynamische Array-Formel eingefügt:
2a) in Spalte A in Zelle A8 zB.: =FILTER(tbSchichten[Personalnr];tbSchichten[Schicht]=LINKS($B7;SUCHEN("-";$B7)-1))
2b) in Spalte B in Zelle B8 zB.: =XVERWEIS($A8#;tbSchichten[Personalnr];tbSchichten[Name];"--";0;1)
vgl. dazu auch die Kommentare zu den Punkten 3f)+3g).

3) Im Arbeitsblatt "Leistungsnachweis" habe ich:
3a) im Bereich D7:AE7 die Formeln für die Monatstage vereinfacht, denn die ersten 28 Tage sind ja in jedem Monat enthalten-
3b) im Bereich D8:AH8 die Formeln für die Wochentage vereinfacht
3c) im Bereich AS7:AS18 ("Monate") die Formeln für die ausgeblendeten Monatsnamen vereinfacht
3d) im Bereich A27:A31 ("Schichtgruppe") habe ich die Formel in A27 für die die Schichtenliste geändert: =EINDEUTIG( tbSchichten[Schicht]; FALSCH;FALSCH)&"-Schicht": Diese Formel erzeugt ab A27 ein dynamisches Array (auf Basis der strukt. Tabelle "tbSchichten"). Dieser Bereich dient weiterhin als Auswahlliste des Schichten-DropDowns in K4:Q4
3e) in der Zelle A26 habe ich die Formel für die ausgewählte Schicht-Nr. stehen lassen: =VERGLEICH(B8;A27#;0). Sie wird aber momentan nirgendwo verwendet.
3f) in der Zelle A9 habe ich eine weitere dynam. Array-Formel für die Personal-Nr.Abfrage der ausgewählten Schicht im DropDown von K4:Q4 eingefügt: =FILTER(tbSchichten[Personalnr];tbSchichten[Schicht]=LINKS($B$8;SUCHEN("-";$B$8)-1))
Diese Personal-Nr. müssen über alle Schichten hinweg eindeutig zu nur 1 Person zugeordnet werden können. Die FILTER()-Funktion greift wiederum auf die Tabelle "tbSchichten" zu. Der Formelteil ab LINKS(..) bewirkt nur ein Entfernen der Zeichen ab "-", es wird also nur nach "A",...,"D", "Pool" gefiltert.
3g) in der Zelle B9 habe ich die dynam. Array-Formel für die Namensabfrage der ab Zelle A9# enthaltenen dynam. Personal-Nr. eingefügt: =XVERWEIS(A9#;tbSchichten[Personalnr];tbSchichten[Name];"--";0;1)
3h) in der Zeile D9:AH9 habe ich dir 31 dynam. Array-Formeln eingefügt, wovon die erste in D9 lautet:
=INDEX(DP_Schichten;VERGLEICH($A9#;Jahresplan!$A$8:$A$55;0);VERGLEICH(D$7;Jahresplan!$E$5:$PK$5;0))
und die letzte in AH9 lautet: =INDEX(DP_Schichten;VERGLEICH($A9#;Jahresplan!$A$8:$A$55;0);VERGLEICH(AH$7;Jahresplan!$E$5:$PK$5;0))
wobei der Name "DP_Schichten" als =Jahresplan!$E$8:$OB$55 definiert ist.
Wie zu sehen ist, greifen diese Formeln über die dynam.Array-Liste der Personal-Nr. in Spalte A ab A9 auf die ausgewählte Schicht zu.
Zu Testzwecken habe ich ein paar Werte ins Arbeitsblatt "Jahresplan" eingefügt.

Damit obiges System funktioniert, müssen die Personal-Nr. in der Tabelle "tbSchichten" vollständig und mit eindeutiger Nummerierung ausgefüllt sein. Dabei darf es durchaus sein, dass es 2 verschiedene Personen gleichen Namens gibt aber mit unterschiedlicher Personal-Nr., jedoch nicht 2 Personal-Nr. für ein und dieselbe Person.

Modifizierte Datei liegt bei.

 

Erst mal vielen lieben Dank für die Hilfe. Funktioniert einwandfrei, auch wenn ich die Hälfte von den Formeln, welche Du gepostet hast überhaupt nicht verstanden habe *g*.

Das mit xverweis versteh ich und das es geschickter ist/war, die Tabelle so zu formatieren, wie Du das gemacht hast.

Mit den Array Formeln hab ich mich bisher noch garnicht auseinander gesetzt, also noch bömische Dörfer für mich. Da werd ich mir mal ein paar Tutorials zu anschauen, damit ich da besser durchsteige.

Könntest Du mir diese Formel vielleicht mal genauer erklären:

=INDEX(DP_Schichten;VERGLEICH($A9#;Jahresplan!$A$8:$A$55;0);VERGLEICH(AH$7;Jahresplan!$E$5:$PK$5;0))

 

Ich hab mal den Jahresplan um den Reiter Einzelnachweis ergänzt, der uns als Betriebsräten dabei helfen soll, die Arbeitszeiterfassung einzelner Mitarbeiter überprüfen zu können.

Ich will versuchen in der Spalte Test die Schicht bzw die Zeit aus dem Jahresplan zu übernehmen und dann, abhängig vom Schichten-Kürzel bei Beginn und Ende die jeweilige Anfangs- und Endzeit durch Excel eintragen lassen. Ich hab es versucht mit der =INDEX(DP_Schichten;VERGLEICH($A9#;Jahresplan!$A$8:$A$55;0);VERGLEICH(AH$7;Jahresplan!$E$5:$PK$5;0)) umzusetzen, aber leider funktioniert das nicht so, wie ich das gehofft habe. Im Leistungsnachweis packt er mir ja abhängig von der ausgewählten Schicht im Drop-Down Menü alle Einträge der Arbeitskollegen in den Monatsplan rein. Im Einzelnachweis hab ich aber nur immer einen Kollegen und die Darstellung der Schicht/Stunden würde in einer Spalte erfolgen.

Im Einzelnachweis hab ich zudem noch eine Spalte erstellt, die mir dort, sollte es sich bei dem Tag um einen Feiertag handelt, ein X in die Spalte reinpackt. Dann wollte ich durch eine Wenn-Abfrage Excel überprüfen lassen, ob sich in der Spalte Stunden eine 8 oder 12 befindet, und mir dann dementsprechend in der Spalte Feiertag ein X reinschreibt. Doch das hat mit der Formel =WENN(ODER(E10="8";E10="12");"X";"") nicht so ganz hingehauen.

 

Hallo,

Der Aufbau der INDEX()-Funktion lautet: =INDEX(Matrix; Zeile; [Spalte]):
Die INDEX()-Funktion bewirkt, dass man aus einem rechteckigen Zellenbereich (=Matrix, Bezug, Array) den Wert der Zelle, die sich in der Zeile Nr. und in der Spalte Nr. dieses Zellenbereiches befindet, abfragen kann.
Ich habe dir in der Zeile Aufbau der INDEX()-Funktion und im Zitatbereich die einander entsprechenden Formelteile mit gleichen Farben eingefärbt.

Der erste Formelteil (rot gefärbt) nennt den Zellbezug. In diesem Fall ist das der Name "DP_Schichten", der im Namensmanager folgendermaßen definiert ist: =Jahresplan!$E$8:$OB$55. Jede Zelle aus dem Zellbereich E8:OB55 des Arbeitsblattes "Jahresplan" kann abgefragt werden, sofern man nur in den beiden nächsten Parametern der Funktion ihre Zeilennummer und die Spaltennummer innerhalb dieses Bereiches angibt.

Der zweite Formelteil (blau gefärbt) nennt die Zeilennummer der Zelle innerhalb des Zellbereiches "Jahresplan!E8:OB55". Die Zeilennummer könnte einfach eine Zahl (zB. gleich 1 sein), wenn ich eine Zelle aus dem Bereich Jahresplan!E8:OB8 (also aus der 1. Zeile des Bereiches) abfragen möchte.
In diesem Fall ist es aber wieder eine Formel VERGLEICH($A9#;Jahresplan!$A$8:$A$55;0) , die diese Zeilennummer ermitteln soll. Sie hat wiederum 3 Parameter: der 1.Parameter ($A9#) enthält den Kriteriumswert, der 2.Parameter den Zellbereich (Jahresplan!A8:A55), in welchem der Kriteriumswert gesucht werden soll; der 3. Parameter (0) zeigt an, wie dieser Wert zu suchen ist (nämlich: ganz genau übereinstimmend).
Dabei handelt es sich bei $A9 um eine erste Personalnummer, die im Personalnummer-Bereich A8:A55 des Arbeitsblattes "Jahresplan" exakt gesucht werden soll, wobei die VERGLEICH-Funktion dann die Zeilennummer innerhalb dieses Bereiches ermittelt. Wenn zB. im Arbeitsblatt "Leistungsnachweis" die C-Schicht ausgewählt ist, steht zB. in A9 als PersonalNr. die 12 (für Filbert, Alex). Diese 12 wird in Jahresplan!A8:A55 gesucht und in Zelle Jahresplan!A26 gefunden, wobei diese Zelle in der 19. Zeile des Bereiches Jahresplan!A8:A55 steht. Genau diese 19 wird als Resultat zurückgeliefert von VERGLEICH(..) und diese 19 (=Zeile 19 innerhalb von DP_Schichten) wird in der INDEX-Funktion als 2.Parameter als Zeilen-Nr. benötigt).
Jetzt lautet aber der 1.Parameter in VERGLEICH(..) auf "$A9#" und darin besteht die Neuheit, die es seit Excel2019 / Excel2021 / Excel365 gibt: die dynamischen Arrays (vorher gab es nur statische Arrays).

Erklärung: Wenn du zB. nur zum Ausprobieren ins Arbeitsblatt "Leistungsnachweis" gehst, dort einen leeren Zellbereich aufsuchst zB. Zelle B33, dort die Formel =A9 eingibst, so erhältst du dort erwartungsgemäß nur den Inhalt von Zelle A9 angezeigt. Gibst du jedoch in diese Zelle B33 die Formel =A9# ein, so bekommst die gesamte PersonalNr.-Auflistung, die ab Zelle A9 vorhanden ist zusammen mit einer blauen Zellumrandung des dynamischen Array-Bereichs, obwohl nur in Zelle B33 die vorgenannte Formel enthalten ist. Ist jedoch in diesem blau umrandeten Zellbereich irgendwo ein Wert oder eine Formel enthalten, erhältst du in Zelle B33 den Fehler "#ÜBERLAUF!" angezeigt. Es muss also der Zellbereich, in den das dyn.Array "verschüttet wird" (wie Microsoft das ausdrückt), leer sein. Es befindet sich nur in der 1.Zelle des dyn.Array-Bereiches eine Formel, die auch für die restlichen Zellen dieses Bereiches gilt. -- Fortsetzung folgt unten (unter INDEX-Funktion)!

Der dritte Formelteil (grün gefärbt) nennt die Spalten-Nr. der Zelle innerhalb des Zellbereiches "Jahresplan!E8:OB55".
In diesem Fall ist es aber wieder eine Formel VERGLEICH(AH$7;Jahresplan!$E$5:$PK$5;0), die diese Spaltennummer ermitteln soll. Sie hat wiederum 3 Parameter: der 1.Parameter (AH$7) enthält den Kriteriumswert, der 2.Parameter den Zellbereich (Jahresplan!E5:PK5), in welchem der Kriteriumswert gesucht werden soll und wiederum exakt (3.Parameter =0) gefunden werden soll.
Dabei handelt es sich beim Wert in AH$7 um das Datum des letzten Tages eines Monats mit 31 Tagen. Hat der Monat weniger als 31 Tage, steht dort der Wert "" (eine leere Zeichenkette).
Wenn zB. im Arbeitsblatt "Leistungsnachweis" die C-Schicht ausgewählt ist und als Monat der Januar, steht zB. in A9 als PersonalNr. die 12 (für Filbert,Alex) und in AH$7 der 31.1.2022 (=44592 als fortlaufende Tageszahl für das Datum). Diese 44592 wird in Jahresplan!E5:PK5 gesucht und in Zelle Jahresplan!AI5 gefunden, wobei diese Zelle in der 31. Spalte des Bereiches Jahresplan!E5:PK5 steht. Genau diese 31 wird als Resultat zurückgeliefert von VERGLEICH(..) und diese 31 (=Spalte 31 innerhalb von DP_Schichten) wird in der INDEX-Funktion als 3. Parameter als Spalten-Nr. benötigt).

Die INDEX-Funktion hat jetzt alle 3 Parameter parat: Den Zellbereich "DP_Schichten" (=Jahresplan!E8:OB55), die Zeilennr. (zb. 19 innerhalb des Zellbereichs) und die Spaltennr. (zB. 31 innerhalb des Zellbereiches). Damit kann die INDEX-Funktion aus der Zelle Jahresplan!AI26 den Zellwert abfragen.
Erklärung: Dyn.Array: Da als 2. Parameter in der INDEX-Funktion nicht bloß eine Zeilennr. angegeben wurde, sondern ein dynam.Array an Zeilennummern von der VERGLEICH-Funktion zurückgeliefert wurde, weil in der VERGLEICH-Funktion als 1.Parameter ein dyn.Array als Kriteriumswert übergeben wurde, liefert auch die INDEX-Funktion nicht einen Einzelwert zurück, sondern dazu passend ein dynamisches Array an Werten.
Bleibt man beim obigen Zahlenbeispiel, werden die Werte des Zellbereich Jahresplan!AI26:AI32 zurückgeliefert (31.1.2022 der C-Schicht) und zwar in den Zellen Leistungsnachweis!AH9:AH15

 

Hallo,

ich habe im Arbeitsblatt "Feiertage" die Feiertagsberechnung formelmäßig ergänzt für die 3 Jahre 2022-2024

Im Arbeitsblatt "Einzelnachweis" habe ich die Formel in F10 korrigiert auf: =WENN(ODER(E10=8;E10=12);"X";"")

Ferner habe ich die Personal-Nr. Abfrage, ausgehend vom Namen, umgestellt:
=XVERWEIS(D6;tbSchichten[Name];tbSchichten[Personalnr];"--";0;1)
Ich möchte aber dazu unbedingt anmerken: Was tust du, wenn es 2 Personen gleichen Namens geben sollte?

Weiters habe ich die Formeln in der Spalte TEST korrigiert (Zelle L10 und folgende):
=INDEX(DP_Schichten;VERGLEICH(J$6;Jahresplan!$A$8:$A$55;0);VERGLEICH($B10;Jahresplan!$E$5:$PK$5;0))
wobei ich weiters die geschweiften Klammern (für die statischen Array-Formeln) entfernt habe.

Modifizierte Datei liegt bei.

 

Erst mal vielen Dank. Dachte eigentlich, dass ich die =INDEX(DP_Schichten;VERGLEICH(J$6;Jahresplan!$A$8:$A$55;0);VERGLEICH($B10;Jahresplan!$E$5:$PK$5;0)) auch so eingetragen hatte. Komisch.

Also würdest Du empfehlen, die Auswahl bei der Personal Nr. zu starten. Könnte die ja auch als Drop-Down Box anlegen, wie die Namen auch.

Was mir gerade aufgefallen ist. Ich hab mal testweise bei der A-Schicht im Jahresplan für den Monat Mai Schichten hinterlegt. Da die Schichtgruppen in 3 und die Kollegen aus dem Pool in mehreren unterschiedlichen Frühschichten arbeiten, habe ich im Reiter Daten die einzelnen Schichtzeiten aufgelistet. Wenn ich aber nun im Jahresplan beispielsweise F8 oder S8 oder F12 eintrage, wird mir das im Leistungsnachweis nicht mehr angezeigt.

 

Hallo,

das musst du selber wissen, ob einmal ein Fall vorkommen kann, dass 2 Personen den gleichen Namen haben. Auf alle Fälle musst du dann wissen, wie du sie dennoch zu unterscheiden gedenkst. Ich habe dir die Formel insofern geändert, dass sie dir beide Personal-Nr. untereinander anzeigt (blaue Formel).

Im Arbeitsblatt "Leistungsnachweis" habe ich einen zusätzlichen Zeilenbereich E12:AI12 mit Formeln eingefügt, der den Schicht-Code je Datum enthält.
Ferner habe ich die Spalte AK13# mit den Monatskranktagen und die Spalte AL13# mit den Resturlaubstagen mit Ende Monat eingefügt. Mit Ende Dezember wird es Probleme geben mit diesen beiden Spalten, denn dort hast du im Arbeitsblatt "Jahresplan" plötzlich wieder eine andere Spalteneinteilung.

Datei liegt bei.

 

Also dann im Jahresplan einfach die beiden anderen Spalten wieder weg machen? Muss ich dann noch was an den Formeln verändern?

Wie erreich ich das denn, das ich mir im Leistungsnachweis die Schichten einzelner Mitarbeiter senkrecht in einer Spalte darstellen lassen kann? Hab das bereits mit der Formel =INDEX(DP_Schichten;VERGLEICH(J$6;Jahresplan!$A$8:$A$55;0);VERGLEICH($B10;Jahresplan!$E$5:$PK$5;0)) versucht, klappt aber natürlich nicht ;-).

 

Hallo,

du meinst wahrscheinlich das Arbeitsblatt "Einzelnachweis".

Ich habe dir dort
a) die bestehenden Formeln für Monatsauswahl (Zelle D4:D5),
b) die bestehenden die Formeln für die Datumsangaben (A10:B40)
c) die Array-Formeln für Beginn und Ende (C10:D40):
=LET(kü;INDEX(DP_Schichten;VERGLEICH($J$6;DP_PersNr;0);VERGLEICH($B10;DP_Tage;0));
zl;XVERGLEICH(kü;tbZeitSchicht[Kürzel];0;1);
WENNFEHLER(INDEX(tbZeitSchicht;zl;{2.3});kü))
d) die Formeln für die Stunden (E10:E40): =WENN(ISTZAHL(C10);REST(D10-C10;1);"")
e) die Formeln für die Feiertagsspalte (F10:F40):=XVERWEIS($B10;tbFeiertage[Feiertag];tbFeiertage[Name];"--";0;-1)
f) die Formeln für die Urlaubsspalte (G10:G40): =WENN(C10="K";"K";"")
g) die Formeln für die Krankheitsspalte (H10:H40): =WENN(C10="K";"K";"")
bearbeitet bzw. neu eingefügt.

Die Array-Formeln für Beginn und Ende benutzen dafür die LET(..)-Funktion:
a) der Variablen "kü" wird das Kürzel, das im Bereich "DP_Schichten" gefunden wurde, zugewiesen;
b) dieser Kü-Wert wird in der strukt.Tabelle "tbZeitSchicht" gesucht und die ZeilenNr. innerhalb dieser Tabelle der Variablen "zl" zugewiesen und schließlich
c) mittels dieser Zl-Nummer aus der Spalte "Anfang" bzw. "Ende" die Uhrzeiten entnommen.
d) Konnte kein passender Kü-Wert in "tbZeitSchicht" gefunden werden, wird dieser einfach zurückgegeben von der LET()-Funktion.

Die restlichen Spalten wirst du sicher auch alleine schaffen.
Datei liegt bei.

 

Bitte nicht wundern. Ich musste mir einen neuen Account erstellen, da ich bei dem alten Account trotz zahlreicher Versuche einfach keine Bestätigungsmail zugeschickt bekomme und ohne diese auch weder Nachrichten, noch Beiträge verfassen bzw. beantworten kann.

Zurück zum Dienstplan. Ich hab diesen mal um ein paar neue Feature erweitert, die es vor allem unseren Schichtführern leicht machen sollen, Löcher (Fehlstunden) im Plan sofort zu erkennen.

Den unteren Bereich habe ich in Wochentage und Wochenenden unterteilt:

Der Soll-Bereich:
Pool: mindestens 2 Kollegen
Frühschicht: 8
Mittagsschicht: 8
Spätschicht (in unserem Fall Nachtschicht): 7

Am Wochenende werden nur 12 Stunden Schichten, jeweils Früh 12 und Abends 12, gearbeitet.


Ich hab mich mal am Wochenende probiert und mit der Funktion WOCHENTAG experimentiert:
=WENN(ODER(WOCHENTAG(E$4;2)=6;WOCHENTAG(E$4;2)=7);"12";"8")

Ich dachte erst, ich könnte es auch so umsetzen:
=Wenn(ODER(WOCHENTAG(E$4;2)>6;"12","8")

Hat aber natürlich wieder mal nicht geklappt.

Der Ist-Bereich:
Dieser Bereich sollte einfach mit dem oberen abgeglichen werden und alle Diskrepanzen mit roter Hintergrundfarbe/weißer Vordergrundfarbe darstellen.

Ich dachte mir, ich nutze die =ZÄHLENWENN Funktion, um einfach die entsprechenden Schichten in der Spalte E7:E55 zu zählen und mir die Anzahl in der Zelle wiederzugeben. Bei Krank, Urlaub & Sonderurlaub hat das soweit ganz gut funktioniert: =ZÄHLENWENN(E7:E55;"K")

Nach dem gleichen Schema sollte ich ja eigentlich in der Lage sein, die Schichten F8, M8 und S8 zählen zu lassen. Doch wie schaut es bei den Pool-Schichten aus. Hiervon gibt es ja nun ne ganze Menge zur Auswahl:
1F8
2F8
3F8
4F8
5F8
6F8
7F8
8F8
9F8
10F8

Und gibt es auch eine Möglichkeit, die "freien Zellen" zu zählen, damit der Schichtführer weiß, dass Kollegen frei haben und möglicherweise einspringen könnten, wenn Not am Mann ist?

Du hattest das mit der Spalte Dezember im Jahresplan angesprochen und das es zu Problemen im Leistungsnachweis kommt. Wie könnte ich das Problem lösen? Einfach die beiden Spalten OC/OD im Jahresplan genauso formatieren wie die Spalten im Vormonat? Kann ich dann einfach ne weitere Spalte vor OE einfügen, um mir die gesamten Krankentage im ganzen Jahr anzeigen zu lassen. Ich weiß jetzt leider nicht auswendig, wie weit sich Dein Suchbereich erstreckt.

Kann ich in der Tabelle tbZeitSchicht einfach weitere Schichten hinzufügen, also die Tabelle um neue Einträge erweitern?

Ist es möglich, im Jahresplan oben links, neben der Auswahl für das Jahr, noch ein Drop-Down Menü hinzuzufügen mit den einzelnen Monat, welches dann den entsprechenden Monat nach vorne holt, da sich der Jahresplan ja horizontal nach rechts erstreckt und man sich so das Scrollen sparen würde?

Bin wie immer über jede Hilfe und jedes Feedback dankbar.

Grüße
Heiko

 

Hallo,

ja natürlich, dazu ist die strukt. Tabelle "tbZeitSchicht" ja da. Einfach in der nächsten freien Zelle in Spalte A (also A34) ein weiteres Kürzel eingeben. Damit wird auch automatisch Zelle B34 und C34 angefügt und zwar als Teil der Tabelle "tbZeitSchicht".

a) Es wird dir ja schon aufgefallen sein, dass für die Monate Jan.-Nov. der Tabellen-Spaltenaufbau folgender ist: Tag1,Tag2, bis Tag30 bzw. Tag31, gefolgt von 1 Spalte "Krank" und 1 Spalte "Urlaub".
b) Im Dez. ist der Tabellen-Spaltenaufbau folgender: Tag1, Tag2, bis Tag 31, gefolgt von 2 Spalten "Krank" und 2 Spalten "Urlaub".

Lösung: Spaltenaufbau aller Monate gleich, also Dez. so wie in Punkt a). Darauf sind die Array-Formeln im Arbeitsblatt "Leistungsnachweis" in den Zellen AK13 und AL13 abgestimmt.

Die letzte Formel kann so nicht funktionieren, sie müsste lauten:
=WENN(WOCHENTAG(E$4;2)>5; 12; 8)
Ich nehme an, dass "12" und "8" Zahlen sind und keine Texte, die ODER()-Funktion ist überflüssig (bzw. was soll sie bewirken?), außerdem fehlt eine schließende Klammer. Die WOCHENTAG(Datum;2)-Funktion liefert, falls Datum ein Montag ist, die Zahl 1, und, falls Datum ein Sonntag ist, die Zahl 7, d.h. für Samstag die Zahl 6.

Die Schicht F8 kannst du wie folgt zählen: =ZÄHLENWENN(E7:E55;"F8")
Die einzelne Pool-Schicht (zB. 3F8) kann gezählt werden: =ZÄHLENWENN(E7:E55;"3F8")
Wenn du die Poolschichten 1F8 bis 10F8 in einer Summe haben willst, funktioniert das so:
=ZÄHLENWENN(E7:E55;"*F8")-ZÄHLENWENN(E7:E55;"F8")
Zu achten ist dabei auf das Sternchen im ersten "*F8": Es werden mit dem ersten ZÄHLENWENN(..) alle Texte gezählt, die mit F8 enden, deshalb ist mit dem zweiten ZÄHLENWENN(...) die Anzahl der F8 zu subtrahieren.