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

Dans Excel, il peut être courant d'insérer une ligne horizontale ou verticale sur un graphique. Cette ligne peut faire référence à une valeur cible ou moyenne. Ce type de ligne facilite la comparaison des valeurs réelles par rapport à la valeur cible ou moyenne, et grâce à cette ligne, vous pouvez facilement voir où les valeurs sont au-dessus ou en dessous de cette valeur de 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 à barres avec une ligne moyenne ou cible dans Excel.

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

• Créer un graphique en colonnes avec une ligne moyenne ou cible horizontalement
• Ajouter une ligne cible avec différentes valeurs dans un graphique en colonnes
• Créer un graphique en colonnes avec une ligne moyenne dynamiquement en utilisant des boutons radio
• Créer un graphique à barres avec une ligne moyenne ou cible verticalement
• Ajouter une ligne cible avec différentes valeurs dans un graphique à barres
• Ajouter une ligne cible / moyenne à un graphique en colonnes ou à barres avec une fonctionnalité pratique
• Télécharger l'exemple de fichier Graphique avec ligne moyenne / cible

Créer un graphique en colonnes avec une ligne moyenne ou cible horizontalement

Pour créer un graphique en colonnes avec une ligne moyenne ou cible horizontalement, veuillez suivre les étapes ci-dessous :

1. Si vous souhaitez créer un graphique en colonnes avec une ligne moyenne, vous devez d'abord calculer la valeur moyenne de la liste de données. Veuillez entrer la formule ci-dessous dans une cellule vide, puis faites glisser la poignée de recopie automatique pour copier la formule dans d'autres cellules, voir capture d'écran :

2. Ensuite, sélectionnez la plage de données incluant les valeurs moyennes, puis cliquez sur Insertion > Insérer un graphique en colonnes ou à barres > Colonnes groupées, voir capture d'écran :

3. Un graphique en colonnes est inséré dans la feuille de calcul, vous pouvez supprimer les éléments inutiles, tels que le titre du graphique, les quadrillages selon vos besoins, voir captures d'écran :

4. Ensuite, veuillez faire un clic droit sur la série de données moyennes (barres orange), et choisissez Changer le type de graphique de la série dans le menu contextuel, voir capture d'écran :

5. Dans la boîte de dialogue Changer le type de graphique, sous la liste Choisissez le type de graphique et l'axe pour votre série de données, sélectionnez Graphique en ligne depuis la liste déroulante Type de graphique pour la série de données Moyenne, voir capture d'écran :

6. Puis, cliquez sur le bouton OK, vous obtiendrez le graphique comme le montre la capture d'écran ci-dessous :

7. Ensuite, vous pouvez ajouter l'étiquette de données pour cette ligne moyenne, double-cliquez pour sélectionner le point de données le plus à droite de la ligne, voir capture d'écran :

8. Puis, cliquez sur Éléments du graphique, cochez la case Étiquettes de données, et l'étiquette de données a été insérée pour la ligne moyenne, voir capture d'écran :

Ajouter une ligne cible avec différentes valeurs dans un graphique en colonnes

Parfois, vous voudrez peut-être comparer les valeurs réelles avec les valeurs cibles qui sont différentes pour chaque ligne. Dans ce cas, pour visualiser plus clairement les valeurs cibles, le type de graphique ci-dessous pourrait vous aider.

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

1. Tout d'abord, sélectionnez la plage de données, puis cliquez sur Insertion > Insérer un graphique en colonnes ou à barres > Colonnes groupées pour insérer le graphique.

2. Ensuite, faites un clic droit sur la série de données cible (barres orange), et choisissez Changer le type de graphique de la série dans le menu contextuel, dans la boîte de dialogue Changer le type de graphique, choisissez Graphique en ligne pour la série de données cible dans la liste déroulante Type de graphique, voir capture d'écran :

3. Puis, cliquez sur le bouton OK, puis double-cliquez sur la ligne cible, et choisissez Format de la série de données, voir capture d'écran :

