Maximum et minimum sur un graphique Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Max et Min sur graphique

Cette astuce Excel montre comment faire ressortir en couleurs les valeurs seuils sur un graphique.



Dans l'exemple finalisé illustré par la capture, des ventes d'articles sont recensées dans un tableau. Un graphique est bâti sur ces ventes de manière à pouvoir les comparer. Et comme vous le constatez, deux marques matérialisées par des points, identifient très clairement et dans des couleurs différentes, la meilleure comme la moins bonne vente. Et bien entendu, si ces valeurs seuils venaient à évoluer, ces marques se déplaceraient automatiquement pour les repérer et les identifier.

Source et présentation
Pour la mise en place de cette astuce, nous proposons de baser l'étude sur un classeur existant.

• Télécharger le classeur graph-max-min.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Pour l'instant, seul un petit tableau agrémente l'unique feuille de ce classeur. Il énonce effectivement les ventes réalisées par article. Ces articles sont énumérés par leurs codes en première colonne. Deux rangées vierges attendent, en colonne D et E, des calculs pour identifier les valeurs seuils, respectivement la plus petite vente et la plus grande.

Calculs des valeurs seuils
En colonne D comme en colonne E, une formule doit être capable d'analyser les ventes pour en ressortir uniquement soit la minimale, soit la maximale. Ces données pourront dès lors être intégrées dans le graphique à construire pour les faire ressortir explicitement sur la courbe. Mais pour qu'elles ne soient pas accompagnées de représentations résiduelles, l'astuce consiste à générer une erreur lorsque la valeur seuil n'est pas identifiée. Et pour pouvoir étudier ces cas, nous devons analyser un critère grâce à la fonction conditionnelle Si.

• Cliquer sur la cellule D4 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner la première vente à analyser en cliquant sur sa cellule C4,
• Taper le symbole égal (=) pour annoncer le critère à honorer,
• Inscrire la fonction pour la plus petite valeur, suivie d'une parenthèse, soit : Min(,
• Désigner toutes les ventes, soit la plage de cellules C4:C13,

La formule peut déborder empêchant la sélection de cette plage. Dans ce contexte, il suffit simplement de saisir les coordonnées de la plage au clavier, sans oublier le symbole deux points séparant les deux bornes.

• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $C$4:$C$13,

En effet, nous allons répliquer la logique du calcul sur les lignes du dessous afin que chaque vente puisse être analysée. Mais la comparaison avec la valeur minimale doit toujours être établie par rapport à cette plage qui ne doit donc pas suivre le déplacement.

• Fermer la parenthèse de la fonction Min,
• Puis, taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,

Lorsque ce critère est vérifié, nous savons que la vente en cours d'analyse est bien la plus petite de toutes. Dans ce cas, nous devons simplement restituer sa valeur. Dans le cas contraire et comme nous l'avons annoncé, nous devons générer une erreur.

• Désigner de nouveau la première vente en saisissant ses coordonnées, soit : C4,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Puis, inscrire le message d'erreur suivant : #N/A,

Il s'agit d'un message d'erreur assez conventionnel retourné par Excel par exemple lorsqu'une recherche n'aboutit pas. Il signifie Not Avaliable, soit Non disponible. Mais nous aurions pu utiliser tout autre message d'erreur comme : #Valeur ou #Div/0!.

• Fermer la parenthèse de la fonction Si,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Grâce à cette astuce, nous conservons active la cellule du résultat pour pouvoir l'exploiter dans l'enchaînement.

• Double cliquer sur la poignée de la cellule du résultat,

De cette manière, nous répliquons la logique du calcul sur la hauteur du tableau des ventes. Et comme vous pouvez le voir, seule la vente la plus faible apparaît isolée au milieu d'un nuage d'erreurs.



Nous devons désormais reproduire sensiblement le même calcul pour la colonne suivante. Mais le critère ne doit pas être établi sur la base de la fonction Min mais à l'aide de la fonction Max. Il est donc conseillé de copier la précédente syntaxe pour pouvoir la répliquer et l'adapter par simple changement de nom de la fonction.

• En cellule E4, adapter la formule comme suit : =SI(C4=MAX($C$4:$C$13); C4; #N/A),
• Puis, la valider et la répliquer sur la hauteur du tableau,

Le constat est le même que précédemment. La vente la plus forte apparaît isolée au milieu d'un attroupement de messages d'erreurs. C'est exactement la configuration que nous souhaitions atteindre pour permettre la création d'un graphique avec la représentation des valeurs seuils.

Graphique avec Max et Min
L'essentiel du travail est désormais accompli. Il ne nous reste plus qu'à bâtir un graphique sur les données et les précédents calculs que nous avons commis. Mais ce graphique doit proposer une particularité. Il doit offrir des marques. Ces marques permettront d'identifier de façon explicitement différente les données situées aux extrémités.

• Sélectionner toutes les données du tableau, soit la plage de cellules B3:E13,
• En haut de la fenêtre Excel, cliquer sur l'onglet Insertion pour activer son ruban,
• Dans la section Graphiques du ruban, cliquer sur l'icône du graphique en courbes,
• Dans les propositions, cliquer sur la miniature du graphique courbe avec marques,

Le graphique est instantanément créé sur la feuille. Et comme vous pouvez le voir, les valeurs seuils sont effectivement marquées tandis que les messages d'erreur sont neutralisés. Ensuite, il convient de déplacer et redimensionner le graphique à votre convenance. Il est opportun de le personnaliser avec des attributs de format en adéquation avec les réglages déjà opérés sur cette feuille. Mais nous ne nous attarderons par sur ces fondamentaux que nous avons déjà longuement visités notamment à l'occasion de la formation sur la présentation et la personnalisation des graphiques Excel. L'objectif ici est désormais de mettre la lumière sur ces marques afin d'identifier avec beaucoup d'aisance l'article le moins rentable et celui le plus prolifique.

• Sur le graphique, double cliquer sur la marque du maximum,

Cette action a pour effet de faire apparaître un volet de personnalisation sur la droite de l'écran.

• Cliquer sur l'icône du pot de peinture (Remplissage et trait),
• Juste en-dessous, cliquer sur le sous menu Marque,
• Dans la catégorie Bordure, choisir un vert clair pour la couleur,
• Puis, régler la largeur sur 6 pt,

Le premier point de référence, celui du maximum, ressort désormais explicitement du lot sur la courbe.

Ensuite, il convient de répliquer exactement la même technique en double cliquant sur la marque du minimum, en lui attribuant un orange assez vif et une largeur de 6 pt.

Désormais, si vous modifiez les valeurs des ventes dans le tableau d'origine de manière à déplacer les données remarquables, vous remarquez que la position et le repérage des valeurs seuils s'ajustent automatiquementsur le graphique Excel.



Pour parfaire la solution, il conviendrait d'appliquer une mise en forme conditionnelle sur le tableau afin de faire ressortir le maximum et le minimum dans les mêmes couleurs que celles employées sur le graphique. De plus, il apparaît opportun de masquer les colonnes D et E pour ne plus afficher les erreurs disgracieuses et nuisibles pour le rendu final.