Simulazione con foglio elettronico

     

Utilizzando il foglio elettronico Excel si può avere, talvolta, l’esigenza di popolare intere zone di dati con dei numeri casuali compresi in un certo intervallo di valori.
Dato che è piuttosto faticoso introdurre manualmente una serie di dati, inventandoseli di volta in volta, si può ricorrere alla funzione:

=CASUALE()

Questa funzione restituisce un numero reale compreso tra 0 e minore di 1.
Spesso, l'esigenza è quella di produrre dei numeri interi,di conseguenza il numero generato da CASUALE() deve essere convertito in intero attraverso la funzione INT().

Ad esempio per generare casualmente un numero intero compreso tra 0 ed 7 useremo la funzione:

=INT(CASUALE()*8)

possiamo allora dedurre che per generare un numero compreso tra 1 ed N occorra la funzione

=1+INT(CASUALE()*N)

cioè

=1+INT(CASUALE()*8)

genera un numero casuale compreso tra 1 e 8.

In particolare, per creare un numero reale casuale compreso tra a e b, utilizza:

=CASUALE()*(b-a)+a

Se si vuole utilizzare CASUALE() per creare un numero casuale senza che i numeri vengano modificati ogni volta che si esegue il calcolo della cella, puoi immettere =CASUALE() nella barra della formula e (col cursore lampeggiante ancora all'interno della barra della formula) premere F9 per convertire la formula in un numero casuale.

Quest'ultimo accorgimento può essere praticato solo su una singola cella.
Se dobbiamo fissare i valori di un intervallo di celle prodotti da CASUALE() è sufficiente copiare l'area dati interessata dalla funzione casuale e reincollarla con Incolla speciale selezionando la voce valori.

Abbiamo detto che CASUALE() produce un numero reale da 0 (compreso) a 1 (escluso).
Un numero reale dovrebbe essere rappresentato con infinite cifre dopo la virgola. Nella pratica potrebbero essere necessario limitare il numero di cifre rappresentate dopo la virgola. Per eseguire questa limitazione può essere utile la funzione ARROTONDA() che ha sintassi:

=ARROTONDA (num;num_cifre)

per arrotondare un numero a un numero specificato di cifre.
num è il numero da arrotondare.
num_cifre specifica il numero di cifre a cui arrotondare num.

● Se num_cifre è maggiore di 0, num verrà arrotondato al numero di decimali specificato.
● Se num_cifre è uguale a 0, num verrà arrotondato al numero intero più vicino.
● Se num_cifre è minore di 0, num verrà arrotondato a sinistra della virgola.
Ad esempio

=ARROTONDA((CASUALE()*8);2)

genera un numero casuale compreso tra 0 e minore di 8 con 2 cifre decimali dopo la virgola.

Un primo esempio di utilizzo della funzione CASUALE() possiamo realizzarlo simulando una serie di lanci di dadi.
In cella A1 inseriamo la formula

= ARROTONDA(CASUALE();0)

poi copiamo e incolliamo (oppure trasciniamo) il contenuto di A1 fino alla cella E4. Selezioniamo l'intervallo di celle A1:E4 definendolo nella barra dei nomi con l'dentificatore AREA.

Copiamo i dati contenuti nell'intervallo AREA e con Incolla speciale reincolliamo solo i valori sulla stessa area.

Immettiamo, poi, nel foglio di calcolo, i seguenti dati

predisponendolo al calcolo delle frequenze assolute e delle frequenze relative. In cella G3 inseriamo la formula

= CONTA.SE(AREA;$F$1)

In cella G4 inseriamo la formula
= CONTA.SE(AREA;$F$2)
Poi, ovviamente, in cella G3 avremo la somma delle due precedenti
=G3+G4
In cella I3 avremo
=G3/$G$5
In cella I4 ci sarà
=G4/$G$5
ed in cella I5 la somma delle due precedenti; il risultato dell'operazione è il seguente:

In questo caso abbiamo simulato il lancio di un dado per 20 volte.
Supponiamo di ripetere il lancio per 50 tentativi. Reinseriamo in cella A1 la formula

= ARROTONDA(CASUALE();0)

Ricopiando questa formula su tutto l'intervallo A1:E10.
Ora bisogna ridefinire l'intervallo AREA.
Per fare questo usiamo il comando Inserisci>Nome>Definisci tramite la combinazione di tasti CTRL-F3.

Dopo aver selezionato l'identificatore AREA clicchiamo sul pulsante in basso a destra per selezionare la nuova AREA dati.

Specificando l'intervallo A1:E10. Ottenendo un risultato simile al seguente.

In questo modo si possono realizzare insiemi di lanci di quantitativi arbitrari.

Un secondo esempio può riguardare la simulazione del popolamento di un pianeta. I governi della Terra prevedono che tra 100 anni, e cioè nel 2123, lo sviluppo demografico raggiungerà limiti insostenibili.
Per risolvere questo problema si pensa a un'emigrazione di massa sul planetoide LV-426 che però richiede, per essere reso abitabile tramite un'operazione di bonifica detta terraforming.
Inoltre si vuole studiare un piano di insediamento sul planetoide simulando un ipotetico esodo su tale pianeta.
Lo studio pone in evidenza l'andamento dell'insediamento su LV-426 in un tempo di 10 anni, avendo rilevato i dati delle nascite, delle morti e delle migrazioni da e verso la Terra.

Si effettua la simulazione con un modello costruito con Excel. In tale modello le grandezze di ingresso sono le seguenti:
● il numero delle persone che si trasferiscono su LV-426 (IMMIGRAZIONI);
● il numero delle persone che rientrano sulla terra (EMIGRAZIONI);
● il numero dei nati (NASCITE);
● il numero di morti (MORTI).

Lo stato del sistema è sintetizzato dal numero di abitanti al primo gennaio di ogni anno:

L'uscita è, invece, la percentuale di incremento:

La popolazione iniziale del pianeta è di 3000 individui. Assumiamo che

● Gli immigrati possano essere in numero variabile da 50 a 100 cioè
=50+INT(CASUALE()*50)
● Gli emigrati possano essere in numero variabile da 0 a 100 cioè
=INT(CASUALE()*100)
● I nati possano essere in numero variabile da 10 a 30 cioè
=10+INT(CASUALE()*20)
● I morti possano essere in numero variabile da 0 a 30 cioè
=INT(CASUALE()*30)

Basandoci sui dati della tabella e sulle suddette funzioni realizziamo una simulazione del sistema che determina la popolazione di ogni anno e l'incremento percentuale.
Dopo aver copiato l'area dei dati dove è stata immessa la funzione CASUALE() e dopo aver incollato con Incolla speciale>Valori sull'area stessa. introduciamo in cella F3 la formula:
=F2+B2+D2-C2-E2

Poi, in cella G2 inseriamo la formula:
=(B2+D2-C2-E2)/F2
formattandola poi, successivamente, in formato percentuale.

Trasciniamo verso il basso (oppure copiamo e incolliamo) le due formule precedenti in modo da completare il prospetto.

Otteniamo in questo modo una simulazione dell'incremento di popolazione del planetoide LV-426 che possiamo avere nei 10 anni successivi a quello iniziale.
Il risultato di questi test è riportato nel file sim.xls allegato.