Discussion :

[Josvin]

Bonjour,

J'ai beau chercher un peu partout, je ne trouve pas la réponse à ma question. Peut-être simplement parce que ça n'est pas faisable, mais je préfère demander avis à des spécialistes avant d'abandonner mon idée.

J'aimerais savoir s'il est possible de cocher automatiquement des cases dans une liste déroulante, selon le texte renseigné dans une autre cellule.
De manière plus concrète :
J'ai une liste déroulante dans une cellule regroupant les âges d'une population (de 18 à 65 ans), utilisée pour faire des comparaisons avec une personne donnée, en fonction de son âge. Selon l'âge de la personne que je veux comparer, je coche systématiquement les cases à + ou - 5 ans (exemple : j'ai une personne de 30 ans, je vais la comparer aux personnes de 25 à 35 ans). Afin de m'éviter de cocher 11 cases à chaque fois, est-il possible que, lorsque je tape par exemple "30" dans une cellule adjacente, les cases de 25 ans à 35 ans se cochent automatiquement dans la liste déroulante ?
Et si oui, évidemment, comment ?

Merci d'avance pour votre aide :-)

Josvin

[Menhir]

Je ne suis pas sûr d'avoir compris ta question mais je pense que tu compliques le problème en mettant deux listes déroulantes dans les cellules cibles.

Si la cellule dans laquelle se trouve l'age de référence est A1, il suffirait de mettre dans les cellules cibles une formule, respectivement =A1-5 et =A2+5 tout simplement.

[Josvin]

