Créer un graphique avec une ligne moyenne / cible dans Excel

Dans Excel, il est courant d’insérer une ligne horizontale ou une Ligne verticale dans un graphique ; cette ligne horizontale ou cette Ligne verticale peut faire référence à une valeur cible ou moyenne. Ce type de ligne facilite la comparaison entre les Valeur réelle et la valeur cible ou moyenne, et vous permet de repérer facilement les valeurs situées au-dessus ou en dessous de cette référence, comme le montrent les captures d’écran ci-dessous. Dans cet article, je vais expliquer comment créer un histogramme ou un Graphique en barres avec une ligne moyenne ou cible dans Excel.

Graphique à colonnes avec ligne horizontale	Graphique en barres avec Ligne verticale

• Créer un Graphique à colonnes avec une ligne moyenne ou cible horizontale
• Ajouter une ligne cible avec Valeur différente dans un Graphique à colonnes
• Créer un Graphique à colonnes avec une ligne moyenne dynamique à l’aide de Bouton radio
• Créer un Graphique en barres avec une ligne moyenne ou cible verticale
• Ajouter une ligne cible avec Valeur différente dans un Graphique en barres
• Ajouter une ligne cible / moyenne à un histogramme ou à un Graphique en barres grâce à une fonctionnalité pratique
• Télécharger le fichier exemple Graphique avec ligne moyenne / cible

Créer un Graphique à colonnes avec une ligne moyenne ou cible horizontale

Pour créer un Graphique à colonnes avec une ligne moyenne ou cible horizontale, procédez comme suit :

1. Pour créer un graphique à colonnes avec une ligne moyenne, commencez par calculer la moyenne de votre série de données. Saisissez la formule ci-dessous dans une cellule vide, puis étirez la poignée de recopie vers les autres cellules, comme illustré :

2. Ensuite, sélectionnez la plage de données incluant les valeurs moyennes, puis cliquez sur Insertion > Insérer un histogramme ou un graphique en barres > Histogramme groupé, comme illustré :

3. Un graphique à colonnes est alors inséré dans la feuille de calcul. Vous pouvez supprimer les éléments inutiles, tels que le titre du graphique ou les lignes de quadrillage, selon vos besoins, comme illustré :

4. Cliquez ensuite avec le bouton droit sur la série de données moyennes (barres oranges), puis choisissez Modifier le type de série – Type de graphique dans le menu contextuel, comme illustré :

5. Dans la boîte de dialogue Modifier le type de Type de graphique, sous la liste Choisir le type de graphique et l’axe pour votre série de données, sélectionnez Graphique en courbes dans la liste déroulante Type de graphique pour la série de données Moyenne, comme illustré :

6. Cliquez ensuite sur le bouton OK pour obtenir le graphique illustré ci-dessous :

7. Vous pouvez ensuite ajouter une étiquette de données à cette ligne moyenne : double-cliquez pour sélectionner le point de données le plus à droite de la courbe, comme illustré :

8. Cliquez ensuite sur Éléments du graphique, cochez la case Étiquettes de données, et l’étiquette de données sera ajoutée à la ligne moyenne, comme illustré :

Ajouter une ligne cible avec Valeur différente dans un Graphique à colonnes

Parfois, vous souhaitez comparer les Valeur réelle avec des valeurs cibles différentes pour chaque ligne. Dans ce cas, pour visualiser clairement ces valeurs cibles, le type de graphique ci-dessous peut vous être utile.

Pour créer ce type de graphique, procédez comme suit :

1. Commencez par sélectionner la plage de données, puis cliquez sur Insertion > Insérer un histogramme ou un graphique en barres > Histogramme groupé pour insérer le graphique.

2. Cliquez ensuite avec le bouton droit sur la série de données cibles (barres oranges) et choisissez Modifier le type de série – Type de graphique dans le menu contextuel. Dans la boîte de dialogue Modifier le type de graphique, sélectionnez Graphique en courbes pour la série de données cibles dans la liste déroulante Type de graphique, comme illustré :

3. Cliquez ensuite sur le bouton OK, puis double-cliquez sur la ligne cible et sélectionnez Format de la série de données, comme illustré :

4. Dans le volet Format de la série de données qui s’ouvre, sous l’onglet Remplissage & Ligne, sélectionnez Aucune ligne dans la section Ligne, comme illustré :

