Alterner automatiquement les couleurs d'un tableau

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


Sujets que vous pourriez aussi aimer :


Alterner automatiquement les couleurs d'un tableau

Dans cette formation Excel, nous proposons de démontrer les prouesses offertes par la mise en forme conditionnelle. L'idée consiste à modifier les remplissages alternés d'une base de données, au choix d'une couleur dans une liste déroulante.



Certes, le tableur propose des réglages automatisés permettant d'obtenir le résultat. En l'occurrence, il s'agit de la commande Mettre sous forme de tableau. Cette fonctionnalité offre des avantages et des inconvénients. Elle rend les plages de cellules dynamiques mais les encapsule dans des noms souvent peu ergonomiques. Pour continuer de piloter ces dernières par leurs références, nous proposons donc de bâtir l'outil par la mise en forme conditionnelle.

Source et présentation de la problématique
Pour la mise en oeuvre de ces travaux, nous proposons de débuter à partir de données existantes à réceptionner.

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

Dans l'unique feuille de ce classeur, nous récupérons le tableau d'une base de données des clients. En cellule E2, vous notez la présence d'une liste déroulante. Elle permet d'émettre un choix de couleur. C'est en fonction de cette couleur désignée que l'alternance des remplissages alternés doit s'opérer dans le tableau.

Identifier les lignes paires
C'est une formule Excel qui doit régir cette règle spécifique de mise en forme conditionnelle. Elle doit par exemple repérer chaque ligne paire. Si l'identification est un succès, un remplissage de fond correspondant à la couleur désignée doit se déclencher.

La fonction Excel Ligne retourne l'indice de ligne d'une cellule désignée. La fonction Excel Mod retourne le reste d'une division. En imbriquant la fonction Ligne dans la fonction Mod, nous saurons si le reste de la division par 2 de la cellule désignée est nul. Dans ce cas, nous saurons que la ligne en cours d'analyse est bien une ligne paire.

Plusieurs règles doivent se cumuler pour honorer le choix de couleur. Elles consistent toutes à identifier les lignes paires. Mais dans le même temps, elles doivent repérer la couleur choisie pour adapter le format en conséquence. C'est la fonction Excel ET qui permet d'énumérer des conditions à recouper.

• Sélectionner la première référence de la base de données, soit la cellule B5,
• Tout en maintenant la touche MAJ enfoncée, sélectionner le dernier mail, soit la cellule H42,

Cette technique permet d'inclure dans la sélection toutes les cellules comprises entre la première et la dernière. Nous désignons ainsi explicitement la plage devant réagir en fonction de la couleur choisie.

• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir 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 juste en-dessous pour l'activer,
• Taper le symbole égal (=) pour initier la syntaxe de la règle,
• Saisir la fonction pour énumérer les critères, suivie d'une parenthèse, soit : ET(,
• Taper la fonction pour le reste de la division, suivie d'une parenthèse, soit : Mod(,
• Saisir la fonction pour l'indice de ligne suivie d'une parenthèse, soit : Ligne(,
• Sélectionner le premier identifiant client, soit la cellule B5,
• Enfoncer trois fois la touche F4 du clavier pour libérer la cellule, ce qui donne : B5,

De cette manière, chaque indice de ligne sera scruté pour toutes les cellules du tableau.

• Fermer la parenthèse de la fonction Ligne,
• Taper un point-virgule (;) pour passer dans le deuxième argument de la fonction Mod,
• Taper le chiffre 2 et fermer la parenthèse,

Nous cherchons ainsi à connaître le reste de la division par 2, pour l'indice de ligne en cours d'analyse. Il convient désormais de finaliser le critère permettant de déclencher la mise en forme dynamique.

• Taper le symbole égal (=) pour la condition à satisfaire,
• Puis, saisir le chiffre 0,

En effet, si le reste de la division retourné par la fonction Mod sur l'indice de ligne vaut zéro, nous savons que la ligne en cours est une ligne paire. Grâce à cette astuce, nous allons pouvoir alterner les couleurs de fond.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Sélectionner la cellule E2 des couleurs, ce qui donne : $E$2,

Cette fois, nous devons la conserver complètement figée. Bien que l'analyse de la mise en forme conditionnelle parcoure l'ensemble des lignes et colonnes du tableau, la vérification de la couleur doit toujours s'opérer par rapport à cette cellule de référence. Elle ne doit donc pas suivre le déplacement de l'analyse.

• Taper le symbole égal (=) pour la seconde condition à vérifier,
• Saisir le texte Bleu entre guillemets, soit : 'Bleu',
• Puis, fermer la parenthèse de la fonction ET pour terminer l'énumération des critères,

La règle que nous venons de bâtir est explicite. Si la ligne est paire est que la couleur choisie est le bleu, nous devons appliquer un remplissage en corrélation.

• Cliquer sur le bouton Format situé en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Cliquer alors sur le bouton Motifs et textures,

Une troisième boîte de dialogue apparaît. Elle est nommée Effets de remplissage.

• Avec la première liste déroulante, choisir un bleu assez soutenu,
• Avec la seconde liste, choisir un bleu pâle,

Nous définissons ainsi un remplissage dynamique des cellules concordantes en dégradé de couleurs.

• Cliquer sur le bouton Ok pour valider ce dégradé,

Nous sommes ainsi de retour sur la deuxième boîte de dialogue, dans l'onglet Remplissage.

• Cliquer de nouveau sur le bouton Ok pour valider ce format dynamique associé,

Nous sommes de retour sur la première boîte de dialogue, au niveau de la règle. Elle offre une synthèse explicite de la situation :

=ET(Mod(Ligne(B5);2)=0;$E$2='Bleu')



Lorsque la ligne analysée est paire et que la couleur de la liste déroulante est le bleu, les cellules concernées doivent se parer d'un remplissage bleu en dégradé.

• Cliquer une dernière fois sur le bouton Ok pour valider la règle du format dynamique,

De retour sur la feuille, au choix de la couleur bleue, vous notez la construction automatique de l'alternance du remplissage dégradé pour l'ensemble des lignes du tableau.



Bien sûr, pour une adaptation complète, il conviendrait de faire réagir dynamiquement les lignes de titre. Dans ce cas, seul le critère sur la couleur choisie doit être satisfait.

Il prévient aussi de prévoir toutes les autres couleurs de la liste déroulante. La seconde condition sur la couleur varie et le format doit être ajusté. Bien entendu, l'intégralité du tableau doit être préalablement désigné.

Pour le rouge :

=ET(MOD(LIGNE(B5);2)=0;$E$2='Rouge')

Pour le vert :

=ET(MOD(LIGNE(B5);2)=0;$E$2='Vert')



Pour le gris :

=ET(MOD(LIGNE(B5);2)=0;$E$2='Gris')



Désormais, une simple impulsion dans la liste déroulante permet d'adapter dynamiquement la mise en forme intégrale du tableau. Et de surcroît, nous avons réussi à produire l'alternance des couleurs par une formule.

Enfin, il convient de figer les volets au-dessus de la ligne 5. De cette manière, en naviguant au travers des cellules du dessous, les lignes de la base de données s'engouffrent sous les en-têtes de ligne.