Sitzplan für Nachschreiber mit bestimmten Kriterien erstellen

[worrest-t]

Hallo zusammen,

bisher konnte ich meine Excel Vorhaben immer eigenständig realisieren, aber dieses Projekt überfordert meine bisherigen Ideen/Kompetenzen, daher wende ich mich hier an euch.

Ich möchte für die Schule eine Nachschreiberliste erstellen, aus welcher die Schüler automatisch in einen Sitzplan verteilt werden. Bedingung ist aber dabei, dass an den jeweils zweier Tischen keine Schüler mit dem gleichen Fach oder aus der gleichen Klasse sitzen.

Die Nachschreiberliste habe ich schon erstellt und sie enthält folgende Zellen: Vorname, Nachname, Klasse und Fach, indem nachgeschrieben wird.

In einem anderen Tabelllenblatt sollen nun die Namen, Vornamen, Klasse und Fach verteilt werden. 2 Zellen könnten dabei einen Tisch symbolisieren, wobei nun wichtig ist, dass Kein Name und Vorname neben einen anderen Namen, Vornamen kommt, wo Klasse oder Fach identisch sind.

Könnt ihr mir Hilfestellung bei der Erstellung geben?

Schönen Gruß

Christian

[WillWissen]

Hi Christian,

mit einer Beispieltabelle hilfst du den Helfern beim Helfen. :32:  Lies mal bitte hier (klick).

[Ego]

Hallo Christian,

fehlen da nicht noch einige Kriterien?

a) Schüler mit gleicher Klasse und/oder gleichem Fach auch möglichst nicht an benachbarten Tischen setzen? Spielt hierbei die Platzseite am Tisch eine Rolle?
b) Wenn die Anzahl der Schüler kleiner als Anzahl der Tische * 2 möglichst alle Tische besetzen?  Ist dies wichtiger als a)?
c) Gibt es bei der Zuweisung von Einzeltischen Prioritäten (Klasse oder Fach)? Ist dies wichtiger als a)

Ich könnte mir vorstellen, dass ein Programm
1) über Zufallsverteilung einige hundert oder tausend mögliche Verteilung nach deinen Kriterien ermittelt
2) für diese Verteilungen eine Bewertung (Bewertungsfunktion nach gewünschten Kriterien a)-c) ) errechnet und
3) die Verteilung mit dem optimalen Wert  ausgibt.

[worrest-t]

Hallo zusammen,

danke für eure freundlichen Hinweise, ich habe im Anhang die Tabelle angefügt und ich werde näher auf die Kriterien eingehen.

Klassenliste
Das erste Tabellenblatt ist eine Klassenliste mit den relevanten Feldern: Vorname, Nachname, Klasse und Fach. Fach und Klasse sind dabei über ein Dropdown Menu formatiert, um mögliche Fehler bei der Eintragung zu vermeiden. Weiter, auch wenn nicht relevant für die Sitzordnung, doppelte Eintragung eines Schülers (Doppellung aus Vorname und Nachname) wird schon jetzt rot markiert.

Sitzplan
Der Sitzplan ist grafisch so angeordnet, wie die Tische in in der Klasse, um ein Drucken und späteres Vergleichen zu erleichtern. Jetzt zu den Kriterien. Es sind 15 Tische und dort können 30 Schüler angeordnet werden.

Kriterien

• Die ersten 15 Schüler können auf alle 15 Tische verteilt werden. Bis 15 Schüler gibt es noch keine Probleme. 
  
• Ab Schüler 16 bis 30 sollen diese so angeordnet werden, dass Schüler mit gleichem Fach und/oder Klasse nicht an einem und dem selben Tisch sitzen.
  
• Benachbarte Tische spielen keine Rolle
  
• Wenn die Anzahl der Schüler kleiner als die der Tische ist, so sollte an jedem Tisch nur 1 Schüler sitzen. Daher kam gerade meine Idee, erstmal alle 15 zu verteilen (Sitzplatz 1,3,5,7 ...).
  
• Bei der Zuweisung der Einzeltische gibt es keine weiteren Prioritäten.
  

Ich hoffe es ist nun verständlicher geworden

Schönen Gruß

Christian

[coemm]

Hallo Christian,

warum sortierst du die Liste, wenn sie vollständig ausgefüllt ist, nicht nach Klasse und dann noch nach Fach.
Es ist doch höchst unwahrscheinlich, dass es mehr als 15 Schüler aus einer Klasse bzw. mit dem gleichen Fach gibt.
Die laufenden Nummern der Liste kannst du vorher den einzelnen Sitzplätzen zuordnen; also Nr 1 - Platz 1; Nr 2 - Platz 3; usw.

