Zufallszahlen mit Ausnahme

[Harald Keil]

Hallo NG

Ich möchte in einer Zelle eine Zufallszahl generieren lassen.

Das klappt auch ganz gut mit dem Befehl =Zufallszahl() oder Zufallsbereich()

Weiters möchte ich aber eine Zufallszahl zwischen 0 und 20 generieren
lassen mit Ausnahme der Zahlen 3, 6, 8 und 9

Wie krieg ich das gebacken?
Bin schon ganz verzweifelt am suchen aber ich bekomm das leider nicht hin.

Vielleicht kann mir jemand von euch helfen.
Bin für jeden Rat Dankbar.

lg. Harald Keil

[Eberhard Funke]

Post by Harald Keil
Ich möchte in einer Zelle eine Zufallszahl generieren lassen.
Das klappt auch ganz gut mit dem Befehl =Zufallszahl() oder Zufallsbereich()
Weiters möchte ich aber eine Zufallszahl zwischen 0 und 20 generieren
lassen mit Ausnahme der Zahlen 3, 6, 8 und 9

Hallo Harald,

kopiere diese benutzerdefinierte Funktion in ein Modul:

Function ZFZ() As Integer
Dim intTmp As Integer
Dim blBingo As Boolean
Do
intTmp = Int((20 * Rnd) + 0)
Select Case intTmp
Case 3, 6, 8, 9
blBingo = True
Case Else
blBingo = False
End Select
Loop While blBingo = True
ZFZ = intTmp
End Function

Aufruf in der Tabelle =ZFZ()

[Harry]

Hallo Eberhard
Vielen Dank für die schnelle Antwort
werd ich gleich ausprobieren.

mfg. Harald

Post by Eberhard Funke

Post by Harald Keil
Ich möchte in einer Zelle eine Zufallszahl generieren lassen.
Das klappt auch ganz gut mit dem Befehl =Zufallszahl() oder Zufallsbereich()
Weiters möchte ich aber eine Zufallszahl zwischen 0 und 20 generieren
lassen mit Ausnahme der Zahlen 3, 6, 8 und 9

Hallo Harald,
Function ZFZ() As Integer
Dim intTmp As Integer
Dim blBingo As Boolean
Do
intTmp = Int((20 * Rnd) + 0)
Select Case intTmp
Case 3, 6, 8, 9
blBingo = True
Case Else
blBingo = False
End Select
Loop While blBingo = True
ZFZ = intTmp
End Function
Aufruf in der Tabelle =ZFZ()

[Bernhard Sander]

Hallo Harald,

Post by Harald Keil
Ich möchte in einer Zelle eine Zufallszahl generieren lassen.
Das klappt auch ganz gut mit dem Befehl =Zufallszahl() oder
Zufallsbereich()
Weiters möchte ich aber eine Zufallszahl zwischen 0 und 20 generieren
lassen mit Ausnahme der Zahlen 3, 6, 8 und 9

Ich geh mal davon aus, dass Du nur ganze Zahlen haben willst:
=INDEX({0.1.2.4.5.7.10.11.12.13.14.15.16.17.18.19.20};1;GANZZAHL(ZUFALLSZAHL()*16)+1)

In der Matrix in den geschweiften Klammern listest Du die Zahlen auf, die
zulässig sind.
*16 hinter Zufallszahl() ist 1 weniger als Zahlen in der Matrix stehen
+1 am Ende ist nötig, weil INDEX mit 1 zu zählen anfängt.


Gruß
Bernhard Sander

[Harry]

Hallo Bernhard

Auch dir Vielen Dank für die schnelle Antwort
Deine Lösung gefällt mir noch besser als die von Eberhard, denn so muss
ich nicht extra ein Makro aufrufen.
Nochmals Danke und lg. Harald

Post by Eberhard Funke
Hallo Harald,

