La mise en forme conditionnelle avec Microsoft Excel

Réponses aux questions de ce chapitre:

■ Comment puis-je indiquer visuellement si les données de température récentes sont compatibles avec le réchauffement climatique?

■ Comment fonctionne la fonction de mise en forme conditionnelle Highlight Cells?

■ Comment puis-je vérifier ou personnaliser mes règles?

■ Comment fonctionnent les barres de données?

■ Comment Excel  traite-t-il les barres de données négatives?

■ Comment fonctionnent les échelles de couleurs?

■ Comment fonctionnent les jeux d’icônes?

■ Comment puis-je coder par couleur les retours de stock mensuels pour que chaque bon mois soit indiqué dans une couleur et chaque mauvais mois dans une autre?

■ Compte tenu des revenus trimestriels des entreprises, comment indiquer les trimestres au cours desquels les revenus ont augmenté par rapport au trimestre précédent dans une couleur et les trimestres au cours desquels les revenus ont diminué par rapport au trimestre précédent dans une autre couleur?

■ Étant donné une liste de dates, comment puis-je indiquer les dates du week-end dans une couleur spécifique?

■ Notre entraîneur de basket-ball a attribué à chaque joueur une note comprise entre 1 et 10 en fonction de sa capacité à jouer la garde, l’attaquant ou le centre. Puis-je créer une feuille de calcul qui indique visuellement la capacité de chaque joueur à jouer la position à laquelle il est affecté?

■ Que fait Stop Si vrai  dans la boîte de dialogue Manage Rules?

■ Comment puis-je utiliser le Reproducteur de formats pour copier un format conditionnel?

Utilisez la mise en forme conditionnelle pour spécifier la mise en forme d’une plage de cellules en fonction du contenu de la plage de cellules. Par exemple, étant donné les résultats des examens pour les étudiants, vous pouvez utiliser la mise en forme conditionnelle pour afficher les noms des étudiants en rouge qui ont une moyenne finale d’au moins 90. Fondamentalement, lorsque vous configurez des conditions pour formater une plage de cellules, Microsoft Excel 2013 vérifie chaque cellule de la plage pour déterminer si l’une des conditions que vous avez spécifiées (comme un score d’examen> 90) est remplie. Excel applique le format que vous avez choisi à toutes les cellules qui remplissent la condition. Si le contenu d’une cellule ne remplit aucune des conditions, le formatage de la cellule reste inchangé. Dans Excel 2007, mise en forme conditionnelle a été entièrement révisé et élargi. Excel 2010 a ajouté quelques améliorations mineures au formatage conditionnel. Dans ce chapitre, vous voyez comment utiliser toutes les fonctionnalités de mise en forme conditionnelle incluses dans Excel 2016.

Pour afficher les options de formatage conditionnel, sélectionnez d’abord la plage que vous souhaitez formater. Ensuite, sous l’onglet Accueil, dans le groupe Styles, cliquez sur la flèche de mise en forme conditionnelle (voir figure ci-dessous) pour ouvrir un menu d’options de mise en forme conditionnelle, comme illustré dans la figure ci-dessous 2.

FIGURE 1:Cette figure montre la commande de mise en forme conditionnelle.

FIGURE  2 : Voici les options de formatage conditionnel.

Voici une brève explication de chaque option:

■ Règles de mise en évidence des cellules Ces règles vous permettent d’attribuer un format aux cellules dont le contenu répond à l’un des critères suivants:

    • • Sont dans une plage numérique spécifique
    • • Faire correspondre une chaîne de texte spécifique
    • • Sont dans une plage spécifique de dates (par rapport à la date actuelle)
  • • Se produisent plusieurs fois (ou une seule fois) dans la plage sélectionnée

■ Règles haut / bas Utilisez ces règles pour attribuer un format à l’un des éléments suivants:

  • • N valeurs les plus grandes ou les plus petites d’une plage. Par exemple, N = 10 mettra en évidence les 10 valeurs les plus grandes ou les 10 plus petites d’une plage.
  • • Pourcentage supérieur ou inférieur de nombres dans une plage.
  • • Des nombres supérieurs ou inférieurs à la moyenne de tous les nombres d’une plage.

■ Barres de données, échelles de couleurs et jeux d’icônes Utilisez ces formats pour identifier facilement les valeurs grandes, petites ou intermédiaires dans la plage sélectionnée. Des barres de données plus grandes sont associées à de plus grands nombres. Avec les échelles de couleurs, vous pouvez, par exemple, faire apparaître des valeurs plus petites en rouge et des valeurs plus grandes en bleu avec une transition en douceur appliquée lorsque les valeurs de la plage passent de petite à grande. Avec les jeux d’icônes, vous pouvez utiliser jusqu’à cinq symboles pour identifier différentes plages de valeurs. Par exemple, vous pouvez afficher une flèche pointant vers le haut pour indiquer une grande valeur, pointant vers la droite pour indiquer une valeur intermédiaire et vers le bas pour indiquer une petite valeur.

■ Nouvelle règle Utilisez cette règle pour créer votre propre formule afin de déterminer si une cellule doit avoir un format spécifique. Par exemple, si une cellule dépasse la valeur de la cellule au-dessus d’elle, vous pouvez appliquer la couleur verte à la cellule. Si la cellule est la cinquième plus grande valeur de sa colonne, vous pouvez appliquer la couleur rouge à la cellule, etc.

