Genereer willekeurig getal op basis van een gegeven gemiddelde en standaarddeviatie in Excel

Het genereren van een reeks willekeurige getallen met een gespecificeerd gemiddelde en standaarddeviatie is een veelvoorkomende vereiste in gebieden zoals statistische simulatie, het testen van algoritmen of het modelleren van processen in sectoren zoals financiën, techniek en onderwijs. Echter, Excel biedt geen direct ingebouwde functie om zo'n lijst met willekeurige getallen te genereren die afgestemd zijn op zowel een gegeven gemiddelde als standaarddeviatie. Als je regelmatig random testdata moet creëren die statistisch overeenkomt met bepaalde kenmerken, kan het begrijpen van hoe dit te bereiken de efficiëntie van je workflow en de kwaliteit van je data enorm verbeteren.

In deze tutorial introduceren we praktische manieren om willekeurige getallen te genereren op basis van het gemiddelde en de standaarddeviatie die je specificeert, met gedetailleerde stap-voor-stap instructies, uitleg van formuleparameters en experts tips voor foutpreventie en probleemoplossing. Bovendien bieden we een VBA-macro-oplossing voor gebruikers die dit proces automatisch willen uitvoeren of grote datasets efficiënt willen genereren.

Genereer willekeurig getal op basis van gegeven gemiddelde en standaarddeviatie

VBA-code - Genereer willekeurige getallen met gespecificeerd gemiddelde en standaarddeviatie

---

Genereer willekeurig getal op basis van gegeven gemiddelde en standaarddeviatie

In Excel kun je een set willekeurige getallen maken die passen bij je gewenste gemiddelde en standaarddeviatie door een combinatie van standaardfuncties toe te passen. Volg deze stappen voor een oplossing die geschikt is voor kleine tot middelgrote datasets of voor snelle, ad-hoc behoeften:

1. Voer eerst je doelgemiddelde en standaarddeviatie in twee aparte lege cellen in. Voor duidelijkheid en organisatie kun je bijvoorbeeld cel B1 gebruiken voor je vereiste gemiddelde en cel B2 voor je vereiste standaarddeviatie. Zie screenshot:

2. Om de initiële gerandomiseerde gegevens te maken, ga je naar cel B3 en voer je de volgende formule in:

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

Nadat je de formule hebt ingevoerd, sleep je de vulgreep omlaag om zoveel rijen te vullen als je nodig hebt voor je random dataset. Elke cel genereert een waarde op basis van het gespecificeerde gemiddelde en standaarddeviatie.

Tip: Binnen de formule =NORMINV(RAND(),$B$1,$B$2):

• RAND() produceert elke keer dat het werkblad herberekend wordt een ander willekeurig getal tussen 0 en 1.
• $B$1 verwijst naar het gemiddelde dat je hebt gespecificeerd.
• $B$2 verwijst naar de gewenste standaarddeviatie.

Voor moderne versies van Excel (2010 en later), overweeg dan om =NORM.INV(RAND(),$B$1,$B$2)te gebruiken, wat functioneel hetzelfde is maar gebruik maakt van bijgewerkte functienamen.

3. Om te controleren of je gegenereerde getallen statistisch overeenkomen met je bedoelde gemiddelde en standaarddeviatie, gebruik je de volgende formules om de werkelijke waarden van je gegenereerde steekproef te berekenen. Bereken in cel D1 het steekproefgemiddelde met:

=AVERAGE(B3:B16)

Bereken in D2 de steekproefstandaarddeviatie met:

=STDEV.P(B3:B16)

Tip:

• B3:B16 is slechts een voorbeeldbereik. Pas dit aan op basis van hoeveel willekeurige waarden je hebt gegenereerd in Stap 2.
• Een grotere willekeurige steekproef resulteert in een werkelijk gemiddelde en standaarddeviatie die dichter bij je gespecificeerde waarden liggen, vanwege de wet van de grote aantallen.

4. Om je reeks verder aan te passen zodat deze exact overeenkomt met je bedoelde gemiddelde en standaarddeviatie, normaliseer je je initiële willekeurige waarden. Voer in cel D3 de volgende formule in:

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

Sleep de vulgreep omlaag door zoveel rijen als je willekeurige getallen hebt. Deze formule standaardiseert je initiële waarden en schaalt ze precies om aan het gemiddelde en de standaarddeviatie in B1 en B2 te voldoen.

Tip:

