Nicht sortieren, sondern Mischen

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Sub Mischen() Application.ScreenUpdating = False Range("B1:B" & Cells(65536, 1).End(xlUp).Row).FormulaR1C1 = "=RAND()" Range("A1:B" & Cells(65536, 1).End(xlUp).Row).Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Columns("B:B").ClearContents End Sub

Hallo, Damiel,
wie ich in meiner ersten Antwort schon darzulegen versuchte, sehe ich einen Ziel- bzw. Definitionskonflikt, wenn versucht werden sol, sowohl die Schwankung des Durchschnittes in einer Teilgruppe aufeinanderfolgender Werte zu minimieren und gleichzeitig die Differnz benachchbarter Werte in der der neuen Gruppierung zu maximieren.
Zwei Extrembeispiele:

Du hast eine Anzahl von Personen, die jeweils ein Jahr Altersunteschied aufweisen.
Eine minimale Schwankung des Durchschnittes des Alters von jeweils zwei aufeinanderfolgenden Personen in der neugruppierten Reihenfolge erhälst Du nur dann, wenn Du jeweils die Personen in der Umgruppierung nebeneinander stellst, die in der sortierten Reihenfolge den gleichen Abstand zu Mitte haben.
Bei Beachtung dieser Forderung zur Neugruppierung, kann jedoch nicht erreicht werden, dass der Abstand der Personen, die in der sortierten Gruppe nebeneinander standen, nun für alle größtmöglich wird in der Neugruppierung, da ja am Ende der neuen Reihe wieder die Personen zusammenstehen, die in der sortierten Reihe in der Mitte zusammen standen.
Du hast eine sortierten Gruppe, die zur Hälfte aus 30 und zur anderen Hälte aus 60jährigen Personen bestehen.
Bei dieser Gruppe können beide Forderungen erfüllt werden.
Der Durchschnitt einer Teilgruppe der neuen Reihe wird maximal zwischen zwei Werten schwankt bei einer ungeraden Anzahl zur Durchnittsbildung bzw. die Schwankung wird gleich Null sein, bei einer geraden Anzahl zur Durchschnittsbildung.
Das Neuordnungsprinzip, jeweils eine Person aus der unteren Hälte der sortierten Gruppen neben einer Person aus der oberen Hälfte der sortierten Gruppe in der Neuordnung nebeneinander zu stellen, führt zu den maximal größten Altersunterschiden nebeneinanderstehender Personen (Platz in der Ursprungsreihe ist hier unbedeutend, es ist also egal, ob der 30jährige, der in der Urprungsreihe neben dem 60jährigen satan nun auch wieder in der neugruppierten Reihe wieder neben dem gleichen 60jährigen steht).

Es ist der von Dir beschreibene Gedanke, wonach einen sortierte Stapel Karten, in der Mitte geteilt und dann abwelchelnd eine Karte von jedem Stapel hintereinander zu einen neuen Gesamtspael zu sammen zuführen. Wenn Du nun feststellst, dass dieses Verfahren keine gute Durchmischung bringt, so ist das richtig, war aber auch nit in durch Deine beiden Forderungen expliziet gefordert.
Um bei dem Kartenstapel zu bleiben, könntest Du nun erneut wieder den Stapel halbieren und durch wechselweises zusammenstellen je einer Karte jedes Stapels zu einem neuen Gesamtsapel, einen weiteren Grad in der Mischung erhalten. Dieser Stapel wird aber nicht mehr den Maximalabstand aller nun aufeinanderfolgender Karten Karten haben, die im Ausgangsstapel nebeneinander lagen. Ein teil wird wieder dichter beisammen liegen als bei der ersten Mischvorgang, ein Teil wird seinen Abstand vergrößern.
Die Ergebnisse können schrittweise manuell mit der Tabelle nachvollzogen werdne (habe ich noch nict durchgeführt, sollte aber klappen). Dazu zuvor die Erläuterung der Spalteninhalte:
Spalte A: Ordnungsnummer, für die Betrachtung uninteressant
Spalte B: Sortierte Liste im Schritt 0
Spalte C: Differenz benachbarter Werte in Spalte B
Spalte D: Mittelwert, gebildet aus der Anzahl "LängeGD" aufeinanderfolgender Werte in der Spalte B
Spalte F: Umsortierte Liste im Schritt 1
Spalte G: Differenz benachbarter Werte in Spalte F
Spalte H: Mittelwert, gebildet aus der Anzahl "LängeGD" aufeinanderfolgender Werte in der Spalte F

Liste in Spalte B liefert umorientierung in Spalte F
Kenngrößen aus den Auswertungszellen J4:K13 spaltenweise in einer neunen Zeile wertmäßg kopieren
Spalte F markiren und wertmäißig ("Inhalt einfügen...") in Spalte B einfügen.
mit Punkt 1 fortfahren.

M.E. wird die Folge der im Schritt 2 zeilenweise untereinander notierten Zeilen zeigen, dass in der Regel immer nur eine Forderung bestens erfüllt werden kann, wenn nicht der besondere Fall eine bestimmten Datenstruktur gegeben ist (s.o., wo die Gesamtgruppe je zur Hälte aus gleichaltirigen besteht).
Natürlich kann man den manuellen Algorithmus auch in VBA programmieren oder als Makro aufzeichnen) und dann solange durchführen, bis man meint, Kritieren für die Optimierung beider Forderungen entwickelt zu haben.
Als Studie, könnte der Mechanismus des Mischens durch eine zufallsorientierte Platzfolge der Werte aus Spalte B in Spalte A ersetzt werden.
Wie beschrieben, meine Überlegung, die experimentell nicht geprüft ist, ist die, dass nur der erste Schritt des nicht zufallsorientierten Mischens zur optimalen Kenngrößen beider Forderungen führen kann.
Mich würde also das endgültige Ergebnis auch interessieren und vor allem, auf welchem Gebiet und für welche Aufgabe eine derartige spezielle Mischung gefordert wird.
Gruß,
Uwe

Auswertungsliste: Schritt maxGD minGD Spanne kl. Diff gr. Diff 0 [J8] [J9] [J10] [J12] [J13] 1 [K8] [K9] [K10] [K12] [K13] 2 [K8] [K9] [K10] [K12] [K13] 3 [K8] [K9] [K10] [K12] [K13]

Hallo, Daniel,
wenn Du Deine Datenmengen so organisieren kannst, dass immer eine Anzahl ausgwertet wird, die eine 2er Potenz ist, dann hast Du damit immer die bestmöglichste Durchmischung gemäß Deinen Kritierien.
Es gibt nach meinen Vorstellungen genau zwei Kenngrößen:

die Spanne der gleitenden Durchnitte (SpanneGD; bei Deiner Sortierung mit LenGD=10 wird SpanneGD=18,9)
die "Wegstrecke", die durch die Summe der Absolutbeträge der Differenzen zwischen zwei benachbarten Werten gemessen wird (Iter; bei Deiner Sortierung Iter=32640)

Für den sortierten Orginaldatensatz ergeben sich die Werte SpanneGD=246 (wieder LenGD=10) und Iter=255.
Die Zahlenfolge n_i+1 = n_i + 1 liefert als Summe maxN*(maxN+1)/2, die bei maxN=256 gerade 32640.
Wenn man nun von jeder Durchmisschung die relativen Kennprößen zur unsortierten Reihe ermittelt, also
relSpGD = SpanneGD_gemischt / SpanneGD_sortiert
relIter = 1 / (Iter_gemischt / Iter_sortiert)
so ist wohl allgemein die Durchmischung zu wählen, die Minialwerte für diese Kenngrößen liefert. In der Regel wird es jedoch hier zu dem eingangs erwähnten Zielkonflikt kommen, da nur in spreziellen Fällen die Miniamlwerte für die gleiche Durchmischung auftreten.
Im Falle der "Potenz2"-Durchmischung trifft dieser Fall, das für beide relativen Kenngrößen ein Minimum ermittelt wird, wohl zu (relSpGD = 0,0768..; relIter = 0,0078125, in verk. Schreibweise: {0,0768; 0,0078125}.
Ein Verteilung mit gleichverteilten Zufallszahlen liefert bei ca. 1000 Versuchen minimalwerte, von dennen nur ein verschwindend geringer Teil unter dem Wertepaar {0,3; 0,01}, was einer SpanneGD von SpanneGD = 0,3*246 = 64,5 und einem Weg von Iter = 255/0,01 = 25500 entspricht.
Doch sofern die Anzahl der Daten durch eine 2er-Potenz 2^p mit p>0 ganzzahlig teilbar ist, liefert der von Dir aufgestellte Algorithmus die eindeutige Lösung.
Dennoch, es war eine interesante Aufgabe, so fand ich.
Gruß,
Uwe

Beliebteste Forumthreads (12 Monate)

17 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

17
Beiträge zum Forumthread

Beiträge zu diesem Forumthread