Étiquette : MFC

[Tuto] Jusqu’où peut-on aller avec les mises en forme conditionnelles sur Excel ?

[Tuto] Jusqu’où peut-on aller avec les mises en forme conditionnelles sur Excel ?

La mise en forme conditionnelle est à Excel ce que les épices sont à la cuisine ! Sans aucune mise en forme, cela peut être bon, mais sans saveur. En trop grande dose, cela peut gâcher tout votre travail. Dans cet article, je souhaite vous présenter quelques fonctionnalités de base :  mise en forme conditionnelle des textes, mise en forme conditionnelle des lignes. Je souhaite surtout vous présenter les fonctionnalités avancées des mises en forme conditionnelles. Enfin, je vous présenterai quelques astuces avancées pour réellement exceller avec les mises en forme conditionnelles !

Introduction

Excel permet de faire des calculs, présenter les résultats dans un graphique et également mettre en forme des données, c’est-à-dire changer le format du nombre, le style de la police, la présence et le style de bordure, et enfin le remplissage (la couleur du fond de la cellule).

La fonctionnalité de mise en forme conditionnelle dans Excel permet d’adapter ces attributs de manière automatique en fonction des critères de vos choix ! Cela peut paraître théorique lorsque c’est écrit ainsi, mais avec quelques cas pratiques, ce sera plus clair.

Cas pratique 0 : Prise en main des mises en forme conditionnelles

Créer une nouvelle règle

La fonction de mise en forme conditionnelle a été placée dans le bandeau “Accueil”, c’est vous dire si elle est fréquemment utilisée !

Comme le menu le présente, plusieurs fonctions pré-configurées existent. Celles-ci seraient d’ailleurs difficiles à mettre en place “sans code”. Elles sont donc utiles, mais ne sont pas extensibles…

La fonction que vous utiliserez le plus souvent est bien la “Nouvelle règle…”. Celle-ci vous correspondra et fera ce que vous lui demanderez.

Excel - Menu mise en forme conditionnelle

Une fois cette action réalisée, la fenêtre suivante apparaît.

Excel - Menu mise en forme conditionnelle - Type reglesDans la liste des options proposées, sur la même idée que précédemment, pour les règles de mise en forme conditionnelle “simples”, les 5 premiers choix pourront être utiles. Pour tous les autres cas, ce sera la dernière option “Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué”.

La formule qui permettra de déclencher la mise en forme sera à saisir dans la barre de formule en 2.

Et en 3, surtout, ne l’oubliez pas ! C’est la mise en forme à adopter si la formule saisie renvoie VRAI.

Combien de fois ai-je oublié de configurer la mise en forme… ? Je ne le compte plus. J’étais pourtant certain de ma formule conditionnelle… et rien ne se passait.

Au bout d’un quart d’heure à vérifier et revérifier, je me rends compte que je n’ai pas configuré de mise en forme spéciale… donc le champ ne se mettait pas en évidence !

Gérer les règles de mise en forme conditionnelleExcel - Gerer les mises en forme conditionnelle

Dans le menu initial des mises en forme conditionnelles, se trouve également une option essentielle : “Gérer les règles…”

Cette option permet non seulement de retrouver l’ensemble des règles qui s’appliquent sur une feuille de calcul, mais elle permet aussit de mettre en pratique une super astuce que je vous dévoile plus loin dans cet article !

L’écran qui apparaît alors est EXTRÊMEMENT important !

Excel - Menu mise en forme conditionnelle - Gerer regles

  1. Après l’oubli de configurer une mise en forme, c’est la deuxième cause de perte de temps ! Les règles affichées ne concernent que la portée affichée. Vous pouvez avoir comme choix : le tableau où se trouve votre souris, la sélection ou la feuille entière. Toutes les règles qui s’appliquent ne sont pas systématiquement affichées !
  2. Le format permet d’identifier d’un coup d’œil la règle recherchée.
  3. La zone “S’applique à” est ESSENTIELLE ! Elle va être la clé pour étendre une règle que vous aurez paramétrée sur une unique cellule.
  4. Last but not least, la case “Interrompre si Vrai”. Eh oui ! Il est possible d’interrompre l’application des règles de mise en forme dès qu’une règle est appliquée. Ainsi, l’ordre des règles va également revêtir une importance.

Avec toutes ces informations en main, vous êtes prêt à mettre en pratique les cas que je vous présente ci-dessous.

Cas pratique 1 : Mise en forme conditionnelle d’un texte

Besoin = Détecter les doublons

Le cas d’utilisation que je vois le plus fréquemment avec Excel, c’est de l’utiliser comme un fichier de commandes ou de factures (voire les deux en même temps !). Je vous invite à lire et à relire mon article décrivant les 4,5 apprentissages qu’un cours Excel devrait vous enseigner, et en particulier le premier point ! Cela étant dit, vous avez un problème à résoudre, c’est celui de ne pas donner deux fois le même numéro de commande ou de facture sur des lignes différentes.

Excel n’étant pas une application de gestion, il n’intègre pas de fonctionnalité d’incrément de numéro ou de création d’un identifiant unique.

Vous devez saisir les numéros de commandes qui contiennent en plus des lettres pour diverses raisons, vous n’avez pas fabriqué une formule de calcul automatique. Vous devez seulement vérifier qu’il n’y a pas eu d’erreur de saisie (et surtout de doublons).

Excel - Mise en forme conditionnelle - Texte

Étape n° 1 : Identifier la formule qui permet de détecter le doublon

Déterminons comment détecter un doublon pour une cellule donnée.

La réponse se trouve dans la question, détecter un doublon, c’est trouver au moins deux fois la même valeur. La formule qui sera utilisée est donc : NB().

Dans la mesure où il s’agira de tester si le nombre d’occurrences une valeur est strictement supérieure à 1, nous nous orienterons vers NB.SI(plage de recherche ; valeur recherchée).

En langage Excel, cela donnera : NB.SI($A$2:$A$19;$A$2)>1.

Cette formule signifie “Compte le nombre de fois que tu trouves la valeur de $A$2 dans la plage de valeur allant de $A$2 à $A$19 et dis-moi si ce nombre est supérieur à 1”.

Étape n° 2 : Tester cette formule sur un seul champ

Pour tester cette formule, il ne reste qu’à ajouter une formule contenant une condition, “Si – Y’a-t-il un doublon » – alors VRAI sinon FAUX.

Cela nous amène à la formule complète : =SI(NB.SI($A$2:$A$19;$A$2)>1;VRAI;FAUX).

Étape n° 3 : Généraliser sa formule sur une plage de champs

Excel - Plage mise en forme conditionnellePour appliquer une règle sur un ensemble de champs, il sera nécessaire de relire et analyser à nouveau la formule qui avait été trouvée.

Il faut analyser les références fixes et réfléchir à celles qui doivent devenir relatives.

Rappel sur les références et l’utilisation du symbole $

Une référence à une cellule avec des symboles $ indique que la référence ne doit pas s’adapter au contexte, l’absence du symbole indique qu’il faut s’adapter au contexte.

Deuxième rappel, $A2 signifie que la colonne ne doit pas s’adapter, mais la ligne oui (ce sera toujours la colonne A). A$2 indique que la colonne doit s’adapter et la ligne non (ce sera toujours la ligne “2”).

Notre formule précédente =SI(NB.SI($A$2:$A$19;$A$2)>1;VRAI;FAUX) possède deux références. L’une correspond à toutes les cellules parmi lesquelles nous recherchons des doublons ($A$2:$A$19), l’autre correspond à la cellule dont nous cherchons les doublons ($A$2).

Comme nous allons étendre la zone sur laquelle nous recherchons des doublons, si la cellule recherchée reste avec les symboles $, la mise en forme changera uniquement si la cellule A2 est en doublon ! Il est donc essentiel de changer manuellement cette référence de $A$2 en A2. Ainsi, la règle adaptera AUTOMATIQUEMENT au contexte où elle s’applique.

Il ne vous reste qu’à étendre la plage d’application de la règle.

Dans le menu “Gérer les règles de mise en forme”, vous devriez avoir cet écran :

Excel - Plage mise en forme conditionnelle - 1

Ainsi, seule la première cellule est vérifiée. Vous allez pouvoir étendre la zone sur laquelle cette règle s’applique avec la petite flèche vers le haut et en sélectionnant toute la plage de vérification.

Excel - Plage mise en forme conditionnelle - 2

Maintenant, c’est tout bon !

Attention : Si vous retiriez les $ de la plage de recherche ($A$2:$A$19), celle-ci changerait également automatiquement. Elle deviendrait A3:A20 pour le test de la 2e ligne, A4:A21 pour la 3e, et ainsi de suite. Au fur et à mesure, les valeurs des premières cellules du tableau ne seraient plus dans la zone de recherche d’un doublon !

Autres cas

A ce stade, vous l’aurez certainement compris, la mise en forme conditionnelle d’une date ou la mise en forme conditionnelle d’une somme n’est pas plus difficile. Il vous faut déterminer la formule qui vous correspond pour détecter quand il faut changer la mise en forme.

Ne pas oublier que la mise en forme conditionnelle peut également adapter le format des nombres. J’ai d’ailleurs récemment découvert la mise à jour des monnaies disponibles dans Excel avec la présence du Bitcoin !

