Formula generica
=MATCH(RAND(),cumulative_probability)
Sommario
Per generare un numero casuale, ponderato con una data probabilità, è possibile utilizzare una tabella helper insieme a una formula basata sulle funzioni RAND e MATCH.
Nell'esempio mostrato, la formula in F5 è:
=MATCH(RAND(),D$5:D$10)
Spiegazione
Questa formula si basa sulla tabella helper visibile nell'intervallo B4: D10. La colonna B contiene i sei numeri che vogliamo come risultato finale. La colonna C contiene il peso di probabilità assegnato a ciascun numero, inserito come percentuale. La colonna D contiene la probabilità cumulativa, creata con questa formula in D5, copiata in basso:
=SUM(D4,C4)
Nota, stiamo intenzionalmente spostando la probabilità cumulativa in basso di una riga, in modo che il valore in D5 sia zero. Questo per assicurarsi che MATCH sia in grado di trovare una posizione per tutti i valori fino a zero come spiegato di seguito.
Per generare un valore casuale, utilizzando la probabilità ponderata nella tabella helper, F5 contiene questa formula, copiata in basso:
=MATCH(RAND(),D$5:D$10)
All'interno di MATCH, il valore di ricerca è fornito dalla funzione RAND. RAND genera un valore casuale compreso tra zero e 1. L'array di ricerca è l'intervallo D5: D10, bloccato in modo che non cambierà mentre la formula viene copiata nella colonna.
Il terzo argomento per MATCH, il tipo di corrispondenza, viene omesso. Quando il tipo di corrispondenza viene omesso, CONFRONTA restituirà la posizione del valore più grande minore o uguale al valore di ricerca *. In termini pratici, ciò significa che la funzione CONFRONTA viaggia lungo i valori in D5: D10 finché non viene incontrato un valore più grande, quindi "torna indietro" alla posizione precedente. Quando CONFRONTA incontra un valore maggiore dell'ultimo valore più grande in D5: D10 (.7 nell'esempio), restituisce l'ultima posizione (6 nell'esempio). Come accennato in precedenza, il primo valore in D5: D10 è deliberatamente zero per garantire che i valori inferiori a .1 vengano "catturati" dalla tabella di ricerca e restituiscano una posizione di 1.
* I valori nell'intervallo di ricerca devono essere ordinati in ordine crescente.
Valore di testo ponderato casuale
Per restituire un valore di testo ponderato casuale (ovvero un valore non numerico), è possibile immettere valori di testo nell'intervallo B5: B10, quindi aggiungere INDICE per restituire un valore in tale intervallo, in base alla posizione restituita da CONFRONTA:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Appunti
- Mi sono imbattuto in questo approccio in un post sul forum su mrexcel.com
- RAND è una funzione volatile e verrà ricalcolata ad ogni modifica del foglio di lavoro
- Dopo aver ottenuto valori casuali, utilizzare incolla valori speciali> per sostituire la formula, se necessario
