Zufallszahlen mit einem bestimmten Mittelwert und einer bestimmten Standardabweichung in Excel generieren

Das Generieren einer Reihe von Zufallszahlen mit einem bestimmten Mittelwert und einer bestimmten Standardabweichung ist eine häufige Anforderung in Bereichen wie statistische Simulation, Algorithmenprüfung oder Modellierungsprozesse in Finanzwesen, Ingenieurwesen und Bildung. Allerdings bietet Excel keine direkte integrierte Funktion zur sofortigen Erstellung einer solchen Liste von Zufallszahlen, die sowohl auf einen gegebenen Mittelwert als auch auf eine Standardabweichung zugeschnitten sind. Wenn Sie häufig Testdaten erstellen müssen, die statistisch bestimmte Merkmale aufweisen, kann das Wissen darüber, wie dies erreicht werden kann, Ihre Arbeitsabläufe erheblich verbessern und die Qualität Ihrer Daten steigern.

In diesem Tutorial stellen wir praktische Methoden vor, um Zufallszahlen basierend auf dem von Ihnen angegebenen Mittelwert und der Standardabweichung zu generieren, mit detaillierten Schritt-für-Schritt-Anweisungen, Erklärungen der Formelparameter und Expertentipps zur Fehlervermeidung und Problembehandlung. Zusätzlich bieten wir eine VBA-Makro-Lösung für Benutzer an, die diesen Prozess automatisieren oder große Datensätze effizient generieren möchten.

Zufallszahlen mit einem bestimmten Mittelwert und einer bestimmten Standardabweichung generieren

VBA-Code - Zufallszahlen mit bestimmtem Mittelwert und Standardabweichung generieren

---

Zufallszahlen mit einem bestimmten Mittelwert und einer bestimmten Standardabweichung generieren

In Excel können Sie eine Reihe von Zufallszahlen erstellen, die Ihrem gewünschten Mittelwert und Ihrer Standardabweichung entsprechen, indem Sie eine Kombination von Standardfunktionen anwenden. Folgen Sie diesen Schritten für eine Lösung, die für kleine bis mittlere Datensätze oder schnelle, ad-hoc Bedarfe geeignet ist:

1. Geben Sie zunächst Ihren Ziel-Mittelwert und Ihre Standardabweichung in zwei separate leere Zellen ein. Für Klarheit und Organisation nehmen wir an, dass Sie Zelle B1 für den erforderlichen Mittelwert und Zelle B2 für die erforderliche Standardabweichung verwenden. Siehe Screenshot:

2. Um die anfänglichen randomisierten Daten zu erstellen, gehen Sie zu Zelle B3 und geben Sie die folgende Formel ein:

=NORMINV(RAND(),$B$1,$B$2)

Nachdem Sie die Formel eingegeben haben, ziehen Sie den Ausfüllknauf nach unten, um so viele Zeilen auszufüllen, wie Sie für Ihren Zufallsdatensatz benötigen. Jede Zelle wird einen Wert auf Basis des angegebenen Mittelwerts und der Standardabweichung generieren.

Tipp: Innerhalb der Formel =NORMINV(RAND(),$B$1,$B$2):

• RAND() erzeugt jedes Mal beim Neuberechnen des Arbeitsblatts eine andere Zufallszahl zwischen 0 und 1.
• $B$1 bezieht sich auf den von Ihnen angegebenen Mittelwert.
• $B$2 bezieht sich auf die gewünschte Standardabweichung.

Für moderne Versionen von Excel (2010 und später) sollten Sie in Betracht ziehen, =NORM.INV(RAND(),$B$1,$B$2)zu verwenden, was funktional dasselbe ist, aber aktualisierte Funktionsnamen widerspiegelt.

3. Um zu überprüfen, dass Ihre generierten Zahlen statistisch Ihrem gewünschten Mittelwert und Ihrer Standardabweichung ähneln, verwenden Sie die folgenden Formeln, um die tatsächlichen Werte Ihres generierten Samples zu berechnen. In Zelle D1 berechnen Sie den Stichprobenmittelwert mit:

=AVERAGE(B3:B16)

In D2 berechnen Sie die Stichprobenstandardabweichung mit:

=STDEV.P(B3:B16)

Tipp:

• B3:B16 ist nur ein Beispielbereich. Passen Sie ihn entsprechend der Anzahl der in Schritt 2 generierten Zufallswerte an.
• Ein größerer Zufallsstichprobenumfang führt durch das Gesetz der großen Zahlen zu einem tatsächlichen Mittelwert und einer Standardabweichung, die näher an Ihren angegebenen Werten liegen.

4. Um Ihre Reihe weiter anzupassen, sodass sie genau Ihrem gewünschten Mittelwert und Ihrer Standardabweichung entspricht, normalisieren Sie Ihre anfänglichen Zufallswerte. Geben Sie in Zelle D3 die folgende Formel ein:

=$B$1+(B3-$D$1)*$B$2/$D$2

Ziehen Sie den Ausfüllknauf nach unten durch so viele Zeilen, wie Sie Zufallszahlen haben. Diese Formel standardisiert Ihre anfänglichen Werte und skaliert sie präzise, um den Mittelwert und die Standardabweichung in B1 und B2 zu erfüllen.

Tipp:

• B1 ist Ihr erforderlicher Mittelwert.
• B2 ist Ihre erforderliche Standardabweichung.
• B3 ist der ursprüngliche Zufallswert.
• D1 ist der Mittelwert dieser ursprünglichen Zufallswerte.
• D2 ist die Standardabweichung dieser ursprünglichen Zufallswerte.

Sie können nun bestätigen, dass die endgültige Menge von Werten Ihre Anforderungen erfüllt, indem Sie ihren Mittelwert und ihre Standardabweichung zur Qualitätskontrolle und Dokumentation neu berechnen.

5. In Zelle D17 berechnen Sie den Mittelwert Ihres finalen Zufallszahlen-Sets mit der folgenden Formel:

=AVERAGE(D3:D16)

Dann berechnen Sie in Zelle D18 die Standardabweichung mit der folgenden Formel:

=STDEV.P(D3:D16)

Tipp: D3:D16 bezieht sich auf Ihren finalen Bereich von Zufallszahlen.

Problembehandlung:

• Wenn Sie einen #WERT!-Fehler sehen, überprüfen Sie alle referenzierten Zellbereiche und stellen Sie sicher, dass keine Formeln auf leere oder ungültige Zellen verweisen.
• Wenn die Formel sich bei jeder Neuberechnung ändert, wählen Sie die endgültigen Zufallszahlen aus, kopieren Sie diese, und verwenden Sie Inhalte einfügen > Werte, um weitere Aktualisierungen zu verhindern.
• Denken Sie daran, dass Zufallszahlengeneratoren in Excel auf Neuberechnung beruhen, daher ist es notwendig, statische Ergebnisse zu speichern, wenn Konsistenz kritisch ist.

---

VBA-Code - Zufallszahlen mit bestimmtem Mittelwert und Standardabweichung generieren

Für Szenarien, in denen Sie schnell eine große Menge an Zufallsdaten erstellen müssen, die einem bestimmten Mittelwert und einer Standardabweichung entsprechen – insbesondere in wiederholenden, automatisierten oder hochvolumigen Fällen – bietet ein VBA-Makro eine zeitsparende Lösung. Mit nur einer einzigen Ausführung können Sie direkt in Ihrer Arbeitsmappe einen vollständigen Datensatz erstellen, wodurch manuelle Wiederholungen reduziert und Fehler beim Kopieren von Formeln minimiert werden.

Dieser Ansatz ist geeignet für:

• Automatisches Generieren von Zufallsdatensätzen für Simulationen, Belastungstests oder Bildungszwecke.
• Situationen, in denen Sie das Ausgabeformat mit minimaler manueller Eingriffe standardisieren möchten.
• Benutzer, die sich mit der Verwendung des VBA-Editors in Excel auskennen.

Im Vergleich zu Formelmethoden kann VBA auch dynamische Anpassungen oder Integration in komplexere Workflows ermöglichen, aber beachten Sie, dass Makros in Ihrer Arbeitsmappe aktiviert sein müssen und möglicherweise explizit im "makrofähigen" .xlsm-Format gespeichert werden müssen.

1. Klicken Sie im Excel-Menüband auf Entwicklertools (falls nicht sichtbar, aktivieren Sie es über Datei > Optionen > Menüband anpassen), dann wählen Sie Visual Basic. Im Fenster Visual Basic for Applications klicken Sie auf Einfügen > Modul und kopieren Sie den folgenden Code in das leere Modulfenster:

Sub GenerateRandomNumbersWithMeanStd()
    Dim outputRange As Range
    Dim meanValue As Double, stdDevValue As Double
    Dim numItems As Long, i As Long
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set outputRange = Application.InputBox("Select the output range", xTitleId, Type:=8)
    meanValue = Application.InputBox("Enter the mean value", xTitleId, "", Type:=1)
    stdDevValue = Application.InputBox("Enter the standard deviation", xTitleId, "", Type:=1)
    
    If outputRange Is Nothing Or meanValue = 0 Or stdDevValue = 0 Then
        MsgBox "Please ensure you have specified all required parameters.", vbExclamation, "KutoolsforExcel"
        Exit Sub
    End If
    
    numItems = outputRange.Count
    Randomize
    
    For i = 1 To numItems
        outputRange.Cells(i).Value = Application.WorksheetFunction.NormInv(Rnd, meanValue, stdDevValue)
    Next i
End Sub

2. Klicken Sie auf die Ausführen Schaltfläche (oder drücken Sie F5) um das Makro zu starten. Ein Dialogfeld fordert Sie auf, den Bereich auszuwählen, in dem Sie die Zufallszahlen ausgeben möchten (z.B. wählen Sie A1:A100 für 100 Werte). Als Nächstes werden Sie aufgefordert, den gewünschten Mittelwert und die Standardabweichung einzugeben. Das Makro füllt den Bereich mit Zufallszahlen, die Ihren Spezifikationen entsprechen.

Tipps und Problembehandlung:

• VBA verwendet die Excel-Funktion NormInv, um normalverteilte Zahlen zu generieren – überprüfen Sie immer, ob Ihre Version dies unterstützt; für ältere Excel-Versionen muss die Funktion eventuell NORMINV heißen.
• Der Zufallsstartwert wird mit Randomize für unterschiedliche Ergebnisse bei jedem Lauf gesetzt.
• Wenn Sie reproduzierbare Ergebnisse wünschen, kommentieren Sie die Randomize-Zeile aus oder entfernen Sie sie.
• Das Makro überschreibt alle vorhandenen Daten im ausgewählten Ausgabebereich, stellen Sie also sicher, dass Sie bei Bedarf einen leeren Bereich auswählen.
• Wenn Sie unangemessene Werte eingeben (z. B. eine negative oder Null-Standardabweichung), wird das Makro nicht fortgesetzt und zeigt eine Warnmeldung an.

---

Verwandte Artikel:

• Zufallszahlen ohne Wiederholung in Excel generieren
• Positive oder negative Zufallszahlen in Excel generieren
• Änderung von Zufallszahlen in Excel stoppen
• Zufällige Ja/Nein-Antworten in Excel generieren