Creare lista codici casuali

Una lista codici casuali può esserci utile se vogliamo schermare le informazioni sensibili sui nostri utenti verso terzi.

Assumiamo per esempio di avere una lista di email collegata con l'età, il luogo di residenza e il sesso della persona. Magari vogliamo condividere una lista di codici che noi sappiamo essere uguali agli uomini con una certa età in una certa zona ma non vogliamo diffondere queste informazioni sensibili. Chi riceverà la lista di codici non potrà scoprire queste informazioni, che saranno solo di nostra pertinenza.

Questa parte può essere utile anche per creare statistiche di vendita nelle quali scopriamo che l'utente X!14E ha fatto 5 acquisti sul nostro e-commerce, dato che magari non vogliamo condividere con chi ci ha creato il sistema di analisi delle informazioni sensibili. Noi sappiamo che si chiama Luca, abita fuori Siena e ha 25 anni - chi analizza i dati non potrebbe mai risalire a queste informazioni, ma può comunque dirci quanti acquisti ha fatto e di che genere.

Excel ha due funzioni che sono collegate con elementi casuali. Purtroppo, CASUALE è solo un numero casuale mentre CASUALE.TRA indica solo un numero in un intervallo definito (ad esempio fra 1 e 12). Per creare codici alfanumerici, dovremo creare un sistema ad hoc.

Database di partenza

Abbiamo quindi una lista con Nome, Sesso, Età, Email e Luogo. Ora dobbiamo creare gli elementi che andranno a comporre i codici associati ai singoli utenti.

Creazione liste per codici

Un trucco per avere una lista di tutta l'alfabeto è utilizzare la funzione CODICE.CARATT a partire dal numero 65, che corrisponde alla A.  Trascinando la formula, avremo tutti i caratteri dell'alfabeto. Questi rappresenteranno il nostro primo elemento.

Il secondo elemento sono alcuni elementi di punteggiatura e valuta.

Il terzo elemento sono i numeri da 1 a 25. Se volessimo avere tutti codici con la stessa lunghezza (numero di caratteri) dovremo limitarci ai numeri da 0 a 9.

Il quarto e ultimo elemento sono nuovamente i codici per l'alfabeto, ma potremmo inserire qualsiasi altra cosa.

Creazione codici casuali con Excel

L'artificio è quello di utilizzare la funzione CONCATENA per collegare fra di loro tutti gli elementi. Nelle versioni più recenti di Excel, è consigliato utilizzare la funzione CONCAT che ne è l'evoluzione. Per individuare gli elementi, dovremo utilizzare la combinazione delle funzioni INDICE e CASUALE.TRA. Andiamo più nel dettaglio.

CONCATENA mette assieme diversi valori. Se creo una funzione con =CONCATENA(20;10) il valore risultante sarà 2010.

INDICE individua un valore all'interno di un elenco. Per scoprire come collegarla con la funzione CONFRONTA, suggerisco questo articolo. Mettiamo di voler identificare il valore in terza posizione di un elenco.

In sostanza INDICE([intervallo];[posizione];0) è ciò che ci interessa.

Nel nostro caso dei codici il ragionamento è simile: INDICE mi individua un valore all'interno di un elenco basandosi sulla funzione CASUALE.TRA che mi segnala un numero casuale in un certo intervallo. Quindi per il primo elemento inseriremo la funzione CASUALE.TRA(1;26) per indicare che ci serve un valore fra 1 e 26 (da A a Z), mentre per il secondo elemento inseriremo CASUALE.TRA(1;5) perchè i valori sono solo 5 (! $ etc) e così via.

Come sempre, ecco il file da scaricare con l'esempio proposto. Dato che le formule sono ancora attive, potresti vedere dei codici diversi da quelli indicati nelle immagini dell'articolo.

Un dettaglio per la formula:

=CONCATENA(
INDICE(LIST!$C$2:$C$27;CASUALE.TRA(1;26);0);
INDICE(LIST!$E$2:$E$6;CASUALE.TRA(1;5);0);
INDICE(LIST!$G$2:$G$26;CASUALE.TRA(1;25);0);
INDICE(LIST!$I$2:$I$27;CASUALE.TRA(1;26)))

Chiaramente l'ultimo INDICE della colonna I potrebbe essere sostituito con il primo INDICE della colonna C, dato che si parla dello stesso elenco.

Per un utente esperto di Excel un'alternativa potrebbe essere rappresentata, per rendere la funzione più scalabile, dalla combinazione fra SCARTO, CASUALE.TRA e CONTA.VALORI.

Creazione codici casuali con la stessa struttura

Vediamo come creare codici casuali con Excel se vogliamo mantenere la stessa struttura di partenza. Ad esempio ABC12X potrebbe diventare MFP49Y e così via, con un numero al posto di un numero e una lettera al posto di una lettera. Ho impostato il sistema fino a 15 caratteri ma puoi espanderlo molto facilmente, se ti serve!

Vediamo adesso dove si trovano le formule che ci servono, segnalate con un colore per spiegare meglio la loro posizione e come mai ci servano:

In questo caso il file è compatibile solo con Excel 365 e successivi, dato che ci sono alcuni passaggi dalla tabella di sinistra al calcolo di destra. In particolare:

=UNICI(TBL_CODICI[START])

La formula UNICI prende tutti i codici univoci della tabella di partenza e li inserisce nella cella D3.

=TESTO.UNISCI("";VERO;W3:AK3)

La formula TESTO.UNISCI prende invece tutti i valori dalla colonna W alla colonna AK, concatenandoli nella stessa cella dalla E3 in poi ed evitando le celle vuote (il VERO della formula).

=MATR.TRASPOSTA(SEQUENZA(MAX(LUNGHEZZA(TBL_CODICI[START]))))

Questa formula prende tutti i valori della tabella di partenza, considerando quella con più caratteri (MAX + LUNGHEZZA) e restituendo i numeri dall'1 alla lunghezza massima possibile. Per esempio, Paolo sarebbe 5 caratteri / Nikola Jokic sarebbe 12 e così via (includendo lo spazio). In questo modo, il sistema può essere dinamico a prescindere dal numero di caratteri del codice di partenza, grazie alla funzione SEQUENZA.. La formula diventa un array che viene poi trasposta con la funzione MATR.TRASPOSTA nella cella G2.

=SE(D3#="";"";STRINGA.ESTRAI(D3#;$G$2#;1))

Se D3# fosse vuoto, cioè se non ci fosse niente nella sequenza di codici, mettiamo vuoto. Altrimenti prendiamo il carattere di riferimento con la funzione STRINGA.ESTRAI, che sarà prima il primo carattere, poi il secondo e così via. Questa formula è nella cella G3.

=G2#

La cella W2 non fa altro che replicare l'array precedente con i numeri in sequenza.

=SE.ERRORE(SE(O(W$2="";G3="");"";SE(CONTA.SE(TBL_LETTERE[[VALORI]:[VALORI]];G3)>0;INDICE(TBL_LETTERE[[VALORI]:[VALORI]];CASUALE.TRA(1;CONTA.VALORI(TBL_LETTERE[[VALORI]:[VALORI]])));INDICE(TBL_NUMERI[[VALORI]:[VALORI]];CASUALE.TRA(1;CONTA.VALORI(TBL_NUMERI[[VALORI]:[VALORI]])))));"")

La cella W3 è il fulcro del nostro modello e segnala che se O il valore in W2 (quindi l'array con i numeri) oppure il valore in G3 (il carattere "esploso" di partenza") è vuoto, allora il risultato deve essere vuoto. Altrimenti, se il valore è una lettera, pesca in maniera casuale dalla tabella lettere - se è un numero, pesca in maniera casuale dalla tabella numeri. In questo caso non ho utilizzato i codici ma la logica è esattamente la stessa.

Purtroppo in questo caso non si possono utilizzare gli array perchè la funzione CASUALE.TRA restituisce sempre lo stesso valore, ma basta ricordarsi di espandere le formule verso il basso!

[Se vuoi scoprire come sono stati indicati i colori nella schermata precedente, dai un occhio a PerfectXL Highlighter, un plugin utilissimo per lavorare su Excel che permette di vedere velocemente quali celle contengano formule e molto altro. Clicca qui per scoprire di più]