■ Effacer les règles Utilisez ces règles pour supprimer tous les formats conditionnels que vous avez créés pour une plage sélectionnée ou pour la feuille de calcul entière.

■ Gérer les règles Utilisez ces règles pour afficher, modifier et supprimer des règles de mise en forme conditionnelle; créer de nouvelles règles; ou modifiez l’ordre dans lequel Excel applique les règles de mise en forme conditionnelle que vous avez définies.

Réponses aux questions de cet article 

Cette section fournit les réponses aux questions répertoriées au début de cet article.

Comment puis-je indiquer visuellement si les données de température récentes sont compatibles avec le réchauffement climatique?

Cette question offre une occasion parfaite d’appliquer la  Règles de mise en forme conditionnelle haut / bas.

Le fichier  (la figure ci-dessous) montre un sous-ensemble de ces données) contient les températures mondiales moyennes pour les années 1881 à 2011. Si le réchauffement climatique s’est produit, vous vous attendez à ce que les chiffres de ces dernières années soient plus importants que les chiffres précédents. années. Pour déterminer si les dernières années sont plus chaudes, essayez de mettre en surbrillance les 20 années les plus chaudes en rouge. Sélectionnez la plage F5: F135 contenant les températures. Sous l’onglet Accueil, dans le groupe Styles, cliquez sur Mise en forme conditionnelle, puis sélectionnez Règles haut / bas. Sélectionnez les 10 meilleurs éléments et remplissez la boîte de dialogue comme illustré à la figure ci-dessous.

Sélectionnez maintenant la plage G5: G135, revenez aux règles Haut / Bas et sélectionnez Top 10%. Accepter la sélection par défaut de 10 met en évidence les 10% supérieurs des températures en rouge et les 10% inférieurs en vert. Notez que toutes les années dans les 10% inférieurs étaient 1918 ou plus tôt, et toutes les années dans les 10% supérieurs étaient 1997 ou plus tard. Enfin, dans la colonne H, mettez en surbrillance les températures supérieures à la moyenne en vert et les températures inférieures à la moyenne en rouge. Il convient de noter que toutes les années antérieures à 1936 avaient des températures inférieures à la moyenne, tandis que toutes les années après 1976 avaient des températures supérieures à la moyenne. Le formatage conditionnel est un outil puissant qui peut être utilisé pour démontrer que la terre (pour quelque raison que ce soit) semble être devenue plus chaude récemment.

FIGURE 3: Utilisez la mise en forme conditionnelle pour implémenter les règles haut / bas.

Comment fonctionne la fonction de mise en forme conditionnelle Cellules en surbrillance?

Le fichier (voir figure ci-dessous) montre comment la fonction Cellules en surbrillance est utilisée. Par exemple, supposons que vous souhaitiez mettre en surbrillance tous les noms en double dans C2: C11 en rouge. Sélectionnez simplement la plage de cellules C2: C11, choisissez Mise en forme conditionnelle dans le groupe Styles de l’onglet Accueil, choisissez Règles de cellule en surbrillance et valeurs en double, puis choisissez Remplissage rouge clair avec du texte rouge foncé. Cliquez sur OK pour appliquer la règle afin que tous les noms apparaissant plusieurs fois (John et Josh) soient mis en évidence en rouge.

FIGURE 4: Utiliser les règles de surbrillance des cellules.

Supposons maintenant que vous souhaitiez mettre en surbrillance en rouge toutes les cellules de la plage D2: D11 contenant le texte “Eric”. Sélectionnez la plage de cellules D2: D11, cliquez sur Mise en forme conditionnelle, choisissez Règles de cellules en surbrillance, puis sélectionnez Texte qui contient. Tapez Eric dans la case de gauche et choisissez Remplir rouge clair avec du texte rouge foncé sur la droite. Comme le montre la figure ci-dessus, Eric et Erica sont mis en évidence. (Erica contient la chaîne de texte “Eric”.)

Supposons que vous ayez une liste de dates (comme dans E2: E11) et que vous souhaitiez mettre en évidence en rouge toute cellule contenant la date d’hier et toute autre date des sept derniers jours. (Voir la feuille de calcul Right Way dans le fichier Highlightcells.xlsx.) Supposons (comme dans la figure 23-5) que la date actuelle est le 19 juillet 2013. Notez que la cellule E3 contient la formule = AUJOURD’HUI() – 1, donc la cellule E3 affiche toujours la date d’hier. La cellule E4 contient la formule = AUJOURD’HUI () – 5.

Commencez par sélectionner la plage de cellules que vous souhaitez formater (E2: E11). Sélectionnez Une date se produisant, puis sélectionnez Hier avec un remplissage vert avec du texte vert foncé. Cliquez sur Mise en forme conditionnelle et règles de mise en surbrillance des cellules, puis choisissez à nouveau A Date Occurrence. Dans la boîte de dialogue Une date se produisant, sélectionnez Dans les 7 derniers jours et Remplissez rouge clair avec du texte rouge foncé. Les règles de formatage créées ultérieurement ont priorité sur les règles créées précédemment (sauf si vous modifiez l’ordre de priorité, comme expliqué dans la section suivante de ce cet article). Cela explique pourquoi le 18/07/2013 est formaté en rouge plutôt qu’en vert.

