Dashboard di Excel - Controlli interattivi

Se hai più dati da visualizzare sul dashboard che non si adattano a una singola schermata, puoi optare per l'utilizzo dei controlli di Excel che fanno parte di Excel Visual Basic. I controlli più comunemente usati sono le barre di scorrimento, i pulsanti di opzione e le caselle di controllo. Incorporandoli nella dashboard, è possibile renderlo interattivo e consentire all'utente di visualizzare le diverse sfaccettature dei dati tramite possibili selezioni.

È possibile fornire controlli interattivi come barre di scorrimento, caselle di controllo e pulsanti di opzione nei dashboard per facilitare i destinatari a visualizzare dinamicamente i diversi aspetti dei dati visualizzati come risultati. Puoi decidere un layout particolare della dashboard insieme ai destinatari e utilizzare lo stesso layout da allora in poi. I controlli interattivi di Excel sono semplici da usare e non richiedono alcuna esperienza in Excel.

I controlli interattivi di Excel saranno disponibili nella scheda SVILUPPATORE sulla barra multifunzione.

Se non trovi la scheda SVILUPPATORE sulla barra multifunzione, procedi come segue:

Fare clic su Personalizza barra multifunzione nella casella Opzioni di Excel.
Seleziona Schede principali nella casella Personalizza barra multifunzione.
Seleziona la casella Sviluppatore nell'elenco Schede principali.

Fare clic su OK. Troverai la scheda SVILUPPATORE sulla barra multifunzione.

Barre di scorrimento nei dashboard

Una delle caratteristiche di qualsiasi dashboard è che ogni componente nella dashboard è il più compatto possibile. Supponiamo che i risultati siano i seguenti:

Se puoi presentare questa tabella con una barra di scorrimento come indicato di seguito, sarebbe più facile navigare tra i dati.

Puoi anche avere una linea target dinamica in un grafico a barre con barra di scorrimento. Quando si sposta la barra di scorrimento su e giù, la linea target si sposta su e giù e le barre che attraversano la linea target verranno evidenziate.

Nelle sezioni seguenti imparerai come creare una barra di scorrimento e come creare una linea di destinazione dinamica collegata a una barra di scorrimento. Imparerai anche come visualizzare le etichette dinamiche nelle barre di scorrimento.

Creazione di una barra di scorrimento

Per creare una barra di scorrimento per una tabella, prima copia le intestazioni delle colonne in un'area vuota del foglio come mostrato di seguito.

Inserisci una barra di scorrimento.
- Fare clic sulla scheda SVILUPPATORE sulla barra multifunzione.
- Fare clic su Inserisci nel gruppo Controlli.
- Fare clic sull'icona della barra di scorrimento sotto Controlli modulo nell'elenco a discesa delle icone.

Porta il cursore sulla colonna I e scorri verso il basso per inserire una barra di scorrimento verticale.
Regolare l'altezza e la larghezza della barra di scorrimento e allinearla alla tabella.

Fare clic con il tasto destro sulla barra di scorrimento.
Fare clic su Controllo formato nell'elenco a discesa.

Viene visualizzata la finestra di dialogo Controllo formato.

Fare clic sulla scheda Controllo.
Digita quanto segue nelle caselle visualizzate.

Fare clic sul pulsante OK. La barra di scorrimento è pronta per l'uso. Hai scelto la cella O2 come collegamento della cella per la barra di scorrimento, che assume valori da 0 a 36, quando sposti la barra di scorrimento su e giù. Successivamente, devi creare una copia dei dati nella tabella con un riferimento basato sul valore nella cella O2.
Nella cella K3, digita quanto segue:

= OFFSET (Riepilogo [@ [S. No.]], $ O $ 2,0).

Premi il pulsante Invio. Compila le celle nella colonna copiando la formula.

Compila le celle nelle altre colonne copiando la formula.

La tua tabella dinamica e scorrevole è pronta per essere copiata nella tua dashboard.

Spostare la barra di scorrimento verso il basso.

