Les tableaux croisés dynamiques sont peut-être l’un des meilleurs outils de présentation de données disponibles dans toute application de tableur. Cela nous amène à la question du jour : pouvez-vous filtrer un tableau croisé dynamique dans Google Sheets avec une formule personnalisée ?
La réponse est bien sûr oui. L’utilisation de formules personnalisées ouvre un nouvel horizon aux capacités de filtrage de l’application, ce qui inclut le tableau croisé dynamique.
Filtrer avec une formule personnalisée dans un tableau croisé dynamique de Google Sheets
Pour notre exemple, nous avons créé le jeu de données suivant…
Et son tableau croisé dynamique correspondant montrant les scores par région :
Par défaut, l’éditeur de tableau croisé dynamique de Google Sheets nous offre déjà une option de filtrage pour filtrer les données. Nous pouvons trouver cette option près du bas de l’éditeur.
Disons que nous voulons filtrer tous les scores par régions qui contiennent « Nord ».
Ajouter le filtre > Région > Statut > Sélectionner toutes les options contenant « Nord »
Le résultat :
Dans la plupart des cas, ce type de filtre suffit. Mais lorsqu’il s’agit de filtrer des données plus spécifiques avec des conditions, l’option par défaut présente certaines limitations.
Limitations du filtre régulier du tableau croisé dynamique
Prenons en compte le scénario où nous voulons trouver tous les scores régionaux par date d’enregistrement dans le tableau croisé dynamique de Google Sheets. La condition de filtrage sera toutes les dates après le 1er février 2022.
Maintenant, si nous utilisons le filtre par défaut par date, nous obtiendrons quelque chose comme ceci :
Toutes les entrées de dates individuelles sont répertoriées. Cela signifie qu’à chaque fois que nous ajoutons une nouvelle date, une nouvelle option sera enregistrée.
Cela rend le filtre régulier du tableau croisé dynamique assez inefficace lorsqu’il s’agit de traiter des données conditionnelles telles que « supérieur à » ou « inférieur à ».
Mais heureusement, nous pouvons utiliser des formules personnalisées pour filtrer dans un tableau croisé dynamique de Google Sheets.
Formule personnalisée simple pour filtrer dans un tableau croisé dynamique (filtrage des dates)
Revenons à la condition de filtrage de la présentation des scores par région après la date du 1er février 2022 dans un tableau croisé dynamique. Cette fois, nous utiliserons une formule personnalisée pour appliquer la condition.
Étape 1 : Ajoutez la « Date d’enregistrement » dans la section « Filtre » de l' »Éditeur de tableau croisé dynamique ».
Étape 2 : Cliquez sur le menu déroulant sous le champ « Statut ». Cela fera apparaître toutes les options de condition disponibles. Sélectionnez l’option « Filtrer par condition ».
Étape 3 : Faites défiler vers le bas pour trouver et sélectionner l’option « La formule personnalisée est ».
Étape 4 : Entrez la formule personnalisée suivante :
'Date d'enregistrement' > DATE(2022,2,1)
Détail de la formule :
'Date d'enregistrement': Le nom de l’en-tête à partir duquel nous obtenons les valeurs de date. Cette référence d’en-tête doit être entre guillemets simples ( »). Notez que tous les noms d’en-tête de l’ensemble de données source sont répertoriés du côté droit de l’éditeur de tableau croisé dynamique.>: La condition « supérieur à ». Nous pouvons également utiliser">="pour inclure la date de départ.DATE(2022,2,1): La référence de date pour la date de départ. Nous avons utilisé la fonctionDATEpour éviter toute erreur de formatage de la date saisie.
Étape 5 : Cliquez sur « OK » pour appliquer la formule personnalisée.
Avantages de l’utilisation d’une formule personnalisée comme filtre pour un tableau croisé dynamique
- Un utilisateur peut appliquer n’importe quelle condition.
- Un nombre illimité de conditions peut être appliqué.
- L’utilisateur peut utiliser des formules avec des références de cellules. Il bénéficie de tous les avantages de l’utilisation de références de lignes ou de colonnes verrouillées.
- Les formules sont entièrement personnalisables grâce à la possibilité d’utiliser des expressions régulières.
Utilisation d’une formule personnalisée d’expression régulière pour filtrer dans un tableau croisé dynamique
Les expressions régulières sont peut-être le meilleur moyen de personnaliser toutes les conditions pour les formules dans Google Sheets. Prenons un exemple pour vous montrer ce que nous voulons dire.
Disons que nous voulons filtrer le tableau croisé dynamique pour afficher les données de toutes les régions « Est ». Cela inclura les régions du Nord-Est ou du Sud-Est. Fondamentalement, toutes les données de région qui contiennent le texte « Est ».
Mais avant de passer au tableau croisé dynamique, voyons ce qui se passe lorsque nous recherchons « Est » à l’aide d’une formule sans expressions régulières. La formule est la suivante :
=A1="Est"
Toutes les valeurs de cellule qui ne contiennent pas une correspondance exacte de texte sont affichées comme « FAUX ». À moins que nous n’incluions les autres valeurs qui contiennent « Est », la formule générale ne fonctionnera pas pour notre condition.
Maintenant, faisons la même chose avec une fonction d’expression régulière : REGEXMATCH. Cela fonctionne de manière similaire à la fonction IF précédente pour rechercher la plage et trouver la valeur donnée. La formule est la suivante :
=REGEXMATCH(A1,"Est")
La fonction REGEXMATCH agit de manière similaire à IF pour trouver la correspondance correcte. Mais cette fois, la fonction fonctionne également pour les correspondances partielles (elle est toujours sensible à la casse). C’est l’un des plus grands avantages de l’utilisation des expressions régulières.
Maintenant, appliquons une formule similaire à celle-ci pour la formule personnalisée du filtre du tableau croisé dynamique dans Google Sheets :
Étape 1 : Dans l’éditeur de tableau croisé dynamique, sélectionnez « Région » comme filtre. Comme précédemment, sélectionnez l’option « La formule personnalisée est ».
Ajouter le filtre : Région > Statut > Filtre par condition > La formule personnalisée est
Étape 2 : Entrez la formule personnalisée suivante :
=REGEXMATCH(A1,"Est")
Notez qu’au lieu d’une plage de cellules, nous avons mis le nom de l’en-tête de colonne (Région) pour l’argument texte/plage de la fonction REGEXMATCH. C’est ce que vous devez faire dans les tableaux croisés dynamiques. C’est simple et facile à appliquer.
Étape 3 : Cliquez sur « OK » pour appliquer le filtre.
Filtrer avec plusieurs conditions dans un tableau croisé dynamique
Un autre avantage de l’utilisation des expressions régulières est que vous pouvez ajouter plusieurs conditions du même type dans la même formule.
Par exemple, disons que nous voulons inclure également « Ouest » comme condition. Avec une petite mise à jour, la formule ressemblera à ceci :
=REGEXMATCH(A1,"Est|Ouest")C’est le symbole « OU » (« | ») pour les expressions régulières. Cela définit la condition pour inclure soit « Est » soit « Ouest » dans le filtre.
Le résultat :
En conclusion
Utiliser une formule personnalisée pour filtrer les données dans un tableau croisé dynamique de Google Sheets n’est pas seulement facile, mais aussi très utile. Grâce à la personnalisation des formules dans Google Sheets, il n’y a pratiquement aucune limite aux conditions de filtrage qu’un utilisateur peut appliquer.
N’hésitez pas à poser vos questions ou à nous donner des conseils dans la section des commentaires ci-dessous.
