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.
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.
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.
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.
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.
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”.
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.
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.
Ce faisant, nous pourrons bénéficier du moteur de recherche intégré du filtre du tableau croisé dynamique bidon.
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.
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é.
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 :
La 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é.
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
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
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
Bonjour Maxime,
Êtes-vous certain que vous travaillez en mode de calcul automatique et non en mode de calcul manuel?
Si ce problème survient lorsque vous êtes en mode automatique, svp, veuillez poser votre question dans notre forum et y insérer votre fichier.
Merci,
Sophie
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
Bonjour Coralie,
Pourriez-vous svp poser votre question dans notre forum et y déposer votre fichier (ou un exemple ce celui-ci) afin que nous puissions répondre à votre question directement à partir de votre fichier?
Merci,
Sophie
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.
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
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
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
Bonjour,
Avec laquelle des 4 méthodes proposées?
Sophie
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.
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.
François,
Cela aurait effectivement été ma réponse…
Félicitations de l’avoir trouvé par vous-même.
Francis
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,
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,
Bonjour,
Vous faites un clic droit de souris sur la zone de liste déroulante et vous choisissez “Assigner une macro”.
Sophie
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
Bonjour,
Vous pouvez poser votre question et y joindre votre fichier sur notre forum ici: https://www.lecfomasque.com/forum/.
Au plaisir,
Sophie
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