Übersicht der Unterrichtsstunden zwischen Klasse, Fach und Lehrkraft umschalten

[Markus86]

Hallo zusammen,
ich hoffe, in diesem Forum Hinweise für eine Lösung meines Problemes zu bekommen.

Als Stundenplaner an der Schule habe ich eine Excel-Tabelle als Übersicht, in welcher Klasse welches Fach von welcher Lehrkraft mit welcher Stundenanzahl unterrichtet wird.
In der ersten Zeile (beginnend mit Spalte B) stehen die Klassen, in der ersten Spalte (beginnend mit Zeilte 2) die Fächer. In den jeweiligen zugehörigen Zellen sind die Lehrkräfte mit ihrer Stundenzahl eingetragen. So kann ich bspw. ablesen: Fach Deutsch, 5a, Lehrkraft Ak, 5 Stunden.

Der Nachteil bei dieser Tabelle: Die Lehrkraft erkennt nicht auf einen Blick, in welchen Klassen sie mit welchen Fächern eingesetzt ist. Sie muss Zelle für Zelle durchgehen und schauen, ob ihr Kürzel auftaucht.

Daher würde ich gern in einem zweiten Tabellenblatt eine Übersicht erhalten, in der in Spalte 1 alle Kürzel aus Tabellenblatt 1 untereinander aufgeführt sind. Rechts neben jedem Kürzel sollen dann Klasse, Fach, Stunden aufgelistet werden.

Einen kleinen Ausschnitt aus der Gesamtübersicht habe ich mal händisch so zusammengefriemelt, wie ich es gern als automatisierte Übersicht hätte.

Meine Frage ist daher, in welche Zelle trage ich welche Formel ein, um eine solch automatisierte Liste zu erhalten? Lässt sich so etwas überhaupt realisieren?

So oder so: Vielen Dank vorab für eure Unterstützung!

Gruß
Markus

[Ralf A]

...Der Nachteil bei dieser Tabelle: Die Lehrkraft erkennt nicht auf einen Blick, in welchen Klassen sie mit welchen Fächern eingesetzt ist. Sie muss Zelle für Zelle durchgehen und schauen, ob ihr Kürzel auftaucht.

Daher würde ich gern in einem zweiten Tabellenblatt eine Übersicht erhalten, in der in Spalte 1 alle Kürzel aus Tabellenblatt 1 untereinander aufgeführt sind. Rechts neben jedem Kürzel sollen dann Klasse, Fach, Stunden aufgelistet werden.

Hi Markus,

wozu? Bevor ich mir die Datei herunterlade, hast Du es schon mal mit Filtern versucht? Oder Datenschnitten? Da braucht es kein Übersichtsblatt. Die Daten sind doch alle vorhanden... sie müssen lediglich bedarfsgerecht gefiltert werden...

[Markus86]

Hallo Ralf,

danke schon mal für die Anregung.
Ich bin nicht sicher, ob es mit Filtern funktioniert oder ob meine Kenntnisse einfach nicht ausreichen.

Hab es probiert, aber es werden dann nicht alle Zellen mit bspw. Kollegin "Dü" angezeigt, weil sich dieses Kürzel in unterschiedlichen Zellen wiederholt (F5, H5, H6, H7).
Mit dem Filter für Dü in Spalte F werden mir halt nicht die Zellen mit Dü aus Spalte H angezeigt. Stattdessen aber die anderen Kollegen, die sich in Zeile 5 befinden und eigentlich ausgeblendet werden sollen.

Beim Datenschnitt wäre es ja ähnlich, denke ich.

Im Prinzip bräuchte ich eine Formel, die mir im Falle von "Dü" alle Zellen liefert, die Dü enthalten. Dies reicht aber noch nicht, da die jeweils rechte Nachbarzelle (sprich Stundenanzahl) sowie die zugehörigen Spltenüberschriften (sprich Klasse) und die Zeilenüberschriften (sprich Unterrichtsfach) ebenfalls eingeblendet werden sollen.

Über weitere Vorschläge wäre ich dankbar.

LG
Markus

[Gast 123]

Hallo

mit Formeln kann ich euch nicht dienen, dafür mit einem Button.  Anklicken, Liste fertig!

Die Liste beginnt ab Spalte AA, damit ihr noch weitere Klassen einfügen könnt.
Die unbenutzten Spalten habe ich ausgeblendet.  Bitte nicht an der Optik stören.
Ich habe die Spalten verkleinert, weil mein Laptop Bildschirm sehr klein ist.

Darf man erfahren für welche Schule das Projekt ist?  Diese Antwort kommt aus Izmir.
Würde mich freuen wenn euch dieser Vorschlag weiterhilft. Das Layout kann man noch ändern.

mfg Gast 123

[Ralf A]

..dachte mir schon, dass Deine Datenstruktur ungünstig ist. Statt x-Mal Klasse-Fach-Lehrer-Stunden-Kürzel nebeneinander zu schreiben, genügen diese 3 oder 4 Spalten und ALLE Daten werden untereinander geschrieben. Und wenn Du Datengültigkeit (Dropdowns) verwendest, brauchst Du nicht mal viel schreiben.
Dann klappts auch mit dem Nachbarn... Dann kannst Du praktisch nach allem filtern...

