La mise en forme conditionnelle est généralement utilisée pour modifier les couleurs de cellule ou de police en réponse aux données saisies dans une cellule, telles qu'une date de retard ou une dépense budgétaire trop élevée, généralement à l'aide des conditions actuelles d'Excel. Toutefois, il est également possible de créer des règles de mise en forme conditionnelle personnalisées à l'aide de formules Excel pour tester les conditions spécifiées par l'utilisateur.
Une telle formule qui combine lesMOD etRANGÉE Les fonctions peuvent être utilisées pour ombrer automatiquement d’autres rangées de données, ce qui rend la lecture de données dans de grandes feuilles de calcul beaucoup plus gérable.
Ombrage de lignes / colonnes dans Excel
L'utilisation d'une formule pour ajouter un ombrage de lignes présente un autre avantage: elle est dynamique, ce qui signifie qu'elle change si le nombre de lignes change. Si des lignes sont insérées ou supprimées, l’ombrage des lignes s’ajuste afin de conserver le motif.
Les lignes alternatives ne sont pas la seule option avec cette formule. En le modifiant légèrement, comme indiqué ci-dessous, la formule peut assombrir n'importe quel motif de rangées; il peut même être utilisé pour ombrer des colonnes au lieu de lignes si vous le souhaitez.
Ombrage des lignes de feuille de calcul dans Excel
La première étape consiste à mettre en évidence la plage de cellules à ombrer, car la formule n’affecte que les cellules sélectionnées. Les instructions pour ombrer les lignes avec une mise en forme conditionnelle utiliseront la formule suivante:
= MOD (ROW (), 2) = 0
- Ouvrir un Exceller feuille de calcul: une feuille de calcul vierge fonctionnera pour ce didacticiel.
- Surligner une plage de cellules dans la feuille de calcul.
- Clique sur leAccueil languette du ruban.
- Clique sur le Mise en forme conditionnelle icône.
- Choisir la Nouvelle règlepossibilité d'ouvrir le Nouvelle règle de formatage boîte.
- dans le Style case, sélectionnez la Classique option.
- Clique sur le Formater uniquement les valeurs classées en haut ou en bas case pour ensuite changer la valeur à Utilisez une formule pour déterminer les cellules à formater.
- Entrez la formule donnée ci-dessus dans la case située sous leUtilisez une formule pour déterminer les cellules à formater option.
- Clique le boîte déroulante à côté de Format avec choisir le jeu de couleurs pour vos rangées en alternance.
- Cliquez surD'accord pour fermer la boîte de dialogue et revenir à la feuille de calcul.
- Les lignes alternatives de la plage sélectionnée doivent maintenant être ombrées avec la couleur de remplissage d'arrière-plan choisie.
Interprétation de la formule MOD
Le modèle que nous avons conçu dépend de la MOD fonction dans la formule. Quoi MOD fait est divise le numéro de ligne (déterminé par le RANGÉE fonction) par le second numéro entre crochets (2) et retourne le reste ou le module comme on l'appelle parfois.
= MOD (ROW (), 2) = 0
À ce stade, la mise en forme conditionnelle prend le relais et compare le module au nombre suivant le signe égal. S'il y a une correspondance (ou plus correctement si la condition est VRAI), la ligne est ombrée, si les nombres des deux côtés du signe égal ne correspondent pas, la condition est FAUX et aucune ombrage ne se produit pour cette ligne.
La condition de=0 dans la formule détermine que la première ligne de la plage n'est pas ombrée, ce qui est fait car cette ligne contient souvent des en-têtes qui ont leur propre formatage.
Choisir d'ombrer les colonnes au lieu de lignes
La formule utilisée pour ombrer des lignes alternées peut être modifiée pour permettre également d’ombrer les colonnes. Le changement requis consiste à utiliser le COLONNE fonction au lieu de RANGÉE fonction dans la formule. Ce faisant, la formule ressemblerait à ceci:
= MOD (COLONNE (), 2) = 0
Changer le motif d'ombrage
Il est facile de changer le motif d’ombrage en changeant l’un ou l’autre des nombres de la formule.
- Pour que l’ombrage des lignes commence par la première ligne au lieu de la seconde, à la fin de la formule, modifiez =0 à=1.
- Pour que chaque troisième ou quatrième ligne soit ombrée au lieu d’une autre, modifiez le paramètre 2 dans la formule à 3 ou 4.
Le nombre entre parenthèses est appelé le diviseur puisque c’est le nombre qui divise dans le MOD une fonction. Si vous vous rappelez en classe de mathématiques, la division par zéro n'était pas autorisée, et ce n'est pas le cas dans Excel non plus. Si vous essayez d'utiliser un 0 entre les crochets à la place du 2, vous n’obtiendrez aucune ombrage dans la plage.
De plus, pour changer vraiment le motif, changez l’opérateur conditionnel ou de comparaison (=) utilisée dans la formule au signe inférieur à (<). En changeant =0 à <2 (moins de 2) par exemple, deux lignes ensemble peuvent être ombrées. Fais ça <3et l’ombrage se fera par groupes de trois rangées.
La seule mise en garde relative à l'utilisation de l'opérateur inférieur à est de s'assurer que le nombre entre crochets est supérieur au nombre figurant à la fin de la formule. Sinon, chaque ligne de la plage sera ombrée.
