Ce didacticiel montrera comment créer des graphiques Excel avec une mise en forme conditionnelle dans toutes les versions d'Excel : 2007, 2010, 2013, 2016 et 2022.
La mise en forme conditionnelle consiste à attribuer une mise en forme personnalisée aux cellules Excel (couleur, police, etc.) en fonction des critères spécifiés (conditions). La fonctionnalité aide à analyser les données, à trouver des valeurs statistiquement significatives et à identifier des modèles au sein d'un ensemble de données donné.
Mais malheureusement, Excel n'offre aucun outil intégré pour appliquer une mise en forme conditionnelle aux graphiques Excel.
Cependant, avec une goutte de magie du tableur, aucun problème n'est insurmontable. Dans ce didacticiel, vous apprendrez à créer un graphique dynamique avec une mise en forme conditionnelle dans laquelle les points de données tracés sont automatiquement mis en forme en fonction de leurs valeurs réelles respectives.
À titre d'exemple, jetez un œil à ce graphique à colonnes, qui décompose les performances d'un directeur commercial sur une période de huit mois. Remarquez comment la couleur de la colonne s'ajuste automatiquement en fonction de sa valeur sous-jacente :
Commencer
Considérez cet exemple d'ensemble de données contenant des données sur les performances des ventes :
Avant de commencer, déplacez votre ensemble de données afin d'avoir trois lignes vides au-dessus du tableau avec vos données brutes. Les lignes vont être utilisées pour définir des règles de mise en forme conditionnelle.
Étape 1 : préparez les données du graphique.
Pour que les choses fonctionnent, vous devez ajouter des données graphiques supplémentaires à l'équation. Pour plus de commodité, jetez un œil à ce à quoi devraient ressembler les données de votre graphique à la fin de la phase de préparation :
Notre première étape consiste à définir les valeurs limites (conditions) qui doivent diviser les valeurs réelles en plusieurs catégories, ce qui permet de les représenter sous forme de séries de données distinctes.
À partir de la capture d'écran ci-dessus, par exemple, vous pouvez voir que les chiffres de vente pour un mois donné doivent se situer entre 90 000 $ et 150 000 $ pour être considérés comme satisfaisants.
Pour créer les règles, utilisez les trois lignes vides au-dessus de l'ensemble de données :
- Ligne 1 (Étiquette) : Utilisez ces cellules pour étiqueter les règles de formatage.
- Ligne 2 (valeur min.) : Les valeurs de cette ligne définissent la limite inférieure pour chaque condition.
- Ligne 3 (valeur max.) : De même, ces valeurs définissent les limites supérieures.
Avec cette méthode, vous pouvez créer autant de règles de mise en forme conditionnelle que vous le souhaitez. Assurez-vous simplement que les plages de valeurs ne se chevauchent pas.
Au fur et à mesure que vous avancez dans la configuration de votre graphique, concevez des éléments de légende de graphique personnalisés qui fourniront un contexte pour le graphique. Entrez cette formule dans C4 et copiez-le sur D4 en faisant glisser la poignée de remplissage :
| 1 | =C1&": de "&TEXT(C2, "$#,##")&" à "&TEXT(C3, "$#,##") |
La formule rassemble une étiquette dynamique d'apparence soignée basée sur les règles de mise en forme conditionnelle précédemment définies. La fonction TEXTE formate les valeurs sous forme de devise. Mais si votre type de données diffère, appliquez plutôt cette formule :
| 1 | =C1&": de "&TEXTE(C2, "#,##")&" à "&TEXTE(C3, "#,##") |
Ou celui-ci lorsque vous travaillez avec des pourcentages :
| 1 | =C1&": de "&TEXT(C2, "#%")&" à "&TEXT(C3, "#%") |
Enfin, trouvez les valeurs du graphique. Tapez la formule suivante dans C5, copiez-le dans C12 et à travers les cellules correspondantes dans colonne D:
| 1 | =SI(ET(C$2<$B5,$B5<=C$3),$B5,NA()) |
La formule compare une valeur réelle donnée en colonne B par rapport aux valeurs limites spécifiées à l'aide des fonctions IF et AND. Si la valeur tombe dans la plage, elle est copiée dans cette colonne. Sinon, la formule renvoie l'erreur #N/A pour éviter que la même valeur ne soit tracée plusieurs fois.
Étape 2 : Configurez un graphique à colonnes.
Après avoir rassemblé toutes les données du graphique, configurez un graphique à colonnes simple ou un graphique à barres comme alternative :
- Mettez en surbrillance toutes les données du graphique à l'exception des colonnes contenant les valeurs réelles et les règles en maintenant la touche Ctrl clé (A4:A12 et C4:D12).
- Allez au Insérer languette.
- Sélectionnez "Insérer une colonne ou un graphique à barres.”
- Choisir "Colonne groupée/Barre groupée.”
En conséquence, vous obtiendrez un graphique avec des points de données tracés en fonction des règles de mise en forme conditionnelle, comme promis :
Étape 3 : modifiez les valeurs de chevauchement et de largeur d'espace.
Avant de fermer ce didacticiel, corrigez le problème des colonnes mal placées causées par les valeurs vides invisibles (les erreurs #N/A) tracées sur le graphique.
Faites un clic droit sur l'une des colonnes et choisissez "Formater la série de données” dans le menu contextuel qui s'affiche.
Dans le volet Office, modifiez la position et la largeur des colonnes :
- Basculez vers le Options de série languette.
- Changer "Chevauchement de séries" à "100%.”
- Met le Largeur de l'espace à "60%.”
Étape 4: Ajustez le schéma de couleurs.
Enfin, ajoutez les touches finales. Sans fermer le Formater la série de données dans le volet Office, modifiez le jeu de couleurs du graphique :
- Allez au Remplir & Ligne languette.
- Sous "Remplir," choisir "Remplissage solide.”
- Clique le "La couleur de remplissage” icône et choisissez vert de la palette de couleurs.
Une fois là-bas, recolorez les autres séries de données, modifiez le titre du graphique et votre graphique entièrement dynamique avec mise en forme conditionnelle est prêt à être utilisé !
