Comment lier le filtre du tableau croisé dynamique à une cellule spécifique dans Excel ?

Dans Excel, vous pouvez souvent vouloir créer des rapports interactifs où votre filtre de tableau croisé dynamique reflète la valeur d'une cellule spécifique. Cela permet aux utilisateurs de sélectionner ou d'entrer une valeur de filtre en un seul endroit, et que le tableau croisé dynamique se mette à jour dynamiquement en fonction de cette entrée. Cette méthode est particulièrement utile lors de la conception de tableaux de bord ou d'interfaces de filtres personnalisés pour l'exploration de données.

Cet article fournit plusieurs solutions pratiques, y compris une approche basée sur VBA et d'autres méthodes intégrées dans Excel, pour vous aider à lier un filtre de tableau croisé dynamique à une valeur de cellule ou obtenir des effets de rapport dynamiques similaires.

• Lier le filtre du tableau croisé dynamique à une cellule spécifique avec un code VBA
• Formule Excel - Utiliser des formules (par exemple, GETPIVOTDATA) en conjonction avec un segment ou un filtre de rapport faisant référence
• Autres méthodes intégrées d'Excel - Connecter les segments de tableau croisé dynamique et les tableaux de bord pour un filtrage interactif

Lier le filtre du tableau croisé dynamique à une cellule spécifique avec un code VBA

Si vous avez besoin d'un lien direct entre une cellule et un filtre de tableau croisé dynamique — de sorte que la modification de la valeur d'une cellule mette automatiquement à jour le filtre du tableau croisé dynamique — VBA offre une solution pratique pour y parvenir. Cette approche convient aux tableaux de bord ou rapports interactifs où les utilisateurs souhaitent rapidement contrôler les segments de données depuis une seule cellule.

Pour que cette technique fonctionne, votre tableau croisé dynamique doit contenir un champ de filtre. Le nom du champ de filtre est crucial pour configurer correctement le code VBA.

Considérons l'exemple suivant : Le tableau croisé dynamique a un champ de filtre nommé Catégorie, avec deux valeurs de filtre : « Dépenses » et « Ventes ». En liant une cellule au filtre du tableau croisé dynamique, vous pouvez contrôler les données affichées en tapant soit « Dépenses », soit « Ventes » dans votre cellule choisie.

Pour mettre cela en œuvre :

• Sélectionnez la cellule que vous souhaitez utiliser comme contrôleur de filtre (par exemple, la cellule H6) et entrez l'une de vos valeurs de filtre à l'avance. Assurez-vous que la valeur correspond exactement à celles disponibles dans le champ de filtre du tableau croisé dynamique.
• Allez dans la feuille de calcul contenant votre tableau croisé dynamique. Faites un clic droit sur l'onglet de la feuille et choisissez Afficher le code dans le menu. Cela ouvre la fenêtre Visual Basic for Applications.

Dans la fenêtre Microsoft Visual Basic for Applications, collez le code VBA suivant dans le volet de code.

