Tutoriel Tableau de Bord Excel

Tutoriel : votre 1er tableau de bord dans Excel Bonjour, Je vous propose votre premier tutorial sur la mise en place d’un tableau de bord sur Excel. Voici comment il fonctionne :

1- Description de ce tableau de bord : Description Excel des éléments que j’ai introduits pour faire ce tableau de bord.

www.tssperformance.com

Récapitulatif en Mind Mapping:

Le tutorial ce fera en 3 articles: de mardi à jeudi. NB : Ce tableau de bord a n’est pas le reflet d’un cas réel, je l’ai créé de toute pièce pour pouvoir vous apprendre “step by step” comment je m’y prends pour faire un tel tableau de bord.

2- Les challenges de ce tableau de bord : o

Plus de 100 produits à représenter: Pour créer un mettre un petit (peut-être grand) niveau de difficulté, j’ai supposé que notre tableau de bord doit présenter plus 100 produits. La difficulté vient du fait que notre tableau de bord pour garantir l’efficacité d’un tableau de bord, il doit tenir : - soit sur une seule page imprimée


- soit sur un écran sans avoir besoin de faire défiler l’écran avec la barre verticale de défilement. La solution Excel : L’utilisation de la barre de défilement associée aux données de produits a été la solution choisie.

o

Comparer en même temps les Ventes N, les Objectifs N, les ventes N-1 et les ventes N-2 Lorsqu’on veut représenter les données, il est important de ne pas rendre touffue notre représentation. Pour illustrer mon propos : avez-vous déjà fait un graphique avec tellement de courbe que vous avez du mal à savoir laquelle va où, laquelle est véritablement au-dessus de l’autre? Je pense que oui. C’est exactement cette situation que j’évite en comparant juste deux données à la fois: - Ventes N et Objectifs N - Ventes N et Ventes N-1 - Ventes N et Ventes N-2 La solution Excel : Créer un graphique dynamique grâce à l’insertion de zone de liste déroulante qui me permet de faire varier les données qui sont liées à mon graphique. Ainsi mon graphique devient dynamique.

o

Rendre évident la valeur des indicateurs les plus importants

Lorsqu’on regarde un tableau de bord, il est important que d’un coup d’œil l’utilisateur voit les valeurs des indicateurs les plus importants. Dans notre cas, j’ai supposé qu’il s’agissait de la marge d’exploitation et de la rentabilité financière. Solution Excel : J’ai opté d’insérer une forme rectangulaire (Excel contient près de 150 formes) et d’y mettre mes différentes valeur avec une taille de police à 90. La couleur choisie est un rouge foncé qui permet d’attirer l’attention. Il faut faire très attention lorsqu’on décide d’utiliser la couleur rouge dans une représentation des données. Je prépare un autre cours sur les couleurs à utiliser lors de la mise en place d’un tableau de bord.

o

Représenter de manière pertinente les réalisations comparées aux budgets :

Comparer en pourcentage est toujours plus parlant. En d’autres termes, il est plus pertinent de dire : j’ai réalisé 22% de plus que mon budget au lieu de dire j’ai réalisé 42.992.400 pour un budget de 38.263.236. C’est encore plus vrai pour les représentations. Solution Excel : Pour le faire résoudre ce problème, nous allons présenter un graphique avec plusieurs types de graphique : colonne et ligne. Tout cela sera vu de manière pratique dans les autres articles. A chaque étape on pourra télécharger les fichiers Excel liés à chaque article. Déjà vous pouvez télécharger le fichier (son nom : « TB Tuto Base de donnée ») dans lequel il y a toutes les données dans ce lien (dans la liste déroulante, choisir préalablement "Tableau de bord tutoriel"). P.S : Avant le téléchargement, vous devriez certainement enregistrer votre email pour ceux qui ne l’ont pas encore fait. Collé à partir de

