Mise en forme conditionnelle dans Excel - en détail avec des exemples

Si vous avez besoin de présenter visuellement des informations, il est très pratique de le faire en utilisant une mise en forme conditionnelle. L'utilisateur qui utilise ce service économise énormément d'énergie et de temps. Pour obtenir les informations nécessaires, une vue rapide du dossier suffit.

Comment faire une mise en forme conditionnelle dans Excel

Vous pouvez trouver "Mise en forme conditionnelle" sur le tout premier onglet du ruban en allant dans la section "Styles". 

Ensuite, vous devez trouver l'icône de flèche un peu à droite avec votre œil, déplacez le curseur dessus. Ensuite, les paramètres s'ouvriront, où vous pourrez configurer de manière flexible tous les paramètres nécessaires. 

Ensuite, vous devez sélectionner l'opérateur approprié pour comparer la variable souhaitée avec un nombre. Il existe quatre opérateurs de comparaison : supérieur à, inférieur à, égal à et entre. Ils sont répertoriés dans le menu des règles. 

Ensuite, nous avons spécifié une série de nombres dans la plage A1:A11.

Formatons un ensemble de données approprié. Ensuite, nous ouvrons le menu des paramètres «Formatage conditionnel» et définissons les critères de sélection de données requis. Dans notre cas, par exemple, nous sélectionnerons le critère "Plus". 

Une fenêtre avec un ensemble d'options s'ouvrira. Dans la partie gauche de la fenêtre, il y a un champ dans lequel vous devez spécifier le nombre 15. Dans la partie droite, la méthode de mise en évidence des informations est indiquée, à condition qu'elle réponde au critère précédemment spécifié. Le résultat s'affichera immédiatement.

Ensuite, terminez la configuration avec le bouton OK.

A quoi sert la mise en forme conditionnelle ?

Dans une certaine mesure, on peut dire que c'est un outil, après avoir appris que travailler avec Excel ne sera plus jamais pareil. C'est parce que cela rend la vie beaucoup plus facile. Au lieu de définir manuellement le formatage d'une cellule qui correspond à chaque fois à une certaine condition, et d'essayer également de vérifier vous-même la conformité à ce critère, il vous suffit de définir les paramètres une fois, puis Excel fera tout lui-même.

Par exemple, vous pouvez faire en sorte que toutes les cellules contenant un nombre supérieur à 100 deviennent rouges. Ou, déterminez combien de jours il reste jusqu'au prochain paiement, puis coloriez en vert les cellules dans lesquelles la date limite n'est pas encore assez éloignée. 

Prenons un exemple.

Ce tableau montre l'inventaire qui est en stock. Il a des colonnes. en tant que produit, les ventes moyennes (mesurées en unités par semaine), le stock et le nombre de semaines restantes de ce produit.

De plus, la tâche du responsable des achats est de déterminer les postes dont le réapprovisionnement est nécessaire. Pour ce faire, vous devez regarder la quatrième colonne à partir de la gauche, qui enregistre le stock de marchandises par semaine.

Supposons que le critère de détermination d'une cause de panique soit inférieur à 3 semaines d'inventaire. Cela indique que nous devons préparer une commande. Si le stock de marchandises est inférieur à deux semaines, cela indique la nécessité de passer une commande de toute urgence. Si le tableau contient un grand nombre de postes, la vérification manuelle du nombre de semaines restantes est assez problématique. Même si vous utilisez la recherche. Et maintenant, voyons à quoi ressemble le tableau, qui met en évidence les biens rares en rouge.

Et en effet, il est beaucoup plus facile de s'y retrouver. 

Certes, cet exemple est pédagogique, ce qui est quelque peu simplifié par rapport à l'image réelle. Et les secondes et minutes économisées grâce à l'utilisation régulière de telles tables se transforment en heures. Maintenant, il suffit juste de regarder le tableau pour comprendre quels biens sont en pénurie, et ne pas passer des heures à analyser chaque cellule (s'il y a des milliers de ces articles de base).

S'il y a des produits «jaunes», vous devez commencer à les acheter. Si la position correspondante est rouge, vous devez le faire immédiatement. 

Par valeur d'une autre cellule

Examinons maintenant l'exemple pratique suivant.

Supposons que nous ayons une telle table et que nous soyons confrontés à la tâche de mettre en évidence les lignes contenant certaines valeurs. 

Donc, si le projet est toujours en cours d'exécution (c'est-à-dire qu'il n'est pas encore terminé, il est donc marqué de la lettre «P»), nous devons alors rendre son arrière-plan rouge. Les projets terminés sont marqués en vert.

