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

Die Erzeugung einer Reihe von Zufallszahlen mit einem spezifischen Mittelwert und einer bestimmten Standardabweichung ist eine häufige Anforderung in Bereichen wie statistischer Simulation, Algorithmustests oder Modellierungsprozessen in den Bereichen Finanzen, Ingenieurwesen und Bildung. Allerdings bietet Excel keine direkte integrierte Funktion zur sofortigen Generierung einer solchen Liste von Zufallszahlen, die sowohl auf einen gegebenen Mittelwert als auch auf eine bestimmte Standardabweichung zugeschnitten sind. Wenn Sie regelmäßig 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 Datenqualität steigern.

In diesem Tutorial stellen wir praktische Methoden vor, um Zufallszahlen basierend auf dem von Ihnen angegebenen Mittelwert und der Standardabweichung zu generieren. Dazu erhalten Sie detaillierte Schritt-für-Schritt-Anweisungen, Erläuterungen der Formelparameter sowie Expertentipps zur Fehlervermeidung und Fehlerbehebung. Darüber hinaus bieten wir eine VBA-Makro-Lösung für Benutzer an, die diesen Prozess automatisieren oder große Datensätze effizient generieren möchten.

Zufallszahl mit vorgegebenem Mittelwert und Standardabweichung generieren

VBA-Code – Zufallszahlen mit angegebenem Mittelwert und Standardabweichung generieren

---

Zufallszahl mit vorgegebenem Mittelwert und 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. Zur besseren Übersicht und Organisation nehmen wir an, dass Sie Zelle B1 für den gewünschten Mittelwert und Zelle B2 für die gewünschte Standardabweichung verwenden. Siehe Screenshot:

2. Um die ursprünglichen Zufallsdaten 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üllkursor nach unten, um so viele Zeilen auszufüllen, wie Sie für Ihren Zufallsdatensatz benötigen. Jede Zelle wird einen Wert basierend auf dem angegebenen Mittelwert und der Standardabweichung generieren.

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

• RAND() erzeugt jedes Mal beim Neuberechnen des Arbeitsblatts eine andere Zufallswahrscheinlichkeit 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 höher) sollten Sie in Betracht ziehen, =NORM.INV(RAND(),$B$1,$B$2)zu verwenden, was funktional dasselbe ist, aber aktualisierte Funktionsnamen widerspiegelt.

3. Um sicherzustellen, dass Ihre generierten Zahlen statistisch Ihrem gewünschten Mittelwert und Ihrer Standardabweichung entsprechen, 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 Zufallswerte an, die Sie in Schritt 2 generiert haben.
• Ein größerer Zufallsstichprobenumfang führt aufgrund des Gesetzes der großen Zahlen zu einem tatsächlichen Mittelwert und einer tatsächlichen 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 ursprünglichen Zufallswerte. Geben Sie in Zelle D3 die folgende Formel ein:

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

Ziehen Sie den Ausfüllkursor durch so viele Zeilen, wie Sie Zufallszahlen haben. Diese Formel standardisiert Ihre ursprünglichen Werte und skaliert sie genau, 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 Reihe von Werten Ihren Anforderungen entspricht, indem Sie deren Mittelwert und Standardabweichung für Qualitätskontrolle und Dokumentationszwecke neu berechnen.

5. In Zelle D17 berechnen Sie den Mittelwert Ihres endgültigen Zufallszahlensatzes 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 den Bereich Ihrer finalen Zufallszahlen.

Fehlerbehebung:

• 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 bei jeder Neuberechnung weiterhin ä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 von der Neuberechnung abhängen, daher ist es notwendig, statische Ergebnisse zu speichern, wenn Konsistenz wichtig ist.

---

VBA-Code – Zufallszahlen mit angegebenem Mittelwert und Standardabweichung generieren

Für Szenarien, in denen Sie schnell eine große Menge an Zufallsdaten erzeugen müssen, die einem bestimmten Mittelwert und einer bestimmten 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 einen vollständigen Datensatz direkt in Ihrer Arbeitsmappe 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, Stresstests oder Lehrdemonstrationen.
• Situationen, in denen Sie das Ausgabeformat standardisieren möchten, mit minimaler manueller Eingriff.
• Benutzer, die mit dem VBA-Editor in Excel vertraut sind.

Im Vergleich zu Formelmethoden kann VBA auch dynamische Anpassungen oder die 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 auf der Excel-Menüleiste auf Entwicklertools (falls nicht sichtbar, aktivieren Sie es über Datei > Optionen > Menüband anpassen), dann wählen Sie Visual Basic. Im Visual-Basic-für-Anwendungen-Fenster 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

2Klicken 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. A1:A100 für 100 Werte). Danach werden Sie aufgefordert, den gewünschten Mittelwert und die gewünschte Standardabweichung einzugeben. Das Makro füllt den Bereich mit Zufallszahlen, die Ihren Spezifikationen entsprechen.

Tipps und Fehlerbehebung:

• VBA verwendet Excels NormInv-Funktion, um normalverteilte Zahlen zu generieren – überprüfen Sie immer, ob Ihre Version dies unterstützt; bei älteren Excel-Versionen muss die Funktion möglicherweise NORMINV heißen.
• Der Zufallsstartwert wird mit Randomize für unterschiedliche Ergebnisse bei jedem Durchlauf festgelegt.
• 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 einen leeren Bereich auswählen, falls nötig.
• 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
• Ändernde Zufallszahlen in Excel stoppen
• Zufälliges Ja oder Nein in Excel generieren