Graphiques de suivi des objectifs avec Excel

Cette technique permet de prendre rapidement connaissance de l'atteinte, du dépassement ou d'un manque par rapport à un objectif donné.

Définir une matrice de données

Dans une feuille de données Data, créer une matrice de données :

  • C3:N3 : les mois de l'année
  • B4:N4 : un nom de vendeur, suivi de ses résultats mensuels
  • B5:N5 : les objectifs mensuels

Créer les tableaux de calculs intermédiaires

Créer le tableau intermédiaire du suivi mensuel :

Le dépassement des objectifs (B9:N9) :

  • Saisir en C9, la formule =SI(C4>C5;C4-C5;0)

    Les références relatives permettent de recopier la formule sans problème sur le reste de la ligne (jusqu'en N9).

La non atteinte des objectifs (B10:N10) :

  • Saisir en C10, la formule =SI(C4>C5;C5-C4;0)

    Les références relatives permettent de recopier la formule sans problème sur le reste de la ligne (jusqu'en N10).

Le résultat écrété aux objectifs (B11:N11) :

  • Saisir en C11, la formule =SI(C9>0;C4-C9;C4)

    Les références relatives permettent de recopier la formule sans problème sur le reste de la ligne (jusqu'en N11).


Créer le tableau intermédiaire du suivi cumulé :

Les résultats consolidés mensuellement (B15:N15) :

  • Saisir en C15, la formule =C4
  • Saisir en D15, la formule =C15+D4

    Les références relatives permettent de recopier la formule D15 sans problème sur le reste de la ligne (jusqu'en N15).

Les objectifs consolidés mensuellement (B16:N16) :

  • Saisir en C16, la formule =C5
  • Saisir en D16, la formule =C16+D5

    Les références relatives permettent de recopier la formule D16 sans problème sur le reste de la ligne (jusqu'en N16).

Le dépassement des objectifs (B17:N17) :

  • Saisir en C17, la formule =SI(C15>C16;C15-C16;0)

    Les références relatives permettent de recopier la formule C17 sans problème sur le reste de la ligne (jusqu'en N17).

La non atteinte des objectifs (B18:N18) :

  • Saisir en C18, la formule =SI(C15>C16;C16-C15;0)

    Les références relatives permettent de recopier la formule C18 sans problème sur le reste de la ligne (jusqu'en N18).

Le résultat écrété aux objectifs (B19:N19) :

  • Saisir en C19, la formule =SI(C17>0;C15-C17;C15)

    Les références relatives permettent de recopier la formule C19 sans problème sur le reste de la ligne (jusqu'en N19).

Voir la capture d'écran

Créer le graphique de suivi mensuel

Sélectionner la plage B9:N11 et ajouter un graphique histogramme empilé

  • ruban > Insertion
  • graphique > Histogramme empilé

Sélectionner la plage B5:N5

Copier, puis coller dans la zone de traçage du graphique

Modifier l'apparence de cette série de données pour Courbe

  • ruban > Création
  • Modifier le type de graphique

Organiser les séries de données :

  • sélectionner le graphique
  • ruban > Création
  • Sélectionner des données
  • organiser les courbes dans l'ordre : Ajustement obj., Plus, Moins, Objectif

Ajuster le rendu général du graphique :

  • échelle des abscisse déclarée sur la plage C3:N3
  • supprimer la légende
  • ajouter un titre au-dessus : Suivi mensuel
  • caler le graphique pour que données soient au regard des colonnes des mois

Ajuster le rendu de chaque série de données :

  • sélectionner la série de données
  • click droit
  • Mettre en forme une série de données :
     
    • Ajustement obj. :

      - remplissage dégradé : linéaire
      - angle = 0°
      - 3 points de couleurs : 0 et 100% = bleu clair ; 50% = bleu foncé
      - bordure : pleine, bleu foncé
      - style de bordure : 0,5pt, trait plein
      - ombre : externe bas droite

    • Plus :

      - remplissage dégradé : linéaire
      - angle = 0°
      - 3 points de couleurs : 0 et 100% = vert clair ; 50% = vert foncé
      - bordure : pleine, vert foncé
      - style de bordure : 0,5pt, trait plein
      - ombre : externe bas droite

    • Moins :

      - remplissage dégradé : linéaire
      - angle = 0°
      - 3 points de couleurs : 0 et 100% = rouge foncé ; 50% = rouge clair
      - bordure : pleine, vert foncé
      - style de bordure : 0,5pt, trait plein
      - ombre : externe bas droite

    • Objectif :

      -pas de marqueur
      - couleur du trait : gris foncé
      - style de trait : 2,25pt, trait plein
      - ombre : externe bas droite

Créer le graphique de suivi cumulé

Sélectionner la plage B15:N19 et ajouter un graphique histogramme empilé

Modifier l'apparence de la série de données Objectifs cumulés :

  • ruban > Création
  • Modifier le type de graphique
  • Courbes

Organiser les séries de données :

  • sélectionner le graphique
  • ruban > Création
  • Sélectionner des données
  • organiser les courbes dans l'ordre : Ajustement obj., Plus, Moins, Objectifs cumulés

Ajuster le rendu général du graphique :

  • échelle des abscisse déclarée sur la plage C3:N3
  • supprimer la légende
  • ajouter un titre au-dessus : Suivi cumulé
  • caler le graphique pour que données soient au regard des colonnes des mois

Ajuster le rendu de chaque série de données :

  • sélectionner la série de données
  • click droit
  • Mettre en forme une série de données :
     
    • Ajustement obj. :

      - remplissage dégradé : linéaire
      - angle = 0°
      - 3 points de couleurs : 0 et 100% = bleu clair ; 50% = bleu foncé
      - bordure : pleine, bleu foncé
      - style de bordure : 0,5pt, trait plein
      - ombre : externe bas droite

    • Plus :

      - remplissage dégradé : linéaire
      - angle = 0°
      - 3 points de couleurs : 0 et 100% = vert clair ; 50% = vert foncé
      - bordure : pleine, vert foncé
      - style de bordure : 0,5pt, trait plein
      - ombre : externe bas droite

    • Moins :

      - remplissage dégradé : linéaire
      - angle = 0°
      - 3 points de couleurs : 0 et 100% = rouge foncé ; 50% = rouge clair
      - bordure : pleine, vert foncé
      - style de bordure : 0,5pt, trait plein
      - ombre : externe bas droite

    • Objectif :

      -pas de marqueur
      - couleur du trait : gris foncé
      - style de trait : 2,25pt, trait plein
      - ombre : externe bas droite

Voir la capture d'écran

Usage

Percevoir la dynamique d'atteinte des objectifs est maintenant facilité :

  • la ligne grise représente les objectifs attendus
  • le remplissage bleu représente le résultat réalisé, jusqu'au seuil de l'objectif
  • un remplissage rouge représente l'écart à l'objectif
  • un remplissage verte représente le dépassement de l'objectif.