Generieren Sie Zufallszahlen mit einem vorgegebenen Mittelwert und einer vorgegebenen Standardabweichung in Excel

Das Generieren einer Reihe von Zufallszahlen mit einem vorgegebenen Mittelwert und einer festgelegten Standardabweichung ist eine häufige Anforderung – etwa in der statistischen Simulation, beim Testen von Algorithmen oder bei der Prozessmodellierung in Bereichen wie Finanzen, Ingenieurwesen und Bildung. Excel bietet jedoch keine direkte integrierte Funktion, um auf einen Schlag eine solche Liste zu erstellen, die exakt diesen statistischen Vorgaben entspricht. Wenn Sie regelmäßig randomisierte Testdaten mit definierten statistischen Eigenschaften benötigen, kann das Wissen, wie sich dies umsetzen lässt, Ihre Arbeitsablaufeffizienz und Datenqualität deutlich steigern.

In diesem Tutorial stellen wir praktische Methoden zum Generieren von Zufallszahlen basierend auf Ihrem vorgegebenen Mittelwert und Ihrer vorgegebenen Standardabweichung vor – inklusive detaillierter Schritt-für-Schritt-Anleitungen, klarer Erklärungen der Formelparameter sowie wertvoller Expertentipps zur Fehlervermeidung und Fehlersuche. Außerdem bieten wir eine VBA-Makrolösung für alle, die diesen Prozess automatisieren oder effizient große Datensätze erstellen möchten.

Generieren Sie Zufallszahlen mit vorgegebenem Mittelwert und Standardabweichung

VBA-Code – Generieren von Zufallszahlen mit festgelegtem Mittelwert und Standardabweichung

---

Generieren Sie Zufallszahlen mit vorgegebenem Mittelwert und Standardabweichung

In Excel können Sie mithilfe einer Kombination integrierter Funktionen eine Reihe von Zufallszahlen generieren, die Ihren gewünschten Mittelwert und Ihre gewünschte Standardabweichung erfüllen. Gehen Sie dazu wie folgt vor – diese Lösung eignet sich ideal für kleine bis mittelgroße Datensätze oder schnelle Ad-hoc-Anforderungen:

1. Geben Sie zunächst Ihren gewünschten Mittelwert und Ihre gewünschte Standardabweichung in zwei separate leere Zellen ein. Für mehr Übersichtlichkeit und bessere Organisation verwenden wir beispielsweise Zelle B1 für den Zielmittelwert und Zelle B2 für die Zielstandardabweichung. Siehe Screenshot:

2. Um die ursprünglichen Zufallsdaten zu generieren, wechseln Sie zu Zelle B3 und geben Sie die folgende Formel ein:

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

Geben Sie nach der Eingabe der Formel das Ausfüllkästchen nach unten, um so viele Zeilen auszufüllen, wie Sie für Ihren Zufallsdatensatz benötigen. Jede Zelle generiert dabei einen Wert basierend auf dem festgelegten Mittelwert und der festgelegten Standardabweichung.

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

• RAND() generiert bei jeder Neuberechnung des Arbeitsblatts eine neue Zufallszahl zwischen 0 und 1.
• $B$1 bezieht sich auf den von Ihnen festgelegten Mittelwert.
• $B$2 bezieht sich auf die gewünschte Standardabweichung.

Verwenden Sie in neueren Excel-Versionen (2010 und höher) stattdessen =NORM.INV(RAND();$B$1;$B$2), was funktional identisch ist, jedoch aktualisierte Funktionsnamen widerspiegelt.

3. Um zu überprüfen, ob Ihre generierten Zahlen statistisch Ihren vorgesehenen Mittelwert und Ihre vorgesehene Standardabweichung widerspiegeln, berechnen Sie den tatsächlichen Wert Ihrer generierten Stichprobe mithilfe der folgenden Formeln. Ermitteln Sie in Zelle D1 den Stichprobenmittelwert mit:

=AVERAGE(B3:B16)

Berechnen Sie in D2 die Stichprobenstandardabweichung mit:

=STDEV.P(B3:B16)

Tipp:

• B3:B16 ist lediglich ein Beispielbereich. Passen Sie ihn entsprechend der Anzahl der in Schritt 2 generierten Zufallswerte an.
• Dank des Gesetzes der großen Zahlen liefert eine größere Zufallsstichprobe einen tatsächlichen Mittelwert und eine tatsächliche Standardabweichung, die Ihren vorgegebenen Werten deutlich näherkommen.

4. Um Ihre Reihe so anzupassen, dass sie exakt Ihren vorgesehenen Mittelwert und Ihre vorgesehene Standardabweichung erreicht, normalisieren Sie Ihre ursprünglichen Zufallswerte. Geben Sie dazu in Zelle D3 die folgende Formel ein:

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

