Excel grafici dinamici

Questa newsletter ha un inizio “shock”. Qualcuno capisce al volo qualcosa vedendo i grafici qua sotto?



Questo è il risultato che si ottiene quando si intende mostrare in un grafico troppi dati.
In tal modo si perde l’immediatezza ed il grafico risulta tutt’altro che chiaro.
È vero che nelle ultime versioni di Excel è stato introdotta la possibilità di applicare un filtro direttamente al grafico, ma ciò non risolverebbe comunque il nostro problema che è: come mostrare soltanto una categoria di dati per volta.
Nel nostro tempo le categorie sono le sedi cui si riferisce il file e delle quali sono mostrati ipotetici dati di vendita nell’arco dei 12 mesi.
Le sedi sono 20 e di qui si comprende il perché il grafico sia sovraffollato (l’immagine sotto mostra solo le prime righe della tabella, per non sottrarre troppo spazio al testo della newsletter).



Per rendere più efficace il grafico, si è stabilito di mostrare solo una sede per volta e la scelta deve essere fatta in modo rapido.
Per fare ciò non servono funzionalità complesse o il ricorso al linguaggio di programmazione Vba, ma una funzione ed una funzionalità abbastanza semplici e note agli utenti che conoscono bene Excel ed anche una piccola funzione che impreziosisce ulteriormente il lavoro.
Il prospetto per il grafico è predisposto in un altro foglio.Nell’intervallo celle B1:M1 sono replicati i 12 mesi dell’anno.
Il primo intervento va fatto nella cella A2 ed il secondo nell’intervallo celle B2:M2. Il risultato finale della tabella che è alla base del grafico sarà il seguente:



Nella cella A2 è sufficiente usare il comando CONVALIDA DATI di tipo ELENCO per costruire un menu a tendina basato sui nomi delle 20 sedi.
In questo frangente adottiamo un menu a tendina statico (ovvero basato sulle celle che contengono i nomi delle 20 sedi esistenti) ma si potrebbe anche rendere dinamico usando la formattazione come tabella ed il nome cella (funzionalità che non è scopo di questa newsletter descrivere).



Dopo aver scelto ELENCO come criterio nella sezione CONSENTI, ci si sposta nella sezione ORIGINE e si vanno a selezionare nel foglio in cui si trovano (in questo caso il Foglio1) le celle contenenti le voci che sono alla base del menu a tendina (in questo caso l’intervallo celle A2:A21).
Dopo aver confermato con OK, nella cella appare il menu a tendina da cui scegliere le voci da immettere.
Nell’intervallo celle B2:M2 va immessa una delle funzioni più note agli utenti: la funzione CERCA.VERT, che deve cercare il prodotto scelto nell’elenco della cella A2 e restituire le vendite da Gennaio in poi.
Partendo dalla cella B2 la funzione che è digitata è la seguente:

=CERCA.VERT($A$2;Foglio1!$A$2:$M$21;2;0)

- Il valore da cercare è il prodotto presente nella cella A2. La cella è bloccata in funzione del trascinamento della funzione in orizzontale per gli altri mesi dell’anno.

- La matrice tabella o tabella di ricerca è definita dall’intervallo cella A2:M21 del foglio denominato Foglio1. Anche qui l’intero intervallo è bloccato per il trascinamento della funzione.

- Il numero 2 individua la colonna della tabella di ricerca in cui si trova il valore da restituire con il CERCA.VERT (la colonna B partendo da A ovvero il valore di Gennaio ovvero la colonna 2).

- Lo 0 (va bene anche FALSO) rappresenta il tipo di ricerca da fare, ovvero la ricerca con corrispondenza esatta.

Tutto va bene ed i giochi sembrerebbero fatti, ma quando si trascina la funzione a destra il risultato è sempre lo stesso.
Questo accade perché nella funzione il numero di colonna che contiene ciò che deve essere riportato è stato digitato a mano (colonna 2) e rimane sempre tale nel trascinamento della funzione.
Il segreto per rendere dinamica la numerazione è immettere una funzione al posto del valore 2 digitato a mano.
Ma qual è questa funzione? Molto semplice: è la funzione COLONNE che conta quante colonne ci sono tra una cella di partenza ed una di destinazione.
Per fare un esempio, la funzione =COLONNE(A1:B1) restituisce il numero di colonne che ci sono tra A e B ovvero 2.
Se si trascina, la funzione diventa =COLONNE(B1:C1) ma il risultato è ancora due (il numero di colonne tra B e C).
Il trucco è bloccare con riferimento assoluto il punto di partenza. In tal modo la funzione iniziale è =COLONNE($A$1:B1) ed il risultato è 2 (da A a B).
Trascinando la funzione a destra diventa =COLONNE($A$1:C1) ed il risultato questa volta è 3 (da A a C). Ecco risolto il problema.
Pertanto, al posto del numero 2 scritto a mano, metteremo (o, per essere più corretti) nidificheremo la funzione COLONNE dentro al CERCA.VERT. La funzione risultante sarà la seguente:

=CERCA.VERT($A$2;Foglio1!$A$2:$M$21; COLONNE($A$1:B1);0)

Ingegnoso, vero? Basta una funzione semplice come COLONNE ed il problema è stato risolto.
Adesso si può selezionare l’intervallo ed inserire il grafico, Una piccola puntualizzazione: se la funzione dovesse essere trascinata in verticale anziché in orizzontale, sarebbe sufficiente usare la funzione RIGHE in luogo di COLONNE.