Office: Jahresdienstplan - Monatsplan - Einzelplan - Wverweis Problem

So ich hab jetzt mal im Jahresplan im Dezember die Spalten umgewandelt und im Namens-Manager den Bereich von DP_Schichten abgeändert in =Jahresplan!$E$7:$OD$55. Somit sind schon mal die Fehler ausgemerzt. Yay, Erfolgserlebnis ;-)!

Ich hab gesehen, dass die folgende Formel genutzt wird, um sich die Anzahl der Krank und Urlaubstage anzeigen zu lassen:
=INDEX(FILTER(DP_Schichten;Jahresplan!$E$1:$OB$1="Krank");VERGLEICH($A13#;DP_PersNr;0);MONAT($AF$6))

Könnte ich das irgendwie ummodulieren, damit ich mir damit auch die Anzahl von SU (Sonderurlaub) anzeigen lassen kann? Im Moment nutze ich noch =WENNFEHLER(ZÄHLENWENN(E13:AI13;"SU");""). Diese Formel nutzt aber die volle Spaltenhöhe aus, da sich die Anzahl der Schichtarbeiter von denen aus dem Pool unterscheidet. Dementsprechend gibt mir Excel in den unteren Zellen dann jeweils eine 0 wieder.

Fragen zur bedingten Formatierung:

Im unteren Bereich des Jahresplans sollen ja die Löcher/fehlenden Kollegen durch eine bedingte Formatierung dargestellt werden.
Abhängig vom Wochentag sind mal mehr, mal weniger oder gar kein Kollegen eingeplant.
Beispielsweise gibt es am Wochenende keine Pool-Kollegen, sonder nur jeweils die Kollegen der 12 Stunden Frühschicht (8 Mann) und die Kollegen der 12 Nachtschicht (7 Mann). Im Ist-Bereich lass ich mir von Excel die Anzahl der eingetragenen Schichten anzeigen. Wenn mir Excel jetzt für Samstag für den Pool 0 anzeigt, wäre das jetzt kein Loch. Also war meine Überlegung:

wenn der ausgewählte Tag ein Wochenende ist - und wenn die Anzahl der Pool Stunden = 0 ist - kein Loch.

In einer normalen Zellen würde ich wahrscheinlich =wenn(wochentag(E$4;2)>5);E$67=0) schreiben, aber so einfach in das Feld für ne Formal bei der bedingter Formatierung reinkopieren, geht dann wohl nicht.

Frage zum Einzelnachweis:

Im Einzelnachweis gibt es eine Spalte für den Feiertag. Gerne würde ich mir hier anstelle des Namens des Feiertages einfach ein "X" anzeigen lassen, vorausgesetzt, der Kollege hat an diesem Tage auch gearbeitet.

Meine Überlegung war nun, eine Hilfsspalte zu erstellen, wo ich die gleiche Abfrage tätige wie in der eigentlichen Spalte Feiertag =XVERWEIS($B10;tbFeiertage[Feiertag];tbFeiertage[Name];"--";0;-1), mir dann aber ein "X" darstellen lasse, sprich statt tbFeiertage[Name] nutze ich tbFeiertage[Gesetzlich]. Wenn ich nun in der Spalte "Beginn" eine Zahl stehen habe und in der Hilfsspalte ist ein Feiertag hinterlegt, dann soll in der eigentlichen Spalte "Feiertag" das X stehen.

Anfangen würde ich wie folgt:

=WENN(UND(P10="X"), (C10=irgendeine Zahl?), F10="X";""). Wie kann ich nun Excel sagen, dass in der Zelle nur eine Zahl stehen braucht als zweiter Teil der Wenn-Funktion?

Frage zur Anzahl der leeren Zellen:

Hab mal ein bißchen gegoogelt und eine Formel gefunden, die mir die Anzahl der leeren Zellen ausgibt, was für unseren Jahresplan sehr nützlich sein kann, da der Schichtführer gleich sieht, wieviele Kollegen frei haben und vielleicht arbeiten wollen/einspringen können etc.

=ANZAHLLEEREZELLEN(IC44:IC55) klappt wunderbar, wenn man zusammenhängende Zellen auswählt. Wie schaut es aber aus, wenn ich mehrere Bereiche habe, die zum Beispiel durch farbige Untergliederungen von einander visuell abgetrennt werden. Excel würde mir hier ja in diesem Fall diese Zellen auch mit aufzählen, oder?

Frage zur Versions-Nummer:

Hier zu Hause auf meinem Heimrechner kann ich ohne Probleme mit dem Excel Plan arbeiten. Auf der Arbeit jedoch habe ich letztes Mal festgestellt, dass ich, vor allem im Reiter "Leistungsnachweis" einige "#Name?" Fehler erhalte. Die Excel Version hier ist Microsoft Office Standard 2019. Nun versuche ich natürlich, unsere Excel-Version durch die Geschäftsleitung aktualisieren zu lassen.

 

Hallo,

Wieso sollen da die Formeln geändert werden? Es genügt ja doch, einfach das Zahlenformat im Zellbereich Leistungsnachweis!AM13:AM24 entsprechend zu ändern: "Standard;;"
Dabei sind die beiden Strichpunkte wichtig: Das Format "Standard" gilt hier für positive Zahlen, das leere Format zwischen den beiden Strichpunkten gilt für negative Zahlen (die in diesem Fall ausgeblendet würden) und das leere Format nach dem zweiten Strichpunkt gilt für 0-Werte (die in diesem Fall auch ausgeblendet werden, was du ja haben möchtest).

Diese Formel ist prinzipiell falsch. Sie müsste wahrscheinlich lauten:
=WENN(UND(WOCHTENTAG(E$4;2)>5; E$67=0); "kein Loch"; "sonst Loch")
oder
=WENN(WOCHTENTAG(E$4;2)>5; WENN(E$67=0; "kein Loch"; "sonst Loch"); "Montag-Freitag")
wobei du die Texte "kein Loch" bzw. "sonst Loch" bzw. "Montag-Freitag" durch deine entsprechenden Formeln ersetzen musst.
Was die bedingte Formatierung betrifft: Diese darfst du natürlich nicht löschen - aber es ist ja ohnehin möglich, nur die Formel(n) zu kopieren (durch entsprechende Auswahl im Kopierbefehl).

Warum so kompliziert? Die Hilfsspalte ist unter diesen Voraussetzungen nicht nötig. Die Formel zB. in Zelle F10 lautet:
=WENN(C10;XVERWEIS($B10;tbFeiertage[Feiertag];tbFeiertage[Gesetzlich];"--";0;-1);"--")
Diese in die Zellen unterhalb kopieren.

Worin besteht da das Problem?
Angenommen es sind 2 nicht zusammenhängende Zellbereiche vorhanden, zB. IC35:IC41 und IC44:IC55
Die Formel lautet: =ANZAHLLEEREZELLEN(IC35:IC41)+ANZAHLLEEREZELLEN(IC44:IC55)

Dazu hatte ich dich in meinem Post#4 vom 27.5. gefragt, welche Version du hast - und was der Hintergrund zu dieser meiner Frage war - und du gabst zur Antwort: Excel2021.
Entsprechend habe ich Funktionen wie zB. "FILTER", "LET", "XVERWEIS", "XVERGLEICH" verwendet. Diese gibt es aber in Excel2019 nicht und entsprechend erscheint dort als Fehlerhinweis "#NAME?" - hatte ich dir auch geschrieben.

Modifizierte Datei liegt bei.

 

Fragen zur Bedingten Formatierung:

Ich komme bei der Bedingten Formatierung irgendwie nicht weiter. Im Jahresplan im unteren Teil, der mir anzeigt, ob Schichten aufgefüllt werden müssen oder nicht, habe ich ja jeweils den Soll-Bereich (wie die Schichten aufgestellt sein müssen abhängig vom Wochentag) und dem Ist-Bereich (wie es derzeit mit der Personalstärke tatsächlich ausschaut).

Nehmen wir den Pool. Montag-Freitag sollten 2 Mann zur Verfügung stehen, am Wochenende nicht. Durch die =zählenwenn Funktion lass ich mir ja die Pool-Schichten und die normalen Frühschichten zusammenzählen. Wenn sich jetzt am Wochenende 0 ergibt, möchte ich, das Excel die Zelle einfach komplett weiß macht (weiße Schrift, weißer Hintergrund). Handelt es sich um einen Wochentag (Mo-Fr) und es ergeben sich 2, soll es schwarz dargestellt werden (schwarze Schrift, schwarzer Hintergrund), alles unter 2 in rot.

Müssten jetzt jeden Tag 2 Mann eingeteilt sein, wäre die Bedingte Formatierung für mich kein Problem, da ich einfach "Regeln zum Hervorheben von Zellen" nützen würden. Jedoch kommt hier noch eine =Wenn Formel ins Spiel und ich bin komplett verwirrt, wie ich das realisieren soll ;-).

