Microsoft Office

Excel: Flexible Dienstpläne erstellen - so geht's

30.3.2022 von Susanne Kowalski

Dienst- und Schichtpläne sollten grundsätzlich übersichtlich, komfortabel und flexibel sein. In Pandemiezeiten ist es besonders wichtig, dass sie sich mit wenig Aufwand und arbeitsgesetzkonform an neue Situationen anpassen lassen. Wir zeigen Ihnen wie das geht.

ca. 4:35 Min
Ratgeber
VG Wort Pixel
Arbeitsplatz
© Dmitry Melnikov / shutterstock.com
© Dmitry Melnikov / shutterstock.com

Dienst- und Schichtpläne gibt es für die unterschiedlichsten Einsatzbereiche, zum Beispiel für die Besetzung der Supermarktkasse oder den Dienst im Pflegeheim. Auch Rahmenbedingungen wie Beschäftigtenzahlen, Zeitfenster von Schichten, Pausenzeiten variieren.

Manchmal müssen zudem Mitarbeitende und Ressourcen aufeinander abgestimmt werden, zum Beispiel Physiotherapeuten und Raumangebot. Obwohl es offenbar „den“ Dienstplan nicht gibt, sind die Anforderungen weitgehend identisch. Bei der Umsetzung hilft der nachfolgende Werkzeugkasten.

Welche Anforderungen gibt es an einem Dienstplan?

Dienst- und Schichtpläne sollen unter anderem guten Überblick verschaffen, bedienerfreulich sein und Teambildung ermöglichen. Praktisch sind Add-ons, die Arbeitszeiten ausweisen, das Einhalten von Vorschriften gewährleisten, Abwesenheitsgründe berücksichtigen, Wochendeinsätze und Homeoffice gerecht verteilen.

Das Grundgerüst wird durch einen geschickten Aufbau und die notwendigen Informationen gelegt.

  • Idealerweise werden die Mitarbeiternamen untereinander in einem Tabellenbereich erfasst, der den eigentlichen Aufbau des Dienstplans nicht beeinträchtigt.
  • Zeiten können sowohl vertikal als auch horizontal ausgerichtet werden. Tipp: Um unter einem Datum mehrere Namen zu verwalten, ist das Verbinden von Zellen unter Start/Ausrichten/Verbinden und zentrieren hilfreich.
Datenmengen-shutterstock

Massendaten in Excel

10 Tipps um große Datenmengen in Excel zu verwalten

Der Umgang mit Massendaten bereitet Anwendern von Tabellenkalkulationsprogrammen immer wieder Probleme. Wir schaffen Abhilfe.

Wie schaut eine Intelligente Auswahl aus?

Das Zuweisen der Mitarbeitenden zu Zeiten und Schichten kann man mit Zell-Dropdowns vereinfachen.

Sollen nur die Personen angezeigt werden, die tatsächlich verfügbar sind, muss Excel analysieren, wer fehlt. Dazu lässt sich automatisch eine reduzierte Mitarbeiterliste generieren, die später als Basis für das Zell-Dropdown dient.

Die eigentliche Verfügbarkeitsprüfung erfolgt durch eine Verknüpfung der Funktionen WENN und SUMMENPRODUKT .

Excel Diensplan Zell-Dropdown Screenshot
Bedienerfreundlich: Aus Angaben zu Abwesenheitszeiten lassen sich mit Hilfe von Formeln Verfügbarkeitslisten für Zell-Dropdowns generieren.
© PC Magazin

Die Ergebnisse von SUMMENPRODUKT werden in WENN genutzt, um die Verfügbarkeit in Form des Namens situationsgerecht ein- und auszublenden.

Für die Prüfung des ersten Mitarbeiters am ersten Tag der Woche lautet die Formel im gezeigten Beispiel in
C3=WENN(SUMMENPRODUKT (($B$10:$B$12<=B$16)*($C$10:$C$12>= B$16));"";$A3.
In den Bereichen B10:B12 (Beginn) und C10:C12 (Ende) befinden sich die auszuwertenden Fehlzeitenangaben, in B16 das zugehörige Datum. Die Formeln in den nachfolgenden Zeilen und nebenstehenden Spalten sind anzupassen.

Microsoft Office - Excel

Microsoft Office

Excel: Wenn-Dann-Funktion - so funktioniert sie!

Mit der Funktion WENN() lassen sich in Excel komplexere Aufgabenstellungen elegant lösen. Unsere Anleitung für Wenn-Dann-Funktionen hilft weiter!

Was ist ein Zell-Dropdown?

Damit die Verfügbarkeitslisten im zugehörigen Zell-Dropdown erscheinen, markieren Sie im Dienstplan den Bereich, in dem die Auswahl erfolgen soll, und wählen Daten/Datenüberprüfung/Einstellungen.

Unter Zulassen entscheiden Sie sich für Liste, und unter Quelle geben Sie die zum Datum gehörende Verfügbarkeitsliste (z. B. C3:C6) an.
Mit OK bestätigen, fertig.

Möchten Sie die Verfügbarkeitsberechnungen nicht dauerhaft anzeigen, weisen Sie den Einträgen die Schriftfarbe Weiß zu.

Das Einrichten eines intelligenten Dienstplans ist zwar arbeitsintensiv, sorgt aber im weiteren Einsatz für Flexibilität und Komfort. Wenn Sie später Namen oder Datumswerte überschreiben, brauchen Sie die Formeln nicht anzupassen.

Excel: Flexible Dienstpläne erstellen -
Automatische Anzeige verfügbarer Mitarbeitender: Die Datenüberprüfung greift auf die reduzierten Listen zu.
© PC Magazin

Wie werden Arbeitszeiten und Pausen eingerichtet?

Eines gleich vorweg: Beim Rechnen mit Zeit- und Datumswerten gibt es einige Besonderheiten, die je nach vorliegender Datenbasis unter Umständen bei Berechnungen auftreten können, sich jedoch schnell beheben lassen.

Mit Hilfe von ZÄHLENWENN können Sie anhand von Namen automatisch die täglichen bzw. wöchentlichen Arbeitszeiten ermitteln.

Dazu wird der Bereich angegeben, in dem Excel nach dem Namen suchen soll, sowie der Name des gesuchten Mitarbeitenden (z. B. =ZÄHLENWENN(B4:C7;"Muster").

Excel: Flexible Dienstpläne erstellen - Rechtssicherheit ZÄHLENWENN Screenshot
Für mehr Rechtssicherheit: ZÄHLENWENN können Sie unter anderem in Kombination mit Warnhinweisen im Schichtbetrieb einsetzen.
© PC Magazin

Die Namen müssen in Anführungszeichen gesetzt werden. Die Arbeitszeit lässt sich auch aus Schichten ermitteln. Hierzu müssen Sie lediglich das Ergebnis von ZÄHLENWENN mit der Anzahl Stunden pro Schicht multiplizieren (=ZÄHLENWENN(Bereichsangabe; "Name")*4).

Die WENN-Funktion macht bei Bedarf im Team mit ZÄHLENWENN auf das Überschreiten von gesetzlich vorgeschriebenen Höchstarbeits- und -Pausenzeiten aufmerksam.

Die Vorschriften zu Pausenregelungen können Sie analog kontrollieren (z. B. =WENN(ZÄHLEN WENN(Bereichsangabe;"Name")>9;45; WENN(ZÄHLENWENN(Bereichsangabe;"Name")>6;30;0))).

Tipp: ZÄHLENWENN und darüber hinaus ZÄHLENWENNS eignen sich zudem, um bei Bedarf die gerechte Verteilung von Wochenend- und Homeofficezeiten zu gewährleisten.

Excel

Microsoft Office

Excel: Zellen automatisch ausfüllen - 8 praktische Tipps

Excel kann Ihnen viel Arbeit ersparen. Wir geben Tipps, wie Sie Felder automatisch ausfüllen lassen, Kalender erstellen und mehr.

Wie richte ich eine Teambildungs-Funktion ein?

Wenn lediglich bestimmte Mitarbeitende Schichten tauschen sollen, ist eine unkomplizierte Möglichkeit, die Namen mit einem Kürzel zu verknüpfen (zum Beispiel T1 für Team 1). Das Kürzel wird wahlweise an den Namen angehängt (z. B. Muster_T1) oder vorangestellt (T1_Muster).

Um Fehler bei der Einteilung zu vermeiden, wird eine Prüfroutine mit LINKS (Kürzel vorn) oder RECHTS (angehängt) und WENN eingerichtet. Sobald unterschiedliche Teammitglieder in einer Schicht eingeteilt werden, erscheint eine Fehlermeldung (Beispielformel zur Abb. oben: =WENN(RECHTS(B9;2)=RECHTS(C9;2); "";"Einteilungsfehler").

Excel: Flexible Dienstpläne erstellen - Fehlerhafte Einteilung von Mitarbeitenden Screenshot
Mit RECHTS, LINKS und dem Allrounder WENN können Sie auf eine fehlerhafte Einteilung von Mitarbeitenden hinweisen.
© PC Magazin

Mitarbeitende über Bedingte Formatierung farblich kennzeichnen

Farbe sorgt in Dienstplänen für eine bessere Übersicht. Mit Hilfe der Bedingten Formatierung können Sie den Namen der Mitarbeitenden automatisch den gewünschten Zellhintergrund zuzuweisen.

Excel: Flexible Dienstpläne erstellen - Bedingte Formatierung Screenshot
Für mehr Übersicht: Mit der Bedingten Formatierung wird den Namen der Mitarbeitenden automatisch ein Zellhintergrund zugewiesen.
© PC Magazin
  1. Markieren Sie den Bereich, in dem die Namen der Mitarbeitenden erfasst bzw. ausgewählt werden. Klicken Sie im Register Start in der Gruppe Formatvorlagen nacheinander auf die Schaltfläche Bedingte Formatierung/Regeln zum Hervorheben von Zellen/Textinhalt.
  2. Im folgenden Dialog erfassen Sie den ersten Namen und wählen aus dem Feld daneben eine Farbe aus oder weisen den Zellhintergrund über Benutzerdefiniertes Format/Ausfüllen zu.
    Gut zu wissen: Die letztgenannte Alternative bietet deutlich mehr Gestaltungsoptionen. Bestätigen Sie mit OK. Heben Sie die Markierung noch nicht auf.
  3. Achtung: Für das Zuweisen weiterer Formate ändert sich die Vorgehensweise. Wählen Sie Bedingte Formatierung/Neue Regel. Im Fenster Neue Formatierungsregel entscheiden Sie sich unter Regeltyp auswählen für Nur Zellen formatieren, die enthalten.
  4. Im Bereich Regelbeschreibung bearbeiten setzen Sie das zweite Auswahlfeld auf gleich. Daraufhin wird der Dialog angepasst. Tragen Sie in das rechte Eingabefeld den Namen des gewünschten Mitarbeitenden ein, und weisen Sie dieser Person über Formatieren/Ausfüllen einen Zellhintergrund in einer Farbe Ihrer Wahl zu.
  5. Nachdem Sie die Einstellungen bestätigt haben, können Sie über Bedingte Formatierung/Neue Regel weiteren Namen Farben zuordnen.

Tipp: Wenn Sie das Ganze noch mit einem Zell-Dropdown koppeln, wird das Erstellen von Dienstplänen noch einfacher.

Shutterstock Frau PC Diagramme excel

Office

Excel-Formeln meistern: Tipps für Berechnungen, Pläne und…

Wir zeigen Ihnen häufig benötigte Excel-Funktionen und -For­meln anhand von Beispielen. Mit diesen Tipps setzen Sie Excel gezielter ein.

Häufige Probleme beim Rechnen mit Zeitwerten

Was das Rechnen mit Zeitwerten anbelangt, kann es trotz korrekter Formeln zu Fehlern kommen. Die häufigsten Probleme und ihre Lösungen haben wir in der Tabelle für Sie zusammengefasst.

Problem: Raute statt Ergebnis

Vollbildansicht
Auswirkung Lösung
Ausweis negativer Zeitsalden im Zeitformat Beispiel: Minusstunden (statt 8 wurden nur 7 Stunden) gearbeitet Unter Datei/Optionen/Erweitert im Bereich Beim Berechnen dieser Arbeitsmappe: die Checkbox 1904-Datumswerte verwenden abhaken.

Problem: Ausweis falscher Ergebnisse

Vollbildansicht
Auswirkung Lösung
Überschreiten der 24-Stunden-Grenze: Excel interpretiert Stunden standardmäßig als Anteil von Tagen (1/24) und kennt maximal 24 Stunden. Beispiel: Ermitteln der Wochenarbeitszeit im Stundenformat (bei einer täglichen Arbeitszeit von 8 Stunden werden für 5 Arbeitstage statt der korrekten Zahl von 40 Stunden lediglich 16 Stunden angezeigt). Im Kontextmenü Zellen formatieren/Register Zahlen aufrufen, dort den Eintrag Benutzerdefiniert auswählen, in das Eingabefeld das Format [h]:mm eingeben und mit OK bestätigen.

Problem: Dezimalzahl statt Uhrzeit

Vollbildansicht
Auswirkung Lösung
Rechnen mit Uhrzeiten Beispiel: Bilden von Summen oder Salden Zeitformat unter Zellen formatieren/Zahlen/Uhrzeit einstellen oder Format mit Pinsel aus der Gruppe Zwischenablage übertragen

Problem: Umrechnen von Zeitwerten

Vollbildansicht
Auswirkung Lösung
Umrechnen von Zeitwerten in Dezimalzahlen Beispiel: Zeitfenster von 00:30 entspricht 0,5 Stunden, Excel weist jedoch den Wert 0,0208 (gerundet) aus von Excel ausgewiesenen Dezimalwert mit 24 multiplizieren

Problem: Umrechnen von Dezimalwerten

Vollbildansicht
Auswirkung Lösung
Umrechnen von Dezimalzahlen in Zeitwerte Beispiel: 0,75 Stunden (00:45) ergeben im Zeitformat 18:00 Stunden von Excel ausgewiesenen Dezimalwert durch 24 dividieren

Einhalten gesetzlicher Arbeitszeitregelungen automatisch überprüfen

Arbeitgeber sind an das Arbeitszeitgesetz gebunden. Das sollte bereits im Rahmen der Dienstplanung berücksichtigt werden. Die wichtigsten Vorgaben und deren Umsetzung haben wir für Sie nachfolgend zusammengefasst. Beachten Sie, dass lediglich die grundlegenden Vorschriften, jedoch keine Ausnahmen berücksichtigt werden.

Hier können Sie unsere Tabelle mit Bestimmungen und der Umsetztung in Excel herunterladen.

Microsoft Excel

Datenverarbeitung mit Excel

Excel als Datenbank: 4 Tipps für mehr Performance

Viele Anwender missbrauchen Excel als Datenbank - aus Bequemlichkeit. Wenn die Datei jedoch immer größer wird, fällt die Zeitersparnis weg.

Mehr lesen

Chronologische Liste und Netflix-Links

Marvel-Filme- und -Serien: Das ist die richtige Reihenfolge

Neuerscheinungen in der Übersicht

Netflix: Neue Filme und Serien

Vorschau auf Film- und Serien-Highlights

Amazon Prime Video: Neuheiten

Weiter zur Startseite  

Mehr zum Thema

Excel Summe Filtern

Office-Tipp

Excel: Teilergebnis-Funktion bei Summen mit Autofiltern…

Mit der Teilergebnis-Funktion in Excel rechnen Sie auch in gefilterten Listen korrekt. Unser Tipp zeigt, wie es geht.

Excel: So erstellen Sie einen Stundenplan

Anleitung

Excel: So erstellen Sie einen Stundenplan

Stundenpläne, Tages- und Wochenplaner kann man immer wieder sehr gut gebrauchen. Mit Excel können Sie sich diese Planungshilfen sehr schnell selbst…

Microsoft Office: Texte oder Tabellen vergleichen - Synchroner Bildlauf

Office Tipps & Tricks

Microsoft Office: Texte oder Tabellen vergleichen - so…

Gelegentlich möchte man zwei Texte oder Tabellen miteinander vergleichen. Das geht mit Word und Excel sehr einfach von der Hand. Wir zeigen Ihnen wie.

Shutterstock Frau PC Diagramme excel

Microsoft Office

Excel: Funktionen zu Druckbereich, Datenimport & Co. -…

Vieles lässt sich in Excel intuitiv lösen. Für einige Probleme müssen die Anwender aber tief in die Trickkiste greifen und Funktionen in der richtigen…

Datenmengen-shutterstock

Massendaten in Excel

10 Tipps um große Datenmengen in Excel zu verwalten

Der Umgang mit Massendaten bereitet Anwendern von Tabellenkalkulationsprogrammen immer wieder Probleme. Wir schaffen Abhilfe.