Generare numeri casuali con una media e una deviazione standard assegnate in Excel

Generare un insieme di numeri casuali con una media e una deviazione standard specifiche è un’esigenza comune in ambiti come la simulazione statistica, il collaudo di algoritmi o la modellazione di processi nei settori finanziario, ingegneristico e didattico. Tuttavia, Excel non offre una funzione predefinita diretta per generare immediatamente un elenco di numeri casuali calibrato su entrambi questi parametri. Se hai spesso bisogno di creare dati di test randomizzati che rispecchino statisticamente caratteristiche precise, sapere come ottenere questo risultato può migliorare notevolmente l’efficienza del tuo flusso di lavoro e la qualità dei dati.

In questa guida ti mostreremo metodi pratici per generare numeri casuali in base a una media e a una deviazione standard specifiche, con istruzioni dettagliate passo dopo passo, spiegazioni chiare sui parametri delle formule e suggerimenti da esperti per evitare errori e risolvere rapidamente eventuali problemi. In più, includiamo una soluzione basata su macro VBA pensata per chi desidera automatizzare il processo o generare grandi set di dati in modo rapido ed efficiente.

Generare numeri casuali con media e deviazione standard assegnate

Codice VBA – Generare numeri casuali con media e deviazione standard specificate

---

Generare numeri casuali con media e deviazione standard assegnate

In Excel puoi generare un insieme di numeri casuali con la media e la deviazione standard desiderate combinando funzioni predefinite. Segui questi passaggi per una soluzione ideale per set di dati di dimensioni piccole o moderate o per esigenze occasionali:

1. Innanzitutto, inserisci la media e la deviazione standard desiderate in due celle vuote separate. Per maggiore chiarezza e organizzazione, ipotizziamo di utilizzare la cella B1 per la media richiesta e la cella B2 per la deviazione standard richiesta. Vedi screenshot:

2. Per generare i dati casuali iniziali, vai alla cella B3 e inserisci la seguente formula:

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

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

Suggerimento:Nella formula =NORMINV(RAND(),$B$1,$B$2):

• RAND() genera ogni volta un numero casuale compreso tra 0 e 1, diverso a ogni ricalcolo del foglio di lavoro.
• $B$1 si riferisce al valore medio specificato.
• $B$2 indica la 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 identica ma riporta i nomi aggiornati delle funzioni.

3. Per verificare che i numeri generati rispecchino statisticamente la media e la deviazione standard previste, utilizza le seguenti formule per calcolare il valore effettivo del campione generato. Nella cella D1, calcola la media del campione con:

=AVERAGE(B3:B16)

Nella cella D2, calcola la deviazione standard del campione con:

=STDEV.P(B3:B16)

Suggerimento:

• B3:B16 è solo un intervallo di esempio. Modificalo in base al numero di valori casuali generati nel Passaggio 2.
• Un campione casuale più ampio produce una media e una deviazione standard effettive più vicine ai valori specificati, grazie alla legge dei grandi numeri.

4. Per adattare ulteriormente la serie in modo che corrisponda esattamente alla media e alla 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 il quadratino di riempimento verso il basso per tutte le righe contenenti numeri casuali. Questa formula normalizza i tuoi valori iniziali e li scala con precisione in modo da rispettare la media e la deviazione standard specificate 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.

Puoi ora confermare che il set finale di valori soddisfi i requisiti ricalcolandone la media e la deviazione standard per il controllo qualità e la documentazione.

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

=AVERAGE(D3:D16)

Quindi, nella cella D18, calcola la deviazione standard con la formula seguente:

=STDEV.P(D3:D16)

Suggerimento: D3:D16 indica l’intervallo dei tuoi numeri casuali finalizzati.

Risoluzione dei problemi:

• Se visualizzi un errore #VALORE!, ricontrolla attentamente 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 usa Incolla speciale > Valori per evitare ulteriori aggiornamenti.
• Ricorda che i generatori casuali in Excel si basano sul ricalcolo, quindi assicurati di salvare risultati statici quando la coerenza è fondamentale.

---

Codice VBA – Generare numeri casuali con media e deviazione standard specificate

Per scenari in cui devi generare rapidamente una grande quantità di dati casuali conformi a una media e a una deviazione standard specifiche—soprattutto in contesti ripetitivi, automatizzati o ad alto volume—una macro VBA rappresenta una soluzione efficiente e risparmia tempo. Con un’unica esecuzione, puoi creare un intero set di dati direttamente nella cartella di lavoro, eliminando la ripetizione manuale e riducendo al minimo gli errori legati alla copia delle formule.

Questo approccio è adatto a:

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

Rispetto ai metodi basati su formule, VBA permette anche regolazioni dinamiche e integrazioni con flussi di lavoro più complessi, ma ricorda che le macro devono essere abilitate nella cartella di lavoro e potrebbero richiedere il salvataggio esplicito nel formato «abilitato alle macro» (.xlsm).

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

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. Fai clic sul pulsante Esegui(o premi)F5) per avviare la macro. Ti apparirà una finestra di dialogo in cui dovrai selezionare l’intervallo in cui inserire i numeri casuali (ad esempio, A1:A100 per generare 100 valori). Subito dopo, ti verrà chiesto di specificare la media e la deviazione standard desiderate. La macro popolerà automaticamente l’intervallo selezionato con numeri casuali che rispettano i tuoi parametri.

Suggerimenti e risoluzione dei problemi:

• VBA utilizza la funzione NormInv di Excel per generare numeri distribuiti normalmente: assicurati sempre che la tua versione la supporti. Nelle versioni precedenti di Excel, la funzione potrebbe essere NORMINV.
• Il seme casuale viene impostato con Randomize per ottenere risultati diversi a ogni esecuzione.
• Se desideri risultati riproducibili, commenta o rimuovi la riga Randomize.
• La macro sovrascriverà eventuali dati già presenti nell’area di posizionamento dell’elenco selezionato, quindi assicurati di scegliere un’area vuota, se necessario.
• Se inserisci valori non validi (ad esempio, una deviazione standard negativa o pari a zero), la macro si interromperà e visualizzerà un messaggio di avviso.

---

Articoli correlati:

• Generare numeri casuali senza ripetizioni in Excel
• Generare numeri casuali positivi o negativi in Excel
• Bloccare i numeri casuali in Excel
• Generare casualmente «sì» o «no» in Excel