Comment puis-je vérifier ou personnaliser mes règles?

Après avoir créé des règles de mise en forme conditionnelle, vous pouvez afficher vos règles en choisissant Gérer les règles dans le menu Mise en forme conditionnelle. Par exemple, sélectionnez les dates dans E2: E11, cliquez sur Mise en forme conditionnelle et choisissez Gérer les règles; vous voyez les règles affichées dans la figure ci-dessous. Vous pouvez voir que la règle de mise en forme des 7 derniers jours sera appliquée avant la règle de mise en forme d’hier. En effet, le format placé dans une cellule est basé sur la première règle correspondante.

FIGURE 5: Cette figure montre la boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle.

Dans la boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle, vous pouvez effectuer les opérations suivantes:

■ Créez une règle en cliquant sur le bouton Nouvelle règle.

■ Modifiez ou modifiez une règle en cliquant sur le bouton Modifier la règle.

■ Supprimez une règle en la sélectionnant, puis en cliquant sur le bouton Supprimer la règle.

■ Modifiez l’ordre de priorité en sélectionnant une règle, puis en cliquant sur la flèche vers le haut ou vers le bas.

Pour illustrer l’utilisation de la boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle, copiez la feuille de calcul précédente (cliquez avec le bouton droit sur l’onglet de la feuille de calcul, cliquez sur Déplacer ou copier, puis activez la case à cocher Créer une copie) dans la feuille de calcul Right Way de   la le fichier Highlightcells.xlsx. Sélectionnez la règle d’hier et cliquez sur la flèche vers le haut. La règle d’hier a désormais une priorité plus élevée que la règle des 7 derniers jours, donc E3 sera formaté en vert et non en rouge. La figure ci-dessous montre à quoi ressemble la boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle, et la figure 23-8 montre que la cellule E3 est verte et la cellule E4 est rouge comme vous le vouliez.

FIGURE 6: Maintenant, Hier a une priorité plus élevée que les 7 derniers jours.

FIGURE 7 : Après avoir modifié la priorité des règles, hier est désormais formaté en vert.

Comment fonctionnent les barres de données?

Lorsque vous avez une longue liste de chiffres, il est bon d’avoir un indicateur visuel qui vous permet d’identifier facilement les grands et les petits nombres. Les barres de données, les échelles de couleurs et les jeux d’icônes (tous introduits dans Excel 2007) sont des outils parfaits pour afficher les différences dans une liste de nombres.

La figure ci-dessous(voir le fichier ) montre l’utilisation des barres de données. Commencez par appliquer les barres de données par défaut aux données de D6: D15. Sélectionnez les données dans D6: D15, cliquez sur Mise en forme conditionnelle, puis choisissez Barres de données. Sélectionnez les barres de données bleues et choisissez Remplissage dégradé pour créer le format indiqué dans la colonne D de la figure ci-dessous. Les cellules contenant un plus grand nombre contiennent des barres bleues plus longues. L’option par défaut consiste à associer la barre de données la plus courte au plus petit nombre dans la plage sélectionnée et la barre de données la plus longue au plus grand nombre. Comme vous pouvez le voir dans la colonne D, la taille des barres de données est directement proportionnelle à la valeur des données; c’est-à-dire que la barre de données pour 4 est deux fois plus grande que la barre de données pour 2, et ainsi de suite.

FIGURE8 : Distinguez visuellement les valeurs numériques à l’aide de barres de données.

Si, après avoir cliqué sur Barres de données, vous choisissez Plus de règles, la boîte de dialogue Nouvelle règle de formatage illustrée à l’une des  figure s’ouvre. (Vous pouvez également afficher cette boîte de dialogue en choisissant Gérer les règles et Modifier la règle ou en double-cliquant sur une règle.) Dans cette boîte de dialogue, vous pouvez modifier les critères utilisés pour n’affecter aucune barre de données et les barres de données les plus longues aux cellules. Dans E6: E15, n’attribuez aucune barre de données aux nombres inférieurs ou égaux à 3 et la barre la plus longue à tout nombre supérieur ou égal à 8. Comme le montre la figure ci-dessus, tous les nombres de la colonne E qui sont inférieurs ou égaux à 3 n’ont pas de barre de données, tous les nombres supérieurs ou égaux à 8 ont la barre la plus longue et les nombres entre 3 et 8 ont des barres graduées. Notez que dans la boîte de dialogue Modifier la règle de formatage, vous pouvez sélectionner la case à cocher Afficher la barre uniquement pour afficher uniquement la barre de couleur et non la valeur de cellule dans les cellules au format conditionnel.

FIGURE 9:Vous pouvez personnaliser vos barres de données à l’aide de cette boîte de dialogue.

Ensuite, dans la colonne F, n’attribuez aucune barre aux nombres dans les 20% inférieurs de la plage F6: F15 et la barre la plus longue aux nombres dans les 20% supérieurs. En d’autres termes, tous les nombres <= 1 + .2 (21 – 1) = 5 n’ont pas de barre de données, et tous les nombres> = 1 + .8 (21 – 1) = 17 ont la barre de données la plus longue. La figure 23-9 montre que dans la colonne F, les cinq premiers nombres n’ont pas de barre de données, et les nombres 17 et 21 ont la barre de données la plus longue.