En effet, ça pourrait se régler aussi simplement... si ma liste déroulante n'était pas extraite d'un tableau croisé dynamique (d'où la nécessité de ma liste) ^^

Je te mets mon fichier (fictif, reprenant le même type de données que l'original dont je ne peux communiquer les données) en PJ, peut-être que ça t'éclaira d'avantage.

Fictif.xlsx

L'idée serait de renseigner l'âge de la personne à comparer en C3, et qu'automatiquement se cochent les âges à plus ou - 5 ans dans B3.

Penses-tu que ce soit faisable ?

[Pierre Fauconnier]

Salut.

Remarque préliminaire: Travaille avec un tableau structuré, ce sera beaucoup plus simple.

Perso, j'ajouterais:

• une cellule nommée Age qui contiendrait l'âge de référence (par exemple en C3 de la feuille du TCD);
• une colonne dans le tableau qui alimente le TCD et qui contient la formule =ET([@Age]>=Age-5;[@Age]<=Age+5). Cette formule renvoie VRAI pour les âges se trouvant dans la limite que tu souhaites.

Dans le TCD, au lieu d'avoir une liste déroulante sur les âges en B3, tu places le champ "Sélection" et tu filtres sur VRAI. Dès lors, dès que tu changes l'âge en C3, les formules du tableau source se mettent à jour, et l'actualisation de ton tcd reprendra les données des personnes dont l'âge se trouve dans la plage choisie...

[Josvin]

Salut,

Premièrement, merci pour l'astuce du tableau structuré, que je ne connaissais absolument pas, et qui semble éviter de nombreux problèmes !

Et surtout, merci beaucoup pour ta réponse très détaillée à ma question.
Effectivement, la solution répond à mon besoin de manière pragmatique.

Je crains cependant qu'elle ne soit claire que pour moi, qui crée le classeur, mais qu'elle ne facilite pas la lecture et l'utilisation du TCD par d'autres utilisateurs (qui a pour but final d'être partagé). Bien que je puisse remplacer "sélection" par "age " dans mon TCD pour une lecture plus facile, le terme VRAI et le fait de devoir actualiser le TCD à chaque modification de C3 ne rendent pas l'utilisation et la lecture plus évidente. A mon (très humble) avis.
A moins que tu aies une solution pour remplacer le terme VRAI dans le TCD par (par exemple) "25<>35"..?

Si ça n'est pas faisable, et s'il n'est pas possible non plus d'appliquer un filtre classique automatique selon la donnée en C3, à ce stade je pense que nous allons devoir nous en tenir au cochage manuel des 11 cages pour chaque cas de comparaison ^^

Merci encore pour ton aide précieuse :-)

[Pierre Fauconnier]

Je pense que les objections que tu soulèves seront rencontrées par la méconnaissance de l'outil et l'absence de formation sur celui-ci. Je pense honnêtement que 10 minutes d'explication ou un A5 dans Word expliquant à quoi correspondent les notions suffisent pour que les utilisateurs puissent utiliser la solution. Le principe est que ce qui se trouve dans "Sélection" soit invariant au niveau du TCD. Si ce n'est pas invariant, tu t'exposes forcément au souci de devoir cocher/décocher (à la main ou par macro)...

Tu peux bien sûr modifier les intitulés des colonnes et la valeur renvoyée dans la cellule





L'actualisation du TCD, c'est à nouveau plus une affaire de formation qu'un problème, en soi. Tu pourrais, par VBA, actualiser le TCD lors de la modif de C3, par exemple.

Cela étant. Si tu dois ne renvoyer chaque fois qu'une ligne, parce que tu ne choisis qu'une plage à la fois, passe-toi de TCD et oriente-toi vers un simple petit tableau avec des formules telles que MOYENNE.SI.ENS... Tu n'auras alors plus besoin ni de la colonne que j'ai ajoutée dans le TCD, ni de l'actualisation du TCD. Comme tu tagues XL365, je te propose MIN.SI.ENS, MOYENNE.SI.ENS et MAX.SI.ENS. Si le fichier devait tourner sur de plus anciennes versions, il faudrait passer par des formules matricielles, mais ce n'est pas un problème en soi...

[Josvin]

Tu as tout à fait raison, c'est un problème de formation et de méconnaissance de l'outil, qu'il ne m'incombe malheureusement pas de résoudre...
Quelques explications pourraient faire l'affaire, mais je me dois d'apporter une solution clé en main et intuitive, même pour les non initiés. Ce que je m'efforce de faire.

Je pense retenir ta solution (encore merci) de modifier la valeur renvoyée dans la cellule. Grace à l'amorce de ta formule, je peux la déployer à chaque tranche d'âge, du type
=SI.CONDITIONS(Age=40;SI(ET([@Age]>=Age-5;[@Age]<=Age+5);"35 à 45 ans";"DO NOT USE");Age=30;SI(ET([@Age]>=Age-5;[@Age]<=Age+5);"25 à 35 ans";"DO NOT USE"), etc.)Ce sera très fastidieux et me demandera d'imbriquer un grand nombre de tests logiques (autant que d'âges différents dans mon tableau source), mais ça devrait fonctionner. Je soumettrai le fichier une fois que j'aurai terminé.

Le temps que je fasse ce long travail à la chaîne, ta remarque concernant l'actualisation automatique, par VBA, du TCD lors de la modif de C3 a retenu tout mon intérêt... Saurais-tu m'indiquer la marche à suivre ?

Je note également ta remarque à propos de MOYENNE.SI.ENS. etc., et je t'en remercie. Cependant, le fichier que nous utilisons ici reste fictif, le fichier sur lequel je travaille est bien plus conséquent et nécessite impérativement la création de plusieurs TCD.

[Josvin]

Ca n'était finalement pas si long que ça...

Voici ce que ça donne :
tcd-Ages.xlsx

Je crois que ça répond à ma demande initiale, si ce n'est l'actualisation automatique du TCD. Si tu peux m'aider sur ce point, je suis preneur

Merci encore pour ton temps !

[Pierre Fauconnier]

Je pense qu'il y avait "un peu" plus court... . Note au passage que l'intervalle calculé est de 11 ans. Il serait bon d'exclure soit le plancher, soit le plafond si tu veux dix ans par tranche.




Pour l'actualisation, tu vas devoir enregistrer ton fichier en xlsm car il y aura une macro...

Dans le module de la feuille 1, il faut placer le code suivant, qui va actualiser le TCD après la modification de la cellule Age. Pour que cela puisse se faire, j'ai, dans le TCD filtré en décochant "DOT NOT USE" de sorte que c'est ce filtre-là qui a été mémorisé. Du coup, à l'actualisation, il prend tout ce qui n'est pas "DO NOT USE", c'est-à-dire l'unique tranche d'âge récupérée...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = Range("age").Address Then
    Feuil1.PivotTables(1).RefreshTable
  End If
End Sub

STP, ne prends pas mal ce que je vais te dire, c'est juste un constat et il n'y a aucune attaque personnelle.

Tu as tout à fait raison, c'est un problème de formation et de méconnaissance de l'outil, qu'il ne m'incombe malheureusement pas de résoudre[...]

Tu es aux RH, selon ton profil. Si ce n'est pas LE département où l'on parle des problèmes de formation et qu'on les règle, où en parle-t-on? Qui plus est, lorsque je parlais plus haut de formation, je ne parlais même pas de formation Excel, mais d'une formation de 10 minutes sur un outil qu'on met en place et qui demande aux gens de faire un clic droit pour actualiser un tableau. Ceux qui vont l'oublier ne l'oublieront que deux ou trois fois puis auront pigé... Je suis quand même effaré de voir que l'on fragilise un fichier en mettant une macro dedans juste pour éviter un clic droit aux utilisateurs... Si tu leur mets un clarck ou une machine à commande numérique dans les mains, tu ne les forme pas? Tu laisses s'asseoir des conducteurs au volant d'un 30 tonnes sans les former? Pourquoi serait-ce différent pour Excel? Je n'ai jamais compris qu'on pouvait mettre un outil comme Excel dans les mains d'utilisateurs sans les former sur l'outil en supposant qu'ils le connaissent. Vraiment, ça me dépasse.

La formule que tu as écrite (ça a dû te prendre du temps ^^) fait 3725 caractères! La mienne 80... La tienne limite la plage des âges utilisés, la mienne pas ^^. Je pense sincèrement que le besoin de formation est bien là et qu'il serait urgent de le résoudre afin de limiter les pertes de temps sur tes fichiers Excel et d'augmenter la fiabilité de tes classeurs... (Bien relire ce que j'ai dit plus haut avant de prendre ombrage de mon propos stp)

[Josvin]

Quelle est cette magie noire que ta formule en 80 caractères avec des "&" ?? Ca fait "un peu" plus court, en effet
J'avoue ne pas la comprendre, mais la prends !
L'intervalle de 11 ans est bel est bien voulu, pas de souci sur ce point.

Merci beaucoup pour le code! C'est la première fois que j'en utiliserai un, je vais chercher un peu pour savoir comment l'implémenter dans mon fichier de base et à chacun de ses TCD. Si au final tout ça fonctionne, je t'en suis infiniment reconnaissant !

Concernant la formation, tu as à la fois raison et à la fois tort (et non, je ne le prends absolument pas mal, je suis bien conscient de n'être capable d'exploiter qu'à peine 1% des capacités d'Excel).
C'est bien dans un département RH que ces questions sont discutées, mais ça n'est pas le mien... Il y a RH et RH, disons.
Je comprends ce que tu dis à propos de la formation pour un simple clic droit, mais dans un monde où plus personne n'a le temps de rien (c'est bien malheureux mais c'est ainsi), et dans une logique de la facilitation du changement (je sais déjà que j'aurai des réfractaires à ce fichier, malgré sa nécessité absolue), le simple fait de devoir les former à son utilisation, au delà d'une explication de base, me fera perdre toute crédibilité, et personne ne voudra utiliser une chose "aussi compliquée" qui nécessite de faire un clic droit à chaque changement de valeur...
Tu serais peut-être surpris de constater le niveau de méconnaissance d'Excel en entreprise. Tout le monde l'utilise, mais l'utilisation se limite à la création d'un tableau et l'application de filtres par colonnes. 90% des utilisateurs lambda ne seraient pas capable de créer un TCD et sont perdus rien qu'à entendre ce mot. Par exemple, tu as pu constater mon faible niveau de connaissance sur Excel, et pourtant, partout où je suis passé (souvent des grands groupes), on m'a reconnu comme un "expert" de la chose. Je suis bien conscient de ne pas l'être (mais alors pas du tout ^^), mais c'est pour te donner une idée du niveau général... Ca fait peur !

Quoi qu'il en soit, un énorme merci pour ton aide et tes conseils plus que précieux ! J'essaie d'appliquer tout ça à mon fichier d'origine, et je te tiendrai au courant si j'ai pu obtenir le résultat voulu :-)

[Pierre Fauconnier]

Pour comprendre la formule que j'ai utilisée, tu as un outil absolument formidable dans Excel (et malheureusement trop peu connu!!), l'évaluateur de formule.



Cet outil permet d'évaluer une formule étape par étape, voire même en descendant dans les "sous-étapes"...






Voir mon billet de blog à ce sujet. Perso, cet outil est sur ma barre d'accès rapide, tellement j'en ai besoin tout le temps...

[Josvin]

Merci pour l'astuce, c'est parfait

[Josvin]

J'ai une dernière question !

Je rentre donc mes codes pour mes 3 TCD (pour mon tableau original), directement dans la feuille concernée. Evidemment, ça ne fonctionne pas ^^

J'ai rentré ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = Range("age").Address Then
    ActiveSheet.PivotTables("TCD_Age_1").RefreshTable
  End If
End Sub
 
Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = Range("age_2").Address Then
    ActiveSheet.PivotTables("TCD_Age_2").RefreshTable
  End If
End Sub
 
Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = Range("age_3").Address Then
    ActiveSheet.PivotTables("TCD_Age_3").RefreshTable
  End If
End Sub

Pourtant, si je ne fais que le premier code (pour le TCD_1), ça fonctionne !

Mais dès que je rajoute la ou les suivantes, ça ne fonctionne plus

Saurais-tu me dire pourquoi ? Comment imbriquer les 3 ?

Merci :-)

