Zufällige Zahlen mit fester Summe generieren?

[Morgrey]

Guten Morgen zusammen!

Ich bin neu in diesem Forum und (relativ) neu im Benutzen von Excel. Daher bitte ich um Verständnis, falls meine Frage bzw. Aufgabenstellung für versierte Benutzer vielleicht in die Kategorie "selbsterklärend" fällt.

Folgendes Problem würde ich gerne angehen:

In Excel sollen 26 Zellen mit zufällig generierten ganzen Zahlen gefüllt werden, die jeweils im Bereich zwischen 24 und 81 liegen. Die 26 generierten Zahlen sollen in Summe exakt den Wert 1400 ergeben.

Mit welcher Formel bzw. auf welchem Wege lässt sich diese Aufgabe lösen?

Vielen herzlichen Dank für eure Hilfe!

Moritz

[LCohen]

=(24+81)/2 * 26 = 1365
=(24+84)/2 * 26 = 1404

Bei einer Ganzzahlenspanne (beginnend mit 24) kommst Du also 1400 mit der Obergrenze 84 am nächsten.

Sonst musst Du leider von der Gleichverteilung der Werte Abstand nehmen. Bzw.: Nicht nur sonst, sondern im Folgenden sowieso.

A1[:A25]: =MIN(ZUFALLSBEREICH(24;84);81) kommt nun Deinem Ziel nahe.
A26: =1400-SUMME(A1:A25) gibt Dir dann den Rest. Falls A26 nicht zwischen 24 und 81, so lange F9 drücken, bis ok.

Am Ergebnis ist zu beanstanden, dass 81 (mit 81, 82, 83, 84) systematisch viermal so häufig "verzufallt" wird, wie die kleineren erlaubten Werte. Unfair! Das liegt an Deiner schlechten Vorgabe.

In meiner Betrachtung ist wiederum ein systematischer Fehler enthalten, da den ersten beiden Berechnungen eine soll-stetige Betrachtung zugrunde liegt, ZUFALLSBEREICH aber diskret funktioniert.

Etwas weniger laienhaft wird Dir möglicherweise sulprobil helfen.

[mmat]

Hallo,

das kleine VBA-Makro müsste die Anforderungen erfüllen

Code:

Option Explicit
Sub zufall()
Dim n As Long, diff As Long
Dim delta As Long, minimum As Long, maximum As Long, anzahl As Long
Dim SummeSoll As Long, SummeIst As Long, z() As Long
  minimum = 24
  maximum = 81
  delta = maximum - minimum + 1
 
  anzahl = 26: ReDim z(anzahl)
  SummeSoll = 1400
 
  Do
    diff = SummeSoll - SummeIst
    If diff >= minimum And diff <= maximum Then Exit Do
   
    SummeIst = 0
    For n = 1 To anzahl - 1
      z(n) = Int(Rnd * delta) + minimum
      SummeIst = SummeIst + z(n)
    Next
    DoEvents: DoEvents: DoEvents
  Loop
 
  z(anzahl) = diff
 
  SummeIst = 0
  For n = 1 To 26
    Cells(n, 1) = z(n): SummeIst = SummeIst + z(n)
  Next
  Cells(anzahl + 1, 1) = SummeIst - SummeSoll
End Sub

Wobei ich zur Qualität der Zufallszahlen ebenfalls keine Garantien übernehme
vg, MM

[Morgrey]

Lieber LCohen,

vielen Dank für die rasche Hilfe. Das bringt mich schon deutlich weiter!

Gruß, Moritz

[Sulprobil]

Hallo,

Anbei ein Ansatz mit Tabellenfunktionen.

Die Datei wird ohne jegliche Gewährleistung zur Verfügung gestellt, aber ich verwende einen aktuellen Virenscanner.


  Zufallszahlen_mit_fester_Summe.xlsm (Größe: 67,8 KB / Downloads: 3)

Viele Grüße,
Bernd P

[snb]

Code:

Sub M_snb()
  Randomize
  ReDim sn(25, 0)
  Cells(1).Resize(26).clearcontents

  For j = 0 To UBound(sn) - 1
    sn(j, 0) = 24 + Int(57 * Rnd)
  Next
  If Application.Sum(sn) < 1400 And (1376 - Application.Sum(sn)) < 57 Then
    sn(j, 0) = 1400 - Application.Sum(sn)
    Cells(1).Resize(26) = sn
  End If
End Sub

[Sulprobil]

Hi Dolf,

Nett, aber ich möchte bei jedem Lauf ein Ergebnis haben, wenn es eins geben kann

Wenn Du 100.000 Zeilen hast, die Eingaben zwischen 0 und 1 liegen müssen, und die Summe 1 sein soll, wird mein Ansatz klappen, aber Du drückst wahrscheinlich Weihnachten noch auf F5

Viele Grüße,
Bernd P

[snb]

Du hast die Code eben nicht getestet. :27:

Code:

Sub M_snb()
  Randomize
  ReDim sn(25, 0)
  Cells(1).Resize(26).ClearContents
    
  Do Until Application.Sum(sn) = 1400
    For j = 0 To UBound(sn) - 1
      sn(j, 0) = 24 + Int(57 * Rnd)
    Next
    If Application.Sum(sn) < 1400 And (1376 - Application.Sum(sn)) < 57 Then sn(j, 0) = 1400 - Application.Sum(sn)
  Loop

  Cells(1).Resize(26) = sn
End Sub

[Sulprobil]

Hallo Dolf,

Probier Deinen Code mal für die Summe 1 mit den Grenzen 0 und 1 und 100.000 Ziehungen.

Du wirst eine Weile warten.

Dein Ansatz ist bei großer Zahl der Ziehungen leider nicht günstig.

Viele Grüße,
Bernd P

[snb]

Bitte, schau mal https://www.clever-excel-forum.de/thread...#pid160241 genau an.

Und wieso 'Dolf' ?