Dans la plage de cellules G6: G13, n’associez aucune barre de données à tous les nombres au niveau ou au-dessous du vingtième centile des données (3) et associez la barre de données la plus longue à tous les nombres au niveau ou au-dessus du quatre-vingtième centile des données (17) . Dans la liste Direction des barres, vous pouvez spécifier si vos barres de données commencent au bord droit ou gauche de la cellule.

Excel  permet un ombrage solide pour les barres de données et vous permet d’orienter les barres de données associées aux valeurs négatives afin qu’elles s’exécutent dans une direction opposée aux valeurs positives. Après avoir sélectionné Valeurs négatives et Axe dans la boîte de dialogue Modifier la description de la règle, vous pouvez déplacer l’axe de la position normale au centre de la cellule ou ouvrir les barres de données négatives vers la gauche (par défaut) ou vers la droite. Le fichier et la figure 11 montrent comment des barres de données solides apparaissent pour un ensemble de données contenant des nombres négatifs. Les deux dernières colonnes de la figure 11 utilisent le paramètre Axe automatique, qui alloue plus d’espace aux entrées positives car elles sont plus grandes que les valeurs négatives.

FIGURE 10: Cette figure montre des barres de données pour les valeurs négatives avec à la fois solide et dégradé shading.

Comment fonctionnent les échelles de couleurs?

Utilisez des échelles de couleurs pour résumer certains ensembles de données. Comme les règles de cellule de surbrillance, une échelle de couleurs utilise l’ombrage des cellules pour afficher les différences de valeurs de cellule. Cette section décrit un exemple d’échelle à trois couleurs. (Le fichier  et la figure ci-dessous illustrent l’utilisation d’une échelle à trois couleurs. Notez que les lignes 19 à 75 sont masquées; pour les afficher, sélectionnez les lignes 18 et 76, cliquez avec le bouton droit sur la sélection, puis cliquez sur Afficher. ) Sélectionnez les rendements annuels des actions, des bons du Trésor et des bons du Trésor dans les cellules B8: D89. Choisissez Mise en forme conditionnelle, Échelles de couleurs, puis Autres règles pour afficher la boîte de dialogue Modifier la règle de mise en forme, qui est remplie comme illustré à la figure suivante.

FIGURE 11 : Cette figure montre des échelles en trois couleurs.

Choisissez la couleur rouge pour indiquer le retour le plus bas, verte pour indiquer le retour le plus élevé et orange pour indiquer le retour au milieu. Étonnamment, Excel apporte de petites modifications à l’ombrage des couleurs de chaque cellule en fonction de la valeur dans la cellule. Dans la colonne B de la figure ci-dessus, le rendement le plus bas est ombré en rouge. A noter que 1931 et 2008 (comme nous le savons tous) ont été de très mauvaises années pour les stocks. À mesure que les retours approchent du cinquantième centile, la couleur des cellules devient progressivement jaune. Ensuite, à mesure que les rendements augmentent du cinquantième centile vers le plus grand retour, la couleur des cellules passe du jaune au vert. La plupart des globules verts et rouges sont associés aux stocks car les rendements annuels des stocks sont plus variable que les rendements des obligations ou des bons du Trésor. Cette variabilité entraîne des retours de stocks, grands et petits, assez fréquents. Pratiquement tous les rendements annuels des bons du Trésor ou des bons du Trésor sont jaunes, car la faible variabilité des rendements annuels de ces investissements signifie que les rendements intermédiaires se produisent la plupart du temps.

FIGURE 12 : Personnalisez une échelle à trois couleurs.

Certaines échelles bicolores ont été créées dans le fichier Scalesiconsdatabars.xlsx illustré à la figure ci-dessous.

Pour ce faire, sélectionnez la plage de cellules; cliquez sur Mise en forme conditionnelle et choisissez Échelles de couleurs. Vous pouvez sélectionner la combinaison de couleurs souhaitée dans la liste donnée ou créer la vôtre en choisissant Plus de règles.

Vous pouvez choisir une échelle à deux couleurs qui indique des valeurs inférieures en blanc et des valeurs supérieures en bleu foncé.

■ Dans la plage de cellules D19: D28, choisissez de rendre la valeur la plus basse blanche et la valeur la plus élevée bleue. À mesure que le nombre augmente, l’ombrage des cellules devient plus sombre.

■ Dans la plage E19: E28, choisissez de rendre les valeurs inférieures ou égales à 3 blanches et les valeurs supérieures ou égales à 8 bleues. Pour les nombres entre 3 et 8, à mesure que les nombres augmentent, l’ombrage des cellules devient plus sombre.

■ Dans la plage F19: F28, choisissez de rendre les valeurs dans les 20% inférieurs de la plage blanche et les nombres dans les 20% supérieurs bleus. Pour les nombres dans les 60 pour cent du milieu, l’ombrage des cellules devient plus sombre lorsque les nombres augmentent.

FIGURE 13 : Cette figure montre des échelles bicolores.

Comment fonctionnent les jeux d’icônes?