Fragen zur Darstellung im Leistungsnachweis:

Beim Leistungsnachweis müssen unsere Schichtleiter ja nur ihre Gruppen auswählen im Drop-Down Menü und Excel zieht die entsprechenden Informationen aus dem Jahresplan raus und packt diese in die Tabelle. Jede feste Schicht verfügt über 7 Mitarbeiter, die Kollegen aus dem Pool sind aber mehr. Kann man es in Excel so darstellen lassen, dass "leere Zellen" einfach ausgeblendet werden?

Fragen zur Wiedergabe von 20 Minuten im Übergabe im Einzelnachweis:

Die Kollegen der festen Schichten erhalten pro Arbeitstag jeweils 00:20 Minuten Übergabe, welche ich gerne im Reiter Einzelnachweis in der Spalte Übergabe wiedergeben möchte. In der Tabelle tbSchichten im Reiter Daten habe ich hierfür eine neue Spalte angelegt mit Übergabe. Ich hab versucht, das mit einem Sverweis zu lösen und mir das Ergebnis in einer Hilfsspalte M anzeigen zu lassen, was wahrscheinlich wieder zu umständlich ist, beim Ausprobieren aber auch nicht funktioniert hat. =SVERWEIS(J6;tbSchichten;6;0). Als Ergebnis erhalte ich #NV.

