I grafici dinamici che usano OFFSET di Excel e i nomi degli intervalli si ridimensionano quando i dati vengono aggiunti o cancellati. A differenza dell’uso delle tabelle per i grafici dinamici, questo metodo è compatibile all’indietro con le prime versioni di Excel.
Utilizza le funzioni OFFSET e COUNTA di Excel per creare intervalli dinamici che si adattano automaticamente quando i dati vengono aggiunti o cancellati.
Clicca per scaricare il file di esempio del tutorial sul ridimensionamento dinamico dei grafici usando OFFSET e i nomi di intervallo. L’esempio include sia dati orizzontali che verticali.
Il metodo OFFSET e il nome dell’intervallo richiedono più lavoro rispetto ai due clic usati per una tabella, ma ti dà il controllo su tutto ciò che riguarda i dati del grafico come mostra il mio libro “Balanced Scorecards and Operational Dashboards with Microsoft Excel”. Questo sito, CriticalToSuccess.com, ha esempi di altri modi di usare OFFSET, per esempio, creando un grafico a scorrimento, controllando i grafici con menu a discesa, e altro. Cerca CriticalToSuccess.com usando il termine “OFFSET” o “intervallo dinamico” per vedere esempi.
Creazione di un grafico statico
Inizia il tuo grafico dinamico creando prima un grafico statico come segue,
1. Fare clic all’interno dell’intervallo B7:D12 in modo che Excel sappia dove si trovano i dati del grafico.
2. Nella scheda Inserisci, nel gruppo Grafici, selezionare un grafico semplice come un grafico a colonne, a barre o a linee.
Spostare questo grafico in modo da avere spazio per aggiungere dati sotto l’intervallo di dati.
Creazione di nomi di intervallo dinamici con OFFSET
Diverse versioni di Excel hanno metodi diversi per creare e modificare formule di nomi di intervallo. Le seguenti istruzioni sono per Excel 2007, 2010 e 2013.
1. Nella scheda Formule, nel gruppo Nome definito, fare clic su Definisci nome. Appare la casella Definisci nome.
Creare nomi di intervallo nella casella Definisci nome.
2. Nella casella di testo Nome, inserire rngVDate. La convenzione di denominazione è,
rng Nome intervallo
V Orientamento verticale dei dati in B7:B16
Date Il tipo di dati nell’intervallo
3. Scrivi un commento nella casella di testo del commento che aiuterà te o altri a sapere a cosa serve questo nome di intervallo.
4. Nella casella Riferimenti a, scrivi la formula OFFSET che calcola l’intervallo di dati Data.
=OFFSET(wrksht!$B$7,0,0,COUNTA(wrksht!$B$7:$B$16),1)
5. Cliccate Ok.
Con questa formula OFFSET potete inserire ulteriori date in qualsiasi punto dell’intervallo B7:B16 a patto che le nuove date siano in fondo alla vecchia lista di date senza celle vuote tra le date.
Ora, ripetete lo stesso processo per creare i nomi degli intervalli per i dati delle vendite e delle unità.
I nomi e le formule che aggiungerete sono,
Nome intervallo dati si riferisce a
Vendite rngVSales =OFFSET(wrksht!$C$7,0,0,COUNTA(wrksht!$C$7:$C$16),1)
Unità rngVUnits =OFFSET(wrksht!$D$7,0,0,0,COUNTA(wrksht!$D$7:$D$16),1)
Clicca qui per imparare come OFFSET calcola la dimensione del nome della gamma dinamica. Questo articolo vi dà anche dei suggerimenti su come controllare i nomi degli intervalli dinamici.
Clicca qui per imparare l’errore che non vorresti mai fare con OFFSET. Fai questo errore?
Clicca per scaricare il file di esempio del tutorial sul ridimensionamento dinamico dei grafici usando OFFSET e i nomi dei range. L’esempio include sia dati orizzontali che verticali.
Inserire un nome di intervallo nella formula della data del grafico
Ora puoi rendere dinamico il tuo grafico inserendo i nomi di intervallo che hai creato nelle formule che i grafici usano come riferimento ai dati. Non preoccupatevi. Questo è molto semplice. Infatti, imparare a fare questo ti dà abilità mistiche e il potere di piegare qualsiasi grafico alla tua volontà. (Vi permette anche di correggere i grafici che si incasinano.)
Sopra, avete creato tre nomi di intervallo dinamico che fanno riferimento ai dati da usare per il grafico,
rngVDate
rngVSales
rngVUnits
Il seguente sostituisce i riferimenti di cella usati dal grafico con i nomi di intervallo dinamico che avete creato,
1. Seleziona il grafico cliccando sul suo bordo.
2. Nella scheda Chart Tools Design, nel gruppo Data, clicca sullo strumento Select Data per visualizzare la finestra di dialogo Select Data Source.
La finestra di dialogo Select Data Source ti dà un controllo magico sui dati usati da un grafico.
3. Sotto le etichette dell’asse orizzontale (categoria), clicca Edit per visualizzare la finestra Axis Labels. È difficile vedere la formula in questa finestra di dialogo, quindi fai clic sull’icona del selettore a destra del riferimento alla cella. Questo visualizza un riquadro più ampio di Axis Labels in cui è possibile fare riferimento alla formula. Fate attenzione prima di modificare!
La casella Etichette asse mostra il riferimento per l’asse orizzontale.
4. Fate attenzione! Prima di provare a modificare la formula, cliccate all’interno della formula e premete il tasto Modifica, F2, in modo da poter modificare la formula. Premete il tasto Fine per andare alla fine della formula. Sostituite i riferimenti alle celle dopo il segno ! con il nome dell’intervallo come mostrato in questa immagine.
La vostra formula dovrebbe ora apparire come,
=’Dynamically Resize Charts to Data.xlsx’!rngVDate
La casella Axis Labels appare così dopo aver premuto F2 e modificato la formula per includere il nome dell’intervallo di date.
TIP: Se fate un errore quando modificate la formula, premete Ctrl+Z (Undo), per tornare indietro.
5. Fate clic su Ok per tornare alla finestra di dialogo Select Data Source in modo da poter aggiungere i nomi delle vendite e delle unità.
Inserimento di un nome di intervallo nella formula delle vendite e delle unità del grafico
Ora, sostituite le formule degli assi delle vendite e delle unità con nomi di intervallo usando quasi lo stesso processo.
1. Nella finestra di dialogo Seleziona origine dati, fate clic su Vendite e sul pulsante Modifica per visualizzare la finestra di dialogo Modifica serie.
Sostituite il riferimento alla cella Salesdata con il suo nome di intervallo.
2. Fate clic sull’icona di selezione a destra della formula Valore serie che fa riferimento alla serie Vendite.
3. Cliccate all’interno della formula, premete F2 per modificare, e cambiate i riferimenti alle celle per i dati Sales con il nome dell’intervallo, rngVSales.
Il grafico ora usa il nome dell’intervallo dinamico per fare riferimento ai dati Sales.