Raum-/Klassenpläne

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

allo Klaus,
habe jetzt erst Zeit gefunden mir deinen Vorschlag anzuschauen. Ich bin beeindruckt, das läuft ja :-) WOW! Obwohl ich Excel sehr gut kenne - naja meine ich zumindest - muss ich zugeben, dass ich nicht so wirklich hinter deine Formel(n) komme. Ich versuche das mal für mich in normales Deutsch zu übersetzen. Wäre nett, wenn du mir einige Sachen erklärst:
Blatt: Ausgangsplan:
Funktion: =H4&ZÄHLENWENN($F$2:F4;F4)-1
Hier wird also in der Zelle mit obiger Formel die Häufigkeit des Inhaltes von ZelleH4 (hier Raumnummer) in Verbindung mit der Person die den Raum belegt anhand des Personenkürzels im Bereich F2:F4 gezählt und ausgegeben. Was soll "-1" nach der Klammer? *mmmh?*
Nun zu der Megaformel *uff*: Blatt Raumplan:
=WENN(ISTNV(SVERWEIS($B$2&$C3;Ausgangsplan!A:Z;3;));"-";SVERWEIS($B$2&$C3;Ausgangsplan!A:Z; 7-SPALTEN($A$1:A1);)&" "&SVERWEIS($B$2&$C3;Ausgangsplan!A:Z;VERGLEICH(D$2;Ausgangsplan!$2:$2;) +1-SPALTEN($A$1:A1);)&" "&SVERWEIS($B$2&$C3;Ausgangsplan!A:Z;7+SPALTEN($A$1:A1)*2;))
...und da hörts dann schon fast auf bei mir... Suche den Inhalt von B2 und C3 (also Raumnummer und erste Stunde) im Ausgangsplan. Ist da nix vorhanden (IST NV) dann schreibe "-". Nun weiss ich nicht weiter.
Was bedeutet A:Z;3 (suche in den Spalten A-Z ab dritte Reihe?
Was bedeutet A:Z;7-Spalten($A$1:A1) bzw. 7+Spalten*2 ?
Ich kann in dieser obige Formel keinen Bezug zur ZähleWenn aus dem ersten Registerblatt erkennen.
Naja, was soll ich sagen - es läuft, ist mir aber immer noch ein Rätsel wie.
Ein paar Erklärungen wären super. Danke.
Chris :-)