Vous pouvez également afficher les différences numériques en utilisant des jeux d’icônes. (Voir la figure ci-dessous et le fichier .) Un jeu d’icônes se compose de trois à cinq symboles. Vous définissez des critères pour associer une icône à chaque valeur d’une plage de cellules. Par exemple, vous pouvez utiliser une flèche vers le bas pour les petits nombres, une flèche vers le haut pour les grands nombres et une flèche horizontale pour les valeurs intermédiaires. La plage de cellules E32: F41 contient deux illustrations de l’utilisation des jeux d’icônes. Pour chaque colonne, vous pouvez utiliser la flèche rouge vers le bas, la flèche horizontale jaune et la flèche verte vers le haut, par exemple.

FIGURE 14 : Cette figure montre comment les jeux d’icônes avec des flèches sont utilisés pour résumer les données.

Vous pouvez affecter des icônes à une plage de valeurs numériques comme suit:

  1. 1. Après avoir sélectionné les nombres dans E32: E41, choisissez Mise en forme conditionnelle et jeux d’icônes;

choisissez Plus de règles et le jeu d’icônes 3 flèches (colorées) dans la liste Styles d’icônes. Dans la colonne E, vous voulez que les nombres inférieurs à 4 pour afficher une flèche vers le bas, les nombres de 4 à 8 pour afficher une flèche horizontale et les nombres 8 ou plus pour afficher une flèche vers le haut. Pour atteindre cet objectif, définissez les options dans la boîte de dialogue Modifier la règle de formatage, comme illustré à la figure  ci-dessous.

FIGURE 16 : Attribuez des icônes à des valeurs numériques.

  1. 2. De la même manière, créez une règle de matage pour F31: F42 de sorte que les flèches vers le haut soient placées dans les cellules contenant des nombres dans le quatre-vingtième centile ou au-dessus de toutes les valeurs (> = 8), et vers le bas les flèches sont placées dans des cellules contenant des nombres dans les 20% inférieurs de toutes les valeurs (<= 1). La figure ci-dessous affiche les paramètres de formatage configurés.

FIGURE 17 :  Attribuez des icônes aux valeurs de centile.

Les paramètres facultatifs incluent Inverser l’ordre des icônes, qui associe les icônes de gauche à de petits nombres et les icônes de droite à de plus grands nombres, et Afficher l’icône uniquement, qui masque le contenu de la cellule. Dans Excel , vous pouvez masquer un sous-ensemble d’icônes. Vous pouvez également personnaliser les jeux d’icônes.

Dans la feuille de calcul Personnaliser les icônes du fichier , vous pouvez utiliser trois icônes pour résumer les retours sur investissement. Le tiers supérieur des retours apparaît avec une flèche grise vers le haut; le tiers inférieur des déclarations contient une flèche rouge vers le bas. Vous pouvez masquer l’icône de flèche fiat pour le tiers médian des retours. La figure ci-dessus montre les icônes résultantes et la figure  ci-dessous montre la boîte de dialogue (accessible depuis Gérer les règles) qui crée cette mise en forme. Vous pouvez personnaliser le jeu d’icônes à trois flèches grises en remplaçant la flèche grise vers le bas par une flèche rouge.

FIGURE 18 Cette figure montre un ensemble d’icônes personnalisé avec une icône cachée.

FIGURE 19:Cette figure montre la boîte de dialogue de personnalisation et de masquage des icônes.

Comment puis-je coder par couleur les retours de stock mensuels pour que chaque bon mois soit indiqué dans une couleur et chaque mauvais mois dans une autre?

Le fichier , illustré à la figure 20 ci-dessous, contient les valeurs mensuelles et les rendements de l’indice boursier Standard & Poor’s. Supposons que vous souhaitiez surligner chaque mois en vert dans lequel l’indice S&P a augmenté de plus de 3% et surligner chaque mois en rouge dans lequel il a baissé de plus de 3%.

Commencez par passer à la cellule C10 (le premier mois contenant une déclaration S&P) et sélectionnez toutes les déclarations mensuelles en appuyant sur Ctrl + Maj + flèche vers le bas. Choisissez Mise en forme conditionnelle, Gérer les règles et Nouvelle règle et sélectionnez Formater uniquement les cellules qui contiennent. Remplissez la boîte de dialogue comme illustré à la figure 21. Vous pouvez indiquer à Excel de formater les cellules de la plage sélectionnée qui sont> 0,03 et, après avoir cliqué sur Format, sélectionnez le format à utiliser (un remplissage vert).

Notez que les listes de polices et de taille de police ne sont pas disponibles, donc votre choix de formatage ne peut pas modifier ces attributs. Vous pouvez utiliser l’onglet Remplir pour ombrer les cellules dans la couleur de votre choix et utiliser l’onglet Bordures pour créer une bordure pour les cellules qui répondent à vos critères conditionnels. Après avoir cliqué sur OK dans la boîte de dialogue Format de cellule, vous revenez à la boîte de dialogue Mise en forme conditionnelle. Vous pouvez maintenant sélectionner à nouveau Nouvelle règle et, de la même manière, configurer les choses de sorte que toutes les cellules contenant des nombres inférieurs à -0,03 soient remplies en rouge. (Voir figure ci-dessous.)

Lorsque vous cliquez sur OK, tous les mois avec un retour S&P supérieur à 3% (voir la cellule C23, par exemple) sont affichés en vert, et tous les mois avec un retour S&P inférieur à -3% (voir la cellule C18) sont affichés dans rouge. Les cellules dans lesquelles les déclarations mensuelles ne remplissent aucune de ces conditions conservent leur formatage d’origine.

FIGURE 20  : Le formatage conditionnel met en évidence les rendements de l’indice boursier S&P.

FIGURE 21: Appliquer une mise en forme spéciale aux retours S&P supérieurs à 3%.

FIGURE 22 : Le stock de couleurs renvoie moins de 3% en rouge et plus de 3% en vert.

Voici quelques conseils utiles concernant la mise en forme conditionnelle:

■ Pour supprimer la mise en forme conditionnelle (ou tout autre format) appliquée à une plage de cellules, sélectionnez simplement la plage de cellules, cliquez sur Mise en forme conditionnelle, choisissez Effacer les règles, puis choisissez Effacer les règles des cellules sélectionnées.

■ Pour sélectionner toutes les cellules d’une feuille de calcul auxquelles la mise en forme conditionnelle s’applique, appuyez sur F5 pour ouvrir la boîte de dialogue Atteindre. Dans la boîte de dialogue, cliquez sur Spécial, sélectionnez Formats conditionnels, puis cliquez sur OK.

■ Si vous souhaitez modifier une règle de mise en forme conditionnelle, choisissez Gérer les règles, puis double-cliquez sur la règle ou cliquez sur Modifier les règles.

■ Vous pouvez supprimer une règle de mise en forme conditionnelle spécifique en sélectionnant Gérer les règles, en sélectionnant la règle, puis en choisissant Supprimer la règle.

Notez qu’après la définition des deux règles, la règle de formatage rouge est répertoriée en premier (car elle a été créée plus récemment que la règle de formatage verte). Dans la boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle, les règles sont répertoriées par ordre de priorité. Dans cet exemple, peu importe la règle qui est répertoriée en premier car aucune cellule ne peut satisfaire aux critères des deux règles. En cas de conflit de règles,     la règle listée en premier est prioritaire. Pour modifier l’ordre des règles de mise en forme conditionnelle, sélectionnez la règle et cliquez sur la flèche vers le haut pour déplacer la règle vers le haut ou la flèche vers le bas pour déplacer la règle vers le bas dans l’ordre de priorité.

Compte tenu des revenus trimestriels des entreprises, comment puis-je indiquer les trimestres au cours desquels les revenus ont augmenté par rapport au trimestre précédent dans une couleur et les trimestres au cours desquels les revenus ont diminué par rapport au trimestre précédent dans une autre couleur?

Le fichier  contient les revenus trimestriels (en millions) pour Amazon.com de 1995 à 2009. (Voir la figure 23.) Vous souhaitez mettre en évidence les trimestres au cours desquels les revenus ont augmenté au cours du trimestre précédent en vert et mettre en évidence les trimestres en dont les revenus ont diminué par rapport au trimestre précédent en rouge.

Utiliser une formule dans la boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle vous permet de spécifier une formule qui définit les conditions qu’Excel vérifie avant d’appliquer la mise en forme à une cellule. Cette section utilise cette option, mais avant d’utiliser cette option de formule, regardez comment Excel évalue certaines fonctions logiques. Le travail se trouve dans le fichier .

FIGURE 23 : Mettez en évidence l’augmentation des ventes en vert et la baisse des ventes en rouge.

Que se passe-t-il lorsque vous tapez une formule telle que = B3 <2 dans la cellule B4?

Si la valeur dans B3 est un nombre inférieur à 2, Excel renvoie la valeur True dans la cellule B4; sinon, Excel renvoie False. Vous pouvez vous référer au fichier , illustré à la figure ci-dessous, pour d’autres exemples comme celui-ci, y compris les combinaisons de ET, OU et NON dans les formules.

■ Dans la cellule B6, la formule = OU (B3 <3, C3> 5) renvoie la valeur True si l’une des conditions, B3 <3 ou C3> 5, est vraie. Étant donné que la valeur de C3 est supérieure à 5, Excel renvoie Vrai.

■ Dans la cellule B7, la formule = ET (B3 = 3, C3> 5) renvoie Vrai si B3 = 3 et C3> 5. Parce que B3 n’est pas égal à 3, Excel renvoie False. Dans la cellule B8, cependant, la formule = ET (B3> 3, C3> 5) renvoie Vrai car B3> 3 et C3> 5 sont toutes les deux vraies.

■ Dans la cellule B9, la formule = NON (B3 <2) renvoie Vrai car B3 <2 renverrait False et une valeur non-false devient Vrai.

FIGURE 24 : Cette figure montre les fonctions logiques.

