Vai al contenuto

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

Microsoft_Excel_Codici_Casuali_Master
Questo è il nostro 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

Microsoft_Excel_Codici_Casuali_Alfabeto
In questo caso specifico, sono 4 elementi diversi.

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

Microsoft_Excel_Codici_Casuali_Finale
Versione finale con i codici per singolo utente.

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.

Microsoft_Excel_Codici_Casuali_Indice_Casuale_Tra

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.

Microsoft_Excel_Codici_Casuali_Finale

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

CREARE_CODICI_CASUALI_EXCEL_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:

CREARE_CODICI_CASUALI_EXCEL_STESSA_STRUTTURA_FORMULE

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ù]

10 commenti su “Creare lista codici casuali”

  1. vittorio broll

    salve volevo chiedere come faccio a risolvere il mio problema di inserire il simbolo euro nella mia formula grazie
    =TESTO.UNISCI(” “;128;SE(SPESA!C4:C28=C10;SPESA!F4:F28;””))

    1. Ciao Vittorio,

      spero di aver capito bene – puoi o cambiare il formato della cella (consigliabile) oppure incapsulare la formula.

      Caso 1: cambi il formato in qualcosa del tipo € #,00;-€ #,00;; il che significa che hai sempre un € per il numero positivo e negativo (con due decimali), altrimenti vuoto

      Caso 2: con la funzione TESTO puoi trasformare le formule modificando la visualizzazione. Ad esempio =TESTO(C2;”€ #,00″) trasforma il contenuto in C2 in un testo che viene visualizzato con l’euro prima, ma in questo caso diventa un testo e non più un numero.

      Spero di esserti stato utile!

      A presto,

      Marco

  2. è possibile creare codici casuali avendo pero fisse le prime 4 cifre numeriche e le due finali in lettere? mi spiego meglio, dovrei sostituire i codici prodotto in una tabella di excel parecchio lunga, vorrei che i codici prodotto del fornitore fossero sostituiti dai “miei codici” prodotto che dovrebbero essere “1234aa” meglio ancora “ab” (dove AB iniziale è il codice fornitore) e poi “1234aa” come codice prodotto ..

    1. Ciao Monica,

      grazie per la domanda!

      Certo, se la prima parte è fissa e ti servono 2 caratteri testuali potresti fare:

      =CONCATENA(1234;INDICE(LIST!$I$2:$I$27;CASUALE.TRA(1;26));INDICE(LIST!$I$2:$I$27;CASUALE.TRA(1;26)))

      Se invece devi prendere dei dati da un’anagrafica, puoi combinare sempre il concatena iniziale con 1234 e poi un CERCA.VERT o INDICE CONFRONTA.

      Spero di esserti stato utile!

      A presto,

      Marco

  3. io vorrei creare una stringa di 4 caratteri casuali, ma scelti da una lista di alcuni numeri e alcune lettere

    1. Ciao Emanuele,

      grazie per la domanda – se c’è una lista in una sola colonna, potrebbe essere così la formula se immaginiamo che i vari caratteri siano nelle celle da K2 a K18:

      =CONCAT(INDICE(K2:K18;CASUALE.TRA(1;CONTA.VALORI(K2:K18)));INDICE(K2:K18;CASUALE.TRA(1;CONTA.VALORI(K2:K18)));INDICE(K2:K18;CASUALE.TRA(1;CONTA.VALORI(K2:K18)));INDICE(K2:K18;CASUALE.TRA(1;CONTA.VALORI(K2:K18))))

      Spero di esserti stato utile, se hai una versione “vecchia” di Excel puoi anche utilizzare CONCATENA anzichè CONCAT!

      A presto,

      Marco

  4. Ciao Marco,
    è possibile creare una lista di codici da una base di partenza già presente?
    esempio:
    nel mio DB ho questi codici:
    AB34FT
    BD12SL
    ….
    è possibile continuare la creazione di codici con la stessa struttura senza che si ripetano quelli già presenti?

    Inoltre mi sembra di capire che non siano mutevoli?
    Grazie in anticipo

    1. Ciao Edoardo,

      grazie per la domanda, è una richiesta interessante!

      Ho aggiornato la guida inserendo anche questa casistica, la trovi in fondo – basta che scarichi il file e potrai utilizzarlo come preferisci, se non hai Excel 365 puoi accedere da Excel online che è gratuito!

      Per verificare che non siano gli stessi si potrebbe pensare a un check ulteriore successivo, ma mi pare una possibilità veramente remotissima, quanti caratteri hai nel tuo esempio al minimo?

      Spero di esserti stato utile.

      A presto,

      Marco

  5. Ciao,
    se volessi invece randomizzare il contenuto di una cella con esattamente lo stesso numero di caratteri della cella originaria?
    Per intenderci, supponiamo abbia un elenco di nomi che vorrei mascherare e randomizzare per motivi di privacy.
    Es. il nome “Anna Conti” lo vorrei sostituire con valori random ma con lo stesso numero di caratteri che lo compongono (es. “A4d! Sc152”).
    Mi sai aiutare?
    Grazie

    1. Ciao Matteo,

      grazie per la domanda, ho aggiunto una parte in fondo all’articolo che potrebbe aiutarti.

      Considera che nel tuo caso Excel considera lo spazio come un numero (almeno con i miei calcoli), quindi avresti esclusivamente un codice alfanumerico. Puoi sicuramente aggiungere regole più complesse, prendendo spunto dalla parte precedente dell’articolo.

      Spero di esserti stato utile!

      A presto,

      Marco

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *