Zeltlager Spielegruppen mit annähernd gleich vielen Mädchen & Jungs im gleichen Alter

[AH-Lohmoar]

Hallo zusammen,

Ich bin bei weiten kein Excel Profi, darum habe ich mich in diesem Forum angemeldet und offe dass mir jemand helfen kann.

Wir veranstalten für das JRK (Jugend Rot Kreuz) jedes jahr ein Zeltlager mit bis zu 200 Teilnehmenden Kindern. Die Kinder sind geschlechter getrennt in verschiedenen Zelten untergebracht mit ca 8 bis 16 Personen im Zelt, je nach Zeltgröße. Um aber für unsere Spieleangebote ungefähr gleich starke Spielegruppen zu erstellen, verbringen wir jedes Jahr im Zeltlager vorab schon mal viel Zeit für diese Gruppeneinteilung, die uns eigentlich zur Bespaßung der Kinder in dieser Zeit fehlt. Darum hier die Frage ob dazu jemand eine Lösung weiß.

Zur erklärung wie wir aktuell vorgehen:
Wir teilen in die Zelte Zettel aus, bei denen sich immer zwei bis drei Kinder zusammen schreiben die auch zusammen in einer Spielegruppe bleiben wollen.
Aus diesen Minigruppen werden dann Spielegruppen zusammen geschrieben, die annähernd gleich viele Jungs und Mädchen beinhalten, deren Durchschnittsalter auch ähnlich ist. und dass möglichst nicht mehrere minigruppen aus einem Zelt zusammen in eine Spielegruppe kommen.

Gibt es eine Möglichkeit dies zu gestalten? mit der KI haben wir das auch schon versucht und sind kläglich gescheitert.
Im Anhang die Testdatei, mit Phantasie Namenund Geburtsdaten um Datenschutztechnisch keine Probleme zu bekommen.
In der Datei auch die KI generierte VBA, die auch nicht funktioniert.

Über Hilfe für unser Ehrenamt wäre ich unendlich dankbar.

Grüße Andy

  Zeltlager_Spielegruppen.xlsm (Größe: 56,49 KB / Downloads: 20)

[ws-53]

Im mof, das es seit ein paar Wochen nicht mehr gibt, gab es dazu von Oge sehr gute Lösungen. Aber da er auch hier aktiv ist, besteht für dich die große Chance, dass er diese hier auch noch einmal einstellt.

[Gast 123]

Hallo Andy

da bist du hier sicher an der richtigen Adresse. 
Mein längster Thread zum Thema Spielparungen waren 128 Frage/Antworten!
Das gab damals hitzige Diskussionen mit Kollegen bezüglich vieler Lösungsvorschläge!

Sieben ineinander verschachtelte for Next Schleifen, woran ich fast 2 Monate programmierte.
Die Formatierte Bedingung des Kollegen Rabe brachte mir den Durchbruch für eine VBA Lösung.

Ehrenamtlich, da bin ich als alter Mann (74) aus Izmir/TR gerne mit dabei.
Ich muss nur erst mal die Aufgabenstellung richtig verstehen. Schaue mir dein Beispiel an.
Melde mich, wenn ich da weitere Fragen habe.  Gib mir aber bitte genug Zeit dafür.
Ich habe auch familiär genug Verpflichtungen, 2 grosse Hunde, 4 Katzen, u.a. Aufgaben.

mfg Gast 123

[ws-53]

...   2 grosse Hunde ...

Dann passe nur gut auf, dass die nicht auch nur ansatzweise rebellisch werden, da sonst deren Verhaftung droht.

[Ralf A]

Zur erklärung wie wir aktuell vorgehen:
Wir teilen in die Zelte Zettel aus, bei denen sich immer zwei bis drei Kinder zusammen schreiben die auch zusammen in einer Spielegruppe bleiben wollen.
Aus diesen Minigruppen werden dann Spielegruppen zusammen geschrieben, die annähernd gleich viele Jungs und Mädchen beinhalten, deren Durchschnittsalter auch ähnlich ist. und dass möglichst nicht mehrere minigruppen aus einem Zelt zusammen in eine Spielegruppe kommen.

Da bleiben noch ein paar Fragen offen...
1. Ihr teilt Zettel aus, auf denen die Kinder ihren Wüsche bzgl. Spielgefährten äußern dürfen. Ich nehme an, dass sind die in Spalte Partner stehenden Werte? Leider lässt sich damit nicht viel anfangen. Da müssten dann schon die Namen oder die ID der Wunschpartner stehen, sonst ist eine korrekte Zuordnung unmöglich. Oder bedeutet das was ganz anderes?
2. Was, wenn ein Kind von ganz vielen gewünscht wird oder von niemandem? Prämissen sind dann vermutlich Alter und Geschlecht?
3. Was willst Du mit dem Durchschnittsalter? Das nutzt doch nix. Besser wäre meines Erachtens nach das gerundete Alter, also 7 und 8 Jahre oder 16 und 17 plus minus ein paar Monate. Also Altersgruppenmäßige Aufteilung.
4. Vielleicht hab ich das aber auch falsch verstanden und Ihr wollt wirklich die Kleinen mit den Großen mischen?
5. Welcher Unterschied besteht zwischen Mini- und Spielegruppe? Und welche Rolle spielt dabei die Zeltnr? 
6- Das hier:

...und dass möglichst nicht mehrere minigruppen aus einem Zelt zusammen in eine Spielegruppe kommen...

ist ein wenig missverständlich formuliert. Ich verstehe das so, ihr wollt möglichst homogene Minigruppen in eine gemeinsame Spielegruppe. Ist das korrekt? Mit der Ausnahme, dass der Mädchen-/Jungenanteil  ungefähr gleich groß sein soll.

[Ego]

Hallo Andy,

A) Lösung im MS-Office Forum
Die von ws... vorgeschlagenen Lösungen aus mof passen nicht zu deinem Problem.

B) Allgemein
Für eine Zuordnung mit Grenzen und Bewertungen wird für Rechner eine rechnerische Optimierung genutzt (da Rechner eben nur rechnen können).
Hierfür müssen 
a) Grenzwerte die unbedingt eingehalten werden müssen definiert werden
b) und eine Bewertungsfunktion aufgestellt werden.

Bei den Grenzwerten sollte man beachten welche Grenzwerte unbedingt einzuhalten sind und welche wünschenswert wären. Das Verletzen der letzteren sollte in der Bewertungsfunktion berücksichtigt werden.
Bei der Berücksichtigung von verschiedenen Kriterien in der Bewertungsfunktion (wie in eurem Fall) ist die richtige Gewichtung der unterschiedlichen Kriterien nicht einfach aber wichtig.

C) Excel Bordmittel (Solver)
Für solche optimierungen hat Excel eine eigene Funktionalität, den Solver.
In der Anlage habe ich eine erste Version eines Solverproblems erstell. Hier noch einige Bemerkungen:

1. Lösungsalgorithmus
Der Solver hat drei unterschiedliche Lösungsalgorithmen. Da die Entscheidung ob eine Minigruppe zu eine Spielegruppe gehört aber binär ist, kann man nur den Algorithmus "EA (Evolutionärer Algorithmus)" nutzen.
a) Dieser Algorithmus findet bei komplexeren Problemen selten die optimale Lösung aber häufig ein gutes Ergebnis.
b) Da der Algorithmus viel über zufällige Variationen der Zuordnungen arbeitet, muss man ihm mitteilen wann er keine weitere Versuche mehr durchführen soll. Beim Solver geschieht dies über ein Vorgabe der Zeit in der der Solver keine Verbesserung gefunden hat. 
Je länger diese Zeit ist, desto wahrscheinlicher ist es, dass die Lösung besser wird.
Andererseits kann man den Solver (ohne schon einmal optimierte Zuordnungen) mehrmals aufrufen und das beste Ergebnis wählen.

2. Das aktuelle Problem in der Anlage
a) Ich habe die Abweichung der Geschlechterverteilung der Spielegruppen von der Geschlechterverteilung des Zeltlagers optimiert (Minimierung der Summe der Quadrate).
Zusätzlich habe ich Verletzungen der Grenzwerte pönalisiert.
b) In Spalte H sind Zuordnungen der Minigruppen zu den Spielegruppen, die vom Solver variiert werden. 
In den Spalten L:S werden daraus die notwendigen Bewertungsparameter ermittel und in den Spalten U:Z wird der Wert der Bewertungsfunktion berechnet.
In den Spalten AB:AG habe ich einige berechnete Zuordnungen gesichert. Durch kopieren dieser Sicherung auf die Spalte H kann man sich die Bewertung genauer anschauen.
c) Zeiten
Wenn ich dem Solver bei der "Zeit ohne Verbesserung" "nur" 120 Sekunden vorgegeben habe wurden nicht alle Grenzwerte eingehalten. Bei beiden Berechnungen mit einer Vorgabe von 300 Sekunden wurden die Grenzwerete in dem Beispiel eingehalten.
Achtung! der Solver läuft also bei der aktuellen Einstellung mehr als 5 Minuten.
d) Achtung !
Ich habe die Altersverteilung der Spielegruppen noch nicht bewertet. Ich hätte dies zwar machen können, diese Bewertung und das Verhältnis zu der Bewertung der Gesschlechterverhältnisse sollte aber von euch kommen.

D)  Optimales Ergebnis
Wenn euch das Ergebnis der Solverberechnung (nach Erweiterung um Altersverteilung) reicht, ist alles ok.
1. Programm brute force
Ansonsten könnte man ein Makro schreiben, dass mit brutaler Rechenpower versucht eine optimale Lösung zu finden.
Auf heutigen PCs kann man zwar nicht alle mögliche Variationen in einem Jahr berechnen, aber nach meinem Verständnis der Bedingungen kann man durch geschickte Programmierung viele Berechnungszweige vorzeitig abbrechen.
Ich glaube es ist auf heutigen PCs möglich bei der angegebenen Problemgrösse das Optimum  in vertretbarer Zeit zu ermitteln.
2. Zu der von Gast... angegebenen Zeit
Falls die Vorgaben vernünftig erstellt und nicht zu häufig geändert werden, erwarte ich eher einen Aufwand von 1-3 Tagen anstelle der "fast 2 Monate" (da diese Art der rekursiven Berechnungen doch immer gleich sind).

