Nous allons aller à la phase finale de notre tutoriel sur la mise en place de ce tableau de bord , les articles précédents nous ont permis de mettre en place, la 1ère partie et la 2ème partie de notre tableau de bord.A la fin de l’article vous pourrez télécharger le fichier Excel du tableau de bord.Aujourd’hui nous allons mettre en place la dernière partie de ce tableau de bord.

Pour arriver à ces graphiques, nous avons utilisé la mise en forme conditionnelle. Pour rendre dynamique nos données nous avons utilisé la barre de défilement inclus dans l’onglet développeur d’Excel.Dans la feuille TableauBord1- Créer les graphiques avec la mise en forme conditionnelle:a- Construction du tableau

o En D37 , F37, H37, J37, L37 et M37 écrire respectivement Total, Agence1, Agence2, Agence3, Agence4 et Agence5

o Pour la colonne Total

En C38 écrire =Données!B4, en D38 écrire = Données!C4, en E38 écrire =D38

Copier cette C38:E38 (Ctrl+C) et coller (Ctrl+V) dans C39 :E47

o Pour les autres colonnes c’est le même principe:

En F38 écrire =Données!D4

G38          =F38

H38          = Données!E4

I38           =H38

J38           = Données!D4

K38          =J38

L38          = Données!E4

M38        =L38

N38         = Données!F4

O38         =N38

Copier (Ctrl+C) F38 :O38 et coller (Ctrl+V) F39 :O47
Notre tableau est créé, maintenant nous allons mettre les mises en forme dans ce tableau pour avoir les graphiques. La méthode que je vais utiliser marche uniquement dans Excel 2007 et 2010. Dans un futur article, j’expliquerai comment faire ces mêmes graphiques sans mise en forme conditionnelle .

Pour le moment…

b- Mettre les mises en forme conditionnelles :

Pour la colonne du total

o Sélectionner D38:D47

o Accueil/Style/Mise en forme conditionnelle/Barre de données/Autres règles

o Dans la boîte de dialogue Nouvelle règle de mise en forme , cochez la case afficher la barre uniquement

o Valider en appuyant sur OK  Pour les autres colonnes ce sera exactement le même processus:o Sélectionner les plages F38:F47, H38 :H47, J38 :J47, L38 :L47, N38 :N47Pour le faire, sélectionner la 1ère plage, appuyer sur la touche de votre clavier Ctrl, et sélectionner les autres plages sans lâcher la touche Ctrl

o Accueil/Style/Mise en forme conditionnelle/Barre de données/Autres règles

o Dans la boîte de dialogue Nouvelle règle de mise en forme , cochez la case afficher la barre uniquement

o Valider en appuyant sur OK

A ce niveau, vos graphiques sont insérés mais votre tableau de bord n’est pas dynamique

2- Rendre dynamique notre tableau avec la barre de défilement

a- Insérer la barre de défilement

o Développeur/Insérer/Barre de défilement

o Glisser votre barre de défilement

o Appuyer sur Ctrl+1ou Ctrl+Maj+1 (pour atteindre le format de la forme)

o Dans la boîte de dialogue Format de contrôle/Contrôle/Cellule liée =$A$2

Valider

b- Créer des noms pour le tableau des données

o Activer la feuille Données

o Sélectionner le B3:H103

o Formules/Noms Définis/Depuis la sélection

Cette action nous a permis de créer en même temps près de 105 noms correspondants chacun à d’une part chaque ligne de produit et d’autres part les montants par agences.Un aperçu de ces noms peu ce faire dans Formules/Noms Définis/Gestionnaire des noms

o Créer un nom qui représente la liste des produitsFormules/Noms définis/Définir un nomNom : ListeProduit

Fait référence à  =Données!$B$4:$B$103

c- Rendre dynamique les noms dans la feuille TableauBord

o Activer la feuille TableauBord

o En C38, remplacer la formule qui y est par = INDIRECT(« Données!B »&(4+A2))

Le résultat de cette formule est =Données!B5 (en supposant qu’il y a la valeur 1 dans la cellule A2)

o En C39 remplacer la formule par = INDEX(ListeProduit;EQUIV(C38;ListeProduit;0)+1)

Pour avoir des précisions sur la fonction INDEX voir l’article INDEX

o Copier cette formule (Ctrl+C) et la coller dans C40 :C47 (Ctrl+V)

o En D38 écrire =INDIRECT($C38) INDIRECT(D$37) ( Il y a un espace entre INDIRECT($C38)et  INDIRECT(D$37) ).

Cet espace me permet de faire l’intersection entre les 2 plages de cellules

o Copier cette formule (Ctrl+C) et la coller dans D39:D47, F38:F47, H38:H47, J38:J47, L38:L47, N38:N47 (Ctrl+V)

 

Je pense qu’on a enfin terminé notre tableau de bord. C’était long mais je l’espère intéressant et clair. S’il y a des points qui restent floues ou ne marchent pas, relisez step-by-step et cherchez l’erreur. Si ça ne marche toujours pas, alors vous serez finalement obliger d’utiliser la place pour les commentaires, et me poser des questions.
Je serai ravi d’y répondre.

Le fichier final du tableau de bord est téléchargeable sur ce lien (dans la liste déroulante, choisir préalablement « Tableau de bord tutoriel »).

A bientôt .

 

Remarque:

Lorsqu’on fait un véritable tableau de bord de la performance qui est destiné à un directeur général, il est important de ne pas le surchargé avec un nombre important d’informations. En général les DG n’ont pas le temps de « jouer » avec les tableaux de données super dynamiques.

A ce niveau la vraie force du concepteur du tableau bord est sa capacité à choisir les indicateurs clés de performances (maximum 20) qui permettent au DG de piloter son entreprise. Un tableau de bord tel qu’on vient de le construire serait plutôt destiné à un responsable opérationnelle de l’entreprise.