Come realizzare fogli Excel pronti all’uso – 2a parte

Concludiamo l’argomento iniziato con il post precedente “Come realizzare fogli Excel pronti all’uso”. La volta scorsa abbiamo visto come, dopo una certa esperienza con Excel, tutti più o meno raccogliamo nella nostra cassetta degli attrezzi modelli e fogli Excel strutturati e predisposti per usi più o meno specifici.

A quale scopo avere fogli Excel già pronti?

Per risparmiare tempo. Non ha senso ricostruire i fogli Excel tutto da zero ogni volta che lavori su una base dati diversa e scommetto che già usi una decina di modelli di Microsoft o altri pensati per usi specifici: per la contabilità, il magazzino, la qualità e così via. Qui trovi una bella collezione di fogli Excel e sono gratuiti.

Anch’io ne ho diversi nella mia scatola degli attrezzi, perché sono utili per l’uso per cui sono stati concepiti, ma difficilmente per altro. Invece i fogli Excel che di solito utilizzo e ti propongo sono più flessibili e automatizzati in modo esteso. Perché la maggior parte delle attività di gestione di un foglio Excel sono simili e ripetitive: aggiorna/importa i dati, verifica i dati, analizza i dati, elabora i dati, controlla i risultati, crea/aggiorna grafici e report, aggiorna la dashboard, stampa, salva, chiudi, eccetera.

Naturalmente ci sono mille usi diversi di Excel, dalla gestione dei conti di famiglia, alla gestione dei report aziendali, e un solo foglio non può fare tutto quello che serve, ma con una dozzina puoi coprire buona parte dei tuoi bisogni. Dal classico report periodico, al pannello di controllo di parametri e indicatori, all’analisi statistica mono/multivariabile, e via dicendo.

Come impostare i fogli Excel

Nel post precedente avevamo iniziato a vedere come si imposta un foglio Excel basilare, non specifico, da impiegare per analisi ed elaborazioni veloci.

La volta scorsa abbiamo visto insieme come:

creare la pagina dati
creare la pagina di calcolo

Questa volta vedremo come creare:

pagina di analisi statistica
report
grafici
pagina di controllo

Pagina di analisi statistica

Prendi una pagina vuota e rinominala “Analisi”. Se non hai altre pagine vuote premi Maiusc+F11 per crearne una nuova. In alternativa puoi farlo con il comando “Inserisci foglio” presente nella barra in basso come ultima linguetta a destra di tutte le pagine.
Nella cella A1 digita il titolo della pagina: “Analisi statistica di una variabile”, imposta grassetto e 14 punti di dimensione.
Nella cella F1 digita “C”, imposta il grassetto.
In G1 digita “inserisci la colonna della variabile da analizzare (lettera della colonna)”.
Nella cella A3 digita “Parametro”, nella cella B3 digita “Valore”, imposta il grassetto.
Nella cella A4 digita “N° MISURE” e nella cella B4 inserisci la formula “=CONTA.NUMERI(INDIRETTO(“Dati!” & $F$1 & “:” & $F$1))”
IN A5 digita “MEDIA” e nella cella B5 inserisci la formula “=MEDIA(INDIRETTO(“Dati!” & $F$1 & “:” & $F$1))”
Nella cella A6 digita “DEVIAZIONE STANDARD” e nella cella B6 inserisci la formula “=DEV.ST(INDIRETTO(“Dati!” & $F$1 & “:” & $F$1))”
Nella cella A7 digita “Coeff. Variazione” e nella cella B7 inserisci la formula “=B6/B5”
In A8 digita “Indice di simmetria” e nella cella B8 inserisci la formula “=ASIMMETRIA(INDIRETTO(“Dati!” & $F$1 & “:” & $F$1))”
Nella cella A9 digita “Curtosi” e nella cella B9 inserisci la formula “=CURTOSI(INDIRETTO(“Dati!” & $F$1 & “:” & $F$1))”
Nella cella A10 digita “Varianza” e nella cella B10 inserisci la formula “=VAR(INDIRETTO(“Dati!” & $F$1 & “:” & $F$1))”
Nella cella A11 digita “MINIMO”, nella cella B11 inserisci la formula “=MIN(INDIRETTO(“Dati!” & $F$1 & “:” & $F$1))” e nella cella C11 inserisci la formula “=B11”
Nella cella A12 digita “1 QUARTILE (25 Percentile)”, nella cella B12 inserisci la formula “=QUARTILE((INDIRETTO(“Dati!” & $F$1 & “:” & $F$1));1)” e nella cella C12 inserisci la formula “=B12-B11”
Nella cella A13 digita “MEDIANA (50 Percentile)”, nella cella B13 inserisci la formula “=MEDIANA(INDIRETTO(“Dati!” & $F$1 & “:” & $F$1))” e nella cella C13 inserisci la formula “=B13-B12”
Nella cella A14 digita “3 QUARTILE (75 Percentile)”, nella cella B14 inserisci la formula “=QUARTILE((INDIRETTO(“Dati!” & $F$1 & “:” & $F$1));3)” e nella cella C14 inserisci la formula “=B14-B13”
Nella cella A15 digita “MASSIMO”, nella cella B15 inserisci la formula “=MAX(INDIRETTO(“Dati!” & $F$1 & “:” & $F$1))” e nella cella C15 inserisci la formula “=B15-B14”
Nella cella A16 digita “Intervallo” e nella cella B16 inserisci la formula “=B15-B11”
Se stai utilizzando una versione abbastanza recente di Excel, allora seleziona l’intervallo di celle C11:C15 e premi F11, comparirà un grafico dei valori, probabilmente a istogrammi; clicca sul pulsante “cambia tipo di grafico” e seleziona gli istogrammi orizzontali, poi clicca su “inverti righe/colonne”, poi seleziona il primo riquadro a sinistra, clicca con il destro per aprire il menu e clicca su “formato serie dati”, vai su riempimento e imposta “nessun riempimento”; fai lo stesso con il secondo riquadro da sinistra, poi clicca su aggiungi “elemento grafico”, “barre di errore”, “altre opzioni” per far apparire la barra di errore; selezionala per far apparire la finestra formato, poi imposta direzione “negativo” e percentuale “100%”; fai fai lo stesso con l’ultimo riquadro a destra per avere il tuo grafico box-plot.

Cosa serve tutto questo?

Quello che abbiamo fatto è usare le funzioni di Excel per creare una pagina che elabora i dati come desideriamo, in questo caso la pagina calcola i parametri principali della variabile in esame. Dopo aver copiato o aggiornato i dati nella tabella della pagina “Dati”, si modifica la lettera della cella F1 puntandola alla colonna della variabile da analizzare.

Tutto sommato è un esempio elementare che si può migliorare con facilità: per esempio si può impostare una tabella delle frequenze, usando la funzione “Frequenza()” su classi calcolate, o usando una pivot che però va impostata ogni volta che devi puntare a una variabile diversa. In entrambi i casi possiamo creare un grafico che mostri le frequenze e si aggiorni all’aggiornamento della tabella.

Lo scopo di tutto questo è mostrarti come è possibile strutturare una pagina per un uso specifico, ma che puoi rendere flessibile usando gli strumenti non avanzati. Se vuoi approfondire la tua conoscenza degli strumenti di analisi di Excel, puoi iniziare da qui e qui.

Report e Grafici

Prendi un’altra pagina vuota e rinominalo “Report”. L’idea è impostare un mini report dopo aver analizzato i dati con le tabelle pivot.
Nella cella A1 digita “Report”, imposta il grassetto, grandezza 14 punti.
Vai alla pagina “Preanalisi”, seleziona e copia una tabella pivot intera, poi incollala nella cella A4 della pagina “Report”; in realtà una volta importati i dati la prima volta, verificati e analizzati, dovrai impostare la pivot per mostrare il valore che vuoi proporre con il report.
Nella cella A3 digita un titolo per la tabella, come “Titolo tabella”, imposta il grassetto, grandezza 12 punti; inserirai il titolo vero una volta trovate il dato che ti interessa
Seleziona una cella della tabella pivot e premi F11 per creare all’istante un grafico pivot legato alla tabella. Rinomina la pagina del grafico a “gReport” o come gradisci. Se vuoi presentare il grafico nella stessa pagina dei dati, seleziona il grafico, clicca con il pulsante destro del mouse, clicca sul “sposta grafico” e trasferiscilo nella pagina “Report” a destra della tabella, o dove preferisci.
Se necessario aggiungi una seconda tabella pivot e un altro grafico.

Sui report

Sull’argomento “report” si possono scrivere centinaia di pagine e dedicheremo diversi post. Due righe per parlare di:

Contenuto
Nella mia esperienza ho visto fiume di report e la maggior parte di questi riportava troppi numeri, tabelle e grafici. E’ comprensibile, ci hai sudato sopra, hai studiato i dati, hai partorito i calcoli e trovato i risultati e non vuoi mostrarli a tutti? No! Nella maggior parte dei casi, il 50-80 % dei dati è inutile o non verrà compreso o considerato. Taglia senza pietà. E quando hai tagliato, taglia ancora. Mostra solo quello che serve. Una o due tabelle, uno o due grafici. Meglio uno. E poi chiedi a qualcuno che non sa niente dell’argomento di dirti sinceramente cosa ne pensa. Approfondiremo l’argomento.
Aspetto
Nelle istruzioni ho messo molto poco sulla definizione dello stile e dell’aspetto del report: bordi, colori, carattere, dimensioni e così via. Perché? Perché scommetto che ne sai più di me e che questa misera pagina ha già assunto o assumerà un aspetto professionale. Parleremo anche di questo, ma c’è una cosa che devi sempre ricordarti quando lavori sull’aspetto di un report: la cosa importante è il dato che vuoi comunicare, che sia un numero, una tabella, un valore in un grafico, un andamento, quindi tutto deve portare chi legge al dato in questione, tutto. Non devono esserci distrazioni.
Gestione
Un report è fatto di tabelle e grafici perché deve presentare dei dati, ma quali sono i migliori strumenti per costruirli? Le pivot? Beh, sono comode, ma non solo la cosa più facile da leggere e gestire. Spesso preferisco usare tabelle fatte con il copia-incolla o agganciate ad altre celle o tabelle, o anche solo un grafico o due. Questo per due semplici motivi: uno, un report non è una dashboard, un report si usa una volta, one shoot; due, una pivot non è così manipolabile e configurabile come ci immaginiamo e come spesso ci serve. Però se hai bisogno di un report dinamico sono ottime.

Quindi a cosa serve questa semplice pagina?

Serve a impostare un report basato su una-due tabelle pivot. Come tali, importando nuovi dati e cambiando i nomi dei cambi, le tabelle dovranno essere reimpostate. Una volta fatta una analisi preliminare nella pagina relativa e individuato il o i valori che si stanno cercando, è sufficiente copiare la pivot nella pagina “Report” sovrascrivendo la pivot esistente e rinominandola la tabella con il nome di quella sovrascritta per agganciare il grafico pivot. In alternativa si può impostare il grafico da capo.

Pagina di controllo

Prendi un altro foglio vuoto e rinominalo “Help” o “Cpanel” o come preferisci.
Nella cella A1 digita “Pagina di Controllo”, imposta il grassetto, grandezza 16 punti.
Seleziona le celle B3-B40 e assegna un colore gradevole alle celle.
Ora seleziona le colonna B e C e imposta una larghezza di 24 o 30.
Seleziona la colonna D e imposta una larghezza di 80.
Poi seleziona “Inserisci” nella barra principale, clicca “Casella di testo” e crea una casella rimando dentro l’intervallo di celle D2:D40.
Nella casella scrivi “Istruzioni per l’uso del foglio di lavoro” e naturalmente di seguito scriverai cosa come l’origine dei dati, gli eventuali controlli da eseguire, come usare gli automatismi che installerai.
Seleziona il menù “Sviluppo” dalla barra principale, se non è visibile devi cliccare con il pulsante destro del mouse sulla barra e selezionare “personalizza barra multifunzione”, o nel caso di versioni più vecchie devi passare dal menù “opzioni” del pulsante “start”. In “sviluppo” clicca su “inserisci”, “controlli”, “pulsanti” e aggiungi un pulsante all’interno della colonna B, diciamo in B4:B5; si aprirà la finestra per assegnare la macro, ma richiudila; scrivi “comando” come testo del pulsante, lo cambierai dopo; poi clicca con il pulsante destro del mouse sul pulsante e copialo almeno una decina di volte sotto il primo pulsante, lungo la colonna B.

Conclusioni

E adesso? Cosa ce ne facciamo di questa pagina?

In questa pagina metteremo i comandi degli automatismi che installeremo nei fogli Excel e le istruzioni, scritte per noi, ma anche per chiunque altro abbia bisogno di usarli. Gli automatismi sono macro che eseguono appunto in automatico e velocemente una serie di attività ripetitive che non saremo più costretti a fare. Ma degli automatismi e di come si realizzano non parleremo qui, in questo post, ma in una serie di post dedicati.

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