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

[Gast 123]

Halo

Aber diese Prozedur ist für das erwünschte Ergebnis untauglich.      Das sehe ich auch so!!

@Uwe   Danke für die Aufklärung über Array, das ist mir immer noch ein Buch mit 7 Siegeln.
ich hatte mir gestern mal Gedanken gemacht, die Kinder mal nach Geschlecht und Alter zu sortieren.
Vielleicht hilft uns diese Sortierung auch weiter die Aufgabe zu lösen??

Heute fiel mir auf, das einige Fragen im Beispiel beantwortet sind. s. Auswertung Spalte H2-H6, P3-P6, T3-T6
Ich habe zwar noch keinen Plan, wie man das lösen kann, aber die Vorgaben sind dort genannt worden.
Muss noch mal drüber schlafen.  Manchmal fällt einem dann spontan etwas ein.

mfg Gast 123

[Ralf A]

...immer wieder schön zu sehen, da wird ein Post eröffnet, dann werden nicht nur die Fragen nicht beantwortet, es kommt überhaupt keine Reaktion mehr seitens des TO...
Nun ja, da die Fragen nicht beantwortet wurden, habe ich, weil mich das Problem interessierte, eben mal 2 Versionen nach meinen Vorstellungen mittels PQ erstellt.
In Version 1, im Blatt Spielegruppen, wurde zwar die annähernde Geschlechterverteilung berücksicht, aber die Gruppenstärke nicht.
In Version 2, im Blatt InAbhängigkeitAnzahlSpielgruppe, der umgekehrte Fall behandelt.
Kann ja beides kombiniert, angepasst werden. Ich betrachte den Fall jetzt allerdings für mich als abgeschlossen...


  Zeltlager_Spielegruppen_PQ.xlsm (Größe: 87 KB / Downloads: 7)

[Gast 123]

Hallo Andy

ich kam auf die Idee mir deine Daten zuerst mal sinnvoll zu sortieren. Schau dir mein Liste bitte selbst an.
Nachdem ich mir die Bedingungen noch einmal genau angeschaut habe, gibt es nichts zu programmieren!

Wie du in meinen sortierten Spalten K-O selbst sehen kannst, ist die Forderung von 0,9 Toleranz der Gruppengrösse schon im 1. Fall -NICHT- erfüllbar!  Zwei Jungs mit 7,1 stehen zwei Mädchen mit 8,7 gegenüber. Das passt schon nicht! Die weitern Daten sind durch die Tolleranz ebenfalls sooo eng begrenzt, das man hier nicht mehr von Zufallsprinzip sprechen kann!

Mein Rat an Euch, nehmt meine sortierte Tabelle, und setzt euch mit den Jugendlichen locker an einen Tisch.
Dann besprecht mit ihnen wer mit wem eine Spielerparrung bilden will.  Dafür braucht man wirklich -kein KI- mehr!
Da reicht der gesunde Menschenverstand, und die Bereitschaft sich untereinander gütig zu einigen.

Ich spreche da aus Erfahrungen, denn ich habe mit Behinderten gearbeitet.
Die freuten sich, wenn solche Themen in der Gruppe offen besprochen wurden.
Damit beende ich diesen Thread, denn bei den engen Toleranzen gibt es nichts zu programmieren!

mfg Gast 123

[AH-Lohmoar]

Hallo Ralf,
Danke für deine Bemühungen.
Ich fang mal von hinten an.
4. Ja wir wollen die kleinen mit den großen mischen, nur die Partner sollen fix zusammen bleiben. Darum habe ich die Minigruppen erstellt um diese aneinander zu binden.
5. Minigruppen sind nur die fixen Partner. Die Spielegruppen sollen möglichst homogen aus den Minigruppen gebildet werden.
2. Es wünst sich nicht jeder irgendwen sondern die 2 bis 3 die zusammen wollen, tragen sich zusammen in eine Minigruppe ein. Diese werden unter den Zeltbewohnern gebildet.
1. aus den in den genenerierten Spielegruppen und den darin enthaltenen Minigruppen soll man dann die einzelnen Kinder ausgeben können.

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:

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.

---

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).

Danke für die Ausführliche Info.

Ich verstehe zwar nur Bahnhof aber ich werde die Datei gerne mal testen.

Wünschenswert wäre eine Berechnungszeit unter 10min. Die Gruppen müssen auch nicht 100% perfekt sein. Das waren sie die letzten 20 Jare, in denen wir das per Hand erledigt haben auch nicht. Wir wollen uns halt Arbeit sparen.

---

Hallo Andy

ich kam auf die Idee mir deine Daten zuerst mal sinnvoll zu sortieren. Schau dir mein Liste bitte selbst an.
Nachdem ich mir die Bedingungen noch einmal genau angeschaut habe, gibt es nichts zu programmieren!

Wie du in meinen sortierten Spalten K-O selbst sehen kannst, ist die Forderung von 0,9 Toleranz der Gruppengrösse schon im 1. Fall -NICHT- erfüllbar!  Zwei Jungs mit 7,1 stehen zwei Mädchen mit 8,7 gegenüber. Das passt schon nicht! Die weitern Daten sind durch die Tolleranz ebenfalls sooo eng begrenzt, das man hier nicht mehr von Zufallsprinzip sprechen kann!

Mein Rat an Euch, nehmt meine sortierte Tabelle, und setzt euch mit den Jugendlichen locker an einen Tisch.
Dann besprecht mit ihnen wer mit wem eine Spielerparrung bilden will.  Dafür braucht man wirklich -kein KI- mehr!
Da reicht der gesunde Menschenverstand, und die Bereitschaft sich untereinander gütig zu einigen.

Ich spreche da aus Erfahrungen, denn ich habe mit Behinderten gearbeitet.
Die freuten sich, wenn solche Themen in der Gruppe offen besprochen wurden.
Damit beende ich diesen Thread, denn bei den engen Toleranzen gibt es nichts zu programmieren!

mfg Gast 123

Hallo Gast 123,

Danke für deine Ausführungen, Wir erstellen die Gruppen seit 20 Jahren nach diesem Prinzip, was einwandfrei immer funktioniert hat. Wir würden das nr gerne automatisieren. 160 Kinder mitreden lassen wer alles zusammen in eine Spielegruppe kommt ist zeittechnisch sicher kontraproduktiv.

Trotzdem danke für deine Zeit.

Gruß Andy

[AH-Lohmoar]

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:

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.

...immer wieder schön zu sehen, da wird ein Post eröffnet, dann werden nicht nur die Fragen nicht beantwortet, es kommt überhaupt keine Reaktion mehr seitens des TO...
Nun ja, da die Fragen nicht beantwortet wurden, habe ich, weil mich das Problem interessierte, eben mal 2 Versionen nach meinen Vorstellungen mittels PQ erstellt.
In Version 1, im Blatt Spielegruppen, wurde zwar die annähernde Geschlechterverteilung berücksicht, aber die Gruppenstärke nicht.
In Version 2, im Blatt InAbhängigkeitAnzahlSpielgruppe, der umgekehrte Fall behandelt.
Kann ja beides kombiniert, angepasst werden. Ich betrachte den Fall jetzt allerdings für mich als abgeschlossen...

Hallo Ralf,

Tut mir leid, Ich habe nicht mit so schnell soooo vielen Antworten gerechnet und war die letzten Tage beruflich, gesundheitlich und mit dem Hof zuhause recht eingespannt. Außerdem tu ich mich gerade noch schwer mich hier zurecht zu finden. Gib mir etwas zeit, dann schaue ich das Ganze gerne an und gib dan adäquates Feedback.

---

@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

Hallo Uwe,

Danke für die Bemühungen, Du hast das richtig verstanden mit den drei Annäherungen. Jeweils aus den Minigruppen die fest zusammen bleiben sollen.

Gruß Andy

[Gast 123]

Hallo Andy

erst mal gute Besserung für deine Gesundheit.  Ich warte mal ab was die Begutachtung der sortierten Liste erbringt.
Wenn ihr da noch eine Möglichkeit seht das per Code zu optimieren können wir es versuchen. 
Wenn es aber nur 2,3 Kombinationen gibt kann man das eher an Hand der Liste manuell entscheiden.

mfg Gast 123