4. Dans le volet Format de la série de données ouvert, sous l'onglet Remplissage et contour, sélectionnez Aucun contour dans la section Ligne, voir capture d'écran :

5. Toujours dans l'onglet Remplissage et contour, passez à la section Marqueur, sélectionnez Intégré dans la section Options du marqueur, puis sélectionnez la barre horizontale dans la liste déroulante Type, enfin, définissez une taille correspondant à la largeur de vos barres dans la zone Taille, voir capture d'écran :

6. Ensuite, définissez une couleur pour les lignes cibles comme vous le souhaitez dans la section Remplissage, voir capture d'écran :

7. Enfin, vous pouvez supprimer les éléments indésirables, tels que le titre du graphique, les quadrillages selon vos besoins.

Créer un graphique en colonnes avec une ligne moyenne dynamiquement en utilisant des boutons radio

Si vous avez un grand tableau avec les ventes d'un produit sur plusieurs années, maintenant, vous voulez afficher la ligne moyenne pour chaque année dans un graphique. Pour traiter cette tâche, vous pouvez utiliser un graphique avec une ligne moyenne en cliquant sur le bouton radio comme le montre la démonstration ci-dessous. Lorsque vous sélectionnez l'année correspondante, le graphique avec la ligne moyenne sera affiché dynamiquement.

Pour créer ce graphique dynamique, vous devez d'abord insérer plusieurs boutons radio, puis créer une table de données auxiliaire sur laquelle le graphique est basé, enfin insérer un graphique lié au bouton radio. Veuillez suivre les étapes ci-dessous :

D'abord, insérez plusieurs boutons radio

1. Cliquez sur Développeur > Insertion > Bouton d'option (Contrôle de formulaire), puis dessinez quatre boutons radio et modifiez le texte selon vos besoins, voir captures d'écran :

2. Ensuite, faites un clic droit sur le premier bouton radio, et choisissez Format de contrôle dans le menu contextuel, voir capture d'écran :

3. Dans la boîte de dialogue Format d'objet ouverte, sous l'onglet Contrôle, sélectionnez une cellule pour lier le bouton radio depuis la zone de texte Lien de cellule, et cliquez sur OK pour la fermer, voir capture d'écran :

Deuxièmement, insérez une table de données auxiliaire pour créer le graphique basé dessus

4. Copiez les noms des produits depuis le tableau original vers une nouvelle plage, puis appliquez la formule ci-dessous dans une cellule vide à côté de vos nouvelles données copiées, puis faites glisser la poignée de remplissage pour obtenir le résultat, voir capture d'écran :

5. Maintenant, lorsque vous cliquez sur un bouton radio, l'année correspondante des données sera affichée automatiquement comme le montre la démonstration ci-dessous :

6. Ensuite, vous pouvez calculer les valeurs moyennes en utilisant la formule ci-dessous, voir capture d'écran :

Troisièmement, Créez le graphique basé sur la nouvelle table de données auxiliaire

Ensuite, sélectionnez 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 avec une ligne moyenne dynamiquement. Voir la démonstration ci-dessous :

Créer un graphique à barres avec une ligne moyenne ou cible verticalement

Cette section présentera comment créer un graphique à barres avec une ligne moyenne ou cible verticalement, veuillez procéder comme suit :

1. Tout d'abord, créez une table de données auxiliaire, entrez X et Y dans deux cellules, puis calculez les valeurs moyennes dans les cellules D2 et D3, voir capture d'écran :

2. Ensuite, entrez les nombres 0 et 1 dans les cellules E2 et E3 pour les valeurs Y, voir capture d'écran :

3. Ensuite, sélectionnez la plage de données originale, puis cliquez sur Insertion > Insérer un graphique en colonnes ou à barres > Barres groupées, voir capture d'écran :

4. Et un graphique à barres a été inséré dans la feuille, ensuite, faites un clic droit sur la barre, et choisissez Sélectionner les données dans le menu contextuel, voir capture d'écran :

