mosprepaPréparez votre certification
Microsoft Office Specialist

MS Excel 2013

Les graphiques avancés

Les graphiques sont des outils efficaces pour visualiser un grand nombre de renseignements et de mettre en évidence la corrélation entre les différentes données d'un tableau. Les graphiques permettent aussi d'avoir une idée générale sur l'évolution des données au cours du temps et même de prévoir les résultats à venir.

Dans ce cours qui constitue la suite de deux autres cours précédents, on va voir les points qui suivent.

  1. Ajout de courbes de tendance à des graphiques
  2. Création de graphiques à deux axes
  3. Création de modèles de graphiques personnalisés

À noter que ces points entrent dans les compétences à préparer pour l'examen 77-427 de la certification MOS niveau Expert (Première partie).

Recevez ce cours au format PDF en envoyant un message ici

Ajout de courbes de tendance à des graphiques

Une courbe de tendance ou Courbe de régression, permet de ressortir la tendance générale de l'évolution des valeurs étudiées, dans le but de faire des analyses plus approfondies et d'en déduire des prévisions. C'est une courbe, qui peut être une ligne droite ou bien une ligne courbe, construite mathématiquement et qui s'ajuste le plus possible aux données mesurées.

Excel, les graphiques avancés

Ajouter une courbe de tendance

Sous Excel l'ajout d'une courbe de tendance à une série se fait en quelques clics, en appliquant l'une des deux méthodes :

Méthode 1

Après avoir activé le graphique en cliquant dessus, suivre les étapes :

  1. Cliquer sur le bouton + (ELEMENTS DE GRAPHIQUE) qui apparait au coin supérieur droit du graphique
  2. Dans le menu qui apparait, cliquer sur la flèche de la ligne Courbe de tendance
  3. Choisir une courbe de tendance

Excel, les graphiques avancés

Méthode 2

  1. Cliquer sur le graphique pour l'activer
  2. Ouvrir l'onglet Création
  3. Ouvrir le bouton Ajouter un élément de graphique
  4. Cliquer sur la ligne Courbe de tendance
  5. Choisir une courbe

Excel, les graphiques avancés

Ces deux façons de faire, proposent quatre types de courbes de tendance : Linéaire, Exponentielle, Prévision linéaire et Moyenne mobile

Pour plus de choix et d'options, cliquer sur la ligne Autres options de la courbe de tendance en bas de la liste, ce qui a pour effet de faire apparaitre le volet latéral des options. Ce volet propose davantage de types de courbes de tendance : Logarithmique, Polynomiale et Puissance. C'est à vous de choisir, parmi les six propositions, la courbe qui exprime mieux la tendance de vos données.

Excel, les graphiques avancés

Comment choisir une courbe de tendance

Une courbe de tendance (de régression), est fiable lorsqu'elle passe le plus proche possible des valeurs étudiées. Dans certains cas, plusieurs courbes peuvent paraitre fiables, elles s'ajustent tellement aux données qu'il faut les départager mathématiquement pour faire le choix. Excel calcule pour chaque courbe de tendance ajoutée, le Coefficient de détermination R2 qui indique son degré de fiabilité ; plus ce coefficient se rapproche de la valeur 1, plus la courbe est fiable.

Pour afficher le R2 d'une courbe, on accède au Format de la courbe de tendance via un clic droit sur la courbe, puis dans le volet latéral qui apparait à droite de l'écran, on active la case Afficher le coefficient de détermination (R2) sur le graphique.

Excel, les graphiques avancés

Dans le graphique ci-dessous, volontairement surchargé en ajoutant plusieurs courbes de tendance avec leurs coefficients de détermination respectifs, on s'aperçoit que le R2 le plus proche de la valeur 1 est celui de la courbe Poly (Ventes) en noir, avec une valeur de 0,9964.

Excel, les graphiques avancés

C'est donc cette courbe, la courbe polynomiale, qui est la plus fiable dans le cas présent. C'est elle qui sera gardée et toutes les autres seront rejetées.

Excel, les graphiques avancés

Personnaliser une courbe de tendance

Dans le but de mettre en évidence une courbe de tendance et de la rendre plus parlante, on peut modifier ses options et son format. Pour ce faire, on clique sur la courbe de tendance avec le bouton droit de la souris, puis on clique sur la ligne Format de la courbe de tendance, pour afficher le volet latéral

Excel, les graphiques avancés

Les options d'une courbe de tendance