Hi Chris,
danke erstmal für die Rückmeldung!
Ich erklär mal meine chaotischen Ansätze:
Funktion: =H4&ZÄHLENWENN($F$2:F4;F4)-1
In F:F steht das Lehrerkürzel. Ich zähle, wie oft das Lehrerkürzel vorkam. Kommt es VIER mal vor (inklusive Überschrift!), dann sind wir in der DRITTEN Stunde: daher das -1 am Ende.
Durch Verknüpfung mit der Raumnummer habe ich nun den Code Raumnummer-Stunde, nach dem ich eindeutig Sverweisen kann.
Den ersten Teil der Raumnummerformel hast du verstanden: er verhindert schlicht die unschönen #NV Fehler.
Nehmen wir den zweiten Monsterteil mal auseinander:
SVERWEIS($B$2&$C3;Ausgangsplan!A:Z; 7-SPALTEN($A$1:A1);)&" "&SVERWEIS($B$2&$C3;Ausgangsplan!A:Z;VERGLEICH(D$2;Ausgangsplan!$2:$2;) +1-SPALTEN($A$1:A1);)&" "&SVERWEIS($B$2&$C3;Ausgangsplan!A:Z;7+SPALTEN($A$1:A1)*2;))
ist zerlegt viel weniger beeindruckend:
SVERWEIS($B$2&$C3;Ausgangsplan!A:Z; 7-SPALTEN($A$1:A1);)
SVERWEIS($B$2&$C3;Ausgangsplan!A:Z;VERGLEICH(D$2;Ausgangsplan!$2:$2;) +1-SPALTEN($A$1:A1);)
SVERWEIS($B$2&$C3;Ausgangsplan!A:Z;7+SPALTEN($A$1:A1)*2;))
Die SVerweise ergeben: 1) Lehrer 2) Klasse 3) Fach. Durch die &" "& verknüpfe ich die drei zu dem String, der im Raumplan stehen soll.
Was SVERWEIS macht setze ich bei Excel-Profi mal als bekannt vorraus (sonst: F1).
Ich suche nach $B$2&$C3, in B2 steht die Raumnummer und in C3 steht die Stunden-Nummer. Das zusammengesetzte Suchergebnis ist zB. 2041 für Raum 204 in Stunde 1.
Den eindeutigen Suchcode 2041 finden wir im Ausgangsplan wieder, generiert durch die Zählenwenn Formel!
Schauen wir uns mal den ersten SVERWEIS an, der den Lehrernamen findet.
SVERWEIS($B$2&$C3;Ausgangsplan!A:Z; 7-SPALTEN($A$1:A1);)
auf deutsch:
SVEWEIS(eindeutigerCode[2041];Ausgangsplan!A:Z;sieben minus eins;nix)
das heisst: gehe in Spalte A (Ausgangsplan!A: ...) solange nach unten, bis du den Code 2041 gefunden hast. Dann gehe innerhalb der 26 Spalten A:Z (Ausgangsplan!A:Z) eine Anzahl Spalten nach rechts, in der der Lehrername steht.
Von Spalte A aus gesehen steht der Lehername 6 Spalten weiter rechts. Die ANZAHL der SPALTEN($A$1:A1) ist gleich 1. Gehe also 7 minus 1 = 6 Spalten nach rechts, da steht der Lehrername.
Jetzt folgt Trick 17: die Dienstag-Formel, um den Lehrer zu finden.
SVERWEIS($B$2&$C3;Ausgangsplan!B:AA;7-SPALTEN($A$1:B1);
Schau genau hin: Die Formel sucht in Ausgangsplan!B nach dem Code 2041, geht dann im Bereich bis zu 26 Spalten (B:AA) solange nach rechts, bis da der Lehrer steht. Von Spalte B ausgehend sind es aber nicht mehr 6, sondern nur noch 5 Spalten nach rechts! Da der Formelteil "7-SPALTEN($A$1:B1)" teils fix und teils variabel ist, verändert sich die ANZAHL der SPALTEN beim kopieren nach rechts. von A1 bis B1 sind es insgesamt ZWEI Spalten, 7 minus 2 = 5, und der Lehrer steht 5 Spalten vom Dienstag-Code aus gesehen rechts.
Verdauen, abstrahieren, verstehen. Am besten nicht weiterlesen, sondern den Formelteil in der Datei nochmal genau ansehen und nachvollziehen :-)
Die zweite SVerweis Formel findet die Klasse:
SVERWEIS($B$2&$C3;Ausgangsplan!A:Z;VERGLEICH(D$2;Ausgangsplan!$2:$2;) +1-SPALTEN($A$1:A1);)
Der Anfang ist klar, suche nach eindeutiger Code. Der zweite Teil auch, suche innerhalb von 26 Spalten A:Z die soundsovielte nach rechts.
VERGLEICH(D$2;Ausgangsplan!$2:$2;) sucht die Spaltennummer des Wochentages, auf den sich diese Formel bezieht (das ist der Grund, warum ich dir die verbundenen Zellen wieder aufdröseln musste! Verzichte IMMER auf verbundene Zellen!).
Der Wochentag Montag ist 7 Spalten von Links im Ausgangsplan vorhanden (schau genau hin: es sieht aus als Stände er in der 8ten Spalte, aber es ist die siebte!).
Spalten(A1:A1) ergibt 1. Also 7-1+1 = 7.
Die gesuchte Klasse steht, von Spalte A im Ausgangsplan aus gesehen, in der 7ten Spalte.
Der Wochentag Dienstag ist 10 Spalten von Links im Ausgangsplan vorhanden (sieht aus wie die 11te!).
Spalten(A1:B1) (Die Formel ist ja einen nach rechts verschoben!!) ergibt 2. 10-1+2 ergibt 11.
Die gesuchte Klasse steht, von Spalte B im Ausgangsplan aus gesehen, in der 11ten Spalte nach rechts! (eigentlich in der 12ten, aber wir gehen von Spalte B aus und überspringen die erste).
Die Lehrerformel mit dem Spalten*2 erklär ich mal nicht ;-) Rechne dir die Spaltenindices einfach mal aus und schau, wie weit rechts der Lehrer von der jeweiligen Suchspalte aus entfernt steht.
Noch Fragen? Fragen! :-)
Grüße,
Klaus M.vdT.

Beliebteste Forumthreads (12 Monate)

8 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread