Come generare un valore casuale in base a una probabilità specificata in Excel?
Quando lavori con Excel, potrebbe capitarti di dover generare valori casuali che rispecchino probabilità specifiche. Ad esempio, immagina di avere una tabella che elenca diversi possibili risultati insieme alle relative probabilità, come nell’immagine seguente.
Questo scenario è comune nelle simulazioni aziendali, nella modellazione di progetti e in contesti didattici, quando si vuole che la selezione casuale rispecchi fedelmente le probabilità o le frequenze indicate dai dati.
Esigenze e casi d’uso tipici includono:
- Simulare risposte a sondaggi o scelte dei clienti, in cui alcune opzioni hanno una probabilità maggiore di essere selezionate.
- Genera set di dati di prova o estrazioni casuali per illustrare efficacemente i concetti di probabilità.
- Automatizza i processi di selezione in cui ogni opzione ha una probabilità nota.
- Progettazione di giochi e analisi del rischio, dove i risultati richiedono distribuzioni di probabilità specifiche.
Di seguito sono illustrati diversi metodi per generare valori casuali in base a probabilità assegnate in Excel, tra cui approcci basati su formule standard, un’automazione avanzata con VBA e l’utilizzo del componente aggiuntivo integrato Analisi dei dati.
➤ VBA: Genera valori casuali con probabilità assegnate
Genera un valore casuale con probabilità
Excel offre una soluzione semplice e accessibile, basata su formule, per generare valori casuali in base a probabilità specifiche. Ideale per compiti rapidi, funziona interamente nei fogli di lavoro senza richiedere configurazioni particolari.
Prima di iniziare, assicurati che i valori siano elencati in una colonna (A2:A8) e che le relative probabilità — espresse come decimali compresi tra 0 e 1 — si trovino nella colonna adiacente (B2:B8). Le probabilità devono sommare esattamente a 1 per garantire precisione. Questa soluzione è perfetta per tabelle con un numero limitato e facilmente gestibile di valori.
1.In una colonna adiacente (a partire da C2), inserire la seguente formula per calcolare le probabilità cumulative:
=SUM($B$2:B2)Trascina quindi questa formula verso il basso per estenderla a tutti i valori. In questo modo si creano intervalli cumulativi per ciascun valore, ideali per associare un numero casuale a un risultato specifico.
2.In una qualsiasi cella vuota (ad esempio D2), inserire la formula seguente per ottenere un valore casuale in base alla propria distribuzione di probabilità:
=INDEX(A$2:A$8,COUNTIF(C$2:C$8,"<="&RAND())+1)Premere Invio per visualizzare un valore casuale. Ogni volta che si preme F9 (per ricalcolare) o quando i dati del foglio cambiano, apparirà un nuovo risultato.
Suggerimenti e precauzioni:
- Le probabilità nella colonna B devono sommare esattamente a 1 (o al 100 % se si utilizzano percentuali; in tal caso, convertirle in decimali per le formule) per garantire una distribuzione equa.
- Questa tecnica è perfetta per elenchi brevi. Con decine o centinaia di valori, tuttavia, le prestazioni potrebbero rallentare e la manutenzione diventare difficoltosa.
- Se devi ripetere la selezione casuale per più estrazioni (ad esempio, per generare un lotto di risultati simulati), ti basta copiare la formula finale in un intervallo sottostante o adiacente.
- Presta attenzione alle righe vuote e agli intervalli non corrispondenti, poiché potrebbero causare errori o risultati imprevisti.
Promemoria sugli errori: Se viene visualizzato un errore #RIF! o #VALORE!, assicurati che la colonna delle probabilità cumulative abbia la stessa lunghezza dei valori e che tutte le probabilità siano numeri validi.
Sblocca la magia di Excel con KUTOOLS AI
- Esecuzione intelligente: Esegui operazioni sulle celle, analizza i dati e crea grafici, il tutto con comandi semplici e intuitivi.
- Formule personalizzate: crea formule su misura per ottimizzare i tuoi flussi di lavoro.
- Codifica VBA: Scrivi e implementa codice VBA in modo semplice e immediato.
- Interpretazione delle formule: Comprendi con facilità anche le formule più complesse.
- Traduzione del testo: Superate le barriere linguistiche direttamente nei vostri fogli di calcolo.
VBA: Genera valori casuali con probabilità assegnate
Per gli utenti che richiedono maggiore automazione o desiderano generare un volume elevato di valori casuali (ad esempio migliaia di campioni), Excel VBA offre una soluzione pratica, più rapida e flessibile rispetto alle formule nei fogli di lavoro. Questo metodo è particolarmente efficace con set di dati ampi o per produrre risultati casuali in blocco destinati a simulazioni.
1.Andare su Strumenti sviluppatore>Visual Basic, quindi nella finestra VBA fare clic su Inserisci>Moduloe incollare il codice seguente nel modulo:
Sub GenerateRandomWithProbability()
Dim rngValues As Range
Dim rngProbs As Range
Dim n As Long
Dim i As Long
Dim cumProbs() As Double
Dim valList() As Variant
Dim randNum As Double
Dim resultRange As Range
Dim idx As Long
' On Error, ignore
On Error Resume Next
xTitleId = "KutoolsforExcel"
' Select values
Set rngValues = Application.InputBox("Select values range", xTitleId, Type:=8)
' Select probabilities
Set rngProbs = Application.InputBox("Select probabilities range", xTitleId, Type:=8)
' Number of random values to generate
n = Application.InputBox("Number of random values to generate", xTitleId, "10", Type:=1)
' Where to output
Set resultRange = Application.InputBox("Select output start cell", xTitleId, Type:=8)
If rngValues.Rows.Count <> rngProbs.Rows.Count Then
MsgBox "Values and probabilities range must be the same size.", vbExclamation
Exit Sub
End If
ReDim cumProbs(1 To rngValues.Count)
ReDim valList(1 To rngValues.Count)
' Calculate cumulative probabilities
cumProbs(1) = rngProbs.Cells(1, 1).Value
valList(1) = rngValues.Cells(1, 1).Value
For i = 2 To rngValues.Count
cumProbs(i) = cumProbs(i - 1) + rngProbs.Cells(i, 1).Value
valList(i) = rngValues.Cells(i, 1).Value
Next i
' Generate random results
For i = 1 To n
randNum = Rnd
For idx = 1 To UBound(cumProbs)
If randNum <= cumProbs(idx) Then
resultRange.Cells(i, 1).Value = valList(idx)
Exit For
End If
Next idx
Next i
End Sub2. Nella finestra VBA, fai clic sul 
pulsante di esecuzione per avviare il codice. Ti verrà chiesto, in sequenza, di selezionare l’intervallo dei valori, l’intervallo delle probabilità, il numero di valori casuali da generare e la cella iniziale di output. La macro riempirà rapidamente le celle di destinazione con valori casuali basati sulle probabilità assegnate.
- È possibile ripetere questo processo per set di dati più ampi, e la colonna di output può essere personalizzata in qualsiasi foglio.
- Se l’intervallo delle probabilità non somma a un valore molto prossimo a 1, il codice potrebbe alterare la distribuzione; convalida sempre le tue ipotesi.
- Questa soluzione è ideale per il campionamento automatizzato, la simulazione e la generazione riproducibile di lotti.
Suggerimenti: Tieni valori e probabilità contigui e allineati in modo chiaro. Salva sempre il tuo lavoro prima di eseguire le macro, poiché le azioni VBA non possono essere annullate con Ripristina.
Articoli correlati:
- Come generare numeri casuali univoci in Excel?
- Come impedire che i numeri casuali cambino in Excel?
- Come generare casualmente “Sì” o “No” in Excel?
Migliori Strumenti per la Produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un’efficienza mai vista prima.Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e Risparmia tempo.Clicca qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab Porta l'interfaccia a schede in Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede all’interno della stessa finestra, invece che in finestre separate.
- Aumenta la tua produttività del 50 % e risparmia centinaia di clic del mouse ogni giorno!
Tutti i componenti aggiuntivi di Kutools in un unico programma di installazione.
Kutools for Office è la suite che include componenti aggiuntivi per Excel, Word, Outlook e PowerPoint, oltre a Office Tab Pro: la soluzione ideale per i team che lavorano su diverse app di Office.
- Suite completa— componenti aggiuntivi per Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Un unico programma di installazione, una sola licenza— configurazione in pochi minuti (pronto per MSI)
- Funziona meglio insieme— produttività ottimizzata tra le app di Office
- Prova gratuita di 30 giorni con tutte le funzionalità— nessuna registrazione, nessuna carta di credito
- Miglior rapporto qualità-prezzo— risparmia rispetto all’acquisto dei singoli componenti aggiuntivi