[excel] Come creare una password casuale di lunghezza arbitraria con un’unica formula

Mi sono chiesto se ci fosse un modo, utilizzando Excel, di generare una password casuale di lunghezza arbitraria con una singola formula.

Naturalmente utilizzando VBA sarebbe semplicissimo, si potrebbe creare una propria funziona ed utilizzarla all’interno della formula. In alternativa si potrebbe creare una tabella di riferimento e generare la password a partire da essa (qui c’è un esempio interessante).

Però mi sono impuntato a voler creare una singola formula, utilizzando le funzioni disponibili nativamente in Excel, per generare una password di lunghezza arbitraria.

Soluzione veloce

Per chi non avesse voglia di proseguire nell’analisi della questione la soluzione è la seguente (per 1 singolo carattere preso tra maiuscole, minuscole, numeri e caratteri speciali):

Per generare una password di 2 caratteri basta concatenare la formula precedente per 2 volte, di 3 caratteri per 3 volte ecc (di seguito l’esempio per 2 caratteri):

Spiegazione

Anzitutto partiamo dalla seguente idea. Se avessi un vettore di 3 caratteri, potrei estrarre casualmente uno dei tre caratteri?

Per farlo mi è sufficiente scrivere:

Ad ogni ricalcolo del foglio questa formula pescherà casualmente un valore tra 10, 20 e 30. Tra le parentesi graffe {} ho inserito una matrice (nello specifico un vettore colonna) di 3 valori. Il punto fa da separatore di riga, altrimenti il backslash farebbe da separatore di colonna.

In ogni caso la presente formula sarebbe equivalente alla seguente situazione:

In A4 in questo caso vedrei un valore casuale preso tra A1:A3.

Seconda idea. Sarebbe possibile generare, al posto dei suddetti valori, dei valori casuali che escano fuori da una funzione? Purtroppo Excel non consente di scrivere esplicitamente una matrice contenente a sua volta delle funzioni, senza passare dall’utilizzo delle celle.

Allora mi sono chiesto: ci sarà un modo per scrivere una stringa e trasformarla in un vettore mediante un separatore di stringa (una specie di str_split per intendersi nel PHP)? Se la stringa è composta di singoli caratteri che dovranno diventare elementi del vettore è effettivamente possibile.

Possiamo cioè trasformare il nome Noctis nel vettore {“N”.”o”.”c”.”t”.”i”.”s”}

Per farlo è sufficiente utilizzare:

Faccio notare che INDIRETTO prendere le righe dalla 1 alla 6. Con RIF.RIGA generiamo un vettore di riferimenti equivalente a {1.2.3.4.5.6}. Per inciso la funzione sarebbe potuta anche essere così:

Dal momento però che mi fa fatica scrivere tutto il vettore da 1 a 6 (più che altro se fosse stato più lungo sarebbe risultato abbastanza impensabile) preferisco generarlo con questo semplice trucchetto.

A questo punto abbiamo capito che possiamo spezzare una stringa arbitraria nel vettore dei caratteri che la compongono.

Adesso prendiamo in considerazione la seguente tabella ASCII.

Grazie alla funzione CARATT.UNI posso estrarre un carattere a partire dal suo riferimento numerico nella suddetta tabella (nello specifico qualunque carattere UNI-CODE, ma non è il momento di approfondire la cosa).

Per esempio se digito:

Ottengo la lettera p minuscola.

Quindi posso generare caratteri casuali mischiando questa funzione con CASUALE.TRA nel modo seguente.

Caratteri maiuscoli:

Caratteri minuscoli:

Caratteri speciali:

Per generare invece i numeri da 0 a 9 mi basta utilizzare:

Quindi usando la formula:

Posso generare una stringa di 4 caratteri contenente un numero casuale, una maiuscola casuale, una minuscola casuale e un carattere speciale casuale.

Inserendo questa dentro la suddetta formula con STRINGA.ESTRAI posso estrarre i quattro caratteri dalla stringa in un vettore di caratteri casuali con la seguente formula:

A questo punto inserendo il tutto dentro a INDICE ottengo la precedente formula e il gioco è fatto.

Per generare delle password casuali di 16 caratteri come queste:

!#lKTHLb0nx2Z(0H
7&x(128’#L#(t6B3
,77YJ”lu35%LKYVm
svkA.ur1Y””+#r*t
15I35t1Z”(/Lz#0a

Ci sarebbe sufficiente utilizzare la formula un po’ più lunga e ripetuta come detto all’inizio.

 

Vedi articolo

[vba] Utilizzare il dizionario nelle macro di Excel per calcolare la media dei voti, in diverse materie per diversi studenti

Propongo un veloce esercizio in VBA sull’utilizzo dei dizionari.

Immaginiamo di avere un elenco di studenti e voti in diverse materie come nell’esempio seguente:

Vogliamo calcolare la media dei voti per ciascun studente in ciascuna materia, mettendo tutto in una tabella tipo pivot nel modo seguente:

La cella gialla è in L10

A tale scopo realizziamo la seguente macro in VBA utilizzando i dizionari. Per usare il dizionario anzitutto aggiungiamo il relativo riferimento da Strumenti > Riferimenti…

Il riferimento che ci interessa è il Microsoft Scripting Runtime, libreria dalla quale andremo a prelevare Scripting.Dictionary

Fatto tutto questo vediamo il codice:

Faccio notare che nel mio esempio la tabella di scrittura parte dalla posizione L10 nel foglio di calcolo attivo.

Quello che abbiamo realizzato è un dizionario di dizionari. Per realizzarlo utilizziamo:

 

Vedi articolo

[vba] Creare una funzione che accetti un numero indefinito di parametri

Per questo esercizio vediamo come creare una funzione personalizzata simile (perché non andremo ad implementare tutte le alternative) al SOMMA.PIÙ.SE in VBA per Excel.

Anzitutto individuiamo le criticità:

  1. Avremo bisogno di accettare un range per la somma ed n coppie di parametri come intervalli e criteri, la nostra funzione avrà quindi la forma di FUNZIONE(somma, [intervallo 1], [criterio 1], …)
  2. I criteri possono essere numerici oppure stringhe di confronto, come per esempio “>2”

Anzitutto vediamo come creare una funzione che accetti n parametri come abbiamo definito di sopra. Per cui digitiamo in VBA:

In questo caso abbiamo inserito il minimo indispensabile, definendo il risultato come un Double, dichiarando internamente una variabile totale ed impostandola su 0. Questa variabile la utilizzeremo in seguito. L’argomento somma è di tipo Range (qui occhio agli indici, come vedremo tra poco!) e poi segue un vettore di parametri args definiti come Variant.

Per scorrere i vari parametri possiamo usare l’istruzione:

A questo punto vogliamo costruire un vettore di criteri lungo quanto l’intervallo della somma e che per ogni chiave contenga un valore equivalente a 0 oppure ad 1, in funzione che si debba o meno sommare il valore nell’intervallo somma.

Per fare questo definiamo:

Con il metodo somma.count misuriamo la dimensione del Range somma, attenzione perché il range si muove da 1 fino a somma.count, mentre il nostro vettore criteri andrà da 0 fino a somma.count – 1

Infine impostiamo tutto il vettore criteri() su 1, questo ci servirà tra poco per le operazioni logiche.

A questo punto non ci rimane che valutare tutti i criteri, tenendo presente che vanno a coppie di intervallo criteri e criterio. Perciò utilizzeremo l’istruzione:

Gli intervalli dei criteri si trovano in posizioni pari, mentre il criterio in posizione dispari.

Infine utilizzeremo il metodo Evaluate sfruttando la funzione SE di Excel per valutare ogni criterio, nel modo seguente:

Faccio notare che in Evaluate va scritta la funzione col nome in inglese. Dal momento che i criteri possono essere numerici oppure stringhe, come abbiamo detto all’inizio, dobbiamo distinguere tra le due cose e quindi scriveremo:

Infine ricordiamoci che tutti i criteri devono essere validi, quindi per modificare ogni posizione del vettore criteri() useremo il prodotto. In questo modo se sono tutti veri risulterà 1, un prodotto di tutti 1 insomma, mentre se anche un solo criterio sarà falso allora il prodotto risulterà 0, dal momento che basta uno 0 nel prodotto per azzerare tutto.

Detto tutto ciò la nostra funzione finale sarà simile a questa:

Inutile dire che la funzione potrebbe essere ulteriormente affinata per coprire tutti i possibili casi in cui può essere utilizzata SOMMA.PIÙ.SE 😉

Vedi articolo

[VBA] Aggiungere nuovo foglio con data corrente in Excel

Immaginiamo di voler aggiungere, in modo programmatico, un nuovo foglio con un nome e la data corrente, per esempio IMPORTAZIONE 10 09 2018.

Quello che vedremmo come risultato nella cartella di lavoro in Excel sarebbe qualcosa di analogo:

Per farlo costruiamo una funzione in VBA nel modo seguente:

Testiamo la funzione mettendola dentro una sub:

Come è possibile notare la funzione restituisce il riferimento al nuovo foglio creato, che possiamo utilizzare nella sub di esempio per scrivere, per esempio, dentro ad una cella.

Se volessimo aggiungere un controllo, nella solita funzione CreaNuovoFoglio, perché non ci siano fogli duplicati, sarebbe sufficiente modificare la funzione nella maniera seguente:

In questo modo al nome del foglio viene posposto un termine n-simo, in questo caso (1), (2) ecc. Se lo si desidera chiaramente si può aggiungere qualunque termine oppure decidere di interrompere l’operazione.

Vedi articolo

[excel] Copiare solo celle visibili e non quelle nascoste

Problema: Quando si seleziona un gruppo di celle e si copiano normalmente, vengono copiate anche le celle nascoste, mentre si vorrebbero copiare solamente le celle visibili

Immaginiamo di avere un intervallo di celle come il seguente:

Se adesso decidessimo di nascondere la terza riga avremmo il seguente risultato:

Selezionando l’intervallo A1:B5 e copiando (per esempio con CTRL+C) copieremo in realtà anche la riga 3 che è nascosta. Lo stesso vale quando si utilizzano i filtri sulle celle.

Per copiare invece solamente le celle visibili dobbiamo fare nel modo seguente:

  1. Selezioniamo l’intervallo che vogliamo copiare come faremmo normalmente
  2. Nella scheda Home spostiamoci su Trova e seleziona e selezioniamo Vai a formato speciale…
  3. Nella finestra selezioniamo Solo celle visibili e premiamo Ok
  4. Eseguiamo la copia come faremmo normalmente (o banalmente premendo CTRL+C)

Adesso abbiamo copiato solo le celle visibili e possiamo incollarle dove vogliamo.

Per maggiori approfondimenti consiglio la guida ufficiale di Office: Copiare solo le celle visibili

Vedi articolo

[vba] Classe per interrogare in SQL un foglio excel

Obiettivo: creare una classe per poter interrogare tramite query SQL un foglio excel, leggendo e scrivendo i dati come se lavorassimo con tabelle SQL

Questo esercizio riprende il punto direttamente da questo precedente: Interrogare foglio di Excel con SQL in VBA

Dopo aver visto come poter interrogare con query SQL un foglio excel, vediamo come creare una classe ad hoc che ci permetta di leggere e scrivere i dati mediante query SQL. Per farlo anzitutto aggiungiamo una nuova classe:

Premendo ALT+F11 apriamo l’editor Visual Basic, ci posizioniamo su un qualunque punto del progetto a sinistra e clicchiamo col destro (per esempio su Form), poi scegliamo Inserisci ⇒ Modulo classe. A questo punto verrà creata una nuova classe. Per rinominarla è sufficiente selezionarla e poi spostarsi nel riquadro delle proprietà.

A questo punto incolliamo dentro la classe appena creata (tenendo presente poi il nome scelto) il seguente codice:

Faccio notare come nel metodo dbout dobbiamo fare un’operazione un po’ superflua in apparenza, ovvero contare prima le righe e le colonne, per poi ridimensionare il vettore result. Questa operazione è necessaria perché ReDim, in VBA, non permette di ridimensionare l’intera matrice (vettore di vettori), ma solo l’ultima dimensione. Quindi, per esempio, result(4,10) potrebbe diventare result(4,20), ma non result(5,10) oppure result(5,20). Un’alternativa sarebbe quella di creare una funzione che trasponga il vettore e lo modifichi, ritrasponendolo di nuovo, ma dal punto di vista del calcolo credo sarebbe molto più oneroso che fare così.

Fatto questo potremo utilizzare la nostra nuova classe nella maniera seguente.

Una volta creata la classe può essere esportata, oppure importata nel modo seguente:

Per chi volesse è possibile scaricare la classe già pronta cliccando qui: DbSQL.zip

Hash MD5: A2A18A42BB11AEA86DF1AB4F547B4414

La classe è stata testata su Excel 2016.

Vedi articolo

Calcolare IRR (Internal Rate of Return o TIR.COST) in VBA

Problema: la funzione TIR.COST (Tasso Interno di Rendimento) restituisce un unico valore per l’IRR anche quando ce ne è più di uno

Senza discutere sull’interpretazione dell’IRR stesso, immaginiamo di avere i seguenti due scenari di flusso di cassa.

I Scenario

Questo produce un grafico VAN-IRR come quello seguente:

In questo caso abbiamo due valori dell’IRR rispettivamente 15,47% e 33,33%; la funzione TIR.COST restituisce solamente il primo valore.

II Scenario

A questa serie di flussi di cassa corrisponde il seguente grafico:

In questo caso abbiamo due valori dell’IRR rispettivamente 1,99% e 19,65%; la funzione TIR.COST restituisce solamente il secondo valore.

Costruiamo quindi in VBA le funzioni necessarie per effettuare questo calcolo; come già mostrato anche qui utilizzeremo una funzione che restituisca un vettore di valori sotto forma di Variant, in modo da poterli valutare singolarmente (per esempio utilizzando le funzioni MIN e MAX, quando i valori sono solo due, quindi =MIN(IRRX(A1:A10)) oppure =MAX(IRRX(A1:A10))).

La funzione finale si chiama IRRX, quella che utilizzeremo dentro il foglio excel.

Le altre funzioni servono rispettivamente a:

  • IRRv1 – calcolare in modo approssimativo tutti gli IRR possibili su uno specifico flusso di cassa, con valore massimo per l’IRR al 30.000% (espresso come max = 300), inutile dire come questo valore possa essere impostato anche a molto meno per velocizzare i calcoli; l’IRR qui viene calcolato per ogni punto percentuale con scarti del 1%
  • IRRv2 – qui calcoliamo, per ogni valore di IRR trovato con la precedente funzione, il valore approssimato mediante iterazione su valori che vanno da IRRv2 ± 1%
  • VAN – questa funziona calcola semplicemente il VAN sul dato IRR

 

Testato su Excel 2016.

Vedi articolo

Creare una funzione personalizzata in VBA per Excel che restituisca una matrice

Obiettivo: creare una funzione personalizzata in Excel che restituisca come risultato una matrice n x m

Supponiamo di voler realizzare una funzione personalizzata in Excel che effettui la medesima operazione di MATR.PRODOTTO, ma allo stesso tempo consideri sempre la matrice in ingresso A come una matrice n x 1 e la matrice in ingresso B come una matrice 1 x m. Facciamo questa semplificazione a titolo di esempio.

Quindi abbiamo due matrici tipo queste:

A =  \begin{bmatrix}  a_1 \\ a_2 \\ a_3  \end{bmatrix}    B =  \begin{bmatrix}  b_1 & b_2 & b_3  \end{bmatrix}

Il loro prodotto risulterà così:

A \times B=  \begin{bmatrix}  a_1b_1 & a_1b_2 & a_1b_3 \\  a_2b_1 & a_2b_2 & a_2b_3 \\  a_3b_1 & a_3b_2 & a_3b_3  \end{bmatrix}

Nel nostro modulo aggiungiamo ora il seguente codice:

Notiamo che in questo modo vengono percorsi tutti i valori del range, indipendentemente dalla loro geometria, come se fossero in una riga o colonna.

Per testare il risultato possiamo fare una verifica su un esempio con la funzione MATR.SOMMA.PRODOTTO. Nel caso specifico creiamo una versione e parametrizzabile del SOMMA.PIÙ.SE.

A1:A7 e B1:B7 sono riempiti con valori da esempio. E2:H2 saranno quattro spazi dove potremo inserire, a nostro piacimento da 0 a 4 nominativi da verificare. In E3 abbiamo la seguente formula di controllo (NB: fatta con MATR.PRODOTTO):

=MATR.SOMMA.PRODOTTO(–(A2:A7=E2:H2);MATR.PRODOTTO(B2:B7;E1:H1))

In E4 aggiungiamo invece la formula con la nostra versione di MATR.PRODOTTO:

=MATR.SOMMA.PRODOTTO(–(A2:A7=E2:H2);MATH_MATRICE(B2:B7;E1:H1))

Scopo raggiunto.

Vedi articolo