Genera numeri casuali dato un certo valore medio 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 test degli algoritmi o la modellazione dei processi in campi come la finanza, l'ingegneria e l'istruzione. Tuttavia, Excel non fornisce una funzione predefinita diretta per generare istantaneamente un elenco di numeri casuali adattati sia a una data media che a una deviazione standard. Se hai bisogno frequente di creare dati di prova 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 esperti per la prevenzione degli errori e la risoluzione dei problemi. Inoltre, forniamo una soluzione macro VBA per gli utenti che devono automatizzare questo processo o generare grandi set di dati in modo efficiente.

Genera numeri casuali dato un certo valore medio e deviazione standard

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

---

Genera numeri casuali dato un certo valore medio e deviazione standard

In Excel, puoi creare un insieme di numeri casuali che si adattino alla tua media e deviazione standard desiderate 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. Innanzitutto, 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 alla 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 specificata.

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

• RAND() produce 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 lo stesso ma riflette i nomi delle funzioni aggiornate.

3. Per verificare che i numeri generati statisticamente assomiglino alla tua media e deviazione standard previste, usa 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 a quanti valori casuali hai generato nel Passo 2.
• Un campione casuale più grande comporta una media e una deviazione standard effettive più vicine ai tuoi valori specificati, a causa della legge dei grandi numeri.

4. Per ulteriormente regolare la tua serie in modo che corrisponda esattamente alla tua media e deviazione standard previste, 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 ricalcolando la loro media e deviazione standard per scopi di garanzia della qualità e documentazione.

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

=AVERAGE(D3:D16)

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

=STDEV.P(D3:D16)

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

Risoluzione dei problemi:

• Se vedi un errore #VALORE!, controlla nuovamente 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 corrispondano 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 singola 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, test di stress o dimostrazioni didattiche.
• Situazioni in cui vuoi standardizzare il formato di output con la minima 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 "con macro abilitate".

1. Nella barra multifunzione di Excel, fai clic su Strumenti di sviluppo (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 codice seguente nella finestra del modulo vuoto:

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. 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 se la tua versione supporta questa; per versioni precedenti di Excel, la funzione potrebbe dover essere NORMINV.
• Il seme casuale è impostato con Randomize per risultati variabili in ogni esecuzione.
• Se vuoi 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
• Interrompere il cambiamento dei numeri casuali in Excel
• Genera risposte casuali 'sì' o 'no' in Excel