En activant le bouton Options de courbe de tendance on peut faire, entre autre, les opérations suivantes :

  • Donner un nom personnalisé à la courbe : Par défaut, une courbe de tendance est désignée par un nom composé de son type suivi du nom de la série mis entre parenthèses, exemple : Linéaire (ventes). Ce nom, qui n'est pas toujours parlant, peut être personnalisé en choisissant l'option Personnalisé dans la zone Nom de la courbe de tendance et en tapant dans la partie réservée un nom significatif comme par exemple Tendance des ventes. Ce nouveau nom est répercuté dans la légende.
  • Afficher l'équation de la courbe sur le graphique. Si nécessaire, on peut afficher l'expression mathématique de la courbe de tendance en cochant la case correspondante. On remarque dans le cas présent qu'il s'agit d'une équation polynomiale d'ordre 2 : y = 0,4713x2 + 19,527x + 1660,3
  • Afficher les prévisions. En se basant sur les données existantes, Excel peut calculer les valeurs prévisionnelles et prolonger la courbe en avant et/ou en arrière pour une certaine période, ce qui donne une idée sur les prévisions

Excel, les graphiques avancés

Le format d'une courbe de tendance

Sous le bouton Remplissage et ligne, on peut modifier la couleur, la largeur, la transparence, le style de ligne, le type de flèche initiale et le type de flèche finale.

Excel, les graphiques avancés

Travail à faire :

Le tableau suivant donne l'évolution de la population d'un pays

Excel, les graphiques avancés

  1. Sur une feuille Excel, saisissez le tableau d'effectif de la population
  2. Insérez le graphique des données
  3. Ajoutez la courbe de tendance mise en forme
  4. Affichez les prévisions pour 2 périodes en avant.

Création de graphiques à deux axes

L'utilisation des graphiques à deux axes verticaux (y) permet d'illustrer facilement le rapport étroit (la corrélation) qui existe entre deux séries à des échelles très différentes ou bien avec des unités de mesure différentes.

Quand utiliser deux axes

Bien des situations où il faut utiliser un graphique à deux axes verticaux, en voici deux exemples :

Le graphique A, représente l'évolution des nombres des cas positifs et des décès relatifs à la pandémie au cours d'une année, dans une région géographique. On remarque que la différence entre les échelles des deux séries est tellement grande que la courbe des décès, en couleur orange, en est écrasée et n'arrive pas à s'exprimer. Il serait donc raisonnable de dédier à cette série des décès son propre axe avec une échelle appropriée.

Excel, les graphiques avancés

Le graphique B, quant à lui, représente deux séries de données avec des unités de mesure différentes, les précipitations en mm et les températures en °C. La moyenne mensuelle des précipitations varie entre 3 mm et 135 mm alors que la moyenne mensuelle de température va de 13°C à 24°C. Le graphique sera plus facile à lire si on assigne pour chacune des deux séries un axe vertical différent.

Excel, les graphiques avancés

Ajouter un axe secondaire

La procédure d'ajout d'un axe secondaire à une série peut se faire de deux manières différentes.

La première façon de faire, consiste à cliquer sur la courbe concernée avec le bouton droit de la souris, puis de choisir dans le menu contextuel la ligne Mettre en forme une série de données et dans le volet qui apparait à droite de l'écran, choisir l'option Axe secondaire.

Excel, les graphiques avancés

Après avoir ajouté l'axe secondaire à la série, on peut améliorer le rendu en ajoutant les titres des axes et en adaptant les couleurs des étiquettes. L'animation suivante montre les différentes étapes.

Excel, les graphiques avancés

La seconde façon de faire est de modifier le type de graphique en choisissant le type Graphique combiné. Suivez les étapes ci-dessous pour faire.

  1. Cliquez sur le graphique pour l'activer et du coup faire apparaitre l'onglet Création
  2. Dans le groupe Type, cliquez sur le bouton Modifier le type de graphique
  3. Dans la fenêtre qui s'ouvre, choisissez le type Graphique combiné
  4. Dans la zone Choisissez le type de graphique et l'axe pour votre série de données, choisissez le type de graphique que vous voulez utiliser pour chaque série puis cochez la case Axe secondaire en face de la série à laquelle vous voulez ajouter l'axe
  5. Validez en cliquant sur OK

Excel, les graphiques avancés

L'animation suivante montre le déroulement des étapes

Excel, les graphiques avancés

Travail à faire :

  1. Sur une feuille Excel, saisissez le tableau des températures et des précipitations, vu précédemment (Tableau B)
  2. Insérez le graphique des données
  3. Ajoutez l'axe secondaire à la série Température
  4. Mettez les deux axes en forme pour améliorer le rendu. Ajoutez les titres des axes et mettez-les en forme.

