Foglio Excel automatizzato

Stasera parliamo del nuovo foglio Excel automatizzato che ho pubblicato e spedito alla nostra piccola comunità. È l’evoluzione del foglio di lavoro che abbiamo realizzato nei post “Come realizzare fogli di lavoro pronti all’uso” e “Come realizzare fogli Excel pronti all’uso – 2a parte”. Allora avevamo ottenuto un foglio Excel flessibile e pronto all’uso con cui eseguire analisi preliminari su una base dati di dimensioni contenute attraverso tabelle pivot e funzioni statistiche. Con questo nuovo foglio di lavoro siamo andati ad automatizzarlo.

Il foglio Excel automatizzato

Il primo foglio di lavoro non aveva macro e si affidava a formule e funzioni e alle caratteristiche di tabelle Excel e tabelle e grafici pivot per ridurre al minimo le attività da svolgere per realizzare:

una prima analisi statistica descrittiva del campo della base dati oggetto del nostro interesse,
e la preparazione di tabelle e grafici pivot con cui realizzare l’analisi esplorativa, l’attività principale da svolgere in manuale.

Con questo secondo foglio excel automatizzato, introduciamo un primo livello di automazione intelligente, con il maggior rapporto tempo impiegato per lo sviluppo e risultati ottenuti. Il codice necessario è piuttosto contenuto, ma allo stesso tempo offre una considerevole flessibilità.

Il primo foglio era composto da diverse pagine preimpostate per le attività da svolgere, oltre quella delle istruzioni, il secondo contiene solo la pagina “Dati” in cui caricare i dati da analizzare e la pagina “Pannello” da cui lanciare gli automatismi. Questo perché gli automatismi si occupano della creazione e predisposizione delle pagine, tipico nel caso di automatismi che eseguono attività senza interazione o con un’interazione minima con l’operatore.

Come si può vedere i comandi a disposizione nel “Pannello” sono 5:

“Analisi descrittiva“, crea una nuova pagina con l’analisi statistica descrittiva del campo che vuoi esaminare;
“Analisi con pivot“, predispone una pagina con una tabella e un grafico pivot con cui eseguire l’analisi;
“Aggiorna pivot“, aggiorna tutte le tabelle pivot del foglio di lavoro;
“Salva“, salva il foglio di lavoro;
“Salva ed esci“, salva e chiude il foglio di lavoro

Nel primo modello automatizzato che ho regalato a fine anno, quello del post “Il primo modello automatizzato”, ho inserito automatismi elementari per la gestione del foglio di lavoro (es: salva ed esci) e un automatismo che per l’esecuzione di controlli sui dati inseriti ha bisogno di informazioni dall’operatore, gestiti una tantum attraverso una pagina di configurazione permanente. In quest’ultimo foglio di lavoro invece i dati necessari sono minimi e variabili, quindi vengono richiesti all’operatore quando necessario.

Come si usa il foglio?

L’uso è intuitivo:

si caricano i dati nella pagina “Dati”;
si lancia l’analisi statistica indicando quale campo della base dati si vuole esaminare; verrà creata una pagina “Analisi descrittiva” in cui verrà inserito il report dello strumento omonimo e altri valori aggiuntivi calcolati tramite formule e funzioni;
si lancia la predisposizione della prima pivot per l’analisi manuale dei dati; verrà creata una pagina “Analisi” con una tabella pivot preimpostata in cui saranno caricati tutti i campi della base dati, almeno come filtri, e verrà creato il relativo grafico pivot;
e si esegue l’analisi lavorando sulla pivot e valutando i risultati sul grafico relativo;
quando necessario si esegue l’aggiornamento di tutte le pivot cliccando sul pulsante relativo.

Tutte le spiegazioni sono inseriti dentro al foglio di lavoro.

Come si esegue l’analisi?

Ci sono diversi modi per procedere a seconda della base dati e degli scopi. Eseguire un’analisi significa di solito individuare o calcolare i valori che ci interessano, studiarne l’andamento nel tempo, individuare i parametri che influiscono sul valore calcolato, direttamente e indirettamente, valutarne le distribuzioni secondo i parametri principali, studiare le loro relazioni e altro ancora.

Nella pratica, lavorando con le tabelle pivot, prima si imposta il valore da esaminare, per esempio resa, o fatturato, quantitativi, dimensioni, eccetera, usando i calcoli disponibili (somma, media, conteggio, %) o impostando un campo calcolato apposito. In caso di più valori si preparano pivot diverse in pagine diverse. Se ne studia l’andamento del tempo, prendendo la data rilevante del record e raggruppandola per periodi significativi (mesi). Poi si identificano i parametri principali (per esempio, prodotti, clienti, articoli, impianti, ecc) e se ne studia la distribuzione, uno alla volta, con pazienza. Via via si usa il grafico pivot per osservare visivamente i dati della pivot e coglierne a colpo d’occhio quello che con i numeri richiede maggior valutazione. Via via che si ottengono risultati, si copia la pivot di lavoro in una posizione diversa o in un’altra pagina e si aggiungono note, spiegazioni e commenti.

Eccetera. E’ opportuno che approfondiamo questo discorso in un post apposito con spiegazioni più estese, esempi ed immagini.

Gli automatismi

Diamo un’occhiata alle macro (Alt+F11 per aprire l’editor vba). Per chi conosce e mastica il vba appare evidente che non c’è nulla di così complesso, è tutto piuttosto semplice ed efficiente.

Il primo comando “Analisi descrittiva” vuole dimostrare come sia possibile usare:

uno degli “strumenti di analisi” attraverso una macro, in questo caso l’”analisi descrittiva”; lo stesso vale per praticamente tutti gli altri strumenti di excel comprese le pivot;
le macro per inserire formule nelle celle di excel; avrei potuto inserire direttamente i valori, come nel caso del report, così come avrei potuto calcolare i dati del report con le funzioni;
le macro per gestire creazione ed eliminazione delle pagine, nulla di così complesso in fondo e piuttosto funzionale.

Il secondo comando “Analisi con pivot“ vuole mostrare come sia possibile creare e configurare una tabella pivot e un grafico pivot attraverso una macro. In questo caso, la macro si limita a caricare tutti i campi per preparare una tabella pivot che l’operatore andrà a manipolare personalmente in modi non prevedibili.

Attraverso una macro è possibile eseguire anche una parte dell’analisi preliminare, per esempio predisponendo non una sola pivot, ma tutte le pivot e grafici relativi per visualizzare andamenti, distribuzioni e relazioni, risparmiando in questo modo all’operatore il lavoro di manipolazione e lasciandogli solo il compito di esaminare e scegliere le pivot più utili.

In alternativa attraverso gli automatismi si potrebbe gestire una parte dell’analisi interrogando però l’operatore per ricevere le informazioni necessarie. Per entrambi i casi però il codice è molto più rilevante e complesso e difficilmente può valere la pena svilupparlo, quando un operatore esperto può fare lo stesso in pochi minuti.

Il terzo comando “aggiorna pivot” serve nel caso l’analisi debba essere ripresa più volte, con aggiornamenti dei dati o revisioni, e quindi si riveli necessario aggiornare le pivot, indipendentemente dal loro numero.

Il risultato complessivo è un foglio Excel automatizzato pratico e flessibile che può rivelarsi utile in molte occasioni e che può essere facilmente integrato con altri foglio di lavoro.

Avvertenze

Se hai bisogno di integrare il foglio di lavoro con altri fogli, puoi trasferire pagine e macro dal modello ai tuoi fogli di lavoro o viceversa. Per trasferire le pagine devi usare il comando di sposta o copia pagina (clicca con il pulsante destro sulla linguetta del nome della pagina). Per trasferire la macro apri l’editor con Alt+F11, nella finestra del progetto seleziona il modulo e trasferiscilo nel tuo foglio di lavoro trascinandolo; altrimenti esportalo (clicca con il pulsante destro e lancia il comando “Esporta file”) e importalo nel tuo fogli di lavoro.

Attenzione, se Excel ha ancora le impostazioni di protezione di base, non potrai usare le macro del foglio di lavoro. Per poter lavorare devi modificare le impostazioni per le macro. Per farlo devi accedere alle opzioni di Excel (versione 2016 tramite menù file, comando “Opzioni”; versioni precedenti pulsante office, pulsante “opzioni di Excel”, poi seleziona “centro protezione”, “impostazioni centro protezione”, “impostazioni macro” e finalmente seleziona l’opzione “abilita macro”. In caso di dubbi vai a questa pagina.

In futuro andremo a riprendere questo foglio excel automatizzato con l’intenzione di vedere come è possibile potenziare gli automatismi, introducendo anche l’uso delle maschere.

Per conoscere gli altri fogli dimostrativi pubblicati da Excel Professionale puoi cliccare qui.