1.2k Aufrufe
Gefragt in Tabellenkalkulation von robbi58 Mitglied (966 Punkte)
wieder getaggt von robbi58

Ein herzliches Hallo an die Runde!

Covid-19 macht mir einen Strich durch die Rechnung, in meinem Fall einen Strich durch den Stundenplan unserer Schule.
Ich habe die Stundenpläne mit Excel-Tabellen an die gesetzlichen Vorgaben angepasst. Funktioniert so weit bestens, aber ein großes Problem steht mir noch bevor, nämlich das Auslesen der Stunden für alle KollegInnen.

Die auszulesenden Tabellenblätter sind bezeichnet mit 1a .... bis 4c. Die Blätter sind alle ident mit der obigen Abbildung (nur andere Stundenbezeichnungen und Lehrpersonen). Ich müsste nun aus allen 12 Tabellenblättern die Stunden für eine bestimmter Lehrperson auslesen. In diesem Fall wäre dies auf dem Arbeitsblatt "Stundenplan" für die Lehrperson "PE",

Meine Idee wäre folgende: Ich gebe in das Feld A3 das Kürzel PE ein, um aus den 12 Blättern alle Stunden von PE auszulesen (Fach und Klasse in den ungeraden Zeilen; das Namenskürzel in die geraden Zeilen) . In den Spalten B, D, F , H , J sollten die Stunden für die 1. und 3. Klassen stehen, in den Spalten C , E , G , I , K die Stunden für die 2. und 4. Klassen ( so wie in der Abbildung oben). Die Unterscheidung in Klassen und Spalten deshalb, da in einer Woche am Montag die 1. und 3. Klassen unterrichtet werden, in der folgenden Woche am Montag die 2. und 4. Klassen.

Vielleicht denke ich zu einfach, vielleicht denke ich zu kompliziert und umständlich. Ich bin für jjeden Gedanken dankbar und auch offen für Alternativen.

Denn die Variante, alle Daten händisch auszulesen und das bei 40 KollegInnen stimmt mich nachdenklich.
Deshalb dieses Posting um 3 Uhr mitten in der Nacht.
Gruß Robert

PS: die unterschiedliche Einfärbung ergibt sich aus den Standardstunden (rot gekennzeichnet) und den zusätzlich anfallenden Stunden (blau gekennzeichnet).

9 Antworten

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Robert,

wie sollen die Stunden denn gezählt werden? Insgesamt oder getrennt nach 1.,3. Klasse / 2.,4. Klasse?  Ich nehme mal an, dass du mit Stunden zählen wissen willst, wie oft das entsprechende Kürzel vorkommt.

Gruß

M.O.
0 Punkte
Beantwortet von robbi58 Mitglied (966 Punkte)

Hallo M. O.!

Im Grunde genommen ist es so, dass die Auslesebereiche und der Einlesebereich immer gleich sind!

Die Daten stehen sowohl für das Auslesen wie das Einlesen stets im Bereich A5 : K16. Das Problem allerdings ist, dass in den Spalten B , D , ... die Stunden aus den 1. und 3. Klassen ausgelesen werden sollen, in den Spalten C , E , ... die Stunden aus den 2. und 4. Klassen. Das Problem ist ja, dass in den Auslesefeldern die stets 2 Lehrpersonen in den betreffenden Stunden eingetragen sind und das Kürzel PE sowohl links bzw. rechts stehen kann. Ich benötige nur die gelb unterlegten Felder (M-1c, Me-3a,...). Das Kürzel kann ich ja ausblenden.

Das nächste Problem ist, dass die betreffende Lehrperson am MO in der 1. Stunde in den Klassen 1. und 3. die Stunde M-1c stehen hat, gleichzeitig aber in der 2. und 4. Klasse m.-4c. Das hängt damit zusammen, dass in den geraden Kalenderwochen die Stundentafel am Montag für die 2. und 4. Klasse gilt, in den ungeraden Kalenderwoichen die Stundentafel für die 1. und 3. Klasse. Am Dienstag ist es dann genau umgekehrt.

Zu unklar! Wenn ja, dann lade ich die Datei mal hoch.

Einstweilen recht herzlichen Dank.

LG Robert

0 Punkte
Beantwortet von
Bearbeitet
Hallo M. O.

Ich habe mir gerade überlegt, dass ich ja in den Tabellenblättern 1a bis 4c die nicht benötigten Tagespläne löschen und ausblenden kann (z.B: MO - 1. und 3. Klassen löschen und ausblenden; 2. und 4. Klassen bestehen lassen; damit bleibt der Auslesebereich stets gleich). Dann wäre zu jeder Stunde maximal nur ein Lehrereintrag (hier PE; für mich wichtig ist der Eintrag über dem Kürzel - z. B. M-4c; damit weiß die Lehrperson, welche Klasse sie aufsuchen muss)) vorhanden. Somit ist beim Auslesen die Unterscheidung zwischen 1. und 3. sowie 2. und 4. Klasse nicht mehr nötig. Das würde das Auslesen ziemlich sicher vereinfachen! Oder?
Tut mir leid, ist mir aber erst jetzt eingefallen!
 

LG Robert
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Robert,

es wäre gut, wenn du mal eine Testdatei einstellen könntest. Und stelle doch bitte in der Datei auch dar, wie das Ergebnis aussehen soll.

