bedingter Zahlengenerator

21.11.2024 14:28:03

renatoaugusto

Hallo allerseits,

Ich möchte von Excel gerne Zufallszahlen unter folgenden Bedingungen generieren lassen:

1.
Bereich:
nur Zahlen aus bspw. 1 bis 100

2.
Zufallswerte
bspw. 20 zufällige Werte aus o.g. Bereich

3.
vorgegebene Kombinationen:
nur Kombinationen (bspw. 10), mit bspw. 6 Werten aus den obigen 20 vorgegebenen Werten

Ich hoffe, das ist so ausreichend deutlich erklärt.
Kann jemand helfen?
Danke im voraus.
VG
Kai

AW: bedingter Zahlengenerator

21.11.2024 14:47:33

Yal

Hallo Kai,

=ZUFALLSBEREICH(1;100)
nach unten auf 20 Zellen ziehen.

VG
Yal

Bei den bisherigen Lösungen …

21.11.2024 15:57:53

RPP63

… sind Dubletten möglich!
20 Zufallszahlen ohne Duplikate aus 1 bis 100 ermittele ich so:

=SORTIEREN(ÜBERNEHMEN(SORTIERENNACH(SEQUENZ(100);ZUFALLSMATRIX(100));20))

Nummer 3 habe ich nicht verstanden.

Gruß Ralf

AW: Bei den bisherigen Lösungen …

22.11.2024 15:10:42

renatoaugusto

Hallo Ralf,

super, vielen Dank, du hast es vorausgesehen, Dubletten sind nicht erwünscht, hatte ich vergessen zu erwähnen.

Beim dritten Punkt hab ich mich etwas schwer getan es verständlich zu formulieren:

Es sollen zehn zufällige Kombinationen mit je 6 Werten aus den zuvor generierten 20 Zahlen erstellt werden, soll heißen die übrigen 80 Werte aus 1 bis 100 sollen nicht mehr berücksichtigt werden.
Ergebnis würde dann bspw. so aussehen:

20 zufällige Werte zwischen 1 und 100:
3, 9, 11, 15, 19, 24, 33, 39, 44, 45, 47, 48, 60, 61, 77, 78, 79, 80, 99, 100

10 Kombinationen mit je 6 Werten aus o.g. 20:
1.: 11, 78, 79, 80, 99, 100
2.: 19, 45, 60, 79, 80, 100
3.: 3, 33, 48, 77, 99, 100 etc.
4.
5.
6.
7.
8.
9.
10.

Variable Eingabewerte sollen sein:
1.
Bereich: hier im Beispiel 1-100

2.
#Zufallszahlen: hier im Beispiel 20

3.
#Kombinationen: hier im Beispiel 10

4.
#Werte/Kombination: hier im Beispiel 6

Für euch bestimmt ein Klacks, ich bekomme es leider nicht hin...
Danke für die bisherige Mühe

VG
Kai

AW: Bei den bisherigen Lösungen …

22.11.2024 15:19:59

renatoaugusto

Achja, wieder vergessen:

auch wieder keine Dubletten, i.e. keine identischen 6er-Kombinationen
VG

AW: Bei den bisherigen Lösungen …

22.11.2024 15:27:39

renatoaugusto

Können zuvor verfasste Beiträge eigentlich später noch mal geändert werden? Sehe keine Option...

AW: Bei den bisherigen Lösungen …

22.11.2024 16:13:17

renatoaugusto

Anbei noch als Beispiel, wie das Ergebnis in etwa aussehen sollte.

https://www.herber.de/bbs/user/173774.xlsx

Gruß
Kai

AW: Bei den bisherigen Lösungen …

22.11.2024 16:49:07

RPP63

Moin mal wieder!
Du brauchst also i Ziehungen mit k Werten, die sich aus dem Zahlenraum l (Anfang) und m (Ende) ergeben, der aus n Zufallszahlen besteht.
Ich bin mir recht sicher, dass man diese 5 Variablen in einer einzigen Formelzelle abbilden kann, die dann fröhlich spillt.
Subsumiert ist das ja nichts anderes als die Auflistung von x Ziehungen im Lotto.
Ja, geht sicherlich (wenngleich es kein "Klacks" ist!), die Umsetzung interessiert mich auch …
… ich weiß nicht, ob ich heute noch Lust dazu habe, werde aber am Wochenende eine Lösung vorstellen.
(Es sei denn, jemand kommt mir zuvor ;))