Notre séquence d'actions dans cette situation sera la suivante :

1. Sélectionnez une plage de valeurs. 
2. Cliquez sur le bouton "Mise en forme conditionnelle" - "Créer une règle".
3. Veuillez noter que dans notre cas, vous devez appliquer la formule sous la forme d'une règle. Ensuite, nous utilisons la fonction IFpour mettre en surbrillance les lignes correspondantes. 

Ensuite, remplissez les lignes comme indiqué sur cette image.

Important! Les chaînes doivent être référencées de manière absolue. Si nous nous référons à une cellule, alors dans ce cas, elle est mixte (avec fixation de colonne).

De même, une règle est créée pour les projets de travail qui n'ont pas été achevés à ce jour. 

Voici à quoi ressemblent nos critères.

Et enfin, nous avons une telle base de données en conséquence.

Conditions multiples

Pour plus de clarté, prenons la plage A1:A11 du tableau pour montrer comment, dans un exemple pratique réel, vous pouvez utiliser plusieurs conditions pour déterminer la mise en forme des cellules.

Les conditions elles-mêmes sont les suivantes : si le nombre dans la cellule dépasse la valeur 6, il est alors surligné en rouge. S'il est vert, alors la couleur est verte. Et enfin, les plus grands nombres, plus de 20, seront surlignés en jaune. 

Il existe plusieurs façons dont la mise en forme conditionnelle est effectuée selon plusieurs règles.

1. Méthode 1. Nous sélectionnons notre plage, après quoi, dans le menu des paramètres de mise en forme conditionnelle, sélectionnez une règle de sélection de cellule telle que "Plus". Le chiffre 6 est écrit à gauche et le formatage est défini à droite. Dans notre cas, le remplissage doit être rouge. Après cela, le cycle est répété deux fois, mais d'autres paramètres sont déjà définis - plus de 10 et la couleur verte et plus de 20 et la couleur jaune, respectivement. Vous obtiendrez un tel résultat.
   

   

2. Méthode 2. Accédez au menu principal des paramètres de l'outil Excel "Formatage conditionnel". Nous y trouvons le menu "Créer une règle" et un clic gauche sur cet élément.
   

   

    Après cela, Ensuite, sélectionnez l'élément "Utiliser la formule ..." (mis en surbrillance sur la figure avec un cadre rouge) et définissez la première condition. Après cela, cliquez sur OK. Et puis le cycle est répété pour les conditions suivantes de la même manière que décrit ci-dessus, mais la formule est utilisée.

   

Il est important d'en tenir compte. Dans notre exemple, certaines cellules correspondent à plusieurs critères à la fois. Excel résout ce conflit comme suit : la règle ci-dessus est appliquée en premier. 

Et voici une capture d'écran pour mieux comprendre.

Par exemple, nous avons le nombre 24. Il remplit les trois conditions en même temps. Dans ce cas, il y aura un remplissage qui répond à la première condition, malgré le fait qu'il relève davantage de la troisième. Par conséquent, s'il est important que les nombres supérieurs à 20 soient remplis de jaune, la troisième condition doit être placée en premier lieu. 

Formatage de date conditionnel

Voyons maintenant comment fonctionne la mise en forme conditionnelle avec les dates. Nous avons une gamme tellement cool.

Dans ce cas, vous devez définir une règle de mise en forme conditionnelle telle que "Date".

Après cela, une boîte de dialogue s'ouvre avec tout un ensemble de conditions. Vous pouvez vous familiariser avec eux en détail dans cette capture d'écran (ils sont répertoriés sur le côté gauche de l'écran sous la forme d'une liste).

Il suffit de choisir celui qui convient et de cliquer sur le bouton "OK".

Nous voyons que les dates qui font référence à la dernière semaine au moment où cette plage a été tracée ont été surlignées en rouge. 

Utiliser des formules

L'ensemble des règles de mise en forme conditionnelle standard est assez important. Mais les situations sont différentes et la liste standard peut ne pas suffire. Dans ce cas, vous pouvez créer votre propre règle de mise en forme à l'aide d'une formule. 

