Contrôler plusieurs tableaux croisés dynamiques avec un seul menu déroulant

Publié le 09 mars 2015
par Sophie Marchand M.Sc.
Liste synchronisée - Tableaux croisés dynamiques

Depuis la venue d’Excel 2010, nous avons la chance de bénéficier des segments (slicers) afin de mettre à jour plusieurs tableaux croisés dynamiques simultanément. Il existe toutefois différentes raisons pour lesquelles, dans certaines situations, nous préférons ne pas utiliser les segments mais plutôt utiliser un menu déroulant, afin de contrôler nos tableaux croisés dynamiques et le cas échéant, les graphiques qui y sont rattachés. Cet article vous présente 4 façons de créer un menu déroulant capable de contrôler plusieurs tableaux croisés dynamiques, chacune avec ses avantages et ses désavantages.

 

Option 1: Les contrôles de formulaires

La première option est d’utilier un contrôle de formulaire, plus précisément, une zone de liste déroulante, afin de contrôler plusieurs tableaux croisés dynamiques simultanément.

Liste synchronisée - Tableaux croisés dynamiques

 

Dans l’exemple ci-bas, nous avons d’abord créé une liste des régions de notre base de données (cellules C17 à C24). Ensuite, nous avons inséré une zone de liste déroulante, que nous avons associée à la cellule A17. En B17, nous avons inséré une fonction Index, afin de récupérer le nom de la région dans la liste.

Liste synchronisée - Tableaux croisés dynamiques

 

Par la suite, nous avons inséré le code VBA suivant, qui spécifie que le filtre de nos deux tableaux croisés dynamiques doit correspondre à la valeur comprise dans la cellule B17.

Liste synchronisée - Tableaux croisés dynamiques

 

Finalement, nous devons assigner notre code VBA à notre zone de liste déroulante (Assign Macro). Par la suite, chaque fois que nous ferons la sélection d’une région dans notre liste, les deux tableaux croisés dynamiques se mettront à jour automatiquement.

Liste synchronisée - Tableaux croisés dynamiques

 

Avantages:  Optimisation de l’espace, facilité d’usage.

Désavantages: Impossible de sélectionner plusieurs items simultanément, difficle à utiliser dans un contexte où il y a de nombreux choix dans la liste, impossible de chercher dans la liste avec un moteur de recherche ou avec des critères de recherche avancés.

 

Option 2: L’auto-complete

Il est également possible de laisser une cellule vide, afin que l’usager spécifie lui-même son choix en l’écrivant dans la cellule.

Liste synchronisée - Tableaux croisés dynamiques

 

Afin de profiter de l’option Auto Complete d’Excel, nous suggérons de placer votre liste d’items de votre menu déroulant juste au-dessus de la cellule où l’usager doit inscrire le nom de la région choisie. Dans l’exemple ci-bas, quand l’usager entrera un F dans la cellule B17, Excel écrira automatiquement France, grâce à son option “Auto-complete”.

Liste synchronisée - Tableaux croisés dynamiques

 

 

Ici, au lieu de créer une zone de liste déroulante, nous créerons un bouton, auquel nous assignerons notre macro. Il faudra donc cliquer sur le bouton “Modifier” pour mettre à jour les deux tableaux croisés dynamiques.

Filtre de tableau croisé dynamique

 

Avantages:  Optimisation de l’espace, facilité d’usage, plus facile à utiliser dans un contexte où il y a de nombreux choix dans la liste.

Désavantages: Impossible de sélectionner plusieurs items simultanément, impossible de chercher dans la liste avec un moteur de recherche ou avec des critères.

 

Vous aimeriez être un Ninja Excel et devenir la référence ultime de votre bureau, ou tout le moins ne plus avoir l’impression de stagner ? Développez des solutions innovatrices en explorant des fonctions et fonctionnalités souvent méconnues mais fort utiles avec nos formations pour devenir Ninja d’Excel.

 

Option 3: Le tableau croisé dynamique bidon

Une autre option est d’insérer un tableau croisé dynamique bidon, que l’on cachera en entier par la suite, sauf pour la portion filtre.

Liste synchronisée - Tableaux croisés dynamiques

 