Gruß Ralf

AW: Bei den bisherigen Lösungen …

22.11.2024 17:21:33

renatoaugusto

Hallo Ralf,

du hast es in einem Satz auf den Punkt gebracht und es zeigt sich, wer in Mathe aufgepasst hat...
Alles in einer Zelle wäre faszinierend und die größere Herausforderung, die Zwei-Schrittlösung vom Ergebnis her sicher nicht schlechter.
Danke im voraus.

Grüße

Annäherung

23.11.2024 08:01:42

RPP63

Eine Ein-Zellen-Lösung habe ich nicht zum Laufen gebracht.
Außerdem musste ich den Zahlenraum (die 12 Zufallszahlen) durch Kopieren und Einfügen als Wert fixieren.
Vorab der Link zur Übungsdatei:
https://www.herber.de/bbs/user/173782.xlsx
Der Reihe nach:
Die Vorgabe

	A	B
1
2
3	Bereich Anfang:	5
4	Bereich Ende:	45
5
6	#Werte aus Bereich:	12
7
8	#Ziehungen	10
9
10	wieviel aus Bereich	4

Hier also 4 aus 12 (12 Zufallszahlen aus dem Zahlenbereich von 5 bis 45) mit insgesamt 10 Ziehungen.
Die Zufallszahlen ermittele ich so:

	D	E	F	G	H	I	J	K	L	M	N	O	P
1	7	10	11	15	23	29	33	35	36	38	39	43

Zelle	Formel
D1	=LET(Anf;B$3;End;B$4; Bereich;SEQUENZ(;End-Anf+1;Anf); unsort;SORTIERENNACH(Bereich;ZUFALLSMATRIX(;SPALTEN(Bereich))); n_Werte_aus_Bereich;ÜBERNEHMEN(unsort;;B$6); SORTIEREN(n_Werte_aus_Bereich;;;1))

