Evitare estrazioni casuali di valori doppi applicando le nuove Funzioni di Excel 365 o le Funzioni Excel 2010-2019
In Excel, per estrarre numeri casuali, si utilizzano tradizionalmente le funzioni CASUALE e CASUALE.TRA (quest'ultima introdotta con Excel 2007). Tuttavia, queste funzioni, applicate singolarmente a diverse celle, non consentono di evitare la generazione di valori duplicati, poiché ogni cella agisce in modo indipendente e non tiene conto dei numeri già estratti. Per gli abbonati alla versione di Excel Microsoft 365, nel 2020 sono state introdotte tre nuove funzioni che, se combinate in modo adeguato, permettono di generare numeri casuali senza ripetizioni.
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.
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.
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
:: 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.
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)
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 Generale Argomenti:
EXCEL
[Valter Borsato: 20/07/2022 | Ultimo aggiornamento 22/12/2023]