Come puoi osservare, il valore nel collegamento cella - barra di scorrimento cambia e i dati nella tabella vengono copiati in base a questo valore. Alla volta, vengono visualizzate 12 righe di dati.

Trascina la barra di scorrimento verso il basso.

Le ultime 12 righe di dati vengono visualizzate poiché il valore corrente è 36 (come mostrato nella cella O2) e 36 è il valore massimo impostato nella finestra di dialogo Controllo modulo.

È possibile modificare la posizione relativa della tabella dinamica, modificare il numero di righe da visualizzare alla volta, il collegamento della cella alla barra di scorrimento, ecc. In base alle proprie esigenze. Come hai visto sopra, questi devono essere impostati nella finestra di dialogo Controllo formato.

Creazione di una linea target dinamica e interattiva

Supponi di voler visualizzare le vendite in base alla regione negli ultimi 6 mesi. Hai anche fissato obiettivi per ogni mese.

Puoi fare quanto segue:

Crea un grafico a colonne che mostri tutte queste informazioni.
Crea una linea target attraverso le colonne.
Rendi interattiva la linea target con una barra di scorrimento.
Rendi dinamica la linea target impostando i valori target nei dati.
Evidenzia i valori che soddisfano l'obiettivo.

Crea un grafico a colonne che mostri tutte queste informazioni

Seleziona i dati. Inserisci un grafico a colonne in cluster.

Crea una linea target attraverso le colonne

Cambia il tipo di grafico in combo. Seleziona il tipo di grafico come Linea per la serie di destinazione e Colonna raggruppata per il resto della serie.

Crea una tabella di base per la linea di destinazione. Renderai questa dinamica più tardi.

Modificare i valori della serie di dati per la linea di destinazione nella colonna di destinazione nella tabella sopra.

Fare clic sul pulsante OK.

Modificare la combinazione di colori per la colonna raggruppata. Cambia la linea di destinazione in una linea tratteggiata verde.

Rendi interattiva la linea target con una barra di scorrimento

Inserisci una barra di scorrimento e posizionala sotto il grafico e dimensionala in modo che si estenda da gennaio a giugno.
Immettere i parametri della barra di scorrimento nella finestra di dialogo Controllo formato.

Crea una tabella con due colonne: Mese e Obiettivo.
Immettere i valori in base alla tabella dati e al collegamento della cella della barra di scorrimento.

Questa tabella mostra il mese e il target corrispondente in base alla posizione della barra di scorrimento.

Rendi dinamica la linea target impostando i valori target nei dati

Ora sei pronto per rendere dinamica la tua Target Line.

Modificare i valori della colonna di destinazione nella tabella di base creata per la linea di destinazione digitando = $ G $ 12 in tutte le righe.

Come saprai, la cella G12 mostra dinamicamente il valore Target.

Come puoi osservare, la Target Line si sposta in base alla barra di scorrimento.

Evidenzia i valori che soddisfano l'obiettivo

Questo è il passo finale. Si desidera evidenziare i valori che soddisfano l'obiettivo in qualsiasi momento.

Aggiungi colonne al lato destro della tabella dei dati: risultati est, risultati nord, risultati sud e risultati ovest.
Nella cella H3, inserisci la seguente formula:

= SE (D3> = $ G $ 12, D3, NA ())

Copia la formula nelle altre celle della tabella. Ridimensiona la tabella.

Come si può osservare, i valori nelle colonne - Risultati Est, Risultati Nord, Risultati Sud e Risultati Ovest cambiano dinamicamente in base alla barra di scorrimento (cioè Valore target). Vengono visualizzati i valori maggiori o uguali al Target e gli altri valori sono solo # N / D.

Modificare l'intervallo di dati del grafico per includere le colonne appena aggiunte nella tabella dei dati.
Fare clic su Cambia tipo di grafico.
Rendi la serie Target Linea e il resto Colonna raggruppata.
Per le serie di dati appena aggiunte, selezionare Asse secondario.
Formattare le serie di dati in modo tale che le serie Est, Nord, Sud e Ovest abbiano un colore di riempimento arancione e le serie Est-Risultati, Nord-Risultati, Sud-Risultati e WestResults abbiano un colore di riempimento verde.
Immettere un'etichetta dati per la linea di destinazione e renderla dinamica con il riferimento di cella al valore del mese nella tabella dei dati dinamici.

Il grafico con la linea target dinamica è pronto per essere incluso nella dashboard.

È possibile cancellare l'asse secondario poiché non è necessario. Mentre muovi la barra di scorrimento, la linea target si sposta e le barre vengono evidenziate di conseguenza. Target Line avrà anche un'etichetta che mostra il mese.

Pulsanti di opzione (radio) di Excel

I pulsanti di opzione vengono normalmente utilizzati per selezionare un'opzione da un determinato insieme di opzioni. È sempre rappresentato da un piccolo cerchio, che avrà un punto in esso quando selezionato. Quando si dispone di una serie di pulsanti di opzione, è possibile selezionarne solo uno.

In Excel, i pulsanti di opzione sono indicati come pulsanti di opzione.

È possibile utilizzare i pulsanti di opzione di Excel nei grafici per scegliere le specifiche dei dati che il lettore desidera esaminare. Ad esempio, nell'esempio nella sezione precedente hai creato una barra di scorrimento per ottenere una linea target dinamica con valori target basati sul mese. È possibile utilizzare i pulsanti di opzione per selezionare un mese e quindi il valore target e basare la linea target sul valore target. Di seguito saranno riportati i passaggi:

Crea un grafico a colonne che mostri tutte queste informazioni.
Crea una linea target attraverso le colonne.
Rendi interattiva la linea di destinazione con i pulsanti di opzione.
Rendi dinamica la linea target impostando i valori target nei dati.
Evidenzia i valori che soddisfano l'obiettivo.

I passaggi 1 e 2 sono gli stessi del caso precedente. Alla fine del secondo passaggio, avrai la seguente tabella.

Rendi interattiva la linea di destinazione con i pulsanti di opzione

Inserisci un pulsante di opzione.
- Fare clic sulla scheda SVILUPPATORE sulla barra multifunzione.
- Fare clic su Inserisci nel gruppo Controlli.
- Fare clic sull'icona del pulsante di opzione sotto Controlli modulo nell'elenco a discesa delle icone.

Posizionalo nell'angolo in alto a destra del grafico.

Fare clic con il tasto destro sul pulsante Opzione. Fare clic sull'opzione Controllo formato nell'elenco a discesa.

Immettere i parametri del pulsante di opzione nella finestra di dialogo Formato oggetto, nella scheda Controllo.

La cella F10 è collegata al pulsante di opzione. Crea 5 copie del pulsante di opzione verticalmente.

Come puoi osservare, tutti i pulsanti di opzione hanno lo stesso nome, indicato come nomi di didascalia. Tuttavia, internamente Excel avrà nomi diversi per questi pulsanti di opzione, che puoi visualizzare nella casella Nome. Inoltre, poiché il pulsante di opzione 1 è stato impostato per collegarsi alla cella F10, anche tutte le copie fanno riferimento alla stessa cella.

Fare clic su uno dei pulsanti di opzione.

Come puoi osservare, il numero nella cella collegata cambia nel numero di serie del pulsante di opzione. Rinomina i pulsanti di opzione in gennaio, febbraio, marzo, aprile, maggio e giugno.

Crea una tabella con due colonne: Mese e Obiettivo. Immettere i valori in base alla tabella dati e al collegamento della cella della barra di scorrimento.

Questa tabella mostra il mese e il target corrispondente in base al pulsante di opzione selezionato.

Rendi dinamica la linea target impostando i valori target nei dati

Ora sei pronto per rendere dinamica la tua Target Line.

Modificare i valori della colonna di destinazione nella tabella di base creata per la linea di destinazione digitando = $ G $ 12 in tutte le righe.

Come saprai, la cella G12 mostra dinamicamente il valore Target.