Damit die Zufallszahlen fixiert sind, muss ich sie in D2 kopieren (mittels =D1#), Zeile 2 kopieren und als Wert einfügen.
4 aus 12 erfolgt in D3 mittels

	D	E	F	G	H	I	J	K	L
3	30	35	38	45

Zelle	Formel
D3	=WENN(ZEILE(X1)>B$8;"";LET(Bereich;FILTER($2:$2;$2:$2>""); SORTIEREN( ÜBERNEHMEN( SORTIERENNACH(Bereich;ZUFALLSMATRIX(;B$6));;B$10);;;1)))

D3 kann jetzt beliebig nach unten gezogen werden.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1				6	8	10	17	22	23	27	29	30	35	37	43
2				8	14	27	28	30	33	35	36	38	43	44	45
3	Bereich Anfang:	5		35	36	38	43
4	Bereich Ende:	45		14	33	38	44
5				27	35	43	45
6	#Werte aus Bereich:	12		35	36	43	45
7				8	14	27	43
8	#Ziehungen	10		28	33	36	38
9				30	33	36	43
10	wieviel aus Bereich	4		27	30	38	43
11				30	43	44	45
12				28	36	44	45
13
14

Zelle	Formel
D1	=LET(Anf;B$3;End;B$4; Bereich;SEQUENZ(;End-Anf+1;Anf); unsort;SORTIERENNACH(Bereich;ZUFALLSMATRIX(;SPALTEN(Bereich))); n_Werte_aus_Bereich;ÜBERNEHMEN(unsort;;B$6); SORTIEREN(n_Werte_aus_Bereich;;;1))
D3	=WENN(ZEILE(X1)>B$8;"";LET(Bereich;FILTER($2:$2;$2:$2>""); SORTIEREN( ÜBERNEHMEN( SORTIERENNACH(Bereich;ZUFALLSMATRIX(;B$6));;B$10);;;1)))

Finale

23.11.2024 08:38:32

RPP63

Final jetzt ohne das bekloppte Kopieren und als Wert einfügen!
Wenn die Datei formelmäßig vorbereitet ist, geht es auch "simpel" so:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1				6	10	12	13	14	15	16	30	36	40	42	43
2
3	Bereich Anfang:	5		12	13	15	40
4	Bereich Ende:	49		6	15	40	42
5				30	36	40	43
6	#Werte aus Bereich:	12		6	30	36	42
7				10	12	15	42
8	#Ziehungen	7		10	13	15	40
9				14	36	40	43
10	wieviel aus Bereich	4
11

Zelle	Formel
D1	=LET(Anf;B$3;End;B$4; Bereich;SEQUENZ(;End-Anf+1;Anf); unsort;SORTIERENNACH(Bereich;ZUFALLSMATRIX(;SPALTEN(Bereich))); n_Werte_aus_Bereich;ÜBERNEHMEN(unsort;;B$6); SORTIEREN(n_Werte_aus_Bereich;;;1))
D3	=WENN(ZEILE(W1)>B$8;""; SORTIEREN( ÜBERNEHMEN( SORTIERENNACH(D$1#;ZUFALLSMATRIX(;B$6));;B$10);;;1))

Auch hier wieder die überarbeitete Testdatei:
https://www.herber.de/bbs/user/173783.xlsx

AW: Finale

23.11.2024 12:29:19

renatoaugusto

Hallo Ralf,

das Ergebnis sieht super aus, die Formeln auch, aber das sind böhmische Dörfer für mich.
Bei mir hört es nach dem S-Verweis schon auf, diese verschachtelten Formeln sind beeindruckend.
Ganz lieben Dank für deine Hilfe. Ich versuche jetzt noch einzufügen, wie oft jede Zahl in den Ziehungen vorkommt.
Hast du einen Tipp für mich, welche Formel das wäre, gibt es so etwas wie "Anzahl"?

Viele Grüße
Kai

böhmische Dörfer ;)

23.11.2024 13:53:10

RPP63

»Ich versuche jetzt noch einzufügen, wie oft jede Zahl in den Ziehungen vorkommt.«
und
»aber das sind böhmische Dörfer für mich«
Nicht falsch verstehen:
Für jemanden mit Selbsteinschätzung kaum Excel-Kenntnisse werden diese Formeln auch in Jahren noch unverständlich sein.
(ähnlich wie für einen Grundschüler, der gerade das kleine 1*1 lernt und plötzlich Stochastik vorgesetzt bekommt) ;)
Zu "wie oft jede Zahl":

	AB	AC
3	Zahl	Anz
4	2	2
5	3	4
6	5	5
7	8	3
8	14	4
9	15	6
10	16	5
11	17	6
12	18	4
13	21	5
14	23	1
15	38	5
16	39	4
17	40	4
18	42	4
19	43	7
20	49	3
21		624
22

Zelle	Formel
AB3	=LET(Header;{"Zahl"."Anz"}; Zahlen;SORTIEREN(EINDEUTIG(ZUSPALTE(D3:Z31;1))); AnzZahl;NACHZEILE(Zahlen;LAMBDA(x;ZÄHLENWENN(D3:AA31;x))); VSTAPELN(Header;HSTAPELN(Zahlen;AnzZahl)))

Das wirst (besser kannst) Du nicht nachvollziehen, nimm es einfach so, dass ich viel Spaß an diesem Thread hatte.

AW: böhmische Dörfer ;)

23.11.2024 13:58:10

RPP63

Die 624 aus obigem Beispiel sind die Anzahl der Leerzellen.
Will man die nicht haben, dann so:

=LET(Header;{"Zahl"."Anz"};

Zahlen;SORTIEREN(EINDEUTIG(ZUSPALTE(D3:Z31;1)));

AnzZahl;NACHZEILE(Zahlen;LAMBDA(x;ZÄHLENWENN(D3:AA31;x)));

WEGLASSEN(VSTAPELN(Header;HSTAPELN(Zahlen;AnzZahl));-1))

AW: böhmische Dörfer ;)

24.11.2024 11:14:41

renatoaugusto

Hallo Ralf,

du sagst es, da mache ich mir keine Illusionen. Dafür fehlt sowohl die Muße als auch das Talent.
Freut mich aber, dass ich einem "alten Hasen" eine passende Aufgabe stellen konnte.

Lässt sich der "wie-oft-gezogen-Wert" auch direkt horizontal in d2-o2 integrieren?

Gruß
Kai

MTRANS()

24.11.2024 11:38:41

RPP63

Dafür gibt es (als einfachste Lösung) MTRANS()
=LET(
Header; {"Zahl". "Anz"};
Zahlen; SORTIEREN(EINDEUTIG(ZUSPALTE(D3:Z31; 1)));
AnzZahl; NACHZEILE(Zahlen; LAMBDA(x; ZÄHLENWENN(D3:AA31; x)));
MTRANS(WEGLASSEN(VSTAPELN(Header; HSTAPELN(Zahlen; AnzZahl)); -1))
)
Ich könnte die Matrix auch gleich anders aufbauen, aber dazu fehlt selbst mir die Muße. ;)