Maintenant, regardez comment Utiliser une formule vous permet de créer un format conditionnel dans une plage de cellules. Commencez par sélectionner la plage de cellules à laquelle vous souhaitez appliquer un format conditionnel. Cliquez sur Mise en forme conditionnelle et choisissez Gérer les règles pour ouvrir la boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle. Choisissez Nouvelle règle, puis sélectionnez Utiliser une formule pour déterminer les cellules à formater (la dernière option, également appelée option de formule). Pour utiliser l’option de formule, vous entrez une formule (la formule doit commencer par un signe égal) qui est vraie si et seulement si vous voulez que la cellule dans le coin supérieur gauche soit affectée au format choisi. Votre formule logique peut être copiée comme une formule ordinaire dans le reste de la plage sélectionnée, utilisez donc judicieusement les signes dollar ($) pour vous assurer que pour chaque cellule de la plage sélectionnée, la formule sera vraie si et seulement si vous voulez que votre format soit appliquer à la cellule. Choisissez Format, puis entrez la mise en forme souhaitée. Cliquez sur OK. Après avoir cliqué sur OK dans la boîte de dialogue Mise en forme conditionnelle, votre formule et la mise en forme sont copiées dans toute la plage de cellules. La mise en forme est appliquée à n’importe quelle cellule de la plage sélectionnée qui satisfait la condition définie dans la formule.

Revenez maintenant au fichier  et vous mettrez en surbrillance en vert les trimestres où les revenus augmentent. Sélectionnez la plage E6: E61 (il n’y a pas de trimestre précédent auquel vous pouvez comparer le chiffre d’affaires dans la cellule E5), puis demandez à Excel de mettre en surbrillance la valeur d’une cellule en vert si elle est supérieure à la valeur de la cellule au-dessus. La figure ci-dessous ​​montre comment configurer la règle.

Si vous entrez = E6> E5 en pointant sur les cellules appropriées, veillez à supprimer les signes $ de la formule dans la boîte de dialogue Mise en forme conditionnelle, sinon la formule ne sera pas copiée. La façon la plus simple d’insérer ou de supprimer des signes dollar est probablement d’utiliser la touche F4. Lorsque vous mettez en surbrillance une référence de cellule telle que A3, appuyez sur F4 inclut ou supprime les signes dollar dans l’ordre suivant: A3, $ A $ 3, A $ 3, $ A3. Ainsi, si vous commencez avec $ A $ 3, appuyez sur F4 pour changer la référence de cellule en A $ 3. La formule de cet exemple garantit que la cellule E6 est colorée en vert si et seulement si les ventes de ce trimestre dépassent le trimestre précédent. Après avoir cliqué sur OK, vous constaterez que tous les trimestres dans lesquels les revenus ont augmenté sont de couleur verte. Dans la cellule E7, par exemple, la formule a été copiée de la manière habituelle, devenant = E7> E 6.

FIGURE 25 : ​​Cette figure montre les paramètres de mise en forme conditionnelle qui afficheront en vert les trimestres au cours desquels les revenus ont augmenté.

Pour ajouter la condition de mise en forme des cellules dans lesquelles les revenus ont diminué, sélectionnez à nouveau la plage E6: E61, ouvrez la boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle, choisissez Nouvelle règle, puis sélectionnez Utiliser une formule pour déterminer les cellules à formater. Entrez la formule = E6 <E5, puis cliquez sur Format. Sous l’onglet Remplissage, changez la couleur de remplissage en rouge, puis cliquez deux fois sur OK. La boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle s’ouvre, comme illustré à la figure .

FIGURE 26: Ces conditions afficheront les trimestres au cours desquels les revenus ont augmenté en vert et les trimestres au cours desquels les revenus ont diminué en rouge.

Vous pouvez utiliser l’option de formule avec des échelles de couleurs, des barres de données et des jeux d’icônes. Sélectionnez l’option lors de la définition des critères pour votre échelle de couleurs, vos barres de données ou vos jeux d’icônes.

Étant donné une liste de dates, comment puis-je indiquer les dates du week-end dans une couleur spécifique?

Le fichier  (voir figure ci-dessous) contient plusieurs dates. Vous souhaitez mettre en surbrillance tous les samedis et dimanches en rouge. Pour ce faire, copiez la formule JOURSEM (C6,2) de la cellule D6 à D7: D69. Choisir Type = 2 pour la fonction JOURSEM renvoie un 1 pour chaque lundi, un 2 pour chaque mardi, etc., de sorte que la fonction renvoie un 6 pour chaque samedi et un 7 pour chaque dimanche.

FIGURE 27:  Utilisez la fonction JOURSEM pour mettre en surbrillance les jours de week-end en rouge.

Sélectionnez la plage D6: D69, cliquez sur Mise en forme conditionnelle, puis choisissez Gérer les règles. Cliquez sur Nouvelle règle et sur l’option de formule et remplissez la boîte de dialogue comme illustré à la figure ci-dessous.

FIGURE 28 : Utilisez la configuration de la boîte de dialogue Modifier la règle de formatage pour afficher les jours de week-end en rouge.

Après avoir cliqué sur OK, chaque date dont le jour de la semaine est égal à 6 (pour samedi) ou 7 (pour dimanche) est colorée en rouge. La formule = OU (D6 = 6, D6 = 7) implique qu’une entrée de cellule de 6 ou 7 activera la couleur de police rouge. Vous auriez également pu utiliser Format uniquement les cellules qui contiennent et> = 6 ou> 5 pour obtenir le même formatage.