Come puoi osservare, la linea target viene visualizzata in base al pulsante di opzione selezionato.

Evidenzia i valori che soddisfano l'obiettivo

Questo è il passo finale. Si desidera evidenziare i valori che soddisfano l'obiettivo in qualsiasi momento.

Aggiungi colonne al lato destro della tabella dei dati: risultati est, risultati nord, risultati sud e risultati ovest.
Nella cella H3, inserisci la seguente formula:

= SE (D3> = $ G $ 12, D3, NA ())

Copia la formula nelle altre celle della tabella. Ridimensiona la tabella.

Modificare l'intervallo di dati del grafico per includere le colonne appena aggiunte nella tabella dei dati.
Fare clic su Cambia tipo di grafico.
Rendi la serie Target Linea e il resto Colonna raggruppata.
Per le serie di dati appena aggiunte, selezionare Asse secondario.
Formattare le serie di dati in modo tale che le serie Est, Nord, Sud e Ovest abbiano un colore di riempimento arancione e le serie Est-Risultati, Nord-Risultati, Sud-Risultati e WestResults abbiano un colore di riempimento verde.

Aggiungi un'etichetta dati dinamica alla linea di destinazione con il valore dalla cella $ G $ 12.
Cancella l'asse secondario poiché non è necessario.
Nella scheda VISTA sulla barra multifunzione, deseleziona la casella Linee della griglia.
Modificare l'opzione Etichetta in Alto nelle opzioni Formato asse. Questo sposta le etichette dell'asse verticale a destra, rendendo ben visibile l'etichetta dei dati della linea di destinazione.

Il tuo grafico con la linea di destinazione dinamica e i pulsanti di opzione è pronto per essere incluso nella dashboard.

Quando si seleziona un pulsante di opzione, la linea target viene visualizzata secondo il valore target del mese selezionato e le barre verranno evidenziate di conseguenza. Target Line avrà anche un'etichetta dati che mostra il valore target.

Caselle di controllo di Excel

Le caselle di controllo vengono normalmente utilizzate per selezionare una o più opzioni da un determinato insieme di opzioni. Le caselle di controllo sono sempre rappresentate da quadratini, che avranno un segno di spunta quando selezionati. Quando si dispone di una serie di caselle di controllo, è possibile selezionarne un numero qualsiasi. Per esempio,

È possibile utilizzare le caselle di controllo di Excel nei grafici per scegliere le specifiche dei dati che il lettore desidera esaminare. Ad esempio, nell'esempio nella sezione precedente, hai creato un grafico a colonne che visualizza i dati di 4 regioni: est, nord, sud e ovest. È possibile utilizzare le caselle di controllo per selezionare le regioni per le quali vengono visualizzati i dati. È possibile selezionare un numero qualsiasi di regioni alla volta.

Puoi iniziare con l'ultimo passaggio della sezione precedente:

Inserisci una casella di controllo.
- Fare clic sulla scheda SVILUPPATORE sulla barra multifunzione.
- Fare clic su Inserisci nel gruppo Controlli.
- Fare clic sull'icona Casella di controllo sotto Controlli modulo nell'elenco a discesa delle icone.

Posizionalo nell'angolo in alto a sinistra del grafico.
Modificare il nome della casella di controllo in Est.

Fare clic con il tasto destro sulla casella di controllo. Fare clic su Controllo formato nell'elenco a discesa.
Immettere i parametri della casella di controllo nella finestra di dialogo Formato controllo, nella scheda Controllo.

Fare clic sul pulsante OK. È possibile osservare che nella cella collegata C19, verrà visualizzato TRUE se si seleziona la casella di controllo e FALSE verrà visualizzato se si deseleziona la casella di controllo.
Copia la casella di controllo e incolla 3 volte in orizzontale.
Cambia i nomi in Nord, Sud e Ovest.

Come puoi osservare, quando copi una casella di controllo, la cella collegata rimane la stessa anche per la casella di controllo copiata. Tuttavia, poiché le caselle di controllo possono avere più selezioni, è necessario rendere diverse le celle collegate.