• B1 is je vereiste gemiddelde.
• B2 is je vereiste standaarddeviatie.
• B3 is de originele willekeurige waarde.
• D1 is het gemiddelde van die originele willekeurige waarden.
• D2 is de standaarddeviatie van die originele willekeurige waarden.

Je kunt nu bevestigen dat de uiteindelijke set waarden aan je vereisten voldoet door hun gemiddelde en standaarddeviatie opnieuw te berekenen voor kwaliteitsborging en documentatie.

5. Bereken in cel D17 het gemiddelde van je definitieve reeks willekeurige getallen met de volgende formule:

=AVERAGE(D3:D16)

Bereken vervolgens in cel D18 de standaarddeviatie met de volgende formule:

=STDEV.P(D3:D16)

Tip: D3:D16 verwijst naar je definitieve bereik van willekeurige getallen.

Probleemoplossing:

• Als je een #WAARDE! fout ziet, controleer dan alle gerefereerde celbereiken en zorg ervoor dat geen formules verwijzen naar lege of ongeldige cellen.
• Als de formule blijft veranderen elke keer dat je herberekent, selecteer dan de definitieve willekeurige getallen, kopieer ze en gebruik Plakken Speciaal > Waarden om verdere updates te voorkomen.
• Onthoud dat willekeurige generatoren in Excel afhankelijk zijn van herberekening, dus het opslaan van statische resultaten is noodzakelijk wanneer consistentie cruciaal is.

---

VBA-code - Genereer willekeurige getallen met gespecificeerd gemiddelde en standaarddeviatie

Voor scenario's waarin je snel een grote hoeveelheid willekeurige data moet produceren die overeenkomt met een gespecificeerd gemiddelde en standaarddeviatie—vooral in herhalende, geautomatiseerde of grote hoeveelheden—aanbiedt een VBA-macro een tijdbesparende oplossing. Met slechts één uitvoering kun je een volledige dataset rechtstreeks in je werkboek creëren, wat handmatige herhaling reduceert en fouten bij het kopiëren van formules minimaliseert.

Deze aanpak is geschikt voor:

• Automatisch genereren van willekeurige datasets voor simulaties, stress tests of educatieve demonstraties.
• Situaties waarin je de output-indeling wilt standaardiseren met minimale handmatige tussenkomst.
• Gebruikers die bekend zijn met het gebruik van de VBA-editor in Excel.

In vergelijking met formulemethoden kan VBA ook dynamische aanpassingen of integratie met meer complexe workflows toestaan, maar houd er rekening mee dat macro's moeten worden ingeschakeld in je werkboek en mogelijk expliciet moeten worden opgeslagen in "macro-enabled" .xlsm-formaat.

1. Klik op de Excel-ribbon op Ontwikkelaarshulpmiddelen (als deze niet zichtbaar is, schakel deze dan in via Bestand > Opties > Aanpassen lint), selecteer dan Visual Basic. In het venster Visual Basic for Applications klik je op Invoegen > Module en kopieer je de volgende code in het lege modulevenster:

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. Klik op de Uitvoeren knop (of druk op F5) om de macro te starten. Een dialoogvenster vraagt je om het bereik te selecteren waar je de willekeurige getallen wilt uitvoeren (bijvoorbeeld, selecteer A1:A100 voor 100 waarden). Vervolgens wordt je gevraagd om het gewenste gemiddelde en de standaarddeviatie in te voeren. De macro zal het bereik vullen met willekeurige getallen die overeenkomen met je specificaties.

Tips en Probleemoplossing:

• VBA gebruikt Excel's NormInv-functie om normaal verdeelde getallen te genereren—controleer altijd of je versie dit ondersteunt; voor oudere Excel-versies moet de functie mogelijk NORMINV zijn.
• De random seed wordt ingesteld met Randomize voor variabele resultaten bij elke uitvoering.
• Als je reproduceerbare resultaten wilt, commentarieer of verwijder dan de Randomize-regel.
• De macro overschrijft alle bestaande gegevens in het geselecteerde uitvoerbereik, dus zorg ervoor dat je een leeg gebied selecteert indien nodig.
• Als je ongeschikte waarden invoert (bijvoorbeeld een negatieve of nul standaarddeviatie), zal de macro niet doorgaan en een waarschuwingstekst tonen.

---

Gerelateerde Artikelen:

• Genereer willekeurige getallen zonder herhaling in Excel
• Genereer positieve of negatieve willekeurige getallen in Excel
• Stop met het veranderen van willekeurige getallen in Excel
• Genereer willekeurig ja of nee in Excel