[Gast 123]

Hallo

@Ego   Oha, Solver, da schwant mir böses, habe ich noch nie gemacht, erinnere mich aber.
Da gab es Anfragen für Rohrzuschnitte oder Bretter auf Längen sägen. Da wirds kompliziert.
Muss mir das ganze noch mal in Ruhe ansehen, aber bei Solver, da bin ich außen vor.

mfg Gast 123

---

Nachtrag   ich dachte das wäre eine reine Verteilung nach dem Zufallsprinzip.  Liege ich da völlig falsch??

[Gast 123]

Hallo

ich habe mir das Beispiel angesehen und sehe das es bereits ein Makro gibt.  Sub SpielegruppenAusMinigruppen()    
Dazu ein professioneller Code, bei dem ich mit meinem bescheidenen Wissen nicht mitkomme.
Leider lief er bei mir sofort in eine Fehlermeldung.  Könnt ihr den Code selbst berichtigen???
Der Fehler kommt bei:  eintrag = Array()   da blicke ich aber nicht durch was das Array macht??

mfg Gast 123

Code:

    ' Minigruppen erfassen
    For i = 4 To 103
        eintrag = Array( _
            ws.Cells(i, 1).Value, _
            CDbl(ws.Cells(i, 2).Value), _
            CLng(ws.Cells(i, 4).Value), _
            CLng(ws.Cells(i, 5).Value) _
        )
        minigruppen.Add eintrag
    Next i
 

---

Nachtrag   ich sehe gerade in #1 das der schlaue Programmierer KI war.  Und die machen Fehler??
Vielleicht läuft der Code ja auf moderneren Versionen als meine Excel 2016 Version??

Frage:   Wieviele Spielgruppen bildet ihr und aus wievielen Kindern besteht jede Spielgruppe??
Mir reichen ungefähre Angaben damit ich die Aufgabenstellung annähernd abschätzen kann.

Nun ja,   das ist echt amüsant.  Ein alter Mann, Volksschüler, soll es mit -KI und Solver- aufnehmen??
Wir werden sehen was ich drauf habe, -harte Nüsse- knacken hat mich schon immer gereizt.
Geht auch schon mal daneben, musste auch mal das Handtuch werfen. Ich kann sie noch zählen!

mfg Gast 123

[Egon12]

Hallo Gast123,

Die Ursache für diesen Fehler sind leere oder falsch gefüllte Zellen. So liest man ein Array nur ein, wenn die Zellen sauber gefüllt sind, was hier vermutlich nicht der Fall ist. Das Einlesen erfolgt mit Konstanten Cdbl --->Zahl und Clng ---> Datumszahl.

Das kann ich erst später nachschauen, da ich grad nur mit dem Tablett hier ins Forum schaue.

Gruß Uwe

[Egon12]

@Gast123,

falls es dich interessiert, wie man solche Fehler verhindert, wäre dies dann beispielhaft so:

Code:

ReDim eintrag(0 To 3)
    Dim nTmp, cTmp1, cTmp2
    For i = 4 To 103
        If IsNumeric(wsDaten.Cells(i, 2)) Then nTmp = CDbl(wsDaten.Cells(i, 2).Value)
        If IsNumeric(wsDaten.Cells(i, 4)) Then cTmp1 = CLng(wsDaten.Cells(i, 4).Value)
        If IsNumeric(wsDaten.Cells(i, 5)) Then cTmp2 = CLng(wsDaten.Cells(i, 5).Value)
        eintrag = Array(wsDaten.Cells(i, 1).Value, nTmp, cTmp1, cTmp2)
        minigruppen.Add eintrag
    Next i

Allerdings macht es keinen Sinn, da die Formeln in Spalte D und E Zahlen bzw. leer ausgeben. Des Weiteren gibt die Formel in wsDaten.Cells(i, 2) m oder w zurück.
Somit reicht:

Code:

eintrag = Array(wsDaten.Cells(i, 1).Value, wsDaten.Cells(i, 2).Value, wsDaten.Cells(i, 4).Value, wsDaten.Cells(i, 5).Value)

völlig.
Da in B "m" bzw "w" ausgegeben wird wird es wohl an anderer Stelle zu Fehlern kommen.

Aber diese Prozedur ist für das erwünschte Ergebnis untauglich.

Wenn ich es richtig verstanden habe braucht diese Sache mindestens 3 Annäherungsalgorithmen.
- annähernd gleiches Alter
- in etwa gleiche Geschlechterteilhabe
- annähern gleich große Gruppenpaare im Bereich zwischen Gruppenminimum und Gruppenmaximum

Kann aber durchaus sein, dass ich es falsch verstanden habe.


Gruß Uwe