Ce faisant, nous pourrons bénéficier du moteur de recherche intégré du filtre du tableau croisé dynamique bidon.

Filtre de tableau croisé dynamique

 

Ici aussi, nous créerons un bouton, auquel nous assignerons notre macro. Il faudra donc cliquer sur le bouton “Modifier” pour mettre à jour les deux tableaux croisés dynamiques.

Liste synchronisée - Tableaux croisés dynamiques

 

Avantages:  Optimisation de l’espace, facilité d’usage, plus facile à utiliser dans un contexte où il y a de nombreux choix dans la liste, plus facile à utiliser quand l’usager ne sait pas exactement ce qu’il recherche.

Désavantages: Impossible de sélectionner plusieurs items simultanément.

 

Option 4: Le tableau croisé dynamique bidon et les segments

Une option intéressante et complète est de créer un tableau croisé dynamique bidon (encore une fois) et de lier ce dernier à un segment (slicer), qui lui, sera lié également aux deux autres tableaux croisés dynamiques. Ce faisant, lorsque l’utilisateur fera une sélection, simple ou multiple, dans le filtre du tableau croisé dynamique bidon (caché en partie), les deux tableaux croisés dynamiques liés se mettront à jour en conséquence. Ici, nous n’avons donc pas besoin de macro et nous avons le loisir de présenter ou non le segment (slicer) rattaché.

Liste synchronisée - Tableaux croisés dynamiques

 

Avantages:  Optimisation de l’espace, facilité d’usage, plus facile à utiliser dans un contexte où il y a de nombreux choix, plus facile à utiliser quand l’usager ne sait pas exactement ce qu’il recherche, possibilité de sélectionner plusieurs choix.

 

 


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

 

Formation complémentaire

Pour apprendre des façons créatives d’utiliser Excel pour créer des solutions surprenantes et régler des problématiques qui semblent parfois sans solution, suivez la formation Excel – Trucs de pro.

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Le CFO masqué - Commentaires d'apprenants ayant suivi la formation : Excel - Trucs de pro

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

