Stundenplan in Excel erstellen: Konflikte finden und die Auslastung der Lehrkräfte prüfen

Wer schon einmal einen Stundenplan von Hand gebaut hat, kennt das Muster: Montagmorgen steht dieselbe Lehrkraft in zwei Räumen, oder zwei Gruppen sitzen im selben Raum. Die Überschneidung fällt erst auf, wenn es zu spät ist, und jede Korrektur zieht neue Konflikte nach sich.

Excel kann dabei helfen, wenn Sie den Plan nicht als Raster, sondern als Liste aufbauen. Aus einer Liste lassen sich Konflikte zählen, Wochenpläne ableiten und Stunden pro Lehrkraft berechnen. Diese Anleitung zeigt Schritt für Schritt, wie das funktioniert, ob für Schule, Weiterbildungseinrichtung, Sprachkurs oder Nachhilfeinstitut.

Der Plan als Liste, nicht als Raster

Ein klassisches Raster mit Wochentagen in Spalten und Stunden in Zeilen sieht gut aus, lässt sich aber kaum auswerten. Besser ist eine Liste, in der jede Unterrichtseinheit eine Zeile ist:

Die Slots (Zeitfenster) definieren Sie einmal auf einem Stammdatenblatt, etwa Slot 1 = 8:00–9:30. Tag, Slot, Gruppe, Fach, Lehrkraft und Raum wählen Sie über Dropdown-Listen, damit Namen immer gleich geschrieben sind.

Konflikte automatisch erkennen

Ein Konflikt liegt vor, wenn am selben Tag im selben Slot dieselbe Lehrkraft, derselbe Raum oder dieselbe Gruppe zweimal vorkommt. Mit ZÄHLENWENNS prüfen Sie das pro Zeile (Tag in A, Slot in B, Gruppe in C, Lehrkraft in E, Raum in F):

=WENN(ZÄHLENWENNS(A:A;A2;B:B;B2;E:E;E2)>1;"Lehrkraft belegt";WENN(ZÄHLENWENNS(A:A;A2;B:B;B2;F:F;F2)>1;"Raum belegt";WENN(ZÄHLENWENNS(A:A;A2;B:B;B2;C:C;C2)>1;"Gruppe belegt";"OK")))

Mit einer bedingten Formatierung färben Sie jede Zeile rot, deren Prüfung nicht „OK“ lautet. Im Beispiel oben gäbe es keinen Konflikt, weil Becker zwar zweimal vorkommt, aber in verschiedenen Slots. Würde Becker im Slot 1 ein zweites Mal eingetragen, wären beide Zeilen sofort rot.

Auslastung gegen das Deputat

Jede Lehrkraft hat ein Deputat, also die vereinbarte Zahl an Wochenstunden. Die tatsächliche Belastung berechnen Sie so: Anzahl der Einheiten pro Woche mal Stunden pro Einheit. Dauert eine Einheit zwei Unterrichtsstunden, gilt:

=ZÄHLENWENN(E:E;"Becker")*2

Steht das Deputat in einer Stammdatentabelle, berechnet =D2/C2 die Auslastung in Prozent. Beispiel: Becker hat 9 Einheiten à 2 Stunden = 18 Stunden bei einem Deputat von 20. Das ergibt 90 %. Ab etwa 100 % sprechen Sie von Überlast, deutlich darunter von Unterlast. Welche Schwellenwerte für Ihre Einrichtung gelten, legen Sie selbst fest.

Wochenplan je Gruppe oder Lehrkraft

Aus der Liste können Sie einen druckfertigen Wochenplan ableiten. Legen Sie ein Raster an und holen Sie sich den Eintrag per Formel, zum Beispiel mit SUMMENPRODUKT und INDEX, oder in älteren Versionen mit einer Hilfsspalte, die Tag, Slot und Gruppe zu einem Schlüssel verkettet (=A2&"|"&B2&"|"&C2) und anschließend mit SVERWEIS abgefragt wird. In einer Dropdown-Zelle wählen Sie die Gruppe, und das Raster aktualisiert sich.

Typische Fehler

• Den Plan zuerst als Raster zeichnen und dann versuchen, ihn auszuwerten.
• Lehrkräfte mit unterschiedlichen Schreibweisen („Becker“, „Becker, A.“), sodass ZÄHLENWENN ins Leere läuft.
• Pausen und Slots im Plan hartcodieren, statt sie zentral zu pflegen.
• Wartungszeiten von Räumen oder Teilzeitregelungen vergessen: Tragen Sie sie als eigene Einträge ein, damit sie blockieren.

Fertige Excel-Vorlage für Stundenplan und Auslastung

Die kostenpflichtige Vorlage „Stundenplan und Auslastung der Lehrkräfte“ setzt diesen Ansatz fertig um. Im Blatt „Stammdaten“ legen Sie Lehrkräfte mit Deputat in Stunden pro Woche, Gruppen, Räume, Fächer, Unterrichtszeiten (Slots) und Auslastungsparameter fest. Das Blatt „Unterricht“ fasst bis zu 800 Einheiten: Sie wählen Tag, Slot, Gruppe, Fach, Lehrkraft und Raum aus Listen, die Datei berechnet Zeit und Stunden und prüft Konflikte mit Hervorhebung und Klartext in der Spalte „Prüfung“.

Der „Wochenplan“ wird per Formeln aus dem Journal aufgebaut, für die gewählte Gruppe oder Lehrkraft, und ist druckfertig. Die „Auslastung“ zeigt Wochenstunden je Lehrkraft im Vergleich zum Deputat, die Auslastung in Prozent, Überlast, die Einheiten nach Wochentagen und zwei Diagramme. Das spart Ihnen den Formelaufbau und die Prüflogik. Enthalten sind eine leere Tabelle und ein ausgefülltes Muster mit fiktiven Daten. Die Datei kommt ohne Makros aus, läuft in Excel 2007 und neuer sowie in LibreOffice und ist auf Deutsch. Deputate und Schwellenwerte tragen Sie nach den Werten Ihrer Einrichtung ein.

FAQ

Kann ich mit Excel einen Stundenplan automatisch generieren lassen?

Nein, Excel prüft und wertet aus, plant aber nicht von selbst. Sie setzen die Einheiten, und die Tabelle zeigt sofort, wo etwas nicht passt. Für viele kleine und mittlere Einrichtungen genügt das, weil die schwierigen Entscheidungen ohnehin von Menschen getroffen werden.

Wie plane ich Doppelstunden?

Legen Sie einen Slot so fest, dass er eine Doppelstunde abdeckt, oder tragen Sie zwei aufeinanderfolgende Slots ein. Wichtig ist, dass die Stundenzahl pro Slot in der Auslastungsrechnung berücksichtigt wird.

Was mache ich bei Räumen mit begrenzter Kapazität?

Ergänzen Sie in den Stammdaten die Platzzahl und vergleichen Sie sie mit der Gruppengröße. Eine Spalte „Platzzahl minus Gruppengröße“ zeigt sofort, wo es eng wird.

Wie halte ich Änderungen im laufenden Jahr nach?

Speichern Sie vor größeren Änderungen eine Kopie mit Datum im Dateinamen. So lässt sich später nachvollziehen, welcher Plan zu welchem Zeitpunkt galt.