Post by Harald Keil
Ich möchte in einer Zelle eine Zufallszahl generieren lassen.
Das klappt auch ganz gut mit dem Befehl =Zufallszahl() oder
Zufallsbereich()
Weiters möchte ich aber eine Zufallszahl zwischen 0 und 20 generieren
lassen mit Ausnahme der Zahlen 3, 6, 8 und 9

=INDEX({0.1.2.4.5.7.10.11.12.13.14.15.16.17.18.19.20};1;GANZZAHL(ZUFALLSZAHL()*16)+1)
In der Matrix in den geschweiften Klammern listest Du die Zahlen auf,
die zulässig sind.
*16 hinter Zufallszahl() ist 1 weniger als Zahlen in der Matrix stehen
+1 am Ende ist nötig, weil INDEX mit 1 zu zählen anfängt.
Gruß
Bernhard Sander

[Dr. Eckehard Pfeifer]

Hallo, ich glaube, dass
=INDEX({0.1.2.4.5.7.10.11.12.13.14.15.16.17.18.19.20};1;RUNDEN(ZUFALLSZAHL()*16+1;0))
besser ist, da hier auch die 20 mit erzeugt wird.
ZUFALLSZAHL()*16+1 bestimmt eine Zufallszahl zwischen 1 und 17, Runden
liefert ganzzahlige die Position für INDEX.

[Bernd P]

Hallo,

Oder ggf.
=CHOOSE(INT(RAND()*16+1),1,2,4,5,7,10,11,12,13,14,15,16,17,18,19,20)

In der deutschen Version
=WAHL(GANZZAHL(ZUFALLSZAHL()*16+1);
1;2;4;5;7;10;11;12;13;14;15;16;17;18;19;20)

Viele Gruesse,
Bernd

[Dr. Eckehard Pfeifer]

Ingrid Pfeifer meldet nach:
=INDEX({0.1.2.4.5.7.10.11.12.13.14.15.16.17.18.19.20};1;RUNDEN(ZUFALLSZAHL()*16,98+0,51;0))
berücksichtigt noch mehr die Gleichverteilung, sonst werden die Ränder etwas
unterrepräsentiert.

[Klaus Blaschke]

Post by Dr. Eckehard Pfeifer
=INDEX({0.1.2.4.5.7.10.11.12.13.14.15.16.17.18.19.20};1;RUNDEN(ZUFALLSZAHL()*16,98+0,51;0))
berücksichtigt noch mehr die Gleichverteilung, sonst werden die Ränder etwas
unterrepräsentiert.

Hallo,
da Harald schrieb:

Zufallszahl() oder Zufallsbereich()

würde ich vorschlagen:

=WAHL(ZUFALLSBEREICH(1;17);0;1;2;4;5;7;10;11;12;13;14;15;16;17;18;19;20)

Gruß
Klaus

[Dr. Eckehard Pfeifer]

Warum in die Ferne schweifen, sieh, das Gute liegt so nah...

So ist das mit den eingefahrenen Gleisen.

[Harry]

Hallo Klaus

Danke erstmal auch an alle Beteiligten für die Super Hilfe
Ich hätte da jezt noch ein Problem das ich nicht so recht gebacken krieg.
Das erste Problem mit der Zufallszahl wäre schon mal gelöst.
Mein 2. Problem

Ich habe in Zelle zb. A1 nun (Momentan) 4 Zufallszahlen.
Genauer gesagt 2, 4, 5 und 10

Ich möchte nun in einer anderen Zelle zb. A2 mehrere Zufallszahlen
erzeugen lassen in Abhängigkeit von Zelle A1.

Das soll so aussehen dass wenn in Zelle A1 die Zahl 2 erzeugt wird, dann
soll in Zelle A2 die Zufallszahl 4, 8, 12, 16, oder 20 erzeugt werden.

Wenn in Zelle A1 die Zahl 4 erzeugt wird dann soll in Zelle A2 eine
Zufallszahl von 8, 12, 16, 20, 24, 28, 32, 36, oder 40 erzeugt werden.

usw.

