Genera numeri casuali dati una certa media e deviazione standard in Excel

La generazione di un insieme di numeri casuali con una media e una deviazione standard specificate è un requisito comune in settori come la simulazione statistica, il collaudo di algoritmi o la modellazione di processi nei campi della finanza, dell'ingegneria e dell'istruzione. Tuttavia, Excel non fornisce una funzione incorporata diretta per generare istantaneamente un elenco di numeri casuali su misura per una data media e deviazione standard. Se hai spesso bisogno di creare dati di test randomizzati che corrispondano statisticamente a determinate caratteristiche, sapere come fare può migliorare notevolmente l'efficienza del tuo flusso di lavoro e la qualità dei dati.

In questo tutorial, introdurremo metodi pratici per generare numeri casuali basati sulla media e la deviazione standard che specifichi, con istruzioni dettagliate passo-passo, spiegazioni dei parametri delle formule e suggerimenti da esperti per la prevenzione degli errori e la risoluzione dei problemi. Inoltre, forniamo una soluzione macro VBA per gli utenti che hanno bisogno di automatizzare questo processo o generare grandi set di dati in modo efficiente.

Genera numeri casuali dati una media e una deviazione standard

Codice VBA - Genera numeri casuali con media e deviazione standard specificate

---

Genera numeri casuali dati una media e una deviazione standard

In Excel, puoi creare un insieme di numeri casuali che si adattino alla tua media desiderata e alla deviazione standard applicando una combinazione di funzioni standard. Segui questi passaggi per una soluzione adatta a set di dati di piccole o medie dimensioni o per esigenze rapide e ad hoc:

1. Per prima cosa, inserisci la tua media obiettivo e la deviazione standard in due celle vuote separate. Per chiarezza e organizzazione, diciamo che usi la cella B1 per la media richiesta e la cella B2 per la deviazione standard richiesta. Vedi screenshot:

2. Per creare i dati randomizzati iniziali, vai nella cella B3 e inserisci la seguente formula:

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

Dopo aver inserito la formula, trascina la maniglia di riempimento verso il basso per riempire tutte le righe necessarie per il tuo set di dati casuali. Ogni cella genererà un valore basato sulla media e la deviazione standard specificate.

Suggerimento: All'interno della formula =NORMINV(RAND(),$B$1,$B$2):

• RAND() genera una diversa probabilità casuale tra 0 e 1 ogni volta che il foglio di lavoro viene ricalcolato.
• $B$1 si riferisce al valore medio che hai specificato.
• $B$2 si riferisce alla deviazione standard desiderata.

Per le versioni moderne di Excel (2010 e successive), considera l'uso di =NORM.INV(RAND(),$B$1,$B$2)che è funzionalmente identico ma riflette nomi di funzione aggiornati.

3. Per verificare che i numeri generati statisticamente assomiglino alla tua media e deviazione standard previste, utilizza le seguenti formule per calcolare i valori effettivi del tuo campione generato. Nella cella D1, calcola la media del campione con:

=AVERAGE(B3:B16)

In D2, calcola la deviazione standard del campione con:

=STDEV.P(B3:B16)

Suggerimento:

• B3:B16 è solo un intervallo di esempio. Adattalo in base al numero di valori casuali che hai generato nel Passo 2.
• Un campione casuale più ampio produce una media e una deviazione standard effettive più vicine ai tuoi valori specificati, a causa della legge dei grandi numeri.

4. Per regolare ulteriormente la tua serie in modo che corrisponda esattamente alla tua media e deviazione standard desiderate, normalizza i tuoi valori casuali iniziali. Nella cella D3, inserisci la seguente formula:

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

Trascina la maniglia di riempimento verso il basso attraverso tutte le righe in cui hai numeri casuali. Questa formula standardizza i tuoi valori iniziali e li scala precisamente per soddisfare la media e la deviazione standard in B1 e B2.

Suggerimento:

• B1 è la tua media richiesta.
• B2 è la tua deviazione standard richiesta.
• B3 è il valore casuale originale.
• D1 è la media di quei valori casuali originali.
• D2 è la deviazione standard di quei valori casuali originali.

Ora puoi confermare che l'insieme finale di valori soddisfa i tuoi requisiti ricalcolandone la media e la deviazione standard per scopi di garanzia della qualità e documentazione.

5. Nella cella D17, calcola la media del tuo set finale di numeri casuali con la seguente formula:

=AVERAGE(D3:D16)

Poi nella cella D18, calcola la deviazione standard con la formula sottostante:

=STDEV.P(D3:D16)

Suggerimento: D3:D16 si riferisce all'intervallo dei tuoi numeri casuali finalizzati.

Risoluzione dei problemi:

• Se vedi un errore #VALORE!, ricontrolla tutti gli intervalli di celle referenziati e assicurati che nessuna formula faccia riferimento a celle vuote o non valide.
• Se la formula continua a cambiare ogni volta che ricalcoli, seleziona i numeri casuali finali, copiali e utilizza Incolla Speciale > Valori per evitare ulteriori aggiornamenti.
• Ricorda che i generatori casuali in Excel si basano sul ricalcolo, quindi salvare risultati statici è necessario quando la coerenza è critica.

---

Codice VBA - Genera numeri casuali con media e deviazione standard specificate

Per scenari in cui hai bisogno di produrre rapidamente una grande quantità di dati casuali che corrispondono a una media e una deviazione standard specificate—specialmente in casi ripetitivi, automatizzati o ad alto volume—una macro VBA offre una soluzione che fa risparmiare tempo. Con una sola esecuzione, puoi creare un set di dati completo direttamente nel tuo foglio di lavoro, riducendo la ripetizione manuale e minimizzando gli errori di copia delle formule.

Questo approccio è adatto per:

• Generazione automatica di set di dati casuali per simulazioni, stress testing o dimostrazioni didattiche.
• Situazioni in cui desideri standardizzare il formato di output con il minimo intervento manuale.
• Utenti a proprio agio nell'utilizzo dell'Editor VBA in Excel.

Rispetto ai metodi basati su formule, VBA può consentire anche aggiustamenti dinamici o integrazioni con flussi di lavoro più complessi, ma tieni presente che le macro devono essere abilitate nel tuo foglio di lavoro e potrebbero richiedere un salvataggio esplicito in formato .xlsm "macro-abilitato".

1. Sulla barra multifunzione di Excel, fai clic su Strumenti di Sviluppo (se non visibile, abilitalo tramite File > Opzioni > Personalizza Barra Multifunzione), poi seleziona Visual Basic. Nella finestra di Visual Basic for Applications, fai clic su Inserisci > Modulo, e copia il codice seguente nella finestra modulo vuota:

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

2Fai clic su Esegui (o premi F5) per avviare la macro. Una finestra di dialogo ti chiederà di selezionare l'intervallo in cui desideri visualizzare i numeri casuali (ad esempio, seleziona A1:A100 per 100 valori). Successivamente, ti verrà chiesto di inserire la media e la deviazione standard desiderate. La macro compilerà l'intervallo con numeri casuali che corrispondono alle tue specifiche.

Suggerimenti e Risoluzione dei Problemi:

• VBA utilizza la funzione NormInv di Excel per generare numeri distribuiti normalmente—assicurati sempre che la tua versione supporti questa funzionalità; per versioni precedenti di Excel, la funzione potrebbe dover essere NORMINV.
• Il seed casuale è impostato con Randomize per risultati variabili in ogni esecuzione.
• Se desideri risultati riproducibili, commenta o rimuovi la riga Randomize.
• La macro sovrascriverà qualsiasi dato esistente nell'intervallo di output selezionato, quindi assicurati di scegliere un'area vuota se necessario.
• Se inserisci valori inappropriati (ad esempio, una deviazione standard negativa o zero), la macro non procederà e mostrerà un messaggio di avviso.

---

Articoli correlati:

• Genera numeri casuali senza ripetizioni in Excel
• Genera numeri casuali positivi o negativi in Excel
• Interrompi la variazione dei numeri casuali in Excel
• Genera casualmente "sì" o "no" in Excel