5. Toujours dans l’onglet Remplissage & Ligne, rendez-vous à la section Marqueur. Dans la section Options de marqueur, sélectionnez Prédéfini, choisissez la barre horizontale dans la liste déroulante Type, puis définissez une taille correspondant à la largeur de vos barres dans le champ Taille, comme illustré :

6. Ensuite, définissez une couleur de votre choix pour les lignes cibles dans la section Remplissage, comme illustré :

7. Enfin, vous pouvez supprimer les éléments inutiles, tels que le titre du graphique ou les lignes de quadrillage, selon vos besoins.

Créer un Graphique à colonnes avec une ligne moyenne dynamique à l’aide de Bouton radio

Si vous disposez d’un vaste tableau présentant les ventes d’un produit sur plusieurs années et que vous souhaitez visualiser une ligne moyenne par année dans un graphique, il vous suffit d’opter pour un graphique avec ligne moyenne en cliquant sur le bouton radio, comme le montre la démonstration ci-dessous. Dès que vous sélectionnez l’année souhaitée, le graphique s’actualise dynamiquement en affichant la ligne moyenne correspondante.

Pour créer ce graphique dynamique, commencez par insérer plusieurs Bouton radio, puis créez un tableau auxiliaire servant de base au graphique, et enfin insérez un graphique lié aux Bouton radio. Procédez comme suit :

Commencez par insérer plusieurs Bouton radio

1. Cliquez sur Développeur > Insérer > Bouton d’option (contrôle de formulaire), puis dessinez quatre boutons radio et modifiez leur texte selon vos besoins, comme illustré :

2. Cliquez ensuite avec le bouton droit sur le premier bouton radio et choisissez Format de contrôle dans le menu contextuel, comme illustré :

3. Dans la boîte de dialogue Format d’objet ouverte, sous l’onglet Contrôle, sélectionnez une cellule à laquelle lier le bouton radio dans la zone de texte Cellule de lien, puis cliquez sur OK pour fermer la boîte de dialogue. Voir la capture d’écran :

Ensuite, insérez un tableau de données auxiliaire pour créer le graphique en vous basant sur

4. Copiez les noms des produits depuis le tableau d’origine vers une nouvelle plage, saisissez ensuite la formule ci-dessous dans une cellule vide adjacente à vos données copiées, puis étirez la poignée de recopie pour obtenir le résultat. Voir la capture d’écran :

5. Désormais, dès que vous cliquez sur un bouton radio, les données de l’année correspondante s’affichent automatiquement, comme illustré dans la démonstration ci-dessous :

6. Ensuite, calculez les valeurs moyennes à l’aide de la formule ci-dessous. Voir la capture d’écran :

Troisièmement, créez le graphique à partir du nouveau tableau de données auxiliaire

Sélectionnez ensuite les nouvelles données créées, puis appliquez les étapes 2 à 8 de la méthode 1 pour créer un graphique en colonnes avec une ligne moyenne, et vous obtiendrez un graphique doté d’une ligne moyenne dynamique. Voyez la démonstration ci-dessous :

Créer un Graphique en barres avec une ligne moyenne ou cible verticale

Cette section explique comment créer un Graphique en barres avec une moyenne ou une cible verticale. Procédez comme suit :

1. Commencez par créer un tableau de données auxiliaire : saisissez X et Y dans deux cellules, puis calculez les valeurs moyennes dans les cellules D2 et D3. Voir la capture d’écran :

2. Ensuite, saisissez les nombres 0 et 1 dans les cellules E2 et E3 pour la valeur de l’axe des Y. Voir la capture d’écran :

3. Sélectionnez ensuite la plage de données d’origine, puis cliquez sur Insertion > Insérer un histogramme ou un graphique en barres > Barres groupées. Voir la capture d’écran :

4. Un graphique en barres a été inséré dans la feuille. Cliquez ensuite avec le bouton droit sur la barre, puis choisissez Sélectionner les données dans le menu contextuel. Voir la capture d’écran :

5. Dans la boîte de dialogue Source des Sélectionner les données qui s’ouvre, cliquez sur le bouton Ajouter. Voir la capture d’écran :

6. Ensuite, dans la boîte de dialogue Modifier la série, saisissez un nom de série dans la zone de texte Nom de la série, puis sélectionnez les cellules contenant les valeurs de l’axe des X (D2:D3 dans cet exemple) dans la zone de texte Valeurs de la série. Voir la capture d’écran :

