Parfois, il peut être difficile d’exclure les lignes masquées manuellement d’un tableau croisé dynamique dans Google Sheets, mais ce n’est pas impossible. Grâce à une formule de tableau dynamique, vous pouvez ajuster la taille de votre tableau en fonction des données sources.
Le processus consiste à ajouter une colonne supplémentaire (une colonne d’aide) à vos données sources, avec la cellule supérieure contenant la formule de tableau mentionnée précédemment.
La colonne d’aide a pour but de marquer automatiquement les lignes masquées manuellement.
La formule vous permettra de distinguer les lignes masquées des lignes visibles.
Après avoir effectué cette étape (que nous verrons en détail plus bas), vous pourrez utiliser les « Filtres » dans l’éditeur de tableau croisé dynamique pour exclure les lignes marquées.
Google Sheets ne dispose pas d’une option intégrée pour exclure les lignes masquées manuellement dans les paramètres du tableau croisé dynamique.
Avantages de l’exclusion des lignes masquées manuellement d’un tableau croisé dynamique
Dans Google Sheets, vous pouvez filtrer systématiquement vos données sources de deux façons :
- À l’aide de la commande de filtre : Cliquez sur Données > Créer un filtre.
- À l’aide d’un segment : Cliquez sur Données > Ajouter un segment.
Ces méthodes vous permettent de sélectionner des éléments spécifiques, tels que la filtration des données par date, région ou produit. Cela masquera les lignes correspondantes dans vos données sources.
Et qu’en est-il du rapport de tableau croisé dynamique connecté ?
La première option, Créer un filtre, n’aura aucun impact sur le rapport de tableau croisé dynamique. Vous devez sélectionner les « Filtres » dans l’éditeur de tableau croisé dynamique pour filtrer les données dans le tableau croisé dynamique.
Cependant, vous pouvez connecter le Segment au tableau croisé dynamique pour que ce dernier soit automatiquement filtré lorsque vous sélectionnez des éléments dans le Segment.
Exemple
Imaginez que l’élément « Mango » soit répertorié dans trois lignes de vos données sources, avec des valeurs de 50, 75 et 100, ce qui donne un total de 225.
Si vous filtrez cet élément à l’aide du Segment ou des « Filtres » dans l’éditeur du tableau croisé dynamique (et non Données > Créer un filtre), il n’apparaîtra plus dans le rapport.
La masquage manuel des lignes est différent de la filtration systématique des données. Dans ce cas, vous masquez aléatoirement des lignes dans un but précis, comme la protection de données sensibles ou l’exclusion d’informations obsolètes. Vous souhaitez conserver ces données plutôt que de les supprimer, mais vous ne voulez pas qu’elles apparaissent dans vos rapports.
Par exemple, si vous masquez manuellement la ligne contenant l’élément « Mango », dont la valeur est de 100, le rapport affichera une valeur agrégée de 125.
Dans quels cas souhaiteriez-vous exclure les lignes masquées manuellement d’un tableau croisé dynamique ?
Prenons l’exemple où vous devez générer un rapport en excluant des données sensibles.
Pour y parvenir, masquez les lignes pour les exclure du rapport, puis imprimez-les ou téléchargez-les au format PDF. Partagez ces copies.
Comment exclure les lignes masquées manuellement d’un tableau croisé dynamique en utilisant une colonne d’aide
Pour voir un exemple en direct, veuillez cliquer sur le lien ci-dessous et faire une copie de ma feuille d’exemple.
Données d’exemple et tableau croisé dynamique
Les données d’exemple suivantes se trouvent dans les colonnes A à C, et un tableau croisé dynamique créé à partir de celles-ci commence en cellule E1.
Pour créer le tableau croisé dynamique, sélectionnez les colonnes A à C et cliquez sur Insérer > Tableau croisé dynamique.
La capture d’écran suivante montre les paramètres du tableau croisé dynamique.
Colonne d’aide
Lors de la sélection d’une colonne d’aide, prenez en compte les critères suivants :
- Elle doit être vide.
- Elle doit être la colonne qui suit immédiatement les données sources.
Dans mon exemple, la colonne D remplit ces critères.
Dans la cellule D1, insérez la formule dynamique de la colonne d’aide suivante :
=VSTACK("Hidden Flag",BYROW(B2:B, LAMBDA(row,SUBTOTAL(103,row))))
Cette formule marquera les lignes masquées manuellement en insérant 0 (zéro) dans les cellules masquées et 1 dans les cellules visibles là où une valeur est présente dans la colonne B.
Quel est le critère de choix de la colonne dans la formule ci-dessus ?
Vous devez sélectionner la colonne qui correspond au champ « Ligne » dans les paramètres du tableau croisé dynamique. Dans mon tableau croisé dynamique, il s’agit du champ « Élément », situé dans B2:B.
Remarque : Pour plus d’informations sur cette formule, veuillez vous référer à mon guide sur la fonction BYROW.
Comment ajouter la colonne d’aide à votre tableau croisé dynamique
Pour inclure la colonne d’aide intitulée ‘Hidden Flag’ (oui, la formule mentionnée ci-dessus insérera l’étiquette ‘Hidden Flag’ dans la cellule D1) dans l’éditeur de tableau croisé dynamique, suivez ces étapes :
- Passez votre curseur sur le rapport de tableau croisé dynamique.
- Cliquez sur l’icône en forme de crayon intitulée « Modifier ».
- Dans l’éditeur, remplacez la plage A:C par A:D.
- Ne validez pas avec la touche Entrée du clavier. Cliquez en dehors du champ et cliquez sur OK.
Comment filtrer le tableau croisé dynamique pour exclure les lignes masquées manuellement
Voici la dernière étape pour exclure les lignes masquées manuellement du tableau croisé dynamique dans Google Sheets :
- Cliquez sur le bouton « Ajouter » à côté de « Filtre » dans l’éditeur de tableau croisé dynamique.
- Sélectionnez « Hidden Flag ».
- Cliquez sur le menu déroulant, décochez « 0 » et cliquez sur « OK ».
Vous pouvez maintenant fermer l’éditeur de tableau croisé dynamique en cliquant sur le bouton « x » en haut à droite.
Cliquez avec le bouton droit de la souris sur la ligne n°6 et sélectionnez « Masquer la ligne ». Vous remarquerez que le total pour l’élément « Mango » passe de 225 à 125.
Conclusion
L’approche par colonne d’aide est la seule méthode pour exclure les lignes masquées manuellement d’un tableau croisé dynamique dans Google Sheets.
Lorsque vous utilisez cette solution de contournement, veillez à prendre en compte un facteur important : si vous avez appliqué des filtres aux données sources à l’aide du Segment, vous devez les supprimer. La méthode ci-dessus exclura également les lignes impactées par ces filtres.