Excel - Menu mise en forme conditionnelle - Format nombre

Cas pratique 2 : Mise en forme conditionnelle d’une autre cellule

Initialement, nous avons tendance à penser que la mise en forme doit s’appliquer sur la cellule contenant les valeurs. Que nenni ! Les règles partent de la cellule à mettre en forme, peu importe où elle se trouve.

Ce sont les règles de mise en forme conditionnelle qui vont déterminer les conditions de changement de la mise en forme.

Ainsi, je peux configurer une cellule que j’appellerais “Garant de la conformité”. Je peux par exemple conditionner sa mise en forme au respect de toutes les règles de gestion de ma feuille Excel.

  • S’il y a un doublon, je passe en rouge.
  • S’il manque une valeur dans une ligne, je passe en bleu.
  • Si le total est négatif, je passe en orange.

D’un coup d’œil, il est alors possible de connaître l’état de cohérence de la feuille / du classeur Excel.

Cas pratique 3 : Mise en forme conditionnelle d’une ligne entière

La mise en forme conditionnelle d’une ligne entière n’est pas plus compliquée que celle d’une cellule unique. Le véritable défi se situe dans la configuration des références ($A$1 ≠ $A1 ≠ A$1 ≠ A1).

La situation que j’utilise régulièrement, c’est de mettre en forme toutes les cellules concernées par un calcul qui ne respecte pas une condition. Par exemple, je vais répartir un travail entre des équipes à l’aide de pourcentage. Bien sûr le total doit faire 100, et je peux le calculer et présenter le résultat.

Excel -MFC - Ligne entiere

L’enjeu reste toujours de gérer les références fixes et relatives. Dans l’exemple présenté, le test est assez simple : =SI($K$2<>1;VRAI;FAUX).

Attention, 100% = 1 et pas 100… Sinon, votre test retournera VRAI tant que le total ne sera pas de 10’000 %.

La plage où appliquer cette règle sera l’ensemble du tableau : $C$2:$K$11.

Hélas, cela ne fonctionnera que pour la première ligne. Dès la deuxième, cela posera un problème ! Avez-vous trouvé pourquoi ?

Il n’y a aucune référence qui est relative dans le test “SI”. Ce sera toujours la cellule K2 qui sera testée. Comme vous souhaitez rendre le test relatif à la ligne où vous vous trouvez, il faudra saisir $K2 au lieu de $K$2.

La bonne réponse est donc : =SI($K2<>1;VRAI;FAUX).

Bonus pour devenir un expert

Dès que possible, j’utilise les tables dans Excel. Cela a un avantage important dans le nommage des cellules dans les formules.

Ainsi, la somme des pourcentages (issue de l’exemple précédent) n’est pas SOMME(C2:J2) mais SOMME(TChargesRepartition[@[Equipe 1]:[Equipe 8]]).Excel - MFC - Table

Cela rend la formule lisible et compréhensible.

Comment faire pour rendre les formules de mise en forme conditionnelle également lisibles ?

Pas de chance ! Les références structurées ne sont pas supportées par cette fonction ! (pas encore… j’espère que cela arrivera un jour !).

Mais, j’ai tout de même une petite astuce pour cela ! Cela s’appelle les “noms”. Oui, oui, les “noms”.

Excel - MFC - Les noms

Cela permet de donner un nom à une plage ou à une cellule. Ainsi, au lieu de désigner les cellules selon leur adresse (A1, K2, …), il est possible de donner un nom à une cellule et d’y faire référence dans les formules.

Reprenons le premier exemple de cet article avec la mise en évidence des doublons.

Excel - Mise en forme conditionnelle - TexteLa formule était : =SI(NB.SI($A$2:$A$19;$A$2)>1;VRAI;FAUX).

Vous pouvez sélectionner toutes les cellules contenant les n° de commandes (de A2 à A19) et leur donner un nom dans la zone nom directement.

Excel - MFC - Donner un nom

La zone nom correspond à cette zone où se trouve habituellement le nom d’une cellule. Ici, j’ai choisi le nom « NoCommandes ».

Ensuite, vous allez pouvoir changer votre formule dans la condition de mise en forme en :

=SI(NB.SI(NoCommandes;A2)>1;VRAI;FAUX)

Cela peut paraître anodin comme changement, mais je vous assure que lorsque vous avez des fichiers Excel complexes avec de très nombreuses mises en forme conditionnelles en place, identifier le périmètre concerné par une formule fait gagner beaucoup de temps !

Dernière action

Avez-vous trouvé la réponse à votre question ? En avez-vous d’autres ? Votre feedback compte pour moi. Un petit commentaire en dessous de l’article me confirmerait que vous avez lu jusqu’ici ! Merci !

>