[worrest-t]

Hallo Conny,

danke für deine Antwort, aber ich glaube so einfach kann es nicht sein, da wir zum Beispiel 6 Fachabi Klassen haben, die parallel in Deutsch die gleichen Themen haben. Daher kommt schon aus jeder Klasse so 3-5 Nachschreiber zusammen. Demnach würde eine reine Verteilung nach Klasse und Fach dazu führen, dass doch wieder häufig welche an einem gleichen Tisch sitzen.

Oder verstehe ich dich falsch?

Ich hatte mir gedacht, dass man die eine Zufallsverteilung mit Bedingungen benötigt, der alle Möglichkeiten prüft und die beste Verteilung ermittelt. Leider fällt mir nicht ein, welche Formel dafür in Frage kommt oder wie ich anfangen könnte. Habe schon lange Google nach allen möglichen Suchbegriffen durchforstet, aber dazu nichts gefunden.

Schönen Gruß

Christian

[DbSam]

Hallo Christian,

Oder verstehe ich dich falsch?

wahrscheinlich:

warum sortierst du die Liste, wenn sie vollständig ausgefüllt ist, nicht nach Klasse und dann noch nach Fach.
Es ist doch höchst unwahrscheinlich, dass es mehr als 15 Schüler aus einer Klasse bzw. mit dem gleichen Fach gibt.
Die laufenden Nummern der Liste kannst du vorher den einzelnen Sitzplätzen zuordnen;
also:
Nr 1 (der Liste) - Sitzplatz 1
Nr 2 (der Liste) - Sitzplatz 3
usw.

Finde ich gut, ist die simpelste Lösung. 



Gruß Carsten

[Ego]

Hallo

@Carsten und Conny,
ich glaube Christian hat recht.
a)Wenn ich die Liste erst nach Klasse und dann nach Fach ordne, ist der 16. Schüler zwar aus einer anderen Klasse als der 1.Schüler, kann aber das gleiche Fach haben.
b)Wenn ich die Liste erst nach Fach und dann nach Klasse ordne, hat der 16. Schüler zwar ein anderes Fach als der 1.Schüler, kann aber aus der gleichen Klasse sein.

@Christian
In der Anlage einmal eine programmierte Lösung.

Bemerkungen:
a) Vorraussetzungen
Da ich in Programmen nicht gerne mit festen Adressen arbeite benötigt das Programm zur Zeit zwei benannte Bereiche:
1) einen benannten Bereich "Liste"
Die Anzahl der Zeilen in diesem Bereich entspricht der Anzahl der Plätze = Anzahl der Tische * 2
Der Bereich umfasst mindestens 5 Spalten
1. Spalte eine Kennung des Schülers (zB laufende Nummer in dieser Liste)
2. Spalte Werte bis zur Anzahl der Schüler (von oben ohne Lücken). Aus dieser Spalte ermittelt das Programm die Anzahl der Schüler.
4. Spalte Klasse oder Fach
5. Spalte Klasse oder Fach
2) eine benannte Zelle "Ausgabe"
Unter dieser Zelle werden die Kennungen des Schülers ausgegeben. Jeweils zwei Plätze (von oben) gehören zu einem Tisch.
Die Zelle "Ausgabe" kann beliebig in der Datei positioniert werden (auch in ein anderes Arbeitsblatt) ohne das Programm anpassen zu müssen.

b) Zufallszahlen
Da die Positionen über Zufallszahlen ermittelt werden, müssen die Versuche eine gültige Lösung zu finden begrenzt werden. Es kann sein, dass das Programm mit den eingetragenen Grenzen keine gültige Lösung findet, obwohl es eine gibt.
Bei einem wiederholten Aufruf kann dann doch eine Lösung gefunden werden.
Ich weis nicht wie schnell dein Rechner ist, daher habe ich die die Grenze der Versuche relativ klein gemacht. Falls das Programm einmal keine Lösung findet und die entsprechende Meldung schnell ausgibt, könntest du im Programm die Konstante "intAnzVersuche" auch erhöhen, um nicht zu häufig manuell die Zuordnung neu anzustossen.

Code:

Option Explicit
Private Sub cbTuwat_Click()