Ziehen Sie das Ausfüllkästchen nach unten über so viele Zeilen, wie Sie Zufallszahlen benötigen. Diese Formel normalisiert Ihre ursprünglichen Werte und skaliert sie präzise auf den in B1 und B2 angegebenen Mittelwert und die Standardabweichung.

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 der endgültige Wertesatz Ihren Anforderungen entspricht, indem Sie zur Qualitätssicherung und Dokumentation erneut dessen Mittelwert und Standardabweichung berechnen.

5. Berechnen Sie in Zelle D17 den Mittelwert Ihres endgültigen Zufallszahlen-Sets mithilfe der folgenden Formel:

=AVERAGE(D3:D16)

Berechnen Sie anschließend in Zelle D18 die Standardabweichung mit der folgenden Formel:

=STDEV.P(D3:D16)

Tipp: D3:D16 bezieht sich auf Ihren endgültigen Bereich der Zufallszahlen.

Fehlerbehebung:

• Wenn Sie einen #WERT!-Fehler sehen, überprüfen Sie alle referenzierten Zellbereiche noch einmal und stellen Sie sicher, dass keine Formeln auf leere oder ungültige Zellen verweisen.
• Wenn sich die Formel bei jeder Neuberechnung ändert, markieren Sie die endgültigen Zufallszahlen, kopieren Sie sie und verwenden Sie Inhalte einfügen > Werte, um weitere Aktualisierungen zu verhindern.
• Beachten Sie, dass Zufallsgeneratoren in Excel auf Neuberechnungen basieren – daher müssen die Ergebnisse als statische Werte gespeichert werden, wenn Konsistenz entscheidend ist.

---

VBA-Code – Generieren von Zufallszahlen mit festgelegtem Mittelwert und Standardabweichung

Für Szenarien, in denen Sie schnell eine große Menge an Zufallsdaten mit einem vorgegebenen Mittelwert und einer festgelegten Standardabweichung generieren müssen – insbesondere bei wiederholten, automatisierten oder volumenintensiven Anwendungsfällen – bietet ein VBA-Makro eine zeitsparende Lösung: Mit nur einem einzigen Aufruf erstellen Sie direkt in Ihrer Arbeitsmappe einen vollständigen Datensatz, reduzieren manuelle Wiederholungen und minimieren Fehler beim Kopieren von Formeln.

Dieser Ansatz eignet sich für:

• Automatisches Generieren von Zufallsdatensätzen für Simulationen, Stresstests oder Lehrdemonstrationen.
• Situationen, in denen Sie das Ausgabeformat mit minimalem manuellem Aufwand standardisieren möchten.
• Benutzer, die mit dem VBA-Editor in Excel vertraut sind.

Im Vergleich zu Formelmethoden ermöglicht VBA zudem dynamische Anpassungen und die nahtlose Integration in komplexere Arbeitsabläufe. Beachten Sie jedoch, dass Makros in Ihrer Arbeitsmappe aktiviert sein müssen und diese gegebenenfalls explizit im makrofähigen Format „.xlsm“ gespeichert werden muss.

1. Klicken Sie im Excel-Menüband auf Entwicklertools(sofern nicht sichtbar, aktivieren Sie diese über)Datei > Optionen > Menüband anpassen), und wählen Sie anschließend Visual Basic. Klicken Sie im Visual-Basic-for-Applications-Fenster auf Einfügen > Modul, und fügen Sie den folgenden Code in das leere Modulfenster ein:

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 Schaltfläche Ausführen(oder drücken Sie)F5), um das Makro zu starten. Anschließend erscheint ein Dialogfeld, in dem Sie den Bereich auswählen, in den die Zufallszahlen eingefügt werden sollen (z. B. A1:A100 für 100 Werte). Danach geben Sie den gewünschten Mittelwert und die Standardabweichung ein – und schon füllt das Makro den ausgewählten Bereich mit Zufallszahlen gemäß Ihren Vorgaben.

Tipps und Fehlerbehebung:

• VBA verwendet die Excel-Funktion NormInv, um normalverteilte Zahlen zu generieren – prüfen Sie stets, ob Ihre Version dies unterstützt. In älteren Excel-Versionen muss die Funktion möglicherweise als NORMINV geschrieben werden.
• Der Zufallsstartwert wird mit Randomize gesetzt, um bei jedem Durchlauf unterschiedliche Ergebnisse zu erhalten.
• Wenn Sie reproduzierbare Ergebnisse wünschen, kommentieren Sie die Zeile Randomize aus oder entfernen Sie sie.
• Das Makro überschreibt sämtliche vorhandenen Daten im ausgewählten Platzierungsbereich der Liste – wählen Sie daher gegebenenfalls einen leeren Bereich.
• Wenn Sie ungeeignete Werte eingeben – beispielsweise eine negative Standardabweichung oder den Wert null –, wird das Makro nicht ausgeführt und zeigt eine Warnmeldung an.

---

Verwandte Artikel:

• Generieren Sie Zufallszahlen ohne Wiederholung in Excel
• Generieren Sie positive oder negative Zufallszahlen in Excel
• Verhindern Sie die Änderung von Zufallszahlen in Excel
• Generieren Sie zufällige Ja- oder Nein-Antworten in Excel