7. Cliquez sur OK > OK pour fermer les boîtes de dialogue. La nouvelle série de données est désormais ajoutée à votre graphique en barres (deux barres oranges). Cliquez ensuite avec le bouton droit sur l’une des barres oranges et choisissez Modifier le type de graphique. Voir la capture d’écran :

8. Dans la boîte de dialogue Modifier le type de Type de graphique qui s’ouvre, sélectionnez Nuage de points avec courbes lissées dans la liste déroulante Type de graphique pour la série de données Moyenne. Voir la capture d’écran :

9. Cliquez ensuite sur OK pour fermer la boîte de dialogue. La nouvelle série de données est convertie en un point de données le long de l’axe Y principal. Cliquez alors avec le bouton droit sur la barre, puis choisissez Sélectionner les données dans le menu contextuel. Voir la capture d’écran :

10. Dans la boîte de dialogue Source des Sélectionner les données, cliquez sur l’option Moyenne dans la liste Plage des noms de série, puis sur le bouton Modifier. Voir la capture d’écran :

11. Une boîte de dialogue Modifier la série s’ouvre. Dans la zone Valeur de la série sur l'axe des X, sélectionnez les deux cellules X (D2:D3), puis, dans la zone Valeur de la série sur l'axe des Y, sélectionnez les deux cellules Y (E2:E3). Voir la capture d’écran :

12. Cliquez sur OK > OK pour fermer les boîtes de dialogue. Vous verrez alors qu’une ligne moyenne verticale a été ajoutée au graphique. Voir la capture d’écran :

13. Cliquez ensuite avec le bouton droit sur l’axe vertical secondaire (l’axe de droite) et choisissez Format de l’axe dans le menu contextuel. Voir la capture d’écran :

14. Dans le volet Format de l’axe ouvert, sous l’onglet Options de l’axe, dans la section Options de l’axe, saisissez 1,0 dans la zone de valeur maximale Maximum. Vous obtiendrez alors le graphique illustré dans la capture d’écran ci-dessous :

15. Enfin, supprimez l’axe vertical secondaire et les lignes de quadrillage inutiles selon vos besoins. Votre graphique en barres avec une ligne moyenne verticale est maintenant prêt ! Consultez la capture d’écran :

Ajouter une ligne cible avec Valeur différente dans un Graphique en barres

Pour ajouter une ligne cible avec Valeur différente dans un Graphique en barres, comme illustré dans les captures d’écran ci-dessous, suivez pas à pas les étapes suivantes :

1. Sélectionnez la plage de données, puis cliquez sur Insertion > Insérer un histogramme ou un graphique en barres > Barres groupées pour insérer un graphique en barres. Voir la capture d’écran :

2. Cliquez ensuite avec le bouton droit sur la série de données cible (les barres oranges) et choisissez Format de la série de données dans le menu contextuel. Voir la capture d’écran :

3. Dans le volet Format de la série de données ouvert, sous l’onglet Options de la série, réglez le chevauchement des séries à 100 %. Voir la capture d’écran :

4. Toujours dans le volet Format de la série de données, rendez-vous à l’onglet Remplissage et trait, puis sélectionnez Aucun remplissage dans la section Remplissage. Voir la capture d’écran :

5. Accédez ensuite à l’onglet Effets, puis, dans la section Ombre, effectuez les opérations suivantes :

• Spécifiez la couleur souhaitée depuis la liste déroulante Couleur;
• Réglez le flou sur 0 pt;
• Ensuite, ajustez l’angle à 0°;
• Enfin, ajustez la distance à 2 pt selon vos besoins.

6. Revenez maintenant à l’onglet Options de la série, sélectionnez Axe secondaire dans la section Options de la série, puis ajustez la largeur de l’intervalle à 90 % selon vos besoins. Voir la capture d’écran :

7. Enfin, supprimez les éléments superflus — comme le titre du graphique, l’axe X supérieur ou les lignes de quadrillage — selon vos besoins. Vous obtiendrez ainsi un graphique en barres avec plusieurs lignes cibles, tel qu’illustré dans la capture d’écran ci-dessous :

Ajoutez une ligne cible ou moyenne à un histogramme ou à un Graphique en barres grâce à une fonctionnalité pratique

Télécharger le fichier exemple Graphique avec ligne moyenne / cible