Comment créer un tableau de bord qui s’actualise et identifie automatiquement les meilleures performances sur Excel !

Dans ce tutoriel, nous allons apprendre à créer un tableau de bord Excel automatique et dynamique qui s’actualise et met en avant les meilleures performances de vos données en temps réel.

Vous ajoutez une nouvelle entrée dans vos ventes, et instantanément, votre tableau de bord met en évidence les chiffres clés, colorant en vert la meilleure vente et en jaune le profit le plus élevé.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, voici un exemple de tableau de ventes et de profits d’une entreprise par mois.

2. Création des plages nommées dynamiques

Pour commencer, nous allons souhaiter créer des plages nommées dynamiques, qui vont nous permettre d’automatiser l’extension de nos plages de données en garantissant que notre tableau de bord reste dynamique et adaptable.

Nous avons d'ailleurs eu l'occasion de traiter en détail cette notion de plages nommées dynamiques dans un précédent tutoriel, il y a quelques semaines. Si vous souhaitez approfondir cette notion, je vous invite à consulter cette vidéo pour une explication plus poussée de la fonction DECALER et de ses applications pratiques.

Pour faire simple, une plage nommée est tout simplement une section de la feuille de calcul à laquelle nous attribuons un nom spécifique pour pouvoir y faire référence plus facilement dans les formules ou les graphiques.

Cette méthode nous évite d’avoir à sélectionner manuellement les plages, ce qui est surtout pratique lorsque les données évoluent fréquemment.

En créant des plages dynamiques, nous ajoutons une dimension d'automatisation supplémentaire : dès que de nouvelles données sont saisies, la plage s’étend automatiquement pour inclure les nouvelles lignes.

Cela garantit que nos graphiques et analyses restent à jour sans avoir à ajuster manuellement les plages sélectionnées, un gain de temps essentiel, surtout lorsque nous manipulons de grandes quantités de données.

Pour créer une plage nommée dynamique, nous nous appuyons principalement sur la fonction DECALER, qui permet de définir une plage qui s’ajuste automatiquement en fonction des entrées.

La syntaxe de base de DECALER est la suivante :

 =DECALER(référence; lignes; colonnes; hauteur;  largeur) 

Où :

• référence : la cellule de départ à partir de laquelle Excel commencera à compter.
• lignes : le nombre de lignes à décaler par rapport à la cellule de référence.
• colonnes : le nombre de colonnes à décaler par rapport à la cellule de référence.
• hauteur : le nombre de lignes que la plage doit couvrir.
• largeur : le nombre de colonnes que la plage doit couvrir.

Dans notre cas, nous utilisons la fonction NBVAL en association avec DECALER pour définir la hauteur de la plage, de sorte qu’elle s’étende en fonction du nombre de cellules non vides.

Commençons par appliquer cette formule à la colonne « Ventes ».

Pour cela, nous nous rendons dans l’onglet Formules, afin de cliquer sur « Gestionnaire de noms ».

Nous pouvons également utiliser le raccourci clavier [Ctrl]+[F3].

Ensuite, nous cliquons sur le bouton Nouveau pour ajouter une nouvelle plage nommée.

Dans le champ « Nom », nous saisissons le nom à donner à la plage des cellules, comme par exemple « nVentes ».

Dans le champ « Fait référence à », nous saisissons la formule suivante :

 =DECALER($B$9; 0; 0; NBVAL($B$9:$B$1000); 1) 

Ensuite, nous cliquons sur OK pour enregistrer cette plage nommée.

Décryptons cette formule :

• Nous avons choisi $B$9 comme cellule de référence, qui est notre premier point de départ dans la colonne « Ventes ».
• Les paramètres 0;0 indiquent que nous ne décalons pas cette cellule ni en ligne, ni en colonne.
• Le paramètre hauteur est défini par NBVAL($B$9:$B$1000), qui compte le nombre de cellules non vides dans la plage définie (ici, de B9 à B1000). Cela nous permet de ne sélectionner que les lignes contenant des données.
• Enfin, 1 définit la largeur de la plage sur une seule colonne.

Nous allons maintenant créer deux autres plages nommées dynamiques pour les colonnes Profit et Mois, suivant exactement la même logique.

Pour la colonne Profit, nous créerons une plage nommée nProfit avec la formule :

 =DECALER($C$9; 0; 0; NBVAL($C$9:$C$1000); 1) 

Pour la colonne Mois, nous définirons nMois avec la formule :

 =DECALER($A$9; 0; 0; NBVAL($A$9:$A$1000); 1) 

En appliquant ces étapes à chaque colonne, nous aurons ainsi des plages dynamiques parfaitement adaptées.

Chacune de ces plages s’agrandira automatiquement dès que nous ajouterons des données, ce qui rendra notre tableau de bord beaucoup plus flexible.