Aber gibt es nicht Stundenplanplaner zu Hauf im Netz?

[EA1950]

Hallo,

bei einem Excel365 würde folgende benutzerdefinierte LAMBDA-Arbeitsblattfunktion, die man unter dem Namen "FachKlasseKürzel" in den Namensmanager einfügt, die von dir gewünschte Umformung durchführen.
Aufzurufen im Arbeitsblatt ist sie mit: =FachKlasseKürzel(A2:I7)

Code:

FachKlasseKürzel = LAMBDA(tb; LET(
    ko; ZEILENWAHL(tb; 1);  dt; WEGLASSEN(tb; 1);
    dtfa; INDEX(dt; ; 1);   dtküSt; WEGLASSEN(dt; ; 1);
    mdt; MATRIXERSTELLEN(
        ZEILEN(dtküSt); SPALTEN(dtküSt) / 2;
        LAMBDA(i; j;
            TEXTVERKETTEN(
                "|"; 0;
                INDEX(dtküSt; i; 2 * j - 1);
                INDEX(ko; 2 * j); INDEX(dtfa; i);
                INDEX(dtküSt; i; 2 * j)
            )
        ));
    mdtsp; ZUSPALTE(mdt);
    grkü; GRUPPIERENNACH(TEXTVOR(mdtsp; "|"); TEXTNACH(mdtsp; "|"); MATRIXZUTEXT; 0; 0);
    zlkü; TEXTTEILEN(TEXTVERKETTEN("<"; 0; INDEX(grkü; ; 2)); {"|". "; "}; "<"; 1; ; "");
    daten; HSTAPELN(INDEX(grkü; ; 1); zlkü);
    kopf; TEXTTEILEN(
        "Kürzel" & WIEDERHOLEN("|Klasse|Fach|Stunden"; (SPALTEN(daten) - 1) / 3); "|"
    );
    VSTAPELN(kopf; daten)
));;

Für dein Excel2016 stellt diese Funktion jedoch eine totale Überforderung dar. Es bleibt da sicherlich nur eine VBA-Lösung.

[Fred11]

Hallo Markus,

so wie deine Ausgangstabelle aktuell aufgebaut ist, ist es meiner Meinung nach in deiner Excel-Version nicht möglich, daraus mit Formeln direkt die gewünschte Übersichtstabelle zu erstellen.

Wenn du dich dazu durchringen kannst, deine Ausgangstabelle so wie von Ralf vorgeschlagen aufzubauen,

Arbeitsblatt mit dem Namen 'Vorschlag 1'
	A	B	C	D
2	Fach	Lehrer	Klasse	Stunden
3	D	AK	5a - Sk	5
4	D	Mat	5b - Hey	5
5	D	Mat	5c - Mat, Ap	5
6	D	SV	5d - Dü, Sv	5
7	Ma	La	5a - Sk	5
8	Ma	Hey	5b - Hey	5
9	Ma	Ap	5c - Mat, Ap	5
10	Ma	Hey	5d - Dü, Sv	5
11	E	Ak	5a - Sk	5
12	E	Cy	5b - Hey	5
13	E	Dü	5c - Mat, Ap	5
14	E	Dü	5d - Dü, Sv	5
15	GuP	Ho	5a - Sk	2
16	GuP	Fo	5b - Hey	2
17	GuP	Fo	5c - Mat, Ap	2
18	GuP	Dü	5d - Dü, Sv	2
19	WAT	Re	5a - Sk	2
20	WAT	Re	5b - Hey	2
21	WAT	Re	5c - Mat, Ap	2
22	WAT	Dü	5d - Dü, Sv	2

Verwendete Systemkomponenten: [Windows (64-bit) NT 10.00] / MS Excel 2021

Diese Tabelle wurde mit Tab2Html (v2.7.1) erstellt. ©Gerd alias Bamberg

dann läßt sich daraus mit einer (allerdings nicht ganz einfachen) Formellösung mit Hilfsspalten deine gewünschte Übersichtstabelle generieren.
(wenn das dann überhaupt noch notwendig ist, da du bei einem solchen Tabellenaufbau jetzt einfach nach Lehrkraft filtern kannst)
Siehe Anhang - "Vorschlag 1"

Wenn du dich nicht dazu durchringen kannst, deine Ausgangstabelle so aufzubauen, ließe sich aus deiner aktuellen Ausgangstabelle eine solche Liste per Formeln als Hilfstabelle erstellen.
Allerdings ist auch das in Excel 2016 etwas umständlich….
Und darauf basierend wiederum deine Übersichtstabelle generieren.
Siehe Anhang "Vorschlag 2"

Also insgesamt alles eher etwas umständlich und die Formeln sind recht komplex.
Ob das für dich so praktikabel und sinnvoll ist, musst du letztendlich selber entscheiden.

Gruß
Fred