Pour ce faire, cliquez sur le bouton "Créer une règle" dans le menu de mise en forme conditionnelle, puis sélectionnez l'élément affiché dans la capture d'écran.

Lignes (par valeur de cellule)

Supposons que nous devions mettre en surbrillance la ligne contenant une cellule avec une certaine valeur. Dans ce cas, nous devons effectuer la même séquence d'actions que ci-dessus, mais pour les chaînes. Autrement dit, vous pouvez choisir la plage à laquelle la mise en forme conditionnelle doit s'appliquer. 

Comment créer une règle

Pour créer une règle, vous devez utiliser l'option appropriée dans la section "Formatage conditionnel". Regardons une séquence spécifique d'actions. 

Vous devez d'abord trouver l'élément "Mise en forme conditionnelle" sur le ruban de l'onglet "Accueil". Il peut être reconnu par les cellules multicolores caractéristiques de différentes tailles avec une image à côté avec un signe égal barré. 

Il y a un bouton "Créer une règle". Vous pouvez également cliquer sur le bouton "Gérer les règles", après quoi une boîte de dialogue s'ouvrira, à travers laquelle vous pouvez également créer une règle en cliquant sur le bouton correspondant dans le coin supérieur gauche. 

Vous pouvez également voir dans cette fenêtre les règles qui ont déjà été appliquées.

Règles de sélection des cellules

D'accord, nous parlons tellement de la façon de créer des règles, mais qu'est-ce que c'est ? La règle de sélection de cellule fait référence aux caractéristiques prises en compte par le programme pour décider comment formater les cellules qui leur correspondent. Par exemple, les conditions peuvent être supérieures à un certain nombre, inférieures à un certain nombre, une formule, etc. Il y en a tout un ensemble. Vous pouvez vous familiariser avec eux et pratiquer leur utilisation "dans le bac à sable". 

Formater toutes les cellules en fonction de leurs valeurs

Une personne peut définir indépendamment la plage : une ligne entière, le document entier ou une seule cellule. Le programme ne peut se concentrer que sur la valeur d'une cellule. Cela ajoute de la flexibilité au processus. 

Mettre en forme uniquement les cellules uniques ou en double

Il existe un grand nombre de règles directement câblées dans le programme. Vous pouvez vous familiariser avec eux dans le menu "Formatage conditionnel". En particulier, pour que le formatage s'applique exclusivement aux valeurs en double ou uniquement aux valeurs uniques, il existe une option spéciale - "Valeurs en double".

Si vous sélectionnez cet élément, une fenêtre avec des paramètres s'ouvre, dans laquelle vous pouvez également choisir l'option opposée - ne sélectionner que des valeurs uniques. 

Valeurs de format dans la plage inférieure et supérieure à la moyenne

Pour formater les valeurs inférieures ou supérieures à la moyenne, il existe également une option spéciale dans le même menu. Mais vous devez sélectionner un autre sous-menu - "Règles de sélection des première et dernière valeurs". 

Formater uniquement les première et dernière valeurs

Dans le même sous-menu, il est possible de ne mettre en évidence que les première et dernière valeurs avec une couleur, une police et d'autres méthodes de formatage spéciales. Selon la norme, il est possible de sélectionner les dix premiers et derniers éléments, ainsi que 10 % du nombre total de cellules incluses dans cette plage. Mais l'utilisateur peut déterminer indépendamment le nombre de cellules qu'il doit sélectionner.

Formater uniquement les cellules avec un contenu spécifique

Pour formater uniquement les cellules avec un contenu spécifique, vous devez sélectionner la règle de formatage Égal à ou Le texte contient. La différence entre eux est que dans le premier cas, la chaîne doit correspondre complètement au critère, et dans le second, seulement partiellement. 

Comme vous pouvez le voir, la mise en forme conditionnelle est une fonctionnalité multifonctionnelle du programme Excel qui offre un grand nombre d'avantages à la personne qui la maîtrise. On sait qu'à première vue, tout cela peut sembler compliqué. Mais en fait, les mains sont attirées vers lui dès qu'il apparaît pour surligner un certain morceau de texte en rouge (ou le formater de toute autre manière), en fonction de son contenu ou selon un autre critère. Cette fonction est incluse dans l'ensemble des connaissances de base d'Excel, sans elle, même le travail amateur avec des feuilles de calcul et des bases de données est impossible.