Si vous utilisez Google Sheets, vous savez peut-être déjà qu’il est possible d’appliquer une mise en forme conditionnelle pour obtenir des couleurs alternées pour les groupes de lignes. Cependant, cette méthode présente un problème lorsqu’il s’agit d’utiliser le filtre dans le menu et de masquer des lignes.
Dans cet article, je vais vous montrer comment résoudre ce problème en utilisant une astuce simple, mais efficace. Avant de commencer, assurez-vous d’avoir suivi mon tutoriel précédent sur la mise en forme conditionnelle basée sur un groupe de données dans Google Sheets.
Maintenant, parlons du problème associé au filtre. Lorsque vous appliquez la mise en forme conditionnelle pour obtenir des couleurs alternées pour les groupes, le filtre peut parfois perturber cette mise en forme. Mais ne vous inquiétez pas, j’ai trouvé une solution pour contourner ce problème.
Voici les étapes à suivre 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 la « Feuille1 ». Pour cet exemple, vous pouvez utiliser les mêmes valeurs que celles que j’ai utilisées dans mon tutoriel précédent.
-
Ensuite, étiquetez la cellule E1 comme « Helper 1 » et la cellule F1 comme « Helper 2 ».
-
Dans la cellule E2, copiez-collez la formule suivante et faites-la glisser vers le bas jusqu’à la dernière ligne contenant une valeur dans la colonne A (jusqu’à E12 selon ma liste d’exemple dans la colonne A).
=sous-total(103,A2)
Note : Je vous expliquerai cette formule et les formules suivantes sous un sous-titre séparé plus tard dans ce tutoriel.
-
Je vais maintenant vous dire quelle formule utiliser dans la colonne « Helper 2 » (dans la cellule F2). Mais avant cela, ajoutez une nouvelle feuille (un deuxième onglet) et nommez-la « Helper Tab ».
-
Dans cet onglet, nous avons besoin de seulement 3 colonnes. Dans la cellule A1 de l’onglet « Helper Tab », entrez la formule de filtrage suivante :
=filter({ligne(Feuil1!A1:A),Feuil1!A1:A},Feuil1!E1:E=1)
Cela va remplir des valeurs dans les colonnes A et B de l’onglet « Helper Tab ».
-
Dans la cellule C1 du même onglet, entrez cette formule :
=ArrayFormula(SI(ESTNA(recherchev(B1:B,unique(B1:B),0))),0,recherchev(B1:B,unique(B1:B),0)))
-
Revenez à la « Feuille1 » et dans la cellule F2, entrez la formule Vlookup suivante :
=ArrayFormula(SI(ESTNA(recherchev(ligne(A2:A),’Helper Tab’!A1:C,3,Faux))),0,recherchev(ligne(A2:A),’Helper Tab’!A1:C,3,Faux)))
-
Dans la « Feuille1 », sélectionnez la plage A2:A. Ensuite, allez dans le menu « Format » et cliquez sur « Mise en forme conditionnelle ».
-
Dans le champ de formule personnalisée, saisissez les deux règles suivantes :
Règle 1 :
=et(estimpair($F2),non(estvide($F2)))Règle 2 :
=et(estpair($F2),non(estvide($F2)))N’oubliez pas de choisir deux couleurs différentes pour ces deux règles.
J’espère que vous avez suivi toutes les étapes ci-dessus. Maintenant, testons ma solution ! Masquez la ligne n°5 et vous verrez que la mise en forme conditionnelle ajuste correctement les couleurs alternatives.
De cette manière, vous pouvez obtenir des couleurs alternées pour les groupes et résoudre les problèmes de filtre dans Google Sheets.
Maintenant, expliquons brièvement le but des colonnes auxiliaires (formules) et de l’onglet auxiliaire :
Commençons par la colonne « Helper 1 » dans la « Feuille1 », puis l’onglet « Helper Tab » et enfin la colonne « Helper 2 » à nouveau dans la « Feuille1 ».
Sous-total en tant que Counta :
La formule de sous-total 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.
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 0.
Nous utiliserons cette colonne comme colonne de critère dans une formule de filtrage dans l’onglet « Helper Tab ».
Formule « Helper Tab » 1 – Filtre :
La cellule A1 de l’onglet « Helper Tab » contient une formule de filtrage qui filtre les éléments visibles de la liste dans la colonne A de la « Feuille1 ».
Avec la liste, la formule renvoie les numéros de ligne correspondants. Ces numéros de ligne nous seront utiles plus tard.
Le critère dans le filtre est Feuil1!E1:E=1, ce qui signifie que la ligne n’est pas masquée.
Formule « Helper Tab » 2 – Formule de tableau pour les couleurs alternées pour les groupes :
Dans le même onglet « Helper Tab », il y a une autre formule dans la cellule C1. Cette formule permet de mettre en forme conditionnelle des couleurs alternatives pour les groupes à l’aide des fonctions ISIMP et ISPAIR, mais nous ne voulons pas de mise en forme sur cet onglet auxiliaire.
La formule de tableau dans la cellule C1 attribue des numéros séquentiels en fonction des groupes.
Si vous me demandez comment, je vous donnerai un tutoriel très détaillé sur l’attribution de numéros séquentiels identiques aux doublons dans une liste dans Google Sheets. Suivez le lien uniquement si vous avez suffisamment de temps libre maintenant.
Si nous le souhaitons, nous pouvons mettre en surbrillance des couleurs alternatives pour les groupes dans la colonne B de cet onglet « Helper Tab ».
Comment ?
Puisque les numéros dans la colonne C sont séquentiels, ces numéros forment un schéma de type impair, pair, impair, pair… pour les groupes.
Nous pouvons mettre en surbrillance toutes les lignes contenant des numéros impairs avec une couleur et les numéros pairs avec une autre couleur.
C’est un onglet auxiliaire et nous ne voulons aucune mise en forme sur cet onglet. Alors que faire ?
Recherchev dans la « Feuille1 » – Attribution de numéros séquentiels aux lignes visibles :
La formule de recherchev dans la cellule F2 (dans la « Feuille1 ») 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 « Helper Tab ».
Si cela correspond, elle renvoie les numéros séquentiels de la colonne C de l’onglet « Helper Tab ». Ainsi, il y aura toujours des numéros séquentiels corrects (non rompus) dans la colonne F de la « Feuille1 ».
Cela signifie que si vous masquez une ligne, cela n’interrompra pas la séquence de numéros dans la colonne F. Parce que l’onglet « Helper Tab » ne contient pas cette ligne.
Nos règles de mise en forme conditionnelle ISIMP et ISPAIR, c’est-à-dire les couleurs alternées pour les groupes, sont basées sur cette colonne F. Ainsi, le filtre n’aura aucun effet sur la mise en forme.
De cette manière, vous pouvez obtenir des couleurs alternées pour les groupes sans problème de filtre dans Google Sheets.
Pour plus de conseils et astuces sur Google Sheets, consultez mon site Crawlan.com.
