Zufallszahlen ohne duplikate zuordnen

[Sören9999]

Hallo zusammen,

da mir schon einmal so gut geholfen wurde, dachte ich, ich nerve nochmal mit einem anderen Problem.

Für eine relativ umfangreiche Tabelle habe ich Zufallszahlen im Bereich von 1 bis 60 erstellt (mit sechs Ausnahmen, die nicht berücksichtigt werden sollen) und das Ganze natürlich ohne Duplikate. Diese Zahlen sollen nun anderen Zellen (Personennamen) zugeordnet werden, wenn diese bestimmte Kriterien erfüllen.
Aktuell sieht mein Lösungsansatz wie folgt aus:

=WENN(UND(U32*24>5;U32*24<9,5;S32="Rot");INDEX('PT-Zahlen'!$F$1:$F$54;ZUFALLSBEREICH(1;54))

Ich prüfe also, ob die Bedingungen erfüllt werden und wenn dem so ist, lasse ich per Zufall eine Zahl aus dem zufällig zusammengewürfelten Pool zuordnen. Hierbei ist das Problem aber, dass nun natürlich Zahlen teilweise doppelt gezogen werden.

Hätte hier vielleicht jemand einen besseren Vorschlag, wie sich die Zellen die Zufallszahlen ohne Duplikate ziehen kann?
Eine direkte Zuordnung der Zufallszahlen zu den einzelnen Zellen ist nicht möglich, da sich die Bedingungen Woche für Woche ändern werden und die Zahlen deshalb immer wieder neu zugeordnet werden müssen.

Vielen Dank im Voraus für jedweden Einfall.
Grüße

[steve1da]

Hola,

verlinkst du bitte deine Fragen in den verschiedenen Foren untereinander?
Danke.

Gruß,
steve1da

[Sören9999]

Natürlich.

Also hier der Verweis: http://www.ms-office-forum.net/forum/showthread.php?p=1841657&posted=1#post1841657

Und bevor die Schläge von allen Seiten kommen: mir wurde schon des Öftern durch Lesen bestehender Einträge geholfen und nicht durch eigene Posts und ja, ich weiß, dass Copy+Paste nicht die feine Englische Art ist, bin aber am Verzweifeln, weshalb ich versuche, mir viele Eindrücke/Meinungen einzuholen.

Vielen Dank!

[RPP63]

Nun, Du darfst Dich nicht wundern, wenn wir uns wegen des ersten Satzes veräppelt fühlen.
Hilfsspalte mit Zufallszahl(), NICHT Zufallsbereich.
So ermittelt man 8 eindeutige Zufalls-Ganzzahlen aus 20:

Tabelle1

	Q	R
1	0,72318615	6
2	0,343178097	16
3	0,408961645	14
4	0,561509188	9
5	0,75737468	4
6	0,199438357	19
7	0,708146416	7
8	0,235270903	18
9	0,170619182
10	0,734774921
11	0,418534071
12	0,823362331
13	0,589411803
14	0,91973942
15	0,363384336
16	0,49746503
17	0,545048958
18	0,960577634
19	0,291184211
20	0,554404878

Formeln der Tabelle

Zelle Formel Q1 =ZUFALLSZAHL() R1 =RANG(Q1;Q:Q)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Gruß Ralf

[WillWissen]

Hallo Sören,

ich habe gute Erfahrungen gemacht, wenn der Zufallsbereich sehr groß benannt wird (z.B. 1 - 1.000.000). Hierbei treten so gut wie nie doppelte Werte auf. Ob das auf deine Datei angewendet werden kann, weiß ich natürlich nicht - ich kenne sie ja nicht :32:

[RPP63]

Genau deshalb nehme ich einen Zufallsbereich von 1 bis 999.999.999.999.999
also 1 Billionen Kombis …

[Sören9999]

Wie gesagt: ich wollte niemandem absichtlich auf den Schlips treten.

Vielen Dank für die Antworten. Der Vorschlag mit der Zufallszahl ist sicherlich eleganter als der Weg, mit dem ich die Zahlen ermittelt habe, bringt mich aber nur bedingt weiter. Der nächste Schritt, also das Verteilen der 54 Zahlen, auf eine sich ändernde Anzahl von Zellen, ohne dass eine Zahl doppelt verteilt wird, stellt mich vor das große Problem.

Da meine Zahlen im Bereich von 1 bis 60 bestehen bleiben müssen, ist leider auch die Idee mit dem großen Zufallsbereich keine Lösung.

Wenn Interesse bestünde, könnte ich die Datei zur Veranschaulichung hochladen?

Grüße
Sören

[WillWissen]

Hi Sören,

das mit der Tabelle ist eine gute Idee. Solltest du sensible Daten drin haben, dann anonymisiere diese bitte. Auch reichen einige wenige Datensätze in der Regel aus; wichtig ist nur, dass der Aufbau der Beispieltabelle deinem Original entspricht. Falls du nicht weißt, wie die Datei hochgeladen werden kann, dann lies bitte hier nach: http://www.clever-excel-forum.de/thread-326.html

[Bamberg]

Servus Sören,

würdest Du auch eine VBA Lösung nehmen?

Hier der Code für ein allgemeines Modul:

Code:

Option Explicit

Public Function MeineZufallszahl(ByVal Von As Long, _
                                ByVal Bis As Long, _
                                Optional ByVal BereitsVerwendet As Variant) As Variant
                               
   Dim Dic As Object, Eintrag As Variant
   Dim i As Long, j As Long, k As Long
   Dim Arr As Variant
   
   MeineZufallszahl = CVErr(xlErrValue)
   
   Randomize
   
   Set Dic = CreateObject("Scripting.Dictionary")
   
   For i = Von To Bis
       Dic(i) = i
   Next i
   
   If Not IsMissing(BereitsVerwendet) And TypeName(BereitsVerwendet) = "Range" Then
       Arr = BereitsVerwendet.Value
       If Not IsArray(Arr) Then
           ReDim Arr(1 To 1, 1 To 1)
           Arr(1, 1) = BereitsVerwendet.Value
       End If
   
       For i = LBound(Arr, 1) To UBound(Arr, 1)
           For j = LBound(Arr, 2) To UBound(Arr, 2)
               k = Val(Arr(i, j))
               If k <> 0 Then
                   If Dic.exists(k) Then
                       Dic.Remove k
                   End If
               End If
           Next j
       Next i
   End If
   
   i = Int((Dic.Count) * Rnd() + 1)
   j = 1
   
   For Each Eintrag In Dic.Items
       If j = i Then
           MeineZufallszahl = Val(Eintrag)
       End If
       j = j + 1
   Next Eintrag

   Set Dic = Nothing

End Function

... und die Verwendung der neuen Funktion im Tabellenblatt:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A
2	34
3	49
4	42
5	9
6	30
7	10

Zelle	Formel
A2	=MeineZufallszahl(1;54)
A3	=MeineZufallszahl(1;54;A$2)
A4	=MeineZufallszahl(1;54;A$2:A3)
A5	=MeineZufallszahl(1;54;A$2:A4)
A6	=MeineZufallszahl(1;54;A$2:A5)
A7	=MeineZufallszahl(1;54;A$2:A6)

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2013

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

LG Gerd

[Sören9999]

  Rückennummern.xlsx (Größe: 20,59 KB / Downloads: 5)

Hallo!

Also, Gerd, so eben aus dem Arm geschüttelt ist der Code sehr beeindruckend und eine VBA-Lösung wäre natürlich auch gerne gesehen. Leider erzeugt er bei mir keine Zahlen, sondern nur #WERT. Den Code habe ich so umgesetzt, wie du schriebst, deswegen wundert es mich sehr.

Ich glaube in der Beispieldatei wird das Problem ganz gut deutlich. Die Zufallszahlen (inklusive Ausnahmen) werden im Blatt "Zahlen" erzeugt und sollen dann den Spielern zugeordnet werden. Nur Spielern, die wirklich eingesetzt werden (Spalte B = Spielt), sollen Rückenummern zugewiesen werden und, wenn diese zur gleichen Zeit spielen, dürfen die Zahlen natürlich nicht doppelt vergeben werden. Deshalb auch drei separate "Zufallspools".

Vielen Dank für die rege Beteiligung.

Grüße
Sören