Ich hab schon mit =wenn(oder( probiert, aber irgendwas mach ich noch
falsch.

Vieleicht hat jemand eine Lösung ?
Vielen Dank und lg. Harald Keil

Post by Dr. Eckehard Pfeifer

Post by Dr. Eckehard Pfeifer
=INDEX({0.1.2.4.5.7.10.11.12.13.14.15.16.17.18.19.20};1;RUNDEN(ZUFALLSZAHL()*16,98+0,51;0))
berücksichtigt noch mehr die Gleichverteilung, sonst werden die Ränder etwas
unterrepräsentiert.

Hallo,
Zufallszahl() oder Zufallsbereich()
=WAHL(ZUFALLSBEREICH(1;17);0;1;2;4;5;7;10;11;12;13;14;15;16;17;18;19;20)
Gruß
Klaus

[Bernd P]

Hallo Harald,

Du gehst bei 2 und 4 jeweils vom Doppelten bis zum 10-fachen, aber bei
beiden in 4er Schritten, wie sieht das Bildungsgesetz allgemein aus
(z. B. bei 5 und 10 - oder koennen es auch die anderen Zahlen aus der
ersten Frage sein: 0, 1, ..., 17, ,.., 20)?

Viele Gruesse,
Bernd

[Harry]

Hallo Bernd

Entschuldige bei meiner Forulierung ist mir leider ein Fehler passiert.
Es sollten bei der 2 in 2er Schritten vorgegangen werden.
Bei der 5 dann in 5er Schritten und bei 10 natürlich in 10er Schritten.

Aber in der Zwischenzeit bin ich selber draufgekommen und glaube eine
passable Lösung gefunden zu haben die wie folgt aussieht.
Ich nehme die Zufallszahl in Zelle AC46 dafür her.

=WENN(AC46=2;INDEX({4.6.8.10.12.14.16.18.20};1;GANZZAHL(ZUFALLSZAHL()*9)+1);WENN(AC46=4;INDEX({8.12.16.20.24.28.32.36.40};1;GANZZAHL(ZUFALLSZAHL()*9)+1);WENN(AC46=5;INDEX({10.15.20.25.30.35.40.45.50};1;GANZZAHL(ZUFALLSZAHL()*9)+1);WENN(AC46=10;INDEX({20.30.40.50.60.70.80.90.100};1;GANZZAHL(ZUFALLSZAHL()*4)+1);""))))

Vielen Dank für die Bemühungen aber die Formel Funktioniert für meine
Bedürfnisse ganuso wie ich sie brauche.

lg. Harald

Post by Eberhard Funke
Hallo Harald,
Du gehst bei 2 und 4 jeweils vom Doppelten bis zum 10-fachen, aber bei
beiden in 4er Schritten, wie sieht das Bildungsgesetz allgemein aus
(z. B. bei 5 und 10 - oder koennen es auch die anderen Zahlen aus der
ersten Frage sein: 0, 1, ..., 17, ,.., 20)?
Viele Gruesse,
Bernd

[Bernd P]

Post by Harry
Hallo Bernd
Entschuldige bei meiner Forulierung ist mir leider ein Fehler passiert.
Es sollten bei der 2 in 2er Schritten vorgegangen werden.
Bei der 5 dann in 5er Schritten und bei 10 natürlich in 10er Schritten.
Aber in der Zwischenzeit bin ich selber draufgekommen und glaube eine
passable Lösung gefunden zu haben die wie folgt aussieht.
Ich nehme die Zufallszahl in Zelle AC46 dafür her.
=WENN(AC46=2;INDEX({4.6.8.10.12.14.16.18.20};1;GANZZAHL(ZUFALLSZAHL()*9)+1);WENN(AC46=4;INDEX({8.12.16.20.24.28.32.36.40};1;GANZZAHL(ZUFALLSZAHL()*9)+1);WENN(AC46=5;INDEX({10.15.20.25.30.35.40.45.50};1;GANZZAHL(ZUFALLSZAHL()*9)+1);WENN(AC46=10;INDEX({20.30.40.50.60.70.80.90.100};1;GANZZAHL(ZUFALLSZAHL()*4)+1);""))))
Vielen Dank für die Bemühungen aber die Formel Funktioniert für meine
Bedürfnisse ganuso wie ich sie brauche.
lg. Harald

Post by Eberhard Funke
Hallo Harald,
Du gehst bei 2 und 4 jeweils vom Doppelten bis zum 10-fachen, aber bei
beiden in 4er Schritten, wie sieht das Bildungsgesetz allgemein aus
(z. B. bei 5 und 10 - oder koennen es auch die anderen Zahlen aus der
ersten Frage sein: 0, 1, ..., 17, ,.., 20)?
Viele Gruesse,
Bernd

[Bernd P]

Hallo Harald,

Eine etwas kuerzere Variante:
=IF(ISERROR(MATCH(C46,{2,4,5,10},0)),"",C46*INT(RAND()*9+2))

Auf deutsch:
=WENN(ISTFEHLER(VERGLEICH(AC46;{2,4,5,10},0));"";AC46*GANZZAHL
(ZUFALLSZAHL()*9+2))
In der Klammer musst Du ggf. Punkte oder Semikolon als Trenner nehmen.

Viele Gruesse,
Bernd

[Klaus Blaschke]

Post by Eberhard Funke
Hallo Harald,
=WENN(ISTFEHLER(VERGLEICH(AC46;{2,4,5,10},0));"";AC46*GANZZAHL
(ZUFALLSZAHL()*9+2))
In der Klammer musst Du ggf. Punkte oder Semikolon als Trenner nehmen.

Hallo Bernd und Harald,
in der Klammer Punkte und nach der Klammer ein Semikolon:

=WENN(ISTFEHLER(VERGLEICH(AC46;{2.4.5.10};0));"";AC46*GANZZAHL(ZUFALLSZAHL()*9+2))

Diese Formel gefällt mir, könntest Du vielleicht noch erklären wie sie
genau funktioniert?

Gruß
Klaus

[Bernd P]

Hallo Klaus,

Danke fuer Deine Korrektur. Ich sitze in London an meinem englischen
Excel und habe die deutsche "Uebersetzung" nur aus dem Kopf
hingeschrieben.

{2.4.5.10} definiert ein Array mit den Zahlen 2, 4, 5, 10, in dem wir
dann AC46 suchen. Falls wir keine dieser Zahlen finden, liefert
Vergleich einen Fehler, den wir abfangen mit ISTFEHLER und dann mit ""
quittieren. Falls wir aber eine der genannten Zahlen haben, erzeugen
wir eine Zufallszahl zwischen 2 und 10 und multiplizieren diese mit
AC46 - dafuer brauchte ich die Bildungsregel von Harald.

Du kannst uebrigens die Formel auch schrittweise nachvollziehen: gehe
mit F2 in den Formeleditor, markiere z. B. den Teil VERGLEICH(AC46;
{2.4.5.10};0) und druecke F9, dann wird dieser Teil durch das
berechnete Ergebnis ersetzt. Am Ende aber mit ESC die Formel
verlassen, sonst ist sie weg und Du hast lediglich noch das Ergebnis
in der Zelle.

Viele Gruesse,
Bernd

[Klaus Blaschke]

Hallo Bernd,

Post by Bernd P
{2.4.5.10} definiert ein Array mit den Zahlen 2, 4, 5, 10, in dem wir
dann AC46 suchen. Falls wir keine dieser Zahlen finden, liefert
Vergleich einen Fehler, den wir abfangen mit ISTFEHLER und dann mit ""
quittieren. Falls wir aber eine der genannten Zahlen haben, erzeugen
wir eine Zufallszahl zwischen 2 und 10 und multiplizieren diese mit
AC46 - dafuer brauchte ich die Bildungsregel von Harald.

Danke!

MfG
Klaus

[Klaus Blaschke]

Post by Harry
Ich nehme die Zufallszahl in Zelle AC46 dafür her.
=WENN(AC46=2;INDEX({4.6.8.10.12.14.16.18.20};1;GANZZAHL(ZUFALLSZAHL()*9)+1);WENN(AC46=4;INDEX({8.12.16.20.24.28.32.36.40};1;GANZZAHL(ZUFALLSZAHL()*9)+1);WENN(AC46=5;INDEX({10.15.20.25.30.35.40.45.50};1;GANZZAHL(ZUFALLSZAHL()*9)+1);WENN(AC46=10;INDEX({20.30.40.50.60.70.80.90.100};1;GANZZAHL(ZUFALLSZAHL()*4)+1);""))))
Vielen Dank für die Bemühungen aber die Formel Funktioniert für meine
Bedürfnisse ganuso wie ich sie brauche.

Hallo Harald,

in deiner Formel hast Du bei der letzten ZUFALLSZAHL()
*4 statt *9 hineingeschrieben.

Wenn man WAHL() und ZUFALLSBEREICH() verwendet ist die Formel etwas
kürzer und wie ich finde auch etwa übersichtlicher.

=WENN(AC46=2;WAHL(ZUFALLSBEREICH(1;9);4;6;8;10;12;14;16;18;20);WENN(AC46=4;WAHL(ZUFALLSBEREICH(1;9);8;12;16;20;24;28;32;36;40);WENN(AC46=5;WAHL(ZUFALLSBEREICH(1;9);10;15;20;25;30;35;40;45;50);WENN(AC46=10;WAHL(ZUFALLSBEREICH(1;9);20;30;40;50;60;70;80;90;100);""))))

Wenn in der Zelle AC46 immer 2, 4, 5 oder 10 drin steht kann man
die Formel noch etwas kürzer machen.

=--(AC46=2)*WAHL(ZUFALLSBEREICH(1;9);4;6;8;10;12;14;16;18;20)+--(AC46=4)*WAHL(ZUFALLSBEREICH(1;9);8;12;16;20;24;28;32;36;40)+--(AC46=5)*WAHL(ZUFALLSBEREICH(1;9);10;15;20;25;30;35;40;45;50)+--(AC46=10)*WAHL(ZUFALLSBEREICH(1;9);20;30;40;50;60;70;80;90;100)

Gruß
Klaus

[Harry]

Hallo an alle

Vielen Dank für die kürzeren Versionen
Ich werd sie gleich ausprobieren
und schöne Grüße Harald

Post by Eberhard Funke

Post by Harry
Ich nehme die Zufallszahl in Zelle AC46 dafür her.
=WENN(AC46=2;INDEX({4.6.8.10.12.14.16.18.20};1;GANZZAHL(ZUFALLSZAHL()*9)+1);WENN(AC46=4;INDEX({8.12.16.20.24.28.32.36.40};1;GANZZAHL(ZUFALLSZAHL()*9)+1);WENN(AC46=5;INDEX({10.15.20.25.30.35.40.45.50};1;GANZZAHL(ZUFALLSZAHL()*9)+1);WENN(AC46=10;INDEX({20.30.40.50.60.70.80.90.100};1;GANZZAHL(ZUFALLSZAHL()*4)+1);""))))
Vielen Dank für die Bemühungen aber die Formel Funktioniert für meine
Bedürfnisse ganuso wie ich sie brauche.

Hallo Harald,
in deiner Formel hast Du bei der letzten ZUFALLSZAHL()
*4 statt *9 hineingeschrieben.
Wenn man WAHL() und ZUFALLSBEREICH() verwendet ist die Formel etwas
kürzer und wie ich finde auch etwa übersichtlicher.
=WENN(AC46=2;WAHL(ZUFALLSBEREICH(1;9);4;6;8;10;12;14;16;18;20);WENN(AC46=4;WAHL(ZUFALLSBEREICH(1;9);8;12;16;20;24;28;32;36;40);WENN(AC46=5;WAHL(ZUFALLSBEREICH(1;9);10;15;20;25;30;35;40;45;50);WENN(AC46=10;WAHL(ZUFALLSBEREICH(1;9);20;30;40;50;60;70;80;90;100);""))))
Wenn in der Zelle AC46 immer 2, 4, 5 oder 10 drin steht kann man
die Formel noch etwas kürzer machen.
=--(AC46=2)*WAHL(ZUFALLSBEREICH(1;9);4;6;8;10;12;14;16;18;20)+--(AC46=4)*WAHL(ZUFALLSBEREICH(1;9);8;12;16;20;24;28;32;36;40)+--(AC46=5)*WAHL(ZUFALLSBEREICH(1;9);10;15;20;25;30;35;40;45;50)+--(AC46=10)*WAHL(ZUFALLSBEREICH(1;9);20;30;40;50;60;70;80;90;100)
Gruß
Klaus

[Andy_D]

Post by Dr. Eckehard Pfeifer
=INDEX({0.1.2.4.5.7.10.11.12.13.14.15.16.17.18.19.20};1;RUNDEN(ZUFALLSZAHL()*16,98+0,51;0))
berücksichtigt noch mehr die Gleichverteilung, sonst werden die Ränder etwas
unterrepräsentiert.

Warum Runden, Index hat kein Problem mit Nachkommastellen:
=INDEX({0.1.2.4.5.7.10.11.12.13.14.15.16.17.18.19.20};1;ZUFALLSZAHL()*17+1)

Gruß
Andy

Post by Dr. Eckehard Pfeifer
--
MfG EP
Entwicklung - Beratung - Training (www.dr-e-pfeifer.net)
XL-Maxibuch (ISBN: 3-86645-231-4)
Microsoft Office 2007-Programmierung (ISBN 3-86645-415-5)

[Dr. Eckehard Pfeifer]

Hallo, Andy, richtig. Aber INDEX schneidet ab, dadurch "rücken" die
Zufallszahlen auf lange Sicht etwas nach links (zur Null hin). Aber was
soll's (man merkt es sicher nicht und: Klaus hat d i e Lösung, oder
nicht?). Wobei ich annehme, dass Excel in ZUFALLSBEREICH nicht mogelt :-).

[Bernd P]

Hallo Eckehard,

Klaus' Loesung mit Zufallsbereich ok ist - und dies scheint es zu sein
- dann ist fuer Excel < 2007
=WAHL(GANZZAHL(ZUFALLSZAHL()*17+1);
0;1;2;4;5;7;10;11;12;13;14;15;16;17;18;19;20)
wohl eine Loesung ohne das Analysis-Addin (ab 2007 wuerde ich Klaus'
Loesung nehmen, aber vorher nicht).

Viele Gruesse,
Bernd

Excel 2002,2003 (deutsch)
Excel 2007 (englisch)

[Andy_D]

Post by Dr. Eckehard Pfeifer
Hallo, Andy, richtig. Aber INDEX schneidet ab, dadurch "rücken" die
Zufallszahlen auf lange Sicht etwas nach links (zur Null hin). Aber was
soll's (man merkt es sicher nicht und: Klaus hat d i e Lösung, oder
nicht?). Wobei ich annehme, dass Excel in ZUFALLSBEREICH nicht mogelt :-).

Hallo Eckehard,
die Gefahr des Abschneidens besteht nicht wegen ZUFALLSZAHL x17+1.
Damit sind allo Möglichkeiten von 1,000... und 17,999... gleichverteilt und
Index nimmt sich nur den Ganzteil.
Ebenso ist Bernds Lösung OK, mit überflüssiger Ganzzahl-Ermittlung.
ZUFALLSBEREICH ist da schon ein Problemkind, da es ein AddIn ist.
Aber mogeln tut es meines Wissens nicht.
Gruß
Andy

Post by Dr. Eckehard Pfeifer
--
MfG EP
Entwicklung - Beratung - Training (www.dr-e-pfeifer.net)
XL-Maxibuch (ISBN: 3-86645-231-4)
Microsoft Office 2007-Programmierung (ISBN 3-86645-415-5)