Tableaux de bord Excel - Contrôles interactifs
Si vous avez plus de données à afficher sur le tableau de bord qui ne rentrent pas dans un seul écran, vous pouvez opter pour l'utilisation de contrôles Excel qui font partie d'Excel Visual Basic. Les contrôles les plus couramment utilisés sont les barres de défilement, les boutons radio et les cases à cocher. En les incorporant dans le tableau de bord, vous pouvez le rendre interactif et permettre à l'utilisateur de visualiser les différentes facettes des données par des sélections possibles.
Vous pouvez fournir des commandes interactives telles que des barres de défilement, des cases à cocher et des boutons radio dans vos tableaux de bord pour permettre aux destinataires de visualiser dynamiquement les différentes facettes des données affichées en tant que résultats. Vous pouvez décider d'une mise en page particulière du tableau de bord avec les destinataires et utiliser la même mise en page ensuite. Les contrôles interactifs Excel sont simples à utiliser et ne nécessitent aucune expertise d'Excel.
Les contrôles interactifs Excel seront disponibles dans l'onglet DÉVELOPPEUR du ruban.
Si vous ne trouvez pas l'onglet DÉVELOPPEUR sur le ruban, procédez comme suit -
- Cliquez sur Personnaliser le ruban dans la zone Options Excel.
- Sélectionnez Onglets principaux dans la zone Personnaliser le ruban.
- Cochez la case Développeur dans la liste des onglets principaux.
- Cliquez sur OK. Vous trouverez l'onglet DÉVELOPPEUR sur le ruban.
Barres de défilement dans les tableaux de bord
L'une des caractéristiques de tout tableau de bord est que chaque composant du tableau de bord est aussi compact que possible. Supposons que vos résultats se présentent comme suit -
Si vous pouvez présenter ce tableau avec une barre de défilement comme indiqué ci-dessous, il serait plus facile de parcourir les données.
Vous pouvez également avoir une ligne cible dynamique dans un graphique à barres avec barre de défilement. Lorsque vous déplacez la barre de défilement vers le haut et vers le bas, la ligne cible se déplace de haut en bas et les barres qui traversent la ligne cible seront mises en surbrillance.
Dans les sections suivantes, vous apprendrez à créer une barre de défilement et à créer une ligne cible dynamique liée à une barre de défilement. Vous apprendrez également à afficher des étiquettes dynamiques dans les barres de défilement.
Créer une barre de défilement
Pour créer une barre de défilement pour un tableau, copiez d'abord les en-têtes des colonnes dans une zone vide de la feuille comme indiqué ci-dessous.
Insérez une barre de défilement.
Cliquez sur l'onglet DÉVELOPPEUR sur le ruban.
Cliquez sur Insérer dans le groupe Contrôles.
Cliquez sur l'icône de la barre de défilement sous Contrôles de formulaire dans la liste déroulante des icônes.
Amenez le curseur sur la colonne I et tirez vers le bas pour insérer une barre de défilement verticale.
Ajustez la hauteur et la largeur de la barre de défilement et alignez-la sur le tableau.
Faites un clic droit sur la barre de défilement.
Cliquez sur Contrôle du format dans la liste déroulante.
La boîte de dialogue Contrôle du format s'affiche.
Cliquez sur l'onglet Contrôle.
Tapez ce qui suit dans les cases qui s'affichent.
Cliquez sur le bouton OK. La barre de défilement est prête à être utilisée. Vous avez choisi la cellule O2 comme lien de cellule pour la barre de défilement, qui prend les valeurs 0 à 36, lorsque vous déplacez la barre de défilement vers le haut et vers le bas. Ensuite, vous devez créer une copie des données dans le tableau avec une référence basée sur la valeur de la cellule O2.
Dans la cellule K3, tapez ce qui suit -
= OFFSET (Résumé [@ [S. No.]], $ O $ 2,0).
Appuyez sur le bouton Entrée. Remplissez les cellules de la colonne en copiant la formule.
Remplissez les cellules des autres colonnes en copiant la formule.
Votre tableau dynamique et déroulant est prêt à être copié dans votre tableau de bord.
Déplacez la barre de défilement vers le bas.
Comme vous pouvez le constater, la valeur de la cellule - lien de cellule de la barre de défilement change et les données du tableau sont copiées en fonction de cette valeur. À la fois, 12 lignes de données sont affichées.
Faites glisser la barre de défilement vers le bas.
Les 12 dernières lignes des données sont affichées car la valeur actuelle est 36 (comme indiqué dans la cellule O2) et 36 est la valeur maximale que vous avez définie dans la boîte de dialogue Contrôle de formulaire.
Vous pouvez modifier la position relative du tableau dynamique, modifier le nombre de lignes à afficher à la fois, le lien de la cellule vers la barre de défilement, etc. en fonction de vos besoins. Comme vous l'avez vu ci-dessus, ceux-ci doivent être définis dans la boîte de dialogue Contrôle du format.
Création d'une ligne cible dynamique et interactive
Supposons que vous souhaitiez afficher les ventes par région au cours des 6 derniers mois. Vous avez également défini des objectifs pour chaque mois.
Vous pouvez faire ce qui suit -
- Créez un histogramme affichant toutes ces informations.
- Créez une ligne cible à travers les colonnes.
- Rendez la ligne cible interactive avec une barre de défilement.
- Rendre la ligne cible dynamique en définissant les valeurs cibles dans vos données.
- Mettez en surbrillance les valeurs qui atteignent la cible.
Créez un histogramme affichant toutes ces informations
Sélectionnez les données. Insérez un histogramme groupé.
Créer une ligne cible dans les colonnes
Changez le type de graphique en combo. Sélectionnez le type de graphique Ligne pour la série cible et Colonne groupée pour le reste de la série.
Créez une table de base pour la ligne cible. Vous rendrez cette dynamique plus tard.
Remplacez les valeurs de la série de données de la ligne cible par la colonne Cible du tableau ci-dessus.
Cliquez sur le bouton OK.
Modifiez le jeu de couleurs de la colonne groupée. Changez la ligne cible en une ligne pointillée verte.
Rendre la ligne cible interactive avec une barre de défilement
Insérez une barre de défilement et placez-la sous le graphique et redimensionnez-la pour qu'elle s'étale de janvier à juin.
Entrez les paramètres de la barre de défilement dans la boîte de dialogue Contrôle du format.
Créez un tableau avec deux colonnes - Mois et Objectif.
Entrez les valeurs basées sur le tableau de données et le lien de cellule de la barre de défilement.
Ce tableau affiche le mois et la cible correspondante en fonction de la position de la barre de défilement.
Rendre la ligne cible dynamique en définissant les valeurs cibles dans vos données
Vous êtes maintenant prêt à rendre votre ligne cible dynamique.
Modifiez les valeurs de la colonne cible dans la table de base que vous avez créée pour la ligne cible en tapant = $ G $ 12 dans toutes les lignes.
Comme vous le savez, la cellule G12 affiche la valeur cible de manière dynamique.
Comme vous pouvez le constater, la ligne cible se déplace en fonction de la barre de défilement.
Mettre en évidence les valeurs qui répondent à la cible
Ceci est la dernière étape. Vous souhaitez mettre en évidence les valeurs répondant à la cible à tout moment.
Ajoutez des colonnes sur le côté droit de votre tableau de données - Résultats Est, Résultats Nord, Résultats Sud et Résultats Ouest.
Dans la cellule H3, entrez la formule suivante -
= SI (D3> = $ G $ 12, D3, NA ())
Copiez la formule dans les autres cellules du tableau. Redimensionnez le tableau.
Comme vous pouvez le constater, les valeurs dans les colonnes - East-Results, North-Results, SouthResults et West-Results changent dynamiquement en fonction de la barre de défilement (c.-à-d. Valeur cible). Les valeurs supérieures ou égales à la cible sont affichées et les autres valeurs sont simplement # N / A.
Modifiez la plage de données du graphique pour inclure les colonnes nouvellement ajoutées dans la table de données.
Cliquez sur Modifier le type de graphique.
Faites en sorte que la série cible soit Line et le reste Clustered Column.
Pour la série de données nouvellement ajoutée, sélectionnez Axe secondaire.
Mettez en forme les séries de données de manière à ce que les séries Est, Nord, Sud et Ouest aient une couleur de remplissage orange et que les séries East-Results, North-Results, South-Results et WestResults aient une couleur de remplissage verte.
Entrez une étiquette de données pour la ligne cible et rendez-la dynamique avec la référence de cellule à la valeur Mois dans la table de données dynamiques.
Votre graphique avec ligne cible dynamique est prêt à être inclus dans le tableau de bord.
Vous pouvez effacer l'axe secondaire car il n'est pas nécessaire. Lorsque vous déplacez la barre de défilement, la ligne cible se déplace et les barres sont mises en surbrillance en conséquence. La ligne cible aura également une étiquette indiquant le mois.
Boutons Excel Option (Radio)
Les boutons radio sont normalement utilisés pour sélectionner une option dans un ensemble d'options donné. Il est toujours représenté par un petit cercle, qui contiendra un point lorsqu'il est sélectionné. Lorsque vous disposez d'un ensemble de boutons radio, vous ne pouvez en sélectionner qu'un seul.
Dans Excel, les boutons radio sont appelés boutons d'options.
Vous pouvez utiliser les boutons d'option Excel dans les graphiques pour choisir les données spécifiques que le lecteur souhaite consulter. Par exemple, dans l'exemple de la section précédente, vous avez créé une barre de défilement pour obtenir une ligne cible dynamique avec des valeurs cibles basées sur le mois. Vous pouvez utiliser les boutons d'option pour sélectionner un mois et donc la valeur cible, et baser la ligne cible sur la valeur cible. Voici les étapes -
- Créez un histogramme affichant toutes ces informations.
- Créez une ligne cible à travers les colonnes.
- Rendez la ligne cible interactive avec les boutons d'option.
- Rendre la ligne cible dynamique en définissant les valeurs cibles dans vos données.
- Mettez en surbrillance les valeurs qui atteignent la cible.
Les étapes 1 et 2 sont les mêmes que dans le cas précédent. À la fin de la deuxième étape, vous aurez le tableau suivant.
Rendre la ligne cible interactive avec les boutons d'option
Insérez un bouton d'option.
Cliquez sur l'onglet DÉVELOPPEUR sur le ruban.
Cliquez sur Insérer dans le groupe Contrôles.
Cliquez sur l'icône du bouton d'option sous Contrôles de formulaire dans la liste déroulante des icônes.
Placez-le dans le coin supérieur droit du graphique.
Faites un clic droit sur le bouton Option. Cliquez sur l'option Contrôle du format dans la liste déroulante.
Entrez les paramètres du bouton d'option dans la boîte de dialogue Format de l'objet, sous l'onglet Contrôle.
La cellule F10 est liée au bouton d'option. Faites 5 copies du bouton d'option verticalement.
Comme vous pouvez le constater, tous les boutons d'options ont le même nom, appelé noms de légende. Mais, en interne, Excel aura des noms différents pour ces boutons d'options, que vous pouvez consulter dans la zone Nom. En outre, comme le bouton d'option 1 a été défini pour se lier à la cellule F10, toutes les copies font également référence à la même cellule.
Cliquez sur l'un des boutons d'options.
Comme vous pouvez le constater, le numéro de la cellule liée devient le numéro de série du bouton d'option. Renommez les boutons d'options en janvier, février, mars, avril, mai et juin.
Créez un tableau avec deux colonnes - Mois et Objectif. Entrez les valeurs basées sur le tableau de données et le lien de cellule de la barre de défilement.
Ce tableau affiche le mois et la cible correspondante en fonction du bouton d'option sélectionné.
Rendre la ligne cible dynamique en définissant les valeurs cibles dans vos données
Vous êtes maintenant prêt à rendre votre ligne cible dynamique.
Modifiez les valeurs de la colonne cible dans la table de base que vous avez créée pour la ligne cible en tapant = $ G $ 12 dans toutes les lignes.
Comme vous le savez, la cellule G12 affiche la valeur cible de manière dynamique.
Comme vous pouvez le constater, la ligne cible s'affiche en fonction du bouton d'option sélectionné.
Mettre en évidence les valeurs qui répondent à la cible
Ceci est la dernière étape. Vous souhaitez mettre en évidence les valeurs répondant à la cible à tout moment.
Ajoutez des colonnes sur le côté droit de votre tableau de données - Résultats Est, Résultats Nord, Résultats Sud et Résultats Ouest.
Dans la cellule H3, entrez la formule suivante -
= SI (D3> = $ G $ 12, D3, NA ())
Copiez la formule dans les autres cellules du tableau. Redimensionnez le tableau.
Comme vous pouvez le constater, les valeurs dans les colonnes - East-Results, North-Results, SouthResults et West-Results changent dynamiquement en fonction de la barre de défilement (c.-à-d. Valeur cible). Les valeurs supérieures ou égales à la cible sont affichées et les autres valeurs sont simplement # N / A.
Modifiez la plage de données du graphique pour inclure les colonnes nouvellement ajoutées dans la table de données.
Cliquez sur Modifier le type de graphique.
Faites en sorte que la série cible soit Line et le reste Clustered Column.
Pour la série de données nouvellement ajoutée, sélectionnez Axe secondaire.
Mettez en forme les séries de données de manière à ce que les séries Est, Nord, Sud et Ouest aient une couleur de remplissage orange et que les séries East-Results, North-Results, South-Results et WestResults aient une couleur de remplissage verte.
Ajoutez une étiquette de données dynamique à la ligne cible avec la valeur de la cellule $ G $ 12.
Effacez l'axe secondaire car il n'est pas nécessaire.
Sous l'onglet VUE du ruban, décochez la case Quadrillage.
Modifiez l'option Étiquette sur Élevé dans les options Format de l'axe. Cela déplace les étiquettes de l'axe vertical vers la droite, ce qui rend votre étiquette de données de ligne cible bien visible.
Votre graphique avec la ligne cible dynamique et les boutons d'option est prêt à être inclus dans le tableau de bord.
Lorsque vous sélectionnez un bouton d'option, la ligne cible s'affiche selon la valeur cible du mois sélectionné et les barres sont mises en surbrillance en conséquence. La ligne cible aura également une étiquette de données indiquant la valeur cible.
Cases à cocher Excel
Les cases à cocher sont normalement utilisées pour sélectionner une ou plusieurs options à partir d'un ensemble d'options donné. Les cases à cocher sont toujours représentées par de petits carrés, qui auront une coche lorsqu'ils sont sélectionnés. Lorsque vous disposez d'un ensemble de cases à cocher, il est possible d'en sélectionner n'importe quel nombre. Par exemple,
Vous pouvez utiliser les cases à cocher Excel dans les graphiques pour choisir les données spécifiques que le lecteur souhaite consulter. Par exemple, dans l'exemple de la section précédente, vous avez créé un histogramme qui affiche les données de 4 régions - Est, Nord, Sud et Ouest. Vous pouvez utiliser les cases à cocher pour sélectionner les régions pour lesquelles les données sont affichées. Vous pouvez sélectionner n'importe quel nombre de régions à la fois.
Vous pouvez commencer par la dernière étape de la section précédente -
Insérez une case à cocher.
Cliquez sur l'onglet DÉVELOPPEUR sur le ruban.
Cliquez sur Insérer dans le groupe Contrôles.
Cliquez sur l'icône Case à cocher sous Contrôles de formulaire dans la liste déroulante des icônes.
Placez-le dans le coin supérieur gauche du graphique.
Modifiez le nom de la case à cocher en Est.
Cliquez avec le bouton droit sur la case à cocher. Cliquez sur Contrôle du format dans la liste déroulante.
Entrez les paramètres de la case à cocher dans la boîte de dialogue Contrôle du format, sous l'onglet Contrôle.
Cliquez sur le bouton OK. Vous pouvez observer que dans la cellule liée C19, TRUE sera affiché si vous cochez la case et FALSE s'affichera si vous désélectionnez la case à cocher.
Copiez la case à cocher et collez-la 3 fois horizontalement.
Changez les noms en Nord, Sud et Ouest.
Comme vous pouvez le constater, lorsque vous copiez une case à cocher, la cellule liée reste la même pour la case à cocher copiée également. Cependant, comme les cases à cocher peuvent avoir plusieurs sélections, vous devez rendre les cellules liées différentes.
Remplacez les cellules liées pour le Nord, le Sud et l'Ouest par 20 USD, 21 USD et 22 USD respectivement.
L'étape suivante consiste à n'avoir que les données des régions sélectionnées dans le graphique.
Créez une structure de table comme suit -
- Tapez = IF ($ C $ 19, H3, NA ()) dans la cellule C21.
- Tapez = IF ($ D $ 19, I3, NA ()) dans la cellule D21.
- Tapez = IF ($ E $ 19, J3, NA ()) dans la cellule E21.
- Tapez = IF ($ F $ 19, K3, NA ()) dans la cellule F21.
- Remplissez les autres lignes du tableau.
Ajoutez la colonne Cible.
Remplacez les données du graphique par cette table.
Le graphique affiche les données des régions sélectionnées qui dépassent la valeur cible définie pour le mois sélectionné.
