Mettez en surbrillance Au-dessous de la moyenne dans le graphique ligne Excel
Pour voir visuellement quand la ligne sur le graphique est au-dessus ou en dessous de la moyenne de la série, nous pouvons simplement insérer une ligne moyenne sur le graphique. Cela ressemblera à quelque chose comme ça.
Est-ce attrayant? Ou celui-là?
Je peux deviner votre réponse.
Alors, comment faire ce graphique qui met en évidence la zone lorsque la ligne est au-dessus ou en dessous de la moyenne? Apprenons-le étape par étape.
Zone de surbrillance lorsque la ligne est au-dessus et au-dessous de la moyenne La meilleure façon d’apprendre est d’apprendre par l’exemple. Commençons donc par un exemple.
Ici, j’ai quelques données qui indiquent les ventes totales effectuées par les vendeurs individuels. Je souhaite visualiser ces données dans un graphique.
Étape 1: Ajouter trois colonnes d’assistance
Pour créer un tel graphique, nous aurons besoin de trois colonnes d’aide. Les premières colonnes d’assistance auront des données moyennes. La deuxième colonne d’aide contiendra tous les points où les ventes sont supérieures à la moyenne. Et la troisième colonne aura les points où les ventes sont inférieures à la moyenne.
La formule dans la colonne moyenne sera donc:
|
=AVERAGE($B$2:$B$7) |
Écrivez cette formule dans la première cellule de la colonne moyenne et faites-la glisser vers le bas. Assurez-vous que toutes les cellules ont les mêmes valeurs. Nous avons utilisé link: / excel-range-name-what-is-an-absolute-reference-in-excel [référencement absolu] pour que les références ne changent pas lorsque la formule est copiée.
Dans la colonne suivante, nous avons besoin de toutes les valeurs UP. Ce sera une différence positive entre les données moyennes et les données de vente. En d’autres termes, nous devons soustraire les données moyennes des ventes uniquement si les ventes sont supérieures à la moyenne.
La formule en D2 sera:
|
=IF(B2>C2,B2-C2, » ») |
Faites-le glisser vers le bas.
Dans la colonne suivante, nous avons besoin de toutes les valeurs Down. Ce sera une différence négative entre la moyenne et les données de ventes. En d’autres termes, nous devons soustraire les données moyennes des ventes uniquement si les ventes sont inférieures à la moyenne.
La formule pour E2 sera:
|
=IF(B2<C2,B2-C2, » ») |
Faites-le glisser vers le bas.
Étape 2: Sélectionnez Sales Adviser and Sales et insérez un graphique linéaire: Dans notre exemple, nous sélectionnons la plage A1: C7. Allez dans Insertion → lien: / tips-best-charts-in-excel-and-how-to-use-them [Charts] → Line and Area → Line with Markers. Nous avons un graphique qui ressemblera à ceci.
Étape 3: Ajouter une fois de plus la série moyenne
Cliquez avec le bouton droit sur le graphique et cliquez sur l’option Sélectionner les données. Ici, cliquez sur le bouton Ajouter dans la section Légende et entrées. Sélectionnez la plage moyenne C2: C7 comme valeurs de la série. Il insérera une ligne sur la ligne moyenne d’origine. Nous en aurons besoin dans le formatage. C’est important.
Étape 4: Ajouter des séries ascendantes et descendantes aux données
Lorsque vous ajoutez à nouveau la série moyenne, ajoutez de la même manière les séries Up et Down une par une à la série. Enfin, vous aurez un graphique qui ressemble à ceci. cela ne ressemble en rien à ce que nous voulons que ce soit, non? Attendez, ça va évoluer.
Étape 5: Changer la deuxième série moyenne, la série ascendante et descendante en graphique à aires empilées Cliquez avec le bouton droit de la souris sur n’importe quelle série du graphique et cliquez sur l’option Modifier le type de graphique de la série de données. Cela changera beaucoup le graphique et il commencera à prendre la forme que nous voulons que ce graphique en courbes Excel soit.
Étape 6: Sélectionnez Aucun remplissage pour le graphique empilé de surface moyenne:
Cliquez avec le bouton droit de la souris sur la zone de la pièce moyenne et cliquez sur la série de données de format. En ligne avec l’option de remplissage, allez à remplir et sélectionnez aucune option de remplissage.
Maintenant, le graphique ressemble exactement à ce à quoi nous voulions qu’il ressemble. Nous avons juste besoin de faire un peu de mise en forme pour l’adapter à notre thème de tableau de bord / présentation.
Étape 7: Formatez le graphique en fonction du thème.
Supprimez tous les éléments moins nécessaires du graphique. J’ai supprimé la légende moyenne du graphique car je n’en ai pas besoin. J’ai supprimé le quadrillage car je souhaite que mon graphique soit plus propre. J’ai changé la couleur de la zone en haut en vert depuis et en bas de la zone en rouge. J’ai nommé le titre du graphique « Ventes par rapport à la moyenne ».
Enfin, j’ai ce graphique.
Remarque: * Ce graphique peut être utilisé comme un graphique objectif / réussite. Changez simplement les valeurs de la série moyenne en objectifs et en boom, vous aurez le graphique cible créative vs réalisation dans Excel.
Téléchargez le fichier modèle ci-dessous:
| C’est une autre fonction essentielle du tableau de bord. Cela vous aide à résumer les valeurs sur des conditions spécifiques.