Dim intPos As Integer
Dim intAnzPlaetze As Integer
Dim intAnzTische As Integer
Dim intAktTisch As Integer
Dim intAnzSchueler As Integer
Dim intAktSchueler As Integer
Dim intAktVersuch As Integer
Dim intAktTest As Integer
Dim varListe As Variant
Dim varPlaetze As Variant
Dim rngAusgabe As Range
Dim bolGefunden As Boolean
Dim collSchueler As Collection
Dim collTische As Collection
Const intAnzVersuche As Integer = 100
Const intAnzTests As Integer = 10
Dim dblStart As Double
dblStart = Timer

varListe = ThisWorkbook.Names("Liste").RefersToRange.Value
Set rngAusgabe = ThisWorkbook.Names("Ausgabe").RefersToRange

intAnzPlaetze = UBound(varListe, 1)
intAnzTische = intAnzPlaetze / 2
bolGefunden = True
intAnzSchueler = 1
While intAnzSchueler <= intAnzPlaetze And bolGefunden
    If varListe(intAnzSchueler, 2) = "" Then
        bolGefunden = False
    Else
        intAnzSchueler = intAnzSchueler + 1
    End If
Wend
intAnzSchueler = intAnzSchueler - 1
If intAnzSchueler < intAnzTische Then
    intAnzTische = intAnzSchueler
End If
Randomize
bolGefunden = False
intAktVersuch = 0

While Not bolGefunden And intAktVersuch < intAnzVersuche
    intAktVersuch = intAktVersuch + 1
    ReDim varPlaetze(1 To intAnzPlaetze, 1 To 2)
    Set collSchueler = New Collection
    For intAktSchueler = 1 To intAnzSchueler
        collSchueler.Add intAktSchueler
    Next intAktSchueler
    For intAktTisch = 1 To intAnzTische
        intPos = Application.WorksheetFunction.RandBetween(1, collSchueler.Count)
        varPlaetze(intAktTisch * 2 - 1, 2) = collSchueler(intPos)
        varPlaetze(intAktTisch * 2 - 1, 1) = varListe(collSchueler(intPos), 1)
        collSchueler.Remove (intPos)
    Next intAktTisch
    If collSchueler.Count > 0 Then
        Set collTische = New Collection
        For intAktTisch = 1 To intAnzTische
            collTische.Add intAktTisch
        Next intAktTisch
        intAktTest = 0
        intAktSchueler = 1
        While intAktSchueler <= collSchueler.Count And intAktTest < intAnzTests
            intPos = Application.WorksheetFunction.RandBetween(1, collTische.Count)
            If varListe(collSchueler(intAktSchueler), 4) <> varListe(varPlaetze(collTische(intPos) * 2 - 1, 2), 4) _
                And varListe(collSchueler(intAktSchueler), 5) <> varListe(varPlaetze(collTische(intPos) * 2 - 1, 2), 5) Then
                varPlaetze(collTische(intPos) * 2, 2) = collSchueler(intAktSchueler)
                varPlaetze(collTische(intPos) * 2, 1) = varListe(collSchueler(intAktSchueler), 1)
                collTische.Remove (intPos)
                intAktSchueler = intAktSchueler + 1
            Else
                intAktTest = intAktTest + 1
            End If
        Wend
        If intAktTest < intAnzTests Then
            bolGefunden = True
        End If
    Else
        bolGefunden = True
    End If
Wend

If bolGefunden Then
    rngAusgabe.Resize(intAnzPlaetze, 1) = varPlaetze
Else
    rngAusgabe.Resize(intAnzPlaetze, 1).ClearContents
    MsgBox "keine passende Zuordnung gefunden." & " " & Timer - dblStart
End If

End Sub

[coemm]

a)Wenn ich die Liste erst nach Klasse und dann nach Fach ordne, ist der 16. Schüler zwar aus einer anderen Klasse als der 1.Schüler, kann aber das gleiche Fach haben.
b)Wenn ich die Liste erst nach Fach und dann nach Klasse ordne, hat der 16. Schüler zwar ein anderes Fach als der 1.Schüler, kann aber aus der gleichen Klasse sein.

Hallo Helmut,
deshalb habe ich ja auch geschrieben:

Es ist doch höchst unwahrscheinlich, dass es mehr als 15 Schüler aus einer Klasse bzw. mit dem gleichen Fach gibt.

[Ego]

Hallo conny,

wenn bei 15 Tischen "mehr als 15 Schüler aus einer Klasse kommen  bzw. das gleiche Fach haben", gibt es keine Lösung, die die Bedingungen erfüllen.

Bei deinem Lösungsvorschlag ist aber die Wahrscheinlichkeit für eine fehlerhafte Lösung schon bei 1/15 falls nur einer der hinteren (>15) Schüler das gleiche Fach hat wie einer der vorderen Schüler.