Excel, les graphiques avancés

Création de graphiques modèles personnalisés

Vous avez passé un temps considérable pour créer un graphique à votre goût, qui respecte bien la charte graphique de votre établissement et vous aimerez avoir le même style pour vos prochains graphiques. Pour ne pas devoir réinventer la roue à chaque fois, vous pouvez enregistrer le graphique tel que vous l'avez créé, comme modèle et comme ça vous pourrez le réutiliser à chaque que vous en aurez besoin.

Créer un graphique modèle personnalisé

Vous avez élaboré votre graphique comme il se doit, il est temps de le sauvegarder sous forme de graphique modèle pour des utilisations ultérieures. L'opération est très simple puisqu'il suffit de cliquer droit sur le graphique et de choisir la ligne Enregistrer comme modèle ... comme le montre la capture ci-dessous.

Excel, les graphiques avancés

Dans la boite de dialogue qui s'ouvre, saisissez un nom significatif pour votre modèle puis cliquez sur le bouton Enregistrer.

Excel, les graphiques avancés

Remarquez deux choses :

  • Un graphique modèle est enregistré comme fichier avec l'extension (.crtx)
  • Les modèles sont enregistrés dans le dossier Charts qui se trouve dans le dossier Templates qui est lui-même un sous-dossier du dossier Microsoft

Utiliser le graphique modèle personnalisé

Une fois le graphique personnalisé est enregistré comme modèle, sa réutilisation est aussi facile que sa création. Pour mettre en œuvre l'opération, on va se baser sur l'évolution, au fil des heures, de la population de bactéries dans un milieu favorable comme le montre le tableau.

Excel, les graphiques avancés

Les étapes à suivre sont montrées par la capture ci-dessous

Excel, les graphiques avancés

  1. Sélectionner les données à représenter
  2. Ouvrir l'onglet Insertion
  3. Dans le groupe Graphiques, cliquer sur le bouton lanceur de boite de dialogue
  4. Dans la boite de dialogue Insérer un graphique, activer l'onglet Tous les graphiques
  5. Sélectionner la ligne Modèles
  6. Choisir le modèle désiré
  7. Valider

Excel, les graphiques avancés

Le graphique modèle personnalisé étant appliqué, il reste à faire quelques petites rectifications pour corriger les imperfections entourées en rouge.

  • Choisir une courbe de tendance la plus expressive possible (le coefficient de détermination R2, le plus proche de la valeur 1)
  • Excel, les graphiques avancés

  • Pour le nom de la courbe de tendance, on va utiliser un nom parlant. Au lieu de Poly. (Nombre Bactéries), on va choisir l'option Personnalisé et taper Tendance de la population des bactéries avec prévisions
  • Excel, les graphiques avancés

  • Les valeurs affichées dans les étiquettes de l'axe horizontal ne correspondent pas aux valeurs de la colonne Heure du tableau. On doit sélectionner les données pour afficher les bonnes valeurs.
  • Excel, les graphiques avancés

Le document suivant montre le graphique après avoir fait les rectifications nécessaires.

Excel, les graphiques avancés

Supprimer un graphique modèle personnalisé

Vous pouvez supprimer un graphique modèle personnalisé que vous n'utilisez plus, en cliquant sur le bouton Gérer les modèles, qui se trouve en bas de la fenêtre Insérer un graphique, section Modèles, ce qui a pour effet d'ouvrir le dossier Charts où sont stockés les graphiques modèles personnalisés. Par un clic droit sur le fichier non désiré, vous ouvrez le menu contextuel et pouvez choisir la commande Supprimer.

Excel, les graphiques avancés

Remarquez, au passage, que par ce même menu vous pouvez partager votre modèle personnalisé avec d'autres personnes.

Excel, les graphiques avancés

Travail à faire :

Le tableau ci-dessous liste le nombre des barrettes mémoires vendues dans un magasin de vente de matériel informatique, au cours d'une année.

  1. Sur une feuille Excel, saisir les données du tableau
  2. Représenter graphiquement ces données
  3. Ajouter un axe secondaire pour l'une des deux séries
  4. Mettre en forme le graphique de telle façon à mettre en valeur la série PrixTotal
  5. Enregistrer le graphique comme modèle
  6. Envoyer le fichier modèle (.crtx) à l'adresse contact@mosprepa.com

Excel, les graphiques avancés

Recevez ce cours au format PDF en envoyant un message ici

MOS Prepa