Fragen zur Statistik:

Da die Kollegen vom Pool keine festen Schichten haben, werden sie von den Schichtführern dort eingeteilt, wo Not am Mann ist. Dies kann eine Nachtschicht, ein Feiertag oder aber auch ein Sonntag sein. Dementsprechend ändert sich natürlich auch die Vergabe der Zuschläge. Um diese "Schichtverteilung" nun so gerecht wie möglich zu gestalten, würden wir gerne in unserem Masterplan eine Statistik bzw. einen Reiter Statistik integrieren, der mir in einem ausgewählten Zeitraum (Monat x - Monat X) die einzelnen Tage (Frühschicht, Feiertag, Samstag etc.) zusammenzählt, welche die Kollegen gearbeitet haben. Ich habe hierfür im Reiter Statistik eine Tabelle mit den einzelnen Kollegen erstellt, sowie die einzelnen Monate mit den jeweiligen Schichten. Wie kann ich nun Excel sagen, dass er mir, anders als im Leistungsnachweis, nun beim ausgewählten Mitarbeiter im Jahresplan nachschaut (Jahr kann man im Reiter Statistik ebenfalls auswählen) und dort im vorgebenen Monat alle entsprechenden Schichten zusammenzählt und die Anzahl wiedergibt.

Die Ergebnisse aus dieser Statistik-Tabelle würden wir dann gerne im Einzelnachweis wiedergeben können, damit auch der Betriebsrat eine Auge darauf werfen kann, dass alles gerecht verteilt wird.

Freu mich auf das Feedback und die Antworten.

Grüße
Heiko

 

Laut Aussage der IT Abteilung unserer Firma können wir die Excel Version auf unserem Rechner nicht aktualisieren, da dies im nächsten halben Jahr für alle Rechner in unserem Firmennetzwerk erfolgt. Nur können wir leider nicht so lange warten.

Natürlich jetzt echt blöd für unseren Dienstplan, da dieser, wie ja auch gewollt und beabsichtigt war, mit Formeln und Funktionen der Excel Version 2021 arbeitet.

 

Wenn mir jemand dabei helfen könnte, die Formeln auf Excel 2019 umzuschreiben, wäre ich echt sehr dankbar.

Grüße
Heiko

 

ein halbes Jahr ist nicht so lang wie es sich anhört. Was habt ihr denn bisher gemacht? Und das geht wohl nicht noch die paar Tage?
Das Problem hier ist das du Arbeit, Zeit, und nicht nur Deine, in ein Projekt steckst was quasi eine Totgeburt ist. Lass es wie es ist und nutze die Datei auf einem Rechner mit dem passenden Programm. Wenn es so dringend und kriegsentscheidend ist, läßt sich auch eine Lösung finden bis die Software für Alle zur Verfügung steht. Zudem sparst du dir dann die Daten der vergangenen Monate in die neue Datei nachzutragen.