20 réflexions sur “Contrôler plusieurs tableaux croisés dynamiques avec un seul menu déroulant”

  1. Bonjour pour mon cas cette macro ne fonction pas, voici le code qu j’ai entrer:
    Sub ListeSynchro()

    ‘ListeSynchro Macro

    ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Jour de conso”).ClearAllFilters
    ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Jour de conso”).CurrentPage = ActiveSheet.Range(“AC”).Value
    ActiveSheet.PivotTables(“PivotTable2”).PivotFields(“Jour de conso”).ClearAllFilters
    ActiveSheet.PivotTables(“PivotTable2”).PivotFields(“Jour de conso”).CurrentPage = ActiveSheet.Range(“AC”).Value
    ActiveSheet.PivotTables(“PivotTable3”).PivotFields(“Jour de conso”).ClearAllFilters
    ActiveSheet.PivotTables(“PivotTable3”).PivotFields(“Jour de conso”).CurrentPage = ActiveSheet.Range(“AC”).Value

    End Sub

    Ps: L’erreur afficher est : Erreur d’exécution ‘1004’: erreur définie par l’application ou par l’objet

    Merci de m’aider pour trouver une solution pour cette erreur

    1. Francis Paquet, M.Sc., ing., EEE

      Bonjour,

      Une erreur 1004 est l’erreur générale de VBA. En général, cela veut dire que VBA ne trouve pas l’objet en question. Dans ces lignes de codes, il y a 6 objets que vous devez vérifier:
      1- Vous faites référence à ActiveSheet, donc l’appel de la macro VBA et les 3 Tableaux Croisés Dynamiques (TCD) doivent être dans cette même feuille d’où la macro est lancée;
      2- Vous faites référence à 3 TCD PivotTable1, PivotTable2 et PivotTable3, êtes-vous certains qu’Excel leur a donné ces noms? Quand vousêtes dans le TCD, le Menu Analyse du TCD vbous permet de voir (et de modifier) le nom du TCD;
      3- Également, la syntaxe fait en sorte qu’un champ Jour de conso doit exister dans votre base de données et doit être utilisé comme filtre du TCD;
      4- Finalement, vous spécifiez un champ nommé, AC. Est-il défini? Est-il défini globalement ou seulement pour la feuille active? S’il est défini localement, ActiveSheet doit être la feuille d’où la macro VBA est exécutée. S’il est défini globalement, vous n’avez pas besoin de ActiveSheet., c’est superflu.

      Voilà, faites ces test et tenez-moi au courant,

      Francis

  2. Bonjour,

    J’ai tenté la première option que vous proposez, la liste déroulante pour lier les filtres de 3 TCD.

    J’ai un listing de nom (situé en M3:M20), que j’aimerai pouvoir utilisé via cette fameuse liste déroulante. Comme expliqué dans votre article, j’ai réussi à créer cette liste avec mon listing de nom, qui, lorsque je change de nom actualise une case (J3 dans mon cas). Cette case évolue donc de 1 à 18 selon le nom que je choisi.
    Cependant j’ai un petit soucis avec la fonction INDEX, elle ne s’actualise pas lorsque que je choisis un nouveau nom dans la liste, alors que la case lié à la liste déroulante s’actualise bien.
    J’ai remarqué qu’elle s’actualise seulement lorsque j’enregistre mon fichier excel, ce qui est tout même un peu lourd.

    Pour résumer, j’ai :
    – une liste de nom (M3:M20)
    – une liste déroulante de ces noms, lié à la cellule J3 (évoluant donc de 1 à 18) ayant pour plage de donnée M3:M20
    – ma fonction INDEX(M3:M20;J3) situé en K3 qui ne s’actualise pas lorsque je choisis un nouveau nom.

    Si vous avez remarqué des incohérences, ou des idées pour palier à ce problème je suis preneur.

    Merci d’avance.

    Maxime

  3. Bonjour,

    Je suis contrôleuse de gestion et j’ai énormément de TCD à mettre à jour chaque semaine.

    Actuellement je dois aller dans chaque onglet (1 TCD par onglet), ajouter la semaine du chiffre d’affaires concerné et ce pour tous mes tableaux et chaque semaine.

    Je souhaiterai donc mettre en place un filtre dans ma page d’accueil (1er onglet de mon fichier Excel) ou je coche la semaine (en complément des autres déjà cochées) et souhaiterais que cela m’automatise tous mes tableaux.

    Mon soucis est que je n’arrive pas à effectuer ma macro, pourriez-vous m’aider ?

    Merci par avance,

    Coralie

  4. LANGLOIS Alexandre

    Bonjour je veux utiliser un formulaire pour contrôler mes tableaux dynamiques mais j’ai un problème avec le code VBA que je ne connais pas bien :

    Sub Zonedeliste1_QuandChangement()


    ‘Macro auto liste déroulante

    ActiveSheet.PivotTables(“Tableau croisé dynamique1”).PivotFields(“SDCI 2017”).ClearAllFilters
    ActiveSheet.PivotTables(“Tableau croisé dynamique1”).PivotFields(“SDCI 2017”).CurrentPage = ActiveSheet.Range(“A1”)
    ActiveSheet.PivotTables(“Tableau croisé dynamique2”).PivotFields(“SDCI 2017”).ClearAllFilters
    ActiveSheet.PivotTables(“Tableau croisé dynamique2”).PivotFields(“SDCI 2017”).CurrentPage = ActiveSheet.Range(“A1”).Value

    End Sub

    L’erreur “1004 erreur définie par l’application ou par l’objet” s’affiche à la 2ème ligne du code.
    Pourtant le nom des tableaux dynamiques sont correctes, le champ “SDCI 2017” existe et la cellule A1 est la cellule de référence de l’index.

    Merci d’avance.

  5. LANGLOIS Alexandre

    Voici mon code :
    Sub Zonedeliste1_QuandChangement()


    ‘Macro auto liste déroulante

    ActiveSheet.PivotTables(“Tableau croisé dynamique1”).PivotFields(“SDCI 2017”).ClearAllFilters
    ActiveSheet.PivotTables(“Tableau croisé dynamique1”).PivotFields(“SDCI 2017”) = ActiveSheet.Range(“A1”)
    ActiveSheet.PivotTables(“Tableau croisé dynamique2”).PivotFields(“SDCI 2017”).ClearAllFilters
    ActiveSheet.PivotTables(“Tableau croisé dynamique2”).PivotFields(“SDCI 2017”) = ActiveSheet.Range(“A1”)

    End Sub

    Et l’erreur “1004” me dit qu’il est impossible de lire la propriété PivotFields de la classe PivotTable..

    PS: Ce sont des tableaux dynamiques copiés sur une même page

    Pouvez-vous m’aider ?
    Merci

    1. Francis Paquet, M.Sc., ing., EEE

      Alexandre,

      Manipulez des filtres de tableaux croisés dynamique en VBA est complexe. Vous pouvez essayer une syntaxe comme celle qui suit pour votre deuxième ligne de commande. Et les lignes 3 et 4 sont une répétition? Pourquoi?
      Donc, la commande serait
      ActiveSheet.PivotTables(« Tableau croisé dynamique1 »).PivotFields(« SDCI 2017 »).pivotfilters.add type:=xlvalueequals , value1:=activesheet.range(« A1 »).value

      Faites-en l’essai et tenez-moi au courant

      Francis

  6. Bonjour,

    je voudrais savoir comment faire si la liste déroulante est sur un autre onglet ?
    le filtre de la liste est sur l’onglet “PIC” et les 2 tableaux sont sur l’onglet “Module TCD”

    merci d’avance

    Jean-Pierre

  7. Bonjour,

    Voici mon code pour enlever un filtre de mon TDC.

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target = ActiveSheet.Range(“cell_nofrais”) Then
    ActiveSheet.PivotTables(“TCD_FACTURATION”).PivotFields(“client”).ClearAllFilters
    end if

    end sub

    Lorsque le code exécute la ligne du “clearfilters”, la macro recommence au début sans donner de message d’erreur jusqu’à ce que le “IF” soit ex/cut/ une deuxième fois et donne un message d’erreur “Incompatibilité de type”.

    Si je sort la ligne du IF, la macro recommence sans cesse sans jamais faire de message d’erreur.

    Merci d’avance pour votre aide.

    1. Bonjour,

      J’ai trouvé mon problème. Mon code est activé sur un changement dans ma feuille et j’ai oublié de désactivé “EnableEvents” . Donc, dès que mon code effectue en changement sur ma feuille, la fonction recommence à chaque fois.

      Erreur de débutant.

      merci tout de même.

        1. Bonjour,

          J’ai essayé la macro 1) qui marche très bien,
          En revanche, j’ai plus d’une cinquantaine de TCD sur une même feuille (voire une centaine).
          Auriez vous une Macro pour un nombre illimité de macro sur une même feuille ?
          Cela m’éviterait de faire un code de plus de cent lignes…

          Merci beaucoup par avance pour votre aide,

  8. Bonjour,

    Vous indiquer dans votre procédure “assigner notre code VBA à notre zone de liste déroulante”, pouvez vous indiquer comment faire cette opération car je suis bloqué à ce niveau…

    Merci à vous,

  9. Bonjour,

    Je tiens tout d’abord à vous remercier, ce forum m’a beaucoup aidé.

    Sur le même sujet j’aimerais si c’est possible avoir de l’aide sur l’un de mes fichiers. Voilà j’ai une base de données de la hiérarchie de l’entreprise et j’aimerais créer un planning des absences, j’aurais besoin d’automatiser ce fichier et pour cela j’ai créé une liste déroulante et des lignes avec rechercheV donc la liste me donne le manager et les lignes recherchent son équipe sauf que j’ai essayé de le faire avec plusieurs TCD (TCD N+3, TCD N+2…) et ma liste ramène que les N+3, mais il faudrait que les autres TCD s’actualisent aussi en fonction de la liste afin de conditionner la recherche a une table bien précise (si le manager est N+3 => la recherche se fera dans la liste des N+2 … ETC) (SVP, comment je rajoute mon fichier?)

    Merci par avance pour vos retours

  10. Bonjour,
    pourriez vous me détailler cette étape
    “nous avons inséré une zone de liste déroulante, que nous avons associée à la cellule A17. En B17, nous avons inséré une fonction Index, afin de récupérer le nom de la région dans la liste.”
    Merci d’avance

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut