Il Calcolo Tabellare, Vol. I: nozioni fondamentali

Il Calcolo Tabellare, Vol. I: nozioni fondamentali
Microsoft®
Excel
Il Calcolo Tabellare
Vol. I , nozioni fondamentali
> La gestione delle tabelle e l’automazione dei processi di calcolo
> Le database queries, l’analisi dei dati e la presentazione dei risultati
> progettazione di dashboard e report
> 28 video scaricabili per un totale di 6 ore di sessioni
> 26 Excel worksheet di supporto ai video
Livello: Da Intermedio ad Avanzato
Massimo Zucchini
iii
Il Calcolo Tabellare, Vol. I: nozioni fondamentali
TITOLO DELL’OPERA Microsoft® Excel: Il Calcolo Tabellare Vol. I, nozioni fondamentali
Copyright© 2016, Massimo Zucchini
Tutti i diritti sono riservati. Nessuna parte di questo libro o i video inclusi può essere riprodotta o
trasmessa in nessuna forma senza il permesso scritto dell’Autore. Il lettore può utilizzare, modificare o
riprodurre i worksheet scaricabili secondo le proprie esigenze.
Ogni cura è stata posta nella raccolta e nella verifica della documentazione e delle informazioni contenute
in questo libro.
Tuttavia l’Autore non può assumersi alcuna responsabilità derivante dall’utilizzo della stessa. Lo stesso
dicasi per ogni persona o società coinvolta nella creazione, nella produzione e nella distribuzione di
questo libro.
Excel, Windows, Youtube, Google sono marchi registrati negli Stati Uniti o negli altri Paesi.
Altri nomi di prodotti o di eventuali aziende citati in queste pagine possono essere marchi dei rispettivi
proprietari. Le aziende, le organizzazioni, i loghi, i prodotti, i nomi di dominio, gli indirizzi di posta
elettronica, i nomi delle persone e le eventuali associazioni ai dati dei database citati negli esempi sono
del tutto fittizi e non è presente alcun riferimento reale a persone o a cose, eventi reali o persone fisiche.
ISBN: 9788898907229
Prima Edizione: Giugno 2016
Edizioni:
1
2
2016
iv
3
4
6
7
8
9
10
Il Calcolo Tabellare, Vol. I: nozioni fondamentali
INDICE
PREFAZIONE.........................................................................................................................................VII
A CHI È RIVOLTO QUESTO LIBRO .........................................................................................................VIII
GLI OBIETTIVI ........................................................................................................................................ IX
COME È ORGANIZZATO QUESTO LIBRO ................................................................................................. X
INTRODUZIONE ..................................................................................................................................... XI
1
2
LE TABELLE E I DATABASE EXCEL .................................................................................................... 1
1.1
TABELLE IN FORMATO HTABLE............................................................................................................ 1
1.2
TABELLE IN FORMATO VTABLE ............................................................................................................ 2
1.3
I CONTROLLI DI COMPLETEZZA E CONFORMITÀ ........................................................................................ 3
AUTOFILTERING (O FILTRI AUTOMATICI) ....................................................................................... 6
2.1
LA FUNZIONE SUBTOTALE() E L’AUTOFILTERING .................................................................................. 7
3
TABELLE COME DATI STRUTTURATI ............................................................................................... 8
4
NAVIGARE IN UN DATABASE COMPLESSO ..................................................................................... 9
5
TABELLE DINAMICHE ................................................................................................................... 10
5.1
ORDINAMENTO DINAMICO .............................................................................................................. 10
5.2
GESTIONE DINAMICA DELLA DIMENSIONE ............................................................................................ 12
5.3
LE FORMULE 3D ............................................................................................................................ 13
6
DUPLICATI E OCCORRENZE MULTIPLE .......................................................................................... 14
7
GRAFICI DINAMICI ....................................................................................................................... 17
8
LA FORMATTAZIONE COND. AVANZATA ...................................................................................... 19
9
TABELLE A SCORRIMENTO ........................................................................................................... 20
10
IL CALCOLO TABELLARE ............................................................................................................... 21
10.1
LA NOTAZIONE MATRICIALE .............................................................................................................. 21
10.2
LA FUNZIONE MATR.SOMMA.PRODOTTO() .................................................................................. 22
Es. 10.1: utilizzo di MATR.SOMMA.PRODOTTO() ............................................................................... 23
Es. 10.2 : MATR.SOMMA.PRODOTTO e gli operatori matriciali ........................................................ 24
Es. 10.3: Tabelle dinamiche e MATR.SOMMA.PRODOTTO() .............................................................. 25
11
LA COLONNA DI “HELPER” ........................................................................................................... 26
12
LE RICERCHE BIDIMENSIONALI..................................................................................................... 27
12.1
CERCA.VERT() E CONFRONTA() .................................................................................................. 28
12.2
INDICE() E CONFRONTA() ........................................................................................................... 29
v
Il Calcolo Tabellare, Vol. I: nozioni fondamentali
12.3
. LA FUNZIONE INDIRETTO() .......................................................................................................... 30
13
LE MATRICI TRASPOSTE ............................................................................................................... 31
14
ESEMPI RIEPILOGATIVI ................................................................................................................ 32
14.1
LA RIFORMATTAZIONE DI UN DATABASE .............................................................................................. 32
14.2
LA DASHBOARD ............................................................................................................................. 33
Gli obiettivi ......................................................................................................................................... 34
Verifica del database: integrity, completezza, aggiornamenti .......................................................... 35
Definizione del layout della dashboard .............................................................................................. 36
Intestazioni dinamiche, immagini fotografiche ................................................................................. 38
Pulsanti di opzione, evidenziare le “barre dei dati” ........................................................................... 39
Verifica e congruenza con gli obiettivi ............................................................................................... 40
14.3
GENERAZIONE DI UN REPORT ............................................................................................................ 42
CONCLUSIONI ...................................................................................................................................... 44
vi
Il Calcolo Tabellare, Vol. I: nozioni fondamentali
Prefazione
Microsoft Excel è sicuramente uno degli strumenti Software che ha avuto più
successo e più larga diffusione negli ultimi anni. La sua versatilità in molteplici
applicazioni è fuori discussione. Nonostante l’introduzione di diversi sistemi gestionali
in numerose organizzazioni, Excel continua a essere utilizzato nella Data Analysis, nel
Business Intelligence, nel Program/Project Management, nella Finance,
nell’Amministrazione, nella Vendita, solo per citare alcuni ambiti applicativi aziendali.
L’utilizzazione di Excel a livello professionale “fa curriculum”: basta scorrere le varie
proposte lavorative in diversi settori per rendersi conto che questo skill è sempre più
richiesto.
Purtroppo però non tutti sfruttano al meglio le potenzialità di questo strumento,
utilizzandolo prevalentemente come semplice foglio di calcolo.
Se state leggendo questa prefazione significa che già usate Excel come strumento di
lavoro o che comunque intendete utilizzarlo. Ebbene, esaminate le vostre capacità sul
campo e poi chiedetevi a quale livello di apprendimento ritenete di essere.
Se poi intendete fare un salto di qualità, migliorando le vostre performance di utilizzo
dello strumento, per intraprendere questo primo passo dello studio del calcolo tabellare
migliorando l’efficienza del vostro lavoro e magari spingervi verso la Data Analysis,
ebbene questo è il libro che fa per voi.
Alla fine di questo percorso vi garantisco che, per quanto riguarda l’argomento in
questione, sarete in grado di organizzare al meglio il vostro lavoro, di eseguire
database queries anche complesse, di applicare correttamente le formule necessarie e
di avere posto le basi per Il calcolo matriciale.
Il calcolo matriciale sarà oggetto di un secondo libro, nel quale troverete molteplici casi
ed esercizi di analisi dei dati tratti dall’uso quotidiano dello strumento.
La versione di Excel utilizzata è la 2007. Questo per far si che anche le persone che
utilizzano versioni successive possano lavorare con il materiale fornito senza problemi.
Anche se potrebbe cambiare l’interfaccia utente, i concetti sono sempre validi e
indipendenti dalle versioni del SW utilizzato.
Per qualsiasi problema, suggerimento, commento non esitate a contattarmi su
[email protected]
Come per tutti gli approfondimenti, vi si richiede solo un minimo di dedizione …
vii
Il Calcolo Tabellare, Vol. I: nozioni fondamentali
A chi è rivolto questo libro
Questo libro è rivolto principalmente a tutte quelle persone che utilizzano Excel
come strumento di lavoro, che possiedono un’esperienza di utilizzo intermedia dello
strumento e quindi sono in grado di applicare i più importanti concetti base.
In questo libro non si troverà una spiegazione dettagliata di come lavorano le funzioni
predefinite. Per questo esiste già una vastissima letteratura.
Tanto è stato scritto su Excel e sul suo utilizzo, tuttavia questa trattazione come
impostazione è unica nel suo genere. Alla fine sarete in grado di utilizzare
efficientemente e professionalmente parte degli strumenti di Excel in un contesto che di
per sé occupa un buon 80-90% dell’utilizzo dell’intero applicativo: il calcolo tabellare.
Il calcolo tabellare è composto di due elementi fondamentali: una parte di livello
intermedio-avanzato (che sarà trattata in questo libro), che utilizza algoritmi basati
sull’applicazione standard delle funzioni Excel e una a livello più avanzato, che implica
l’utilizzo del calcolo matriciale e che sarà trattata in un secondo volume.
Questo libro fa parte dunque di un percorso formativo. E’ il primo passo dove si
apprende tutto quanto è necessario per utilizzare poi il calcolo matriciale,
l’elaborazione testuale, la programmazione VBA e infine l’automazione completa dei
tool Excel.
viii
Il Calcolo Tabellare, Vol. I: nozioni fondamentali
Gli obiettivi
Alla fine di questo libro dovreste:

aver compreso le differenze di rappresentazione delle tabelle in Excel, essere in
grado di ordinare una tabella dinamicamente secondo vari criteri e renderla
indipendente dalle dimensioni, definire grafici dinamici egli indici a scorrimento;

aver compreso come strutturare un database e verificarne la conformità,
navigarlo agevolmente, evidenziare informazioni con i filtri automatici o con la
formattazione condizionale avanzata, utilizzare le formule 3D, importare
database “testuali”, aver compreso il problema delle occorrenze multiple e
come utilizzare la colonna di helper per risolverlo;

aver compreso le basi della notazione matriciale, essere in grado di utilizzare
semplici funzioni matriciali ed eventualmente applicarle per riformattare un
database;

aver compreso cosa è una database query, come utilizzare al meglio le funzioni
di ricerca bidimensionale e come nidificarle;

avere quindi una solida base per impostare e comprendere gli elementi di
calcolo matriciale;

essere in grado di presentare i dati e sviluppare dashboard semplici ma
professionali utilizzando tutti gli strumenti presentati. Sotto è riportato un
esempio di dashboard presentato nel Cap. 14.
ix
Il Calcolo Tabellare, Vol. I: nozioni fondamentali
Come è organizzato questo libro
Il libro è nato come approfondimento di argomenti trattati nelle pagine di
www.excelsynthesis.com (come ottenere la Excellence in Excel con il metodo dell’8020) e si avvale quindi di alcuni link a questo sito.
Il libro è suddiviso in tre parti.

Prima parte: Introduzione al calcolo tabellare, differenze tra VTable e HTable,
definizione dei database Excel, lavorare con l’autofiltering, navigare in database
complessi, le tabelle e i grafici dinamici, automatizzare l’ordinamento e la
dimensione di un report tabellare, le tabelle a scorrimento, la formattazione
condizionale avanzata, le tabelle come “dati strutturati”, il problema delle
occorrenze multiple, le ricerche bidimensionali e le formule 3D.

La seconda parte: Il calcolo tabellare, le database queries, utilizzo avanzato
delle formule di ricerca tabellare, la colonna di helper per risolvere le
occorrenze multiple e alcuni esempi di utilizzo avanzati.

La terza parte: un esempio di riformattazione di un database a struttura
orizzontale e una semplice dashboard per l’analisi di un database clienti.
Pur nella consapevolezza che il modo più proficuo per apprendere quanto sopra siano
delle lezioni con un tutor, ritengo che questo libro possa sostituire un corso, in virtù dei
contenuti multimediali qui utilizzati, rimandando ai video la maggior parte dei concetti la
cui trattazione, altrimenti, risulterebbe difficoltosa. Troverete dei link attraverso i quali
potrete scaricare e non solo vedere ben 28 video, per un totale di circa 6 ore di
spiegazioni e 26 worksheet Excel di riferimento.
Ogni capitolo contiene una nota descrittiva degli argomenti trattati, la cui applicazione
pratica è demandata ai video supportati da un worksheet di lavoro.
Seguite il video con davanti il worksheet e poi potrete riprovare a rifare il lavoro da soli,
questo è il metodo di lavoro per imparare velocemente e in maniera efficace.
Alla fine di ogni paragrafo troverete i rispettivi link con indicato a lato l’indirizzo in forma
estesa.
I video sono volutamente brevi ma esaustivi, si scaricano in formato .zip, la durata
massima va dai 15 ai 20 min, e in un attimo con una connessione di media velocità
riuscirete ad avere il video da seguire e il relativo foglio di lavoro.
x
Il Calcolo Tabellare, Vol. I: nozioni fondamentali
Introduzione
“Dovete imparare le regole del gioco.
E poi giocare meglio di chiunque altro”
Albert Einstein
Basta entrare in Excel, vedere come sono organizzati i fogli di lavoro e come sono
indicizzate le celle elementari per rendersi conto che la tabella (definita come un
insieme di dati strutturati in righe e colonne) è il fondamento sui cui si basa questo
strumento.
Per padroneggiarne al meglio tutte le potenzialità è necessario capire come strutturare
i dati di input, al fine di garantire un’elaborazione efficace ed efficiente. Elaborare che
sostanzialmente significa essere in grado di “interrogare” (eseguire delle “query”) tali
tabelle e ricavarne delle sottotabelle o dei report i più possibili comunicativi.
Quando le tabelle raggiungono una dimensione ragguardevole (migliaia di righe e
decine di colonne) vengono denominate database.
Il calcolo Tabellare potrebbe essere definito come la capacità di generare
sottotabelle, o report, che corrispondono a definiti criteri e utilizzando un certo formato
predefinito.
Il calcolo tabellare è la base per la creazione di dashboard, è il fondamento della Data
Analysis, del Problem Solving e del Business Intelligence, o più semplicemente
consente di affrontare i nostri compiti quotidiani utilizzando Excel in maniera più
professionale e proficua.
Qualsiasi report o dashboard complessa sono caratterizzati al minimo da tre elementi
fondamentali:

un database;

un foglio di calcolo;

un foglio di report/dashboard.
xi
Il Calcolo Tabellare, Vol. I: nozioni fondamentali
Nel foglio di calcolo viene implementato l’algoritmo che elaborando le informazioni
contenute nel database, secondo un certo criterio, fornisce alcuni risultati che poi sono
formattati e presentati nel relativo report.
Questa è l’organizzazione più efficiente. Nel foglio di calcolo si trovano generalmente
tabelle o vari riferimenti utili per il successivo report o dashboard, ed è proprio qui che
saranno applicati i concetti del calcolo tabellare.
Iniziamo, prima di tutto nel definire le tabelle e i database Excel, oggetto del primo
capitolo.
xii