Gruß Ralf

falsch verstanden

24.11.2024 11:39:51

RPP63

Habe es falsch verstanden!
Einen Moment!

aber jetzt!

24.11.2024 11:41:55

RPP63

In D2:
=NACHSPALTE(D1#;LAMBDA(x;ZÄHLENWENN(D3:Z31;x)))

AW: aber jetzt!

24.11.2024 12:34:09

renatoaugusto

Ich wünschte, ich könnte solche Formeln auch einfach so aus dem Ärmel schütteln. Besten Dank.

Durch diesen statistischen Wert ist mir aufgefallen, dass noch kleine Verfeinerungen möglich sind.
Vielleicht hast du noch Lust auf neue Herausforderungen...

Manche Werte werden oft, andere gar nicht gezogen:
Vielleicht könnte man vorgeben, dass jeder Wert mindestens ein mal gezogen werden muss, oder, und dass könnte der Endgegner sein,
dass jeder Wert z.b. mindestens 3, und höchstens 6 mal gezogen werden darf, sodass in den Ziehungen alle 20 Werte ähnlich gut repräsentiert sind.

Außerdem versuche ich gleich eine Version zu erstellen, welche die Ziehungen aus vorgegebenen statt aus den zufälligen Werten erstellt.

Gruß
Kai

OT: Ich war mal so frei...

25.11.2024 16:24:18

{Boris}

https://www.herber.de/excelformeln/pages/Alternative_zur_Ermittlung_von_Zufallszahlen_ohne_Wiederholungen.html

VG, Boris

AW: Bei den bisherigen Lösungen …

22.11.2024 17:47:23

Yal

Moin,

wenn man eine Liste von 100 Werten und den Rang dazu holt, kann man vielleicht die Bereich 1 bis 5, 10 bis 15, usw. zusammen bringen.

in A1
=ZUFALLSMATRIX(100;1;1;1000000;FALSCH)

in B1
=RANG.GLEICH(A1#;A1#)

in C1
=VSTAPELN(B5:B10;B15:B20)

oder mit Indirekt
=VSTAPELN(indirekt("B"&D1&":B"&(D1+D2);indirekt("B"&D3&":B"& (D3+D4))

Wobei ausserst fragwürdig ist, warum die Ergebnis 5-10 und 15-20 besser/sinnvoller als einfach die Ergebnisse 1-12 (?)

Rang.Gleich scheint sich nicht einfach in einem "LET" einfangen zu lassen, was die Zusammenfassung erschwert.

Sollte komplexe Gebilde könnte man mit VBA abdecken.

VG
Yal

AW: Bei den bisherigen Lösungen …

22.11.2024 18:07:03

renatoaugusto

Hallo Yal,

Danke für deinen Input.
Leider sind meine Kenntnisse derart rudimentär, dass ich deinen Ausführungen kaum folgen kann, merke aber, dass es doch nicht so trivial zu sein scheint, wie ich dachte.

VG

In der angegebenen Excelversion...

21.11.2024 15:28:44

{Boris}

Hi Yal,

...kann man die auch spillen lassen:

=ZUFALLSMATRIX(20;1;1;100;1)

VG, Boris

bedingter Zahlengenerator

Forumthread: bedingter Zahlengenerator

22
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

bedingter Zahlengenerator

Forumthread: bedingter Zahlengenerator

22 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

22
Beiträge zum Forumthread

Beiträge zu diesem Forumthread