Notre entraîneur de basket-ball a attribué à chaque joueur une note comprise entre 1 et 10 en fonction de sa capacité à jouer la garde, l’attaquant ou le centre.Puis-je créer une feuille de calcul qui indique visuellement la capacité de chaque joueur à jouer la position à laquelle il est affecté?

Le fichier Basketball.xlsx, illustré à la figureci-dessous contient les notes attribuées à 20 joueurs pour chaque position et la position (1 = garde, 2 = avant, 3 = centre) jouée par chaque joueur. Le but ici est de remplir en rouge la cellule contenant la note de chaque joueur pour le poste auquel elle est affectée.

FIGURE 29 : Cette feuille de travail évalue la capacité de chaque joueur à jouer une position.

Commencez par sélectionner la gamme C3: E22, qui contient les notes des joueurs. Cliquez sur Mise en forme conditionnelle et choisissez Gérer les règles.

Choisissez Nouvelle règle, puis choisissez l’option de formule. Remplissez maintenant la boîte de dialogue comme illustré à la figure ci-dessous.

La formule = $ A3 = C $ 1 compare la position assignée du joueur à l’en-tête de colonne (1, 2 ou 3) de la ligne 1. Si la position assignée du joueur est définie sur 1 (Garde), sa note dans la colonne C, qui est sa cote de garde, apparaît en rouge. De même, si la position assignée au joueur est définie sur 2, sa note en avant dans la colonne D apparaît en rouge. Enfin, si la position attribuée est définie sur 3, sa note centrale dans la colonne E apparaît en rouge. Notez que si vous n’incluez pas le signe dollar avec le A et le 1 dans la formule, il ne sera pas copié correctement vers le bas et entre.

FIGURE 30: Utilisez la configuration de la boîte de dialogue Modifier la règle de formatage pour afficher les notes des joueurs en rouge.

Notez également qu’Excel  autorise les formats conditionnels créés avec des formules pour référencer les données dans d’autres feuilles de calcul.

À quoi sert l’option Arrêter si vrai dans la boîte de dialogue Gérer les règles?

Supposons que Stop si vrai soit sélectionné pour une règle. Si une cellule satisfait à cette règle, toutes les règles de précédent inférieur sont ignorées. Utilisez le fichier pour illustrer l’utilisation de Stop si vrai. Ce fichier affiche le revenu médian de chaque État américain pour les années comprises entre 1984 et 2010. Supposons (comme illustré à la figure ) que vous souhaitez afficher des flèches vers le haut pour les 10 États qui ont le revenu médian le plus élevé en 2010 et aucune icône n’apparaît pour tout autre état. La clé est de faire de la première règle aucun format pour les états     avec les 40 revenus médians les plus bas, puis pour sélectionner Stop si Vrai. Créez ensuite le jeu d’icônes souhaité.

Les paramètres qui masquent les flèches pour les 40 États ayant les 40 revenus médians les plus bas en 2010 sont illustrés à la figure ci-dessous.

FIGURE 31: Utilisez les flèches vers le haut pour mettre en évidence les 10 États ayant le revenu médian le plus élevé.

FIGURE 32 : Cette figure montre les paramètres qui garantissent que seuls les 10 premiers revenus médians sont mis en évidence avec des flèches vers le haut.

Les paramètres de la deuxième règle sont illustrés à la figure ci-dessous. Notez que les 20% des États les plus importants impliquent 0,2 * 50 = 10 États. Les paramètres restants sont sans importance.

FIGURE 33: Cette figure montre les paramètres qui garantissent que les 10 premiers états du revenu médian ont une flèche vers le haut. Comment puis-je utiliser le Reproduire la mise en forme pour copier un format conditionnel?

L’icône Format Painter (voir le pinceau illustré à la figure ci-dessous) vous permet de peindre le format (y compris les formats conditionnels) de n’importe quelle cellule ou groupe de cellules à n’importe quel groupe de cellules. Sélectionnez la cellule ou le groupe de cellules au format que vous souhaitez copier et cliquez sur l’icône Reproduire la mise en forme. Utilisez le pinceau pour sélectionner les cellules dans lesquelles vous souhaitez copier le format.

FIGURE 34 : Cette figure montre les paramètres qui garantissent que les 10 premiers états du revenu médian ont une flèche vers le haut. Comment puis-je utiliser le Reproduire la mise en forme pour copier un format conditionnel?

L’icône Format Painter (voir le pinceau illustré à la figure ci-dessous) vous permet de peindre le format (y compris les formats conditionnels) de n’importe quelle cellule ou groupe de cellules à n’importe quel groupe de cellules. Sélectionnez la cellule ou le groupe de cellules au format que vous souhaitez copier et cliquez sur l’icône Reproduire la mise en forme. Utilisez le pinceau pour sélectionner les cellules dans lesquelles vous souhaitez copier le format.

FIGURE 35 : Cette figure montre l’icône Reproduire la mise en forme.

Si vous souhaitez copier un format dans une plage qui inclut des cellules non adjacentes, double-cliquez sur Reproduire la mise en forme et sélectionnez toutes les cellules dans lesquelles vous souhaitez copier le format. Pour désactiver le Reproduire la mise en forme, cliquez sur l’icône Reproduire la mise en forme.

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Fonctions Excel

Macro VBA Utiles

Excel Pratique

Programmation VBA

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x
()
x