Code VBA : Lier le filtre du tableau croisé dynamique à une cellule spécifique

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error Resume Next
    If Intersect(Target, Range("H6")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("Sheet1").PivotTables("PivotTable2")
    Set xPFile = xPTable.PivotFields("Category")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    Application.ScreenUpdating = True
End Sub

Remarques :

Après avoir collé le code, appuyez sur Alt + Q pour fermer la fenêtre de l'éditeur VBA et retourner à Excel.

Maintenant, l'état du filtre de votre tableau croisé dynamique est contrôlé par le contenu de la cellule H6. Il suffit de changer la valeur dans la cellule H6 (en « Ventes » ou « Dépenses ») pour mettre à jour instantanément l'affichage du tableau croisé dynamique. Si vous rencontrez des problèmes, vérifiez que la valeur de la cellule référencée correspond exactement à une valeur de filtre dans le tableau croisé dynamique, et que les noms dans votre code sont correctement attribués.

Chaque fois que vous modifiez le contenu de la cellule, le tableau croisé dynamique actualise ses données filtrées en conséquence.

Conseils et dépannage : Si la valeur du champ de filtre dans la cellule ne correspond pas exactement aux éléments disponibles (y compris la capitalisation et les espaces), le code peut ne pas appliquer le filtre comme prévu. Vérifiez toujours que vos champs et noms de table sont orthographiés correctement dans le code VBA. Si vous souhaitez utiliser ce paramétrage sur plusieurs tableaux croisés dynamiques, vous pouvez adapter davantage le code ou l'étendre à l'aide de boucles.

Découvrez la magie d'Excel avec Kutools AI

• Exécution intelligente : Effectuez des opérations sur les cellules, analysez les données et créez des graphiques, le tout piloté par des commandes simples.
• Formules personnalisées : Générez des formules adaptées pour rationaliser vos flux de travail.
• Codage VBA : Écrivez et implémentez du code VBA sans effort.
• Interprétation des formules : Comprenez facilement des formules complexes.
• Traduction de texte : Surmontez les barrières linguistiques dans vos feuilles de calcul.

Améliorez vos capacités Excel avec des outils alimentés par l'IA. Téléchargez maintenant et découvrez une efficacité sans précédent !

Formule Excel - Utiliser des formules (par exemple, GETPIVOTDATA) en conjonction avec un segment ou un filtre de rapport faisant référence

Bien qu'Excel n'offre pas de méthode purement native pour lier directement le filtre d'un tableau croisé dynamique à une cellule, vous pouvez obtenir des rapports dynamiques et afficher des valeurs pertinentes avec des formules telles que GETPIVOTDATA en combinaison avec des segments ou des filtres de rapport. Cette solution est utile lorsque vous souhaitez créer des tableaux de bord où les valeurs récapitulatives se mettent à jour instantanément en fonction d'une sélection de filtre ou d'une autre saisie de cellule, rendant l'analyse des données plus interactive.

Les scénarios applicables incluent des panneaux de rapport dynamiques, des tableaux de bord ou des résumés comparatifs où vous souhaitez que le résultat affiché suive les sélections des segments, ou reflète les données liées au contenu d'une cellule. L'avantage principal est que cette méthode fonctionne bien pour afficher des données récapitulatives mises à jour. Cependant, l'état réel du filtre du tableau croisé dynamique ne peut pas être programmé uniquement par une formule de cellule.

Exemple : Afficher un résumé du tableau croisé dynamique basé sur une valeur de cellule

Supposons que vous ayez un tableau croisé dynamique résumant les ventes par catégorie (par exemple, « Ventes », « Dépenses »). Vous pouvez utiliser GETPIVOTDATA pour extraire la valeur pertinente pour une catégorie spécifiée dans une cellule.

1. Supposons que la cellule H6 contienne la catégorie que vous souhaitez afficher (par exemple, « Ventes »). Placez la formule suivante dans votre cellule de résumé (par exemple, I6) :

=GETPIVOTDATA("Sum of Amount",$B$4,"Category",H6)

2. Après avoir entré la formule dans I6, appuyez sur Entrée. Maintenant, chaque fois que vous changez H6 pour une catégorie valide (comme « Dépenses » ou « Ventes »), I6 se mettra à jour instantanément pour afficher le total pour cette catégorie, selon le tableau croisé dynamique actuel.

Astuce : Bien que cette méthode ne change pas le filtre dans le tableau croisé dynamique lui-même, elle affiche efficacement les données résultantes comme si elles étaient filtrées par la cellule, fournissant un affichage dynamique lié à votre saisie de cellule cible. Vous pouvez également utiliser cette méthode pour alimenter des graphiques, des tableaux récapitulatifs ou des tableaux de bord.

Dépannage : Si la formule renvoie une erreur #REF! ou #VALEUR!, vérifiez que vos références de cellules sont correctes, que la catégorie saisie existe dans votre tableau croisé dynamique et que le nom du champ/somme correspond exactement.

Autres méthodes intégrées d'Excel - Connecter les segments de tableau croisé dynamique et les tableaux de bord pour un filtrage interactif

Les outils Segment et Filtre de rapport d’Excel offrent des options conviviales et intégrées pour un filtrage interactif sans écrire de code VBA. Vous pouvez utiliser ces méthodes pour obtenir un effet de tableau de bord, reliant plusieurs tableaux croisés dynamiques ou affichages à un ou plusieurs segments.

Une approche courante consiste à insérer un segment lié à votre champ de tableau croisé dynamique (par exemple, « Catégorie »). Les utilisateurs cliquent simplement sur les éléments souhaités dans le segment et les tableaux croisés dynamiques se mettent à jour en conséquence. Si vous avez plusieurs tableaux croisés dynamiques basés sur la même source de données, vous pouvez connecter un seul segment à toutes les tables pour un filtrage synchronisé, rendant votre interface de rapport plus intuitive et cohérente.

Pour créer un segment et le lier :

• Cliquez dans votre tableau croisé dynamique et allez à Analyser le tableau croisé dynamique (ou l'onglet Options, selon la version d'Excel) > Insérer un segment.
• Cochez le champ souhaité (par exemple, Catégorie) et cliquez sur OK. Le segment apparaît sur la feuille et permet aux utilisateurs de filtrer visuellement.
• Pour lier un segment à plusieurs tableaux croisés dynamiques, faites un clic droit sur le segment, choisissez Connexions au rapport (ou Connexions au tableau croisé dynamique), et cochez tous les tableaux croisés dynamiques que vous souhaitez synchroniser.
  C'est particulièrement puissant pour les scénarios de tableau de bord où diverses visualisations répondent ensemble aux filtres des utilisateurs.

Avantages : Très facile à utiliser pour la plupart des besoins de filtrage interactif et ne nécessite ni macros ni code personnalisé. Excellent pour les tableaux de bord ou les rapports partagés où simplicité et fiabilité sont cruciales. La limitation est que l'automatisation absolue cellule-filtre (liaison cellule-filtre) n'est pas prise en charge nativement — l'assignation directe de valeur au filtre nécessite VBA ou des outils externes.

Dépannage : Si un segment ne se connecte pas à plusieurs tableaux croisés dynamiques, assurez-vous que toutes les tables sont construites à partir du même cache/source de données. L'option Connexions au rapport n'apparaît que si les tables sont compatibles.

Résumé conseil : Lorsque vous choisissez la méthode optimale pour lier les filtres du tableau croisé dynamique aux valeurs de cellule ou pour créer des tableaux de bord interactifs, tenez compte de votre niveau requis d'automatisation, des limites de version d'Excel et de l'autorisation de VBA/macros dans votre environnement. Pour des besoins de base, les segments et les formules (GETPIVOTDATA) offrent des résultats rapides et robustes. Pour une automatisation avancée, la solution VBA offre un meilleur contrôle. Vérifiez toujours que les noms de champs et les éléments de filtre sont utilisés de manière cohérente pour des résultats précis. En cas d'erreurs, vérifiez les valeurs d'entrée des cellules et assurez-vous que tous les noms correspondent exactement entre le code, les formules et l'ensemble de données.

Articles connexes :

• Comment combiner plusieurs feuilles en un tableau croisé dynamique dans Excel ?
• Comment créer un tableau croisé dynamique à partir d'un fichier texte dans Excel ?
• Comment filtrer un tableau croisé dynamique basé sur une valeur de cellule spécifique dans Excel ?