[Pierre Fauconnier]

Tu ne peux avoir qu'une procédure Worksheet_Change par feuille. Si tes TCD sont sur la même feuille, tu dois regrouper les tests dans la même procédure

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Sub Worksheet_Change(ByVal Target As Range)
  Select Case Target.Address
    Case Range("age").Address
      ActiveSheet.PivotTables("TCD_Age_1").RefreshTable
    Case Range("age_2").Address
      ActiveSheet.PivotTables("TCD_Age_2").RefreshTable
    Case Range("age_3").Address
      ActiveSheet.PivotTables("TCD_Age_3").RefreshTable
  End Select
End Sub

Cela Dit, si tes TCD s'appuient sur le même tableau, ce qui me semble être le cas ici, en rafraîchir un rafraîchit automatiquement les autres, normalement, car ils utilisent le même "cache" de données (le "cache", c'est une photo qui est prise au moment de la création du tcd et qui est rafraîchie lors de l'actualisation du tcd, car le tcd ne travaille pas directement sur le tableau source, mais sur une "copie" mise en mémoire pour gagner du temps et utiliser des algorithmes de calcul non disponible pour un simple tableau Excel).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Union(Range("age"), Range("age_2"), Range("age_3"))) Is Nothing Then _
    ActiveSheet.PivotTables(1).PivotCache.Refresh
End Sub

[Josvin]

Ah d'accord, je comprends mieux :-)

C'est tout bon maintenant, tout fonctionne, merci beaucoup !!