Nous pouvons utiliser le format conditionnel pour obtenir des couleurs alternées pour les groupes de lignes dans Google Sheets. Cependant, cela pose un problème avec le filtrage du menu Filtre et les lignes masquées. Je parle d’une règle de mise en évidence pour mettre en évidence les lignes lorsque la valeur change (nouveau groupe commence) dans une colonne et du problème de cette règle de mise en évidence avec le filtre.
Je dispose déjà d’un tutoriel sur la règle de format conditionnel pour obtenir des couleurs alternées pour le groupe de données dans Google Sheets.
Ici, le sujet ne concerne pas seulement la règle de format conditionnel pour obtenir des couleurs alternées pour les groupes, mais aussi le problème associé au filtre.
Si vous avez du temps libre, vous pouvez suivre mon tutoriel ci-dessus (ce n’est pas nécessaire pour autant) et appliquer le format conditionnel aux groupes de lignes en utilisant des couleurs alternées. Ensuite, filtrez vos données groupées mises en évidence.
Vous verrez que le filtrage casse parfois les couleurs alternées. J’ai une solution, ou mieux à dire une solution de contournement, pour ce problème complexe en utilisant un onglet d’aide et quelques colonnes d’aide.
Dans l’exemple de capture d’écran ci-dessous, il y a une liste filtrée dans la colonne A. Les éléments similaires ont été regroupés par tri. De plus, j’ai défini des couleurs alternées pour les groupes de données (lignes).
Voyez ce qui se passe lorsque je filtre la ligne contenant « Orange ». Les couleurs alternées des groupes se rafraîchissent, n’est-ce pas ? Cet article décrit la même chose.
Permettez-moi de vous aider à résoudre les problèmes de couleurs alternées pour les groupes de lignes et de filtre associés dans Google Sheets.
Étapes pour résoudre le problème de filtre associé aux couleurs alternées pour les groupes dans Google Sheets
Tout d’abord, créez votre liste dans la colonne A de « Sheet1 ». Pour tester, vous pouvez utiliser les mêmes valeurs ci-dessus (données sur les fruits) car mon explication ci-dessous sera basée là-dessus.
À l’étape suivante, étiquetez la cellule E1 comme « Aide 1 » et la cellule F1 comme « Aide 2 ».
Dans la cellule E2, copiez-collez la formule suivante et faites-la glisser vers le bas jusqu’à la dernière ligne qui contient une valeur dans la colonne A (jusqu’à E12 selon mon exemple de liste dans la colonne A).
=subtotal(103,A2)Note : J’expliquerai cette formule et les formules suivantes sous un sous-titre distinct à une étape ultérieure de ce tutoriel.
Je vais vous dire quelle formule utiliser dans la colonne d’aide 2 (cellule F2). Avant cela, « Ajoutez un nouvel onglet » (un deuxième onglet) et nommez-le « Onglet d’aide ».
Dans celui-ci, nous avons besoin de 3 colonnes seulement. Dans la cellule A1 de l’onglet d’aide, entrez la formule de filtre ci-dessous.
=filter({row(Sheet1!A1:A),Sheet1!A1:A},Sheet1!E1:E=1)Cela va remplir certaines valeurs dans la colonne A et B de l’onglet d’aide.
Dans la cellule C1 du même onglet, entrez cette formule.
=ArrayFormula(IFNA(match(B1:B,unique(B1:B),0)))Revenez à « Sheet1 » et dans la cellule F2, entrez la formule Vlookup ci-dessous.
=ArrayFormula(IFNA(vlookup(row(A2:A),'Onglet d'aide'!A1:C,3,0)))Sélectionnez la plage A2:A dans « Sheet1 ». Ensuite, allez dans la Mise en forme conditionnelle (menu Format).
Là, dans le champ de formule personnalisée, entrez les règles ci-dessous une par une.
Règle 1:
=and(isodd($F2),not(isblank($F2)))Règle 2:
=and(iseven($F2),not(isblank($F2)))N’oubliez pas de choisir deux couleurs différentes pour les deux règles ci-dessus.
J’espère que vous avez terminé toutes les étapes ci-dessus. Ensuite, testons ma solution de contournement !
Masquez la ligne n°5. La mise en forme conditionnelle ajustera/correctement rafraîchira les couleurs alternées.
De cette façon, vous pouvez résoudre les problèmes de couleurs alternées pour les groupes et les problèmes de filtre dans Google Sheets.
Pouvez-vous expliquer l’utilité des colonnes d’aide (formules) et de l’onglet d’aide ?
Commençons par la colonne d’aide E dans « Sheet1 », puis l’onglet d’aide, puis la colonne d’aide F à nouveau dans « Sheet1 ».
Subtotal comme Counta
La formule Subtotal qui utilise la fonction numéro 103 dans la cellule E2 est équivalente à Counta. Mais il y a une différence. La formule compte la cellule A2 si la ligne est visible.
Étant donné que nous avons copié cette formule dans E3:E12, les formules renverraient 1 dans toutes les lignes sauf les lignes masquées, le cas échéant.
Qu’en est-il des lignes masquées ?
Si l’une des lignes est masquée dans la plage, la valeur de la cellule correspondante dans la colonne E sera de 0.
Nous utiliserons cette colonne comme colonne de critères dans une formule de filtre dans l’onglet d’aide.
Formule d’onglet d’aide 1 – Filtre
La cellule A1 de l’onglet d’aide contient une formule de filtre qui filtre les éléments visibles de la liste dans la colonne A de « Sheet1 ».
Avec la liste, la formule renvoie les numéros de ligne correspondants. Ces numéros de ligne seront utiles plus tard.
Le critère du filtre est Sheet1!E1:E=1, ce qui signifie que si la ligne n’est pas masquée.
Formule d’onglet d’aide 2 – Formule de tableau pour les couleurs alternées pour les groupes
Dans le même onglet d’aide, il y a une autre formule dans la cellule C1. Cette formule peut être utilisée pour mettre en forme conditionnelle des couleurs alternées pour les groupes en utilisant les fonctions ISODD/ISEVEN, mais nous ne voulons pas de surlignage dans cet onglet d’aide.
La formule de tableau dans la cellule C1 attribue des chiffres séquentiels en fonction des groupes.
Si vous me demandez comment, voici un tutoriel très détaillé – Assign Same Sequential Numbers to Duplicates in a List in Google Sheets. Ne suivez le lien que si vous avez suffisamment de temps libre dès maintenant.
Si nous le voulons, nous pouvons mettre en évidence des couleurs alternées pour les groupes dans la colonne B de cet « onglet d’aide ».
Comment ?
Puisque les nombres de la colonne C sont séquentiels, ces nombres sont comme une séquence impaire, paire, impaire, paire… pour les groupes.
Nous pouvons mettre en évidence toutes les lignes contenant des nombres impairs avec une couleur et des nombres pairs avec une autre couleur.
Il s’agit d’un onglet d’aide et nous ne voulons pas de mise en évidence sur cet onglet. Alors ?
Vlookup dans Sheet1 – Assigner des nombres séquentiels aux lignes visibles
La formule Vlookup dans la cellule F2 (dans « Sheet1″) utilise les numéros de ligne comme clés de recherche et recherche les mêmes numéros de ligne dans la colonne A de l' »Onglet d’aide ».
Si les correspondances existent, elle renverra les numéros séquentiels de la colonne C de l' »Onglet d’aide ». Ainsi, il y aura toujours des numéros séquentiels corrects (non rompus) dans la colonne F de « Sheet1 ».
Cela signifie que si vous masquez une ligne, cela ne cassera pas la séquence de chiffres dans la colonne F. Parce que l' »Onglet d’aide » ne contient pas cette ligne.
Nos règles de mise en forme conditionnelle ISODD et ISEVEN, je veux dire les couleurs alternées pour les groupes, sont basées sur cette colonne F. Le filtre n’a donc aucun effet sur la mise en évidence.
De cette façon, vous pouvez obtenir des couleurs alternées pour les groupes sans problèmes de filtre dans Google Sheets.