Tutoriel Tableau de bord sur Excel Partie 2 Notre article du tutoriel va porter sur la mise en place de la 1ère partie de ce tableau. Pour pouvoir suivre cet article, il est préférable de lire l’article précédent et de télécharger le fichier avec la base de données appelé TB Tuto Base de donnée . A la fin de l’article vous pourrez télécharger le fichier Excel lié à cet article. Voici ce qu'on va apprendre à faire aujourd'hui.


Insérer des formes dans Excel et les rendre dynamiques: 1- Insérer la forme rectangulaire • Activer la feuille Tableaubord • Insertion/Forme/Rectangle • Ferme une forme de rectangle en faisant glisser la souris sans lâcher • Normalement votre rectangle est sélectionnée; 2- Mise en forme de la forme Dans le nouveau menu Outils de dessin : • Format/Taille/ Hauteur : 4,84cm Largeur : 8,4cm • Dans Remplissage de la forme : Bleu, Accentuation1, plus clair 80% • Dans contour de la forme : Bleu foncé, Texte 2, plus clair 40% Epaisseur : choisir ¾ pt

3- Insérer la valeur de la marge d’exploitation dans la forme • Si votre rectangle n’est pas sélectionné alors sélectionner le • Mettez-vous sur la barre des formules ; • Ecrire = • Activer la feuille « Donnée », sélectionner la cellule L9 • Valider • Ruban/Accueil/ Police : Verdana, Taille : 90 4- Faire pareil pour insérer les autres rectangles: • Rectangle pour « Marge d’exploitation » : Largeur 8,4cm ; Hauteur : 0,84cm • Rectangle pour la valeur de la rentabilité financière, Largeur 6,99cm, Hauteur : 4,84cm • Rectangle pour « Rentabilité financière», largeur 6,99cm hauteur : 0,83cm • Un dernier rectangle vide (pour y mettre le graphique) largeur : 8,41cm hauteur : 5,68cm Si tout s’est bien passé vous devriez avoir ceci:


Créer le graphique Réalisation vs Budget: 1- Retraiter les données pour les mettre en pourcentage Comme on l’a vu dans l’article d’introduction il est plus pertinent de travailler avec les pourcentages qu’uniquement les valeurs. Nous allons d’abord retraiter les données pour les avoir en pourcentage. Le principe : le budget correspond à 100% • Activer la feuille «Données » • En N2 et O2 écrire: Réalisation% et Budget% • En N3, écrire: =L3/N3 • Copier et coller cette formule sur N3 :N6 • Sur O3 :O6 écrire 100% 2- Insérer le graphique • Sélectionner N2:O6 • Insérer Graphique/Colonne/Histogramme 2D/Histogramme groupé 3- Changer le type de graphique pour la série budget • Sélectionner la série budget. Deux méthodes: - Sélectionner directement sur le graphique - Sélectionner le graphique dans le menu Outils de graphique/Disposition/Sélection Active Dans la liste déroulante sélectionner «Série “Budget10%“ » • Toujours dans le menu Outils de graphique/Création/Modifier le type de graphique/Courbe/courbe

4- Rajouter les libellés dans l’axe horizontal • Sur le graphique, cliquez sur le bouton droit de la souris • Sélectionner les données... • Etiquette de l’axe horizontal (abscisse)/Modifier • Dans plage de données =Données!$K$3:$K$6


Voici votre graphique et à côté le graphique qu’on veut avoir

5- Changer la position de la légende • Sélectionner le graphique • Outils de graphique/Légende/Afficher la légende en bas 6- Mettre les valeurs au-dessus des éléments: Résultat=42.992 EBE=85.985 Marge Brute=107.481 Vente=251.338 • Sur la feuille Données, à P3 écrire =L3/1000 • Copier la formule • Coller sur P4 :P6 • Sélectionner la série Réalisation% • Appuyez sur Ctrl+1 (raccourci clavier pour entrer dans Format de série) • Dans Options des séries/Tracer la série/Axe secondaire • Cliquez sur Fermer Normalement voici à quoi ressemble votre graphique:


• Cliquez sur le graphique avec le bouton droit de la souris • Sélectionnez des données • Dans Entrées de légende (Série) Sélectionner Réalisation% (cellule N3) • Dans Etiquettes de l’axe horizontale (abscisse) Cliquez sur Modifier, dans plage d’étiquette des axes =Données!$P$3:$P$6 • Dans Entrées de légende (Série) Sélectionner Budget% • Dans Etiquettes de l’axe horizontale (abscisse) Cliquez sur Modifier, dans plage d’étiquette des axes =Données!$K$3:$K$6 • Nous allons maintenant supprimer l’axe secondaire, 2 méthodes o Sélectionner l’axe/cliquer la touche du clavier Supprimer o Outils de graphique/Axes/Axe Secondaire vertical/Aucun 7- Supprimer le quadrillage et mise en forme du graphique • Supprimer le quadrillage Outils de graphique/Disposition/Quadrillage Horizontale principale/Aucun • Agrandir la taille des colonnes de la série : Sélectionner la série Réalisation Ctrl+1 Options des séries/Largeur de l’intervalle 86% Fermer • Rendre le graphique transparent : - Outils de graphique/Disposition/Sélection active/ Zone de graphique - Ctr+1 /Remplissage/ Aucun Remplissage - Outils de graphique/Disposition/Sélection active/ Zone de traçage - Ctr+1 /Remplissage/ Aucun Remplissage • Mettre le graphique dans la bonne feuille - Couper le graphique(le sélectionner/Ctrl+X) - Activer la feuille TableauBord - Au niveau du rectangle vide Coller (Ctrl+V) Et voici fini la 1ère partie de ce tutoriel, je l’avoue il est un peu long, mais je l’espère tout aussi clair. Pour télécharger le fichier Excel lié à cet article cliquez ici. Si vous avez des questions ou besoin d’éclaircissement, n’hésitez surtout pas. Collé à partir de

Tutoriel Tableau de bord sur Excel Partie 3 Hello,


Nous allons continuer de faire notre tableau de bord. Aujourd’hui nous apprendrons à faire notre graphique dynamique :

