Alterner les couleurs par groupes avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Alterner les couleurs par groupes

Avec cette nouvelle astuce Excel, nous allons voir comment alterner très facilement les couleurs d'un tableau sur un pas défini dynamiquement.



Sur l'exemple illustré par la capture, l'utilisateur choisit un palier numérique à respecter à l'aide d'une liste déroulante située sur la droite du tableau. Instantanément, les couleurs de ce tableau s'alternent en respectant cette donnée dynamique grâce à une mise en forme conditionnelle toute particulière.

Classeur source
Pour la démonstration de cette nouvelle astuce Excel, nous suggérons d'appuyer l'étude sur un tableau existant et suffisamment long pour opérer l'alternance de couleurs.

• Télécharger le classeur alterner-couleurs.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Le tableau présente les ventes réalisées par les commerciaux par tranches de cinq actions. Dans ces conditions, il apparaîtrait opportun d'alterner les couleurs toutes les cinq lignes pour clairement les différencier les uns des autres. Mais comme nous l'avons expliqué, nous allons nous laisser la liberté de définir ce pas numérique grâce à la liste déroulante située en cellule G4.

Majorer les indices de ligne
Pour bâtir une mise en forme conditionnelle capable d'alterner les couleurs en fonction de la valeur numérique choisie par l'utilisateur, l'astuce consiste dans un premier temps à majorer les indices de ligne sur un multiple du pas à respecter. Et pour cela, nous pouvons exploiter la fonction Excel Plafond. Nous entendons le démontrer en étape intermédiaire dans la colonne F sur la droite du tableau.

• Cliquer sur la cellule F4 à droite du premier commercial du tableau,
• Taper le symbole égal (=) pour débuter la syntaxe de la formule,
• Inscrire la fonction de majoration suivie d'une parenthèse, soit : Plafond(,

En premier argument, nous devons lui indiquer quel est le nombre à majorer. En second argument, nous devons lui transmettre la précision, soit le palier qui sera utilisé en multiple selon le contexte.

• Inscrire la fonction pour l'indice de ligne, sans paramètre, soit : Ligne(),

De cette manière, elle renseigne sur l'indice de la ligne en cours et va suivre la progression du calcul répliqué. Par contre, il s'agit de l'indice absolu. Pour ce premier calcul, elle va donc retourner la valeur 4. Or pour bien maîtriser l'alternance que nous souhaitons produire, nous voulons considérer cette ligne comme la première ligne du tableau. Il suffit donc de retrancher trois unités à ce résultat, pour enlever du décompte les trois lignes situées au-dessus.

• Retrancher trois unités, soit : -3,
• Taper un point-virgule (;) pour passer dans l'argument de la précision de la fonction Plafond,
• Taper alors les coordonnées de la cellule G4,

Il s'agit de la cellule hébergeant le palier défini par l'utilisateur avec la liste déroulante.

• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $G$4,

En effet, nous allons répliquer cette formule sur les lignes du dessous pour observer son comportement. Tandis que l'indice de ligne doit continuer de progresser, le palier quant à lui ne doit pas changer.

• Fermer la parenthèse de la fonction Plafond,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Ainsi, nous la gardons active. Comme vous pouvez le voir, le premier résultat pour la ligne 1 a été majoré sur la valeur du pas défini, soit 2 ici.

• Tirer la poignée de la formule sur quelques lignes vers le bas,

Comme vous pouvez le voir, tant que l'indice de ligne est inférieur ou égal au palier défini, la valeur est majorée sur cette donnée, mais au-delà, elle est majorée sur le multiple suivant et ainsi de suite. Nous obtenons ainsi une alternance de valeurs paires, identiques par groupe de deux, en cohérence avec le pas défini. Nous avons donc déjà ciblé dynamiquement les zones sur lesquelles les alternances de couleurs doivent se produire. La syntaxe de la petite formule que nous avons construite est la suivante :

=PLAFOND(LIGNE()-3; $G$4).

Alterner les couleurs en fonction du pas
Pour que la règle de mise en forme conditionnelle puisse détecter ces paliers pour les faire ressortir les uns par rapport aux autres, l'astuce consiste à utiliser la fonction Est.Pair ou la fonction Est.Impair sur ce précédent résultat divisé par le pas de la liste déroulante. A chaque fois qu'elle répondra par Vrai par exemple, la mise en valeur pourra être déclenchée automatiquement, un groupe sur deux.

• Sélectionner de nouveau la première cellule du calcul intermédiaire, en cliquant sur F4,
• Dans sa barre de formule, sélectionner la syntaxe sans le symbole égal et la copier (CTRL + C),
• Puis, sortir de la barre de formule avec la touche Echap du clavier,
• Sélectionner alors les données du tableau sans les titres, soit la plage de cellules B4:E23,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Dès lors, taper le symbole égal (=) pour initier la syntaxe de la règle,
• Inscrire la fonction pour détecter les nombres pairs suivie d'une parenthèse, soit : Est.Pair(,
• Coller (CTRL + V) la syntaxe du précédent calcul : PLAFOND(LIGNE()-3;$G$4),
• Réaliser alors la division par le pas défini, soit : /$G$4,
• Puis, fermer la parenthèse de la fonction Est.Pair,

A chaque fois que ce critère est vérifié, donc à chaque fois que la fonction Est.Pair retourne le booléen Vrai, nous savons que nous sommes passés sur un nouveau groupe alterné. Nous devons donc le faire ressortir dans une mise en forme explicitement différente.

• Pour cela, cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet remplissage,
• Dans la palette de couleurs, choisir un gris foncé plus clair que le fond de la feuille,
• Activer alors l'onglet Police de la boîte de dialogue,
• Avec la seconde liste déroulante, choisir un vert clair pour la couleur du texte,
• Puis, valider ces attributs de format avec le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue qui confirme l'apparence que devrons prendre les groupes alternés en cohérence avec la formule de la règle.

• Cliquer sur le bouton Ok pour valider la création de cette règle de mise enforme,

De retour sur la feuille, vous constatez que les couleurs des lignes sont alternées par groupes, en cohérence avec le palier défini. Et si vous changez ce dernier avec la liste déroulante, vous avez le plaisir de remarquer que les alternances s'ajustent automatiquement.



Et si d'aventure vous souhaitiez commencer l'alternance par le nouveau jeu de couleurs, soit à partir du premier groupe impair, dans la règle de mise en forme conditionnelle, il suffit de remplacer la fonction Est.Pair par la fonction Est.Impair.