5. Dans la boîte de dialogue Sélectionner une source de données qui s'affiche, cliquez sur le bouton Ajouter, voir capture d'écran :

6. Puis, dans la boîte de dialogue Modifier la série, entrez un nom de série dans la zone de texte Nom de la série, puis sélectionnez les cellules avec les valeurs X (D2:D3 dans cet exemple) dans la zone de texte Valeurs de la série, voir capture d'écran :

7. Cliquez sur OK > OK pour fermer les boîtes de dialogue, et la nouvelle série de données est maintenant ajoutée à votre graphique à barres (deux barres orange), puis faites un clic droit sur la barre orange et choisissez Changer le type de graphique de la série, voir capture d'écran :

8. Dans la boîte de dialogue Changer le type de graphique qui s'affiche, sélectionnez Nuage de points avec lignes lissées dans la liste déroulante Type de graphique pour la série de données Moyenne, voir capture d'écran :

9. Puis, cliquez sur OK pour fermer la boîte de dialogue, et la nouvelle série de données est convertie en un point de données le long de l'axe Y principal, ensuite, faites un clic droit sur la barre, puis choisissez Sélectionner les données dans le menu contextuel, voir capture d'écran :

10. Dans la boîte de dialogue Sélectionner une source de données, dans la liste des Entrées de légende (séries), cliquez sur l'option Moyenne, puis cliquez sur le bouton Modifier, voir capture d'écran :

11. Ensuite, une boîte de dialogue Modifier la série apparaît, dans la zone Valeurs X de la série, sélectionnez les deux cellules X (D2:D3), et dans la zone Valeurs Y de la série, sélectionnez deux cellules Y (E2:E3), voir capture d'écran :

12. Cliquez sur OK > OK pour fermer les boîtes de dialogue, et vous pouvez voir qu'une ligne moyenne verticale a été ajoutée au graphique, voir capture d'écran :

13. Ensuite, faites un clic droit sur l'axe vertical secondaire (l'axe de droite), et choisissez Format de l'axe dans le menu contextuel, voir 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, tapez 1.0 dans la zone Limite maximale , et vous obtiendrez le graphique comme le montre la capture d'écran ci-dessous :

15. Enfin, vous pouvez supprimer l'axe vertical secondaire et les quadrillages indésirables selon vos besoins, maintenant, un graphique à barres avec une ligne moyenne verticale est terminé. Voir la capture d'écran :

Ajouter une ligne cible avec différentes valeurs dans un graphique à barres

Pour ajouter la ligne cible avec différentes valeurs dans un graphique à barres comme le montrent les captures d'écran ci-dessous, veuillez suivre étape par étape :

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

2. Ensuite, faites un clic droit sur la série de données cible (barres orange), et choisissez Format de la série de données dans le menu contextuel, voir capture d'écran :

3. Dans le volet Format de la série de données ouvert, sous l'onglet Options de la série, changez le chevauchement de la série à 100%, voir capture d'écran :

4. Toujours dans le volet Format de la série de données, passez à l'onglet Remplissage et contour, sélectionnez Aucun remplissage dans la section Remplissage, voir capture d'écran :

5. Ensuite, allez dans l'onglet Effets, sous la section Ombre, veuillez effectuer les opérations suivantes :

• Spécifiez une couleur dont vous avez besoin dans le menu déroulant Couleur ;
• Réglez le flou à 0 pt ;
• Puis, ajustez l'angle à 0° ;
• Enfin, changez la distance à 2pt selon vos besoins.

6. Maintenant, revenez à l'onglet Options de la série, sélectionnez Axe secondaire dans la section Options de la série, et changez la largeur de l'intervalle à 90% selon vos besoins. Voir la capture d'écran :

7. Enfin, supprimez les éléments indésirables, tels que le titre du graphique, l'axe X supérieur, les quadrillages selon vos besoins, vous obtiendrez le graphique à barres avec différentes lignes cibles comme le montre la capture d'écran ci-dessous :

Ajouter une ligne cible / moyenne à un graphique en colonnes ou à barres avec une fonctionnalité pratique

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