Comment filtrer un tableau croisé dynamique en fonction d'une valeur de cellule spécifique dans Excel ?
Dans Excel, les tableaux croisés dynamiques sont largement utilisés pour résumer, analyser et explorer efficacement les données. Par défaut, le filtrage dans un tableau croisé dynamique se fait généralement en sélectionnant les éléments souhaités dans le menu déroulant du filtre. Bien que cette approche offre une certaine flexibilité, il existe des scénarios où une méthode de filtrage plus dynamique est nécessaire - par exemple, vous pourriez vouloir que les résultats du tableau croisé dynamique changent automatiquement en fonction de la valeur saisie dans une cellule spécifique de la feuille de calcul. Cela est particulièrement utile lors de la préparation de tableaux de bord, l'automatisation des flux de travail ou la création de rapports interactifs pour les utilisateurs finaux qui pourraient ne pas être à l'aise avec le filtrage manuel.
Excel ne fournit pas de fonctionnalité standard qui relie nativement la valeur d'une cellule à un filtre de tableau croisé dynamique (sans utiliser de code). Cependant, il existe plusieurs techniques pratiques pour répondre à cette exigence, chacune ayant ses propres avantages et points à considérer. Ce tutoriel présente d'abord une méthode VBA simple pour connecter directement une cellule à un filtre de tableau croisé dynamique, afin que ce dernier se mette à jour instantanément lorsque la valeur de la cellule change. De plus, nous aborderons des méthodes alternatives, telles que l'utilisation de formules Excel (par exemple, GETPIVOTDATA, FILTER) pour afficher des résultats filtrés, et l'utilisation de Segmentations comme contrôles graphiques de filtres. Comprendre ces options vous aide à choisir la meilleure méthode pour votre flux de travail Excel et l'expérience utilisateur.
➤ Filtrer un tableau croisé dynamique en fonction d'une valeur de cellule spécifique avec du code VBA
➤ Formule Excel - Afficher les résultats filtrés du tableau croisé dynamique en fonction d'une valeur de cellule
➤ Autres méthodes intégrées d'Excel - Utiliser des Segmentations comme filtres interactifs pour les tableaux croisés dynamiques
Filtrer un tableau croisé dynamique en fonction d'une valeur de cellule spécifique avec du code VBA
Si vous recherchez une véritable interactivité dynamique - c'est-à-dire lorsque vous saisissez une valeur dans une cellule et que le filtre du tableau croisé dynamique réagit automatiquement au changement - VBA offre une solution directe. Cela est particulièrement utile dans les tableaux de bord, les modèles pour collègues ou les situations où des ajustements de filtre rapides sont nécessaires en modifiant une seule cellule. Cependant, cette méthode nécessite une familiarité de base avec l'éditeur VBA, et, comme pour toutes les macros, votre classeur doit être enregistré dans un format prenant en charge les macros (.xlsm).
Le code VBA suivant vous permet de lier dynamiquement une cellule de feuille de calcul à un filtre de tableau croisé dynamique. Suivez ces étapes attentivement et assurez-vous de modifier le nom de la feuille de calcul, le nom du tableau croisé dynamique et la référence du champ selon les besoins dans votre classeur :
Étape 1 : Entrez la valeur par laquelle vous souhaitez filtrer votre tableau croisé dynamique dans une cellule de la feuille de calcul (par exemple, tapez ou sélectionnez la valeur de filtrage dans la cellule H6).
Étape 2 : Ouvrez la feuille de calcul contenant votre tableau croisé dynamique cible. Faites un clic droit sur l'onglet de la feuille en bas d'Excel et sélectionnez Afficher le code dans le menu contextuel. Cela ouvre la fenêtre de l'éditeur VBA pour la feuille de calcul.
Étape 3 : Dans la fenêtre Microsoft Visual Basic for Applications (VBA) ouverte, collez le code suivant dans le module de code de la feuille de calcul (et non un module standard) :
Code VBA : Filtrer un tableau croisé dynamique en fonction d'une valeur de cellule
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:H7")) 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 :
- "Sheet1" est la feuille de calcul contenant le tableau croisé dynamique. Ajustez si nécessaire.
- "PivotTable2" est le nom de votre tableau croisé dynamique. Vous pouvez le trouver dans l'onglet Analyse du tableau croisé dynamique.
- "Category" est le champ que vous souhaitez filtrer. Il doit correspondre exactement au nom du champ.
- H6 est la cellule de filtrage. Assurez-vous que la valeur correspond à un élément de la liste de filtres.
- Les valeurs de filtrage doivent correspondre caractère par caractère. Les espaces supplémentaires ou les fautes de frappe peuvent provoquer des erreurs ou des résultats vides.
Étape 4 : Appuyez sur Alt + Q pour fermer l'éditeur VBA et revenir à Excel.
Maintenant, votre tableau croisé dynamique devrait automatiquement filtrer pour n'afficher que les données correspondant à la valeur entrée dans la cellule H6. Cette macro s'exécute chaque fois que la valeur dans H6 change, facilitant ainsi l'ajustement dynamique de votre résumé de données.
Vous pouvez modifier la valeur dans la cellule de filtrage à tout moment - le tableau croisé dynamique se mettra à jour instantanément dès que le contenu de la cellule sera modifié ou remplacé.
Dépannage :
- Assurez-vous que les macros sont activées dans votre classeur.
- Revérifiez que les noms de la feuille de calcul, du tableau croisé dynamique et des champs correspondent à votre configuration réelle.
- Assurez-vous que la valeur de filtrage dans H6 correspond exactement aux valeurs du tableau croisé dynamique.
- Cette approche VBA fonctionne pour les filtres à un seul champ. Pour plusieurs champs, un script supplémentaire est requis.
Formule Excel – Afficher les résultats filtrés du tableau croisé dynamique en fonction d'une valeur de cellule
Pour les utilisateurs qui préfèrent ne pas activer les macros, Excel propose des approches basées sur des formules pour afficher les résultats du tableau croisé dynamique en fonction d'une valeur de cellule spécifique. Bien que des fonctions comme GETPIVOTDATA et FILTER ne modifient pas réellement les paramètres de filtre du tableau croisé dynamique, elles peuvent référencer dynamiquement et présenter des résultats récapitulatifs qui répondent aux entrées de l'utilisateur.
Cette solution est particulièrement utile lors de la création de tableaux récapitulatifs personnalisés, de tableaux de bord ou de rapports qui reflètent les critères changeants saisis par l'utilisateur - sans altérer la vue originale du tableau croisé dynamique.
Utilisation de GETPIVOTDATA :
Supposons que votre tableau croisé dynamique (nommé "PivotTable2") résume les ventes par catégorie, et que la valeur de filtrage est entrée dans la cellule H6. Vous pouvez utiliser GETPIVOTDATA pour afficher le total des ventes pour la catégorie spécifiée dans H6 :
1. Sélectionnez la cellule où vous souhaitez afficher le résultat récapitulatif (par exemple, I6) :
=GETPIVOTDATA("Sum of Sales", $A$4, "Category", $H$6)2. Appuyez sur Entrée. Lorsque vous changez la valeur dans H6, le résultat dans I6 se met à jour automatiquement pour refléter le résumé correspondant du tableau croisé dynamique.
Si votre tableau croisé dynamique utilise des noms de champs ou une disposition différente, ajustez la formule en conséquence. Pour générer automatiquement une formule GETPIVOTDATA, tapez = dans une cellule, puis cliquez sur une cellule de valeur dans votre tableau croisé dynamique. Excel insérera la formule appropriée, que vous pourrez ensuite modifier selon vos besoins.
Utilisation de FILTER avec une Table Helper :
Si vous souhaitez extraire des enregistrements détaillés de votre jeu de données d'origine (plutôt que simplement des résumés du tableau croisé dynamique), et que vous utilisez Excel 365 ou Excel 2019, la fonction FILTER permet un filtrage dynamique basé sur une valeur de cellule :
Supposons que vos données sources soient dans la plage A1:C100 et que Category soit dans la colonne A.
1. Sélectionnez la cellule de départ où les enregistrements filtrés doivent apparaître (par exemple, J6) :
=FILTER(A2:C100, A2:A100 = H6, "No data")2. Appuyez sur Entrée. Les lignes correspondantes se déverseront dans les cellules adjacentes, listant tous les enregistrements où la catégorie correspond à la valeur dans H6. La mise à jour de H6 actualisera instantanément les résultats.
Pour faire correspondre les groupements de tableaux croisés dynamiques ou filtrer selon plusieurs critères, envisagez de combiner GETPIVOTDATA et FILTER, ou d'étendre la formule avec des conditions logiques supplémentaires.
📝 Conseils & Avertissements :
- Ces formules ne modifient pas le filtre réel du tableau croisé dynamique. Elles fournissent uniquement une vue distincte et dynamique basée sur les valeurs des cellules.
- Pour changer directement les filtres du tableau croisé dynamique, VBA est requis.
- Assurez-vous que les noms de champs utilisés dans
GETPIVOTDATAcorrespondent exactement à ceux du tableau croisé dynamique (casse et espacement). - Si vous voyez des erreurs
#REF!, vérifiez que vos références sont valides et que la structure du tableau croisé dynamique n'a pas changé.
Autres méthodes intégrées d'Excel – Utiliser des Segmentations comme filtres interactifs pour les tableaux croisés dynamiques
Si les solutions VBA ou basées sur des formules ne conviennent pas pleinement à votre flux de travail, les Segmentations d'Excel offrent une autre méthode interactive pour filtrer les tableaux croisés dynamiques. Les Segmentations sont des contrôles de filtre visuels qui permettent aux utilisateurs de filtrer les données avec une interface simple point-and-click. Bien qu'elles ne puissent pas être liées directement aux valeurs des cellules - ce qui signifie que vous ne pouvez pas changer une cellule pour contrôler une Segmentation - elles sont intuitives et très efficaces pour les tableaux de bord et rapports utilisés par des utilisateurs non techniques.
Comment ajouter et utiliser une Segmentation :
- Sélectionnez n'importe quelle cellule dans votre tableau croisé dynamique.
- Allez à l'onglet Analyser le tableau croisé dynamique (ou onglet Analyser dans les versions antérieures), et cliquez sur Insérer une Segmentation.
- Dans la boîte de dialogue Insérer des Segmentations, cochez le champ que vous souhaitez filtrer (par exemple, Category), puis cliquez sur OK.
- La Segmentation apparaîtra sur votre feuille de calcul. Cliquez sur un bouton pour filtrer le tableau croisé dynamique par cette valeur. Maintenez Ctrl pour sélectionner plusieurs éléments.
Les Segmentations peuvent être formatées, redimensionnées et liées à plusieurs tableaux croisés dynamiques pour un filtrage synchronisé entre différents rapports. Elles sont particulièrement utiles dans les tableaux de bord ou les classeurs partagés où les utilisateurs peuvent ne pas être à l'aise avec les filtres déroulants mais ont encore besoin de filtrer facilement les données sans utiliser VBA ni éditer des formules.
Limitations : Les Segmentations ne prennent pas en charge la liaison native aux valeurs des cellules. Si votre flux de travail nécessite un filtrage dynamique contrôlé par une saisie dans une cellule, les Segmentations doivent être considérées comme un outil complémentaire plutôt qu'un substitut aux méthodes basées sur VBA ou des formules.
De plus, si vos données sont stockées dans une Table Excel (pas un tableau croisé dynamique), vous pouvez toujours utiliser des Segmentations en sélectionnant la table et en allant à l'onglet Conception de la Table > Insérer une Segmentation.
Dépannage : Si la Segmentation ne semble pas filtrer le tableau croisé dynamique, vérifiez les Connexions au Rapport (sous l'onglet Segmentations ou Analyser) pour vous assurer qu'elle est correctement connectée au(x) tableau(x) croisé(s) dynamique(s) prévu(s).
Chacune des méthodes ci-dessus sert un objectif différent : VBA permet le filtrage lié à une cellule, les formules fournissent une présentation dynamique des résultats, et les Segmentations offrent un filtrage graphique convivial. Choisissez l'approche qui correspond le mieux à vos besoins en termes d'automatisation, de flexibilité et de facilité d'utilisation. Les filtres déroulants traditionnels des tableaux croisés dynamiques restent disponibles comme option de secours de base.
Articles connexes :
- Comment combiner plusieurs feuilles dans un tableau croisé dynamique dans Excel ?
- Comment créer un tableau croisé dynamique à partir d'un fichier texte dans Excel ?
- Comment lier un filtre de tableau croisé dynamique à une certaine cellule dans Excel ?
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels