www.valterborsato.it

Evitare estrazioni casuali di valori doppi applicando le nuove Funzioni di Excel 365 o le Funzioni Excel 2010-2019

Per estrarre dei numeri casuali in Excel si utilizzano la Funzione CASUALE, oppure la Funzione CASUALE.TRA (introdotta con Excel 2007)
Applicando queste Funzioni cella per cella, non è possibile evitare un'estrazione doppia di un valore numerico o testuale.
Per gli abbonati alla versione di Excel Microsoft 365, nel 2020 sono state rese disponibili 3 interessanti Funzioni che, adeguatamente annidate, permettono di impostare la restituzione di un valore casuale senza il rischio di creare dei valori doppi.

Nel presente tutorial vengono presentate le Funzioni di Excel 365 per estrarre casualmente un campione di valori, senza la presenza di ripetizioni (valori duplicati). Viene inoltre spiegato come effettuare la medesima operazione anche nelle versioni di Excel 2010-2019.

Contenuti tutorial: Estrazione casuale di valori univoci in Excel

[1] - Introduzione: Estrazione casuale di valori univoci (no duplicati) 
[2] - Creazione di una estrazione casuale senza valori duplicati in Excel Microsoft 365 - Esempio di applicazione
[3] - Creazione di una estrazione numerica casuale senza valori duplicati (Excel 365) - Esempio di applicazione
[4] - Creazione di una estrazione casuale senza valori duplicati (Excel 2010-2019) - Esempio di applicazione
[5] - Approfondimenti sull'estrazione casuale di numeri e matrici (link a tutorial)
Riepilogo e guida alla sintassi delle Funzioni applicate nel tutorial, per l'estrazione casuale di valori
:: La Funzione SEQUENZA (Sintassi)
:: La Funzione MATR.CASUALE (Sintassi)
:: La Funzione DATI.ORDINA.PER (Sintassi)
:: La Funzione INDICE
:: La Funzione RIGHE
:: La Funzione RANGO.US
:: La Funzione CASUALE
:: La Funzione UNICI

Estrazione casuale di valori univoci (no duplicati) 

Excel mette a disposizione due Funzioni per creare un'estrazione casuale: CASUALE e CASUALE.TRA
Con queste due Funzioni è possibile estrarre dei valori in una singola cella, ma non è possibile la creazione di una matrice. Se si vuole effettuare un'estrazione univoca bisogna in primo luogo creare una nuova colonna abbinando, ai valori che si vogliono estrarre, un numero decimale casuale generato dalla Funzione CASUALE()
Successivamente, su questo nuovo elenco, sarà possibile applicare le Funzioni INDICE; RANGO.UG e CONTA.SE 
Un esempio sull'impostazione di questa formula è presentato alla fine di questo tutorial.

Quale primo esempio, consideriamo come è possibile creare un'estrazione di dati univoci con Excel Microsoft 365, senza la necessità di creare una colonna aggiuntiva intermedia e applicando le Funzioni: DATI.ORDINA.PER; MATR.CASUALE e SEQUENZA

Creazione di una estrazione casuale senza valori duplicati in Excel Microsoft 365 - Esempio di applicazione

Quale esempio di applicazione, si vuole creare un'estrazione casuale di 6 città provenienti da un elenco (vedi immagine sottostante) di 13 capoluoghi. Ovviamente in questa estrazione non si vuole incorrere nel rischio di estrarre dei valori duplicati.

Creazione di una estrazione casuale senza valori (testuali o numerici) duplicati in Excel Microsoft 365 - tabella esempio di applicazione

Nella cella C3 del Foglio di lavoro (vedi immagine successiva) è stata inserita la Formula:

=INDICE(DATI.ORDINA.PER(A2:A14;MATR.CASUALE(RIGHE(A2:A14)));SEQUENZA(6))

La Funzione INDICE serve per rappresentare i valori (6) definiti dalla Funzione SEQUENZA(6).

La matrice su cui lavora INDICE, viene costituita dalla Funzione DATI.ORDINA.PER (con i valori presenti nell'intervallo A2:A14).
Questi valori sono disposti casualmente dalla funzione MATR.CASUALE e il numero dei valori di questa matrice (13) sono individuati dalla Funzione RIGHE (A2:A14).
Nello specifico, il risultato delle due Funzioni MATR.CASUALE(RIGHE(A2:A14) restituisce una matrice di 13 numeri casuali decimali.
Nidificando questo risultato nella funzione DATI.ORDINA.PER(A2:A14;MATR.CASUALE(RIGHE(A2:A14))) viene creata una matrice "casualmente" ordinata con i nomi delle città.

Per vedere il risultato di queste Funzioni sulla barra della formula selezionare solo questa parte della sintassi e premere il tasto F9.

Creazione di una estrazione casuale senza valori: controllo formula

Il risultato sarà la sostituzione delle Funzioni selezionate con il loro risultato, ovvero:
=INDICE({"Rovigo"."Trieste"."Treviso"."Belluno"."Gorizia"."Padova"."Udine"."Bolzano"."Trento"."Pordenone"};SEQUENZA(6))

::Osservazione. Nell'immagine di cui sopra, la parentesi graffa iniziale e finale rappresenta la matrice. Per annullare questa visualizzazione e tornare alla struttura completa delle formula utilizzare il comando CTRL+Z (annulla).

Ovviamente l'ordine univoco dei nomi delle città viene riordinato ogni volta che la Funzione viene ricalcolata (Tasto F9).

Rispetto questo INDICE la Funzione SEQUENZA permette di prendere i primi sei valori che di volta in volta saranno diversi a seconda di come la Funzione MATR.CASUALE disporrà l'elenco delle città.

Il risultato è l'estrazione casuale (senza possibili valori duplicati) di 6 città diverse da un elenco complessivo di 13

Creazione di una estrazione casuale senza valori (testuali o numerici) duplicati in Excel Microsoft 365 - esempio di applicazione

:: Tutte le volte che il file viene ricalcolato (apertura del documento o modifica contenuti) l'estrazione dei dati viene ripetuta.
Per ricalcolare "manualmente"  il Foglio di lavoro è possibile utilizzare il tasto F9.
Ogni volta che si preme il tasto funzione F9 viene pertanto restituita una nuova estrazione dei valori.

Creazione di una estrazione numerica casuale senza valori duplicati

Operazione per certi versi simile alla precedente.
In questo caso si ipotizza di voler estrarre casualmente senza valori duplicati una serie di 5 numeri da 1 a 50.
Anche in questo caso verranno applicate le Funzioni INDICE; MATR.CASUALE; SEQUENZA e in aggiunta a queste la Funzione UNICO.

Nell'immagine sottostante, nella cella A2 è stata inserita la seguente formula:

=INDICE(UNICI(MATR.CASUALE(20;1;1;50;VERO));SEQUENZA(10))

Una volta inserita la Funzione, la matrice dinamica di Excel produrrà automaticamente una serie di 10 numeri senza nessun valore duplicato.

Nello specifico la funzione INDICE ha a stessa impostazione e funzione del precedente esempio, e anche in questo caso lavora con una definizione di numero di righe (10) indicati dall'ultima Funzione SEQUENZA(10)
La Funzione UNICI serve per escludere gli eventuali valori doppi.
L'elemento fondamentale della formula e sicuramente la Funzione MATR.CASUALE che in questo caso definisce tutti e 5 gli argomenti previsti.
Il primo argomento 20 indica quante righe di numeri casuali devono essere generati.
Attenzione che di queste 20 righe la Funzione UNICI escluderà quelle doppie e la Funzione SEQUENZA preleverà solo le prime 10!
I successi numeri: 1 rappresenta il numero di colonne. Il numero 1 successivo indica il valore minimo, e 50 individua il valore massimo. L'ultimo argomento VERO definisce che i numeri devono essere interi e non decimali.

Il risultato di questa Formula, viene ricalcolato ogni volta che i file viene aperto; che intervengono modifiche dati, o che si effettua un ricalcolo premendo il tasto F9.

Creazione di una estrazione casuale senza valori duplicati (Excel 2010-2019) - Esempio di applicazione

Per facilitare una comparazione fra le due soluzioni proposte, viene utilizzato lo stesso caso del primo esempio. Ovvero si vuole creare un elenco casuale di 6 città provenienti da una lista di 13 capoluoghi.
Ovviamente anche in questa estrazione non si vuole incorrere nell'errore di proporre dei valori duplicati.

Se si imputa in sei celle diverse la Funzione INDICE assieme alla Funzione CASUALE.TRA
=INDICE(A2:A14;CASUALE.TRA(1;13))
l risultato è ovviamente un'estrazione casuale, ma con estrema facilità possono comparire più volte nell'elenco le stesse città. Il motivo è abbastanza semplice si tratta di sei diverse estrazioni totalmente indipendenti l'una dalle altre.

Creazione di una estrazione casuale (con rischio di valori duplicati)

Per poter creare una estrazione dati casuale senza valori duplicati nelle versioni di Excel 2010-2019, è necessario in primo luogo organizzare una colonna a destra dei valori da estrarre, che restituisca un numero casuale decimale compreso fra lo zero e il numero uno.
Per ottenere questo, nella cella B2, successivamente trascinata nelle sottostanti celle, è stata imputata la Funzione =CASUALE()
Il numero decimale casuale generato nell'intervallo celle B2:B14, sarà fondamentale per il calcolo della Formula imputata nella cella D2 (anche in questo caso trascinata nelle sottostante celle fino arrivare ai 6 valori necessari).

La Funzione imputata nella cella D2 è la seguente:

=INDICE($A$2:$A$14;RANGO.UG(B2;$B$2:$B$14))

La Funzione INDICE assume la stessa Funzionalità dei casi precedenti.
In questo caso però viene nidificata la Funzione RANGO.UG che permette la restituzione della posizione di un valore all'interno di un elenco di numeri.

La Funzione RANGO classifica la posizione del numero presente nelle cella B2 rispetto la lista $B$2:$B$14 e il risultato viene trasferito nella cella D2. Proseguendo, sarà uguale anche per i successivi dati. Ovvero nella cella D3, viene portato il rango del valore contenuto nella cella B3 confrontato sempre nella lista  $B$2:$B$14 e avanti così... per le successive righe.
Il risultato sarà la restituzione nella celle da D2 fino a D7 di una lista di numeri interi (esempio 7,6,3,11,10,1) ovvero le posizioni dei valori nella colonna B.
Questa lista univoca di numeri viene utilizzata dalla Funzione INDICE quali riferimento di riga per recuperare il corrispondente valore presente nell'intervallo celle $A$2:$A$14 (le città).

:: NOTA. L'estrazione da parte della Funzione CASUALE() di un numero decimale duplicato (in una lista di pochi valori) è quasi impossibile.
Se però si dovessero estrarre grandi quantità di informazioni alla Formula di cui sopra si potrebbe aggiungere la Funzione di verifica CONTA.SE
Ovvero: =INDICE($A$2:$A$14;RANGO.UG(B2;$B$2:$B$14)+CONTA.SE($B$2:B2;B2)-1)

Creazione di una estrazione casuale senza valori (testuali o numerici) duplicati (Excel 2010-2019) - Esempio di applicazione

Riepilogo della sintassi delle Funzioni applicate nel tutorial

Nel presente tutorial vengono applicate alcune Funzioni che non ricorrono frequentemente nelle applicazione di Excel.
Per facilitare la consultazione della sintassi e della conseguente applicazione, vengono di seguito elencate.

:: La Funzione SEQUENZA (Sintassi)
:: La Funzione MATR.CASUALE (Sintassi)
:: La Funzione DATI.ORDINA.PER (Sintassi)
:: La Funzione INDICE
:: La Funzione RIGHE
:: La Funzione RANGO.US
:: La Funzione CASUALE
:: La Funzione UNICI

La Funzione SEQUENZA (Sintassi)

La Funzione SEQUENZA (nome inglese della Funzione SEQUENCE) permette di generare un elenco di numeri sequenziali all'interno di una matrice ad esempio 1, 2, 3, 4. oppure  2, 4, 6, 8 etc.
Sintassi della Funzione:
=SEQUENZA(righe,[colonne],[start],[step])
Nella quale:
righe definisce il numero di righe da restituire
colonne (argomento facoltativo) definisce il numero di colonne da restituire
start (argomento facoltativo) rappresenta il primo numero della sequenza
step (argomento facoltativo) definisce l'incremento di ciascun valore successivo nella matrice.

La Funzione MATR.CASUALE (Sintassi)

La Funzione MATR.CASUALE (nome inglese della Funzione RANDARRAY) permette di generare una matrice di numeri casuali. La quantità di righe e colonne viene specificata negli argomenti della Funzione.
Sintassi della Funzione:
=MATR.CASUALE([righe],[colonne],[min],[max],[numero_intero])
Nella quale:
righe (argomento facoltativo) rappresenta il numero totale delle righe da restituire
colonne (argomento facoltativo) rappresenta il numero totale delle colonne da restituire
min (argomento facoltativo) valore minimo che può essere restituito
max (argomento facoltativo) valore massimo che può essere restituito
numero_intero (argomento facoltativo) valore logico, VERO restituisce numero intero, FALSO numero decimale.

La Funzione DATI.ORDINA.PER (Sintassi)

La Funzione DATI.ORDINA.PER (nome inglese della Funzione SORTBY) permette di ordinare il contenuto di un intervallo o di una matrice sulla base dei valori di un'altro intervallo o una matrice. L'ordinamento può essere effettuato sulla base di una oppure più colonne.
Sintassi della Funzione:
=DATI.ORDINA.PER(matrice;per_matrice1;[tipo_orientamento1];...)
nella quale:
matrice rappresenta la matrice oppure l’intervallo dati da ordinare
per_matrice1 rappresenta la matrice o l’intervallo in base a cui effettuare l'ordinamento
tipo_ordinamento1 (argomento facoltativo) indica l'ordine da utilizzare: 1 per crescente, -1 per decrescente.

La Funzione INDICE (Sintassi)

La Funzione INDICE (nome inglese della Funzione INDEX) restituisce il valore di una cella nell'intersezione di una determinata riga e colonna incluse in un certo intervallo.
Sintassi della Funzione:
=INDICE(Matrice;Riga;[Col])
nella quale:
Matrice
rappresenta l'area entro la quale ricercare il valore;
Riga
indica la Riga della Matrice da cui restituire il valore
Col
(argomento facoltativo) indica la colonna della Matrice da cui restituire il valore.
Se entrambi gli argomenti Riga e Col vengono individuati, la Funzione INDICE permette di restituire il valore contenuto nella cella dell'intersezione di Riga e Colonna.

La Funzione RIGHE (Sintassi)

La Funzione RIGHE (nome inglese della Funzione ROWS) restituisce il numero di righe presenti in un riferimento o in una matrice
Sintassi della Funzione:
=RIGHE(matrice)
nella quale:
matrice rappresenta una matrice, formula di matrice oppure un riferimento a un intervallo di celle del quale si vuole conoscere il numero di righe.

La Funzione RANGO.US (Sintassi)

La Funzione RANGO.US (nome inglese della Funzione RANK.EQ) restituisce il rango (posizione) di un numero all'interno di un elenco di numeri.
Sintassi della Funzione:
=RANGO.UG(num; rif; [ordine])
nella quale:
num rappresenta il numero del quale si vuole determinare la posizione
rif riferimento ad una matrice o un elenco di numeri. I valori testuali vengono ignorati
ordine (argomento facoltativo) 0= decrescente; 1 crescente. Se il valore viene omesso viene considerato uguale a zero.

La Funzione CASUALE (Sintassi)

La Funzione CASUALE (nome inglese della Funzione RAND) genera un numero casuale decimale compreso fra zero e uno.
Sintassi della funzione:
=CASUALE()
nella quale:
La Funzione non prevede impostazione di argomenti.
Le due parentesi vanno comunque inserite. La Funzione viene ricalcolata ogni qual volta il Foglio di lavoro viene aperto o modificato
Per ricalcolare la Funzione CASUALE, è possibile utilizzare il fasto Funzione F9.

La Funzione UNICI (Sintassi)

La funzione UNICI (nome inglese della Funzione UNIQUE) restituisce un elenco di valori univoci rispetto un elenco o un intervallo di celle.
Sintassi della funzione:
=UNICI(matrice; [by_col]; [exactly_once]) )
nella quale:
matrice rappresenta l'intervallo di celle o la matrice da cui restituire i soli valori univoci
by_col (argomento facoltativo) consente di confrontare i valori per riga (FALSO) o per colonna (VERO)
exactly_once (argomento facoltativo) restituisce gli argomenti che compaiono solo una volta (VERO) restituisce ogni elemento distintivo (FALSO). Se l'argomento viene omesso è impostato come FALSO.
Osservazioni. in alcune nuove Funzioni di Excel Microsoft 365 la Funzione stessa o alcuni argomenti non sono stati (per il momento) tradotti in italiano.

Approfondimenti sull'estrazione casuale di numeri e matrici

:: Per un approfondimento su altre Funzioni di estrazione casuale: EXCEL: le Funzione CASUALE e CASUALE.TRA per sorteggiare e generare estrazioni numeriche casuali.
:: Per un approfondimento sulla Funzione MATR.CASUALE: Creare matrici casuali di numeri con la Funzione MATR.CASUALE

Indice argomenti

[Valter Borsato: 31/1/2021]