L’utilisation de plages dynamiques devient essentielle lorsque nous travaillons sur un tableau de bord avec des données en constante évolution. Une bonne pratique consiste à limiter les plages de NBVAL à une étendue raisonnable (ici, jusqu'à 1000 lignes).

Cela nous permet de gérer les données courantes sans alourdir le fichier, et de gagner en performance.

Une autre technique pour rendre notre tableau dynamique aurait consisté à transformer notre base de données en tableau structuré.

En effet, celui-ci s’étend automatiquement dès que nous ajoutons des nouvelles lignes de données, ce qui simplifie énormément les mises à jour.

Par exemple, si nous avons un tableau structuré nommé « VentesProduits » contenant les colonnes « Mois », « Ventes » et « Profit », nous pourrions écrire une formule de somme comme :

 =SOMME(VentesProduits[Profit]) 

Cependant, pour des projets de grande envergure avec des formules avancées et des plages dynamiques spécifiques, les plages nommées peuvent offrir plus de personnalisation.

3. Création du graphique dynamique

Maintenant que nos plages nommées sont en place, créons le graphique qui affichera les données de manière visuelle et dynamique.

Nous commençons par sélectionner les cellules du tableau, puis nous allons dans Insertion > Graphiques et choisissons le graphique à colonnes.

Une fois le graphique créé, nous cliquons sur le bouton « Sélectionner les données » du menu « Création de graphique ».

Pour chaque série, nous remplaçons les plages de données par nos noms dynamiques nVentes et nProfit.

Pour insérer directement le nom dynamique, nous pouvons appuyer sur la touche [F3] pour lister tous les noms disponibles, et l’insérer en double cliquant sur celui-ci :

Attention de bien conserver le nom de la feuille de calcul avant celui de la plage nommée pour que cela fonctionne correctement !

Nous faisons de même pour l’axe horizontal en sélectionnant nMois.

Grâce à ces liaisons, notre graphique est désormais prêt à s’adapter automatiquement aux changements dans notre tableau !

Pour nous en rendre compte, il suffit d’ajouter une ligne en dessous du tableau, avec les ventes de juillet :

4. Mise en forme conditionnelle pour la mise en évidence des meilleures performances

Pour faire ressortir la meilleure performance de manière visuelle, nous allons mettre en place une mise en forme conditionnelle qui colorera en vert la barre représentant la vente la plus élevée. Cela nous permettra de voir d’un seul coup d’œil le mois avec la meilleure vente.

Pour commencer, insérons une colonne juste à droite de notre tableau, que nous appellerons « Max ».

Cette colonne servira uniquement à identifier la valeur maximale de la plage « Ventes », et nous l’utiliserons pour faire apparaître une seule barre de couleur différente dans le graphique, représentant la meilleure vente.

Ensuite, nous sélectionnons les cellules C9 à C1000, en saisissant la plage dans la zone des noms :

Dans cette plage de cellule, nous saisissons la formule :

 =SI(B9=MAX(nVentes);B9;#N/A) 

Cette formule permet de tester si la valeur en B9 correspond au maximum de la plage nVentes. Si c’est le cas, elle affiche cette valeur. Sinon, elle renvoie une erreur #N/A, qui n’apparaîtra pas dans le graphique.

Une fois la formule saisie dans C9, nous validons en appuyant sur [Ctrl]+[Entrée] pour appliquer la formule à toutes les cellules sélectionnées dans la colonne « Max » jusqu’à la ligne 1000, remplissant ainsi la colonne entière avec cette logique.

Maintenant que nous avons la colonne « Max » configurée pour identifier la valeur maximale, nous allons l’intégrer dans notre graphique via une plage nommée.

Appuyons sur [Ctrl]+[F3] pour ouvrir le Gestionnaire de noms.

Dans la fenêtre du gestionnaire, nous allons sélectionner la plage nommée existante « nVentes » et copier sa formule de référence pour nous en servir comme base pour la nouvelle plage.

Une fois cette formule copiée, nous créons une nouvelle plage nommée en cliquant sur Nouveau. Nous appelons cette nouvelle plage nVentesMax.

Dans la zone « Fait référence à », nous collons la formule copiée de nVentes, et nous modifions la cellule de référence B9 qui devient C9, tout en conservant la suite de la formule, pour que le nombre de cellules à extraire soit toujours dépendant du nombre réel de valeurs saisies dans la colonne « Ventes ».

La formule de nVentesMax devient ainsi :

 =DECALER(Excelformation.fr!$C$9; 0; 0; NBVAL(Excelformation.fr!$B$9:$B$1000);  1) 

Pour terminer, nous allons ajouter cette nouvelle série au graphique afin de mettre en évidence la valeur maximale.

Sélectionnons notre graphique et, dans l’onglet Création de graphique, cliquons sur Sélectionner les données.

Dans la fenêtre qui s’ouvre, nous ajoutons une nouvelle série de données en cliquant sur Ajouter. Nous la nommons « Max Vente » pour plus de clarté.

Dans le champ Valeurs de la série, nous entrons la plage nommée nVentesMax comme nous l’avons fait juste avant :

Cette action permettra de ne prendre en compte que la valeur maximale de la colonne « Ventes » et d'ignorer les autres valeurs grâce aux erreurs #N/A.

Une fois cette série ajoutée, nous pouvons modifier son apparence dans le graphique pour la faire ressortir visuellement.

Nous commençons par sélectionner un style de graphique :

Puis, pour finir, il ne reste plus qu’à positionner la barre de données « Max » au-dessus de la barre correspondante pour qu’elle s’affiche bien en évidence sur le graphique.

Pour cela, nous cliquons sur la série « Max » dans notre graphique en cliquant sur celle-ci, puis nous effectuons un clic droit afin de sélectionner « Mettre en forme une série de données » :

Sur le panneau latéral, nous choisissons ensuite une superposition des séries à 100% :

Puis, il ne reste plus qu’à mettre en forme la barre en retirant la couleur de remplissage et en ajoutant un éclat :

En suivant la même méthode nous allons maintenant mettre en évident la série la moins performante, en ajoutant la colonne « Ventes Min » avec la formule :

 =SI(B9=MIN(nVentes);B9;#N/A) 

Nous créons la plage nommée « nVentesMin » avec la formule :

 =DECALER(Excelformation.fr!$d$9; 0; 0; NBVAL(Excelformation.fr!$B$9:$B$1000);  1) 

Puis nous ajoutons cette plage sur le graphique.

À présent, nous pouvons modifier les données existantes, ou ajouter de nouvelles données à la suite de la base pour que le graphique tiennent compte automatiquement de celles-ci :