Pour rappel, pour mieux suivre cet article, il vous faut lire l’article précédent (http://www.tssperformance.com/blog2.php?id=95) et télécharger le fichier Excel qui comporte les données TB Tuto Base de donnée (http://www.tssperformance.com/telechargement.php):. 1. Créer un graphique simple en courbe et la mettre dans l’autre feuille :  Ouvrir la feuille Données  Sélectionner K14:M26  Ruban/Insertion/Graphique/Ligne/Courbe 2D/Courbe  Valider  Sélectionner le graphique  Couper (Ctrl+X)  Activer la feuille TableauBord  Cliquez G20  Coller (Ctrl+V) 2. Créer des données dynamiques avec un contrôle formulaire (zone de liste déroulante)  Ruban/Développeur/Contrôle/Insérer Zone de liste déroulante (contrôle formulaire)  Si vous n’avez pas le menu Développeur dans Excel 2007, faire: Ruban/Bouton Windows/Option/Standard : Cocher Afficher l’onglet développeur

Insérer la Zone de liste déroulante


Faire glisser la souris sur la feuille jusqu’à avoir la taille adéquat.

 

  

Nous allons maintenant enregistrer les éléments qui vont entrer dans la zone de liste déroulante En A5, A6 et A7 écrire respectivement: Objectif N, Année N-1 et Année N-2 Sélectionner A5:A7 et nommer cette plage comme “Liste”  Formule/Définir un nom : Nom : Liste Fait référence à =TableauBord!$A$5:$A$7 Valider (et votre nom a été créer) cliquez en appuyant sur la touche clavier Ctrl pour sélectionner Appuyer sur Ctr+1 (raccourci clavier qui nous ramène dans la boîte de dialogue Format de contrôle) Dans la boite de dialogue format de contrôle, Contrôle: Plage d’entrée: Liste Cellule liée: $A$3

Valider Pour se rassurer que votre zone de liste déroulante fonctionne faites ce test :   

cliquez sur une cellule dans la feuille (pour désactiver la zone de liste déroulante) Cliquez sur la flèche de la zone de liste déroulante Changer les valeurs : à chaque changement de valeur dans la liste déroulante la cellule A3 change de valeur (de 1 à 3)

Si c’est le cas, alors votre Zone de liste déroulante marche bien. Si ce n’est pas le cas, alors refaites pas à pas la partie 2... Et si ça ne marche toujours pas, alors je pense que vous n’avez plus le choix, vous allez enfin utiliser les commentaires pour poser votre question (c’est juste après l’article en bas). Et je me ferai un plaisir de vous éclairer. Maintenant que notre liste déroulante est insérer, nous allons la relier aux données de notre graphique. 3. Nommer les données dynamiques  Nommer la cellule A3 comme ControlCourbe  Ruban/Formules/Nom définis/Définir un nom Dans la boîte de dialogue Définir un nom: Nom : CourbeDyn Zone : Classeur Fait référence à: =INDEX(Données!$M$15:$O$26;;Controlcourbe) Valider


Pour avoir des explications sur la fonction INDEX l’article INDEX (http://www.tssperformance.com/blog2.php?id=93 )vous sera utile. Dans notre cas, cette formule nous permet de faire varier la plage de cellules CourbeDyn avec la cellule A3. Cette dernière est elle-même liée à la Zone de liste déroulante. 4. Insérer le nom dynamique dans le graphique  Sélectionner le graphique en cliquant sur le bouton droit  Cliquez sur sélectionner les données  Dans la boîte de dialogue Sélectionner la source de données, dans Entrées de légende (Série)  Sélectionner Objectif N



Cliquez sur Modifier Dans la boîte de dialogue Modifier la série, dans Valeur de la série écrire =Données!CourbeDyn




Cliquez sur OK

Et Fermer

Normalement, votre graphique devrait être bel et bien dynamique selon le choix de la Zone de liste déroulante. Vous devriez avoir quelque chose comme cette image :

5. Mise en forme du graphique dynamique :  La légende Sélectionner la légende, cliquez sur la touche Suppr du clavier  L’axe Horizontal Sélectionner l’axe horizontal, en cliquant sur Mars par exemple avec la souris Accueil/Taille Police 7 Agrandir le graphique sur l’horizontale jusqu’à avoir les libellés de l’axe positionnés en horizontal  L’axe vertical Sélectionner l’axe vertical, en cliquant par exemple sur 15 000 000 avec le pointeur de la souris Appuyez sur Ctrl+1 ou Ctrl+Maj+1 (pour avoir le Format de l’axe) Allez dans nombre/Catégorie/Personnalisé/ Code Format : # ##0 Important : juste après le 0, il faut ajouter un espace. Chaque espace ajouté représente les montants divisés par mille Cliquez sur ajouter Validez




Quadrillage Sélectionner le quadrillage en cliquant dessus. Si vous ne réussissez pas cliquez uniquement sur le quadrillage, vous pouvez passer par le menu pour sélectionner le quadrillage:  Cliquez sur le graphique  Outils de graphique/Disposition/Sélection active dans la liste déroulantes des éléments du graphique, choisir Quadrillage principal de Axe vertical  Cliquez sur Suppr de votre clavier

http://www.tssperformance.com/blog2.php?id=97  <>

Tutoriel Tableau de bord sur Excel Partie 4 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 TableauBord

1- 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 :N47


Pour 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 produits Formules/Noms définis/Définir un nom Nom : 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.

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. Collé à partir de

<>


Tutoriel Tableau de Bord Excel

Recommend Documents