Gruß

M.O.
0 Punkte
Beantwortet von robbi58 Mitglied (966 Punkte)
Hallo M.O.

Anbei die Beispielsdatei!

https://www.dropbox.com/scl/fi/a1l6zbo0d1vtspdx8cwdj/Beispielsdatei_Stundenplan.xlsx?dl=0&rlkey=7a6lwfbf1wvs9hey9ayq9zsbq

Das Blatt Stundenplan ist jener Bereich, der eingelesen werden soll. Ich habe dann zwei Klassen angefügt, die eingelesen werden sollen.

Wie gesagt, Ein- und Auslesebereich stimmen bei allen Blättern überein. Was hier fehlt, sind die restlichen Klassen (je ein Tabellenblatt - 1a,1b,1c,2a,2b,2c,3a,3b,3c,4a,4b,4c; das sind insgesamt 12 Tabellenblätter).

Ich bedanke mich jetzt schon für dein Bemühen!

LG Robert
+1 Punkt
Beantwortet von m-o Profi (22.7k Punkte)

Hallo Robert,

da du ja über mehrere Tabellenblätter die Stunden zusammenstellen willst, habe ich dir eine Function gebastelt.

Kopiere den folgenden Code in ein allgemeines Modul deiner Tabelle:

Function Stundenplan(ByVal strKuerzel As String) As String
Dim arrKlassen
Dim k As Integer
Dim t As Integer

'Klassenblätter, die durchlaufen werden sollen
arrKlassen = Array("1a", "1b", "1c", "2a", "2b", "2c", "3a", "3b", "3c", "4a", "4b", "4c")

For k = 0 To UBound(arrKlassen)
  'Alle Arbeitsblätter in Datei durchlaufen
  For t = 1 To ThisWorkbook.Worksheets.Count
     'und nur dann suchen, wenn das entsprechende Arbeitsblatt auch vorhanden ist
     If ThisWorkbook.Worksheets(t).Name = arrKlassen(k) Then
       'hier wird geprüft, ob das gesuchte Kürzel dem Inhalt der Zelle des Tages und der Stunde des aufgerufenen Stundenplan entspricht
       '(da die Stundenplanbezeichnung über dem Kürzel steht, muss das Kürzel in einer Zeile tiefer verglichen werden)
         If ThisWorkbook.Worksheets(t).Cells(Application.Caller.Row + 1, Application.Caller.Column) = strKuerzel Then
           'falls ja, wird die Stundenbezeichnung in Variable geschrieben
           Stundenplan = ThisWorkbook.Worksheets(t).Cells(Application.Caller.Row, Application.Caller.Column)
           Exit Function
         End If
      End If
 Next t
Next k

'Falls nichts gefunden, bleibt Stundenplan leer
Stundenplan = ""

End Function


Der Aufruf erfolgt einfach, indem du in die Zelle B5 die folgende Formel schreibst:

=Stundenplan($A$3)

Die Formel kannst du dann nach links ziehen und auch nach unten kopieren.

Voraussetzung ist, dass die Tabellen wie in deinem Beispiel immer gleich aufgebaut sind, da das Kürzel ausgehend von der Adresse der Zelle, aus der die Function aufgerufen wird, in den einzelnen Blättern gesucht wird. Wird die Formel z.B. aus der Zelle B5 aufgerufen, wird geprüft, ob in einem der angegebenen Tabellenblätter im Zelle B6 das gesuchte Kürzel steht und falls ja, dann der Wert aus Zelle B5 zurückgegeben.

Gruß

M.O.

0 Punkte
Beantwortet von robbi58 Mitglied (966 Punkte)
Herzlichen Dank an M.O.!

Bin erst jetzt dazugekommen, das Makro auszuprobieren. Entsprechend deinen Anleitungen bin ich vorgegangen und wie immer, es hat bestens funktioniert. Da erspare ich mir für die Erstellung der Arbeitspläne für die KollegInnen viel an Arbeit.

Auf ein virtuelles Bier oder Corona-Bier für dich, M.O.

PS: Ich finde es toll, dass es solche Foren gibt, wo auch weniger begnadete Excel-User sehr rasch Hilfe bekommen können.
0 Punkte
Beantwortet von robbi58 Mitglied (966 Punkte)
Hallo M.O.

Eine Frage habe ich zur Formel:
Wird der Bereich, in der abgefragt wird, durch jene Zellen festgelegt, in denen ich die Formel (=Stundeplan($A$3)) eingebe? Ich habe nämlich versucht, den Bereich bei allen Tabellenblättern um 5 Spalten zu erweitern (Bereich B5:P16), da hat es leider nicht funktioniert! Aus der Formel kann ich mit meinen bescheidenen Kenntnissen keine Parameter herausfinden, um diese anzupassen.
Danke und einen schönen Abend an alle.
LG Robert M.
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Robert,

du brauchst keinen Bereich anpassen. Wenn du den Bereich erweitert hast und die Formel in die entsprechenden Zellen gezogen hast, dann lasse mal das Tabellenblatt neu berechnen (Menuband Daten - Neu berechnen).

Wie bereits oben geschrieben prüft die Function die Zelle in den im Code hinterlegten Tabellenblättern, aus der die Formel aufgerufen wird.

Gruß

M.O.
...